#
# Data about customer ...
# pretpostavljate sta je ovo ... sve nase musterije :)
# mislim da bi ovu tabelu trebalo prosiriti!
#
DROP TABLE customer;
CREATE TABLE customer (
customer_no	integer NOT NULL,
status		integer NOT NULL,
custtype	integer NOT NULL,
organization	char(100),
address_1	char(50),
address_2	char(50),
address_3	char(50),
postcode	char(7),
telephone	char(20),
fax		char(20),
email		char(64),
last_date	date,
contact		char(100),
password	char(64),
cust_agent	integer,
discount	double,
refer		integer,
card_no		char(20),
card_expire	date,
card_type	integer,
card_name	integer,
PRIMARY KEY	(customer_no)
);

#
# Nemam pojma sta je ... deluje mi kao da je to proviyija koju oni dobijaju!
#
DROP TABLE remittance;
CREATE TABLE remittance (
invoice_no	integer NOT NULL,
amount		double NOT NULL,
invoice_date	date NOT NULL,
payment_type	integer NOT NULL,
card_type	integer NOT NULL,
card_no		char(20) NOT NULL,
card_name	char(50) NOT NULL,
cheque_no	char(50) NOT NULL,
issue_no	char(50) NOT NULL,
remittance_no	integer	NOT NULL,
expiry		date,
card_issue_no	integer,
status		integer,
automatic_payment	boolean,
pdq		integer,
epdq		integer,
PRIMARY KEY	(invoice_no)
);

#
# Sve primljene porudzbine
#
DROP TABLE orders;
CREATE TABLE orders (
order_no	integer NOT NULL,
custkey		integer NOT NULL,
package		integer	NOT NULL,
order_date	date NOT NULL,
period		date NOT NULL,
price		double NOT NULL,
status		integer NOT NULL,
discount	double NOT NULL,
description	char(100),
notes		varchar,
order_agent	integer NOT NULL,
contact		char(100),
orig_price	double,
telephone	char(20),
fax		char(20),
email		char(64),
price_now	double NOT NULL,
price_monthly	double,
price_quarterly	double,
price_yearly	double,
price_biyearly	double,
address_1	char(64) NOT NULL,
address_2	char(64),
address_3	char(64),
postcode	char(7),
block_no	char(10),
fix_price	double,
fix_status	integer,
fix_new		boolean,
domain_name	char(64),
invisible	boolean,
PRIMARY KEY	(order_no)
);

#
# Razlika sa prethodnom veryijom je ta da se sve porudzbine nalaze u orders
# tabeli, a ako iz nekog razloga treba da budu nevidljive (obrisane, istekla
# im vaznost, suspendovane, ...) postavlja se FLAG u orders tabelu, a u
# tabeli orders_invisible se postavlja slog ciji je primarni kljuc order_no
# ustvari broj porudzbine iz orders tabele ... i podaci o datumu brisanja ... 
# 
DROP TABLE invisible_orders;
CREATE TABLE orders (
order_no	integer NOT NULL,
frozen		boolean,
cancel_date	date,
suspend_date	date,
PRIMARY KEY	(order_no)
);

#
# Kada se nesto promeni u orders tabeli dodaje se slog u log tabelu
#
DROP TABLE orders_update_logs;
CREATE TABLE orders_update_logs (
order_no	integer NOT NULL,
order_date	date NOT NULL,
updates		varchar NOT NULL,
new		varchar NOT NULL,
PRIMARY KEY	(order_no, order_date)
);
#
# Kartica .. za sta li je ovo ?
# meni ovo deluje kao nalog da se odradi ono sto je klijent zeleo!
# (bez obzira da li je placeno ili ne ... ili ako je placeno
# ticket moze da se izda automatski)
#
DROP TABLE ticket;
CREATE TABLE ticket (
ticket_no	integer NOT NULL,
ticket_date	date NOT NULL,
modification_date date NOT NULL,
staff_nick	integer NOT NULL,
status		integer,
category	integer NOT NULL,
type		integer NOT NULL,
area		integer NOT NULL,
title		char(50),
description	char(100),
custkey		char(50),
additional_description	char(100),
staff_nick_assigned_by	integer,
staff_nick_escalated_by	integer,
identity	char(20),
domain		integer,
PRIMARY KEY	(ticket_no)
);

#
# LOG WEB interfejsa
#
DROP TABLE web_log;
CREATE TABLE web_log (
log_no		integer NOT NULL,
from_ip		char(15),
log_date	date NOT NULL,
script		char(64) NOT NULL,
command		varchar NOT NULL,
);

#
# Kontakt ...
# Svaki put kada nas klijent kontaktira upisuje se kratak
# opis njegovih zelja ovde
#
DROP TABLE contactinfo;
CREATE TABLE contactinfo (
contact_no	integer NOT NULL,
custkey		integer,
contact_date	date,
contact_text	varchar,
category	integer,
staff		integer,
PRIMARY KEY	(contact_no)
);

#
# Ovo mi stvarno nije jasno !!!
#
DROP TABLE campaign;
CREATE TABLE campaign (
campaign_no	integer NOT NULL,
date_start	date,
date_end	date,
description	varchar,
campaign_id	integer,
PRIMARY KEY	(campaign)
);

