#!/usr/bin/env python3
"""
prod_backtester.py

Production-style backtester for the user's short top-gainers strategy with realistic frictions.
- Pipeline: top-gainers(24h) -> filters -> rank by overbought_index -> take top_n
- Intrabar exits using high/low with SL priority for shorts
- Fees, funding, slippage, tick rounding
- Liquidity thresholds on 24h and 1h quote volume
- Cross-margin portfolio: cap of total open notional as fraction of equity

Inputs:
  --cache-db PATH                  SQLite with table price_indicators (schema: symbol, datetime_utc, open, high, low, close, volume, rsi, stochastic, mfi, overbought_index, atr_ratio, gain_24h_before)
  --initial-equity FLOAT          Starting equity (USDT)
  --position-notional FLOAT       Per-trade notional (USDT)
  --max-notional-frac FLOAT       Max open notional as fraction of equity (e.g., 0.5)
  --open-hour-kyiv INT            Local Kyiv hour for entries (0..23); we convert using kyiv_offset_hours
  --kyiv-offset-hours INT         Fixed UTC offset; set 3 if you don't handle DST (default 3)
  --top-n INT                     Trades to open per day after OB ranking
  --preselect-mult INT            Preselect factor by 24h gain (top_n * preselect_mult)
  --hold-hours INT
  --cooldown-days INT
  --min-ob FLOAT                  Minimum overbought_index (0=disable)
  --min-rsi FLOAT
  --min-stoch FLOAT
  --min-mfi FLOAT
  --require-at-least-n-high INT
  --max-atr-ratio FLOAT
  --risk-pct FLOAT                SL distance as fraction of entry (e.g., 0.055)
  --base-tp-mult FLOAT            (currently unused; keep 0 if no TP)
  --max-extra-tp FLOAT            (currently unused)
  --fee-rate FLOAT                Per side (e.g., 0.001 for 0.1%)
  --funding-rate-hour FLOAT       Hourly funding rate (e.g., 0.00002 for 0.002%/h)
  --slippage-per-side FLOAT       e.g., 0.0003 for 3 bps
  --tick-pct FLOAT                Price quantization as pct of price (e.g., 0.0001 for 0.01%)
  --min-qv-24h FLOAT              Minimum 24h quote volume (USDT)
  --min-qv-1h FLOAT               Minimum 1h quote volume (USDT)
  --out-trades PATH               CSV to write closed trades with equity curve
  --out-summary PATH              CSV or JSON with summary metrics

Example:
  python3 prod_backtester.py --cache-db combined_cache.db --initial-equity 200 --position-notional 20 --max-notional-frac 0.5 \
    --open-hour-kyiv 1 --kyiv-offset-hours 3 --top-n 6 --preselect-mult 10 --hold-hours 45 --cooldown-days 5 \
    --min-ob 70 --min-rsi 60 --min-stoch 50 --min-mfi 50 --require-at-least-n-high 2 --max-atr-ratio 0.05 \
    --risk-pct 0.055 --fee-rate 0.001 --funding-rate-hour 0.00002 --slippage-per-side 0.0003 --tick-pct 0.0001 \
    --min-qv-24h 100000 --min-qv-1h 10000 --out-trades trades.csv --out-summary summary.csv
"""
import argparse, sqlite3, pandas as pd, numpy as np
from datetime import datetime, timedelta
from collections import namedtuple

def round_tick(price: float, tick_pct: float) -> float:
    if not np.isfinite(price) or price <= 0:
        return price
    step = price * tick_pct
    if step <= 0:
        return price
    return round(price / step) * step

def load_data(cache_db: str):
    con = sqlite3.connect(cache_db)
    df = pd.read_sql_query("SELECT * FROM price_indicators", con, parse_dates=["datetime_utc"])
    con.close()
    df = df.sort_values(["symbol", "datetime_utc"]).reset_index(drop=True)
    dfs = {}
    for sym, g in df.groupby("symbol"):
        gg = g.set_index("datetime_utc")[["open","high","low","close","volume","rsi","stochastic","mfi","overbought_index","atr_ratio","gain_24h_before"]].copy()
        gg["quote_volume"] = gg["volume"] * gg["close"]
        gg["qv_24h"] = gg["quote_volume"].rolling(24, min_periods=1).sum()
        dfs[sym] = gg
    all_times = pd.to_datetime(sorted(df["datetime_utc"].unique()))
    return dfs, all_times

