-- ═══════════════════════════════════════════════════════════════════════════
-- 수집통계 수동 조회 (MariaDB) — 하루치 먼저 모으고 그 안에서만 집계
--
-- 원본 Python: kis_trader/web/feed_collect_stats.py  → build_feed_collect_stats()
-- 웹 API:       GET /api/feed_collect_stats?date=YYYY-MM-DD&heavy=0|1
--
-- DB: 192.168.0.141:3306/kis_quant_db  (환경변수 DB_* 와 동일)
--
-- 사용법 (mysql 클라이언트):
--   mysql -h 192.168.0.141 -u <USER> -p kis_quant_db < scripts/feed_collect_stats_manual.sql
-- 또는 HeidiSQL/DBeaver 에서 @day8 만 바꿔 구간 실행
--
-- 2026-08-28 기준 대략적 행수 (전체 테이블 420만 vs 하루):
--   ws_ticks        ~34만/일
--   ws_orderbook    ~18만/일
--   ls_ws_ticks     ~69만/일  (전체 560만)
--   ls_ws_orderbook ~64만/일
-- ═══════════════════════════════════════════════════════════════════════════

USE kis_quant_db;

SET @day8   = '20260828';                          -- ← 여기만 바꾸면 됨
SET @day    = CONCAT(LEFT(@day8,4),'-',SUBSTR(@day8,5,2),'-',SUBSTR(@day8,7,2));
SET @like   = CONCAT(@day8, '%');
SET @t0     = CONCAT(@day8, '000000');
SET @t1     = CONCAT(@day8, '235959');
SET @ts0    = CONCAT(@day, ' 00:00:00');
SET @ts1    = CONCAT(@day, ' 23:59:59');
SET @fb_age = 3;   -- LIVE_FEED_FALLBACK_MAX_AGE_SEC (DB env, 기본 3)
SET @soft   = 10;  -- FEED_STATS_DISCONNECT_SOFT_SEC
SET @hard   = 60;  -- FEED_STATS_DISCONNECT_HARD_SEC
SET @cap    = 1800; -- FEED_STATS_DISCONNECT_CAP_SEC

-- ─────────────────────────────────────────────────────────────────────────
-- A. 빠른 요약 (1~3초) — 웹 탭 상단 숫자와 동일
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== A1 ws_ticks 벤더별 ===' AS section;
SELECT source AS k, COUNT(*) AS n, COUNT(DISTINCT code) AS codes
FROM ws_ticks
WHERE tick_time >= @t0 AND tick_time <= @t1
GROUP BY source
ORDER BY n DESC;

SELECT '=== A2 ws_orderbook 벤더별 ===' AS section;
SELECT source AS k, COUNT(*) AS n, COUNT(DISTINCT code) AS codes
FROM ws_orderbook
WHERE snap_time >= @t0 AND snap_time <= @t1
GROUP BY source
ORDER BY n DESC;

SELECT '=== A3 ls_ws_ticks (DATETIME 범위, 인덱스 idx_ls_tick_ts) ===' AS section;
SELECT COUNT(*) AS n, COUNT(DISTINCT code) AS codes
FROM ls_ws_ticks
WHERE ts >= @ts0 AND ts <= @ts1;

SELECT '=== A4 ls_ws_orderbook ===' AS section;
SELECT COUNT(*) AS n, COUNT(DISTINCT code) AS codes
FROM ls_ws_orderbook
WHERE snap_time >= @t0 AND snap_time <= @t1;

SELECT '=== A5 filter_eval (호가 source=filter_eval) ===' AS section;
SELECT COALESCE(strategy,'') AS k, COUNT(*) AS n, COUNT(DISTINCT code) AS c
FROM ws_orderbook
WHERE source = 'filter_eval'
  AND snap_time >= @t0 AND snap_time <= @t1
GROUP BY strategy
ORDER BY n DESC;

SELECT '=== A6 ls_ws_candles ===' AS section;
SELECT COUNT(*) AS n, COUNT(DISTINCT code) AS codes
FROM ls_ws_candles
WHERE datetime LIKE @like;


-- ─────────────────────────────────────────────────────────────────────────
-- B. 하루치 TEMP TABLE (한 번만) — 이후 C/D/E는 이 테이블만 스캔 (~34만)
--    세션 끊기면 TEMP 사라짐. 다시 B부터 실행.
-- ─────────────────────────────────────────────────────────────────────────

DROP TEMPORARY TABLE IF EXISTS tmp_fs_ticks;
CREATE TEMPORARY TABLE tmp_fs_ticks (
  id BIGINT,
  code VARCHAR(32),
  tick_time VARCHAR(14),
  tick_time_raw VARCHAR(32),
  source VARCHAR(16),
  recv_ts VARCHAR(30),
  KEY (source, code),
  KEY (recv_ts)
) AS
SELECT id, code, tick_time, tick_time_raw, source, recv_ts
FROM ws_ticks
WHERE tick_time >= @t0 AND tick_time <= @t1;

