"""trading_brain.py 鈥?Unified learning database for the GoldAutoTrade system.

Phase 1 provides the foundation: SQLite storage for trade events and agent
judgments, with a simple query API that bridges data production to consumption.

Tables:
  trade_events     鈥?Every closed trade with 50+ entry features + outcome label
  agent_judgments  鈥?Every AI macro direction judgment + post-hoc validation

Design:
  - WAL mode for concurrent read/write
  - Singleton pattern (get_brain)
  - Non-blocking writes (exceptions are logged, never crash the caller)
  - All queries return sensible defaults on empty data

Usage::

    from src.learning.trading_brain import get_brain

    brain = get_brain()
    brain.record_trade(features, outcome)
    stats = brain.query_win_rate(agent_aligned=1)
"""

from __future__ import annotations

import json
import logging
import os
import sqlite3
import threading
import time as _time
from pathlib import Path
from typing import Any

import config_manager

logger = logging.getLogger(__name__)


# 鈹€鈹€ Paths 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

def _brain_db_path() -> Path:
    d = config_manager.get_data_dir() / "learning"
    d.mkdir(parents=True, exist_ok=True)
    return d / "trading_brain.db"


# 鈹€鈹€ SQL DDL 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

_DDL_TRADE_EVENTS = """
CREATE TABLE IF NOT EXISTS trade_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket          INTEGER DEFAULT 0,
    decision_id     TEXT DEFAULT '',
    signal_id       TEXT DEFAULT '',

    -- entry features
    opened_at       TEXT DEFAULT '',
    strategy_id     TEXT DEFAULT '',
    strategy_trust  TEXT DEFAULT '',
    side            TEXT DEFAULT '',
    confidence      REAL DEFAULT 0.0,
    entry_price     REAL DEFAULT 0.0,
    sl_price        REAL DEFAULT 0.0,
    tp_price        REAL DEFAULT 0.0,
    volume_lots     REAL DEFAULT 0.0,
    session         TEXT DEFAULT '',
    regime          TEXT DEFAULT '',
    adx_value       REAL DEFAULT 0.0,
    atr_value       REAL DEFAULT 0.0,
    vol_ratio       REAL DEFAULT 1.0,
    spread          REAL DEFAULT 0.0,

    -- AI judgment features
    agent_direction TEXT DEFAULT '',
    agent_confidence REAL DEFAULT 0.0,
    agent_aligned   INTEGER DEFAULT 0,
    macro_authority TEXT DEFAULT '',
    direction_quality TEXT DEFAULT '',

    -- strategy state at entry
    strategy_win_rate_20    REAL DEFAULT 0.0,
    strategy_profit_factor   REAL DEFAULT 0.0,
    strategy_loss_streak     INTEGER DEFAULT 0,

    -- risk state at entry
    risk_level      TEXT DEFAULT '',
    cooldown_active INTEGER DEFAULT 0,
    daily_loss_pct  REAL DEFAULT 0.0,

    -- outcome (label)
    closed_at       TEXT DEFAULT '',
    pnl             REAL DEFAULT 0.0,
    pnl_atr         REAL DEFAULT 0.0,
    is_win          INTEGER DEFAULT 0,
    sl_hit          INTEGER DEFAULT 0,
    tp_hit          INTEGER DEFAULT 0,
    duration_min    INTEGER DEFAULT 0,

    -- post-trade analysis
    exit_reason     TEXT DEFAULT '',
    peak_profit_atr REAL DEFAULT 0.0,
    max_adverse_atr REAL DEFAULT 0.0,
    capture_ratio   REAL DEFAULT 0.0,

    created_at      TEXT DEFAULT (datetime('now'))
)
"""

_DDL_TRADE_INDEXES = [
    "CREATE INDEX IF NOT EXISTS idx_te_decision ON trade_events(decision_id)",
    "CREATE INDEX IF NOT EXISTS idx_te_strategy ON trade_events(strategy_id, opened_at)",
    "CREATE INDEX IF NOT EXISTS idx_te_regime_win ON trade_events(regime, is_win)",
    "CREATE INDEX IF NOT EXISTS idx_te_agent ON trade_events(agent_direction, agent_aligned, is_win)",
    "CREATE INDEX IF NOT EXISTS idx_te_session ON trade_events(session, is_win)",
    "CREATE INDEX IF NOT EXISTS idx_te_win ON trade_events(is_win, strategy_id, regime)",
    "CREATE INDEX IF NOT EXISTS idx_te_opened ON trade_events(opened_at)",
]

_DDL_AGENT_JUDGMENTS = """
CREATE TABLE IF NOT EXISTS agent_judgments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    judged_at       TEXT NOT NULL,
    direction       TEXT NOT NULL DEFAULT '',
    confidence      REAL DEFAULT 0.0,
    probability     REAL DEFAULT 0.0,

    -- judgment context
    reason          TEXT DEFAULT '',
    source          TEXT DEFAULT '',
    degraded        INTEGER DEFAULT 0,

    -- market state at judgment
    regime          TEXT DEFAULT '',
    adx_value       REAL DEFAULT 0.0,
    vol_ratio       REAL DEFAULT 1.0,
    dxy_correlation REAL DEFAULT 0.0,
    ema_bias        TEXT DEFAULT '',

    -- post-hoc validation (updated later)
    validated_at    TEXT DEFAULT '',
    direction_correct INTEGER DEFAULT -1,
    validation_method TEXT DEFAULT '',
    price_drift_pct REAL DEFAULT 0.0,
    aligned_win_rate REAL DEFAULT -1.0,
    counter_win_rate REAL DEFAULT -1.0,

    -- lifecycle
    valid_until     TEXT DEFAULT '',
    expired         INTEGER DEFAULT 0,
    disempowered    INTEGER DEFAULT 0,

    created_at      TEXT DEFAULT (datetime('now'))
)
"""

