"""
mine_sweep_db.py — Sweep Database Dashboard

Reads results/sweep_database.db and writes results/sweep_dashboard.md.
Also prints to stdout.

Usage:
    python3 mine_sweep_db.py
    python3 mine_sweep_db.py --asset COINBASE_BTCUSD --timeframe 1D
"""

import sqlite3
import os
import sys
import json
import glob
import argparse
import warnings
import pandas as pd
from datetime import datetime

warnings.filterwarnings("ignore")
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

import strategies.strategy_activation_scores as _strategy_module
from config import OOS_START as _OOS_START, WEIGHT_COLS, mlp_data_path

DB_FILE = "results/sweep_database.db"
DASHBOARD_FILE = "results/sweep_dashboard.md"
DB_SCHEMA_VER = 3  # must match auto_optimize_loop.py — bump when new signals are added

THRESHOLD_COLS = [
    "i_long_entry_activation_threshold",
    "i_long_exit_activation_threshold",
    "i_long_exit_activation_confirmation_threshold",
    "i_trailing_stop_threshold",
    "i_m3_momentum_period",
]

RANGE_COLS = WEIGHT_COLS + THRESHOLD_COLS


def suggest_step(new_start, new_stop, current_step):
    """Return an appropriate step for a tightened range.

    Targets ~15 grid points. Returns current_step unchanged if it already
    gives ≥ 10 points. Reduces to the largest 'nice' step that still gives
    ≥ 10 points when the range is too narrow for the current step.
    """
    width = new_stop - new_start
    if width <= 0 or current_step <= 0:
        return current_step
    # Weight params (step ≤ 5) need ≥ 10 points; threshold params need ≥ 8
    min_points = 8 if current_step > 5 else 10
    if width / current_step >= min_points:
        return current_step  # already adequate
    # Weight params use fine steps; threshold params use coarser ones
    if current_step <= 5:
        candidates = [1, 2, 3, 5]
    else:
        candidates = [5, 10, 15, 20, 25, 30, 35, 40]
    chosen = candidates[0]  # smallest as fallback
    for s in candidates:
        if width / s >= min_points:
            chosen = s
    return float(chosen)


def tier_label(n):
    if n < 200:    return "🔴 SPARSE"
    if n < 1000:   return "🟡 EMERGING"
    if n < 5000:   return "🟢 USABLE"
    if n < 15000:  return "🟢🟢 RICH"
    return "🔵 DEEP"


def runs_to_next_tier(n, rows_per_run_est):
    if n >= 15000 or rows_per_run_est <= 0:
        return None
    target = 1000 if n < 1000 else (5000 if n < 5000 else 15000)
    needed = max(0, target - n)
    return max(1, round(needed / rows_per_run_est))


def _parse_param_entry(v):
    """Parse a single param JSON entry into (start, stop, step) or None.
    Handles both start/stop ranges and values-only locked entries.
    For values-only entries, returns (v0, v0, 0) — zero width flags as already locked.
    """
    if not isinstance(v, dict):
        return None
    if 'start' in v and 'stop' in v:
        return (float(v['start']), float(v['stop']), float(v.get('step', 1)))
    if 'values' in v and v['values']:
        # Locked to a discrete list — represent as (min, max, 0) with zero step
        vals = [float(x) for x in v['values']]
        return (min(vals), max(vals), 0)
    return None


def load_param_ranges():
    """Load param ranges per (asset, tf) from their specific JSON files.
    Returns a dict: {(asset, tf): {param: (start, stop, step)}, ...}
    Also builds a fallback global union keyed as (None, None).
    Specific asset/tf files take priority over generic fallback files.
    values-only entries (locked params) are represented as (v, v, 0) — zero width.
    """
    per_asset = {}
    fallback = {}
    import re as _re
    for jf in sorted(glob.glob("strategies/params/params_strategy_activation_scores_*.json")):
        fname = os.path.basename(jf)
        stem = fname.replace("params_strategy_activation_scores_", "").replace(".json", "")
        parts = stem.rsplit("_", 1)
        if len(parts) == 2:
            asset_key, tf_key = parts[0], parts[1]
            if _re.match(r"^\d+[HDMhdm]$", tf_key):
                try:
                    with open(jf) as f:
                        p = json.load(f)
                    ranges = {}
                    for k, v in p.items():
                        parsed = _parse_param_entry(v)
                        if parsed is not None:
                            ranges[k] = parsed
                    per_asset[(asset_key, tf_key)] = ranges
                    continue
                except Exception:
                    pass
        # Generic file (no asset/tf in name) — add to fallback
        try:
            with open(jf) as f:
                p = json.load(f)
            for k, v in p.items():
                if k not in fallback:
                    parsed = _parse_param_entry(v)
                    if parsed is not None:
                        fallback[k] = parsed
        except Exception:
            pass
    per_asset[(None, None)] = fallback
    return per_asset


