-- =============================================================================
-- TV WALL — Vendor Performance Query — Sede Central (Profit Legacy 2k8)
-- =============================================================================
-- Descripción: Consulta unificada que produce todas las métricas de rendimiento
--              por vendedor para el mes en curso desde la sede Central.
--              Incluye: ventas, devoluciones, peso neto, clientes efectivos,
--              clientes nuevos y reactivados, cotizaciones.
-- Tablas:       factura, reng_fac, dev_cli, reng_dvc, cotiz_c, vendedor, art
-- Conexión:     sqlsrv_central (CEN_TAMC)
-- 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.fact_num) AS cant_facturas,
        COALESCE(SUM(fv.tot_neto / NULLIF(fv.tasa, 0)), 0) AS ventas_usd,
        COALESCE(SUM(pesos.peso_doc), 0) AS peso_facturas
    FROM factura fv
    LEFT JOIN (
        SELECT fvr.fact_num, SUM(fvr.total_art * a.peso) AS peso_doc
        FROM reng_fac fvr
        INNER JOIN art a ON fvr.co_art = a.co_art
        GROUP BY fvr.fact_num
    ) pesos ON fv.fact_num = pesos.fact_num
    WHERE fv.anulada = 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.fact_num) AS cant_devoluciones,
        COALESCE(SUM(dc.tot_neto / NULLIF(dc.tasa, 0)), 0) AS devoluciones_usd,
        COALESCE(SUM(pesos.peso_doc), 0) AS peso_devoluciones
    FROM dev_cli dc
    LEFT JOIN (
        SELECT dcr.fact_num, SUM(dcr.total_art * a.peso) AS peso_doc
        FROM reng_dvc dcr
        INNER JOIN art a ON dcr.co_art = a.co_art
        GROUP BY dcr.fact_num
    ) pesos ON dc.fact_num = pesos.fact_num
    WHERE dc.anulada = 0 AND dc.fec_emis >= @fecha_ini AND dc.fec_emis < @fecha_fin
    GROUP BY dc.co_ven
),
-- 3. Clientes Efectivos
Clientes_Efectivos AS (
    SELECT 
        fv.co_ven,
        COUNT(DISTINCT TRIM(fv.co_cli)) AS clientes_venta_efectiva
    FROM factura fv
    WHERE fv.anulada = 0 
      AND fv.fec_emis >= @fecha_ini AND fv.fec_emis < @fecha_fin
      AND TRIM(fv.co_cli) <> ''
      AND NOT EXISTS (
          SELECT 1
          FROM dev_cli dc
          WHERE dc.anulada = 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 factura
    WHERE anulada = 0
    GROUP BY co_cli
),
VentasPeriodo AS (
    SELECT DISTINCT fv.co_cli, fv.co_ven
    FROM factura fv
    WHERE fv.anulada = 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.anulada = 0 OR c.anulada IS NULL) THEN 1 ELSE 0 END) AS cant_cotizaciones_mes,
        COALESCE(SUM(CASE WHEN (c.anulada = 0 OR c.anulada IS NULL) THEN c.tot_neto / NULLIF(c.tasa, 0) ELSE 0 END), 0) AS monto_cotizaciones_usd
    FROM cotiz_c 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 vendedor 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.fun_ven = 1 
  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;