import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)) + '/..')
from database import TradeDB

db = TradeDB()
try:
    print("[LS Candidates History]")
    ls_cand = db.conn.execute("""
        SELECT HOUR(created_at) as hr, count(*) 
        FROM ls_candidates_history 
        WHERE date(created_at) = '2026-08-31' 
        GROUP BY HOUR(created_at)
    """).fetchall()
    for row in ls_cand: print(dict(row))

    print("\n[Target Candidates History]")
    tc_hist = db.conn.execute("""
        SELECT HOUR(scan_time) as hr, count(*) 
        FROM target_candidates_history 
        WHERE date(scan_time) = '2026-08-31' 
        GROUP BY HOUR(scan_time)
    """).fetchall()
    for row in tc_hist: print(dict(row))
    
    print("\n[WS Orderbook Zeros - KIS]")
    cols = [dict(r)["Field"] for r in db.conn.execute("SHOW COLUMNS FROM ws_orderbook").fetchall()]
    ts_col = 'ts' if 'ts' in cols else ('timestamp' if 'timestamp' in cols else 'snap_time')
    
    zeros = db.conn.execute(f"""
        SELECT count(*) as c 
        FROM ws_orderbook 
        WHERE date({ts_col}) = '2026-08-31' 
        AND (total_bid_qty = 0 OR total_ask_qty = 0 OR best_bid = 0 OR best_ask = 0)
    """).fetchone()
    print("Zeros count:", dict(zeros))

    print("\n[Sample WS Orderbook Zeros - KIS]")
    samp = db.conn.execute(f"""
        SELECT * 
        FROM ws_orderbook 
        WHERE date({ts_col}) = '2026-08-31' 
        LIMIT 1
    """).fetchone()
    print("Sample:", dict(samp))

finally:
    db.close()