-- !!!!!!!!!!There  are line breaks, it's just copy of putty screen !!!!!--


-- Contains the current bonus date interval, it must contain one row
 create table t_current_date (date_from date, date_to date);
 
-- It's for order trigger
 create table temp_total_netto (id int primary key, total_netto numeric(18,2));

CREATE VIEW t_worker_bonus AS
 SELECT DISTINCT 
 		d.id, d.personal_netto, d.personal_trade_margin, d.personal_bonus, d.total_netto, d.total_trade_margin, 
 		d.total_bonus, l1.total_netto1, l1.total_bonus1, l2.total_netto2, l2.total_bonus2, l3.total_netto3, l3.total_bonus3, a.sponsor AS active_sponsor,
        CASE
            WHEN d."level" < 400 THEN 0::numeric
            WHEN d.total_bonus < 250::numeric THEN round(100::numeric * d.total_bonus * (0.00 + COALESCE(ib.db0, 0::numeric))) / 100::numeric
            WHEN d.total_bonus < 500::numeric THEN round(100::numeric * d.total_bonus * (0.05 + COALESCE(ib.db1, 0::numeric))) / 100::numeric
            WHEN d.total_bonus < 1500::numeric THEN round(100::numeric * d.total_bonus * (0.10 + COALESCE(ib.db2, 0::numeric))) / 100::numeric
            WHEN d.total_bonus < 3000::numeric THEN round(100::numeric * d.total_bonus * (0.15 + COALESCE(ib.db3, 0::numeric))) / 100::numeric
            ELSE round(100::numeric * d.total_bonus * (0.20 + COALESCE(ib.db4, 0::numeric))) / 100::numeric
        END AS distributor_bonus,
        CASE
            WHEN COALESCE(d.total_bonus,0) >= (250 + COALESCE(ib.tbq, 0))::numeric THEN
            CASE
                WHEN d."level" < 400 THEN 0::numeric
                WHEN l1.total_bonus1 < 10000::numeric THEN round(100::numeric * l1.total_bonus1 * (0.08 + COALESCE(ib.t1b1, 0::numeric))) / 100::numeric
                WHEN l1.total_bonus1 < 20000::numeric THEN round(100::numeric * l1.total_bonus1 * (0.09 + COALESCE(ib.t1b2, 0::numeric))) / 100::numeric
                ELSE round(100::numeric * l1.total_bonus1 * (0.10 + COALESCE(ib.t1b3, 0::numeric))) / 100::numeric
            END
            ELSE 0::numeric
        END AS team_bonus_level1,
        CASE
            WHEN COALESCE(d.total_bonus,0) >= (250 + COALESCE(ib.tbq, 0))::numeric AND l1.total_bonus1 >= (5000 + COALESCE(ib.t2_level1_criteria, 0))::numeric THEN
            CASE
                WHEN d."level" < 400 THEN 0::numeric
                WHEN l2.total_bonus2 < 20000::numeric THEN round(100::numeric * l2.total_bonus2 * (0.05 + COALESCE(ib.t2b1, 0::numeric))) / 100::numeric
                WHEN l2.total_bonus2 < 50000::numeric THEN round(100::numeric * l2.total_bonus2 * (0.06 + COALESCE(ib.t2b2, 0::numeric))) / 100::numeric
                ELSE round(100::numeric * l2.total_bonus2 * (0.07 + COALESCE(ib.t2b3, 0::numeric))) / 100::numeric
            END
            ELSE 0::numeric
        END AS team_bonus_level2,
        CASE
            WHEN COALESCE(d.total_bonus,0) >= (250 + COALESCE(ib.tbq, 0))::numeric AND l1.total_bonus1 >= (5000 + COALESCE(ib.t3_level1_criteria, 0))::numeric AND l2.total_bonus2 >= (10000 + COALESCE(ib.t3_level2_criteria))::numeric THEN
            CASE
                WHEN d."level" < 400 THEN 0::numeric
                WHEN l3.total_bonus3 < 50000::numeric THEN round(100::numeric * l3.total_bonus3 * (0.02 + COALESCE(ib.t3b1, 0::numeric))) / 100::numeric
                WHEN l3.total_bonus3 < 100000::numeric THEN round(100::numeric * l3.total_bonus3 * (0.03 + COALESCE(ib.t3b2, 0::numeric))) / 100::numeric
                ELSE round(100::numeric * l3.total_bonus3 * (0.04 + COALESCE(ib.t3b3, 0::numeric))) / 100::numeric
            END
            ELSE 0::numeric
        END AS team_bonus_level3,
		0.00 + COALESCE(ib.db0, 0::numeric) AS db0, 0.05 + COALESCE(ib.db1, 0::numeric) AS db1, 
        0.10 + COALESCE(ib.db2, 0::numeric) AS db2, 0.15 + COALESCE(ib.db3, 0::numeric) AS db3, 
        0.20 + COALESCE(ib.db4, 0::numeric) AS db4, 250 + COALESCE(ib.tbq, 0) AS tbq, 
        5000 + COALESCE(ib.t2_level1_criteria, 0) AS t2_level1_criteria, 
        5000 + COALESCE(ib.t3_level1_criteria, 0) AS t3_level1_criteria, 10000 + COALESCE(ib.t3_level2_criteria, 0) AS t3_level2_criteria
   FROM t_worker_order d
   JOIN t_team_level1 l1 ON d.id = l1.id
   JOIN t_team_level2 l2 ON d.id = l2.id
   JOIN t_team_level3 l3 ON d.id = l3.id
   JOIN t_worker_activation a ON d.id = a.id
   LEFT JOIN individual_bonus_2 ib ON d.id = ib.distr_id AND now() >= ib.valid_from AND now() <= ib.valid_to
  ORDER BY d.id, d.personal_netto, d.personal_trade_margin, d.personal_bonus, d.total_netto, d.total_trade_margin, d.total_bonus, 
  		l1.total_netto1, l1.total_bonus1, l2.total_netto2, l2.total_bonus2, l3.total_netto3, l3.total_bonus3, a.sponsor,
  		distributor_bonus, team_bonus_level1, team_bonus_level2, team_bonus_level3, 
  		db0, db1, db2, db3, db4, tbq, t2_level1_criteria, t3_level1_criteria, t3_level2_criteria;

