#!/usr/bin/env python3
"""
Genera database/sql/insert_ubigeo_exterior.sql a partir de Extranjero_SUBIR.xlsx.

Mapea la jerarquía del voto en el exterior de ONPE al UBIGEO del sistema:
  DEPARTAMENTO = continente   -> departments (code 2 díg, exterior=1)
  PROVINCIA    = país         -> provinces   (code DDPP)
  DISTRITO     = ciudad       -> districts   (code DDPPCC)
  LOCAL        = sede/embajada-> voting_locations
  MESA         = número       -> voting_tables

El SQL resultante es idempotente (re-ejecutable) y NO hardcodea ids:
resuelve todas las FK por subconsulta sobre `code` y por (district, name).
"""
import re
import openpyxl

XLSX = "Extranjero_SUBIR.xlsx"
SHEET = "PR-ESP_Presidencial_2026-06-06_"
OUT = "database/sql/insert_ubigeo_exterior.sql"
DEPT_BASE = 26  # primer code de continente (existentes: 01..25)

def norm(s):
    if s is None:
        return ""
    return re.sub(r"\s+", " ", str(s)).strip()

def sq(s):
    return s.replace("'", "''")

wb = openpyxl.load_workbook(XLSX, read_only=True, data_only=True)
ws = wb[SHEET]
rows = [r for r in ws.iter_rows(values_only=True)][1:]

# Estructuras preservando orden de aparición (orden ONPE / coherente con table_number)
continents = []                  # [name]
countries = {}                   # cont -> [country]
cities = {}                      # (cont,country) -> [city]
locations = {}                   # (cont,country,city) -> [loc]
tables = {}                      # (cont,country,city,loc) -> [table_number]

for r in rows:
    if not r or r[0] is None:
        continue
    tn = int(r[0])
    loc, city, country, cont = norm(r[1]), norm(r[2]), norm(r[3]), norm(r[4])
    if cont not in countries:
        continents.append(cont); countries[cont] = []
    if country not in countries[cont]:
        countries[cont].append(country); cities[(cont, country)] = []
    if city not in cities[(cont, country)]:
        cities[(cont, country)].append(city); locations[(cont, country, city)] = []
    if loc not in locations[(cont, country, city)]:
        locations[(cont, country, city)].append(loc); tables[(cont, country, city, loc)] = []
    tables[(cont, country, city, loc)].append(tn)

# Asignación de códigos
dept_code = {}    # cont -> "26"
prov_code = {}    # (cont,country) -> "2601"
dist_code = {}    # (cont,country,city) -> "260101"
for i, cont in enumerate(continents):
    dc = f"{DEPT_BASE + i:02d}"
    dept_code[cont] = dc
    for j, country in enumerate(countries[cont], start=1):
        pc = f"{dc}{j:02d}"
        prov_code[(cont, country)] = pc
        for k, city in enumerate(cities[(cont, country)], start=1):
            dist_code[(cont, country, city)] = f"{pc}{k:02d}"

out = []
w = out.append
w("-- =====================================================================")
w("-- UBIGEO EXTERIOR (voto peruano en el extranjero) — datos generados")
w("-- Fuente: Extranjero_SUBIR.xlsx (PR-ESP Presidencial 2026-06-06)")
w("-- Generador: database/sql/generate_exterior_sql.py")
w("--")
w("-- Idempotente y re-ejecutable. No hardcodea ids: resuelve FK por `code`")
w("-- y por (district, name). Requiere la columna departments.exterior")
w("-- (la añade la migración add_exterior_to_departments_table; el bloque de")
w("--  abajo también la crea si faltara, para cargas manuales del dump).")
w("-- =====================================================================")
w("")
w("SET NAMES utf8mb4;")
w("SET @OLD_FK := @@FOREIGN_KEY_CHECKS;")
w("")
w("-- 0) Asegura la columna `exterior` (idempotente, por si se carga sin migrar)")
w("SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS")
w("    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'departments' AND COLUMN_NAME = 'exterior');")
w("SET @ddl := IF(@col_exists = 0,")
w("    'ALTER TABLE departments ADD COLUMN exterior TINYINT(1) NOT NULL DEFAULT 0 AFTER code, ADD INDEX departments_exterior_index (exterior)',")
w("    'DO 0');")
w("PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;")
w("")

