<?php
/**
 * import_locations.php (NO CREA TABLAS)
 * - Completa/actualiza SOLO las tablas existentes: provinces, municipalities, postal_codes
 * - Modo WEB (formulario) y CLI (flags)
 * - Idempotente: usa INSERT IGNORE sobre claves existentes
 *
 * Tablas esperadas (según tu dump):
 *   provinces(code VARCHAR(2) PK, name, country_code default 'ES')
 *   municipalities(code VARCHAR(5) PK, name, province_code VARCHAR(2))
 *   postal_codes(id AI PK, code VARCHAR(5), municipality_code VARCHAR(5))
 *
 * CSV aceptados:
 *   Provincias -> NO requiere CSV (usa listado español 01..52)
 *   Municipios:
 *      A) INE/CodeForSpain: municipio_id, provincia_id, nombre
 *      B) Genérico: code, name, province_code
 *   Códigos postales:
 *      A) Directo: code, municipality_code
 *      B) Con nombre: postal_code, municipality_name  (se resuelve a municipality_code por nombre + provincia = LEFT(postal_code,2))
 *
 * Seguridad: cambia ADMIN_SECRET y accede con ?key=TU_SECRETO
 */

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);

const ADMIN_SECRET = 'Manolo'; // <-- CAMBIA ESTO
function is_cli(): bool { return (PHP_SAPI === 'cli' || php_sapi_name() === 'cli'); }
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

/* --------------------------- Conexión y checks --------------------------- */
function pdo_connect(string $host, string $db, string $user, string $pass): PDO {
  return new PDO("mysql:host={$host};dbname={$db};charset=utf8mb4", $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  ]);
}
function table_exists(PDO $pdo, string $t): bool {
  try { $pdo->query("SELECT 1 FROM `{$t}` LIMIT 1"); return true; }
  catch(Throwable $e){ return false; }
}

/* --------------------------- Operaciones --------------------------- */
function upsert_provinces(PDO $pdo): void {
  if (!table_exists($pdo,'provinces')) { echo "❌ Falta tabla `provinces` (no se crean tablas)\n"; return; }
  $list = [
    ['01','Araba/Álava'],['02','Albacete'],['03','Alicante'],['04','Almería'],
    ['05','Ávila'],['06','Badajoz'],['07','Balears, Illes'],['08','Barcelona'],
    ['09','Burgos'],['10','Cáceres'],['11','Cádiz'],['12','Castellón'],
    ['13','Ciudad Real'],['14','Córdoba'],['15','Coruña, A'],['16','Cuenca'],
    ['17','Girona'],['18','Granada'],['19','Guadalajara'],['20','Gipuzkoa'],
    ['21','Huelva'],['22','Huesca'],['23','Jaén'],['24','León'],
    ['25','Lleida'],['26','Rioja, La'],['27','Lugo'],['28','Madrid'],
    ['29','Málaga'],['30','Murcia'],['31','Navarra'],['32','Ourense'],
    ['33','Asturias'],['34','Palencia'],['35','Palmas, Las'],['36','Pontevedra'],
    ['37','Salamanca'],['38','Santa Cruz de Tenerife'],['39','Cantabria'],['40','Segovia'],
    ['41','Sevilla'],['42','Soria'],['43','Tarragona'],['44','Teruel'],
    ['45','Toledo'],['46','Valencia'],['47','Valladolid'],['48','Bizkaia'],
    ['49','Zamora'],['50','Zaragoza'],['51','Ceuta'],['52','Melilla'],
  ];
  $ins = $pdo->prepare("INSERT IGNORE INTO `provinces` (`code`,`name`,`country_code`) VALUES (?,?, 'ES')");
  $added=0;
  foreach($list as [$code,$name]){
    $ins->execute([$code,$name]);
    if ($ins->rowCount()>0){ $added++; echo "➕ Provincia: [$code] $name\n"; }
  }
  echo $added? "✅ Provincias añadidas: $added\n" : "✔ Provincias ya estaban completas\n";
}

function import_municipalities(PDO $pdo, string $csv_path): void {
  if (!table_exists($pdo,'municipalities')) { echo "❌ Falta tabla `municipalities` (no se crean tablas)\n"; return; }
  if (!is_readable($csv_path)) { echo "❌ No puedo leer CSV municipios: $csv_path\n"; return; }

  $fh = fopen($csv_path,'r'); if(!$fh){ echo "❌ Error abriendo CSV municipios\n"; return; }
  $header = fgetcsv($fh);
  $map = array_flip(array_map('strtolower',$header ?: []));

  // Intento de detección de esquema
  $hasINE = isset($map['municipio_id'], $map['provincia_id'], $map['nombre']);
  $hasGEN = isset($map['code'], $map['name'], $map['province_code']);

  if (!$hasINE && !$hasGEN) {
    echo "❌ Cabeceras esperadas (una de las dos opciones):\n";
    echo "   A) municipio_id, provincia_id, nombre\n";
    echo "   B) code, name, province_code\n";
    fclose($fh); return;
  }

  $ins = $pdo->prepare("INSERT IGNORE INTO `municipalities` (`code`,`name`,`province_code`) VALUES (?,?,?)");
  $added=0; $rows=0;

  while(($row=fgetcsv($fh))!==false){
    $rows++;
    if ($hasINE){
      $code = trim((string)($row[$map['municipio_id']] ?? ''));
      $prov = trim((string)($row[$map['provincia_id']] ?? ''));
      $name = trim((string)($row[$map['nombre']] ?? ''));
    } else {
      $code = trim((string)($row[$map['code']] ?? ''));
      $prov = trim((string)($row[$map['province_code']] ?? ''));
      $name = trim((string)($row[$map['name']] ?? ''));
    }
    if($code===''||$prov===''||$name==='') continue;
    $ins->execute([$code,$name,$prov]);
    if ($ins->rowCount()>0) $added++;
  }
  fclose($fh);
  echo "✅ Municipios procesados: $rows, añadidos: $added\n";
}

