#!/usr/bin/env python3
"""Hindsight opportunity cost of unfilled (timed-out) passive entry orders.

Labels are hindsight_annotation only — not causal missed signals for promotion.
Mirrors the anchoring and threshold conventions of same_side_opp_cost.py so the
two diagnostics are directly comparable.

Counterfactual: had the bot crossed the spread (entry_pricing.price_side=other)
at order submission time it would have filled at ~the 5m candle open — the same
price the K-line backtest assumes. So the path is anchored on that candle open.

Freqtrade deletes zero-fill timeout entries from the database, so the events can
only be recovered from the logs. Pass every rotated log that covers the window.

Run inside the freqtrade container (the host has no pandas):

    ssh ubuntu@43.164.75.52 'docker exec -i freqtrade python3 -' \
      < freqtrade/scripts/unfilled_entry_opp_cost.py
"""
from __future__ import annotations

import argparse
import re
import sqlite3
from datetime import datetime, timedelta, timezone
from pathlib import Path

import pandas as pd

HORIZONS_H = (6, 24)
# Futures 2x: hard stoploss -5% equity ~= 2.5% price; trail arm +5% profit ~= 2.5% price.
HARD_STOP_PRICE = 0.025
TRAIL_ARM_PRICE = 0.025
# Filled-trade control extends past the last miss so late entries keep a full horizon.
CONTROL_TAIL_DAYS = 9

CANCEL_RE = re.compile(
    r"^(?P<ts>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}),\d+ .* "
    r"(?P<side_word>Buy|Sell) order fully cancelled\. Removing Trade\("
    r"id=(?P<trade_id>\d+), pair=(?P<pair>[^,]+), amount=0, "
    r"is_short=(?P<is_short>True|False), leverage=(?P<lev>[^,]+), "
    r"open_rate=(?P<open_rate>[^,]+), open_since=(?P<open_since>[^)]+)\)"
)


