from __future__ import annotations

from collections.abc import Iterator
from decimal import Decimal
import sqlite3
from sqlite3 import Connection

from fastapi.testclient import TestClient

from jarvis_finance.api.dependencies import get_db
from jarvis_finance.api.main import create_app
from jarvis_finance.imports.common import utc_now
from jarvis_finance.market_data.prices import EquityPriceQuote, MockEquityPriceProvider
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 _client(conn: Connection) -> TestClient:
    app = create_app()

    def override_db() -> Iterator[Connection]:
        yield conn

    app.dependency_overrides[get_db] = override_db
    return TestClient(app)


def _seed(conn: Connection) -> None:
    now = "2026-05-16T12:00:00Z"
    conn.execute("INSERT INTO platforms(platform_id, name, platform_type, country, default_currency, created_at) VALUES ('p1', 'Broker', 'broker', 'CH', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('a1', 'p1', 'Depot', 'brokerage', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO instruments(instrument_id, asset_class, name, ticker, isin, exchange, currency, provider_symbol, data_provider_primary, created_at) VALUES ('inst1', 'stock', 'Demo Equity', 'DEMO', 'CH0000000000', 'SIX', 'CHF', 'DEMO.SW', 'fmp', ?)", (now,))
    conn.execute("INSERT INTO instrument_price_mappings(mapping_id, instrument_id, isin, ticker, exchange, currency, provider, provider_symbol, mapping_status, confidence, created_at) VALUES ('map1', 'inst1', 'CH0000000000', 'DEMO', 'SIX', 'CHF', 'fmp', 'DEMO.SW', 'mapped', '1', ?)", (now,))
    conn.execute("INSERT INTO crypto_wallets(wallet_id, wallet_name, wallet_type, created_at) VALUES ('w1', 'Cold Wallet', 'hardware', ?)", (now,))
    conn.execute("INSERT INTO crypto_assets(asset_id, symbol, coin_name, coingecko_id, binance_symbol, created_at) VALUES ('btc', 'BTC', 'Bitcoin', 'bitcoin', 'BTCUSDT', ?)", (now,))
    conn.execute("INSERT INTO crypto_holdings(crypto_holding_id, wallet_id, asset_id, quantity, verification_status, created_at) VALUES ('h1', 'w1', 'btc', '0.1', 'ok', ?)", (now,))
    conn.commit()


