#!/usr/bin/env python3
"""
MEC Export-Dateien zu Kurs-Booking Import

Importiert Events und Buchungen aus MEC Export-Dateien (JSON/CSV)
in das Kurs-Booking Plugin.

Usage:
    python3 mec_import_files.py --events events.json [--dry-run]
    python3 mec_import_files.py --bookings bookings.csv [--dry-run]
    python3 mec_import_files.py --events events.json --bookings bookings.csv

Options:
    --events FILE       MEC Events JSON-Export
    --bookings FILE     MEC Buchungen CSV-Export (Tab-separiert)
    --dry-run           Nur anzeigen, nicht importieren
    --limit N           Anzahl begrenzen
    --force             Bereits importierte erneut importieren
"""

import csv
import json
import subprocess
import sys
import argparse
import re
from pathlib import Path
from typing import Any

# WordPress Container (Staging)
WP_CONTAINER = "mec_wordpress_staging"

# Status-Mapping für CSV-Import
# Bestätigung + Verifizierung → Buchungs-Status
CSV_STATUS_MAPPING = {
    ("Bestätigt", "Verifiziert"): "confirmed",
    ("Bestätigt", "Nicht verifiziert"): "pending",
    ("Confirmed", "Verified"): "confirmed",
    ("Confirmed", "Not Verified"): "pending",
}

# CSV-Spalten (deutsch)
CSV_COLUMNS = {
    "id": "ID",
    "event_title": "Veranstaltung",
    "start_datetime": "Anfangsdatum & Uhrzeit",
    "end_datetime": "Enddatum & Schlusszeit",
    "location": "Standort",
    "order_time": "Bestellzeit",
    "course": "Kurs",
    "transaction_id": "Transaktions-ID",
    "total_price": "Gesamtpreis",
    "gateway": "Schnittstelle",
    "name": "Name",
    "email": "E-Mail",
    "ticket_variant": "Variante des Tickets",
    "confirmation": "Bestätigung",
    "verification": "Verifizierung",
    "other_dates": "Andere Termine",
    "checkin_status": "Checkin Status",
    "checkin_datetime": "Checkin DateTime",
    "confirmation_number": "Bestätigungsnummer",
    "phone": "Telefonnummer",
}

# Custom Fields (werden als JSON gespeichert)
CUSTOM_FIELD_COLUMNS = [
    "Name der Eltern",
    "Nachname der Eltern",
    "Geburtsdatum (bei Kindern und Jugendlichen)",
    "Adresse",
    "Name des Pferdes",
    "Geschlecht des Pferdes",
    "Ich benötige eine Gastbox",
    "Anzahl der Gastboxen",
]


def run_wp_eval(php_code: str) -> str:
    """Führt PHP-Code via WP-CLI aus."""
    cmd = [
        "docker", "exec", WP_CONTAINER,
        "wp", "eval", php_code, "--allow-root",
    ]
    result = subprocess.run(cmd, capture_output=True, text=True)
    return result.stdout.strip()


def get_event_id_mapping() -> dict[int, int]:
    """Erstellt Mapping von MEC Event-ID zu Kurs Post-ID."""
    php_code = """
$posts = get_posts(array(
    'post_type' => 'kurs',
    'posts_per_page' => -1,
    'meta_key' => '_mec_original_id',
));
$mapping = array();
foreach ($posts as $post) {
    $mec_id = get_post_meta($post->ID, '_mec_original_id', true);
    if ($mec_id) {
        $mapping[$mec_id] = $post->ID;
    }
}
echo json_encode($mapping);
"""
    result = run_wp_eval(php_code)
    if result:
        try:
            mapping = json.loads(result)
            return {int(k): int(v) for k, v in mapping.items()}
        except (json.JSONDecodeError, ValueError):
            pass
    return {}


