from __future__ import annotations

import json
from datetime import UTC, datetime
from decimal import Decimal, InvalidOperation
from sqlite3 import Connection, Row
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 _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
        LIMIT 1
        """,
        (account_id, snapshot_type),
    ).fetchone()


def _cash_movements_after_anchor(conn: Connection, account_id: str, anchor_date: str | None) -> Decimal:
    if not anchor_date:
        return Decimal("0")
    row = conn.execute(
        """
        SELECT COALESCE(SUM(CAST(net_amount_chf AS TEXT)), '0') AS movement
        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 > ?
        """,
        (account_id, anchor_date),
    ).fetchone()
    return Decimal(str(row["movement"] or "0"))


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")
        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 manual or anchor
            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 manual or anchor:
                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 (manual or anchor):
                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
            )
            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=(
                        manual["balance_date"]
                        if manual 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")
    snapshot_id = _uuid("cashsnap")
    now = _now()
    source = "manual" if request.snapshot_type in {"manual_balance", "reconciliation"} else "csv_import" if request.snapshot_type == "csv_anchor_balance" else "system_calculated"
    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)
        VALUES (?, ?, ?, ?, ?, 'CHF', ?, ?, ?, ?, 'user', ?)
        """,
        (snapshot_id, request.account_id, request.snapshot_type, request.balance_date, decimal_text(amount, 2), decimal_text(amount, 2), source, request.note, now, audit_id),
    )
    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)])