def floor5(value: datetime) -> datetime:
    """Snap to the 5m candle that contains `value`."""
    return value.replace(minute=(value.minute // 5) * 5, second=0, microsecond=0)


def pair_to_feather(data_dir: Path, pair: str) -> Path:
    # BTC/USDT:USDT -> BTC_USDT_USDT-5m-futures.feather
    base, quote = pair.split(":")[0].split("/")
    return data_dir / f"{base}_{quote}_{quote}-5m-futures.feather"


def load_klines(data_dir: Path, pair: str, cache: dict[str, list[dict]]) -> list[dict]:
    if pair in cache:
        return cache[pair]
    path = pair_to_feather(data_dir, pair)
    if not path.exists():
        print(f"  !! missing feather {path.name} — {pair} skipped")
        cache[pair] = []
        return []
    frame = pd.read_feather(path)
    frame["date"] = pd.to_datetime(frame["date"], utc=True)
    cache[pair] = [
        {
            "ts": row.date.to_pydatetime(),
            "open": float(row.open),
            "high": float(row.high),
            "low": float(row.low),
            "close": float(row.close),
        }
        for row in frame.itertuples(index=False)
    ]
    return cache[pair]


def path_metrics(klines: list[dict], t0: datetime, side: str, hours: int):
    t1 = t0 + timedelta(hours=hours)
    window = [k for k in klines if t0 <= k["ts"] < t1]
    if not window:
        return None
    entry = window[0]["open"]
    high = max(k["high"] for k in window)
    low = min(k["low"] for k in window)
    last = window[-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,
        "end_ret": end_ret,
        "bars": len(window),
        "stop_hit": mae >= HARD_STOP_PRICE,
        "trail_armed": mfe >= TRAIL_ARM_PRICE,
    }


def parse_cancelled(text: str) -> list[dict]:
    """Recover deleted zero-fill entry timeouts from one log's contents."""
    events = []
    for line in text.splitlines():
        match = CANCEL_RE.search(line)
        if not match:
            continue
        events.append(
            {
                "cancel_ts": datetime.strptime(match.group("ts"), "%Y-%m-%d %H:%M:%S").replace(
                    tzinfo=timezone.utc
                ),
                "pair": match.group("pair"),
                "side": "short" if match.group("is_short") == "True" else "long",
                "intended_rate": float(match.group("open_rate")),
                "order_ts": datetime.strptime(
                    match.group("open_since"), "%Y-%m-%d %H:%M:%S"
                ).replace(tzinfo=timezone.utc),
            }
        )
    return events


def load_cancelled(logs: list[Path]) -> list[dict]:
    events: list[dict] = []
    for log in logs:
        if not log.exists():
            print(f"  !! missing log {log}")
            continue
        events.extend(parse_cancelled(log.read_text(errors="replace")))
    events.sort(key=lambda e: e["order_ts"])
    return events


def load_filled(db: Path, since: datetime, until: datetime) -> list[dict]:
    con = sqlite3.connect(f"file:{db}?mode=ro", uri=True)
    rows = con.execute(
        "SELECT pair, is_short, open_date, open_rate, close_profit FROM trades ORDER BY open_date"
    ).fetchall()
    con.close()
    out = []
    for pair, is_short, open_date, open_rate, close_profit in rows:
        opened = datetime.fromisoformat(str(open_date)).replace(tzinfo=timezone.utc)
        if not since <= opened <= until:
            continue
        out.append(
            {
                "pair": pair,
                "side": "short" if is_short else "long",
                "order_ts": opened,
                "intended_rate": float(open_rate),
                "close_profit": close_profit,
            }
        )
    return out


def annotate(items: list[dict], data_dir: Path, cache: dict[str, list[dict]]) -> list[dict]:
    for item in items:
        klines = load_klines(data_dir, item["pair"], cache)
        anchor = floor5(item["order_ts"])
        item["anchor"] = anchor
        for hours in HORIZONS_H:
            item[f"h{hours}"] = path_metrics(klines, anchor, item["side"], hours) if klines else None
    return items


def median(values: list[float]) -> float:
    return sorted(values)[len(values) // 2]


def summarize(rows: list[dict], hours: int, label: str) -> None:
    valid = [r for r in rows if r.get(f"h{hours}")]
    if not valid:
        print(f"  {label} h{hours}: no data")
        return
    stop = sum(1 for r in valid if r[f"h{hours}"]["stop_hit"])
    armed = sum(1 for r in valid if r[f"h{hours}"]["trail_armed"])
    print(
        f"  {label:<10} h{hours:>2}  n={len(valid):<3} "
        f"MFE中位={median([r[f'h{hours}']['mfe'] for r in valid]) * 100:6.2f}%  "
        f"MAE中位={median([r[f'h{hours}']['mae'] for r in valid]) * 100:6.2f}%  "
        f"末端中位={median([r[f'h{hours}']['end_ret'] for r in valid]) * 100:6.2f}%  "
        f"先触止损={stop}/{len(valid)}  锁盈激活={armed}/{len(valid)}"
    )


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "--root",
        default="/freqtrade/user_data",
        help="user_data root as seen from inside the container",
    )
    parser.add_argument(
        "--log",
        action="append",
        default=None,
        help="log file(s) to scan; repeat for rotated logs (default: freqtrade.log.1 + freqtrade.log)",
    )
    parser.add_argument("--db", default=None, help="baseline bot database (default: tradesv3-v5.sqlite)")
    args = parser.parse_args()

    root = Path(args.root)
    logs = (
        [Path(p) for p in args.log]
        if args.log
        else [root / "logs" / "freqtrade.log.1", root / "logs" / "freqtrade.log"]
    )
    db = Path(args.db) if args.db else root / "tradesv3-v5.sqlite"
    data_dir = root / "data" / "binance" / "futures"
    cache: dict[str, list[dict]] = {}

    cancelled = annotate(load_cancelled(logs), data_dir, cache)
    if not cancelled:
        print("no cancelled entry events found")
        return
    first = min(e["order_ts"] for e in cancelled)
    last = max(e["order_ts"] for e in cancelled)
    control_until = last + timedelta(days=CONTROL_TAIL_DAYS)
    filled = annotate(load_filled(db, first, control_until), data_dir, cache)

    print(f"\n=== 漏单事件（被动限价超时删单）n={len(cancelled)} ===")
    for event in cancelled:
        line = (
            f"{event['order_ts']:%Y-%m-%d %H:%M} {event['pair']:<18} {event['side']:<6} "
            f"意向价={event['intended_rate']:<10.6g}"
        )
        for hours in HORIZONS_H:
            metrics = event[f"h{hours}"]
            if not metrics:
                line += f" | h{hours}: 无数据"
                continue
            line += (
                f" | h{hours}: 锚价={metrics['entry']:.6g} MFE={metrics['mfe'] * 100:+.2f}% "
                f"MAE={metrics['mae'] * 100:+.2f}% 末端={metrics['end_ret'] * 100:+.2f}%"
                f"{' 先止损' if metrics['stop_hit'] else ''}"
                f"{' 锁盈' if metrics['trail_armed'] and not metrics['stop_hit'] else ''}"
            )
        print(line)

    print(
        f"\n=== 对照：同期已成交入场 n={len(filled)} "
        f"（{first:%m-%d}~{control_until:%m-%d}）==="
    )
    for hours in HORIZONS_H:
        summarize(cancelled, hours, "漏单")
        summarize(filled, hours, "已成交")
        print()

    realized = [f["close_profit"] for f in filled if f["close_profit"] is not None]
    if realized:
        wins = sum(1 for r in realized if r > 0)
        print(
            f"已成交单实际结果: n={len(realized)} 中位收益={median(realized) * 100:+.2f}% "
            f"胜率={wins}/{len(realized)} ({wins / len(realized) * 100:.0f}%)"
        )
    print("\n标签: hindsight_annotation — 不可作为可部署因果漏信号或晋级依据。")


if __name__ == "__main__":
    main()