"""
현재 쌓인 7일 초과 구데이터 즉시 정리:
- ws_candles: candle_time < 7일 전 (180만행)
- ls_ws_ticks: ts < 7일 전 (56만행)
- ls_ws_orderbook: recv_ts < 7일 전 (217만행)

ws_ticks / ws_orderbook: 이미 7일 내 데이터만 있음 → 스킵
"""
import sys, os, time, datetime
sys.path.insert(0, '/home/hoon/kis_bot')
os.chdir('/home/hoon/kis_bot')

from database import TradeDB

KEEP_DAYS = 7
CHUNK = 5000
MAX_LOOPS = 2000

db = TradeDB()

def cleanup_chunked(table, col, cutoff_str):
    total = 0
    for i in range(MAX_LOOPS):
        cur = db.conn.execute(
            f"DELETE FROM {table} WHERE {col} < %s LIMIT %s",
            (cutoff_str, CHUNK),
        )
        n = int(getattr(cur, 'rowcount', 0) or 0)
        total += n
        if n < CHUNK:
            break
        if total % 50000 == 0:
            print(f"  [{table}] 진행 중... {total}행 삭제됨")
        time.sleep(0.05)
    return total

try:
    cutoff_dt = datetime.datetime.now() - datetime.timedelta(days=KEEP_DAYS)
    cutoff_str = cutoff_dt.strftime("%Y-%m-%d %H:%M:%S")
    cutoff_candle = cutoff_dt.strftime("%Y%m%d%H%M")
    print(f"cutoff: {cutoff_str} (candles: {cutoff_candle})\n")

    # 1. ws_candles
    print("=== ws_candles 정리 중 ===")
    t0 = time.time()
    n = cleanup_chunked("ws_candles", "candle_time", cutoff_candle)
    print(f"  완료: {n}행 삭제 ({time.time()-t0:.1f}초)\n")

    # 2. ls_ws_ticks
    print("=== ls_ws_ticks 정리 중 ===")
    t0 = time.time()
    n = cleanup_chunked("ls_ws_ticks", "ts", cutoff_str)
    print(f"  완료: {n}행 삭제 ({time.time()-t0:.1f}초)\n")

    # 3. ls_ws_orderbook
    print("=== ls_ws_orderbook 정리 중 ===")
    t0 = time.time()
    n = cleanup_chunked("ls_ws_orderbook", "recv_ts", cutoff_str)
    print(f"  완료: {n}행 삭제 ({time.time()-t0:.1f}초)\n")

    # 검증
    print("=== 정리 후 현황 ===")
    cutoff7 = cutoff_str
    for table, col in [('ws_candles', 'candle_time'), ('ls_ws_ticks', 'ts'), ('ls_ws_orderbook', 'recv_ts')]:
        use_cutoff = cutoff_candle if table == 'ws_candles' else cutoff7
        row = db.conn.execute(
            f"SELECT COUNT(*) AS cnt FROM {table} WHERE {col} < %s", (use_cutoff,)
        ).fetchone()
        total_row = db.conn.execute(f"SELECT COUNT(*) AS cnt FROM {table}").fetchone()
        print(f"  {table}: 7일 초과 잔여={dict(row)['cnt']}행, 전체={dict(total_row)['cnt']}행")

finally:
    db.close()
    print("\n완료")