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

# --- KONFIGURATION ---
SOURCE_FILE = 'TBL_Event_Kopfdaten.xlsx'
TARGET_FILE = 'db/maintenance/maintenance_logbook.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):
    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 migrate():
    if not os.path.exists(SOURCE_FILE): return print(f"FEHLER: {SOURCE_FILE} fehlt.")

    print("Lade Excel...")
    try:
        if SOURCE_FILE.endswith('.csv'):
            df = pd.read_csv(SOURCE_FILE, sep=';', dtype=str)
        else:
            df = pd.read_excel(SOURCE_FILE, dtype=str)
    except Exception as e:
        print(f"Fehler beim Lesen: {e}")
        return

    # 1. Filter: Nur Logbucheinträge
    # Wir prüfen auf "WAHR", "TRUE", "1", "YES" (case-insensitive)
    df['is_log'] = df['Logbucheintrag'].astype(str).str.upper().isin(['WAHR', 'TRUE', '1', 'YES'])
    df = df[df['is_log']]
    print(f"Gefundene Logbuch-Zeilen: {len(df)}")

    # 2. Deduplizierung (Höchste Revision pro LfdNr)
    df['Revnr_des_Events'] = pd.to_numeric(df['Revnr_des_Events'], errors='coerce').fillna(0)
    df = df.sort_values(by=['LfdNr', 'Revnr_des_Events'], ascending=[True, False])
    df_clean = df.drop_duplicates(subset=['LfdNr'], keep='first')

    # 3. "Entfernt" Filter (auch Logs können gelöscht sein)
    df_clean = df_clean[~df_clean['DBentry_Type'].astype(str).str.lower().str.contains('entfernt', na=False)]

    print(f"Zu importierende Einträge: {len(df_clean)}")

    # 4. Mapping
    logs = load_json(TARGET_FILE)  # Bestehende laden (um nicht zu überschreiben)
    existing_ids = {l['id'] for l in logs}

    new_logs = []

    for _, row in df_clean.iterrows():
        lfd_nr = row.get('LfdNr', '').strip()
        if not lfd_nr: continue

        new_id = f"LOG-OLD-{lfd_nr}"
        if new_id in existing_ids: continue

        task_title = clean_text(row.get('Event', 'Ohne Titel'))
        instruktion = clean_text(row.get('Instruktionen', ''))
        comm = clean_text(row.get('Kommentare', ''))
        category = clean_text(row.get('Verantwortlichkeitsebene', 'Allgemein'))

        full_desc = instruktion
        if comm and comm.lower() != 'nan':
            full_desc += f"\n\nKommentar: {comm}"

        # Text
        instruktion = clean_text(row.get('Instruktionen', ''))
        comm = clean_text(row.get('Kommentare', ''))
        full_desc = instruktion
        if comm and comm.lower() != 'nan':
            full_desc += f"\n\nKommentar: {comm}"

        # Datum
        date_str = row.get('DBentry_Date', '')
        try:
            date_obj = pd.to_datetime(date_str)
            date_iso = date_obj.strftime('%Y-%m-%d')
            created_at_iso = date_obj.isoformat()
        except:
            date_iso = datetime.now().strftime('%Y-%m-%d')
            created_at_iso = datetime.now().isoformat()
        plant_index = clean_text(row.get('Stand', ''))

        entry = {
            "id": new_id,
            "type": "L",
            "task": task_title,
            "description": full_desc,
            "category": category,
            "plantIndex": plant_index, # <--- NEU
            "date": date_iso,
            "createdBy": row.get('DBentry_Userkey', 'Import'),
            "createdAt": created_at_iso,
            "status": "Completed"
        }
        new_logs.append(entry)

    # 5. Speichern
    logs.extend(new_logs)
    # Sortieren nach Datum (neu oben)
    logs.sort(key=lambda x: x['date'], reverse=True)

    save_json(TARGET_FILE, logs)
    print(f"Erfolg! {len(new_logs)} alte Logs importiert.")


if __name__ == '__main__':
    migrate()