from __future__ import annotations

import json
from datetime import UTC, datetime
from decimal import Decimal, InvalidOperation
from hashlib import sha256
from sqlite3 import Connection, Row
from typing import Any
from uuid import uuid4

from fastapi import HTTPException

from jarvis_finance.api.schemas.overview import CashSummary
from jarvis_finance.api.schemas.positions import (
    ActionItem,
    CashPosition,
    CashPositionDetail,
    CashSnapshot,
    CashSnapshotConfirmRequest,
    CashSnapshotPreviewRequest,
    ConfirmResponse,
    PreviewResponse,
)
from jarvis_finance.ledger.cash import _signed_cash_delta, calculate_cash_balances
from jarvis_finance.services.api_helpers import decimal_text
from jarvis_finance.services.portfolio_aggregation import latest_official_postfinance_cash

CANONICAL_CASH_ACCOUNTS = [
    {"platform": "Raiffeisen", "account_name": "Raiffeisen", "balance_mode": "csv_calculated"},
    {"platform": "PostFinance Cash", "account_name": "PostFinance Cash-Konto", "balance_mode": "manual"},
    {"platform": "AKB", "account_name": "AKB Zinsbereitstellungskonto", "balance_mode": "manual"},
    {"platform": "AKB", "account_name": "AKB gemeinsames Konto", "balance_mode": "manual"},
    {"platform": "AKB", "account_name": "AKB Aargauer-Sparkonto", "balance_mode": "manual"},
    {"platform": "AKB", "account_name": "AKB Haushaltskonto", "balance_mode": "csv_calculated"},
]


def _now() -> str:
    return datetime.now(UTC).replace(microsecond=0).isoformat().replace("+00:00", "Z")


def _uuid(prefix: str) -> str:
    return f"{prefix}_{uuid4().hex[:16]}"


def _snapshot_semantic_identity(
    *, account_id: str, snapshot_type: str, balance_date: str, amount_chf: str, source: str
) -> str:
    payload = json.dumps(
        [account_id, snapshot_type, balance_date, amount_chf, source],
        ensure_ascii=True,
        separators=(",", ":"),
    )
    return sha256(f"cash_snapshot_v1|{payload}".encode()).hexdigest()


def _decimal(value: str, field: str) -> Decimal:
    try:
        parsed = Decimal(str(value))
    except (InvalidOperation, ValueError) as exc:
        raise HTTPException(status_code=422, detail=f"Invalid decimal for {field}") from exc
    if parsed < 0:
        raise HTTPException(status_code=422, detail=f"{field} must be zero or positive")
    return parsed


def _audit(conn: Connection, *, action: str, entity_type: str, entity_id: str, payload: dict, note: str, created_by: str = "user") -> str:
    audit_id = _uuid("audit")
    ts = _now()
    conn.execute(
        """
        INSERT INTO audit_log(audit_id, timestamp, source, action, entity_type, entity_id, old_values_json, new_values_json, user_text_note, confirmed, confirmation_timestamp, created_by, quality_status, created_at)
        VALUES (?, ?, 'vue_dashboard', ?, ?, ?, NULL, ?, ?, 1, ?, ?, 'ok', ?)
        """,
        (audit_id, ts, action, entity_type, entity_id, json.dumps(payload, sort_keys=True), note, ts, created_by, ts),
    )
    return audit_id


def _ensure_platform(conn: Connection, name: str) -> str:
    row = conn.execute("SELECT platform_id FROM platforms WHERE lower(name)=lower(?)", (name,)).fetchone()
    if row:
        return row["platform_id"]
    platform_id = _uuid("plat")
    conn.execute(
        "INSERT INTO platforms(platform_id, name, platform_type, default_currency, created_at) VALUES (?, ?, 'bank', 'CHF', ?)",
        (platform_id, name, _now()),
    )
    return platform_id


