#!/usr/bin/env python3
"""Render the preserved 2026-07-16..17 D48 vs RiskCap-active-pool forward window."""

from __future__ import annotations

import json
import subprocess
from pathlib import Path

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import pandas as pd


HERE = Path(__file__).resolve().parent
HOST = "ubuntu@43.164.75.52"
START = "2026-07-16T03:18:00Z"
END = "2026-07-17T03:48:49Z"

REMOTE = r'''
import json,sqlite3,sys
from collections import Counter
from datetime import datetime,timezone
def norm(x):
 d=datetime.fromisoformat(x.replace("Z","+00:00")); return d.astimezone(timezone.utc).replace(tzinfo=None).isoformat(sep=" ",timespec="microseconds")
s,e=norm(sys.argv[1]),norm(sys.argv[2])
def read(label,db):
 con=sqlite3.connect(f"file:{db}?mode=ro",uri=True); con.row_factory=sqlite3.Row
 cols="id,pair,is_short,is_open,open_date,close_date,open_rate,close_rate,stake_amount,leverage,close_profit,close_profit_abs,exit_reason,strategy,timeframe"
 rows=lambda q,p:[dict(r) for r in con.execute(q,p)]
 opened=rows(f"select {cols} from trades where open_date>=? and open_date<? order by open_date",(s,e))
 carried=rows(f"select {cols} from trades where open_date<? and (close_date is null or close_date>=?) order by open_date",(s,s))
 exits=rows(f"select {cols} from trades where close_date>=? and close_date<? order by close_date",(s,e))
 closed=[r for r in opened if r["close_date"] and r["close_date"]<e]
 return {"label":label,"db":db,"opened":opened,"carried":carried,"exits":exits,"closed_opened":closed}
print(json.dumps({"window":{"start":sys.argv[1],"end":sys.argv[2]},"datasets":[read("D48","/data/freqtrade/user_data/tradesv3-v5.sqlite"),read("RiskCap + active21","/data/freqtrade/user_data/tradesv3-riskcap-active-forward.sqlite")]},separators=(",",":")))
'''


def fetch() -> dict:
    result = subprocess.run(["ssh", HOST, "python3", "-", START, END], input=REMOTE, text=True, capture_output=True, check=True, timeout=60)
    return json.loads(result.stdout)


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