#
# Operateri, njihovi podaci i nivoi privilegija
# Oko privilegija treba da se dogovorimo!
#     1 - read only
#     2 - r/w
#     3 - modify staff data 
#     4 - ...
#
DROP TABLE staff;
CREATE TABLE staff (
staff_no	integer NOT NULL,
nick		char(20) NOT NULL,
password	char(20) NOT NULL,
name		char(20),
telephone	char(20),
email		char(64),
is_manager	boolean NOT NULL DEFAULT=FALSE,
auth_level	integer,
PRIMARY KEY	(staff_nick)
);

#
# Ako ne gresim ovo je profaktura napravljena za odredjenu uslugu!
# Treba samo proveriti koji su im bitni podaci za profakturu ...
# mozda se nesto promenilo :)
#
DROP TABLE invoice;
CREATE TABLE invoice (
invoice_no	integer NOT NULL,
order_no	integer NOT NULL,
issue_no	integer NOT NULL,
amount		double NOT NULL,
invoice_date	date NOT NULL,
status		integer,
to_print	boolean,
credit		boolean,
first_invoice	integer,
lpd_m		char(20),
lpd_q		char(20),
lpd_y		char(20),
lpd_by		char(20),
lpd_s		char(20),
print_date	date,
PRIMARY KEY	(invoice_no)
);

#
# Customer status
#
DROP TABLE customer_status;
CREATE TABLE customer_status (
status_no		integer NOT NULL,
short_status	char(20),
full_status	vhar(100),
PRIMARY KEY	(status_no)
);

#
# Customer type
#
DROP TABLE customer_type;
CREATE TABLE customer_type (
type_no		integer NOT NULL,
short_type	char(20),
full_type	vhar(100),
PRIMARY KEY	(type_no)
);

#
# Payment type
#
DROP TABLE payment_type;
CREATE TABLE payment_type (
payment_no	integer NOT NULL,
short_type	char(20),
full_type	vhar(100),
PRIMARY KEY	(payment_no)
);
INSERT INTO payment_type VALUES (1,"Cache", "Payed in cache!");
INSERT INTO payment_type VALUES (2,"Check", "Payed with some check!");

#
# Card type ... 
#
DROP TABLE card_type;
CREATE TABLE card_type (
type_no		integer NOT NULL,
card		char(20),
note		char(100),
PRIMARY KEY	(type_no, card)
);

#
# Card named ... Masters / Visa / Diners / ...
#
DROP TABLE card_name;
CREATE TABLE card_name (
type_no		integer NOT NULL,
card		char(20),
note		char(100),
PRIMARY KEY	(type_no)
);
INSERT INTO card_name VALUES (1,"Visa", "Visa card");
INSERT INTO card_name VALUES (2,"Masters", "Masters card");
INSERT INTO card_name VALUES (3,"Diners", "Diners card");

#
# Order status
#
DROP TABLE order_status;
CREATE TABLE order_status (
status_no	integer NOT NULL,
status		char(30),
note		char(100),
PRIMARY KEY	(status_no)
);

#
# Authoriyation level ..
#
DROP TABLE auth_level;
CREATE TABLE auth_level (
level_no	integer NOT NULL,
auth_short	char(30),
auth_descript	char(200),
PRIMARY KEY	(level_no)
);

#
# PACKAGE ... consists of some products
# it does not matter what kind of products :)
# deo sa cenama mi je pao na pamet dok sam bio na cucavcu (samo da znate :)
#
DROP TABLE package_definition;
CREATE TABLE package_definition (
package_no	integer NOT NULL,
package_name	char(30),
package_short_desc	char(50),
package_description	varchar,
price_now	double NOT NULL,
price_monthly	double,
price_quarterly	double,
price_yearly	double,
price_biyearly	double,
invisible	boolean,
PRIMARY KEY	(package_no)
);

# 
# U prethodnoj tabeli se nalazi opis pakovanja .. a ovde se nalazi spisak
# proizvoda koji pripadaju pakovanju!!!
#
DROP TABLE package_consists_of;
CREATE TABLE package_consists_of (
package_no	integer NOT NULL,
product_no	integer,
PRIMARY KEY	(package_no, product_no)
);

#
# Products data ...
#
DROP TABLE products;
CREATE TABLE products (
product_no	integer NOT NULL,
name		char(50),
short_descript	char(200),
descpription	varchar,
product_table	char(30),
PRIMARY KEY	(product_no)
);

#
# A ovo sad je primer ... sta treba da postoji za svaki proizvod ...
#
DROP TABLE vazduh;
CREATE TABLE vazduh (
no		integer NOT NULL,	# r/b
column_name	char(20),		# Naziv kolone u tabeli proizvoda
column_type	char(20),		# Tip polja (moze, a i ne mora)
edit_text	char(50),		# Tekst koji pise pre edit polja na HTML stranici
short_desc	char(50),		# Kratak opis .. moze se pokazivati kao tooltip
description	varchar,		# Dugacak opis ... mozda za help
default_data	varchar,		# Default podatak za to polje
range_start	char(10),		# Ovo je zanimljivo za select boxove 
range_stop	char(10),		# (definises sta ispisuje u slbx)
PRIMARY KEY	(no)
);

#
# Na osnovu podataka iz prethodne tabele se kreira tabela o proizvodu!
#