

CREATE SEQUENCE GTBAPI_USUARIO;

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

CREATE TABLE TBAPI (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    LOCALIZACAO_ID INTEGER,
    ESTABELECIMENTO_ID INTEGER,
    STATUS INTEGER);

ALTER TABLE TBAPI
ADD CONSTRAINT PK_TBAPI
PRIMARY KEY (ID);

CREATE SEQUENCE GTBAPI;

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

CREATE TABLE TBAPI_FAMILIA (
    ID INTEGER NOT NULL,
    API_ID INTEGER,
    FAMILIA_ID INTEGER);

ALTER TABLE TBAPI_FAMILIA
ADD CONSTRAINT PK_TBAPI_FAMILIA
PRIMARY KEY (ID);

CREATE SEQUENCE GTBAPI_FAMILIA;

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

CREATE GENERATOR GTBINTEGRACAO_API;

CREATE TABLE TBINTEGRACAO_API (
    ID               INTEGER,
    TABELA           VARCHAR(100) DEFAULT '',
    TABELA_ID        INTEGER DEFAULT 0,
    VERSSAO          INTEGER DEFAULT 0,
    STATUS_REGISTRO  VARCHAR(10)
);

CREATE INDEX ITBINTEGRACAO_API_01 ON TBINTEGRACAO_API (ID);
CREATE INDEX ITBINTEGRACAO_API_02 ON TBINTEGRACAO_API (TABELA, TABELA_ID, VERSSAO);
CREATE INDEX ITBINTEGRACAO_API_03 ON TBINTEGRACAO_API (TABELA, TABELA_ID);
CREATE INDEX ITBINTEGRACAO_API_04 ON TBINTEGRACAO_API (TABELA);
CREATE INDEX ITBINTEGRACAO_API_05 ON TBINTEGRACAO_API (TABELA_ID);
CREATE INDEX ITBINTEGRACAO_API_06 ON TBINTEGRACAO_API (VERSSAO);

CREATE OR ALTER TRIGGER TTBINTEGRACAO_API_B01I FOR TBINTEGRACAO_API
ACTIVE BEFORE INSERT POSITION 0
as
begin

  new.VERSSAO = 1;  

  if (new.id is null) then
    new.id = gen_id(gTBINTEGRACAO_API,1);
end;

CREATE OR ALTER TRIGGER TTBINTEGRACAO_API_B01U FOR TBINTEGRACAO_API
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  new.verssao = new.verssao + 1;
end;

COMMENT ON COLUMN TBINTEGRACAO_API.STATUS_REGISTRO IS 
'I - Incluido
A - Alterado
E - Excluido';

CREATE GENERATOR GTBINTEGRACAO_API_DETALHE;

CREATE TABLE TBINTEGRACAO_API_DETALHE (
    ID              BIGINT NOT NULL,
    INTEGRACAO_ID   INTEGER DEFAULT 0,
    USUARIO_ID      INTEGER DEFAULT 0,
    API_ID  		INTEGER DEFAULT 0,
    VERSSAO         INTEGER DEFAULT 0,
    REG             BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

ALTER TABLE TBINTEGRACAO_API_DETALHE ADD CONSTRAINT PK_TBINTEGRACAO_API_DETALHE PRIMARY KEY (ID);

CREATE INDEX ITBINTEGRACAO_API_DETALH_01 ON TBINTEGRACAO_API_DETALHE (INTEGRACAO_ID, USUARIO_ID, API_ID);
CREATE INDEX ITBINTEGRACAO_API_DETALH_02 ON TBINTEGRACAO_API_DETALHE (INTEGRACAO_ID);
CREATE INDEX ITBINTEGRACAO_API_DETALH_03 ON TBINTEGRACAO_API_DETALHE (USUARIO_ID);
CREATE INDEX ITBINTEGRACAO_API_DETALH_04 ON TBINTEGRACAO_API_DETALHE (API_ID);


CREATE OR ALTER TRIGGER TTBINTEGRACAO_API_DET_B01I FOR TBINTEGRACAO_API_DETALHE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gTBINTEGRACAO_API_detalhe,1);
end;

CREATE OR ALTER procedure SPI_INTEGRACAO_API (
    TABELA varchar(50),
    TABELA_ID integer,
    STATUS varchar(10))
as
begin
  UPDATE OR INSERT INTO tbintegracao_api (STATUS_REGISTRO, TABELA, TABELA_ID)
    VALUES (:STATUS, :TABELA, :TABELA_ID)
  MATCHING (TABELA, TABELA_ID);
end;

end;

CREATE INDEX ITBAPI_FAMILIA_01 ON TBAPI_FAMILIA (API_ID);
CREATE INDEX ITBAPI_01 ON TBAPI (STATUS);
end;
