"""Fabric IQ Readiness-Analyse für Power BI Semantic Models.

Analysiert drei Dimensionen (Metadaten-Vollständigkeit, Star-Schema-Konformität,
Best Practices) und erzeugt einen gewichteten Readiness-Score mit Findings
und Verbesserungsempfehlungen.
"""
from __future__ import annotations

import re
from dataclasses import dataclass, field

from core.models import ReportMetadata


# ---------------------------------------------------------------------------
# Ranking-Schwellenwerte (Modul-Konstanten)
# ---------------------------------------------------------------------------

RANKING_THRESHOLD_READY = 80       # ≥ 80% → "IQ Ready"
RANKING_THRESHOLD_PARTIAL = 50     # ≥ 50% → "Teilweise Ready"
                                   # < 50% → "Nicht Ready"

RANKING_LABEL_READY = "IQ Ready"
RANKING_LABEL_PARTIAL = "Teilweise Ready"
RANKING_LABEL_NOT_READY = "Nicht Ready"

# Schweregrad-Sortierung (höher = schlimmer)
SEVERITY_ORDER: dict[str, int] = {"Hoch": 3, "Mittel": 2, "Niedrig": 1, "High": 3, "Medium": 2, "Low": 1}

# ---------------------------------------------------------------------------
# i18n Translations
# ---------------------------------------------------------------------------
_TRANSLATIONS: dict[str, dict[str, str]] = {
    "de": {
        "severity_high": "Hoch",
        "severity_medium": "Mittel",
        "severity_low": "Niedrig",
        "dim_metadata": "Metadaten",
        "dim_schema": "Schema",
        "dim_best_practices": "Best Practices",
        "cat_completeness": "Vollständigkeit",
        "cat_relationship_quality": "Beziehungsqualität",
        "cat_table_classification": "Tabellenklassifizierung",
        "cat_naming": "Benennung",
        "cat_performance": "Performance",
        "cat_visibility": "Sichtbarkeit",
        "cat_error_prevention": "Fehlerprävention",
        "obj_table": "Tabelle",
        "obj_column": "Spalte",
        "obj_measure": "Measure",
        "obj_relationship": "Beziehung",
        "obj_model": "Modell",
        "obj_full_model": "Gesamtmodell",
        "ranking_ready": "IQ Ready",
        "ranking_partial": "Teilweise Ready",
        "ranking_not_ready": "Nicht Ready",
        # Markdown headers
        "md_top_priorities": "## 🎯 Top-Prioritäten\n",
        "md_score_overview": "## 📊 Score-Übersicht\n",
        "md_detail_findings": "## 📋 Detail-Findings\n",
        "md_metric": "Metrik",
        "md_value": "Wert",
        "md_readiness_score": "Readiness-Score",
        "md_metadata": "Metadaten",
        "md_schema": "Schema",
        "md_best_practices": "Best Practices",
        "md_ranking": "Ranking",
        "md_severity": "Schweregrad",
        "md_object": "Objekt",
        "md_rule": "Regel",
        "md_recommendation": "Empfehlung",
        # Rules
        "rule_description_missing": "Beschreibung fehlt",
        "rule_no_key_column": "Keine Schlüsselspalte definiert",
        "rule_no_synonyms": "Keine Synonyme definiert",
        "rule_format_string_missing": "Format-String fehlt",
        "rule_m2m_detected": "Many-to-Many-Beziehung erkannt",
        "rule_1to1_detected": "One-to-One-Beziehung erkannt",
        "rule_bidir_filter": "Bidirektionale Kreuzfilterung erkannt",
        "rule_inactive_rel": "Inaktive Beziehung erkannt",
        "rule_table_unclassified": "Tabelle konnte nicht klassifiziert werden",
        "rule_forbidden_affix": "Verbotenes Präfix/Suffix im Tabellennamen",
        "rule_col_underscore_camel": "Spaltenname enthält Unterstrich oder CamelCase",
        "rule_cryptic_measure": "Kryptischer Measure-Name",
        "rule_leading_trailing_spaces": "Führende/nachfolgende Leerzeichen im Namen",
        "rule_rel_no_key": "Beziehung basiert nicht auf Schlüsselspalten",
        "rule_fk_not_hidden": "FK-Spalte ist nicht versteckt",
        "rule_mixed_storage": "Gemischter Storage-Modus erkannt",
        "rule_datatype_mismatch": "Unterschiedliche Datentypen in Beziehungsspalten",
        "rule_calc_col_not_hidden": "Berechnete Spalte ist nicht versteckt",
        "rule_helper_table_not_hidden": "Hilfstabelle ist nicht versteckt",
        "rule_no_format_string": "Kein Format-String definiert",
        "rule_numeric_no_format": "Numerische Spalte ohne Format-Kontext",
        "rule_calc_col_no_expr": "Berechnete Spalte ohne Expression",
        "rule_measure_no_expr": "Measure ohne Expression",
        "rule_empty_model": "Leeres Modell",
        # Recommendations
        "rec_add_table_desc": "Fügen Sie eine Beschreibung für die Tabelle '{name}' hinzu.",
        "rec_mark_key_col": "Markieren Sie mindestens eine Spalte in '{name}' als Schlüssel (is_key=True).",
        "rec_add_col_desc": "Fügen Sie eine Beschreibung für die Spalte '{name}' hinzu.",
        "rec_add_synonym": "Definieren Sie mindestens ein Synonym für die Spalte '{name}'.",
        "rec_add_measure_desc": "Fügen Sie eine Beschreibung für das Measure '{name}' hinzu.",
        "rec_add_format_string": "Definieren Sie einen Format-String für das Measure '{name}'.",
        "rec_resolve_m2m": "Lösen Sie die m:n-Beziehung zwischen '{from_t}' und '{to_t}' über eine Brückentabelle auf.",
        "rec_merge_1to1": "Prüfen Sie, ob '{from_t}' und '{to_t}' zusammengeführt werden können.",
        "rec_set_single_filter": "Ändern Sie die Kreuzfilterung zwischen '{from_t}' und '{to_t}' auf 'Single'.",
        "rec_remove_inactive": "Prüfen Sie, ob die inaktive Beziehung zwischen '{from_t}' und '{to_t}' entfernt werden kann.",
        "rec_classify_table": "Prüfen Sie die Beziehungen der Tabelle '{name}' für eine klare Fakt-/Dimensionszuordnung.",
        "rec_remove_affix": "Entfernen Sie technische Präfixe/Suffixe aus '{name}'.",
        "rec_use_spaces": "Verwenden Sie Leerzeichen statt '_' oder CamelCase in '{name}'.",
        "rec_meaningful_name": "Verwenden Sie einen aussagekräftigen Namen statt '{name}'.",
        "rec_trim_table": "Entfernen Sie Leerzeichen am Anfang/Ende des Tabellennamens.",
        "rec_trim_col": "Entfernen Sie Leerzeichen am Anfang/Ende des Spaltennamens.",
        "rec_trim_measure": "Entfernen Sie Leerzeichen am Anfang/Ende des Measure-Namens.",
        "rec_mark_key": "Markieren Sie '{from_col}' oder '{to_col}' als Schlüsselspalte (is_key=True).",
        "rec_hide_fk": "Verstecken Sie die FK-Spalte '{col}' in '{table}' (is_hidden=True).",
        "rec_unify_storage": "Tabelle '{name}' (DirectQuery) mischt mit Import-Tabellen. Vereinheitlichen Sie den Modus.",
        "rec_align_datatypes": "Gleichen Sie die Datentypen an: '{from_col}' ({from_type}) ≠ '{to_col}' ({to_type}).",
        "rec_hide_calc_col": "Verstecken Sie die berechnete Spalte '{col}' in '{table}' (is_hidden=True).",
        "rec_hide_helper": "Verstecken Sie die Hilfstabelle '{name}' (is_hidden=True).",
        "rec_define_format": "Definieren Sie einen Format-String für das Measure '{name}'.",
        "rec_numeric_format": "Definieren Sie einen Format-Kontext für die numerische Spalte '{name}'.",
        "rec_calc_col_expr": "Definieren Sie einen DAX-Ausdruck für die berechnete Spalte '{name}'.",
        "rec_measure_expr": "Definieren Sie einen DAX-Ausdruck für das Measure '{name}'.",
        "rec_empty_model": "Das Modell enthält keine Tabellen. Fügen Sie Tabellen hinzu.",
    },
    "en": {
        "severity_high": "High",
        "severity_medium": "Medium",
        "severity_low": "Low",
        "dim_metadata": "Metadata",
        "dim_schema": "Schema",
        "dim_best_practices": "Best Practices",
        "cat_completeness": "Completeness",
        "cat_relationship_quality": "Relationship Quality",
        "cat_table_classification": "Table Classification",
        "cat_naming": "Naming",
        "cat_performance": "Performance",
        "cat_visibility": "Visibility",
        "cat_error_prevention": "Error Prevention",
        "obj_table": "Table",
        "obj_column": "Column",
        "obj_measure": "Measure",
        "obj_relationship": "Relationship",
        "obj_model": "Model",
        "obj_full_model": "Full Model",
        "ranking_ready": "IQ Ready",
        "ranking_partial": "Partially Ready",
        "ranking_not_ready": "Not Ready",
        # Markdown headers
        "md_top_priorities": "## 🎯 Top Priorities\n",
        "md_score_overview": "## 📊 Score Overview\n",
        "md_detail_findings": "## 📋 Detail Findings\n",
        "md_metric": "Metric",
        "md_value": "Value",
        "md_readiness_score": "Readiness Score",
        "md_metadata": "Metadata",
        "md_schema": "Schema",
        "md_best_practices": "Best Practices",
        "md_ranking": "Ranking",
        "md_severity": "Severity",
        "md_object": "Object",
        "md_rule": "Rule",
        "md_recommendation": "Recommendation",
        # Rules
        "rule_description_missing": "Description missing",
        "rule_no_key_column": "No key column defined",
        "rule_no_synonyms": "No synonyms defined",
        "rule_format_string_missing": "Format string missing",
        "rule_m2m_detected": "Many-to-many relationship detected",
        "rule_1to1_detected": "One-to-one relationship detected",
        "rule_bidir_filter": "Bidirectional cross-filtering detected",
        "rule_inactive_rel": "Inactive relationship detected",
        "rule_table_unclassified": "Table could not be classified",
        "rule_forbidden_affix": "Forbidden prefix/suffix in table name",
        "rule_col_underscore_camel": "Column name contains underscore or CamelCase",
        "rule_cryptic_measure": "Cryptic measure name",
        "rule_leading_trailing_spaces": "Leading/trailing spaces in name",
        "rule_rel_no_key": "Relationship not based on key columns",
        "rule_fk_not_hidden": "FK column is not hidden",
        "rule_mixed_storage": "Mixed storage mode detected",
        "rule_datatype_mismatch": "Different data types in relationship columns",
        "rule_calc_col_not_hidden": "Calculated column is not hidden",
        "rule_helper_table_not_hidden": "Helper table is not hidden",
        "rule_no_format_string": "No format string defined",
        "rule_numeric_no_format": "Numeric column without format context",
        "rule_calc_col_no_expr": "Calculated column without expression",
        "rule_measure_no_expr": "Measure without expression",
        "rule_empty_model": "Empty model",
        # Recommendations
        "rec_add_table_desc": "Add a description for table '{name}'.",
        "rec_mark_key_col": "Mark at least one column in '{name}' as key (is_key=True).",
        "rec_add_col_desc": "Add a description for column '{name}'.",
        "rec_add_synonym": "Define at least one synonym for column '{name}'.",
        "rec_add_measure_desc": "Add a description for measure '{name}'.",
        "rec_add_format_string": "Define a format string for measure '{name}'.",
        "rec_resolve_m2m": "Resolve the m:n relationship between '{from_t}' and '{to_t}' using a bridge table.",
        "rec_merge_1to1": "Consider merging '{from_t}' and '{to_t}' into a single table.",
        "rec_set_single_filter": "Change the cross-filtering between '{from_t}' and '{to_t}' to 'Single'.",
        "rec_remove_inactive": "Consider removing the inactive relationship between '{from_t}' and '{to_t}'.",
        "rec_classify_table": "Review the relationships of table '{name}' for a clear fact/dimension assignment.",
        "rec_remove_affix": "Remove technical prefixes/suffixes from '{name}'.",
        "rec_use_spaces": "Use spaces instead of '_' or CamelCase in '{name}'.",
        "rec_meaningful_name": "Use a meaningful name instead of '{name}'.",
        "rec_trim_table": "Remove leading/trailing spaces from the table name.",
        "rec_trim_col": "Remove leading/trailing spaces from the column name.",
        "rec_trim_measure": "Remove leading/trailing spaces from the measure name.",
        "rec_mark_key": "Mark '{from_col}' or '{to_col}' as key column (is_key=True).",
        "rec_hide_fk": "Hide FK column '{col}' in '{table}' (is_hidden=True).",
        "rec_unify_storage": "Table '{name}' (DirectQuery) is mixed with Import tables. Unify the storage mode.",
        "rec_align_datatypes": "Align data types: '{from_col}' ({from_type}) ≠ '{to_col}' ({to_type}).",
        "rec_hide_calc_col": "Hide calculated column '{col}' in '{table}' (is_hidden=True).",
        "rec_hide_helper": "Hide helper table '{name}' (is_hidden=True).",
        "rec_define_format": "Define a format string for measure '{name}'.",
        "rec_numeric_format": "Define a format context for numeric column '{name}'.",
        "rec_calc_col_expr": "Define a DAX expression for calculated column '{name}'.",
        "rec_measure_expr": "Define a DAX expression for measure '{name}'.",
        "rec_empty_model": "The model contains no tables. Add tables.",
    },
}

