from __future__ import annotations

from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from io import BytesIO
from statistics import median
from typing import Any

from openpyxl import load_workbook


def _norm(value: Any) -> str:
    return " ".join(str(value or "").strip().casefold().split())


def _decimal(value: Any) -> Decimal | None:
    text = str(value or "").strip().replace("'", "").replace(" ", "")
    if not text:
        return None
    if "," in text and "." in text:
        text = text.replace(".", "").replace(",", ".") if text.rfind(",") > text.rfind(".") else text.replace(",", "")
    elif "," in text:
        text = text.replace(",", ".")
    try:
        return Decimal(text)
    except InvalidOperation:
        return None


def _date(value: Any) -> str | None:
    if isinstance(value, (datetime, date)):
        return value.date().isoformat() if isinstance(value, datetime) else value.isoformat()
    text = str(value or "").strip()[:10]
    for fmt in ("%Y-%m-%d", "%d.%m.%Y", "%d/%m/%Y"):
        try:
            return datetime.strptime(text, fmt).date().isoformat()
        except ValueError:
            continue
    return None


def fold_raiffeisen_rows(rows: list[dict[str, Any]]) -> list[dict[str, Any]]:
    """Fold provider continuation records before identity or classification.

    A continuation has descriptive text but no account, booking/value date or amount.
    It extends the preceding logical record and can never become an independent row.
    """
    logical: list[dict[str, Any]] = []
    for source in rows:
        item = dict(source)
        low = {_norm(key): value for key, value in item.items()}
        text_key = next((key for key in item if _norm(key) == "text"), "Text")
        description = str(low.get("text") or "").strip()
        continuation = bool(description) and not any(
            str(low.get(key) or "").strip()
            for key in ("iban", "booked at", "valuta date", "credit/debit amount")
        )
        if continuation and logical:
            previous_key = next((key for key in logical[-1] if _norm(key) == "text"), "Text")
            logical[-1][previous_key] = " ".join(
                part for part in (str(logical[-1].get(previous_key) or "").strip(), description) if part
            )
            continue
        item[text_key] = description
        logical.append(item)
    return logical


def parse_raiffeisen_physical_records(
    records: list[list[str]], *, has_balance: bool | None = None
) -> list[dict[str, Any]]:
    """Right-align malformed provider rows and fold their continuation lines.

    The provider emits unquoted commas inside descriptions. Amount, balance and
    value date are therefore recovered from the right edge before any identity
    or merchant logic runs.
    """
    logical: list[dict[str, Any]] = []
    for physical in records:
        values = [str(value or "") for value in physical]
        while values and not values[-1].strip():
            values.pop()
        if not values:
            continue
        if not values[0].strip():
            continuation = " ".join(value.strip() for value in values[2:] if value.strip())
            if continuation and logical:
                logical[-1]["Text"] = " ".join((str(logical[-1]["Text"]).strip(), continuation)).strip()
            continue
        if len(values) < 5:
            continue
        # Legacy exports omit the running Balance column.  Current provider
        # exports include it and may contain unquoted commas in Text.
        row_has_balance = has_balance if has_balance is not None else len(values) >= 6
        right_columns = 3 if row_has_balance else 2
        description = ", ".join(value.strip() for value in values[2:-right_columns] if value.strip())
        logical.append({
            "IBAN": values[0].strip(),
            "Booked At": values[1].strip(),
            "Text": description,
            "Credit/Debit Amount": values[-3 if row_has_balance else -2].strip(),
            "Balance": values[-2].strip() if row_has_balance else "",
            "Valuta Date": values[-1].strip(),
        })

    # Never infer duplicates from similar descriptions. Running balances and
    # provider row positions can distinguish legitimate same-day bookings with
    # identical amounts; duplicate handling belongs in the review layer.
    return logical


