-- Contratos aprobados pero sin firmar
SELECT 
    c.numero_contrato,
    cl.nombre AS cliente,
    cl.email,
    cl.telefono,
    c.fecha_inicio,
    c.monto_total,
    c.moneda,
    CURRENT_DATE - c.fecha_inicio AS dias_vencidos,
    ec.fecha_cambio AS fecha_aprobacion,
    COALESCE(pa.responsable, 'Sin asignar') AS responsable_seguimiento
FROM core.contrato c
JOIN core.cliente cl ON c.id_cliente = cl.id_cliente
JOIN core.estado_contrato ec ON c.id_contrato = ec.id_contrato
JOIN catalog.estado e ON ec.id_estado = e.id_estado
LEFT JOIN (
    SELECT 
        pa.id_contrato,
        p.nombre || ' ' || p.apellido_paterno AS responsable
    FROM core.personal_asignado pa
    JOIN core.personal p ON pa.id_personal = p.id_personal
    WHERE pa.es_responsable_principal = TRUE
) pa ON c.id_contrato = pa.id_contrato
WHERE e.nombre_estado = 'Aprobado'
  AND c.firmado = FALSE
  AND ec.es_estado_actual = TRUE
  AND c.activo = TRUE
ORDER BY dias_vencidos DESC;

-- Contratos próximos a vencer
SELECT 
    c.numero_contrato,
    cl.nombre AS cliente,
    cl.tipo_cliente,
    cl.contacto_principal,
    cl.email,
    cl.telefono,
    c.fecha_inicio,
    c.fecha_fin,
    c.fecha_fin - CURRENT_DATE AS dias_restantes,
    c.monto_total,
    e.nombre_estado AS estado_actual,
    -- Calcular renovación sugerida (10% incremento)
    ROUND(c.monto_total * 1.10, 2) AS monto_renovacion_sugerido,
    -- Información del responsable
    pa.responsable_principal,
    pa.email_responsable
FROM core.contrato c
JOIN core.cliente cl ON c.id_cliente = cl.id_cliente
JOIN core.estado_contrato ec ON c.id_contrato = ec.id_contrato
JOIN catalog.estado e ON ec.id_estado = e.id_estado
LEFT JOIN (
    SELECT 
        pa.id_contrato,
        p.nombre_completo AS responsable_principal,
        p.email AS email_responsable
    FROM core.personal_asignado pa
    JOIN core.personal p ON pa.id_personal = p.id_personal
    WHERE pa.es_responsable_principal = TRUE
      AND (pa.fecha_fin IS NULL OR pa.fecha_fin >= CURRENT_DATE)
) pa ON c.id_contrato = pa.id_contrato
WHERE c.fecha_fin BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
  AND e.nombre_estado IN ('Firmado', 'En Ejecución')
  AND ec.es_estado_actual = TRUE
  AND c.activo = TRUE
ORDER BY c.fecha_fin ASC;

-- Estado de equipos en tiempo real
SELECT 
    e.id_equipo,
    e.nombre AS equipo,
    e.marca,
    e.modelo,
    e.numero_serie,
    e.categoria,
    e.ubicacion,
    e.estado_equipo,
    -- Si está en uso, mostrar en qué contrato
    CASE 
        WHEN e.estado_equipo = 'En Uso' THEN c.numero_contrato
        ELSE NULL
    END AS contrato_asignado,
    CASE 
        WHEN e.estado_equipo = 'En Uso' THEN cl.nombre
        ELSE NULL
    END AS cliente_actual,
    CASE 
        WHEN e.estado_equipo = 'En Uso' THEN ea.fecha_devolucion_estimada
        ELSE NULL
    END AS disponible_desde,
    -- Calibración
    CASE 
        WHEN e.requiere_calibracion THEN
            CASE 
                WHEN e.fecha_proxima_calibracion < CURRENT_DATE 
                THEN ' VENCIDA'
                WHEN e.fecha_proxima_calibracion < CURRENT_DATE + INTERVAL '15 days'
                THEN ' PRÓXIMA'
                ELSE ' VIGENTE'
            END
        ELSE 'N/A'
    END AS estado_calibracion,
    e.fecha_proxima_calibracion
FROM core.equipo e
LEFT JOIN core.equipo_asignado ea ON e.id_equipo = ea.id_equipo 
    AND ea.estado_asignacion = 'Activo'
LEFT JOIN core.contrato c ON ea.id_contrato = c.id_contrato
LEFT JOIN core.cliente cl ON c.id_cliente = cl.id_cliente
WHERE e.activo = TRUE
ORDER BY 
    CASE e.estado_equipo
        WHEN 'Disponible' THEN 1
        WHEN 'En Uso' THEN 2
        WHEN 'Mantenimiento' THEN 3
        WHEN 'Baja' THEN 4
    END,
    e.categoria,
    e.nombre;

