CREATE SEQUENCE seq_level_address start with 2;

CREATE TABLE level_address (
	id int DEFAULT nextval('seq_level_address') PRIMARY KEY,
	descr varchar(50)
);


CREATE SEQUENCE seq_title;

CREATE TABLE title (
	id int DEFAULT nextval('seq_title') PRIMARY KEY,
	descr varchar(20)
);

CREATE SEQUENCE seq_payment_method start with 6;

CREATE TABLE payment_method (
	id int DEFAULT nextval('seq_payment_method') PRIMARY KEY,
	descr varchar(50) NOT NULL
);

CREATE TABLE distributor_type (
	id char(1) PRIMARY KEY,
	descr varchar(25)
);

CREATE TABLE user_roles (
	id int PRIMARY KEY,
	descr varchar(40),
	dist_type char(1) REFERENCES distributor_type
);



CREATE TABLE distributor (
	country_code varchar(3) NOT NULL REFERENCES country,
	id int  PRIMARY KEY,
	level_address_id int REFERENCES  level_address,
	firstname varchar(25),
	lastname varchar(25),
	password varchar (25),
	birthdate date,
	
	tel1 varchar (25),
	tel2 varchar (25),
	titel_id int REFERENCES title,
	mobil varchar (25),
	email varchar (50),
	fax varchar (50),
	language_id int REFERENCES language DEFAULT 1,
	level_id int REFERENCES user_roles NOT NULL, 

	sponsor int REFERENCES distributor,
	creation_date date NOT NULL,
	last_order_date timestamp with time zone,
	pay_method_id int REFERENCES payment_method DEFAULT 4,
	comment text,
	VAT_ID varchar(30),
	transferbonus boolean DEFAULT true,
	active boolean DEFAULT true,
	printlock boolean DEFAULT false,
	statusdate date,
	shipping_id int REFERENCES shipping DEFAULT 1,
	couple_partner varchar (50),
	deleted boolean DEFAULT false,
	www varchar(50),
	homepage date,
	company varchar(50),
	old_distr boolean DEFAULT false,
	customer_service boolean DEFAULT false,
	newsletter boolean  DEFAULT true
	
);

CREATE SEQUENCE seq_bank_account;

CREATE TABLE bank_account (
	id int DEFAULT nextval('seq_bank_account') PRIMARY KEY ,
	distr_id int REFERENCES distributor NOT NULL,
	bank_code varchar(50),
	iban varchar(30),
	account_number varchar(25) NOT NULL,
	bank varchar(50),
	default_bill boolean NOT NULL,
	comment text,
	default_bonus boolean NOT NULL
);

CREATE SEQUENCE seq_address;

CREATE TABLE address (
	id int DEFAULT nextval('seq_address') PRIMARY KEY ,
	distr_id int NOT NULL REFERENCES distributor,
	private boolean NOT NULL,
	comment text,
	country_post_code varchar(2) REFERENCES country(country_post_code) DEFAULT 'A',
	postcode int NOT NULL,
	city varchar(50) NOT NULL,
	street varchar(50) NOT NULL,
	default_delivery boolean NOT NULL,
	default_bill boolean NOT NULL,
	delivery_name varchar(50)
);

CREATE TABLE evolution_internet_addresses (
	url varchar(255)
);

CREATE SEQUENCE seq_cc_org start with 3;

CREATE TABLE cc_org (
	id int DEFAULT nextval('seq_cc_org') PRIMARY KEY,
	org varchar(50) NOT NULL
);

CREATE SEQUENCE seq_creditcard;

CREATE TABLE creditcard (
	id int DEFAULT nextval('seq_creditcard') PRIMARY KEY,
	distr_id int NOT NULL REFERENCES distributor,
	cc_org_id int REFERENCES cc_org,
	cc_number varchar(50) NOT NULL,
	valid_from date, 
	comment text,
	valid_to date
);
