USE [Sis2000];
GO

IF OBJECT_ID('sp_Tarjetaform', 'P') IS NOT NULL
    DROP PROCEDURE sp_Tarjetaform;
GO

-- =====================================================
-- sp_Tarjetaform
-- Retorna el tipo de formulario y todos los datos del
-- lote/canal necesarios para cargar el componente de
-- activación en el frontend.
-- Usado por: CardModel.spTarjetaForm (CardModel.js)
-- =====================================================
CREATE PROCEDURE sp_Tarjetaform
    @p_xcodigo_unico VARCHAR(8),
    @p_resultado     INT OUTPUT,
    @p_mensaje       VARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @v_ctarjeta  INT;
    DECLARE @v_ctarlot   INT;
    DECLARE @v_iestado   INT;
    DECLARE @v_cplan     VARCHAR(10);
    DECLARE @v_cramos    VARCHAR(6);
    DECLARE @v_ccanalalt INT;
    DECLARE @v_cproducto CHAR(40);
    DECLARE @v_xform     VARCHAR(50);
    DECLARE @v_plan_existe INT = 0;

    -- 1. Buscar tarjeta
    SELECT
        @v_ctarjeta = ctarjeta,
        @v_ctarlot  = ctarlot,
        @v_iestado  = iestado
    FROM tatarjeta
    WHERE xcodigo_unico = @p_xcodigo_unico;

    IF @v_ctarjeta IS NULL
    BEGIN
        SET @p_resultado = 0;
        SET @p_mensaje   = 'Tarjeta no encontrada';
        SELECT
            @p_xcodigo_unico AS xcodigo_unico,
            'persons'        AS tipo_formulario,
            NULL AS cramos, NULL AS cplan, NULL AS ccanalalt,
            NULL AS ctarlot, NULL AS xnombre_lote, NULL AS xcanalalt,
            NULL AS canal_fingreso, NULL AS canal_cusuario, NULL AS canal_ccategoria,
            NULL AS canal_fultmod, NULL AS canal_cusuariomod, NULL AS canal_ccategoriamod,
            NULL AS canal_bactivo, NULL AS canal_bfacturatar,
            NULL AS cproducto, NULL AS nombre_producto, NULL AS estatus;
        RETURN;
    END

    IF @v_iestado = 0
    BEGIN
        SET @p_resultado = 0;
        SET @p_mensaje   = 'Tarjeta anulada o inactiva';
        SELECT
            @p_xcodigo_unico AS xcodigo_unico,
            'persons'        AS tipo_formulario,
            NULL AS cramos, NULL AS cplan, NULL AS ccanalalt,
            NULL AS ctarlot, NULL AS xnombre_lote, NULL AS xcanalalt,
            NULL AS canal_fingreso, NULL AS canal_cusuario, NULL AS canal_ccategoria,
            NULL AS canal_fultmod, NULL AS canal_cusuariomod, NULL AS canal_ccategoriamod,
            NULL AS canal_bactivo, NULL AS canal_bfacturatar,
            NULL AS cproducto, NULL AS nombre_producto, @v_iestado AS estatus;
        RETURN;
    END

    -- 2. Datos del lote
    SELECT
        @v_ccanalalt = L.ccanalalt,
        @v_cplan     = LTRIM(RTRIM(L.cplan)),
        @v_cramos    = LTRIM(RTRIM(L.cramos))
    FROM tatarlot L
    WHERE L.ctarlot = @v_ctarlot;

    -- 3. Buscar plan en maplanes_per primero, luego maplanes
    SELECT TOP 1
        @v_plan_existe = 1,
        @v_cproducto   = cproducto
    FROM maplanes_per
    WHERE LTRIM(RTRIM(cplan)) = LTRIM(RTRIM(@v_cplan))
      AND cramo = CAST(@v_cramos AS SMALLINT)
      AND iestado = 'V';

    IF @v_plan_existe = 0
    BEGIN
        SELECT TOP 1
            @v_plan_existe = 1,
            @v_cproducto   = cproducto
        FROM maplanes
        WHERE LTRIM(RTRIM(cplan)) = LTRIM(RTRIM(@v_cplan))
          AND cramo = CAST(@v_cramos AS SMALLINT)
          AND iestado = 'V';
    END

    -- 4. Obtener xform del producto
    IF @v_cproducto IS NOT NULL
    BEGIN
        SELECT @v_xform = xform
        FROM maproductos
        WHERE cproducto = @v_cproducto;
    END

    -- 5. Fallback por ramo si no hay formulario
    IF @v_xform IS NULL OR LTRIM(RTRIM(@v_xform)) = ''
    BEGIN
        SELECT TOP 1 @v_xform = xform
        FROM maproductos
        WHERE cramo  = CAST(@v_cramos AS SMALLINT)
          AND xform IS NOT NULL
          AND xform  <> ''
        ORDER BY cproducto;
    END

    SET @v_xform    = ISNULL(NULLIF(LTRIM(RTRIM(@v_xform)), ''), 'persons');
    SET @p_resultado = 1;
    SET @p_mensaje   = 'OK';

    -- 6. Retornar todos los datos necesarios para el frontend
    SELECT
        @p_xcodigo_unico AS xcodigo_unico,
        @v_xform         AS tipo_formulario,
        @v_cramos        AS cramos,
        @v_cplan         AS cplan,
        @v_ccanalalt     AS ccanalalt,
        L.ctarlot        AS ctarlot,
        L.xnombre_lote   AS xnombre_lote,
        C.xcanalalt      AS xcanalalt,
        C.fingreso       AS canal_fingreso,
        C.cusuario       AS canal_cusuario,
        C.ccategoria     AS canal_ccategoria,
        C.fultmod        AS canal_fultmod,
        C.cusuariomod    AS canal_cusuariomod,
        C.ccategoriamod  AS canal_ccategoriamod,
        C.bactivo        AS canal_bactivo,
        C.bfacturatar    AS canal_bfacturatar,
        @v_cproducto     AS cproducto,
        P.xdescripcion_l AS nombre_producto,
        @v_iestado       AS estatus
    FROM tatarlot L
    LEFT JOIN macanalalt C  ON C.ccanalalt = L.ccanalalt
    LEFT JOIN maproductos P ON LTRIM(RTRIM(P.cproducto)) = LTRIM(RTRIM(@v_cproducto))
    WHERE L.ctarlot = @v_ctarlot;
END
GO

PRINT 'SP sp_Tarjetaform creado exitosamente'
GO
