DROP TABLE auth_level;
DROP SEQUENCE level_no;
DROP TABLE domains;
DROP SEQUENCE domain_no;
DROP TABLE staff;
DROP SEQUENCE staff_no;
DROP TABLE customer_status;
DROP SEQUENCE cs_no;
DROP TABLE invoice_status;
DROP SEQUENCE is_no;
DROP TABLE customer_type;
DROP SEQUENCE ctype_no;
DROP SEQUENCE customer_no;
DROP TABLE customer;
DROP TABLE address;
DROP SEQUENCE address_no;
DROP TABLE order_status;
DROP SEQUENCE order_status_no;
DROP TABLE price;
DROP SEQUENCE price_no;
DROP TABLE package_definition;
DROP SEQUENCE package_no;
DROP TABLE products;
DROP SEQUENCE product_no;
DROP TABLE package_consists_of;
DROP TABLE order_consists_of;
DROP TABLE order_consists_of;
DROP TABLE renewal_period;
DROP SEQUENCE renewal_no;
DROP TABLE orders;
DROP SEQUENCE order_no;
DROP TABLE invoice;
DROP SEQUENCE invoice_no;
DROP TABLE card_name;
DROP SEQUENCE card_no;
DROP TABLE card_type;
DROP SEQUENCE ct_no;
DROP TABLE customer_card;
DROP SEQUENCE ccard_no;
DROP TABLE remittance;
DROP SEQUENCE remittance_no;
DROP TABLE invisible_orders;
DROP TABLE web_log;
DROP SEQUENCE web_log_no;
DROP TABLE contactinfo;
DROP SEQUENCE contact_no;
DROP TABLE payment_type;
DROP SEQUENCE pt_no;
DROP TABLE product_description;
DROP TABLE telephone;
DROP SEQUENCE telephone_no;
DROP TABLE orders_update_logs;
DROP SEQUENCE oul_log_no;

CREATE TABLE auth_level (
level_no	integer NOT NULL UNIQUE,
level_date	timestamp DEFAULT NOW(),
auth_short	char(30),
auth_descript	char(30),
staff_read	boolean DEFAULT FALSE,
staff_write	boolean DEFAULT FALSE,
customer_read	boolean DEFAULT FALSE,
customer_write	boolean DEFAULT FALSE,
invoice_read	boolean DEFAULT FALSE,
invoice_write	boolean DEFAULT FALSE,
products_read	boolean DEFAULT FALSE,
products_write	boolean DEFAULT FALSE,
deleted		boolean DEFAULT FALSE,
UNIQUE		(level_no, level_date),
PRIMARY KEY	(level_no, level_date)
);
CREATE SEQUENCE level_no;
CREATE INDEX idx_auth_level ON auth_level (level_no, level_date);
INSERT INTO auth_level VALUES (1, NOW(), 'ADMINISTRATOR', 'Administrator', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE);
INSERT INTO auth_level VALUES (2, NOW(), 'READ', 'Read only', TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE);
INSERT INTO auth_level VALUES (3, NOW(), 'SALES', 'Sales', TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE);
INSERT INTO auth_level VALUES (4, NOW(), 'SALES_MANAGER', 'Maganager',  TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE);
INSERT INTO auth_level VALUES (5, NOW(), 'OPERATOR', 'Operator',  TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE);
INSERT INTO auth_level VALUES (6, NOW(), 'CUSTOMER_SUPPORT', 'Customer support', TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE);
INSERT INTO auth_level VALUES (7, NOW(), 'RESELLER', 'Reseller', TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE);
CREATE TABLE domains (
domain_no	integer NOT NULL UNIQUE,
domain_date	timestamp DEFAULT NOW(),
domain		char(20),
domain_name	varchar(100),
domain_data	varchar(255),
deleted		boolean DEFAULT FALSE,
UNIQUE		(domain_no, domain_date),
PRIMARY KEY (domain_no, domain_date)
);
CREATE SEQUENCE domain_no START 4;
CREATE INDEX idx_domain ON domains (domain_no, domain_date);

INSERT INTO domains VALUES (1, NOW(), 'FreeZone', 'freezone.co.uk', '', FALSE);
INSERT INTO domains VALUES (2, NOW(), 'DomainExpress', 'domainexpress.co.uk', '', FALSE);
INSERT INTO domains VALUES (3, NOW(), 'PurpleNet', 'purplenet.co.uk', '', FALSE);

