CREATE TABLE TBREGISTRO_VISITA (
    ID INTEGER NOT NULL,
    TIPO_VISITA_ID INTEGER,
    REPRESENTANTE_ID INTEGER,
    CLIENTE_ID INTEGER,
    CLIENTE_PROSPECCAO_NOME VARCHAR(100),
    DATAHORA TIMESTAMP DEFAULT 'NOW',
    OBSERVACAO VARCHAR(500),
    LATITUDE VARCHAR(20),
    LONGITUDE VARCHAR(20),
    JUSTIF_CONTINGENCIA VARCHAR(500),
    LOCALIZACAO_VALIDA CHAR(1) DEFAULT '0',
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBREGISTRO_VISITA
ADD CONSTRAINT PK_TBREGISTRO_VISITA
PRIMARY KEY (ID);


COMMENT ON COLUMN TBREGISTRO_VISITA.LOCALIZACAO_VALIDA IS
'Define se a localizacao da visita esta de acordo com a localizacao cadastrada para o cliente.';


CREATE SEQUENCE GTBREGISTRO_VISITA;


CREATE INDEX ITBREGISTRO_VISITA_01
ON TBREGISTRO_VISITA (STATUSEXCLUSAO);



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

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


CREATE TRIGGER TTBREGISTRO_VISITA_B0U FOR TBREGISTRO_VISITA
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 (
            'TBREGISTRO_VISITA',
            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 TTBREGISTRO_VISITA_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


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

    HISTORICO = '';

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

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

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

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBREGISTRO_VISITA', 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 TTBREGISTRO_VISITA_B1U IS 'Registra historico de alteracoes.';