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

# --- KONFIGURATION neue Excel --> neue JSON ---
EXCEL_FILE = 'maintenance_import.xlsx'
DEFINITIONS_FILE = 'db/maintenance/maintenance_definitions.json'
HARDWARE_FILE = 'db/hardware/test_equipment_data.json'
COMPANIES_FILE = 'db/maintenance/maintenance_companies.json'  # NEU


# Backup Helper (nur eine Kopie reicht)
def backup(filepath):
    if os.path.exists(filepath):
        import shutil
        ts = int(datetime.now().timestamp())
        bkp = f"{filepath}.{ts}.bak"
        try:
            shutil.copy(filepath, bkp)
        except:
            pass


def load_json(filepath):
    if os.path.exists(filepath):
        try:
            with open(filepath, 'r', encoding='utf-8') as f:
                return json.load(f)
        except:
            return []
    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 resolve_assets(asset_string, inventory):
    if not asset_string or pd.isna(asset_string): return []
    lookup_map = {}
    for pn, item in inventory.items():
        part_name = item.get('name', '')
        for s in item.get('serials', []):
            if s.get('id'):
                label = f"{s.get('sn')} ({pn} - {part_name})"
                lookup_map[s.get('sn')] = {"id": s['id'], "label": label}
                lookup_map[part_name] = {"id": s['id'], "label": label}
    resolved_list = []
    for raw in [a.strip() for a in str(asset_string).split(',')]:
        if not raw: continue
        if raw in lookup_map:
            resolved_list.append(json.dumps(lookup_map[raw]))
        else:
            resolved_list.append(raw)
    return resolved_list


def get_or_create_company(company_name, companies_list):
    """Sucht Firma nach Namen oder legt sie an."""
    if not company_name or pd.isna(company_name): return "", False
    name_clean = str(company_name).strip()
    if not name_clean: return "", False

    # Suche (Case Insensitive)
    for c in companies_list:
        if c.get('name', '').lower() == name_clean.lower():
            return c['id'], False

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


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

    backup(DEFINITIONS_FILE)
    backup(COMPANIES_FILE)  # Backup auch für Firmen

    definitions = load_json(DEFINITIONS_FILE)
    companies = load_json(COMPANIES_FILE)
    hardware_data = load_json(HARDWARE_FILE)
    if isinstance(hardware_data, list): hardware_data = {}  # Fallback
    inventory = hardware_data.get('inventory', {})

    try:
        df = pd.read_excel(EXCEL_FILE, dtype=str).fillna('')
    except Exception as e:
        return print(f"Excel-Fehler: {e}")

    count_new = 0
    count_upd = 0
    companies_changed = False

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

        def_id = row.get('ID', '').strip()
        existing = next((d for d in definitions if d['id'] == def_id), None) if def_id else None

        if not def_id and not existing:
            def_id = f"MT-{datetime.now().year}-{uuid.uuid4().hex[:5]}"

        # Firmen-Logik
        comp_name = row.get('Firma', '').strip()
        comp_id, changed = get_or_create_company(comp_name, companies)
        if changed: companies_changed = True

        new_data = {
            "id": def_id,
            "task": task,
            "description": row.get('Beschreibung', '').strip(),
            "topic": row.get('Bereich', 'Allgemein').strip(),
            "testbench": row.get('Prüfstand', '').strip(),
            "plantIndex": row.get('Index', '').strip(),
            "intervalValue": row.get('Intervall Wert', '1').strip(),
            "intervalUnit": row.get('Intervall Einheit', 'Monate').strip(),
            "startDate": row.get('Startdatum', datetime.now().strftime('%Y-%m-%d')).strip(),
            "duration": row.get('Dauer', '').strip(),
            "active": row.get('Aktiv', '').lower() in ['x', 'ja', 'true', '1', 'aktiv'],
            "requiresReview": row.get('Review', '').lower() in ['x', 'ja', 'true', '1'],
            "designatedReviewer": "",
            "checklist": [s.strip() for s in row.get('Checkliste', '').split(';') if s.strip()],
            "maintenanceObjects": resolve_assets(row.get('Assets', ''), inventory),
            "externalCompanyId": comp_id  # <--- Hier gesetzt
        }

        if existing:
            # Preserve existing media
            new_data['images'] = existing.get('images', [])
            new_data['documents'] = existing.get('documents', [])
            existing.update(new_data)
            count_upd += 1
        else:
            new_data['images'] = []
            new_data['documents'] = []
            definitions.append(new_data)
            count_new += 1

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

    print(f"\nFertig! {count_new} neu, {count_upd} aktualisiert.")


if __name__ == '__main__':
    process_excel()