CREATE VIEW t_team_level3 AS  SELECT a1.id, a1.sponsor, sum(a2.total_netto2) AS total_netto3, sum(a2.total_bonus2) AS total_bonus3
   FROM t_worker_activation a1
   LEFT JOIN t_team_level2 a2 ON a2.sponsor = a1.id
  GROUP BY a1.id, a1.sponsor;



CREATE VIEW t_team_level2 AS SELECT a1.id, a1.sponsor, sum(a2.total_netto1) AS total_netto2, sum(a2.total_bonus1) AS total_bonus2
   FROM t_worker_activation a1
   LEFT JOIN t_team_level1 a2 ON a2.sponsor = a1.id
  GROUP BY a1.id, a1.sponsor;


CREATE VIEW t_team_level1 AS
  SELECT a1.id, a1.sponsor, sum(a2.total_netto) AS total_netto1, sum(a2.total_bonus) AS total_bonus1
   FROM t_worker_activation a1
   LEFT JOIN (t_worker_activation a2
   JOIN distributor d ON a2.id = d.id AND d.level_id >= 300) ON a2.sponsor = a1.id
  GROUP BY a1.id, a1.sponsor;




create view t_distributor_order as SELECT d.id, sum(h.total_netto) AS total_netto, sum(h.total_bonus) AS total_bonus, sum(h.total_trade_margin) AS total_trade_margin, ncscache.ncsid AS ncs
   FROM distributor d
   LEFT JOIN order_header h ON h.ordered_by = d.id
   LEFT JOIN ncscache ON d.id = ncscache.id
  WHERE h.order_status_id = 60 AND h.order_date >= (select date_from from t_current_date) AND h.order_date < (select date_to from t_current_date)
  GROUP BY d.id, ncscache.ncsid;



