#!/usr/bin/env python3
from __future__ import annotations

import json
import sqlite3
from collections import Counter, defaultdict
from datetime import datetime, timedelta, timezone
from pathlib import Path

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from matplotlib.patches import Rectangle

ROOT = Path(__file__).resolve().parent
START = pd.Timestamp("2026-08-11T03:27:34Z")
END = pd.Timestamp("2026-08-13T03:27:34Z")


def iso(value) -> str | None:
    if value is None:
        return None
    parsed = pd.Timestamp(value)
    if parsed.tzinfo is None:
        parsed = parsed.tz_localize("UTC")
    return parsed.isoformat().replace("+00:00", "Z")


raw = json.loads((ROOT / "candles.json").read_text())
frame = pd.DataFrame(raw["candles"])
frame["time"] = pd.to_datetime(frame["time"], utc=True)
for column in ("open", "high", "low", "close", "volume", "atr", "adx"):
    frame[column] = pd.to_numeric(frame[column], errors="coerce")
frames = {pair: data.sort_values("time").set_index("time") for pair, data in frame.groupby("pair")}

db = sqlite3.connect(f"file:{(ROOT / 'trades.sqlite').resolve()}?mode=ro", uri=True)
db.row_factory = sqlite3.Row
trades = {row["id"]: dict(row) for row in db.execute("SELECT * FROM trades")}


def entry_order(trade: dict) -> dict:
    side = "sell" if trade["is_short"] else "buy"
    row = db.execute(
        "SELECT * FROM orders WHERE ft_trade_id=? AND ft_order_side=? AND order_type='limit' ORDER BY order_date LIMIT 1",
        (trade["id"], side),
    ).fetchone()
    return dict(row)


def path_metric(pair: str, when, side: str, hours: int) -> dict | None:
    when = pd.Timestamp(when)
    if when.tzinfo is None:
        when = when.tz_localize("UTC")
    data = frames[pair]
    eligible = data[data.index >= when.floor("5min")]
    if eligible.empty:
        return None
    start_time = eligible.index[0]
    end_time = start_time + pd.Timedelta(hours=hours)
    if data.index.max() < end_time:
        return None
    rows = data[(data.index >= start_time) & (data.index <= end_time)]
    entry = float(rows.iloc[0].close)
    high, low, last = float(rows.high.max()), float(rows.low.min()), float(rows.iloc[-1].close)
    if side == "short":
        mfe, mae, end_ret = (entry - low) / entry, (high - entry) / entry, (entry - last) / entry
    else:
        mfe, mae, end_ret = (high - entry) / entry, (entry - low) / entry, (last - entry) / entry
    return {
        "entry": entry, "mfe": mfe, "mae": mae, "endReturn": end_ret,
        "trailArm": mfe >= 0.025, "hardStop": mae >= 0.025,
    }


def aggregate(items: list[dict | None]) -> dict:
    rows = [item for item in items if item is not None]
    if not rows:
        return {"n": 0}
    return {
        "n": len(rows),
        "mfeMean": float(np.mean([x["mfe"] for x in rows])),
        "maeMean": float(np.mean([x["mae"] for x in rows])),
        "endReturnMean": float(np.mean([x["endReturn"] for x in rows])),
        "endReturnMedian": float(np.median([x["endReturn"] for x in rows])),
        "positiveEndRate": float(np.mean([x["endReturn"] > 0 for x in rows])),
        "trailArmRate": float(np.mean([x["trailArm"] for x in rows])),
        "hardStopRate": float(np.mean([x["hardStop"] for x in rows])),
    }


def cluster(events: list[dict], gap_minutes: int = 15) -> list[dict]:
    groups: list[dict] = []
    for event in sorted(events, key=lambda x: (x["pair"], x["side"], pd.Timestamp(x["time"]))):
        when = pd.Timestamp(event["time"])
        if when.tzinfo is None:
            when = when.tz_localize("UTC")
        if groups and groups[-1]["pair"] == event["pair"] and groups[-1]["side"] == event["side"]:
            last = pd.Timestamp(groups[-1]["last"])
            if when - last <= pd.Timedelta(minutes=gap_minutes):
                groups[-1]["last"] = iso(when)
                groups[-1]["events"] += 1
                groups[-1]["shadowEligible"] = groups[-1].get("shadowEligible", False) or event.get("shadowEligible", False)
                continue
        groups.append({
            "pair": event["pair"], "side": event["side"], "time": iso(when),
            "last": iso(when), "events": 1, "reason": event.get("reason"),
            "shadowEligible": event.get("shadowEligible", False),
        })
    return groups


