"""
Acceso de solo lectura a la base de datos del sistema propio de Track Mar
("Utopia", MySQL). Consultas escritas contra el esquema real, relevado y
validado con Osvaldo el 2026-09-07 (ver config.py, sección 2, para el
detalle de cada decisión de mapeo).
"""
from sqlalchemy import create_engine, text
import config


def get_engine():
    """Crea la conexión de solo lectura. Requiere que la base sea alcanzable
    por red desde donde corra este script (ver sección 9 del documento .docx)."""
    return create_engine(config.get_connection_string(), pool_pre_ping=True)


def obtener_cotizaciones_sin_seguimiento(engine):
    """Eje 1: cotizaciones sin seguimiento, con esquema de 3 niveles definido
    junto con Osvaldo el 2026-09-07:

    - AGENDA_COTIZACIONES tiene UNIQUE KEY en COTIZACION (una fila por
      cotización, comment de la tabla: "SEGUIMIENTO DE COTIZACIONES
      REPUESTOS"). COMENTARIO es un historial concatenado (cada seguimiento
      nuevo se agrega al texto existente) y FECHA_REGISTRO se actualiza en
      cada agregado — así que FECHA_REGISTRO ya es, de por sí, la fecha del
      último toque, sin importar cuántas veces se tocó. COMENTARIO2,
      COMENTARIO3 y RESPUESTA son campos legados sin uso actual (confirmado:
      <0.2% de las filas, y las pocas cargadas son de 2014-2015) — no se
      usan acá.
    - VTA_CALZADA es el estado real de la cotización ('SEGUIMIENTO',
      'EVALUATIVA', 'PENDIENTE', 'PERDIDA...', 'POSTERGO INVERSION', etc.),
      no COTIZACIONES_EMITIDAS.ESTADO (que solo indica autorización
      interna). "Ya no se sigue más" = VTA_CALZADA empieza con 'PERDIDA' o
      es 'POSTERGO INVERSION'.
    - "Ya vendida" = tiene fila en VENTAS con ese COTIZACION.
    - Ventana de 5 semanas: solo cotizaciones creadas en los últimos 35 días
      (sin esto, el histórico completo desde 2002 hace la alerta
      inutilizable).
    - Excluye clientes con ACTIVIDAD=100 ("Orden de Taller") y el cliente
      256 (TRACK-MAR S.A.C.I., la propia empresa como cliente interno).
    - El vendedor/responsable es CLIENTES.VENDEDOR, no
      COTIZACIONES_EMITIDAS.EMPLEADO.

    Niveles de alerta (mutuamente excluyentes, por cotización):
    - 'nunca_contactada': sin ningún comentario en AGENDA_COTIZACIONES y
      pasaron más de UMBRAL_HORAS_COTIZACION_SIN_PRIMER_CONTACTO horas
      desde que se creó la cotización.
    - 'escalamiento': tiene comentario, pero pasaron más de
      UMBRAL_DIAS_COTIZACION_ESCALAMIENTO días desde el último
      FECHA_REGISTRO (gana por sobre 'sin_novedad' si se cumplen las dos).
    - 'sin_novedad': tiene comentario, pasaron más de
      UMBRAL_HORAS_COTIZACION_SIN_NOVEDAD horas (pero no llegó al umbral de
      escalamiento) desde el último FECHA_REGISTRO.
    """
    sql = text("""
        SELECT * FROM (
            SELECT c.NUMERO AS cotizacion_id, c.SUCURSAL AS sucursal_id,
                   c.CLIENTE AS cliente_id, cli.RAZON_SOCIAL AS cliente_nombre,
                   cli.WHATSAPP AS cliente_whatsapp, cli.VENDEDOR AS vendedor_id,
                   ag.VTA_CALZADA AS estado_seguimiento,
                   COALESCE(NULLIF(ag.FECHA_REGISTRO, '0000-00-00'), c.FECHA) AS fecha_ultima_actividad,
                   CASE
                       WHEN (ag.COMENTARIO IS NULL OR ag.COMENTARIO = '')
                            AND c.FECHA < NOW() - INTERVAL :horas_primer_contacto HOUR
                           THEN 'nunca_contactada'
                       WHEN (ag.COMENTARIO IS NOT NULL AND ag.COMENTARIO <> '')
                            AND COALESCE(NULLIF(ag.FECHA_REGISTRO, '0000-00-00'), c.FECHA) < NOW() - INTERVAL :dias_escalamiento DAY
                           THEN 'escalamiento'
                       WHEN (ag.COMENTARIO IS NOT NULL AND ag.COMENTARIO <> '')
                            AND COALESCE(NULLIF(ag.FECHA_REGISTRO, '0000-00-00'), c.FECHA) < NOW() - INTERVAL :horas_sin_novedad HOUR
                           THEN 'sin_novedad'
                       ELSE NULL
                   END AS nivel_alerta
            FROM COTIZACIONES_EMITIDAS c
            JOIN CLIENTES cli ON cli.CODIGO = c.CLIENTE
            LEFT JOIN VENTAS v ON v.COTIZACION = c.NUMERO
            LEFT JOIN AGENDA_COTIZACIONES ag ON ag.COTIZACION = c.NUMERO
            WHERE c.ESTADO = 'AUTORIZADA'
              AND v.NUMERO IS NULL
              AND cli.ACTIVIDAD <> 100
              AND cli.CODIGO <> 256
              AND c.FECHA > CURDATE() - INTERVAL 35 DAY
              AND (ag.VTA_CALZADA IS NULL OR ag.VTA_CALZADA = ''
                   OR (ag.VTA_CALZADA NOT LIKE 'PERDIDA%' AND ag.VTA_CALZADA <> 'POSTERGO INVERSION'))
        ) x
        WHERE x.nivel_alerta IS NOT NULL
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql, {
            "horas_primer_contacto": config.UMBRAL_HORAS_COTIZACION_SIN_PRIMER_CONTACTO,
            "horas_sin_novedad": config.UMBRAL_HORAS_COTIZACION_SIN_NOVEDAD,
            "dias_escalamiento": config.UMBRAL_DIAS_COTIZACION_ESCALAMIENTO,
        })]


def obtener_ventas_perdidas_sin_motivo(engine):
    """Eje 1: ítems marcados como venta perdida sin motivo cargado."""
    sql = text("""
        SELECT DISTINCT vp.COTIZACION AS cotizacion_id, c.SUCURSAL AS sucursal_id,
               c.EMPLEADO AS vendedor_id
        FROM VENTAS_PERDIDAS vp
        JOIN COTIZACIONES_EMITIDAS c ON c.NUMERO = vp.COTIZACION
        WHERE vp.MOTIVO IS NULL OR vp.MOTIVO = ''
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql)]