def get_title_to_kurs_mapping() -> dict[str, int]:
    """Erstellt Mapping von Event-Titel zu Kurs Post-ID."""
    php_code = """
$posts = get_posts(array(
    'post_type' => 'kurs',
    'posts_per_page' => -1,
));
$mapping = array();
foreach ($posts as $post) {
    $mapping[$post->post_title] = $post->ID;
}
echo json_encode($mapping);
"""
    result = run_wp_eval(php_code)
    if result:
        try:
            return json.loads(result)
        except json.JSONDecodeError:
            pass
    return {}


# ============================================================================
# JSON EVENT IMPORT
# ============================================================================


def load_events_json(filepath: Path) -> list[dict]:
    """Lädt Events aus MEC JSON-Export."""
    with open(filepath, "r", encoding="utf-8") as f:
        data = json.load(f)

    # Das JSON ist ein Array von Event-Objekten
    if isinstance(data, list):
        return data
    elif isinstance(data, dict) and "events" in data:
        return data["events"]
    else:
        return [data]


def transform_json_event(event: dict) -> dict:
    """Transformiert JSON-Event zu Kurs-Booking Format."""
    meta = event.get("meta", {})

    # Basis-Daten
    kurs = {
        "post_title": event.get("title", ""),
        "post_content": event.get("content", ""),
        "post_status": "publish",
        "post_type": "kurs",
        "meta": {},
    }

    # Original MEC ID
    kurs["meta"]["_mec_original_id"] = str(event.get("ID", ""))

    # Produktart und Buchungsart
    kurs["meta"]["_kurs_produktart"] = "kurs"
    kurs["meta"]["_kurs_buchungsart"] = "direkt"

    # Datum
    kurs["meta"]["_kurs_start_date"] = meta.get("mec_start_date", "")
    kurs["meta"]["_kurs_end_date"] = meta.get("mec_end_date", "")

    # Zeit - aus time.start_raw/end_raw wenn vorhanden
    time_data = event.get("time", {})
    if time_data.get("start_raw"):
        kurs["meta"]["_kurs_start_time"] = time_data["start_raw"]
    if time_data.get("end_raw"):
        kurs["meta"]["_kurs_end_time"] = time_data["end_raw"]

    # Durchführungsart
    if meta.get("mec_moved_online_link"):
        kurs["meta"]["_kurs_type"] = "online"
        kurs["meta"]["_kurs_zoom_link"] = meta.get("mec_moved_online_link", "")
    else:
        kurs["meta"]["_kurs_type"] = "vor_ort"

    # Ort - aus locations Object (Name und Adresse separat)
    locations = event.get("locations", {})
    if locations:
        first_location = list(locations.values())[0]
        loc_name = first_location.get("name", "")
        loc_address = first_location.get("address", "")
        loc_url = first_location.get("url", "")

        if loc_name:
            kurs["meta"]["_kurs_location"] = loc_name
        if loc_address:
            kurs["meta"]["_kurs_address"] = loc_address
        if loc_url:
            kurs["meta"]["_kurs_location_url"] = loc_url

    # Buchungs-Limit
    booking_meta = meta.get("mec_booking", {})
    if isinstance(booking_meta, dict):
        limit = booking_meta.get("bookings_limit", 0)
        kurs["meta"]["_kurs_max_participants"] = int(limit) if limit else 0
    kurs["meta"]["_kurs_booking_enabled"] = "1"

    # Abrechnung
    kurs["meta"]["_kurs_abrechnung"] = "eigen"
    kurs["meta"]["_kurs_deposit_enabled"] = "0"
    kurs["meta"]["_kurs_deposit_percent"] = "30"

    # Tickets/Varianten
    tickets = meta.get("mec_tickets", {})
    varianten = []

    if isinstance(tickets, dict):
        for ticket_id, ticket in tickets.items():
            if isinstance(ticket, dict) and ticket.get("name"):
                variante = {
                    "id": int(ticket_id) if str(ticket_id).isdigit() else 0,
                    "name": ticket.get("name", "Standard-Ticket"),
                    "preis": str(ticket.get("price", "0")),
                    "limit": str(ticket.get("limit", "0")),
                }
                varianten.append(variante)

    if not varianten:
        varianten.append({
            "id": 1,
            "name": "Standard-Ticket",
            "preis": "0",
            "limit": "0",
        })

    kurs["meta"]["_kurs_varianten"] = varianten

    return kurs


