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


CREATE GENERATOR GTBWORKFLOW_CAMPO_ENCERRAMENTO;

CREATE TABLE TBWORKFLOW_CAMPO_ENCERRAMENTO (
    ID                      INTEGER NOT NULL,
    WORKFLOW_ID             INTEGER NOT NULL,
    WORKFLOW_TAREFA_ID      INTEGER NOT NULL,
    WORKFLOW_CAMPO_ID       INTEGER NOT NULL,
    CAMPO_VALOR_ITEM        BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    TAREFA_ID_ENCERRAMENTO  INTEGER
);




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

ALTER TABLE TBWORKFLOW_CAMPO_ENCERRAMENTO ADD CONSTRAINT PK_TBWORKFLOW_CAMPO_ENCERRAMENT PRIMARY KEY (ID, WORKFLOW_ID, WORKFLOW_TAREFA_ID, WORKFLOW_CAMPO_ID);


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



SET TERM ^ ;



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



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

    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = 'TAREFA '||OLD.TAREFA_ID_ENCERRAMENTO||' EXCLUIDO DO ENCERRAMENTO DO CAMPO '||OLD.WORKFLOW_CAMPO_ID;
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA', OLD.WORKFLOW_TAREFA_ID, HISTORICO);
    END
END
^


/* Trigger: TTBWORKFLOW_CAMPO_ENC_B01I */
CREATE OR ALTER TRIGGER TTBWORKFLOW_CAMPO_ENC_B01I FOR TBWORKFLOW_CAMPO_ENCERRAMENTO
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbworkflow_campo_encerramento,1);
end
^


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

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'TAREFA '||NEW.TAREFA_ID_ENCERRAMENTO||' NO CAMPO '||OLD.WORKFLOW_CAMPO_ID||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA', OLD.WORKFLOW_TAREFA_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


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

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = 'TAREFA '||NEW.TAREFA_ID_ENCERRAMENTO||' INCLUIDO NO ENCERRAMENTO DO CAMPO '||NEW.WORKFLOW_CAMPO_ID;
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA', NEW.WORKFLOW_TAREFA_ID, HISTORICO);
    END
END
^

SET TERM ; ^



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

COMMENT ON COLUMN TBWORKFLOW_CAMPO_ENCERRAMENTO.TAREFA_ID_ENCERRAMENTO IS 
'ID da Tarefa que será encerrada';


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

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_campo_b02d for tbworkflow_campo
active before delete position 1
AS
declare variable WORKFLOW_ITEM_ID   INTEGER;
declare variable WORKFLOW_DESCRICAO VARCHAR(200);
begin
   WORKFLOW_ITEM_ID   = COALESCE((SELECT FIRST 1 H.ID
                                      FROM TBWORKFLOW_ITEM_CAMPO  T,
                                           TBWORKFLOW_ITEM_TAREFA G,
                                           TBWORKFLOW_TAREFA      H
                                       WHERE t.WORKFLOW_TAREFA_CAMPO_ID = OLD.ID
                                         AND G.WORKFLOW_ITEM_ID         = T.WORKFLOW_ITEM_ID
                                         AND H.ID                       = G.WORKFLOW_TAREFA_MODELO_ID
                                    ), 0);

   WORKFLOW_DESCRICAO = COALESCE((SELECT FIRST 1 H.TITULO
                                      FROM TBWORKFLOW_ITEM_CAMPO  T,
                                           TBWORKFLOW_ITEM_TAREFA G,
                                           TBWORKFLOW_TAREFA      H
                                       WHERE t.WORKFLOW_TAREFA_CAMPO_ID = OLD.ID
                                         AND G.WORKFLOW_ITEM_ID         = T.WORKFLOW_ITEM_ID
                                         AND H.ID                       = G.WORKFLOW_TAREFA_MODELO_ID

                                    ),'');

   IF(:WORKFLOW_ITEM_ID > 0)THEN
   BEGIN
       EXCEPTION "..." ' CAMPO: '||COALESCE(OLD.ROTULO,'')||', NA TAREFA: '||:WORKFLOW_DESCRICAO||' ESTA SENDO UTILIZADO EM WORKFLOWS, OPERAÇÃO CANCELADA!';
   END


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

   DELETE FROM TBWORKFLOW_CAMPO_ENCERRAMENTO A WHERE A.WORKFLOW_ID = OLD.WORKFLOW_ID AND A.WORKFLOW_TAREFA_ID = OLD.WORKFLOW_TAREFA_ID AND A.WORKFLOW_CAMPO_ID = OLD.ID;

end
^

SET TERM ; ^

