CREATE INDEX ITBWORKFLOW_ITEM_03
ON TBWORKFLOW_ITEM (WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_ITEM_04
ON TBWORKFLOW_ITEM (PROGRESSO);

CREATE INDEX ITBWORKFLOW_ITEM_05
ON TBWORKFLOW_ITEM (STATUS_CONCLUSAO);

CREATE INDEX ITBWORKFLOW_ITEM_06
ON TBWORKFLOW_ITEM (USUARIO_ID);

CREATE INDEX ITBWORKFLOW_ITEM_07
ON TBWORKFLOW_ITEM (DESCRICAO);

CREATE INDEX ITBWORKFLOW_DESTINATARIO_05
ON TBWORKFLOW_DESTINATARIO (WORKFLOW_ID);

CREATE INDEX ITBWORKFLOW_DESTINATARIO_06
ON TBWORKFLOW_DESTINATARIO (WORKFLOW_TAREFA_ID);

ALTER TABLE TBWORKFLOW_DESTINATARIO
ADD REINICIAR VARCHAR(1) CHARACTER SET UTF8 
DEFAULT '0' 
COLLATE UTF8;

CREATE INDEX ITBWORKFLOW_DESTINATARIO_07
ON TBWORKFLOW_DESTINATARIO (REINICIAR);

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


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 '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' INICIADA.',
              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 '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' PAUSADA.',
              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 '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' REINICIADA.',
              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 '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' CONCLUIDA.',
              FN_CURRENT_USER_ID()
         );
    END  

    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 3)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 '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' REINICIADA.',
              FN_CURRENT_USER_ID()
         );
    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(NEW.STATUS_CONCLUSAO = '1' AND OLD.STATUS_CONCLUSAO = '3')THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = 1
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        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;