def get_param_range(param_ranges, asset, tf, col):
    """Look up a param range for a specific asset/tf.
    Specific file takes priority; falls back to generic only if col not in specific file.
    """
    specific = param_ranges.get((asset, tf), {})
    if col in specific:
        return specific[col]
    return param_ranges.get((None, None), {}).get(col)


def _is_locked_negative(param_ranges, asset, tf, col):
    """True if the param is already constrained to ≤ 0 in the params JSON."""
    r = get_param_range(param_ranges, asset, tf, col)
    if r is None:
        return False
    start, stop, step = r
    return stop <= 0


def _is_locked_positive(param_ranges, asset, tf, col):
    """True if the param is already constrained to > 0 in the params JSON."""
    r = get_param_range(param_ranges, asset, tf, col)
    if r is None:
        return False
    start, stop, step = r
    # Positive lock: start > 0 (or start >= step when step > 0)
    return start > 0


def _build_top_n_section(conn, asset, timeframe, n):
    """
    Fetch top-N results from the DB, run OOS backtests for each, and return
    (md_lines, console_lines) containing the formatted section.
    md_lines  → written to the dashboard markdown file
    console_lines → printed to stdout
    """
    df = pd.read_sql(
        "SELECT * FROM sweep_results WHERE asset=? AND timeframe=? AND composite_score > 0 "
        "ORDER BY composite_score DESC LIMIT ?",
        conn, params=(asset, timeframe, n)
    )

    md, con = [], []

    def _out(md_line, con_line=None):
        md.append(md_line)
        con.append(con_line if con_line is not None else md_line)

    if df.empty:
        _out(f"*No results found for {asset} {timeframe}.*")
        return md, con

    # Load data file for OOS backtests
    data_file = mlp_data_path(asset, timeframe)
    oos_available = os.path.exists(data_file)
    df_full = None
    if oos_available:
        df_full = pd.read_csv(data_file)
        df_full.columns = df_full.columns.str.lower()
        if 'time' in df_full.columns:
            df_full['time'] = pd.to_datetime(df_full['time'], utc=True).dt.tz_localize(None)

    # Run OOS backtest for each candidate
    rows = []
    for rank, (_, db_row) in enumerate(df.iterrows(), 1):
        params = db_row.to_dict()
        oos_pnl, oos_trades = None, None
        if oos_available:
            try:
                df_sig = _strategy_module.generate_signals(df_full.copy(), **params)
                oos_m  = _strategy_module.calculate_metrics(df_sig, score_start=_OOS_START, min_trades=1)
                oos_pnl    = oos_m.get('Total P&L %', 0.0)
                oos_trades = int(oos_m.get('Total Trades', 0))
            except Exception:
                pass
        rows.append({
            'rank':       rank,
            'pnl':        db_row['total_pnl_pct'],
            'dd':         db_row['max_drawdown'],
            'calmar':     db_row['calmar_ratio'],
            'sortino':    db_row['sortino_ratio'],
            'trades':     int(db_row['total_trades']),
            'composite':  db_row['composite_score'],
            'oos_pnl':    oos_pnl,
            'oos_trades': oos_trades,
        })

    oos_col = "OOS available" if oos_available else "OOS N/A (data file missing)"
    title = f"## Top {n} Results — {asset} {timeframe}  *(IS 2017–2024 | {oos_col})*"
    _out(title, f"\n{title}")
    _out("")

    # Markdown table
    md.append("| # | IS P&L% | IS DD% | IS Calmar | IS Sortino | IS Trades | Composite | OOS P&L% | OOS Trades |")
    md.append("|---|---------|--------|-----------|------------|-----------|-----------|----------|------------|")
    # Console header
    hdr = f"{'#':>3}  {'IS P&L%':>10}  {'IS DD%':>7}  {'Calmar':>8}  {'Sortino':>8}  {'Tr':>4}  {'Composite':>10}  {'OOS P&L%':>10}  {'OOS Tr':>7}"
    con.append(hdr)
    con.append("-" * len(hdr))

    for r in rows:
        oos_pnl_s    = f"{r['oos_pnl']:+,.1f}%" if r['oos_pnl'] is not None else "—"
        oos_trades_s = str(r['oos_trades'])       if r['oos_trades'] is not None else "—"
        md.append(
            f"| {r['rank']} | {r['pnl']:,.0f}% | {r['dd']:.2f}% | {r['calmar']:.4f} | "
            f"{r['sortino']:.4f} | {r['trades']} | {r['composite']:.4f} | "
            f"{oos_pnl_s} | {oos_trades_s} |"
        )
        con.append(
            f"{r['rank']:>3}  {r['pnl']:>10,.0f}  {r['dd']:>7.2f}  {r['calmar']:>8.4f}  "
            f"{r['sortino']:>8.4f}  {r['trades']:>4}  {r['composite']:>10.4f}  "
            f"{oos_pnl_s:>10}  {oos_trades_s:>7}"
        )

    _out("")
    return md, con


