ALTER TABLE TBPROGRAMACAO
    ADD OPERADOR_ID INTEGER,
    ADD JUSTIFICATIVA_ID INTEGER;

COMMENT ON COLUMN TBPROGRAMACAO.OPERADOR_ID IS
'ID DO OPERADOR, É UTILIZADO NA TRIGER Q GRAVA O HISTORICO';

COMMENT ON COLUMN TBPROGRAMACAO.JUSTIFICATIVA_ID IS
'ID DA JUSTIFICATIVA, É UTILIZADO NA TRIGER Q GRAVA O HISTORICO';

CREATE trigger tbprogramacao_bu5 for tbprogramacao
active before update position 0
AS
begin

    if(old.status = 1 and new.status = 2)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 0 , NEW.operador_id,NEW.justificativa_id);
    end

    if(old.status = 2 and new.status = 3)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 2 , NEW.operador_id,NEW.justificativa_id);
    end

    if(old.status = 2 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id);
    end

    if(old.status = 3 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id);
    end
    
end;

CREATE OR ALTER trigger tbprogramacao_bu5 for tbprogramacao
active before update position 0
AS
begin

    if(old.status = 1 and new.status = 2)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 0 , NEW.operador_id,NEW.justificativa_id);

        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'I',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 2 and new.status = 3)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 2 , NEW.operador_id,NEW.justificativa_id);
        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'F',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 2 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id);
        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'P',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 3 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id);
    end
    
end;

CREATE OR ALTER trigger tbregistro_parada_bi0 for tbregistro_parada
active before insert position 0
AS
DECLARE VARIABLE ID INT;
BEGIN
  IF(NEW.STATUS > 0)THEN
  BEGIN
    FOR
        SELECT P.ID FROM TBPROGRAMACAO P
    
        WHERE 1 = 1
        AND P.GP_ID   = NEW.VINCULO_ID
        AND P.UP_ID   = NEW.SUBVINCULO_ID
        AND P.ESTACAO = NEW.TABELA_ID
        AND P.STATUS  = 2
    
    INTO :ID
    DO
    BEGIN
        UPDATE TBPROGRAMACAO SET STATUS = 1 WHERE ID = :ID;
    END
  END
END;

CREATE OR ALTER trigger tbregistro_parada_bi0 for tbregistro_parada
active before insert position 0
AS
DECLARE VARIABLE ID INT;
BEGIN
  IF(NEW.STATUS > 0)THEN
  BEGIN
    FOR
        SELECT P.ID FROM TBPROGRAMACAO P
    
        WHERE 1 = 1
        AND P.GP_ID   = NEW.VINCULO_ID
        AND P.UP_ID   = NEW.SUBVINCULO_ID
        AND P.ESTACAO = NEW.TABELA_ID
        AND P.STATUS  = 2
    
    INTO :ID
    DO
    BEGIN
        UPDATE TBPROGRAMACAO P SET P.STATUS = 1, P.operador_id = new.operador_id, p.justificativa_id = new.status  WHERE ID = :ID;
    END
  END
END;

ALTER TABLE TBPROGRAMACAO_REGISTRO
    ADD REQUISICAO_ID INTEGER;

COMMENT ON COLUMN TBPROGRAMACAO_REGISTRO.REQUISICAO_ID IS
'ID DA ULTIMA REQUISICAO DO TALAO';

ALTER TABLE TBPROGRAMACAO ALTER COLUMN REQUISICAO_ID
SET DEFAULT 0

CREATE OR ALTER trigger tbprogramacao_bu5 for tbprogramacao
active before update position 0
AS
begin

    if(old.status = 1 and new.status = 2)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID, REQUISICAO_ID)
                        VALUES (new.id, current_timestamp, 0 , NEW.operador_id,NEW.justificativa_id, NEW.requisicao_id);

        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'I',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 2 and new.status = 3)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID, REQUISICAO_ID)
                        VALUES (new.id, current_timestamp, 2 , NEW.operador_id,NEW.justificativa_id, NEW.requisicao_id);
        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'F',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 2 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID, REQUISICAO_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id, NEW.requisicao_id);
        INSERT INTO
                TBFERRAMENTARIA_HISTORICO
                (
                    FERRAMENTA_ID,
                    STATUS,
                    OPERADOR_ID,
                    GP_ID,
                    UP_ID,
                    ESTACAO,
                    MOTIVO_PARADA,
                    TALAO_ID
                )
                
                VALUES (
                    new.ferramenta_id,
                    'P',
                    new.operador_id,
                    new.gp_id,
                    new.up_id,
                    new.estacao,
                    new.justificativa_id,
                    new.tabela_id
                );
    end

    if(old.status = 3 and new.status = 1)then
    begin
        INSERT INTO TBPROGRAMACAO_REGISTRO (PROGRAMACAO_ID, DATAHORA, STATUS, OPERADOR_ID, JUSTIFICATIVA_ID, REQUISICAO_ID)
                        VALUES (new.id, current_timestamp, 1 , NEW.operador_id,NEW.justificativa_id, NEW.requisicao_id);
    end
    
end;

CREATE OR ALTER trigger ttbrequisicao_b0d_02 for tbrequisicao
active before delete position 0
AS
DECLARE VARIABLE PROGRAMACAO_ID INTEGER;
DECLARE VARIABLE STATUS INTEGER;
DECLARE VARIABLE LINHA INTEGER;
BEGIN

  FOR
     SELECT first 1
         P.ID,
         P.STATUS,
         ROW_NUMBER() OVER (ORDER BY P.DATAHORA_FIM DESC) AS LINHA
     FROM TBPROGRAMACAO P
     WHERE 1 = 1
     AND P.GP_ID = OLD.GP_ID
     AND P.REMESSA_ID = OLD.REMESSA
     AND P.PRODUTO_ID = OLD.PRODUTO_ID
     AND P.TAMANHO = OLD.TAMANHO
     AND P.STATUS_REQUISICAO = 1

     ORDER BY P.DATAHORA_FIM DESC
  INTO
    :PROGRAMACAO_ID,
    :STATUS,
    :LINHA
  DO
  BEGIN

    --NAO PERMITE EXCLUIR O VINCULO SE O TALAO FOI FINALIZADO
    IF(:STATUS = 3)THEN
    BEGIN
        EXCEPTION "..." 'REQUISICAO JÁ FOI UTILIZADA, REMESSA:'||:OLD.REMESSA||' GP:'||OLD.GP_ID||'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
    END ELSE
    BEGIN
        UPDATE TBPROGRAMACAO P SET P.STATUS = 3, P.STATUS_REQUISICAO = 0, P.requisicao_id = 0 WHERE P.ID = :PROGRAMACAO_ID;
    END

  END
end;




