ALTER TABLE TBWORKFLOW_CAMPO
ADD FAIXA_VALOR_MIN NUMERIC(10,2)
DEFAULT 0;

ALTER TABLE TBWORKFLOW_CAMPO
ADD FAIXA_VALOR_MAX NUMERIC(10,2)
DEFAULT 99999999.99;

ALTER TABLE TBWORKFLOW
ADD TIPO VARCHAR(10) CHARACTER SET UTF8 
DEFAULT '0' 
COLLATE UTF8;

CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_B01D FOR TBWORKFLOW_ITEM
ACTIVE BEFORE DELETE POSITION 0
AS
begin
   if(old.PROGRESSO > 0)then
   begin
      exception "..." 'O WORKFLOW JÁ ESTÁ EM ANDAMENTO, OPERAÇÃO CANCELADA!';
   end
    
   if(old.STATUS_CONCLUSAO = '3')then
   begin
      exception "..." 'O WORKFLOW JÁ FOI CONCLUÍDO, OPERAÇÃO CANCELADA!';
   end
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_B02D FOR TBWORKFLOW_ITEM
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'REGISTRO EXCLUIDO';
  END
  
  IF (HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_ITEM',OLD.ID, HISTORICO);
      
      INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
          WORKFLOW_ITEM_TAREFA_ID,
          WORKFLOW_ITEM_ID,
          HISTORICO,
          USUARIO_ID
       )VALUES(
          0,
          OLD.ID,
          'WOKRFLOW EXCLUIDO 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;

CREATE OR ALTER TRIGGER TTBWORKFLOW_ITEM_B02I FOR TBWORKFLOW_ITEM
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'REGISTRO INCLUIDO';
  END
  
  IF (HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_ITEM',NEW.ID, HISTORICO);
      
      INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
          WORKFLOW_ITEM_TAREFA_ID,
          WORKFLOW_ITEM_ID,
          HISTORICO,
          USUARIO_ID
       )VALUES(
          0,
          NEW.ID,
          'WOKRFLOW INCLUIDO 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;

CREATE OR ALTER TRIGGER TTBWORKFLOW_B02I FOR TBWORKFLOW
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'REGISTRO INCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_ITEM',NEW.ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_B02D FOR TBWORKFLOW
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'REGISTRO EXCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_ITEM',OLD.ID, HISTORICO);
  END
end;

CREATE OR ALTER 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 (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', 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;

CREATE TRIGGER TTBWORKFLOW_B01D FOR TBWORKFLOW
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable WORKFLOW_ITEM_ID INTEGER;
begin
   :WORKFLOW_ITEM_ID = COALESCE((SELECT FIRST 1 A.ID FROM TBWORKFLOW_ITEM A WHERE A.WORKFLOW_ID = OLD.ID AND A.STATUS_CONCLUSAO = '1'),0);
   IF(:WORKFLOW_ITEM_ID > 0)THEN
   BEGIN
       EXCEPTION "..." 'MODELO POSSUI WORKFLOWS EM ABERTO, OPERAÇÃO CANCELADA!';
   END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_TAREFA_B01D FOR TBWORKFLOW_TAREFA
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'TAREFA '||OLD.TITULO||' EXCLUIDA';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW',OLD.WORKFLOW_ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_TAREFA_B021 FOR TBWORKFLOW_TAREFA
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'TAREFA '||NEW.TITULO||' INCLUIDA';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
      EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW',NEW.WORKFLOW_ID, HISTORICO);
  END
end;

CREATE OR ALTER 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 (OLD.REQUERIDO <> NEW.REQUERIDO) THEN
  BEGIN
     HISTORICO = HISTORICO||' REQ:'||OLD.REQUERIDO||'->'||NEW.REQUERIDO;
  END 

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

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

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

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_TAREFA', OLD.WORKFLOW_TAREFA_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;

CREATE OR ALTER TRIGGER TTBWORKFLOW_CAMPO_B01D FOR TBWORKFLOW_CAMPO
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'CAMPO '||OLD.ROTULO||' EXCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',OLD.WORKFLOW_TAREFA_ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_CAMPO_B021 FOR TBWORKFLOW_CAMPO
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
begin
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'CAMPO '||NEW.ROTULO||' INCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',NEW.WORKFLOW_TAREFA_ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_DESTINATARIO_B01D FOR TBWORKFLOW_DESTINATARIO
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
declare variable USUARIO   VARCHAR(500);
begin
  USUARIO = COALESCE((SELECT FIRST 1 iif(char_length(a.nome) > 1,a.nome,a.usuario) from tbusuario a where a.codigo = old.usuario_id),'');
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'USUARIO '||:USUARIO||' EXCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',OLD.WORKFLOW_TAREFA_ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_DESTINATARIO_B021 FOR TBWORKFLOW_DESTINATARIO
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
declare variable USUARIO   VARCHAR(500);
begin
  USUARIO = COALESCE((SELECT FIRST 1 iif(char_length(a.nome) > 1,a.nome,a.usuario) from tbusuario a where a.codigo = new.usuario_id),'');
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'USUARIO '||:USUARIO||' INCLUIDO';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',NEW.WORKFLOW_TAREFA_ID, HISTORICO);
  END
end;


CREATE OR ALTER TRIGGER TTBWORKFLOW_DEPENDENCIA_B01D FOR TBWORKFLOW_DEPENDENCIA
ACTIVE BEFORE DELETE POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
declare variable TAREFA    VARCHAR(500);
begin
  TAREFA = COALESCE((SELECT FIRST 1 A.TITULO from TBWORKFLOW_TAREFA a where a.ID = old.WORKFLOW_TAREFA_ID),'');
  IF(OLD.ID > 0)THEN
  BEGIN
    HISTORICO = 'DEP. TAREFA '||:TAREFA||' EXCLUIDA';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',OLD.WORKFLOW_DEPENDENTE_ID, HISTORICO);
  END
end;

CREATE OR ALTER TRIGGER TTBWORKFLOW_DEPENDENCIA_B021 FOR TBWORKFLOW_DEPENDENCIA
ACTIVE BEFORE INSERT POSITION 2
AS
declare variable HISTORICO VARCHAR(500);
declare variable TAREFA    VARCHAR(500);
begin
  TAREFA = COALESCE((SELECT FIRST 1 A.TITULO from TBWORKFLOW_TAREFA a where a.ID = NEW.WORKFLOW_TAREFA_ID),'');
  IF(NEW.ID > 0)THEN
  BEGIN
    HISTORICO = 'DEP. TAREFA '||:TAREFA||' INCLUIDA';
  END
  
  IF(HISTORICO <> '') THEN
  BEGIN
    EXECUTE PROCEDURE SPI_HISTORICO('TBWORKFLOW_TAREFA',NEW.WORKFLOW_DEPENDENTE_ID, HISTORICO);
  END
end;


UPDATE TBWORKFLOW SET TIPO = '0';