Database Seeding

"""seed.py — Seed development database with realistic test data."""
import random
from datetime import datetime, timedelta
from faker import Faker
import psycopg2

fake = Faker()
Faker.seed(42)  # Deterministic output for reproducibility
random.seed(42)

DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "dev_db",
    "user": "dev_user",
    "password": "dev_password",
}

NUM_USERS = 50
NUM_PRODUCTS = 30
NUM_ORDERS = 100


def seed():
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    # Seed users
    user_ids = []
    for _ in range(NUM_USERS):
        cur.execute(
            """INSERT INTO users (email, password_hash, full_name, created_at)
               VALUES (%s, %s, %s, %s)
               ON CONFLICT (email) DO NOTHING
               RETURNING id""",
            (
                fake.unique.email(),
                fake.sha256(),
                fake.name(),
                fake.date_time_between(start_date="-2y", end_date="now"),
            ),
        )
        row = cur.fetchone()
        if row:
            user_ids.append(row[0])

    # Seed products
    product_ids = []
    for i in range(NUM_PRODUCTS):
        cur.execute(
            """INSERT INTO products (name, description, price, stock_quantity, sku)
               VALUES (%s, %s, %s, %s, %s)
               ON CONFLICT (sku) DO NOTHING
               RETURNING id""",
            (
                fake.catch_phrase(),
                fake.paragraph(nb_sentences=3),
                round(random.uniform(9.99, 499.99), 2),
                random.randint(0, 500),
                f"SKU-{i+1:05d}",
            ),
        )
        row = cur.fetchone()
        if row:
            product_ids.append(row[0])

    # Seed orders with order items
    statuses = ["pending", "confirmed", "shipped", "delivered"]
    for _ in range(NUM_ORDERS):
        user_id = random.choice(user_ids)
        status = random.choice(statuses)
        items = random.sample(product_ids, k=random.randint(1, 5))
        total = 0.0

        cur.execute(
            """INSERT INTO orders (user_id, status, total_amount, shipping_address, ordered_at)
               VALUES (%s, %s, 0, %s, %s) RETURNING id""",
            (user_id, status, fake.address(), fake.date_time_between("-1y", "now")),
        )
        order_id = cur.fetchone()[0]

        for pid in items:
            qty = random.randint(1, 4)
            price = round(random.uniform(9.99, 499.99), 2)
            total += qty * price
            cur.execute(
                """INSERT INTO order_items (order_id, product_id, quantity, unit_price)
                   VALUES (%s, %s, %s, %s)""",
                (order_id, pid, qty, price),
            )

        cur.execute(
            "UPDATE orders SET total_amount = %s WHERE id = %s", (round(total, 2), order_id)
        )

    conn.commit()
    cur.close()
    conn.close()
    print(f"Seeded {len(user_ids)} users, {len(product_ids)} products, {NUM_ORDERS} orders.")


if __name__ == "__main__":
    seed()

-- seed.sql — Idempotent seed data for local development
-- Run with: psql -U dev_user -d dev_db -f seed.sql

BEGIN;

-- Users
INSERT INTO users (id, email, password_hash, full_name, created_at) VALUES
  (1, 'alice@example.com',  'hash_alice',  'Alice Johnson',  '2024-03-15 09:00:00'),
  (2, 'bob@example.com',    'hash_bob',    'Bob Martinez',   '2024-05-20 14:30:00'),
  (3, 'carol@example.com',  'hash_carol',  'Carol Chen',     '2024-07-01 11:15:00'),
  (4, 'dave@example.com',   'hash_dave',   'Dave Okafor',    '2024-09-10 08:45:00'),
  (5, 'eve@example.com',    'hash_eve',    'Eve Andersson',  '2024-11-28 16:00:00')
ON CONFLICT (id) DO NOTHING;

-- Products
INSERT INTO products (id, name, description, price, stock_quantity, sku) VALUES
  (1, 'Wireless Keyboard',   'Bluetooth mechanical keyboard',  79.99,  150, 'SKU-00001'),
  (2, 'USB-C Hub',           '7-in-1 USB-C docking station',   49.99,  300, 'SKU-00002'),
  (3, 'Noise-Cancelling Headphones', 'Over-ear ANC headphones', 199.99, 75, 'SKU-00003'),
  (4, '4K Monitor',          '27-inch IPS 4K display',         399.99,  40, 'SKU-00004'),
  (5, 'Laptop Stand',        'Adjustable aluminum stand',       34.99, 200, 'SKU-00005')
ON CONFLICT (id) DO NOTHING;

-- Orders
INSERT INTO orders (id, user_id, status, total_amount, shipping_address, ordered_at) VALUES
  (1, 1, 'delivered',  129.98, '123 Oak St, Portland, OR 97201',   '2024-12-01 10:00:00'),
  (2, 2, 'shipped',    199.99, '456 Elm Ave, Austin, TX 78701',    '2025-01-05 14:20:00'),
  (3, 3, 'confirmed',  484.98, '789 Pine Rd, Seattle, WA 98101',   '2025-01-10 09:30:00'),
  (4, 1, 'pending',     49.99, '123 Oak St, Portland, OR 97201',   '2025-01-12 16:45:00')
ON CONFLICT (id) DO NOTHING;

-- Order items
INSERT INTO order_items (id, order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 1, 79.99),
  (2, 1, 2, 1, 49.99),
  (3, 2, 3, 1, 199.99),
  (4, 3, 4, 1, 399.99),
  (5, 3, 5, 1, 34.99),
  (6, 4, 2, 1, 49.99)
ON CONFLICT (id) DO NOTHING;

-- Reset sequences to avoid conflicts with future inserts
SELECT setval('users_id_seq',    (SELECT MAX(id) FROM users));
SELECT setval('products_id_seq', (SELECT MAX(id) FROM products));
SELECT setval('orders_id_seq',   (SELECT MAX(id) FROM orders));
SELECT setval('order_items_id_seq', (SELECT MAX(id) FROM order_items));

COMMIT;