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

# --- KONFIGURATION ---
EXCEL_FILE = 'structure_import.xlsx'
JSON_FILE = 'db/facility/structure.json'
HARDWARE_FILE = 'db/hardware/test_equipment_data.json'
BACKUP_FILE = f'db/facility/structure_backup_{int(datetime.now().timestamp())}.json'


def load_json(filepath):
    """Lädt eine JSON-Datei sicher."""
    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):
    """Speichert Daten als JSON mit Einrückung."""
    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 is_empty(val):
    """Prüft ob ein Wert leer, nan oder 'none' ist."""
    if val is None: return True
    if pd.isna(val): return True
    s_val = str(val).strip().lower()
    return s_val in ['', 'nan', 'none', 'null']


def clean_list(val_str):
    """Bereinigt kommagetrennte Listen von nan/none/leeren Einträgen."""
    if is_empty(val_str): return []
    res = []
    for item in str(val_str).split(','):
        cleaned = item.strip()
        if not is_empty(cleaned):
            res.append(cleaned)
    return res


def resolve_hardware(hw_string, inventory):
    """Löst Hardware-Namen zu JSON-Strings auf, ignoriert nan/none."""
    if is_empty(hw_string): return []

    lookup = {}
    for pn, item in inventory.items():
        name = item.get('name', '')
        for s in item.get('serials', []):
            if s.get('id'):
                label = f"{s.get('sn')} ({pn} - {name})"
                lookup[str(s.get('sn'))] = {"id": s['id'], "label": label}
                lookup[str(name)] = {"id": s['id'], "label": label}

    result = []
    for raw in str(hw_string).split(','):
        val = raw.strip()
        if is_empty(val): continue
        if val in lookup:
            result.append(json.dumps(lookup[val]))
        else:
            result.append(val)
    return result


def parse_details(details_str):
    """Key:Value;Key2:Value2 - Filtert nan/none-Werte aus."""
    if is_empty(details_str): return {}
    d = {}
    for pair in str(details_str).split(';'):
        if ':' in pair:
            parts = pair.split(':', 1)
            key, val = parts[0].strip(), parts[1].strip()
            if not is_empty(key) and not is_empty(val):
                d[key] = val
    return d


def build_tree_from_sheet(df, inventory):
    """Baut die hierarchische Struktur aus dem DataFrame."""
    roots = []
    stack = []

    for _, row in df.iterrows():
        # 1. Level und Name ermitteln
        level = -1
        name = ""
        for l in range(1, 7):
            col = f"L{l}"
            val = row.get(col)
            if not is_empty(val):
                level = l
                name = str(val).strip()
                break

        if level == -1: continue

        # 2. Node erstellen
        mesh_list = clean_list(row.get('Meshes'))
        hw_list = resolve_hardware(row.get('Hardware'), inventory)
        resp_list = clean_list(row.get('Responsibility'))
        details_dict = parse_details(row.get('Details'))
        label_val = row.get('Label')
        storage_val = row.get('Storage')

        # Basis-Node
        node = {
            "id": str(uuid.uuid4()),
            "name": name,
            "children": [],
            "showStatusLabel": True
        }

        # Optionale Felder nur hinzufügen, wenn sie NICHT leer/none sind
        if not is_empty(label_val): node["labelName"] = str(label_val).strip()
        if mesh_list: node["meshNames"] = mesh_list
        if hw_list: node["hardware"] = hw_list
        if resp_list: node["responsibility"] = resp_list
        if not is_empty(storage_val): node["storageLink"] = str(storage_val).strip()
        if details_dict: node["details"] = details_dict

        # 3. In Baum einsortieren
        if level == 1:
            roots.append(node)
            stack = [(1, node)]
        else:
            while stack and stack[-1][0] >= level:
                stack.pop()

            if stack:
                parent = stack[-1][1]
                parent['children'].append(node)
                stack.append((level, node))
            else:
                # Fallback für Waisen
                roots.append(node)
                stack = [(level, node)]

    return roots


def process_excel():
    """Hauptprozess zum Laden der Excel und Speichern der JSON."""
    if not os.path.exists(EXCEL_FILE):
        print(f"Datei {EXCEL_FILE} wurde nicht gefunden.")
        return

    # Backup erstellen
    if os.path.exists(JSON_FILE):
        import shutil
        shutil.copy(JSON_FILE, BACKUP_FILE)
        print(f"Backup erstellt: {BACKUP_FILE}")

    # Daten laden
    hw_data = load_json(HARDWARE_FILE)
    inventory = hw_data.get('inventory', {})

    current_struct = load_json(JSON_FILE)
    flows = current_struct.get('fluesse', [])

    try:
        # Sheet 1: Units
        df_units = pd.read_excel(EXCEL_FILE, sheet_name='Units', dtype=str)
        units = build_tree_from_sheet(df_units, inventory)
        print(f"Units importiert: {len(units)} Hauptgruppen.")

        # Sheet 2: Buildings
        df_build = pd.read_excel(EXCEL_FILE, sheet_name='Buildings', dtype=str)
        buildings = build_tree_from_sheet(df_build, inventory)
        print(f"Buildings importiert: {len(buildings)} Hauptgebäude.")

        # Finales JSON zusammenbauen
        new_struct = {
            "betriebseinheiten": units,
            "gebaeude": buildings,
            "fluesse": flows
        }

        save_json(JSON_FILE, new_struct)
        print(f"Erfolg! Die Struktur wurde in {JSON_FILE} gespeichert.")

    except Exception as e:
        print(f"Ein Fehler ist aufgetreten: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    process_excel()