gate_events = []
for item in raw["gates"]:
    details = json.loads(item["details_json"])
    gate_events.append({
        "pair": item["pair"], "side": item["side"], "time": item["occurred_at"], "reason": item["reason"],
        "shadowEligible": bool(details.get("replacementShadow", {}).get("eligible")),
    })
gate_clusters = cluster(gate_events)

reference_gap_events = []
for row in raw["candles"]:
    if (
        row["verdict"] == "question"
        and row["firstDivergence"] in {"signal_detection", "risk_gate"}
        and row["referencePresent"] is True
        and row["referenceSide"] in {"long", "short"}
        and row["strategyAction"] != ("enter_long" if row["referenceSide"] == "long" else "enter_short")
    ):
        reference_gap_events.append({
            "pair": row["pair"], "side": row["referenceSide"], "time": row["time"],
            "reason": "btc_filter_or_signal_mapping", "btcBull1d": row["btcBull1d"],
            "divergence": row["firstDivergence"],
        })
reference_gap_clusters = cluster(reference_gap_events)

new_ids = [trade_id for trade_id, trade in trades.items() if START <= pd.Timestamp(trade["open_date"], tz="UTC") < END]
closed_ids = [trade_id for trade_id, trade in trades.items() if trade["close_date"] and START <= pd.Timestamp(trade["close_date"], tz="UTC") < END]

new_trade_rows = []
for trade_id in sorted(new_ids):
    trade = trades[trade_id]
    order = entry_order(trade)
    latest = frames[trade["pair"]].iloc[-1]
    mark = float(latest.close)
    side = "short" if trade["is_short"] else "long"
    gross_mark = ((trade["open_rate"] - mark) if trade["is_short"] else (mark - trade["open_rate"])) * trade["amount"]
    new_trade_rows.append({
        "id": trade_id, "pair": trade["pair"], "side": side,
        "orderTime": iso(order["order_date"]), "fillTime": iso(order["order_filled_date"]),
        "fillLatencySeconds": (pd.Timestamp(order["order_filled_date"]) - pd.Timestamp(order["order_date"])).total_seconds(),
        "openRate": trade["open_rate"], "isOpen": bool(trade["is_open"]),
        "closeTime": iso(trade["close_date"]), "closeRate": trade["close_rate"],
        "realizedPnl": trade["close_profit_abs"], "profitRatio": trade["close_profit"],
        "exitReason": trade["exit_reason"], "initialStop": trade["initial_stop_loss"],
        "latestMark": mark, "grossOpenPnl": gross_mark if trade["is_open"] else None,
    })

cancelled = {
    "caseId": "zectimeout-20260812-0850", "pair": "ZEC/USDT:USDT", "side": "short",
    "orderTime": "2026-08-12T08:50:08.724Z", "cancelTime": "2026-08-12T09:00:11Z",
    "price": 475.76, "orderId": "dry_run_sell_ZEC/USDT:USDT_047dba01-71b1-42ee-9ebf-3cfc8320ff69",
    "dsOrderCreated": "agree", "dsSignalCandle": "question/fill", "dsCancellationCaptured": False,
}

replacement_gaps = []
for trade_id in (47, 50, 51, 52):
    orders = [dict(x) for x in db.execute(
        "SELECT * FROM orders WHERE ft_trade_id=? AND ft_order_side='stoploss' ORDER BY order_date", (trade_id,)
    )]
    for before, after in zip(orders, orders[1:]):
        if before["status"] == "canceled" and before["order_update_date"]:
            replacement_gaps.append((pd.Timestamp(after["order_date"]) - pd.Timestamp(before["order_update_date"])).total_seconds())

