/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBPEDIDO_CUPOM_ITEM;

CREATE TABLE TBPEDIDO_CUPOM_ITEM (
    ID                   INTEGER NOT NULL,
    PEDIDO_CUPOM_ID      INTEGER,
    FAMILIA_ID           INTEGER,
    PERCENTUAL_DESCONTO  NUMERIC(15,4)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPEDIDO_CUPOM_ITEM ADD CONSTRAINT PK_TBPEDIDO_CUPOM_ITEM PRIMARY KEY (ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPEDIDO_CUPOM_ITEM_B01D */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_ITEM_B01D FOR TBPEDIDO_CUPOM_ITEM
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'DESCONTO: '||FN_LPAD(OLD.ID, 4, '0')||' EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', OLD.PEDIDO_CUPOM_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPEDIDO_CUPOM_ITEM_B01I */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_ITEM_B01I FOR TBPEDIDO_CUPOM_ITEM
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPEDIDO_CUPOM_ITEM, 1);
END
^


/* Trigger: TTBPEDIDO_CUPOM_ITEM_B01U */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_ITEM_B01U FOR TBPEDIDO_CUPOM_ITEM
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE OLD_FAMILIA VARCHAR(500);
DECLARE VARIABLE NEW_FAMILIA VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_FAMILIA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBFAMILIA A WHERE A.CODIGO = OLD.FAMILIA_ID), '');
    NEW_FAMILIA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBFAMILIA A WHERE A.CODIGO = NEW.FAMILIA_ID), '');

    IF (OLD.FAMILIA_ID <> NEW.FAMILIA_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' FAMILIA:'||OLD_FAMILIA||'->'||NEW_FAMILIA;
    END

    IF (OLD.PERCENTUAL_DESCONTO <> NEW.PERCENTUAL_DESCONTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' % DESCONT:'||OLD.PERCENTUAL_DESCONTO||'->'||NEW.PERCENTUAL_DESCONTO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'DESCONTO: '||FN_LPAD(OLD.ID, 4, '0')||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', OLD.PEDIDO_CUPOM_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPEDIDO_CUPOM_ITEM_B02I */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_ITEM_B02I FOR TBPEDIDO_CUPOM_ITEM
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'DESCONTO: '||FN_LPAD(NEW.ID, 4, '0')||' INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', NEW.PEDIDO_CUPOM_ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBPEDIDO_CUPOM;

CREATE TABLE TBPEDIDO_CUPOM (
    ID             INTEGER NOT NULL,
    DESCRICAO      VARCHAR(100),
    STATUS         CHAR(1),
    DATA_REGISTRO  DATE DEFAULT 'NOW',
    DATA_VALIDADE  DATE,
    CUPOM_CODIGO   VARCHAR(12)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPEDIDO_CUPOM ADD CONSTRAINT PK_TBPEDIDO_CUPOM PRIMARY KEY (ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPEDIDO_CUPOM_B01D */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_B01D FOR TBPEDIDO_CUPOM
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', OLD.ID, HISTORICO);
    END
END
^


/* Trigger: TTBPEDIDO_CUPOM_B01I */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_B01I FOR TBPEDIDO_CUPOM
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE CUPOM VARCHAR(12);
BEGIN

    CUPOM = COALESCE((SELECT FIRST 1 A.CUPOM_CODIGO FROM TBPEDIDO_CUPOM A WHERE A.CUPOM_CODIGO = NEW.CUPOM_CODIGO), '');

    IF (NEW.CUPOM_CODIGO = CUPOM) THEN
    BEGIN
        EXCEPTION "..." 'CODIGO DO CUPOM: '||NEW.CUPOM_CODIGO||' JA EM USO!';
    END

    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPEDIDO_CUPOM, 1);
END
^


/* Trigger: TTBPEDIDO_CUPOM_B01U */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_B01U FOR TBPEDIDO_CUPOM
ACTIVE BEFORE UPDATE POSITION 0
AS
declare variable HISTORICO varchar(5000);
BEGIN
    
    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END

    IF (OLD.DATA_REGISTRO <> NEW.DATA_REGISTRO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.REG:'||FORMATDATE(OLD.DATA_REGISTRO)||'->'||FORMATDATE(NEW.DATA_REGISTRO);
    END

    IF (OLD.DATA_VALIDADE <> NEW.DATA_VALIDADE) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.VALID:'||FORMATDATE(OLD.DATA_VALIDADE)||'->'||FORMATDATE(NEW.DATA_VALIDADE);
    END

    IF (OLD.CUPOM_CODIGO <> NEW.CUPOM_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' COD.CUPOM:'||FORMATDATE(OLD.CUPOM_CODIGO)||'->'||FORMATDATE(NEW.CUPOM_CODIGO);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPEDIDO_CUPOM_B02D */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_B02D FOR TBPEDIDO_CUPOM
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBPEDIDO_CUPOM_ITEM A WHERE A.PEDIDO_CUPOM_ID = OLD.ID; /* Excluí os filhos do registro principal */
END
^


/* Trigger: TTBPEDIDO_CUPOM_B02I */
CREATE OR ALTER TRIGGER TTBPEDIDO_CUPOM_B02I FOR TBPEDIDO_CUPOM
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPEDIDO_CUPOM', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBPEDIDO_CUPOM.STATUS IS 
'0 - INATIVO;
1 - ATIVO;';

UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO)
    VALUES (12440, 'VEN', 'VEN', '1', '', 'REGISTRO DE CUPONS DE DESCONTOS PARA PEDIDOS')
    MATCHING (CONTROLE);