@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
REM ====== CONFIG ======
set HOST=localhost
set DB=thirty
set USER=Fresh
set PASS=9j\V<2&2-f02Pre5(R=~E<$wg}.N:B
set PHP_EXE=php
REM set PHP_EXE=C:\xampp\php\php.exe
REM ====================

set BASE=%~dp0
set TMP=%BASE%_tmp_locations
set CSV_MUN=%BASE%municipalities_full.csv
set CSV_CP=%BASE%postal_codes_full.csv
set IMPORT_PHP=%BASE%import_locations.php

mkdir "%TMP%" >nul 2>&1

echo.
echo === Fuerza TLS 1.2 para descargas (PowerShell) ===
powershell -NoProfile -Command "[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12"

echo.
echo === Descargando municipios (INE/CodeForSpain) ===
powershell -NoProfile -Command "try { Invoke-WebRequest -UseBasicParsing -Uri 'https://raw.githubusercontent.com/codeforspain/ds-organizacion-administrativa/master/data/municipios.csv' -OutFile '%TMP%\municipios.csv'; exit 0 } catch { Write-Host $_.Exception.Message; exit 1 }"
if errorlevel 1 (echo [ERROR] No se pudo descargar municipios.csv && exit /b 1)

echo.
echo === Generando municipalities_full.csv ===
powershell -NoProfile -Command ^
  "$csv = Import-Csv -Path '%TMP%\municipios.csv';" ^
  "$out = @(); $seen = @{};" ^
  "foreach($r in $csv) {" ^
  "  $code = [string]$r.municipio_id; $prov = ([string]$r.provincia_id).PadLeft(2,'0'); $name = [string]$r.nombre;" ^
  "  if([string]::IsNullOrWhiteSpace($code) -or [string]::IsNullOrWhiteSpace($name) -or [string]::IsNullOrWhiteSpace($prov)) { continue }" ^
  "  if(-not $seen.ContainsKey($code)) { $out += [PSCustomObject]@{ code=$code; name=$name; province_code=$prov }; $seen[$code]=$true }" ^
  "}" ^
  "$out | Export-Csv -Path '%CSV_MUN%' -NoTypeInformation -Encoding UTF8"
if errorlevel 1 (echo [ERROR] Fallo creando %CSV_MUN% && exit /b 1)

echo.
echo === Descargando C.P. (GeoNames) ===
powershell -NoProfile -Command "try { Invoke-WebRequest -UseBasicParsing -Uri 'https://download.geonames.org/export/zip/ES.zip' -OutFile '%TMP%\ES.zip'; exit 0 } catch { Write-Host $_.Exception.Message; exit 1 }"
if errorlevel 1 (echo [ERROR] No se pudo descargar ES.zip && echo Si tu PowerShell es antiguo, abre una consola y ejecuta: ^
    ^
    powershell -NoProfile -Command \"[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12\" ^
    ^
    y vuelve a ejecutar este .bat && exit /b 1)

echo.
echo === Extrayendo ES.txt y generando postal_codes_full.csv ===
powershell -NoProfile -Command ^
  "Add-Type -AssemblyName 'System.IO.Compression.FileSystem';" ^
  "if (Test-Path '%TMP%\ES.txt') { Remove-Item '%TMP%\ES.txt' -Force }" ^
  "[IO.Compression.ZipFile]::ExtractToDirectory('%TMP%\ES.zip','%TMP%');" ^
  "if (-Not (Test-Path '%TMP%\ES.txt')) { Write-Host 'ES.txt no encontrado tras extraer ZIP'; exit 1 }" ^
  "$rows = Get-Content -Path '%TMP%\ES.txt'; $seen=@{}; $out=@();" ^
  "foreach($line in $rows) { $parts = $line -split \"`t\"; if($parts.Count -lt 3){ continue } $cp=$parts[1].Trim(); $mun=$parts[2].Trim(); if(!$cp -or !$mun){ continue } $key=\"$cp|$mun\"; if(-not $seen.ContainsKey($key)){ $out += [PSCustomObject]@{ postal_code=$cp; municipality_name=$mun }; $seen[$key]=$true } }" ^
  "$out | Export-Csv -Path '%CSV_CP%' -NoTypeInformation -Encoding UTF8"
if errorlevel 1 (echo [ERROR] Fallo creando %CSV_CP% && exit /b 1)

echo.
echo === Importando en MySQL (provincias + municipios + C.P.) ===
"%PHP_EXE%" "%IMPORT_PHP%" --host=%HOST% --db=%DB% --user=%USER% --pass="%PASS%" --seed-provinces --municipalities="%CSV_MUN%" --postalcodes="%CSV_CP%"
if errorlevel 1 (echo [ERROR] Fallo en import_locations.php && exit /b 1)

echo.
echo === Limpieza temporal ===
rmdir /s /q "%TMP%" >nul 2>&1

echo.
echo ==== Hecho ====
echo Municipios: "%CSV_MUN%"
echo Codigos Postales: "%CSV_CP%"
exit /b 0
