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

# --- KONFIGURATION ---
SOURCE_FILE = 'TBL_Mängel.xlsx'
TARGET_FILE = 'db/maintenance/maintenance_orders.json'


def clean_text(text):
    """Entfernt Excel-Artefakte wie _x000D_."""
    if not text or pd.isna(text): return ""
    text = str(text)
    text = text.replace('_x000D_', ' ').replace('\r', ' ').replace('\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):
    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 not date_val: return ""
    try:
        return pd.to_datetime(date_val).strftime('%Y-%m-%d')
    except:
        return ""


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

    print("Lade bestehende Aufträge...")
    orders = load_json(TARGET_FILE)
    order_map = {o['id']: o for o in orders}

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

    new_issues = []
    count = 0

    for _, row in df.iterrows():
        issue_id_raw = row.get('LfdNr_Mängel', '').strip()
        if not issue_id_raw: continue

        new_id = f"ISS-{issue_id_raw}"

        if any(o['id'] == new_id for o in orders): continue

        # Parent Link
        parent_detail_id = row.get('LfdNr_Detail', '').strip()
        source_task_id = ""
        parent_order = None

        if parent_detail_id:
            lookup_id = f"ORD-{parent_detail_id}"
            if lookup_id in order_map:
                source_task_id = lookup_id
                parent_order = order_map[lookup_id]

        is_done = str(row.get('Mängel_Abgemeldet', '')).upper() in ['WAHR', 'TRUE', '1', 'YES']
        status = 'Completed' if is_done else 'OPEN'

        prio_raw = str(row.get('Mangel_testkritisch', '')).strip()
        priority = 'Medium'
        is_blocker = False
        if prio_raw == '1':
            priority = 'Critical'
            is_blocker = True
        elif prio_raw == '2':
            priority = 'Low'

        # Clean Text hier anwenden!
        text = clean_text(row.get('Mängel_Text', 'Ohne Titel'))

        testbench = parent_order['testbench'] if parent_order else ""
        plant_index = parent_order['plantIndex'] if parent_order else ""

        logs = []
        creator = row.get('DBentry_Userkey', '')
        created_date = parse_date(row.get('DBentry_Date'))
        logs.append({"user": creator, "timestamp": created_date, "note": "Mangel erstellt (Import)."})

        changelog = clean_text(row.get('ChangeLog', ''))
        if changelog:
            logs.append({"user": "System", "timestamp": "", "note": f"Log: {changelog}"})

        new_issue = {
            "id": new_id,
            "type": "I",
            "sourceTask": source_task_id,
            "task": text,
            "description": text,
            "priority": priority,
            "category": row.get('Verantwortlicher_Bereich', 'Allgemein'),
            "isBlocker": is_blocker,
            "topic": row.get('Verantwortlicher_Bereich', 'Allgemein'),
            "affectedAssets": [],
            "testbench": testbench,
            "plantIndex": plant_index,
            "assigned": "",
            "date": created_date or datetime.now().strftime('%Y-%m-%d'),
            "status": status,
            "requiresReview": True,
            "designatedReviewer": "",
            "completedDate": parse_date(row.get('Mängel_Abgemeldet_Date')) if is_done else "",
            "completedBy": row.get('Mängel_Abgemeldet_Userkey', ''),
            "logs": logs
        }

        new_issues.append(new_issue)
        count += 1

    print(f"Importiere {count} neue Issues...")
    orders.extend(new_issues)
    save_json(TARGET_FILE, orders)
    print("Fertig.")


if __name__ == '__main__':
    migrate()