SELECT '=== B tmp_fs_ticks 적재 ===' AS section;
SELECT COUNT(*) AS n, COUNT(DISTINCT code) AS codes, COUNT(DISTINCT source) AS srcs
FROM tmp_fs_ticks;


DROP TEMPORARY TABLE IF EXISTS tmp_fs_ob;
CREATE TEMPORARY TABLE tmp_fs_ob (
  id BIGINT,
  code VARCHAR(32),
  snap_time VARCHAR(14),
  source VARCHAR(16),
  recv_ts VARCHAR(30),
  reject_code VARCHAR(64),
  strategy VARCHAR(64),
  KEY (source, code)
) AS
SELECT id, code, snap_time, source, recv_ts, reject_code, strategy
FROM ws_orderbook
WHERE snap_time >= @t0 AND snap_time <= @t1;

SELECT '=== B tmp_fs_ob 적재 ===' AS section;
SELECT COUNT(*) AS n, COUNT(DISTINCT code) AS codes FROM tmp_fs_ob;


-- ─────────────────────────────────────────────────────────────────────────
-- C. 폴백나이(usable) — feed_collect_stats._feed_age_cut_stats 동일 축
--    (웹 기본 조회에서 제일 무거움: TIMESTAMPDIFF 전행)
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== C1 ws_ticks lag by source (tmp) ===' AS section;
SELECT source AS src,
       COUNT(*) AS total,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec <= @fb_age THEN 1 ELSE 0 END) AS pass_n,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec > @fb_age THEN 1 ELSE 0 END) AS fail_n,
       SUM(CASE WHEN lag_sec IS NULL THEN 1 ELSE 0 END) AS unknown_n
FROM (
  SELECT source,
         TIMESTAMPDIFF(SECOND,
           STR_TO_DATE(
             CASE
               WHEN CHAR_LENGTH(IFNULL(tick_time,'')) >= 14 THEN LEFT(tick_time, 14)
               WHEN CHAR_LENGTH(IFNULL(tick_time_raw,'')) >= 14 THEN LEFT(tick_time_raw, 14)
               WHEN CHAR_LENGTH(IFNULL(tick_time_raw,'')) >= 6 THEN
                 CONCAT(COALESCE(NULLIF(LEFT(IFNULL(tick_time,''), 8), ''), DATE_FORMAT(recv_ts, '%Y%m%d')),
                        RIGHT(tick_time_raw, 6))
               ELSE NULL
             END,
             '%Y%m%d%H%i%s'),
           recv_ts) AS lag_sec
  FROM tmp_fs_ticks
) t
GROUP BY source;

SELECT '=== C2 전량 fail 분(종목×분) ===' AS section;
SELECT COUNT(*) AS empty_minutes
FROM (
  SELECT code, LEFT(tick_time, 12) AS mk
  FROM (
    SELECT code, tick_time,
           TIMESTAMPDIFF(SECOND,
             STR_TO_DATE(LEFT(tick_time, 14), '%Y%m%d%H%i%s'),
             recv_ts) AS lag_sec
    FROM tmp_fs_ticks
  ) x
  GROUP BY code, LEFT(tick_time, 12)
  HAVING SUM(CASE WHEN lag_sec IS NULL OR lag_sec <= @fb_age THEN 1 ELSE 0 END) = 0
     AND COUNT(*) > 0
) z;

SELECT '=== C3 ws_orderbook lag (filter_eval 제외, tmp) ===' AS section;
SELECT source AS src,
       COUNT(*) AS total,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec <= @fb_age THEN 1 ELSE 0 END) AS pass_n,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec > @fb_age THEN 1 ELSE 0 END) AS fail_n,
       SUM(CASE WHEN lag_sec IS NULL THEN 1 ELSE 0 END) AS unknown_n
FROM (
  SELECT source,
         TIMESTAMPDIFF(SECOND,
           STR_TO_DATE(
             CASE WHEN CHAR_LENGTH(snap_time) >= 14 THEN LEFT(snap_time, 14)
                  WHEN CHAR_LENGTH(snap_time) >= 6 THEN CONCAT(@day8, RIGHT(snap_time, 6))
                  ELSE NULL END,
             '%Y%m%d%H%i%s'),
           recv_ts) AS lag_sec
  FROM tmp_fs_ob
  WHERE source <> 'filter_eval'
) t
GROUP BY source;


-- ─────────────────────────────────────────────────────────────────────────
-- D. 끊김(disconnect) — recv_ts LAG (장중 09:00~15:30)
--    feed_collect_stats._feed_disconnect_stats
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== D ws_ticks recv_ts gap (tmp, 장중) ===' AS section;
SELECT source AS vendor,
       COUNT(*) AS pair_n,
       SUM(CASE WHEN gap_sec >= @soft THEN 1 ELSE 0 END) AS soft_n,
       SUM(CASE WHEN gap_sec >= @hard THEN 1 ELSE 0 END) AS hard_n,
       MAX(gap_sec) AS max_gap,
       ROUND(AVG(CASE WHEN gap_sec >= @soft THEN gap_sec END), 1) AS avg_soft_gap
