import pandas as pd
from datetime import datetime

# === Configuración ===
archivo_excel = "pacientes.xlsx"  # el Excel que vas a subir
archivo_paciente      = "paciente.sql"
archivo_establec      = "establecimiento.sql"
archivo_ingreso       = "ingreso.sql"


def texto_a_sql(valor):
    """Devuelve un literal de texto SQL (o NULL si viene vacío)."""
    if pd.isna(valor):
        return "NULL"
    s = str(valor).strip()
    if s == "" or s.upper() == "NAN":
        return "NULL"
    s = s.replace("'", "''")
    return f"'{s}'"


def fecha_a_sql(valor):
    """Convierte una fecha a 'YYYY-MM-DD' (o NULL)."""
    if pd.isna(valor):
        return "NULL"
    if not isinstance(valor, (pd.Timestamp, datetime)):
        valor = pd.to_datetime(valor, dayfirst=True, errors="coerce")
    if pd.isna(valor):
        return "NULL"
    return f"'{valor.strftime('%Y-%m-%d')}'"


def main():
    df = pd.read_excel(archivo_excel, dtype=str)

    print("Columnas detectadas:", list(df.columns))

    sentencias_pac   = []
    sentencias_estab = []
    sentencias_ing   = []

    vistos_estab    = set()
    vistos_paciente = set()
    vistos_ingreso  = set()

    for i, fila in df.iterrows():
        # ---------- INTERNO ----------
        interno_raw = fila.get("INTERNO", "")
        if interno_raw is None or str(interno_raw).strip() == "":
            print(f"Fila {i}: sin INTERNO, se omite.")
            continue
        interno = str(interno_raw).strip()
        interno_sql = texto_a_sql(interno)

        # ---------- ESTABLECIMIENTO ----------
        cod_est_raw = fila.get("CODIGO_ESTABLECIMIENTO", "")
        cod_est = str(cod_est_raw).strip() if cod_est_raw is not None else ""

        if cod_est != "" and cod_est not in vistos_estab:
            cod_est_sql          = texto_a_sql(cod_est)
            nom_est_sql          = texto_a_sql(fila.get("NOMBRE_ESTABLECIMIENTO"))
            epc_ciudad_sql       = texto_a_sql(fila.get("EPC_CIUDAD"))
            epc_depart_sql       = texto_a_sql(fila.get("EPC_DEPARTAMENTO"))
            regional_sql         = texto_a_sql(fila.get("REGIONAL"))
            regional_norm_sql    = texto_a_sql(fila.get("REGIONAL_NORMALIZADA"))

            sentencia_e = (
                "INSERT INTO establecimiento "
                "(codigo_establecimiento, nombre_establecimiento, "
                "epc_ciudad, epc_departamento, regional, regional_normalizada) "
                f"VALUES ({cod_est_sql}, {nom_est_sql}, "
                f"{epc_ciudad_sql}, {epc_depart_sql}, "
                f"{regional_sql}, {regional_norm_sql}) "
                "ON CONFLICT (codigo_establecimiento) DO UPDATE SET "
                "nombre_establecimiento = EXCLUDED.nombre_establecimiento, "
                "epc_ciudad             = EXCLUDED.epc_ciudad, "
                "epc_departamento       = EXCLUDED.epc_departamento, "
                "regional               = EXCLUDED.regional, "
                "regional_normalizada   = EXCLUDED.regional_normalizada;"
            )
            sentencias_estab.append(sentencia_e)
            vistos_estab.add(cod_est)

        # ---------- PACIENTE ----------
        if interno not in vistos_paciente:
            tipo_doc_sql    = texto_a_sql(fila.get("TIPO_DOCUMENTO"))
            num_doc_sql     = texto_a_sql(fila.get("NUMERO_DOCUMENTO"))
            primer_ape_sql  = texto_a_sql(fila.get("PRIMER_APELLIDO"))
            segundo_ape_sql = texto_a_sql(fila.get("SEGUNDO_APELLIDO"))
            primer_nom_sql  = texto_a_sql(fila.get("PRIMER_NOMBRE"))
            segundo_nom_sql = texto_a_sql(fila.get("SEGUNDO_NOMBRE"))
            sexo_sql        = texto_a_sql(fila.get("SEXO"))
            fecha_nac_sql   = fecha_a_sql(fila.get("FECHA_NACIMIENTO"))

            sentencia_p = (
                "INSERT INTO paciente "
                "(interno, tipo_documento, numero_documento, "
                "primer_apellido, segundo_apellido, primer_nombre, segundo_nombre, "
                "fecha_nacimiento, edad, sexo, activo) "
                f"VALUES ({interno_sql}, {tipo_doc_sql}, {num_doc_sql}, "
                f"{primer_ape_sql}, {segundo_ape_sql}, "
                f"{primer_nom_sql}, {segundo_nom_sql}, "
                f"{fecha_nac_sql}, NULL, {sexo_sql}, true) "
                "ON CONFLICT (interno) DO UPDATE SET "
                "tipo_documento   = EXCLUDED.tipo_documento, "
                "numero_documento = EXCLUDED.numero_documento, "
                "primer_apellido  = EXCLUDED.primer_apellido, "
                "segundo_apellido = EXCLUDED.segundo_apellido, "
                "primer_nombre    = EXCLUDED.primer_nombre, "
                "segundo_nombre   = EXCLUDED.segundo_nombre, "
                "fecha_nacimiento = EXCLUDED.fecha_nacimiento, "
                "sexo             = EXCLUDED.sexo, "
                "activo           = true;"
            )
            sentencias_pac.append(sentencia_p)
            vistos_paciente.add(interno)

        # ---------- INGRESO ----------
        if interno not in vistos_ingreso:
            cod_est_sql_ing = texto_a_sql(cod_est) if cod_est != "" else "NULL"
            estado_sql      = texto_a_sql(fila.get("ESTADO"))
            fecha_ing_sql   = fecha_a_sql(fila.get("FECHA_INGRESO"))
            nac_sql         = texto_a_sql(fila.get("NACIONALIDAD"))

            sentencia_i = (
                "INSERT INTO ingreso "
                "(interno, codigo_establecimiento, estado, fecha_ingreso, nacionalidad) "
                f"VALUES ({interno_sql}, {cod_est_sql_ing}, {estado_sql}, "
                f"{fecha_ing_sql}, {nac_sql}) "
                "ON CONFLICT (interno) DO UPDATE SET "
                "codigo_establecimiento = EXCLUDED.codigo_establecimiento, "
                "estado                 = EXCLUDED.estado, "
                "fecha_ingreso          = EXCLUDED.fecha_ingreso, "
                "nacionalidad           = EXCLUDED.nacionalidad;"
            )
            sentencias_ing.append(sentencia_i)
            vistos_ingreso.add(interno)

    # ---------- Guardar archivos SQL ----------
    with open(archivo_establec, "w", encoding="utf-8") as f_est:
        f_est.write("-- UPSERTS TABLA ESTABLECIMIENTO\n")
        for s in sentencias_estab:
            f_est.write(s + "\n")

    with open(archivo_paciente, "w", encoding="utf-8") as f_pac:
        f_pac.write("-- UPSERTS TABLA PACIENTE\n")
        # Marcamos todos como inactivos, y los del Excel quedarán activos
        f_pac.write("UPDATE paciente SET activo = false;\n")
        for s in sentencias_pac:
            f_pac.write(s + "\n")

    with open(archivo_ingreso, "w", encoding="utf-8") as f_ing:
        f_ing.write("-- UPSERTS TABLA INGRESO\n")
        for s in sentencias_ing:
            f_ing.write(s + "\n")

    print("Archivos generados:")
    print("  ", archivo_establec, f"({len(sentencias_estab)} sentencias)")
    print("  ", archivo_paciente, f"({len(sentencias_pac)} sentencias + UPDATE activo=false)")
    print("  ", archivo_ingreso, f"({len(sentencias_ing)} sentencias)")


if __name__ == "__main__":
    main()