filled_opportunities = [
    {"pair": row["pair"], "side": row["side"], "time": row["orderTime"]} for row in new_trade_rows
]
cohorts = {}
for hours in (6, 24):
    cohorts[f"filled{hours}h"] = aggregate([path_metric(x["pair"], x["time"], x["side"], hours) for x in filled_opportunities])
    cohorts[f"gateBlocked{hours}h"] = aggregate([path_metric(x["pair"], x["time"], x["side"], hours) for x in gate_clusters])
    cohorts[f"referenceGap{hours}h"] = aggregate([path_metric(x["pair"], x["time"], x["side"], hours) for x in reference_gap_clusters])
    cohorts[f"cancelled{hours}h"] = aggregate([path_metric(cancelled["pair"], cancelled["orderTime"], cancelled["side"], hours)])
    cohorts[f"shadowEligible{hours}h"] = aggregate([
        path_metric(x["pair"], x["time"], x["side"], hours) for x in gate_clusters if x.get("shadowEligible")
    ])
    cohorts[f"shadowIneligible{hours}h"] = aggregate([
        path_metric(x["pair"], x["time"], x["side"], hours) for x in gate_clusters if not x.get("shadowEligible")
    ])

closed = [trades[x] for x in closed_ids]
profit = sum(x["close_profit_abs"] for x in closed)
loss_gross = -sum(x["close_profit_abs"] for x in closed if x["close_profit_abs"] < 0)
profit_gross = sum(x["close_profit_abs"] for x in closed if x["close_profit_abs"] > 0)
metrics = {
    "window": {"start": iso(START), "end": iso(END)},
    "runtime": {
        "strategy": "VolatilityBreakoutRiskCap", "actualVersion": "5.4.1-d48-riskcap-observe",
        "auditorPayloadVersion": "5.4-d48-riskcap-forward", "timeframe": "5m", "mode": "dry-run",
        "pairsObserved": len(frames), "candles": len(frame),
    },
    "trades": {
        "newEntries": len(new_ids), "newClosed": sum(not trades[x]["is_open"] for x in new_ids),
        "newOpen": sum(bool(trades[x]["is_open"]) for x in new_ids),
        "newRealizedPnl": sum((trades[x]["close_profit_abs"] or 0) for x in new_ids),
        "newOpenGrossMarkPnl": sum((x["grossOpenPnl"] or 0) for x in new_trade_rows),
        "closedInWindow": len(closed), "closedPnl": profit,
        "wins": sum(x["close_profit_abs"] > 0 for x in closed), "losses": sum(x["close_profit_abs"] < 0 for x in closed),
        "profitFactor": None if loss_gross == 0 else profit_gross / loss_gross,
        "entryAttempts": len(new_ids) + 1, "entryFills": len(new_ids), "entryFillRate": len(new_ids) / (len(new_ids) + 1),
        "fillLatencyMedianSeconds": float(np.median([x["fillLatencySeconds"] for x in new_trade_rows])),
        "fillLatencyMaxSeconds": max(x["fillLatencySeconds"] for x in new_trade_rows),
        "newTradeRows": new_trade_rows, "cancelledEntry": cancelled,
    },
    "auditor": {
        "events": 6482, "successful": 6480, "coverage": 6480 / 6482,
        "candleEvents": 6336, "candleSucceeded": 6334, "orderEvents": 146, "orderSucceeded": 146,
        "entrySubmissionReviewed": 5, "entrySubmissions": 5,
        "verdicts": {"agree": 6325, "question": 100, "insufficient_evidence": 55, "no_result": 2},
        "questionFirstDivergence": {"signal_detection": 69, "execution": 24, "risk_gate": 6, "fill": 1},
        "schema": "1.2", "prompt": "freqtrade-causal-audit-v3", "model": "deepseek-v4-flash",
        "deadLetters": 2, "deadLetterReason": "Responses API returned empty output text",
        "stopReplacementGapSeconds": {
            "n": len(replacement_gaps), "median": float(np.median(replacement_gaps)),
            "p95": float(np.percentile(replacement_gaps, 95)), "max": max(replacement_gaps),
        },
    },
    "cohorts": {
        "gateRawObservations": len(gate_events), "gateClusters": len(gate_clusters),
        "shadowEligibleRaw": sum(x["shadowEligible"] for x in gate_events),
        "shadowEligibleClusters": sum(x.get("shadowEligible", False) for x in gate_clusters),
        "referenceGapRawQuestions": len(reference_gap_events), "referenceGapClusters": len(reference_gap_clusters),
        "referenceGapBtcBullFalse": sum(x.get("btcBull1d") is False for x in reference_gap_events),
        "referenceGapSides": dict(Counter(x["side"] for x in reference_gap_events)),
        "paths": cohorts,
    },
}
(ROOT / "metrics.json").write_text(json.dumps(metrics, ensure_ascii=False, indent=2))