def check_event_already_imported(mec_id: int) -> bool:
    """Prüft ob ein Event bereits importiert wurde."""
    php_code = f"""
$posts = get_posts(array(
    'post_type' => 'kurs',
    'meta_key' => '_mec_original_id',
    'meta_value' => '{mec_id}',
    'posts_per_page' => 1,
));
echo count($posts) > 0 ? '1' : '0';
"""
    result = run_wp_eval(php_code)
    return result == "1"


def create_kurs_post(kurs_data: dict, dry_run: bool = False) -> int:
    """Erstellt einen neuen Kurs-Post."""
    if dry_run:
        print(f"  [DRY-RUN] Würde erstellen: {kurs_data['post_title']}")
        return 0

    import base64
    title = kurs_data["post_title"].replace("'", "\\'").replace('"', '\\"')
    # Use base64 encoding to preserve HTML and newlines correctly
    content_b64 = base64.b64encode(kurs_data["post_content"].encode('utf-8')).decode('ascii')

    php_code = f"""
$post_data = array(
    'post_title'   => '{title}',
    'post_content' => base64_decode('{content_b64}'),
    'post_status'  => '{kurs_data["post_status"]}',
    'post_type'    => 'kurs',
);
$post_id = wp_insert_post($post_data);
echo $post_id;
"""
    result = run_wp_eval(php_code)

    try:
        post_id = int(result)
    except ValueError:
        print(f"  [ERROR] Konnte Post nicht erstellen: {result}", file=sys.stderr)
        return 0

    if post_id <= 0:
        return 0

    # Meta-Daten speichern
    for key, value in kurs_data["meta"].items():
        if isinstance(value, (list, dict)):
            value_json = json.dumps(value).replace("'", "\\'")
            php_code = f"update_post_meta({post_id}, '{key}', json_decode('{value_json}', true));"
        else:
            value_escaped = str(value).replace("'", "\\'")
            php_code = f"update_post_meta({post_id}, '{key}', '{value_escaped}');"
        run_wp_eval(php_code)

    return post_id


def import_events_from_file(filepath: Path, args) -> dict:
    """Importiert Events aus JSON-Datei."""
    print()
    print("=" * 60)
    print("EVENT-IMPORT aus JSON")
    print("=" * 60)
    print(f"Datei: {filepath}")
    print()

    events = load_events_json(filepath)
    if args.limit:
        events = events[:args.limit]

    print(f"Gefunden: {len(events)} Events")
    print()

    stats = {"total": len(events), "imported": 0, "skipped": 0, "errors": 0}

    # Titel → MEC-ID Mapping erstellen für späteren Booking-Import
    title_to_mec_id = {}

    for i, event in enumerate(events, 1):
        mec_id = event.get("ID", 0)
        title = event.get("title", "Unbekannt")
        title_to_mec_id[title] = mec_id

        print(f"[{i}/{len(events)}] {title}")

        if not args.force and check_event_already_imported(mec_id):
            print(f"  -> Übersprungen (bereits importiert)")
            stats["skipped"] += 1
            continue

        kurs_data = transform_json_event(event)

        # Details anzeigen
        print(f"  MEC ID: {mec_id}")
        print(f"  Datum: {kurs_data['meta'].get('_kurs_start_date', 'N/A')} - {kurs_data['meta'].get('_kurs_end_date', 'N/A')}")
        print(f"  Zeit: {kurs_data['meta'].get('_kurs_start_time', 'N/A')} - {kurs_data['meta'].get('_kurs_end_time', 'N/A')}")
        loc_name = kurs_data['meta'].get('_kurs_location', 'N/A')
        loc_addr = kurs_data['meta'].get('_kurs_address', '')
        print(f"  Ort: {loc_name}")
        if loc_addr:
            print(f"  Adresse: {loc_addr[:60]}")
        print(f"  Varianten: {len(kurs_data['meta'].get('_kurs_varianten', []))}")

        post_id = create_kurs_post(kurs_data, dry_run=args.dry_run)

        if post_id > 0:
            print(f"  -> Importiert als Post ID: {post_id}")
            stats["imported"] += 1
        elif args.dry_run:
            stats["imported"] += 1
        else:
            stats["errors"] += 1

        print()

    # Titel-Mapping speichern für Booking-Import
    return stats, title_to_mec_id


