-- Database Designed by Andrew Thorsen -- CS 325 - Fall 2017
-- Used by Drew Hengehold 2025
-- Date last mod: 11/12/2017
prompt employee

drop table employee cascade constraints;

--employee contains the info on all staff at the shop
create table employee
(empl_id	char(6) not null,
 empl_lname	varchar2(16) not null,
 empl_fname	varchar2(16) not null,
 empl_discount 	decimal(3, 2) default '0.30',
 empl_salary	integer,
 empl_phone	varchar2(10),
 primary key (empl_id)
);


drop table empl_emails cascade constraints;

-- empl emails is to hold mutiple emails for each employee
create table empl_emails
(empl_id	char(6) not null,
 empl_email	varchar2(60) not null,
 primary key (empl_id, empl_email),
 foreign key (empl_id) references employee
);


-- customer is the superclass for all people who make perchases
drop table customer cascade constraints;

create table customer
(cust_num	char(6) not null,
 cust_type	char(1) check(cust_type in('m', 'g')) not null,
 cust_lname	varchar2(16) not null,
 primary key (cust_num)
);


drop table guest cascade constraints;

-- guest table is for casual patrons
create table guest
(cust_num	char(6) not null,
 primary key (cust_num),
 foreign key (cust_num) references customer
);


drop table member cascade constraints;

--member is for frequent customers who have subscribed

create table member
(cust_num	char(6) not null,
 mem_fname	varchar2(16) not null,
 mem_reward	char(1) check(mem_reward in('v', 'm')),
 mem_fav_tea	varchar2(20),
 mem_email	varchar2(60) not null,
 mem_address	varchar2(50),
 mem_discount	decimal(3, 2) default '0.90',
 primary key (cust_num),
 foreign key (cust_num) references customer
);

drop table reservation cascade constraints;

-- to hold all reservations
create table reservation
(res_num	char(3) not null,
 cust_num	char(6) not null,
 res_time	date not null,
 num_of_guests	smallint,
 primary key (res_num),
 foreign key (cust_num) references customer
);


drop table sale cascade constraints;

--sale it to hold all sale line items and who bought them
create table sale
(sale_num	char(3) not null,
 sale_amt	decimal(4, 2) not null,
 cust_num	char(6) not null,
 empl_id	char(6) not null,
 primary key (sale_num),
 foreign key (cust_num) references customer,
 foreign key (empl_id) references employee
);


drop table sale_item cascade constraints;

--sale_item is the superclass to the items perchasable in store
create table sale_item
(item_id	char(3) not null,
 item_type	char(1) check(item_type in('f', 't')) not null,
 item_name	varchar2(30) not null,
 stock_quant	integer check(stock_quant > 0),
 item_price	decimal(4, 2) check(item_price > 0) not null,
 image_path VARCHAR2(255),
 primary key (item_id)
);


drop table tea cascade constraints;

-- holds tea info
create table tea
(item_id	char(3) not null,
 tea_brand	varchar2(30),
 tea_method	varchar2(6) check(tea_method in('loose', 'bagged')) not null,
 tea_type	varchar2(6) check(tea_type in('red', 'white', 'green', 'black',
						'herbal','fruit')),
primary key(item_id),
foreign key(item_id) references sale_item
);


drop table food cascade constraints;

-- hods food item
create table food
(item_id	char(3) not null,
 primary key(item_id),
 foreign key(item_id) references sale_item
);

drop table sale_line_item cascade constraints;

-- sale line item is to hold what items and the quantity of that item sold
create table sale_line_item
(sale_num	char(10) not null,
 item_id	char(3) not null,
 sale_qnty	integer not null,
 primary key(sale_num, item_id),
 foreign key(sale_num) references sale,
 foreign key(item_id) references sale_item
);

