#!/usr/bin/env python3
"""025980 lag~28s vs 어제 아침 max~5s — 스키마 확인 후 1회 조회."""
from __future__ import annotations

import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

from database import TradeDB
from kis_trader.utils.env import get_env_from_db

LAG = """
        TIMESTAMPDIFF(
            SECOND,
            STR_TO_DATE(
                CASE
                    WHEN CHAR_LENGTH(tick_time) >= 14 THEN LEFT(tick_time, 14)
                    WHEN CHAR_LENGTH(tick_time) = 6 THEN CONCAT(DATE_FORMAT(recv_ts, '%%Y%%m%%d'), tick_time)
                    ELSE NULL
                END,
                '%%Y%%m%%d%%H%%i%%s'
            ),
            recv_ts
        )
"""

db = TradeDB()
cols = [r["Field"] for r in db.conn.execute("SHOW COLUMNS FROM ws_ticks").fetchall()]
print("ws_ticks cols:", cols)

print("DB_CUT_ON", get_env_from_db("WS_TICK_DB_SAVE_LAG_CUT_ENABLED"))  # 2026-09-06 통일 (구 KIWOOM_TICK_LIVE_MAX_LAG_SEC)
print("TIME_MAX", get_env_from_db("KIWOOM_TICK_TIME_MAX_LAG_SEC"))
print("FLUSH", get_env_from_db("WS_TICK_DB_FLUSH_SEC"))
print("BATCH", get_env_from_db("WS_TICK_DB_BATCH_SIZE"))

sel = [
    c
    for c in (
        "id",
        "code",
        "tick_time",
        "tick_time_raw",
        "recv_ts",
        "price",
        "volume",
        "source",
    )
    if c in cols
]
sel_sql = ", ".join(sel)

print("\n== 오늘 kiwoom lag>5 ==")
rows = db.conn.execute(
    f"SELECT {sel_sql}, {LAG} AS lag_sec FROM ws_ticks "
    "WHERE source=%s AND recv_ts >= %s AND recv_ts < %s AND "
    f"{LAG} > 5 ORDER BY lag_sec DESC LIMIT 20",
    ("kiwoom", "2026-08-19 09:00:00", "2026-08-19 09:15:00"),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 025980 09:04:50~09:06:00 (id순) ==")
rows = db.conn.execute(
    f"SELECT {sel_sql}, {LAG} AS lag_sec FROM ws_ticks "
    "WHERE source=%s AND code=%s AND recv_ts >= %s AND recv_ts <= %s "
    "ORDER BY id",
    ("kiwoom", "025980", "2026-08-19 09:04:50", "2026-08-19 09:06:00"),
).fetchall()
print("n", len(rows))
for r in rows:
    print(dict(r))

# 같은 recv 초에 몰린 종목 수 (WS 스레드 스톨 흔적)
print("\n== 오늘 09:05:30~09:05:40 recv 초별 건수/종목 ==")
rows = db.conn.execute(
    "SELECT recv_ts, COUNT(*) n, COUNT(DISTINCT code) codes "
    "FROM ws_ticks WHERE source=%s AND recv_ts >= %s AND recv_ts <= %s "
    "GROUP BY recv_ts ORDER BY recv_ts",
    ("kiwoom", "2026-08-19 09:05:30", "2026-08-19 09:05:40"),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 오늘 09:00~09:10 lag 분포 ==")
rows = db.conn.execute(
    f"SELECT {LAG} AS lag_sec, COUNT(*) n FROM ws_ticks "
    "WHERE source=%s AND recv_ts >= %s AND recv_ts < %s "
    f"GROUP BY {LAG} ORDER BY lag_sec DESC LIMIT 15",
    ("kiwoom", "2026-08-19 09:00:00", "2026-08-19 09:10:00"),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 어제 09:00~09:15 lag 분포 top ==")
rows = db.conn.execute(
    f"SELECT {LAG} AS lag_sec, COUNT(*) n FROM ws_ticks "
    "WHERE source=%s AND recv_ts >= %s AND recv_ts < %s "
    f"GROUP BY {LAG} ORDER BY lag_sec DESC LIMIT 15",
    ("kiwoom", "2026-08-18 09:00:00", "2026-08-18 09:15:00"),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 어제 10:51~12:00 (잔존) lag 분포 top ==")
rows = db.conn.execute(
    f"SELECT {LAG} AS lag_sec, COUNT(*) n FROM ws_ticks "
    "WHERE source=%s AND recv_ts >= %s AND recv_ts < %s "
    f"GROUP BY {LAG} ORDER BY lag_sec DESC LIMIT 10",
    ("kiwoom", "2026-08-18 10:51:00", "2026-08-18 12:00:00"),
).fetchall()
for r in rows:
    print(dict(r))