"""3% 고정 익절 vs 현재 trail stop 전략 비교 시뮬레이션.

전략:
  A. Trail Stop only         : ATR 1.0×[3.0~5.0%]
  B. TP 3% + Trail Stop 손절 : +3% 도달 시 익절, 못 도달하면 trail stop
  C. TP 2% + Trail Stop 손절 : +2% 도달 시 익절
  D. TP 5% + Trail Stop 손절 : +5% 도달 시 익절
"""
import sys, os
from datetime import datetime, timedelta
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from dotenv import load_dotenv
load_dotenv(os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '.env'))

import oracledb

# ── 파라미터 ──────────────────────────────────────────────────────────────────
VOL_LOOKBACK   = 61
ATR_LOOKBACK   = 28
LOOKBACK_DAYS  = 3
VOL_MIN        = 8.0

ATR_MULT       = 1.0
ATR_MIN_R      = 0.030
ATR_MAX_R      = 0.050
MAX_TRAIL_BARS = 240

BUDGET   = 15_000_000
MAX_POS  = 3
PER_POS  = BUDGET // MAX_POS
FEE      = 0.0005

TICKERS = [
    'KRW-XRP','KRW-BTC','KRW-ETH','KRW-SOL','KRW-DOGE',
    'KRW-ADA','KRW-SUI','KRW-NEAR','KRW-KAVA','KRW-SXP',
    'KRW-AKT','KRW-SONIC','KRW-IP','KRW-ORBS','KRW-VIRTUAL',
    'KRW-BARD','KRW-XPL','KRW-KITE','KRW-ENSO','KRW-0G',
]
_TK = ",".join(f"'{t}'" for t in TICKERS)

SIGNAL_SQL = f"""
WITH
base AS (
    SELECT ticker, ts, open_p, close_p, high_p, low_p, volume_p,
        LAG(close_p,  1) OVER (PARTITION BY ticker ORDER BY ts) prev_close_1,
        LAG(open_p,   1) OVER (PARTITION BY ticker ORDER BY ts) prev_open_1,
        LAG(volume_p, 1) OVER (PARTITION BY ticker ORDER BY ts) prev_vol_1,
        LAG(close_p,  2) OVER (PARTITION BY ticker ORDER BY ts) prev_close_2,
        LAG(open_p,   2) OVER (PARTITION BY ticker ORDER BY ts) prev_open_2,
        LAG(volume_p, 2) OVER (PARTITION BY ticker ORDER BY ts) prev_vol_2,
        LAG(close_p,  3) OVER (PARTITION BY ticker ORDER BY ts) prev_close_3,
        LAG(open_p,   3) OVER (PARTITION BY ticker ORDER BY ts) prev_open_3,
        LAG(volume_p, 3) OVER (PARTITION BY ticker ORDER BY ts) prev_vol_3,
        GREATEST(
            high_p - low_p,
            ABS(high_p - LAG(close_p,1) OVER (PARTITION BY ticker ORDER BY ts)),
            ABS(low_p  - LAG(close_p,1) OVER (PARTITION BY ticker ORDER BY ts))
        ) tr
    FROM backtest_ohlcv
    WHERE interval_cd = 'minute1'
      AND ts  >= TO_TIMESTAMP(:warmup_since, 'YYYY-MM-DD HH24:MI:SS')
      AND ticker IN ({_TK})
),
indicators AS (
    SELECT ticker, ts, open_p, close_p,
        volume_p / NULLIF(
            AVG(volume_p) OVER (PARTITION BY ticker ORDER BY ts
                                ROWS BETWEEN {VOL_LOOKBACK} PRECEDING AND 2 PRECEDING), 0
        ) vr0,
        prev_vol_1 / NULLIF(
            AVG(volume_p) OVER (PARTITION BY ticker ORDER BY ts
                                ROWS BETWEEN {VOL_LOOKBACK} PRECEDING AND 2 PRECEDING), 0
        ) vr1,
        prev_vol_2 / NULLIF(
            AVG(volume_p) OVER (PARTITION BY ticker ORDER BY ts
                                ROWS BETWEEN {VOL_LOOKBACK} PRECEDING AND 2 PRECEDING), 0
        ) vr2,
        prev_vol_3 / NULLIF(
            AVG(volume_p) OVER (PARTITION BY ticker ORDER BY ts
                                ROWS BETWEEN {VOL_LOOKBACK} PRECEDING AND 2 PRECEDING), 0
        ) vr3,
        prev_close_1, prev_open_1,
        prev_close_2, prev_open_2,
        prev_close_3, prev_open_3,
        AVG(tr) OVER (PARTITION BY ticker ORDER BY ts
                      ROWS BETWEEN {ATR_LOOKBACK} PRECEDING AND 1 PRECEDING)
        / NULLIF(prev_close_1, 0) atr_raw
    FROM base
)
SELECT ticker, ts sig_ts, close_p sig_price,
       vr0, vr1, vr2, vr3, atr_raw,
       prev_close_3, prev_close_2, prev_close_1, close_p
FROM indicators
WHERE ts  >= TO_TIMESTAMP(:check_since, 'YYYY-MM-DD HH24:MI:SS')
  AND vr0 >= :min_vol
  AND close_p      > open_p
  AND prev_close_1 > prev_open_1
  AND prev_close_2 > prev_open_2
  AND prev_close_3 > prev_open_3
  AND close_p      > prev_close_1
  AND prev_close_1 > prev_close_2
  AND prev_close_2 > prev_close_3
  AND vr0 > vr1 AND vr1 > vr2 AND vr2 > vr3
ORDER BY ticker, ts
"""


