CREATE TABLE TBINTEGRACAO_QUIOSQUE (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    CNPJ VARCHAR(30),
    STATUS INTEGER);

ALTER TABLE TBINTEGRACAO_QUIOSQUE
ADD CONSTRAINT PK_TBINTEGRACAO_QUIOSQUE
PRIMARY KEY (ID);

COMMENT ON COLUMN TBINTEGRACAO_QUIOSQUE.STATUS IS
'1 - ativo
0 - inativo';

CREATE SEQUENCE GTBINTEGRACAO_QUIOSQUE;

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

COMMENT ON TABLE TBINTEGRACAO_QUIOSQUE IS 'tabela de quiosques que devem receber e ter seus dados importados no sistema';

CREATE TABLE TBADQUIRENTE (
    ID INTEGER NOT NULL,
    ID_POS INTEGER,
    DESCRICAO VARCHAR(50),
    CNPJ VARCHAR(20),
    MERCHANTID VARCHAR(50),
    CHAVE_REQUISICAO VARCHAR(50),
    STATUS INTEGER);

ALTER TABLE TBADQUIRENTE
ADD CONSTRAINT PK_TBADQUIRENTE
PRIMARY KEY (ID);

COMMENT ON COLUMN TBADQUIRENTE.STATUS IS
'1 - ativo
0 - inativo';

CREATE SEQUENCE GTBADQUIRENTE;

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


CREATE TABLE TBPOS (
    ID INTEGER NOT NULL,
    ID_EMPRESA INTEGER,
    DESCRICAO VARCHAR(50),
    NUMERO_SERIE VARCHAR(50),
    STATUS INTEGER);

ALTER TABLE TBPOS
ADD CONSTRAINT PK_TBPOS
PRIMARY KEY (ID);

COMMENT ON COLUMN TBPOS.STATUS IS
'1 - ativo
0 - inativo';

CREATE SEQUENCE GTBPOS;

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


CREATE TABLE TBFORMA_PAGAMENTO_SAT (
    ID INTEGER NOT NULL,
    STATUS INTEGER,
    TIPO INTEGER,
    DESCRICAO VARCHAR(50),
    ID_NFE INTEGER,
    ID_SAT INTEGER,
    TEF INTEGER,
    POS INTEGER,
    PAGAMENTO_FORMA_ID INTEGER);

ALTER TABLE TBFORMA_PAGAMENTO_SAT
ADD CONSTRAINT PK_TBFORMA_PAGAMENTO_SAT
PRIMARY KEY (ID);

COMMENT ON COLUMN TBFORMA_PAGAMENTO_SAT.STATUS IS
'1 - ativo
2 - inativo';

COMMENT ON COLUMN TBFORMA_PAGAMENTO_SAT.TIPO IS
'1 - Dinheiro
2 - Cheque
3 - Cartao
4 - Vale
5 - Crediario
9 - Outros';

COMMENT ON COLUMN TBFORMA_PAGAMENTO_SAT.TEF IS
'1 - Sim
2 - Nao';

COMMENT ON COLUMN TBFORMA_PAGAMENTO_SAT.POS IS
'1 - Sim
2 - Nao';

CREATE SEQUENCE GTBFORMA_PAGAMENTO_SAT;

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

--novas tabelas

CREATE TABLE TBQUIOSQUE (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    STATUS INTEGER);

ALTER TABLE TBQUIOSQUE
ADD CONSTRAINT PK_TBQUIOSQUE
PRIMARY KEY (ID);

COMMENT ON COLUMN TBQUIOSQUE.STATUS IS
'1 - ativo
0 - inativo';

CREATE SEQUENCE GTBQUIOSQUE;

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

CREATE TABLE TBQUIOSQUE_CAIXA (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    STATUS INTEGER);

ALTER TABLE TBQUIOSQUE_CAIXA
ADD CONSTRAINT PK_TBQUIOSQUE_CAIXA
PRIMARY KEY (ID);

COMMENT ON COLUMN TBQUIOSQUE_CAIXA.STATUS IS
'1 - ativo
0 - inativo';

CREATE SEQUENCE GTBQUIOSQUE_CAIXA;

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

CREATE TABLE TBCRT (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    STATUS INTEGER);

ALTER TABLE TBCRT
ADD CONSTRAINT PK_TBCRT
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCRT;

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

CREATE TABLE TBINTEGRACAO (
    ID BIGINT NOT NULL,
    TIPO INTEGER,
    TABELA VARCHAR(50),
    TABELA_ID INTEGER,
    DATA_INTEGRADO TIMESTAMP,
    DATA_TENTATIVA TIMESTAMP,
    DATA_REGISTRO TIMESTAMP,
    LOG_ERRO BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    STATUS INTEGER);

ALTER TABLE TBINTEGRACAO
ADD CONSTRAINT PK_TBINTEGRACAO
PRIMARY KEY (ID);

COMMENT ON COLUMN TBINTEGRACAO.TIPO IS
'1 - quiosques
2 - ...
3 - ...';

