

create table payforms(
payform            char(    3) not null upshift label "Key",
description        char(   20) label "Form of Payment") 
	primary key (payform)
;


create table states(
state              char(    2) not null upshift label "State",
sname              char(   20) label "Name") 
	primary key (state)
;


create table orderlines(
order_nbr          integer not null label "Order Nbr.",
line_nbr           smallint not null format "##" label "Line Nbr.",
item               smallint label "Item Code",
supplier           integer label "Supplier Code",
quantity           smallint format "###" label "Quantity",
discount           smallint label "Discount",
price              money(  9,  2) label "Unit Price") 
	primary key (order_nbr,line_nbr)
;


create table orders(
order_nbr          integer not null label "Order Number",
customer           integer not null label "Customer Code",
order_date         date label "Date of Order",
pay_date           date label "Payment day",
payform            char(    3) upshift label "Form of Payment",
order_status       char(    1) upshift check ($ in ("Y", "N")) label "Invoiced") 
	primary key (order_nbr)
;


create table customers(
customer           integer not null label "Customer Code",
company            char(   25) upshift label "Company",
surname            char(   25) label "Surname",
fname              char(   15) label "Name",
address1           char(   40) label "Address1",
address2           char(   40) label "Address2",
city               char(   15) label "City",
zip                integer format "&&&&&" label "Zip Code",
phone              char(   14) label "Phone",
payform            char(    3) upshift label "Form of Payment",
total_invoice      money( 11,  2) label "Total Invoiced",
state              char(    2) not null upshift default "CA" label "State") 
	primary key (customer)
;
create index i2_customers on customers(company      );


create table suppliers(
supplier           integer not null label "Supplier Key",
company            char(   25) upshift label "Company",
surname            char(   25) label "Surname",
fname              char(   15) label "Name",
address1           char(   25) label "Address1",
address2           char(   25) label "Address2",
city               char(   15) label "City",
zip                integer format "&&&&&" label "Zip Code",
phone              char(   14) label "Phone",
state              char(    2) not null upshift default "CA" label "State") 
	primary key (supplier)
;
create index i2_supplier on suppliers(company      );


create table items(
item               smallint not null label "Item Code",
supplier           integer not null label "Supplier Code",
description        char(   15) label "Description",
retail_price       money(  9,  2) label "Retail Price",
cost_price         money(  9,  2) label "Cost Price",
stock              smallint label "Stocks",
over_stock         smallint label "Over Stock",
under_stock        smallint label "Under Stock",
last_entry_date    date label "Last Entry Date",
last_exit_date     date label "Last Exit Date") 
	primary key (item,supplier)
;
create index i2_items on items(description      );
create index i3_items on items(item      );
create index i4_items on items(supplier      );
alter table orderlines         
	foreign key fk2_lines(order_nbr) 	references orders
	on update restrict 
	on delete restrict 
;

alter table orderlines         
	foreign key fk1_lines(item,supplier) 	references items
	on update restrict 
	on delete restrict 
;

alter table orders             
	foreign key fk2_orders(payform) 	references payforms
	on update restrict 
	on delete restrict 
;

alter table orders             
	foreign key fk1_orders(customer) 	references customers
	on update restrict 
	on delete restrict 
;

alter table suppliers          
	foreign key fk1_suppliers(state) 	references states
	on update restrict 
	on delete restrict 
;

alter table items              
	foreign key fk1_items(supplier) 	references suppliers
	on update restrict 
	on delete restrict 
;


create view invoiced_items(text, totshipped)
as select
		description,
		sum(price * (1 - discount/100) * quantity)
	from orderlines, items, orders
	where
		items.item = orderlines.item and
		orderlines.supplier = items.supplier and
		orders.order_nbr = orderlines.order_nbr and
		orders.order_status = "Y"
	group by 1
;

