#!/usr/bin/env python3
"""Render the 2026-07-17..19 D48 forward-review charts from live read-only evidence."""

from __future__ import annotations

import base64
import json
import re
import subprocess
import sys
import urllib.parse
import urllib.request
from datetime import datetime, timedelta, timezone
from pathlib import Path
from zoneinfo import ZoneInfo

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from matplotlib.lines import Line2D
from matplotlib.patches import Rectangle
from matplotlib.ticker import FuncFormatter


HOST = "ubuntu@43.164.75.52"
WINDOW_START = "2026-07-17T07:01:08Z"
WINDOW_END = "2026-07-19T07:01:08Z"
PAIRS = ["EDGE/USDT:USDT", "VANRY/USDT:USDT", "POWER/USDT:USDT"]
OUT_DIR = Path(__file__).resolve().parent

COLORS = {
    "up": "#00A344",
    "down": "#D50000",
    "signal": "#2563EB",
    "block": "#64748B",
    "order": "#D97706",
    "fill": "#111827",
    "stop": "#DC2626",
    "exit": "#0F766E",
    "profit": "#15803D",
    "loss": "#B91C1C",
    "indicator": "#087F8C",
    "secondary": "#5D6B78",
    "border": "#D9E0E7",
    "surface_subtle": "#F4F6F8",
}

REMOTE_SCRIPT = r'''
import base64, json, re, sqlite3, sys, urllib.parse, urllib.request
from datetime import datetime, timezone

start = datetime.fromisoformat(sys.argv[1].replace("Z", "+00:00"))
end = datetime.fromisoformat(sys.argv[2].replace("Z", "+00:00"))
pairs = sys.argv[3:]
start_sql = start.astimezone(timezone.utc).replace(tzinfo=None).isoformat(sep=" ", timespec="microseconds")
end_sql = end.astimezone(timezone.utc).replace(tzinfo=None).isoformat(sep=" ", timespec="microseconds")

cfg = json.load(open("/data/freqtrade/user_data/config.json"))
token = base64.b64encode((cfg["api_server"]["username"] + ":" + cfg["api_server"]["password"]).encode()).decode()

def fetch_candles(pair):
    query = urllib.parse.urlencode({"pair": pair, "timeframe": "5m", "limit": "1000"})
    request = urllib.request.Request(
        "http://127.0.0.1:8080/api/v1/pair_candles?" + query,
        headers={"Authorization": "Basic " + token, "Accept": "application/json"},
    )
    with urllib.request.urlopen(request, timeout=10) as response:
        payload = json.load(response)
    return [dict(zip(payload["columns"], row)) for row in payload["data"]]

def db_payload(path):
    con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    con.row_factory = sqlite3.Row
    trades = [dict(row) for row in con.execute(
        """SELECT id,pair,is_short,open_date,close_date,open_rate,close_rate,stake_amount,
                  leverage,close_profit,close_profit_abs,exit_reason,stop_loss
           FROM trades WHERE open_date >= ? AND open_date < ? ORDER BY open_date""",
        (start_sql, end_sql),
    )]
    orders = {}
    for trade in trades:
        orders[str(trade["id"])] = [dict(row) for row in con.execute(
            """SELECT id,ft_order_side,order_type,status,price,average,amount,filled,
                      order_date,order_filled_date,order_update_date,stop_price,ft_is_open
               FROM orders WHERE ft_trade_id=? ORDER BY order_date,id""",
            (trade["id"],),
        )]
    return {"trades": trades, "orders": orders}

block_re = re.compile(
    r"^(?P<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}).*"
    r"entry_blocked reason=(?P<reason>\S+) pair=(?P<pair>\S+) side=(?P<side>\S+) occupied=(?P<occupied>.*)$"
)
blocks = []
with open("/data/freqtrade/user_data/logs/freqtrade.log", errors="replace") as handle:
    for line in handle:
        match = block_re.search(line)
        if not match:
            continue
        stamp = datetime.fromisoformat(match.group("time")).replace(tzinfo=timezone.utc)
        if start <= stamp < end:
            blocks.append({**match.groupdict(), "time": stamp.isoformat()})

result = {
    "fetched_at": datetime.now(timezone.utc).isoformat(),
    "window": {"start": sys.argv[1], "end": sys.argv[2]},
    "strategy": {"name": "VolatilityBreakout", "version": "5.3-d48-shortgate", "timeframe": "5m", "mode": "dry-run"},
    "candles": {pair: fetch_candles(pair) for pair in pairs},
    "same": db_payload("/data/freqtrade/user_data/tradesv3-v5.sqlite"),
    "other": db_payload("/data/freqtrade/user_data/tradesv3-entry-other-forward.sqlite"),
    "blocks": blocks,
}
print(json.dumps(result, separators=(",", ":"), default=str))
'''


