from __future__ import annotations

from collections.abc import Iterator
import sqlite3
from datetime import UTC, datetime
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.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', 'Raiffeisen', 'bank', 'CH', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO platforms(platform_id, name, platform_type, country, default_currency, created_at) VALUES ('p2', 'True Wealth', 'broker', 'CH', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('cash1', 'p1', 'Privatkonto', 'cash', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('broker1', 'p2', 'Depot', 'brokerage', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO instruments(instrument_id, asset_class, name, ticker, isin, currency, created_at) VALUES ('inst1', 'stock', 'Demo Equity', 'DEMO', 'CH0000000000', 'CHF', ?)", (now,))
    conn.execute("INSERT INTO market_prices(market_price_id, instrument_id, price_date, close, currency, provider, quality_status, created_at) VALUES ('mp1', 'inst1', '2026-05-15', '10.50', 'CHF', 'local', 'ok', ?)", (now,))
    conn.execute("INSERT INTO cash_balances(cash_balance_id, account_id, balance_date, currency, amount_original, fx_rate_to_chf, amount_chf, source_type, quality_status, created_at) VALUES ('cb1', 'cash1', '2026-05-16', 'CHF', '1000', '1', '1000', 'fixture', 'ok', ?)", (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, 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 ('h1', 'w1', 'btc', '0.25', 'ok', ?)", (now,))
    fresh_price_time = datetime.now(UTC).replace(microsecond=0).isoformat()
    conn.execute("INSERT INTO crypto_prices(crypto_price_id, asset_id, coingecko_id, price_currency, price, provider, provider_timestamp, fetched_at, quality_status) VALUES ('cp1', 'btc', 'bitcoin', 'CHF', '80000', 'CoinGecko', ?, ?, 'fresh')", (fresh_price_time, fresh_price_time))
    conn.commit()


def test_instrument_search_is_local_sanitized_and_decimal_free() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    payload = _client(conn).post("/api/instruments/search", json={"asset_class": "stock", "query": "demo"}).json()

    assert payload[0]["label"] == "Demo Equity"
    assert payload[0]["isin"] == "CH0000000000"
    assert payload[0]["provider"] == "local"
    assert "api_key" not in str(payload).lower()
    assert "secret" not in str(payload).lower()


def test_cash_preview_confirm_writes_snapshot_and_audit_only_after_confirm() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)
    request = {"account_id": "cash1", "currency": "CHF", "amount": "250.25", "transaction_type": "cash_deposit", "booking_date": "2026-05-17", "note": "manual deposit"}

    preview = client.post("/api/cash/preview", json=request).json()
    assert preview["warnings"] == []
    assert preview["amount_chf"] == "250.25"
    assert conn.execute("SELECT COUNT(*) FROM audit_log").fetchone()[0] == 0

    confirmed = client.post("/api/cash/confirm", json={**request, "preview_id": preview["preview_id"], "confirm": True}).json()

    assert confirmed["status"] == "confirmed"
    assert isinstance(confirmed["audit_id"], str)
    assert conn.execute("SELECT COUNT(*) FROM cash_balances WHERE source_type='vue_manual_cash'").fetchone()[0] == 1
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE action='cash_confirm'").fetchone()[0] == 1


def test_equity_position_preview_confirm_writes_transaction_and_audit() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)
    request = {"asset_class": "stock", "account_id": "broker1", "instrument_id": "inst1", "quantity": "3", "currency": "CHF", "trade_date": "2026-05-17", "transaction_type": "initial_snapshot", "cost_basis_original": "30", "note": "opening snapshot"}

    preview = client.post("/api/positions/preview", json=request).json()
    assert preview["asset_class"] == "stock"
    assert preview["fx_status"] == "not_needed"
    assert preview["warnings"] == []

    confirmed = client.post("/api/positions/confirm", json={**request, "preview_id": preview["preview_id"], "confirm": True}).json()

    assert confirmed["status"] == "confirmed"
    assert conn.execute("SELECT COUNT(*) FROM transactions WHERE source_type='vue_manual_position'").fetchone()[0] == 1
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE action='position_confirm'").fetchone()[0] == 1


def test_details_audit_and_history_endpoints_are_user_safe() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)

    equity = client.get("/api/portfolio/positions/broker1:inst1").json()
    crypto = client.get("/api/crypto/positions/btc").json()
    cash = client.get("/api/cash/positions/cash1:CHF").json()
    market_history = client.get("/api/market/prices/inst1/history").json()
    crypto_history = client.get("/api/crypto/prices/btc/history").json()

    assert equity["available_actions"] == [{"label": "Verlauf", "enabled": True, "reason": None}]
    assert cash["available_actions"][0]["label"] == "Einzahlung"
    assert crypto["available_actions"][-1]["label"] == "Live-Modus später"
    assert market_history[0]["value"] == "10.50"
    assert crypto_history[0]["value"] == "80000"
    rendered = str([equity, crypto, cash, market_history, crypto_history]).lower()
    assert "api_key" not in rendered and "token" not in rendered and "secret" not in rendered