-- Carga laboral actual por empleado
WITH carga_actual AS (
    SELECT 
        p.id_personal,
        p.numero_empleado,
        p.nombre_completo,
        p.puesto,
        p.departamento,
        COUNT(DISTINCT pa.id_contrato) AS cantidad_proyectos,
        SUM(pa.porcentaje_dedicacion) AS porcentaje_total,
        STRING_AGG(
            c.numero_contrato || ' (' || pa.rol_proyecto || ')', 
            ', ' 
            ORDER BY pa.fecha_inicio DESC
        ) AS proyectos_activos,
        MAX(pa.fecha_inicio) AS ultimo_proyecto_asignado
    FROM core.personal p
    LEFT JOIN core.personal_asignado pa ON p.id_personal = pa.id_personal
    LEFT JOIN core.contrato c ON pa.id_contrato = c.id_contrato
    WHERE p.activo = TRUE
      AND (pa.fecha_fin IS NULL OR pa.fecha_fin >= CURRENT_DATE)
      AND (c.activo = TRUE OR c.id_contrato IS NULL)
    GROUP BY p.id_personal, p.numero_empleado, p.nombre_completo, p.puesto, p.departamento
)
SELECT 
    numero_empleado,
    nombre_completo,
    puesto,
    departamento,
    cantidad_proyectos,
    porcentaje_total AS capacidad_utilizada,
    100 - porcentaje_total AS capacidad_disponible,
    -- Clasificación de carga
    CASE 
        WHEN porcentaje_total = 0 THEN 'DISPONIBLE'
        WHEN porcentaje_total <= 50 THEN ' BAJA'
        WHEN porcentaje_total <= 80 THEN ' MEDIA'
        WHEN porcentaje_total <= 100 THEN ' ALTA'
        ELSE ' SOBRECARGADO'
    END AS nivel_carga,
    proyectos_activos,
    ultimo_proyecto_asignado
FROM carga_actual
ORDER BY porcentaje_total DESC, departamento, nombre_completo;

-- Cola de trabajo del laboratorio
SELECT 
    m.codigo_muestra,
    m.prioridad,
    c.numero_contrato,
    cl.nombre AS cliente,
    tm.nombre AS tipo_muestra,
    m.fecha_entrada,
    CURRENT_TIMESTAMP - m.fecha_entrada AS tiempo_espera,
    tm.tiempo_procesamiento_dias AS sla_dias,
    -- Calcular fecha límite SLA
    DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias AS fecha_limite_sla,
    -- Días hasta vencimiento SLA
    (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) - CURRENT_DATE AS dias_hasta_vencimiento,
    -- Estado de cumplimiento
    CASE 
        WHEN (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) < CURRENT_DATE 
        THEN ' VENCIDO'
        WHEN (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) <= CURRENT_DATE + 1
        THEN ' URGENTE'
        WHEN (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) <= CURRENT_DATE + 2
        THEN ' PRÓXIMO'
        ELSE ' OK'
    END AS estado_sla,
    m.ubicacion_almacen,
    m.cantidad,
    m.unidad_medida,
    CASE WHEN tm.requiere_refrigeracion THEN ' Refrigerado' ELSE 'Ambiente' END AS almacenamiento
FROM core.muestra m
JOIN core.contrato c ON m.id_contrato = c.id_contrato
JOIN core.cliente cl ON c.id_cliente = cl.id_cliente
JOIN catalog.tipo_muestra tm ON m.id_tipo_muestra = tm.id_tipo_muestra
WHERE m.estado_muestra = 'Recibida'
ORDER BY 
    -- Priorizar por: vencidos > urgentes > prioridad > fecha entrada
    CASE 
        WHEN (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) < CURRENT_DATE THEN 1
        WHEN (DATE(m.fecha_entrada) + tm.tiempo_procesamiento_dias) <= CURRENT_DATE + 1 THEN 2
        ELSE 3
    END,
    CASE m.prioridad
        WHEN 'Urgente' THEN 1
        WHEN 'Alta' THEN 2
        WHEN 'Normal' THEN 3
        WHEN 'Baja' THEN 4
    END,
    m.fecha_entrada ASC;

-- Reporte de no conformidades
SELECT 
    a.id_analisis,
    m.codigo_muestra,
    c.numero_contrato,
    cl.nombre AS cliente,
    tm.nombre AS tipo_muestra,
    a.tipo_analisis,
    a.metodo_utilizado,
    a.norma_referencia,
    a.fecha_analisis,
    a.valor_numerico,
    a.valor_esperado_min,
    a.valor_esperado_max,
    a.unidad_resultado,
    -- Calcular desviación
    CASE 
        WHEN a.valor_numerico < a.valor_esperado_min 
        THEN ROUND(((a.valor_esperado_min - a.valor_numerico) / a.valor_esperado_min * 100)::NUMERIC, 2)
        WHEN a.valor_numerico > a.valor_esperado_max
        THEN ROUND(((a.valor_numerico - a.valor_esperado_max) / a.valor_esperado_max * 100)::NUMERIC, 2)
        ELSE 0
    END AS desviacion_porcentual,
    -- Tipo de no conformidad
    CASE 
        WHEN a.valor_numerico < a.valor_esperado_min THEN 'Por debajo del límite'
        WHEN a.valor_numerico > a.valor_esperado_max THEN 'Por encima del límite'
    END AS tipo_no_conformidad,
    p.nombre_completo AS analista,
    e.nombre AS equipo_utilizado,
    a.observaciones
FROM core.analisis a
JOIN core.muestra m ON a.id_muestra = m.id_muestra
JOIN core.contrato c ON m.id_contrato = c.id_contrato
JOIN core.cliente cl ON c.id_cliente = cl.id_cliente
JOIN catalog.tipo_muestra tm ON m.id_tipo_muestra = tm.id_tipo_muestra
LEFT JOIN core.personal p ON a.id_personal_analista = p.id_personal
LEFT JOIN core.equipo e ON a.equipo_utilizado = e.id_equipo
WHERE a.cumple_especificacion = FALSE
  AND a.fecha_analisis >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY a.fecha_analisis DESC;