def review_case_from_trade(row: dict) -> dict:
    closed = not row["isOpen"]
    return {
        "case_id": f"trade-{row['id']}", "pair": row["pair"], "side": row["side"],
        "reference": {"kind": "reference_signal", "present": True, "time": row["orderTime"],
                      "rule_id": "d48-core-breakout-v1", "defined_before_outcome": True},
        "strategy_signal": {"present": True, "time": row["orderTime"]},
        "gate": {"status": "passed", "reason": None},
        "order": {"submitted": True, "time": row["orderTime"], "intended_price": row["openRate"]},
        "fill": {"filled": True, "time": row["fillTime"], "price": row["openRate"]},
        "exit": {"present": closed, "time": row["closeTime"], "reason": row["exitReason"], "stop_price": row["initialStop"]},
        "outcome": {"pnl_ratio": row["profitRatio"], "mfe_ratio": None, "mae_ratio": None},
        "evidence": [f"sqlite:trades:{row['id']}", "auditor:candle_close", "auditor:order_created"],
    }


cases = [review_case_from_trade(row) for row in new_trade_rows]
cases.append({
    "case_id": cancelled["caseId"], "pair": cancelled["pair"], "side": cancelled["side"],
    "reference": {"kind": "reference_signal", "present": True, "time": cancelled["orderTime"],
                  "rule_id": "d48-core-breakout-v1", "defined_before_outcome": True},
    "strategy_signal": {"present": True, "time": cancelled["orderTime"]},
    "gate": {"status": "passed", "reason": None},
    "order": {"submitted": True, "time": cancelled["orderTime"], "intended_price": cancelled["price"]},
    "fill": {"filled": False, "time": None, "price": None},
    "exit": {"present": False, "time": None, "reason": None, "stop_price": None},
    "outcome": {"pnl_ratio": None, "mfe_ratio": None, "mae_ratio": None},
    "evidence": ["freqtrade.log:fully_cancelled", "auditor:order_created:agree", "auditor:candle_close:question/fill"],
})
review = {
    "review_id": "2026-08-13-bot1-48h", "as_of": iso(END),
    "strategy": {"bot_id": "bot1", "name": "VolatilityBreakoutRiskCap", "version": "5.4.1-d48-riskcap-observe"},
    "window": {"start": iso(START), "end": iso(END), "timeframe": "5m"}, "cases": cases,
}
(ROOT / "review.json").write_text(json.dumps(review, ensure_ascii=False, indent=2))


def candle_plot(ax, data: pd.DataFrame, title: str):
    if data.empty:
        ax.text(0.5, 0.5, "No data", ha="center", va="center")
        return
    xs = mdates.date2num(data.index.to_pydatetime())
    width = (5 / 1440) * 0.72
    for x, row in zip(xs, data.itertuples()):
        color = "#15803D" if row.close >= row.open else "#B91C1C"
        ax.vlines(x, row.low, row.high, color=color, linewidth=0.5, alpha=0.75)
        bottom, height = min(row.open, row.close), abs(row.close - row.open)
        if height:
            ax.add_patch(Rectangle((x - width / 2, bottom), width, height, facecolor=color, edgecolor=color, linewidth=0.2, alpha=0.7))
        else:
            ax.hlines(row.close, x - width / 2, x + width / 2, color=color, linewidth=0.7)
    ax.grid(color="#E2E8F0", linewidth=0.5, alpha=0.8)
    ax.set_title(title, loc="left", fontsize=9, fontweight="bold")
    ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d\n%H:%M", tz=timezone.utc))
    ax.tick_params(labelsize=7)


def overlay(ax, row: dict, show_exit: bool = True):
    order_time = pd.Timestamp(row["orderTime"])
    ax.scatter(order_time, row["openRate"], marker="v" if row["side"] == "short" else "^", s=54,
               color="#2563EB", edgecolor="white", linewidth=0.5, zorder=7, label="strategy signal")
    ax.scatter(pd.Timestamp(row["fillTime"]), row["openRate"], marker="o", s=20, color="#111827", zorder=8, label="fill")
    ax.axhline(row["initialStop"], color="#DC2626", linewidth=0.9, label="initial stop")
    if row["closeTime"] and show_exit:
        ax.scatter(pd.Timestamp(row["closeTime"]), row["closeRate"], marker="D", s=38,
                   color="#0F766E", edgecolor="white", linewidth=0.4, zorder=8, label="exit")