def test_crypto_detail_aggregates_wallet_holdings_before_joining_latest_price() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    now = "2026-05-16T12:00:00Z"
    conn.execute("INSERT INTO crypto_wallets(wallet_id, wallet_name, wallet_type, created_at) VALUES ('w2', 'Exchange Wallet', 'exchange', ?)", (now,))
    conn.execute("UPDATE crypto_holdings SET quantity='0.30' WHERE crypto_holding_id='h1'")
    conn.execute("INSERT INTO crypto_holdings(crypto_holding_id, wallet_id, asset_id, quantity, verification_status, created_at) VALUES ('h2', 'w2', 'btc', '0.20', '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 ('cp_old', 'btc', 'bitcoin', 'CHF', '70000', 'local', '2026-05-15T12:00:00Z', '2026-05-15T12:00:00Z', 'stale')")
    conn.commit()

    detail = _client(conn).get("/api/crypto/positions/btc").json()

    by_wallet = {row["wallet"]: row for row in detail["wallet_allocations"]}
    assert list(by_wallet) == ["Cold Wallet", "Exchange Wallet"]
    assert by_wallet["Cold Wallet"]["quantity"] == "0.30"
    assert by_wallet["Exchange Wallet"]["quantity"] == "0.20"
    assert by_wallet["Cold Wallet"]["market_value_chf"] == "24000.00"
    assert sum(float(row["quantity"]) for row in detail["wallet_allocations"]) == float(detail["quantity_total"])


def test_wallet_summaries_and_details_use_latest_price_without_duplicate_coin_rows() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    conn.execute("INSERT INTO crypto_prices(crypto_price_id, asset_id, coingecko_id, price_currency, price, provider, provider_timestamp, fetched_at, quality_status) VALUES ('cp_old', 'btc', 'bitcoin', 'CHF', '70000', 'local', '2026-05-15T12:00:00Z', '2026-05-15T12:00:00Z', 'stale')")
    conn.commit()

    client = _client(conn)
    wallets = client.get("/api/wallets").json()
    detail = client.get("/api/wallets/w1").json()

    assert wallets[0]["market_value_chf"] == "20000.00"
    assert [coin["asset_id"] for coin in detail["coins"]] == ["btc"]
    assert detail["coins"][0]["market_value_chf"] == "20000.00"


def test_crypto_manual_adjustment_preview_confirm_writes_transaction_and_audit() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)
    request = {"asset_id": "btc", "wallet_id": "w1", "quantity": "0.12500001", "transaction_type": "increase", "effective_date": "2026-05-17", "note": "manual top-up"}

    preview = client.post("/api/crypto/actions/preview", json=request).json()
    assert preview["summary"].startswith("Crypto Bestand erhöhen")
    assert preview["warnings"] == []
    assert conn.execute("SELECT COUNT(*) FROM crypto_transactions").fetchone()[0] == 0

    confirmed = client.post("/api/crypto/actions/confirm", json={**request, "preview_id": preview["preview_id"], "confirm": True}).json()

    assert confirmed["status"] == "confirmed"
    assert conn.execute("SELECT quantity FROM crypto_transactions WHERE source='manual_dashboard'").fetchone()[0] == "0.12500001"
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE action='crypto_manual_adjustment'").fetchone()[0] == 1


def test_crypto_set_zero_allows_zero_preview_and_confirms_adjustment() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)
    request = {"asset_id": "btc", "wallet_id": "w1", "quantity": "0", "transaction_type": "set_zero", "effective_date": "2026-05-17", "note": "close manual test holding"}

    preview = client.post("/api/crypto/actions/preview", json=request).json()
    assert preview["summary"].startswith("Crypto auf 0 setzen")

    confirmed = client.post("/api/crypto/actions/confirm", json={**request, "preview_id": preview["preview_id"], "confirm": True}).json()
    assert confirmed["status"] == "confirmed"
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE action='crypto_manual_adjustment'").fetchone()[0] == 1


def test_cash_withdrawal_and_correction_preview_confirm_use_positive_amount_and_audit() -> None:
    conn = _connect(); apply_migrations(conn); _seed(conn)
    client = _client(conn)
    withdrawal = {"account_id": "cash1", "currency": "CHF", "amount": "25.00", "transaction_type": "Auszahlung", "booking_date": "2026-05-17", "note": "cash out"}
    correction = {"account_id": "cash1", "currency": "CHF", "amount": "5.00", "transaction_type": "Korrektur", "booking_date": "2026-05-17", "note": "manual correction"}

    w_preview = client.post("/api/cash/preview", json=withdrawal).json()
    assert w_preview["summary"] == "Auszahlung CHF 25.00"
    w_confirm = client.post("/api/cash/confirm", json={**withdrawal, "preview_id": w_preview["preview_id"], "confirm": True}).json()
    assert w_confirm["status"] == "confirmed"
    assert conn.execute("SELECT transaction_type, gross_amount_original FROM transactions WHERE notes='cash out'").fetchone()[:] == ("cash_withdrawal", "25.00")

    c_preview = client.post("/api/cash/preview", json=correction).json()
    c_confirm = client.post("/api/cash/confirm", json={**correction, "preview_id": c_preview["preview_id"], "confirm": True}).json()
    assert c_confirm["status"] == "confirmed"
    assert conn.execute("SELECT transaction_type FROM transactions WHERE notes='manual correction'").fetchone()[0] == "manual_cash_correction"
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE action='cash_confirm'").fetchone()[0] >= 2


def test_vue_dashboard_scripts_exist_are_executable_and_do_not_print_secret_names() -> None:
    from pathlib import Path
    import stat

    repo = Path(__file__).resolve().parents[2]
    for rel in ["scripts/start_vue_dashboard.sh", "scripts/stop_vue_dashboard.sh"]:
        script = repo / rel
        assert script.exists()
        assert script.stat().st_mode & stat.S_IXUSR
        text = script.read_text()
        assert "API_KEY" not in text
        assert "TOKEN" not in text
        assert "SECRET" not in text