init_db.py

import os
import sqlite3


def ensure_database(db_path: str = "demo_store.db"):
    if os.path.exists(db_path):
        return

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

    cur.executescript(
        """
        DROP TABLE IF EXISTS order_items;
        DROP TABLE IF EXISTS orders;
        DROP TABLE IF EXISTS products;
        DROP TABLE IF EXISTS customers;

        CREATE TABLE customers (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            country TEXT NOT NULL,
            segment TEXT NOT NULL
        );

        CREATE TABLE products (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            category TEXT NOT NULL,
            price REAL NOT NULL
        );

        CREATE TABLE orders (
            id INTEGER PRIMARY KEY,
            customer_id INTEGER NOT NULL,
            order_date TEXT NOT NULL,
            status TEXT NOT NULL,
            shipping_days INTEGER NOT NULL,
            FOREIGN KEY(customer_id) REFERENCES customers(id)
        );

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

    customers = [
        (1, "Acme BV", "Netherlands", "Enterprise"),
        (2, "Bright Retail", "Germany", "SMB"),
        (3, "Northwind Foods", "Belgium", "Mid-Market"),
        (4, "Contoso Health", "Netherlands", "Enterprise"),
        (5, "Blue Ocean Travel", "France", "SMB"),
        (6, "Urban Style", "Spain", "Mid-Market"),
        (7, "Green Energy Partners", "Denmark", "Enterprise"),
        (8, "Delta Logistics", "Germany", "Enterprise"),
    ]

    products = [
        (1, "Laptop Pro 14", "Electronics", 1499.0),
        (2, "Noise Cancelling Headphones", "Electronics", 299.0),
        (3, "Office Chair Ergo", "Furniture", 399.0),
        (4, "Standing Desk", "Furniture", 699.0),
        (5, "USB-C Dock", "Accessories", 149.0),
        (6, "4K Monitor", "Electronics", 549.0),
        (7, "Notebook Pack", "Stationery", 19.0),
        (8, "Premium Pen Set", "Stationery", 39.0),
    ]

    orders = [
        (1, 1, "2026-01-06", "shipped", 2),
        (2, 2, "2026-01-18", "shipped", 4),
        (3, 3, "2026-02-03", "delivered", 3),
        (4, 4, "2026-02-14", "delayed", 9),
        (5, 1, "2026-02-28", "delivered", 2),
        (6, 5, "2026-03-05", "shipped", 5),
        (7, 6, "2026-03-11", "delivered", 3),
        (8, 7, "2026-03-29", "delayed", 11),
        (9, 8, "2026-04-02", "delivered", 4),
        (10, 2, "2026-04-15", "shipped", 3),
        (11, 3, "2026-04-20", "delivered", 2),
        (12, 4, "2026-05-01", "shipped", 5),
    ]

    order_items = [
        (1, 1, 1, 2, 1499.0),
        (2, 1, 5, 2, 149.0),
        (3, 2, 3, 4, 399.0),
        (4, 3, 2, 3, 299.0),
        (5, 3, 7, 10, 19.0),
        (6, 4, 4, 2, 699.0),
        (7, 4, 6, 2, 549.0),
        (8, 5, 2, 5, 299.0),
        (9, 5, 8, 6, 39.0),
        (10, 6, 5, 8, 149.0),
        (11, 7, 1, 1, 1499.0),
        (12, 7, 6, 2, 549.0),
        (13, 8, 4, 3, 699.0),
        (14, 8, 3, 5, 399.0),
        (15, 9, 1, 1, 1499.0),
        (16, 9, 2, 2, 299.0),
        (17, 10, 7, 25, 19.0),
        (18, 10, 8, 10, 39.0),
        (19, 11, 6, 4, 549.0),
        (20, 11, 5, 5, 149.0),
        (21, 12, 4, 1, 699.0),
        (22, 12, 3, 2, 399.0),
    ]

    cur.executemany("INSERT INTO customers VALUES (?, ?, ?, ?)", customers)
    cur.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", products)
    cur.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", orders)
    cur.executemany("INSERT INTO order_items VALUES (?, ?, ?, ?, ?)", order_items)

    conn.commit()
    conn.close()