""" Schema mapping system for flexible database integration. Allows customers to map their existing database schema to our product model. """ from typing import Dict, Any, Optional, Callable, Union from dataclasses import dataclass import json from .models import Product from .exceptions import ValidationError @dataclass class FieldMapping: """Mapping configuration for a single field.""" source_column: str target_field: str transform_function: Optional[Callable] = None default_value: Any = None required: bool = False @dataclass class SchemaMapping: """Complete schema mapping configuration.""" table_name: str id_mapping: FieldMapping title_mapping: FieldMapping image_url_mapping: FieldMapping category_mapping: Optional[FieldMapping] = None brand_mapping: Optional[FieldMapping] = None price_mapping: Optional[FieldMapping] = None rating_mapping: Optional[FieldMapping] = None description_mapping: Optional[FieldMapping] = None custom_mappings: Dict[str, FieldMapping] = None def __post_init__(self): if self.custom_mappings is None: self.custom_mappings = {} class SchemaMapper: """Maps customer database schemas to our Product model.""" def __init__(self, mapping: SchemaMapping): self.mapping = mapping def map_row_to_product(self, row: Dict[str, Any]) -> Product: """Convert a database row to a Product object using the mapping.""" try: # Extract required fields product_id = self._extract_field(row, self.mapping.id_mapping) title = self._extract_field(row, self.mapping.title_mapping) image_url = self._extract_field(row, self.mapping.image_url_mapping) # Extract optional fields category = self._extract_field(row, self.mapping.category_mapping) brand = self._extract_field(row, self.mapping.brand_mapping) price = self._extract_field(row, self.mapping.price_mapping) rating = self._extract_field(row, self.mapping.rating_mapping) description = self._extract_field(row, self.mapping.description_mapping) # Extract custom fields metadata = {} for field_name, field_mapping in self.mapping.custom_mappings.items(): value = self._extract_field(row, field_mapping) if value is not None: metadata[field_name] = value return Product( id=str(product_id), title=str(title), image_url=image_url, category=category, brand=brand, price=float(price) if price is not None else None, rating=float(rating) if rating is not None else None, description=description, metadata=metadata ) except Exception as e: raise ValidationError(f"Failed to map row to product: {e}") def _extract_field(self, row: Dict[str, Any], mapping: Optional[FieldMapping]) -> Any: """Extract and transform a field from a database row.""" if mapping is None: return None # Get raw value from row raw_value = row.get(mapping.source_column) # Use default if value is None if raw_value is None: if mapping.required: raise ValidationError(f"Required field '{mapping.source_column}' is missing") return mapping.default_value # Apply transformation if provided if mapping.transform_function: try: return mapping.transform_function(raw_value) except Exception as e: raise ValidationError(f"Transform failed for field '{mapping.source_column}': {e}") return raw_value class SchemaMappingBuilder: """Builder for creating schema mappings with common patterns.""" @staticmethod def from_dict(config: Dict[str, Any]) -> SchemaMapping: """Create schema mapping from dictionary configuration.""" return SchemaMapping( table_name=config['table_name'], id_mapping=FieldMapping(**config['id_mapping']), title_mapping=FieldMapping(**config['title_mapping']), image_url_mapping=FieldMapping(**config['image_url_mapping']), category_mapping=FieldMapping(**config['category_mapping']) if config.get('category_mapping') else None, brand_mapping=FieldMapping(**config['brand_mapping']) if config.get('brand_mapping') else None, price_mapping=FieldMapping(**config['price_mapping']) if config.get('price_mapping') else None, rating_mapping=FieldMapping(**config['rating_mapping']) if config.get('rating_mapping') else None, description_mapping=FieldMapping(**config['description_mapping']) if config.get('description_mapping') else None, custom_mappings={ name: FieldMapping(**mapping_config) for name, mapping_config in config.get('custom_mappings', {}).items() } ) @staticmethod def from_json(json_config: str) -> SchemaMapping: """Create schema mapping from JSON configuration.""" config = json.loads(json_config) return SchemaMappingBuilder.from_dict(config) @staticmethod def create_simple_mapping( table_name: str, id_column: str, title_column: str, image_url_column: str, 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 ) -> SchemaMapping: """Create a simple schema mapping with basic field mappings.""" return SchemaMapping( table_name=table_name, id_mapping=FieldMapping(source_column=id_column, target_field="id", required=True), title_mapping=FieldMapping(source_column=title_column, target_field="title", required=True), image_url_mapping=FieldMapping(source_column=image_url_column, target_field="image_url", required=True), category_mapping=FieldMapping(source_column=category_column, target_field="category") if category_column else None, brand_mapping=FieldMapping(source_column=brand_column, target_field="brand") if brand_column else None, price_mapping=FieldMapping(source_column=price_column, target_field="price") if price_column else None, rating_mapping=FieldMapping(source_column=rating_column, target_field="rating") if rating_column else None, description_mapping=FieldMapping(source_column=description_column, target_field="description") if description_column else None ) # Common transform functions class TransformFunctions: """Common transformation functions for field mapping.""" @staticmethod def to_string(value: Any) -> str: """Convert value to string.""" return str(value) if value is not None else None @staticmethod def to_float(value: Any) -> float: """Convert value to float.""" if value is None: return None try: return float(value) except (ValueError, TypeError): return None @staticmethod def to_int(value: Any) -> int: """Convert value to integer.""" if value is None: return None try: return int(value) except (ValueError, TypeError): return None @staticmethod def clean_url(value: Any) -> str: """Clean and validate URL.""" if value is None: return None url = str(value).strip() if url and not url.startswith(('http://', 'https://')): return f"https://{url}" return url @staticmethod def extract_category(value: Any) -> str: """Extract category from hierarchical path.""" if value is None: return None # Handle "Electronics > Audio > Headphones" format if ' > ' in str(value): return str(value).split(' > ')[-1] return str(value) @staticmethod def normalize_price(value: Any) -> float: """Normalize price value.""" if value is None: return None try: # Remove currency symbols and convert to float price_str = str(value).replace('$', '').replace(',', '').strip() return float(price_str) except (ValueError, TypeError): return None # Example schema configurations for common e-commerce platforms class CommonSchemas: """Pre-defined schema mappings for common e-commerce platforms.""" @staticmethod def shopify() -> SchemaMapping: """Shopify product schema mapping.""" return SchemaMapping( table_name="products", id_mapping=FieldMapping(source_column="id", target_field="id", required=True), title_mapping=FieldMapping(source_column="title", target_field="title", required=True), image_url_mapping=FieldMapping( source_column="featured_image", target_field="image_url", required=True, transform_function=TransformFunctions.clean_url ), category_mapping=FieldMapping( source_column="product_type", target_field="category", transform_function=TransformFunctions.to_string ), brand_mapping=FieldMapping( source_column="vendor", target_field="brand", transform_function=TransformFunctions.to_string ), price_mapping=FieldMapping( source_column="variants_price", target_field="price", transform_function=TransformFunctions.normalize_price ), description_mapping=FieldMapping( source_column="body_html", target_field="description", transform_function=lambda x: str(x).replace('
', '').replace('
', '') if x else None ) ) @staticmethod def woocommerce() -> SchemaMapping: """WooCommerce product schema mapping.""" return SchemaMapping( table_name="wp_posts", id_mapping=FieldMapping(source_column="ID", target_field="id", required=True), title_mapping=FieldMapping(source_column="post_title", target_field="title", required=True), image_url_mapping=FieldMapping( source_column="post_excerpt", target_field="image_url", required=True, transform_function=TransformFunctions.clean_url ), description_mapping=FieldMapping( source_column="post_content", target_field="description", transform_function=lambda x: str(x).replace('', '').replace('
', '') if x else None ), custom_mappings={ 'category': FieldMapping( source_column="post_category", target_field="category", transform_function=TransformFunctions.to_string ), 'price': FieldMapping( source_column="meta_value", target_field="price", transform_function=TransformFunctions.normalize_price ) } ) @staticmethod def magento() -> SchemaMapping: """Magento product schema mapping.""" return SchemaMapping( table_name="catalog_product_entity", id_mapping=FieldMapping(source_column="entity_id", target_field="id", required=True), title_mapping=FieldMapping(source_column="name", target_field="title", required=True), image_url_mapping=FieldMapping( source_column="image", target_field="image_url", required=True, transform_function=TransformFunctions.clean_url ), category_mapping=FieldMapping( source_column="category_path", target_field="category", transform_function=TransformFunctions.extract_category ), brand_mapping=FieldMapping( source_column="manufacturer", target_field="brand", transform_function=TransformFunctions.to_string ), price_mapping=FieldMapping( source_column="price", target_field="price", transform_function=TransformFunctions.normalize_price ), rating_mapping=FieldMapping( source_column="rating_summary", target_field="rating", transform_function=lambda x: float(x) / 20 if x else None # Convert to 5-star scale ) ) @staticmethod def custom_json() -> str: """Return a JSON template for custom schema mapping.""" return json.dumps({ "table_name": "your_products_table", "id_mapping": { "source_column": "product_id", "target_field": "id", "required": True }, "title_mapping": { "source_column": "product_name", "target_field": "title", "required": True }, "image_url_mapping": { "source_column": "image_url", "target_field": "image_url", "required": True, "transform_function": "clean_url" }, "category_mapping": { "source_column": "category", "target_field": "category" }, "brand_mapping": { "source_column": "brand", "target_field": "brand" }, "price_mapping": { "source_column": "price", "target_field": "price", "transform_function": "normalize_price" }, "rating_mapping": { "source_column": "rating", "target_field": "rating" }, "description_mapping": { "source_column": "description", "target_field": "description" }, "custom_mappings": { "sku": { "source_column": "sku", "target_field": "sku" }, "stock": { "source_column": "stock_quantity", "target_field": "stock" } } }, indent=2)