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

# --- KONFIGURATION ---
SOURCE_FILE = 'TBL_Abstellmassnahmen.xlsx'
TARGET_FILE = 'db/maintenance/maintenance_orders.json'
COMPANIES_FILE = 'db/maintenance/maintenance_companies.json'


def clean_text(text):
    if not text or pd.isna(text): return ""
    text = str(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):
    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 get_or_create_company(name, companies_list):
    if not name or pd.isna(name): return "", False
    clean_name = str(name).strip()
    if not clean_name: return "", False

    for c in companies_list:
        if c.get('name', '').lower() == clean_name.lower():
            return c['id'], False

    new_id = f"COMP-{uuid.uuid4().hex[:8]}"
    print(f"  -> Neue Firma: {clean_name}")
    companies_list.append({
        "id": new_id, "name": clean_name, "contact_person": "", "email": "", "phone": "", "services": "Importiert"
    })
    return new_id, True


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

    print("Lade Daten...")
    orders = load_json(TARGET_FILE)
    companies = load_json(COMPANIES_FILE)

    # Map für Issue-Lookup
    issue_map = {o['id']: o for o in orders if o.get('type') == 'I'}

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

    new_actions = []
    count = 0
    companies_changed = False

    for _, row in df.iterrows():
        action_id_raw = row.get('LfdNr_Abstellmassnahme', '').strip()
        if not action_id_raw: continue

        new_id = f"ACT-{action_id_raw}"
        if any(o['id'] == new_id for o in orders): continue

        # Parent Issue Link
        issue_id_raw = row.get('LfdNr_Mängel', '').strip()
        linked_issue_id = ""
        parent_issue = None

        if issue_id_raw:
            lookup_id = f"ISS-{issue_id_raw}"
            if lookup_id in issue_map:
                linked_issue_id = lookup_id
                parent_issue = issue_map[lookup_id]

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

        # Text
        text = clean_text(row.get('Abstellung_Text', 'Maßnahme'))

        # Firma
        comp_id, changed = get_or_create_company(row.get('Firma'), companies)
        if changed: companies_changed = True

        # Daten erben
        testbench = parent_issue['testbench'] if parent_issue else ""
        plant_index = parent_issue['plantIndex'] if parent_issue else ""
        affected_assets = parent_issue.get('affectedAssets', []) if parent_issue else []

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

        new_action = {
            "id": new_id,
            "type": "A",
            "linkedIssue": linked_issue_id,
            "task": text,
            "description": text,  # Beschreibung = Titel, da oft kurz
            "estimatedTime": "",
            "spareParts": "",
            "topic": row.get('Verantwortlicher_Bereich', 'Allgemein'),
            "affectedAssets": affected_assets,  # Assets vom Issue erben
            "testbench": testbench,
            "plantIndex": plant_index,
            "assigned": row.get('Verantwortlicher_Userkey', ''),
            "date": parse_date(row.get('Abstellung_Zieltermin')) or datetime.now().strftime('%Y-%m-%d'),
            "status": status,
            "externalCompanyId": comp_id,
            "completedDate": parse_date(row.get('Abstellung_Abgemeldet_Date')) if is_done else "",
            "completedBy": "",  # In Excel nicht explizit für Abschluss, meist der Ersteller
            "logs": logs
        }

        new_actions.append(new_action)
        count += 1

    print(f"Importiere {count} Maßnahmen...")
    orders.extend(new_actions)
    save_json(TARGET_FILE, orders)

    if companies_changed:
        save_json(COMPANIES_FILE, companies)
        print("Firmen-Datenbank aktualisiert.")

    print("Fertig.")


if __name__ == '__main__':
    migrate()