#!/usr/bin/env python3
"""
Script: load_instalaciones_terceros.py

Carga incremental de Instalaciones de Terceros

Origen:
- Giscloud (giscloud.huella_telecom)
- ServiceNow + CM (mantenimiento correctivo)

Modalidad:
- CREATE TABLE IF NOT EXISTS
- Inserción incremental (NOT EXISTS)
- Clave lógica: (tarea_id, origen)
"""

import logging
from datetime import date
import psycopg2

# ================= CONFIG =================

PG_CONN = {
    "host": "localhost",
    "dbname": "postgres",
    "user": "postgres",
    "password": "post3008"
}

SCHEMA = "aux"
TABLE = "instalaciones_terceros"

# ================= LOGGING =================

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s"
)

# ================= DDL =================

DDL_CREATE_TABLE = f"""
CREATE TABLE IF NOT EXISTS {SCHEMA}.{TABLE} (
    tarea_id TEXT NOT NULL,
    origen TEXT NOT NULL,
    fecha TIMESTAMP,
    latitud DOUBLE PRECISION,
    longitud DOUBLE PRECISION,
    ageom geometry(Point,4326),
    partido TEXT,
    upload_date DATE NOT NULL,
    CONSTRAINT uq_instalaciones_terceros
        UNIQUE (tarea_id, origen)
);
"""

DDL_GRANTS = f"""
GRANT SELECT ON {SCHEMA}.{TABLE} TO om_read;
GRANT SELECT ON {SCHEMA}.{TABLE} TO jibanez;
"""

# ================= SQL GISCLOUD =================

SQL_INSERT_GISCLOUD = f"""
INSERT INTO {SCHEMA}.{TABLE}
    (tarea_id, origen, fecha, latitud, longitud, ageom, partido, upload_date)
SELECT
    ht.proy_name || ':' || ht.fid       AS tarea_id,
    'GISCLOUD'                          AS origen,
    ht._created                         AS fecha,
    ST_Y(ht.geom)                       AS latitud,
    ST_X(ht.geom)                       AS longitud,
    ht.geom                             AS ageom,
    ht.partido                          AS partido,
    %s                                  AS upload_date
FROM giscloud.huella_telecom ht
WHERE NOT EXISTS (
    SELECT 1
    FROM {SCHEMA}.{TABLE} it
    WHERE it.tarea_id = ht.proy_name || ':' || ht.fid
      AND it.origen   = 'GISCLOUD'
);
"""

# ================= SQL SERVICENOW =================

SQL_INSERT_SN = f"""
INSERT INTO {SCHEMA}.{TABLE}
    (tarea_id, origen, fecha, latitud, longitud, ageom, partido, upload_date)
SELECT
    i.task_id                           AS tarea_id,
    'SN_MTTOCORRECTIVO'                 AS origen,
    m.sys_created_on                    AS fecha,
    c.latitud                           AS latitud,
    c.longitud                          AS longitud,
    ST_SetSRID(
        ST_MakePoint(c.longitud, c.latitud),
        4326
    )                                   AS ageom,
    muni.nam                            AS partido,            -- campo nombre del municipio
    %s                                  AS upload_date
FROM sn.metric m
JOIN sn.instance i
    ON i.number = m.instance
JOIN sn.task t
    ON t.number = i.task_id
LEFT JOIN cm.ci_sfat_mfat_bfat c
    ON c.nombre_cliente = t.cmdb_ci
LEFT JOIN cm.municipio muni
    ON ST_Contains(
        muni.geom,
        ST_SetSRID(ST_MakePoint(c.longitud, c.latitud), 4326)
    )
WHERE m.metric ILIKE '%%CTO DE TERCEROS EN CUADRA%%'
  AND m.metric_definition = 'Si'
  AND c.latitud  IS NOT NULL
  AND c.longitud IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM {SCHEMA}.{TABLE} it
      WHERE it.tarea_id = i.task_id
        AND it.origen   = 'SN_MTTOCORRECTIVO'
  );
"""

# ================= MAIN =================

def log_execution(conn):
    sql = """
        INSERT INTO z_procesos_python.scripts_ejecutados
            (nombre_script, ultima_ejecucion)
        VALUES
            ('load_instalaciones_terceros',
             CURRENT_TIMESTAMP AT TIME ZONE 'America/Argentina/Buenos_Aires');
    """
    with conn.cursor() as cur:
        cur.execute(sql)

def main():
    today = date.today()
    conn = psycopg2.connect(**PG_CONN)
    conn.autocommit = False

    try:
        with conn.cursor() as cur:
            logging.info("Verificando / creando tabla")
            cur.execute(DDL_CREATE_TABLE)
            cur.execute(DDL_GRANTS)
            conn.commit()

            logging.info("Insertando nuevos registros desde Giscloud")
            cur.execute(SQL_INSERT_GISCLOUD, (today,))
            logging.info(f"Giscloud insertados: {cur.rowcount}")
            conn.commit()

            logging.info("Insertando nuevos registros desde ServiceNow")
            cur.execute(SQL_INSERT_SN, (today,))
            logging.info(f"ServiceNow insertados: {cur.rowcount}")
            conn.commit()

        logging.info("Proceso finalizado correctamente")
        logging.info("Registrando ejecución en scripts_ejecutados")
        log_execution(conn)
        conn.commit()

    except Exception as e:
        conn.rollback()
        logging.error(f"Error durante la carga: {e}")
        raise

    finally:
        conn.close()

if __name__ == "__main__":
    main()