# Demonstration of six tables for a simple
# ordering system ...

# For the shop: store, aisle, product tables
# For the customer: customer, request, item tables

# Note - all fields start with same letter in each table
# Note - all tables have a unique id field
# Note - all tables first letter in unique
# Note - all table names are singular

# Note - sample data includes 2 records at each level so
# that we can test the dynamics but keep it as simple as poss

drop database if exists shopper;
create database shopper;
use shopper;

# Store, Aisle, Products

create table store (
        sid int primary key not null auto_increment,
        s_name text);
insert into store (sid, s_name) values
        (301,"The Emporium"),
        (302,"Cleaning made Easy");

create table aisle (
        aid int primary key not null auto_increment,
        a_sid int,
        a_name text);
insert into aisle (aid, a_sid, a_name) values
        (201, 301, "African Products"),
        (202, 301, "Chinese Products"),
        (203, 302, "Products for Bathrooms"),
        (204, 302, "Products for Outhouses");

create table product (
        pid int primary key not null auto_increment,
        p_aid int,
        p_name text,
        p_price int);
# To add later - purchase cost, VAT, etc
# Potential further tables - assoc. products,
# stock levels, shipments due etc.
insert into product (pid, p_aid, p_name, p_price) values
        (101, 201, "Rhino Horn", 100000),
        (102, 201, "Biltong", 500),
        (103, 202, "Sweet and Sour Soup", 130),
        (104, 202, "Flied Lice", 120),
        (105, 203, "Squeegee", 287),
        (106, 204, "Spiderweb", 5);

select * from (store join aisle on sid = a_sid) join
        product on aid = p_aid ;

# ------------------------------------------------

create table customer (
        cid int primary key not null auto_increment,
        c_name text,
        c_addy text );
insert into customer (cid, c_name, c_addy) values
        (601, "Jean James", "501 Tight Street"),
        (602, "Joe Public", "10 Market Place");

create table request (
        rid int primary key not null auto_increment,
        r_cid int);
# To add - date placed, status, shipped, etc
insert into request (rid, r_cid) values
        (501,601),(502,602),(503,601) ;

create table item (
        iid int primary key not null auto_increment,
        i_rid int,
        i_pid int,
        i_count int);
# Assuming unit sales - not half a pound of potatoes
insert into item (iid, i_rid, i_pid, i_count) values
        (401, 501, 101, 3),
        (402, 502, 102, 1),
        (403, 502, 101, 1),
        (404, 503, 103, 1),
        (405, 503, 104, 2),
        (406, 503, 106, 15);
# Make sure (at this stage) data is clean
# Some orders with one item and some with several
# Some products not on any order

# Should be able to formulate queries as follows:

# Product listings
# Customer listings
# Order listings
# Order pricing
# Products that have not been ordered
# Products by popularity (income)
# Products by popularity (quantity)
# Products by popularity (number of orders they are on)

# But first ... join all tables - "The ultimate join"

select c_name,p_name,i_count,p_price,a_name,s_name from
        (((( customer
        join request on cid = r_cid )
        join item on rid=i_rid)
        join product on pid = i_pid)
        join aisle on aid = p_aid)
        join store on sid = a_sid;

# Better structured - see as follows

select c_name,p_name,i_count,p_price,a_name,s_name from
         ((store
         join aisle on sid = a_sid)
         join product on aid = p_aid)
         join
         ((customer
         join request on cid = r_cid )
         join item on rid=i_rid)
         on pid = i_pid ;

# And here are some of the others!
# Look for all the orphan records (never-ordered products)

select s_name,a_name,p_name,i_count from
         ((store
         join aisle on sid = a_sid)
         join product on aid = p_aid)
         left join item on pid=i_pid where i_count is NULL;

# Order pricing - firstly make up the order (i.e. order by)
# Then group items by order id - BEWARE - [[example - i_count]]
# some fields appear CORRUPTED as only first value reported

select c_name,rid,i_count,sum(i_count) as n_items,
        sum(i_count*p_price) as req_total from

         (((customer
         join request on cid = r_cid )
         join item on rid=i_rid)
         join product on i_pid = pid)
         group by rid
         order by rid;

# Order value taken grouped by product sorted by value

select p_name,p_price/100 as price,sum(i_count) as count,
        sum(p_price*i_count/100) as total
        from product left join item on pid = i_pid
        group by p_name
        order by total desc;

# Dump out tables for completeness

select * from store;
select * from aisle;
select * from product;
select * from customer;
select * from request;
select * from item;