def show_top_n(conn, asset, timeframe, n):
    """Print top-N results (with OOS) to stdout and write to dashboard file."""
    md_lines, con_lines = _build_top_n_section(conn, asset, timeframe, n)
    for line in con_lines:
        print(line)
    os.makedirs("results", exist_ok=True)
    with open(DASHBOARD_FILE, "w") as f:
        f.write("\n".join(md_lines) + "\n")
    print(f"\n[Dashboard → {DASHBOARD_FILE}]")


def main():
    parser = argparse.ArgumentParser(description="Sweep Database Dashboard")
    parser.add_argument("--asset", help="Filter to specific asset")
    parser.add_argument("--timeframe", help="Filter to specific timeframe")
    parser.add_argument("--top", type=int, metavar="N",
                        help="Show top N results with IS+OOS metrics. With --asset+--timeframe: "
                             "quick table only (skips full dashboard). Without --top, top 10 is "
                             "always appended to the dashboard when --asset+--timeframe are set.")
    args = parser.parse_args()

    if not os.path.exists(DB_FILE):
        print(f"DB not found: {DB_FILE}")
        sys.exit(1)

    conn = sqlite3.connect(DB_FILE)

    if args.top:
        if not args.asset or not args.timeframe:
            print("--top requires both --asset and --timeframe")
            sys.exit(1)
        show_top_n(conn, args.asset, args.timeframe, args.top)
        conn.close()
        return
    all_param_ranges = load_param_ranges()

    lines = []
    console_lines = []

    def out(s=""):
        lines.append(s)

    def con(s=""):
        """Print to console only (not written to dashboard file)."""
        console_lines.append(s)
        print(s)

    # ─────────────────────────────────────────────────────────
    # Section 0: Header + Data Inventory
    # ─────────────────────────────────────────────────────────
    def _freshness_label(dt):
        """🟢 < 24 h  |  🟡 1–5 d  |  🔴 > 5 d"""
        age = datetime.now() - dt
        hours = age.total_seconds() / 3600
        days  = age.days
        if hours < 1:
            return "🟢 just now"
        elif hours < 24:
            return f"🟢 {int(hours)}h ago"
        elif days < 5:
            return f"🟡 {days}d ago"
        else:
            return f"🔴 {days}d ago"

    row = conn.execute("SELECT MAX(recorded_at) FROM sweep_results").fetchone()
    last_recorded = row[0] if row and row[0] else None
    if last_recorded:
        last_dt = datetime.fromisoformat(last_recorded)
        freshness_line = f"Results age: {_freshness_label(last_dt)} (last run: {last_dt.strftime('%Y-%m-%d %H:%M')})"
    else:
        freshness_line = "Results age: 🔴 no data"

    out("# Sweep Database Dashboard")
    out(f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    out(freshness_line)
    out(f"DB: {DB_FILE}")
    out()

    inv_df = pd.read_sql(f"""
        SELECT asset, timeframe,
               COUNT(*) AS rows,
               COUNT(DISTINCT run_id) AS runs,
               COUNT(DISTINCT CASE WHEN search_strategy='optuna' THEN run_id END) AS optuna_runs,
               MAX(recorded_at) AS last_updated
        FROM sweep_results
        GROUP BY asset, timeframe
        ORDER BY asset, timeframe
    """, conn)

    if args.asset:
        inv_df = inv_df[inv_df["asset"] == args.asset]
    if args.timeframe:
        inv_df = inv_df[inv_df["timeframe"] == args.timeframe]

    if inv_df.empty:
        out("No data found in database.")
        conn.close()
        with open(DASHBOARD_FILE, "w") as f:
            f.write("\n".join(lines) + "\n")
        print(f"\n[Dashboard → {DASHBOARD_FILE}]")
        return

    out("## Data Inventory")
    out()
    out(f"| {'Asset':<20} | {'TF':<5} | {'Rows':>6} | {'Runs':>4} | {'Optuna':>6} | {'Last Updated':<19} | Tier |")
    out(f"|{'-'*22}|{'-'*7}|{'-'*8}|{'-'*6}|{'-'*8}|{'-'*21}|{'-'*14}|")
    rows_per_run_map = {}
    for _, row in inv_df.iterrows():
        n = int(row["rows"])
        r = int(row["runs"])
        o = int(row["optuna_runs"]) if row["optuna_runs"] is not None else 0
        key = (row["asset"], row["timeframe"])
        rows_per_run_map[key] = n / max(r, 1)
        t = tier_label(n)
        out(f"| {row['asset']:<20} | {row['timeframe']:<5} | {n:>6} | {r:>4} | {o:>6} | {str(row['last_updated'])[:19]:<19} | {t} |")
    out()
    out(f"Total rows: {int(inv_df['rows'].sum()):,}")
    out()

    out("### Progress to Next Tier")
    out()
    con(f"\n{'Asset-TF':<34} {'Rows':>5}  Tier")
    con("-" * 65)
    for _, row in inv_df.iterrows():
        n = int(row["rows"])
        key = (row["asset"], row["timeframe"])
        est = rows_per_run_map[key]
        runs_needed = runs_to_next_tier(n, est)
        label = tier_label(n)
        combo = f"{row['asset']}-{row['timeframe']}"
        if runs_needed:
            out(f"  {combo:<30} {n:>5} rows  {label}  — ~{runs_needed} more run(s) to next tier  (est. {est:.0f} rows/run)")
            con(f"  {combo:<32} {n:>5}  {label}  (~{runs_needed} runs to next)")
        else:
            out(f"  {combo:<30} {n:>5} rows  {label}")
            con(f"  {combo:<32} {n:>5}  {label}")
    out()
    out("---")
    out()

    # Load full dataset — filter to current schema version so sign analysis
    # only uses rows that were optimized with the current set of signals.
    # Old-schema rows (missing new params) are preserved in the DB but excluded.
    qf = "WHERE schema_ver=?"
    qp = [DB_SCHEMA_VER]
    if args.asset:
        qf += " AND asset=?"; qp.append(args.asset)
    if args.timeframe:
        qf += " AND timeframe=?"; qp.append(args.timeframe)
    df_all = pd.read_sql(f"SELECT * FROM sweep_results {qf}", conn, params=qp)

    # ─────────────────────────────────────────────────────────
    # Section 1: Sign Stability
    # ─────────────────────────────────────────────────────────
    out("## Sign Stability")
    out("*% of top-quartile results (pnl_dd_percentile ≥ 75) with a positive value for each weight.*")
    out("*Combos with ≥ 50 rows only. Lock verdict requires N ≥ 200 and ≤ 5% / ≥ 95% positive.*")
    out()

    eligible_combos = set(
        (row["asset"], row["timeframe"])
        for _, row in inv_df[inv_df["rows"] >= 50].iterrows()
    )
    excluded = len(inv_df) - len(eligible_combos)
    if excluded > 0:
        out(f"⚠  {excluded}/{len(inv_df)} combo(s) excluded (SPARSE — fewer than 50 rows)")
        out()

    if not eligible_combos:
        out("*No combos with ≥ 50 rows yet.*")
        out()
    else:
        df_top = df_all[
            df_all.apply(lambda r: (r["asset"], r["timeframe"]) in eligible_combos, axis=1) &
            (df_all["pnl_dd_percentile"] >= 75)
        ]
        if df_top.empty:
            out("*No top-quartile rows yet.*")
            out()
        else:
            out(f"| {'Param':<32} | {'% Pos':>6} | {'N':>5} | Confidence | Verdict |")
            out(f"|{'-'*34}|{'-'*8}|{'-'*7}|{'-'*12}|{'-'*30}|")
            sign_rows = []
            for col in WEIGHT_COLS:
                if col not in df_top.columns:
                    continue
                col_data = df_top[col].dropna()
                if len(col_data) < 5:
                    continue
                n = len(col_data)
                pct_pos = 100.0 * (col_data > 0).sum() / n
                dist = abs(pct_pos - 50)
                if n >= 200 and dist >= 30:   conf = "HIGH"
                elif n >= 50 and dist >= 20:  conf = "MEDIUM"
                else:                          conf = "LOW"
                if n >= 200 and pct_pos <= 5:
                    # Check if already locked negative across all eligible combos
                    all_locked = all(
                        _is_locked_negative(all_param_ranges, a, t, col)
                        for a, t in eligible_combos
                    )
                    verdict = "✅ Applied" if all_locked else "🔒 Lock NEGATIVE (strong)"
                elif n >= 200 and pct_pos >= 95:
                    all_locked = all(
                        _is_locked_positive(all_param_ranges, a, t, col)
                        for a, t in eligible_combos
                    )
                    verdict = "✅ Applied" if all_locked else "🔒 Lock POSITIVE (strong)"
                elif dist >= 30 and conf in ("HIGH", "MEDIUM"):
                    verdict = "⬇  Likely negative" if pct_pos < 50 else "⬆  Likely positive"
                else:
                    verdict = "❓ No clear direction"
                sign_rows.append((col, pct_pos, n, conf, verdict))
            sign_rows.sort(key=lambda r: abs(r[1] - 50), reverse=True)
            for col, pct_pos, n, conf, verdict in sign_rows:
                out(f"| {col:<32} | {pct_pos:>5.1f}% | {n:>5} | {conf:<10} | {verdict} |")
            out()

    out("---")
    out()

    # ─────────────────────────────────────────────────────────
    # Section 2: Cross-Asset Universality
    # ─────────────────────────────────────────────────────────
    out("## Cross-Asset Universality")
    out("*Top-quartile results, per-asset means on the timeframe with most data (≥ 200 rows required).*")
    out("*Low spread + same sign across all assets = universal signal.*")
    out()

    # Per asset: pick timeframe with most data (min 200 rows)
    asset_best_tf = {}
    for _, row in inv_df[inv_df["rows"] >= 200].iterrows():
        a = row["asset"]
        if a not in asset_best_tf or row["rows"] > asset_best_tf[a][1]:
            asset_best_tf[a] = (row["timeframe"], row["rows"])

    if len(asset_best_tf) < 2:
        out("*Need ≥ 2 assets at EMERGING tier (200+ rows) for cross-asset comparison.*")
        out()
    else:
        asset_means = {}
        for asset, (tf, _) in sorted(asset_best_tf.items()):
            sub = df_all[
                (df_all["asset"] == asset) &
                (df_all["timeframe"] == tf) &
                (df_all["pnl_dd_percentile"] >= 75)
            ]
            if not sub.empty:
                asset_means[asset] = (tf, sub[WEIGHT_COLS].mean())

        if len(asset_means) < 2:
            out("*Insufficient top-quartile data for cross-asset comparison.*")
            out()
        else:
            asset_list = sorted(asset_means.keys())
            short_names = [a.split("_")[-1][:6] for a in asset_list]
            col_hdr = " | ".join(f"{s:>7}" for s in short_names)
            tf_note = ", ".join(f"{a}={asset_means[a][0]}" for a in asset_list)
            out(f"*(timeframes used: {tf_note})*")
            out()
            out(f"| {'Param':<32} | {col_hdr} | {'Spread':>7} | Universal? |")
            out(f"|{'-'*34}|" + "".join(f"{'-'*9}|" for _ in asset_list) + f"{'-'*9}|{'-'*12}|")

            univ_rows = []
            for col in WEIGHT_COLS:
                means = []
                for a in asset_list:
                    m = asset_means[a][1].get(col, float('nan'))
                    means.append(m)
                valid = [m for m in means if m == m]
                if len(valid) < 2:
                    continue
                spread = max(valid) - min(valid)
                signs = set("pos" if m > 0 else "neg" for m in valid)
                universal = len(signs) == 1 and spread < 30
                univ_rows.append((col, means, spread, universal))

            univ_rows.sort(key=lambda r: (r[3], -r[2]), reverse=True)
            for col, means, spread, universal in univ_rows:
                ms = " | ".join(f"{m:>+7.1f}" if m == m else f"{'N/A':>7}" for m in means)
                label = "✅ Yes" if universal else "❌ No"
                out(f"| {col:<32} | {ms} | {spread:>7.1f} | {label} |")
            out()

    out("---")
    out()

    # ─────────────────────────────────────────────────────────
    # Section 3: Timeframe Stability (per asset)
    # ─────────────────────────────────────────────────────────
    out("## Timeframe Stability")
    out("*Per asset: mean param value across timeframes (top-quartile, ≥ 200 rows per TF required).*")
    out("*Stable = same sign + spread < 30 across all timeframes shown.*")
    out()

    tf_stability_shown = False
    for asset in sorted(df_all["asset"].unique()):
        asset_tfs = inv_df[(inv_df["asset"] == asset) & (inv_df["rows"] >= 200)]
        if len(asset_tfs) < 2:
            continue
        tfs = sorted(asset_tfs["timeframe"].tolist())
        tf_means = {}
        for tf in tfs:
            sub = df_all[
                (df_all["asset"] == asset) &
                (df_all["timeframe"] == tf) &
                (df_all["pnl_dd_percentile"] >= 75)
            ]
            if not sub.empty:
                tf_means[tf] = sub[WEIGHT_COLS].mean()
        if len(tf_means) < 2:
            continue
        tf_stability_shown = True
        out(f"### {asset}  (TFs with ≥ 200 rows: {', '.join(tfs)})")
        out()
        col_hdr = " | ".join(f"{tf:>6}" for tf in tfs)
        out(f"| {'Param':<32} | {col_hdr} | Stable? |")
        out(f"|{'-'*34}|" + "".join(f"{'-'*8}|" for _ in tfs) + f"{'-'*9}|")
        for col in WEIGHT_COLS:
            means = [tf_means.get(tf, pd.Series()).get(col, float('nan')) for tf in tfs]
            valid = [m for m in means if m == m]
            if len(valid) < 2:
                continue
            spread = max(valid) - min(valid)
            signs = set("pos" if m > 0 else "neg" for m in valid)
            stable = len(signs) == 1 and spread < 30
            ms = " | ".join(f"{m:>+6.1f}" if m == m else f"{'N/A':>6}" for m in means)
            label = "✅ Yes" if stable else "❌ Shifts"
            out(f"| {col:<32} | {ms} | {label} |")
        out()

    if not tf_stability_shown:
        out("*Need ≥ 2 timeframes with EMERGING tier (200+ rows) per asset.*")
        out()

    out("---")
    out()

    # ─────────────────────────────────────────────────────────
    # Section 4: Range Tightening Suggestions
    # ─────────────────────────────────────────────────────────
    out("## Range Tightening Suggestions")
    out("*EMERGING tier (200+ rows) only. q5–q95 of top-quartile rows.*")
    out("*'✂ Tighten' = suggested range covers 90% of mass but is < 70% of current width.*")
    out("*Treat as hypotheses — review before applying to params JSON.*")
    out()

    tightening_shown = False
    for _, inv_row in inv_df[inv_df["rows"] >= 200].iterrows():
        asset, tf = inv_row["asset"], inv_row["timeframe"]
        sub = df_all[
            (df_all["asset"] == asset) &
            (df_all["timeframe"] == tf) &
            (df_all["pnl_dd_percentile"] >= 75)
        ]
        if len(sub) < 20:
            continue
        tightening_shown = True
        out(f"### {asset} {tf}  ({int(inv_row['rows'])} rows, {len(sub)} top-quartile)")
        out()
        out(f"| {'Param':<32} | {'Current':>16} | {'q5–q95':>16} | Action |")
        out(f"|{'-'*34}|{'-'*18}|{'-'*18}|{'-'*10}|")
        for col in RANGE_COLS:
            if col not in sub.columns:
                continue
            col_data = sub[col].dropna()
            if len(col_data) < 10:
                continue
            q05 = col_data.quantile(0.05)
            q95 = col_data.quantile(0.95)
            current = get_param_range(all_param_ranges, asset, tf, col)
            if current:
                curr_start, curr_stop, curr_step = current
                current_width = curr_stop - curr_start
                if current_width == 0:
                    # Zero-width: locked to a single value (e.g. values=[0])
                    curr_str = f"locked={curr_start:g}"
                    action = "✅ Applied"
                else:
                    curr_str = f"[{curr_start:g}, {curr_stop:g}]"
                    new_width = q95 - q05
                    if new_width < 0.7 * current_width:
                        new_step = suggest_step(q05, q95, curr_step)
                        step_note = f" step→{new_step:g}" if new_step != curr_step else ""
                        action = f"✂ Tighten{step_note}"
                    else:
                        action = "—"
            else:
                curr_str = "unknown"
                action = "?"
            sugg_str = f"[{q05:+.1f}, {q95:+.1f}]"
            out(f"| {col:<32} | {curr_str:>16} | {sugg_str:>16} | {action} |")
        out()

    if not tightening_shown:
        out("*No EMERGING tier combos (200+ rows) yet.*")
        out()

    out("---")
    out()

    # ─────────────────────────────────────────────────────────
    # Section 5: Degenerate Result Summary
    # ─────────────────────────────────────────────────────────
    out("## Degenerate Results")
    out("*Entry ≈ exit threshold (within 10 units), or trading on >15% of bars.*")
    out()

    if "i_long_entry_activation_threshold" in df_all.columns and \
       "i_long_exit_activation_threshold" in df_all.columns:
        # Estimate bar count from date range + timeframe
        tf_hours = {"1D": 24, "12H": 12, "8H": 8, "6H": 6, "4H": 4, "2H": 2, "1H": 1}
        _start = pd.to_datetime(df_all["train_start"], errors="coerce")
        _end   = pd.to_datetime(df_all["train_end"],   errors="coerce")
        _days  = (_end - _start).dt.days.clip(lower=1)
        _hrs   = df_all["timeframe"].map(tf_hours).fillna(24)
        df_all["_est_bars"] = (_days * 24 / _hrs).round()

        df_all["_degen"] = (
            (df_all["i_long_entry_activation_threshold"] - df_all["i_long_exit_activation_threshold"]).abs() < 10
        ) | (
            df_all["i_long_entry_activation_threshold"] < df_all["i_long_exit_activation_threshold"]
        ) | (
            (df_all["total_trades"] / df_all["_est_bars"].clip(lower=1)) > 0.15
        )
        degen_summary = df_all.groupby(["asset", "timeframe"]).agg(
            total=("_degen", "count"),
            degen=("_degen", "sum")
        ).reset_index()
        for _, row in degen_summary.iterrows():
            pct = 100.0 * row["degen"] / max(row["total"], 1)
            flag = "⚠ HIGH" if pct > 20 else "ok"
            out(f"  {row['asset']} {row['timeframe']}: {int(row['degen'])}/{int(row['total'])} rows degenerate ({pct:.1f}%)  {flag}")
        out()
    else:
        out("*Threshold columns not available in DB.*")
        out()

    out("---")
    out()

    # ─────────────────────────────────────────────────────────
    # Action Summary
    # ─────────────────────────────────────────────────────────
    out("## Recommended Actions")
    out("*(REVIEW BEFORE APPLYING — data-driven hypotheses, not automatic changes)*")
    out()

    high_actions = []
    medium_actions = []
    not_yet = []

    # Sign stability → lock candidates (skip already-applied)
    if eligible_combos:
        df_tq = df_all[
            df_all.apply(lambda r: (r["asset"], r["timeframe"]) in eligible_combos, axis=1) &
            (df_all["pnl_dd_percentile"] >= 75)
        ]
        for col in WEIGHT_COLS:
            if col not in df_tq.columns:
                continue
            col_data = df_tq[col].dropna()
            if len(col_data) < 200:
                continue
            n = len(col_data)
            pct_pos = 100.0 * (col_data > 0).sum() / n
            if pct_pos <= 5:
                # Skip if already locked negative across all eligible combos
                if all(_is_locked_negative(all_param_ranges, a, t, col)
                       for a, t in eligible_combos):
                    continue
                high_actions.append(
                    f"Lock `{col}` to NEGATIVE range (e.g. stop ≤ -5 in params JSON)\n"
                    f"    Basis: {pct_pos:.1f}% positive across {n} top-quartile rows"
                )
            elif pct_pos >= 95:
                if all(_is_locked_positive(all_param_ranges, a, t, col)
                       for a, t in eligible_combos):
                    continue
                high_actions.append(
                    f"Lock `{col}` to POSITIVE range (e.g. start ≥ 5 in params JSON)\n"
                    f"    Basis: {pct_pos:.1f}% positive across {n} top-quartile rows"
                )

    # Range tightening → medium confidence (skip already-applied)
    for _, inv_row in inv_df[inv_df["rows"] >= 200].iterrows():
        asset, tf = inv_row["asset"], inv_row["timeframe"]
        sub = df_all[
            (df_all["asset"] == asset) &
            (df_all["timeframe"] == tf) &
            (df_all["pnl_dd_percentile"] >= 75)
        ]
        if len(sub) < 20:
            continue
        for col in WEIGHT_COLS:
            if col not in sub.columns:
                continue
            col_data = sub[col].dropna()
            if len(col_data) < 10:
                continue
            q05 = col_data.quantile(0.05)
            q95 = col_data.quantile(0.95)
            current = get_param_range(all_param_ranges, asset, tf, col)
            if current:
                curr_start, curr_stop, curr_step = current
                cw = curr_stop - curr_start
                if cw == 0:
                    continue  # already locked to a single value — skip
                nw = q95 - q05
                if cw > 0 and nw < 0.5 * cw:
                    new_step = suggest_step(q05, q95, curr_step)
                    step_note = f", step {curr_step:g}→{new_step:g}" if new_step != curr_step else ""
                    medium_actions.append(
                        f"Narrow `{col}` for {asset} {tf}: [{q05:+.1f}, {q95:+.1f}]{step_note} "
                        f"(current: [{curr_start:g}, {curr_stop:g}])"
                    )

    # SPARSE combos → not yet actionable
    for _, row in inv_df[inv_df["rows"] < 50].iterrows():
        key = (row["asset"], row["timeframe"])
        est = rows_per_run_map.get(key, 0)
        rn = runs_to_next_tier(int(row["rows"]), est)
        not_yet.append(
            f"{row['asset']}-{row['timeframe']}: {int(row['rows'])} rows (SPARSE) — "
            f"run {rn or '?'} more batch run(s) before drawing conclusions"
        )

    if high_actions:
        out("### HIGH CONFIDENCE")
        con("\n### HIGH CONFIDENCE")
        for a in high_actions:
            out(f"  - [ ] {a}")
            con(f"  - [ ] {a}")
        out()
    if medium_actions:
        out("### MEDIUM CONFIDENCE (consider after manual review)")
        con("\n### MEDIUM CONFIDENCE")
        for a in medium_actions:
            out(f"  - [ ] {a}")
            con(f"  - [ ] {a}")
        out()
    if not_yet:
        out("### NOT YET ACTIONABLE")
        con("\n### NOT YET ACTIONABLE")
        for a in not_yet:
            out(f"  - {a}")
            con(f"  - {a}")
        out()
    if not high_actions and not medium_actions and not not_yet:
        out("*No actionable findings yet — accumulate more data.*")
        out()
        con("\nNo actionable findings yet.")

    # ─────────────────────────────────────────────────────────
    # Section: Top-N Results with OOS (only when single combo filtered)
    # ─────────────────────────────────────────────────────────
    if args.asset and args.timeframe:
        top_n = args.top if args.top else 10
        out("---")
        con("\n" + "─" * 60)
        print(f"\nComputing OOS metrics for top {top_n} results ({args.asset} {args.timeframe})...")
        md_lines, con_lines = _build_top_n_section(conn, args.asset, args.timeframe, top_n)
        for line in md_lines:
            out(line)
        for line in con_lines:
            con(line)

    out("---")
    out(f"*Generated by mine_sweep_db.py — {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}*")

    conn.close()

    os.makedirs("results", exist_ok=True)
    with open(DASHBOARD_FILE, "w") as f:
        f.write("\n".join(lines) + "\n")
    print(f"\n[Dashboard → {DASHBOARD_FILE}]")


if __name__ == "__main__":
    main()