-- =============================================================================
-- TV WALL — Vendor Performance Query — Sede Caldera (Profit Plus 2k12)
-- =============================================================================
-- Descripción: Consulta unificada que produce todas las métricas de rendimiento
--              por vendedor para el mes en curso desde la sede Caldera.
--              Incluye: ventas, devoluciones, peso neto, clientes efectivos,
--              clientes nuevos y reactivados, cotizaciones.
-- Tablas:       saFacturaVenta, saFacturaVentaReng, saDevolucionCliente,
--               saDevolucionClienteReng, saCotizacionCliente, saVendedor, saArticulo
-- Conexión:     sqlsrv_caldera (adm_tam)
-- Fecha:        2026-06-23
-- =============================================================================

DECLARE @fecha_ini SMALLDATETIME = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
DECLARE @fecha_fin SMALLDATETIME = DATEADD(MONTH, 1, @fecha_ini);
DECLARE @hoy DATE = CAST(GETDATE() AS DATE);

WITH 
-- 1. Ventas
Ventas AS (
    SELECT 
        fv.co_ven,
        COUNT(fv.doc_num) AS cant_facturas,
        COALESCE(SUM(fv.total_neto / NULLIF(fv.tasa, 0)), 0) AS ventas_usd,
        COALESCE(SUM(pesos.peso_doc), 0) AS peso_facturas
    FROM saFacturaVenta fv
    LEFT JOIN (
        SELECT fvr.doc_num, SUM(fvr.total_art * a.peso) AS peso_doc
        FROM saFacturaVentaReng fvr
        INNER JOIN saArticulo a ON fvr.co_art = a.co_art
        GROUP BY fvr.doc_num
    ) pesos ON fv.doc_num = pesos.doc_num
    WHERE fv.anulado = 0 AND fv.fec_emis >= @fecha_ini AND fv.fec_emis < @fecha_fin
    GROUP BY fv.co_ven
),
-- 2. Devoluciones
Devoluciones AS (
    SELECT 
        dc.co_ven,
        COUNT(dc.doc_num) AS cant_devoluciones,
        COALESCE(SUM(dc.total_neto / NULLIF(dc.tasa, 0)), 0) AS devoluciones_usd,
        COALESCE(SUM(pesos.peso_doc), 0) AS peso_devoluciones
    FROM saDevolucionCliente dc
    LEFT JOIN (
        SELECT dcr.doc_num, SUM(dcr.total_art * a.peso) AS peso_doc
        FROM saDevolucionClienteReng dcr
        INNER JOIN saArticulo a ON dcr.co_art = a.co_art
        GROUP BY dcr.doc_num
    ) pesos ON dc.doc_num = pesos.doc_num
    WHERE dc.anulado = 0 AND dc.fec_emis >= @fecha_ini AND dc.fec_emis < @fecha_fin
    GROUP BY dc.co_ven
),
-- 3. Clientes Efectivos (Con Facturas, sin Devoluciones en el mes)
Clientes_Efectivos AS (
    SELECT 
        fv.co_ven,
        COUNT(DISTINCT TRIM(fv.co_cli)) AS clientes_venta_efectiva
    FROM saFacturaVenta fv
    WHERE fv.anulado = 0 
      AND fv.fec_emis >= @fecha_ini AND fv.fec_emis < @fecha_fin
      AND TRIM(fv.co_cli) <> ''
      AND NOT EXISTS (
          SELECT 1
          FROM saDevolucionCliente dc
          WHERE dc.anulado = 0
            AND dc.co_ven = fv.co_ven
            AND dc.co_cli = fv.co_cli
            AND dc.fec_emis >= @fecha_ini AND dc.fec_emis < @fecha_fin
      )
    GROUP BY fv.co_ven
),
-- 4. Clientes Nuevos y Reactivados
HistoricoClientes AS (
    SELECT 
        co_cli,
        MIN(fec_emis) AS primera_compra,
        MAX(CASE WHEN fec_emis < @fecha_ini THEN fec_emis ELSE NULL END) AS ultima_compra_previa
    FROM saFacturaVenta
    WHERE anulado = 0
    GROUP BY co_cli
),
VentasPeriodo AS (
    SELECT DISTINCT fv.co_cli, fv.co_ven
    FROM saFacturaVenta fv
    WHERE fv.anulado = 0 AND fv.fec_emis >= @fecha_ini AND fv.fec_emis < @fecha_fin
      AND TRIM(fv.co_cli) <> ''
),
NuevosReactivados AS (
    SELECT 
        v.co_ven,
        SUM(CASE WHEN hc.primera_compra >= @fecha_ini THEN 1 ELSE 0 END) AS clientes_nuevos,
        SUM(CASE WHEN hc.primera_compra < @fecha_ini AND hc.ultima_compra_previa >= DATEADD(MONTH, -6, @fecha_fin) THEN 1 ELSE 0 END) AS clientes_reactivados
    FROM VentasPeriodo v
    INNER JOIN HistoricoClientes hc ON v.co_cli = hc.co_cli
    GROUP BY v.co_ven
),
-- 5. Cotizaciones
Cotizaciones AS (
    SELECT 
        c.co_ven,
        SUM(CASE WHEN c.anulado = 0 THEN 1 ELSE 0 END) AS cant_cotizaciones_mes,
        COALESCE(SUM(CASE WHEN c.anulado = 0 THEN c.total_neto / NULLIF(c.tasa, 0) ELSE 0 END), 0) AS monto_cotizaciones_usd
    FROM saCotizacionCliente c
    WHERE c.fec_emis >= @fecha_ini AND c.fec_emis < @fecha_fin
    GROUP BY c.co_ven
)
-- QUERY PRINCIPAL
SELECT 
    COALESCE(TRIM(ven.co_ven), 'TOTAL') AS co_ven,
    COALESCE(TRIM(ven.ven_des), '') AS ven_des,
    
    -- Rentabilidad y Operación
    SUM(COALESCE(v.cant_facturas, 0)) AS cant_facturas,
    SUM(COALESCE(d.cant_devoluciones, 0)) AS cant_devoluciones,
    CAST(SUM(COALESCE(v.ventas_usd, 0)) AS DECIMAL(18,2)) AS ventas_usd,
    CAST(SUM(COALESCE(d.devoluciones_usd, 0)) AS DECIMAL(18,2)) AS devoluciones_usd,
    CAST(SUM(COALESCE(v.ventas_usd, 0) - COALESCE(d.devoluciones_usd, 0)) AS DECIMAL(18,2)) AS ventas_netas_usd,
    SUM(COALESCE(v.peso_facturas, 0) - COALESCE(d.peso_devoluciones, 0)) AS peso_neto,
    
    -- Cartera de Clientes
    SUM(COALESCE(ce.clientes_venta_efectiva, 0)) AS clientes_efectivos,
    SUM(COALESCE(nr.clientes_nuevos, 0)) AS clientes_nuevos,
    SUM(COALESCE(nr.clientes_reactivados, 0)) AS clientes_reactivados,
    
    -- Proyección (Cotizaciones)
    SUM(COALESCE(c.cant_cotizaciones_mes, 0)) AS cant_cotizaciones,
    CAST(SUM(COALESCE(c.monto_cotizaciones_usd, 0)) AS DECIMAL(18,2)) AS monto_cotizaciones_usd
    
FROM saVendedor ven
LEFT JOIN Ventas v ON ven.co_ven = v.co_ven
LEFT JOIN Devoluciones d ON ven.co_ven = d.co_ven
LEFT JOIN Clientes_Efectivos ce ON ven.co_ven = ce.co_ven
LEFT JOIN NuevosReactivados nr ON ven.co_ven = nr.co_ven
LEFT JOIN Cotizaciones c ON ven.co_ven = c.co_ven
WHERE ven.inactivo = 0 
  AND (
      COALESCE(v.cant_facturas, 0) > 0 OR 
      COALESCE(d.cant_devoluciones, 0) > 0 OR 
      COALESCE(c.cant_cotizaciones_mes, 0) > 0
  )
GROUP BY GROUPING SETS (
    (ven.co_ven, ven.ven_des),
    ()
)
ORDER BY 
    GROUPING(ven.co_ven),
    ventas_netas_usd DESC;