CREATE TABLE TBWORKFLOW (
  ID INTEGER  NOT NULL,
  TITULO VARCHAR(50),
  DESCRICAO VARCHAR(200),
  USUARIO_ID INTEGER,
  STATUS CHAR(1) DEFAULT '1',
  STATUSEXCLUSAO CHAR(1) DEFAULT '0',
  DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
  DATAHORA_UPDATE TIMESTAMP ,
  DATAHORA_DELETE TIMESTAMP 
);

ALTER TABLE TBWORKFLOW ADD PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_02 
ON TBWORKFLOW (STATUSEXCLUSAO);

CREATE GENERATOR GTBWORKFLOW;

CREATE TRIGGER TTBWORKFLOW_B0I FOR TBWORKFLOW
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_B0I IS 'Gerar ID automaticamente.';

CREATE INDEX ITBWORKFLOW_01 ON TBWORKFLOW(ID,STATUSEXCLUSAO);

CREATE TRIGGER TTBWORKFLOW_B0U FOR TBWORKFLOW
ACTIVE BEFORE 
  UPDATE
POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';


CREATE TRIGGER TTBWORKFLOW_B1U FOR TBWORKFLOW
ACTIVE BEFORE 
  UPDATE
POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    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 (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_B1U IS 'Registra historico de alteracoes.';




---------- TAREFA ---------------
---------------------------------

CREATE TABLE TBWORKFLOW_TAREFA (
  ID INTEGER  NOT NULL,
  WORKFLOW_ID INTEGER  NOT NULL,
  TITULO VARCHAR(50),
  DESCRICAO VARCHAR(200) ,
  SEQUENCIA SMALLINT DEFAULT 1,
  TEMPO_PREVISTO NUMERIC(15,4),
  STATUS CHAR(1) DEFAULT '1',
  ORDEM INTEGER,
  PONTO_REPROVACAO INTEGER,
  STATUSEXCLUSAO CHAR(1) DEFAULT '0',
  DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
  DATAHORA_UPDATE TIMESTAMP ,
  DATAHORA_DELETE TIMESTAMP 
);

COMMENT ON COLUMN TBWORKFLOW_TAREFA.TEMPO_PREVISTO IS
'Tempo previsto para execucao da tarefa em minutos.';

COMMENT ON COLUMN TBWORKFLOW_TAREFA.ORDEM IS
'Ordenacao (index) da tarefa.';

COMMENT ON COLUMN TBWORKFLOW_TAREFA.PONTO_REPROVACAO IS
'Id da tarefa que sera reativada quando a tarefa corrente for reprovada.';


ALTER TABLE TBWORKFLOW_TAREFA ADD PRIMARY KEY (ID,WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_TAREFA_02
ON TBWORKFLOW_TAREFA (STATUSEXCLUSAO);

CREATE GENERATOR GTBWORKFLOW_TAREFA;

CREATE TRIGGER TTBWORKFLOW_TAREFA_B0I FOR TBWORKFLOW_TAREFA
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_TAREFA, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_TAREFA_B0I IS 'Gerar ID automaticamente.';

CREATE INDEX ITBWORKFLOW_TAREFA_01 ON TBWORKFLOW_TAREFA(ID,WORKFLOW_ID,STATUSEXCLUSAO);


CREATE TRIGGER TTBWORKFLOW_TAREFA_B0U FOR TBWORKFLOW_TAREFA
ACTIVE BEFORE 
  UPDATE
POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_TAREFA',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_TAREFA_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBWORKFLOW_TAREFA_B1U FOR TBWORKFLOW_TAREFA
ACTIVE BEFORE 
UPDATE
POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

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

  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.SEQUENCIA <> NEW.SEQUENCIA) THEN
  BEGIN
     HISTORICO = HISTORICO||' SEQ:'||OLD.SEQUENCIA||'->'||NEW.SEQUENCIA;
  END

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

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

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_TAREFA', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     :HISTORICO);
  END
END;

COMMENT ON TRIGGER TTBWORKFLOW_TAREFA_B1U IS 'Registra historico de alteracoes.';



-------- DESTINATARIO -----------
---------------------------------

CREATE TABLE TBWORKFLOW_DESTINATARIO (
  ID INTEGER  NOT NULL,
  WORKFLOW_ID INTEGER  NOT NULL,
  WORKFLOW_TAREFA_ID INTEGER  NOT NULL,
  USUARIO_ID INTEGER ,
  STATUSEXCLUSAO CHAR(1) DEFAULT '0',
  DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
  DATAHORA_UPDATE TIMESTAMP ,
  DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBWORKFLOW_DESTINATARIO ADD PRIMARY KEY (ID,WORKFLOW_ID,WORKFLOW_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_DESTINATARIO_01 
ON TBWORKFLOW_DESTINATARIO(ID,WORKFLOW_ID,WORKFLOW_TAREFA_ID,STATUSEXCLUSAO);

CREATE INDEX ITBWORKFLOW_DESTINATARIO_02
ON TBWORKFLOW_DESTINATARIO(STATUSEXCLUSAO);

CREATE GENERATOR GTBWORKFLOW_DESTINATARIO;

CREATE TRIGGER TTBWORKFLOW_DESTINATARIO_B0I FOR TBWORKFLOW_DESTINATARIO
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_DESTINATARIO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_DESTINATARIO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBWORKFLOW_DESTINATARIO_B0U FOR TBWORKFLOW_DESTINATARIO
ACTIVE BEFORE 
  UPDATE
POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_DESTINATARIO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_DESTINATARIO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBWORKFLOW_DESTINATARIO_B1U FOR TBWORKFLOW_DESTINATARIO
ACTIVE BEFORE 
UPDATE
POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

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

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_DESTINATARIO', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     :HISTORICO);
  END
END;

COMMENT ON TRIGGER TTBWORKFLOW_DESTINATARIO_B1U IS 'Registra historico de alteracoes.';





---------- CAMPOS DINAMICOS ----------
--------------------------------------

CREATE TABLE TBWORKFLOW_CAMPO (
    ID INTEGER NOT NULL,
    WORKFLOW_ID INTEGER NOT NULL,
    WORKFLOW_TAREFA_ID INTEGER NOT NULL,
    ROTULO VARCHAR(100),
    TIPO CHAR(1),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP);

ALTER TABLE TBWORKFLOW_CAMPO ADD PRIMARY KEY (ID,WORKFLOW_ID,WORKFLOW_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_CAMPO_01
ON TBWORKFLOW_CAMPO(ID);

CREATE INDEX ITBWORKFLOW_CAMPO_02
ON TBWORKFLOW_CAMPO(WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_CAMPO_03
ON TBWORKFLOW_CAMPO(WORKFLOW_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_CAMPO_04
ON TBWORKFLOW_CAMPO(STATUSEXCLUSAO);



CREATE SEQUENCE GTBWORKFLOW_CAMPO;



CREATE TRIGGER TTBWORKFLOW_CAMPO_B0I FOR TBWORKFLOW_CAMPO
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_CAMPO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_CAMPO_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBWORKFLOW_CAMPO_B0U FOR TBWORKFLOW_CAMPO
ACTIVE BEFORE 
  UPDATE
POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_CAMPO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_CAMPO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE TRIGGER TTBWORKFLOW_CAMPO_B1U FOR TBWORKFLOW_CAMPO
ACTIVE BEFORE 
UPDATE
POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

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

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

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_CAMPO', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     :HISTORICO);
  END
END;

COMMENT ON TRIGGER TTBWORKFLOW_CAMPO_B1U IS 'Registra historico de alteracoes.';





---------- ITEM ----------
--------------------------

CREATE TABLE TBWORKFLOW_ITEM (
  ID INTEGER  NOT NULL,
  WORKFLOW_ID INTEGER  NOT NULL,
  TITULO VARCHAR(50) ,
  DESCRICAO VARCHAR(200) ,
  PROGRESSO SMALLINT DEFAULT 0,
  STATUS_CONCLUSAO CHAR(1) DEFAULT '0',
  USUARIO_ID INTEGER,
  DATAHORA_INI_PREVISTA TIMESTAMP ,
  DATAHORA_FIM_PREVISTA TIMESTAMP ,
  DATAHORA_INI TIMESTAMP ,
  DATAHORA_FIM TIMESTAMP ,
  STATUSEXCLUSAO CHAR(1) DEFAULT '0',
  DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
  DATAHORA_UPDATE TIMESTAMP ,
  DATAHORA_DELETE TIMESTAMP 
);


ALTER TABLE TBWORKFLOW_ITEM ADD PRIMARY KEY (ID,WORKFLOW_ID);

COMMENT ON COLUMN TBWORKFLOW_ITEM.DATAHORA_INI IS
'Definido ao iniciar a primeira tarefa pela trigger TTBWORKFLOW_ITEM_B1U.';

COMMENT ON COLUMN TBWORKFLOW_ITEM.DATAHORA_FIM IS
'Definido ao finalizar a ultima tarefa pela trigger TTBWORKFLOW_ITEM_B1U.';

COMMENT ON COLUMN TBWORKFLOW_ITEM.PROGRESSO IS
'Definido de acordo com o andamento das tarefas pela trigger TTBWORKFLOW_ITEM_TAREFA_B3U.';

CREATE INDEX ITBWORKFLOW_ITEM_02
ON TBWORKFLOW_ITEM (STATUSEXCLUSAO);

CREATE GENERATOR GTBWORKFLOW_ITEM;

CREATE TRIGGER TTBWORKFLOW_ITEM_B0I FOR TBWORKFLOW_ITEM
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B0I IS 'Gerar ID automaticamente.';


CREATE INDEX ITBWORKFLOW_ITEM_01 ON TBWORKFLOW_ITEM(ID,WORKFLOW_ID,STATUSEXCLUSAO);


CREATE TRIGGER TTBWORKFLOW_ITEM_B0U FOR TBWORKFLOW_ITEM
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN

    IF (NEW.STATUSEXCLUSAO = '1' AND OLD.PROGRESSO > 0) THEN
    BEGIN
        EXCEPTION "..." 'Workflow em andamento. Operacao cancelada.';
    END

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B0U IS 'Bloquear exclusao quando o workflow ja estiver iniciado.';


CREATE TRIGGER TTBWORKFLOW_ITEM_B1U FOR TBWORKFLOW_ITEM
ACTIVE BEFORE 
  UPDATE
POSITION 1
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B1U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBWORKFLOW_ITEM_B2U FOR TBWORKFLOW_ITEM
ACTIVE BEFORE UPDATE POSITION 2
AS
BEGIN

    -- Primeira tarefa sendo concluida.
    IF (OLD.PROGRESSO = 0 AND NEW.PROGRESSO > 0) THEN
    BEGIN
        NEW.DATAHORA_INI = CURRENT_TIMESTAMP;
    END

    -- Ultima tarefa sendo concluida.
    IF (NEW.PROGRESSO = 100) THEN
    BEGIN
        NEW.DATAHORA_FIM = CURRENT_TIMESTAMP;
    END

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B2U IS 'Definindo data de inicio e fim do workflow.';


CREATE TRIGGER TTBWORKFLOW_ITEM_B3U FOR TBWORKFLOW_ITEM
ACTIVE BEFORE UPDATE POSITION 3
AS
BEGIN
    
    -- Se nao estiver sendo encerrada
    IF (NEW.STATUS_CONCLUSAO <> '3') THEN
    BEGIN
    
        IF (NEW.PROGRESSO = 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '0';
        END
    
        ELSE IF (NEW.PROGRESSO = 100) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '2';
        END
    
        ELSE IF (NEW.PROGRESSO > 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '1';
        END

    END

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B3U IS 'Define o status do item de workflow.';


CREATE TRIGGER TTBWORKFLOW_ITEM_B4U FOR TBWORKFLOW_ITEM
ACTIVE BEFORE UPDATE POSITION 4
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' USU:'||OLD.USUARIO_ID||'->'||NEW.USUARIO_ID;
    END
    
    IF (OLD.DATAHORA_INI_PREVISTA <> NEW.DATAHORA_INI_PREVISTA) THEN
    BEGIN
       HISTORICO = HISTORICO||' INI PREV:'||OLD.DATAHORA_INI_PREVISTA||'->'||NEW.DATAHORA_INI_PREVISTA;
    END
    
    IF (OLD.DATAHORA_FIM_PREVISTA <> NEW.DATAHORA_FIM_PREVISTA) THEN
    BEGIN
       HISTORICO = HISTORICO||' FIM PREV:'||OLD.DATAHORA_FIM_PREVISTA||'->'||NEW.DATAHORA_FIM_PREVISTA;
    END
    
    IF (OLD.DATAHORA_INI <> NEW.DATAHORA_INI) THEN
    BEGIN
       HISTORICO = HISTORICO||' INI:'||OLD.DATAHORA_INI||'->'||NEW.DATAHORA_INI;
    END
    
    IF (OLD.DATAHORA_FIM <> NEW.DATAHORA_FIM) THEN
    BEGIN
       HISTORICO = HISTORICO||' FIM:'||OLD.DATAHORA_FIM||'->'||NEW.DATAHORA_FIM;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_B4U IS 'Registra historico de alteracoes.';







-------------------------------------------
-------------- MOVIMENTACAO ---------------
-------------------------------------------
CREATE TABLE TBWORKFLOW_ITEM_MOVIMENTACAO (
    ID INTEGER NOT NULL,
    WORKFLOW_ITEM_ID INTEGER,
    WORKFLOW_ITEM_TAREFA_ID INTEGER,
    USUARIO_ID INTEGER,
    STATUS_CONCLUSAO CHAR(1),
    DATAHORA TIMESTAMP DEFAULT 'NOW');

ALTER TABLE TBWORKFLOW_ITEM_MOVIMENTACAO
ADD CONSTRAINT PK_TBWORKFLOW_ITEM_MOVIMENTACAO
PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_ITEM_MOVIMENTAC_01
ON TBWORKFLOW_ITEM_MOVIMENTACAO (WORKFLOW_ITEM_ID);

CREATE SEQUENCE GTBWORKFLOW_ITEM_MOVIMENTACAO;

CREATE TRIGGER TTBWORKFLOW_ITEM_MOVIMENT_B0I FOR TBWORKFLOW_ITEM_MOVIMENTACAO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
    IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
        NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_MOVIMENTACAO, 1);
end;



---------- TAREFA DO ITEM -----------
-------------------------------------------

CREATE TABLE TBWORKFLOW_ITEM_TAREFA (
    ID INTEGER NOT NULL,
    WORKFLOW_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_ID INTEGER NOT NULL,
    TITULO VARCHAR(50),
    DESCRICAO VARCHAR(200),
    SEQUENCIA SMALLINT,
    STATUS_CONCLUSAO CHAR(1) DEFAULT '0',
    USUARIO_CONCLUSAO_ID INTEGER,
    TEMPO_PREVISTO NUMERIC(15,4),
    DATAHORA_INI_PREVISTA TIMESTAMP,
    DATAHORA_FIM_PREVISTA TIMESTAMP,
    DATAHORA_INI_RECALCULADA TIMESTAMP,
    DATAHORA_FIM_RECALCULADA TIMESTAMP,
    TEMPO_CONCLUSAO NUMERIC(15,4),
    DATAHORA_INI_CONCLUSAO TIMESTAMP,
    DATAHORA_FIM_CONCLUSAO TIMESTAMP,
    COMENTARIO VARCHAR(500),
    DOMINGO CHAR(1) DEFAULT '0',
    SEGUNDA CHAR(1) DEFAULT '1',
    TERCA CHAR(1) DEFAULT '1',
    QUARTA CHAR(1) DEFAULT '1',
    QUINTA CHAR(1) DEFAULT '1',
    SEXTA CHAR(1) DEFAULT '1',
    SABADO CHAR(1) DEFAULT '0',
    HORARIO_PERMITIDO VARCHAR(50),
    ORDEM INTEGER,
    PONTO_REPROVACAO INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP,
    VERIFICADOR INTEGER DEFAULT 0);


COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.TEMPO_PREVISTO IS
'Tempo previsto para execucao da tarefa em minutos.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_INI_PREVISTA IS
'Data inicial prevista para a tarefa.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_FIM_PREVISTA IS
'Data final prevista para a tarefa.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_INI_RECALCULADA IS
'Data inicial recalculada.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_FIM_RECALCULADA IS
'Data final recalculada, antes de finalizar.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.TEMPO_CONCLUSAO IS
'Tempo de conclusao da tarefa em minutos.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_INI_CONCLUSAO IS
'Data inicial que a tarefa foi efetuada.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.DATAHORA_FIM_CONCLUSAO IS
'Data final que a tarefa foi efetuada.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.HORARIO_PERMITIDO IS
'Faixa de horario permitida para a tarefa ser executada.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.ORDEM IS
'Ordenacao (index) da tarefa.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.PONTO_REPROVACAO IS
'Id da tarefa que sera reativada quando a tarefa corrente for reprovada.';

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.VERIFICADOR IS
'Verificar quando o insert de movimentacao ja tiver sido feito. Utilizado na trigger TTBWORKFLOW_ITEM_TAREFA_B2U';



ALTER TABLE TBWORKFLOW_ITEM_TAREFA
ADD CONSTRAINT PK_TBWORKFLOW_ITEM_TAREFA
PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_ITEM_TAREFA_01
ON TBWORKFLOW_ITEM_TAREFA (STATUSEXCLUSAO);


CREATE SEQUENCE GTBWORKFLOW_ITEM_TAREFA;




CREATE TRIGGER TTBWORKFLOW_ITEM_TAREFA_B0I FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_TAREFA, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBWORKFLOW_ITEM_TAREFA_B1I FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
DECLARE VARIABLE MAIOR_DATA TIMESTAMP;
BEGIN

    MAIOR_DATA = (SELECT MAX(WIT.DATAHORA_FIM_PREVISTA)
                    FROM TBWORKFLOW_ITEM_TAREFA WIT
                    WHERE WIT.STATUSEXCLUSAO = '0'
                    AND WIT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID);
    
    UPDATE TBWORKFLOW_ITEM WI
    SET WI.DATAHORA_FIM_PREVISTA = :MAIOR_DATA
    WHERE WI.ID = OLD.WORKFLOW_ITEM_ID;

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B1I IS 'Define a data final prevista do workflow.';



CREATE TRIGGER TTBWORKFLOW_ITEM_TAREFA_B0U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM_TAREFA',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_TAREFA_B1U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (OLD.SEQUENCIA <> NEW.SEQUENCIA) THEN
    BEGIN
        HISTORICO = HISTORICO||' SEQ:'||OLD.SEQUENCIA||'->'||NEW.SEQUENCIA;
    END
    
    IF (OLD.STATUS_CONCLUSAO <> NEW.STATUS_CONCLUSAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS CONC:'||OLD.STATUS_CONCLUSAO||'->'||NEW.STATUS_CONCLUSAO;
    END
    
    IF (OLD.USUARIO_CONCLUSAO_ID <> NEW.USUARIO_CONCLUSAO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO CONC:'||OLD.USUARIO_CONCLUSAO_ID||'->'||NEW.USUARIO_CONCLUSAO_ID;
    END

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

    IF (OLD.DATAHORA_INI_PREVISTA <> NEW.DATAHORA_INI_PREVISTA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT INI PREV:'||OLD.DATAHORA_INI_PREVISTA||'->'||NEW.DATAHORA_INI_PREVISTA;
    END

    IF (OLD.DATAHORA_FIM_PREVISTA <> NEW.DATAHORA_FIM_PREVISTA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT FIM PREV:'||OLD.DATAHORA_FIM_PREVISTA||'->'||NEW.DATAHORA_FIM_PREVISTA;
    END

    IF (OLD.DATAHORA_INI_RECALCULADA <> NEW.DATAHORA_INI_RECALCULADA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT INI REC:'||OLD.DATAHORA_INI_RECALCULADA||'->'||NEW.DATAHORA_INI_RECALCULADA;
    END

    IF (OLD.DATAHORA_FIM_RECALCULADA <> NEW.DATAHORA_FIM_RECALCULADA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT FIM REC:'||OLD.DATAHORA_FIM_RECALCULADA||'->'||NEW.DATAHORA_FIM_RECALCULADA;
    END

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

    IF (OLD.DATAHORA_INI_CONCLUSAO <> NEW.DATAHORA_INI_CONCLUSAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT INI CONC:'||OLD.DATAHORA_INI_CONCLUSAO||'->'||NEW.DATAHORA_INI_CONCLUSAO;
    END

    IF (OLD.DATAHORA_FIM_CONCLUSAO <> NEW.DATAHORA_FIM_CONCLUSAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT FIM CONC:'||OLD.DATAHORA_FIM_CONCLUSAO||'->'||NEW.DATAHORA_FIM_CONCLUSAO;
    END
    
    IF (OLD.COMENTARIO <> NEW.COMENTARIO) THEN
    BEGIN
        HISTORICO = HISTORICO||' COMENT:'||OLD.COMENTARIO||'->'||NEW.COMENTARIO;
    END

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

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

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

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

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM_TAREFA', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B1U IS 'Registra historico de alteracoes.';


CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_TAREFA_B2U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 2
AS
begin
    
    -- Não registrar quando ja tiver sido registrado e a tarefa tiver sendo criada.
    IF ( (NEW.VERIFICADOR = 1) AND ((NEW.STATUS_CONCLUSAO <> '0') OR (OLD.STATUS_CONCLUSAO = '3' AND NEW.STATUS_CONCLUSAO = '0')) ) THEN
    BEGIN
        
        INSERT INTO TBWORKFLOW_ITEM_MOVIMENTACAO (
            WORKFLOW_ITEM_ID,
            WORKFLOW_ITEM_TAREFA_ID,
            USUARIO_ID,
            STATUS_CONCLUSAO
        )
        VALUES (
            OLD.WORKFLOW_ITEM_ID,
            OLD.ID,
            NEW.USUARIO_CONCLUSAO_ID,
            NEW.STATUS_CONCLUSAO
        );

    END

    -- NEW.VERIFICADOR = 0; setado na proxima trigger (TTBWORKFLOW_ITEM_TAREFA_B3U)

end;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B2U IS 'Grava movimentacao da tarefa.';



CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_TAREFA_B3U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 3
AS
DECLARE VARIABLE QTD_TAREFA INTEGER;
DECLARE VARIABLE QTD_CONCLUIDA INTEGER;
BEGIN

    -- Se tiver concluindo ou reiniciando a tarefa.
    IF ((NEW.VERIFICADOR = 1) AND (NEW.STATUS_CONCLUSAO = '3' OR OLD.STATUS_CONCLUSAO = '3')) THEN
    BEGIN

        QTD_TAREFA  = (SELECT COUNT(WT.ID)
                        FROM TBWORKFLOW_ITEM_TAREFA WT
                        WHERE WT.STATUSEXCLUSAO = '0'
                        AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID);

        -- Se estiver reiniciando a tarefa.
        IF (OLD.STATUS_CONCLUSAO = '3') THEN
        BEGIN
            -- Subtrai -1 porque deve contar os concluidos anteriores menos o concluido atual, que esta sendo reiniciado.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)-1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND WT.STATUS_CONCLUSAO = '3');
        END
        ELSE
        BEGIN
            -- Adiciona +1 porque deve contar os concluidos anteriores e o concluido atual.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)+1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND WT.STATUS_CONCLUSAO = '3');
        END

        UPDATE TBWORKFLOW_ITEM W
        SET W.PROGRESSO = ((:QTD_CONCLUIDA * 100) / :QTD_TAREFA)
        WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
    END

    NEW.VERIFICADOR = 0;

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B3U IS 'Calcular o progresso do workflow.';



CREATE TRIGGER TTBWORKFLOW_ITEM_TAREFA_B4U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 4
AS
BEGIN
    
    -- Se a tarefa estiver sendo concluida
    IF (NEW.STATUS_CONCLUSAO = '3') THEN
    BEGIN

        UPDATE TBNOTIFICACAO N SET
            N.EXECUTADO = '1'
        WHERE
            N.TABELA = 'TBWORKFLOW_ITEM_TAREFA'
        AND N.TABELA_ID = OLD.ID;

    END

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_TAREFA_B4U IS 'Setar como enviadas as notificacoes da tarefa quando ela estiver sendo concluida.';








---------- DESTINATARIO DO ITEM -----------
-------------------------------------------

CREATE TABLE TBWORKFLOW_ITEM_DESTINATARIO (
    ID INTEGER NOT NULL,
    WORKFLOW_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_TAREFA_ID INTEGER NOT NULL,
    USUARIO_ID INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP);

ALTER TABLE TBWORKFLOW_ITEM_DESTINATARIO
ADD CONSTRAINT PK_TBWORKFLOW_ITEM_DESTINATARIO
PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_ITEM_DESTINATAR_01
ON TBWORKFLOW_ITEM_DESTINATARIO (STATUSEXCLUSAO);

CREATE SEQUENCE GTBWORKFLOW_ITEM_DESTINATARIO;



CREATE TRIGGER TTBWORKFLOW_ITEM_DESTIN_B0I FOR TBWORKFLOW_ITEM_DESTINATARIO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_DESTINATARIO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_DESTIN_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBWORKFLOW_ITEM_DESTIN_B0U FOR TBWORKFLOW_ITEM_DESTINATARIO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM_DESTINATARIO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_DESTIN_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE TRIGGER TTBWORKFLOW_ITEM_DESTIN_B1U FOR TBWORKFLOW_ITEM_DESTINATARIO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM_DESTINATARIO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_DESTIN_B1U IS 'Registra historico de alteracoes.';




-------------------------------------------
-------- CAMPOS DINAMICOS DO ITEM ---------
-------------------------------------------
CREATE TABLE TBWORKFLOW_ITEM_CAMPO (
    ID                       INTEGER NOT NULL,
    WORKFLOW_ID              INTEGER NOT NULL,
    WORKFLOW_ITEM_ID         INTEGER NOT NULL,
    WORKFLOW_ITEM_TAREFA_ID  INTEGER NOT NULL,
    ROTULO                   VARCHAR(100),
    TIPO                     CHAR(1),
    VALOR                    VARCHAR(1000),
    STATUSEXCLUSAO           CHAR(1) DEFAULT '0',
    DATAHORA_INSERT          TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE          TIMESTAMP,
    DATAHORA_DELETE          TIMESTAMP
);

ALTER TABLE TBWORKFLOW_ITEM_CAMPO ADD PRIMARY KEY (ID, WORKFLOW_ID, WORKFLOW_ITEM_ID, WORKFLOW_ITEM_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_01 ON TBWORKFLOW_ITEM_CAMPO (ID);
CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_02 ON TBWORKFLOW_ITEM_CAMPO (WORKFLOW_ID);
CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_03 ON TBWORKFLOW_ITEM_CAMPO (WORKFLOW_ITEM_ID);
CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_04 ON TBWORKFLOW_ITEM_CAMPO (WORKFLOW_ITEM_TAREFA_ID);
CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_05 ON TBWORKFLOW_ITEM_CAMPO (STATUSEXCLUSAO);

CREATE GENERATOR GTBWORKFLOW_ITEM_CAMPO;


CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_CAMPO_B0I FOR TBWORKFLOW_ITEM_CAMPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_CAMPO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_CAMPO_B0I IS 
'Gerar ID automaticamente.';


CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_CAMPO_B0U FOR TBWORKFLOW_ITEM_CAMPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM_CAMPO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_CAMPO_B0U IS 
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_CAMPO_B1U FOR TBWORKFLOW_ITEM_CAMPO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

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

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

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

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_ITEM_CAMPO', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     :HISTORICO);
  END

END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_CAMPO_B1U IS 
'Registra historico de alteracoes.';





-------------------------------------------
-------------- COMENTARIO -----------------
-------------------------------------------
CREATE TABLE TBWORKFLOW_ITEM_COMENTARIO (
    ID INTEGER NOT NULL,
    WORKFLOW_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_TAREFA_ID INTEGER NOT NULL,
    COMENTARIO VARCHAR(500),
    USUARIO_ID INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP);

ALTER TABLE TBWORKFLOW_ITEM_COMENTARIO
ADD CONSTRAINT PK_TBWORKFLOW_ITEM_COMENTARIO
PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_ITEM_COMENTARIO_01
ON TBWORKFLOW_ITEM_COMENTARIO (STATUSEXCLUSAO);

CREATE SEQUENCE GTBWORKFLOW_ITEM_COMENTARIO;



CREATE TRIGGER TTBWORKFLOW_ITEM_COMENT_B0I FOR TBWORKFLOW_ITEM_COMENTARIO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_COMENTARIO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_COMENT_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBWORKFLOW_ITEM_COMENT_B0U FOR TBWORKFLOW_ITEM_COMENTARIO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM_COMENTARIO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_COMENT_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE TRIGGER TTBWORKFLOW_ITEM_COMENT_B1U FOR TBWORKFLOW_ITEM_COMENTARIO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM_COMENTARIO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_COMENT_B1U IS 'Registra historico de alteracoes.';




------- USUÁRIO NOTIFICADO DO ITEM --------
-------------------------------------------

CREATE TABLE TBWORKFLOW_ITEM_NOTIFICADO (
    ID INTEGER NOT NULL,
    WORKFLOW_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_ID INTEGER NOT NULL,
    WORKFLOW_ITEM_TAREFA_ID INTEGER NOT NULL,
    USUARIO_ID INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP);

ALTER TABLE TBWORKFLOW_ITEM_NOTIFICADO
ADD CONSTRAINT PK_TBWORKFLOW_ITEM_NOTIFICADO
PRIMARY KEY (ID);

CREATE INDEX ITBWORKFLOW_ITEM_NOTIFICADO_01
ON TBWORKFLOW_ITEM_NOTIFICADO (STATUSEXCLUSAO);


CREATE SEQUENCE GTBWORKFLOW_ITEM_NOTIFICADO;


CREATE TRIGGER TTBWORKFLOW_ITEM_NOTIF_B0I FOR TBWORKFLOW_ITEM_NOTIFICADO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_NOTIFICADO, 1);
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_NOTIF_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBWORKFLOW_ITEM_NOTIF_B0U FOR TBWORKFLOW_ITEM_NOTIFICADO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBWORKFLOW_ITEM_NOTIFICADO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_NOTIF_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE TRIGGER TTBWORKFLOW_ITEM_NOTIF_B1U FOR TBWORKFLOW_ITEM_NOTIFICADO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM_NOTIFICADO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBWORKFLOW_ITEM_NOTIF_B1U IS 'Registra historico de alteracoes.';










INSERT INTO TBCONTROLE_U (ID, GRUPO, PARAMETRO, VALOR_EXT, COMENTARIO, MENU, MENUS, VALOR_ADMIN) 
VALUES (220, 'WEBWOR', 'PERMITE REINICIAR UMA TAREFA DE WORKFLOW', '0', '0 = NÃO | 1 = SIM', '29012', NULL, NULL);

INSERT INTO TBCONTROLE_U (ID, GRUPO, PARAMETRO, VALOR_EXT, COMENTARIO, MENU, MENUS, VALOR_ADMIN) 
VALUES (224, 'WEBWOR', 'PERFIL PARA WORKFLOW', '0', '1 = ADMINISTRADOR | 2 = CRIADOR | 3 = OPERADOR', NULL, NULL, NULL);



INSERT INTO TBMENU (DESCRICAO, CONTROLE, GRUPO, SUBGRUPO, TIPO)
VALUES ('CADASTRO DE MODELO DE WORKFLOW', 29010, 'WOR', 'WOR', 1);

INSERT INTO TBMENU (DESCRICAO, CONTROLE, GRUPO, SUBGRUPO, TIPO)
VALUES ('CADASTRO DE WORKFLOW', 29011, 'WOR', 'WOR', 1);

INSERT INTO TBMENU (DESCRICAO, CONTROLE, GRUPO, SUBGRUPO, TIPO)
VALUES ('WORKFLOW', 29012, 'WOR', 'WOR', 1);

INSERT INTO TBMENU (DESCRICAO, CONTROLE, GRUPO, SUBGRUPO, TIPO)
VALUES ('CRONOGRAMA DE WORKFLOW', 29013, 'WOR', 'WOR', 1);



INSERT INTO TBUSUARIO_PERFIL_GRUPO (PERFIL_ID, MENU_GRUPO, INCLUIR, ALTERAR, EXCLUIR, IMPRIMIR, NEGAR)
VALUES (1, 'WOR', 1, 1, 1, 1, 0);