_archive_scripts/_backfill_trades.py

"""One-time: recover 12 closed trades from daemon logs into runtime DB."""
import sqlite3, pathlib, datetime as _dt

RUNTIME_DB = pathlib.Path.home() / "borsa_uygulamasi_runtime/huggingface-space/paper_trading/trading.db"

# Raw fills parsed from daemon stderr log (all 29 entries, in chronological order)
FILLS = [
    # (date, action, qty, symbol, price, commission)
    ("2026-02-25", "BUY",   99, "RALYH",  150.7753, 15.67),
    ("2026-02-26", "BUY",   49, "THYAO",  306.4031, 15.76),
    ("2026-02-27", "BUY", 1597, "IZENR",    9.4347, 15.82),
    ("2026-03-02", "SELL",  99, "RALYH",  147.4263, 15.32),
    ("2026-03-02", "SELL",  49, "THYAO",  287.6061, 14.80),
    ("2026-03-02", "SELL",1597, "IZENR",    8.9055, 14.93),
    ("2026-03-02", "BUY", 1484, "AKSA",     9.8749, 15.39),
    ("2026-03-02", "BUY", 1648, "IZENR",    8.9145, 15.43),
    ("2026-03-02", "BUY",   79, "ASTOR",  183.3917, 15.21),
    ("2026-03-02", "BUY",   50, "THYAO",  287.8939, 15.11),
    ("2026-03-03", "SELL",1648, "IZENR",    8.9455, 15.48),
    ("2026-03-04", "SELL",  50, "THYAO",  283.8580, 14.90),
    ("2026-03-04", "BUY",  185, "AKBNK",   78.5392, 15.26),
    ("2026-03-05", "SELL",  79, "ASTOR",  172.2139, 14.29),
    ("2026-03-05", "SELL", 185, "AKBNK",   80.9595, 15.73),
    ("2026-03-05", "BUY",   84, "ASTOR",  172.3862, 15.20),
    ("2026-03-05", "BUY",   98, "RALYH",  147.2736, 15.15),
    ("2026-03-05", "BUY", 2702, "BTCIM",    5.4027, 15.33),
    ("2026-03-06", "SELL",1484, "AKSA",    10.0150, 15.61),
    ("2026-03-06", "SELL",  98, "RALYH",  143.7281, 14.79),
    ("2026-03-06", "BUY",   51, "THYAO",  284.1420, 15.22),
    ("2026-03-09", "SELL",  51, "THYAO",  267.1164, 14.30),
    ("2026-03-11", "SELL",  84, "ASTOR",  193.7031, 17.08),
    ("2026-03-12", "SELL",2702, "BTCIM",    6.1269, 17.38),
    # Mar 30 BUYs already in DB as open trades — skip
]

def match_trades(fills):
    """FIFO matching: pair BUY fills with subsequent SELL fills by (symbol, qty)."""
    open_positions = []  # list of BUY fills waiting for a match
    closed = []
    
    for f in fills:
        date, action, qty, sym, price, comm = f
        if action == "BUY":
            open_positions.append(f)
        else:  # SELL
            # find matching BUY with same symbol and qty
            for i, op in enumerate(open_positions):
                if op[3] == sym and op[2] == qty:
                    entry = op
                    exit_ = f
                    closed.append((entry, exit_))
                    open_positions.pop(i)
                    break
    return closed

def build_trade_record(entry, exit_):
    e_date, _, qty, sym, e_price, e_comm = entry
    x_date, _, _, _, x_price, x_comm = exit_
    
    gross_pnl = round((x_price - e_price) * qty, 4)
    net_pnl = round(gross_pnl - e_comm - x_comm, 4)
    return_pct = round((x_price - e_price) / e_price * 100, 4)
    
    d1 = _dt.date.fromisoformat(e_date)
    d2 = _dt.date.fromisoformat(x_date)
    holding_days = (d2 - d1).days
    
    return {
        "symbol": sym,
        "side": "BUY",
        "entry_price": e_price,
        "exit_price": x_price,
        "quantity": qty,
        "entry_date": e_date,
        "exit_date": x_date,
        "entry_commission": e_comm,
        "exit_commission": x_comm,
        "gross_pnl": gross_pnl,
        "net_pnl": net_pnl,
        "return_pct": return_pct,
        "holding_days": holding_days,
        "signal_confidence": 0.0,
        "predicted_return": 0.0,
        "entry_reason": "ML_BUY_SIGNAL",
        "exit_reason": "SIGNAL_SELL",
        "is_closed": 1,
        "created_at": f"{e_date} 10:30:00",
        "updated_at": f"{x_date} 10:30:00",
    }

def main():
    pairs = match_trades(FILLS)
    print(f"Matched {len(pairs)} closed trades from daemon logs\n")
    
    records = []
    total_net = 0.0
    winners = 0
    for entry, exit_ in pairs:
        r = build_trade_record(entry, exit_)
        records.append(r)
        total_net += r["net_pnl"]
        if r["net_pnl"] > 0:
            winners += 1
        print(f"  {r['symbol']:6s} {r['entry_date']}→{r['exit_date']}  "
              f"net={r['net_pnl']:+.2f}  ret={r['return_pct']:+.2f}%  "
              f"hold={r['holding_days']}d")
    
    losers = len(records) - winners
    win_pnl = sum(r["net_pnl"] for r in records if r["net_pnl"] > 0)
    loss_pnl = abs(sum(r["net_pnl"] for r in records if r["net_pnl"] <= 0))
    pf = win_pnl / loss_pnl if loss_pnl > 0 else 0
    
    print(f"\nSummary: {len(records)} trades, {winners}W/{losers}L")
    print(f"  WR={winners/len(records)*100:.1f}%  PF={pf:.2f}  Net={total_net:+.2f}")
    
    # Insert into runtime DB
    db = sqlite3.connect(str(RUNTIME_DB))
    cur = db.cursor()
    
    # Check for existing closed trades to avoid duplicates
    cur.execute("SELECT COUNT(*) FROM trades WHERE is_closed=1")
    existing = cur.fetchone()[0]
    if existing > 0:
        print(f"\n⚠ Already {existing} closed trades in DB. Skipping insert.")
        db.close()
        return
    
    cols = list(records[0].keys())
    placeholders = ",".join(["?"] * len(cols))
    col_names = ",".join(cols)
    
    for r in records:
        vals = [r[c] for c in cols]
        cur.execute(f"INSERT INTO trades ({col_names}) VALUES ({placeholders})", vals)
    
    db.commit()
    print(f"\n✅ Inserted {len(records)} closed trades into {RUNTIME_DB}")
    
    # Verify
    cur.execute("SELECT COUNT(*) FROM trades WHERE is_closed=1")
    print(f"   Closed trades in DB: {cur.fetchone()[0]}")
    cur.execute("SELECT COUNT(*) FROM trades WHERE is_closed=0")
    print(f"   Open trades in DB: {cur.fetchone()[0]}")
    db.close()

if __name__ == "__main__":
    main()