"""
ls_ws_ticks.chetime 실제 값 샘플 확인
→ REGEXP_REPLACE 없이 처리 가능한지 패턴 파악
"""
import sys, os
sys.path.insert(0, '/home/hoon/kis_bot')
os.chdir('/home/hoon/kis_bot')

from database import TradeDB
db = TradeDB()

try:
    # chetime 샘플 (다양한 패턴 확인)
    rows = db.conn.execute(
        "SELECT chetime, ts FROM ls_ws_ticks "
        "WHERE ts >= '2026-09-02 09:00:00' AND ts <= '2026-09-02 15:40:00' "
        "ORDER BY ts LIMIT 20"
    ).fetchall()
    print("=== chetime 샘플 ===")
    for r in rows:
        d = dict(r)
        print(f"  chetime={repr(d['chetime'])!r:30s}  ts={d['ts']}")

    # chetime NULL 비율
    r = db.conn.execute(
        "SELECT "
        "  COUNT(*) AS total, "
        "  SUM(CASE WHEN chetime IS NULL OR chetime='' THEN 1 ELSE 0 END) AS null_cnt, "
        "  SUM(CASE WHEN CHAR_LENGTH(chetime) >= 6 THEN 1 ELSE 0 END) AS has6, "
        "  SUM(CASE WHEN CHAR_LENGTH(chetime) >= 14 THEN 1 ELSE 0 END) AS has14, "
        "  SUM(CASE WHEN chetime REGEXP '[^0-9]' THEN 1 ELSE 0 END) AS has_nonnumeric "
        "FROM ls_ws_ticks "
        "WHERE ts >= '2026-09-02 09:00:00' AND ts <= '2026-09-02 15:40:00'"
    ).fetchone()
    d = dict(r)
    print(f"\n=== chetime 통계 (당일) ===")
    print(f"  총행: {d['total']}")
    print(f"  NULL/빈값: {d['null_cnt']}")
    print(f"  6자리 이상: {d['has6']}")
    print(f"  14자리 이상: {d['has14']}")
    print(f"  비숫자 포함: {d['has_nonnumeric']}")

    # 비숫자 포함 샘플
    if d['has_nonnumeric']:
        rows2 = db.conn.execute(
            "SELECT chetime FROM ls_ws_ticks "
            "WHERE ts >= '2026-09-02 09:00:00' AND ts <= '2026-09-02 15:40:00' "
            "AND chetime REGEXP '[^0-9]' LIMIT 5"
        ).fetchall()
        print(f"\n  비숫자 포함 샘플:")
        for r in rows2:
            print(f"    {repr(dict(r)['chetime'])}")

finally:
    db.close()