COMMENT ON COLUMN TBINTEGRACAO.STATUS IS
'0 - pendente
1 - sincronisado
2 - sincronisado manualmente';

CREATE SEQUENCE GTBINTEGRACAO;

create or alter procedure spi_integracao (
    tabela varchar(50),
    tabela_id integer,
    tipo integer)
as
begin
  INSERT INTO TBINTEGRACAO (TIPO, TABELA, TABELA_ID, DATA_REGISTRO, STATUS)
                  VALUES (:TIPO, :TABELA, :TABELA_ID, current_timestamp, 1);
end;

COMMENT ON PARAMETER SPI_INTEGRACAO.TIPO IS
'1-quiosque, 2-..., 3-...';

CREATE INDEX ITBINTEGRACAO_03
ON TBINTEGRACAO (DATA_REGISTRO);

CREATE INDEX ITBINTEGRACAO_02
ON TBINTEGRACAO (VERSSAO);

CREATE INDEX ITBINTEGRACAO_01
ON TBINTEGRACAO (TIPO,TABELA,TABELA_ID);

CREATE INDEX ITBINTEGRACAO_ITENS_03
ON TBINTEGRACAO_ITENS (STATUS);

CREATE INDEX ITBINTEGRACAO_ITENS_02
ON TBINTEGRACAO_ITENS (INTEGRACAO_ID);

CREATE INDEX ITBINTEGRACAO_ITENS_01
ON TBINTEGRACAO_ITENS (TIPO);

CREATE OR ALTER VIEW VWINTEGRACAO_PENDENTE (
    ID,
    TABELA,
    TABELA_ID,
    VERSSAO,
    ID_ITEM,
    VERSSAO_ITEM,
    LOG_ERRO
)
as
select
    ID,
    TABELA,
    TABELA_ID,
    VERSSAO,
    ID_ITEM,
    VERSSAO_ITEM,
    LOG_ERRO
from(
    select
        i.id,
        i.tabela,
        i.tabela_id,
        i.verssao,
        coalesce(t.id_mestre,0) as id_item,
        coalesce(t.verssao,0) as verssao_item,
        t.log_erro
    from tbintegracao i
    left join tbintegracao_itens t on t.integracao_id = i.id
) where verssao_item < verssao
;

CREATE OR ALTER trigger ttbquiosque_caixa_b02i for tbquiosque_caixa
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    execute procedure SPI_INTEGRACAO('TBQUIOSQUE_CAIXA',NEW.ID,1);

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Incluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBQUIOSQUE_CAIXA',NEW.ID, HISTORICO);
    END
end;

CREATE OR ALTER trigger ttbquiosque_caixa_b01u for tbquiosque_caixa
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBQUIOSQUE_CAIXA',NEW.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBQUIOSQUE_CAIXA',NEW.ID,1);
end;

CREATE OR ALTER trigger ttbquiosque_caixa_b01d for tbquiosque_caixa
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBQUIOSQUE_CAIXA',old.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBQUIOSQUE_CAIXA',old.ID,1);
end;

CREATE OR ALTER trigger ttbpos_b02i for tbpos
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    execute procedure SPI_INTEGRACAO('TBPOS',new.ID,1);

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Incluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPOS',NEW.ID, HISTORICO);
    END
end;

CREATE OR ALTER trigger ttbpos_b01u for tbpos
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID_EMPRESA <> NEW.ID_EMPRESA) THEN
    BEGIN
        HISTORICO = HISTORICO||'ID da Empresa:'|| OLD.ID_EMPRESA||'->'||NEW.ID_EMPRESA;
    END

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

    IF ( OLD.NUMERO_SERIE <> NEW.NUMERO_SERIE) THEN
    BEGIN
        HISTORICO = HISTORICO||',Numero de Serie:'|| OLD.NUMERO_SERIE||'->'||NEW.NUMERO_SERIE;
    END

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

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPOS',NEW.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBPOS',new.ID,1);
end;

CREATE OR ALTER trigger ttbpos_b01d for tbpos
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPOS',old.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBPOS',old.ID,1);
end;

CREATE OR ALTER trigger ttbadquirente_b02i for tbadquirente
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    execute procedure SPI_INTEGRACAO('TBADQUIRENTE',new.ID,1);

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Incluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBADQUIRENTE',NEW.ID, HISTORICO);
    END
end;

CREATE OR ALTER trigger ttbadquirente_b01u for tbadquirente
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID_POS <> NEW.ID_POS) THEN
    BEGIN
        HISTORICO = HISTORICO||'ID da POS:'|| OLD.ID_POS||'->'||NEW.ID_POS;
    END

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

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

    IF ( OLD.MERCHANTID <> NEW.MERCHANTID) THEN
    BEGIN
        HISTORICO = HISTORICO||',Numero de Merchant:'|| OLD.MERCHANTID||'->'||NEW.MERCHANTID;
    END

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBADQUIRENTE',NEW.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBADQUIRENTE',new.ID,1);
end;

