ALTER TABLE TBPDV_CONFERENCIA
ALTER COLUMN OBSERVACAO TYPE VARCHAR(500);

ALTER TABLE TBCATALOGO_PRODUTO
    ADD ALOCAR CHAR(1) CHARACTER SET UTF8 DEFAULT '0' COLLATE UTF8,
    ADD ESTOQUE INTEGER DEFAULT 1,
    ADD CANAL_VENDAS_ID INTEGER;

COMMENT ON COLUMN TBCATALOGO_PRODUTO.ALOCAR IS
'0 - NAO ALOCA AO FINALIZAR;
1 - ALOCA AO FINALIZAR;';

COMMENT ON COLUMN TBCATALOGO_PRODUTO.ESTOQUE IS
'1 - (SALDO FISICO - ALOCADO);
2 - (SALDO FISICO - PEDIDO EM ABERTO);';

/******************************************************************************/
/******************************************************************************/

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


CREATE GENERATOR GTBCANAL_VENDAS;

CREATE TABLE TBCANAL_VENDAS (
    ID         INTEGER NOT NULL,
    DESCRICAO  VARCHAR(100)
);




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

ALTER TABLE TBCANAL_VENDAS ADD CONSTRAINT PK_TBCANAL_VENDAS PRIMARY KEY (ID);


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



SET TERM ^ ;



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



/* Trigger: TTBCANAL_VENDAS_B01D */
CREATE OR ALTER TRIGGER TTBCANAL_VENDAS_B01D FOR TBCANAL_VENDAS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE CATALOGO_PRODUTO VARCHAR(500);
BEGIN
    
    CATALOGO_PRODUTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBCATALOGO_PRODUTO A WHERE A.CANAL_VENDAS_ID = OLD.ID), '');

    IF (CATALOGO_PRODUTO <> '') THEN
    BEGIN
        EXCEPTION "..." 'CANAL DE VENDAS VINCULADO AO CATALOGO DE PRODUTOS '||CATALOGO_PRODUTO||'. OPERACAO NAO AUTORIZADA!';
    END
END
^


/* Trigger: TTBCANAL_VENDAS_B01I */
CREATE OR ALTER TRIGGER TTBCANAL_VENDAS_B01I FOR TBCANAL_VENDAS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (COALESCE(NEW.ID, 0) = 0) THEN
        NEW.ID = GEN_ID(GTBCANAL_VENDAS, 1);
END
^


/* Trigger: TTBCANAL_VENDAS_B01U */
CREATE OR ALTER TRIGGER TTBCANAL_VENDAS_B01U FOR TBCANAL_VENDAS
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 (HISTORICO <> '') THEN
    BEGIN
       HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
       EXECUTE PROCEDURE SPI_HISTORICO('TBCANAL_VENDAS', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBCANAL_VENDAS_B02D */
CREATE OR ALTER TRIGGER TTBCANAL_VENDAS_B02D FOR TBCANAL_VENDAS
ACTIVE BEFORE DELETE POSITION 1
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('TBCANAL_VENDAS', OLD.ID, HISTORICO);
    END
END
^


/* Trigger: TTBCANAL_VENDAS_B02I */
CREATE OR ALTER TRIGGER TTBCANAL_VENDAS_B02I FOR TBCANAL_VENDAS
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('TBCANAL_VENDAS', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^



/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcatalogo_produto_b01u for tbcatalogo_produto
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_TABELA_PRECO VARCHAR(500);
DECLARE VARIABLE NEW_TABELA_PRECO VARCHAR(500);
DECLARE VARIABLE OLD_LOCALIZACAO VARCHAR(500);
DECLARE VARIABLE NEW_LOCALIZACAO VARCHAR(500);
DECLARE VARIABLE OLD_CANAL_VENDAS VARCHAR(500);
DECLARE VARIABLE NEW_CANAL_VENDAS VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.RAZAOSOCIAL  FROM TBESTABELECIMENTO A    WHERE A.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.RAZAOSOCIAL  FROM TBESTABELECIMENTO A    WHERE A.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    OLD_TABELA_PRECO    = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO        FROM TBTABELA_PRECO B       WHERE B.ID = OLD.TABELA_PRECO_ID), '');
    NEW_TABELA_PRECO    = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO        FROM TBTABELA_PRECO B       WHERE B.ID = NEW.TABELA_PRECO_ID), '');
    OLD_LOCALIZACAO     = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.DESCRICAO    FROM TBLOCALIZACAO C        WHERE C.CODIGO = OLD.LOCALIZACAO_ID), '');
    NEW_LOCALIZACAO     = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.DESCRICAO    FROM TBLOCALIZACAO C        WHERE C.CODIGO = NEW.LOCALIZACAO_ID), '');
    OLD_CANAL_VENDAS    = COALESCE((SELECT FIRST 1 FN_LPAD(D.ID, 4, '0')||' - '||D.DESCRICAO        FROM TBCANAL_VENDAS D       WHERE D.ID = OLD.CANAL_VENDAS_ID), '');
    NEW_CANAL_VENDAS    = COALESCE((SELECT FIRST 1 FN_LPAD(D.ID, 4, '0')||' - '||D.DESCRICAO        FROM TBCANAL_VENDAS D       WHERE D.ID = NEW.CANAL_VENDAS_ID), '');

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

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

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

    IF (OLD.ESTABELECIMENTO_ID <> NEW.ESTABELECIMENTO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' ESTAB:'||OLD_ESTABELECIMENTO||'->'||NEW_ESTABELECIMENTO;
    END

    IF (OLD.LOCALIZACAO_ID <> NEW.LOCALIZACAO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' LOCALIZACAO:'||OLD_LOCALIZACAO||'->'||NEW_LOCALIZACAO;
    END

    IF (OLD.TABELA_PRECO_ID <> NEW.TABELA_PRECO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' TAB.PRECO:'||OLD_TABELA_PRECO||'->'||NEW_TABELA_PRECO;
    END

    IF (OLD.CONTATO_VENDEDOR <> NEW.CONTATO_VENDEDOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' CONTATO:'||FN_MASK_V2(OLD.CONTATO_VENDEDOR, 'FONE')||'->'||FN_MASK_V2(NEW.CONTATO_VENDEDOR, 'FONE');
    END

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

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

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

    IF (OLD.CANAL_VENDAS_ID <> NEW.CANAL_VENDAS_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' CANAL VENDAS:'||OLD_CANAL_VENDAS||'->'||NEW_CANAL_VENDAS;
    END

    IF (OLD.LINK <> NEW.LINK) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPO LINK ALTERADO!';
    END

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

SET TERM ; ^

