from __future__ import annotations

from dataclasses import asdict, dataclass
from datetime import UTC, datetime
from decimal import Decimal
import hashlib
import re
from sqlite3 import Connection

from jarvis_finance.quality.freshness import FreshnessStatus, combined_freshness, freshness_status


@dataclass(frozen=True)
class ReconciliationRecord:
    account_id: str
    account_label: str
    currency: str
    status: str
    reason_codes: list[str]
    as_of: str | None
    last_successful_import: str | None
    ledger_balance: str | None
    snapshot_balance: str | None
    difference: str | None
    freshness_status: FreshnessStatus
    data_quality_status: FreshnessStatus


@dataclass(frozen=True)
class SnapshotMetadata:
    snapshot_kind: str
    as_of: str | None
    received_at: str | None
    source: str
    currency: str | None
    base_currency: str | None
    quote_currency: str | None
    value: str | None
    freshness_status: FreshnessStatus
    availability_status: FreshnessStatus
    issue_codes: list[str]
    is_estimated: bool


def _rows(conn: Connection, query: str, params: tuple[object, ...] = ()):  # sqlite Row typing is intentionally loose
    return conn.execute(query, params).fetchall()


def _money_text(value: Decimal) -> str:
    """Keep the stored Decimal precision; never hide a real sub-unit difference."""
    whole, dot, fractional = format(value, "f").partition(".")
    if not dot:
        return f"{whole}.00"
    return f"{whole}.{fractional.ljust(2, '0')}"


def _safe_account_id(value: str) -> str:
    return "account-" + hashlib.sha256(value.encode("utf-8")).hexdigest()[:12]


def _safe_account_label(value: str) -> str:
    """Preserve ordinary friendly names; remove identifier and filesystem-shaped labels."""
    sensitive = (
        r"\b[A-Z]{2}\s?\d{2}(?:\s?[A-Z0-9]){11,30}\b"  # compact or spaced IBAN
        r"|\b\d{10,}\b"  # long account number
        r"|\b0x[a-f0-9]{20,}\b"  # Ethereum / hex wallet
        r"|\bbc1[ac-hj-np-z02-9]{11,}\b"  # Bitcoin Bech32
        r"|\b[13][a-km-zA-HJ-NP-Z1-9]{25,34}\b"  # Bitcoin Base58
        r"|\b[A-Za-z0-9_-]{32,}\b"  # typical long wallet/token identifier
        r"|(?:^|\s)[~.]?[/\\\\]"  # POSIX/Windows path
        r"|\.(?:csv|xlsx|sqlite|db|json|txt)\b"
    )
    return "Konto (redigiert)" if re.search(sensitive, value, flags=re.IGNORECASE) else value


def _safe_source(value: str | None) -> str:
    allowed = {"manual", "manual_total_value", "csv_import", "system_calculated", "frankfurter", "twelvedata", "mock", "synthetic_fx"}
    return value if value in allowed else "local"


def _latest_cash_snapshot(conn: Connection, account_id: str, snapshot_types: tuple[str, ...]):
    placeholders = ",".join("?" for _ in snapshot_types)
    return conn.execute(
        f"""
        SELECT snapshot_type, balance_date, amount_original, currency, created_at, source
        FROM cash_account_snapshots
        WHERE account_id=? AND snapshot_type IN ({placeholders})
        ORDER BY balance_date DESC, created_at DESC
        LIMIT 1
        """,
        (account_id, *snapshot_types),
    ).fetchone()


def _ledger_value(conn: Connection, account_id: str):
    anchor = _latest_cash_snapshot(conn, account_id, ("csv_anchor_balance",))
    if anchor is None:
        return None, None, None, None
    movement = conn.execute(
        """
        SELECT net_amount_original, currency_original, trade_date
        FROM transactions
        WHERE account_id=? AND COALESCE(is_voided, 0)=0 AND COALESCE(is_confirmed, 1)=1
          AND source_type LIKE '%csv%' AND trade_date > ?
        ORDER BY trade_date
        """,
        (account_id, anchor["balance_date"]),
    ).fetchall()
    currency = str(anchor["currency"] or "").upper()
    if any(str(row["currency_original"] or "").upper() != currency for row in movement):
        return None, None, currency, "ledger_currency_mismatch"
    value = Decimal(str(anchor["amount_original"]))
    for row in movement:
        if row["net_amount_original"] in (None, ""):
            return None, None, currency, "ledger_amount_missing"
        value += Decimal(str(row["net_amount_original"]))
    latest_booking = movement[-1]["trade_date"] if movement else anchor["balance_date"]
    return value, latest_booking, currency, None


def _last_successful_import(conn: Connection, account_id: str) -> str | None:
    row = conn.execute(
        """SELECT MAX(created_at) AS received_at FROM transactions
           WHERE account_id=? AND COALESCE(is_voided, 0)=0 AND source_type LIKE '%csv%'""",
        (account_id,),
    ).fetchone()
    return row["received_at"] if row else None