create VIEW t_worker_activation as  SELECT two.id, acscache.acsid AS sponsor, two.total_netto, two.total_bonus
   FROM t_worker_order two
   LEFT JOIN acscache ON two.id = acscache.id;


create view t_worker_order as SELECT d.id, max(d1.total_netto) AS personal_netto, max(d1.total_bonus) AS personal_bonus, max(d1.total_trade_margin) AS personal_trade_margin, sum(d2.total_netto) AS total_netto, sum(d2.total_bonus) AS total_bonus, sum(d2.total_trade_margin) AS total_trade_margin, d.sponsor
   FROM distributor d
   LEFT JOIN t_distributor_order d1 ON d1.id = d.id
   LEFT JOIN t_distributor_order d2 ON d2.ncs = d.id
  GROUP BY d.id, d.sponsor;


CREATE OR REPLACE FUNCTION ncscache_update() RETURNS TRIGGER AS $ncscache_update$
BEGIN
   if TG_OP = 'INSERT' then
        INSERT INTO acscache(id, acsid) VALUES(NEW.id, acs(NEW.id));
        INSERT INTO ncscache(id, ncsid) VALUES(NEW.id, ncs(NEW.id));

   ELSEIF TG_OP = 'UPDATE' THEN
        IF NEW.level_id<300 AND OLD.level_id<300 and OLD.sponsor=NEW.sponsor THEN
                RETURN NEW;
        END IF;
        IF NEW.level_id=OLD.level_id AND NEW.active=OLD.active AND NEW.deleted=OLD.deleted AND NEW.sponsor=OLD.sponsor THEN
                return NEW;
        END IF;

                UPDATE ncscache SET ncsid=ncs(id);
   ELSEIF TG_OP='DELETE' THEN
        DELETE FROM acscache WHERE id=OLD.id;
        DELETE FROM ncscache WHERE id=OLD.id;
        RETURN OLD;
   END IF;
   RETURN NEW;
END;
$ncscache_update$ LANGUAGE plpgsql;

CREATE TRIGGER ncscache_update_trigger AFTER INSERT OR UPDATE OR DELETE ON distributor FOR EACH ROW EXECUTE PROCEDURE ncscache_update();

CREATE OR REPLACE FUNCTION acscache_update_before() RETURNS TRIGGER  AS $acscache_update_before$
DECLARE
 v_ordered_by integer;
BEGIN
 if TG_OP = 'INSERT' THEN
    v_ordered_by=NEW.ordered_by;
 ELSE
    v_ordered_by=OLD.ordered_by;
 end if;
   if TG_OP = 'UPDATE' then
    if old.total_netto = new.total_netto and old.order_status_id = new.order_status_id THEN
     RETURN NEW;
    end if;
   end if;

--  CREATE TEMP TABLE oldvalues (id int, total_netto numeric(18,2));

  delete from temp_totalnetto where id=v_ordered_by;
  INSERT INTO temp_totalnetto select id, total_netto from t_worker_order where id=v_ordered_by;
    if TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
 END;
$acscache_update_before$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acscache_update_after() RETURNS TRIGGER AS $acscache_update_after$
DECLARE
 tn_old RECORD;
 tn_new RECORD;
 v_ordered_by integer;
 v_level_id integer;
 v_status60 bool;