# ============================================================================
# CSV BOOKING IMPORT
# ============================================================================


def load_bookings_csv(filepath: Path) -> list[dict]:
    """Lädt Buchungen aus MEC CSV-Export (Tab-separiert)."""
    bookings = []

    with open(filepath, "r", encoding="utf-8-sig") as f:
        # Tab-separiert lesen
        reader = csv.DictReader(f, delimiter="\t")
        for row in reader:
            bookings.append(row)

    return bookings


def parse_price(price_str: str) -> float:
    """Parst Preisstring zu Float."""
    if not price_str:
        return 0.0
    # "150.00 €" oder "150,00 €" -> 150.00
    cleaned = re.sub(r"[^\d,.]", "", price_str)
    cleaned = cleaned.replace(",", ".")
    try:
        return float(cleaned)
    except ValueError:
        return 0.0


def get_booking_status(confirmation: str, verification: str) -> str:
    """Ermittelt Buchungs-Status aus CSV-Werten."""
    conf_lower = confirmation.lower() if confirmation else ""
    verif_lower = verification.lower() if verification else ""

    if "bestätigt" in conf_lower or "confirmed" in conf_lower:
        if "verifiziert" in verif_lower or "verified" in verif_lower:
            return "confirmed"
        return "pending"
    return "cancelled"


def transform_csv_booking(row: dict, title_to_kurs: dict) -> dict | None:
    """Transformiert CSV-Zeile zu Buchungs-Format."""

    # Event-Titel für Mapping
    event_title = row.get("Veranstaltung", "") or row.get("Kurs", "")

    # Kurs-ID ermitteln
    kurs_id = title_to_kurs.get(event_title)
    if not kurs_id:
        # Versuche partielle Übereinstimmung
        for title, kid in title_to_kurs.items():
            if event_title in title or title in event_title:
                kurs_id = kid
                break

    if not kurs_id:
        print(f"  [WARN] Kein Kurs gefunden für: {event_title}")
        return None

    # Status ermitteln
    confirmation = row.get("Bestätigung", "")
    verification = row.get("Verifizierung", "")
    status = get_booking_status(confirmation, verification)

    # Preis parsen
    total_price = parse_price(row.get("Gesamtpreis", "0"))

    # Custom Fields sammeln
    custom_fields = {}
    for col in CUSTOM_FIELD_COLUMNS:
        if col in row and row[col]:
            custom_fields[col] = row[col]

    # Buchungsdaten zusammenstellen
    buchung = {
        "post_title": f"Buchung #{row.get('ID', '0')}",
        "post_status": "publish",
        "post_type": "kurs-buchung",
        "meta": {
            "_buchung_kurs_id": str(kurs_id),
            "_buchung_variante_id": "0",
            "_buchung_name": row.get("Name", ""),
            "_buchung_email": row.get("E-Mail", ""),
            "_buchung_phone": row.get("Telefonnummer", ""),
            "_buchung_anzahl": "1",
            "_buchung_total_price": str(total_price),
            "_buchung_deposit_amount": "0",
            "_buchung_status": status,
            "_buchung_created_at": row.get("Bestellzeit", ""),
            "_buchung_confirmed_at": row.get("Bestellzeit", "") if status == "confirmed" else "",
            "_mec_original_booking_id": row.get("ID", ""),
            "_mec_transaction_id": row.get("Transaktions-ID", ""),
            "_buchung_custom_fields": custom_fields,
        },
    }

    # Ticket-Variante
    ticket_variant = row.get("Variante des Tickets", "")
    if ticket_variant:
        buchung["meta"]["_buchung_ticket_name"] = ticket_variant

    return buchung


