#!/usr/bin/env python3
"""Compare passive (same-side) and crossing (other-side) entry pricing.

The causal cohort is a matched order opportunity: both bots submitted an entry
for the same pair and direction at approximately the same time. Fully cancelled
zero-fill entries are recovered from the logs because Freqtrade removes their
trade and order rows from SQLite.

Example:

    ssh HOST python3 - \
      --db-same /data/freqtrade/user_data/tradesv3-v5.sqlite \
      --log-same /data/freqtrade/user_data/logs/freqtrade.log \
      --db-other /data/freqtrade/user_data/tradesv3-entry-other-forward.sqlite \
      --log-other /data/freqtrade/user_data/logs/freqtrade-entry-other-forward.log \
      --since 2026-07-17T03:49:02Z \
      < freqtrade/scripts/entry_pricing_compare.py
"""

from __future__ import annotations

import argparse
import json
import math
import re
import sqlite3
from datetime import datetime, timedelta, timezone
from pathlib import Path
from statistics import mean, median


ENTRY_DELETE_RE = re.compile(
    r"^(?P<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}),\d+ .* "
    r"(?P<side>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),.*open_rate=(?P<open_rate>[^,]+),"
)


def normalize_since(value: str) -> str:
    parsed = datetime.fromisoformat(value.replace("Z", "+00:00"))
    if parsed.tzinfo is not None:
        parsed = parsed.astimezone(timezone.utc).replace(tzinfo=None)
    return parsed.isoformat(sep=" ", timespec="microseconds")


def entry_side(is_short: bool) -> str:
    return "sell" if is_short else "buy"


def load_filled_entries(db_path: str, since: str) -> list[dict]:
    con = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    con.row_factory = sqlite3.Row
    rows = con.execute(
        """
        SELECT t.id AS trade_id, t.pair, t.is_short, t.is_open, t.open_date,
               t.close_date, t.close_profit_abs,
               o.order_date, o.order_filled_date, o.price, o.average
        FROM trades t
        JOIN orders o ON o.ft_trade_id = t.id
        WHERE t.open_date >= ?
              AND o.order_type = 'limit'
              AND o.status = 'closed'
        ORDER BY o.order_date, o.id
        """,
        (since,),
    )
    entries = []
    for row in rows:
        item = dict(row)
        if item["order_date"] is None or item["order_filled_date"] is None:
            continue
        if item["average"] is None or item["average"] <= 0:
            continue
        side = entry_side(bool(item["is_short"]))
        order = con.execute(
            "SELECT ft_order_side FROM orders WHERE ft_trade_id=? AND order_date=?",
            (item["trade_id"], item["order_date"]),
        ).fetchone()
        if order is None or order["ft_order_side"] != side:
            continue
        item.update(
            {
                "side": "short" if item["is_short"] else "long",
                "status": "filled",
                "submitted_at": item["order_date"],
                "submitted_at_provenance": "sqlite_order",
                "fill_latency_s": (
                    datetime.fromisoformat(item["order_filled_date"])
                    - datetime.fromisoformat(item["order_date"])
                ).total_seconds(),
                "fill_price": float(item["average"]),
                "intended_price": float(item["price"]),
            }
        )
        entries.append(item)
    con.close()
    return entries


def load_cancelled_entries(
    log_path: str,
    since: str,
    entry_timeout_minutes: float,
) -> list[dict]:
    cutoff = datetime.fromisoformat(since)
    timeout = timedelta(minutes=entry_timeout_minutes)
    entries = []
    with Path(log_path).open(encoding="utf-8", errors="replace") as handle:
        for line in handle:
            match = ENTRY_DELETE_RE.search(line)
            if match is None:
                continue
            cancelled_at = datetime.fromisoformat(match.group("time"))
            submitted_at = cancelled_at - timeout
            if submitted_at < cutoff:
                continue
            entries.append(
                {
                    "trade_id": int(match.group("trade_id")),
                    "pair": match.group("pair"),
                    "is_short": match.group("is_short") == "True",
                    "side": "short" if match.group("is_short") == "True" else "long",
                    "status": "cancelled",
                    "submitted_at": submitted_at.isoformat(sep=" ", timespec="microseconds"),
                    "submitted_at_provenance": f"cancel_time_minus_{entry_timeout_minutes:g}m",
                    "cancelled_at": cancelled_at.isoformat(sep=" ", timespec="microseconds"),
                    "fill_latency_s": None,
                    "fill_price": None,
                    "intended_price": float(match.group("open_rate")),
                    "is_open": False,
                    "close_date": None,
                    "close_profit_abs": None,
                }
            )
    return entries