FROM (
  SELECT source,
         TIMESTAMPDIFF(SECOND,
           LAG(recv_ts) OVER (PARTITION BY source, code ORDER BY recv_ts, id),
           recv_ts) AS gap_sec
  FROM tmp_fs_ticks
  WHERE TIME(recv_ts) BETWEEN '09:00:00' AND '15:30:00'
) t
WHERE gap_sec IS NOT NULL AND gap_sec > 0 AND gap_sec < @cap
GROUP BY source;


-- ─────────────────────────────────────────────────────────────────────────
-- E. LS 틱 (별도 테이블 — ts 범위가 인덱스 탐)
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== E1 ls_ws_ticks lag ===' AS section;
SELECT COUNT(*) AS total,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec <= @fb_age THEN 1 ELSE 0 END) AS pass_n,
       SUM(CASE WHEN lag_sec IS NOT NULL AND lag_sec > @fb_age THEN 1 ELSE 0 END) AS fail_n,
       SUM(CASE WHEN lag_sec IS NULL THEN 1 ELSE 0 END) AS unknown_n
FROM (
  SELECT TIMESTAMPDIFF(SECOND,
    STR_TO_DATE(
      CASE WHEN CHAR_LENGTH(REGEXP_REPLACE(IFNULL(chetime,''), '[^0-9]', '')) >= 14
           THEN LEFT(REGEXP_REPLACE(chetime, '[^0-9]', ''), 14)
           WHEN CHAR_LENGTH(REGEXP_REPLACE(IFNULL(chetime,''), '[^0-9]', '')) >= 6
           THEN CONCAT(DATE_FORMAT(ts,'%Y%m%d'), RIGHT(REGEXP_REPLACE(chetime, '[^0-9]', ''), 6))
           ELSE DATE_FORMAT(ts,'%Y%m%d%H%i%s') END,
      '%Y%m%d%H%i%s'),
    ts) AS lag_sec
  FROM ls_ws_ticks
  WHERE ts >= @ts0 AND ts <= @ts1
) t;

SELECT '=== E2 ls_ws_ticks disconnect (장중) ===' AS section;
SELECT COUNT(*) AS pair_n,
       SUM(CASE WHEN gap_sec >= @soft THEN 1 ELSE 0 END) AS soft_n,
       SUM(CASE WHEN gap_sec >= @hard THEN 1 ELSE 0 END) AS hard_n,
       MAX(gap_sec) AS max_gap
FROM (
  SELECT TIMESTAMPDIFF(SECOND,
           LAG(ts) OVER (PARTITION BY code ORDER BY ts, id),
           ts) AS gap_sec
  FROM ls_ws_ticks
  WHERE ts >= @ts0 AND ts <= @ts1
    AND TIME(ts) BETWEEN '09:00:00' AND '15:30:00'
) t
WHERE gap_sec IS NOT NULL AND gap_sec > 0 AND gap_sec < @cap;


-- ─────────────────────────────────────────────────────────────────────────
-- F. 상세(heavy=1) — DISTINCT 종목 교집합 (웹 「상세 집계」)
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== F 틱/호가 종목 교집합 (kis vs kiwoom) ===' AS section;
SELECT
  (SELECT COUNT(DISTINCT code) FROM tmp_fs_ticks WHERE source='kis') AS kis_tick_codes,
  (SELECT COUNT(DISTINCT code) FROM tmp_fs_ticks WHERE source='kiwoom') AS kw_tick_codes,
  (SELECT COUNT(DISTINCT a.code)
   FROM (SELECT DISTINCT code FROM tmp_fs_ticks WHERE source='kis') a
   JOIN (SELECT DISTINCT code FROM tmp_fs_ticks WHERE source='kiwoom') b ON a.code=b.code) AS both_tick,
  (SELECT COUNT(DISTINCT code) FROM tmp_fs_ob WHERE source='kis_h0stasp0') AS kis_ob_codes,
  (SELECT COUNT(DISTINCT code) FROM tmp_fs_ob WHERE source='kiwoom_0d') AS kw_ob_codes;


-- ─────────────────────────────────────────────────────────────────────────
-- G. 테이블 전체 vs 하루 (왜 느린지 확인용)
-- ─────────────────────────────────────────────────────────────────────────

SELECT '=== G 전체 테이블 규모 ===' AS section;
SELECT 'ws_ticks' AS tbl, COUNT(*) AS total_rows FROM ws_ticks
UNION ALL
SELECT 'ws_orderbook', COUNT(*) FROM ws_orderbook
UNION ALL
SELECT 'ls_ws_ticks', COUNT(*) FROM ls_ws_ticks
UNION ALL
SELECT 'ls_ws_orderbook', COUNT(*) FROM ls_ws_orderbook;