hermes-mcp/tests/test_concern_cleanup.py

from __future__ import annotations

import sqlite3
from uuid import uuid4

from hermes_mcp.store import (
    DB_PATH,
    delete_concern,
    init_db,
    prune_older_than_hours,
    upsert_decision_profile,
    upsert_concern,
    upsert_cycle,
    upsert_decision,
    upsert_strategy_assignment,
    upsert_strategy_group,
    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 _total_count(table: str) -> int:
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        row = conn.execute(f"select count(*) as n from {table}").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


def test_prune_older_than_hours_keeps_concerns_and_config_tables():
    init_db()
    concern_id = f"test:{uuid4().hex}"
    cycle_id = f"cycle:{uuid4().hex}"
    decision_id = f"decision:{uuid4().hex}"
    profile_id = f"profile:{uuid4().hex}"
    group_id = f"group:{uuid4().hex}"
    assignment_id = f"assignment:{uuid4().hex}"
    old_at = "2026-04-24T00:00:00+00:00"

    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="preserve me",
    )
    upsert_decision_profile(id=profile_id, name="Config profile", config={"keep": True})
    upsert_strategy_group(id=group_id, concern_id=concern_id, name="Config group", strategy_family="mixed", decision_profile_id=profile_id)
    upsert_strategy_assignment(id=assignment_id, strategy_group_id=group_id, strategy_id="trend-1", strategy_type="trend_follower", role="primary")
    upsert_cycle(id=cycle_id, started_at=old_at, 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="{}", observed_at=old_at)
    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="{}",
        created_at=old_at,
    )
    upsert_narrative(
        id=f"narr:{uuid4().hex}",
        cycle_id=cycle_id,
        concern_id=concern_id,
        summary="old",
        key_drivers_json="[]",
        risk_flags_json="[]",
        uncertainties_json="[]",
        confidence=0.8,
        created_at=old_at,
    )
    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="old",
        confidence=0.7,
        requires_action=True,
        created_at=old_at,
    )
    upsert_regime_sample(id=f"regime:{uuid4().hex}", cycle_id=cycle_id, concern_id=concern_id, timeframe="1h", regime_json="{}", captured_at=old_at)
    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(?, ?, ?, ?, ?, ?, ?, ?)",
            (f"action:{uuid4().hex}", decision_id, "trader", "switch", "{}", None, "done", old_at),
        )
        conn.commit()

    before_counts = {
        "cycles": _total_count("cycles"),
        "decisions": _total_count("decisions"),
        "actions": _total_count("actions"),
        "observations": _total_count("observations"),
        "states": _total_count("states"),
        "narratives": _total_count("narratives"),
        "coverage_gaps": _total_count("coverage_gaps"),
        "regime_samples": _total_count("regime_samples"),
    }

    deleted = prune_older_than_hours(24)

    assert deleted["cycles"] == before_counts["cycles"] - _total_count("cycles")
    assert deleted["decisions"] == before_counts["decisions"] - _total_count("decisions")
    assert deleted["actions"] == before_counts["actions"] - _total_count("actions")
    assert deleted["observations"] == before_counts["observations"] - _total_count("observations")
    assert deleted["states"] == before_counts["states"] - _total_count("states")
    assert deleted["narratives"] == before_counts["narratives"] - _total_count("narratives")
    assert deleted["coverage_gaps"] == before_counts["coverage_gaps"] - _total_count("coverage_gaps")
    assert deleted["regime_samples"] == before_counts["regime_samples"] - _total_count("regime_samples")
    assert "concerns" not in deleted
    assert "decision_profiles" not in deleted
    assert "strategy_groups" not in deleted
    assert "strategy_assignments" not in deleted

    assert _count("concerns", concern_id, "id") == 1
    assert _count("decision_profiles", profile_id, "id") == 1
    assert _count("strategy_groups", group_id, "id") == 1
    assert _count("strategy_assignments", assignment_id, "id") == 1
    assert _count("decisions", concern_id) == 0
    assert _count("observations", concern_id) == 0
    assert _count("states", concern_id) == 0
    assert _count("narratives", concern_id) == 0
    assert _count("regime_samples", concern_id) == 0