def fetch_evidence() -> dict:
    command = ["ssh", HOST, "python3", "-", WINDOW_START, WINDOW_END, *PAIRS]
    completed = subprocess.run(
        command,
        input=REMOTE_SCRIPT,
        text=True,
        capture_output=True,
        check=True,
        timeout=60,
    )
    return json.loads(completed.stdout)


def utc(value: str | pd.Timestamp) -> pd.Timestamp:
    return pd.Timestamp(value, tz="UTC") if pd.Timestamp(value).tzinfo is None else pd.Timestamp(value).tz_convert("UTC")


def candle_frame(rows: list[dict]) -> pd.DataFrame:
    frame = pd.DataFrame(rows)
    frame["date"] = pd.to_datetime(frame["date"], utc=True)
    frame = frame.set_index("date").sort_index()
    for column in [
        "open", "high", "low", "close", "volume", "atr", "atr_baseline",
        "donchian_high", "donchian_low", "adx", "ema_trend",
    ]:
        frame[column] = pd.to_numeric(frame[column], errors="coerce")
    return frame


def beijing_formatter(value: float, _position: int) -> str:
    dt = mdates.num2date(value).astimezone(ZoneInfo("Asia/Shanghai"))
    return dt.strftime("%m-%d\n%H:%M")


def price_precision(pair: str) -> int:
    return {"EDGE/USDT:USDT": 4, "VANRY/USDT:USDT": 6, "POWER/USDT:USDT": 5}[pair]


def draw_candles(ax: plt.Axes, frame: pd.DataFrame, timeframe_minutes: int) -> None:
    width = timeframe_minutes / 1440 * 0.68
    price_range = max(frame["high"].max() - frame["low"].min(), 1e-12)
    min_height = price_range / 1200
    for stamp, row in frame.iterrows():
        x = mdates.date2num(stamp.to_pydatetime())
        color = COLORS["up"] if row["close"] >= row["open"] else COLORS["down"]
        ax.vlines(x, row["low"], row["high"], color=color, linewidth=0.7, zorder=2)
        height = max(abs(row["close"] - row["open"]), min_height)
        bottom = (row["open"] + row["close"] - height) / 2 if abs(row["close"] - row["open"]) < min_height else min(row["open"], row["close"])
        ax.add_patch(Rectangle((x - width / 2, bottom), width, height, facecolor=color, edgecolor=color, linewidth=0, zorder=2))


def style_axes(ax: plt.Axes, pair: str | None = None) -> None:
    ax.set_facecolor("white")
    ax.yaxis.grid(True, color="gray", alpha=0.10, linewidth=0.8, zorder=0)
    ax.xaxis.grid(True, color="gray", alpha=0.10, linewidth=0.8, zorder=0)
    for spine in ax.spines.values():
        spine.set_color("gray")
        spine.set_alpha(0.30)
    ax.tick_params(axis="both", labelsize=8, colors="gray")
    ax.xaxis.set_major_formatter(FuncFormatter(beijing_formatter))
    if pair:
        precision = price_precision(pair)
        ax.yaxis.set_major_formatter(FuncFormatter(lambda value, _pos: f"{value:,.{precision}f}"))


def entry_order(trade: dict, orders: list[dict]) -> dict:
    entry_side = "sell" if trade["is_short"] else "buy"
    return next(order for order in orders if order["order_type"] == "limit" and order["ft_order_side"] == entry_side)


