UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO, DATAHORA)
                      VALUES (29030, 'WOR', 'WOR', '1   ', '', 'REGISTRO DE TAREFAS DE WORKFLOW', '2021-06-29 08:21:02')
                    MATCHING (CONTROLE);

COMMIT WORK;

ALTER TABLE TBWORKFLOW_ITEM_DESTINATARIO
ADD NOTIFICACAO VARCHAR(1) CHARACTER SET UTF8 
DEFAULT 0 
COLLATE UTF8;

ALTER TABLE TBWORKFLOW_ITEM_CAMPO ADD ITENS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
ALTER TABLE TBWORKFLOW_CAMPO 	  ADD ITENS BLOB SUB_TYPE 1 SEGMENT SIZE 80;

ALTER TABLE TBWORKFLOW_ITEM_TAREFA 
    ADD WORKFLOW_TAREFA_MODELO_ID INTEGER;
	
ALTER TABLE TBWORKFLOW_ITEM_CAMPO 
    ADD WORKFLOW_TAREFA_CAMPO_ID INTEGER;

ALTER TABLE TBWORKFLOW_TAREFA 	   
	ADD TIPO_DESCRICAO VARCHAR(1) DEFAULT 1;
	
ALTER TABLE TBWORKFLOW_CAMPO 	   
	ADD REQUERIDO VARCHAR(1) DEFAULT 0;

ALTER TABLE TBWORKFLOW_CAMPO
	ADD EXIBIR_PAINEL VARCHAR(1) DEFAULT 0;
	
CREATE GENERATOR GTBWORKFLOW_ITEM_HISTORICO;

CREATE TABLE TBWORKFLOW_ITEM_HISTORICO (
    ID                        INTEGER NOT NULL,
    WORKFLOW_ITEM_ID          INTEGER DEFAULT 0,
    WORKFLOW_ITEM_TAREFA_ID   INTEGER DEFAULT 0,
    HISTORICO                 VARCHAR(1000),
    USUARIO_ID                INTEGER,
    DATAHORA                  TIMESTAMP DEFAULT 'NOW'
);

/******************************************************************************/
/****                             PRIMARY KEYS                             ****/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_ITEM_HISTORICO ADD PRIMARY KEY (ID);

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;

        IF(((:QTD_CONCLUIDA * 100) / :QTD_TAREFA) >= 100)THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.PROGRESSO = 100,
                W.STATUS_CONCLUSAO = '3'
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        NEW.VERIFICADOR = 0;
    END

END;

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;

        IF(:QTD_CONCLUIDA >= 100)THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.PROGRESSO = 100,
                W.STATUS_CONCLUSAO = '3'
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        NEW.VERIFICADOR = 0;
    END

END;

CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_TAREFA_B05U FOR TBWORKFLOW_ITEM_TAREFA
ACTIVE BEFORE UPDATE POSITION 5
AS
BEGIN
    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 0)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||(SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_ITEM_ID)||' INICIADA PELO OPERADOR '||
              COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(B.NOME) > 0, B.NOME,B.USUARIO) FROM TBUSUARIO B WHERE B.CODIGO = FN_CURRENT_USER_ID()),''),
              FN_CURRENT_USER_ID()
         );
    END 

    IF(NEW.STATUS_CONCLUSAO = 2 AND OLD.STATUS_CONCLUSAO = 1)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||(SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_ITEM_ID)||' PAUSADA PELO OPERADOR '||
              COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(B.NOME) > 0, B.NOME,B.USUARIO) FROM TBUSUARIO B WHERE B.CODIGO = FN_CURRENT_USER_ID()),''),
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 2)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||(SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_ITEM_ID)||' REINICIADA PELO OPERADOR '||
              COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(B.NOME) > 0, B.NOME,B.USUARIO) FROM TBUSUARIO B WHERE B.CODIGO = FN_CURRENT_USER_ID()),''),
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 3 AND OLD.STATUS_CONCLUSAO = 1)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||(SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_ITEM_ID)||' CONCLUIDA PELO OPERADOR '||
              COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(B.NOME) > 0, B.NOME,B.USUARIO) FROM TBUSUARIO B WHERE B.CODIGO = FN_CURRENT_USER_ID()),''),
              FN_CURRENT_USER_ID()
         );
    END