def ensure_canonical_cash_accounts(conn: Connection, *, created_by: str = "user") -> dict:
    created: list[str] = []
    existing_names = [item["account_name"] for item in CANONICAL_CASH_ACCOUNTS]
    duplicates = [
        row["account_name"]
        for row in conn.execute(
            f"""
            SELECT account_name, COUNT(*) AS n
            FROM accounts
            WHERE account_type='cash' AND account_name IN ({','.join('?' for _ in existing_names)})
            GROUP BY account_name HAVING COUNT(*) > 1
            """,
            existing_names,
        ).fetchall()
    ]
    for item in CANONICAL_CASH_ACCOUNTS:
        rows = conn.execute("SELECT account_id FROM accounts WHERE account_type='cash' AND account_name=?", (item["account_name"],)).fetchall()
        if len(rows) > 1:
            continue
        platform_id = _ensure_platform(conn, item["platform"])
        if rows:
            account_id = rows[0]["account_id"]
            conn.execute(
                "UPDATE accounts SET platform_id=?, currency='CHF', balance_mode=?, portfolio_bucket='cash', updated_at=? WHERE account_id=?",
                (platform_id, item["balance_mode"], _now(), account_id),
            )
        else:
            account_id = _uuid("acct")
            conn.execute(
                """
                INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, balance_mode, portfolio_bucket, performance_included, is_active, created_at)
                VALUES (?, ?, ?, 'cash', 'CHF', ?, 'cash', 0, 1, ?)
                """,
                (account_id, platform_id, item["account_name"], item["balance_mode"], _now()),
            )
            created.append(item["account_name"])
            _audit(conn, action="cash_account_seed_confirm", entity_type="cash_account", entity_id=account_id, payload=item, note="Canonical cash account ensured", created_by=created_by)
    conn.commit()
    return {"created": len(created), "created_accounts": created, "duplicates": duplicates}


def _latest_snapshot(conn: Connection, account_id: str, snapshot_type: str):
    return conn.execute(
        """
        SELECT * FROM cash_account_snapshots
        WHERE account_id=? AND snapshot_type=?
        ORDER BY balance_date DESC, created_at DESC, snapshot_id DESC
        LIMIT 1
        """,
        (account_id, snapshot_type),
    ).fetchone()


def _latest_effective_snapshot(conn: Connection, account_id: str):
    """Newest confirmed cash day; type precedence breaks same-day ties."""

    return conn.execute(
        """SELECT * FROM cash_account_snapshots
            WHERE account_id=? AND amount_chf IS NOT NULL
            ORDER BY balance_date DESC,
              CASE snapshot_type
                WHEN 'reconciliation' THEN 4
                WHEN 'manual_balance' THEN 3
                WHEN 'csv_anchor_balance' THEN 2
                WHEN 'calculated_balance' THEN 1
                ELSE 0 END DESC,
              created_at DESC,snapshot_id DESC LIMIT 1""",
        (account_id,),
    ).fetchone()


_BUDGET_PROJECTED_CANONICAL_SOURCES = {
    "akb_bank",
    "credit_card_csv",
    "csv_seed",
    "import_candidate",
    "raiffeisen_bank",
    "visa_credit_card",
}


