reids.sql

-- Table: order
DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
    id int PRIMARY KEY,
    paymentID int NOT NULL,
    chargeID int NOT NULL
);

-- Table: item
DROP TABLE IF EXISTS item;
CREATE TABLE item(
    id int PRIMARY KEY,
    orderID int,
    name varchar(255),
    price float
);

-- Table: payment
DROP TABLE IF EXISTS payment;
CREATE TABLE payment(
    id int PRIMARY KEY,
    orderID int,
    card_type varchar(255),
    last_4_card_number int,
    zip int,
    cardholder_firstname varchar(255),
    cardholder_lastname varchar(255),
    method varchar(255)
);

-- Table: charges
DROP TABLE IF EXISTS charges;
CREATE TABLE charges(
    id int PRIMARY KEY,
    orderID int NOT NULL,
    date DATETIME,
    subtotal float,
    taxes float,
    total float
);

-- foreign keys
-- Reference: 
DROP TABLE IF EXISTS item_date;
CREATE TABLE item_date AS
SELECT item.id, item.orderID, item.name, item.price, charges.date
FROM item
JOIN charges ON item.orderID = charges.orderID;


DROP TABLE IF EXISTS coffee_by_day;
CREATE TABLE coffee_by_day(
    date DATETIME,
    coffee_count float
);