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


CREATE GENERATOR GTBCFOP_INVERSO_REGRA;

CREATE TABLE TBCFOP_INVERSO_REGRA (
    ID                   INTEGER NOT NULL,
    CFOP_CODIGO          VARCHAR(5),
    ESTABELECIMENTO_ID   INTEGER,
    CFOP_INVERSO_CODIGO  VARCHAR(5),
    OPERACAO_FISCAL      VARCHAR(3)
);




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

ALTER TABLE TBCFOP_INVERSO_REGRA ADD CONSTRAINT PK_TBCFOP_INVERSO_REGRA PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBCFOP_INVERSO_REGRA_01 ON TBCFOP_INVERSO_REGRA (CFOP_CODIGO);
CREATE INDEX ITBCFOP_INVERSO_REGRA_02 ON TBCFOP_INVERSO_REGRA (ESTABELECIMENTO_ID);
CREATE INDEX ITBCFOP_INVERSO_REGRA_03 ON TBCFOP_INVERSO_REGRA (CFOP_INVERSO_CODIGO);
CREATE INDEX ITBCFOP_INVERSO_REGRA_04 ON TBCFOP_INVERSO_REGRA (OPERACAO_FISCAL);


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



SET TERM ^ ;



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



/* Trigger: TTBCFOP_INVERSO_REGRA_B01D */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01D FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = OLD.CFOP_CODIGO), '');
    OLD_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = OLD.CFOP_INVERSO_CODIGO), '');
    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    OLD_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = OLD.OPERACAO_FISCAL), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO: '||OLD_CFOP_INVERSO||' EXCLUIDO';
    END

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


/* Trigger: TTBCFOP_INVERSO_REGRA_B01I */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01I FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE INSERT POSITION 0
AS     
DECLARE VARIABLE CFOP_INVERSO_CODIGO VARCHAR(10);
DECLARE VARIABLE CFOP_INVERSO_ESTAB VARCHAR(500);
BEGIN

    :CFOP_INVERSO_CODIGO = COALESCE((SELECT FIRST 1 A.CFOP_INVERSO_CODIGO FROM TBCFOP_INVERSO_REGRA A WHERE A.CFOP_CODIGO = NEW.CFOP_CODIGO AND COALESCE(A.ESTABELECIMENTO_ID, 0) = NEW.ESTABELECIMENTO_ID), '');
    :CFOP_INVERSO_ESTAB = COALESCE((SELECT FIRST 1 B.RAZAOSOCIAL FROM TBCFOP_INVERSO_REGRA A, TBESTABELECIMENTO B WHERE A.ESTABELECIMENTO_ID = B.CODIGO AND A.CFOP_CODIGO = NEW.CFOP_CODIGO AND COALESCE(A.ESTABELECIMENTO_ID, 0) = NEW.ESTABELECIMENTO_ID), 'TODOS OS ESTABELECIMENTOS');

    IF (COALESCE(:CFOP_INVERSO_CODIGO, '') <> '') THEN
    BEGIN
        EXCEPTION "..." 'O CFOP '||:CFOP_INVERSO_CODIGO||' JA ESTA CONFIGURADO COMO CFOP INVERSO PARA '||:CFOP_INVERSO_ESTAB||'. OPERACAO CANCELADA!';
    END

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


/* Trigger: TTBCFOP_INVERSO_REGRA_B01U */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01U FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_OPERACAO_FISCAL VARCHAR(500);
DECLARE VARIABLE NEW_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = OLD.CFOP_CODIGO), '');
    NEW_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = NEW.CFOP_CODIGO), '');
    OLD_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = OLD.CFOP_INVERSO_CODIGO), '');
    NEW_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = NEW.CFOP_INVERSO_CODIGO), '');
    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    OLD_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = OLD.OPERACAO_FISCAL), '');
    NEW_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = NEW.OPERACAO_FISCAL), '');

    IF (OLD.CFOP_CODIGO IS DISTINCT FROM NEW.CFOP_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP:'||OLD_CFOP_CODIGO||'->'||NEW_CFOP_CODIGO;
    END

    IF (OLD.ESTABELECIMENTO_ID IS DISTINCT FROM NEW.ESTABELECIMENTO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' ESTABELECIMENTO:'||OLD_ESTABELECIMENTO||'->'||NEW_ESTABELECIMENTO;
    END

    IF (OLD.CFOP_INVERSO_CODIGO IS DISTINCT FROM NEW.CFOP_INVERSO_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO:'||OLD_CFOP_INVERSO||'->'||NEW_CFOP_INVERSO;
    END

    IF (OLD.OPERACAO_FISCAL IS DISTINCT FROM NEW.OPERACAO_FISCAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' OP.FISCAL:'||OLD_OPERACAO_FISCAL||'->'||NEW_OPERACAO_FISCAL;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'CFOP INVERSO: '||NEW_CFOP_INVERSO||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', OLD.CFOP_CODIGO, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBCFOP_INVERSO_REGRA_B02I */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B02I FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    NEW_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = NEW.CFOP_CODIGO), '');
    NEW_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = NEW.CFOP_INVERSO_CODIGO), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    NEW_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = NEW.OPERACAO_FISCAL), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO: '||NEW_CFOP_INVERSO||' INCLUIDO';
    END

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

SET TERM ; ^

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

SET TERM ^ ;

CREATE OR ALTER procedure SPI_CFOP_INVERSO_REGRA
as
declare variable ICFOP_CODIGO varchar(10);
declare variable ICFOP_INVERSO_CODIGO varchar(10);
declare variable IOPERACAO_FISCAL varchar(10);
declare variable DADOS blob sub_type 1 segment size 80;
BEGIN

    FOR
        SELECT
            T.CODIGO,
            T.CFOP_INVERSO,
            T.OPERACAO_FISCAL
        
        FROM TBCFOP T
        WHERE COALESCE(T.CODIGO, '') <> ''
        AND COALESCE(T.CFOP_INVERSO, '') <> ''

    INTO
        :ICFOP_CODIGO,
        :ICFOP_INVERSO_CODIGO,
        :IOPERACAO_FISCAL

    DO
    BEGIN

        UPDATE OR INSERT INTO TBCFOP_INVERSO_REGRA (CFOP_CODIGO, ESTABELECIMENTO_ID, CFOP_INVERSO_CODIGO, OPERACAO_FISCAL)
            VALUES (:ICFOP_CODIGO, 0, :ICFOP_INVERSO_CODIGO, :IOPERACAO_FISCAL)
            MATCHING (CFOP_CODIGO, ESTABELECIMENTO_ID, CFOP_INVERSO_CODIGO);

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

        :DADOS = COALESCE((
            FN_TO_JSON_V2(
                FN_TO_PAR_JSON_V2('CODIGO', :ICFOP_CODIGO),
                FN_TO_PAR_JSON_V2('CFOP_INVERSO', :ICFOP_INVERSO_CODIGO),
                FN_TO_PAR_JSON_V2('OPERACAO_FISCAL', :IOPERACAO_FISCAL)
            )), '');

        EXECUTE PROCEDURE SPI_BACKUP('TBCFOP', :ICFOP_CODIGO, :DADOS);


        UPDATE TBCFOP
            SET CFOP_INVERSO = '',
                OPERACAO_FISCAL = ''
            WHERE CODIGO = :ICFOP_CODIGO;

    END
END
^

SET TERM ; ^

