USE Sis2000;
GO

-- 1. Ventas Generales
IF OBJECT_ID('sp_TarjetaReporteVentasGenerales', 'P') IS NOT NULL
    DROP PROCEDURE sp_TarjetaReporteVentasGenerales;
GO

CREATE PROCEDURE sp_TarjetaReporteVentasGenerales
    @fdesde DATE = NULL,
    @fhasta DATE = NULL
AS
BEGIN
    SELECT 
        T.ctarjeta AS id,
        C.xcanalalt AS name_client,
        P.xplan AS name_insurance,
        T.xfactura AS bill_number, 
        T.factivacion AS bill_date,
        T.xfoto_factura AS file_bill, 
        L.xnombre_lote AS channel_branch, 
        T.cpoliza AS id_buy, 
        T.xcodigo_unico AS code_card, 
        T.cnpoliza AS cnpoliza_sisip,
        T.factivacion as activate_date,
        YEAR(T.factivacion) as fanopol, 
        MONTH(T.factivacion) as fmespol,
        T.iestado AS id_status,
        CASE 
            WHEN T.iestado = 2 THEN 'Activo'
            WHEN T.iestado = 1 THEN 'Inactivo'
            WHEN T.iestado IN (11, 12) THEN 'Cerrado'
            ELSE 'Anulados' 
        END as status
    FROM tatarjeta T
    LEFT JOIN tatarlot L ON L.ctarlot = T.ctarlot
    LEFT JOIN macanalalt C ON C.ccanalalt = L.ccanalalt
    LEFT JOIN maplanes P ON P.cplan = L.cplan AND P.cramo = L.cramos
    WHERE T.iestado IN (2, 11, 12) 
    AND (@fdesde IS NULL OR T.factivacion >= @fdesde)
    AND (@fhasta IS NULL OR T.factivacion <= @fhasta);
END
GO

-- 2. Canales Tarjetas
IF OBJECT_ID('sp_TarjetaReporteCanalesTarjetas', 'P') IS NOT NULL
    DROP PROCEDURE sp_TarjetaReporteCanalesTarjetas;
GO

CREATE PROCEDURE sp_TarjetaReporteCanalesTarjetas
AS
BEGIN
    SELECT 
        C.ccanalalt AS id, 
        C.xcanalalt AS name_client,
        COUNT(DISTINCT L.ctarlot) AS total_lots,
        COUNT(DISTINCT T.ctarjeta) AS total_cards,
        SUM(CASE WHEN T.iestado IN (2, 11, 12) THEN 1 ELSE 0 END) AS total_bills
    FROM macanalalt C
    LEFT JOIN tatarlot L ON L.ccanalalt = C.ccanalalt
    LEFT JOIN tatarjeta T ON T.ctarlot = L.ctarlot
    GROUP BY C.ccanalalt, C.xcanalalt;
END
GO

-- 3. Lotes Tarjetas
IF OBJECT_ID('sp_TarjetaReporteLotesTarjetas', 'P') IS NOT NULL
    DROP PROCEDURE sp_TarjetaReporteLotesTarjetas;
GO

CREATE PROCEDURE sp_TarjetaReporteLotesTarjetas
AS
BEGIN
    SELECT 
        L.ctarlot AS id, 
        L.xnombre_lote AS nombre_lote,
        COUNT(DISTINCT T.ctarjeta) AS total_cards,
        SUM(CASE WHEN T.iestado = 1 THEN 1 ELSE 0 END) AS inactivos,
        SUM(CASE WHEN T.iestado = 2 THEN 1 ELSE 0 END) AS activos,
        SUM(CASE WHEN T.iestado IN (11, 12) THEN 1 ELSE 0 END) AS cerrados,
        SUM(CASE WHEN T.iestado = 0 THEN 1 ELSE 0 END) AS anulados,
        C.xcanalalt AS name_client,
        P.xplan AS producto
    FROM tatarlot L
    LEFT JOIN tatarjeta T ON T.ctarlot = L.ctarlot
    LEFT JOIN macanalalt C ON C.ccanalalt = L.ccanalalt
    LEFT JOIN maplanes P ON P.cplan = L.cplan AND P.cramo = L.cramos
    GROUP BY L.ctarlot, L.xnombre_lote, C.xcanalalt, P.xplan;
END
GO

-- 4. Tarjetas (Inventario)
IF OBJECT_ID('sp_TarjetaReporteInventarioTarjetas', 'P') IS NOT NULL
    DROP PROCEDURE sp_TarjetaReporteInventarioTarjetas;
GO

CREATE PROCEDURE sp_TarjetaReporteInventarioTarjetas
    @ccanalalt INT = NULL,
    @ctarlot INT = NULL,
    @iestado INT = NULL
AS
BEGIN
    SELECT 
        T.ctarjeta AS id, 
        T.xcodigo_unico AS codigo_tarjeta, 
        C.xcanalalt AS canal, 
        L.xnombre_lote AS lote,
        T.cpoliza AS poliza, 
        T.cnpoliza AS poliza_sisip, 
        T.xfactura AS nro_factura, 
        T.factivacion AS fecha_activacion,
        CASE 
            WHEN T.iestado = 2 THEN 'Activo'
            WHEN T.iestado = 1 THEN 'Inactivo'
            WHEN T.iestado IN (11, 12) THEN 'Cerrado'
            ELSE 'Anulados' 
        END as status
    FROM tatarjeta T
    LEFT JOIN tatarlot L ON L.ctarlot = T.ctarlot
    LEFT JOIN macanalalt C ON C.ccanalalt = L.ccanalalt
    LEFT JOIN maplanes P ON P.cplan = L.cplan AND P.cramo = L.cramos
    WHERE (@ccanalalt IS NULL OR C.ccanalalt = @ccanalalt)
    AND (@ctarlot IS NULL OR L.ctarlot = @ctarlot)
    AND (@iestado IS NULL OR T.iestado = @iestado);
END
GO

PRINT 'SPs de Reportes de Tarjetas creados exitosamente.';
GO