def authoritative_cash_movements(
    conn: Connection,
    *,
    account_id: str,
    after: str | None,
    through: str | None = None,
) -> dict[str, Any]:
    """Return budget imports plus non-duplicated canonical adjustments.

    For linked household accounts, confirmed budget rows are authoritative for
    imported activity. Confirmed canonical adjustments that are not projected
    import sources remain additive (for example ``vue_manual_cash``). Accounts
    without a budget projection use the complete confirmed canonical ledger.
    """

    has_budget_projection = bool(
        conn.execute(
            """SELECT 1 FROM budget_accounts
                WHERE linked_account_id=? AND is_active=1 LIMIT 1""",
            (account_id,),
        ).fetchone()
    )
    rows: list[Row] = []
    if has_budget_projection:
        conditions = []
        params: list[Any] = [account_id]
        if after:
            conditions.append("bt.transaction_date>?")
            params.append(after)
        if through:
            conditions.append("bt.transaction_date<=?")
            params.append(through)
        suffix = "".join(f" AND {condition}" for condition in conditions)
        rows.extend(
            conn.execute(
                f"""SELECT bt.transaction_date day,CAST(bt.amount_chf AS NUMERIC) amount
                 FROM budget_transactions bt
                  JOIN budget_accounts ba ON ba.budget_account_id=bt.account_id
                 WHERE ba.linked_account_id=? AND ba.is_active=1
                    AND bt.status='confirmed' AND bt.amount_chf IS NOT NULL{suffix}
                  ORDER BY bt.transaction_date,bt.created_at,bt.budget_transaction_id""",
                tuple(params),
            ).fetchall()
        )

    canonical_conditions = []
    canonical_params: list[Any] = [account_id]
    if after:
        canonical_conditions.append("trade_date>?")
        canonical_params.append(after)
    if through:
        canonical_conditions.append("trade_date<=?")
        canonical_params.append(through)
    if has_budget_projection:
        placeholders = ",".join("?" for _ in _BUDGET_PROJECTED_CANONICAL_SOURCES)
        canonical_conditions.extend(
            [
                "LOWER(COALESCE(source_type,'')) NOT LIKE '%csv%'",
                f"LOWER(COALESCE(source_type,'')) NOT IN ({placeholders})",
            ]
        )
        canonical_params.extend(sorted(_BUDGET_PROJECTED_CANONICAL_SOURCES))
    canonical_suffix = "".join(
        f" AND {condition}" for condition in canonical_conditions
    )
    canonical_rows = conn.execute(
        f"""SELECT trade_date day,CAST(net_amount_chf AS NUMERIC) amount
              FROM transactions
             WHERE account_id=? AND COALESCE(is_voided,0)=0
               AND COALESCE(is_confirmed,1)=1 AND net_amount_chf IS NOT NULL
               {canonical_suffix}
             ORDER BY trade_date,created_at,transaction_id""",
        tuple(canonical_params),
    ).fetchall()
    rows.extend(canonical_rows)
    days = sorted({str(row["day"]) for row in rows})
    return {
        "amount": sum(
            (Decimal(str(row["amount"] or "0")) for row in rows), Decimal("0")
        ),
        "last_date": days[-1] if days else None,
        "days": days,
        "source": (
            "budget+canonical"
            if has_budget_projection and canonical_rows
            else "budget"
            if has_budget_projection
            else "canonical"
        ),
        "count": len(rows),
    }


def _cash_movements_after_anchor(
    conn: Connection, account_id: str, anchor_date: str | None
) -> Decimal:
    if not anchor_date:
        return Decimal("0")
    return authoritative_cash_movements(
        conn, account_id=account_id, after=anchor_date
    )["amount"]


def _calculated_balance(conn: Connection, account_id: str) -> Decimal:
    anchor = _latest_snapshot(conn, account_id, "csv_anchor_balance")
    if anchor:
        return Decimal(str(anchor["amount_chf"])) + _cash_movements_after_anchor(conn, account_id, anchor["balance_date"])
    rows = conn.execute(
        "SELECT amount_chf FROM cash_balances WHERE account_id=?",
        (account_id,),
    ).fetchall()
    if rows:
        return sum(
            (
                Decimal(str(row["amount_chf"]))
                for row in rows
                if row["amount_chf"] is not None
            ),
            Decimal("0"),
        )
    # Legacy/demo datasets can have a transaction ledger without materialized
    # cash_balances. Keep the shared read model compatible without overriding an
    # explicit snapshot projection.
    ledger = calculate_cash_balances(conn, persist_alerts=False)
    return sum(
        (
            balance.amount_chf
            for (ledger_account_id, _currency), balance in ledger.balances.items()
            if ledger_account_id == account_id and balance.amount_chf is not None
        ),
        Decimal("0"),
    )


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