def _get_conn():
    kwargs = dict(user=os.environ["ORACLE_USER"],
                  password=os.environ["ORACLE_PASSWORD"],
                  dsn=os.environ["ORACLE_DSN"])
    wallet = os.environ.get("ORACLE_WALLET")
    if wallet:
        kwargs["config_dir"] = wallet
    return oracledb.connect(**kwargs)


def _fetch_bars(cur, ticker, entry_ts):
    """진입 시점 이후 봉 데이터 조회 (캐시용)."""
    cur.execute(
        """SELECT ts, close_p, high_p FROM backtest_ohlcv
           WHERE ticker=:t AND interval_cd='minute1'
           AND ts >= :entry
           ORDER BY ts FETCH FIRST :n ROWS ONLY""",
        {"t": ticker, "entry": entry_ts, "n": MAX_TRAIL_BARS + 1}
    )
    return [(ts, float(cp), float(hp)) for ts, cp, hp in cur.fetchall()]


def simulate_trail_only(bars, entry_price, atr_raw):
    """전략 A: Trail Stop만 사용."""
    ar = atr_raw if (atr_raw and atr_raw == atr_raw) else 0.0
    atr_stop = max(ATR_MIN_R, min(ATR_MAX_R, ar * ATR_MULT)) if ar > 0 else ATR_MAX_R

    running_peak = entry_price
    for i, (ts, close_p, _) in enumerate(bars):
        running_peak = max(running_peak, close_p)
        drop = (running_peak - close_p) / running_peak if running_peak > 0 else 0.0
        if drop >= atr_stop:
            pnl = (close_p - entry_price) / entry_price * 100
            peak_pct = (running_peak - entry_price) / entry_price * 100
            return dict(status="트레일손절", exit_ts=ts, exit_price=close_p,
                        peak=running_peak, peak_pct=peak_pct,
                        pnl=pnl, held_bars=i + 1)

    last_ts, last_price = bars[-1][0], bars[-1][1]
    pnl = (last_price - entry_price) / entry_price * 100
    peak_pct = (running_peak - entry_price) / entry_price * 100
    status = "타임아웃" if len(bars) >= MAX_TRAIL_BARS else "진행중"
    return dict(status=status, exit_ts=last_ts, exit_price=last_price,
                peak=running_peak, peak_pct=peak_pct,
                pnl=pnl, held_bars=len(bars))