-- Timeline completo de un contrato (ejemplo: CTR-2024-001)
WITH timeline AS (
    -- Creación del contrato
    SELECT 
        'Creación' AS evento,
        c.fecha_creacion AS fecha_evento,
        'Contrato creado' AS descripcion,
        c.creado_por AS usuario,
        1 AS orden
    FROM core.contrato c
    WHERE c.numero_contrato = 'CTR-2024-001'
    
    UNION ALL
    
    -- Cambios de estado
    SELECT 
        'Cambio Estado' AS evento,
        ec.fecha_cambio AS fecha_evento,
        'Estado: ' || e.nombre_estado AS descripcion,
        ec.usuario_responsable AS usuario,
        2 AS orden
    FROM core.estado_contrato ec
    JOIN catalog.estado e ON ec.id_estado = e.id_estado
    JOIN core.contrato c ON ec.id_contrato = c.id_contrato
    WHERE c.numero_contrato = 'CTR-2024-001'
    
    UNION ALL
    
    -- Asignación de personal
    SELECT 
        'Personal Asignado' AS evento,
        pa.fecha_inicio AS fecha_evento,
        'Asignado: ' || p.nombre_completo || ' (' || pa.rol_proyecto || ')' AS descripcion,
        'Sistema' AS usuario,
        3 AS orden
    FROM core.personal_asignado pa
    JOIN core.personal p ON pa.id_personal = p.id_personal
    JOIN core.contrato c ON pa.id_contrato = c.id_contrato
    WHERE c.numero_contrato = 'CTR-2024-001'
    
    UNION ALL
    
    -- Asignación de equipos
    SELECT 
        'Equipo Asignado' AS evento,
        ea.fecha_asignacion AS fecha_evento,
        'Equipo: ' || e.nombre || ' (' || e.numero_serie || ')' AS descripcion,
        'Sistema' AS usuario,
        4 AS orden
    FROM core.equipo_asignado ea
    JOIN core.equipo e ON ea.id_equipo = e.id_equipo
    JOIN core.contrato c ON ea.id_contrato = c.id_contrato
    WHERE c.numero_contrato = 'CTR-2024-001'
    
    UNION ALL
    
    -- Entrada de muestras
    SELECT 
        'Muestra Recibida' AS evento,
        m.fecha_entrada AS fecha_evento,
        'Muestra: ' || m.codigo_muestra || ' - ' || tm.nombre AS descripcion,
        'Sistema' AS usuario,
        5 AS orden
    FROM core.muestra m
    JOIN catalog.tipo_muestra tm ON m.id_tipo_muestra = tm.id_tipo_muestra
    JOIN core.contrato c ON m.id_contrato = c.id_contrato
    WHERE c.numero_contrato = 'CTR-2024-001'
    
    UNION ALL
    
    -- Análisis completados
    SELECT 
        'Análisis Completado' AS evento,
        a.fecha_resultados AS fecha_evento,
        'Análisis: ' || a.tipo_analisis || ' - ' || 
        CASE WHEN a.cumple_especificacion THEN ' Conforme' ELSE ' No conforme' END AS descripcion,
        p.nombre_completo AS usuario,
        6 AS orden
    FROM core.analisis a
    JOIN core.muestra m ON a.id_muestra = m.id_muestra
    JOIN core.contrato c ON m.id_contrato = c.id_contrato
    LEFT JOIN core.personal p ON a.id_personal_analista = p.id_personal
    WHERE c.numero_contrato = 'CTR-2024-001'
      AND a.fecha_resultados IS NOT NULL
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY fecha_evento) AS num,
    evento,
    fecha_evento,
    TO_CHAR(fecha_evento, 'DD/MM/YYYY HH24:MI') AS fecha_formateada,
    descripcion,
    usuario
FROM timeline
ORDER BY fecha_evento ASC;

-- Log de actividad de las últimas 24 horas
SELECT 
    cl.tabla,
    cl.operacion,
    cl.id_registro,
    cl.usuario,
    cl.fecha_cambio,
    cl.ip_origen,
    -- Mostrar cambios específicos en campos importantes
    CASE 
        WHEN cl.tabla = 'contrato' THEN
            jsonb_pretty(
                jsonb_build_object(
                    'monto_anterior', cl.datos_anteriores->'monto_total',
                    'monto_nuevo', cl.datos_nuevos->'monto_total',
                    'estado_anterior', cl.datos_anteriores->'firmado',
                    'estado_nuevo', cl.datos_nuevos->'firmado'
                )
            )
        ELSE NULL
    END AS cambios_criticos
FROM audit.cambios_log cl
WHERE cl.fecha_cambio >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
ORDER BY cl.fecha_cambio DESC
LIMIT 100;

