Spaces:
Running
Running
| """ | |
| data_factory/schemas.py | |
| ======================== | |
| SQLite CREATE TABLE statements for all four domains. | |
| Each schema is fully self-contained and has been verified to create | |
| without errors in SQLite 3.x. | |
| """ | |
| from __future__ import annotations | |
| import sqlite3 | |
| import random | |
| from datetime import date, timedelta | |
| from typing import Callable | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| # SQL SCHEMAS | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| ECOMMERCE_SCHEMA = """ | |
| CREATE TABLE IF NOT EXISTS categories ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL UNIQUE | |
| ); | |
| CREATE TABLE IF NOT EXISTS products ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| category_id INTEGER NOT NULL REFERENCES categories(id), | |
| price REAL NOT NULL CHECK(price >= 0), | |
| stock_quantity INTEGER NOT NULL DEFAULT 0 | |
| ); | |
| CREATE TABLE IF NOT EXISTS customers ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| email TEXT NOT NULL UNIQUE, | |
| country TEXT NOT NULL, | |
| tier TEXT NOT NULL DEFAULT 'bronze' | |
| CHECK(tier IN ('bronze', 'silver', 'gold')), | |
| created_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS orders ( | |
| id INTEGER PRIMARY KEY, | |
| customer_id INTEGER NOT NULL REFERENCES customers(id), | |
| status TEXT NOT NULL DEFAULT 'pending' | |
| CHECK(status IN ('pending','processing','shipped','delivered','cancelled')), | |
| created_at TEXT NOT NULL, | |
| total_amount REAL NOT NULL CHECK(total_amount >= 0) | |
| ); | |
| CREATE TABLE IF NOT EXISTS order_items ( | |
| id INTEGER PRIMARY KEY, | |
| order_id INTEGER NOT NULL REFERENCES orders(id), | |
| product_id INTEGER NOT NULL REFERENCES products(id), | |
| quantity INTEGER NOT NULL CHECK(quantity > 0), | |
| unit_price REAL NOT NULL CHECK(unit_price >= 0) | |
| ); | |
| CREATE TABLE IF NOT EXISTS reviews ( | |
| id INTEGER PRIMARY KEY, | |
| product_id INTEGER NOT NULL REFERENCES products(id), | |
| customer_id INTEGER NOT NULL REFERENCES customers(id), | |
| rating INTEGER NOT NULL CHECK(rating BETWEEN 1 AND 5), | |
| created_at TEXT NOT NULL | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id); | |
| CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id); | |
| CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status); | |
| CREATE INDEX IF NOT EXISTS idx_orders_created ON orders(created_at); | |
| CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id); | |
| CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id); | |
| CREATE INDEX IF NOT EXISTS idx_reviews_product ON reviews(product_id); | |
| CREATE INDEX IF NOT EXISTS idx_customers_tier ON customers(tier); | |
| """ | |
| HEALTHCARE_SCHEMA = """ | |
| CREATE TABLE IF NOT EXISTS patients ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| date_of_birth TEXT NOT NULL, | |
| gender TEXT NOT NULL CHECK(gender IN ('M','F','Other')), | |
| blood_type TEXT NOT NULL, | |
| country TEXT NOT NULL, | |
| registered_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS doctors ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| specialization TEXT NOT NULL, | |
| department TEXT NOT NULL, | |
| experience_years INTEGER NOT NULL CHECK(experience_years >= 0), | |
| consultation_fee REAL NOT NULL CHECK(consultation_fee >= 0) | |
| ); | |
| CREATE TABLE IF NOT EXISTS appointments ( | |
| id INTEGER PRIMARY KEY, | |
| patient_id INTEGER NOT NULL REFERENCES patients(id), | |
| doctor_id INTEGER NOT NULL REFERENCES doctors(id), | |
| scheduled_at TEXT NOT NULL, | |
| status TEXT NOT NULL | |
| CHECK(status IN ('scheduled','completed','cancelled','no_show')), | |
| notes TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS diagnoses ( | |
| id INTEGER PRIMARY KEY, | |
| appointment_id INTEGER NOT NULL REFERENCES appointments(id), | |
| icd_code TEXT NOT NULL, | |
| description TEXT NOT NULL, | |
| severity TEXT NOT NULL CHECK(severity IN ('mild','moderate','severe')) | |
| ); | |
| CREATE TABLE IF NOT EXISTS medications ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| category TEXT NOT NULL, | |
| unit_price REAL NOT NULL CHECK(unit_price >= 0) | |
| ); | |
| CREATE TABLE IF NOT EXISTS prescriptions ( | |
| id INTEGER PRIMARY KEY, | |
| appointment_id INTEGER NOT NULL REFERENCES appointments(id), | |
| medication_id INTEGER NOT NULL REFERENCES medications(id), | |
| dosage TEXT NOT NULL, | |
| duration_days INTEGER NOT NULL CHECK(duration_days > 0), | |
| quantity INTEGER NOT NULL CHECK(quantity > 0) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_appt_patient ON appointments(patient_id); | |
| CREATE INDEX IF NOT EXISTS idx_appt_doctor ON appointments(doctor_id); | |
| CREATE INDEX IF NOT EXISTS idx_appt_status ON appointments(status); | |
| CREATE INDEX IF NOT EXISTS idx_diag_appt ON diagnoses(appointment_id); | |
| CREATE INDEX IF NOT EXISTS idx_presc_appt ON prescriptions(appointment_id); | |
| CREATE INDEX IF NOT EXISTS idx_presc_med ON prescriptions(medication_id); | |
| """ | |
| FINANCE_SCHEMA = """ | |
| CREATE TABLE IF NOT EXISTS fin_customers ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| email TEXT NOT NULL UNIQUE, | |
| country TEXT NOT NULL, | |
| kyc_status TEXT NOT NULL CHECK(kyc_status IN ('pending','verified','rejected')), | |
| created_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS accounts ( | |
| id INTEGER PRIMARY KEY, | |
| customer_id INTEGER NOT NULL REFERENCES fin_customers(id), | |
| account_type TEXT NOT NULL | |
| CHECK(account_type IN ('savings','current','fixed_deposit','loan')), | |
| balance REAL NOT NULL DEFAULT 0, | |
| currency TEXT NOT NULL DEFAULT 'USD', | |
| status TEXT NOT NULL CHECK(status IN ('active','dormant','closed')), | |
| opened_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS transactions ( | |
| id INTEGER PRIMARY KEY, | |
| account_id INTEGER NOT NULL REFERENCES accounts(id), | |
| txn_type TEXT NOT NULL CHECK(txn_type IN ('credit','debit')), | |
| amount REAL NOT NULL CHECK(amount > 0), | |
| currency TEXT NOT NULL DEFAULT 'USD', | |
| merchant TEXT, | |
| created_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS loans ( | |
| id INTEGER PRIMARY KEY, | |
| customer_id INTEGER NOT NULL REFERENCES fin_customers(id), | |
| loan_type TEXT NOT NULL | |
| CHECK(loan_type IN ('personal','home','auto','business')), | |
| principal_amount REAL NOT NULL, | |
| interest_rate REAL NOT NULL, | |
| tenure_months INTEGER NOT NULL, | |
| status TEXT NOT NULL CHECK(status IN ('active','closed','defaulted')), | |
| disbursed_at TEXT NOT NULL | |
| ); | |
| CREATE TABLE IF NOT EXISTS loan_payments ( | |
| id INTEGER PRIMARY KEY, | |
| loan_id INTEGER NOT NULL REFERENCES loans(id), | |
| amount_paid REAL NOT NULL CHECK(amount_paid > 0), | |
| payment_date TEXT NOT NULL, | |
| is_late INTEGER NOT NULL DEFAULT 0 CHECK(is_late IN (0,1)) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_acct_customer ON accounts(customer_id); | |
| CREATE INDEX IF NOT EXISTS idx_txn_account ON transactions(account_id); | |
| CREATE INDEX IF NOT EXISTS idx_txn_type ON transactions(txn_type); | |
| CREATE INDEX IF NOT EXISTS idx_loan_customer ON loans(customer_id); | |
| CREATE INDEX IF NOT EXISTS idx_lp_loan ON loan_payments(loan_id); | |
| """ | |
| HR_SCHEMA = """ | |
| CREATE TABLE IF NOT EXISTS departments ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL UNIQUE, | |
| location TEXT NOT NULL, | |
| budget REAL NOT NULL CHECK(budget >= 0) | |
| ); | |
| CREATE TABLE IF NOT EXISTS employees ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| email TEXT NOT NULL UNIQUE, | |
| department_id INTEGER NOT NULL REFERENCES departments(id), | |
| job_title TEXT NOT NULL, | |
| hire_date TEXT NOT NULL, | |
| salary REAL NOT NULL CHECK(salary >= 0), | |
| status TEXT NOT NULL CHECK(status IN ('active','resigned','terminated')) | |
| ); | |
| CREATE TABLE IF NOT EXISTS performance_reviews ( | |
| id INTEGER PRIMARY KEY, | |
| employee_id INTEGER NOT NULL REFERENCES employees(id), | |
| review_year INTEGER NOT NULL, | |
| rating INTEGER NOT NULL CHECK(rating BETWEEN 1 AND 5), | |
| reviewer_id INTEGER NOT NULL REFERENCES employees(id), | |
| comments TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS projects ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| department_id INTEGER NOT NULL REFERENCES departments(id), | |
| start_date TEXT NOT NULL, | |
| end_date TEXT, | |
| budget REAL NOT NULL, | |
| status TEXT NOT NULL | |
| CHECK(status IN ('planned','active','completed','cancelled')) | |
| ); | |
| CREATE TABLE IF NOT EXISTS project_assignments ( | |
| id INTEGER PRIMARY KEY, | |
| employee_id INTEGER NOT NULL REFERENCES employees(id), | |
| project_id INTEGER NOT NULL REFERENCES projects(id), | |
| role TEXT NOT NULL, | |
| hours_allocated INTEGER NOT NULL CHECK(hours_allocated > 0) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_emp_dept ON employees(department_id); | |
| CREATE INDEX IF NOT EXISTS idx_emp_status ON employees(status); | |
| CREATE INDEX IF NOT EXISTS idx_pr_employee ON performance_reviews(employee_id); | |
| CREATE INDEX IF NOT EXISTS idx_proj_dept ON projects(department_id); | |
| CREATE INDEX IF NOT EXISTS idx_pa_employee ON project_assignments(employee_id); | |
| CREATE INDEX IF NOT EXISTS idx_pa_project ON project_assignments(project_id); | |
| """ | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| # SCHEMA REGISTRY | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| SCHEMA_MAP: dict[str, str] = { | |
| "ecommerce": ECOMMERCE_SCHEMA, | |
| "healthcare": HEALTHCARE_SCHEMA, | |
| "finance": FINANCE_SCHEMA, | |
| "hr": HR_SCHEMA, | |
| } | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| # COMPACT SCHEMA CONTEXT (injected into every training prompt) | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| SCHEMA_CONTEXT: dict[str, str] = { | |
| "ecommerce": """\ | |
| Database: ecommerce (SQLite, read-only) | |
| TABLES | |
| ------ | |
| categories(id INTEGER PK, name TEXT) | |
| products(id INTEGER PK, name TEXT, category_id INTEGER FK→categories.id, price REAL, stock_quantity INTEGER) | |
| customers(id INTEGER PK, name TEXT, email TEXT, country TEXT, tier TEXT ∈ {bronze|silver|gold}, created_at TEXT ISO-8601) | |
| orders(id INTEGER PK, customer_id INTEGER FK→customers.id, status TEXT ∈ {pending|processing|shipped|delivered|cancelled}, created_at TEXT ISO-8601, total_amount REAL) | |
| order_items(id INTEGER PK, order_id INTEGER FK→orders.id, product_id INTEGER FK→products.id, quantity INTEGER, unit_price REAL) | |
| reviews(id INTEGER PK, product_id INTEGER FK→products.id, customer_id INTEGER FK→customers.id, rating INTEGER 1-5, created_at TEXT ISO-8601) | |
| NOTES | |
| ----- | |
| - Use created_at >= '2024-01-01' for date filtering (ISO text sort works) | |
| - SQLite window functions: RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD | |
| - strftime('%Y-%m', created_at) returns 'YYYY-MM' | |
| - All monetary values in USD | |
| """, | |
| "healthcare": """\ | |
| Database: healthcare (SQLite, read-only) | |
| TABLES | |
| ------ | |
| patients(id INTEGER PK, name TEXT, date_of_birth TEXT ISO-8601, gender TEXT ∈ {M|F|Other}, blood_type TEXT, country TEXT, registered_at TEXT ISO-8601) | |
| doctors(id INTEGER PK, name TEXT, specialization TEXT, department TEXT, experience_years INTEGER, consultation_fee REAL) | |
| appointments(id INTEGER PK, patient_id INTEGER FK→patients.id, doctor_id INTEGER FK→doctors.id, scheduled_at TEXT ISO-8601, status TEXT ∈ {scheduled|completed|cancelled|no_show}, notes TEXT nullable) | |
| diagnoses(id INTEGER PK, appointment_id INTEGER FK→appointments.id, icd_code TEXT, description TEXT, severity TEXT ∈ {mild|moderate|severe}) | |
| medications(id INTEGER PK, name TEXT, category TEXT, unit_price REAL) | |
| prescriptions(id INTEGER PK, appointment_id INTEGER FK→appointments.id, medication_id INTEGER FK→medications.id, dosage TEXT, duration_days INTEGER, quantity INTEGER) | |
| NOTES | |
| ----- | |
| - consultation_fee is in USD per visit | |
| - ICD codes follow WHO ICD-10 format (e.g. 'I10', 'E11') | |
| - SQLite window functions available | |
| """, | |
| "finance": """\ | |
| Database: finance (SQLite, read-only) | |
| TABLES | |
| ------ | |
| fin_customers(id INTEGER PK, name TEXT, email TEXT, country TEXT, kyc_status TEXT ∈ {pending|verified|rejected}, created_at TEXT ISO-8601) | |
| accounts(id INTEGER PK, customer_id INTEGER FK→fin_customers.id, account_type TEXT ∈ {savings|current|fixed_deposit|loan}, balance REAL, currency TEXT, status TEXT ∈ {active|dormant|closed}, opened_at TEXT ISO-8601) | |
| transactions(id INTEGER PK, account_id INTEGER FK→accounts.id, txn_type TEXT ∈ {credit|debit}, amount REAL, currency TEXT, merchant TEXT nullable, created_at TEXT ISO-8601) | |
| loans(id INTEGER PK, customer_id INTEGER FK→fin_customers.id, loan_type TEXT ∈ {personal|home|auto|business}, principal_amount REAL, interest_rate REAL, tenure_months INTEGER, status TEXT ∈ {active|closed|defaulted}, disbursed_at TEXT ISO-8601) | |
| loan_payments(id INTEGER PK, loan_id INTEGER FK→loans.id, amount_paid REAL, payment_date TEXT ISO-8601, is_late INTEGER ∈ {0|1}) | |
| NOTES | |
| ----- | |
| - All monetary values in USD unless currency column specifies otherwise | |
| - is_late = 1 means the payment was overdue | |
| - SQLite window functions available | |
| """, | |
| "hr": """\ | |
| Database: hr (SQLite, read-only) | |
| TABLES | |
| ------ | |
| departments(id INTEGER PK, name TEXT, location TEXT, budget REAL) | |
| employees(id INTEGER PK, name TEXT, email TEXT, department_id INTEGER FK→departments.id, job_title TEXT, hire_date TEXT ISO-8601, salary REAL, status TEXT ∈ {active|resigned|terminated}) | |
| performance_reviews(id INTEGER PK, employee_id INTEGER FK→employees.id, review_year INTEGER, rating INTEGER 1-5, reviewer_id INTEGER FK→employees.id, comments TEXT nullable) | |
| projects(id INTEGER PK, name TEXT, department_id INTEGER FK→departments.id, start_date TEXT ISO-8601, end_date TEXT nullable, budget REAL, status TEXT ∈ {planned|active|completed|cancelled}) | |
| project_assignments(id INTEGER PK, employee_id INTEGER FK→employees.id, project_id INTEGER FK→projects.id, role TEXT, hours_allocated INTEGER) | |
| NOTES | |
| ----- | |
| - salary is annual in USD | |
| - performance rating: 1 (lowest) to 5 (highest) | |
| - end_date is NULL for ongoing projects | |
| - SQLite window functions available | |
| """, | |
| } | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| # SEED FUNCTIONS (deterministic, SEED=42) | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| def _rdate(rng: random.Random, start: str = "2022-01-01", end: str = "2024-12-31") -> str: | |
| s = date.fromisoformat(start) | |
| e = date.fromisoformat(end) | |
| return (s + timedelta(days=rng.randint(0, (e - s).days))).isoformat() | |
| def seed_ecommerce(conn: sqlite3.Connection, seed: int = 42) -> None: | |
| rng = random.Random(seed) | |
| cats = ["Electronics", "Clothing", "Books", "Home & Garden", | |
| "Sports & Outdoors", "Toys & Games", "Beauty", "Automotive"] | |
| conn.executemany("INSERT INTO categories(id,name) VALUES(?,?)", enumerate(cats, 1)) | |
| products = [ | |
| (1,"Wireless Headphones",1,149.99,50),(2,"Laptop Stand",1,59.99,120), | |
| (3,"USB-C Hub",1,49.99,90),(4,"Webcam 4K",1,89.99,30), | |
| (5,"Cotton T-Shirt",2,19.99,200),(6,"Winter Jacket",2,129.99,60), | |
| (7,"Running Shorts",2,34.99,150),(8,"Clean Code",3,39.99,80), | |
| (9,"Deep Learning Book",3,59.99,45),(10,"Coffee Maker",4,89.99,40), | |
| (11,"Air Purifier",4,199.99,25),(12,"Yoga Mat",5,29.99,150), | |
| (13,"Resistance Bands",5,14.99,200),(14,"Lego City Set",6,79.99,60), | |
| (15,"Face Serum",7,34.99,100),(16,"Dash Cam",8,119.99,35), | |
| ] | |
| conn.executemany("INSERT INTO products VALUES(?,?,?,?,?)", products) | |
| countries = ["India","USA","Germany","UK","Canada","Australia","France","Brazil"] | |
| tiers = ["bronze","silver","gold"] | |
| customers = [] | |
| for i in range(1, 51): | |
| customers.append((i, f"Customer {i}", f"cust{i}@shop.com", | |
| rng.choice(countries), rng.choice(tiers), _rdate(rng))) | |
| conn.executemany("INSERT INTO customers VALUES(?,?,?,?,?,?)", customers) | |
| statuses = ["pending","processing","shipped","delivered","cancelled"] | |
| orders = [] | |
| for i in range(1, 201): | |
| orders.append((i, rng.randint(1, 50), rng.choice(statuses), | |
| _rdate(rng), round(rng.uniform(20, 800), 2))) | |
| conn.executemany("INSERT INTO orders VALUES(?,?,?,?,?)", orders) | |
| items = [] | |
| for i in range(1, 301): | |
| items.append((i, rng.randint(1, 200), rng.randint(1, 16), | |
| rng.randint(1, 5), round(rng.uniform(10, 200), 2))) | |
| conn.executemany("INSERT INTO order_items VALUES(?,?,?,?,?)", items) | |
| reviews = [] | |
| for i in range(1, 151): | |
| reviews.append((i, rng.randint(1, 16), rng.randint(1, 50), | |
| rng.randint(1, 5), _rdate(rng))) | |
| conn.executemany("INSERT INTO reviews VALUES(?,?,?,?,?)", reviews) | |
| conn.commit() | |
| def seed_healthcare(conn: sqlite3.Connection, seed: int = 42) -> None: | |
| rng = random.Random(seed) | |
| specs = [("Cardiology","Cardiology"), ("Neurology","Neurology"), | |
| ("Orthopedics","Orthopedics"), ("Dermatology","Dermatology"), | |
| ("Pediatrics","Pediatrics"), ("Oncology","Oncology"), | |
| ("Endocrinology","Endocrinology"), ("Gastroenterology","Gastroenterology")] | |
| for i, (spec, dept) in enumerate(specs, 1): | |
| conn.execute("INSERT INTO doctors VALUES(?,?,?,?,?,?)", | |
| (i, f"Dr. {['Smith','Patel','Kim','Müller','Okafor','Chen','Lopez','Roy'][i-1]}", | |
| spec, dept, rng.randint(2, 25), round(rng.uniform(50, 350), 2))) | |
| genders = ["M", "F", "Other"] | |
| blood_types = ["A+","A-","B+","B-","O+","O-","AB+","AB-"] | |
| countries = ["India","USA","Germany","UK","Canada","Australia"] | |
| for i in range(1, 101): | |
| conn.execute("INSERT INTO patients VALUES(?,?,?,?,?,?,?)", | |
| (i, f"Patient {i}", _rdate(rng, "1950-01-01", "2010-01-01"), | |
| rng.choice(genders), rng.choice(blood_types), | |
| rng.choice(countries), _rdate(rng, "2020-01-01", "2024-12-31"))) | |
| appt_statuses = ["scheduled", "completed", "cancelled", "no_show"] | |
| weights = [0.15, 0.60, 0.15, 0.10] | |
| for i in range(1, 301): | |
| conn.execute("INSERT INTO appointments VALUES(?,?,?,?,?,?)", | |
| (i, rng.randint(1, 100), rng.randint(1, 8), | |
| _rdate(rng, "2022-01-01", "2024-12-31"), | |
| rng.choices(appt_statuses, weights)[0], None)) | |
| icd_codes = ["I10","E11","J45","M54","K21","F32","G43","L30","N39","R05", | |
| "C50","Z87","I25","E78","J18"] | |
| descs = ["Hypertension","Type 2 Diabetes","Asthma","Back Pain","GERD", | |
| "Depression","Migraine","Dermatitis","UTI","Cough", | |
| "Breast Cancer","Family History","Coronary Artery Disease", | |
| "Hyperlipidemia","Pneumonia"] | |
| severities = ["mild","moderate","severe"] | |
| for i in range(1, 201): | |
| conn.execute("INSERT INTO diagnoses VALUES(?,?,?,?,?)", | |
| (i, rng.randint(1, 300), rng.choice(icd_codes), | |
| rng.choice(descs), rng.choice(severities))) | |
| meds = [("Metformin","Antidiabetic",0.15),("Lisinopril","Antihypertensive",0.20), | |
| ("Atorvastatin","Statin",0.25),("Amoxicillin","Antibiotic",0.30), | |
| ("Ibuprofen","NSAID",0.10),("Omeprazole","PPI",0.18), | |
| ("Sertraline","Antidepressant",0.35),("Cetirizine","Antihistamine",0.08), | |
| ("Paracetamol","Analgesic",0.05),("Aspirin","Antiplatelet",0.07)] | |
| for i, (name, cat, price) in enumerate(meds, 1): | |
| conn.execute("INSERT INTO medications VALUES(?,?,?,?)", (i, name, cat, price)) | |
| dosages = ["1x daily","2x daily","3x daily","once at night","as needed"] | |
| for i in range(1, 251): | |
| conn.execute("INSERT INTO prescriptions VALUES(?,?,?,?,?,?)", | |
| (i, rng.randint(1, 300), rng.randint(1, 10), | |
| rng.choice(dosages), rng.randint(5, 60), rng.randint(10, 90))) | |
| conn.commit() | |
| def seed_finance(conn: sqlite3.Connection, seed: int = 42) -> None: | |
| rng = random.Random(seed) | |
| countries = ["India","USA","Germany","UK","Singapore","UAE","Canada"] | |
| kyc = ["pending","verified","verified","verified","rejected"] | |
| for i in range(1, 51): | |
| conn.execute("INSERT INTO fin_customers VALUES(?,?,?,?,?,?)", | |
| (i, f"FinClient {i}", f"fincli{i}@bank.com", | |
| rng.choice(countries), rng.choice(kyc), _rdate(rng))) | |
| acct_types = ["savings","savings","current","fixed_deposit"] | |
| statuses = ["active","active","active","dormant","closed"] | |
| for i in range(1, 101): | |
| conn.execute("INSERT INTO accounts VALUES(?,?,?,?,?,?,?)", | |
| (i, rng.randint(1, 50), rng.choice(acct_types), | |
| round(rng.uniform(100, 100000), 2), "USD", | |
| rng.choice(statuses), _rdate(rng))) | |
| merchants = [None, "Amazon", "Walmart", "Netflix", "Uber", "Apple", | |
| "Google Pay", "Zomato", "Flipkart", "Airbnb"] | |
| for i in range(1, 501): | |
| conn.execute("INSERT INTO transactions VALUES(?,?,?,?,?,?,?)", | |
| (i, rng.randint(1, 100), rng.choice(["credit","debit"]), | |
| round(rng.uniform(5, 10000), 2), "USD", | |
| rng.choice(merchants), _rdate(rng))) | |
| loan_types = ["personal","home","auto","business"] | |
| loan_statuses = ["active","active","closed","defaulted"] | |
| for i in range(1, 51): | |
| conn.execute("INSERT INTO loans VALUES(?,?,?,?,?,?,?,?)", | |
| (i, rng.randint(1, 50), rng.choice(loan_types), | |
| round(rng.uniform(5000, 500000), 2), | |
| round(rng.uniform(5, 18), 2), rng.randint(12, 360), | |
| rng.choice(loan_statuses), _rdate(rng))) | |
| for i in range(1, 201): | |
| conn.execute("INSERT INTO loan_payments VALUES(?,?,?,?,?)", | |
| (i, rng.randint(1, 50), round(rng.uniform(500, 10000), 2), | |
| _rdate(rng), rng.randint(0, 1))) | |
| conn.commit() | |
| def seed_hr(conn: sqlite3.Connection, seed: int = 42) -> None: | |
| rng = random.Random(seed) | |
| depts = [("Engineering","Bangalore",8000000),("Marketing","Mumbai",3000000), | |
| ("Finance","Delhi",2000000),("HR","Chennai",1500000), | |
| ("Sales","Hyderabad",5000000),("Product","Pune",4000000), | |
| ("Legal","Delhi",1000000),("Operations","Kolkata",2500000)] | |
| for i, (name, loc, bud) in enumerate(depts, 1): | |
| conn.execute("INSERT INTO departments VALUES(?,?,?,?)", (i, name, loc, bud)) | |
| titles = ["Software Engineer","Senior Engineer","Staff Engineer","Principal Engineer", | |
| "Engineering Manager","Product Manager","Data Analyst","Data Scientist", | |
| "Marketing Specialist","Sales Executive","HR Specialist","Finance Analyst", | |
| "Director","VP","Legal Counsel"] | |
| statuses = ["active","active","active","active","resigned","terminated"] | |
| for i in range(1, 101): | |
| conn.execute("INSERT INTO employees VALUES(?,?,?,?,?,?,?,?)", | |
| (i, f"Employee {i}", f"emp{i}@corp.com", | |
| rng.randint(1, 8), rng.choice(titles), | |
| _rdate(rng, "2015-01-01", "2024-01-01"), | |
| round(rng.uniform(25000, 200000), 2), rng.choice(statuses))) | |
| for i in range(1, 201): | |
| conn.execute("INSERT INTO performance_reviews VALUES(?,?,?,?,?,?)", | |
| (i, rng.randint(1, 100), rng.randint(2019, 2024), | |
| rng.randint(1, 5), rng.randint(1, 100), | |
| rng.choice(["Excellent work","Good performance","Needs improvement", | |
| "Outstanding","Meeting expectations"]))) | |
| proj_statuses = ["planned","active","active","completed","cancelled"] | |
| for i in range(1, 51): | |
| sd = _rdate(rng, "2021-01-01", "2024-01-01") | |
| conn.execute("INSERT INTO projects VALUES(?,?,?,?,?,?,?)", | |
| (i, f"Project {i}", rng.randint(1, 8), sd, | |
| _rdate(rng, sd, "2025-06-01") if rng.random() > 0.25 else None, | |
| round(rng.uniform(50000, 2000000), 2), rng.choice(proj_statuses))) | |
| roles = ["Lead","Senior Developer","Developer","Tester","Analyst","DevOps"] | |
| for i in range(1, 251): | |
| conn.execute("INSERT INTO project_assignments VALUES(?,?,?,?,?)", | |
| (i, rng.randint(1, 100), rng.randint(1, 50), | |
| rng.choice(roles), rng.randint(20, 400))) | |
| conn.commit() | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| # REGISTRY | |
| # ───────────────────────────────────────────────────────────────────────────── | |
| SEED_MAP: dict[str, Callable] = { | |
| "ecommerce": seed_ecommerce, | |
| "healthcare": seed_healthcare, | |
| "finance": seed_finance, | |
| "hr": seed_hr, | |
| } | |
| def build_connection(domain: str, seed: int = 42) -> sqlite3.Connection: | |
| """Return a seeded in-memory SQLite connection for the given domain.""" | |
| conn = sqlite3.connect(":memory:", check_same_thread=False) | |
| conn.row_factory = sqlite3.Row | |
| conn.execute("PRAGMA foreign_keys = ON") | |
| conn.executescript(SCHEMA_MAP[domain]) | |
| SEED_MAP[domain](conn, seed=seed) | |
| return conn | |