def simulate_tp_trail(bars, entry_price, atr_raw, tp_r):
    """전략 B/C/D: 고정 익절 + Trail Stop 손절.

    먼저 +tp_r% 도달하면 익절.
    못 도달하고 trail stop 걸리면 손절.
    """
    ar = atr_raw if (atr_raw and atr_raw == atr_raw) else 0.0
    atr_stop = max(ATR_MIN_R, min(ATR_MAX_R, ar * ATR_MULT)) if ar > 0 else ATR_MAX_R
    tp_price = entry_price * (1 + tp_r)

    running_peak = entry_price
    for i, (ts, close_p, high_p) in enumerate(bars):
        # 고가로 익절 체크 (봉 내 고가가 익절가 도달하면 익절)
        if high_p >= tp_price:
            pnl = tp_r * 100
            peak_pct = (max(running_peak, high_p) - entry_price) / entry_price * 100
            return dict(status=f"익절+{tp_r*100:.0f}%", exit_ts=ts, exit_price=tp_price,
                        peak=max(running_peak, high_p), peak_pct=peak_pct,
                        pnl=pnl, held_bars=i + 1)

        running_peak = max(running_peak, close_p)
        drop = (running_peak - close_p) / running_peak if running_peak > 0 else 0.0
        if drop >= atr_stop:
            pnl = (close_p - entry_price) / entry_price * 100
            peak_pct = (running_peak - entry_price) / entry_price * 100
            return dict(status="트레일손절", exit_ts=ts, exit_price=close_p,
                        peak=running_peak, peak_pct=peak_pct,
                        pnl=pnl, held_bars=i + 1)

    last_ts, last_price = bars[-1][0], bars[-1][1]
    pnl = (last_price - entry_price) / entry_price * 100
    peak_pct = (running_peak - entry_price) / entry_price * 100
    status = "타임아웃" if len(bars) >= MAX_TRAIL_BARS else "진행중"
    return dict(status=status, exit_ts=last_ts, exit_price=last_price,
                peak=running_peak, peak_pct=peak_pct,
                pnl=pnl, held_bars=len(bars))


def apply_pos_limit(sim_results):
    open_positions = []
    taken, skipped = [], []
    for r in sim_results:
        open_positions = [ex for ex in open_positions if ex > r['entry_ts']]
        if len(open_positions) < MAX_POS:
            open_positions.append(r['exit_ts'])
            taken.append(r)
        else:
            skipped.append(r)
    return taken, skipped


def print_summary(label, taken):
    n = len(taken)
    if n == 0:
        print(f"  {label:35s}  거래없음")
        return
    wins  = sum(1 for r in taken if r['pnl'] > 0)
    total = sum(PER_POS * (r['pnl'] / 100) - PER_POS * FEE * 2 for r in taken)
    avg_h = sum(r['held_bars'] for r in taken) / n
    ret   = total / BUDGET * 100
    print(f"  {label:35s}  {n:>3d}건  {wins/n*100:>4.0f}%  {total:>+12,.0f}원  {ret:>+5.2f}%  {avg_h:>5.0f}봉")


def print_detail(label, taken):
    print(f"\n{'━'*110}")
    print(f"  {label}")
    print(f"{'━'*110}")
    for i, r in enumerate(taken, 1):
        krw = PER_POS * (r['pnl'] / 100) - PER_POS * FEE * 2
        sign = "▲" if r['pnl'] > 0 else "▼"
        print(f"  #{i:02d} {r['ticker']:12s} [{sign}]  진입 {str(r['entry_ts'])[:16]}  "
              f"고점 {r['peak_pct']:>+.2f}%  → {r['status']} {r['held_bars']}봉  "
              f"PNL {r['pnl']:>+.2f}%  ({krw:>+,.0f}원)")


