db.py

import json
import sqlite3
from pathlib import Path

# Project root is one level above /app
BASE_DIR = Path(__file__).resolve().parent.parent
JSON_PATH = BASE_DIR / "product_database.json"
DB_PATH = BASE_DIR / "products.db"


def connect():
    return sqlite3.connect(DB_PATH)


def init_db():
    """Create tables if they don't exist."""
    with connect() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                category TEXT NOT NULL,
                name TEXT NOT NULL,
                manufacturer TEXT,
                model TEXT,
                description TEXT,
                price REAL DEFAULT 0.0,
                UNIQUE(category, name)
            );
        """)
        conn.commit()


def seed_from_json(limit_per_category=20):
    """
    Read product_database.json and insert products into SQLite.
    For now, we seed only cameras to keep it simple.
    """
    if not JSON_PATH.exists():
        raise FileNotFoundError(f"Missing JSON file: {JSON_PATH}")

    data = json.loads(JSON_PATH.read_text(encoding="utf-8"))

    cameras = data.get("cameras", {})
    items = list(cameras.items())[:limit_per_category]

    with connect() as conn:
        inserted = 0
        for name, meta in items:
            manufacturer = meta.get("manufacturer")
            model = meta.get("model")
            description = meta.get("description")
            price = float(meta.get("price", 0.0) or 0.0)

            # INSERT OR IGNORE prevents duplicates if you run seeding twice
            cur = conn.execute("""
                INSERT OR IGNORE INTO products
                (category, name, manufacturer, model, description, price)
                VALUES (?, ?, ?, ?, ?, ?);
            """, ("cameras", name, manufacturer, model, description, price))

            # rowcount is 1 if inserted, 0 if ignored due to duplicate
            if cur.rowcount == 1:
                inserted += 1

        conn.commit()

    return inserted


def count_products():
    with connect() as conn:
        (n,) = conn.execute("SELECT COUNT(*) FROM products;").fetchone()
        return n


if __name__ == "__main__":
    init_db()
    inserted = seed_from_json(limit_per_category=25)
    total = count_products()
    print(f"Seed complete. Inserted {inserted} new products. Total now: {total}.")
    print(f"SQLite DB location: {DB_PATH}")