_DDL_JUDGMENT_INDEXES = [
    "CREATE INDEX IF NOT EXISTS idx_aj_time ON agent_judgments(judged_at)",
    "CREATE INDEX IF NOT EXISTS idx_aj_correct ON agent_judgments(direction_correct, direction)",
    "CREATE INDEX IF NOT EXISTS idx_aj_dir ON agent_judgments(direction, validated_at)",
]

_DDL_REGIME_TRANSITIONS = """
CREATE TABLE IF NOT EXISTS regime_transitions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    from_regime     TEXT NOT NULL DEFAULT '',
    to_regime       TEXT NOT NULL DEFAULT '',
    trigger_time    TEXT NOT NULL DEFAULT '',
    confidence      REAL DEFAULT 0.0,
    return_1h       REAL DEFAULT 0.0,
    return_4h       REAL DEFAULT 0.0,
    return_8h       REAL DEFAULT 0.0,
    is_valid        INTEGER DEFAULT -1,
    created_at      TEXT DEFAULT (datetime('now'))
)
"""

_DDL_REGIME_TRANSITION_INDEXES = [
    "CREATE INDEX IF NOT EXISTS idx_rt_time ON regime_transitions(trigger_time)",
    "CREATE INDEX IF NOT EXISTS idx_rt_from_to ON regime_transitions(from_regime, to_regime)",
    "CREATE INDEX IF NOT EXISTS idx_rt_valid ON regime_transitions(is_valid)",
]


_DDL_PROMPT_PERSPECTIVE_STATS = """CREATE TABLE IF NOT EXISTS prompt_perspective_stats (
    perspective TEXT NOT NULL,
    regime TEXT NOT NULL DEFAULT "",
    n INTEGER DEFAULT 0,
    correct INTEGER DEFAULT 0,
    accuracy REAL DEFAULT 0.0,
    last_updated TEXT DEFAULT "",
    PRIMARY KEY (perspective, regime)
)"""


# 鈹€鈹€ Singleton 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

_BRAIN: TradingBrain | None = None
_LOCK = threading.Lock()


def get_brain(db_path: str | Path | None = None) -> "TradingBrain":
    global _BRAIN
    with _LOCK:
        if _BRAIN is None:
            _BRAIN = TradingBrain(db_path)
        return _BRAIN


# 鈹€鈹€ Brain 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€


_CONDITION_COL_MAP = {
    "regime": "regime",
    "session": "session",
    "side": "side",
    "agent_direction": "agent_direction",
    "agent_aligned": "agent_aligned",
    "strategy_id": "strategy_id",
    "vol_bucket": "vol_bucket",
    "risk_level": "risk_level",
    "macro_authority": "macro_authority",
    "exit_reason": "exit_reason",
}