def load_attempts(
    db_path: str,
    log_path: str,
    since: str,
    entry_timeout_minutes: float,
) -> list[dict]:
    rows = [
        *load_filled_entries(db_path, since),
        *load_cancelled_entries(log_path, since, entry_timeout_minutes),
    ]
    return sorted(rows, key=lambda row: row["submitted_at"])


def match_attempts(
    same: list[dict],
    other: list[dict],
    tolerance_seconds: float,
) -> tuple[list[dict], list[dict], list[dict]]:
    used_other: set[int] = set()
    matched = []
    unmatched_same = []
    for left in same:
        left_time = datetime.fromisoformat(left["submitted_at"])
        candidates = []
        for index, right in enumerate(other):
            if index in used_other:
                continue
            if (right["pair"], right["side"]) != (left["pair"], left["side"]):
                continue
            delta = abs(
                (
                    datetime.fromisoformat(right["submitted_at"]) - left_time
                ).total_seconds()
            )
            if delta <= tolerance_seconds:
                candidates.append((delta, index, right))
        if not candidates:
            unmatched_same.append(left)
            continue
        delta, index, right = min(candidates, key=lambda item: item[0])
        used_other.add(index)
        matched.append({"same": left, "other": right, "submitted_delta_s": delta})
    unmatched_other = [
        row for index, row in enumerate(other) if index not in used_other
    ]
    return matched, unmatched_same, unmatched_other


def adverse_bps(same: dict, other: dict) -> float | None:
    if same["status"] != "filled" or other["status"] != "filled":
        return None
    same_price = same["fill_price"]
    other_price = other["fill_price"]
    if same["side"] == "short":
        return (same_price - other_price) / same_price * 10_000
    return (other_price - same_price) / same_price * 10_000


def finite(values: list[float | None]) -> list[float]:
    return [
        value
        for value in values
        if value is not None and math.isfinite(value)
    ]


def summarize(
    matched: list[dict],
    unmatched_same: list[dict],
    unmatched_other: list[dict],
    minimum_opportunities: int,
) -> dict:
    both_filled = [
        item
        for item in matched
        if item["same"]["status"] == item["other"]["status"] == "filled"
    ]
    recovered = [
        item
        for item in matched
        if item["same"]["status"] == "cancelled"
        and item["other"]["status"] == "filled"
    ]
    reverse = [
        item
        for item in matched
        if item["same"]["status"] == "filled"
        and item["other"]["status"] == "cancelled"
    ]
    paired_costs = finite([adverse_bps(item["same"], item["other"]) for item in both_filled])
    same_latencies = finite([item["same"]["fill_latency_s"] for item in both_filled])
    other_latencies = finite([item["other"]["fill_latency_s"] for item in both_filled])
    paired_closed = [
        item
        for item in both_filled
        if not item["same"]["is_open"]
        and not item["other"]["is_open"]
        and item["same"]["close_profit_abs"] is not None
        and item["other"]["close_profit_abs"] is not None
    ]
    paired_pnl_delta = [
        item["other"]["close_profit_abs"] - item["same"]["close_profit_abs"]
        for item in paired_closed
    ]
    matched_count = len(matched)
    same_filled = sum(item["same"]["status"] == "filled" for item in matched)
    other_filled = sum(item["other"]["status"] == "filled" for item in matched)
    ready = matched_count >= minimum_opportunities
    if not ready:
        decision = "CONTINUE"
        reason = f"{matched_count}/{minimum_opportunities} matched opportunities"
    elif other_filled <= same_filled:
        decision = "REJECT"
        reason = "other-side pricing did not improve matched fill count"
    else:
        decision = "REVIEW_COST"
        reason = "fill improvement exists; approve only after recovered PnL covers crossing cost"
    return {
        "matched_opportunities": matched_count,
        "minimum_opportunities": minimum_opportunities,
        "ready_for_decision": ready,
        "decision": decision,
        "decision_reason": reason,
        "matched_fill_rate_same": same_filled / matched_count if matched_count else None,
        "matched_fill_rate_other": other_filled / matched_count if matched_count else None,
        "both_filled": len(both_filled),
        "recovered_by_other": len(recovered),
        "lost_by_other": len(reverse),
        "paired_entry_cost_bps": {
            "n": len(paired_costs),
            "median": median(paired_costs) if paired_costs else None,
            "mean": mean(paired_costs) if paired_costs else None,
            "max": max(paired_costs) if paired_costs else None,
        },
        "paired_fill_latency_s": {
            "same_median": median(same_latencies) if same_latencies else None,
            "other_median": median(other_latencies) if other_latencies else None,
        },
        "paired_closed_pnl_delta_other_usdt": {
            "n": len(paired_pnl_delta),
            "sum": sum(paired_pnl_delta) if paired_pnl_delta else None,
            "mean": mean(paired_pnl_delta) if paired_pnl_delta else None,
        },
        "unmatched_same": len(unmatched_same),
        "unmatched_other": len(unmatched_other),
    }


