import uno
from com.sun.star.table.CellHoriJustify import LEFT as HORIZONTAL_LEFT
from com.sun.star.table.CellVertJustify import CENTER as VERTICAL_CENTER
from com.sun.star.awt.FontWeight import NORMAL as FONT_NORMAL
import time

# Farbcodes (Hex)
COLORS = {
    "risk_light": 0xFCE4D6,
    "risk_dark": 0xF8CBAD,
    "res_light": 0xE2EFDA,
    "res_dark": 0xC6E0B4,
}
# -------------------------
# Ereignis: Änderung im Blatt Anleitung
# -------------------------
def on_instruction_change(event=None):
    """Wird ausgelöst bei Änderung im Blatt 'Anleitung'."""
    try:
        doc = XSCRIPTCONTEXT.getDocument()
        sheet = doc.CurrentController.ActiveSheet
        if sheet.getName() != "Anleitung":
            return

        sel = doc.CurrentController.getSelection()
        if not hasattr(sel, "RangeAddress"):
            return

        col, row = sel.RangeAddress.StartColumn, sel.RangeAddress.StartRow

        # --- Übertragung von C7–C9 auf Risikobewertung & Ressourcen ---
        if row in (6, 7, 8) and col == 2:  # C7=2,6 / C8=2,7 / C9=2,8
            date_val = sheet.getCellByPosition(2, 6).String.strip()     # C7
            person_val = sheet.getCellByPosition(2, 7).String.strip()   # C8
            activity_val = sheet.getCellByPosition(2, 8).String.strip() # C9

            for target_name in ("Risikobewertung", "Ressourcen"):
                try:
                    target = doc.Sheets.getByName(target_name)
                    target.getCellByPosition(0, 1).String = date_val
                    target.getCellByPosition(1, 1).String = activity_val
                    target.getCellByPosition(2, 1).String = person_val
                except Exception as e:
                    show_message(f"Fehler beim Aktualisieren von {target_name}: {e}")

        # --- Gerätefilter bei Änderung in C10 ---
        elif row == 9 and col == 2:  # C10
            device = sheet.getCellByPosition(2, 9).String.strip()
            show_message(f"Gerät ausgewählt: '{device}'")
            filter_device_rows(doc, device)

    except Exception as e:
        show_message(f"Fehler in on_instruction_change: {e}")

# -------------------------
# Ereignis: on_document_change
# -------------------------
def on_document_change(event=None):
    doc = XSCRIPTCONTEXT.getDocument()
    try:
        doc.addActionLock()
        controller = doc.CurrentController
        sheet = controller.ActiveSheet
        sel = controller.getSelection()

        if not hasattr(sel, "RangeAddress"):
            return

        ra = sel.RangeAddress
        col, row = ra.StartColumn, ra.StartRow
        sheet_name = sheet.getName()

        allowed = (
            "Soziale Beziehungen",
            "Arbeitsorganisation",
            "Arbeitsumgebung",
            "Arbeitsmittel",
            "Arbeitsaufgabe",
        )

        # nur relevante Blätter und Spalten D(3)–G(6)
        if sheet_name not in allowed or col < 3 or col > 6:
            return

        cell = sheet.getCellByPosition(col, row)
        val = cell.String.strip().lower()

        # --- FALL A: falscher Inhalt ---
        if val not in ("", "x"):
            show_message("In dieser Zelle kann nur 'x' eingetragen werden.")
            cell.String = "x"
            val = "x"

        # max. 1 x pro Zeile
        if count_x_in_row(sheet, row) > 1:
            show_message("Pro Zeile kann nur ein 'x' eingetragen werden.")
            cell.String = ""
            return

        item_text = sheet.getCellByPosition(1, row).String.strip()
        if not item_text:
            return

        ws_risk = doc.Sheets.getByName("Risikobewertung")
        ws_res = doc.Sheets.getByName("Ressourcen")

        # Risikobewertung (F=5, G=6)
        if col in (5, 6):
            if val == "x":
                rating = "traf eher nicht zu" if col == 5 else "traf gar nicht zu"
                color = COLORS["risk_light"] if col == 5 else COLORS["risk_dark"]
                add_risk_entry(ws_risk, sheet_name, item_text, rating, color, row, col)
            else:
                remove_entry(ws_risk, sheet_name, item_text)

        # Ressourcen (D=3, E=4)
        elif col in (3, 4):
            if val == "x":
                rating = "traf vollkommen zu" if col == 3 else "traf eher zu"
                color = COLORS["res_dark"] if col == 3 else COLORS["res_light"]
                add_resource_entry(ws_res, sheet_name, item_text, rating, color, row, col)
            else:
                remove_entry(ws_res, sheet_name, item_text)

    except Exception as e:
        try:
            show_message(f"Fehler: {e}")
        except Exception:
            print(f"Fehler: {e}")
    finally:
        try:
            doc.removeActionLock()
        except Exception:
            pass

