CREATE TABLE TBCUSTO_PADRAO (
    ID INTEGER NOT NULL,
    FAMILIA_ID INTEGER,
    DESCRICAO INTEGER);

ALTER TABLE TBCUSTO_PADRAO
ADD CONSTRAINT PK_TBCUSTO_PADRAO
PRIMARY KEY (ID);

COMMENT ON COLUMN TBCUSTO_PADRAO.FAMILIA_ID IS
'id da familia';

COMMENT ON COLUMN TBCUSTO_PADRAO.DESCRICAO IS
'descricao da regra';

COMMENT ON TABLE TBCUSTO_PADRAO IS 'tabela com as configuracoes padroes dos fatores que compoem o custo';

ALTER TABLE TBCUSTO_PADRAO
    ALTER DESCRICAO TYPE VARCHAR(150);

CREATE SEQUENCE GTBCUSTO_PADRAO;

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

CREATE TABLE TBCUSTO_PADRAO_ITEM (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100) DEFAULT '',
    PERCENTUAL NUMERIC(15,2) DEFAULT 0,
    FATOR INTEGER DEFAULT 0,
    AVOS INTEGER DEFAULT 0,
    USAR_FATOR INTEGER DEFAULT 0,
    EDITAVEL INTEGER DEFAULT 0,
    PADRAO_ID INTEGER DEFAULT 0);

ALTER TABLE TBCUSTO_PADRAO_ITEM
ADD CONSTRAINT PK_TBCUSTO_PADRAO_ITEM
PRIMARY KEY (ID);

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.DESCRICAO IS
'DESCRICAO DO ITEM';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.PERCENTUAL IS
'% SOBRE O CUSTO DO PRODUTO';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.FATOR IS
'O PERCENTUAL EQUIVALE A UMA FRACAO DE ((PERCENTUAL/AVOS) * FATOR)';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.AVOS IS
'O PERCENTUAL EQUIVALE A UMA FRACAO DE ((PERCENTUAL/AVOS) * FATOR)';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.USAR_FATOR IS
'SE USA O FATOR E OS AVOS NOS CALCULO PARA OBTER O PERCENTUAL
EXP: DESPESA FINANCEIRA 1%(PERCENTUAL) PARA 30(AVOS) DIAS MAIS O VALOR PADRAO(FATOR) HE 60, SE ABILITADO usar_fator CALCULA O PERCENTUAL ((1/30) * 60) = 2 OU ((60/30) * 1) = 2
1- SIM
2- NAO';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.EDITAVEL IS
'SE HE POCIVEL EDITAR ESTE ITEM NA TELA DE CALCULO DE CUSTO
1- SIM
2- NAO';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.PADRAO_ID IS
'TBCUSTO_PADRAO.ID';

CREATE SEQUENCE GTBCUSTO_PADRAO_ITEM;

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

COMMENT ON TABLE TBCUSTO_PADRAO_ITEM IS 'TABELA COM OS ITEM DE COMPOEM O CUSTO DO PRODUTO';

CREATE TABLE TBCUSTO_PADRAO_ITEM_CONTA (
    ID INTEGER NOT NULL,
    PADRAO_ITEM_ID INTEGER,
    CONTA VARCHAR(50));

ALTER TABLE TBCUSTO_PADRAO_ITEM_CONTA
ADD CONSTRAINT PK_TBCUSTO_PADRAO_ITEM_CONTA
PRIMARY KEY (ID);

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM_CONTA.PADRAO_ITEM_ID IS
'TBCUSTO_PADRAO_ITEM.ID';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM_CONTA.CONTA IS
'CONTA DO ITEM DE TBCUSTO_PADRAO_ITEM';

CREATE SEQUENCE GTBCUSTO_PADRAO_ITEM_CONTA;

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

CREATE TABLE TBCUSTO_PADRAO_ITEM_CONTA (
    ID INTEGER NOT NULL,
    PADRAO_ITEM_ID INTEGER,
    CONTA VARCHAR(50));