-- Dashboard ejecutivo con comparativa MoM
WITH mes_actual AS (
    SELECT 
        COUNT(DISTINCT fc.id_contrato) AS contratos,
        SUM(fc.monto_usd) AS ingresos,
        AVG(fc.monto_usd) AS ticket_promedio,
        COUNT(DISTINCT fc.sk_cliente) AS clientes,
        SUM(fc.cantidad_muestras) AS muestras,
        SUM(CASE WHEN fc.completado_a_tiempo THEN 1 ELSE 0 END)::FLOAT / 
            NULLIF(COUNT(*), 0) * 100 AS tasa_cumplimiento
    FROM dwh.fact_contrato fc
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    WHERE dt.anio = EXTRACT(YEAR FROM CURRENT_DATE)
      AND dt.mes = EXTRACT(MONTH FROM CURRENT_DATE)
),
mes_anterior AS (
    SELECT 
        COUNT(DISTINCT fc.id_contrato) AS contratos,
        SUM(fc.monto_usd) AS ingresos,
        AVG(fc.monto_usd) AS ticket_promedio,
        COUNT(DISTINCT fc.sk_cliente) AS clientes,
        SUM(fc.cantidad_muestras) AS muestras,
        SUM(CASE WHEN fc.completado_a_tiempo THEN 1 ELSE 0 END)::FLOAT / 
            NULLIF(COUNT(*), 0) * 100 AS tasa_cumplimiento
    FROM dwh.fact_contrato fc
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    WHERE dt.anio = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 month')
      AND dt.mes = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 month')
)
SELECT 
    'Contratos' AS metrica,
    ma.contratos AS mes_actual,
    man.contratos AS mes_anterior,
    ROUND(((ma.contratos - man.contratos)::NUMERIC / NULLIF(man.contratos, 0) * 100), 1) AS cambio_porcentual,
    CASE 
        WHEN ma.contratos > man.contratos THEN ' Crecimiento'
        WHEN ma.contratos < man.contratos THEN ' Decrecimiento'
        ELSE ' Sin cambio'
    END AS tendencia
FROM mes_actual ma, mes_anterior man

UNION ALL

SELECT 
    'Ingresos USD',
    ma.ingresos,
    man.ingresos,
    ROUND(((ma.ingresos - man.ingresos) / NULLIF(man.ingresos, 0) * 100), 1),
    CASE 
        WHEN ma.ingresos > man.ingresos THEN ' Crecimiento'
        WHEN ma.ingresos < man.ingresos THEN ' Decrecimiento'
        ELSE ' Sin cambio'
    END
FROM mes_actual ma, mes_anterior man

UNION ALL

SELECT 
    'Ticket Promedio',
    ma.ticket_promedio,
    man.ticket_promedio,
    ROUND(((ma.ticket_promedio - man.ticket_promedio) / NULLIF(man.ticket_promedio, 0) * 100), 1),
    CASE 
        WHEN ma.ticket_promedio > man.ticket_promedio THEN ' Crecimiento'
        WHEN ma.ticket_promedio < man.ticket_promedio THEN ' Decrecimiento'
        ELSE ' Sin cambio'
    END
FROM mes_actual ma, mes_anterior man

UNION ALL

SELECT 
    'Clientes Activos',
    ma.clientes,
    man.clientes,
    ROUND(((ma.clientes - man.clientes)::NUMERIC / NULLIF(man.clientes, 0) * 100), 1),
    CASE 
        WHEN ma.clientes > man.clientes THEN ' Crecimiento'
        WHEN ma.clientes < man.clientes THEN ' Decrecimiento'
        ELSE 'Sin cambio'
    END
FROM mes_actual ma, mes_anterior man

UNION ALL

SELECT 
    'Muestras Procesadas',
    ma.muestras,
    man.muestras,
    ROUND(((ma.muestras - man.muestras)::NUMERIC / NULLIF(man.muestras, 0) * 100), 1),
    CASE 
        WHEN ma.muestras > man.muestras THEN ' Crecimiento'
        WHEN ma.muestras < man.muestras THEN ' Decrecimiento'
        ELSE ' Sin cambio'
    END
FROM mes_actual ma, mes_anterior man

UNION ALL

SELECT 
    'Cumplimiento SLA %',
    ma.tasa_cumplimiento,
    man.tasa_cumplimiento,
    ROUND((ma.tasa_cumplimiento - man.tasa_cumplimiento), 1),
    CASE 
        WHEN ma.tasa_cumplimiento > man.tasa_cumplimiento THEN ' Mejora'
        WHEN ma.tasa_cumplimiento < man.tasa_cumplimiento THEN ' Deterioro'
        ELSE ' Sin cambio'
    END
FROM mes_actual ma, mes_anterior man;

-- Análisis de ingresos por trimestre
SELECT 
    dt.anio,
    dt.trimestre,
    dt.anio_trimestre,
    dc.tipo_cliente,
    COUNT(DISTINCT fc.id_contrato) AS cantidad_contratos,
    SUM(fc.monto_usd) AS ingresos_totales,
    ROUND(AVG(fc.monto_usd), 2) AS ticket_promedio,
    ROUND(SUM(fc.monto_usd) / SUM(SUM(fc.monto_usd)) OVER (PARTITION BY dt.anio, dt.trimestre) * 100, 1) AS porcentaje_mix,
    -- Métricas operacionales
    SUM(fc.cantidad_muestras) AS total_muestras,
    ROUND(AVG(fc.duracion_dias), 1) AS duracion_promedio_dias,
    -- Cumplimiento
    ROUND(
        SUM(CASE WHEN fc.completado_a_tiempo THEN 1 ELSE 0 END)::NUMERIC / 
        NULLIF(COUNT(*), 0) * 100, 
        1
    ) AS porcentaje_on_time
FROM dwh.fact_contrato fc
JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
JOIN dwh.dim_cliente dc ON fc.sk_cliente = dc.sk_cliente AND dc.es_actual
WHERE dt.anio >= EXTRACT(YEAR FROM CURRENT_DATE) - 2  -- Últimos 2 años
GROUP BY dt.anio, dt.trimestre, dt.anio_trimestre, dc.tipo_cliente
ORDER BY dt.anio DESC, dt.trimestre DESC, ingresos_totales DESC;