CREATE TABLE staff (
staff_no	integer NOT NULL UNIQUE,
staff_nick	char(32) NOT NULL,
password	char(32) NOT NULL,
domain_no	integer NOT NULL REFERENCES domains (domain_no),
name		char(20),
telephone	char(20),
email		char(64),
is_manager	boolean NOT NULL DEFAULT FALSE,
auth_level	integer NOT NULL REFERENCES auth_level (level_no),
last_logon	timestamp DEFAULT NOW(), 
last_logoff	timestamp DEFAULT NULL,
last_action	timestamp DEFAULT NOW(),
deleted		boolean DEFAULT FALSE,
PRIMARY KEY (staff_no)
);
CREATE SEQUENCE staff_no START 4;
CREATE INDEX idx_staff ON staff (staff_no, staff_nick);
INSERT INTO staff VALUES (1, 'tony', 'warlock', 1, 'Antonio Novakovic', '+38164/11-674-52', 'tony@rcub.bg.ac.yu', FALSE,  1, NULL, NULL, NOW(), FALSE);
INSERT INTO staff VALUES (2, 'sasha','warlock', 2, 'Sasa Mirkovic',     '+38111/3414-519',  'mirko@rcub.bg.ac.yu', FALSE, 1, NULL, NULL, NOW(), FALSE);
INSERT INTO staff VALUES (3, 'djos', 'warlock', 3, 'Djordje Starcevic', '+38111/667-090',   'djos@rcub.bg.ac.yu', TRUE, 1, NULL, NULL, NOW(), FALSE);

CREATE TABLE customer_status (
status_no	integer NOT NULL UNIQUE,
status_date	timestamp DEFAULT NOW(),
short_status	char(20),
full_status	char(100),
deleted		boolean DEFAULT FALSE,
UNIQUE		(status_no, status_date),
PRIMARY KEY	(status_no, status_date)
);
CREATE SEQUENCE cs_no;
CREATE INDEX idx_cstatus ON customer_status (status_no, status_date);

CREATE TABLE invoice_status (
status_no	integer NOT NULL UNIQUE,
status_date	timestamp DEFAULT NOW(),
short_status	char(20),
full_status	char(100),
deleted		boolean DEFAULT FALSE,
UNIQUE		(status_no, status_date),
PRIMARY KEY	(status_no, status_date)
);
CREATE SEQUENCE is_no;
CREATE INDEX idx_istatus ON invoice_status (status_no, status_date);

CREATE TABLE customer_type (
type_no		integer NOT NULL UNIQUE,
type_date	timestamp DEFAULT NOW(),
short_type	char(30),
full_type	char(100),
deleted		boolean DEFAULT FALSE,
UNIQUE		(type_no, type_date),
PRIMARY KEY	(type_no, type_date)
);
CREATE SEQUENCE ctype_no;
CREATE INDEX idx_customer_type ON customer_type (type_no, type_date);

CREATE TABLE customer (
customer_no	integer NOT NULL UNIQUE,
customer_date	timestamp DEFAULT NOW(),
email		char(100) NOT NULL,
status_no	integer NOT NULL REFERENCES customer_status (status_no),
type_no		integer NOT NULL REFERENCES customer_type (type_no),
domain_no	integer NOT NULL REFERENCES domains (domain_no),
cust_name	char(60),
cust_surname	char(60),
organization	char(100),
last_date	timestamp,
contact		char(100),
password	char(32),
staff_no	integer NOT NULL REFERENCES staff (staff_no),
discount	decimal(10,2),
refer		integer,
deleted		boolean DEFAULT FALSE,
UNIQUE		(customer_no, customer_date),
PRIMARY KEY	(customer_no, customer_date)
);
CREATE SEQUENCE customer_no;
CREATE INDEX idx_customer ON customer (customer_no, customer_date);

CREATE TABLE address (
address_no	integer NOT NULL UNIQUE,
address_date	timestamp DEFAULT NOW(),
customer_no	integer NOT NULL REFERENCES customer (customer_no),
street_1	varchar(100),
street_2	varchar(100),
street_3	varchar(100),
city		varchar(50),
postcode	char(7),
county		char(50),
country		varchar(30),
deleted		boolean DEFAULT FALSE,
UNIQUE		(address_no, address_date),
PRIMARY KEY (address_no, address_date)
);
CREATE SEQUENCE address_no;
CREATE INDEX idx_address ON address (address_no, address_date);

