create_sample_db.py

"""
Create a sample SQLite database for testing the Autonomous SQL Data Scientist.
"""

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "data" / "sample.db"


def create_sample_database():
    Path(DB_PATH).parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Sales table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sales (
            id INTEGER PRIMARY KEY,
            product TEXT,
            category TEXT,
            amount REAL,
            sale_date DATE,
            region TEXT
        )
    """)

    # Sample data
    sales_data = [
        ("Widget A", "Electronics", 299.99, "2024-01-15", "North"),
        ("Widget B", "Electronics", 149.99, "2024-01-16", "South"),
        ("Gadget X", "Electronics", 599.99, "2024-02-01", "East"),
        ("Tool Y", "Hardware", 49.99, "2024-02-10", "West"),
        ("Tool Z", "Hardware", 89.99, "2024-02-15", "North"),
        ("Widget A", "Electronics", 299.99, "2024-03-01", "South"),
        ("Gadget X", "Electronics", 599.99, "2024-03-05", "North"),
        ("Gadget X", "Electronics", 599.99, "2024-03-12", "East"),
        ("Tool Y", "Hardware", 49.99, "2024-03-20", "North"),
        ("Widget B", "Electronics", 149.99, "2024-04-01", "West"),
    ]
    cursor.executemany(
        "INSERT INTO sales (product, category, amount, sale_date, region) VALUES (?, ?, ?, ?, ?)",
        sales_data,
    )

    conn.commit()
    conn.close()
    print(f"Sample database created at {DB_PATH}")


if __name__ == "__main__":
    create_sample_database()