#!/usr/bin/env python3
"""키움 tick_time vs recv_ts 증거 (DB, 즉시 실행)."""
import sys
from datetime import datetime

sys.path.insert(0, "/home/hoon/kis_bot")
from database import TradeDB

WATCH = ("089970", "005930", "028670", "178320")

db = TradeDB()
try:
    print("=== 키움: recv 최근 5분 vs tick_time lag ===")
    rows = db.conn.execute(
        """
        SELECT code, tick_time, recv_ts, price, volume,
               TIMESTAMPDIFF(SECOND,
                 STR_TO_DATE(tick_time, '%%Y%%m%%d%%H%%i%%s'),
                 recv_ts) AS lag_sec
        FROM ws_ticks
        WHERE source='kiwoom'
          AND recv_ts >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
        ORDER BY recv_ts DESC
        LIMIT 30
        """
    ).fetchall()
    for r in rows:
        d = dict(r)
        print(
            f"  {d['code']} tick={d['tick_time']} recv={d['recv_ts']} "
            f"lag={d['lag_sec']}s vol={d['volume']} px={d['price']}"
        )

    print("\n=== vol=0 vs vol>0 (오늘 kiwoom, recv>=10:43) ===")
    r = db.conn.execute(
        """
        SELECT
          SUM(CASE WHEN COALESCE(volume,0)=0 THEN 1 ELSE 0 END) vol0,
          SUM(CASE WHEN COALESCE(volume,0)>0 THEN 1 ELSE 0 END) vol_pos,
          COUNT(*) total
        FROM ws_ticks
        WHERE source='kiwoom' AND recv_ts >= '2026-08-18 10:43:00'
        """
    ).fetchone()
    print(dict(r))

    print("\n=== 관심종목 오늘 kiwoom tick_time max vs recv max ===")
    for code in WATCH:
        row = db.conn.execute(
            """
            SELECT COUNT(*) n,
                   MAX(tick_time) tmax, MAX(recv_ts) rmax,
                   MAX(TIMESTAMPDIFF(SECOND,
                     STR_TO_DATE(tick_time, '%%Y%%m%%d%%H%%i%%s'),
                     recv_ts)) max_lag
            FROM ws_ticks
            WHERE source='kiwoom' AND code=%s
              AND recv_ts >= '2026-08-18 09:00:00'
            """,
            (code,),
        ).fetchone()
        print(f"  {code}: {dict(row)}")
finally:
    db.close()