seed.py

"""Seed a small SQLite sales database for the agent to query.

Run once:
    python -m src.seed
"""
from __future__ import annotations

import os
import random
import sqlite3
from datetime import date, timedelta
from pathlib import Path

from dotenv import load_dotenv

load_dotenv()

DB_PATH = Path(os.getenv("DB_PATH", "data/sales.db"))


SCHEMA_SQL = """
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL,
    email         TEXT    NOT NULL,
    phone         TEXT    NOT NULL,
    city          TEXT    NOT NULL,
    country       TEXT    NOT NULL,
    segment       TEXT    NOT NULL CHECK (segment IN ('SMB', 'Enterprise', 'Consumer')),
    signup_date   DATE    NOT NULL
);

CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL,
    category      TEXT    NOT NULL,
    unit_price    REAL    NOT NULL,
    in_stock      INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date    DATE    NOT NULL,
    status        TEXT    NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE order_items (
    item_id       INTEGER PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES orders(order_id),
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL,
    unit_price    REAL    NOT NULL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date     ON orders(order_date);
CREATE INDEX idx_items_order     ON order_items(order_id);
CREATE INDEX idx_items_product   ON order_items(product_id);
"""


CUSTOMERS = [
    ("Acme Robotics",       "Istanbul", "TR", "Enterprise"),
    ("Blue Whale Cafe",     "Izmir",    "TR", "SMB"),
    ("Cosmo Foods",         "Ankara",   "TR", "Enterprise"),
    ("Delta Studio",        "Bursa",    "TR", "SMB"),
    ("Eagle Logistics",     "Berlin",   "DE", "Enterprise"),
    ("Fjord Apparel",       "Oslo",     "NO", "SMB"),
    ("Greenline Pharma",    "London",   "UK", "Enterprise"),
    ("Harbor Books",        "Lisbon",   "PT", "SMB"),
    ("Iris Cosmetics",      "Paris",    "FR", "Consumer"),
    ("Jade Travel",         "Madrid",   "ES", "Consumer"),
    ("Kappa Hardware",      "Munich",   "DE", "SMB"),
    ("Lunar Records",       "Amsterdam","NL", "Consumer"),
]


PRODUCTS = [
    ("Wireless Mouse",      "Electronics",  24.90),
    ("Mechanical Keyboard", "Electronics", 129.00),
    ("4K Monitor 27\"",     "Electronics", 349.00),
    ("USB-C Hub",           "Electronics",  39.50),
    ("Standing Desk",       "Furniture",   429.00),
    ("Ergonomic Chair",     "Furniture",   289.00),
    ("Desk Lamp",           "Furniture",    59.00),
    ("Coffee Beans 1kg",    "Grocery",      18.00),
    ("Green Tea 250g",      "Grocery",       9.50),
    ("Dark Chocolate Bar",  "Grocery",       4.20),
    ("Notebook A5",         "Stationery",    6.00),
    ("Gel Pen Set",         "Stationery",   12.50),
    ("Yoga Mat",            "Sports",       45.00),
    ("Running Shoes",       "Sports",      119.00),
    ("Backpack 25L",        "Sports",       79.00),
]


STATUSES = ["pending", "shipped", "delivered", "cancelled"]
STATUS_WEIGHTS = [5, 10, 70, 15]  # most orders delivered


def main() -> None:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    if DB_PATH.exists():
        DB_PATH.unlink()

    conn = sqlite3.connect(DB_PATH)
    conn.executescript(SCHEMA_SQL)

    random.seed(42)
    today = date(2026, 6, 1)

    # Customers
    for i, (name, city, country, segment) in enumerate(CUSTOMERS, start=1):
        signup = today - timedelta(days=random.randint(60, 900))
        slug = name.lower().replace(" ", ".").replace("\"", "")
        tld = "tr" if country == "TR" else "com"
        email = f"contact@{slug}.{tld}"
        # Deterministic Turkish-format mobile number.
        phone = f"+90 5{random.randint(30, 59):02d} {random.randint(100, 999)} {random.randint(10, 99)} {random.randint(10, 99)}"
        conn.execute(
            "INSERT INTO customers VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (i, name, email, phone, city, country, segment, signup.isoformat()),
        )

    # Products
    for i, (name, category, price) in enumerate(PRODUCTS, start=1):
        in_stock = 0 if random.random() < 0.1 else 1
        conn.execute(
            "INSERT INTO products VALUES (?, ?, ?, ?, ?)",
            (i, name, category, price, in_stock),
        )

    # Orders + items
    order_id = 0
    item_id = 0
    n_orders = 120
    for _ in range(n_orders):
        order_id += 1
        cust_id = random.randint(1, len(CUSTOMERS))
        days_ago = random.randint(0, 180)
        order_date = today - timedelta(days=days_ago)
        status = random.choices(STATUSES, weights=STATUS_WEIGHTS, k=1)[0]
        conn.execute(
            "INSERT INTO orders VALUES (?, ?, ?, ?)",
            (order_id, cust_id, order_date.isoformat(), status),
        )

        n_items = random.randint(1, 4)
        chosen = random.sample(range(1, len(PRODUCTS) + 1), n_items)
        for product_id in chosen:
            item_id += 1
            qty = random.randint(1, 5)
            # snapshot of price at order time (small jitter)
            base_price = PRODUCTS[product_id - 1][2]
            unit_price = round(base_price * random.uniform(0.95, 1.05), 2)
            conn.execute(
                "INSERT INTO order_items VALUES (?, ?, ?, ?, ?)",
                (item_id, order_id, product_id, qty, unit_price),
            )

    conn.commit()

    # Quick summary
    print(f"Seeded: {DB_PATH}")
    for table in ["customers", "products", "orders", "order_items"]:
        (count,) = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()
        print(f"  {table:<12} {count:>4} rows")
    conn.close()


if __name__ == "__main__":
    main()