def _post_snapshot_cash_deltas(
    conn: Connection,
    account_id: str,
    snapshot_date: str,
) -> dict[str, tuple[Decimal, Decimal | None]]:
    rows = conn.execute(
        """SELECT * FROM transactions
           WHERE account_id=? AND trade_date>?
             AND COALESCE(is_confirmed,0)=1 AND COALESCE(is_voided,0)=0
           ORDER BY trade_date,created_at,transaction_id""",
        (account_id, snapshot_date),
    ).fetchall()
    deltas: dict[str, tuple[Decimal, Decimal | None]] = {}

    def add_delta(
        currency: str,
        original: Decimal,
        amount_chf: Decimal | None,
    ) -> None:
        previous_original, previous_chf = deltas.get(
            currency,
            (Decimal("0"), Decimal("0")),
        )
        deltas[currency] = (
            previous_original + original,
            None
            if previous_chf is None or amount_chf is None
            else previous_chf + amount_chf,
        )

    direct_transaction_ids: set[str] = set()
    for row in rows:
        original, amount_chf = _signed_cash_delta(row)
        if original == 0 and amount_chf in {None, Decimal("0")}:
            continue
        direct_transaction_ids.add(str(row["transaction_id"]))
        add_delta(str(row["currency_original"]), original, amount_chf)

    # Existing equity workflows post the cash leg to the selected cash account,
    # while their security transaction remains on the depot account.
    postings = conn.execute(
        """SELECT currency,amount_original,amount_chf,source_type,created_at
             FROM cash_balances
            WHERE account_id=? AND balance_date>?
              AND (source_type LIKE 'vue_equity_sale:%'
                   OR source_type='vue_equity_dividend')
            ORDER BY balance_date,created_at,cash_balance_id""",
        (account_id, snapshot_date),
    ).fetchall()
    for posting in postings:
        source_type = str(posting["source_type"] or "")
        linked_transaction_id = (
            source_type.split(":", 1)[1]
            if source_type.startswith("vue_equity_sale:")
            else None
        )
        if linked_transaction_id in direct_transaction_ids:
            continue
        if source_type == "vue_equity_dividend":
            duplicate = conn.execute(
                """SELECT 1 FROM transactions
                    WHERE account_id=? AND source_type='vue_equity_dividend'
                      AND created_at=? AND currency_original=?
                      AND CAST(net_amount_original AS TEXT)=CAST(? AS TEXT)
                      AND COALESCE(is_confirmed,0)=1 AND COALESCE(is_voided,0)=0
                    LIMIT 1""",
                (
                    account_id,
                    posting["created_at"],
                    posting["currency"],
                    posting["amount_original"],
                ),
            ).fetchone()
            if duplicate:
                continue
        add_delta(
            str(posting["currency"]),
            Decimal(str(posting["amount_original"])),
            Decimal(str(posting["amount_chf"]))
            if posting["amount_chf"] is not None
            else None,
        )
    return deltas


