<?php

declare(strict_types=1);

/**
 * Importa ciudades de Colombia desde archivo DANE (DIVIPOLA CSV).
 *
 * Uso:
 *   /Applications/XAMPP/xamppfiles/bin/php scripts/import_dane_divipola.php /ruta/divipola.csv
 *   /Applications/XAMPP/xamppfiles/bin/php scripts/import_dane_divipola.php /ruta/divipola.csv --delimiter=';'
 *   /Applications/XAMPP/xamppfiles/bin/php scripts/import_dane_divipola.php /ruta/divipola.csv --replace
 */

$options = parseArguments(array_slice($argv, 1));
if ($options['help'] || $options['csv'] === '') {
    printUsage();
    exit($options['help'] ? 0 : 1);
}

$csvPath = $options['csv'];
if (!is_file($csvPath)) {
    fwrite(STDERR, "[FAIL] No existe el archivo CSV: {$csvPath}\n");
    exit(1);
}

$envPath = dirname(__DIR__) . '/.env';
if (!is_file($envPath)) {
    fwrite(STDERR, "[FAIL] No se encontró el archivo .env\n");
    exit(1);
}

$env = parseEnvFile($envPath);
$dbHost = (string) ($env['DB_HOST'] ?? '127.0.0.1');
$dbPort = (int) ($env['DB_PORT'] ?? 3306);
$dbName = (string) ($env['DB_DATABASE'] ?? 'veltrix_pos');
$dbUser = (string) ($env['DB_USERNAME'] ?? 'root');
$dbPass = (string) ($env['DB_PASSWORD'] ?? '');

try {
    $pdo = new PDO(
        sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', $dbHost, $dbPort, $dbName),
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
} catch (Throwable $e) {
    fwrite(STDERR, '[FAIL] Conexión DB: ' . $e->getMessage() . PHP_EOL);
    exit(1);
}

if (!cityMetadataColumnsExist($pdo)) {
    fwrite(
        STDERR,
        "[FAIL] La tabla ciudades no tiene columnas DANE (departamento/codigo_dane).\n" .
        "Ejecuta antes: SOURCE database/migrations/018_add_dane_city_metadata.sql;\n"
    );
    exit(1);
}

$countryId = ensureColombiaCountry($pdo);

$handle = fopen($csvPath, 'rb');
if ($handle === false) {
    fwrite(STDERR, "[FAIL] No se pudo abrir el CSV: {$csvPath}\n");
    exit(1);
}

$firstLine = fgets($handle);
if (!is_string($firstLine)) {
    fclose($handle);
    fwrite(STDERR, "[FAIL] El CSV está vacío.\n");
    exit(1);
}

$delimiter = $options['delimiter'] ?? detectDelimiter($firstLine);
rewind($handle);

$headers = fgetcsv($handle, 0, $delimiter);
if (!is_array($headers) || $headers === []) {
    fclose($handle);
    fwrite(STDERR, "[FAIL] No se pudo leer la cabecera del CSV.\n");
    exit(1);
}

$headerMap = [];
foreach ($headers as $index => $header) {
    $headerMap[normalizeHeader((string) $header)] = $index;
}

$deptCodeIndex = findHeaderIndex(
    $headerMap,
    [
        'codigo_departamento',
        'cod_departamento',
        'codigo_dpto',
        'cod_dpto',
        'dpto_codigo',
        'departamento_codigo',
        'codigo_dane_del_departamento',
        'codigo_dane_departamento',
    ],
    ['departamento', 'codigo']
);
$deptNameIndex = findHeaderIndex(
    $headerMap,
    [
        'nombre_departamento',
        'nom_departamento',
        'departamento_nombre',
        'departamento',
        'nom_dpto',
        'dpto_nombre',
    ],
    ['departamento', 'nombre']
);
$cityCodeIndex = findHeaderIndex(
    $headerMap,
    [
        'codigo_municipio',
        'cod_municipio',
        'codigo_mpio',
        'cod_mpio',
        'municipio_codigo',
        'codigo_dane_del_municipio',
        'codigo_dane_municipio',
        'codigo_divipola',
    ],
    ['municipio', 'codigo']
);
$cityNameIndex = findHeaderIndex(
    $headerMap,
    [
        'nombre_municipio',
        'nom_municipio',
        'municipio_nombre',
        'municipio',
        'nom_mpio',
        'nombre_ciudad',
        'ciudad',
    ],
    ['municipio', 'nombre']
);

if ($cityCodeIndex === null || $cityNameIndex === null) {
    fclose($handle);
    fwrite(
        STDERR,
        "[FAIL] No se identificaron columnas de municipio en el CSV.\n" .
        "Cabeceras detectadas: " . implode(', ', array_keys($headerMap)) . PHP_EOL
    );
    exit(1);
}

$now = date('Y-m-d H:i:s');
$existingCodes = existingDaneCodes($pdo, $countryId);
$upsert = $pdo->prepare(
    'INSERT INTO ciudades (pais_id, nombre, departamento, codigo_dane, created_at)
     VALUES (:pais_id, :nombre, :departamento, :codigo_dane, :created_at)
     ON DUPLICATE KEY UPDATE
        nombre = VALUES(nombre),
        departamento = VALUES(departamento)'
);

if ($options['replace']) {
    $clear = $pdo->prepare('DELETE FROM ciudades WHERE pais_id = :pais_id');
    $clear->execute([':pais_id' => $countryId]);
    $existingCodes = [];
}

$totalRows = 0;
$inserted = 0;
$updated = 0;
$skipped = 0;

try {
    $pdo->beginTransaction();

    while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
        $totalRows++;

        $deptCode = digitsOnly(getCsvValue($row, $deptCodeIndex));
        $deptName = normalizeText(getCsvValue($row, $deptNameIndex));
        $cityCodeRaw = digitsOnly(getCsvValue($row, $cityCodeIndex));
        $cityName = normalizeText(getCsvValue($row, $cityNameIndex));

        if ($cityCodeRaw === '' || $cityName === '') {
            $skipped++;
            continue;
        }

        $cityCode = normalizeCityCode($cityCodeRaw, $deptCode);
        if ($cityCode === '') {
            $skipped++;
            continue;
        }

        if ($deptName === '') {
            $deptName = null;
        }

        $upsert->execute([
            ':pais_id' => $countryId,
            ':nombre' => truncateUtf8($cityName, 120),
            ':departamento' => $deptName !== null ? truncateUtf8($deptName, 120) : null,
            ':codigo_dane' => $cityCode,
            ':created_at' => $now,
        ]);

        if (isset($existingCodes[$cityCode])) {
            $updated++;
        } else {
            $existingCodes[$cityCode] = true;
            $inserted++;
        }
    }

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    fclose($handle);
    fwrite(STDERR, '[FAIL] Error importando DANE: ' . $e->getMessage() . PHP_EOL);
    exit(1);
}

