from __future__ import annotations

import re
from collections import Counter
from decimal import Decimal
from sqlite3 import Connection
from typing import Any


CLASSIFICATION_VERSION = "household_classification_v2"


def normalize_merchant(value: Any) -> str:
    text = str(value or "").casefold().replace("\u00a0", " ")
    text = re.sub(r"[^\wäöüéèàçß]+", " ", text, flags=re.UNICODE)
    return " ".join(text.split())


def _account_role(source_type: str) -> str:
    if source_type == "visa_credit_card":
        return "card_liability"
    if source_type in {"akb_bank", "raiffeisen_bank"}:
        return "bank_cash"
    return source_type or "unknown"


def _category(conn: Connection, category_id: str | None) -> tuple[str | None, str | None]:
    if not category_id:
        return None, None
    row = conn.execute(
        "SELECT category_id,name FROM budget_categories WHERE category_id=? AND is_active=1",
        (category_id,),
    ).fetchone()
    return (str(row["category_id"]), str(row["name"])) if row else (None, None)


def _base(*, state: str, semantics: str, message: str) -> dict[str, Any]:
    return {
        "classification_version": CLASSIFICATION_VERSION,
        "category_id": None,
        "category_name": None,
        "user_state": state,
        "transaction_semantics": semantics,
        "budget_effect_chf": "0.00" if semantics in {"transfer", "credit_card_payment", "user_confirmed_unmatched_transfer", "unmatched_neutral_transfer"} else None,
        "user_message": message,
        "origin": "unresolved",
        "learned_rule": False,
        "evidence_count": 0,
        "conflict_count": 0,
        "create_counterbooking": False,
        "internal_reason": "unresolved_exception",
        "evidence_transaction_ids": [],
    }


def _confirmed_history(conn: Connection, merchant: str, source_type: str) -> list[dict[str, str]]:
    role = _account_role(source_type)
    rows = conn.execute(
        """SELECT t.budget_transaction_id,t.category_id,c.source_type,c.merchant,c.description
           FROM budget_transactions t
           JOIN budget_transaction_candidates c ON c.transaction_candidate_id=t.source_candidate_id
           WHERE t.status='confirmed' AND t.category_id IS NOT NULL
           ORDER BY t.transaction_date,t.budget_transaction_id"""
    ).fetchall()
    result: list[dict[str, str]] = []
    for item in rows:
        historical_merchant = normalize_merchant(item["merchant"] or item["description"])
        historical_source = str(item["source_type"] or "")
        if (
            historical_merchant == merchant
            and historical_source == source_type
            and _account_role(historical_source) == role
        ):
            result.append({"transaction_id": str(item["budget_transaction_id"]), "category_id": str(item["category_id"])})
    return result


def _exact_rule(conn: Connection, merchant: str, source_type: str) -> tuple[str | None, str | None, str | None]:
    direct = conn.execute(
        """SELECT merchant_id,default_category_id FROM budget_merchants
           WHERE is_active=1 AND normalized_name=? AND default_category_id IS NOT NULL
           ORDER BY merchant_id LIMIT 1""",
        (merchant,),
    ).fetchone()
    if direct:
        category_id, name = _category(conn, str(direct["default_category_id"]))
        if category_id:
            return category_id, name, str(direct["merchant_id"])
    aliases = conn.execute(
        """SELECT a.alias_id,a.pattern,a.match_type,m.default_category_id
           FROM budget_merchant_aliases a JOIN budget_merchants m ON m.merchant_id=a.merchant_id
           WHERE a.is_active=1 AND m.is_active=1 AND m.default_category_id IS NOT NULL
             AND (a.source_type IS NULL OR a.source_type='all' OR a.source_type=?)
           ORDER BY a.priority DESC,a.alias_id""",
        (source_type,),
    ).fetchall()
    for alias in aliases:
        pattern = normalize_merchant(alias["pattern"])
        matched = merchant == pattern if str(alias["match_type"]) == "exact" else bool(pattern and pattern in merchant)
        if matched:
            category_id, name = _category(conn, str(alias["default_category_id"]))
            if category_id:
                return category_id, name, str(alias["alias_id"])
    return None, None, None


def _source_rule(conn: Connection, merchant: str, source_type: str) -> tuple[str | None, str | None, str | None]:
    rows = conn.execute(
        """SELECT rule_id,merchant_contains,category_id FROM budget_review_rules
           WHERE is_active=1 AND category_id IS NOT NULL
             AND (source_type IS NULL OR source_type='' OR source_type=? )
           ORDER BY priority DESC,rule_id""",
        (source_type,),
    ).fetchall()
    for rule in rows:
        token = normalize_merchant(rule["merchant_contains"])
        if token and token in merchant:
            category_id, name = _category(conn, str(rule["category_id"]))
            if category_id:
                return category_id, name, str(rule["rule_id"])
    return None, None, None