def main():
    now          = datetime.now()
    check_since  = (now - timedelta(days=LOOKBACK_DAYS)).strftime('%Y-%m-%d 00:00:00')
    warmup_since = (now - timedelta(days=LOOKBACK_DAYS + 1)).strftime('%Y-%m-%d 00:00:00')

    print(f"=== 고정 익절 vs Trail Stop 비교 시뮬 ===")
    print(f"기간: {check_since[:10]} ~ 현재  |  4봉 VOL≥{VOL_MIN}x  |  "
          f"손절: ATR {ATR_MULT}×[{ATR_MIN_R*100:.0f}~{ATR_MAX_R*100:.0f}%]")
    print(f"자본 {BUDGET//10000}만원 / 포지션 {PER_POS//10000}만원 / 동시 {MAX_POS}개\n")

    conn = _get_conn()
    cur  = conn.cursor()
    cur.arraysize = 10_000

    cur.execute(SIGNAL_SQL, {
        "warmup_since": warmup_since,
        "check_since":  check_since,
        "min_vol":      VOL_MIN,
    })
    rows = cur.fetchall()
    if not rows:
        print(f"해당 기간 VOL>={VOL_MIN}x 4봉 가속 시그널 없음")
        conn.close()
        return

    # 진입봉 + bars 수집
    signals = []
    for row in rows:
        ticker, sig_ts, sig_price, vr0, vr1, vr2, vr3, atr_raw, \
            p3, p2, p1, p0 = row
        cur.execute(
            """SELECT close_p, ts FROM backtest_ohlcv
               WHERE ticker=:t AND interval_cd='minute1'
               AND ts > :sig AND ts <= :sig + INTERVAL '3' MINUTE
               ORDER BY ts FETCH FIRST 1 ROWS ONLY""",
            {"t": ticker, "sig": sig_ts}
        )
        entry_row = cur.fetchone()
        if not entry_row:
            continue
        entry_price, entry_ts = float(entry_row[0]), entry_row[1]
        bars = _fetch_bars(cur, ticker, entry_ts)
        if not bars:
            continue
        signals.append({
            'ticker':      ticker,
            'entry_ts':    entry_ts,
            'entry_price': entry_price,
            'vr':          [float(x) if x else 0.0 for x in [vr3, vr2, vr1, vr0]],
            'prices':      [float(x) if x else 0.0 for x in [p3, p2, p1, p0]],
            'atr_raw':     float(atr_raw) if atr_raw else 0.0,
            'bars':        bars,
        })

    signals.sort(key=lambda x: x['entry_ts'])
    print(f"시그널 {len(signals)}건 → 전략별 시뮬 중...\n")

    # ── 전략별 시뮬 ───────────────────────────────────────────────────────────
    strategies = [
        ("A. Trail Stop [3~5%]",      None),
        ("B. 익절 2% + Trail Stop",    0.02),
        ("C. 익절 3% + Trail Stop",    0.03),
        ("D. 익절 5% + Trail Stop",    0.05),
    ]

    results = {}
    for label, tp_r in strategies:
        sim = []
        for s in signals:
            if tp_r is None:
                r = simulate_trail_only(s['bars'], s['entry_price'], s['atr_raw'])
            else:
                r = simulate_tp_trail(s['bars'], s['entry_price'], s['atr_raw'], tp_r)
            sim.append({**s, **r})
        taken, _ = apply_pos_limit(sim)
        results[label] = taken

    # ── 요약 비교표 ───────────────────────────────────────────────────────────
    print(f"{'━'*95}")
    print(f"  {'전략':35s}  {'거래':>3s}  {'승률':>4s}  {'합산손익':>12s}  {'수익률':>5s}  {'평균보유':>5s}")
    print(f"{'━'*95}")
    for label, taken in results.items():
        print_summary(label, taken)
    print(f"{'━'*95}")

    # ── 전략별 건별 상세 ──────────────────────────────────────────────────────
    for label, taken in results.items():
        print_detail(label, taken)

    conn.close()


if __name__ == '__main__':
    main()