Build a Bookstore Database

-- MILESTONE 1 — Build the BookNook bookstore schema
-- Four tables, linked by primary keys (PK) and foreign keys (FK).

-- customers: one row per shopper. The PK uniquely identifies each row.
CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,          -- PK: unique id for each customer
    full_name     TEXT    NOT NULL,             -- NOT NULL = required, no blanks
    email         TEXT    NOT NULL UNIQUE,       -- UNIQUE = no two customers share one
    joined_on     DATE    NOT
...

-- MILESTONE 2 — Insert sample rows so we have something to query.
-- Insert PARENTS before CHILDREN: customers + products first, then orders,
-- then order_items (a FK can only point at a row that already exists).

INSERT INTO customers (customer_id, full_name, email, joined_on) VALUES
    (1, 'Ada Lovelace',   'ada@example.com',   '2025-01-10'),
    (2, 'Alan Turing',    'alan@example.com',  '2025-02-02'),
    (3, 'Grace Hopper',   'grace@example.com', '2025-02-20');

INSERT INTO products (pro
...

-- MILESTONE 3a — Revenue per category
-- JOIN three tables, multiply price x quantity, then total it per category.

SELECT
    p.category,
    SUM(p.price * oi.quantity)  AS revenue   -- money earned per book line
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id   -- attach the book's price + category
GROUP BY p.category                               -- one output row per category
ORDER BY revenue DESC;

-- ✅ Expected result:
--   Tech  | 110.50   (39.00 + 32.50 + 39.00)
--  
...

-- MILESTONE 3b — Top customers by amount spent
-- Walk customers → orders → order_items → products to add up each spend.

SELECT
    c.full_name,
    COUNT(DISTINCT o.order_id)   AS orders,      -- how many orders they placed
    SUM(p.price * oi.quantity)   AS total_spent  -- their lifetime spend
FROM customers c
JOIN orders       o  ON o.customer_id = c.customer_id
JOIN order_items  oi ON oi.order_id   = o.order_id
JOIN products     p  ON p.product_id  = oi.product_id
GROUP BY c.customer_id, 
...

-- MILESTONE 4 — Speed up the hot queries with indexes.
-- An index is a lookup structure (like a book's index) the database can scan
-- instead of reading every row. Index the columns you FILTER or JOIN on.

-- We JOIN order_items to orders on order_id and to products on product_id a LOT:
CREATE INDEX idx_items_order   ON order_items(order_id);
CREATE INDEX idx_items_product ON order_items(product_id);

-- We frequently look up orders by customer and filter by status:
CREATE INDEX idx_orders_cu
...

-- MILESTONE 5a — A VIEW: save a query under a name and reuse it.
-- A view is a stored SELECT. It holds no data of its own — it re-runs each
-- time you query it, so it's always up to date.

CREATE VIEW order_totals AS
SELECT
    o.order_id,
    o.customer_id,
    o.status,
    SUM(p.price * oi.quantity)  AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products    p  ON p.product_id  = oi.product_id
GROUP BY o.order_id, o.customer_id, o.status;

-- Now query 
...

-- MILESTONE 5b — A TRANSACTION: place an order atomically.
-- A transaction groups statements so they ALL succeed or ALL roll back.
-- "Atomic" = no half-finished state. Perfect for "take stock + record sale".

BEGIN TRANSACTION;

-- 1) Create the order header
INSERT INTO orders (order_id, customer_id, ordered_on, status)
VALUES (104, 3, '2025-04-10', 'pending');

-- 2) Add a line item: 2 copies of "Clean Code" (product 11)
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (104, 1
...

-- MILESTONE 6 — Advanced touch: rank best-sellers WITHIN each category.
-- A CTE (the WITH block) names a sub-result. A WINDOW FUNCTION (RANK() OVER...)
-- computes a value across a group of rows WITHOUT collapsing them with GROUP BY.

WITH sales AS (                                  -- CTE: revenue per book
    SELECT
        p.category,
        p.title,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.cate
...