def check_booking_already_imported(mec_booking_id: str) -> bool:
    """Prüft ob eine Buchung bereits importiert wurde."""
    php_code = f"""
$posts = get_posts(array(
    'post_type' => 'kurs-buchung',
    'meta_key' => '_mec_original_booking_id',
    'meta_value' => '{mec_booking_id}',
    'posts_per_page' => 1,
));
echo count($posts) > 0 ? '1' : '0';
"""
    result = run_wp_eval(php_code)
    return result == "1"


def create_buchung_post(buchung_data: dict, dry_run: bool = False) -> int:
    """Erstellt einen neuen Buchungs-Post."""
    if dry_run:
        print(f"  [DRY-RUN] Würde erstellen: {buchung_data['post_title']}")
        return 0

    title = buchung_data["post_title"].replace("'", "\\'")

    php_code = f"""
$post_data = array(
    'post_title'   => '{title}',
    'post_content' => '',
    'post_status'  => '{buchung_data["post_status"]}',
    'post_type'    => 'kurs-buchung',
);
$post_id = wp_insert_post($post_data);
echo $post_id;
"""
    result = run_wp_eval(php_code)

    try:
        post_id = int(result)
    except ValueError:
        print(f"  [ERROR] Konnte Buchung nicht erstellen: {result}", file=sys.stderr)
        return 0

    if post_id <= 0:
        return 0

    # Meta-Daten speichern
    for key, value in buchung_data["meta"].items():
        if isinstance(value, (list, dict)):
            value_json = json.dumps(value).replace("'", "\\'")
            php_code = f"update_post_meta({post_id}, '{key}', json_decode('{value_json}', true));"
        else:
            value_escaped = str(value).replace("'", "\\'")
            php_code = f"update_post_meta({post_id}, '{key}', '{value_escaped}');"
        run_wp_eval(php_code)

    return post_id


def import_bookings_from_file(filepath: Path, args, title_to_mec_id: dict = None) -> dict:
    """Importiert Buchungen aus CSV-Datei."""
    print()
    print("=" * 60)
    print("BUCHUNGS-IMPORT aus CSV")
    print("=" * 60)
    print(f"Datei: {filepath}")
    print()

    # Titel → Kurs-ID Mapping laden
    print("Lade Event-Mappings...")
    title_to_kurs = get_title_to_kurs_mapping()

    if not title_to_kurs:
        print("[ERROR] Keine Kurse gefunden! Bitte zuerst Events importieren.")
        return {"total": 0, "imported": 0, "skipped": 0, "errors": 0}

    print(f"Gefunden: {len(title_to_kurs)} Kurse für Mapping")
    print()

    # Buchungen laden
    bookings = load_bookings_csv(filepath)
    if args.limit:
        bookings = bookings[:args.limit]

    print(f"Gefunden: {len(bookings)} Buchungen")
    print()

    stats = {"total": len(bookings), "imported": 0, "skipped": 0, "errors": 0}

    for i, row in enumerate(bookings, 1):
        booking_id = row.get("ID", "?")
        event_title = row.get("Veranstaltung", "") or row.get("Kurs", "")
        name = row.get("Name", "Unbekannt")

        print(f"[{i}/{len(bookings)}] Buchung #{booking_id} - {name}")
        print(f"  Event: {event_title[:40]}...")

        # Bereits importiert?
        if not args.force and check_booking_already_imported(str(booking_id)):
            print(f"  -> Übersprungen (bereits importiert)")
            stats["skipped"] += 1
            continue

        # Transformieren
        buchung_data = transform_csv_booking(row, title_to_kurs)

        if not buchung_data:
            stats["errors"] += 1
            continue

        # Details anzeigen
        print(f"  Kurs-ID: {buchung_data['meta']['_buchung_kurs_id']}")
        print(f"  E-Mail: {buchung_data['meta']['_buchung_email']}")
        print(f"  Preis: {buchung_data['meta']['_buchung_total_price']} EUR")
        print(f"  Status: {buchung_data['meta']['_buchung_status']}")

        # Importieren
        post_id = create_buchung_post(buchung_data, dry_run=args.dry_run)

        if post_id > 0:
            print(f"  -> Importiert als Post ID: {post_id}")
            stats["imported"] += 1
        elif args.dry_run:
            stats["imported"] += 1
        else:
            stats["errors"] += 1

        print()

    return stats


