from __future__ import annotations

import sqlite3
from sqlite3 import Connection

from jarvis_finance.api.main import create_app
from jarvis_finance.api.schemas.positions import CashSnapshotConfirmRequest
from jarvis_finance.services.cash_service import ensure_canonical_cash_accounts, confirm_cash_snapshot
from jarvis_finance.services.portfolio_advisor import get_portfolio_advisor_snapshot
from jarvis_finance.storage.migrations import apply_migrations


def _connect() -> Connection:
    conn = sqlite3.connect(":memory:", check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def _seed_portfolio(conn: Connection) -> None:
    now = "2026-06-30T10:00:00Z"
    fresh_price_time = "2099-06-30T10:00:00Z"
    ensure_canonical_cash_accounts(conn)
    cash_id = conn.execute("SELECT account_id FROM accounts WHERE account_name='PostFinance Cash-Konto'").fetchone()[0]
    confirm_cash_snapshot(conn, CashSnapshotConfirmRequest(account_id=cash_id, snapshot_type="manual_balance", balance_date="2026-06-30", amount_chf="10000.00", note="advisor fixture", preview_id="p", confirm=True))

    conn.execute("INSERT INTO platforms(platform_id, name, platform_type, default_currency, created_at) VALUES ('plat_pf', 'PostFinance', 'broker', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct_pf', 'plat_pf', 'Depot', 'brokerage', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO instruments(instrument_id, asset_class, name, ticker, isin, currency, created_at) VALUES ('inst_vti', 'etf', 'Vanguard Total Stock Market ETF', 'VTI', 'US9229087690', 'USD', ?)", (now,))
    conn.execute("INSERT INTO transactions(transaction_id, transaction_type, account_id, instrument_id, trade_date, gross_amount_chf, net_amount_chf, currency_original, source_type, is_confirmed, quality_status, created_at) VALUES ('tx_pf', 'initial_snapshot', 'acct_pf', 'inst_vti', '2026-06-30', '30000.00', '30000.00', 'USD', 'fixture', 1, 'ok', ?)", (now,))

    conn.execute("INSERT INTO platforms(platform_id, name, platform_type, default_currency, created_at) VALUES ('plat_tw', 'True Wealth', 'roboadvisor', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct_tw', 'plat_tw', 'TrueWealth Gesamtwert', 'managed_portfolio', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO account_value_snapshots(snapshot_id, account_id, valuation_date, total_value_chf, source_type, created_at) VALUES ('snap_tw', 'acct_tw', '2026-06-30', '50000.00', 'fixture', ?)", (now,))

    conn.execute("INSERT INTO crypto_wallets(wallet_id, wallet_name, wallet_type, created_at) VALUES ('wallet', 'Cold Wallet', 'hardware', ?)", (now,))
    conn.execute("INSERT INTO crypto_assets(asset_id, symbol, coin_name, coingecko_id, created_at) VALUES ('btc', 'BTC', 'Bitcoin', 'bitcoin', ?)", (now,))
    conn.execute("INSERT INTO crypto_holdings(crypto_holding_id, wallet_id, asset_id, quantity, verification_status, created_at) VALUES ('holding', 'wallet', 'btc', '0.1', 'ok', ?)", (now,))
    conn.execute("INSERT INTO crypto_prices(crypto_price_id, asset_id, coingecko_id, price_currency, price, provider, provider_timestamp, fetched_at, quality_status) VALUES ('price', 'btc', 'bitcoin', 'CHF', '100000.00', 'fixture', ?, ?, 'fresh')", (fresh_price_time, fresh_price_time))
    conn.commit()


def test_portfolio_advisor_snapshot_lives_in_finance_manager_with_exact_local_values() -> None:
    conn = _connect()
    apply_migrations(conn)
    _seed_portfolio(conn)

    snapshot = get_portfolio_advisor_snapshot(conn)

    assert snapshot.title == "Allokations- und Datenchecks (Beta)"
    assert snapshot.total_value_chf == "100000.00"
    sources = {item.key: item for item in snapshot.source_allocation}
    assert sources["truewealth"].current_value_chf == "50000.00"
    assert sources["own_equities"].current_value_chf == "30000.00"
    assert sources["cash"].current_value_chf == "10000.00"
    assert sources["crypto"].current_value_chf == "10000.00"
    assert "Unternehmen/Holdings" in snapshot.tabs
    assert {node.parent_key for node in snapshot.drilldown_nodes} >= {"equities", "cash", "crypto"}
    assert any(node.label == "USA" and node.parent_key == "equities" for node in snapshot.drilldown_nodes)
    lookthrough = {item.source_key: item for item in snapshot.lookthrough_placeholders}
    assert lookthrough["truewealth"].status == "pending_import"
    assert "Top Holdings" in lookthrough["own_etfs"].dimensions
    imports = {item.source_key: item for item in snapshot.import_sources}
    assert imports["truewealth_screens"].expected_files == ["*.png"]
    assert "asset_allocation" in imports["truewealth_screens"].mapped_to
    assert all(step.execution_allowed is False for step in snapshot.workflow)
    assert any("Keine Auto-Orders" in guardrail for guardrail in snapshot.guardrails)
    signals = {item.symbol: item for item in snapshot.investment_signals}
    assert signals["VTI"].signal == "WATCH"
    assert signals["VTI"].execution_allowed is False
    assert signals["VTI"].source == "local_rules_v1"
    assert "data_quality_incomplete" in signals["VTI"].risk_factors
    assert signals["BTC"].signal in {"HOLD", "REDUCE"}
    assert signals["BTC"].portfolio_bucket == "crypto"


def test_portfolio_advisor_api_route_is_registered_in_finance_manager_app() -> None:
    app = create_app()

    paths = app.openapi()["paths"]

    assert "/api/portfolio/advisor" in paths