CREATE TABLE TBPROGRAMA_PONTOS (
    ID INTEGER NOT NULL,
    STATUS INTEGER DEFAULT 1,
    DESCRICAO VARCHAR(100),
    DATA_ZERAR TIMESTAMP);

ALTER TABLE TBPROGRAMA_PONTOS
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS
PRIMARY KEY (ID);

COMMENT ON COLUMN TBPROGRAMA_PONTOS.DATA_ZERAR IS
'data em que a contagem de pontos é zerada';

CREATE SEQUENCE GTBPROGRAMA_PONTOS;

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

COMMENT ON TABLE TBPROGRAMA_PONTOS IS 'programas de pontos para clientes';

CREATE trigger ttbprograma_pontos_b02i for tbprograma_pontos
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

CREATE trigger tbprograma_pontos_bu0 for tbprograma_pontos
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF ( NEW.descricao <> old.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição:'||old.descricao||' -> '||NEW.descricao||', ';
    END

    IF ( NEW.data_zerar <> old.data_zerar) THEN
    BEGIN
        HISTORICO = HISTORICO||' Dat. Zerar:'||formatdatetime(old.data_zerar)||' -> '||formatdatetime(NEW.data_zerar)||', ';
    END

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

CREATE trigger tbprograma_pontos_bd0 for tbprograma_pontos
active before delete position 0
AS
begin
  exception "..." 'Não é possível excluir programa de pontos, inative!';
end;

CREATE TABLE TBPROGRAMA_PONTOS_NOTIFICAR (
    ID INTEGER NOT NULL,
    USUARIO_ID INTEGER DEFAULT 0,
    STATUS INTEGER DEFAULT 1);

ALTER TABLE TBPROGRAMA_PONTOS_NOTIFICAR
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_NOTIFICAR
PRIMARY KEY (ID);

CREATE SEQUENCE GTBPROGRAMA_PONTOS_NOTF;

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

CREATE TABLE TBPROGRAMA_PONTOS_PREMIOS (
    ID INTEGER NOT NULL,
    PRODUTO_ID INTEGER,
    PONTOS INTEGER,
    STATUS INTEGER DEFAULT 1,
    TIPO INTEGER);

ALTER TABLE TBPROGRAMA_PONTOS_PREMIOS
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_PREMIOS
PRIMARY KEY (ID);

COMMENT ON COLUMN TBPROGRAMA_PONTOS_PREMIOS.TIPO IS
'1 - Multiplo, Sempre que atingir um multiplo recebe o premio
2 - Resgate, o premio deve ser resgatado
3 - Meta, o premio é alcançado uma vez';

CREATE SEQUENCE GTBPROGRAMA_PONTOS_PREM;

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

ALTER TABLE TBPROGRAMA_PONTOS
    ADD TIPO INTEGER;

COMMENT ON COLUMN TBPROGRAMA_PONTOS.TIPO IS
'1 - Multiplo, Sempre que atingir um multiplo recebe o premio
2 - Resgate, o premio deve ser resgatado
3 - Meta, o premio é alcançado uma vez';

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

    HISTORICO = '';

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

    IF ( NEW.descricao <> old.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição:'||old.descricao||' -> '||NEW.descricao||',';
    END

    IF ( NEW.data_zerar <> old.data_zerar) THEN
    BEGIN
        HISTORICO = HISTORICO||' Dat. Zerar:'||formatdatetime(old.data_zerar)||' -> '||formatdatetime(NEW.data_zerar)||',';
    END

    IF ( NEW.tipo <> old.tipo) THEN
    BEGIN
        HISTORICO = HISTORICO||' Tipo:'||old.tipo||' -> '||new.tipo||',';
    END

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

ALTER TABLE TBPROGRAMA_PONTOS_NOTIFICAR
    ADD PROGRAMA_PONTOS_ID INTEGER;


CREATE trigger ttbprograma_pontos_notif_b02i for tbprograma_pontos_notificar
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' Usuário '||new.usuario_id||' Incluido';
    END

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

CREATE trigger ttbprograma_pontos_notif_b01d for tbprograma_pontos_notificar
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' Usuário '||old.usuario_id||' Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPROGRAMA_PONTOS',old.programa_pontos_id, HISTORICO);
    END
end;

CREATE trigger ttbprograma_pontos_notif_b01u for tbprograma_pontos_notificar
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' Usuário '||new.usuario_id||' Status:'||new.status||' -> '||old.status;
    END

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

CREATE trigger ttbprograma_pontos_prem_b01i for tbprograma_pontos_premios
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' Produto '||new.produto_id||' Incluido';
    END

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

CREATE trigger ttbprograma_pontos_prem_b01d for tbprograma_pontos_premios
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' Produto '||old.produto_id||' Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPROGRAMA_PONTOS',old.programa_pontos_id, HISTORICO);
    END
end;

