from __future__ import annotations

import sqlite3
from uuid import uuid4

from hermes_mcp.store import (
    DB_PATH,
    delete_concern,
    init_db,
    upsert_concern,
    upsert_cycle,
    upsert_decision,
    upsert_narrative,
    upsert_observation,
    upsert_regime_sample,
    upsert_state,
)


def _count(table: str, value: str, column: str = "concern_id") -> int:
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        row = conn.execute(f"select count(*) as n from {table} where {column} = ?", (value,)).fetchone()
        return int(row["n"] if row else 0)


def test_delete_concern_purges_related_rows():
    init_db()
    concern_id = f"test:{uuid4().hex}"
    cycle_id = f"cycle:{uuid4().hex}"
    decision_id = f"decision:{uuid4().hex}"
    action_id = f"action:{uuid4().hex}"

    upsert_concern(
        id=concern_id,
        account_id="acct-1",
        market_symbol="xrpusd",
        base_currency="XRP",
        quote_currency="USD",
        strategy_id="trend-1",
        source="test",
        status="active",
        notes="cleanup target",
    )
    upsert_cycle(id=cycle_id, started_at="2026-04-19T00:00:00+00:00", finished_at=None, status="ok", trigger="test")
    upsert_observation(id=f"obs:{uuid4().hex}", cycle_id=cycle_id, concern_id=concern_id, source="test", kind="snapshot", payload_json="{}")
    upsert_state(
        id=f"state:{uuid4().hex}",
        cycle_id=cycle_id,
        concern_id=concern_id,
        market_regime="bull",
        volatility_state="normal",
        liquidity_state="good",
        sentiment_pressure="neutral",
        event_risk="low",
        execution_quality="good",
        confidence=0.9,
        payload_json="{}",
    )
    upsert_narrative(
        id=f"narr:{uuid4().hex}",
        cycle_id=cycle_id,
        concern_id=concern_id,
        summary="cleanup target",
        key_drivers_json="[]",
        risk_flags_json="[]",
        uncertainties_json="[]",
        confidence=0.8,
    )
    upsert_decision(
        id=decision_id,
        cycle_id=cycle_id,
        concern_id=concern_id,
        action="replace_with_grid",
        target_strategy="grid-1",
        target_policy_json="{}",
        reason_summary="cleanup target",
        confidence=0.7,
        requires_action=True,
    )
    upsert_regime_sample(id=f"regime:{uuid4().hex}", cycle_id=cycle_id, concern_id=concern_id, timeframe="1h", regime_json="{}", captured_at="2026-04-19T00:00:00+00:00")
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute(
            "insert into actions(id, decision_id, target, command, request_json, response_json, status, executed_at) values(?, ?, ?, ?, ?, ?, ?, ?)",
            (action_id, decision_id, "trader", "switch", "{}", None, "pending", None),
        )
        conn.commit()

    deleted = delete_concern(concern_id=concern_id)
    assert deleted["concerns"] == 1
    assert deleted["decisions"] == 1
    assert deleted["actions"] == 1
    assert deleted["observations"] == 1
    assert deleted["states"] == 1
    assert deleted["narratives"] == 1
    assert deleted["regime_samples"] == 1

    assert _count("concerns", concern_id, "id") == 0
    for table in ("observations", "states", "narratives", "decisions", "coverage_gaps", "regime_samples"):
        assert _count(table, concern_id) == 0
    assert _count("actions", decision_id, "decision_id") == 0