def signal_row(frame: pd.DataFrame, order_time: pd.Timestamp, side: str = "short") -> tuple[pd.Timestamp, pd.Series]:
    column = "enter_short" if side == "short" else "enter_long"
    # A 5m candle stamped 02:20 becomes eligible at 02:25.  Never attribute an
    # order to the still-forming 02:25 candle just because the API later shows it.
    eligible = frame.index + pd.Timedelta(minutes=5) <= order_time
    candidates = frame.loc[eligible & (frame.index >= order_time - pd.Timedelta(minutes=15))]
    candidates = candidates[pd.to_numeric(candidates[column], errors="coerce").fillna(0) == 1]
    if candidates.empty:
        raise RuntimeError(f"No authentic {column} signal before {order_time}")
    return candidates.index[-1], candidates.iloc[-1]


def draw_execution_chart(evidence: dict, frames: dict[str, pd.DataFrame]) -> tuple[Path, list[dict]]:
    trades = evidence["same"]["trades"]
    fig = plt.figure(figsize=(20, 16), dpi=150, facecolor="white")
    outer = fig.add_gridspec(2, 2, hspace=0.32, wspace=0.18)
    metrics: list[dict] = []

    for index, trade in enumerate(trades):
        pair = trade["pair"]
        orders = evidence["same"]["orders"][str(trade["id"])]
        entry = entry_order(trade, orders)
        order_time = utc(entry["order_date"])
        fill_time = utc(entry["order_filled_date"])
        exit_time = utc(trade["close_date"])
        signal_time, signal = signal_row(frames[pair], order_time)
        context_start = signal_time - pd.Timedelta(hours=2)
        context_end = exit_time + pd.Timedelta(hours=1)
        local = frames[pair].loc[context_start:context_end].copy()

        inner = outer[index].subgridspec(4, 1, height_ratios=[3.4, 0.01, 1, 0.72], hspace=0.03)
        ax = fig.add_subplot(inner[0])
        ax_vol = fig.add_subplot(inner[2], sharex=ax)
        ax_ind = fig.add_subplot(inner[3], sharex=ax)

        draw_candles(ax, local, 5)
        ax.plot(local.index, local["donchian_low"], color=COLORS["indicator"], linewidth=1.0, alpha=0.9, label="Donchian48 low (strategy)", zorder=3)
        ax.plot(local.index, local["ema_trend"], color=COLORS["secondary"], linewidth=0.9, alpha=0.75, label="EMA200 (strategy)", zorder=3)

        signal_y = float(signal["high"]) * 1.006
        ax.scatter(signal_time, signal_y, marker="v", s=70, color=COLORS["signal"], edgecolor="white", linewidth=0.5, zorder=7)
        ax.annotate(f"5m authentic short signal\n{signal_time.tz_convert('Asia/Shanghai'):%m-%d %H:%M}", (signal_time, signal_y), xytext=(0, 16), textcoords="offset points", ha="center", fontsize=8, color=COLORS["signal"])

        intended = float(entry["price"])
        ax.hlines(intended, order_time, fill_time, color=COLORS["order"], linestyle="--", linewidth=2.0, zorder=5)
        ax.scatter(fill_time, float(entry["average"]), marker="o", s=38, color=COLORS["fill"], zorder=8)
        latency = (fill_time - order_time).total_seconds()
        ax.annotate(f"fill {latency:.1f}s", (fill_time, float(entry["average"])), xytext=(6, -15), textcoords="offset points", fontsize=8, color=COLORS["fill"])

        stop_orders = [order for order in orders if order["ft_order_side"] == "stoploss" and order.get("stop_price")]
        for stop in stop_orders:
            left = utc(stop["order_date"])
            right_raw = stop.get("order_filled_date") or stop.get("order_update_date") or trade["close_date"]
            right = utc(right_raw)
            ax.hlines(float(stop["stop_price"]), left, right, color=COLORS["stop"], linewidth=1.15, alpha=0.92, zorder=4)

        pnl = float(trade["close_profit_abs"])
        outcome_color = COLORS["profit"] if pnl > 0 else COLORS["loss"]
        ax.scatter(exit_time, float(trade["close_rate"]), marker="D", s=58, color=COLORS["stop"], edgecolor="white", linewidth=0.6, zorder=9)
        exit_offset = -31 if pnl < 0 else 19
        ax.annotate(f"stop / trailing-stop exit\n{pnl:+.2f} USDT", (exit_time, float(trade["close_rate"])), xytext=(-6, exit_offset), textcoords="offset points", ha="right", fontsize=8.5, color=outcome_color, fontweight="bold")

        volume_colors = np.where(local["close"] >= local["open"], COLORS["up"], COLORS["down"])
        ax_vol.bar(local.index, local["volume"], width=5 / 1440 * 0.68, color=volume_colors, alpha=0.75)
        ax_vol.set_ylabel("Volume", fontsize=8, color="gray")
        ax_ind.plot(local.index, local["adx"], color=COLORS["indicator"], linewidth=1.0, label="ADX")
        ax_ind.axhline(25, color=COLORS["order"], linestyle="--", linewidth=0.8, label="ADX=25")
        atr_ratio = local["atr"] / local["atr_baseline"]
        ax_ind.plot(local.index, atr_ratio * 25, color=COLORS["secondary"], linewidth=0.8, alpha=0.8, label="ATR/baseline x25")
        ax_ind.set_ylabel("Momentum", fontsize=8, color="gray")

        hold = frames[pair].loc[fill_time:exit_time]
        entry_price = float(entry["average"])
        mfe = (entry_price - hold["low"].min()) / entry_price
        mae = (entry_price - hold["high"].max()) / entry_price
        future = frames[pair].loc[signal_time:signal_time + pd.Timedelta(hours=2)].iloc[1:]
        boundary = float(signal["donchian_low"])
        reclaim = future[future["close"] > boundary]
        breakout_atr = (boundary - float(signal["close"])) / float(signal["atr"])
        candle_range = max(float(signal["high"] - signal["low"]), 1e-12)
        body_ratio = abs(float(signal["close"] - signal["open"])) / candle_range
        close_location = (float(signal["close"] - signal["low"])) / candle_range
        frame_15m = resample_15m(frames[pair].loc[: signal_time + pd.Timedelta(minutes=5)])
        range_high_15m = frame_15m["high"].rolling(32).max().shift(1)
        range_low_15m = frame_15m["low"].rolling(32).min().shift(1)
        # Use the latest 15m candle that was fully complete when the 5m signal
        # became eligible.  For a 09:00 5m signal evaluated at 09:05, that is
        # the 08:45 15m candle, not the still-forming 09:00 candle.
        context_pos = len(frame_15m) - 1
        prior_pos = max(0, context_pos - 4)
        context_close = float(frame_15m.iloc[context_pos]["close"])
        close_2h_ago = float(frame_15m.iloc[max(0, context_pos - 8)]["close"])
        metrics.append({
            "trade_id": trade["id"],
            "pair": pair,
            "signal_time": signal_time.isoformat(),
            "breakout_atr": breakout_atr,
            "body_ratio": body_ratio,
            "close_location": close_location,
            "adx": float(signal["adx"]),
            "atr_ratio": float(signal["atr"] / signal["atr_baseline"]),
            "ema_distance_atr": float((signal["ema_trend"] - signal["close"]) / signal["atr"]),
            "return_15m_last_2h_pct": (context_close / close_2h_ago - 1) * 100,
            "range_high_15m_change_1h_pct": (float(range_high_15m.iloc[context_pos]) / float(range_high_15m.iloc[prior_pos]) - 1) * 100,
            "range_low_15m_change_1h_pct": (float(range_low_15m.iloc[context_pos]) / float(range_low_15m.iloc[prior_pos]) - 1) * 100,
            "reclaim_within_2h": not reclaim.empty,
            "bars_to_reclaim": None if reclaim.empty else int((reclaim.index[0] - signal_time) / pd.Timedelta(minutes=5)),
            "mfe_unlevered_pct": mfe * 100,
            "mae_unlevered_pct": mae * 100,
            "pnl_usdt": pnl,
            "fill_latency_s": latency,
        })

        ax.set_title(
            f"{pair.replace(':USDT', '')} short #{trade['id']}  |  dry-run  |  {pnl:+.2f} USDT",
            fontsize=12,
            fontweight="bold",
            color="#17212B",
            pad=7,
        )
        ax.set_ylabel("Price (USDT)", fontsize=9, color="gray")
        ax.set_xlim(context_start, context_end)
        style_axes(ax, pair)
        style_axes(ax_vol)
        style_axes(ax_ind)
        ax.tick_params(axis="x", labelbottom=False)
        ax_vol.tick_params(axis="x", labelbottom=False)
        ax_ind.tick_params(axis="x", labelrotation=0)
        ax.legend(loc="upper left", fontsize=7, frameon=True, edgecolor=COLORS["border"], ncol=2)
        ax_ind.legend(loc="upper left", fontsize=6.5, frameon=False, ncol=3)

    fig.suptitle(
        "D48 last 48h: 5m execution paths (bot1 / price_side=same)\n"
        "Blue=authentic strategy signal | amber dashed=real order | black=real dry-run fill | red=active stop | x-axis=UTC+8",
        fontsize=17,
        fontweight="bold",
        color="#17212B",
        y=0.995,
    )
    path = OUT_DIR / "d48_48h_execution_5m.png"
    fig.savefig(path, dpi=150, facecolor="white", bbox_inches="tight")
    plt.close(fig)
    return path, metrics


