"""Booking Import Service. Supports import from CSV, JSON (MEC format), and Excel files. Features: - Automatic customer matching/creation by email - Overwrite protection (skip existing bookings by default) - Validation and error reporting - Support for MEC WordPress export format """ import contextlib import csv import io import json import logging from datetime import UTC, datetime from decimal import Decimal, InvalidOperation from typing import Any from customer_portal.models.booking import Booking from customer_portal.models.customer import Customer logger = logging.getLogger(__name__) class BookingImportService: """Service for importing bookings from various file formats.""" # Field mappings for different formats FIELD_MAPPINGS = { # CSV/Excel column -> internal field "id": "wp_booking_id", "wp_id": "wp_booking_id", "wordpress_id": "wp_booking_id", "buchungsnummer": "booking_number", "booking_number": "booking_number", "number": "booking_number", "kurs_id": "wp_kurs_id", "kurs": "kurs_title", "kurs_title": "kurs_title", "kurs_titel": "kurs_title", "course": "kurs_title", "course_title": "kurs_title", "datum": "kurs_date", "date": "kurs_date", "kurs_date": "kurs_date", "kurs_datum": "kurs_date", "uhrzeit": "kurs_time", "time": "kurs_time", "kurs_time": "kurs_time", "end_time": "kurs_end_time", "kurs_end_time": "kurs_end_time", "ort": "kurs_location", "location": "kurs_location", "kurs_location": "kurs_location", "status": "status", "buchungsstatus": "status", "preis": "total_price", "price": "total_price", "total_price": "total_price", "gesamtpreis": "total_price", "ticket_typ": "ticket_type", "ticket_type": "ticket_type", "anzahl": "ticket_count", "count": "ticket_count", "ticket_count": "ticket_count", "name": "customer_name", "kunde": "customer_name", "customer_name": "customer_name", "kundenname": "customer_name", "email": "customer_email", "e-mail": "customer_email", "customer_email": "customer_email", "telefon": "customer_phone", "phone": "customer_phone", "customer_phone": "customer_phone", "sevdesk_invoice_id": "sevdesk_invoice_id", "rechnung_id": "sevdesk_invoice_id", "sevdesk_invoice_number": "sevdesk_invoice_number", "rechnungsnummer": "sevdesk_invoice_number", "erstellt": "wp_created_at", "created_at": "wp_created_at", "wp_created_at": "wp_created_at", } # Status mappings (German -> internal) STATUS_MAPPINGS = { "bestaetigt": "confirmed", "bestätigt": "confirmed", "confirmed": "confirmed", "ausstehend": "pending", "pending": "pending", "storniert": "cancelled", "cancelled": "cancelled", "canceled": "cancelled", "stornierung angefragt": "cancel_requested", "cancel_requested": "cancel_requested", } @classmethod def import_from_csv( cls, db_session, file_content: str | bytes, overwrite: bool = False, delimiter: str = ";", ) -> dict[str, Any]: """Import bookings from CSV file. Args: db_session: Database session file_content: CSV file content (string or bytes) overwrite: If True, update existing bookings. If False, skip them. delimiter: CSV delimiter (default: semicolon for German Excel) Returns: Import result dictionary """ if isinstance(file_content, bytes): # Try UTF-8 with BOM first, then UTF-8, then Latin-1 for encoding in ["utf-8-sig", "utf-8", "latin-1"]: try: file_content = file_content.decode(encoding) break except UnicodeDecodeError: continue reader = csv.DictReader(io.StringIO(file_content), delimiter=delimiter) rows = list(reader) return cls._import_rows(db_session, rows, overwrite, source="CSV") @classmethod def import_from_json( cls, db_session, file_content: str | bytes, overwrite: bool = False, ) -> dict[str, Any]: """Import bookings from JSON file (MEC format). Supports both: - Array of booking objects: [{"id": 1, ...}, ...] - MEC export format: {"bookings": [...], "meta": {...}} Args: db_session: Database session file_content: JSON file content overwrite: If True, update existing bookings Returns: Import result dictionary """ if isinstance(file_content, bytes): file_content = file_content.decode("utf-8-sig") data = json.loads(file_content) # Handle different JSON structures if isinstance(data, list): rows = data elif isinstance(data, dict): # MEC export format or similar rows = data.get("bookings", data.get("data", data.get("items", []))) if not rows and "id" in data: # Single booking object rows = [data] else: return { "success": False, "error": "Ungültiges JSON-Format", "created": 0, "updated": 0, "skipped": 0, "errors": [], } return cls._import_rows(db_session, rows, overwrite, source="JSON") @classmethod def import_from_excel( cls, db_session, file_content: bytes, overwrite: bool = False, ) -> dict[str, Any]: """Import bookings from Excel file (.xlsx). Args: db_session: Database session file_content: Excel file content (bytes) overwrite: If True, update existing bookings Returns: Import result dictionary """ try: import openpyxl except ImportError: return { "success": False, "error": "openpyxl nicht installiert. Bitte 'pip install openpyxl' ausführen.", "created": 0, "updated": 0, "skipped": 0, "errors": [], } try: workbook = openpyxl.load_workbook(io.BytesIO(file_content), data_only=True) sheet = workbook.active # Get headers from first row headers = [cell.value for cell in sheet[1] if cell.value] if not headers: return { "success": False, "error": "Keine Spaltenüberschriften gefunden", "created": 0, "updated": 0, "skipped": 0, "errors": [], } # Convert rows to dictionaries rows = [] for row in sheet.iter_rows(min_row=2, values_only=True): if any(cell is not None for cell in row): row_dict = {} for i, value in enumerate(row): if i < len(headers) and headers[i]: row_dict[headers[i]] = value rows.append(row_dict) return cls._import_rows(db_session, rows, overwrite, source="Excel") except Exception as e: logger.exception("Excel import error") return { "success": False, "error": f"Excel-Lesefehler: {e!s}", "created": 0, "updated": 0, "skipped": 0, "errors": [], } @classmethod def _import_rows( cls, db_session, rows: list[dict], overwrite: bool, source: str, ) -> dict[str, Any]: """Process and import rows. Args: db_session: Database session rows: List of row dictionaries overwrite: If True, update existing bookings source: Source format name for logging Returns: Import result dictionary """ result = { "success": True, "source": source, "total_rows": len(rows), "created": 0, "updated": 0, "skipped": 0, "skipped_existing": 0, "errors": [], "warnings": [], } if not rows: result["warnings"].append("Keine Daten zum Importieren gefunden") return result for row_num, row in enumerate(rows, start=2): # Start at 2 (header is row 1) try: import_result = cls._import_single_booking( db_session, row, overwrite, row_num ) if import_result["status"] == "created": result["created"] += 1 elif import_result["status"] == "updated": result["updated"] += 1 elif import_result["status"] == "skipped_existing": result["skipped_existing"] += 1 result["skipped"] += 1 elif import_result["status"] == "skipped": result["skipped"] += 1 if import_result.get("reason"): result["warnings"].append( f"Zeile {row_num}: {import_result['reason']}" ) if import_result.get("error"): result["errors"].append( f"Zeile {row_num}: {import_result['error']}" ) except Exception as e: logger.exception(f"Error importing row {row_num}") result["errors"].append(f"Zeile {row_num}: {e!s}") # Commit if we have any successful imports if result["created"] > 0 or result["updated"] > 0: try: db_session.commit() except Exception as e: db_session.rollback() result["success"] = False result["error"] = f"Datenbank-Fehler: {e!s}" result["created"] = 0 result["updated"] = 0 return result @classmethod def _import_single_booking( cls, db_session, row: dict, overwrite: bool, row_num: int, ) -> dict[str, Any]: """Import a single booking row. Args: db_session: Database session row: Row dictionary overwrite: If True, update existing bookings row_num: Row number for error reporting Returns: Single import result """ # Normalize field names normalized = cls._normalize_row(row) # Validate required fields if not normalized.get("customer_email"): return {"status": "skipped", "reason": "Keine E-Mail-Adresse"} # Find or create customer customer = cls._find_or_create_customer(db_session, normalized) # Check for existing booking wp_booking_id = normalized.get("wp_booking_id") booking_number = normalized.get("booking_number") existing = None if wp_booking_id: existing = ( db_session.query(Booking) .filter(Booking.wp_booking_id == int(wp_booking_id)) .first() ) if not existing and booking_number: existing = ( db_session.query(Booking) .filter(Booking.booking_number == str(booking_number)) .first() ) # Overwrite protection if existing and not overwrite: return { "status": "skipped_existing", "reason": f"Buchung existiert bereits (ID: {existing.id})", } # Create or update booking if existing: booking = existing status = "updated" else: # Generate wp_booking_id if not provided if not wp_booking_id: # Use negative IDs for imported bookings without WP ID max_negative = ( db_session.query(Booking.wp_booking_id) .filter(Booking.wp_booking_id < 0) .order_by(Booking.wp_booking_id.asc()) .first() ) wp_booking_id = (max_negative[0] - 1) if max_negative else -1 booking = Booking( customer_id=customer.id, wp_booking_id=int(wp_booking_id), created_at=datetime.now(UTC), ) db_session.add(booking) status = "created" # Update booking fields cls._update_booking_fields(booking, normalized) booking.customer_id = customer.id booking.synced_at = datetime.now(UTC) booking.updated_at = datetime.now(UTC) return {"status": status, "booking_id": booking.id if existing else None} @classmethod def _normalize_row(cls, row: dict) -> dict: """Normalize row field names to internal format. Args: row: Raw row dictionary Returns: Normalized dictionary """ normalized = {} custom_fields = {} # Handle nested 'customer' object (MEC format) if "customer" in row and isinstance(row["customer"], dict): customer_data = row["customer"] if customer_data.get("email"): normalized["customer_email"] = customer_data["email"] if customer_data.get("name"): normalized["customer_name"] = customer_data["name"] if customer_data.get("phone"): normalized["customer_phone"] = customer_data["phone"] for key, value in row.items(): if value is None or value == "": continue # Skip nested objects (already processed above) if isinstance(value, dict): continue # Convert key to lowercase and strip key_lower = str(key).lower().strip() # Check if it's a known field if key_lower in cls.FIELD_MAPPINGS: internal_key = cls.FIELD_MAPPINGS[key_lower] normalized[internal_key] = value else: # Store as custom field custom_fields[key] = value if custom_fields: normalized["custom_fields"] = custom_fields return normalized @classmethod def _find_or_create_customer(cls, db_session, data: dict) -> Customer: """Find existing customer or create new one. Args: db_session: Database session data: Normalized booking data Returns: Customer instance """ email = data.get("customer_email", "").lower().strip() customer = db_session.query(Customer).filter(Customer.email == email).first() if not customer: # Sprint 12: All customer data goes to custom_fields full_name = data.get("customer_name", "") phone = data.get("customer_phone", "") # Store name and phone in custom_fields custom_fields = {} if full_name: custom_fields["name"] = full_name if phone: custom_fields["phone"] = phone # Create customer with only required fields customer = Customer( email=email, custom_fields=custom_fields if custom_fields else None, created_at=datetime.now(UTC), ) db_session.add(customer) db_session.flush() # Get ID return customer @classmethod def _update_booking_fields(cls, booking: Booking, data: dict) -> None: """Update booking fields from normalized data. Args: booking: Booking instance data: Normalized data dictionary """ # Direct field mappings if data.get("wp_kurs_id"): booking.wp_kurs_id = int(data["wp_kurs_id"]) if data.get("booking_number"): booking.booking_number = str(data["booking_number"]) if data.get("kurs_title"): booking.kurs_title = str(data["kurs_title"]) if data.get("kurs_location"): booking.kurs_location = str(data["kurs_location"]) if data.get("ticket_type"): booking.ticket_type = str(data["ticket_type"]) if data.get("ticket_count"): try: booking.ticket_count = int(data["ticket_count"]) except (ValueError, TypeError): booking.ticket_count = 1 # Status with mapping if data.get("status"): status_raw = str(data["status"]).lower().strip() booking.status = cls.STATUS_MAPPINGS.get(status_raw, status_raw) # Price parsing if data.get("total_price"): booking.total_price = cls._parse_price(data["total_price"]) # Date parsing if data.get("kurs_date"): booking.kurs_date = cls._parse_date(data["kurs_date"]) # Time fields if data.get("kurs_time"): booking.kurs_time = cls._parse_time(data["kurs_time"]) if data.get("kurs_end_time"): booking.kurs_end_time = cls._parse_time(data["kurs_end_time"]) # Customer snapshot if data.get("customer_name"): booking.customer_name = str(data["customer_name"]) if data.get("customer_email"): booking.customer_email = str(data["customer_email"]) if data.get("customer_phone"): booking.customer_phone = str(data["customer_phone"]) # sevDesk if data.get("sevdesk_invoice_id"): with contextlib.suppress(ValueError, TypeError): booking.sevdesk_invoice_id = int(data["sevdesk_invoice_id"]) if data.get("sevdesk_invoice_number"): booking.sevdesk_invoice_number = str(data["sevdesk_invoice_number"]) # WordPress created date if data.get("wp_created_at"): wp_created = cls._parse_datetime(data["wp_created_at"]) if wp_created: booking.wp_created_at = wp_created # Custom fields if data.get("custom_fields"): existing = booking.custom_fields or {} existing.update(data["custom_fields"]) booking.custom_fields = existing @classmethod def _parse_price(cls, value: Any) -> Decimal | None: """Parse price value to Decimal. Handles German format (1.234,56) and English format (1,234.56) """ if value is None: return None if isinstance(value, (int, float, Decimal)): return Decimal(str(value)) # String parsing price_str = str(value).strip() # Remove currency symbols and whitespace for char in ["EUR", "€", "$", " "]: price_str = price_str.replace(char, "") if not price_str: return None # Detect format and normalize # German: 1.234,56 -> English: 1234.56 if "," in price_str and "." in price_str: if price_str.rfind(",") > price_str.rfind("."): # German format: 1.234,56 price_str = price_str.replace(".", "").replace(",", ".") else: # English format: 1,234.56 price_str = price_str.replace(",", "") elif "," in price_str: # Could be German decimal: 12,50 price_str = price_str.replace(",", ".") try: return Decimal(price_str) except InvalidOperation: return None @classmethod def _parse_date(cls, value: Any): """Parse date value to date object.""" if value is None: return None if hasattr(value, "date"): return value.date() if hasattr(value, "date") else value if isinstance(value, str): value = value.strip() # Try various formats formats = [ "%Y-%m-%d", # ISO: 2024-01-15 "%d.%m.%Y", # German: 15.01.2024 "%d/%m/%Y", # European: 15/01/2024 "%m/%d/%Y", # US: 01/15/2024 "%Y-%m-%d %H:%M:%S", # ISO with time "%d.%m.%Y %H:%M", # German with time ] for fmt in formats: try: return datetime.strptime(value, fmt).date() except ValueError: continue return None @classmethod def _parse_time(cls, value: Any) -> str | None: """Parse time value to HH:MM string.""" if value is None: return None if hasattr(value, "strftime"): return value.strftime("%H:%M") time_str = str(value).strip() # Already in correct format if len(time_str) == 5 and time_str[2] == ":": return time_str # Handle H:MM format if len(time_str) == 4 and time_str[1] == ":": return f"0{time_str}" # Handle HHMM format if len(time_str) == 4 and time_str.isdigit(): return f"{time_str[:2]}:{time_str[2:]}" return time_str[:5] if len(time_str) >= 5 else time_str @classmethod def _parse_datetime(cls, value: Any): """Parse datetime value.""" if value is None: return None if isinstance(value, datetime): return value if isinstance(value, str): value = value.strip() formats = [ "%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%dT%H:%M:%SZ", "%d.%m.%Y %H:%M:%S", "%d.%m.%Y %H:%M", "%Y-%m-%d", ] for fmt in formats: try: return datetime.strptime(value, fmt) except ValueError: continue return None @classmethod def get_import_template_csv(cls) -> str: """Generate CSV template for import. Returns: CSV template string """ headers = [ "buchungsnummer", "email", "name", "telefon", "kurs", "datum", "uhrzeit", "ort", "status", "preis", "ticket_typ", "anzahl", ] return ";".join(headers) + "\n" @classmethod def get_import_template_json(cls) -> str: """Generate JSON template for import. Returns: JSON template string """ template = { "bookings": [ { "id": 1234, "number": "KB-2024-0001", "customer": { "email": "kunde@example.com", "name": "Max Mustermann", "phone": "+43 123 456789", }, "kurs_title": "Beispielkurs", "kurs_date": "2024-01-15", "kurs_time": "10:00", "kurs_location": "Wien", "status": "confirmed", "price": 150.00, "ticket_type": "Standard", "ticket_count": 1, "custom_fields": { "Zusatzfeld 1": "Wert 1", }, } ] } return json.dumps(template, indent=2, ensure_ascii=False)