entry.py

import sqlite3
from typing import List, Dict, Any, Optional


def add_customer(conn: sqlite3.Connection, name: str, email: Optional[str], city: Optional[str],
                 state: Optional[str], signup_date: Optional[str]) -> int:
    if not name:
        raise ValueError("name is required")
    cur = conn.execute(
        "INSERT INTO customers (name, email, city, state, signup_date) VALUES (?, ?, ?, ?, ?);",
        (name, email, city, state, signup_date),
    )
    return cur.lastrowid


def add_product(conn: sqlite3.Connection, sku: str, name: str, category: Optional[str], price: float) -> int:
    if not sku or not name:
        raise ValueError("sku and name are required")
    if price is None or price < 0:
        raise ValueError("price must be non-negative")
    cur = conn.execute(
        "INSERT INTO products (sku, name, category, price) VALUES (?, ?, ?, ?);",
        (sku, name, category, price),
    )
    return cur.lastrowid


def add_store(conn: sqlite3.Connection, name: str, city: Optional[str], state: Optional[str], region: Optional[str]) -> int:
    if not name:
        raise ValueError("name required")
    cur = conn.execute(
        "INSERT INTO stores (name, city, state, region) VALUES (?, ?, ?, ?);",
        (name, city, state, region),
    )
    return cur.lastrowid


def _get_product_price(conn: sqlite3.Connection, product_id: int) -> Optional[float]:
    cur = conn.execute("SELECT price FROM products WHERE id = ?;", (product_id,))
    row = cur.fetchone()
    return row[0] if row else None


def create_order(conn: sqlite3.Connection,
                 customer_id: int,
                 store_id: int,
                 items: List[Dict[str, Any]],
                 order_date: Optional[str] = None,
                 status: str = "completed") -> int:
   
    if not items or not isinstance(items, list):
        raise ValueError("items must be a non-empty list")


    cur = conn.execute("SELECT id FROM customers WHERE id = ?;", (customer_id,))
    if cur.fetchone() is None:
        raise ValueError(f"customer_id {customer_id} not found")

    cur = conn.execute("SELECT id FROM stores WHERE id = ?;", (store_id,))
    if cur.fetchone() is None:
        raise ValueError(f"store_id {store_id} not found")

    validated_items = []
    total_amount = 0.0
    for it in items:
        if "product_id" not in it or "quantity" not in it:
            raise ValueError("each item must contain product_id and quantity")
        pid = int(it["product_id"])
        qty = int(it["quantity"])
        if qty <= 0:
            raise ValueError("quantity must be > 0")
        unit_price = it.get("unit_price", None)
        if unit_price is None:
            fetched = _get_product_price(conn, pid)
            if fetched is None:
                raise ValueError(f"product_id {pid} not found and unit_price not provided")
            unit_price = float(fetched)
        else:
            unit_price = float(unit_price)
        if unit_price < 0:
            raise ValueError("unit_price must be non-negative")
        validated_items.append((pid, qty, unit_price))
        total_amount += round(qty * unit_price, 2)

    cur = conn.execute(
        "INSERT INTO orders (customer_id, store_id, order_date, total_amount, status) VALUES (?, ?, ?, ?, ?);",
        (customer_id, store_id, order_date, round(total_amount, 2), status),
    )
    order_id = cur.lastrowid

    order_item_rows = [(order_id, pid, qty, unit_price) for (pid, qty, unit_price) in validated_items]
    conn.executemany(
        "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?);",
        order_item_rows,
    )

    return order_id