# -------------------------
# Hilfsfunktionen: on_instruction_change
# -------------------------
def get_active_doc():
    return XSCRIPTCONTEXT.getDocument()

def show_message(msg, title="Hinweis"):
    """Zeigt Nachricht in Statusleiste und Konsole."""
    try:
        doc = get_active_doc()
        frame = doc.CurrentController.Frame
        if frame:
            frame.StatusIndicator.start(title, 0)
            frame.StatusIndicator.setText(str(msg))
            time.sleep(2)
            frame.StatusIndicator.end()
        print(f"[{title}] {msg}")
    except Exception:
        print(f"[{title}] {msg}")

def count_x_in_row(sheet, row):
    """Zählt X in D..G in einer Zeile."""
    cnt = 0
    for c in range(3, 7):
        try:
            if sheet.getCellByPosition(c, row).String.strip().lower() == "x":
                cnt += 1
        except Exception:
            pass
    return cnt

def get_next_free_row_limited(sheet, max_scan=1000):
    """Sucht nächste freie Zeile ab Zeile 4, limitiert auf max_scan Zeilen."""
    start = 3
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)
    vals = rng.getDataArray()
    for i in range(len(vals) - 1, -1, -1):
        if vals[i][0] != "":
            return start + i + 1
    return start

def find_existing_row_limited(sheet, sheet_name, item_text, max_scan=1000):
    """Suche nach Eintrag in Spalte B/C, begrenzt auf max_scan Zeilen."""
    start = 3
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 2, end)  # B..C
    vals = rng.getDataArray()
    for idx, row in enumerate(vals, start=start):
        if row[0] == sheet_name and row[1] == item_text:
            return idx
    return None
    
def get_last_used_row(sheet, col=0):
    """Gibt die letzte belegte Zeile in einer Spalte zurück."""
    used_range = sheet.getCellRangeByPosition(col, 0, col, sheet.Rows.Count-1)
    data = used_range.getDataArray()
    for i in reversed(range(len(data))):
        if str(data[i][0]).strip() != "":
            return i
    return -1

def remove_entry(sheet, sheet_name, item_text):
    idx = find_existing_row_limited(sheet, sheet_name, item_text)
    if idx is not None:
        sheet.Rows.removeByIndex(idx, 1)

def set_row_values(sheet, row, sheet_name, item_text, rating, color, src_row):
    """Setzt Werte und Formatierung für eingefügte Zeile."""
    sheet.getCellByPosition(0, row).String = f"A{src_row + 1}"
    sheet.getCellByPosition(1, row).String = sheet_name
    sheet.getCellByPosition(2, row).String = item_text
    sheet.getCellByPosition(3, row).String = rating

    for col in range(0, 5):
        cell = sheet.getCellByPosition(col, row)
        try:
            cell.CellBackColor = 0xFFFFFF
            cell.CharHeight = 12
            cell.CharWeight = FONT_NORMAL
            cell.IsTextWrapped = True
        except Exception:
            pass

    cell_c = sheet.getCellByPosition(2, row)
    try:
        cell_c.CellBackColor = color
        cell_c.HoriJustify = HORIZONTAL_LEFT
        cell_c.VertJustify = VERTICAL_CENTER
    except Exception:
        pass

    adjust_row_height_heuristic(sheet, row, extra_points=6)