CREATE OR ALTER trigger ttbadquirente_b01d for tbadquirente
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBADQUIRENTE',old.ID, HISTORICO);
    END

    execute procedure SPI_INTEGRACAO('TBADQUIRENTE',old.ID,1);
end;

CREATE TABLE TBINTEGRACAO_TABELA_ORDEM (
    ID INTEGER NOT NULL,
    TABELA VARCHAR(50),
    TIPO INTEGER,
    ORDEM INTEGER);

ALTER TABLE TBINTEGRACAO_TABELA_ORDEM
ADD CONSTRAINT PK_TBINTEGRACAO_TABELA_ORDEM
PRIMARY KEY (ID);

CREATE SEQUENCE GTBINTEGRACAO_TABELA_ORDEM;

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


CREATE OR ALTER VIEW VWINTEGRACAO_PENDENTE(
    ID,
    TABELA,
    TABELA_ID,
    VERSSAO,
    ID_ITEM,
    VERSSAO_ITEM,
    LOG_ERRO,
    ORDEM)
AS
select
    ID,
    TABELA,
    TABELA_ID,
    VERSSAO,
    ID_ITEM,
    VERSSAO_ITEM,
    LOG_ERRO,
    ORDEM
from(
    select
        i.id,
        i.tabela,
        i.tabela_id,
        i.verssao,
        coalesce(t.id_mestre,0) as id_item,
        coalesce(t.verssao,0) as verssao_item,
        t.log_erro,
        coalesce(o.ordem, 9999) as ORDEM
    from tbintegracao i
    left join tbintegracao_itens t on t.integracao_id = i.id
    left join TBINTEGRACAO_TABELA_ORDEM o on o.tabela = i.tabela and o.tipo = i.tipo
) where verssao_item < verssao
;


CREATE OR ALTER trigger ttbempresa_b03i for tbempresa
active before insert position 0
AS
declare variable tipo_cliente integer;
begin
    --new.endereco2 = new.endereco;
   --- new.numero2 = new.numero;
    
    if(new.ecommerce = 1 or new.replicar_cliente = 1 )then
    begin

        if(new.ecommerce = 1)then
        begin
           new.conta_contabil = FN_PARAMETRO('SISTEMA.CONTA_ECOMMERCE');
        end

        INSERT INTO TBCLIENTE (
             RAZAOSOCIAL,
             CODIGO,
             REPRESENTANTE_CODIGO,
             TRANSPORTADORA_CODIGO,
             PAGAMENTO_CONDICAO,
             PAGAMENTO_FORMA,
             STATUS,
             UF,
             BLOQUEIA_PEDIDO,
             BLOQUEIA_NOTAFISCAL,
             REPRESENTANTE_COMISSAO,
             GRADE_SECUNDARIA,
             LIMITE_CREDITO,
             SUFRAMA,
             GRUPO,
             BOQUEIA_PAGAMENTO_CONDICAO,
             TIPO_CODIGO,
             VENDEDOR_CODIGO,
             MATRIZ_ID,
             VENDEDOR_COMISSAO,
             ATENDE_GRADE_COMPLETA,
             SUFRAMA_VENCIMENTO,
             LOJA,
             BLOQUEIO_DIAS_ATRASO,
             BLOQUEIO_DIAS_SEMMOV,
             TAG,
             PRIORIDADE,
             INATIVA_DIAS_SEMMOV,
             CONFIRMACAO_AUTOMATICA,
             PAFECF,
             FRETE,
             SUBSTITUIDO_TRIBUTARIO,
             DATA_BLOQUEIO,
             BLOQUEIO_MOTIVO,
             NOTIFICAR_DIAS_ATRASO,
             NOTIFICAR_ATRASO,
             ETIQUETA,
             EMPRESA_VINCULO,
             BLOQUEIO_DIAS_ATRASO_NF,
             ECOMMERCE
       ) VALUES (
             new.razaosocial,
             new.codigo,
             iif(new.representante_id = 0, coalesce((SELECT FIRST 1 P.valor_ext  FROM tbcontrole_n P WHERE P.id = 272),0), new.representante_id),
             0,
             0,
             0,
             '1',
             new.uf,
             '',
             '',
             0,
             '0',
             0,
             '',
             '0',
             '',
             1,
             new.vendedor_id,
             0,
             0,
             '',
             NULL,
             '',
             0,
             0,
             '',
             10,
             0,
             '',
             '',
             0,
             '0',
             NULL,
             '',
             0,
             0,
             substring(new.razaosocial from 1 for 6),
             new.codigo,
             0,
             iif(new.replicar_cliente = 1, 0, 1)
        );

        tipo_cliente = coalesce((select first 1 E.valor_ext FROM tbcontrole_n E WHERE E.id = 281),59);

        INSERT INTO TBEMPRESA_VINCULO_TIPO (EMPRESA_VINCULO, CLIENTE_TIPO_ID, STATUS) VALUES (NEW.codigo, :tipo_cliente, 1);
    end
end;