# Best-Practice Naming-Präfixe/Suffixe (case-insensitive)
FORBIDDEN_TABLE_AFFIXES: list[str] = [
    "dim_", "fact_", "fakt_", "tbl_",
    "_dim", "_fact", "_fakt", "_tbl",
]


# ---------------------------------------------------------------------------
# Ergebnis-Dataclasses
# ---------------------------------------------------------------------------

@dataclass
class Finding:
    """Ein einzelner Analysebefund."""
    severity: str          # "Hoch", "Mittel", "Niedrig"
    dimension: str         # "Metadaten", "Schema", "Best Practices"
    category: str          # z.B. "Benennung", "Performance", "Sichtbarkeit", "Fehlerprävention"
    object_type: str       # "Tabelle", "Spalte", "Measure", "Beziehung"
    object_name: str       # Name des betroffenen Objekts
    table_name: str        # Zugehöriger Tabellenname (leer bei Tabellen-Findings)
    rule: str              # Kurzbeschreibung der verletzten Regel
    recommendation: str    # Verbesserungsempfehlung (max. 200 Zeichen)


@dataclass
class DimensionResult:
    """Ergebnis einer einzelnen Analysedimension."""
    dimension_name: str    # "Metadaten", "Schema", "Best Practices"
    score_percent: int     # 0–100, ganzzahlig
    findings: list[Finding] = field(default_factory=list)