def list_cash_positions(conn: Connection) -> list[CashPosition]:
    ledger = calculate_cash_balances(conn, persist_alerts=False)
    rows = conn.execute(
        """
        SELECT a.account_id, a.account_name, a.account_type, a.currency, COALESCE(a.balance_mode,'manual') AS balance_mode, p.name AS platform
        FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id
        WHERE a.is_active=1
          AND (
              a.account_type='cash'
              OR a.account_id IN (
                  SELECT account_id FROM postfinance_account_roles
                  WHERE role='etrading_cash'
              )
          )
        ORDER BY p.name, a.account_name
        """
    ).fetchall()
    positions: list[CashPosition] = []
    official_components = latest_official_postfinance_cash(conn)
    official_by_account: dict[str, list] = {}
    for component in official_components:
        official_by_account.setdefault(component.account_id, []).append(component)
    for row in rows:
        account_id = row["account_id"]
        if account_id in official_by_account:
            components = {
                component.currency: component
                for component in official_by_account[account_id]
            }
            snapshot_date = next(iter(components.values())).snapshot_date
            deltas = _post_snapshot_cash_deltas(conn, account_id, snapshot_date)
            for currency in sorted(set(components) | set(deltas)):
                component = components.get(currency)
                delta_original, delta_chf = deltas.get(
                    currency,
                    (Decimal("0"), Decimal("0")),
                )
                amount_original = (
                    component.amount_original if component else Decimal("0")
                ) + delta_original
                baseline_chf = component.amount_chf if component else Decimal("0")
                amount_chf = (
                    baseline_chf + delta_chf if delta_chf is not None else baseline_chf
                )
                status = (
                    "Abgleich offen"
                    if delta_chf is None
                    else "Offiziell abgeglichen + Bewegungen"
                    if delta_original or delta_chf
                    else "Offiziell abgeglichen"
                )
                positions.append(
                    CashPosition(
                        id=f"{account_id}:{currency}",
                        account_id=account_id,
                        platform=row["platform"],
                        account_label=row["account_name"],
                        account_type=row["account_type"],
                        currency=currency,
                        balance_mode="official_components",
                        amount=decimal_text(amount_original, 2),
                        amount_chf=decimal_text(amount_chf, 2),
                        calculated_balance_chf=decimal_text(amount_chf, 2),
                        manual_balance_chf=None,
                        csv_anchor_balance_chf=None,
                        used_value_chf=decimal_text(amount_chf, 2),
                        difference_chf="0.00",
                        last_manual_reconciliation=snapshot_date,
                        last_imported_booking=_last_imported_booking(conn, account_id),
                        status=status,
                    )
                )
            continue
        manual = _latest_snapshot(conn, account_id, "manual_balance")
        anchor = _latest_snapshot(conn, account_id, "csv_anchor_balance")
        effective_snapshot = _latest_effective_snapshot(conn, account_id)
        mode = row["balance_mode"] or "manual"
        default_currency = str(row["currency"])
        materialized_rows = conn.execute(
            """SELECT currency,amount_original,amount_chf,source_type
                 FROM cash_balances WHERE account_id=?
                 ORDER BY balance_date,created_at,cash_balance_id""",
            (account_id,),
        ).fetchall()
        materialized_by_currency: dict[str, list[Row]] = {}
        for balance_row in materialized_rows:
            materialized_by_currency.setdefault(
                str(balance_row["currency"]), []
            ).append(balance_row)
        ledger_currencies = {
            currency
            for ledger_account_id, currency in ledger.balances
            if ledger_account_id == account_id
        }
        # Manual balances and CSV anchors are account-level CHF controls. They must
        # not be added to per-currency ledger rows from the same account.
        currencies = (
            {default_currency}
            if effective_snapshot
            else set(ledger_currencies) | set(materialized_by_currency)
            or {default_currency}
        )
        for currency in sorted(currencies):
            ledger_balance = ledger.balances.get((account_id, currency))
            currency_rows = materialized_by_currency.get(currency, [])
            # Equity actions write their cash leg only to cash_balances. Other
            # materialized rows are used only when no same-currency ledger exists,
            # avoiding duplicate vue_manual_cash transaction/materialization pairs.
            additional_rows = (
                [
                    balance_row
                    for balance_row in currency_rows
                    if str(balance_row["source_type"] or "").startswith(
                        ("vue_equity_sale:", "vue_equity_dividend")
                    )
                ]
                if ledger_balance is not None
                else currency_rows
            )
            if effective_snapshot:
                calculated = _calculated_balance(conn, account_id)
                amount_original = (
                    calculated if currency.upper() == "CHF" else Decimal("0")
                )
            elif ledger_balance is not None:
                calculated = ledger_balance.amount_chf or Decimal("0")
                amount_original = ledger_balance.amount_original
            else:
                calculated = Decimal("0")
                amount_original = Decimal("0")
            if not effective_snapshot:
                amount_original += sum(
                    (
                        Decimal(str(balance_row["amount_original"]))
                        for balance_row in additional_rows
                    ),
                    Decimal("0"),
                )
                calculated += sum(
                    (
                        Decimal(str(balance_row["amount_chf"]))
                        for balance_row in additional_rows
                        if balance_row["amount_chf"] is not None
                    ),
                    Decimal("0"),
                )
            manual_amount = (
                Decimal(str(manual["amount_chf"]))
                if manual and currency == default_currency
                else None
            )
            if effective_snapshot:
                effective_value = Decimal(str(effective_snapshot["amount_chf"]))
                effective_value += _cash_movements_after_anchor(
                    conn, account_id, str(effective_snapshot["balance_date"])
                )
                used = effective_value
            else:
                used = (
                    manual_amount
                    if mode == "manual" and manual_amount is not None
                    else calculated
                )
            difference = (
                manual_amount - calculated
                if manual_amount is not None
                else Decimal("0")
            )
            missing_fx = (
                ledger_balance is not None and ledger_balance.amount_chf is None
            ) or any(
                balance_row["amount_chf"] is None for balance_row in additional_rows
            )
            status = (
                "Abgleich offen"
                if missing_fx or manual_amount is not None and difference != 0
                else "Aktuell"
            )
            positions.append(
                CashPosition(
                    id=f"{account_id}:{currency}",
                    account_id=account_id,
                    platform=row["platform"],
                    account_label=row["account_name"],
                    account_type=row["account_type"],
                    currency=currency,
                    balance_mode=mode,
                    amount=decimal_text(amount_original, 2),
                    amount_chf=decimal_text(used, 2),
                    calculated_balance_chf=decimal_text(calculated, 2),
                    manual_balance_chf=(
                        decimal_text(manual_amount, 2)
                        if manual_amount is not None
                        else None
                    ),
                    csv_anchor_balance_chf=(
                        decimal_text(anchor["amount_chf"], 2)
                        if anchor and currency == default_currency
                        else None
                    ),
                    used_value_chf=decimal_text(used, 2),
                    difference_chf=decimal_text(difference, 2),
                    last_manual_reconciliation=(
                        effective_snapshot["balance_date"]
                        if effective_snapshot and currency == default_currency
                        else None
                    ),
                    last_imported_booking=_last_imported_booking(conn, account_id),
                    status=status,
                )
            )
    return positions