ALTER TABLE TBCUSTO_PADRAO_ITEM_CONTA
ADD CONSTRAINT PK_TBCUSTO_PADRAO_ITEM_CONTA
PRIMARY KEY (ID);

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM_CONTA.PADRAO_ITEM_ID IS
'TBCUSTO_PADRAO_ITEM.ID';

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM_CONTA.CONTA IS
'CONTA DO ITEM DE TBCUSTO_PADRAO_ITEM';

CREATE SEQUENCE GTBCUSTO_PADRAO_ITEM_CONTA;

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

COMMENT ON TABLE TBCUSTO_PADRAO_ITEM_CONTA IS 'TABELA COM AS CONTAS REFERENTES A UM ITEM DO PADRAO, AS CONTAS AQUI VINCULADOS SERAO REMOVIDAS DAS DESPESAS';

CREATE INDEX ITBCUSTO_PADRAO_ITEM_01
ON TBCUSTO_PADRAO_ITEM (PADRAO_ID);

CREATE INDEX ITBCUSTO_PADRAO_ITEM_CONTA_01
ON TBCUSTO_PADRAO_ITEM_CONTA (PADRAO_ITEM_ID);

ALTER TABLE TBCUSTO_PADRAO_ITEM
    ADD INCENTIVO INTEGER;

COMMENT ON COLUMN TBCUSTO_PADRAO_ITEM.INCENTIVO IS
'0- o insentivo nao influencia
1- o insentivo influencia';

ALTER TABLE TBCUSTO_PADRAO_ITEM ALTER COLUMN INCENTIVO
SET DEFAULT 0
;

CREATE TABLE TBCUSTO_INCENTIVO (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    PERCENTUAL INTEGER DEFAULT 0);

ALTER TABLE TBCUSTO_INCENTIVO
ADD CONSTRAINT PK_TBCUSTO_INCENTIVO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCUSTO_INCENTIVO;

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


COMMENT ON TABLE TBCUSTO_INCENTIVO IS 'tabela com os tipos de incentivo';

ALTER TABLE TBCUSTO_INCENTIVO
    ALTER PERCENTUAL TYPE NUMERIC(15,2);

ALTER TABLE TBCUSTO_PADRAO
    ADD INCENTIVO INTEGER,
    ADD PERC_INCENTIVO NUMERIC(15,2);

COMMENT ON COLUMN TBCUSTO_PADRAO.INSCENTIVO IS
'0-sem incentivo
1-com incentivo';

COMMENT ON COLUMN TBCUSTO_PADRAO.PERC_INCENTIVO IS
'incentivo padrao';

ALTER TABLE TBCUSTO_PADRAO_ITEM
    ADD PERC_IR NUMERIC(15,2);

ALTER TABLE TBCUSTO_INCENTIVO
    ADD PERC_IR INTEGER;

COMMENT ON COLUMN TBCUSTO_INCENTIVO.PERC_IR IS
'% do imposto de renda';

ALTER TABLE TBCUSTO_INCENTIVO
    ADD PERCENTUAL_IR NUMERIC(15,2);

CREATE trigger ttbcusto_incentivo_b02i for tbcusto_incentivo
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Incluido: ' ||new.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_INCENTIVO',
              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;

CREATE trigger ttbcusto_incentivo_b01u for tbcusto_incentivo
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   if(new.descricao <> old.descricao)then
   begin
       HISTORICO = 'Descrição: ' ||new.descricao||' -> '||old.descricao;
   end

   if(new.percentual <> old.percentual)then
   begin
       HISTORICO = 'Percentual: ' ||new.percentual||' -> '||old.percentual;
   end

   if(new.percentual_ir <> old.percentual_ir)then
   begin
       HISTORICO = 'Percentual IR: ' ||new.percentual_ir||' -> '||old.percentual_ir;
   end

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_INCENTIVO',
              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;

CREATE trigger ttbcusto_incentivo_b01d for tbcusto_incentivo
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Excluído: ' ||old.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_INCENTIVO',
              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;