-- Análisis de clientes más valiosos
WITH cliente_metricas AS (
    SELECT 
        dc.id_cliente,
        dc.nombre,
        dc.tipo_cliente,
        dc.ciudad,
        dc.estado,
        COUNT(DISTINCT fc.id_contrato) AS total_contratos,
        SUM(fc.monto_usd) AS valor_total_lifetime,
        AVG(fc.monto_usd) AS ticket_promedio,
        MAX(dt.fecha) AS ultimo_contrato,
        MIN(dt.fecha) AS primer_contrato,
        -- Calcular antigüedad del cliente
        EXTRACT(YEAR FROM AGE(MAX(dt.fecha), MIN(dt.fecha))) AS antiguedad_anios,
        -- Frecuencia de contratación (contratos por año)
        ROUND(
            COUNT(DISTINCT fc.id_contrato)::NUMERIC / 
            NULLIF(EXTRACT(YEAR FROM AGE(MAX(dt.fecha), MIN(dt.fecha))), 0),
            2
        ) AS contratos_por_anio,
        -- Cumplimiento
        ROUND(
            SUM(CASE WHEN fc.completado_a_tiempo THEN 1 ELSE 0 END)::NUMERIC / 
            NULLIF(COUNT(*), 0) * 100,
            1
        ) AS tasa_cumplimiento
    FROM dwh.fact_contrato fc
    JOIN dwh.dim_cliente dc ON fc.sk_cliente = dc.sk_cliente AND dc.es_actual
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    GROUP BY dc.id_cliente, dc.nombre, dc.tipo_cliente, dc.ciudad, dc.estado
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY valor_total_lifetime DESC) AS ranking,
    nombre AS cliente,
    tipo_cliente,
    ciudad,
    TO_CHAR(valor_total_lifetime, 'L999,999,999') AS valor_lifetime,
    total_contratos,
    TO_CHAR(ticket_promedio, 'L999,999') AS ticket_promedio,
    antiguedad_anios || ' años' AS cliente_desde,
    contratos_por_anio AS frecuencia,
    tasa_cumplimiento || '%' AS cumplimiento,
    -- Clasificación
    CASE 
        WHEN valor_total_lifetime >= 1000000 THEN 'Diamante'
        WHEN valor_total_lifetime >= 500000 THEN ' Platino'
        WHEN valor_total_lifetime >= 200000 THEN ' Oro'
        WHEN valor_total_lifetime >= 50000 THEN ' Plata'
        ELSE 'Bronce'
    END AS categoria,
    ultimo_contrato
FROM cliente_metricas
ORDER BY valor_total_lifetime DESC
LIMIT 20;

-- Análisis de tiempo en cada estado
WITH transiciones AS (
    SELECT 
        fc.id_contrato,
        fec.sk_estado_nuevo,
        fec.sk_estado_anterior,
        de_nuevo.nombre_estado AS estado,
        de_anterior.nombre_estado AS estado_anterior,
        AVG(fec.dias_en_estado_anterior) AS dias_promedio
    FROM dwh.fact_estado_contrato fec
    JOIN dwh.fact_contrato fc ON fec.sk_contrato = fc.sk_contrato
    JOIN dwh.dim_estado de_nuevo ON fec.sk_estado_nuevo = de_nuevo.sk_estado
    LEFT JOIN dwh.dim_estado de_anterior ON fec.sk_estado_anterior = de_anterior.sk_estado
    GROUP BY fc.id_contrato, fec.sk_estado_nuevo, fec.sk_estado_anterior, 
             de_nuevo.nombre_estado, de_anterior.nombre_estado
)
SELECT 
    estado_anterior || ' → ' || estado AS transicion,
    COUNT(*) AS cantidad_contratos,
    ROUND(AVG(dias_promedio), 1) AS dias_promedio,
    ROUND(MIN(dias_promedio), 1) AS dias_minimo,
    ROUND(MAX(dias_promedio), 1) AS dias_maximo,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY dias_promedio), 1) AS mediana,
    ROUND(STDDEV(dias_promedio), 1) AS desviacion_estandar,
    -- Identificar outliers (más de 2 desviaciones estándar)
    COUNT(CASE 
        WHEN dias_promedio > (AVG(dias_promedio) + 2 * STDDEV(dias_promedio)) 
        THEN 1 
    END) AS casos_anormales
FROM transiciones
WHERE estado_anterior IS NOT NULL
GROUP BY estado_anterior, estado
HAVING COUNT(*) >= 5  -- Solo transiciones con suficiente data
ORDER BY dias_promedio DESC;