CREATE TABLE order_status (
status_no	integer NOT NULL UNIQUE,
status_date	timestamp DEFAULT NOW(),
status		char(30),
note		varchar(200),
deleted		boolean DEFAULT FALSE,
UNIQUE		(status_no, status_date),
PRIMARY KEY	(status_no, status_date)
);
CREATE SEQUENCE order_status_no;
CREATE INDEX idx_order_status ON order_status (status_no, status_date);

CREATE TABLE price (
price_no	integer   NOT NULL UNIQUE,
price_date	timestamp DEFAULT NOW(),
price		decimal(16, 2),
price_once	decimal(16, 2),
price_monthly	decimal(16, 2),
price_quarterly	decimal(16, 2),
price_yearly	decimal(16, 2),
price_biyearly	decimal(16, 2),
deleted		boolean DEFAULT FALSE,
UNIQUE 		(price_no, price_date),
PRIMARY KEY (price_no, price_date)
);
CREATE SEQUENCE price_no;
CREATE INDEX idx_price ON price (price_no, price_date);

CREATE TABLE package_definition (
package_no		integer NOT NULL UNIQUE,
package_date		timestamp DEFAULT NOW() UNIQUE,
domain_no		integer NOT NULL REFERENCES domains (domain_no),
name			char(30),
package_short_desc	char(50),
package_description	varchar(100),
renewal_period		integer,
price_no		integer REFERENCES price (price_no),
invisible		boolean DEFAULT FALSE,
deleted			boolean DEFAULT FALSE,
UNIQUE			 (package_no, package_date),
PRIMARY KEY (package_no, package_date)
);
CREATE SEQUENCE package_no;
CREATE INDEX idx_package_def ON package_definition (package_no, package_date);

CREATE TABLE renewal_period (
renewal_no		integer NOT NULL UNIQUE,
renewal_date		timestamp DEFAULT NOW(),
renewal_text_short	char(20),
renewal_text		varchar(100),
renewal_valid		integer NOT NULL,
deleted			boolean DEFAULT FALSE,
UNIQUE			 (renewal_no, renewal_date),
PRIMARY KEY (renewal_no, renewal_date)
);
CREATE SEQUENCE renewal_no START 5;
CREATE INDEX idx_renewal ON renewal_period (renewal_no, renewal_date);
INSERT INTO renewal_period VALUES (0, NOW(), 'no renewal', 'No renewal period', 0);
INSERT INTO renewal_period VALUES (1, NOW(), 'monthly', 'Monthly payment period', 1);
INSERT INTO renewal_period VALUES (2, NOW(), 'quarterly', 'Quarterly payment period', 4);
INSERT INTO renewal_period VALUES (3, NOW(), 'yearly', 'Yearly payment period', 12);
INSERT INTO renewal_period VALUES (4, NOW(), 'biyearly', 'Biyearly payment period', 24);

CREATE TABLE products (
product_no	integer NOT NULL UNIQUE,
product_date	timestamp DEFAULT NOW(),
domain_no	integer NOT NULL REFERENCES domains (domain_no),
name		char(50),
short_descript	char(50),
description	varchar(250),
price_no	integer,
renewal_period  integer,
product_table	varchar(50),
deleted		boolean DEFAULT FALSE,
UNIQUE		(product_no, product_date),
PRIMARY KEY	(product_no, product_date)
);
CREATE SEQUENCE product_no;
CREATE INDEX idx_products ON products (product_no, product_date);

CREATE TABLE package_consists_of (
package_no	integer NOT NULL REFERENCES package_definition (package_no),
package_date	timestamp DEFAULT NOW(),
product_no	integer REFERENCES products (product_no),
quantity	decimal(16,2),
deleted		boolean DEFAULT FALSE,
UNIQUE		(package_no, product_no),
PRIMARY KEY	(package_no, product_no)
);
CREATE INDEX idx_package_cof ON package_consists_of (package_no, product_no);

