#!/usr/bin/env bash
# jv_raw_records から 2020-01-01 より前の行をバッチ削除する。
# 判定: payload_json の meet_year が 2019 以下のみ（meet_date 単独は使わない。
# RACE/RA は meet_date が開催日だが、削除と取得の並走でレース行を誤消しした事故あり）
#
# 例:
#   ./scripts/jv_delete_raw_before_2020.sh
#   # 20160101 からの取得中は古い行が再投入されるため、取得完了後にも再実行を推奨
#
set -euo pipefail

ROOT="$(cd "$(dirname "$0")/.." && pwd)"
# shellcheck source=/dev/null
source "${ROOT}/scripts/jv_load_env.sh" 2>/dev/null || true

BATCH="${JV_DELETE_RAW_BATCH:-5000}"
LOG_DIR="${ROOT}/logs"
mkdir -p "$LOG_DIR"
LOG="${LOG_DIR}/jv_delete_raw_before_2020.log"

log() {
  echo "[$(TZ=Asia/Tokyo date '+%F %T')] $*" | tee -a "$LOG"
}

PRE2020_WHERE='JSON_EXTRACT(payload_json, '"'"'$.meet_year'"'"') IS NOT NULL AND CAST(JSON_UNQUOTE(JSON_EXTRACT(payload_json, '"'"'$.meet_year'"'"')) AS UNSIGNED) < 2020'

log "=== delete jv_raw before 2020-01-01 batch=${BATCH} ==="

before=$(mysql keiba_ai -N -e "
  SELECT COUNT(*) FROM jv_raw_records WHERE ${PRE2020_WHERE};
" 2>>"$LOG" || echo "?")
log "target_rows_before=${before}"

total=0
while true; do
  n=$(mysql keiba_ai -N -e "
    DELETE FROM jv_raw_records
    WHERE ${PRE2020_WHERE}
    LIMIT ${BATCH};
    SELECT ROW_COUNT();
  " 2>>"$LOG" || echo 0)
  n="${n:-0}"
  if [[ "$n" -le 0 ]]; then
    break
  fi
  total=$((total + n))
  log "deleted_batch=${n} total=${total}"
  sleep 0.5
done

left=$(mysql keiba_ai -N -e "
  SELECT COUNT(*) FROM jv_raw_records WHERE ${PRE2020_WHERE};
" 2>/dev/null || echo "?")
log "=== done total_deleted≈${total} remaining_pre2020=${left} ==="

# 正規化済みテーブル（あれば）
for tbl in races race_results race_entries odds_lines payouts; do
  if mysql keiba_ai -N -e "SHOW TABLES LIKE '${tbl}'" 2>/dev/null | grep -q .; then
    n=$(mysql keiba_ai -N -e "
      SELECT COUNT(*) FROM ${tbl} r
      WHERE EXISTS (SELECT 1 FROM races rc WHERE rc.id = r.race_id AND rc.race_date < '2020-01-01')
    " 2>/dev/null || echo 0)
    if [[ "${n:-0}" -gt 0 ]] 2>/dev/null; then
      log "WARN: ${tbl} may have pre-2020 via races — run normalize cleanup separately"
    fi
  fi
done

rpre=$(mysql keiba_ai -N -e "SELECT COUNT(*) FROM races WHERE race_date < '2020-01-01';" 2>/dev/null || echo 0)
if [[ "${rpre:-0}" -gt 0 ]]; then
  log "deleting races/race children before 2020-01-01 count=${rpre}"
  mysql keiba_ai -e "
    DELETE rr FROM race_results rr
    INNER JOIN races r ON r.id = rr.race_id WHERE r.race_date < '2020-01-01';
    DELETE re FROM race_entries re
    INNER JOIN races r ON r.id = re.race_id WHERE r.race_date < '2020-01-01';
    DELETE ol FROM odds_lines ol
    INNER JOIN races r ON r.id = ol.race_id WHERE r.race_date < '2020-01-01';
    DELETE p FROM payouts p
    INNER JOIN races r ON r.id = p.race_id WHERE r.race_date < '2020-01-01';
    DELETE FROM races WHERE race_date < '2020-01-01';
  " >>"$LOG" 2>&1 || log "WARN: races cleanup failed"
fi

df -h / | tee -a "$LOG"