from decimal import Decimal
import json
import sqlite3

import pytest

from binance_quant.models import OrderResult, OrderSide, Signal, SignalType
from binance_quant.storage import Storage


def test_storage_records_signal_and_order(tmp_path):
    db_path = tmp_path / "audit.sqlite3"
    storage = Storage(db_path)
    storage.initialize()

    signal_id = storage.record_signal(Signal("BTCUSDT", SignalType.BUY, OrderSide.BUY, "test buy"))
    order_id = storage.record_order_result(
        OrderResult(
            symbol="BTCUSDT",
            side=OrderSide.BUY,
            client_order_id="bq-test",
            status="FILLED",
            raw={"orderId": 123},
            dry_run=True,
        )
    )

    assert signal_id == 1
    assert order_id == 1
    assert storage.list_orders()[0]["client_order_id"] == "bq-test"


def test_storage_records_risk_decision(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    signal = Signal("BTCUSDT", SignalType.BUY, OrderSide.BUY, "test buy")

    row_id = storage.record_risk_decision(signal, approved=False, reason="cooldown active", quote_amount=Decimal("10"))

    assert row_id == 1


def test_storage_preserves_zero_created_at_for_signal(tmp_path):
    db_path = tmp_path / "audit.sqlite3"
    storage = Storage(db_path)
    storage.initialize()
    signal = Signal("BTCUSDT", SignalType.BUY, OrderSide.BUY, "test buy", created_at_ms=0)

    storage.record_signal(signal)

    with sqlite3.connect(db_path) as connection:
        row = connection.execute("SELECT created_at_ms FROM signals").fetchone()
    assert row[0] == 0


def test_storage_preserves_zero_created_at_for_risk_decision(tmp_path):
    db_path = tmp_path / "audit.sqlite3"
    storage = Storage(db_path)
    storage.initialize()
    signal = Signal("BTCUSDT", SignalType.BUY, OrderSide.BUY, "test buy", created_at_ms=0)

    storage.record_risk_decision(signal, approved=True, reason="accepted", quote_amount=Decimal("10"))

    with sqlite3.connect(db_path) as connection:
        row = connection.execute("SELECT created_at_ms FROM risk_decisions").fetchone()
    assert row[0] == 0


def test_storage_initialize_creates_nested_parent_directory(tmp_path):
    db_path = tmp_path / "nested" / "audit.sqlite3"
    storage = Storage(db_path)

    storage.initialize()

    assert db_path.is_file()


def test_storage_records_order_raw_json_with_decimal_values(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    storage.record_order_result(_order_result("bq-decimal", raw={"quoteAmount": Decimal("10.5")}))

    [order] = storage.list_orders()
    assert json.loads(order["raw_json"]) == {"quoteAmount": "10.5"}


def test_storage_lists_orders_newest_first_with_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    storage.record_order_result(_order_result("bq-first"))
    storage.record_order_result(_order_result("bq-second"))
    storage.record_order_result(_order_result("bq-third"))

    orders = storage.list_orders(limit=2)

    assert [order["client_order_id"] for order in orders] == ["bq-third", "bq-second"]
    assert [order["id"] for order in orders] == [3, 2]


def test_storage_rejects_negative_order_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(ValueError, match="limit must be non-negative"):
        storage.list_orders(limit=-1)


def test_storage_records_auto_buy_run_and_events(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    run_id = storage.record_auto_buy_run(
        symbol="BTCUSDT",
        status="protected",
        score=Decimal("4.5"),
        reason="selected",
        quote_amount=Decimal("5"),
    )
    event_id = storage.record_auto_buy_event(
        run_id,
        event_type="market_buy",
        symbol="BTCUSDT",
        client_order_id="bqab-buy",
        status="FILLED",
        raw={"orderId": 1, "signature": "redacted"},
    )

    assert run_id == 1
    assert event_id == 1
    [run] = storage.list_auto_buy_runs()
    assert run["symbol"] == "BTCUSDT"
    assert run["status"] == "protected"
    assert run["score"] == "4.5"
    [event] = storage.list_auto_buy_events(run_id)
    assert event["event_type"] == "market_buy"
    assert json.loads(event["raw_json"]) == {"orderId": 1, "signature": "redacted"}


def test_storage_updates_auto_buy_run_status(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_auto_buy_run("BTCUSDT", "buy_submitted", Decimal("4"), "submitted", Decimal("5"))

    storage.update_auto_buy_run_status(run_id, "protected", "protective stop accepted")

    [run] = storage.list_auto_buy_runs()
    assert run["status"] == "protected"
    assert run["reason"] == "protective stop accepted"


def test_storage_counts_live_auto_buy_attempts_since(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    first = storage.record_auto_buy_run("BTCUSDT", "buy_submitted", Decimal("4"), "first", Decimal("5"), created_at_ms=1000)
    storage.record_auto_buy_run("ETHUSDT", "no_candidate", Decimal("0"), "none", Decimal("5"), created_at_ms=2000)
    storage.record_auto_buy_run("SOLUSDT", "rollback_completed", Decimal("5"), "rollback", Decimal("5"), created_at_ms=3000)
    storage.record_auto_buy_run("BNBUSDT", "reserved", Decimal("5"), "reserved", Decimal("5"), created_at_ms=4000)

    assert first == 1
    assert storage.count_auto_buy_attempts_since(0) == 3
    assert storage.count_auto_buy_attempts_since(2000) == 2


def test_storage_finds_recent_auto_buy_attempt_for_symbol(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_auto_buy_run("BTCUSDT", "protected", Decimal("4"), "old", Decimal("5"), created_at_ms=1000)
    storage.record_auto_buy_run("ETHUSDT", "protected", Decimal("4"), "other", Decimal("5"), created_at_ms=5000)
    storage.record_auto_buy_run("SOLUSDT", "reserved", Decimal("4"), "pending", Decimal("5"), created_at_ms=7000)

    assert storage.has_recent_auto_buy_attempt("BTCUSDT", since_ms=999)
    assert not storage.has_recent_auto_buy_attempt("BTCUSDT", since_ms=1001)
    assert storage.has_recent_auto_buy_attempt("SOLUSDT", since_ms=6999)


def test_storage_reserves_auto_buy_attempt_atomically(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    first = storage.reserve_auto_buy_attempt(
        symbol="BTCUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quote_amount=Decimal("5"),
        daily_limit=1,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=2000,
    )
    second = storage.reserve_auto_buy_attempt(
        symbol="ETHUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quote_amount=Decimal("5"),
        daily_limit=1,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=2001,
    )

    assert first.approved
    assert first.run_id == 1
    assert second.approved is False
    assert second.reason == "daily auto-buy limit reached"
    [run] = storage.list_auto_buy_runs()
    assert run["status"] == "reserved"


def test_storage_reservation_rejects_recent_symbol_attempt(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_auto_buy_run("BTCUSDT", "reserved", Decimal("4.5"), "selected", Decimal("5"), created_at_ms=2000)

    decision = storage.reserve_auto_buy_attempt(
        symbol="BTCUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quote_amount=Decimal("5"),
        daily_limit=5,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=3000,
    )

    assert decision.approved is False
    assert decision.reason == "symbol cooldown active"
    [run] = storage.list_auto_buy_runs()
    assert run["status"] == "reserved"


def test_storage_records_auto_buy_event_raw_json_with_decimal_values(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_auto_buy_run("BTCUSDT", "protected", Decimal("4"), "selected", Decimal("5"))

    storage.record_auto_buy_event(run_id, "market_buy", "BTCUSDT", "bqab-decimal", "FILLED", {"quoteAmount": Decimal("5.25")})

    [event] = storage.list_auto_buy_events(run_id)
    assert json.loads(event["raw_json"]) == {"quoteAmount": "5.25"}


def test_storage_rejects_auto_buy_event_with_missing_run_id(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(sqlite3.IntegrityError):
        storage.record_auto_buy_event(
            run_id=999,
            event_type="market_buy",
            symbol="BTCUSDT",
            client_order_id="bqab-missing-run",
            status="FILLED",
            raw={"orderId": 1},
        )


def test_storage_preserves_zero_created_at_for_auto_buy_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    storage.record_auto_buy_run("BTCUSDT", "protected", Decimal("4"), "selected", Decimal("5"), created_at_ms=0)

    [run] = storage.list_auto_buy_runs()
    assert run["created_at_ms"] == 0


def test_storage_preserves_zero_created_at_for_auto_buy_event(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_auto_buy_run("BTCUSDT", "protected", Decimal("4"), "selected", Decimal("5"))

    storage.record_auto_buy_event(
        run_id,
        "market_buy",
        "BTCUSDT",
        "bqab-zero-time",
        "FILLED",
        {"orderId": 1},
        created_at_ms=0,
    )

    [event] = storage.list_auto_buy_events(run_id)
    assert event["created_at_ms"] == 0


def test_storage_rejects_negative_auto_buy_run_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(ValueError, match="limit must be non-negative"):
        storage.list_auto_buy_runs(limit=-1)


def test_storage_records_auto_sell_run_and_events(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    run_id = storage.record_auto_sell_run(
        symbol="BTCUSDT",
        status="sold",
        score=Decimal("4.5"),
        reason="selected",
        quantity=Decimal("0.001"),
    )
    event_id = storage.record_auto_sell_event(
        run_id,
        event_type="market_sell",
        symbol="BTCUSDT",
        client_order_id="bqas-sell",
        status="FILLED",
        raw={"orderId": 1, "quantity": Decimal("0.001")},
    )

    assert run_id == 1
    assert event_id == 1
    [run] = storage.list_auto_sell_runs()
    assert run["symbol"] == "BTCUSDT"
    assert run["status"] == "sold"
    assert run["score"] == "4.5"
    assert run["quantity"] == "0.001"
    [event] = storage.list_auto_sell_events(run_id)
    assert event["event_type"] == "market_sell"
    assert json.loads(event["raw_json"]) == {"orderId": 1, "quantity": "0.001"}


def test_storage_updates_auto_sell_run_status(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_auto_sell_run("BTCUSDT", "sell_submitted", Decimal("4"), "submitted", Decimal("0.001"))

    storage.update_auto_sell_run_status(run_id, "sold", "market sell filled")

    [run] = storage.list_auto_sell_runs()
    assert run["status"] == "sold"
    assert run["reason"] == "market sell filled"


def test_storage_counts_live_auto_sell_attempts_since(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    first = storage.record_auto_sell_run("BTCUSDT", "sell_submitted", Decimal("4"), "first", Decimal("0.001"), created_at_ms=1000)
    storage.record_auto_sell_run("ETHUSDT", "no_candidate", Decimal("0"), "none", Decimal("0"), created_at_ms=2000)
    storage.record_auto_sell_run("SOLUSDT", "sell_failed", Decimal("5"), "failed", Decimal("0.01"), created_at_ms=3000)
    storage.record_auto_sell_run("BNBUSDT", "reserved", Decimal("5"), "reserved", Decimal("0.1"), created_at_ms=4000)

    assert first == 1
    assert storage.count_auto_sell_attempts_since(0) == 3
    assert storage.count_auto_sell_attempts_since(2000) == 2


def test_storage_finds_recent_auto_sell_attempt_for_symbol(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_auto_sell_run("BTCUSDT", "sold", Decimal("4"), "old", Decimal("0.001"), created_at_ms=1000)
    storage.record_auto_sell_run("ETHUSDT", "sold", Decimal("4"), "other", Decimal("0.01"), created_at_ms=5000)
    storage.record_auto_sell_run("SOLUSDT", "reserved", Decimal("4"), "pending", Decimal("0.1"), created_at_ms=7000)

    assert storage.has_recent_auto_sell_attempt("BTCUSDT", since_ms=999)
    assert not storage.has_recent_auto_sell_attempt("BTCUSDT", since_ms=1001)
    assert storage.has_recent_auto_sell_attempt("SOLUSDT", since_ms=6999)


def test_storage_reserves_auto_sell_attempt_atomically(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    first = storage.reserve_auto_sell_attempt(
        symbol="BTCUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quantity=Decimal("0.001"),
        daily_limit=1,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=2000,
    )
    second = storage.reserve_auto_sell_attempt(
        symbol="ETHUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quantity=Decimal("0.01"),
        daily_limit=1,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=2001,
    )

    assert first.approved
    assert first.run_id == 1
    assert second.approved is False
    assert second.reason == "daily auto-sell limit reached"
    [run] = storage.list_auto_sell_runs()
    assert run["status"] == "reserved"


def test_storage_auto_sell_reservation_rejects_recent_symbol_attempt(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_auto_sell_run("BTCUSDT", "reserved", Decimal("4.5"), "selected", Decimal("0.001"), created_at_ms=2000)

    decision = storage.reserve_auto_sell_attempt(
        symbol="BTCUSDT",
        score=Decimal("4.5"),
        reason="selected",
        quantity=Decimal("0.001"),
        daily_limit=5,
        day_start_ms=1000,
        recent_since_ms=1000,
        created_at_ms=3000,
    )

    assert decision.approved is False
    assert decision.reason == "symbol cooldown active"
    [run] = storage.list_auto_sell_runs()
    assert run["status"] == "reserved"


def test_storage_rejects_auto_sell_event_with_missing_run_id(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(sqlite3.IntegrityError):
        storage.record_auto_sell_event(
            run_id=999,
            event_type="market_sell",
            symbol="BTCUSDT",
            client_order_id="bqas-missing-run",
            status="FILLED",
            raw={"orderId": 1},
        )


def test_storage_preserves_zero_created_at_for_auto_sell_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    storage.record_auto_sell_run("BTCUSDT", "sold", Decimal("4"), "selected", Decimal("0.001"), created_at_ms=0)

    [run] = storage.list_auto_sell_runs()
    assert run["created_at_ms"] == 0


def test_storage_preserves_zero_created_at_for_auto_sell_event(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_auto_sell_run("BTCUSDT", "sold", Decimal("4"), "selected", Decimal("0.001"))

    storage.record_auto_sell_event(
        run_id,
        "market_sell",
        "BTCUSDT",
        "bqas-zero-time",
        "FILLED",
        {"orderId": 1},
        created_at_ms=0,
    )

    [event] = storage.list_auto_sell_events(run_id)
    assert event["created_at_ms"] == 0


def test_storage_rejects_negative_auto_sell_run_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(ValueError, match="limit must be non-negative"):
        storage.list_auto_sell_runs(limit=-1)


def test_storage_records_spot_trade_fill_once_per_symbol_and_trade_id(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    trade = {
        "id": 42,
        "orderId": 123,
        "orderListId": -1,
        "price": "100.50",
        "qty": "0.01000000",
        "quoteQty": "1.00500000",
        "commission": "0.00001000",
        "commissionAsset": "BTC",
        "time": 1000,
        "isBuyer": True,
        "isMaker": False,
        "isBestMatch": True,
    }

    inserted = storage.record_spot_trade_fill("btcusdt", trade)
    duplicate = storage.record_spot_trade_fill("BTCUSDT", trade)

    assert inserted is True
    assert duplicate is False
    [row] = storage.list_spot_trade_fills("BTCUSDT")
    assert row["symbol"] == "BTCUSDT"
    assert row["trade_id"] == 42
    assert row["order_id"] == 123
    assert row["side"] == "BUY"
    assert row["price"] == "100.50"
    assert json.loads(row["raw_json"]) == trade


def test_storage_returns_latest_spot_trade_time_by_symbol(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_spot_trade_fill("BTCUSDT", _spot_trade(1, time_ms=1000))
    storage.record_spot_trade_fill("BTCUSDT", _spot_trade(2, time_ms=3000))
    storage.record_spot_trade_fill("ETHUSDT", _spot_trade(1, time_ms=2000))

    assert storage.latest_spot_trade_time_ms("BTCUSDT") == 3000
    assert storage.latest_spot_trade_time_ms("ETHUSDT") == 2000
    assert storage.latest_spot_trade_time_ms("SOLUSDT") is None


def test_storage_records_ai_trade_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    run_id = storage.record_ai_trade_run(
        symbol="BTCUSDT",
        action="HOLD",
        confidence=Decimal("0.42"),
        status="no_action",
        reason="mixed market",
        raw={"model": "codex", "score": Decimal("0.42")},
    )

    assert run_id == 1
    [run] = storage.list_ai_trade_runs()
    assert run["id"] == run_id
    assert run["symbol"] == "BTCUSDT"
    assert run["action"] == "HOLD"
    assert run["confidence"] == "0.42"
    assert run["status"] == "no_action"
    assert run["reason"] == "mixed market"
    assert run["auto_buy_run_id"] is None
    assert run["auto_sell_run_id"] is None
    assert json.loads(run["raw_json"]) == {"model": "codex", "score": "0.42"}


def test_storage_updates_ai_trade_run_status_and_links_downstream_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    auto_buy_run_id = storage.record_auto_buy_run("BTCUSDT", "protected", Decimal("0.88"), "AI: up", Decimal("5"))
    ai_run_id = storage.record_ai_trade_run(
        symbol="BTCUSDT",
        action="BUY",
        confidence=Decimal("0.88"),
        status="pending",
        reason="upside momentum",
        raw={"prediction": "BUY"},
    )

    storage.update_ai_trade_run_status(
        ai_run_id,
        status="protected",
        reason="AI: upside momentum; protective stop accepted",
        auto_buy_run_id=auto_buy_run_id,
        raw={"prediction": "BUY", "final": "protected"},
    )

    [run] = storage.list_ai_trade_runs()
    assert run["status"] == "protected"
    assert run["reason"] == "AI: upside momentum; protective stop accepted"
    assert run["auto_buy_run_id"] == auto_buy_run_id
    assert run["auto_sell_run_id"] is None
    assert json.loads(run["raw_json"]) == {"final": "protected", "prediction": "BUY"}


def test_storage_lists_ai_trade_runs_newest_first_with_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_ai_trade_run(None, "HOLD", Decimal("0"), "no_action", "first", created_at_ms=1000)
    storage.record_ai_trade_run("BTCUSDT", "BUY", Decimal("0.8"), "rejected", "second", created_at_ms=2000)
    storage.record_ai_trade_run("ETHUSDT", "SELL", Decimal("0.9"), "sold", "third", created_at_ms=3000)

    runs = storage.list_ai_trade_runs(limit=2)

    assert [run["reason"] for run in runs] == ["third", "second"]
    assert [run["id"] for run in runs] == [3, 2]


def test_storage_preserves_zero_created_at_for_ai_trade_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    storage.record_ai_trade_run(None, "HOLD", Decimal("0"), "rejected", "live mode required", created_at_ms=0)

    [run] = storage.list_ai_trade_runs()
    assert run["created_at_ms"] == 0


def test_storage_rejects_negative_ai_trade_run_limit(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    with pytest.raises(ValueError, match="limit must be non-negative"):
        storage.list_ai_trade_runs(limit=-1)


def test_storage_records_futures_ai_trade_run(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()

    run_id = storage.record_futures_ai_trade_run(
        symbol="BTCUSDT",
        action="OPEN_LONG",
        confidence=Decimal("0.82"),
        status="predicted",
        reason="trend",
        leverage=3,
        margin_amount=Decimal("5"),
        stop_loss_price=Decimal("59000"),
        take_profit_price=Decimal("63000"),
        close_percent=None,
        raw={"confidence": Decimal("0.82")},
        created_at_ms=0,
    )

    [run] = storage.list_futures_ai_trade_runs()
    assert run_id == 1
    assert run["created_at_ms"] == 0
    assert run["symbol"] == "BTCUSDT"
    assert run["action"] == "OPEN_LONG"
    assert run["confidence"] == "0.82"
    assert run["leverage"] == 3
    assert run["margin_amount"] == "5"
    assert run["stop_loss_price"] == "59000"
    assert run["take_profit_price"] == "63000"
    assert run["close_percent"] is None
    assert json.loads(run["raw_json"]) == {"confidence": "0.82"}


def test_storage_records_futures_order_event_and_position_snapshot(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_futures_ai_trade_run(
        "BTCUSDT",
        "OPEN_LONG",
        Decimal("0.82"),
        "predicted",
        "trend",
        raw={},
    )

    event_id = storage.record_futures_order_event(
        run_id=run_id,
        event_type="stop_algo",
        symbol="BTCUSDT",
        client_order_id=None,
        client_algo_id="bqf-stop",
        status="NEW",
        raw={"algoId": 123},
        created_at_ms=0,
    )
    snapshot_id = storage.record_futures_position_snapshot(
        run_id=run_id,
        symbol="BTCUSDT",
        side="LONG",
        quantity=Decimal("0.002"),
        entry_price=Decimal("60000"),
        mark_price=Decimal("60100"),
        liquidation_price=Decimal("55000"),
        isolated_margin=Decimal("5"),
        unrealized_pnl=Decimal("0.2"),
        created_at_ms=0,
    )

    assert event_id == 1
    assert snapshot_id == 1
    [event] = storage.list_futures_order_events(run_id)
    assert event["event_type"] == "stop_algo"
    assert event["client_algo_id"] == "bqf-stop"
    [snapshot] = storage.list_futures_position_snapshots(run_id)
    assert snapshot["side"] == "LONG"
    assert snapshot["quantity"] == "0.002"


def test_storage_detects_recent_futures_close_for_cooldown(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    storage.record_futures_ai_trade_run(
        "BTCUSDT",
        "CLOSE",
        Decimal("0.82"),
        "closed",
        "closed",
        raw={},
        created_at_ms=1_000,
    )
    storage.record_futures_ai_trade_run(
        "ETHUSDT",
        "CLOSE",
        Decimal("0.82"),
        "rejected",
        "no position",
        raw={},
        created_at_ms=1_500,
    )
    storage.record_futures_ai_trade_run(
        "BNBUSDT",
        "OPEN_LONG",
        Decimal("0.82"),
        "protected",
        "protected",
        raw={},
        created_at_ms=1_500,
    )

    assert storage.has_recent_futures_close("BTCUSDT", 900) is True
    assert storage.has_recent_futures_close("BTCUSDT", 1_100) is False
    assert storage.has_recent_futures_close("ETHUSDT", 900) is False
    assert storage.has_recent_futures_close("BNBUSDT", 900) is False


def test_storage_updates_futures_ai_trade_run_status(tmp_path):
    storage = Storage(tmp_path / "audit.sqlite3")
    storage.initialize()
    run_id = storage.record_futures_ai_trade_run("BTCUSDT", "OPEN_LONG", Decimal("0.82"), "predicted", "trend", raw={})

    storage.update_futures_ai_trade_run_status(run_id, "protected", "protected", raw={"final": "protected"})

    [run] = storage.list_futures_ai_trade_runs()
    assert run["status"] == "protected"
    assert run["reason"] == "protected"
    assert json.loads(run["raw_json"]) == {"final": "protected"}


def _order_result(client_order_id: str, raw: dict | None = None) -> OrderResult:
    return OrderResult(
        symbol="BTCUSDT",
        side=OrderSide.BUY,
        client_order_id=client_order_id,
        status="FILLED",
        raw=raw or {"orderId": client_order_id},
        dry_run=True,
    )


def _spot_trade(trade_id: int, *, time_ms: int) -> dict:
    return {
        "id": trade_id,
        "orderId": trade_id + 100,
        "orderListId": -1,
        "price": "100",
        "qty": "0.01",
        "quoteQty": "1",
        "commission": "0.00001",
        "commissionAsset": "BTC",
        "time": time_ms,
        "isBuyer": True,
        "isMaker": False,
        "isBestMatch": True,
    }