CREATE TABLE orders (
order_no	integer NOT NULL UNIQUE,
order_date	timestamp NOT NULL DEFAULT NOW() UNIQUE,
cust_no		integer NOT NULL REFERENCES customer (customer_no),
domain_no	integer NOT NULL REFERENCES domains (domain_no),
price_no	integer REFERENCES price (price_no),
renewal_no	integer REFERENCES renewal_period (renewal_no),
status_no	integer NOT NULL REFERENCES order_status (status_no),
discount	decimal(16,2) NOT NULL,
description	varchar(100),
notes		varchar(500),
order_agent	integer NOT NULL REFERENCES staff (staff_no),
contact		varchar(200),
invisible	boolean DEFAULT FALSE,
deleted		boolean DEFAULT FALSE,
UNIQUE		(order_no, order_date),
PRIMARY KEY	(order_no, order_date)
);
CREATE SEQUENCE order_no;
CREATE INDEX idx_orders ON orders (order_no, order_date);

CREATE TABLE order_consists_of (
order_no	integer NOT NULL REFERENCES orders (order_no),
order_date	timestamp DEFAULT NOW(),
data_type	char(10),
data_no		integer,
quantity	decimal(16,2),
price_no	integer REFERENCES price(price_no),
deleted		boolean DEFAULT FALSE,
UNIQUE		(order_no, order_date, data_type, data_no),
PRIMARY KEY	(order_no, order_date, data_type, data_no)
);
CREATE INDEX idx_order_cof ON package_consists_of (order_no, data_type, data_no);


CREATE TABLE invoice (
invoice_no	integer NOT NULL UNIQUE,
invoice_date	timestamp NOT NULL,
order_no	integer NOT NULL REFERENCES orders (order_no),
issue_no	char(20) NOT NULL,
price		decimal(16,2) NOT NULL,
status_no	integer NOT NULL REFERENCES invoice_status (status_no),
automatic_renewal integer REFERENCES renewal_period (renewal_no),
to_print	boolean DEFAULT TRUE,
payed		boolean DEFAULT FALSE,
payed_date	timestamp,
staff_no	integer REFERENCES staff (staff_no),
deleted		boolean DEFAULT FALSE,
UNIQUE		(invoice_no, invoice_date),
PRIMARY KEY	(invoice_no, invoice_date)
);
CREATE SEQUENCE invoice_no;
CREATE INDEX idx_invoice ON invoice (invoice_no, invoice_date);

CREATE TABLE card_name (
card_no		integer NOT NULL UNIQUE,
card_date	timestamp DEFAULT NOW(),
card_short	char(30) NOT NULL,
note		varchar(200),
deleted		boolean DEFAULT FALSE,
UNIQUE		(card_no, card_date),
PRIMARY KEY	(card_no, card_date)
);
CREATE SEQUENCE card_no START 4;
CREATE INDEX idx_card_name ON card_name (card_no, card_date);
INSERT INTO card_name VALUES (1, NOW(), 'Visa', 'Visa card', FALSE);
INSERT INTO card_name VALUES (2, NOW(), 'Masters', 'Masters card', FALSE);
INSERT INTO card_name VALUES (3, NOW(), 'Diners', 'Diners card', FALSE);

CREATE TABLE card_type (
type_no		integer NOT NULL UNIQUE,
type_date	timestamp DEFAULT NOW(),
type		char(50),
note		varchar(200),
deleted		boolean DEFAULT FALSE,
UNIQUE		(type_no, type_date),
PRIMARY KEY	(type_no, type_date)
);
CREATE SEQUENCE ct_no;
CREATE INDEX idx_card_type ON card_type (type_no, type_date);

CREATE TABLE customer_card (
ccard_no	integer NOT NULL UNIQUE,  
ccard_date	timestamp DEFAULT NOW(),
customer_no	integer NOT NULL REFERENCES customer (customer_no),
card_no		integer NOT NULL REFERENCES card_name (card_no),
card_type	integer REFERENCES card_type (type_no),
name_on_card	char(100) NOT NULL,
card_expire	timestamp NOT NULL,
card_number	char(30) NOT NULL,
deleted		boolean DEFAULT FALSE,
UNIQUE		 (ccard_no, ccard_date),
PRIMARY KEY (ccard_no, ccard_date)
);
CREATE SEQUENCE ccard_no;
CREATE INDEX idx_customer_card ON customer_card (ccard_no, ccard_date);

