seed_complex_postgres.sql

-- Complex, multi-table schema designed specifically to stress-test joins.
-- Run this in the Supabase SQL Editor (or any Postgres you've connected).
-- It deliberately includes "gaps" (customers with no orders, products never
-- ordered, an empty category, products with no reviews) so LEFT/RIGHT JOIN
-- questions actually have something interesting to surface.

DROP TABLE IF EXISTS reviews, order_items, orders, products, categories, customers CASCADE;

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category_id INTEGER REFERENCES categories(id),
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  country TEXT NOT NULL,
  signup_date DATE NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  order_date DATE NOT NULL,
  status TEXT NOT NULL  -- 'completed' | 'pending' | 'cancelled'
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  product_id INTEGER REFERENCES products(id),
  customer_id INTEGER REFERENCES customers(id),
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT
);

-- 5 categories, one of which (Outdoor) will end up with zero products.
INSERT INTO categories (name) VALUES
('Electronics'),('Home & Kitchen'),('Books'),('Toys'),('Outdoor');

-- 12 products. Products 11 and 12 will never appear in order_items
-- (never-ordered products, for LEFT/RIGHT JOIN tests).
INSERT INTO products (name, category_id, price) VALUES
('Wireless Mouse', 1, 24.99),
('Mechanical Keyboard', 1, 89.99),
('USB-C Hub', 1, 34.50),
('Blender', 2, 59.00),
('Air Fryer', 2, 119.99),
('Cookbook: Quick Meals', 3, 18.50),
('Sci-Fi Novel Set', 3, 45.00),
('Building Blocks', 4, 29.99),
('Remote Control Car', 4, 39.99),
('Puzzle 1000pc', 4, 14.99),
('Camping Tent (never ordered)', 5, 149.99),
('Hiking Backpack (never ordered)', 5, 79.99);
-- Note: category_id 5 (Outdoor) intentionally has no products yet... but
-- since we just gave it 2, swap the test: Outdoor exists with unordered
-- products, and there is NO category with zero products by default —
-- run `DELETE FROM products WHERE category_id = 5;` after seeding if you
-- specifically want to test an empty-category LEFT JOIN.

-- 10 customers. Customers 9 and 10 will never place an order.
INSERT INTO customers (name, email, country, signup_date) VALUES
('Alice Johnson','alice@example.com','USA','2024-01-15'),
('Bob Smith','bob@example.com','USA','2024-02-20'),
('Carla Diaz','carla@example.com','Mexico','2024-03-05'),
('David Kim','david@example.com','South Korea','2024-03-22'),
('Elena Petrova','elena@example.com','Russia','2024-04-10'),
('Farid Hassan','farid@example.com','Egypt','2024-05-01'),
('Grace Lin','grace@example.com','Taiwan','2024-05-19'),
('Hiro Tanaka','hiro@example.com','Japan','2024-06-02'),
('Ivy Chen (no orders)','ivy@example.com','Canada','2024-06-15'),
('Jack Brown (no orders)','jack@example.com','UK','2024-06-28');

-- Orders for customers 1-8 only (9 and 10 stay order-less).
INSERT INTO orders (customer_id, order_date, status) VALUES
(1,'2025-01-05','completed'),
(1,'2025-03-12','completed'),
(2,'2025-01-20','completed'),
(2,'2025-04-02','cancelled'),
(3,'2025-02-01','completed'),
(4,'2025-02-15','completed'),
(4,'2025-05-01','pending'),
(5,'2025-02-28','completed'),
(6,'2025-03-10','completed'),
(7,'2025-03-15','completed'),
(7,'2025-06-01','completed'),
(8,'2025-04-20','completed');

-- Order items — only referencing products 1-10 (11 & 12 stay unordered).
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1,1,2,24.99),(1,3,1,34.50),
(2,2,1,89.99),
(3,4,1,59.00),
(4,5,1,119.99),
(5,6,3,18.50),
(6,7,2,45.00),
(7,8,1,29.99),
(8,9,1,39.99),(8,10,2,14.99),
(9,1,1,24.99),(9,2,1,89.99),
(10,4,2,59.00),
(11,6,1,18.50),(11,7,1,45.00),
(12,3,3,34.50);

-- Reviews — only for products 1,2,4,6,7,9 (the rest have zero reviews,
-- including the never-ordered ones — good for a LEFT JOIN avg-rating test).
INSERT INTO reviews (product_id, customer_id, rating, comment) VALUES
(1,1,5,'Great mouse, very responsive'),
(1,3,4,'Works well'),
(2,2,5,'Best keyboard I have owned'),
(4,3,3,'Decent blender, a bit noisy'),
(6,5,4,'Good recipes, easy to follow'),
(6,7,5,'Loved it'),
(7,6,4,'Solid story collection'),
(9,4,2,'Remote control stopped working after a week');