CREATE SEQUENCE seq_article_group start with 11;

CREATE TABLE article_group (
	id int default nextval('seq_article_group') PRIMARY KEY,
	descr varchar(50) NOT NULL,
	parent_id int REFERENCES article_group 
);

CREATE SEQUENCE seq_trade_name start with 2;

CREATE SEQUENCE seq_supplier_order start with 1;

CREATE TABLE trade_name (
	id int DEFAULT nextval('seq_trade_name') PRIMARY KEY,
	descr varchar(50),
	trade_name_foreign boolean DEFAULT false 
);

CREATE TABLE article (
	id varchar(10) PRIMARY KEY,
	descr varchar(50),
	art_group_id int REFERENCES article_group,
	trade_name_id int REFERENCES trade_name,
	comment text,
	combi boolean DEFAULT false
	inventory int DEFAULT 0,
	minimum_inventory int DEFAULT 0
);

CREATE TABLE supplier_order (
	id int DEFAULT nextval('seq_supplier_order') PRIMARY KEY,
	article_id varchar(10),
	quantity int,
	supplier_name varchar(50),
	order_id varchar(50),	
	order_date date
);

ALTER TABLE country ADD FOREIGN KEY (freight_art_id) REFERENCES article;

CREATE TABLE article_history (
  art_id varchar(10) REFERENCES article,
  valid_from date,
  valid_to date,
  netto double precision,
  netto_customer double precision,
  netto_vip_customer double precision,
  points double precision,
  bonus double precision,
  vat_id int,
  PRIMARY KEY(art_id, valid_from)
);
 


CREATE TABLE combi_article_content (
	combi_art_id varchar(10) REFERENCES article,
	art_id varchar(10)  REFERENCES article,
	quantity int NOT NULL,
	PRIMARY KEY(combi_art_id, art_id)
);

CREATE SEQUENCE seq_store;

CREATE TABLE store (
	id int DEFAULT nextval('seq_store') PRIMARY KEY,
	descr varchar(50) NOT NULL,
  	street varchar(50),
	post_code int,
	city varchar(50),
	comment text,
	country_code varchar(3) REFERENCES country
);

ALTER TABLE country ADD FOREIGN KEY (default_store_id) REFERENCES store;

CREATE SEQUENCE seq_campaign;

CREATE TABLE campaign (
	id int DEFAULT nextval('seq_campaign') PRIMARY KEY,
	descr varchar(50) NOT NULL ,
	camp_from date,
	camp_to date,
	comment text
);

CREATE TABLE campaign_article (
	campaign_id int REFERENCES campaign,
	article_id varchar(10) REFERENCES article,
	bonus double precision,
	netto double precision,
	points double precision,
	PRIMARY KEY(campaign_id, article_id)
);
 
CREATE TABLE inventory (
	art_id varchar(10) REFERENCES article,
	store_id int REFERENCES store,
	inventory int,
	PRIMARY KEY (art_id, store_id)
);

CREATE SEQUENCE seq_promotion;
CREATE TABLE promotion (
	id int DEFAULT nextval('seq_promotion') PRIMARY KEY,
	descr varchar(50) NOT NULL,
	prom_from date NULL ,
	prom_to date NULL ,
	condition text, 
	comment text,
	max_delivery int,
	type char(1)
);

CREATE TABLE promotion_article (
	promotion_id int REFERENCES promotion,
	article_id varchar(10) REFERENCES article,
	bonus double precision,
	netto double precision,
	points double precision,
	PRIMARY KEY(promotion_id, article_id)
);

CREATE TABLE promotion_target (
	promotion_id int REFERENCES promotion,
	target_id int,
	PRIMARY KEY(promotion_id, target_id)
);

CREATE SEQUENCE seq_shipping;
CREATE TABLE shipping (
	id int DEFAULT nextval('seq_shipping') PRIMARY KEY ,
	descr varchar(50)
);


