# Database design / test for "Don't cook , get it Delivered!"
#
# Methodology - test out table design / fields needed
#

drop database if exists orders;
create database orders;
use orders;

# a) Restaurants

create table restaurant (
        rid int primary key not null auto_increment,
        r_name text,
        r_street text,
        r_town text,
        r_pc text,
        r_phone text,
        r_website text,
        r_webdirectory text,
        r_o_email text,
        r_a_email text,
        r_rtid int
        );

insert into restaurant (rid,r_name,r_street,r_town,r_pc,
        r_phone,r_website,r_webdirectory,r_o_email,
        r_a_email,r_rtid)
        values
        (1,"The Spotted Dick","4 Bold Street","Newport",
        "NP19 4OU","01633 665511","http://www.dick.gov.uk",
        "spotdick","order@dick.gov.uk","gerald@richard.gov.uk",
        101),
        (2,"The Refa","Market Place","Melksham","SN12 7GG",
        "01225 706580","http://www.puffdragon.co.uk",
        "puffrefa","order@puffdragon.co.uk","gias@puffdragon.co.uk",
        102),
        (3,"The Sultan","43 Bath Road","Melksham","SN12 4RE",
        "01225 665544","http://www.sultangrub.co.uk",
        "sultan","sendme@sultangrub.co.uk","patel@yahoo.co.uk",102);

create table restaurant_type (
        rtid int primary key not null auto_increment,
        rt_name text
        );

insert into restaurant_type ( rtid, rt_name) values
        (101, "Pudderies"),
        (102, "Indian"),
        (103, "Chinese"),
        (104, "Juice bar");

# b) Categories within restaurants

create table category (
        cid int primary key not null auto_increment,
        c_name text ,
        c_rid int,
        c_sortval int
        );

insert into category (cid, c_name, c_rid, c_sortval)
        values
        (201, "Starters", 2, 1),
        (202, "Chef Specials", 2, 2),
        (203, "Rices", 2, 4),
        (204, "Mains", 2, 3),
        (205, "Veggies", 2, 5),
        (206, "Appetisers", 3, 1),
        (207, "Currys", 3, 2),
        (208, "Rices", 3, 4),
        (209, "Baltis", 3, 3),
        (210, "Veggies", 3, 5),
        (215, "Desserts", 1, 1),
        (216, "Toppings", 1, 3);

# c) Products withing categories

create table product (
        pid int primary key not null auto_increment,
        p_name text ,
        p_desc text ,
        p_cid int,
        p_price int,
        p_sortval int,
        p_veggie enum ('Y','N')
        );

insert into product (pid, p_name, p_desc, p_cid,
        p_price, p_sortval, p_veggie) values
        (301, "Onion Bhajee", "Deep fried battered onion bits",
                 201, 295, 2, 'Y'),
        (302, "Cow Caches", "Chunks of cow in pastry",
                201, 445, 1, 'N'),
        (303, "Prawn Balls", "Shellfish testes",
                201, 435, 3, 'N'),
        (304, "Onion Bhajee", "Onion rings soaked in coconut sauce",
                 206, 390, 2, 'Y');

select * from ((product join category on cid = p_cid)
        join restaurant on rid = c_rid)
        join restaurant_type on rtid = r_rtid
        where rid = 2
        order by rid, c_sortval, p_sortval;

# d) Options
create table optionz (
        oid int primary key not null auto_increment,
        o_name text
        );

# ALTERABLE BY THE ORDERER

# e) Restautant Review
# create table r_review (
# int rrid primary key not null auto_increment,
# text rr_text
# );
#
# # f) Product Review
# create table p_review (
# int prid primary key not null auto_increment,
# text pr_text
# );
#
# # g) account info
# create table account (
# int aid primary key not null auto_increment,
# text a_name
# );
#
# # h) Transaction info
# create table transaction (
# int tid primary key not null auto_increment,
# );
#
# # i) Line items
# create table line (
# int lid primary key not null auto_increment,
# );
#