def resample_15m(frame: pd.DataFrame) -> pd.DataFrame:
    result = frame[["open", "high", "low", "close", "volume"]].resample("15min", label="left", closed="left").agg({
        "open": "first", "high": "max", "low": "min", "close": "last", "volume": "sum",
    })
    counts = frame["close"].resample("15min", label="left", closed="left").count()
    return result[counts == 3].dropna()


def structure_annotation(ax: plt.Axes, when: str, price: float, text: str) -> None:
    ax.annotate(
        text + "\n(hindsight annotation)",
        (utc(when), price),
        xytext=(18, 24),
        textcoords="offset points",
        fontsize=8,
        color="#6B7280",
        arrowprops={"arrowstyle": "->", "color": "#9CA3AF", "linestyle": ":", "linewidth": 1},
        bbox={"boxstyle": "round,pad=0.3", "facecolor": "white", "edgecolor": "#9CA3AF", "linestyle": ":", "alpha": 0.92},
        zorder=8,
    )


def draw_structure_chart(evidence: dict, frames: dict[str, pd.DataFrame]) -> Path:
    start = utc(WINDOW_START)
    end = utc(WINDOW_END)
    fig, axes = plt.subplots(3, 1, figsize=(20, 15), dpi=150, facecolor="white", sharex=True)
    trades_by_pair: dict[str, list[dict]] = {pair: [] for pair in PAIRS}
    for trade in evidence["same"]["trades"]:
        trades_by_pair[trade["pair"]].append(trade)

    for ax, pair in zip(axes, PAIRS):
        source = frames[pair].loc[start:end]
        frame = resample_15m(source)
        draw_candles(ax, frame, 15)
        rolling_low = frame["low"].rolling(32).min().shift(1)
        rolling_high = frame["high"].rolling(32).max().shift(1)
        ax.plot(frame.index, rolling_low, color="#9CA3AF", linestyle=":", linewidth=0.9, label="15m 8h range low (review-only)")
        ax.plot(frame.index, rolling_high, color="#9CA3AF", linestyle=":", linewidth=0.9, alpha=0.75, label="15m 8h range high (review-only)")

        authentic = source[pd.to_numeric(source["enter_short"], errors="coerce").fillna(0) == 1]
        for stamp, row in authentic.iterrows():
            ax.scatter(stamp, row["high"] * 1.005, marker="v", s=42, color=COLORS["signal"], zorder=6)

        for block in evidence["blocks"]:
            if block["pair"] != pair:
                continue
            stamp = utc(block["time"])
            nearby = source.loc[:stamp].tail(1)
            if not nearby.empty:
                ax.scatter(stamp, nearby.iloc[0]["close"], marker="x", s=48, color=COLORS["block"], linewidth=1.5, zorder=7)

        for trade in trades_by_pair[pair]:
            orders = evidence["same"]["orders"][str(trade["id"])]
            entry = entry_order(trade, orders)
            fill_time = utc(entry["order_filled_date"])
            exit_time = utc(trade["close_date"])
            ax.scatter(fill_time, float(entry["average"]), marker="o", s=38, color=COLORS["fill"], zorder=8)
            ax.scatter(exit_time, float(trade["close_rate"]), marker="D", s=48, color=COLORS["stop"], edgecolor="white", linewidth=0.5, zorder=8)
            ax.axvspan(fill_time, exit_time, color=COLORS["surface_subtle"], alpha=0.45, zorder=0)

        if pair == "EDGE/USDT:USDT":
            structure_annotation(ax, "2026-07-18T02:10:00Z", 0.4297, "First break reclaimed: failed_breakout")
            structure_annotation(ax, "2026-07-18T17:05:00Z", 0.4290, "Second break extended: breakout_followthrough")
        elif pair == "VANRY/USDT:USDT":
            structure_annotation(ax, "2026-07-18T02:20:00Z", 0.005234, "Shallow break, sustained expansion: followthrough")
        elif pair == "POWER/USDT:USDT":
            structure_annotation(ax, "2026-07-18T09:00:00Z", 0.08024, "Deep break then reclaim: failed_breakout")

        ax.set_title(pair.replace(":USDT", "") + " perpetual — 15m technical structure", fontsize=12, fontweight="bold", color="#17212B")
        ax.set_ylabel("Price (USDT)", fontsize=9, color="gray")
        ax.set_xlim(start, end)
        style_axes(ax, pair)
        ax.legend(loc="upper left", fontsize=7, frameon=True, edgecolor=COLORS["border"], ncol=2)

    handles = [
        Line2D([0], [0], marker="v", color="none", markerfacecolor=COLORS["signal"], markeredgecolor=COLORS["signal"], label="Authentic 5m signal (exact timestamp)"),
        Line2D([0], [0], marker="x", color=COLORS["block"], linestyle="none", label="Real risk-gate block"),
        Line2D([0], [0], marker="o", color="none", markerfacecolor=COLORS["fill"], label="Real dry-run fill"),
        Line2D([0], [0], marker="D", color="none", markerfacecolor=COLORS["stop"], label="Stop / trailing-stop exit"),
    ]
    axes[-1].legend(handles=handles, loc="lower left", fontsize=8, frameon=True, edgecolor=COLORS["border"], ncol=4)
    axes[-1].set_xlabel("UTC+8 | 15m candles aggregated from Freqtrade 5m OHLC; signal timestamps are not resampled", fontsize=9, color="gray")
    fig.suptitle(
        "D48 last 48h: 15m structure with the authentic 5m decision chain\n"
        "Gray dotted lines and text are review-only/hindsight annotations, never reference signals",
        fontsize=17,
        fontweight="bold",
        color="#17212B",
        y=0.995,
    )
    fig.tight_layout(rect=[0, 0, 1, 0.965])
    path = OUT_DIR / "d48_48h_structure_15m.png"
    fig.savefig(path, dpi=150, facecolor="white", bbox_inches="tight")
    plt.close(fig)
    return path


def main() -> int:
    plt.rcParams["font.sans-serif"] = ["DejaVu Sans"]
    plt.rcParams["axes.unicode_minus"] = False
    evidence = fetch_evidence()
    frames = {pair: candle_frame(rows) for pair, rows in evidence["candles"].items()}
    execution_path, metrics = draw_execution_chart(evidence, frames)
    structure_path = draw_structure_chart(evidence, frames)
    print(json.dumps({
        "fetched_at": evidence["fetched_at"],
        "execution_chart": str(execution_path),
        "structure_chart": str(structure_path),
        "signal_metrics": metrics,
    }, ensure_ascii=False, indent=2))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())