def adjust_row_height_heuristic(sheet, row_index, extra_points=6):
    """Heuristische Zeilenhöhenanpassung."""
    try:
        base = 400
        line_height = 400
        text = sheet.getCellByPosition(2, row_index).String
        if not text:
            new_height = base + int(extra_points * 20)
        else:
            line_breaks = text.count("\n")
            approx_lines = max(1, (len(text) // 60) + 1)
            total_lines = max(1, line_breaks + approx_lines)
            new_height = base + int(total_lines * line_height) + int(extra_points * 20)
        sheet.Rows.getByIndex(row_index).Height = int(new_height)
    except Exception:
        pass

# -------------------------
# Risikobewertung
# -------------------------
def add_risk_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
    if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
        return

    start = 3
    max_scan = 1000
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)
    vals = rng.getDataArray()

    first = last = None
    for idx, r in enumerate(vals, start=start):
        if r[0] == sheet_name:
            if first is None:
                first = idx
            last = idx

    if first is None:
        insert_row = get_next_free_row_limited(sheet)
    elif col == 6:
        insert_row = first
    else:
        insert_row = last + 1

    sheet.Rows.insertByIndex(insert_row, 1)
    set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)

# -------------------------
# Ressourcen
# -------------------------
def add_resource_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
    if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
        return

    start = 3
    max_scan = 1000
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)
    vals = rng.getDataArray()

    first = last = None
    for idx, r in enumerate(vals, start=start):
        if r[0] == sheet_name:
            if first is None:
                first = idx
            last = idx

    if first is None:
        insert_row = get_next_free_row_limited(sheet)
    elif col == 3:
        insert_row = first
    else:
        insert_row = last + 1

    sheet.Rows.insertByIndex(insert_row, 1)
    set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)

# -------------------------
# Neues: Zeilen nach Gerät filtern (nur belegte Zeilen)
# -------------------------
filter_lock = False

def filter_device_rows(doc, device_value):
    """Blätter filtern basierend auf Dropdown-Auswahl C10, Kopfzeilen bleiben sichtbar, Teilstring-Matching."""
    global filter_lock
    if filter_lock:
        return
    filter_lock = True
    try:
        target_sheets = [
            "Soziale Beziehungen",
            "Arbeitsorganisation",
            "Arbeitsumgebung",
            "Arbeitsmittel",
            "Arbeitsaufgabe",
        ]

        for sheet_name in target_sheets:
            sheet = doc.Sheets[sheet_name]
            last_row = get_last_used_row(sheet, col=0)
            if last_row == -1:
                continue  # keine Daten
            for row_idx in range(last_row + 1):
                cell_val = sheet.getCellByPosition(0, row_idx).String.strip()

                # Kopfzeile immer sichtbar
                if row_idx == 0:
                    sheet.Rows.getByIndex(row_idx).IsVisible = True
                    continue

                if device_value.lower() in ("gerät auswählen", ""):
                    sheet.Rows.getByIndex(row_idx).IsVisible = True
                else:
                    # Mehrfachgeräte erkennen
                    devices_in_cell = [d.strip().lower() for d in cell_val.split(",")]
                    sheet.Rows.getByIndex(row_idx).IsVisible = device_value.lower() in devices_in_cell
    finally:
        filter_lock = False

# -------------------------
# Hilfsfunktionen: on_document_change
# -------------------------
def show_message(msg, title="Hinweis"):
    """Zeigt Nachricht in Statusleiste und Konsole."""
    try:
        doc = XSCRIPTCONTEXT.getDocument()
        frame = doc.CurrentController.Frame
        if frame:
            frame.StatusIndicator.start(title, 0)
            frame.StatusIndicator.setText(str(msg))
            time.sleep(2)
            frame.StatusIndicator.end()
        print(f"[{title}] {msg}")
    except Exception:
        print(f"[{title}] {msg}")