CREATE OR ALTER trigger ttbcusto_incentivo_b01u for tbcusto_incentivo
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   if(new.descricao <> old.descricao)then
   begin
       HISTORICO = 'Descrição: ' ||old.descricao||' -> '||new.descricao;
   end

   if(new.percentual <> old.percentual)then
   begin
       HISTORICO = 'Percentual: ' ||old.percentual||' -> '||new.percentual;
   end

   if(new.percentual_ir <> old.percentual_ir)then
   begin
       HISTORICO = 'Percentual IR: ' ||old.percentual_ir||' -> '||new.percentual_ir;
   end

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_INCENTIVO',
              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;


CREATE trigger ttbcusto_padrao_b02i for tbcusto_padrao
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Incluido: ' ||new.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              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;

CREATE trigger ttbcusto_padrao_b01u for tbcusto_padrao
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   if(new.descricao <> old.descricao)then
   begin
      HISTORICO = 'Incluido: ' ||old.descricao ||' -> '|| new.descricao;
   end

   if(new.perc_incentivo <> old.perc_incentivo)then
   begin
      HISTORICO = 'Incentivo: ' ||old.perc_incentivo ||' -> '|| new.perc_incentivo;
   end

   if(new.incentivo <> old.incentivo)then
   begin
      HISTORICO = 'Abilita Incentivo: ' ||old.incentivo ||' -> '|| new.incentivo;
   end


   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              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;

CREATE trigger ttbcusto_padrao_b01d for tbcusto_padrao
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Excluido: ' ||old.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              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;

CREATE trigger ttbcusto_padrao_item_b02i for tbcusto_padrao_item
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Item Incluido: ' ||new.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              new.padrao_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;

CREATE trigger ttbcusto_padrao_item_b01u for tbcusto_padrao_item
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = '';

   if(new.descricao <> old.descricao)then
   begin
      HISTORICO = 'Item '||old.id||' Descrição: ' ||old.descricao ||' -> '|| new.descricao;
   end

   if(new.percentual <> old.percentual)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Percentual: ' ||old.percentual ||' -> '|| new.percentual;
   end

   if(new.fator <> old.fator)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Fator: ' ||old.fator ||' -> '|| new.fator;
   end

   if(new.avos <> old.avos)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Avos: ' ||old.avos ||' -> '|| new.avos;
   end

   if(new.usar_fator <> old.usar_fator)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Usar Fator: ' ||old.usar_fator ||' -> '|| new.usar_fator;
   end

   if(new.editavel <> old.editavel)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Editavel: ' ||old.editavel ||' -> '|| new.editavel;
   end

   if(new.incentivo <> old.incentivo)then
   begin
      HISTORICO = iif(historico <> '',  HISTORICO || ', ', '') || 'Item '||old.id||' Incentivo: ' ||old.incentivo ||' -> '|| new.incentivo;
   end


   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              new.padrao_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;


CREATE trigger ttbcusto_padrao_item_b01d for tbcusto_padrao_item
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Item Excluido: ' ||old.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              old.padrao_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;


CREATE trigger ttbcusto_padrao_item_conta_b02i for tbcusto_padrao_item_conta
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Item '||new.padrao_item_id||' Conta Incluido: ' ||new.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              (select first 1 k.padrao_id from tbcusto_padrao_item k where k.id = new.padrao_item_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;


CREATE trigger ttbcusto_padrao_item_conta_b01d for tbcusto_padrao_item_conta
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Item '||old.padrao_item_id||' Conta Excluido: ' ||old.id;

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              (select first 1 k.padrao_id from tbcusto_padrao_item k where k.id = old.padrao_item_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;

CREATE trigger ttbcusto_padrao_item_conta_b01u for tbcusto_padrao_item_conta
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = '';

   if(new.conta <> old.conta)then
   begin
      HISTORICO = 'Item:'||old.padrao_item_id||' Conta:'||new.id||' Conta Código: ' ||old.conta ||' -> '|| new.conta;
   end

   if (HISTORICO <> '') Then
   Begin
       INSERT INTO TBHISTORICO
              (
              TABELA,
              TABELA_ID,
              USUARIO, IP,
              HISTORICO
              )
       VALUES(
              'TBCUSTO_PADRAO',
              (select first 1 k.padrao_id from tbcusto_padrao_item k where k.id = new.padrao_item_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;


	