def analyze_akb_balance_workbook(data: bytes, *, as_of: str | None = None) -> dict[str, Any]:
    """Read an AKB point-in-time workbook as balances only, never transactions."""
    workbook = load_workbook(BytesIO(data), read_only=True, data_only=True)
    snapshots: list[dict[str, str]] = []
    for sheet in workbook.worksheets:
        raw_rows = [tuple(row) for row in sheet.iter_rows(values_only=True)]
        while raw_rows and not any(value not in (None, "") for value in raw_rows[0]):
            raw_rows.pop(0)
        if not raw_rows:
            continue
        first = raw_rows[0]
        # Current AKB snapshots are intentionally minimal two-column sheets
        # without a header. Headered synthetic/vendor variants remain supported.
        first_balance = _decimal(first[1]) if len(first) > 1 else None
        header_mode = first_balance is None
        headers = [_norm(value) for value in first] if header_mode else []
        account_index = next((i for i, value in enumerate(headers) if value in {"account", "konto", "kontobezeichnung"}), None)
        balance_index = next((i for i, value in enumerate(headers) if value in {"balance", "saldo", "kontostand"}), None)
        currency_index = next((i for i, value in enumerate(headers) if value in {"currency", "währung", "waehrung"}), None)
        as_of_index = next((i for i, value in enumerate(headers) if value in {"as of", "stichtag", "datum"}), None)
        if not header_mode:
            account_index, balance_index, currency_index, as_of_index = 0, 1, None, None
        if account_index is None or balance_index is None:
            continue
        source_rows = raw_rows[1:] if header_mode else raw_rows
        for row in source_rows:
            if account_index >= len(row) or balance_index >= len(row):
                continue
            label = str(row[account_index] or "").strip()
            balance = _decimal(row[balance_index])
            if not label or balance is None:
                continue
            currency = str(row[currency_index] or "CHF").upper() if currency_index is not None and currency_index < len(row) else "CHF"
            row_date = _date(row[as_of_index]) if as_of_index is not None and as_of_index < len(row) else as_of
            snapshots.append({"account_label": label[:120], "balance": format(balance, ".2f"), "currency": currency, "as_of": row_date or ""})
    workbook.close()
    dates = {item["as_of"] for item in snapshots if item["as_of"]}
    return {
        "contract": "account_balance_snapshot",
        "snapshot_count": len(snapshots),
        "transaction_count": 0,
        "as_of": next(iter(dates)) if len(dates) == 1 else None,
        "total_chf": format(sum((Decimal(item["balance"]) for item in snapshots if item["currency"] == "CHF"), Decimal("0")), ".2f"),
        "snapshots": snapshots,
    }


def summarize_visa_rows(rows: list[dict[str, Any]]) -> dict[str, Any]:
    booked = []
    dates: list[str] = []
    transaction_ids: set[str] = set()
    payments: list[Decimal] = []
    for source in rows:
        low = {_norm(key): value for key, value in source.items()}
        status = _norm(low.get("status") or low.get("transactionstatus") or low.get("bookingstatus"))
        if status in {"pending", "pendent", "authorised", "authorized", "vorgemerkt"}:
            continue
        booked.append(source)
        parsed_date = _date(low.get("date") or low.get("valutadate"))
        if parsed_date:
            dates.append(parsed_date)
        transaction_id = str(low.get("transactionid") or "").strip()
        if transaction_id:
            transaction_ids.add(transaction_id)
        if "ihre zahlung" in _norm(low.get("merchantname") or low.get("details")):
            amount = _decimal(low.get("amount"))
            if amount is not None:
                payments.append(abs(amount))
    return {
        "booked_transactions": len(booked),
        "period_start": min(dates) if dates else None,
        "period_end": max(dates) if dates else None,
        "unique_transaction_ids": len(transaction_ids),
        "payment_count": len(payments),
        "payment_total_chf": format(sum(payments, Decimal("0")), ".2f"),
    }


def summarize_migros_rows(rows: list[dict[str, Any]]) -> dict[str, Any]:
    receipts: dict[str, dict[str, Any]] = {}
    for source in rows:
        low = {_norm(key): value for key, value in source.items()}
        key = "|".join(_norm(low.get(name)) for name in ("datum", "zeit", "filiale", "kassennummer", "transaktionsnummer"))
        record = receipts.setdefault(key, {"date": _date(low.get("datum")), "total": Decimal("0"), "rows": 0})
        amount = _decimal(low.get("umsatz"))
        if amount is not None:
            record["total"] += amount
        record["rows"] += 1
    dates = [record["date"] for record in receipts.values() if record["date"]]
    totals = [abs(record["total"]) for record in receipts.values()]
    return {
        "article_rows": len(rows),
        "receipt_count": len(receipts),
        "period_start": min(dates) if dates else None,
        "period_end": max(dates) if dates else None,
        "median_receipt_chf": format(Decimal(str(median(totals))) if totals else Decimal("0"), ".2f"),
        "receipts_at_least_50": sum(total >= Decimal("50") for total in totals),
        "amount_threshold_review_count": 0,
        "duplicate_article_rows_removed": 0,
    }