function import_postal_codes(PDO $pdo, string $csv_path): void {
  if (!table_exists($pdo,'postal_codes')) { echo "❌ Falta tabla `postal_codes` (no se crean tablas)\n"; return; }
  if (!is_readable($csv_path)) { echo "❌ No puedo leer CSV C.P.: $csv_path\n"; return; }

  $fh = fopen($csv_path,'r'); if(!$fh){ echo "❌ Error abriendo CSV C.P.\n"; return; }
  $header = fgetcsv($fh);
  $map = array_flip(array_map('strtolower',$header ?: []));

  $hasDirect = isset($map['code'], $map['municipality_code']); // code=CP, municipality_code=INE(5)
  $hasByName = isset($map['postal_code'], $map['municipality_name']); // postal_code, municipality_name

  if (!$hasDirect && !$hasByName){
    echo "❌ Cabeceras esperadas:\n";
    echo "   A) code, municipality_code\n";
    echo "   B) postal_code, municipality_name\n";
    fclose($fh); return;
  }

  $ins = $pdo->prepare("INSERT IGNORE INTO `postal_codes` (`code`,`municipality_code`) VALUES (?,?)");
  $selMuni = $pdo->prepare("SELECT `code` FROM `municipalities` WHERE `name` COLLATE utf8mb4_unicode_ci = ? AND `province_code` = ? LIMIT 1");

  $added=0; $rows=0; $unresolved=0;

  while(($row=fgetcsv($fh))!==false){
    $rows++;
    if ($hasDirect){
      $cp   = trim((string)($row[$map['code']] ?? ''));
      $mune = trim((string)($row[$map['municipality_code']] ?? ''));
    } else {
      $cp   = trim((string)($row[$map['postal_code']] ?? ''));
      $munn = trim((string)($row[$map['municipality_name']] ?? ''));
      $prov = substr($cp,0,2);
      if ($cp===''||$munn===''||strlen($prov)!==2){ $unresolved++; continue; }
      // Resolver municipality_code por nombre + provincia (case/accent-insensitive por colación)
      $selMuni->execute([$munn, $prov]);
      $found = $selMuni->fetchColumn();
      if (!$found){ $unresolved++; continue; }
      $mune = $found;
    }
    if ($cp===''||$mune==='') continue;
    $ins->execute([$cp,$mune]);
    if ($ins->rowCount()>0) $added++;
  }
  fclose($fh);
  echo "✅ Códigos postales procesados: $rows, añadidos: $added";
  if ($unresolved>0) echo " (no resueltos por nombre: $unresolved)";
  echo "\n";
}

/* --------------------------- CLI --------------------------- */
if (is_cli()){
  $opt = getopt("", ["host:","db:","user:","pass:","seed-provinces","municipalities::","postalcodes::"]);
  $host=$opt['host']??'localhost'; $db=$opt['db']??'thirty'; $user=$opt['user']??'root'; $pass=$opt['pass']??'';
  try{
    $pdo = pdo_connect($host,$db,$user,$pass);
    echo "✅ Conectado a MySQL ({$host}/{$db})\n";
    if (array_key_exists('seed-provinces',$opt)) upsert_provinces($pdo);
    if (!empty($opt['municipalities'])) import_municipalities($pdo, $opt['municipalities']);
    if (!empty($opt['postalcodes']))    import_postal_codes($pdo, $opt['postalcodes']);
    if (!array_key_exists('seed-provinces',$opt) && empty($opt['municipalities']) && empty($opt['postalcodes'])) {
      echo "ℹ Nada que hacer. Usa --seed-provinces, --municipalities=CSV, --postalcodes=CSV\n";
    }
    echo "✨ Finalizado.\n";
  }catch(Throwable $e){ fwrite(STDERR,"❌ ".$e->getMessage()."\n"); exit(1); }
  exit;
}

/* --------------------------- WEB UI --------------------------- */
session_start();
if (!isset($_SESSION['csrf'])) $_SESSION['csrf']=bin2hex(random_bytes(16));
$csrf=$_SESSION['csrf'];

