CREATE TABLE TBCLIENTE_CONTATO (
    ID INTEGER NOT NULL,
	CLIENTE_ID INTEGER,
    NOME VARCHAR(50),
    SETOR VARCHAR(50),
    FUNCAO VARCHAR(50),
    DATA_ANIVERSARIO DATE,
    EMAIL VARCHAR(100),
    FONE_1 VARCHAR(25),
    FONE_2 VARCHAR(25),
    FONE_3 VARCHAR(25),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);


ALTER TABLE TBCLIENTE_CONTATO
ADD CONSTRAINT PK_TBCLIENTE_CONTATO
PRIMARY KEY (ID);


CREATE SEQUENCE GTBCLIENTE_CONTATO;


CREATE INDEX ITBCLIENTE_CONTATO_01
ON TBCLIENTE_CONTATO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBCLIENTE_CONTATO_B0I FOR TBCLIENTE_CONTATO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBCLIENTE_CONTATO, 1);
END;

COMMENT ON TRIGGER TTBCLIENTE_CONTATO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBCLIENTE_CONTATO_B0U FOR TBCLIENTE_CONTATO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBCLIENTE_CONTATO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBCLIENTE_CONTATO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBCLIENTE_CONTATO_B1U FOR TBCLIENTE_CONTATO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCLIENTE_CONTATO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBCLIENTE_CONTATO_B1U IS 'Registra historico de alteracoes.';