def select_candidates(dfs, t, args, last_open_time):
    rows = []
    for sym, gg in dfs.items():
        if t not in gg.index: continue
        rows.append((sym, gg.loc[t]))
    if not rows: return []
    # Preselect by 24h gain
    rows = sorted(rows, key=lambda x: (float(x[1]["gain_24h_before"]) if pd.notna(x[1]["gain_24h_before"]) else -1e9), reverse=True)[:args.top_n*args.preselect_mult]
    cands = []
    for sym, row in rows:
        rsi=row["rsi"]; stoch=row["stochastic"]; mfi=row["mfi"]; ob=row["overbought_index"]; atr=row["atr_ratio"]
        qv24=row["qv_24h"]; qv1h=row["quote_volume"]; entry=row["close"]
        if pd.isna(entry) or entry <= 0: continue
        if not (pd.notna(qv24) and qv24 >= args.min_qv_24h and pd.notna(qv1h) and qv1h >= args.min_qv_1h): continue
        if args.min_ob>0 and (pd.isna(ob) or ob < args.min_ob): continue
        cnt=0
        if args.min_rsi>0 and pd.notna(rsi) and rsi>=args.min_rsi: cnt+=1
        if args.min_stoch>0 and pd.notna(stoch) and stoch>=args.min_stoch: cnt+=1
        if args.min_mfi>0 and pd.notna(mfi) and mfi>=args.min_mfi: cnt+=1
        if args.require_at_least_n_high>0 and cnt<args.require_at_least_n_high: continue
        if args.max_atr_ratio>0 and (pd.isna(atr) or atr>args.max_atr_ratio): continue
        # 3h-high proximity
        gg = dfs[sym]; prior = gg.loc[t - pd.Timedelta(hours=3): t - pd.Timedelta(hours=1)]
        if len(prior) >= 1:
            recent_high = prior["close"].max()
            if entry < 0.98*recent_high: continue
        lo = last_open_time.get(sym)
        if lo is not None and (t - lo) < pd.Timedelta(days=args.cooldown_days): continue
        cands.append((sym, row))
    if not cands: return []
    cands = sorted(cands, key=lambda x: (float(x[1]["overbought_index"]) if pd.notna(x[1]["overbought_index"]) else -1e9), reverse=True)[:args.top_n]
    return cands

def sim_exit_short(sym, t, row, dfs, args):
    gg = dfs[sym]
    raw_entry = float(row["close"])
    entry = round_tick(raw_entry*(1-args.slippage_per_side), args.tick_pct)
    sl_price = entry*(1+args.risk_pct) if args.risk_pct>0 else None
    sl_price = round_tick(sl_price, args.tick_pct) if sl_price is not None else None
    final_time = t + pd.Timedelta(hours=args.hold_hours)
    path = gg.loc[t:final_time]
    exit_price=None; exit_type="24h"; exit_time = final_time if final_time in gg.index else (path.index[-1] if len(path)>0 else t)
    idxs = list(path.index)
    if len(idxs)<=1:
        ex_raw = float(gg.loc[exit_time,"close"]) if exit_time in gg.index else raw_entry
        exit_price = round_tick(ex_raw*(1+args.slippage_per_side), args.tick_pct)
    else:
        for i in range(1, len(idxs)):
            bt=idxs[i]; bar_high=float(gg.loc[bt,"high"])
            if sl_price is not None and bar_high >= sl_price:
                exit_price = round_tick(sl_price*(1+args.slippage_per_side), args.tick_pct)
                exit_time=bt; exit_type="SL"; break
        if exit_price is None:
            ex_raw = float(gg.loc[exit_time,"close"]) if exit_time in gg.index else float(gg.iloc[-1]["close"])
            exit_price = round_tick(ex_raw*(1+args.slippage_per_side), args.tick_pct)
    gross = (entry - exit_price)/entry
    holding_hours = max(0.0,(exit_time - t).total_seconds()/3600.0)
    costs = 2*args.fee_rate + args.funding_rate_hour*holding_hours
    net = gross - costs
    return entry, exit_time, exit_price, exit_type, gross, net