def get_cash_summary(conn: Connection) -> CashSummary:
    # Read paths must never seed or refresh accounts; setup is an explicit Confirm action.
    positions = list_cash_positions(conn)
    total = sum(Decimal(p.used_value_chf) for p in positions)
    last_dates = [p.last_manual_reconciliation for p in positions if p.last_manual_reconciliation]
    return CashSummary(
        base_currency="CHF",
        cash_chf=decimal_text(total, 2),
        manual_accounts_count=sum(1 for p in positions if p.balance_mode == "manual"),
        csv_calculated_accounts_count=sum(1 for p in positions if p.balance_mode == "csv_calculated"),
        open_reconciliation_count=sum(1 for p in positions if p.status == "Abgleich offen"),
        last_reconciliation_at=max(last_dates) if last_dates else None,
        positions=positions,
    )


def preview_cash_snapshot(conn: Connection, request: CashSnapshotPreviewRequest) -> PreviewResponse:
    row = conn.execute("SELECT account_id, account_type FROM accounts WHERE account_id=?", (request.account_id,)).fetchone()
    if not row or row["account_type"] != "cash":
        raise HTTPException(status_code=404, detail="Cash account not found")
    if request.snapshot_type not in {"manual_balance", "csv_anchor_balance", "calculated_balance", "reconciliation"}:
        raise HTTPException(status_code=422, detail="Invalid snapshot_type")
    amount = _decimal(request.amount_chf, "amount_chf")
    return PreviewResponse(preview_id=_uuid("preview"), asset_class="cash", summary=f"{request.snapshot_type} CHF per {request.balance_date}", amount_chf=decimal_text(amount, 2), fx_status="not_needed", warnings=[])