def count_x_in_row(sheet, row):
    """Zählt X in D..G in einer Zeile."""
    cnt = 0
    for c in range(3, 7):
        try:
            if sheet.getCellByPosition(c, row).String.strip().lower() == "x":
                cnt += 1
        except Exception:
            pass
    return cnt

def get_next_free_row_limited(sheet, max_scan=1000):
    """Sucht nächste freie Zeile ab Zeile 4, limitiert auf max_scan Zeilen."""
    start = 3
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)
    vals = rng.getDataArray()
    for i in range(len(vals) - 1, -1, -1):
        if vals[i][0] != "":
            return start + i + 1
    return start

def find_existing_row_limited(sheet, sheet_name, item_text, max_scan=1000):
    """Suche nach Eintrag in Spalte B/C, begrenzt auf max_scan Zeilen."""
    start = 3
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 2, end)  # B..C
    vals = rng.getDataArray()
    for idx, row in enumerate(vals, start=start):
        if row[0] == sheet_name and row[1] == item_text:
            return idx
    return None

def remove_entry(sheet, sheet_name, item_text):
    idx = find_existing_row_limited(sheet, sheet_name, item_text)
    if idx is not None:
        sheet.Rows.removeByIndex(idx, 1)

# -------------------------
# Risikobewertung
# -------------------------
def add_risk_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
    if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
        return

    start = 3
    max_scan = 1000
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)  # Spalte B
    vals = rng.getDataArray()

    first = last = None
    for idx, r in enumerate(vals, start=start):
        if r[0] == sheet_name:
            if first is None:
                first = idx
            last = idx

    # Einfügeposition bestimmen
    if first is None:
        insert_row = get_next_free_row_limited(sheet)
    elif col == 6:
        insert_row = first
    else:
        insert_row = last + 1

    sheet.Rows.insertByIndex(insert_row, 1)
    set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)

# -------------------------
# Ressourcen
# -------------------------
def add_resource_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
    if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
        return

    start = 3
    max_scan = 1000
    end = min(sheet.Rows.Count - 1, start + max_scan - 1)
    rng = sheet.getCellRangeByPosition(1, start, 1, end)
    vals = rng.getDataArray()

    first = last = None
    for idx, r in enumerate(vals, start=start):
        if r[0] == sheet_name:
            if first is None:
                first = idx
            last = idx

    if first is None:
        insert_row = get_next_free_row_limited(sheet)
    elif col == 3:
        insert_row = first
    else:
        insert_row = last + 1

    sheet.Rows.insertByIndex(insert_row, 1)
    set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)

# -------------------------
# Formatierung & Höhe setzen
# -------------------------
def set_row_values(sheet, row, sheet_name, item_text, rating, color, src_row):
    sheet.getCellByPosition(0, row).String = f"A{src_row + 1}"
    sheet.getCellByPosition(1, row).String = sheet_name
    sheet.getCellByPosition(2, row).String = item_text
    sheet.getCellByPosition(3, row).String = rating

    for col in range(0, 11):
        cell = sheet.getCellByPosition(col, row)
        try:
            cell.CellBackColor = 0xFFFFFF
            cell.CharHeight = 12
            cell.CharWeight = FONT_NORMAL
            cell.IsTextWrapped = True
        except Exception:
            pass

    cell_c = sheet.getCellByPosition(2, row)
    try:
        cell_c.CellBackColor = color
        cell_c.HoriJustify = HORIZONTAL_LEFT
        cell_c.VertJustify = VERTICAL_CENTER
    except Exception:
        pass

    adjust_row_height_heuristic(sheet, row, extra_points=6)

def adjust_row_height_heuristic(sheet, row_index, extra_points=6):
    try:
        base = 400
        line_height = 400
        text = sheet.getCellByPosition(2, row_index).String
        if not text:
            new_height = base + int(extra_points * 20)
        else:
            line_breaks = text.count("\n")
            approx_lines = max(1, (len(text) // 60) + 1)
            total_lines = max(1, line_breaks + approx_lines)
            new_height = base + int(total_lines * line_height) + int(extra_points * 20)
        sheet.Rows.getByIndex(row_index).Height = int(new_height)
    except Exception:
        pass