CREATE GENERATOR GTBWHATSAPP_TAREFA_PERM;

CREATE TABLE TBWHATSAPP_TAREFA_PERM (
    ID                    INTEGER NOT NULL,
    WHATSAPP_ID           INTEGER,
    WHATSAPP_COMANDOS_ID  INTEGER,
    DATAHORA              TIMESTAMP DEFAULT 'NOW',
    STATUS                CHAR(1) DEFAULT '1'
);

ALTER TABLE TBWHATSAPP_TAREFA_PERM ADD PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER TBWHATSAPP_TAREFA_PERM_B01D FOR TBWHATSAPP_TAREFA_PERM
ACTIVE BEFORE DELETE POSITION 7
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_PERMISSAO VARCHAR(500);
BEGIN

    HISTORICO = '';

    SELECT TRIM(T.DESCRICAO) FROM tbwhatsapp_tarefas T WHERE T.id = OLD.whatsapp_comandos_id INTO DESC_PERMISSAO;
    HISTORICO = HISTORICO||'Permissao de '||DESC_PERMISSAO||' excluida';

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


CREATE OR ALTER TRIGGER TBWHATSAPP_TAREFA_PERM_B01U FOR TBWHATSAPP_TAREFA_PERM
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_PERMISSAO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        SELECT TRIM(T.DESCRICAO) FROM tbwhatsapp_tarefas T WHERE T.id = NEW.whatsapp_comandos_id INTO DESC_PERMISSAO;

        IF(OLD.STATUS = '0' AND NEW.STATUS = '1') THEN  HISTORICO = HISTORICO||'Permissao de '||DESC_PERMISSAO||' habilitada';

        IF(OLD.STATUS = '1' AND NEW.STATUS = '0') THEN HISTORICO = HISTORICO||'Permissao de '||DESC_PERMISSAO||' desabilitada';

    END
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBWHATSAPP',NEW.whatsapp_id, HISTORICO);
    END
end;


CREATE OR ALTER TRIGGER TBWHATSAPP_TAREFA_PERM_B02I FOR TBWHATSAPP_TAREFA_PERM
ACTIVE BEFORE INSERT POSITION 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_PERMISSAO VARCHAR(500);
BEGIN

    HISTORICO = '';
    SELECT TRIM(T.DESCRICAO) FROM tbwhatsapp_tarefas T WHERE T.id = NEW.whatsapp_comandos_id INTO DESC_PERMISSAO;
    HISTORICO = HISTORICO||'Permissao de '||DESC_PERMISSAO||' adicionada';

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

ALTER TABLE TBWHATSAPP_TAREFAS
    ADD REQUER_PERMICAO INTEGER;

ALTER TABLE TBWHATSAPP_TAREFAS
    ADD ORDEM INTEGER;



CREATE OR ALTER TRIGGER TTBWHATSAPP_TAREFA_PERM_B0I FOR TBWHATSAPP_TAREFA_PERM
ACTIVE BEFORE INSERT POSITION 0
as
begin
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBWHATSAPP_TAREFA_PERM,1);
end;

CREATE INDEX TTBWHATSAPP_TAREFAS_03
ON TBWHATSAPP_TAREFAS (REQUER_PERMICAO);


CREATE OR ALTER VIEW VWWHATSAPP_TAREFA(
    ID,
    DESC_ID,
    DESCRICAO,
    PARAMETRO,
    NIVEL,
    ordem,
    MENU,
    SUBMENU)
AS
SELECT
                    T.ID,
                    FN_LPAD(T.ID, 4, '0') AS DESC_ID,
                    T.DESCRICAO,
                    '' PARAMETRO,
                    t.nivel,
                    t.ordem,

                    coalesce(
                        (select
                            coalesce(
                                (select
                                    coalesce(
                                        (select
                                            coalesce(
                                                (select
                                                    coalesce(
                                                        (select f5.grupo from TBWHATSAPP_TAREFAS f5 where f5.id = f4.grupo)
                    ,'')||'.'||f4.grupo from TBWHATSAPP_TAREFAS f4 where f4.id = f3.grupo)
                    ,'')||'.'||f3.grupo from TBWHATSAPP_TAREFAS f3 where f3.id = f2.grupo)
                    ,'')||'.'||f2.grupo from TBWHATSAPP_TAREFAS f2 where f2.id = f.grupo)
                    ,'')||'.'||f.grupo from TBWHATSAPP_TAREFAS f where f.id = t.grupo)
                    ,'')||'.'||t.grupo||'.'||t.id as MENU,


                    coalesce(
                        (select
                            f.grupo from TBWHATSAPP_TAREFAS f where f.id = t.grupo)
                    ,'')||'.'||t.grupo as SUBMENU


                FROM TBWHATSAPP_TAREFAS T
                where t.requer_permicao = 1

                order by t.grupo, t.ordem
;

CREATE trigger ttbpedido_item_processado_b13u for tbpedido_item_processado
active before update position 0
AS
begin

    if (New.quantidade_saldo < 0) Then
    Begin
         Exception "..." 'SALDO DA PECA NAO PODE FICAR NEGATIVO! SALDO DISPONIVEL: '||Old.quantidade_saldo||' PRODUTO ID:'||Old.produto_codigo||' PECA:'||Old.controle||' BAIXAR:'||(Old.quantidade_saldo + (New.quantidade_saldo*-1))||'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
    End

end;

CREATE INDEX ITBWHATSAPP_RESPOSTA_01
ON TBWHATSAPP_RESPOSTA (STATUS);

CREATE INDEX ITBWHATSAPP_TAREFAS_02
ON TBWHATSAPP_TAREFAS (STATUS);

CREATE INDEX ITBWHATSAPP_TAREFAS_01
ON TBWHATSAPP_TAREFAS (GRUPO);

ALTER TABLE TBWHATSAPP
    ADD VALOR VARCHAR(50);

CREATE INDEX ITBWHATSAPP_TAREFA_PERM_03
ON TBWHATSAPP_TAREFA_PERM (WHATSAPP_COMANDOS_ID);

CREATE INDEX ITBWHATSAPP_TAREFA_PERM_02
ON TBWHATSAPP_TAREFA_PERM (STATUS);

CREATE INDEX ITBWHATSAPP_TAREFA_PERM_01
ON TBWHATSAPP_TAREFA_PERM (WHATSAPP_ID);