END;

/******************************************************************************/
/****                                TABLES                                ****/
/******************************************************************************/


CREATE GENERATOR GTBWORKFLOW_DEPENDENCIA;

CREATE TABLE TBWORKFLOW_DEPENDENCIA (
    ID                       INTEGER NOT NULL,
    WORKFLOW_TAREFA_ID  	 INTEGER,
    WORKFLOW_DEPENDENTE_ID   INTEGER,
    USUARIO_ID  			 INTEGER
);
/******************************************************************************/
/****                             PRIMARY KEYS                             ****/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_DEPENDENCIA ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                         TRIGGERS FOR TABLES                          ****/
/******************************************************************************/



/* TRIGGER: TTBWORKFLOW_DEPENDENCIA_B01I */
CREATE OR ALTER TRIGGER TTBWORKFLOW_DEPENDENCIA_B01I FOR TBWORKFLOW_DEPENDENCIA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF(NEW.ID IS NULL OR NEW.ID = 0) THEN
  BEGIN
      NEW.ID = GEN_ID(GTBWORKFLOW_DEPENDENCIA, 1);
  END
END;

/******************************************************************************/
/****                                TABLES                                ****/
/******************************************************************************/


CREATE GENERATOR GTBWORKFLOW_ITEM_DEPENDENCIA;

CREATE TABLE TBWORKFLOW_ITEM_DEPENDENCIA (
    ID                       	 INTEGER NOT NULL,
    WORKFLOW_ITEM_TAREFA_ID  	 INTEGER,
    WORKFLOW_ITEM_DEPENDENTE_ID  INTEGER,
    USUARIO_ID  			 	 INTEGER
);
/******************************************************************************/
/****                             PRIMARY KEYS                             ****/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_ITEM_DEPENDENCIA ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                         TRIGGERS FOR TABLES                          ****/
/******************************************************************************/



/* TRIGGER: TTBWORKFLOW_DEPENDENCIA_B01I */
CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_DEPEND_B01I FOR TBWORKFLOW_ITEM_DEPENDENCIA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF(NEW.ID IS NULL OR NEW.ID = 0) THEN
  BEGIN
      NEW.ID = GEN_ID(GTBWORKFLOW_ITEM_DEPENDENCIA, 1);
  END
END;

ALTER TABLE TBWORKFLOW_DEPENDENCIA
ADD WORKFLOW_ID INTEGER;

ALTER TABLE TBWORKFLOW_ITEM_DEPENDENCIA
ADD WORKFLOW_ID INTEGER;

ALTER TABLE TBWORKFLOW_ITEM_DEPENDENCIA
ADD WORKFLOW_ITEM_ID INTEGER;

CREATE INDEX ITBWORKFLOW_ITEM_TAREFA_02
ON TBWORKFLOW_ITEM_TAREFA (WORKFLOW_TAREFA_MODELO_ID);

CREATE INDEX ITBWORKFLOW_DEPENDENCIA_01
ON TBWORKFLOW_DEPENDENCIA (WORKFLOW_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_DEPENDENCIA_02
ON TBWORKFLOW_DEPENDENCIA (WORKFLOW_DEPENDENTE_ID);

CREATE INDEX ITBWORKFLOW_DEPENDENCIA_03
ON TBWORKFLOW_DEPENDENCIA (WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_ITEM_DEPENDENCIA_01
ON TBWORKFLOW_ITEM_DEPENDENCIA (WORKFLOW_ITEM_TAREFA_ID);

CREATE INDEX ITBWORKFLOW_ITEM_DEPENDENCIA_02
ON TBWORKFLOW_ITEM_DEPENDENCIA (WORKFLOW_ITEM_DEPENDENTE_ID);

CREATE INDEX ITBWORKFLOW_ITEM_DEPENDENCIA_03
ON TBWORKFLOW_ITEM_DEPENDENCIA (WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_ITEM_DEPENDENCIA_04
ON TBWORKFLOW_ITEM_DEPENDENCIA (WORKFLOW_ITEM_ID);

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