CREATE TABLE bonus_definition (
	bonus_id int PRIMARY KEY,
	bonus_descr varchar(50),
	event_id int,
	valid_from date,
	valid_to date,
	condition text,
	distr_id int REFERENCES distributor ,
	level_id int REFERENCES user_roles,
	priority int,
	classname varchar(50),
	data varchar (255),
	comment text 
);

create sequence seq_distributor_history;

CREATE TABLE distributor_history (
	distr_hist_id int PRIMARY KEY DEFAULT nextval('seq_distributor_history'),
	distr_id int NOT NULL REFERENCES distributor,
	level_id int NOT NULL REFERENCES user_roles,
	level_from date,
	level_to date
);

CREATE TABLE bonus_metrics (
	distr_hist_id int REFERENCES distributor_history,
	metric varchar (10),
	data  double precision,
	PRIMARY KEY(distr_hist_id, metric) 
);

CREATE SEQUENCE seq_individual_bonus;

CREATE TABLE individual_bonus (
    id integer PRIMARY KEY DEFAULT nextval('seq_individual_bonus'),
    descr character varying(50),
    distr_id int REFERENCES distributor,
    q_metric character varying(6),
    q_type integer,
    q_value numeric(18,3),
    calc_metric character varying(6),
    calc_value numeric(18,3),
    calc_value_type integer,
    calc_type integer,
    valid_from date,
    valid_to date
);

CREATE VIEW distributor_without_first_order as 
	select a.id as Distr_ID,
	a.lastname,
	c.descr as Level,
	sum(b.total_points) as PV 
	from distributor a left outer join
					 order_header b on a.id=b.ordered_by join 
					 user_roles c on a.level_id=c.id and
					 		 a.level_id >200 and partner_type_id=0 and 
					 		 a.id not between 43000000 and
					 		 43001999 and a.id not between 49000000 and 49001999 and 
					 		 a.active=true and
					 		 ((b.order_status_id=60 and b.bonus_date is not null and b.payment_status=true) or (b.ordered_by is null)) and 
					 		 a.id not in (41000503,43002026,43002224,43002237,43002360,43002375,43002405,49002960) 
	group by a.id, 
		a.lastname,
		c.descr 
	having sum(b.total_points) < 50 or 
		sum(b.total_points) is null 
	order by a.id;

 