# 1) Departamentos (continentes)
w("-- 1) Departamentos = continentes")
vals = ", ".join(
    f"('{sq(cont)}', '{dept_code[cont]}', 1, NOW(), NOW())" for cont in continents
)
w("INSERT INTO departments (name, code, exterior, created_at, updated_at) VALUES")
w(vals)
w("ON DUPLICATE KEY UPDATE name = VALUES(name), exterior = VALUES(exterior);")
w("")

# 2) Provincias (países)
w("-- 2) Provincias = países")
for cont in continents:
    for country in countries[cont]:
        pc = prov_code[(cont, country)]
        w(f"INSERT INTO provinces (department_id, name, code, created_at, updated_at)")
        w(f"SELECT id, '{sq(country)}', '{pc}', NOW(), NOW() FROM departments WHERE code = '{dept_code[cont]}'")
        w(f"ON DUPLICATE KEY UPDATE name = VALUES(name), department_id = VALUES(department_id);")
w("")

# 3) Distritos (ciudades)
w("-- 3) Distritos = ciudades")
for cont in continents:
    for country in countries[cont]:
        for city in cities[(cont, country)]:
            cc = dist_code[(cont, country, city)]
            pc = prov_code[(cont, country)]
            w(f"INSERT INTO districts (province_id, name, code, created_at, updated_at)")
            w(f"SELECT id, '{sq(city)}', '{cc}', NOW(), NOW() FROM provinces WHERE code = '{pc}'")
            w(f"ON DUPLICATE KEY UPDATE name = VALUES(name), province_id = VALUES(province_id);")
w("")

# 4) Locales de votación
w("-- 4) Locales de votación (idempotente por (district, name))")
for cont in continents:
    for country in countries[cont]:
        for city in cities[(cont, country)]:
            cc = dist_code[(cont, country, city)]
            for loc in locations[(cont, country, city)]:
                cnt = len(tables[(cont, country, city, loc)])
                w(f"INSERT INTO voting_locations (district_id, name, tables_count, created_at, updated_at)")
                w(f"SELECT d.id, '{sq(loc)}', {cnt}, NOW(), NOW() FROM districts d WHERE d.code = '{cc}'")
                w(f"  AND NOT EXISTS (SELECT 1 FROM voting_locations vl WHERE vl.district_id = d.id AND vl.name = '{sq(loc)}');")
w("")

# 5) Mesas (una sentencia por local cubre todas sus mesas)
w("-- 5) Mesas (table_number desde el Excel; idempotente por (location, number))")
for cont in continents:
    for country in countries[cont]:
        for city in cities[(cont, country)]:
            cc = dist_code[(cont, country, city)]
            for loc in locations[(cont, country, city)]:
                tns = tables[(cont, country, city, loc)]
                union = " UNION ALL ".join(f"SELECT {tn} AS n" for tn in tns)
                w(f"INSERT INTO voting_tables (voting_location_id, table_number, created_at, updated_at)")
                w(f"SELECT vl.id, tn.n, NOW(), NOW()")
                w(f"FROM voting_locations vl")
                w(f"JOIN districts d ON d.id = vl.district_id AND d.code = '{cc}'")
                w(f"JOIN ({union}) tn")
                w(f"WHERE vl.name = '{sq(loc)}'")
                w(f"  AND NOT EXISTS (SELECT 1 FROM voting_tables x WHERE x.voting_location_id = vl.id AND x.table_number = tn.n);")
w("")
w("-- Resumen esperado:")
w(f"--   departments(exterior=1): {len(continents)}")
w(f"--   provinces : {sum(len(v) for v in countries.values())}")
w(f"--   districts : {sum(len(v) for v in cities.values())}")
w(f"--   voting_locations : {sum(len(v) for v in locations.values())}")
w(f"--   voting_tables : {sum(len(v) for v in tables.values())}")

with open(OUT, "w", encoding="utf-8") as f:
    f.write("\n".join(out) + "\n")

print("OK ->", OUT)
print("continents:", len(continents),
      "countries:", sum(len(v) for v in countries.values()),
      "cities:", sum(len(v) for v in cities.values()),
      "locations:", sum(len(v) for v in locations.values()),
      "tables:", sum(len(v) for v in tables.values()))