create table staff (
	code varchar2(10) not null,
	name varchar2(50),
	phone varchar2(20),
	email varchar2(50),
	password varchar2(16),
	rank varchar2(50),
	primary key (code)
);

create table customer (
	code varchar2(10) not null,
	name varchar2(50),
	address varchar2(100),
	phone varchar2(20),
	primary key (code)
);

create table supplier (
	code varchar2(10) not null,
	name varchar2(50),
	address varchar2(100),
	phone varchar2(20),
	email varchar2(50),
	url varchar2(100),
	primary key (code)
);

create table product (
	code varchar2(10) not null,
	category varchar2(50),
	name varchar2(50),
	on_hand integer,
	low_limit integer,
	price number(10,2),
	primary key (code),
	check (on_hand >= 0)
);

create table supplying (
	supplier_code varchar2(10) not null,
	product_code varchar2(10) not null,
	price number(10,2),
	on_hand integer,
	quote date,
	primary key (supplier_code,product_code),
	foreign key (supplier_code) references supplier(code),
	foreign key (product_code) references product(code)
);

create table invoice (
	invoice_number varchar2(10) not null,
	customer_code varchar2(10),
	issue_date date,
	staff_in_charge varchar(10),
	voided integer,
	primary key (invoice_number)
);
			
create table invoice_item (
	unique_id varchar2(20) not null,
	invoice_number varchar2(10),
	product_code varchar2(10),
	price number(10,2),
	qty integer,
	primary key (unique_id),
	foreign key (invoice_number) references invoice,
	foreign key (product_code) references product(code)
);

create table po (
	po_number varchar2(10) not null,
	supplier_code varchar2(10),
	issue_date date,
	staff_in_charge varchar2(10),
	voided integer,
	primary key (po_number),
	foreign key (supplier_code) references supplier(code)
);

create table po_item (
	unique_id varchar2(10),
	po_number varchar2(10),
	product_code varchar2(10),
	price number(10,2),
	qty integer,
	primary key (unique_id),
	foreign key (po_number) references po,
	foreign key (product_code) references product(code)
);