ALTER TABLE TBWHATSAPP_TAREFAS
    ADD VERSAO_IMP INTEGER,
    ADD UPDATE_TEG VARCHAR(50) CHARACTER SET UTF8 COLLATE UTF8;

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbwhatsapp_tarefas_b02i for tbwhatsapp_tarefas
active before insert position 5
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbwhatsapp_tarefas_b01u for tbwhatsapp_tarefas
active before update position 5
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

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

    ----------------------------------------------------------------------------

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

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

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

    IF (OLD.REQUER_PERMICAO IS DISTINCT FROM NEW.REQUER_PERMICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' REQ.PERM:'||OLD.REQUER_PERMICAO||'->'||NEW.REQUER_PERMICAO;
    END

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO '||FN_LPAD(OLD.ID, 4, '0')||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBWHATSAPP_TAREFAS', OLD.ID, FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbwhatsapp_tarefas_b02d for tbwhatsapp_tarefas
active before delete position 5
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

CREATE GENERATOR GTBWHATSAPP_WEB_SERV;

CREATE TABLE TBWHATSAPP_WEB_SERV (
    ID           INTEGER NOT NULL,
    TAREFA_ID    INTEGER,
    CODIGO       INTEGER,
    SERVIDOR_ID  INTEGER,
    REMOVER      INTEGER,
    UPDATE_TEG   VARCHAR(50)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBWHATSAPP_WEB_SERV ADD CONSTRAINT PK_TBWHATSAPP_WEB_SERV PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBWHATSAPP_WEB_SERV_01 ON TBWHATSAPP_WEB_SERV (TAREFA_ID);
CREATE INDEX ITBWHATSAPP_WEB_SERV_02 ON TBWHATSAPP_WEB_SERV (CODIGO);
CREATE INDEX ITBWHATSAPP_WEB_SERV_03 ON TBWHATSAPP_WEB_SERV (SERVIDOR_ID);
CREATE INDEX ITBWHATSAPP_WEB_SERV_04 ON TBWHATSAPP_WEB_SERV (REMOVER);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBWHATSAPP_WEB_SERV_B01D */
CREATE OR ALTER TRIGGER TTBWHATSAPP_WEB_SERV_B01D FOR TBWHATSAPP_WEB_SERV
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE TAREFA VARCHAR(500);
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    TAREFA = COALESCE((SELECT FIRST 1 R.DESCRICAO FROM TBWHATSAPP_TAREFAS R WHERE R.ID = OLD.TAREFA_ID),'');

    IF (OLD.SERVIDOR_ID > 0 AND OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' TAREFA DO BOT EXCLUIDA: '||OLD.TAREFA_ID||' - '||TAREFA;
    END

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


/* Trigger: TTBCASO_WEB_SERV_B01I */
CREATE OR ALTER TRIGGER TTBWHATSAPP_WEB_SERV_B01I FOR TBWHATSAPP_WEB_SERV
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBWHATSAPP_WEB_SERV, 1);
END
^


/* Trigger: TTBCASO_WEB_SERV_B02I */
CREATE OR ALTER TRIGGER TTBWHATSAPP_WEB_SERV_B02I FOR TBWHATSAPP_WEB_SERV
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE TAREFA VARCHAR(500);
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    TAREFA = COALESCE((SELECT FIRST 1 R.DESCRICAO FROM TBWHATSAPP_TAREFAS R WHERE R.ID = NEW.TAREFA_ID),'');

    IF (NEW.SERVIDOR_ID > 0 AND NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' TAREFA DO BOT INCLUIDA: '||NEW.TAREFA_ID||' - '||TAREFA;
    END

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

SET TERM ; ^


/******************************************************************************/
/******************************************************************************/

INSERT INTO TBPARAMETRO (TABELA, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO)
    VALUES ('TBWHATSAPP_TAREFAS', 'SERVIDOR_PAINEL', 'servidor das tarefas do bot', 'https://kayros.gcweb.app.br', 'URL DO SERVIDOR DAS TAREFAS DO BOT', 'WEB11502');

INSERT INTO TBPARAMETRO (TABELA, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO)
    VALUES ('TBWHATSAPP_TAREFAS', 'INCREMENTAR_VERSAO', 'incrementar versão das tarefas do bot', '0', '1 - SIM, SE ESTE FOR UM SERVIDOR DE TAREFAS DO BOT
0 - NÃO, SE NÃO É UM SERVIDOR', 'WEB11502');

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

ALTER SEQUENCE GTBWHATSAPP_TAREFAS RESTART WITH 9990 INCREMENT BY 1;
ALTER SEQUENCE GTBWHATSAPP_WEB_SERV RESTART WITH 9990 INCREMENT BY 1;