def classify_household_row(conn: Connection, row: dict[str, Any]) -> dict[str, Any]:
    source_type = str(row.get("source_type") or "")
    description = normalize_merchant(row.get("merchant") or row.get("description"))
    classification = str(row.get("classification") or "")
    disposition = str(row.get("disposition") or "")
    amount = Decimal(str(row.get("signed_amount") or "0"))

    # Priority 1: transfers are neutral only when the pairing engine found a
    # safe counterbooking. Transaction-specific exceptions belong in audited
    # rules/decisions, never in product code.
    if disposition == "transfer_confirmed" or row.get("pairing_class") == "safe":
        result = _base(state="special_case", semantics="transfer", message="Interner Transfer erkannt.")
        result.update(origin="safe_transfer", internal_reason="safe_own_transfer")
        return result

    # Priority 2: card settlement/refund/reversal semantics always precede expense categories.
    if classification in {"credit_card_payment", "credit_card_payment_counterpost"} or "ihre zahlung danke" in description:
        result = _base(
            state="special_case",
            semantics="credit_card_payment",
            message="Kreditkartenrückzahlung. Die Gegenbuchung ist in den vorhandenen Importen noch nicht gefunden.",
        )
        result.update(origin="card_payment_semantics", internal_reason="credit_card_payment_neutral")
        return result
    if classification in {"credit_card_refund", "credit_card_reversal"} or amount > 0 and source_type == "visa_credit_card":
        result = _base(state="special_case", semantics="refund", message="Rückerstattung oder Storno erkannt.")
        result.update(origin="refund_or_reversal", internal_reason=classification or "positive_card_refund")
        return result

    # A bank interest credit is ordinary income, never a transfer or balance
    # correction. Reuse the most specific existing income category and fall
    # back to the already established broad income category; never create a
    # near-duplicate category during preview.
    if (
        source_type in {"akb_bank", "raiffeisen_bank"}
        and amount > 0
        and any(token in description for token in ("zins", "interest"))
    ):
        category = conn.execute(
            """SELECT category_id,name FROM budget_categories
               WHERE is_active=1 AND category_type='income'
                 AND lower(name) IN ('zinsertrag','kapitalertrag','sonstige einnahmen')
               ORDER BY CASE lower(name)
                 WHEN 'zinsertrag' THEN 1 WHEN 'kapitalertrag' THEN 2 ELSE 3 END
               LIMIT 1"""
        ).fetchone()
        if category:
            result = _base(
                state="proposal_ready",
                semantics="income",
                message="Bestehende Kategorie für den Zinsertrag vorgeschlagen.",
            )
            result.update(
                category_id=str(category["category_id"]),
                category_name=str(category["name"]),
                origin="bank_interest_existing_category",
                internal_reason="bank_interest_income_category_reused",
            )
            return result

    # Priority 4: an explicit exact merchant/alias rule.
    category_id, category_name, rule_id = _exact_rule(conn, description, source_type)
    if category_id:
        result = _base(state="proposal_ready", semantics="expense" if amount < 0 else "income", message="Kategorie aus Ihrer Händlerregel vorgeschlagen.")
        result.update(category_id=category_id, category_name=category_name, origin="exact_merchant_rule", internal_reason="exact_merchant_rule", rule_id=rule_id)
        return result

    # Priority 5: same normalized merchant and same account role/source history.
    history = _confirmed_history(conn, description, source_type)
    category_counts = Counter(item["category_id"] for item in history)
    if len(category_counts) == 1:
        selected = next(iter(category_counts))
        category_id, category_name = _category(conn, selected)
        if category_id:
            evidence = [item["transaction_id"] for item in history]
            result = _base(state="proposal_ready", semantics="expense" if amount < 0 else "income", message="Kategorie aus ähnlichen bestätigten Buchungen vorgeschlagen.")
            result.update(
                category_id=category_id,
                category_name=category_name,
                origin="learned_merchant_history" if len(history) >= 2 else "confirmed_merchant_history",
                learned_rule=len(history) >= 2,
                evidence_count=len(history),
                evidence_transaction_ids=evidence,
                internal_reason="consistent_confirmed_merchant_history",
            )
            return result
    if len(category_counts) > 1:
        result = _base(state="decision_needed", semantics="expense" if amount < 0 else "income", message="Bisherige Buchungen wurden unterschiedlich kategorisiert.")
        result.update(origin="conflicting_history", evidence_count=len(history), conflict_count=len(category_counts), internal_reason="conflicting_confirmed_categories", evidence_transaction_ids=[item["transaction_id"] for item in history])
        return result

    # Priority 6: source-specific review/category rules.
    category_id, category_name, rule_id = _source_rule(conn, description, source_type)
    if category_id:
        result = _base(state="proposal_ready", semantics="expense" if amount < 0 else "income", message="Kategorie aus einer passenden Quellregel vorgeschlagen.")
        result.update(category_id=category_id, category_name=category_name, origin="source_rule", internal_reason="source_specific_rule", rule_id=rule_id)
        return result

    # Migros uses the one existing broad category where available; receipt size is irrelevant.
    if source_type != "migros_receipts" and "migros" in description:
        match = conn.execute("SELECT category_id,name FROM budget_categories WHERE is_active=1 AND lower(name)='essen + haushalt' LIMIT 1").fetchone()
        if match:
            result = _base(state="proposal_ready", semantics="expense", message="Kategorie Essen + Haushalt vorgeschlagen.")
            result.update(category_id=str(match["category_id"]), category_name=str(match["name"]), origin="migros_existing_category", internal_reason="migros_existing_food_household_category")
            return result

    return _base(
        state="decision_needed",
        semantics="expense" if amount < 0 else "income",
        message="Bitte eine Kategorie auswählen.",
    )