-- Análisis de eficiencia por tipo de muestra
SELECT 
    dtm.categoria,
    dtm.nombre AS tipo_muestra,
    COUNT(DISTINCT fm.id_muestra) AS total_muestras,
    -- Tiempos de procesamiento
    ROUND(AVG(fm.tiempo_entrada_proceso_dias), 1) AS dias_promedio_inicio,
    ROUND(AVG(fm.tiempo_proceso_finalizacion_dias), 1) AS dias_promedio_analisis,
    ROUND(AVG(fm.tiempo_total_dias), 1) AS dias_promedio_total,
    -- Comparar con SLA
    ROUND(AVG(fm.tiempo_total_dias - dtm.tiempo_promedio_dias), 1) AS desviacion_vs_sla,
    -- Cumplimiento
    ROUND(
        SUM(CASE WHEN fm.finalizado_a_tiempo THEN 1 ELSE 0 END)::NUMERIC / 
        NULLIF(COUNT(*), 0) * 100,
        1
    ) AS porcentaje_on_time,
    -- Calidad
    ROUND(AVG(fm.cantidad_analisis), 1) AS analisis_promedio_por_muestra,
    ROUND(
        SUM(fm.analisis_conformes)::NUMERIC / 
        NULLIF(SUM(fm.cantidad_analisis), 0) * 100,
        1
    ) AS porcentaje_conformidad,
    -- Tendencia últimos 3 meses
    ROUND(
        (SELECT AVG(tiempo_total_dias) 
         FROM dwh.fact_muestra fm2
         JOIN dwh.dim_tiempo dt ON fm2.sk_fecha_entrada = dt.sk_tiempo
         WHERE fm2.sk_tipo_muestra = fm.sk_tipo_muestra
           AND dt.fecha >= CURRENT_DATE - INTERVAL '3 months'
        ) - AVG(fm.tiempo_total_dias),
        1
    ) AS cambio_ultimos_3m
FROM dwh.fact_muestra fm
JOIN dwh.dim_tipo_muestra dtm ON fm.sk_tipo_muestra = dtm.sk_tipo_muestra
GROUP BY dtm.categoria, dtm.nombre, fm.sk_tipo_muestra, dtm.tiempo_promedio_dias
HAVING COUNT(*) >= 10
ORDER BY total_muestras DESC;

-- Análisis geográfico de negocio
SELECT 
    du.estado,
    du.region,
    COUNT(DISTINCT fc.sk_cliente) AS clientes_unicos,
    COUNT(DISTINCT fc.id_contrato) AS total_contratos,
    SUM(fc.monto_usd) AS ingresos_totales,
    ROUND(AVG(fc.monto_usd), 2) AS ticket_promedio,
    -- Participación
    ROUND(
        SUM(fc.monto_usd) / SUM(SUM(fc.monto_usd)) OVER () * 100,
        1
    ) AS porcentaje_ingresos_nacional,
    -- Crecimiento YoY
    SUM(CASE WHEN dt.anio = EXTRACT(YEAR FROM CURRENT_DATE) THEN fc.monto_usd ELSE 0 END) AS ingresos_2024,
    SUM(CASE WHEN dt.anio = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN fc.monto_usd ELSE 0 END) AS ingresos_2023,
    ROUND(
        (SUM(CASE WHEN dt.anio = EXTRACT(YEAR FROM CURRENT_DATE) THEN fc.monto_usd ELSE 0 END) - 
         SUM(CASE WHEN dt.anio = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN fc.monto_usd ELSE 0 END)) / 
        NULLIF(SUM(CASE WHEN dt.anio = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN fc.monto_usd ELSE 0 END), 0) * 100,
        1
    ) AS crecimiento_yoy,
    -- Potencial (ingresos / clientes)
    ROUND(SUM(fc.monto_usd) / NULLIF(COUNT(DISTINCT fc.sk_cliente), 0), 2) AS valor_por_cliente
FROM dwh.fact_contrato fc
JOIN dwh.dim_cliente dc ON fc.sk_cliente = dc.sk_cliente AND dc.es_actual
JOIN dwh.dim_ubicacion du ON fc.sk_ubicacion = du.sk_ubicacion
JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
WHERE dt.anio >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY du.estado, du.region
ORDER BY ingresos_totales DESC;

-- Proyección basada en tendencias históricas
WITH historico_trimestral AS (
    SELECT 
        dt.anio,
        dt.trimestre,
        dt.anio_trimestre,
        SUM(fc.monto_usd) AS ingresos,
        COUNT(DISTINCT fc.id_contrato) AS contratos,
        ROUND(AVG(fc.monto_usd), 2) AS ticket_promedio
    FROM dwh.fact_contrato fc
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    WHERE dt.anio >= EXTRACT(YEAR FROM CURRENT_DATE) - 2
    GROUP BY dt.anio, dt.trimestre, dt.anio_trimestre
),
crecimiento AS (
    SELECT 
        AVG(
            (ingresos - LAG(ingresos) OVER (ORDER BY anio, trimestre)) / 
            NULLIF(LAG(ingresos) OVER (ORDER BY anio, trimestre), 0) * 100
        ) AS tasa_crecimiento_promedio
    FROM historico_trimestral
)
SELECT 
    'Histórico' AS tipo,
    anio_trimestre,
    TO_CHAR(ingresos, 'L999,999,999') AS ingresos,
    contratos,
    ticket_promedio
FROM historico_trimestral
ORDER BY anio DESC, trimestre DESC
LIMIT 8

UNION ALL

SELECT 
    'Proyección' AS tipo,
    TO_CHAR(CURRENT_DATE + INTERVAL '3 months', 'YYYY-Q') || 
        EXTRACT(QUARTER FROM CURRENT_DATE + INTERVAL '3 months')::TEXT AS anio_trimestre,
    TO_CHAR(
        (SELECT ingresos FROM historico_trimestral ORDER BY anio DESC, trimestre DESC LIMIT 1) * 
        (1 + (SELECT tasa_crecimiento_promedio FROM crecimiento) / 100),
        'L999,999,999'
    ) AS ingresos_proyectados,
    (SELECT contratos FROM historico_trimestral ORDER BY anio DESC, trimestre DESC LIMIT 1) + 
        ROUND((SELECT contratos FROM historico_trimestral ORDER BY anio DESC, trimestre DESC LIMIT 1) * 
              (SELECT tasa_crecimiento_promedio FROM crecimiento) / 100) AS contratos_estimados,
    (SELECT ticket_promedio FROM historico_trimestral ORDER BY anio DESC, trimestre DESC LIMIT 1) AS ticket_estimado;