def resample15(data: pd.DataFrame) -> pd.DataFrame:
    return data.resample("15min", label="left", closed="left").agg(
        {"open": "first", "high": "max", "low": "min", "close": "last", "volume": "sum"}
    ).dropna()


best_gate = None
for item in gate_clusters:
    metric = path_metric(item["pair"], item["time"], item["side"], 6)
    if metric and (best_gate is None or metric["mfe"] > best_gate[1]["mfe"]):
        best_gate = (item, metric)

plot_cases = [(f"{x['pair'].split('/')[0]} trade #{x['id']}", x) for x in new_trade_rows]
fig, axes = plt.subplots(6, 2, figsize=(17, 24), constrained_layout=True)
for idx, (label, row) in enumerate(plot_cases):
    opened = pd.Timestamp(row["orderTime"])
    closed_at = pd.Timestamp(row["closeTime"]) if row["closeTime"] else END
    data = frames[row["pair"]]
    left = data[(data.index >= opened - pd.Timedelta(hours=3)) & (data.index <= opened + pd.Timedelta(hours=8))]
    right = resample15(data[(data.index >= opened - pd.Timedelta(hours=3)) & (data.index <= closed_at + pd.Timedelta(hours=2))])
    candle_plot(axes[idx, 0], left, label + " · 5m execution")
    candle_plot(axes[idx, 1], right, label + " · 15m structure")
    show_left_exit = bool(row["closeTime"] and pd.Timestamp(row["closeTime"]) <= left.index.max())
    overlay(axes[idx, 0], row, show_exit=show_left_exit)
    overlay(axes[idx, 1], row, show_exit=True)

data = frames[cancelled["pair"]]
submitted = pd.Timestamp(cancelled["orderTime"]); cancel_time = pd.Timestamp(cancelled["cancelTime"])
left = data[(data.index >= submitted - pd.Timedelta(hours=3)) & (data.index <= submitted + pd.Timedelta(hours=8))]
right = resample15(data[(data.index >= submitted - pd.Timedelta(hours=3)) & (data.index <= submitted + pd.Timedelta(hours=18))])
candle_plot(axes[4, 0], left, "ZEC zero-fill timeout · 5m execution")
candle_plot(axes[4, 1], right, "ZEC zero-fill timeout · 15m hindsight path")
for ax in axes[4]:
    ax.scatter(submitted, cancelled["price"], marker="v", s=54, color="#2563EB", zorder=7, label="strategy signal")
    ax.hlines(cancelled["price"], submitted, cancel_time, color="#D97706", linestyle="--", linewidth=1.5, label="pending order")
    ax.scatter(cancel_time, cancelled["price"], marker="x", s=48, color="#D97706", zorder=8, label="zero-fill cancel")

if best_gate:
    item, metric = best_gate
    when = pd.Timestamp(item["time"]); data = frames[item["pair"]]
    left = data[(data.index >= when - pd.Timedelta(hours=3)) & (data.index <= when + pd.Timedelta(hours=8))]
    right = resample15(data[(data.index >= when - pd.Timedelta(hours=3)) & (data.index <= when + pd.Timedelta(hours=18))])
    candle_plot(axes[5, 0], left, f"Best blocked path {item['pair']} · 5m")
    candle_plot(axes[5, 1], right, f"Best blocked path {item['pair']} · 15m (hindsight)")
    price = float(data[data.index >= when.floor("5min")].iloc[0].close)
    for ax in axes[5]:
        ax.scatter(when, price, marker="x", s=55, color="#64748B", zorder=8, label="risk block")

for row_axes in axes:
    for ax in row_axes:
        handles, labels = ax.get_legend_handles_labels()
        if handles:
            unique = dict(zip(labels, handles))
            ax.legend(unique.values(), unique.keys(), fontsize=6, loc="best")
fig.suptitle("Bot1 48h causal order review · authentic Freqtrade analyzed candles", fontsize=14, fontweight="bold")
fig.savefig(ROOT / "layered-cases.png", dpi=165, facecolor="white")
plt.close(fig)

print(json.dumps(metrics, ensure_ascii=False, indent=2))