def obtener_visitas_sin_resultado(engine, dias_habiles_umbral=None):
    """Eje 2 (redefinido junto con Osvaldo el 2026-09-07): giras planificadas
    en CLIENTES_GIRA cuya ventana [FECHA_INICIO, FECHA_CIERRE] ya venció, sin
    ningún CLIENTES_VISITAS de tipo visita (TIPO '01'/'04') para ese cliente
    en esa ventana. El umbral se aplica en días de calendario sobre
    FECHA_CIERRE (el cálculo de días hábiles queda para una iteración
    posterior, igual que en la v1 de este archivo)."""
    dias_habiles_umbral = dias_habiles_umbral or config.UMBRAL_DIAS_HABILES_VISITA_SIN_RESULTADO
    sql = text("""
        SELECT g.NRO_GIRA AS visita_id, g.CODIGO_CLIENTE AS cliente_id,
               cli.SUCURSAL AS sucursal_id, g.EMPLEADO AS vendedor_id,
               g.FECHA_CIERRE AS fecha
        FROM CLIENTES_GIRA g
        JOIN CLIENTES cli ON cli.CODIGO = g.CODIGO_CLIENTE
        WHERE g.FECHA_INICIO IS NOT NULL AND g.FECHA_INICIO <> '0000-00-00'
          AND g.FECHA_CIERRE IS NOT NULL AND g.FECHA_CIERRE <> '0000-00-00'
          AND g.FECHA_CIERRE < CURDATE() - INTERVAL :dias DAY
          AND NOT EXISTS (
              SELECT 1 FROM CLIENTES_VISITAS v
              WHERE v.CLIENTE = g.CODIGO_CLIENTE
                AND v.TIPO IN ('01', '04')
                AND v.FECHA_VISITA BETWEEN g.FECHA_INICIO AND g.FECHA_CIERRE
          )
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql, {"dias": dias_habiles_umbral})]


def obtener_contactos_en_frio_sin_cotizacion(engine, horas_umbral=None):
    """Eje 3 (redefinido junto con Osvaldo el 2026-09-07): llamados
    (CLIENTES_VISITAS.TIPO = '02') sin ninguna cotización para ese mismo
    cliente dentro de la ventana de N horas posteriores al llamado. El campo
    CLIENTES_VISITAS.COTIZACION no se usa: en la base real está cargado en
    menos del 1% de los llamados y, cuando lo está, la fecha de la
    cotización vinculada no siempre coincide con la del llamado."""
    horas_umbral = horas_umbral or config.UMBRAL_HORAS_CONTACTO_FRIO_SIN_COTIZACION
    sql = text("""
        SELECT v.ID AS contacto_id, cli.SUCURSAL AS sucursal_id,
               v.CLIENTE AS cliente_id, cli.RAZON_SOCIAL AS cliente_nombre,
               cli.WHATSAPP AS cliente_whatsapp
        FROM CLIENTES_VISITAS v
        JOIN CLIENTES cli ON cli.CODIGO = v.CLIENTE
        WHERE v.TIPO = '02'
          AND v.FECHA_VISITA < NOW() - INTERVAL :horas HOUR
          AND NOT EXISTS (
              SELECT 1 FROM COTIZACIONES_EMITIDAS c
              WHERE c.CLIENTE = v.CLIENTE
                AND c.FECHA BETWEEN v.FECHA_VISITA AND v.FECHA_VISITA + INTERVAL :horas HOUR
          )
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql, {"horas": horas_umbral})]