def test_market_quote_chart_tables_created_by_migrations() -> None:
    conn = _connect(); apply_migrations(conn)
    tables = {r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    assert "equity_price_points" in tables
    assert "crypto_price_points" in tables
    assert "equity_intraday_candles" in tables
    assert "binance_symbol" in {r[1] for r in conn.execute("PRAGMA table_info(crypto_assets)")}


def test_market_status_endpoint_is_sanitized_and_cache_only() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    payload = _client(conn).get("/api/market/status").json()
    assert payload["render_provider_calls"] is False
    assert payload["mapped_equity_instruments"] == 1
    assert payload["mapped_crypto_assets"] == 1
    rendered = str(payload).lower()
    assert "api_key" not in rendered and "token" not in rendered and "secret" not in rendered


def test_equity_quote_refresh_writes_quote_and_chart_point(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    def fake_provider(name: str):
        return MockEquityPriceProvider({"DEMO.SW": Decimal("42.10")}, currency="CHF", timestamps={"DEMO.SW": "2026-05-16T12:05:00Z"})

    monkeypatch.setattr("jarvis_finance.services.market_service.equity_price_provider_by_name", fake_provider)
    payload = _client(conn).post("/api/market/equity/update-quotes?instrument_id=inst1", json={"provider": "auto", "interval": "5m", "price_date": "2026-05-18"}).json()
    assert payload["quality_status"] == "fresh"
    assert payload["latest_price"] == "42.10"
    assert conn.execute("SELECT COUNT(*) FROM market_prices WHERE instrument_id='inst1'").fetchone()[0] == 1
    assert conn.execute("SELECT COUNT(*) FROM equity_price_points WHERE instrument_id='inst1'").fetchone()[0] == 1


def test_equity_chart_get_is_read_only_even_when_only_legacy_price_exists(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    conn.execute("INSERT INTO market_prices(market_price_id, instrument_id, price_date, price_timestamp, close, currency, provider, provider_symbol, quality_status, created_at) VALUES ('mp1', 'inst1', '2026-05-16', '2026-05-16T12:00:00Z', '41.00', 'CHF', 'local', 'DEMO.SW', 'fresh', ?)", (utc_now(),))
    conn.commit()

    def forbidden(*args, **kwargs):  # provider must not be touched by chart GET
        raise AssertionError("provider called during chart render")

    monkeypatch.setattr("jarvis_finance.services.market_service.equity_price_provider_by_name", forbidden)
    before = conn.total_changes
    payload = _client(conn).get("/api/equity/inst1/chart?range=1d&interval=5m").json()
    assert payload["chart_points"][0]["price"] == "41.00"
    assert conn.total_changes == before
    assert conn.execute("SELECT COUNT(*) FROM equity_price_points WHERE instrument_id='inst1'").fetchone()[0] == 0


def test_equity_candles_endpoint_uses_yfinance_only_on_explicit_call_and_caches(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    calls = {"count": 0}

    def fake_history(symbol: str, *, range_key: str, interval_key: str):
        calls["count"] += 1
        assert symbol == "DEMO.SW"
        return ([
            {"timestamp": "2026-05-20T09:30:00-04:00", "open": Decimal("190.1"), "close": Decimal("191.2"), "low": Decimal("189.8"), "high": Decimal("191.5"), "volume": 1000, "currency": "USD", "exchange_timezone": "America/New_York"},
            {"timestamp": "2026-05-20T09:35:00-04:00", "open": Decimal("191.2"), "close": Decimal("190.8"), "low": Decimal("190.5"), "high": Decimal("191.9"), "volume": 1200, "currency": "USD", "exchange_timezone": "America/New_York"},
        ], "USD", "America/New_York")

    monkeypatch.setattr("jarvis_finance.services.market_service._yfinance_history", fake_history)
    client = _client(conn)
    assert client.get("/api/equity/positions").status_code == 200
    assert calls["count"] == 0
    payload = client.get("/api/equity/inst1/candles?range=1d&interval=5m&refresh=true").json()
    assert payload["provider"] == "yfinance"
    assert payload["quality_status"] == "fresh"
    assert payload["candles"][0] == {"time": "2026-05-20T09:30:00-04:00", "open": "190.1", "high": "191.5", "low": "189.8", "close": "191.2", "volume": "1000"}
    assert conn.execute("SELECT COUNT(*) FROM equity_intraday_candles WHERE instrument_id='inst1'").fetchone()[0] == 2
    cached = client.get("/api/equity/inst1/candles?range=1d&interval=5m").json()
    assert calls["count"] == 1
    assert len(cached["candles"]) == 2


def test_equity_candles_provider_error_and_missing_symbol_are_clear(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    def broken(*args, **kwargs):
        raise RuntimeError("provider_down")

    monkeypatch.setattr("jarvis_finance.services.market_service._yfinance_history", broken)
    client = _client(conn)
    error_payload = client.get("/api/equity/inst1/candles?refresh=true").json()
    assert error_payload["quality_status"] == "provider_error"
    assert "provider_down" in error_payload["warnings"][0]
    conn.execute("UPDATE instrument_price_mappings SET provider_symbol=NULL WHERE instrument_id='inst1'")
    conn.execute("UPDATE instruments SET provider_symbol=NULL WHERE instrument_id='inst1'")
    conn.commit()
    missing_payload = client.get("/api/equity/inst1/candles?refresh=true").json()
    assert missing_payload["quality_status"] == "missing_provider_symbol"


def test_crypto_coingecko_refresh_writes_quote_and_chart_point(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    class FakeCoinGecko:
        def get_crypto_price(self, coingecko_id: str, currency: str = "CHF"):
            from jarvis_finance.market.providers import PriceQuote
            return PriceQuote(coingecko_id=coingecko_id, currency=currency.upper(), price=Decimal("80000"), provider="CoinGecko", provider_timestamp="2026-05-16T12:00:00Z", quality_status="fresh")

    monkeypatch.setattr("jarvis_finance.services.market_service.CoinGeckoClient", lambda: FakeCoinGecko())
    payload = _client(conn).post("/api/market/crypto/update-live-stats?asset_id=btc", json={"provider": "coingecko", "currency": "CHF"}).json()
    assert payload["quality_status"] == "fresh"
    assert payload["latest_price"] == "80000"
    assert conn.execute("SELECT COUNT(*) FROM crypto_prices WHERE asset_id='btc'").fetchone()[0] == 1
    assert conn.execute("SELECT COUNT(*) FROM crypto_price_points WHERE asset_id='btc'").fetchone()[0] == 1


def test_crypto_binance_live_stats_and_chart_cached(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    class FakeBinance:
        def ticker_24h(self, symbol: str):
            return {"lastPrice": "70000", "priceChange": "100", "priceChangePercent": "0.14", "highPrice": "71000", "lowPrice": "69000", "volume": "123"}

    monkeypatch.setattr("jarvis_finance.services.market_service.BinanceClient", lambda: FakeBinance())
    client = _client(conn)
    stats = client.post("/api/market/crypto/update-live-stats?asset_id=btc", json={"provider": "binance"}).json()
    assert stats["provider"] == "binance"
    assert stats["quality_status"] == "fresh"
    chart = client.get("/api/crypto/btc/chart?currency=USD").json()
    assert chart["chart_points"][0]["price"] == "70000"


def test_missing_symbols_are_clear_not_guessed() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    conn.execute("UPDATE instrument_price_mappings SET provider_symbol=NULL WHERE instrument_id='inst1'")
    conn.execute("UPDATE instruments SET provider_symbol=NULL WHERE instrument_id='inst1'")
    conn.execute("UPDATE crypto_assets SET binance_symbol=NULL WHERE asset_id='btc'")
    conn.commit()
    client = _client(conn)
    eq = client.post("/api/market/equity/update-quotes?instrument_id=inst1", json={"provider": "auto"}).json()
    cr = client.post("/api/market/crypto/update-live-stats?asset_id=btc", json={"provider": "binance"}).json()
    assert eq["quality_status"] == "missing"
    assert "Provider-Symbol fehlt" in eq["warnings"]
    assert cr["quality_status"] == "unsupported_pair"


def test_equity_batch_update_endpoint_matches_frontend_contract(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    def fake_provider(name: str):
        return MockEquityPriceProvider({"DEMO.SW": Decimal("42.10")}, currency="CHF", timestamps={"DEMO.SW": "2026-05-16T12:05:00Z"})

    monkeypatch.setattr("jarvis_finance.services.market_service.equity_price_provider_by_name", fake_provider)
    payload = _client(conn).post("/api/market/equity/update-quotes", json={"provider": "auto", "limit": 10, "price_date": "2026-05-18"}).json()
    assert payload["updated"] == 1
    assert payload["skipped"] == 0
    assert payload["render_provider_calls"] is False
    assert conn.execute("SELECT COUNT(*) FROM market_prices WHERE instrument_id='inst1'").fetchone()[0] == 1


def test_equity_full_refresh_dry_run_calls_provider_for_cached_rows_without_writes(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    conn.execute(
        "INSERT INTO market_prices(market_price_id,instrument_id,price_date,price_timestamp,close,currency,provider,provider_symbol,provider_market,quality_status,created_at) VALUES('existing','inst1','2026-05-18','2026-05-18T12:00:00Z','40','CHF','fmp','DEMO.SW','SIX','fresh',?)",
        (utc_now(),),
    )
    conn.commit()
    calls = {"count": 0}

    def fake_provider(name: str):
        calls["count"] += 1
        return MockEquityPriceProvider({"DEMO.SW": Decimal("42.10")}, currency="CHF", timestamps={"DEMO.SW": "2026-05-18T12:05:00Z"})

    monkeypatch.setattr("jarvis_finance.services.market_service.equity_price_provider_by_name", fake_provider)
    before = tuple(conn.execute("SELECT COUNT(*),MAX(close) FROM market_prices").fetchone())
    payload = _client(conn).post(
        "/api/market/equity/update-quotes",
        json={"provider": "auto", "limit": 100, "price_date": "2026-05-18", "only_missing": False, "dry_run": True, "pacing_seconds": 0},
    ).json()
    after = tuple(conn.execute("SELECT COUNT(*),MAX(close) FROM market_prices").fetchone())
    assert calls["count"] == 1
    assert before == after == (1, "40")
    assert payload["mode"] == "dry_run"
    assert payload["target_date"] == "2026-05-18"
    assert payload["result_price_date_from"] == payload["result_price_date_to"] == "2026-05-18"
    assert payload["provider_calls"] == 1
    assert payload["would_update"] == 1 and payload["updated"] == 0
    assert payload["persistence_performed"] is False
    assert payload["results"][0]["status"] == "would_update"


def test_equity_dry_run_endpoint_is_allowed_in_disabled_write_mode_and_forces_no_write(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    def fake_provider(name: str):
        return MockEquityPriceProvider({"DEMO.SW": Decimal("42.10")}, currency="CHF", timestamps={"DEMO.SW": "2026-05-18T12:05:00Z"})

    monkeypatch.setattr("jarvis_finance.services.market_service.equity_price_provider_by_name", fake_provider)
    app = create_app(write_mode="disabled")

    def override_db() -> Iterator[Connection]:
        yield conn

    app.dependency_overrides[get_db] = override_db
    client = TestClient(app)
    response = client.post(
        "/api/market/equity/update-quotes/dry-run",
        json={"provider": "auto", "limit": 100, "price_date": "2026-05-18", "only_missing": True, "dry_run": False, "pacing_seconds": 0},
    )
    assert response.status_code == 200
    payload = response.json()
    assert payload["mode"] == "dry_run" and payload["would_update"] == 1
    assert payload["persistence_performed"] is False
    assert conn.execute("SELECT COUNT(*) FROM market_prices").fetchone()[0] == 0
    assert client.post("/api/market/equity/update-quotes", json={"dry_run": True}).status_code == 403
    remote = TestClient(app, client=("203.0.113.10", 50000))
    assert remote.post("/api/market/equity/update-quotes/dry-run", json={}).status_code == 403


def test_crypto_batch_update_endpoint_matches_frontend_contract(monkeypatch) -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)

    class FakeBinance:
        def ticker_24h(self, symbol: str):
            return {"lastPrice": "70000", "priceChange": "100", "priceChangePercent": "0.14", "highPrice": "71000", "lowPrice": "69000", "volume": "123"}

    monkeypatch.setattr("jarvis_finance.services.market_service.BinanceClient", lambda: FakeBinance())
    payload = _client(conn).post("/api/market/crypto/update-live-stats", json={"provider": "binance", "limit": 20, "currency": "CHF"}).json()
    assert payload["updated"] == 1
    assert payload["skipped"] == 0
    assert payload["render_provider_calls"] is False
    assert conn.execute("SELECT COUNT(*) FROM crypto_price_points WHERE asset_id='btc'").fetchone()[0] == 1