/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBUSUARIO_TIPO;

CREATE TABLE TBUSUARIO_TIPO (
    ID                  INTEGER NOT NULL,
    DESCRICAO           VARCHAR(50),
    STATUS              CHAR(1),
    NIVEL_CHAMADO       INTEGER,
    PRIORIDADE_CHAMADO  INTEGER
);




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

ALTER TABLE TBUSUARIO_TIPO ADD CONSTRAINT PK_TBUSUARIO_TIPO PRIMARY KEY (ID);


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



SET TERM ^ ;



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



/* Trigger: TTBUSUARIO_TIPO_B01D */
CREATE OR ALTER TRIGGER TTBUSUARIO_TIPO_B01D FOR TBUSUARIO_TIPO
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE USUARIO INTEGER;
BEGIN
    
    USUARIO = COALESCE((SELECT FIRST 1 A.CODIGO FROM TBUSUARIO A WHERE A.TIPO_ID = OLD.ID), 0);

    IF (USUARIO > 0) THEN
    BEGIN
        EXCEPTION "..." 'TIPO DE USUARIO EM USO. EXCLUSÃO NÃO AUTORIZADA!';
    END
END
^


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

SET TERM ; ^

/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBUSUARIO_TIPO.NIVEL_CHAMADO IS 
'0 - ADMINISTRADOR;
1 - NIVEL 1;
2 - NIVEL 2;
3 - ...;';

COMMENT ON COLUMN TBUSUARIO_TIPO.PRIORIDADE_CHAMADO IS 
'1 - BAIXA;
2 - MÉDIA;
3 - ALTA;';

--------------------------------------------------------------------------------
-- REGISTROS

UPDATE OR INSERT INTO TBUSUARIO_TIPO (ID, DESCRICAO, STATUS, NIVEL_CHAMADO, PRIORIDADE_CHAMADO) VALUES (1, 'ADMINISTRATIVO', '1', 1, 2) MATCHING (ID);
UPDATE OR INSERT INTO TBUSUARIO_TIPO (ID, DESCRICAO, STATUS, NIVEL_CHAMADO, PRIORIDADE_CHAMADO) VALUES (2, 'PRODUTIVO', '1', 1, 3) MATCHING (ID);
UPDATE OR INSERT INTO TBUSUARIO_TIPO (ID, DESCRICAO, STATUS, NIVEL_CHAMADO, PRIORIDADE_CHAMADO) VALUES (3, 'APOIO', '1', 1, 3) MATCHING (ID);
UPDATE OR INSERT INTO TBUSUARIO_TIPO (ID, DESCRICAO, STATUS, NIVEL_CHAMADO, PRIORIDADE_CHAMADO) VALUES (4, 'TI', '1', 0, 2) MATCHING (ID);

/******************************************************************************/
/****					  Modificações da TBUSUARIO		   				   ****/
/******************************************************************************/

ALTER TABLE TBUSUARIO
    ADD TIPO_ID INTEGER;

COMMENT ON COLUMN TBUSUARIO.TIPO_ID IS
'Tipo de Usuário de Acordo com a Tabela TBUSUARIO_TIPO';

SET TERM ^ ;

CREATE OR ALTER trigger ttbusuario_b01u for tbusuario
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(1000);
BEGIN

    HISTORICO = '';

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

    IF(OLD.NOME <> NEW.NOME) THEN
    BEGIN
       HISTORICO = HISTORICO||', Nome Alterado';
    END

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

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

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

    IF(OLD.SKYPE <> NEW.SKYPE) THEN
    BEGIN
       HISTORICO = HISTORICO||', Skype Alterado';
    END

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

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

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

    IF(OLD.NIVEL_OC <> NEW.NIVEL_OC) THEN
    BEGIN
       HISTORICO = HISTORICO||', Nivel OC: '||OLD.NIVEL_OC||' -> '||NEW.NIVEL_OC;
    END

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

    IF(OLD.EMAIL_SMTP <> NEW.EMAIL_SMTP) THEN
    BEGIN
       HISTORICO = HISTORICO||', Email SMTP: '||OLD.EMAIL_SMTP||' -> '||NEW.EMAIL_SMTP;
    END

    IF(OLD.EMAIL_USERNAME <> NEW.EMAIL_USERNAME) THEN
    BEGIN
       HISTORICO = HISTORICO||', User Alterado';
    END

    IF(OLD.EMAIL_PASS <> NEW.EMAIL_PASS) THEN
    BEGIN
       HISTORICO = HISTORICO||', Password Alterado';
    END

    IF(OLD.PORTA_SMTP <> NEW.PORTA_SMTP) THEN
    BEGIN
       HISTORICO = HISTORICO||', Porta SMTP: '||OLD.PORTA_SMTP||' -> '||NEW.PORTA_SMTP;
    END

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

    IF(OLD.SSL_TLS <> NEW.SSL_TLS) THEN
    BEGIN
       HISTORICO = HISTORICO||', Con.Segura: '||OLD.SSL_TLS||' -> '||NEW.SSL_TLS;
    END

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

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

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

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

    IF(OLD.TIPO_ID <> NEW.TIPO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||', Tipo Usuario: '||OLD.TIPO_ID||' -> '||NEW.TIPO_ID;
    END

    IF(HISTORICO <> '') THEN
    BEGIN
       historico = FN_ELIPSES(historico,500);
       EXECUTE PROCEDURE SPI_HISTORICO('TBUSUARIO',NEW.CODIGO, HISTORICO);
    END
END
^

SET TERM ; ^

UPDATE TBUSUARIO A SET A.TIPO_ID = 1;