-- Análisis de retención por cohorte
WITH primer_contrato AS (
    SELECT 
        fc.sk_cliente,
        MIN(dt.fecha) AS fecha_primer_contrato,
        TO_CHAR(MIN(dt.fecha), 'YYYY-MM') AS cohorte
    FROM dwh.fact_contrato fc
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    GROUP BY fc.sk_cliente
),
actividad_mensual AS (
    SELECT 
        pc.cohorte,
        TO_CHAR(dt.fecha, 'YYYY-MM') AS mes_actividad,
        COUNT(DISTINCT fc.sk_cliente) AS clientes_activos,
        EXTRACT(MONTH FROM AGE(dt.fecha, pc.fecha_primer_contrato)) AS meses_desde_inicio
    FROM primer_contrato pc
    JOIN dwh.fact_contrato fc ON pc.sk_cliente = fc.sk_cliente
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    GROUP BY pc.cohorte, TO_CHAR(dt.fecha, 'YYYY-MM'), 
             EXTRACT(MONTH FROM AGE(dt.fecha, pc.fecha_primer_contrato))
)
SELECT 
    cohorte,
    (SELECT COUNT(DISTINCT sk_cliente) FROM primer_contrato WHERE cohorte = am.cohorte) AS clientes_nuevos,
    meses_desde_inicio || ' meses' AS periodo,
    clientes_activos,
    ROUND(
        clientes_activos::NUMERIC / 
        (SELECT COUNT(DISTINCT sk_cliente) FROM primer_contrato WHERE cohorte = am.cohorte) * 100,
        1
    ) AS tasa_retencion
FROM actividad_mensual am
WHERE meses_desde_inicio IN (0, 3, 6, 12, 24)  -- Hitos importantes
ORDER BY cohorte DESC, meses_desde_inicio;

-- Identificar clientes inactivos
WITH ultimo_contrato AS (
    SELECT 
        dc.id_cliente,
        dc.nombre,
        dc.tipo_cliente,
        dc.email,
        MAX(dt.fecha) AS fecha_ultimo_contrato,
        CURRENT_DATE - MAX(dt.fecha) AS dias_inactivo,
        COUNT(DISTINCT fc.id_contrato) AS contratos_historicos,
        SUM(fc.monto_usd) AS valor_lifetime,
        AVG(fc.monto_usd) AS ticket_promedio_historico
    FROM dwh.dim_cliente dc
    JOIN dwh.fact_contrato fc ON dc.sk_cliente = fc.sk_cliente
    JOIN dwh.dim_tiempo dt ON fc.sk_fecha_inicio = dt.sk_tiempo
    WHERE dc.es_actual = TRUE
    GROUP BY dc.id_cliente, dc.nombre, dc.tipo_cliente, dc.email
)
SELECT 
    nombre AS cliente,
    tipo_cliente,
    email,
    fecha_ultimo_contrato,
    dias_inactivo,
    CASE 
        WHEN dias_inactivo > 365 THEN ' CRÍTICO'
        WHEN dias_inactivo > 180 THEN ' ALTO'
        WHEN dias_inactivo > 90 THEN ' MEDIO'
        ELSE ' BAJO'
    END AS riesgo_churn,
    contratos_historicos,
    TO_CHAR(valor_lifetime, 'L999,999,999') AS valor_perdido_potencial,
    TO_CHAR(ticket_promedio_historico, 'L999,999') AS ticket_historico,
    -- Acción sugerida
    CASE 
        WHEN dias_inactivo > 365 THEN 'Llamada ejecutiva inmediata'
        WHEN dias_inactivo > 180 THEN 'Campaña de reactivación + descuento'
        WHEN dias_inactivo > 90 THEN 'Email de seguimiento'
        ELSE 'Monitorear'
    END AS accion_recomendada
FROM ultimo_contrato
WHERE dias_inactivo > 90  -- Más de 3 meses sin actividad
  AND valor_lifetime > 50000  -- Solo clientes valiosos
ORDER BY valor_lifetime DESC, dias_inactivo DESC
LIMIT 50;

-- Análisis de calidad por cliente
SELECT 
    dc.nombre AS cliente,
    dc.tipo_cliente,
    COUNT(DISTINCT fm.id_muestra) AS total_muestras,
    SUM(fm.cantidad_analisis) AS total_analisis,
    SUM(fm.analisis_conformes) AS analisis_ok,
    SUM(fm.analisis_no_conformes) AS analisis_fallidos,
    ROUND(
        SUM(fm.analisis_no_conformes)::NUMERIC / 
        NULLIF(SUM(fm.cantidad_analisis), 0) * 100,
        1
    ) AS tasa_no_conformidad,
    -- Comparar con promedio general
    ROUND(
        (SUM(fm.analisis_no_conformes)::NUMERIC / NULLIF(SUM(fm.cantidad_analisis), 0) * 100) -
        (SELECT SUM(analisis_no_conformes)::NUMERIC / NULLIF(SUM(cantidad_analisis), 0) * 100 
         FROM dwh.fact_muestra),
        1
    ) AS desviacion_vs_promedio,
    -- Tipos de análisis con más problemas
    (SELECT STRING_AGG(DISTINCT tipo_analisis, ', ')
     FROM dwh.fact_analisis fa
     JOIN dwh.fact_muestra fm2 ON fa.sk_muestra = fm2.sk_muestra
     WHERE fm2.sk_contrato IN (
         SELECT DISTINCT fc.sk_contrato 
         FROM dwh.fact_contrato fc 
         WHERE fc.sk_cliente = dc.sk_cliente
     )
     AND fa.cumple_especificacion = FALSE
    ) AS analisis_problematicos