def obtener_ritmo_contactos_mes(engine):
    """Eje 3: cantidad de llamados (TIPO='02') del mes en curso, por
    sucursal del cliente, para proyectar si se va a llegar al objetivo
    mensual. Excluye sucursales que no son de tipo 'REAL' (ej. el bucket
    interno "Transferencia")."""
    sql = text("""
        SELECT s.CODIGO AS sucursal_id, s.NOMBRE AS sucursal_nombre,
               COUNT(v.ID) AS contactos_mes_actual
        FROM SUCURSALES s
        LEFT JOIN CLIENTES cli ON cli.SUCURSAL = s.CODIGO
        LEFT JOIN CLIENTES_VISITAS v
               ON v.CLIENTE = cli.CODIGO
              AND v.TIPO = '02'
              AND v.FECHA_VISITA >= DATE_FORMAT(NOW(), '%Y-%m-01')
        WHERE s.TIPO = 'REAL'
        GROUP BY s.CODIGO, s.NOMBRE
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql)]


def obtener_sucursales(engine):
    """Sucursales reales (excluye buckets internos tipo 'TRANSITO'), con el
    email de alerta del encargado para rutear la alerta diaria."""
    sql = text("""
        SELECT CODIGO AS sucursal_id, NOMBRE AS sucursal_nombre, EMAIL_ALERTA AS email_alerta
        FROM SUCURSALES
        WHERE TIPO = 'REAL'
    """)
    with engine.connect() as conn:
        return [dict(r._mapping) for r in conn.execute(sql)]