// gate
$allowed = (isset($_GET['key']) && hash_equals(ADMIN_SECRET, $_GET['key'])) ||
           (isset($_POST['key']) && hash_equals(ADMIN_SECRET, $_POST['key']));
if (!$allowed){ http_response_code(403); ?>
<!doctype html><meta charset="utf-8"><title>403</title>
<body style="font-family:system-ui;padding:24px">
<h1>🚫 Acceso restringido</h1>
<p>Define tu clave en <code>ADMIN_SECRET</code> y accede con <code>?key=TU_SECRETO</code>.</p>
</body><?php exit; }

if ($_SERVER['REQUEST_METHOD']==='POST' && isset($_POST['csrf']) && hash_equals($_SESSION['csrf'], $_POST['csrf'])){
  $host=$_POST['host']??'localhost'; $db=$_POST['db']??'thirty'; $user=$_POST['user']??'root'; $pass=$_POST['pass']??'';
  ?><!doctype html><meta charset="utf-8"><title>Importando…</title>
  <pre style="background:#111;color:#0f0;padding:16px;border-radius:12px;white-space:pre-wrap"><?php
  try{
    $pdo = pdo_connect($host,$db,$user,$pass);
    echo "✅ Conectado a MySQL ({$host}/{$db})\n"; @ob_flush(); @flush();
    if (!empty($_POST['seed_provinces'])) upsert_provinces($pdo);
    if (!empty($_FILES['municipalities']['tmp_name'])) import_municipalities($pdo, $_FILES['municipalities']['tmp_name']);
    if (!empty($_FILES['postalcodes']['tmp_name']))    import_postal_codes($pdo, $_FILES['postalcodes']['tmp_name']);
    echo "✨ Finalizado.\n";
  }catch(Throwable $e){ echo "❌ ".$e->getMessage()."\n"; }
  ?></pre><p><a href="<?php echo h($_SERVER['PHP_SELF']).'?key='.urlencode(ADMIN_SECRET); ?>">← Volver</a></p><?php
  exit;
}

?><!doctype html>
<html lang="es"><meta charset="utf-8">
<title>Completar tablas de ubicaciones (ES)</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<style>
  body{font-family:system-ui,Segoe UI,Roboto,Arial;margin:0;background:#0b1020;color:#e2e8f0}
  .wrap{max-width:900px;margin:0 auto;padding:28px}
  .card{background:#0f172a;border:1px solid #1e293b;border-radius:14px;padding:18px;margin:0 0 16px}
  label{display:block;margin:8px 0 4px;font-weight:600}
  input[type=text],input[type=password]{width:100%;padding:10px;border-radius:8px;border:1px solid #334155;background:#0b1220;color:#e2e8f0}
  input[type=file]{width:100%}
  .row{display:grid;grid-template-columns:1fr 1fr;gap:12px}
  .btn{background:#2563eb;color:#fff;border:none;border-radius:10px;padding:12px 16px;font-weight:700;cursor:pointer}
  .btn:hover{filter:brightness(1.1)}
  .hint{color:#94a3b8;font-size:13px}
  code{background:#111827;border-radius:6px;padding:2px 6px}
</style>
<body><div class="wrap">
<h1>Completar tablas existentes (España)</h1>

<form method="post" enctype="multipart/form-data">
  <input type="hidden" name="csrf" value="<?php echo h($csrf); ?>">
  <input type="hidden" name="key" value="<?php echo h(ADMIN_SECRET); ?>">

  <div class="card">
    <h2>Conexión MySQL</h2>
    <div class="row">
      <div><label>Host</label><input type="text" name="host" value="localhost"></div>
      <div><label>Base de datos</label><input type="text" name="db" value="thirty"></div>
    </div>
    <div class="row">
      <div><label>Usuario</label><input type="text" name="user" value="Fresh"></div>
      <div><label>Contraseña</label><input type="password" name="pass" value=""></div>
    </div>
  </div>

  <div class="card">
    <h2>Acciones</h2>
    <label><input type="checkbox" name="seed_provinces" value="1" checked> Insertar provincias (01–52)</label>
    <p class="hint">No crea tablas; solo INSERT IGNORE en las existentes.</p>
  </div>

  <div class="card">
    <h2>CSV (opcional)</h2>
    <label>Municipios (CSV)</label>
    <input type="file" name="municipalities" accept=".csv">
    <p class="hint">Cabeceras admitidas:
      <br>• <code>municipio_id,provincia_id,nombre</code> (INE/CodeForSpain)
      <br>• <code>code,name,province_code</code> (genérico)</p>
    <label>Códigos postales (CSV)</label>
    <input type="file" name="postalcodes" accept=".csv">
    <p class="hint">Cabeceras admitidas:
      <br>• <code>code,municipality_code</code>
      <br>• <code>postal_code,municipality_name</code> (se resuelve por nombre+provincia = primeros 2 dígitos del C.P.)</p>
  </div>

  <button class="btn" type="submit">Ejecutar</button>
</form>

<p class="hint">⚠️ Cambia <code>ADMIN_SECRET</code> y protege la URL. El script no crea tablas por diseño.</p>
</div></body>
</html>