fclose($handle);

echo "[OK] Importación DANE completada\n";
echo "Archivo: {$csvPath}\n";
echo "Delimitador: " . ($delimiter === "\t" ? '\\t' : $delimiter) . "\n";
echo "Filas leídas: {$totalRows}\n";
echo "Insertadas: {$inserted}\n";
echo "Actualizadas: {$updated}\n";
echo "Omitidas: {$skipped}\n";

function parseArguments(array $args): array
{
    $options = [
        'csv' => '',
        'delimiter' => null,
        'replace' => false,
        'help' => false,
    ];

    foreach ($args as $arg) {
        if ($arg === '-h' || $arg === '--help') {
            $options['help'] = true;
            continue;
        }

        if ($arg === '--replace') {
            $options['replace'] = true;
            continue;
        }

        if (str_starts_with($arg, '--delimiter=')) {
            $value = (string) substr($arg, strlen('--delimiter='));
            $value = $value === '\\t' ? "\t" : $value;
            $options['delimiter'] = $value !== '' ? $value[0] : null;
            continue;
        }

        if ($options['csv'] === '') {
            $options['csv'] = $arg;
        }
    }

    return $options;
}

function printUsage(): void
{
    echo "Uso:\n";
    echo "  /Applications/XAMPP/xamppfiles/bin/php scripts/import_dane_divipola.php /ruta/divipola.csv [--delimiter=';'] [--replace]\n\n";
    echo "Opciones:\n";
    echo "  --delimiter=;   Delimitador CSV (por defecto se detecta automáticamente)\n";
    echo "  --replace       Borra ciudades actuales de Colombia antes de importar\n";
}

function parseEnvFile(string $envPath): array
{
    $result = [];
    $lines = file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: [];
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || str_starts_with($line, '#') || !str_contains($line, '=')) {
            continue;
        }
        [$key, $value] = explode('=', $line, 2);
        $result[trim($key)] = trim($value);
    }

    return $result;
}

function detectDelimiter(string $line): string
{
    $candidates = [';', ',', "\t", '|'];
    $bestDelimiter = ';';
    $bestCount = 0;

    foreach ($candidates as $candidate) {
        $count = count(str_getcsv($line, $candidate));
        if ($count > $bestCount) {
            $bestCount = $count;
            $bestDelimiter = $candidate;
        }
    }

    return $bestDelimiter;
}

