CREATE TABLE TBAPI_CNP (
    ID INTEGER NOT NULL,
	CNP_ID VARCHAR(100),
	PREFIXO VARCHAR(10),
    USUARIO VARCHAR(200),
    SENHA VARCHAR(200),
    CLIENT_ID VARCHAR(200),
    CLIENT_SECRET VARCHAR(200),
    URL_HOST VARCHAR(100),
    ACCESS_TOKEN VARCHAR(100),
    REFRESH_TOKEN VARCHAR(100),
    EXPIRE_TOKEN TIMESTAMP,
    STATUS INTEGER);

ALTER TABLE TBAPI_CNP
ADD CONSTRAINT PK_TBAPI_CNP
PRIMARY KEY (ID);

COMMENT ON COLUMN TBAPI_CNP.USUARIO IS
'DADO DE LOGIN PARA ENTRAR NO GS1';

COMMENT ON COLUMN TBAPI_CNP.SENHA IS
'DADO DE LOGIN PARA ENTRAR NO GS1';

COMMENT ON COLUMN TBAPI_CNP.CLIENT_ID IS
'DADO GERADO PELO GS1 AO CRIAR UM APP NA PLATAFORMA';

COMMENT ON COLUMN TBAPI_CNP.CLIENT_SECRET IS
'DADO GERADO PELO GS1 AO CRIAR UM APP NA PLATAFORMA';

CREATE SEQUENCE GTBAPI_CNP;

create trigger ttbapi_cnp_b01i for tbapi_cnp
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbapi_cnp,1);
end;

CREATE INDEX ITBAPI_CNP_01
ON TBAPI_CNP (STATUS);

CREATE INDEX ITBAPI_CNP_02
ON TBAPI_CNP (USUARIO);


CREATE OR ALTER TRIGGER TTBAPI_CNP_B09U FOR TBAPI_CNP
ACTIVE BEFORE UPDATE POSITION 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(8000);

BEGIN
  HISTORICO = '';

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

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

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = FN_ELIPSES(HISTORICO, 500);
        EXECUTE PROCEDURE SPI_HISTORICO('TBAPI_CNP', NEW.ID, HISTORICO);
    END

END;