BEGIN
 v_status60:=false;
 if TG_OP = 'INSERT' THEN
    if NEW.order_status_id=60 then
        v_status60:=true;
    end if;
    v_ordered_by=NEW.ordered_by;
 ELSE
    v_ordered_by=OLD.ordered_by;
 end if;
  if TG_OP = 'UPDATE' then
   if old.total_netto = new.total_netto and old.order_status_id= new.order_status_id THEN
     RETURN NEW;
   end if;
 if old.order_status_id<60 and new.order_status_id=60 or old.order_status_id=60 and new.order_status_id<>60 then
      v_status60:=true;
  end if;
 end if;

 select into v_level_id level_id from distributor where id=v_ordered_by;
 select into tn_old * from temp_totalnetto where id= v_ordered_by;
 select into tn_new * from t_worker_order where id=v_ordered_by;
 if (tn_old.total_netto = 0 or tn_old.total_netto is null) and tn_new.total_netto>0 or tn_old.total_netto>0 and (tn_new.total_netto=0 or tn_new.total_netto is null) or v_level_id<300 and v_status60 then
--      RAISE NOTICE 'UPDATING ACS values';
        create temp table c_worker_order(id int primary key, total_netto numeric(18,2), sponsor int) without OIDS;
        -- execute 'insert into c_worker_order select id, total_netto, sponsor from t_worker_order';
		execute 'insert into c_worker_order select o.id, coalesce(o.total_netto,0) - coalesce((select min(tbq) from individual_bonus_2 b where b.distr_id = o.id and now() between b.valid_from and b.valid_to), 0), o.sponsor from t_worker_order o';

 		execute 'update acscache set acsid=acs2(id) where id in (select * from st1(ncs(' || v_ordered_by || ')))';
        drop table c_worker_order;
 end if;
-- drop table oldvalues;
 if TG_OP = 'DELETE' THEN
   RETURN OLD;
 end if;

 RETURN NEW;
END;
$acscache_update_after$ LANGUAGE plpgsql;
create trigger acscache_trigger_before BEFORE INSERT OR UPDATE OR DELETE ON order_header FOR EACH ROW EXECUTE PROCEDURE acscache_update_before();
create trigger acscache_trigger_after AFTER INSERT OR UPDATE OR DELETE ON order_header FOR EACH ROW EXECUTE PROCEDURE acscache_update_after();



-- same as acs, but it works from a temp table due to performance reasons..
CREATE OR REPLACE FUNCTION acs2(integer) RETURNS integer
    AS $_$
declare
  row RECORD;
  dist_id integer;
begin
   dist_id := $1;
   while dist_id <> 0 loop
     EXECUTE 'select * from c_worker_order where id=' || dist_id  into row;
     if row.id is NULL then
       return 0;
     end if;
     if row.total_netto > 0 and dist_id <> $1 then
       return dist_id;
     end if;
     dist_id := row.sponsor;
    end loop;
    return 0;
end;
$_$
    LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION checkandfixcache() RETURNS void
AS $$
DECLARE
 c_ncs int;
 c_acs int;
BEGIN
        c_ncs:=0;
        RAISE NOTICE 'Checking NCS values';
        select count(*) into c_ncs from ncscache where ncsid<>ncs(id);
        if c_ncs>0 then
                RAISE NOTICE 'Invalid NCS values. Fixing them..';
                update ncscache set ncsid=ncs(id);

        end if;

        RAISE NOTICE 'Checking ACS values';

        create temp table c_worker_order(id int primary key, total_netto numeric(18,2), sponsor int) without OIDS;
        -- execute 'insert into c_worker_order select id, total_netto, sponsor from t_worker_order';
		execute 'insert into c_worker_order select o.id, coalesce(o.total_netto,0) - coalesce((select min(tbq) from individual_bonus_2 b where b.distr_id = o.id and now() between b.valid_from and b.valid_to), 0), o.sponsor from t_worker_order o';

        c_acs:=0;
        execute 'select count(*) from acscache where acsid<>acs2(id)' into c_acs;
        if c_acs>0 then
                RAISE NOTICE 'Invalid acs values. Fixing them...';
                execute 'update acscache set acsid=acs2(id)';
        end if;

        drop table c_worker_order;

END;
$$ LANGUAGE plpgsql;