function normalizeHeader(string $value): string
{
    $value = preg_replace('/^\xEF\xBB\xBF/u', '', trim($value)) ?? '';
    if (function_exists('mb_strtolower')) {
        $value = mb_strtolower($value, 'UTF-8');
    } else {
        $value = strtolower($value);
    }

    $converted = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
    if (is_string($converted) && $converted !== '') {
        $value = $converted;
    }

    $value = preg_replace('/[^a-z0-9]+/', '_', $value) ?? '';
    return trim($value, '_');
}

function findHeaderIndex(array $headerMap, array $candidates, array $contains): ?int
{
    foreach ($candidates as $candidate) {
        if (isset($headerMap[$candidate])) {
            return (int) $headerMap[$candidate];
        }
    }

    foreach ($headerMap as $normalized => $index) {
        $allMatch = true;
        foreach ($contains as $piece) {
            if (!str_contains($normalized, $piece)) {
                $allMatch = false;
                break;
            }
        }
        if ($allMatch) {
            return (int) $index;
        }
    }

    return null;
}

function getCsvValue(array $row, ?int $index): string
{
    if ($index === null || !array_key_exists($index, $row)) {
        return '';
    }

    return trim((string) $row[$index]);
}

function digitsOnly(string $value): string
{
    return preg_replace('/\D+/', '', $value) ?? '';
}

function normalizeText(string $value): string
{
    $value = trim($value);
    $value = preg_replace('/\s+/u', ' ', $value) ?? $value;
    return $value;
}

function truncateUtf8(string $value, int $maxLength): string
{
    if ($maxLength <= 0) {
        return '';
    }

    if (function_exists('mb_substr')) {
        return mb_substr($value, 0, $maxLength, 'UTF-8');
    }

    return substr($value, 0, $maxLength);
}

function normalizeCityCode(string $cityCode, string $deptCode): string
{
    $cityCode = digitsOnly($cityCode);
    $deptCode = digitsOnly($deptCode);

    if ($cityCode === '') {
        return '';
    }

    if (strlen($cityCode) === 5) {
        return $cityCode;
    }

    if (strlen($cityCode) <= 3 && $deptCode !== '') {
        return str_pad(substr($deptCode, 0, 2), 2, '0', STR_PAD_LEFT) .
            str_pad(substr($cityCode, 0, 3), 3, '0', STR_PAD_LEFT);
    }

    if (strlen($cityCode) === 4) {
        return '0' . $cityCode;
    }

    if (strlen($cityCode) > 5) {
        return substr($cityCode, 0, 5);
    }

    return str_pad($cityCode, 5, '0', STR_PAD_LEFT);
}

function cityMetadataColumnsExist(PDO $pdo): bool
{
    $stmt = $pdo->query(
        'SELECT column_name
         FROM information_schema.columns
         WHERE table_schema = DATABASE()
           AND table_name = "ciudades"
           AND column_name IN ("departamento", "codigo_dane")'
    );

    $columns = [];
    foreach ($stmt->fetchAll() ?: [] as $row) {
        $column = (string) ($row['column_name'] ?? '');
        if ($column !== '') {
            $columns[$column] = true;
        }
    }

    return isset($columns['departamento'], $columns['codigo_dane']);
}

function ensureColombiaCountry(PDO $pdo): int
{
    $select = $pdo->prepare(
        'SELECT id
         FROM paises
         WHERE codigo_iso2 = :iso2
         LIMIT 1'
    );
    $select->execute([':iso2' => 'CO']);
    $id = (int) ($select->fetchColumn() ?: 0);
    if ($id > 0) {
        return $id;
    }

    $insert = $pdo->prepare(
        'INSERT INTO paises (nombre, codigo_iso2, codigo_iso3, created_at)
         VALUES (:nombre, :iso2, :iso3, :created_at)'
    );
    $insert->execute([
        ':nombre' => 'Colombia',
        ':iso2' => 'CO',
        ':iso3' => 'COL',
        ':created_at' => date('Y-m-d H:i:s'),
    ]);

    return (int) $pdo->lastInsertId();
}

function existingDaneCodes(PDO $pdo, int $countryId): array
{
    $stmt = $pdo->prepare(
        'SELECT codigo_dane
         FROM ciudades
         WHERE pais_id = :pais_id
           AND codigo_dane IS NOT NULL
           AND codigo_dane <> ""'
    );
    $stmt->execute([':pais_id' => $countryId]);

    $result = [];
    foreach ($stmt->fetchAll() ?: [] as $row) {
        $code = (string) ($row['codigo_dane'] ?? '');
        if ($code !== '') {
            $result[$code] = true;
        }
    }

    return $result;
}
