from __future__ import annotations

import hashlib
import json
from dataclasses import dataclass
from datetime import date, datetime
from decimal import Decimal
from pathlib import Path
from sqlite3 import Connection
from typing import Any
from zoneinfo import ZoneInfo

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.imports.common import stable_id, utc_now
from jarvis_finance.market.providers import MarketDataProvider, refresh_crypto_prices
from jarvis_finance.services.portfolio_analytics import _exclusive_lock

SOURCE_KEY = "daily_crypto_valuation_v1"
ROLE = "crypto_portfolio"
ZERO = Decimal("0")


@dataclass(frozen=True)
class CryptoValuationRunResult:
    run_id: str
    status: str
    as_of: str
    price_total: int
    price_stored: int
    valuation_stored: int
    reason_codes: tuple[str, ...]
    idempotent: bool = False


def _decimal(value: object) -> Decimal:
    return Decimal(str(value or "0"))


def _fmt(value: Decimal) -> str:
    return format(value, "f")


def _result(row: Any, *, idempotent: bool) -> CryptoValuationRunResult:
    return CryptoValuationRunResult(
        run_id=str(row["run_id"]),
        status=str(row["status"]),
        as_of=str(row["as_of"]),
        price_total=int(row["price_total"]),
        price_stored=int(row["price_stored"]),
        valuation_stored=int(row["valuation_stored"]),
        reason_codes=tuple(json.loads(row["reason_codes_json"] or "[]")),
        idempotent=idempotent,
    )


def _scope_contract(conn: Connection) -> tuple[str | None, dict[str, str] | None, list[str]]:
    rows = conn.execute(
        """SELECT psc.account_id,pc.coverage_from,pc.coverage_to,pc.status,pc.source
           FROM performance_scope_classifications psc
           LEFT JOIN performance_cashflow_coverage pc ON pc.account_id=psc.account_id
           WHERE psc.included=1 AND psc.classification_role=?
             AND psc.decision_version='investment_performance_scope_v1'
           ORDER BY psc.account_id""",
        (ROLE,),
    ).fetchall()
    if not rows:
        return None, None, ["crypto_activation_required"]
    if len(rows) != 1:
        return None, None, ["crypto_scope_ambiguous"]
    row = rows[0]
    if not row["coverage_from"] or row["status"] != "complete":
        return str(row["account_id"]), None, ["crypto_history_confirmation_required"]
    return str(row["account_id"]), dict(row), []


def _quantities(
    conn: Connection,
    *,
    as_of: str,
    coverage: dict[str, str],
) -> tuple[dict[str, Decimal], list[str]]:
    start = str(coverage["coverage_from"])
    source = str(coverage["source"] or "")
    quantities: dict[str, Decimal] = {}
    reasons: list[str] = []
    baseline_rows = conn.execute(
        """SELECT asset_id,quantity,verification_status,
                  COALESCE(substr(last_verified_at,1,10),legacy_snapshot_date) AS verified_date
           FROM crypto_holdings ORDER BY asset_id,wallet_id"""
    ).fetchall()
    if "snapshot" in source:
        if not baseline_rows:
            reasons.append("confirmed_crypto_start_balance_missing")
        for row in baseline_rows:
            if row["verification_status"] != "verified" or row["verified_date"] != start:
                reasons.append("confirmed_crypto_start_balance_missing")
                continue
            quantities[str(row["asset_id"])] = quantities.get(str(row["asset_id"]), ZERO) + _decimal(row["quantity"])
    elif "transaction" not in source:
        reasons.append("crypto_tracking_mode_unconfirmed")

    comparator = ">" if "snapshot" in source else ">="
    for row in conn.execute(
        f"""SELECT asset_id,transaction_type,quantity,fee_quantity,from_wallet_id,to_wallet_id
           FROM crypto_transactions
           WHERE confirmation_status='confirmed'
             AND substr(transaction_datetime,1,10){comparator}?
             AND substr(transaction_datetime,1,10)<=?
           ORDER BY transaction_datetime,created_at,crypto_transaction_id""",
        (start, as_of),
    ).fetchall():
        asset_id = str(row["asset_id"])
        quantity = _decimal(row["quantity"])
        fee = _decimal(row["fee_quantity"])
        kind = str(row["transaction_type"])
        delta = ZERO
        if kind == "buy":
            delta = quantity - fee
        elif kind == "sell":
            delta = -(quantity + fee)
        elif kind == "fee":
            delta = -fee
        elif kind == "manual_adjustment":
            delta = quantity if row["to_wallet_id"] else -quantity
        elif kind == "transfer":
            delta = -fee
        else:
            reasons.append("unsupported_crypto_activity")
            continue
        quantities[asset_id] = quantities.get(asset_id, ZERO) + delta
    if any(value < ZERO for value in quantities.values()):
        reasons.append("negative_crypto_position")
    return {asset_id: value for asset_id, value in quantities.items() if value != ZERO}, sorted(set(reasons))


