import pandas as pd
import json
import os
import re
from datetime import datetime

# --- KONFIGURATION ---
SOURCE_FILE = 'TBL_Event_Details.xlsx'
TARGET_FILE = 'db/maintenance/maintenance_orders.json'
DEFINITIONS_FILE = 'db/maintenance/maintenance_definitions.json'


def clean_text(text):
    """Entfernt Excel-Artefakte wie _x000D_."""
    if not text or pd.isna(text): return ""
    text = str(text)
    # Ersetze _x000D_ durch Leerzeichen oder Umbruch
    text = text.replace('_x000D_', '\n')
    return text.strip()


def load_json(filepath):
    if os.path.exists(filepath):
        with open(filepath, 'r', encoding='utf-8') as f:
            return json.load(f)
    return []


def save_json(filepath, data):
    os.makedirs(os.path.dirname(filepath), exist_ok=True)
    with open(filepath, 'w', encoding='utf-8') as f:
        json.dump(data, f, indent=2, ensure_ascii=False)


def parse_date(date_val):
    if pd.isna(date_val) or str(date_val).strip() == '': return ""
    try:
        dt = pd.to_datetime(date_val, errors='raise')
        return dt.strftime('%Y-%m-%d')
    except:
        s = str(date_val).strip()
        try:
            return datetime.strptime(s, '%d.%m.%Y').strftime('%Y-%m-%d')
        except:
            pass
        try:
            return datetime.strptime(s, '%m/%d/%Y').strftime('%Y-%m-%d')
        except:
            pass
    return ""


def migrate():
    if not os.path.exists(SOURCE_FILE): return print(f"FEHLER: {SOURCE_FILE} fehlt.")

    print("Lade Definitionen...")
    defs = load_json(DEFINITIONS_FILE)
    def_map = {d['id']: d for d in defs}

    print("Lade Excel...")
    try:
        df = pd.read_excel(SOURCE_FILE, dtype=str)
    except:
        df = pd.read_csv(SOURCE_FILE, sep='\t', dtype=str)

    orders = []
    count = 0

    for _, row in df.iterrows():
        detail_id = row.get('LfdNr_Detail', '').strip()
        plan_id_raw = row.get('LfdNr', '').strip()

        if not detail_id or not plan_id_raw: continue

        new_id = f"ORD-{detail_id}"
        parent_id = f"OLD-{plan_id_raw}"

        parent_def = def_map.get(parent_id)
        if not parent_def: continue

        is_done = str(row.get('Event_Abgemeldet', '')).upper() in ['WAHR', 'TRUE', '1', 'YES']
        status = 'Completed' if is_done else 'Pending'

        date_end = parse_date(row.get('Event_Date_End'))
        date_orig = parse_date(row.get('ursprünglicher_Termin'))
        final_due_date = date_end if date_end else (date_orig if date_orig else "")
        if status == 'Pending' and not final_due_date:
            final_due_date = datetime.now().strftime('%Y-%m-%d')

        done_date = parse_date(row.get('Event_Done_Date'))

        logs = []
        # Hier säubern wir die Texte
        comm = clean_text(row.get('Event_Kommentare', ''))
        if comm:
            logs.append({"user": "Migration", "timestamp": "", "note": f"Alte Kommentare: {comm}"})

        reason = clean_text(row.get('Verschiebung_begründung', ''))
        if reason:
            logs.append({"user": "Migration", "timestamp": "", "note": f"Verschiebung: {reason}"})

        new_order = {
            "id": new_id,
            "type": "M",
            "parentTaskId": parent_id,
            "task": parent_def['task'],
            "description": parent_def['description'],
            "topic": parent_def.get('topic', ''),
            "testbench": parent_def.get('testbench', ''),
            "plantIndex": parent_def.get('plantIndex', ''),
            "maintenanceObjects": parent_def.get('maintenanceObjects', []),
            "consumablesList": parent_def.get('consumablesList', []),
            "requiresReview": parent_def.get('requiresReview', False),
            "designatedReviewer": parent_def.get('designatedReviewer', ''),
            "externalCompanyId": parent_def.get('externalCompanyId', ''),
            "assigned": "",
            "date": final_due_date,
            "status": status,
            "completedDate": done_date if status == 'Completed' else "",
            "completedBy": row.get('Event_Abgemeldet_Userkey', ''),
            "logs": logs
        }

        orders.append(new_order)
        count += 1

    print(f"Migration fertig. {count} Aufträge generiert.")
    save_json(TARGET_FILE, orders)


if __name__ == '__main__':
    migrate()