CREATE TABLE order_status (
	id int PRIMARY KEY,
	descr varchar(100) NOT NULL,
	next_process varchar(100),
	payment_state boolean
);

CREATE SEQUENCE seq_order_type start with 4;

CREATE TABLE order_type (
	id int DEFAULT nextval('seq_order_type') PRIMARY KEY,
	descr varchar(30)
);

CREATE SEQUENCE seq_order_header;

CREATE SEQUENCE seq_bill_id start with 5912;

CREATE TABLE order_header (
	id bigint DEFAULT nextval('seq_order_header') PRIMARY KEY,
	bill_id bigint,
	
	bonus_date date  ,
	order_date date,
	ordered_by int REFERENCES distributor,
	
	comment text,
	payment_status boolean, 
	pay_method_id int REFERENCES payment_method,
	pay_method_descr varchar(50),
	account_id int,
	
	store_descr varchar(50),
	shipping_descr varchar(50),
	order_status_id int REFERENCES order_status,
	order_type int REFERENCES order_type,
	delivery_country_id varchar(2) REFERENCES country(country_post_code),
	delivery_postcode int,
	delivery_city varchar (50),
	delivery_street varchar (50),
	delivery_title varchar(20),
	delivery_name varchar(50),
	billing_title varchar(20),
	billing_name varchar(50),
	billing_country_id varchar(2) REFERENCES country(country_post_code),
	billing_postcode int,
	billing_city varchar(50),
	billing_street varchar(50),
	total_brutto decimal(18,2),
	total_netto decimal(18,2),
	total_bonus decimal(18,2),
	total_points decimal(18,3),
	total_trade_margin decimal(18,2)
);

CREATE SEQUENCE seq_order_status_history;

CREATE TABLE order_status_history (
	id bigint DEFAULT nextval('seq_order_status_history') PRIMARY KEY,
	order_id bigint REFERENCES order_header,
	from_order_status int REFERENCES order_status,
	to_order_status int REFERENCES order_status ,
	from_payment_status boolean,
	to_payment_status boolean,
	status_changed_date timestamp with time zone,
	status_changed_by int REFERENCES distributor
);



CREATE TABLE order_detail (
	order_id bigint REFERENCES order_header,
	comment text,
	art_name varchar(50),
	art_position int,
	quantity int,
	bonus decimal(18,2),
	netto double precision,
	points double precision,
	brutto double precision,
	trade_margin double precision,
	vat_value double precision,
	campaign_id int REFERENCES campaign,
	promotion_id int  REFERENCES promotion,

	PRIMARY KEY (order_id, art_position)
);

CREATE SEQUENCE seq_subscription_header;

CREATE TABLE subscription_header (
	id bigint DEFAULT nextval('seq_subscription_header') PRIMARY KEY,
	descr varchar(50),
	distr_id int REFERENCES distributor,
	creation_date date,
	start_time date,
	auto_extension boolean, 
	operating_time date,
	delivery_intervall int,
	comment text,
	modification_comment text,
	modified_by int REFERENCES distributor,
	next_delivery date,
	pay_method_id int REFERENCES payment_method,
	account_id int
);

CREATE TABLE subscription_detail (
	subscr_id bigint REFERENCES subscription_header,
	art_id varchar(10) REFERENCES article,
	quantity int,
	comment text,
	modification_comment text,
	PRIMARY KEY(subscr_id, art_id)
);