def run_portfolio(dfs, all_times, args):
    open_hour_utc = (args.open_hour_kyiv - args.kyiv_offset_hours) % 24
    open_times = [t for t in all_times if t.hour == open_hour_utc]
    last_open_time = {}
    equity = args.initial_equity
    open_positions=[]; closed=[]
    for t in open_times:
        # close due
        keep=[]
        for pos in open_positions:
            if pos["exit_time"] <= t:
                pnl = pos["net_return"]*pos["notional"]
                equity += pnl
                closed.append({**pos, "realized_pnl": pnl, "equity_after": equity})
            else:
                keep.append(pos)
        open_positions=keep
        used = sum(p["notional"] for p in open_positions)
        cap = args.max_notional_frac * equity
        cands = select_candidates(dfs, t, args, last_open_time)
        for sym, row in cands:
            if used + args.position_notional > cap: break
            entry, xt, xp, xtype, gross, net = sim_exit_short(sym, t, row, dfs, args)
            pos = {
                "open_time_utc": t, "symbol": sym, "entry_price": entry,
                "exit_time": xt, "exit_price": xp, "exit_type": xtype,
                "gross_return": gross, "net_return": net, "notional": args.position_notional,
            }
            open_positions.append(pos)
            used += args.position_notional
            last_open_time[sym] = t
    # close remaining
    for pos in open_positions:
        pnl = pos["net_return"]*pos["notional"]
        equity += pnl
        closed.append({**pos, "realized_pnl": pnl, "equity_after": equity})
    pf = pd.DataFrame(closed).sort_values("open_time_utc").reset_index(drop=True)
    if len(pf):
        pf["cum_pnl"] = pf["realized_pnl"].cumsum()
        pf["equity"]  = args.initial_equity + pf["cum_pnl"]
        rets = pf["realized_pnl"]/pf["notional"]
        wins = (rets>0)
        peak = np.maximum.accumulate(pf["equity"].values)
        dd = (pf["equity"].values/peak - 1).min()*100.0
        summary = {
            "trades": int(len(pf)),
            "win_rate_%": float(wins.mean()*100),
            "avg_win_%": float(rets[rets>0].mean()*100) if (rets>0).any() else 0.0,
            "avg_loss_%": float(rets[rets<0].mean()*100) if (rets<0).any() else 0.0,
            "profit_factor": float((rets[rets>0].sum()/abs(rets[rets<0].sum())) if (rets<0).any() else float("inf")),
            "equity_start": float(args.initial_equity),
            "equity_end": float(pf["equity"].values[-1]) if len(pf) else float(args.initial_equity),
            "max_drawdown_%": float(dd),
        }
    else:
        summary = {"trades":0,"win_rate_%":0,"avg_win_%":0,"avg_loss_%":0,"profit_factor":0,"equity_start":float(args.initial_equity),"equity_end":float(args.initial_equity),"max_drawdown_%":0}
        pf["equity"] = []
    return pf, summary

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--cache-db", required=True)
    ap.add_argument("--initial-equity", type=float, required=True)
    ap.add_argument("--position-notional", type=float, required=True)
    ap.add_argument("--max-notional-frac", type=float, required=True)
    ap.add_argument("--open-hour-kyiv", type=int, default=1)
    ap.add_argument("--kyiv-offset-hours", type=int, default=3)
    ap.add_argument("--top-n", type=int, default=6)
    ap.add_argument("--preselect-mult", type=int, default=10)
    ap.add_argument("--hold-hours", type=int, default=45)
    ap.add_argument("--cooldown-days", type=int, default=5)
    ap.add_argument("--min-ob", type=float, default=70.0)
    ap.add_argument("--min-rsi", type=float, default=60.0)
    ap.add_argument("--min-stoch", type=float, default=50.0)
    ap.add_argument("--min-mfi", type=float, default=50.0)
    ap.add_argument("--require-at-least-n-high", type=int, default=2)
    ap.add_argument("--max-atr-ratio", type=float, default=0.05)
    ap.add_argument("--risk-pct", type=float, default=0.055)
    ap.add_argument("--base-tp-mult", type=float, default=0.0)
    ap.add_argument("--max-extra-tp", type=float, default=0.0)
    ap.add_argument("--fee-rate", type=float, default=0.001)
    ap.add_argument("--funding-rate-hour", type=float, default=0.00002)
    ap.add_argument("--slippage-per-side", type=float, default=0.0003)
    ap.add_argument("--tick-pct", type=float, default=0.0001)
    ap.add_argument("--min-qv-24h", type=float, default=100000.0)
    ap.add_argument("--min-qv-1h", type=float, default=10000.0)
    ap.add_argument("--out-trades", required=True)
    ap.add_argument("--out-summary", required=True)
    args = ap.parse_args()

    dfs, all_times = load_data(args.cache_db)
    pf, summary = run_portfolio(dfs, all_times, args)
    pf.to_csv(args.out_trades, index=False)
    # summary to CSV with one row
    pd.DataFrame([summary]).to_csv(args.out_summary, index=False)
    print("Wrote:", args.out_trades, "and", args.out_summary)

if __name__ == "__main__":
    main()