def _exact_prices(conn: Connection, *, as_of: str, asset_ids: list[str]) -> tuple[dict[str, dict[str, str]], list[str]]:
    prices: dict[str, dict[str, str]] = {}
    missing: list[str] = []
    for asset_id in asset_ids:
        row = conn.execute(
            """SELECT crypto_price_id,price,provider,provider_timestamp,fetched_at
               FROM crypto_prices
               WHERE asset_id=? AND price_currency='CHF' AND quality_status='fresh'
                 AND substr(COALESCE(provider_timestamp,fetched_at),1,10)=?
                 AND CAST(price AS NUMERIC)>0
               ORDER BY COALESCE(provider_timestamp,fetched_at) DESC,fetched_at DESC,crypto_price_id DESC
               LIMIT 1""",
            (asset_id, as_of),
        ).fetchone()
        if not row:
            missing.append(asset_id)
            continue
        prices[asset_id] = {
            "price_id": str(row["crypto_price_id"]),
            "price": str(row["price"]),
            "provider": str(row["provider"]),
            "provider_timestamp": str(row["provider_timestamp"] or row["fetched_at"]),
        }
    return prices, missing


def run_daily_crypto_valuation(
    conn: Connection,
    *,
    as_of: str | None = None,
    price_provider: MarketDataProvider | None = None,
    lock_path: Path | None = None,
) -> CryptoValuationRunResult:
    """Materialize at most one active crypto account valuation for a calendar day.

    A missing/failed provider input never creates a new valuation. Historical runs only
    consume exact-date, already persisted prices; they never substitute today's quote.
    """

    requested = date.fromisoformat(as_of) if as_of else datetime.now(ZoneInfo("Europe/Zurich")).date()
    day = requested.isoformat()
    lock = lock_path or Path("/tmp/jarvis-finance-crypto-valuation.lock")
    with _exclusive_lock(lock):
        account_id, coverage, reasons = _scope_contract(conn)
        price_stored = 0
        if price_provider is not None and requested == datetime.now(ZoneInfo("Europe/Zurich")).date():
            refreshed = refresh_crypto_prices(
                conn,
                provider=price_provider,
                currency="CHF",
                max_age_seconds=0,
                dry_run=False,
            )
            price_stored = refreshed.updated_count
            if refreshed.error_count or refreshed.missing_local_price_count:
                reasons.append("crypto_provider_incomplete")

        quantities: dict[str, Decimal] = {}
        if account_id and coverage:
            if day < str(coverage["coverage_from"]) or day > str(coverage["coverage_to"]):
                reasons.append("crypto_day_outside_confirmed_coverage")
            else:
                quantities, quantity_reasons = _quantities(conn, as_of=day, coverage=coverage)
                reasons.extend(quantity_reasons)
        prices, missing_assets = _exact_prices(conn, as_of=day, asset_ids=sorted(quantities))
        if missing_assets:
            reasons.append("crypto_exact_date_price_missing")
        if not quantities:
            reasons.append("confirmed_crypto_positions_missing")
        payload = {
            "account_id": account_id,
            "as_of": day,
            "coverage": coverage,
            "quantities": {key: _fmt(value) for key, value in sorted(quantities.items())},
            "prices": prices,
        }
        fingerprint = hashlib.sha256(
            json.dumps(payload, sort_keys=True, separators=(",", ":")).encode()
        ).hexdigest()
        existing = conn.execute(
            "SELECT * FROM market_data_runs WHERE source_key=? AND as_of=? AND input_fingerprint=?",
            (SOURCE_KEY, day, fingerprint),
        ).fetchone()
        if existing and existing["status"] == "complete":
            return _result(existing, idempotent=True)

        now = utc_now()
        run_id = stable_id("market-run", SOURCE_KEY, day, fingerprint)
        if not existing:
            conn.execute(
                """INSERT INTO market_data_runs(
                       run_id,source_key,as_of,input_fingerprint,status,started_at
                   ) VALUES(?,?,?,?,?,?)""",
                (run_id, SOURCE_KEY, day, fingerprint, "running", now),
            )
            conn.commit()
        else:
            run_id = str(existing["run_id"])

        unique_reasons = sorted(set(reasons))
        valuation_stored = 0
        complete = bool(account_id and quantities and not missing_assets and not unique_reasons)
        if complete:
            total = sum((quantity * _decimal(prices[asset_id]["price"]) for asset_id, quantity in quantities.items()), ZERO)
            latest = conn.execute(
                """SELECT snapshot_id
                   FROM portfolio_valuation_snapshots
                   WHERE scope_kind='account' AND scope_id=? AND substr(valuation_at,1,10)=?
                   ORDER BY snapshot_version DESC,captured_at DESC,snapshot_id DESC LIMIT 1""",
                (account_id, day),
            ).fetchone()
            version = int(
                conn.execute(
                    """SELECT COALESCE(MAX(snapshot_version),0)+1
                       FROM portfolio_valuation_snapshots
                       WHERE scope_kind='account' AND scope_id=? AND substr(valuation_at,1,10)=?""",
                    (account_id, day),
                ).fetchone()[0]
            )
            conn.execute(
                """INSERT INTO portfolio_valuation_snapshots(
                       snapshot_id,scope_kind,scope_id,account_id,value_original,currency,
                       base_currency,fx_rate_to_base,fx_direction,valuation_at,source,
                       captured_at,snapshot_version,supersedes_snapshot_id,source_reference,quality_status,reason_codes_json
                   ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                (
                    stable_id("crypto-account-valuation", run_id, account_id),
                    "account",
                    account_id,
                    account_id,
                    _fmt(total),
                    "CHF",
                    "CHF",
                    "1",
                    "original_to_base",
                    day,
                    SOURCE_KEY,
                    now,
                    version,
                    str(latest["snapshot_id"]) if latest else None,
                    f"{run_id}:{fingerprint}",
                    "complete",
                    "[]",
                ),
            )
            valuation_stored = 1

        status = "complete" if complete else "partial"
        audit_id = record_audit_event(
            conn,
            source=SOURCE_KEY,
            action="daily_crypto_valuation_completed",
            entity_type="market_data_run",
            entity_id=run_id,
            old_values={},
            new_values={
                "as_of": day,
                "status": status,
                "price_inputs": len(prices),
                "price_rows_written": price_stored,
                "valuation_rows_written": valuation_stored,
                "reason_codes": unique_reasons,
                "input_fingerprint": fingerprint,
            },
            created_by="system",
        )
        conn.execute(
            """UPDATE market_data_runs SET status=?,completed_at=?,price_total=?,price_stored=?,
                      fx_total=0,fx_stored=0,benchmark_total=0,benchmark_stored=0,
                      valuation_stored=?,missing_instruments_json=?,reason_codes_json=?,audit_id=?
               WHERE run_id=?""",
            (
                status,
                utc_now(),
                len(quantities),
                price_stored,
                valuation_stored,
                json.dumps([{"asset_id": item, "reason_code": "crypto_exact_date_price_missing"} for item in missing_assets]),
                json.dumps(unique_reasons),
                audit_id,
                run_id,
            ),
        )
        conn.commit()
        row = conn.execute("SELECT * FROM market_data_runs WHERE run_id=?", (run_id,)).fetchone()
        return _result(row, idempotent=False)