""" Unified database adapters for connecting to various customer database systems. Used by both SDK and API. """ import sqlite3 from abc import ABC, abstractmethod from typing import List, Dict, Any, Optional, Iterator import pandas as pd from ...database.models import Product class DatabaseError(Exception): """Database operation error.""" pass class DatabaseConfig: """Configuration for database connection.""" def __init__( self, connection_string: str, table_name: str = "products", database_name: Optional[str] = None, id_column: str = "id", title_column: str = "title", image_url_column: str = "image_url", category_column: Optional[str] = None, brand_column: Optional[str] = None, price_column: Optional[str] = None, rating_column: Optional[str] = None, description_column: Optional[str] = None ): self.connection_string = connection_string self.table_name = table_name self.database_name = database_name self.id_column = id_column self.title_column = title_column self.image_url_column = image_url_column self.category_column = category_column self.brand_column = brand_column self.price_column = price_column self.rating_column = rating_column self.description_column = description_column class DatabaseAdapter(ABC): """Abstract base class for database adapters.""" def __init__(self, config: DatabaseConfig): self.config = config @abstractmethod def connect(self) -> None: """Establish database connection.""" pass @abstractmethod def disconnect(self) -> None: """Close database connection.""" pass @abstractmethod def get_all_products(self) -> Iterator[Product]: """Get all products from the database.""" pass @abstractmethod def get_product_count(self) -> int: """Get total number of products.""" pass @abstractmethod def get_product_by_id(self, product_id: str) -> Optional[Product]: """Get a specific product by ID.""" pass # Default implementations for read-only helpers. Subclasses can override for efficiency. def get_products_filtered( 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 (in-memory fallback).""" if limit <= 0: return [] offset = max(offset, 0) def _matches(product: Product) -> bool: if category and product.category != category: return False if brand and product.brand != brand: return False if min_price is not None and product.price < float(min_price): return False if max_price is not None and product.price > float(max_price): return False if availability and product.availability != availability: return False return True results: List[Product] = [] skipped = 0 for product in self.get_all_products(): if not _matches(product): continue if skipped < offset: skipped += 1 continue results.append(product) if len(results) >= limit: break return results def search_products(self, query: str, limit: int = 20) -> List[Product]: """Simple LIKE-based search using in-memory filtering as fallback.""" if not query or limit <= 0: return [] q = query.casefold() matches: List[Product] = [] for product in self.get_all_products(): haystack = f"{product.title} {product.description or ''} {product.brand or ''}" if q in haystack.casefold(): matches.append(product) if len(matches) >= limit: break return matches def get_categories(self) -> List[str]: """Return distinct categories.""" categories = {prod.category for prod in self.get_all_products() if getattr(prod, "category", None)} return sorted(categories) def get_brands(self) -> List[str]: """Return distinct brands.""" brands = {prod.brand for prod in self.get_all_products() if getattr(prod, "brand", None)} return sorted(brands) class MongoDBAdapter(DatabaseAdapter): """MongoDB adapter for document-store catalogs.""" def __init__(self, config: DatabaseConfig): super().__init__(config) self.client = None self.database = None self.collection = None def connect(self) -> None: """Connect to MongoDB.""" try: from pymongo import MongoClient except ImportError as exc: # pragma: no cover - import error path raise DatabaseError( "pymongo is required for MongoDB support. Install with: pip install pymongo" ) from exc self.client = MongoClient(self.config.connection_string) db_name = self.config.database_name if not db_name: try: default_db = self.client.get_default_database() db_name = default_db.name if default_db else None except Exception: db_name = None if not db_name: raise DatabaseError( "MongoDB connection requires database_name in config or connection string" ) self.database = self.client[db_name] collection_name = self.config.table_name or "products" self.collection = self.database[collection_name] def disconnect(self) -> None: """Close MongoDB connection.""" if self.client: self.client.close() self.client = None self.database = None self.collection = None def _ensure_collection(self): if self.collection is None: raise DatabaseError("MongoDB collection not initialized") def _doc_to_product(self, doc: Dict[str, Any]) -> Product: """Convert MongoDB document to Product.""" id_field = self.config.id_column or "_id" raw_id = doc.get(id_field) or doc.get("_id") product_id = str(raw_id) if raw_id is not None else "" def _get_value(field: Optional[str], fallback_key: str) -> Any: if field and field in doc: return doc.get(field) return doc.get(fallback_key) def _coerce_float(value: Any, default: Optional[float] = 0.0) -> Optional[float]: try: if value is None: return default return float(value) except (TypeError, ValueError): return default def _coerce_int(value: Any) -> Optional[int]: try: if value is None: return None return int(value) except (TypeError, ValueError): return None metadata_value = doc.get("metadata") if metadata_value is None: metadata_value = {} elif not isinstance(metadata_value, dict): metadata_value = {"raw": metadata_value} return Product( id=product_id, title=str(_get_value(self.config.title_column, "title") or ""), description=str(_get_value(self.config.description_column, "description") or ""), price=float(_coerce_float(_get_value(self.config.price_column, "price"), 0.0) or 0.0), currency=str(doc.get("currency", "USD")), category=str(_get_value(self.config.category_column, "category") or ""), brand=str(_get_value(self.config.brand_column, "brand") or ""), image_url=str(_get_value(self.config.image_url_column, "image_url") or ""), availability=str(doc.get("availability", "in_stock")), rating=_coerce_float(doc.get("rating"), None), review_count=_coerce_int(doc.get("review_count")), metadata=metadata_value, ) def get_all_products(self) -> Iterator[Product]: self._ensure_collection() cursor = self.collection.find({}) for doc in cursor: yield self._doc_to_product(doc) def get_product_count(self) -> int: self._ensure_collection() return self.collection.count_documents({}) def get_product_by_id(self, product_id: str) -> Optional[Product]: self._ensure_collection() id_field = self.config.id_column or "_id" query: Dict[str, Any] = {} if id_field == "_id": try: from bson import ObjectId # type: ignore query["_id"] = ObjectId(product_id) except Exception: query["_id"] = product_id else: query[id_field] = product_id doc = self.collection.find_one(query) if not doc: return None return self._doc_to_product(doc) def get_products_filtered( 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]: self._ensure_collection() query: Dict[str, Any] = {} def _set_filter(field: Optional[str], fallback: str, value: Any) -> None: if value is None: return field_name = field or fallback query[field_name] = value _set_filter(self.config.category_column, "category", category) _set_filter(self.config.brand_column, "brand", brand) _set_filter(None, "availability", availability) price_field = self.config.price_column or "price" price_clause: Dict[str, Any] = {} if min_price is not None: price_clause["$gte"] = float(min_price) if max_price is not None: price_clause["$lte"] = float(max_price) if price_clause: query[price_field] = price_clause sort_field = self.config.id_column or "_id" cursor = ( self.collection.find(query) .sort(sort_field, -1) .skip(max(offset, 0)) .limit(max(limit, 0)) ) return [self._doc_to_product(doc) for doc in cursor] def search_products(self, query: str, limit: int = 20) -> List[Product]: self._ensure_collection() if not query: return [] regex = {"$regex": query, "$options": "i"} search_fields = [ self.config.title_column or "title", self.config.description_column or "description", self.config.brand_column or "brand", "category", ] filters = {"$or": [{field: regex} for field in search_fields]} cursor = self.collection.find(filters).limit(max(limit, 0)) return [self._doc_to_product(doc) for doc in cursor] def get_categories(self) -> List[str]: self._ensure_collection() field = self.config.category_column or "category" categories = self.collection.distinct(field) return sorted(filter(None, categories)) def get_brands(self) -> List[str]: self._ensure_collection() field = self.config.brand_column or "brand" brands = self.collection.distinct(field) return sorted(filter(None, brands)) class SQLiteAdapter(DatabaseAdapter): """SQLite database adapter.""" def __init__(self, config: DatabaseConfig): super().__init__(config) self.connection: Optional[sqlite3.Connection] = None def connect(self) -> None: """Connect to SQLite database.""" try: # Parse SQLite connection string to extract file path conn_str = self.config.connection_string if conn_str.startswith("sqlite:///"): # Remove sqlite:/// prefix db_path = conn_str.replace("sqlite:///", "") elif conn_str.startswith("sqlite://"): # Handle sqlite:// (without trailing /) db_path = conn_str.replace("sqlite://", "") else: # Already a file path db_path = conn_str # Ensure path exists (create directory if needed) from pathlib import Path db_path_obj = Path(db_path) if not db_path_obj.parent.exists(): db_path_obj.parent.mkdir(parents=True, exist_ok=True) # Enable SQLite thread-safe mode (check_same_thread=False allows connections from different threads) # SQLite handles thread safety internally when using this mode self.connection = sqlite3.connect(db_path, check_same_thread=False) self.connection.row_factory = sqlite3.Row except sqlite3.Error as e: raise DatabaseError(f"Failed to connect to SQLite database: {e}") def disconnect(self) -> None: """Close SQLite connection.""" if self.connection: self.connection.close() self.connection = None def get_all_products(self) -> Iterator[Product]: """Get all products from SQLite database.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} """ cursor = self.connection.execute(query) for row in cursor: # SQLite Row supports dict-like access but not .get() method # Check if keys exist using 'key' in row.keys() row_keys = row.keys() yield Product( id=str(row['id']), title=str(row['title']), description=row['description'] if row['description'] else '', price=float(row['price']) if row['price'] else 0.0, currency=row['currency'] if 'currency' in row_keys else 'USD', category=row['category'] if row['category'] else '', brand=row['brand'] if row['brand'] else '', image_url=row['image_url'] if row['image_url'] else '', availability=row['availability'] if 'availability' in row_keys else 'in_stock', rating=float(row['rating']) if row['rating'] else None, review_count=int(row['review_count']) if 'review_count' in row_keys and row['review_count'] else None ) except sqlite3.Error as e: raise DatabaseError(f"Failed to fetch products: {e}") def get_product_count(self) -> int: """Get total number of products.""" if not self.connection: raise DatabaseError("Database not connected") try: cursor = self.connection.execute(f"SELECT COUNT(*) FROM {self.config.table_name}") return cursor.fetchone()[0] except sqlite3.Error as e: raise DatabaseError(f"Failed to get product count: {e}") def get_product_by_id(self, product_id: str) -> Optional[Product]: """Get a specific product by ID.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} WHERE {self.config.id_column} = ? """ cursor = self.connection.execute(query, (product_id,)) row = cursor.fetchone() if not row: return None # SQLite Row supports dict-like access but not .get() method row_keys = row.keys() return Product( id=str(row['id']), title=str(row['title']), description=row['description'] if row['description'] else '', price=float(row['price']) if row['price'] else 0.0, currency=row['currency'] if 'currency' in row_keys else 'USD', category=row['category'] if row['category'] else '', brand=row['brand'] if row['brand'] else '', image_url=row['image_url'] if row['image_url'] else '', availability=row['availability'] if 'availability' in row_keys else 'in_stock', rating=float(row['rating']) if row['rating'] else None, review_count=int(row['review_count']) if 'review_count' in row_keys and row['review_count'] else None ) except sqlite3.Error as e: raise DatabaseError(f"Failed to fetch product {product_id}: {e}") class PostgreSQLAdapter(DatabaseAdapter): """PostgreSQL database adapter.""" def __init__(self, config: DatabaseConfig): super().__init__(config) self.connection = None def connect(self) -> None: """Connect to PostgreSQL database.""" try: import psycopg2 self.connection = psycopg2.connect(self.config.connection_string) except ImportError: raise DatabaseError("psycopg2 is required for PostgreSQL support. Install with: pip install psycopg2-binary") except Exception as e: raise DatabaseError(f"Failed to connect to PostgreSQL database: {e}") def disconnect(self) -> None: """Close PostgreSQL connection.""" if self.connection: self.connection.close() self.connection = None def get_all_products(self) -> Iterator[Product]: """Get all products from PostgreSQL database.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} """ cursor = self.connection.cursor() cursor.execute(query) for row in cursor.fetchall(): yield Product( id=str(row[0]), title=str(row[1]), description=row[7] if row[7] else '', price=float(row[5]) if row[5] else 0.0, currency=row[8] if len(row) > 8 and row[8] else 'USD', category=row[3] if row[3] else '', brand=row[4] if row[4] else '', image_url=row[2] if row[2] else '', availability=row[9] if len(row) > 9 and row[9] else 'in_stock', rating=float(row[6]) if row[6] else None ) except Exception as e: raise DatabaseError(f"Failed to fetch products: {e}") def get_product_count(self) -> int: """Get total number of products.""" if not self.connection: raise DatabaseError("Database not connected") try: cursor = self.connection.cursor() cursor.execute(f"SELECT COUNT(*) FROM {self.config.table_name}") return cursor.fetchone()[0] except Exception as e: raise DatabaseError(f"Failed to get product count: {e}") def get_product_by_id(self, product_id: str) -> Optional[Product]: """Get a specific product by ID.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} WHERE {self.config.id_column} = %s """ cursor = self.connection.cursor() cursor.execute(query, (product_id,)) row = cursor.fetchone() if not row: return None return Product( id=str(row[0]), title=str(row[1]), description=row[7] if row[7] else '', price=float(row[5]) if row[5] else 0.0, currency=row[8] if len(row) > 8 and row[8] else 'USD', category=row[3] if row[3] else '', brand=row[4] if row[4] else '', image_url=row[2] if row[2] else '', availability=row[9] if len(row) > 9 and row[9] else 'in_stock', rating=float(row[6]) if row[6] else None ) except Exception as e: raise DatabaseError(f"Failed to fetch product {product_id}: {e}") class MySQLAdapter(DatabaseAdapter): """MySQL database adapter.""" def __init__(self, config: DatabaseConfig): super().__init__(config) self.connection = None def connect(self) -> None: """Connect to MySQL database.""" try: import mysql.connector self.connection = mysql.connector.connect(self.config.connection_string) except ImportError: raise DatabaseError("mysql-connector-python is required for MySQL support. Install with: pip install mysql-connector-python") except Exception as e: raise DatabaseError(f"Failed to connect to MySQL database: {e}") def disconnect(self) -> None: """Close MySQL connection.""" if self.connection: self.connection.close() self.connection = None def get_all_products(self) -> Iterator[Product]: """Get all products from MySQL database.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} """ cursor = self.connection.cursor(dictionary=True) cursor.execute(query) for row in cursor.fetchall(): yield Product( id=str(row['id']), title=str(row['title']), description=row['description'] if row['description'] else '', price=float(row['price']) if row['price'] else 0.0, currency=row.get('currency', 'USD'), category=row['category'] if row['category'] else '', brand=row['brand'] if row['brand'] else '', image_url=row['image_url'] if row['image_url'] else '', availability=row.get('availability', 'in_stock'), rating=float(row['rating']) if row['rating'] else None ) except Exception as e: raise DatabaseError(f"Failed to fetch products: {e}") def get_product_count(self) -> int: """Get total number of products.""" if not self.connection: raise DatabaseError("Database not connected") try: cursor = self.connection.cursor() cursor.execute(f"SELECT COUNT(*) FROM {self.config.table_name}") return cursor.fetchone()[0] except Exception as e: raise DatabaseError(f"Failed to get product count: {e}") def get_product_by_id(self, product_id: str) -> Optional[Product]: """Get a specific product by ID.""" if not self.connection: raise DatabaseError("Database not connected") try: query = f""" SELECT {self.config.id_column} as id, {self.config.title_column} as title, {self.config.image_url_column} as image_url, {self.config.category_column or 'NULL'} as category, {self.config.brand_column or 'NULL'} as brand, {self.config.price_column or 'NULL'} as price, {self.config.rating_column or 'NULL'} as rating, {self.config.description_column or 'NULL'} as description, COALESCE(currency, 'USD') as currency, COALESCE(availability, 'in_stock') as availability FROM {self.config.table_name} WHERE {self.config.id_column} = %s """ cursor = self.connection.cursor(dictionary=True) cursor.execute(query, (product_id,)) row = cursor.fetchone() if not row: return None return Product( id=str(row['id']), title=str(row['title']), description=row['description'] if row['description'] else '', price=float(row['price']) if row['price'] else 0.0, currency=row.get('currency', 'USD'), category=row['category'] if row['category'] else '', brand=row['brand'] if row['brand'] else '', image_url=row['image_url'] if row['image_url'] else '', availability=row.get('availability', 'in_stock'), rating=float(row['rating']) if row['rating'] else None ) except Exception as e: raise DatabaseError(f"Failed to fetch product {product_id}: {e}") def create_database_adapter(config: DatabaseConfig) -> DatabaseAdapter: """Factory function to create appropriate database adapter.""" connection_string = config.connection_string.lower() if connection_string.startswith('sqlite'): return SQLiteAdapter(config) elif connection_string.startswith('postgresql') or connection_string.startswith('postgres'): return PostgreSQLAdapter(config) elif connection_string.startswith('mysql'): return MySQLAdapter(config) elif connection_string.startswith('mongodb'): return MongoDBAdapter(config) else: raise DatabaseError(f"Unsupported database type in connection string: {config.connection_string}")