"""Generiert eine Datenarchitektur-Beschreibung aus den Metadaten.

Analysiert die Power Query M-Ausdrücke um Schichten (Extract/Transform/Load),
Quellsysteme und Abhängigkeiten zu identifizieren. Sprachabhängig (de/en).
"""
from __future__ import annotations

from collections import defaultdict

from core.models import ReportMetadata

_T: dict[str, dict[str, str]] = {
    "de": {
        "sources_heading": "#### Quellsysteme",
        "layers_heading": "#### Schichtenmodell",
        "th_layers": "| Schicht | Tabellen |",
        "sep_layers": "|---------|----------|",
        "modes_heading": "#### Speichermodi",
        "deps_heading": "#### Abhängigkeiten zwischen Quellen und Tabellen",
        "empty": "Keine Datenarchitektur-Informationen verfügbar.",
        "unknown": "Unbekannt",
        "layer_calculated": "Berechnete Tabellen",
        "layer_staging": "Staging / Extract",
        "layer_semantic": "Semantic Model",
        "layer_measures": "Measure-Tabellen",
        "layer_data": "Datentabellen",
    },
    "en": {
        "sources_heading": "#### Source Systems",
        "layers_heading": "#### Layer Model",
        "th_layers": "| Layer | Tables |",
        "sep_layers": "|-------|--------|",
        "modes_heading": "#### Storage Modes",
        "deps_heading": "#### Dependencies Between Sources and Tables",
        "empty": "No data architecture information available.",
        "unknown": "Unknown",
        "layer_calculated": "Calculated tables",
        "layer_staging": "Staging / Extract",
        "layer_semantic": "Semantic Model",
        "layer_measures": "Measure tables",
        "layer_data": "Data tables",
    },
}


def generate_architecture(metadata: ReportMetadata, language: str = "de") -> str:
    """Erzeugt eine Markdown-Beschreibung der Datenarchitektur."""
    t = _T.get(language, _T["de"])
    sections: list[str] = []

    # 1) Quellsysteme identifizieren
    source_types = _identify_sources(metadata, t)
    if source_types:
        sections.append(t["sources_heading"] + "\n")
        for stype, details in sorted(source_types.items()):
            sections.append(f"- **{stype}**: {', '.join(sorted(details))}")
        sections.append("")

    # 2) Schichtenmodell aus Query Groups und M-Ausdrücken ableiten
    layers = _identify_layers(metadata, t)
    if layers:
        sections.append(t["layers_heading"] + "\n")
        sections.append(t["th_layers"])
        sections.append(t["sep_layers"])
        for layer_name, tables in layers.items():
            sections.append(f"| {layer_name} | {', '.join(sorted(tables))} |")
        sections.append("")

    # 3) Speichermodi-Übersicht
    modes = _summarize_modes(metadata, t)
    if modes:
        sections.append(t["modes_heading"] + "\n")
        for mode, tables in sorted(modes.items()):
            sections.append(f"- **{mode}**: {', '.join(sorted(tables))}")
        sections.append("")

    # 4) Abhängigkeiten als Mermaid-Diagramm
    dep_diagram = _generate_dependency_diagram(metadata)
    if dep_diagram:
        sections.append(t["deps_heading"] + "\n")
        sections.append(dep_diagram)

    if not sections:
        return t["empty"]

    return "\n".join(sections)


def _identify_sources(metadata: ReportMetadata, t: dict[str, str]) -> dict[str, set[str]]:
    """Identifiziert Quellsysteme aus Data Sources und Power Queries."""
    sources: dict[str, set[str]] = defaultdict(set)

    for ds in metadata.data_sources:
        stype = ds.source_type or t["unknown"]
        detail = ds.connection_string or ds.name
        if detail:
            sources[stype].add(detail[:60])

    # Ergänze aus Power Query source-Feld
    for pq in metadata.power_queries:
        if not pq.source or pq.source in ("m", "calculated"):
            continue
        if ":" in pq.source:
            stype = pq.source.split(":")[0].strip()
            detail = pq.source.split(":", 1)[1].strip()
            sources[stype].add(detail[:60])

    return dict(sources)


def _identify_layers(metadata: ReportMetadata, t: dict[str, str]) -> dict[str, list[str]]:
    """Identifiziert Schichten aus Tabellennamen und M-Ausdrücken."""
    layers: dict[str, list[str]] = {}

    for table in metadata.tables:
        if table.is_hidden:
            continue
        name_lower = table.name.lower()

        if table.mode == "Calculated":
            layers.setdefault(t["layer_calculated"], []).append(table.name)
        elif "stg" in name_lower or "staging" in name_lower or "raw" in name_lower:
            layers.setdefault(t["layer_staging"], []).append(table.name)
        elif (name_lower.startswith("fact") or name_lower.startswith("dim")
              or table.table_type in ("Faktentabelle", "Dimensionstabelle")):
            layers.setdefault(t["layer_semantic"], []).append(table.name)
        elif not any(not c.is_calculated for c in table.columns):
            # Keine physischen Spalten → Measure-Tabelle
            layers.setdefault(t["layer_measures"], []).append(table.name)
        else:
            layers.setdefault(t["layer_data"], []).append(table.name)

    return layers


def _summarize_modes(metadata: ReportMetadata, t: dict[str, str]) -> dict[str, list[str]]:
    """Gruppiert Tabellen nach Speichermodus."""
    modes: dict[str, list[str]] = defaultdict(list)
    for table in metadata.tables:
        if table.is_hidden:
            continue
        mode = table.mode or t["unknown"]
        modes[mode].append(table.name)
    return dict(modes)


def _generate_dependency_diagram(metadata: ReportMetadata) -> str:
    """Erzeugt ein kompaktes Mermaid-Diagramm der Quell-Abhängigkeiten."""
    lines = ["```mermaid", "flowchart TD"]

    seen: set[str] = set()
    source_nodes: set[str] = set()
    table_nodes: set[str] = set()

    for pq in metadata.power_queries:
        if not pq.source or pq.source in ("m", "calculated"):
            continue
        src_id = _safe_id("q_" + pq.source)
        tbl_id = _safe_id("t_" + pq.target_table)
        src_label = pq.source[:50]

        if src_id not in source_nodes:
            lines.append(f"    {src_id}[(\"{_esc(src_label)}\")]")
            source_nodes.add(src_id)

        tbl = next((t for t in metadata.tables if t.name == pq.target_table and not t.is_hidden), None)
        if tbl:
            if tbl_id not in table_nodes:
                lines.append(f"    {tbl_id}[\"{_esc(pq.target_table)}\"]")
                table_nodes.add(tbl_id)

            edge = f"{src_id}->{tbl_id}"
            if edge not in seen:
                lines.append(f"    {src_id} --> {tbl_id}")
                seen.add(edge)

    lines.append("```")

    if not seen:
        return ""
    return "\n".join(lines)


def _safe_id(text: str, max_len: int = 40) -> str:
    """Erzeugt eine sichere Mermaid-Knoten-ID mit begrenzter Länge."""
    raw = "".join(c if c.isalnum() else "_" for c in text)
    if len(raw) <= max_len:
        return raw
    import hashlib
    suffix = hashlib.md5(text.encode()).hexdigest()[:8]
    return raw[: max_len - 9] + "_" + suffix


def _esc(text: str) -> str:
    """Escaped Sonderzeichen für Mermaid-Labels."""
    result = text.replace('"', "'").replace("\n", " ")
    if len(result) > 50:
        result = result[:47] + "..."
    result = result.rstrip(". ")
    return result
