drop table shop_customer;
create table shop_customer
(
	cust_id    int primary key auto_increment,
	user       varchar(20) NOT NULL,
	pswd       varchar(20) NOT NULL,
	fname      varchar(30) NOT NULL,
	lname      varchar(30) NOT NULL,
	address    varchar(99) NOT NULL,
	email      varchar(99) NOT NULL,
	phone      varchar(20)
);  


drop table shop_product;
create table shop_product
(
	prod_id    int primary key auto_increment,
	prod_desc  varchar(99)  NOT NULL,
	prod_type  varchar(25)  NOT NULL,
	prod_avail char(1)      NOT NULL,
	prod_price decimal(6,2) NOT NULL
);  
insert into shop_product values(0,'Vanilla',         'ice cream','Y',2.50);
insert into shop_product values(0,'Chocolate',       'ice cream','Y',2.50);
insert into shop_product values(0,'Strawberry',      'ice cream','Y',2.50);
insert into shop_product values(0,'Butter Pecan',    'ice cream','Y',3.00);
insert into shop_product values(0,'Praline',         'ice cream','Y',3.00);
insert into shop_product values(0,'Pistachio',       'ice cream','Y',3.00);
insert into shop_product values(0,'Coffee',          'ice cream','Y',3.00);
insert into shop_product values(0,'Rocky Road',      'ice cream','Y',3.00);
insert into shop_product values(0,'Cherry Jubilee',  'ice cream','Y',3.00);
insert into shop_product values(0,'French Vanilla',  'ice cream','N',3.00);
insert into shop_product values(0,'Chocolate Chip',  'ice cream','N',3.00);
insert into shop_product values(0,'Cookies & Cream', 'ice cream','N',3.00);
insert into shop_product values(0,'Double Chocolate','ice cream','N',3.50);
insert into shop_product values(0,'Chocolate Fudge', 'ice cream','N',3.50);
insert into shop_product values(0,'Almond Fudge',    'ice cream','N',3.00);
insert into shop_product values(0,'Peanut Butter',   'ice cream','N',3.00);
insert into shop_product values(0,'Nutty Coconut',   'ice cream','N',3.00);
insert into shop_product values(0,'Rainbow Sherbet', 'ice cream','Y',2.75);
insert into shop_product values(0,'Orange Sherbet',  'ice cream','N',2.75);
insert into shop_product values(0,'Lemon Sherbet',   'ice cream','N',2.75);
insert into shop_product values(0,'Hot Fudge',      'topping','Y',0.25);
insert into shop_product values(0,'Caramel',        'topping','Y',0.25);
insert into shop_product values(0,'Sprinkles',      'topping','Y',0.25);
insert into shop_product values(0,'Nuts',           'topping','Y',0.25);
insert into shop_product values(0,'Whipped Cream',  'topping','Y',0.25);
insert into shop_product values(0,'Cherries',       'topping','Y',0.25);
insert into shop_product values(0,'Hot Fudge Sunday','specialty','Y', 5.50);
insert into shop_product values(0,'Banana Split',    'specialty','Y', 6.25);
insert into shop_product values(0,'Waffle Cone',     'specialty','Y', 0.75);
insert into shop_product values(0,'Ice Cream Cake',  'specialty','N',21.50);


drop table shop_cart;
create table shop_cart
(
	item_id   int primary key auto_increment,
	cust_id   int          NOT NULL,
	prod_id   int          NOT NULL,
	prod_desc varchar(99)  NOT NULL,
	type      varchar(25)  NOT NULL,
	price     decimal(6,2) NOT NULL,
	qty       int          NOT NULL
);


drop table shop_order;
create table shop_order
(
	order_id    int primary key auto_increment,
	cust_id     int         NOT NULL,
	ship_fname  varchar(30) NOT NULL,
	ship_lname  varchar(30) NOT NULL,
	ship_addr   varchar(99) NOT NULL,
	ship_email  varchar(99) NOT NULL,
	credit_card varchar(50) NOT NULL,
	order_date  timestamp   NOT NULL DEFAULT NOW() 
);


drop table shop_order_detail;
create table shop_order_detail
(
	order_dtl_id int primary key auto_increment,
	order_id     int          NOT NULL,
	prod_id      int          NOT NULL,
	prod_desc    varchar(99)  NOT NULL,
	type         varchar(25)  NOT NULL,
	price        decimal(6,2) NOT NULL,
	qty          int          NOT NULL,
	tax          decimal(6,4) NOT NULL	
);