def compact_attempt(row: dict) -> dict:
    return {
        key: row.get(key)
        for key in (
            "trade_id", "pair", "side", "status", "submitted_at",
            "submitted_at_provenance", "fill_latency_s", "fill_price",
            "intended_price", "is_open", "close_profit_abs",
        )
    }


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--db-same", required=True)
    parser.add_argument("--log-same", required=True)
    parser.add_argument("--db-other", required=True)
    parser.add_argument("--log-other", required=True)
    parser.add_argument("--since", required=True)
    parser.add_argument("--entry-timeout-minutes", type=float, default=10)
    parser.add_argument("--match-tolerance-seconds", type=float, default=90)
    parser.add_argument("--minimum-opportunities", type=int, default=20)
    parser.add_argument("--json", action="store_true")
    args = parser.parse_args()

    since = normalize_since(args.since)
    same = load_attempts(
        args.db_same, args.log_same, since, args.entry_timeout_minutes
    )
    other = load_attempts(
        args.db_other, args.log_other, since, args.entry_timeout_minutes
    )
    matched, unmatched_same, unmatched_other = match_attempts(
        same, other, args.match_tolerance_seconds
    )
    summary = summarize(
        matched, unmatched_same, unmatched_other, args.minimum_opportunities
    )
    result = {
        "window": {"since": since, "as_of": datetime.now(timezone.utc).isoformat()},
        "parameters": {
            "entry_timeout_minutes": args.entry_timeout_minutes,
            "match_tolerance_seconds": args.match_tolerance_seconds,
            "minimum_opportunities": args.minimum_opportunities,
        },
        "summary": summary,
        "matched": [
            {
                "same": compact_attempt(item["same"]),
                "other": compact_attempt(item["other"]),
                "submitted_delta_s": item["submitted_delta_s"],
                "other_adverse_bps": adverse_bps(item["same"], item["other"]),
            }
            for item in matched
        ],
        "unmatched_same": [compact_attempt(row) for row in unmatched_same],
        "unmatched_other": [compact_attempt(row) for row in unmatched_other],
    }
    if args.json:
        print(json.dumps(result, ensure_ascii=False, indent=2))
        return 0

    print(
        f"matched opportunities: {summary['matched_opportunities']}/"
        f"{summary['minimum_opportunities']} -> {summary['decision']}"
    )
    print(
        "matched fill rate:"
        f" same={summary['matched_fill_rate_same']:.1%}"
        f" other={summary['matched_fill_rate_other']:.1%}"
    )
    print(
        f"both filled={summary['both_filled']}"
        f" recovered_by_other={summary['recovered_by_other']}"
        f" lost_by_other={summary['lost_by_other']}"
    )
    cost = summary["paired_entry_cost_bps"]
    print(
        f"other-side adverse entry cost: n={cost['n']}"
        f" median={cost['median']:.3f}bps"
        f" mean={cost['mean']:.3f}bps"
        f" max={cost['max']:.3f}bps"
    )
    latency = summary["paired_fill_latency_s"]
    print(
        "paired median fill latency:"
        f" same={latency['same_median']:.3f}s"
        f" other={latency['other_median']:.3f}s"
    )
    pnl = summary["paired_closed_pnl_delta_other_usdt"]
    print(
        f"paired closed PnL delta (other-same): n={pnl['n']}"
        f" sum={pnl['sum']:+.3f} USDT"
    )
    print(
        f"unmatched attempts: same={summary['unmatched_same']}"
        f" other={summary['unmatched_other']} (portfolio-path evidence, not pricing alpha)"
    )
    return 0


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