make_test_db.py

"""
make_test_db.py — creates a small sample SQLite database with two tables:
  - users  (20 users)
  - orders (100 orders, deliberately skewed: user_id=1 owns ~80% of them)

This is purely TEST data, so we can verify Chitral preserves the same
skewed distribution when it generates synthetic (fake) data.

Run with:
    python3 make_test_db.py
"""

import sqlite3
import random
import os

DB_PATH = "test_data.db"

# Remove old test db if it exists, so we start fresh
if os.path.exists(DB_PATH):
    os.remove(DB_PATH)

conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()

# ---- Create tables ----
cur.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
)
""")

cur.execute("""
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
)
""")

# ---- Insert 20 users ----
users = [(i, f"user_{i}", f"user_{i}@example.com") for i in range(1, 21)]
cur.executemany("INSERT INTO users (id, name, email) VALUES (?, ?, ?)", users)

# ---- Insert 100 orders, SKEWED: user_id=1 gets ~80 of them ----
order_id = 1
orders = []

# 80 orders for user_id = 1 (the "heavy" user)
for _ in range(80):
    amount = round(random.uniform(10, 500), 2)
    orders.append((order_id, 1, amount))
    order_id += 1

# remaining 20 orders spread across the other 19 users
for _ in range(20):
    user_id = random.randint(2, 20)
    amount = round(random.uniform(10, 500), 2)
    orders.append((order_id, user_id, amount))
    order_id += 1

cur.executemany("INSERT INTO orders (id, user_id, amount) VALUES (?, ?, ?)", orders)

conn.commit()
conn.close()

print(f"Created {DB_PATH} with 20 users and 100 orders.")
print("user_id=1 owns 80 of the 100 orders (80% skew) — this is what we'll check Chitral reproduces.")
print(f"Full path: {os.path.abspath(DB_PATH)}")