CREATE TABLE remittance (
remittance_no		integer NOT NULL,
remittance_date		timestamp DEFAULT NOW(),
invoice_no		integer NOT NULL REFERENCES invoice (invoice_no),
invoice_date		timestamp NOT NULL,
amount			decimal(16,2) NOT NULL,
payment_type		integer NOT NULL,
ccard_no		integer NOT NULL REFERENCES customer_card (ccard_no),
cheque_no		char(50) NOT NULL,
issue_no		char(50) NOT NULL,
expiry			timestamp,
status			integer,
automatic_payment	boolean,
pdq			integer,
epdq			integer,
UNIQUE 			(remittance_no, remittance_date),
PRIMARY KEY (remittance_no, remittance_date)
);
CREATE SEQUENCE remittance_no;
CREATE INDEX idx_remittance ON remittance (remittance_no, remittance_date);

CREATE TABLE invisible_orders (
order_no	integer NOT NULL REFERENCES orders (order_no),
invisible_date	timestamp NOT NULL DEFAULT NOW(),
frozen		boolean,
cancel_date	timestamp,
suspend_date	timestamp,
UNIQUE		(order_no, invisible_date),
PRIMARY KEY	(order_no, invisible_date)
);
CREATE INDEX idx_invisible_orders ON invisible_orders (order_no, invisible_date);

CREATE TABLE web_log (
log_no		integer NOT NULL PRIMARY KEY,
log_date	timestamp NOT NULL DEFAULT NOW(),
from_ip		inet,
script		varchar(50) NOT NULL,
parameters	varchar(4096) NOT NULL
);
CREATE SEQUENCE web_log_no;
CREATE INDEX idx_web_log ON web_log (log_no);

CREATE TABLE contactinfo (
contact_no	integer NOT NULL,
contact_date	timestamp DEFAULT NOW(),
customer_no	integer REFERENCES customer (customer_no),
domain_no	integer NOT NULL REFERENCES domains (domain_no),
contact_text	varchar,
category	integer,
staff_no	integer NOT NULL REFERENCES staff (staff_no),
deleted		boolean DEFAULT FALSE,
UNIQUE		(contact_no, contact_date),
PRIMARY KEY	(contact_no, contact_date)
);
CREATE SEQUENCE contact_no;
CREATE INDEX idx_contactinfo ON contactinfo (contact_no, contact_date);


CREATE TABLE payment_type (
type_no		integer NOT NULL,
type_date	timestamp DEFAULT NOW(),
short_type	char(20),
full_type	char(100),
deleted		boolean DEFAULT FALSE,
UNIQUE		(type_no, type_date),
PRIMARY KEY	(type_no, type_date)
);
CREATE SEQUENCE pt_no START 3; 
CREATE INDEX idx_payment_type ON payment_type (type_no, type_date);
INSERT INTO payment_type VALUES (1, NOW(),'Cache', 'Payed in cache!', FALSE);
INSERT INTO payment_type VALUES (2, NOW(),'Check', 'Payed with check!', FALSE);

CREATE TABLE product_description (
product_no	integer NOT NULL REFERENCES products (product_no),
column_no	integer,
column_name	char(20),		
column_type	char(20),	
short_desc	char(50),
description	varchar(250),
default_data	varchar(250),
range		varchar(250),
deleted		boolean DEFAULT FALSE,
PRIMARY KEY	(product_no, column_name)
);
CREATE INDEX idx_product_description ON product_xxx (product_no, product_date);

CREATE TABLE telephone (
telephone_no	integer NOT NULL,
telephone_date	timestamp DEFAULT NOW(),
customer_no	integer NOT NULL REFERENCES customer (customer_no),
telephone	char(25),
fax		char(25),
deleted		boolean DEFAULT FALSE,
UNIQUE		(telephone_no, telephone_date),
PRIMARY KEY (telephone_no, telephone_date)
);
CREATE SEQUENCE telephone_no;
CREATE INDEX idx_telephone ON telephone (telephone_no, telephone_date);

CREATE TABLE orders_update_logs (
log_no		integer NOT NULL PRIMARY KEY,
order_no	integer NOT NULL REFERENCES orders (order_no),
order_date	timestamp NOT NULL REFERENCES ORDERS (order_date),
lold		varchar(1000) NOT NULL,
lnew		varchar(1000) NOT NULL
);
CREATE SEQUENCE oul_log_no;
CREATE INDEX idx_oul ON orders_update_logs (log_no);

DROP VIEW proizvodi;
CREATE VIEW proizvodi AS SELECT SUBSTR(a.name, 1, 20), b.column_name, b.column_no FROM products a, product_description b WHERE b.product_no=a.product_no ORDER BY a.name, b.column_no;
GRANT ALL ON proizvodi TO PUBLIC;