def confirm_cash_snapshot(conn: Connection, request: CashSnapshotConfirmRequest) -> ConfirmResponse:
    if not request.confirm:
        raise HTTPException(status_code=400, detail="Explicit confirm required")
    preview_cash_snapshot(conn, request)
    amount = _decimal(request.amount_chf, "amount_chf")
    source = "manual" if request.snapshot_type in {"manual_balance", "reconciliation"} else "csv_import" if request.snapshot_type == "csv_anchor_balance" else "system_calculated"
    amount_text = decimal_text(amount, 2)
    semantic_identity = _snapshot_semantic_identity(
        account_id=request.account_id,
        snapshot_type=request.snapshot_type,
        balance_date=request.balance_date,
        amount_chf=amount_text,
        source=source,
    )
    existing = conn.execute(
        """SELECT snapshot_id,audit_id FROM cash_account_snapshots
           WHERE semantic_identity=? OR (
             semantic_identity IS NULL AND account_id=? AND snapshot_type=?
             AND balance_date=? AND amount_chf=? AND source=?
           )
           ORDER BY created_at,snapshot_id LIMIT 1""",
        (
            semantic_identity,
            request.account_id,
            request.snapshot_type,
            request.balance_date,
            amount_text,
            source,
        ),
    ).fetchone()
    if existing:
        return ConfirmResponse(
            status="confirmed",
            entity_id=existing["snapshot_id"],
            audit_id=existing["audit_id"],
            message="Identischer Kontostand bereits bestätigt · keine neue Zeile",
        )
    snapshot_id = _uuid("cashsnap")
    now = _now()
    audit_id = _audit(conn, action=f"cash_{request.snapshot_type}_confirm", entity_type="cash_account", entity_id=request.account_id, payload=request.model_dump(), note=request.note)
    conn.execute(
        """
        INSERT INTO cash_account_snapshots(
            snapshot_id, account_id, snapshot_type, balance_date, amount_original,
            currency, amount_chf, source, note, created_at, created_by, audit_id,
            semantic_identity
        )
        VALUES (?, ?, ?, ?, ?, 'CHF', ?, ?, ?, ?, 'user', ?, ?)
        """,
        (
            snapshot_id,
            request.account_id,
            request.snapshot_type,
            request.balance_date,
            amount_text,
            amount_text,
            source,
            request.note,
            now,
            audit_id,
            semantic_identity,
        ),
    )
    conn.commit()
    return ConfirmResponse(status="confirmed", entity_id=snapshot_id, audit_id=audit_id, message="Kontostand gespeichert · Audit geschrieben")


def get_cash_position_detail(conn: Connection, position_id: str) -> CashPositionDetail:
    position = next((p for p in get_cash_summary(conn).positions if p.id == position_id), None)
    if not position:
        raise HTTPException(status_code=404, detail="Cash position not found")
    account_id = position.account_id
    rows = conn.execute(
        """
        SELECT snapshot_id, snapshot_type, balance_date, amount_chf, source, COALESCE(note,'') AS note, audit_id
        FROM cash_account_snapshots
        WHERE account_id=?
        ORDER BY balance_date DESC, created_at DESC
        LIMIT 30
        """,
        (account_id,),
    ).fetchall()
    history: list[CashSnapshot] = []
    previous: Decimal | None = None
    for row in reversed(rows):
        current = Decimal(str(row["amount_chf"]))
        diff = None if previous is None else decimal_text(current - previous, 2)
        previous = current
        history.append(CashSnapshot(snapshot_id=row["snapshot_id"], snapshot_type=row["snapshot_type"], balance_date=row["balance_date"], amount_chf=decimal_text(current, 2), source=row["source"], note=row["note"], difference_to_previous_chf=diff, audit_hint="Audit vorhanden" if row["audit_id"] else None))
    history = list(reversed(history))
    return CashPositionDetail(**position.model_dump(), last_change=position.last_manual_reconciliation, history=history, available_actions=[ActionItem(label="Einzahlung", enabled=True), ActionItem(label="Auszahlung", enabled=True), ActionItem(label="Kontostand aktualisieren", enabled=True), ActionItem(label="CSV-Anfangsstand setzen", enabled=True), ActionItem(label="Abgleich bestätigen", enabled=True), ActionItem(label="Verlauf anzeigen", enabled=True)])