"""Database models and persistence helpers for product metadata.""" from __future__ import annotations import sqlite3 from dataclasses import dataclass from typing import Any, Dict, List, Optional from datetime import datetime import json @dataclass class Product: """Domain model representing a product stored in the catalog database.""" id: str title: str description: str price: float currency: str category: str brand: str image_url: str availability: str rating: Optional[float] = None review_count: Optional[int] = None created_at: Optional[datetime] = None updated_at: Optional[datetime] = None metadata: Optional[Dict[str, Any]] = None def to_dict(self) -> Dict[str, Any]: """Serialize the product to a JSON-friendly dictionary.""" def _serialize_timestamp(value: Optional[datetime]) -> Optional[str]: if value is None: return None if isinstance(value, datetime): return value.isoformat() # SQLite returns ISO-formatted strings; pass through unchanged if isinstance(value, str): return value return str(value) return { "id": self.id, "title": self.title, "description": self.description, "price": self.price, "currency": self.currency, "category": self.category, "brand": self.brand, "image_url": self.image_url, "availability": self.availability, "rating": self.rating, "review_count": self.review_count, "created_at": _serialize_timestamp(self.created_at), "updated_at": _serialize_timestamp(self.updated_at), "metadata": self.metadata or {}, } class ProductDatabase: """SQLite-backed data access layer used by the API and vector store manager.""" def __init__(self, db_path: str): self.db_path = db_path self.init_database() def init_database(self) -> None: """Ensure required tables and indexes exist.""" with sqlite3.connect(self.db_path) as conn: conn.execute( """ CREATE TABLE IF NOT EXISTS products ( id TEXT PRIMARY KEY, title TEXT NOT NULL, description TEXT, price REAL NOT NULL, currency TEXT DEFAULT 'XAF', category TEXT NOT NULL, brand TEXT, image_url TEXT NOT NULL, availability TEXT DEFAULT 'in_stock', rating REAL, review_count INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, metadata TEXT ) """ ) conn.execute( """ CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, parent_id INTEGER, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (parent_id) REFERENCES categories (id) ) """ ) conn.execute( """ CREATE TABLE IF NOT EXISTS brands ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, description TEXT, logo_url TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ ) conn.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)") conn.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)") conn.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)") conn.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)") conn.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)") conn.commit() def add_product(self, product: Product) -> None: """Insert or replace a product record.""" with sqlite3.connect(self.db_path) as conn: conn.execute( """ INSERT OR REPLACE INTO products (id, title, description, price, currency, category, brand, image_url, availability, rating, review_count, metadata, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP) """, ( product.id, product.title, product.description, product.price, product.currency, product.category, product.brand, product.image_url, product.availability, product.rating, product.review_count, json.dumps(product.metadata) if product.metadata else None, ), ) conn.commit() def get_product(self, product_id: str) -> Optional[Product]: """Fetch a product by identifier.""" with sqlite3.connect(self.db_path) as conn: conn.row_factory = sqlite3.Row cursor = conn.execute("SELECT * FROM products WHERE id = ?", (product_id,)) row = cursor.fetchone() if row: return self._row_to_product(row) return None def get_products( self, limit: int = 100, offset: int = 0, category: Optional[str] = None, brand: Optional[str] = None, min_price: Optional[float] = None, max_price: Optional[float] = None, availability: Optional[str] = None, ) -> List[Product]: """Return paginated products with optional filters.""" query = "SELECT * FROM products WHERE 1=1" params = [] if category: query += " AND category = ?" params.append(category) if brand: query += " AND brand = ?" params.append(brand) if min_price is not None: query += " AND price >= ?" params.append(min_price) if max_price is not None: query += " AND price <= ?" params.append(max_price) if availability: query += " AND availability = ?" params.append(availability) query += " ORDER BY created_at DESC LIMIT ? OFFSET ?" params.extend([limit, offset]) with sqlite3.connect(self.db_path) as conn: conn.row_factory = sqlite3.Row cursor = conn.execute(query, params) rows = cursor.fetchall() return [self._row_to_product(row) for row in rows] def get_all_products(self) -> List[Product]: """Return all products (used for initial embedding generation).""" with sqlite3.connect(self.db_path) as conn: conn.row_factory = sqlite3.Row cursor = conn.execute("SELECT * FROM products ORDER BY id") rows = cursor.fetchall() return [self._row_to_product(row) for row in rows] def get_categories(self) -> List[str]: """Return the unique category values.""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute("SELECT DISTINCT category FROM products ORDER BY category") return [row[0] for row in cursor.fetchall()] def get_brands(self) -> List[str]: """Return the unique brand values.""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute("SELECT DISTINCT brand FROM products WHERE brand IS NOT NULL ORDER BY brand") return [row[0] for row in cursor.fetchall()] def search_products(self, query: str, limit: int = 20) -> List[Product]: """Simple LIKE-based text search used for admin tooling.""" with sqlite3.connect(self.db_path) as conn: conn.row_factory = sqlite3.Row cursor = conn.execute( """ SELECT * FROM products WHERE title LIKE ? OR description LIKE ? ORDER BY created_at DESC LIMIT ? """, (f"%{query}%", f"%{query}%", limit), ) rows = cursor.fetchall() return [self._row_to_product(row) for row in rows] def delete_product(self, product_id: str) -> bool: """Delete a product by ID. Returns True if a row was removed.""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute("DELETE FROM products WHERE id = ?", (product_id,)) conn.commit() return cursor.rowcount > 0 def update_product(self, product: Product) -> bool: """Persist updates to an existing product record.""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute( """ UPDATE products SET title = ?, description = ?, price = ?, currency = ?, category = ?, brand = ?, image_url = ?, availability = ?, rating = ?, review_count = ?, metadata = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? """, ( product.title, product.description, product.price, product.currency, product.category, product.brand, product.image_url, product.availability, product.rating, product.review_count, json.dumps(product.metadata) if product.metadata else None, product.id, ), ) conn.commit() return cursor.rowcount > 0 def get_product_count(self) -> int: """Return the total number of catalog entries.""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute("SELECT COUNT(*) FROM products") result = cursor.fetchone() return int(result[0]) if result else 0 def _row_to_product(self, row: sqlite3.Row) -> Product: """Convert a SQLite row into a Product dataclass instance.""" metadata = json.loads(row["metadata"]) if row["metadata"] else None return Product( id=row["id"], title=row["title"], description=row["description"] or "", price=row["price"], currency=row["currency"], category=row["category"], brand=row["brand"] or "", image_url=row["image_url"], availability=row["availability"], rating=row["rating"], review_count=row["review_count"], created_at=row["created_at"], updated_at=row["updated_at"], metadata=metadata, )