# ============================================================================
# MAIN
# ============================================================================


def main():
    """Hauptfunktion."""
    parser = argparse.ArgumentParser(
        description="MEC Export-Dateien zu Kurs-Booking Import"
    )
    parser.add_argument("--events", type=Path, help="Events JSON-Datei")
    parser.add_argument("--bookings", type=Path, help="Buchungen CSV-Datei")
    parser.add_argument("--dry-run", action="store_true", help="Nur anzeigen")
    parser.add_argument("--limit", type=int, help="Anzahl begrenzen")
    parser.add_argument("--force", action="store_true", help="Erneut importieren")
    args = parser.parse_args()

    if not args.events and not args.bookings:
        parser.print_help()
        print("\n[ERROR] Mindestens --events oder --bookings angeben!")
        sys.exit(1)

    print("=" * 60)
    print("MEC zu Kurs-Booking Import (aus Dateien)")
    print("=" * 60)

    if args.dry_run:
        print("[DRY-RUN MODUS - Keine Änderungen]")

    # Statistiken
    event_stats = {"total": 0, "imported": 0, "skipped": 0, "errors": 0}
    booking_stats = {"total": 0, "imported": 0, "skipped": 0, "errors": 0}
    title_to_mec_id = {}

    # Events importieren
    if args.events:
        if not args.events.exists():
            print(f"[ERROR] Datei nicht gefunden: {args.events}")
            sys.exit(1)
        event_stats, title_to_mec_id = import_events_from_file(args.events, args)

    # Buchungen importieren
    if args.bookings:
        if not args.bookings.exists():
            print(f"[ERROR] Datei nicht gefunden: {args.bookings}")
            sys.exit(1)
        booking_stats = import_bookings_from_file(args.bookings, args, title_to_mec_id)

    # Zusammenfassung
    print()
    print("=" * 60)
    print("IMPORT ABGESCHLOSSEN!")
    print("=" * 60)

    if args.events:
        print()
        print("EVENTS:")
        print(f"  Gesamt:       {event_stats['total']}")
        print(f"  Importiert:   {event_stats['imported']}")
        print(f"  Übersprungen: {event_stats['skipped']}")
        print(f"  Fehler:       {event_stats['errors']}")

    if args.bookings:
        print()
        print("BUCHUNGEN:")
        print(f"  Gesamt:       {booking_stats['total']}")
        print(f"  Importiert:   {booking_stats['imported']}")
        print(f"  Übersprungen: {booking_stats['skipped']}")
        print(f"  Fehler:       {booking_stats['errors']}")

    if args.dry_run:
        print()
        print("Dies war ein Dry-Run. Führe ohne --dry-run aus, um zu importieren.")


if __name__ == "__main__":
    main()