class TradingBrain:
    """Unified learning database for trade events and AI judgments."""

    def __init__(self, db_path: str | Path | None = None):
        self._path = Path(db_path) if db_path else _brain_db_path()
        self._local = threading.local()
        self._init_db()

    @property
    def _conn(self) -> sqlite3.Connection:
        if not hasattr(self._local, "conn") or self._local.conn is None:
            self._local.conn = sqlite3.connect(str(self._path), timeout=10.0)
            self._local.conn.execute("PRAGMA journal_mode=WAL")
            self._local.conn.execute("PRAGMA synchronous=NORMAL")
            self._local.conn.execute("PRAGMA foreign_keys=OFF")
            self._local.conn.row_factory = sqlite3.Row
        return self._local.conn

    def _init_db(self) -> None:
        try:
            conn = self._conn
            conn.execute(_DDL_TRADE_EVENTS)
            conn.execute(_DDL_AGENT_JUDGMENTS)
            conn.execute(_DDL_REGIME_TRANSITIONS)
            # Migration: add perspective + voting_round_id columns
            for _col in ("perspective", "voting_round_id"):
                try:
                    conn.execute("ALTER TABLE agent_judgments ADD COLUMN " + _col + " TEXT DEFAULT ''")
                except Exception:
                    pass
            conn.execute(_DDL_PROMPT_PERSPECTIVE_STATS)
            for idx_sql in _DDL_TRADE_INDEXES + _DDL_JUDGMENT_INDEXES + _DDL_REGIME_TRANSITION_INDEXES:
                try:
                    conn.execute(idx_sql)
                except sqlite3.OperationalError:
                    pass
            conn.commit()
        except Exception:
            pass

    def close(self) -> None:
        if hasattr(self._local, "conn") and self._local.conn is not None:
            try:
                self._local.conn.close()
            except Exception:
                pass
            self._local.conn = None

    # 鈹€鈹€ Write API 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

    def _find_existing_trade_id(self, values: dict[str, Any]) -> int | None:
        decision_id = str(values.get("decision_id", "") or "").strip()
        signal_id = str(values.get("signal_id", "") or "").strip()
        opened_at = str(values.get("opened_at", "") or "").strip()
        strategy_id = str(values.get("strategy_id", "") or "").strip()
        closed_at = str(values.get("closed_at", "") or "").strip()
        side = str(values.get("side", "") or "").strip().upper()
        ticket = int(values.get("ticket", 0) or 0)

        if decision_id and closed_at:
            row = self._conn.execute(
                "SELECT id FROM trade_events WHERE decision_id=? AND closed_at=? LIMIT 1",
                (decision_id, closed_at),
            ).fetchone()
            if row:
                return int(row["id"])

        if signal_id and closed_at:
            row = self._conn.execute(
                "SELECT id FROM trade_events WHERE signal_id=? AND closed_at=? LIMIT 1",
                (signal_id, closed_at),
            ).fetchone()
            if row:
                return int(row["id"])

        if ticket > 0 and closed_at:
            row = self._conn.execute(
                "SELECT id FROM trade_events WHERE ticket=? AND closed_at=? AND side=? LIMIT 1",
                (ticket, closed_at, side),
            ).fetchone()
            if row:
                return int(row["id"])

        if strategy_id and opened_at and closed_at:
            row = self._conn.execute(
                "SELECT id FROM trade_events WHERE strategy_id=? AND opened_at=? AND closed_at=? AND side=? LIMIT 1",
                (strategy_id, opened_at, closed_at, side),
            ).fetchone()
            if row:
                return int(row["id"])

        return None

    def record_trade(self, entry_features: dict[str, Any], outcome: dict[str, Any]) -> int | None:
        """Insert a completed trade into trade_events. Non-blocking.

        Args:
            entry_features: Dict with keys matching trade_events columns
                (strategy_id, side, confidence, regime, session, adx_value,
                 atr_value, vol_ratio, spread, agent_direction, agent_aligned, ...)
            outcome: Dict with keys (pnl, pnl_atr, is_win, sl_hit, tp_hit,
                duration_min, exit_reason, capture_ratio, ...)

        Returns row id on success, None on failure.
        """
        ef = {k: v for k, v in (entry_features or {}).items()}
        oc = {k: v for k, v in (outcome or {}).items()}

        columns = [
            "ticket", "decision_id", "signal_id",
            "opened_at", "strategy_id", "strategy_trust", "side",
            "confidence", "entry_price", "sl_price", "tp_price", "volume_lots",
            "session", "regime", "adx_value", "atr_value", "vol_ratio", "spread",
            "agent_direction", "agent_confidence", "agent_aligned",
            "macro_authority", "direction_quality",
            "strategy_win_rate_20", "strategy_profit_factor", "strategy_loss_streak",
            "risk_level", "cooldown_active", "daily_loss_pct",
            "closed_at", "pnl", "pnl_atr", "is_win", "sl_hit", "tp_hit",
            "duration_min", "exit_reason", "peak_profit_atr", "max_adverse_atr",
            "capture_ratio",
        ]

        try:
            values = {}
            for col in columns:
                val = ef.get(col) if col in ef else oc.get(col)
                if val is None:
                    val = oc.get(col)
                values[col] = val

            if (
                int(values.get("ticket", 0) or 0) <= 0
                and not str(values.get("decision_id", "") or "").strip()
                and not str(values.get("signal_id", "") or "").strip()
                and not str(values.get("opened_at", "") or "").strip()
            ):
                return None

            existing_id = self._find_existing_trade_id(values)
            if existing_id is not None:
                return existing_id

            placeholders = ", ".join(f":{c}" for c in columns)
            sql = f"INSERT INTO trade_events ({', '.join(columns)}) VALUES ({placeholders})"
            cur = self._conn.execute(sql, values)
            self._conn.commit()
            return int(cur.lastrowid)
        except Exception as e:
            return None


    def get_strategy_win_rate(self, strategy_id: str, regime: str = "", limit: int = 20) -> dict[str, Any]:
        """Query recent win rate and profit factor for a strategy-regime pair.
        Returns {win_rate, profit_factor, trade_count, is_cold, pnl_list}.
        is_cold = True when win_rate < 0.40 in last 20 trades (or limit trades)."""
        try:
            where = "WHERE strategy_id = ?"
            params: list[Any] = [strategy_id]
            if regime and regime != "UNKNOWN":
                where += " AND regime = ?"
                params.append(regime)

            sql = f"""
                SELECT pnl, opened_at, exit_reason
                FROM trade_events
                {where}
                ORDER BY opened_at DESC
                LIMIT ?
            """
            params.append(limit)
            rows = self._conn.execute(sql, params).fetchall()
            count = len(rows)
            if count < 3:
                return {"win_rate": 0.5, "profit_factor": 1.0, "trade_count": count, "is_cold": False, "pnl_list": []}

            pnls = [row["pnl"] for row in rows]
            wins = sum(1 for p in pnls if p > 0)
            win_rate = wins / count
            avg_win = sum(p for p in pnls if p > 0) / max(1, wins)
            avg_loss = abs(sum(p for p in pnls if p <= 0)) / max(1, count - wins)
            pf = avg_win / max(0.01, avg_loss)
            is_cold = win_rate < 0.40 and count >= 5
            return {"win_rate": round(win_rate, 4), "profit_factor": round(pf, 4),
                    "trade_count": count, "is_cold": is_cold, "pnl_list": pnls}
        except Exception:
            return {"win_rate": 0.5, "profit_factor": 1.0, "trade_count": 0, "is_cold": False, "pnl_list": []}

    def record_judgment(self, judgment: dict[str, Any]) -> int | None:
        """Insert an AI macro direction judgment. Non-blocking.

        Args:
            judgment: Dict with keys (direction, confidence, reason, source,
                regime, adx_value, vol_ratio, dxy_correlation, ema_bias, ...)
        """
        fields = [
            "judged_at", "direction", "confidence", "probability",
            "reason", "source", "degraded",
            "regime", "adx_value", "vol_ratio", "dxy_correlation", "ema_bias",
            "valid_until", "expired", "disempowered",
        ]
        try:
            vals = {}
            for f in fields:
                v = judgment.get(f)
                if v is not None:
                    vals[f] = v
            if "judged_at" not in vals:
                vals["judged_at"] = _time.strftime("%Y-%m-%dT%H:%M:%S", _time.gmtime())
            placeholders = ", ".join(f":{f}" for f in fields if f in vals)
            cols = ", ".join(f for f in fields if f in vals)
            sql = f"INSERT INTO agent_judgments ({cols}) VALUES ({placeholders})"
            cur = self._conn.execute(sql, vals)
            self._conn.commit()
            return cur.lastrowid
        except Exception:
            return None

    def validate_judgment(
        self,
        judged_at: str,
        direction_correct: bool,
        price_drift_pct: float = 0.0,
        aligned_win_rate: float | None = None,
        counter_win_rate: float | None = None,
    ) -> bool:
        """Update a judgment record with post-hoc validation result."""
        try:
            updates = {
                "validated_at": _time.strftime("%Y-%m-%dT%H:%M:%S", _time.gmtime()),
                "direction_correct": 1 if direction_correct else 0,
                "price_drift_pct": round(float(price_drift_pct or 0), 4),
                "validation_method": "price_drift",
            }
            if aligned_win_rate is not None:
                updates["aligned_win_rate"] = round(float(aligned_win_rate), 4)
            if counter_win_rate is not None:
                updates["counter_win_rate"] = round(float(counter_win_rate), 4)

            set_clause = ", ".join(f"{k}=:{k}" for k in updates)
            sql = f"UPDATE agent_judgments SET {set_clause} WHERE judged_at=:ref_ts AND validated_at=''"
            updates["ref_ts"] = judged_at
            self._conn.execute(sql, updates)
            self._conn.commit()
            # Auto-update perspective accuracy stats
            try:
                _pr = self._conn.execute("SELECT perspective FROM agent_judgments WHERE judged_at=:ts", {"ts": judged_at}).fetchone()
                if _pr and str(_pr["perspective"] or "").strip():
                    _dc = 1 if direction_correct else 0
                    _p = str(_pr["perspective"])
                    self._conn.execute(
                        "INSERT INTO prompt_perspective_stats "
                        "(perspective, regime, n, correct, accuracy, last_updated) "
                        "VALUES (:p, :r, 1, :dc, :dc, :lu) "
                        "ON CONFLICT(perspective, regime) DO UPDATE SET "
                        "n = n + 1, correct = correct + :dc2, "
                        "accuracy = ROUND(CAST(correct + :dc3 AS REAL) / (n + 1), 4), "
                        "last_updated = :lu2",
                        {"p": _p, "r": "", "dc": _dc, "dc2": _dc, "dc3": _dc,
                         "lu": updates["validated_at"], "lu2": updates["validated_at"]},
                    )
                    self._conn.commit()
            except Exception:
                pass
            return True
        except Exception:
            return False

    def auto_validate_latest_judgment(
        self,
        *,
        trade_side: str = "",
        pnl: float = 0.0,
        agent_direction: str = "",
        price_drift_pct: float = 0.0,
        aligned_win_rate: float | None = None,
        counter_win_rate: float | None = None,
    ) -> bool:
        """Validate unvalidated judgments using price drift as primary signal.

        Price drift is the ground truth: LONG + positive drift = correct,
        SHORT + negative drift = correct. When drift is unavailable (old data),
        falls back to PnL-based proxy.

        Validates ALL unvalidated judgments with matching direction, not just
        the latest one 鈥?no more permanently orphaned judgments.
        """
        try:
            direction = str(agent_direction or "").upper()
            side = str(trade_side or "").upper()
            if direction not in {"LONG", "SHORT"}:
                return False

            rows = self._conn.execute(
                """
                SELECT judged_at, direction
                FROM agent_judgments
                WHERE validated_at='' AND direction=?
                ORDER BY judged_at DESC
                """,
                (direction,),
            ).fetchall()
            if not rows:
                return False

            # Determine correctness from price drift (primary) or PnL (fallback)
            drift = float(price_drift_pct or 0.0)
            if abs(drift) > 0.0001:
                direction_correct = (direction == "LONG" and drift > 0) or (direction == "SHORT" and drift < 0)
            else:
                side_upper = str(side or "").upper()
                if side_upper not in {"BUY", "SELL"}:
                    return False
                aligned = (side_upper == "BUY" and direction == "LONG") or (side_upper == "SELL" and direction == "SHORT")
                realized_win = float(pnl or 0.0) > 0.0
                direction_correct = realized_win if aligned else not realized_win

            validated_count = 0
            for row in rows:
                if self.validate_judgment(
                    str(row["judged_at"]),
                    direction_correct=direction_correct,
                    price_drift_pct=drift,
                    aligned_win_rate=aligned_win_rate,
                    counter_win_rate=counter_win_rate,
                ):
                    validated_count += 1

            return validated_count > 0
        except Exception:
            return False

    def validate_stale_judgments(self, current_price: float, max_age_hours: int = 8) -> int:
        """Timeout stale unvalidated judgments older than max_age_hours.

        Judgments sitting unvalidated for too long are marked as timed out
        (direction_correct=-1, validation_method='timeout') to prevent
        indefinite accumulation of orphaned records.
        """
        try:
            import datetime as _dt
            cutoff = (_dt.datetime.utcnow() - _dt.timedelta(hours=max_age_hours)).strftime(
                "%Y-%m-%dT%H:%M:%S"
            )
            rows = self._conn.execute(
                """
                SELECT judged_at FROM agent_judgments
                WHERE validated_at='' AND judged_at < ?
                """,
                (cutoff,),
            ).fetchall()
            count = 0
            for row in rows:
                self._conn.execute(
                    """
                    UPDATE agent_judgments
                    SET validated_at=:now, direction_correct=-1, validation_method='timeout'
                    WHERE judged_at=:ts AND validated_at=''
                    """,
                    {
                        "now": _dt.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S"),
                        "ts": str(row["judged_at"]),
                    },
                )
                count += 1
            if count:
                self._conn.commit()
            return count
        except Exception:
            return 0

    # 鈹€鈹€ Query API 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

    def record_judgment_vote(
        self,
        judgment: dict[str, object],
        perspective: str,
        voting_round_id: str,
    ) -> int | None:
        """Record a voting-perspective judgment with tracking fields."""
        j = dict(judgment)
        j["perspective"] = str(perspective or "")[:20]
        j["voting_round_id"] = str(voting_round_id or "")
        return self.record_judgment(j)

    def update_perspective_stats(
        self,
        perspective: str,
        regime: str,
        correct: int,
    ) -> bool:
        """Manually update accuracy stats for a perspective-regime pair."""
        try:
            import datetime as _dt
            now = _dt.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S")
            self._conn.execute(
                "INSERT INTO prompt_perspective_stats "
                "(perspective, regime, n, correct, accuracy, last_updated) "
                "VALUES (:p, :r, 1, :c, :c, :now) "
                "ON CONFLICT(perspective, regime) DO UPDATE SET "
                "n = n + 1, correct = correct + :c2, "
                "accuracy = ROUND(CAST(correct + :c3 AS REAL) / (n + 1), 4), "
                "last_updated = :now2",
                {"p": str(perspective), "r": str(regime), "c": correct,
                 "c2": correct, "c3": correct, "now": now, "now2": now},
            )
            self._conn.commit()
            return True
        except Exception:
            return False

    def get_perspective_weights(
        self,
        regime: str = "",
        min_samples: int = 5,
    ) -> dict[str, float]:
        """Voting weights per perspective in a regime."""
        try:
            rows = self._conn.execute(
                "SELECT perspective, accuracy, n FROM prompt_perspective_stats "
                "WHERE (regime = :r OR regime = '') AND n >= :min ORDER BY accuracy DESC",
                {"r": str(regime or ""), "min": min_samples},
            ).fetchall()
            if not rows:
                return {"technical": 1.0, "macro": 1.0, "feedback": 1.0}
            avg_acc = sum(float(r["accuracy"]) for r in rows) / len(rows)
            result = {}
            for row in rows:
                p = str(row["perspective"])
                result[p] = round(1.0 + (float(row["accuracy"]) - avg_acc) * 2.0, 4)
            for p in ("technical", "macro", "feedback"):
                if p not in result:
                    result[p] = 1.0
            return result
        except Exception:
            return {"technical": 1.0, "macro": 1.0, "feedback": 1.0}

    def query_win_rate(self, **conditions: Any) -> dict[str, Any]:
        """Query win rate under given column conditions.

        Example:
            brain.query_win_rate(agent_aligned=1, regime="TRENDING")

        Returns {"n": int, "wins": int, "losses": int, "win_rate": float,
                 "avg_pnl": float, "avg_pnl_atr": float}
        """
        try:
            where_parts = []
            params: dict[str, Any] = {}
            for col, val in conditions.items():
                if val is not None:
                    where_parts.append(f"{col}=:c_{col}")
                    params[f"c_{col}"] = val

            where = ("WHERE " + " AND ".join(where_parts)) if where_parts else ""
            sql = f"""
                SELECT COUNT(*) AS n,
                       SUM(CASE WHEN is_win=1 THEN 1 ELSE 0 END) AS wins,
                       SUM(CASE WHEN is_win=0 THEN 1 ELSE 0 END) AS losses,
                       ROUND(AVG(CASE WHEN is_win=1 THEN 1.0 ELSE 0.0 END), 4) AS win_rate,
                       ROUND(AVG(pnl), 2) AS avg_pnl,
                       ROUND(AVG(pnl_atr), 4) AS avg_pnl_atr
                FROM trade_events {where}
            """
            row = self._conn.execute(sql, params).fetchone()
            if row is None or row["n"] == 0:
                return {"n": 0, "wins": 0, "losses": 0, "win_rate": 0.0,
                        "avg_pnl": 0.0, "avg_pnl_atr": 0.0}
            return {
                "n": row["n"], "wins": row["wins"], "losses": row["losses"],
                "win_rate": round(row["win_rate"] or 0.0, 4),
                "avg_pnl": round(row["avg_pnl"] or 0.0, 2),
                "avg_pnl_atr": round(row["avg_pnl_atr"] or 0.0, 4),
            }
        except Exception:
            return {"n": 0, "wins": 0, "losses": 0, "win_rate": 0.0,
                    "avg_pnl": 0.0, "avg_pnl_atr": 0.0}

    def query_recent_trades(self, n: int = 20) -> list[dict[str, Any]]:
        """Return the N most recent trade events."""
        try:
            rows = self._conn.execute(
                "SELECT * FROM trade_events ORDER BY opened_at DESC LIMIT ?", (n,)
            ).fetchall()
            return [dict(r) for r in rows]
        except Exception:
            return []

    def query_by_regime_session(self) -> list[dict[str, Any]]:
        """Win rate grouped by regime + session."""
        try:
            rows = self._conn.execute("""
                SELECT regime, session,
                       COUNT(*) AS n,
                       ROUND(AVG(CASE WHEN is_win=1 THEN 1.0 ELSE 0.0 END), 4) AS win_rate,
                       ROUND(AVG(pnl), 2) AS avg_pnl
                FROM trade_events
                WHERE regime != ''
                GROUP BY regime, session
                ORDER BY n DESC
            """).fetchall()
            return [dict(r) for r in rows]
        except Exception:
            return []

    def query_judgment_accuracy(self, direction: str = "") -> dict[str, Any]:
        """Return the AI's direction accuracy (optionally filtered by direction)."""
        try:
            where = "WHERE validated_at != '' AND direction_correct >= 0"
            params: tuple = ()
            if direction:
                where += " AND direction=?"
                params = (direction,)
            sql = f"""
                SELECT COUNT(*) AS n,
                       ROUND(AVG(CASE WHEN direction_correct=1 THEN 1.0 ELSE 0.0 END), 4) AS accuracy,
                       ROUND(AVG(confidence), 4) AS avg_confidence
                FROM agent_judgments
                {where}
            """
            row = self._conn.execute(sql, params).fetchone()
            if row is None or row["n"] == 0:
                return {"n": 0, "accuracy": 0.0, "avg_confidence": 0.0}
            return {"n": row["n"], "accuracy": round(row["accuracy"] or 0.0, 4),
                    "avg_confidence": round(row["avg_confidence"] or 0.0, 4)}
        except Exception:
            return {"n": 0, "accuracy": 0.0, "avg_confidence": 0.0}

    def query_recent_judgment_cases(
        self, regime: str = "", limit: int = 5
    ) -> list[dict[str, Any]]:
        """Return recent validated judgments with correctness, filtered by regime."""
        try:
            where = "WHERE validated_at != '' AND direction_correct >= 0"
            params: list[Any] = []
            if regime:
                where += " AND regime=?"
                params.append(regime)
            rows = self._conn.execute(
                f"""
                SELECT judged_at, direction, confidence, regime,
                       direction_correct, price_drift_pct
                FROM agent_judgments
                {where}
                ORDER BY judged_at DESC
                LIMIT ?
                """,
                params + [limit],
            ).fetchall()
            return [dict(r) for r in rows]
        except Exception:
            return []

    def query_win_rate_by_bucket(
        self, feature: str, low: float, high: float
    ) -> dict[str, Any]:
        """Return win rate for rows where `feature` is in [low, high]."""
        try:
            sql = f"""
                SELECT COUNT(*) AS n,
                       ROUND(AVG(CASE WHEN is_win=1 THEN 1.0 ELSE 0.0 END), 4) AS win_rate
                FROM trade_events
                WHERE {feature} >= ? AND {feature} < ?
            """
            row = self._conn.execute(sql, (low, high)).fetchone()
            if row is None or row["n"] == 0:
                return {"n": 0, "win_rate": 0.0}
            return {"n": row["n"], "win_rate": round(row["win_rate"] or 0.0, 4)}
        except Exception:
            return {"n": 0, "win_rate": 0.0}

    def table_sizes(self) -> dict[str, int]:
        """Return row counts for diagnostic purposes."""
        result = {}
        for table in ("trade_events", "agent_judgments", "regime_transitions"):
            try:
                row = self._conn.execute(
                    f"SELECT COUNT(*) AS n FROM {table}"
                ).fetchone()
                result[table] = row["n"] if row else 0
            except Exception:
                result[table] = -1
        return result

    # 鈹€鈹€ Regime Transition Tracking 鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€鈹€

    def record_regime_transition(
        self, from_regime: str, to_regime: str, confidence: float = 0.0
    ) -> int | None:
        """Record a regime change for later validation."""
        try:
            now = _time.strftime("%Y-%m-%dT%H:%M:%S", _time.gmtime())
            cur = self._conn.execute(
                """
                INSERT INTO regime_transitions (from_regime, to_regime, trigger_time, confidence)
                VALUES (:from_r, :to_r, :ts, :conf)
                """,
                {"from_r": from_regime, "to_r": to_regime, "ts": now, "conf": round(float(confidence), 2)},
            )
            self._conn.commit()
            return cur.lastrowid
        except Exception:
            return None

    def validate_regime_transition(
        self, trigger_time: str, current_price: float, reference_price: float = 0.0
    ) -> bool:
        """Validate a regime transition using price drift at 1h/4h/8h windows."""
        try:
            import datetime as _dt
            trigger_dt = _dt.datetime.strptime(trigger_time, "%Y-%m-%dT%H:%M:%S")
            now_dt = _dt.datetime.utcnow()
            delta_h = (now_dt - trigger_dt).total_seconds() / 3600.0

            updates = {}
            if reference_price > 0 and current_price > 0:
                drift = (current_price - reference_price) / reference_price * 100.0
                if delta_h >= 1.0:
                    updates["return_1h"] = round(drift, 4) if abs(delta_h - 1.0) < 0.5 else 0.0
                if delta_h >= 4.0:
                    updates["return_4h"] = round(drift, 4) if abs(delta_h - 4.0) < 1.0 else 0.0
                if delta_h >= 8.0:
                    updates["return_8h"] = round(drift, 4)
                    updates["is_valid"] = 1 if drift > 0 else 0

            if not updates:
                return False

            set_clause = ", ".join(f"{k}=:{k}" for k in updates)
            updates["ts"] = trigger_time
            sql = f"UPDATE regime_transitions SET {set_clause} WHERE trigger_time=:ts"
            self._conn.execute(sql, updates)
            self._conn.commit()
            return True
        except Exception:
            return False

    def query_regime_transition_stats(self) -> list[dict[str, Any]]:
        """Aggregate regime transition accuracy statistics."""
        try:
            rows = self._conn.execute("""
                SELECT from_regime, to_regime,
                       COUNT(*) AS n,
                       ROUND(AVG(CASE WHEN is_valid=1 THEN 1.0 ELSE 0.0 END), 4) AS accuracy,
                       ROUND(AVG(return_1h), 4) AS avg_return_1h,
                       ROUND(AVG(return_4h), 4) AS avg_return_4h,
                       ROUND(AVG(return_8h), 4) AS avg_return_8h
                FROM regime_transitions
                WHERE is_valid >= 0
                GROUP BY from_regime, to_regime
                ORDER BY n DESC
            """).fetchall()
            return [dict(r) for r in rows]
        except Exception:
            return []

    def get_strategy_performance_multiplier(self, strategy_id: str, regime: str = "") -> float:
        """Calculate a priority multiplier based on recent win rate (last 20 trades).
        
        Returns 0.5 (poor) to 1.5 (excellent). Default is 1.0.
        """
        try:
            where = "WHERE strategy_id = ?"
            params = [strategy_id]
            if regime and regime != "UNKNOWN":
                where += " AND regime = ?"
                params.append(regime)
            
            sql = f"""
                SELECT pnl
                FROM trade_events
                {where}
                ORDER BY opened_at DESC
                LIMIT 20
            """
            rows = self._conn.execute(sql, params).fetchall()
            if not rows:
                # If no regime-specific data, fallback to global strategy performance
                if regime:
                    return self.get_strategy_performance_multiplier(strategy_id, "")
                return 1.0
            
            pnls = [row["pnl"] for row in rows]
            wins = sum(1 for p in pnls if p > 0)
            win_rate = wins / len(pnls)
            
            # Multiplier logic: 
            # 50% win rate -> 1.0
            # 70% win rate -> 1.2
            # 30% win rate -> 0.8
            multiplier = 1.0 + (win_rate - 0.5) * 1.0
            
            # Add profit factor influence if enough data
            avg_win = sum(p for p in pnls if p > 0) / max(1, wins)
            avg_loss = abs(sum(p for p in pnls if p <= 0)) / max(1, len(pnls) - wins)
            pf = avg_win / max(0.01, avg_loss)
            
            if pf > 2.0:
                multiplier += 0.1
            elif pf < 0.8:
                multiplier -= 0.1
                
            return max(0.5, min(1.5, multiplier))
        except Exception:
            return 1.0

    def get_regime_llm_trust(self, regime: str) -> dict:
        try:
            r = self._conn.execute("SELECT COUNT(*) AS n, ROUND(AVG(CASE WHEN is_win=1 THEN 1.0 ELSE 0.0 END), 4) AS accuracy FROM trade_events WHERE regime=? AND is_win IN (0,1)", (regime,)).fetchone()
            l = self._conn.execute("SELECT COUNT(*) AS n, ROUND(AVG(CASE WHEN direction_correct=1 THEN 1.0 ELSE 0.0 END), 4) AS accuracy FROM agent_judgments WHERE regime=? AND direction_correct>=0", (regime,)).fetchone()
            rn = int(r[0]) if r and r[0] else 0
            ra = float(r[1]) if r and r[1] else 0.5
            ln = int(l[0]) if l and l[0] else 0
            la = float(l[1]) if l and l[1] else 0.5
            sd = rn + ln >= 10
            return {"regime_accuracy": round(ra, 4), "regime_count": rn, "llm_accuracy": round(la, 4), "llm_count": ln, "trust_regime": sd and ra > 0.55 and ra > la * 1.15, "trust_llm": sd and la > 0.55 and la > ra * 1.15, "sufficient_data": sd}
        except Exception:
            return {"regime_accuracy": 0.5, "regime_count": 0, "llm_accuracy": 0.5, "llm_count": 0, "trust_regime": False, "trust_llm": False, "sufficient_data": False}

    def get_strategy_regime_stats(self, strategy_id: str, regime: str, limit: int = 20) -> dict:
        """Return win rate for a strategy-regime pair. Used for dynamic strategy filtering."""
        try:
            rows = self._conn.execute(
                "SELECT pnl, is_win FROM trade_events WHERE strategy_id=? AND regime=? AND is_win IN (0,1) ORDER BY opened_at DESC LIMIT ?",
                (strategy_id, regime, limit),
            ).fetchall()
            n = len(rows)
            if n < 3:
                return {"n": n, "win_rate": None, "trades": n, "reliable": False}
            wins = sum(1 for r in rows if r["is_win"])
            wr = wins / n
            return {"n": n, "win_rate": round(wr, 4), "trades": n, "reliable": n >= 5}
        except Exception:
            return {"n": 0, "win_rate": None, "trades": 0, "reliable": False}

    def query_composite_win_rate(self, conditions: dict, min_samples: int = 30) -> dict:
        try:
            where_clauses = []
            params = []
            for key, val in conditions.items():
                if val is None or str(val).strip() in ("", "UNKNOWN", "NONE"):
                    continue
                col = _CONDITION_COL_MAP.get(key)
                if col:
                    where_clauses.append(f"{col} = ?")
                    params.append(str(val).strip())
                else:
                    logger.warning("query_composite_win_rate ignored unknown condition key: %s", key)
            where_sql = " AND ".join(where_clauses) if where_clauses else "1=1"
            sql = f"""
                SELECT COUNT(*) AS n,
                       ROUND(AVG(CASE WHEN is_win=1 THEN 1.0 ELSE 0.0 END), 4) AS win_rate,
                       ROUND(AVG(pnl), 2) AS avg_pnl,
                       ROUND(SUM(pnl), 2) AS net_pnl,
                       ROUND(AVG(CASE WHEN pnl > 0 THEN pnl END), 2) AS avg_win,
                       ROUND(AVG(CASE WHEN pnl <= 0 THEN pnl END), 2) AS avg_loss
                FROM trade_events
                WHERE {where_sql} AND is_win IN (0,1)
            """
            row = self._conn.execute(sql, params).fetchone()
            if not row:
                return self._empty_composite_stats()
            n = int(row["n"] or 0)
            avg_win = float(row["avg_win"] or 0.0)
            avg_loss = float(row["avg_loss"] or 0.0)
            profit_factor = round(avg_win / max(0.01, abs(avg_loss)), 2) if n > 0 else 0.0
            return {
                "win_rate": round(float(row["win_rate"] or 0.5), 4) if n > 0 else 0.5,
                "n": n,
                "avg_pnl": round(float(row["avg_pnl"] or 0.0), 2),
                "net_pnl": round(float(row["net_pnl"] or 0.0), 2),
                "avg_win": round(avg_win, 2),
                "avg_loss": round(avg_loss, 2),
                "profit_factor": profit_factor,
                "reliable": n >= min_samples,
            }
        except Exception:
            logger.warning("query_composite_win_rate failed for conditions %s", conditions, exc_info=True)
            return self._empty_composite_stats()


    def query_recent_closed_trades(self, n: int = 100) -> list[dict]:
        """Return most recent closed trades with outcome data for pattern mining."""
        try:
            rows = self._conn.execute(
                "SELECT id, side, pnl, is_win, regime, session, adx_value, vol_ratio, " +
                "strategy_id, agent_direction, closed_at, opened_at, confidence " +
                "FROM trade_events WHERE is_win IN (0,1) AND pnl IS NOT NULL " +
                "ORDER BY COALESCE(closed_at, opened_at) DESC LIMIT ?",
                (n,),
            ).fetchall()
            return [dict(r) for r in rows]
        except Exception:
            return []
    def _empty_composite_stats(self) -> dict:
        return {
            "win_rate": 0.5,
            "n": 0,
            "avg_pnl": 0.0,
            "net_pnl": 0.0,
            "avg_win": 0.0,
            "avg_loss": 0.0,
            "profit_factor": 0.0,
            "reliable": False,
        }