CREATE trigger ttbprograma_pontos_prem_b01u for tbprograma_pontos_premios
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( new.pontos <> old.pontos) THEN
    BEGIN
        HISTORICO = HISTORICO||' Produto '||old.produto_id||' Pontos:'||new.pontos||' -> '||old.pontos;
    END

    IF ( new.produto_id <> old.produto_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Produto:'||new.produto_id||' -> '||old.produto_id;
    END

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

CREATE TABLE TBPROGRAMA_PONTOS_CLIENTE (
    ID INTEGER NOT NULL,
    EMPRESA_ID INTEGER,
    PREMIO_ID INTEGER,
    PONTOS_SALDO NUMERIC(15,2),
    PONTOS_ACUMULADOS NUMERIC(15,2));

ALTER TABLE TBPROGRAMA_PONTOS_CLIENTE
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_CLIENTE
PRIMARY KEY (ID);

CREATE SEQUENCE GTBPROGRAMA_PON_CLIENTE;

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

COMMENT ON TABLE TBPROGRAMA_PONTOS_CLIENTE IS 'tabela com o saldo de pontos dos clientes';

create or alter function fn_add_pontos (
    empresa_id integer,
    programa_id integer,
    pontos integer)
returns integer
as
declare variable produto_id integer;
declare variable id integer;
declare variable pontos_saldo numeric(15,2);
declare variable pontos_acumulados numeric(15,2);
begin

  for
    select
        p.id,
        p.produto_id
    from tbprograma_pontos_premios p
    where p.programa_pontos_id = :PROGRAMA_ID
  into
    :ID,
    :PRODUTO_ID
  do begin

     PONTOS_SALDO = 0;
     PONTOS_ACUMULADOS = 0;


     select
            s.pontos_saldo,
            s.pontos_acumulados
        from TBPROGRAMA_PONTOS_CLIENTE s
        where s.empresa_id = :EMPRESA_ID
        and s.premio_id = :ID
      into
        :PONTOS_SALDO,
        :PONTOS_ACUMULADOS;

     UPDATE OR INSERT INTO TBPROGRAMA_PONTOS_CLIENTE (EMPRESA_ID, PREMIO_ID, PONTOS_SALDO, PONTOS_ACUMULADOS)
                                         VALUES (:EMPRESA_ID, :ID, coalesce(:PONTOS_SALDO,0) + :PONTOS, coalesce(:PONTOS_ACUMULADOS,0) + :PONTOS)
                                       MATCHING (EMPRESA_ID, PREMIO_ID);

  end


  return 1;
end;

COMMENT ON PARAMETER FN_ADD_PONTOS.EMPRESA_ID IS
'id da empresa';

COMMENT ON PARAMETER FN_ADD_PONTOS.PROGRAMA_ID IS
'id do programa de pontos';

COMMENT ON PARAMETER FN_ADD_PONTOS.PONTOS IS
'qtd de pontos';

CREATE TABLE TBPROGRAMA_PONTOS_H (
    ID INTEGER NOT NULL,
    PROGRAMA_PONTOS_ID INTEGER DEFAULT 0,
    EMPRESA_ID INTEGER DEFAULT 0,
    PONTOS NUMERIC(15,2) DEFAULT 0,
    DATA_HORA TIMESTAMP DEFAULT 'now',
    ORIGEM VARCHAR(50) DEFAULT '');

ALTER TABLE TBPROGRAMA_PONTOS_H
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_H
PRIMARY KEY (ID);

CREATE SEQUENCE GTBPROGRAMA_PONTOS_H;

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

COMMENT ON TABLE TBPROGRAMA_PONTOS_H IS 'tabela de historico de pontos de fidelidade';

CREATE INDEX ITBPROGRAMA_PONTOS_H_03
ON TBPROGRAMA_PONTOS_H (PROGRAMA_PONTOS_ID,EMPRESA_ID,DATA_HORA);

CREATE INDEX IITBPROGRAMA_PONTOS_H_02
ON TBPROGRAMA_PONTOS_H (DATA_HORA);

CREATE INDEX ITBPROGRAMA_PONTOS_H_01
ON TBPROGRAMA_PONTOS_H (PROGRAMA_PONTOS_ID,EMPRESA_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_CLIENTE_02
ON TBPROGRAMA_PONTOS_CLIENTE (EMPRESA_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_CLIENTE_01
ON TBPROGRAMA_PONTOS_CLIENTE (EMPRESA_ID,PREMIO_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_PREMIOS_01
ON TBPROGRAMA_PONTOS_PREMIOS (PROGRAMA_PONTOS_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_NOTIFICA_02
ON TBPROGRAMA_PONTOS_NOTIFICAR (USUARIO_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_NOTIFICA_01
ON TBPROGRAMA_PONTOS_NOTIFICAR (PROGRAMA_PONTOS_ID);

CREATE OR ALTER function fn_add_pontos (
    empresa_id integer,
    programa_id integer,
    pontos integer,
    origem varchar(50) = '')
returns integer
as
declare variable produto_id integer;
declare variable id integer;
declare variable pontos_saldo numeric(15,2);
declare variable pontos_acumulados numeric(15,2);
begin

  for
    select
        p.id,
        p.produto_id
    from tbprograma_pontos_premios p
    where p.programa_pontos_id = :PROGRAMA_ID
  into
    :ID,
    :PRODUTO_ID
  do begin

     PONTOS_SALDO = 0;
     PONTOS_ACUMULADOS = 0;


     select
            s.pontos_saldo,
            s.pontos_acumulados
        from TBPROGRAMA_PONTOS_CLIENTE s
        where s.empresa_id = :EMPRESA_ID
        and s.premio_id = :ID
      into
        :PONTOS_SALDO,
        :PONTOS_ACUMULADOS;

     UPDATE OR INSERT INTO TBPROGRAMA_PONTOS_CLIENTE (EMPRESA_ID, PREMIO_ID, PONTOS_SALDO, PONTOS_ACUMULADOS)
                                         VALUES (:EMPRESA_ID, :ID, coalesce(:PONTOS_SALDO,0) + :PONTOS, coalesce(:PONTOS_ACUMULADOS,0) + :PONTOS)
                                       MATCHING (EMPRESA_ID, PREMIO_ID);
  end

  INSERT INTO TBPROGRAMA_PONTOS_H (PROGRAMA_PONTOS_ID, EMPRESA_ID, PONTOS, DATA_HORA, ORIGEM)
                         VALUES (:PROGRAMA_ID, :EMPRESA_ID, :PONTOS, current_timestamp, :ORIGEM);

  return 1;
end;

COMMENT ON PARAMETER FN_ADD_PONTOS.EMPRESA_ID IS
'id da empresa';

COMMENT ON PARAMETER FN_ADD_PONTOS.PROGRAMA_ID IS
'id do programa de pontos';

COMMENT ON PARAMETER FN_ADD_PONTOS.PONTOS IS
'qtd de pontos';

CREATE OR ALTER trigger tbprograma_pontos_cliente_biu0 for tbprograma_pontos_cliente
active before insert or update position 0
AS
    declare variable pontos integer;
    declare variable produto_id integer;
    declare variable tipo integer;
    declare variable receber integer;
    declare variable programa_pontos_id integer;
    declare variable descricao varchar(100);
    declare variable usuario_id integer;
    declare variable qtd integer;
    declare variable email varchar(300);
    declare variable empresa varchar(300);
    declare variable produto varchar(300);
    declare variable MENSAGEM1 varchar(7000);
    declare variable MENSAGEM2 varchar(7000);
begin

  if(new.pontos_saldo > 0)then
  begin

      :receber = 0;

      select
        P.pontos,
        P.produto_id,
        S.descricao,
        S.tipo,
        P.programa_pontos_id
      FROM TBPROGRAMA_PONTOS_PREMIOS P, TBPROGRAMA_PONTOS S
      WHERE S.id = P.programa_pontos_id
      AND P.id = NEW.premio_id
      AND S.status = 1
      AND P.status = 1
      into
         :pontos,
         :produto_id,
         :descricao,
         :tipo,
         :programa_pontos_id;

      if(:tipo = 1 and new.pontos_saldo >= :pontos)then
      begin
        :receber = 1;
        :qtd = new.pontos_saldo / (:pontos * 1.0000);
        new.pontos_saldo = new.pontos_saldo - (:qtd * :pontos);
      end

      if(:tipo = 3 and new.pontos_saldo >= :pontos)then
      begin
        :receber = 1;
        :qtd = 1;
      end

      if(:receber = 1)then
      begin

         :empresa = coalesce((select lpad(e.codigo,6,0)||' - '||e.nomefantasia||' CNPJ/CPF:'||fn_mask(e.cnpj) from tbempresa e where e.codigo = new.empresa_id),'ID->'||new.empresa_id);
         :produto = coalesce((select lpad(o.codigo,6,0)||' - '||o.descricao from tbproduto o where o.codigo = :produto_id),'ID->'||:produto_id);


         :MENSAGEM1 = '<h3>'||:descricao||'</h3>'||
                      'Cliente: '||:empresa||'<br>'||
                      'Prêmio: '||:qtd||' x '||:produto||'<br>'||
                      '<a href="https://gc.delfa.com.br/_12260?CLIENTE='||new.empresa_id||'"  target="_blank">Abrir Histórico</a>';

         :MENSAGEM2 = '';

         for
             select
                u.codigo as usuario_id,
                u.email
             from tbprograma_pontos_notificar n, tbusuario u
             where n.programa_pontos_id = :programa_pontos_id
             and n.status = 1
             and u.status = 1
             and char_length(coalesce(u.email,'')) > 0
             and u.codigo = n.usuario_id
         into
            :usuario_id,
            :email
         do begin

            INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
                   VALUES (1, :usuario_id, 0, 0, :descricao, 0, 0, current_timestamp, -1, current_timestamp, 0, 'TBPROGRAMA_PONTOS_CLIENTE', new.id, :MENSAGEM1||:MENSAGEM2);


            INSERT INTO TBEMAIL (EMAIL, USUARIO_ID, ASSUNTO, STATUS, DATAHORA, DATAHORA_ENVIO, FLAG, PAINEL_ID, CORPO)
                   VALUES (:email, 0, :descricao, 0, current_timestamp, null, 0, 0, :MENSAGEM1||:MENSAGEM2);

            INSERT INTO TBPROGRAMA_PONTOS_H (PROGRAMA_PONTOS_ID, EMPRESA_ID, PONTOS, DATA_HORA, ORIGEM)
                         VALUES (:programa_pontos_id, new.empresa_id, 0, current_timestamp, fn_elipses('Prêmio: '||:qtd||' x '||:produto, 50));


         end
      end
   end

end;

ALTER TABLE TBPROGRAMA_PONTOS
    ADD PONTOS_PEDIDO INTEGER DEFAULT 0,
    ADD PONTOS_NOTAS INTEGER DEFAULT 0,
    ADD FATOR_PONTOS INTEGER DEFAULT 1,
    ADD FAMILIA_ID INTEGER DEFAULT 3,
    ADD BAIXAR_SALDO INTEGER DEFAULT 2;

COMMENT ON COLUMN TBPROGRAMA_PONTOS.PONTOS_PEDIDO IS
'1 - sim
0 - nao';

COMMENT ON COLUMN TBPROGRAMA_PONTOS.PONTOS_NOTAS IS
'1 - sim
0 - nao';

COMMENT ON COLUMN TBPROGRAMA_PONTOS.FATOR_PONTOS IS
'fator multiplicador de pontos';

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

COMMENT ON COLUMN TBPROGRAMA_PONTOS.BAIXAR_SALDO IS
'0 - nao
1 - faturar nota
2 - ganhar premio';

ALTER TABLE TBPROGRAMA_PONTOS
    ADD PONTOS_VALIDADE NUMERIC(15,4);

COMMENT ON COLUMN TBPROGRAMA_PONTOS.PONTOS_VALIDADE IS
'quantidade de meses que os pontos valem';

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

    HISTORICO = '';

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

    IF ( NEW.descricao <> old.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição:'||old.descricao||' -> '||NEW.descricao||',';
    END

    IF ( NEW.data_zerar <> old.data_zerar) THEN
    BEGIN
        HISTORICO = HISTORICO||' Dat. Zerar:'||formatdatetime(old.data_zerar)||' -> '||formatdatetime(NEW.data_zerar)||',';
    END

    IF ( NEW.tipo <> old.tipo) THEN
    BEGIN
        HISTORICO = HISTORICO||' Tipo:'||old.tipo||' -> '||new.tipo||',';
    END

    IF ( NEW.pontos_pedido <> old.pontos_pedido) THEN
    BEGIN
        HISTORICO = HISTORICO||' Pto. Pedido:'||old.pontos_pedido||' -> '||new.pontos_pedido||',';
    END

    IF ( NEW.pontos_notas <> old.pontos_notas) THEN
    BEGIN
        HISTORICO = HISTORICO||' Pto. NF:'||old.pontos_notas||' -> '||new.pontos_notas||',';
    END

    IF ( NEW.fator_pontos <> old.fator_pontos) THEN
    BEGIN
        HISTORICO = HISTORICO||' Fator:'||old.fator_pontos||' -> '||new.fator_pontos||',';
    END

    IF ( NEW.familia_id <> old.familia_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Família:'||old.familia_id||' -> '||new.familia_id||',';
    END

    IF ( NEW.pontos_validade <> old.pontos_validade) THEN
    BEGIN
        HISTORICO = HISTORICO||' Validade:'||old.pontos_validade||' -> '||new.pontos_validade||',';
    END

    IF ( NEW.baixar_saldo <> old.baixar_saldo) THEN
    BEGIN
        HISTORICO = HISTORICO||' Baixar Saldo:'||old.baixar_saldo||' -> '||new.baixar_saldo||',';
    END

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


CREATE TABLE TBPROGRAMA_PONTOS_DETALHE (
    ID INTEGER NOT NULL,
    EMPRESA_ID INTEGER,
    PREMIO_ID INTEGER,
    PONTOS INTEGER,
    VALIDADE TIMESTAMP,
    SALDO INTEGER,
    STATUS COMPUTED BY (iif(VALIDADE >= current_timestamp, 3, iif(SALDO > 0, 1, 2))),
    DESCRICAO INTEGER,
    PROGRAMA_ID INTEGER);

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_DETALHE
PRIMARY KEY (ID);

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.VALIDADE IS
'data em que vai vencer os pontos';

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.SALDO IS
'quantidade de pontos validos';

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.STATUS IS
'1 - valido
2 - baixado
3 - vencido';

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.DESCRICAO IS
'origem ou observacao sobre os pontos';

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.PROGRAMA_ID IS
'id do programa de pontos';

CREATE SEQUENCE GTBPROGRAMA_PONTOS_D;

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

COMMENT ON TABLE TBPROGRAMA_PONTOS_DETALHE IS 'tabela com as pontuacoes de cada cliente, cmo um estrato';


CREATE trigger ttbprograma_pontos_d_b01u for tbprograma_pontos_detalhe
active before update position 0
AS
  declare variable cliente varchar(100);
begin

  if(new.saldo >= 0)then
  begin
    cliente = coalesce((select lpad(e.codigo,6,0)||' - '||e.razaosocial from tbcliente e where e.codigo = new.empresa_id), NEW.EMPRESA_ID);
    exception "..." 'Saldo de Pontos não pode ficar negativo, Saldo:'||new.saldo||', Cliente:'||cliente;
  end

end;

CREATE OR ALTER trigger ttbprograma_pontos_d_b01u for tbprograma_pontos_detalhe
active before update position 0
AS
  declare variable cliente varchar(100);
begin

  if(new.saldo >= 0)then
  begin
    cliente = coalesce((select lpad(e.codigo,6,0)||' - '||e.razaosocial from tbcliente e where e.codigo = new.empresa_id), NEW.EMPRESA_ID);
    exception "..." 'Saldo de Pontos não pode ficar negativo, Saldo:'||new.saldo||', Cliente:'||cliente;
  end

end;

CREATE trigger ttbpedido_a02u for tbpedido
active after update position 0
AS
    declare variable pontos integer;
begin

   --validar pontuacao pedidos
   --pontos = FN_PONTOS_VALIDAR_PEDIDO(new.pedido);

end;

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
    ADD PEDIDO_ID INTEGER,
    ADD NFS_ID INTEGER;

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.PEDIDO_ID IS
'id do pedido de origem';

COMMENT ON COLUMN TBPROGRAMA_PONTOS_DETALHE.NFS_ID IS
'id da nota de origem';

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
    ADD OBSERVACAO VARCHAR(100);

CREATE OR ALTER function fn_pontos_validar_pedido (
    pedido_id integer,
    pedido_item_id integer)
returns integer
as
declare variable id integer;
declare variable status integer;
declare variable descricao varchar(400);
declare variable data_zerar timestamp;
declare variable tipo integer;
declare variable pontos_pedido integer;
declare variable pontos_notas integer;
declare variable fator_pontos numeric(15,2);
declare variable familia_id integer;
declare variable baixar_saldo integer;
declare variable pontos_validade numeric(18,4);
declare variable pedido integer;
declare variable cliente_codigo integer;
declare variable valor numeric(18,10);
declare variable valor_total numeric(18,4);
declare variable premio_id integer;
declare variable pponto numeric(15,2);
declare variable psaldo numeric(15,2);
declare variable gravou integer;
begin

    GRAVOU = 0;

  for

     select
        ID,
        STATUS,
        DESCRICAO,
        DATA_ZERAR,
        TIPO,
        PONTOS_PEDIDO,
        PONTOS_NOTAS,
        FATOR_PONTOS,
        FAMILIA_ID,
        BAIXAR_SALDO,
        PONTOS_VALIDADE
    from tbprograma_pontos p
    where p.status = 1
    and p.pontos_pedido = 1

  into
    ID,
    STATUS,
    DESCRICAO,
    DATA_ZERAR,
    TIPO,
    PONTOS_PEDIDO,
    PONTOS_NOTAS,
    FATOR_PONTOS,
    FAMILIA_ID,
    BAIXAR_SALDO,
    PONTOS_VALIDADE
  do begin
     for

        select first 1
            p.pedido,
            p.cliente_codigo,
            sum(i.valor_total * iif(i.situacao < 3, 1, -1)) as valor_total
        from tbpedido p, tbpedido_item i, tbproduto s
        where p.pedido = i.pedido
        and i.produto_codigo = s.codigo
        and p.pedido = :PEDIDO_ID
        and s.familia_codigo = :FAMILIA_ID
        and i.controle = :PEDIDO_ITEM_ID
        
        group by 1,2
     into
        PEDIDO,
        CLIENTE_CODIGO,
        VALOR_TOTAL
     do begin

        for
            select
                k.id
            from tbprograma_pontos_premios k
            where k.programa_pontos_id = 2
            and k.status = 1
        into
           PREMIO_ID
        do begin

            PPONTO = 0;
            PSALDO = 0;

            select l.pontos, l.saldo from TBPROGRAMA_PONTOS_DETALHE l
            where  EMPRESA_ID   = :CLIENTE_CODIGO
            and PREMIO_ID       = :PREMIO_ID
            and PEDIDO_ID       = :PEDIDO_ID
            into
                PPONTO,
                PSALDO;

            UPDATE OR INSERT INTO TBPROGRAMA_PONTOS_DETALHE (EMPRESA_ID, PREMIO_ID, PONTOS, VALIDADE, SALDO, observacao, PROGRAMA_ID, PEDIDO_ID, NFS_ID)
                                   VALUES (:CLIENTE_CODIGO, :PREMIO_ID, coalesce(:PPONTO,0) + (:VALOR_TOTAL * :FATOR_PONTOS),
                                   dateadd(month, iif(:PONTOS_VALIDADE > 0, :PONTOS_VALIDADE, 12000), current_timestamp),
                                   coalesce(:PSALDO,0) + (:VALOR_TOTAL * :FATOR_PONTOS), fn_elipses('Pontos de Pedidos, Pedido:'||:PEDIDO_ID,100), :ID , :PEDIDO_ID, 0)

                                   MATCHING (EMPRESA_ID, PREMIO_ID, PEDIDO_ID);

            GRAVOU = GRAVOU + 1;
        end
     end
  end

  return GRAVOU;
end;

CREATE OR ALTER trigger ttbpedido_item_b2i for tbpedido_item
active before insert position 2
AS
declare variable vFlag Integer;
declare variable vFamilia_Id Integer;
declare variable vUF char(2);
declare variable vRepresentante_Id Integer;
declare variable vVendedor_Id Integer;
declare variable vComissao Numeric(15,2);
declare variable pontos integer;
begin

  --validar pontuacao pedidos
  pontos = FN_PONTOS_VALIDAR_PEDIDO(new.pedido, new.controle);


  vFlag = Coalesce((Select FLAG_REPRESENTANTE From TbPedido Where Pedido = New.Pedido),1);

  if (vFlag = 0) then
      Begin
      vFamilia_Id = Coalesce((Select Familia_Codigo From TbProduto Where Codigo = New.Produto_Codigo),0);
      vUF = Coalesce((Select UF From TbCliente Where Codigo = New.Cliente_Codigo),'XX');
    
      For Select First 1 Representante_Id, Vendedor_Id, Comissao
            From TbCliente_Representante C
           Where C.Familia_Id = :vFamilia_Id
             and (
                  C.Cliente_id = New.Cliente_Codigo or
                  C.Cliente_uf = :vUF or
                  C.Cliente_uf = 'XX'
                  )
           Order By C.Cliente_id Desc, C.Cliente_uf Desc
            Into :vRepresentante_Id, :vVendedor_Id, :vComissao
              Do Begin
                 Update TbPedido P Set
                 P.Representante_Codigo = :vRepresentante_Id,
                 P.Vendedor_Id = :vVendedor_Id,
                 P.Representante_Comissao = :vComissao,
                 P.FLAG_REPRESENTANTE = 1
                 Where Pedido = New.Pedido;
                 End
      End
END;

CREATE OR ALTER trigger ttbpedido_item_b10u for tbpedido_item
active before update position 10
AS
declare variable ecommerce integer;
declare variable pendentes integer;
declare variable pontos integer;
begin

  if((new.situacao <> old.situacao) or (new.valor_total <> old.valor_total))then
  begin
    --validar pontuacao pedidos
    pontos = FN_PONTOS_VALIDAR_PEDIDO(new.pedido, new.controle);
  end


  if(new.situacao <> old.situacao)then
  begin
      ecommerce = coalesce((select first 1 p.ecommerce from tbpedido p where p.pedido = new.pedido),0);

      if(ecommerce = 1)then
      begin
         pendentes = coalesce((select count(i.controle) as pendentes from tbpedido_item i where i.pedido = new.pedido and i.situacao = 1),1);
    
         if(pendentes = 0)then
         begin
            update tbpedido c set c.status_ecommerce = 4 where c.pedido = new.pedido;
         end
      end
  end
end;

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
    ADD SITUACAO COMPUTED BY (iif(VALIDADE >= current_timestamp, 3, iif(SALDO > 0, 1, 2)));

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE DROP SITUACAO;

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
    ALTER STATUS COMPUTED BY (iif(VALIDADE < current_timestamp, 3, iif(SALDO = 0, 2, 1)));


CREATE OR ALTER trigger ttbpedido_item_b998d for tbpedido_item
active before delete position 0
AS
begin
    --remover pontos do item que esta sendo excluido
    if(old.situacao < 3)then
    begin
      update TBPROGRAMA_PONTOS_DETALHE d
      set d.pontos = d.pontos - old.valor_total,
      d.saldo = d.saldo - old.valor_total

      where d.pedido_id = old.pedido;
    end

end;

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_05
ON TBPROGRAMA_PONTOS_DETALHE (NFS_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_04
ON TBPROGRAMA_PONTOS_DETALHE (PEDIDO_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_03
ON TBPROGRAMA_PONTOS_DETALHE (PREMIO_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_02
ON TBPROGRAMA_PONTOS_DETALHE (SALDO);

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_01
ON TBPROGRAMA_PONTOS_DETALHE (PROGRAMA_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_06
ON TBPROGRAMA_PONTOS_DETALHE COMPUTED BY (iif(VALIDADE < current_timestamp, 3, iif(SALDO > 0, 1, 2)));

CREATE OR ALTER trigger ttbnf_lote_b99iu for tbnf_lote
active before insert or update position 99
AS
declare variable vLote Integer;
begin

    if (New.Xml = '07' and new.nfs_controle > 0) then
    begin
       --validar pontuacao nota
       pontos = fn_pontos_validar_nota(new.nfs_controle);
    end

    if (New.Xml = '08' and new.nfs_controle > 0) then
    begin
		--zerar pontuacao nota
       update TBPROGRAMA_PONTOS_DETALHE d set d.pontos = 0, d.saldo = 0 where d.nfs_id = old.nfs_controle;
    end
end;

CREATE OR ALTER trigger ttbpedido_item_b998d for tbpedido_item
active before delete position 998
AS
begin
    --remover pontos do item que esta sendo excluido
    if(old.situacao < 3)then
    begin
      update TBPROGRAMA_PONTOS_DETALHE d
      set d.pontos = d.pontos - old.valor_total,
      d.saldo = d.saldo - old.valor_total

      where d.pedido_id = old.pedido;
    end

end;

CREATE INDEX ITBPROGRAMA_PONTOS_PREMIOS_02
ON TBPROGRAMA_PONTOS_PREMIOS (STATUS);

CREATE INDEX ITBPROGRAMA_PONTOS_05
ON TBPROGRAMA_PONTOS (TIPO);

CREATE INDEX ITBPROGRAMA_PONTOS_04
ON TBPROGRAMA_PONTOS (FAMILIA_ID);

CREATE INDEX ITBPROGRAMA_PONTOS_03
ON TBPROGRAMA_PONTOS (PONTOS_PEDIDO);

CREATE INDEX ITBPROGRAMA_PONTOS_02
ON TBPROGRAMA_PONTOS (PONTOS_NOTAS);

CREATE INDEX ITBPROGRAMA_PONTOS_01
ON TBPROGRAMA_PONTOS (STATUS);

CREATE TABLE TBPROGRAMA_PONTOS_PREMIACAO (
    ID INTEGER NOT NULL,
    PREMIO_ID INTEGER,
    EMPRESA_ID INTEGER,
    PROGRAMA_ID INTEGER,
    PONTOS NUMERIC(15,4),
    STATUS INTEGER);

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO
ADD CONSTRAINT PK_TBPROGRAMA_PONTOS_PREMIACAO
PRIMARY KEY (ID);

COMMENT ON COLUMN TBPROGRAMA_PONTOS_PREMIACAO.STATUS IS
'1 - pendente
2 - baixado
3 - encerrado';

CREATE SEQUENCE GTBPROGRAMA_PONTOS_PRE;

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

COMMENT ON TABLE TBPROGRAMA_PONTOS_PREMIACAO IS 'tabela com os premios obitidos';

CREATE trigger ttbprograma_pontos_d_b99iu for tbprograma_pontos_detalhe
active after insert or update position 99
AS
declare variable ID INTEGER;
declare variable PONTOS INTEGER;
declare variable PROGRAMA_ID INTEGER;
declare variable SALDO BIGINT;
declare variable BLOQUEADO NUMERIC(18,4);
begin

   for
        select
            *
        from(
            select
                s.id,
                s.pontos,
                f.id as PROGRAMA_ID,
                sum(d.saldo) as SALDO,
                coalesce((select sum(a.pontos) from tbprograma_pontos_premiacao a where a.premio_id = s.id and a.empresa_id = d.empresa_id and a.programa_id = f.id and a.status = 1),0) BLOQUEADO
            from TBPROGRAMA_PONTOS_DETALHE d, tbprograma_pontos_premios s, tbproduto p, tbprograma_pontos f
            where d.status = 1
            and s.id = d.premio_id
            and p.codigo = s.produto_id
            and f.id = d.programa_id
            and s.status = 1
            and f.status = 1
            and d.empresa_id = new.empresa_id
            and d.premio_id = new.premio_id
            and f.id = new.programa_id
            
            group by s.id,s.pontos, f.id, d.empresa_id
        ) where (SALDO - BLOQUEADO) >= pontos
  into
    ID,
    PONTOS,
    PROGRAMA_ID,
    SALDO,
    BLOQUEADO
  do begin
    INSERT INTO TBPROGRAMA_PONTOS_PREMIACAO (PREMIO_ID, EMPRESA_ID, PROGRAMA_ID, PONTOS, STATUS)
                                 VALUES (new.premio_id, new.empresa_id, new.programa_id, :PONTOS , 1);
  end
end;

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO
    ADD STATUS_NFS INTEGER;

COMMENT ON COLUMN TBPROGRAMA_PONTOS_PREMIACAO.STATUS_NFS IS
'1 - pendente
2 - emitida';

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO ALTER COLUMN STATUS_NFS
SET DEFAULT 1;

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO
    ADD HISTORICO_BAIXA BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE trigger ttbprograma_pontos_pre_b02i for tbprograma_pontos_premiacao
active before insert position 0
AS
declare variable baixar_saldo integer;
begin
   baixar_saldo = coalesce((select p.baixar_saldo  from tbprograma_pontos p where p.id = new.programa_id),0);

   if(baixar_saldo = 2)then
   begin
     new.status = 2;
   end
end;

DROP INDEX ITBPROGRAMA_PONTOS_DETALHE_06;

CREATE INDEX ITBPROGRAMA_PONTOS_DETALHE_06
ON TBPROGRAMA_PONTOS_DETALHE COMPUTED BY (iif(VALIDADE < current_timestamp, 3, iif(SALDO = 0, 2, 1)));

CREATE OR ALTER trigger ttbprograma_pontos_pre_b99u for tbprograma_pontos_premiacao
active before update position 99
AS
    declare variable id integer;
    declare variable saldo numeric(15,4);
    declare variable saldo_premio numeric(15,4);
begin

  if( (old.status = 1) or (new.status = 1) )then
  begin

     new.historico_baixa = '0|0';
     saldo_premio        = new.pontos;

     for

       select
          d.id,
          d.saldo
       from tbprograma_pontos_detalhe d
       where d.saldo      = 1
       and d.premio_id    = new.premio_id
       and d.empresa_id   = new.empresa_id
       and d.programa_id  = new.programa_id

     into
       :ID,
       :SALDO
     do begin

        if(saldo_premio > 0)then
        begin
            if(saldo_premio > :SALDO)then
            begin
    
               :saldo_premio = :saldo_premio - :SALDO;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:SALDO;
               update tbprograma_pontos_detalhe h set h.saldo = 0 where h.id = :ID;
    
            end else
            begin
    
               :SALDO = :SALDO - :saldo_premio;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:saldo_premio;
               update tbprograma_pontos_detalhe h set h.saldo = :SALDO where h.id = :ID;

               saldo_premio = 0;
    
            end
        end
     end

     if(saldo_premio > 0)then
     begin
        INSERT INTO TBPROGRAMA_PONTOS_DETALHE (EMPRESA_ID, PREMIO_ID, PONTOS, VALIDADE, SALDO, PROGRAMA_ID, PEDIDO_ID, NFS_ID, OBSERVACAO)
                               VALUES (new.empresa_id, new.premio_id, (:saldo_premio * -1) , '3000-01-01 00:00:00', (:saldo_premio * -1), new.programa_id, 0, 0, 'Saldo de baixa de prêmio');
     end

  end


end;

ALTER TABLE TBPROGRAMA_PONTOS_DETALHE
    ADD FLAG INTEGER DEFAULT 0;

CREATE OR ALTER trigger ttbprograma_pontos_d_b99iu for tbprograma_pontos_detalhe
active after insert or update position 99
AS
declare variable ID INTEGER;
declare variable PONTOS INTEGER;
declare variable TIPO INTEGER;
declare variable PROGRAMA_ID INTEGER;
declare variable qtd numeric(18,8);
declare variable SALDO BIGINT;
declare variable BLOQUEADO NUMERIC(18,4);
begin

   if(new.flag = 0)then
   begin
       for
            select
                *
            from(
                select
                    s.id,
                    s.pontos,
                    f.id as PROGRAMA_ID,
                    f.tipo,
                    sum(d.saldo) as SALDO,
                    coalesce((select sum(a.pontos) from tbprograma_pontos_premiacao a where a.premio_id = s.id and a.empresa_id = d.empresa_id and a.programa_id = f.id and a.status = 1),0) BLOQUEADO
                from TBPROGRAMA_PONTOS_DETALHE d, tbprograma_pontos_premios s, tbproduto p, tbprograma_pontos f
                where d.status = 1
                and s.id = d.premio_id
                and p.codigo = s.produto_id
                and f.id = d.programa_id
                and s.status = 1
                and f.status = 1
                and d.empresa_id = new.empresa_id
                and d.premio_id = new.premio_id
                and f.id = new.programa_id
                
                group by s.id,s.pontos, f.id, f.tipo, d.empresa_id
            ) where (SALDO - BLOQUEADO) >= pontos
      into
        ID,
        PONTOS,
        PROGRAMA_ID,
        TIPO,
        SALDO,
        BLOQUEADO
      do begin

        --premio multiplo
        if(:TIPO = 1)then
        begin
            --qtd multipla do saldo - o premios com saldo nao baixado
            qtd = (SALDO - BLOQUEADO) / PONTOS;
            qtd = fn_array(cast(qtd as varchar(20)),1,'.');
    
            INSERT INTO TBPROGRAMA_PONTOS_PREMIACAO (PREMIO_ID, EMPRESA_ID, PROGRAMA_ID, PONTOS, STATUS, QUANTIDADE)
                                         VALUES (new.premio_id, new.empresa_id, new.programa_id, :PONTOS , 1, :qtd);
        end

        --premio unico
        if(:TIPO = 3)then
        begin

            qtd = coalesce((select count(o.id) from TBPROGRAMA_PONTOS_PREMIACAO o where o.premio_id = new.premio_id and o.empresa_id = new.empresa_id and o.programa_id = new.programa_id),0);
    
            -- se empresa nunca recebel este premio
            if(qtd = 0)then
            begin
                INSERT INTO TBPROGRAMA_PONTOS_PREMIACAO (PREMIO_ID, EMPRESA_ID, PROGRAMA_ID, PONTOS, STATUS, QUANTIDADE)
                                             VALUES (new.premio_id, new.empresa_id, new.programa_id, :PONTOS , 1, 1);
            end
        end

      end
   end
end;



ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO
    ADD QUANTIDADE INTEGER;

COMMENT ON COLUMN TBPROGRAMA_PONTOS_PREMIACAO.QUANTIDADE IS
'quantidade de premios';


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

CREATE OR ALTER TRIGGER TTBPROGRAMA_PONTOS_PRE_B02I FOR TBPROGRAMA_PONTOS_PREMIACAO
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable baixar_saldo integer;
begin

   baixar_saldo = coalesce((select p.baixar_saldo  from tbprograma_pontos p where p.id = new.programa_id),0);

   if(baixar_saldo = 2 and inserting)then
   begin
     new.status = 2;
   end

   if(baixar_saldo = 1 and updating and new.status_nfs = 2)then
   begin
     new.status = 2;
   end

end;

CREATE OR ALTER TRIGGER TTBPROGRAMA_PONTOS_PRE_B100D FOR TBPROGRAMA_PONTOS_PREMIACAO
ACTIVE BEFORE DELETE POSITION 100
AS
    declare variable ID integer;
    declare variable BAIXADO numeric(15,4);
begin
  if(old.status = 2)then
  begin

     for
        select
            fn_array(OSPLIT,1,'|') as ID,
            fn_array(OSPLIT,2,'|') as BAIXADO
        from split(coalesce(old.historico_baixa,''),'#')
     into
       :ID,
       :BAIXADO
     do begin
       update tbprograma_pontos_detalhe h set h.saldo = (h.saldo + :BAIXADO), h.flag = 1 where h.id = :ID;
     end

     delete from tbprograma_pontos_detalhe h where h.observacao like '%#'||old.id||'#%' and h.pedido_id = 0 and h.nfs_id = 0;
  end
end;

CREATE OR ALTER TRIGGER TTBPROGRAMA_PONTOS_PRE_B100U FOR TBPROGRAMA_PONTOS_PREMIACAO
ACTIVE BEFORE UPDATE POSITION 100
AS
    declare variable ID integer;
    declare variable BAIXADO numeric(15,4);
begin
  if(old.status = 2 and new.status = 1)then
  begin

     for
        select
            fn_array(OSPLIT,1,'|') as ID,
            fn_array(OSPLIT,2,'|') as BAIXADO
        from split(coalesce(new.historico_baixa,''),'#')
     into
       :ID,
       :BAIXADO
     do begin
       update tbprograma_pontos_detalhe h set h.saldo = (h.saldo + :BAIXADO), h.flag = 1 where h.id = :ID;
     end

     delete from tbprograma_pontos_detalhe h where h.observacao like '%#'||new.id||'#%' and h.pedido_id = 0 and h.nfs_id = 0;

     new.historico_baixa = '';
  end
end;

CREATE OR ALTER TRIGGER TTBPROGRAMA_PONTOS_PRE_B99I FOR TBPROGRAMA_PONTOS_PREMIACAO
ACTIVE BEFORE INSERT POSITION 99
AS
    declare variable id integer;
    declare variable saldo numeric(15,4);
    declare variable saldo_premio numeric(15,4);
begin

  if(new.status = 2)then
  begin

     new.historico_baixa = '0|0';
     saldo_premio        = new.quantidade * new.pontos;

     for

       select
          d.id,
          d.saldo
       from tbprograma_pontos_detalhe d
       where d.saldo      > 0
       and d.premio_id    = new.premio_id
       and d.empresa_id   = new.empresa_id
       and d.programa_id  = new.programa_id

     into
       :ID,
       :SALDO
     do begin

        if(saldo_premio > 0)then
        begin
            if(saldo_premio > :SALDO)then
            begin
    
               :saldo_premio = :saldo_premio - :SALDO;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:SALDO;
               update tbprograma_pontos_detalhe h set h.saldo = 0, h.flag = 1 where h.id = :ID;
    
            end else
            begin
    
               :SALDO = :SALDO - :saldo_premio;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:saldo_premio;
               update tbprograma_pontos_detalhe h set h.saldo = :SALDO, h.flag = 1 where h.id = :ID;

               saldo_premio = 0;
    
            end
        end
     end

     if(saldo_premio > 0)then
     begin
        INSERT INTO TBPROGRAMA_PONTOS_DETALHE (EMPRESA_ID, PREMIO_ID, PONTOS, VALIDADE, SALDO, PROGRAMA_ID, PEDIDO_ID, NFS_ID, OBSERVACAO,FLAG)
                               VALUES (new.empresa_id, new.premio_id, (:saldo_premio * -1) , '3000-01-01 00:00:00', (:saldo_premio * -1), new.programa_id, 0, 0, 'Saldo de baixa de prêmio',1);
     end

  end


end;

CREATE OR ALTER TRIGGER TTBPROGRAMA_PONTOS_PRE_B99U FOR TBPROGRAMA_PONTOS_PREMIACAO
ACTIVE BEFORE UPDATE POSITION 99
AS
    declare variable id integer;
    declare variable saldo numeric(15,4);
    declare variable saldo_premio numeric(15,4);
begin

  if( (old.status = 1) and (new.status = 2) )then
  begin

     new.historico_baixa = '0|0';
     saldo_premio        = new.quantidade * new.pontos;

     for

       select
          d.id,
          d.saldo
       from tbprograma_pontos_detalhe d
       where d.saldo      > 0
       and d.premio_id    = new.premio_id
       and d.empresa_id   = new.empresa_id
       and d.programa_id  = new.programa_id

     into
       :ID,
       :SALDO
     do begin

        if(saldo_premio > 0)then
        begin
            if(saldo_premio > :SALDO)then
            begin
    
               :saldo_premio = :saldo_premio - :SALDO;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:SALDO;
               update tbprograma_pontos_detalhe h set h.saldo = 0, h.flag = 1 where h.id = :ID;
    
            end else
            begin
    
               :SALDO = :SALDO - :saldo_premio;
               new.historico_baixa = new.historico_baixa ||'#'||:ID||'|'||:saldo_premio;
               update tbprograma_pontos_detalhe h set h.saldo = :SALDO, h.flag = 1 where h.id = :ID;

               saldo_premio = 0;
    
            end
        end
     end

     if(saldo_premio > 0)then
     begin
        INSERT INTO TBPROGRAMA_PONTOS_DETALHE (EMPRESA_ID, PREMIO_ID, PONTOS, VALIDADE, SALDO, PROGRAMA_ID, PEDIDO_ID, NFS_ID, OBSERVACAO, FLAG)
                               VALUES (new.empresa_id, new.premio_id, (:saldo_premio * -1) , '3000-01-01 00:00:00', (:saldo_premio * -1), new.programa_id, 0, 0, 'Saldo de baixa de prêmio #'||new.id||'#',1);
     end

  end


end;

DROP TRIGGER TBPROGRAMA_PONTOS_CLIENTE_BIU0;

DROP TRIGGER TTBPROGRAMA_PON_CLIENTE_B01I;

DROP TRIGGER TTBPROGRAMA_PONTOS_H_B01I;

DROP FUNCTION FN_ADD_PONTOS;

DROP TABLE TBPROGRAMA_PONTOS_CLIENTE;

DROP TABLE TBPROGRAMA_PONTOS_H;

CREATE OR ALTER trigger ttbnf_lote_b99iu for tbnf_lote
active before insert or update position 99
AS
declare variable vLote Integer;
begin

    if (New.Xml = '07' and new.nfs_controle > 0) then
    begin
       --validar pontuacao nota
       pontos = fn_pontos_validar_nota(new.nfs_controle);
    end

    if (((New.Xml = '08') or (New.Xml = '09') or (New.Xml = '10')  ) and new.nfs_controle > 0) then
    begin
       --zerar pontuacao nota
       update TBPROGRAMA_PONTOS_DETALHE d set d.pontos = 0, d.saldo = 0 where d.nfs_id = old.nfs_controle;
    end
end;

CREATE OR ALTER trigger ttbpedido_item_b10u for tbpedido_item
active before update position 10
AS
declare variable ecommerce integer;
declare variable pendentes integer;
declare variable pontos integer;
begin

  if(((new.situacao = 3 and old.situacao = 1) or (new.situacao = 1 and old.situacao = 3)) or (new.valor_total <> old.valor_total))then
  begin
    --validar pontuacao pedidos
    pontos = FN_PONTOS_VALIDAR_PEDIDO(new.pedido, new.controle);
  end


  if(new.situacao <> old.situacao)then
  begin
      ecommerce = coalesce((select first 1 p.ecommerce from tbpedido p where p.pedido = new.pedido),0);

      if(ecommerce = 1)then
      begin
         pendentes = coalesce((select count(i.controle) as pendentes from tbpedido_item i where i.pedido = new.pedido and i.situacao = 1),1);
    
         if(pendentes = 0)then
         begin
            update tbpedido c set c.status_ecommerce = 4 where c.pedido = new.pedido;
         end
      end
  end
end;

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO
    ADD NFS_BAIXA INTEGER;

COMMENT ON COLUMN TBPROGRAMA_PONTOS_PREMIACAO.NFS_BAIXA IS
'id da nota em que foi baixado';

ALTER TABLE TBPROGRAMA_PONTOS_PREMIACAO ALTER COLUMN NFS_BAIXA
SET DEFAULT 0;

CREATE OR ALTER trigger ttbnf_lote_b99iu for tbnf_lote
active before insert or update position 99
AS
declare variable vLote Integer;
begin

    if (New.Xml = '07' and new.nfs_controle > 0) then
    begin
       --validar pontuacao nota
       pontos = fn_pontos_validar_nota(new.nfs_controle);
       pontos = FN_PREMIOS_BAIXAR(new.nfs_controle,1);
    end

    if (((New.Xml = '08') or (New.Xml = '09') or (New.Xml = '10')  ) and new.nfs_controle > 0) then
    begin
       --zerar pontuacao nota
       update TBPROGRAMA_PONTOS_DETALHE d set d.pontos = 0, d.saldo = 0 where d.nfs_id = old.nfs_controle;
       pontos = FN_PREMIOS_BAIXAR(new.nfs_controle,2);
    end
end;

CREATE OR ALTER function fn_premios_baixar (
    nfs_id integer,
    flag integer)
returns integer
as
declare variable premio_id integer;
declare variable programa_pontos_id integer;
declare variable empresa_codigo integer;
declare variable quantidade numeric(15,4);
declare variable qtd_premios numeric(15,4);
declare variable produto varchar(100);
declare variable baixar_saldo integer;
begin

    for
      select
        m.id,
        m.programa_pontos_id,
        d.quantidade,
        f.empresa_codigo,
        p.descricao,
        w.baixar_saldo
      from tbnfs_item s, tbnfs_item_saldo d, tbproduto p, tbprograma_pontos_premios m, tbnfs f, tbprograma_pontos w
      where s.nfs_controle > 0
      and s.nfs_controle    = :NFS_ID
      and d.nfs_item_controle = s.controle
      and d.nfs_controle      = s.nfs_controle
      and d.quantidade        > 0
      and s.produto_codigo    = p.codigo
      and m.produto_id        = s.produto_codigo
      and m.status            = 1
      and f.controle          = s.nfs_controle
      and w.id                = m.programa_pontos_id
   into
      premio_id,
      programa_pontos_id,
      quantidade,
      empresa_codigo,
      PRODUTO,
      BAIXAR_SALDO
   do begin

       if(FLAG = 1)then
       begin
          for
            select
                sum(c.quantidade)
            from tbprograma_pontos_premiacao c
            where c.premio_id  = :premio_id
            and c.empresa_id   = :empresa_codigo
            and c.programa_id  = :programa_pontos_id
            and c.status_nfs = 1
          into
            :QTD_PREMIOS
          do begin
             if(:QTD_PREMIOS = quantidade)then
             begin
                update tbprograma_pontos_premiacao c
                    set c.status_nfs = 2,
                    c.nfs_baixa    = :NFS_ID
                where c.premio_id  = :premio_id
                and c.empresa_id   = :empresa_codigo
                and c.programa_id  = :programa_pontos_id
                and c.status_nfs = 1;
             end else
             begin
                if(:QTD_PREMIOS > 0)then
                begin
                    exception "..." 'Este Cliente tem '||coalesce(:QTD_PREMIOS,0)||' - '||coalesce(:PRODUTO,'')||' e esta faturando '||:quantidade;
                end
             end
          end
       end else
       begin
            update tbprograma_pontos_premiacao c
                    set c.status_nfs = 1,
                    c.nfs_baixa    = 0,
                    c.status       = iif(:baixar_saldo = 1, 1, c.status)
                where c.premio_id  = :premio_id
                and c.empresa_id   = :empresa_codigo
                and c.programa_id  = :programa_pontos_id
                and c.status_nfs   = 2
                and c.nfs_baixa    = :NFS_ID;
       end

   end

  return 1;
end;

COMMENT ON PARAMETER FN_PREMIOS_BAIXAR.FLAG IS
'1 baixar, 2 - desfazer a baixa';

CREATE OR ALTER trigger tbprograma_pontos_premiacao_bi for tbprograma_pontos_premiacao
active before insert position 0
as
declare variable MENSAGEM1 Varchar(7000);
declare variable MENSAGEM2 Varchar(7000);
declare variable descricao Varchar(200);
declare variable empresa Varchar(100);
declare variable produto Varchar(100);
declare variable usuario_id integer;
declare variable email Varchar(300);
declare variable url Varchar(300);
begin

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

    :produto   = coalesce((select first 1 lpad(j.codigo,6,0)||'-'||j.descricao   from tbproduto j, tbprograma_pontos_premios a where a.id = new.premio_id and a.produto_id = j.codigo),'');
    :empresa   = coalesce((select first 1 lpad(z.codigo,6,0)||'-'||z.razaosocial from tbempresa z where z.codigo = new.empresa_id),'');
    :descricao = coalesce((select first 1 x.descricao                            from tbprograma_pontos x where x.id = new.programa_id),'');

    :url = coalesce((select first 1 n.valor_ext from tbcontrole_n n where n.id = 279 ),'https://gc.delfa.com.br');

    :MENSAGEM1 =  'Cliente: '||:empresa||'<br>'||
                  'Prêmio: '||new.quantidade||' x '||:produto||'<br>'||
                  '<a href="'||:url||'/_12260?CLIENTE='||new.empresa_id||'&TIPO=1" target="_blank">Abrir Histórico</a>';
    
     :MENSAGEM2 = '';
    
     for
         select
            u.codigo as usuario_id,
            u.email
         from tbprograma_pontos_notificar n, tbusuario u
         where n.programa_pontos_id = new.programa_id
         and n.status = 1
         and u.status = 1
         and char_length(coalesce(u.email,'')) > 0
         and u.codigo = n.usuario_id
     into
        :usuario_id,
        :email
     do begin
    
        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
               VALUES (1, :usuario_id, 0, 0, :descricao, 0, 0, current_timestamp, -1, current_timestamp, 0, 'TBPROGRAMA_PONTOS_PREMIACAO', new.id, :MENSAGEM1||:MENSAGEM2);
    
    
        INSERT INTO TBEMAIL (EMAIL, USUARIO_ID, ASSUNTO, STATUS, DATAHORA, DATAHORA_ENVIO, FLAG, PAINEL_ID, CORPO)
               VALUES (:email, 0, :descricao, 0, current_timestamp, null, 0, 0, :MENSAGEM1||:MENSAGEM2);
    
    
     end
end;

CREATE OR ALTER trigger ttbpedido_item_b998d for tbpedido_item
active before delete position 998
AS
declare variable pontos integer;
begin

    --validar pontuacao pedidos
    pontos = FN_PONTOS_VALIDAR_PEDIDO(old.pedido, old.controle);

end;