FROM dwh.dim_cliente dc
JOIN dwh.fact_contrato fc ON dc.sk_cliente = fc.sk_cliente
JOIN dwh.fact_muestra fm ON fc.sk_contrato = fm.sk_contrato
WHERE dc.es_actual = TRUE
GROUP BY dc.sk_cliente, dc.nombre, dc.tipo_cliente
HAVING SUM(fm.cantidad_analisis) >= 20  -- Mínimo 20 análisis
ORDER BY tasa_no_conformidad DESC
LIMIT 30;

-- Dashboard híbrido operacional + analítico
WITH operacional_hoy AS (
    -- Datos OLTP en tiempo real
    SELECT 
        COUNT(DISTINCT CASE WHEN m.fecha_entrada::date = CURRENT_DATE THEN m.id_muestra END) AS muestras_hoy,
        COUNT(DISTINCT CASE WHEN m.estado_muestra = 'Recibida' THEN m.id_muestra END) AS cola_procesamiento,
        COUNT(DISTINCT CASE WHEN m.estado_muestra = 'En Proceso' THEN m.id_muestra END) AS en_proceso,
        COUNT(DISTINCT CASE WHEN a.fecha_analisis::date = CURRENT_DATE THEN a.id_analisis END) AS analisis_hoy,
        COUNT(DISTINCT CASE WHEN c.fecha_creacion::date = CURRENT_DATE THEN c.id_contrato END) AS contratos_nuevos_hoy
    FROM core.muestra m
    LEFT JOIN core.analisis a ON m.id_muestra = a.id_muestra
    LEFT JOIN core.contrato c ON m.id_contrato = c.id_contrato
),
tendencias_mes AS (
    -- Datos OLAP históricos
    SELECT 
        AVG(CASE WHEN dt.dia_mes = EXTRACT(DAY FROM CURRENT_DATE) THEN muestras_dia ELSE NULL END) AS promedio_muestras_mismo_dia,
        AVG(muestras_dia) AS promedio_muestras_mes,
        MAX(muestras_dia) AS pico_muestras_mes
    FROM (
        SELECT 
            dt.dia_mes,
            COUNT(*) AS muestras_dia
        FROM dwh.fact_muestra fm
        JOIN dwh.dim_tiempo dt ON fm.sk_fecha_entrada = dt.sk_tiempo
        WHERE dt.anio = EXTRACT(YEAR FROM CURRENT_DATE)
          AND dt.mes = EXTRACT(MONTH FROM CURRENT_DATE)
        GROUP BY dt.dia_mes
    ) sub
)
SELECT 
    'Muestras ingresadas hoy' AS metrica,
    oh.muestras_hoy AS valor_actual,
    tm.promedio_muestras_mismo_dia AS promedio_historico,
    CASE 
        WHEN oh.muestras_hoy > tm.promedio_muestras_mismo_dia * 1.2 THEN ' Por encima'
        WHEN oh.muestras_hoy < tm.promedio_muestras_mismo_dia * 0.8 THEN ' Por debajo'
        ELSE ' Normal'
    END AS comparacion
FROM operacional_hoy oh, tendencias_mes tm

UNION ALL

SELECT 
    'Muestras en cola',
    oh.cola_procesamiento,
    NULL,
    CASE 
        WHEN oh.cola_procesamiento > 50 THEN ' Alta'
        WHEN oh.cola_procesamiento > 20 THEN ' Media'
        ELSE ' Baja'
    END
FROM operacional_hoy oh

UNION ALL

SELECT 
    'Análisis completados hoy',
    oh.analisis_hoy,
    tm.promedio_muestras_mes,
    CASE 
        WHEN oh.analisis_hoy >= tm.promedio_muestras_mes THEN ' On track'
        ELSE ' Retrasado'
    END
FROM operacional_hoy oh, tendencias_mes tm;

-- Crear función que genera reporte
CREATE OR REPLACE FUNCTION generar_reporte_diario()
RETURNS void AS $$
BEGIN
    -- Tu query aquí
    COPY (
        SELECT * FROM vista_dashboard_ejecutivo
    ) TO '/reportes/dashboard_' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') || '.csv'
    WITH CSV HEADER;
END;
$$ LANGUAGE plpgsql;

-- Programar con pg_cron (requiere extensión)
SELECT cron.schedule(
    'reporte-diario',
    '0 8 * * *',  -- Todos los días a las 8 AM
    'SELECT generar_reporte_diario()'
);

import pandas as pd
import psycopg2
from datetime import datetime

# Conectar a PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="seguimiento_contratos",
    user="analytics_user",
    password="password"
)

# Ejecutar reporte
query = """
    SELECT * FROM dwh.v_dashboard_ejecutivo
    WHERE anio = EXTRACT(YEAR FROM CURRENT_DATE)
"""

df = pd.read_sql(query, conn)

# Exportar a Excel con formato
filename = f"reporte_ejecutivo_{datetime.now().strftime('%Y%m%d')}.xlsx"
with pd.ExcelWriter(filename, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Dashboard', index=False)
    
# Enviar por email
from email.mime.application import MIMEApplication
# ... código de email