ALTER TABLE TBACESSO_IP
    ADD TODOS INTEGER DEFAULT 1;

UPDATE TBACESSO_IP SET 
    IP = '*',
    STATUS = 1,
    DESCRICAO = 'PADRÃO',
    BLOQUEAR_HORA = 0,
    BLOQUEAR = 0,
    HORA_INICIO = '00:00:00',
    HORA_FIM = '23:59:59',
    SEG = 1,
    TER = 1,
    QUA = 1,
    QUI = 1,
    SEX = 1,
    SAB = 1,
    DOM = 1,
    TODOS = 1
WHERE (ID = 1);

CREATE TABLE TBACESSO_IP_USUARIO (
    ID INTEGER NOT NULL,
    USUARIO_ID INTEGER,
    ACESSO_IP_ID INTEGER);

ALTER TABLE TBACESSO_IP_USUARIO
ADD CONSTRAINT PK_TBACESSO_IP_USUARIO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBACESSO_IP_USUARIO;

SET TERM ^ ;

create trigger Ttbacesso_ip_usuario_b01i for tbacesso_ip_usuario
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbacesso_ip_usuario,1);
end
^

SET TERM ; ^


UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO, DATAHORA)
                      VALUES (11850, 'ADM', 'ADM', '1   ', '', 'CONFIGURAÇÕES DE ACESSO AO SERVIDOR', '2022-01-27 13:39:31')
                    MATCHING (CONTROLE);

COMMIT WORK;

CREATE trigger ttbacesso_ip_b02i for tbacesso_ip
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBACESSO_IP',NEW.ID, HISTORICO);
    END
END;


CREATE trigger ttbacesso_ip_b01d for tbacesso_ip
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBACESSO_IP',OLD.ID, HISTORICO);
    END
END;


CREATE trigger ttbacesso_ip_b02d for tbacesso_ip
active before delete position 1
AS
BEGIN
   IF(OLD.ID > 0) THEN
   BEGIN
      DELETE FROM TBACESSO_IP_USUARIO WHERE ACESSO_IP_ID = OLD.ID;
   END
END;


CREATE trigger ttbacesso_ip_b01u for tbacesso_ip
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

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

    IF ( NEW.STATUS IS DISTINCT FROM OLD.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBS:'||COALESCE(OLD.STATUS, 0)||'->'||COALESCE(NEW.STATUS, 0);
    END

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

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

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

    IF ( NEW.BLOQUEAR_HORA IS DISTINCT FROM OLD.BLOQUEAR_HORA) THEN
    BEGIN
        HISTORICO = HISTORICO||' BLOQ.HR:'||COALESCE(OLD.BLOQUEAR_HORA, 0)||'->'||COALESCE(NEW.BLOQUEAR_HORA, 0);
    END

    IF ( NEW.BLOQUEAR IS DISTINCT FROM OLD.BLOQUEAR) THEN
    BEGIN
        HISTORICO = HISTORICO||' BLOQ.ACESSO:'||COALESCE(OLD.BLOQUEAR, 0)||'->'||COALESCE(NEW.BLOQUEAR, 0);
    END

    IF ( NEW.SEG IS DISTINCT FROM OLD.SEG) THEN
    BEGIN
        HISTORICO = HISTORICO||' SEG:'||COALESCE(OLD.SEG, 0)||'->'||COALESCE(NEW.SEG, 0);
    END

    IF ( NEW.TER IS DISTINCT FROM OLD.TER) THEN
    BEGIN
        HISTORICO = HISTORICO||' TER:'||COALESCE(OLD.TER, 0)||'->'||COALESCE(NEW.TER, 0);
    END

    IF ( NEW.QUA IS DISTINCT FROM OLD.QUA) THEN
    BEGIN
        HISTORICO = HISTORICO||' QUA:'||COALESCE(OLD.QUA, 0)||'->'||COALESCE(NEW.QUA, 0);
    END

    IF ( NEW.QUI IS DISTINCT FROM OLD.QUI) THEN
    BEGIN
        HISTORICO = HISTORICO||' QUI:'||COALESCE(OLD.QUI, 0)||'->'||COALESCE(NEW.QUI, 0);
    END

    IF ( NEW.SEX IS DISTINCT FROM OLD.SEX) THEN
    BEGIN
        HISTORICO = HISTORICO||' SEG:'||COALESCE(OLD.SEX, 0)||'->'||COALESCE(NEW.SEX, 0);
    END

    IF ( NEW.SAB IS DISTINCT FROM OLD.SAB) THEN
    BEGIN
        HISTORICO = HISTORICO||' SAB:'||COALESCE(OLD.SAB, 0)||'->'||COALESCE(NEW.SAB, 0);
    END

    IF ( NEW.DOM IS DISTINCT FROM OLD.DOM) THEN
    BEGIN
        HISTORICO = HISTORICO||' SEG:'||COALESCE(OLD.DOM, 0)||'->'||COALESCE(NEW.DOM, 0);
    END

    IF ( NEW.TODOS IS DISTINCT FROM OLD.TODOS) THEN
    BEGIN
        HISTORICO = HISTORICO||' TODOS:'||COALESCE(OLD.TODOS, 0)||'->'||COALESCE(NEW.TODOS, 0);
    END


    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' REG.ALTERADO: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBACESSO_IP',NEW.ID, HISTORICO);
    END
END;

SET TERM ^ ;

CREATE trigger ttbacesso_ip_usuario_b01d for tbacesso_ip_usuario
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(100);
BEGIN

    HISTORICO = '';
    USUARIO = COALESCE((SELECT FIRST 1 IIF( CHAR_LENGTH(COALESCE(U.NOME,'')) > 0, U.NOME, U.USUARIO) AS DESCRICAO FROM TBUSUARIO U WHERE U.CODIGO = OLD.USUARIO_ID), '');

    IF ( OLD.ACESSO_IP_ID > 0 AND OLD.ID > 0 ) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO INCLUIDO: '||FN_LPAD(OLD.USUARIO_ID, 4, '0')||' - '||USUARIO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBACESSO_IP',OLD.ACESSO_IP_ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbacesso_ip_usuario_b02i for tbacesso_ip_usuario
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(100);
BEGIN

    HISTORICO = '';
    USUARIO = COALESCE((SELECT FIRST 1 IIF( CHAR_LENGTH(COALESCE(U.NOME,'')) > 0, U.NOME, U.USUARIO) AS DESCRICAO FROM TBUSUARIO U WHERE U.CODIGO = NEW.USUARIO_ID), '');

    IF ( NEW.ACESSO_IP_ID > 0 AND NEW.ID > 0 ) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO INCLUIDO: '||FN_LPAD(NEW.USUARIO_ID, 4, '0')||' - '||USUARIO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBACESSO_IP',NEW.ACESSO_IP_ID, HISTORICO);
    END
END
^

SET TERM ; ^