def list_reconciliation_records(conn: Connection, *, now: datetime | None = None) -> list[ReconciliationRecord]:
    accounts = _rows(
        conn,
        """SELECT account_id, account_name, currency FROM accounts
           WHERE is_active=1 AND account_type='cash' ORDER BY account_name""",
    )
    records: list[ReconciliationRecord] = []
    for account in accounts:
        account_id = account["account_id"]
        currency = str(account["currency"] or "").upper()
        ledger, ledger_as_of, ledger_currency, ledger_issue = _ledger_value(conn, account_id)
        snapshot = _latest_cash_snapshot(conn, account_id, ("manual_balance", "reconciliation"))
        snapshot_value = Decimal(str(snapshot["amount_original"])) if snapshot else None
        snapshot_as_of = snapshot["balance_date"] if snapshot else None
        snapshot_currency = str(snapshot["currency"] or "").upper() if snapshot else None
        last_import = _last_successful_import(conn, account_id)
        value_statuses: list[FreshnessStatus] = [
            freshness_status(available=ledger is not None, as_of=ledger_as_of, now=now),
            freshness_status(available=snapshot_value is not None, as_of=snapshot_as_of, now=now),
        ]
        quality = combined_freshness(value_statuses)
        reason_codes: list[str] = []
        difference: Decimal | None = None
        if ledger is None and snapshot_value is None:
            status = "unavailable"
            reason_codes.append(ledger_issue or "no_comparable_balance")
        elif ledger is None or snapshot_value is None:
            status = "partial"
            reason_codes.append("ledger_balance_missing" if ledger is None else "comparison_snapshot_missing")
            if ledger_issue:
                reason_codes.append(ledger_issue)
        elif not ledger_currency or not snapshot_currency or ledger_currency != snapshot_currency or ledger_currency != currency:
            status = "not_comparable"
            reason_codes.append("currency_mismatch")
        elif ledger_as_of != snapshot_as_of:
            status = "not_comparable"
            reason_codes.append("as_of_mismatch")
        elif quality == "unknown":
            # A missing business date prevents comparison; staleness does not.
            status = "not_comparable"
            reason_codes.append("source_as_of_unknown")
        else:
            difference = ledger - snapshot_value
            if difference == Decimal("0"):
                status = "reconciled"
            else:
                status = "difference"
                reason_codes.append("balance_difference")
        records.append(
            ReconciliationRecord(
                account_id=_safe_account_id(account_id),
                account_label=_safe_account_label(account["account_name"]),
                currency=currency,
                status=status,
                reason_codes=reason_codes,
                as_of=ledger_as_of if ledger_as_of == snapshot_as_of else None,
                last_successful_import=last_import,
                ledger_balance=_money_text(ledger) if ledger is not None else None,
                snapshot_balance=_money_text(snapshot_value) if snapshot_value is not None else None,
                difference=_money_text(difference) if difference is not None else None,
                freshness_status=quality,
                data_quality_status=quality,
            )
        )
    return records


def list_snapshot_metadata(conn: Connection, *, now: datetime | None = None) -> list[SnapshotMetadata]:
    """Return only each logical series' newest stored metadata, in stable bounded order."""
    snapshots: list[SnapshotMetadata] = []
    valuations = _rows(
        conn,
        """SELECT account_id, valuation_date, created_at, source_type, currency, total_value_chf, quality_status
           FROM account_value_snapshots
           WHERE COALESCE(is_active,1)=1
           ORDER BY account_id, valuation_date DESC,
                    CASE source_type WHEN 'truewealth_official_import' THEN 3 WHEN 'truewealth_manual_provisional' THEN 2 ELSE 1 END DESC,
                    COALESCE(valuation_at,created_at) DESC, created_at DESC, snapshot_id DESC""",
    )
    latest_accounts: set[str] = set()
    for row in valuations:
        account_key = str(row["account_id"] or "unknown")
        if account_key in latest_accounts:
            continue
        latest_accounts.add(account_key)
        status = freshness_status(available=True, as_of=row["valuation_date"], now=now)
        issues = [] if status == "fresh" else ["valuation_as_of_stale" if status == "stale" else "valuation_as_of_unknown"]
        snapshots.append(SnapshotMetadata("account_valuation", row["valuation_date"], row["created_at"], _safe_source(row["source_type"]), row["currency"], None, None, _money_text(Decimal(str(row["total_value_chf"]))), status, status, issues, str(row["quality_status"] or "").lower() in {"estimated", "estimate"}))

    fx_rows = _rows(
        conn,
        """SELECT base_currency, quote_currency, rate_date, rate_timestamp, created_at, provider, rate_type, rate, quality_status
           FROM fx_rates
           ORDER BY base_currency, quote_currency, provider, rate_type,
                    COALESCE(rate_timestamp, rate_date) DESC, created_at DESC, fx_rate_id DESC""",
    )
    latest_fx: set[tuple[str, str, str, str]] = set()
    for row in fx_rows:
        key = (str(row["base_currency"] or ""), str(row["quote_currency"] or ""), str(row["provider"] or ""), str(row["rate_type"] or ""))
        if key in latest_fx:
            continue
        latest_fx.add(key)
        as_of = row["rate_timestamp"] or row["rate_date"]
        available = row["rate"] not in (None, "")
        status = freshness_status(available=available, as_of=as_of, now=now)
        issues = [] if status == "fresh" else ["fx_rate_missing" if not available else "fx_as_of_stale" if status == "stale" else "fx_as_of_unknown"]
        snapshots.append(SnapshotMetadata("fx_rate", as_of, row["created_at"], _safe_source(row["provider"]), None, row["base_currency"], row["quote_currency"], _money_text(Decimal(str(row["rate"]))) if available else None, status, status, issues, False))

    # UI and API remain bounded even if a local history is unexpectedly large.
    return snapshots[:200]


def build_reconciliation_snapshot(conn: Connection, *, now: datetime | None = None) -> dict[str, object]:
    reference = now or datetime.now(UTC)
    reconciliations = list_reconciliation_records(conn, now=reference)
    snapshots = list_snapshot_metadata(conn, now=reference)
    statuses = [item.data_quality_status for item in reconciliations] + [item.availability_status for item in snapshots]
    return {
        "reconciliations": [asdict(item) for item in reconciliations],
        "snapshots": [asdict(item) for item in snapshots],
        "data_quality_status": combined_freshness(statuses),
    }