def main() -> int:
    data=fetch(); d48,risk=data["datasets"]; start,end=ts(START),ts(END)
    fig=plt.figure(figsize=(18,10),dpi=160,facecolor="white"); gs=fig.add_gridspec(2,3,height_ratios=[1.2,1],hspace=.30,wspace=.25); ax=fig.add_subplot(gs[0,:])
    for y,ds in [(1,d48),(0,risk)]:
        ax.hlines(y,start,end,color="#CBD5E1",linewidth=2)
        if ds["carried"]:
            carried_names=", ".join(t["pair"].split('/')[0] for t in ds["carried"])
            ax.scatter(start,y,marker="o",s=90,facecolors="white",edgecolors="#7C3AED",linewidth=2,zorder=5)
            ax.annotate(f"carried: {carried_names}",(start,y),xytext=(8,10),textcoords="offset points",fontsize=8,color="#7C3AED")
        for t in ds["opened"]:
            x=ts(t["open_date"]); ax.scatter(x,y,marker="v",s=90,color="#2563EB",edgecolor="white",linewidth=.7,zorder=6); ax.text(x,y-.16,t["pair"].split('/')[0],ha="center",fontsize=8,color="#1E3A8A")
        for t in ds["exits"]:
            x=ts(t["close_date"]); good=(t["close_profit_abs"] or 0)>0; color="#15803D" if good else "#B91C1C"; ax.scatter(x,y,marker="D",s=65,color=color,edgecolor="white",linewidth=.7,zorder=7); ax.text(x,y+.15,f"{t['close_profit_abs']:+.2f}U",ha="center",fontsize=8,color=color)
    ax.set_yticks([0,1],["RiskCap + active21","D48 main"]); ax.set_xlim(start,end); ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d\n%H:%M",tz=start.tz)); ax.set_title("Forward event timeline · entries, carried exposure, and exits",loc="left",fontweight="bold"); ax.grid(True,axis="x",alpha=.12)

    ax=fig.add_subplot(gs[1,0]); rows=[]
    for ds in [d48,risk]:
        for t in ds["exits"]: rows.append((ds["label"]+"\n"+t["pair"].split('/')[0],t["close_profit_abs"] or 0))
    ax.bar(range(len(rows)),[v for _,v in rows],color=["#15803D" if v>0 else "#B91C1C" for _,v in rows]); ax.set_xticks(range(len(rows)),[k for k,_ in rows],rotation=35,ha="right"); ax.axhline(0,color="#64748B",linewidth=.8); ax.set_title("Realized exits in window (USDT)",loc="left",fontweight="bold")

    ax=fig.add_subplot(gs[1,1]); opened=d48["opened"]+risk["opened"]; colors=["#111827"]*len(d48["opened"])+["#2563EB"]*len(risk["opened"]); labels=[("D48" if i<len(d48["opened"]) else "RC")+"\n"+t["pair"].split('/')[0] for i,t in enumerate(opened)]; ax.bar(range(len(opened)),[t["stake_amount"] for t in opened],color=colors); ax.set_xticks(range(len(opened)),labels,rotation=35,ha="right"); ax.set_title("New-trade stake amount (USDT)",loc="left",fontweight="bold")

    ax=fig.add_subplot(gs[1,2]); ax.axis("off"); rc_closed=risk["closed_opened"]; rc_net=sum(t["close_profit_abs"] or 0 for t in rc_closed); wins=sum((t["close_profit_abs"] or 0)>0 for t in rc_closed); gross_win=sum(t["close_profit_abs"] or 0 for t in rc_closed if (t["close_profit_abs"] or 0)>0); gross_loss=abs(sum(t["close_profit_abs"] or 0 for t in rc_closed if (t["close_profit_abs"] or 0)<=0)); d48_exits=sum(t["close_profit_abs"] or 0 for t in d48["exits"])
    text=(f"RAW WINDOW READ\n\nD48 main\n  carried in: {len(d48['carried'])}\n  new entries: {len(d48['opened'])}\n  exits: {len(d48['exits'])}\n  realized exits: {d48_exits:+.2f} USDT\n\nRiskCap + active21\n  carried in: {len(risk['carried'])}\n  new entries: {len(risk['opened'])}\n  closed new entries: {len(rc_closed)}\n  realized: {rc_net:+.2f} USDT\n  win rate: {wins/len(rc_closed):.0%}\n  PF: {gross_win/gross_loss:.2f}\n\nNOT A CLEAN A/B\n• 12 pairs vs 21 pairs\n• 2 carried positions vs empty start\n• no matched entries\n• sizing and universe changed together")
    ax.text(.02,.98,text,va="top",fontsize=11,color="#334155",bbox={"facecolor":"#F8FAFC","edgecolor":"#CBD5E1","boxstyle":"round,pad=.6"})
    for a in fig.axes[:-1]:
        a.set_facecolor("white");
        for spine in a.spines.values(): spine.set_color("#CBD5E1")
    fig.suptitle("D48 main vs retired RiskCap active-pool forward · 2026-07-16 03:18 to 2026-07-17 03:48 UTC",fontsize=15,fontweight="bold"); fig.text(.5,.025,"SQLite trades/orders · dry-run · 5m · 2x isolated. Raw outcomes are descriptive; cohort mismatch blocks causal attribution to RiskCapFix.",ha="center",fontsize=9,color="#64748B"); fig.tight_layout(rect=[0,.05,1,.94]); out=HERE/"riskcap_forward_comparison.png"; fig.savefig(out,bbox_inches="tight",facecolor="white"); plt.close(fig)
    manifest={"review_id":"2026-07-21-riskcap-forward-comparison","window":{"start":START,"end":END},"sources":[d48["db"],risk["db"],"freqtrade-riskcap-active-forward.log","config-riskcap-forward.json"],"chart":out.name,"analyzed_candles":"unavailable for this retired window from the live 1000-bar API horizon","raw":{"d48":{"carried":len(d48["carried"]),"new_entries":len(d48["opened"]),"exits":len(d48["exits"]),"realized_exit_usdt":d48_exits},"riskcap_active21":{"carried":len(risk["carried"]),"new_entries":len(risk["opened"]),"closed_new":len(rc_closed),"realized_usdt":rc_net,"winrate":wins/len(rc_closed),"pf":gross_win/gross_loss}},"causal_comparison_valid":False,"confounders":["pair universe 12 vs 21","starting exposure two carried positions vs empty","RiskCap sizing and active-pool universe changed together","no matched new entries in the preserved window"],"visual_review":None}
    (HERE/"manifest.json").write_text(json.dumps(manifest,ensure_ascii=False,indent=2)+"\n",encoding="utf-8"); print(json.dumps(manifest,ensure_ascii=False,indent=2)); return 0


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