@dataclass
class ReadinessResult:
    """Vollständiges Ergebnis der Fabric IQ Readiness-Analyse."""
    readiness_score_percent: int     # 0–100, gewichteter Gesamtscore
    ranking_label: str               # "IQ Ready", "Teilweise Ready", "Nicht Ready"
    metadata_score: int              # 0–100
    schema_score: int                # 0–100
    best_practices_score: int        # 0–100
    findings: list[Finding] = field(default_factory=list)  # Sortiert nach Schweregrad (absteigend)
    metadata_result: DimensionResult | None = None
    schema_result: DimensionResult | None = None
    best_practices_result: DimensionResult | None = None


# ---------------------------------------------------------------------------
# Analyzer-Klasse
# ---------------------------------------------------------------------------

class ReadinessAnalyzer:
    """Analysiert die Fabric IQ Readiness eines Power BI Semantic Models.

    Bewertet drei Dimensionen mit konfigurierbaren Gewichten:
    - Metadaten-Vollständigkeit (Default 40%)
    - Star-Schema-Konformität (Default 30%)
    - Best Practices (Default 30%)
    """

    def __init__(
        self,
        weight_metadata: float = 0.40,
        weight_schema: float = 0.30,
        weight_best_practices: float = 0.30,
        language: str = "de",
    ) -> None:
        """Initialisiert den ReadinessAnalyzer mit konfigurierbaren Gewichten.

        Args:
            weight_metadata: Gewicht für Metadaten-Dimension (Default 0.40).
            weight_schema: Gewicht für Schema-Dimension (Default 0.30).
            weight_best_practices: Gewicht für Best-Practices-Dimension (Default 0.30).
            language: Sprache für Ausgabetexte ("de" oder "en", Default "de").
        """
        self.weight_metadata = weight_metadata
        self.weight_schema = weight_schema
        self.weight_best_practices = weight_best_practices
        self._t = _TRANSLATIONS.get(language, _TRANSLATIONS["de"])

    # -----------------------------------------------------------------------
    # Statische Hilfsmethoden
    # -----------------------------------------------------------------------

    @staticmethod
    def _is_filled(value: str | list | None) -> bool:
        """Prüft ob ein Metadaten-Wert als 'ausgefüllt' gilt.

        - Strings: nicht None und nicht leer nach strip()
        - Listen: nicht leer
        - None: nicht ausgefüllt

        Args:
            value: Der zu prüfende Wert.

        Returns:
            True wenn der Wert als ausgefüllt gilt.
        """
        if value is None:
            return False
        if isinstance(value, str):
            return len(value.strip()) > 0
        if isinstance(value, list):
            return len(value) > 0
        return False

    def _get_ranking_label(self, score: float) -> str:
        """Ordnet einem Readiness-Score das passende Ranking-Label zu.

        Args:
            score: Score in Prozent (0–100).

        Returns:
            Ranking-Label als String.
        """
        if score >= RANKING_THRESHOLD_READY:
            return self._t["ranking_ready"]
        elif score >= RANKING_THRESHOLD_PARTIAL:
            return self._t["ranking_partial"]
        else:
            return self._t["ranking_not_ready"]

    @staticmethod
    def _severity_sort_key(severity: str) -> int:
        """Gibt den Sortierungswert für einen Schweregrad zurück (höher = schlimmer).

        Wird für absteigende Sortierung verwendet (negiert beim Sortieren).

        Args:
            severity: Schweregrad-String (DE: "Hoch"/"Mittel"/"Niedrig", EN: "High"/"Medium"/"Low").

        Returns:
            Numerischer Sortierwert (3 für Hoch/High, 2 für Mittel/Medium, 1 für Niedrig/Low, 0 für unbekannt).
        """
        return SEVERITY_ORDER.get(severity, 0)

    # -----------------------------------------------------------------------
    # Private Hilfsmethoden
    # -----------------------------------------------------------------------

    def _find_column(self, metadata: ReportMetadata, table_name: str, column_name: str):
        """Sucht ein ColumnInfo-Objekt anhand von Tabellen- und Spaltenname.

        Args:
            metadata: Die ReportMetadata-Instanz.
            table_name: Name der Tabelle.
            column_name: Name der Spalte.

        Returns:
            Das gefundene ColumnInfo-Objekt oder None.
        """
        for table in metadata.tables:
            if table.name == table_name:
                for col in table.columns:
                    if col.name == column_name:
                        return col
        return None

    # -----------------------------------------------------------------------
    # Öffentliche Methoden
    # -----------------------------------------------------------------------

    def analyze(self, metadata: ReportMetadata) -> ReadinessResult:
        """Führt die vollständige Fabric IQ Readiness-Analyse durch.

        Validiert Gewichte, prüft auf leere Metadaten, führt die drei
        Dimensionsprüfungen durch und berechnet den gewichteten Gesamtscore.

        Args:
            metadata: Die zu analysierende ReportMetadata-Instanz.

        Returns:
            ReadinessResult mit Gesamtscore, Ranking-Label und Findings.

        Raises:
            ValueError: Wenn die Summe der Gewichte nicht 1.0 ergibt (Toleranz 0.001).
        """
        # Gewichts-Validierung
        weight_sum = self.weight_metadata + self.weight_schema + self.weight_best_practices
        if abs(weight_sum - 1.0) > 0.001:
            raise ValueError(
                f"Die Summe der Gewichte muss 1.0 ergeben, ist aber {weight_sum:.4f}."
            )

        # Early-Return für leere Metadaten (keine Tabellen UND keine Measures)
        has_tables = len(metadata.tables) > 0
        has_measures = len(metadata.measures) > 0
        if not has_tables and not has_measures:
            return ReadinessResult(
                readiness_score_percent=0,
                ranking_label=self._t["ranking_not_ready"],
                metadata_score=0,
                schema_score=0,
                best_practices_score=0,
                findings=[],
                metadata_result=None,
                schema_result=None,
                best_practices_result=None,
            )

        # Drei Dimensionsprüfungen durchführen
        meta_result = self._check_metadata(metadata)
        schema_result = self._check_schema(metadata)
        bp_result = self._check_best_practices(metadata)

        # Gewichteten Gesamtscore berechnen
        raw_score = (
            meta_result.score_percent * self.weight_metadata
            + schema_result.score_percent * self.weight_schema
            + bp_result.score_percent * self.weight_best_practices
        )
        # Runden und auf [0, 100] clampen
        total_score = max(0, min(100, round(raw_score)))

        # Ranking-Label zuweisen
        ranking_label = self._get_ranking_label(total_score)

        # Alle Findings aggregieren und nach Schweregrad absteigend sortieren
        all_findings = (
            meta_result.findings + schema_result.findings + bp_result.findings
        )
        all_findings.sort(key=lambda f: -self._severity_sort_key(f.severity))

        return ReadinessResult(
            readiness_score_percent=total_score,
            ranking_label=ranking_label,
            metadata_score=meta_result.score_percent,
            schema_score=schema_result.score_percent,
            best_practices_score=bp_result.score_percent,
            findings=all_findings,
            metadata_result=meta_result,
            schema_result=schema_result,
            best_practices_result=bp_result,
        )

    def format_markdown(self, result: ReadinessResult) -> str:
        """Formatiert ein ReadinessResult als strukturierten Markdown-Bericht.

        Struktur (in dieser Reihenfolge):
        1. Top-Prioritäten (bis zu 3 Findings mit höchstem Schweregrad) — nur wenn Findings vorhanden
        2. Score-Übersicht als Markdown-Tabelle — immer
        3. Detail-Findings gruppiert nach Dimension — nur wenn Findings vorhanden

        Args:
            result: Das zu formatierende Analyseergebnis.

        Returns:
            Markdown-formatierter Bericht als String.
        """
        sections: list[str] = []

        has_findings = len(result.findings) > 0

        # --- 1. Zusammenfassung (Top-Prioritäten) ---
        if has_findings:
            sections.append(self._format_top_priorities(result.findings))

        # --- 2. Score-Übersicht ---
        sections.append(self._format_score_overview(result))

        # --- 3. Detail-Findings gruppiert nach Dimension ---
        if has_findings:
            sections.append(self._format_detail_findings(result.findings))

        return "\n".join(sections)

    def _format_top_priorities(self, findings: list[Finding]) -> str:
        """Formatiert die Top-Prioritäten-Zusammenfassung."""
        lines: list[str] = []
        lines.append(self._t["md_top_priorities"])

        top_findings = findings[:3]
        for finding in top_findings:
            obj_desc = finding.object_name
            if finding.table_name:
                obj_desc = f"{finding.object_name}' in '{finding.table_name}"
            lines.append(
                f"- **[{finding.severity}]** {finding.object_type} '{obj_desc}': {finding.recommendation}"
            )

        lines.append("")
        return "\n".join(lines)

    def _format_score_overview(self, result: ReadinessResult) -> str:
        """Formatiert die Score-Übersicht als Markdown-Tabelle."""
        t = self._t
        lines: list[str] = []
        lines.append(t["md_score_overview"])
        lines.append(f"| {t['md_metric']} | {t['md_value']} |")
        lines.append("|---|---|")
        lines.append(f"| {t['md_readiness_score']} | {result.readiness_score_percent}% |")
        lines.append(f"| {t['md_metadata']} | {result.metadata_score}% |")
        lines.append(f"| {t['md_schema']} | {result.schema_score}% |")
        lines.append(f"| {t['md_best_practices']} | {result.best_practices_score}% |")
        lines.append(f"| {t['md_ranking']} | {result.ranking_label} |")
        lines.append("")
        return "\n".join(lines)

    def _format_detail_findings(self, findings: list[Finding]) -> str:
        """Formatiert die Detail-Findings gruppiert nach Dimension."""
        t = self._t
        lines: list[str] = []
        lines.append(t["md_detail_findings"])

        # Gruppierung nach Dimension in definierter Reihenfolge
        dimension_order = [t["dim_metadata"], t["dim_schema"], t["dim_best_practices"]]

        for dimension in dimension_order:
            dim_findings = [f for f in findings if f.dimension == dimension]
            if not dim_findings:
                continue

            # Innerhalb der Gruppe nach Schweregrad sortieren (absteigend)
            dim_findings.sort(key=lambda f: -self._severity_sort_key(f.severity))

            lines.append(f"### {dimension}\n")
            lines.append(f"| {t['md_severity']} | {t['md_object']} | {t['md_rule']} | {t['md_recommendation']} |")
            lines.append("|---|---|---|---|")

            for finding in dim_findings:
                obj_desc = finding.object_name
                if finding.table_name:
                    obj_desc = f"{finding.object_type} '{finding.object_name}' in '{finding.table_name}'"
                else:
                    obj_desc = f"{finding.object_type} '{finding.object_name}'"
                lines.append(
                    f"| {finding.severity} | {obj_desc} | {finding.rule} | {finding.recommendation} |"
                )

            lines.append("")

        return "\n".join(lines)

    # -----------------------------------------------------------------------
    # Private Prüfer-Methoden (Stubs für spätere Implementierung)
    # -----------------------------------------------------------------------

    def _check_metadata(self, metadata: ReportMetadata) -> DimensionResult:
        """Prüft die Metadaten-Vollständigkeit des Modells.

        Prüft für jede nicht-versteckte Tabelle: Beschreibung, Key-Spalte.
        Prüft für jede nicht-versteckte Spalte: Beschreibung, Synonyme.
        Prüft für jedes Measure: Beschreibung, Format-String.
        Berechnet Completeness-Score als Verhältnis ausgefüllter zu geprüfter Felder.

        Args:
            metadata: Die zu analysierende ReportMetadata-Instanz.

        Returns:
            DimensionResult mit Completeness-Score und Findings.
        """
        # Nicht-versteckte Tabellen ermitteln
        visible_tables = [t for t in metadata.tables if not t.is_hidden]

        # Sonderfall: keine nicht-versteckten Tabellen UND keine Measures
        if not visible_tables and not metadata.measures:
            return DimensionResult(
                dimension_name=self._t["dim_metadata"],
                score_percent=100,
                findings=[],
            )

        findings: list[Finding] = []
        total_fields = 0
        filled_fields = 0

        # --- Tabellen-Prüfung ---
        for table in visible_tables:
            # 1. Beschreibung der Tabelle
            total_fields += 1
            if self._is_filled(table.description):
                filled_fields += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_metadata"],
                    category=self._t["cat_completeness"],
                    object_type=self._t["obj_table"],
                    object_name=table.name,
                    table_name="",
                    rule=self._t["rule_description_missing"],
                    recommendation=self._t["rec_add_table_desc"].format(name=table.name),
                ))

            # 2. Mindestens eine Key-Spalte (nicht-versteckte Spalte mit is_key=True)
            total_fields += 1
            visible_columns = [c for c in table.columns if not c.is_hidden]
            has_key = any(c.is_key for c in visible_columns)
            if has_key:
                filled_fields += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_metadata"],
                    category=self._t["cat_completeness"],
                    object_type=self._t["obj_table"],
                    object_name=table.name,
                    table_name="",
                    rule=self._t["rule_no_key_column"],
                    recommendation=self._t["rec_mark_key_col"].format(name=table.name),
                ))

            # --- Spalten-Prüfung (nur nicht-versteckte Spalten) ---
            for col in visible_columns:
                # 3. Beschreibung der Spalte
                total_fields += 1
                if self._is_filled(col.description):
                    filled_fields += 1
                else:
                    findings.append(Finding(
                        severity=self._t["severity_medium"],
                        dimension=self._t["dim_metadata"],
                        category=self._t["cat_completeness"],
                        object_type=self._t["obj_column"],
                        object_name=col.name,
                        table_name=table.name,
                        rule=self._t["rule_description_missing"],
                        recommendation=self._t["rec_add_col_desc"].format(name=col.name),
                    ))

                # 4. Mindestens ein Synonym
                total_fields += 1
                if self._is_filled(col.synonyms):
                    filled_fields += 1
                else:
                    findings.append(Finding(
                        severity=self._t["severity_medium"],
                        dimension=self._t["dim_metadata"],
                        category=self._t["cat_completeness"],
                        object_type=self._t["obj_column"],
                        object_name=col.name,
                        table_name=table.name,
                        rule=self._t["rule_no_synonyms"],
                        recommendation=self._t["rec_add_synonym"].format(name=col.name),
                    ))

        # --- Measure-Prüfung ---
        for measure in metadata.measures:
            # 5. Beschreibung des Measures
            total_fields += 1
            if self._is_filled(measure.description):
                filled_fields += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_metadata"],
                    category=self._t["cat_completeness"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_description_missing"],
                    recommendation=self._t["rec_add_measure_desc"].format(name=measure.name),
                ))

            # 6. Format-String definiert
            total_fields += 1
            if self._is_filled(measure.format_string):
                filled_fields += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_metadata"],
                    category=self._t["cat_completeness"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_format_string_missing"],
                    recommendation=self._t["rec_add_format_string"].format(name=measure.name),
                ))

        # Score berechnen
        if total_fields == 0:
            score = 100
        else:
            score = round((filled_fields / total_fields) * 100)

        return DimensionResult(
            dimension_name=self._t["dim_metadata"],
            score_percent=score,
            findings=findings,
        )

    def _check_schema(self, metadata: ReportMetadata) -> DimensionResult:
        """Prüft die Star-Schema-Konformität des Modells.

        Klassifiziert Tabellen als Fakt-, Dimensions- oder unklassifizierte Tabellen
        basierend auf den Beziehungen im Modell. Erzeugt Findings für problematische
        Beziehungstypen (ManyToMany, bidirektional, inaktiv, OneToOne).

        Args:
            metadata: Die zu analysierende ReportMetadata-Instanz.

        Returns:
            DimensionResult mit Schema-Score und Findings.
        """
        findings: list[Finding] = []
        all_tables = metadata.tables
        total_tables = len(all_tables)

        # Sonderfall: keine Tabellen
        if total_tables == 0:
            return DimensionResult(
                dimension_name=self._t["dim_schema"],
                score_percent=0,
                findings=[],
            )

        # Alle Tabellennamen sammeln
        table_names = {t.name for t in all_tables}

        # --- Beziehungsanalyse ---
        # Für jede 1:n-Beziehung: Many-Seite und One-Seite identifizieren
        # many_side_connections: {many_table: set of one_side_tables}
        many_side_connections: dict[str, set[str]] = {name: set() for name in table_names}
        # one_side_tables: Tabellen die mindestens einmal auf der One-Seite stehen
        one_side_tables: set[str] = set()

        m2m_count = 0
        both_count = 0

        for rel in metadata.relationships:
            cardinality = rel.cardinality

            # --- ManyToMany Finding ---
            if cardinality == "ManyToMany":
                m2m_count += 1
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_schema"],
                    category=self._t["cat_relationship_quality"],
                    object_type=self._t["obj_relationship"],
                    object_name=f"{rel.from_table} ↔ {rel.to_table}",
                    table_name="",
                    rule=self._t["rule_m2m_detected"],
                    recommendation=self._t["rec_resolve_m2m"].format(from_t=rel.from_table, to_t=rel.to_table),
                ))

            # --- OneToOne Finding ---
            elif cardinality == "OneToOne":
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_schema"],
                    category=self._t["cat_relationship_quality"],
                    object_type=self._t["obj_relationship"],
                    object_name=f"{rel.from_table} ↔ {rel.to_table}",
                    table_name="",
                    rule=self._t["rule_1to1_detected"],
                    recommendation=self._t["rec_merge_1to1"].format(from_t=rel.from_table, to_t=rel.to_table),
                ))

            # --- 1:n Beziehung: Many-Seite und One-Seite bestimmen ---
            elif cardinality == "OneToMany":
                # OneToMany: from_table ist One-Seite, to_table ist Many-Seite
                one_table = rel.from_table
                many_table = rel.to_table
                if many_table in many_side_connections:
                    many_side_connections[many_table].add(one_table)
                one_side_tables.add(one_table)

            elif cardinality == "ManyToOne":
                # ManyToOne: from_table ist Many-Seite, to_table ist One-Seite
                many_table = rel.from_table
                one_table = rel.to_table
                if many_table in many_side_connections:
                    many_side_connections[many_table].add(one_table)
                one_side_tables.add(one_table)

            # --- Bidirektionale Kreuzfilterung Finding ---
            if rel.cross_filter == "Both":
                both_count += 1
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_schema"],
                    category=self._t["cat_relationship_quality"],
                    object_type=self._t["obj_relationship"],
                    object_name=f"{rel.from_table} ↔ {rel.to_table}",
                    table_name="",
                    rule=self._t["rule_bidir_filter"],
                    recommendation=self._t["rec_set_single_filter"].format(from_t=rel.from_table, to_t=rel.to_table),
                ))

            # --- Inaktive Beziehung Finding ---
            if not rel.is_active:
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_schema"],
                    category=self._t["cat_relationship_quality"],
                    object_type=self._t["obj_relationship"],
                    object_name=f"{rel.from_table} → {rel.to_table}",
                    table_name="",
                    rule=self._t["rule_inactive_rel"],
                    recommendation=self._t["rec_remove_inactive"].format(from_t=rel.from_table, to_t=rel.to_table),
                ))

        # --- Tabellenklassifizierung ---
        # Faktentabellen: Many-Seite von 1:n-Beziehungen zu ≥ 2 verschiedenen One-Seite-Tabellen
        fact_tables: set[str] = set()
        for table_name, one_sides in many_side_connections.items():
            if len(one_sides) >= 2:
                fact_tables.add(table_name)

        # Dimensionstabellen: On the One-side of at least one 1:n relationship, NOT a fact table
        dimension_tables: set[str] = one_side_tables - fact_tables

        # Klassifizierte Tabellen
        classified_tables = fact_tables | dimension_tables

        # Unklassifizierte Tabellen
        unclassified_tables = table_names - classified_tables

        for tbl_name in sorted(unclassified_tables):
            findings.append(Finding(
                severity=self._t["severity_low"],
                dimension=self._t["dim_schema"],
                category=self._t["cat_table_classification"],
                object_type=self._t["obj_table"],
                object_name=tbl_name,
                table_name="",
                rule=self._t["rule_table_unclassified"],
                recommendation=self._t["rec_classify_table"].format(name=tbl_name),
            ))

        # --- Schema-Score berechnen ---
        classified_count = len(classified_tables)
        score = max(0, round((classified_count / total_tables) * 100 - 10 * m2m_count - 5 * both_count))

        return DimensionResult(
            dimension_name=self._t["dim_schema"],
            score_percent=score,
            findings=findings,
        )

    def _check_best_practices(self, metadata: ReportMetadata) -> DimensionResult:
        """Prüft die Einhaltung von Power BI Best Practices.

        Prüft Regeln in den Kategorien:
        - Benennung (Naming-Conventions)
        - Performance (Schlüssel, FK, Storage-Modi, Datentypen)
        - Sichtbarkeit und Formatierung (Layout-Regeln)
        - Fehlerprävention (Error Prevention)

        Args:
            metadata: Die zu analysierende ReportMetadata-Instanz.

        Returns:
            DimensionResult mit Best-Practice-Score und Findings.
        """
        findings: list[Finding] = []
        total_checks = 0
        passed_checks = 0

        # Sonderfall: keine Tabellen → Score 0%, Finding "Hoch" (Req 4.17)
        if not metadata.tables:
            findings.append(Finding(
                severity=self._t["severity_high"],
                dimension=self._t["dim_best_practices"],
                category=self._t["cat_error_prevention"],
                object_type=self._t["obj_model"],
                object_name=self._t["obj_full_model"],
                table_name="",
                rule=self._t["rule_empty_model"],
                recommendation=self._t["rec_empty_model"],
            ))
            return DimensionResult(
                dimension_name=self._t["dim_best_practices"],
                score_percent=0,
                findings=findings,
            )

        # =================================================================
        # Kategorie: Benennung (Naming-Regeln)
        # =================================================================

        # Regex für CamelCase-Erkennung: Kleinbuchstabe direkt gefolgt von Großbuchstabe
        camel_case_pattern = re.compile(r"[a-z][A-Z]")
        # Regex für kryptische Measure-Namen: nur Großbuchstaben und Ziffern
        cryptic_pattern = re.compile(r"^[A-Z0-9]+$")

        # --- 4.1: Tabellennamen auf verbotene Präfixe/Suffixe prüfen ---
        for table in metadata.tables:
            total_checks += 1
            name_lower = table.name.lower()
            has_forbidden_affix = any(
                name_lower.startswith(affix) or name_lower.endswith(affix)
                for affix in FORBIDDEN_TABLE_AFFIXES
            )
            if has_forbidden_affix:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_naming"],
                    object_type=self._t["obj_table"],
                    object_name=table.name,
                    table_name="",
                    rule=self._t["rule_forbidden_affix"],
                    recommendation=self._t["rec_remove_affix"].format(name=table.name),
                ))
            else:
                passed_checks += 1

        # --- 4.2: Spaltennamen auf Unterstriche oder CamelCase prüfen ---
        for table in metadata.tables:
            for col in table.columns:
                total_checks += 1
                has_underscore = "_" in col.name
                has_camel_case = bool(camel_case_pattern.search(col.name))
                if has_underscore or has_camel_case:
                    findings.append(Finding(
                        severity=self._t["severity_low"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_naming"],
                        object_type=self._t["obj_column"],
                        object_name=col.name,
                        table_name=table.name,
                        rule=self._t["rule_col_underscore_camel"],
                        recommendation=self._t["rec_use_spaces"].format(name=col.name),
                    ))
                else:
                    passed_checks += 1

        # --- 4.3: Measure-Namen auf kryptische Abkürzungen prüfen ---
        for measure in metadata.measures:
            total_checks += 1
            is_too_short = len(measure.name) < 3
            is_cryptic = bool(cryptic_pattern.match(measure.name))
            if is_too_short or is_cryptic:
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_naming"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_cryptic_measure"],
                    recommendation=self._t["rec_meaningful_name"].format(name=measure.name),
                ))
            else:
                passed_checks += 1

        # --- 4.4: Alle Objektnamen auf führende/nachfolgende Leerzeichen prüfen ---
        # Tabellennamen
        for table in metadata.tables:
            total_checks += 1
            if table.name != table.name.strip():
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_naming"],
                    object_type=self._t["obj_table"],
                    object_name=table.name,
                    table_name="",
                    rule=self._t["rule_leading_trailing_spaces"],
                    recommendation=self._t["rec_trim_table"],
                ))
            else:
                passed_checks += 1

        # Spaltennamen
        for table in metadata.tables:
            for col in table.columns:
                total_checks += 1
                if col.name != col.name.strip():
                    findings.append(Finding(
                        severity=self._t["severity_low"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_naming"],
                        object_type=self._t["obj_column"],
                        object_name=col.name,
                        table_name=table.name,
                        rule=self._t["rule_leading_trailing_spaces"],
                        recommendation=self._t["rec_trim_col"],
                    ))
                else:
                    passed_checks += 1

        # Measure-Namen
        for measure in metadata.measures:
            total_checks += 1
            if measure.name != measure.name.strip():
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_naming"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_leading_trailing_spaces"],
                    recommendation=self._t["rec_trim_measure"],
                ))
            else:
                passed_checks += 1

        # =================================================================
        # Kategorie: Performance
        # =================================================================

        # --- 4.5: Beziehungen auf Schlüsselspalten prüfen ---
        for rel in metadata.relationships:
            total_checks += 1
            from_col = self._find_column(metadata, rel.from_table, rel.from_column)
            to_col = self._find_column(metadata, rel.to_table, rel.to_column)
            from_is_key = from_col.is_key if from_col else False
            to_is_key = to_col.is_key if to_col else False
            if from_is_key or to_is_key:
                passed_checks += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_performance"],
                    object_type=self._t["obj_relationship"],
                    object_name=f"{rel.from_table}.{rel.from_column} → {rel.to_table}.{rel.to_column}",
                    table_name="",
                    rule=self._t["rule_rel_no_key"],
                    recommendation=self._t["rec_mark_key"].format(from_col=rel.from_column, to_col=rel.to_column),
                ))

        # --- 4.6: FK-Spalten als versteckt prüfen ---
        for rel in metadata.relationships:
            # FK-Spalte bestimmen: Many-Seite der Beziehung
            fk_table: str | None = None
            fk_column: str | None = None
            if rel.cardinality == "OneToMany":
                # to_table ist Many-Seite → to_column ist FK
                fk_table = rel.to_table
                fk_column = rel.to_column
            elif rel.cardinality == "ManyToOne":
                # from_table ist Many-Seite → from_column ist FK
                fk_table = rel.from_table
                fk_column = rel.from_column

            if fk_table and fk_column:
                total_checks += 1
                col_obj = self._find_column(metadata, fk_table, fk_column)
                if col_obj and col_obj.is_hidden:
                    passed_checks += 1
                else:
                    findings.append(Finding(
                        severity=self._t["severity_low"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_performance"],
                        object_type=self._t["obj_column"],
                        object_name=fk_column,
                        table_name=fk_table,
                        rule=self._t["rule_fk_not_hidden"],
                        recommendation=self._t["rec_hide_fk"].format(col=fk_column, table=fk_table),
                    ))

        # --- 4.7: Gemischte Storage-Modi (DirectQuery + Import) prüfen ---
        import_tables = [t for t in metadata.tables if t.mode and t.mode.lower() == "import"]
        dq_tables = [t for t in metadata.tables if t.mode and t.mode.lower() == "directquery"]

        if import_tables and dq_tables:
            for dq_table in dq_tables:
                total_checks += 1
                findings.append(Finding(
                    severity=self._t["severity_medium"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_performance"],
                    object_type=self._t["obj_table"],
                    object_name=dq_table.name,
                    table_name="",
                    rule=self._t["rule_mixed_storage"],
                    recommendation=self._t["rec_unify_storage"].format(name=dq_table.name),
                ))
        else:
            # Wenn keine Mischung vorliegt, zählen wir für jede DQ-Tabelle einen bestandenen Check
            for _ in dq_tables:
                total_checks += 1
                passed_checks += 1

        # --- 4.8: Datentyp-Übereinstimmung bei Beziehungsspalten prüfen ---
        for rel in metadata.relationships:
            total_checks += 1
            from_col = self._find_column(metadata, rel.from_table, rel.from_column)
            to_col = self._find_column(metadata, rel.to_table, rel.to_column)
            if from_col and to_col:
                if from_col.data_type == to_col.data_type:
                    passed_checks += 1
                else:
                    findings.append(Finding(
                        severity=self._t["severity_high"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_performance"],
                        object_type=self._t["obj_relationship"],
                        object_name=f"{rel.from_table}.{rel.from_column} → {rel.to_table}.{rel.to_column}",
                        table_name="",
                        rule=self._t["rule_datatype_mismatch"],
                        recommendation=self._t["rec_align_datatypes"].format(from_col=rel.from_column, from_type=from_col.data_type, to_col=rel.to_column, to_type=to_col.data_type),
                    ))
            else:
                # Spalte nicht gefunden → kann nicht geprüft werden, zählt als bestanden
                passed_checks += 1

        # =================================================================
        # Kategorie: Sichtbarkeit und Formatierung
        # =================================================================

        # --- 4.9: Berechnete Spalten als versteckt prüfen ---
        for table in metadata.tables:
            for col in table.columns:
                if col.is_calculated:
                    total_checks += 1
                    if col.is_hidden:
                        passed_checks += 1
                    else:
                        findings.append(Finding(
                            severity=self._t["severity_low"],
                            dimension=self._t["dim_best_practices"],
                            category=self._t["cat_visibility"],
                            object_type=self._t["obj_column"],
                            object_name=col.name,
                            table_name=table.name,
                            rule=self._t["rule_calc_col_not_hidden"],
                            recommendation=self._t["rec_hide_calc_col"].format(col=col.name, table=table.name),
                        ))

        # --- 4.10: Hilfstabellen als versteckt prüfen ---
        for table in metadata.tables:
            if table.table_type == "Hilfstabelle":
                total_checks += 1
                if table.is_hidden:
                    passed_checks += 1
                else:
                    findings.append(Finding(
                        severity=self._t["severity_low"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_visibility"],
                        object_type=self._t["obj_table"],
                        object_name=table.name,
                        table_name="",
                        rule=self._t["rule_helper_table_not_hidden"],
                        recommendation=self._t["rec_hide_helper"].format(name=table.name),
                    ))

        # --- 4.11: Format-Strings für Measures prüfen ---
        for measure in metadata.measures:
            total_checks += 1
            if measure.format_string is not None and measure.format_string != "":
                passed_checks += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_low"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_visibility"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_no_format_string"],
                    recommendation=self._t["rec_define_format"].format(name=measure.name),
                ))

        # --- 4.12: Numerische Spalten ohne Format-Kontext prüfen ---
        # FK- und Key-Spalten sammeln (diese benötigen kein Format)
        fk_key_columns: set[tuple[str, str]] = set()
        for rel in metadata.relationships:
            fk_key_columns.add((rel.from_table, rel.from_column))
            fk_key_columns.add((rel.to_table, rel.to_column))
        for table in metadata.tables:
            for col in table.columns:
                if col.is_key:
                    fk_key_columns.add((table.name, col.name))

        numeric_types = {"Int64", "Double", "Decimal"}
        for table in metadata.tables:
            for col in table.columns:
                if col.data_type in numeric_types and not col.is_hidden:
                    # Spalte ist numerisch und sichtbar
                    if (table.name, col.name) in fk_key_columns:
                        # FK/Key-Spalten sind ausgenommen
                        continue
                    total_checks += 1
                    # Prüfe ob ein Format-Kontext existiert (format_string-Feld existiert nicht
                    # direkt auf ColumnInfo, daher prüfen wir nur ob die Spalte als FK/Key verwendet wird)
                    # Da ColumnInfo kein format_string hat, erzeugen wir das Finding
                    findings.append(Finding(
                        severity=self._t["severity_low"],
                        dimension=self._t["dim_best_practices"],
                        category=self._t["cat_visibility"],
                        object_type=self._t["obj_column"],
                        object_name=col.name,
                        table_name=table.name,
                        rule=self._t["rule_numeric_no_format"],
                        recommendation=self._t["rec_numeric_format"].format(name=col.name),
                    ))

        # =================================================================
        # Kategorie: Fehlerprävention
        # =================================================================

        # --- 4.13: Berechnete Spalten müssen einen Expression-Wert haben ---
        for table in metadata.tables:
            for col in table.columns:
                if col.is_calculated:
                    total_checks += 1
                    if col.expression is not None and col.expression.strip() != "":
                        passed_checks += 1
                    else:
                        findings.append(Finding(
                            severity=self._t["severity_high"],
                            dimension=self._t["dim_best_practices"],
                            category=self._t["cat_error_prevention"],
                            object_type=self._t["obj_column"],
                            object_name=col.name,
                            table_name=table.name,
                            rule=self._t["rule_calc_col_no_expr"],
                            recommendation=self._t["rec_calc_col_expr"].format(name=col.name),
                        ))

        # --- 4.14: Measures müssen einen Expression-Wert haben ---
        for measure in metadata.measures:
            total_checks += 1
            if measure.expression is not None and measure.expression.strip() != "":
                passed_checks += 1
            else:
                findings.append(Finding(
                    severity=self._t["severity_high"],
                    dimension=self._t["dim_best_practices"],
                    category=self._t["cat_error_prevention"],
                    object_type=self._t["obj_measure"],
                    object_name=measure.name,
                    table_name=measure.table_name,
                    rule=self._t["rule_measure_no_expr"],
                    recommendation=self._t["rec_measure_expr"].format(name=measure.name),
                ))

        # =================================================================
        # Score berechnen
        # =================================================================
        if total_checks > 0:
            score = round((passed_checks / total_checks) * 100)
        else:
            score = 0

        return DimensionResult(
            dimension_name=self._t["dim_best_practices"],
            score_percent=score,
            findings=findings,
        )
