ALTER TABLE TBCASO_REGISTRO
    ADD CASO_ORIGEM_ID INTEGER DEFAULT 0;

COMMENT ON COLUMN TBCASO_REGISTRO.CASO_ORIGEM_ID IS
'ID do Caso de Origem, somente preenchido se caso gerado após disparo da tabela TBCASO_ACAO_ENCERRAMENTO';

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

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b01d for tbcaso_painel
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE CASO_ID INTEGER;
DECLARE VARIABLE VINCULO_ENCERRAMENTO INTEGER;
BEGIN

    HISTORICO = '';


    :CASO_ID = COALESCE((SELECT FIRST 1 A.ID FROM TBCASO_REGISTRO A WHERE A.PAINEL_ID = OLD.ID), 0);
    IF (COALESCE(:CASO_ID, 0) > 0) THEN
    BEGIN
        EXCEPTION "..." 'JA EXISTEM CASOS VINCULADOS A ESTE PAINEL DE CASOS. EXCLUSAO NAO AUTORIZADA!';
    END

    :VINCULO_ENCERRAMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(B.PAINEL_ID, 4, '0')||' - '||C.TITULO FROM TBCASO_ACAO_ENCERRAMENTO B INNER JOIN TBCASO_PAINEL C ON C.ID = B.PAINEL_ID WHERE B.NEW_PAINEL_ID = OLD.ID), 0);
    IF (COALESCE(:VINCULO_ENCERRAMENTO, 0) > 0) THEN
    BEGIN
        EXCEPTION "..." 'PAINEL DE CASO VINCULADO AO ENCERRAMENTO DO PAINEL: '||:VINCULO_ENCERRAMENTO||'. EXCLUSAO NAO AUTORIZADA!';
    END


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

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

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b02d for tbcaso_painel
active before delete position 1
AS
BEGIN
   IF(OLD.ID > 0) THEN
   BEGIN
      DELETE FROM TBCASO_VISUALIZACAO_CONF V WHERE V.PAINEL_ID = OLD.ID;    -- VISUALIZAÇÃO ESTÁ VINCULADA COM PAINEL DURANTE INCLUSÃO.
      DELETE FROM TBCASO_SQL S WHERE S.ID = OLD.SQL_EMAIL_CONTATO;          -- SQL DO CAMPO DOS EMAILS DOS CONATATOS

      DELETE FROM TBCASO_PAINEL_CONF A WHERE A.PAINEL_ID = OLD.ID;          -- CAMPOS (ALT+2)
      DELETE FROM TBCASO_USUARIOS B WHERE B.PAINEL_ID = OLD.ID;             -- USUÁRIOS (ALT+3)
      DELETE FROM TBCASO_PARAMETROS_USUARIO C WHERE C.PAINEL_ID = OLD.ID;   -- PARÂMETROS (ALT+4)
      DELETE FROM TBCASO_STATUS D WHERE D.PAINEL_ID = OLD.ID;               -- STATUS (ALT+5)
      DELETE FROM TBCASO_MOTIVO E WHERE E.PAINEL_ID = OLD.ID;               -- MOTIVO (ALT+6)
      DELETE FROM TBCASO_VISUALIZACAO_CAMPO F WHERE F.PAINEL_ID = OLD.ID;   -- VISUALIZAÇÃO (ALT+8)
      DELETE FROM TBCASO_VALIDACAO G WHERE G.PAINEL_ID = OLD.ID;            -- VALIDAÇÃO (ALT+9)
      DELETE FROM TBCASO_CONTATO_CONF H WHERE H.PAINEL_ID = OLD.ID;         -- CONTATO (ALT+0)
      DELETE FROM TBCASO_CAMPOS_AGRUPAMENTO I WHERE I.PAINEL_ID = OLD.ID;   -- AGRUPAMENTOS (ALT+7)
      DELETE FROM TBCASO_WORKFLOWS J WHERE J.PAINEL_ID = OLD.ID;            -- MODELOS DE WORKFLOW (ALT+R)
      DELETE FROM TBCASO_ACAO_ENCERRAMENTO K WHERE K.PAINEL_ID = OLD.ID;    -- ACOES DE ENCERRAMENTO DOS CASOS (DISPARO DE NOVOS CASOS)
      
      -- DELETE FROM TBMENU M WHERE M.CONTROLE = 99000 + OLD.ID;               -- EXCLUSÃO DO PAINEL DA LISTA DE MENUS
   END
END
^

SET TERM ; ^


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

CREATE GENERATOR GTBCASO_ACAO_ENCERRAMENTO;

CREATE TABLE TBCASO_ACAO_ENCERRAMENTO (
    ID             INTEGER NOT NULL,
    PAINEL_ID      INTEGER,
    NOVO_CASO      INTEGER DEFAULT 0,
    NEW_PAINEL_ID  INTEGER
);




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

ALTER TABLE TBCASO_ACAO_ENCERRAMENTO ADD CONSTRAINT PK_TBCASO_ACAO_ENCERRAMENTO PRIMARY KEY (ID);


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



SET TERM ^ ;



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



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

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

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


/* Trigger: TTBCASO_ACAO_ENCERRAMENTO_B01I */
CREATE OR ALTER TRIGGER TTBCASO_ACAO_ENCERRAMENTO_B01I FOR TBCASO_ACAO_ENCERRAMENTO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN

    IF (COALESCE(NEW.NEW_PAINEL_ID, 0) = 0) THEN
    BEGIN
        NEW.NEW_PAINEL_ID = NEW.PAINEL_ID;
    END

    IF (COALESCE(NEW.ID, 0) = 0) THEN
        NEW.ID = GEN_ID(GTBCASO_ACAO_ENCERRAMENTO, 1);
END
^




/* Trigger: TTBCASO_ACAO_ENCERRAMENTO_B01U */
CREATE OR ALTER TRIGGER TTBCASO_ACAO_ENCERRAMENTO_B01U FOR TBCASO_ACAO_ENCERRAMENTO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_NEW_PAINEL VARCHAR(500);
DECLARE VARIABLE NEW_NEW_PAINEL VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_NEW_PAINEL = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBCASO_PAINEL A WHERE A.ID = OLD.NEW_PAINEL_ID), '');
    NEW_NEW_PAINEL = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBCASO_PAINEL A WHERE A.ID = NEW.NEW_PAINEL_ID), '');

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

    IF (OLD.NEW_PAINEL_ID <> NEW.NEW_PAINEL_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' PAINEL DESTINO:'||OLD_NEW_PAINEL||'->'||NEW_NEW_PAINEL;
    END

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


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

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

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

SET TERM ; ^



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

COMMENT ON COLUMN TBCASO_ACAO_ENCERRAMENTO.NOVO_CASO IS 
'0 - Não Gera Novo Caso;
1 - Gera Novo Caso;';

COMMENT ON COLUMN TBCASO_ACAO_ENCERRAMENTO.NEW_PAINEL_ID IS 
'ID do Painel que será exibido após a finalização do Caso

Se opção de NOVO_CASO = 1 será disparado a inclusão de um novo caso';

