#!/bin/bash
set -euo pipefail

ROOT="$(cd "$(dirname "$0")/.." && pwd)"
cd "$ROOT"

test -n "${MYSQL_PWD:-}" || { echo "export MYSQL_PWD='(mysql root password)'" >&2; exit 1; }

mysql -u root -e "
USE keiba_ai;

-- sample / scraper テストで入った不要データを削除
DELETE FROM race_results WHERE race_id IN (SELECT id FROM races WHERE data_source IN ('sample_sql','jra_accessS'));
DELETE FROM race_entries WHERE race_id IN (SELECT id FROM races WHERE data_source IN ('sample_sql','jra_accessS'));
DELETE FROM payouts      WHERE race_id IN (SELECT id FROM races WHERE data_source IN ('sample_sql','jra_accessS'));
DELETE FROM predictions  WHERE race_id IN (SELECT id FROM races WHERE data_source IN ('sample_sql','jra_accessS'));
DELETE FROM races        WHERE data_source IN ('sample_sql','jra_accessS');

-- 孤立したマスタを掃除（参照がないもののみ）
DELETE h FROM horses h
LEFT JOIN race_entries e ON e.horse_id=h.id
LEFT JOIN race_results r ON r.horse_id=h.id
WHERE e.id IS NULL AND r.id IS NULL;

DELETE j FROM jockeys j
LEFT JOIN race_entries e ON e.jockey_id=j.id
WHERE e.id IS NULL;

DELETE t FROM trainers t
LEFT JOIN race_entries e ON e.trainer_id=t.id
WHERE e.id IS NULL;
"

mysql -u root -N -e "
SELECT 'races_total',COUNT(*) FROM keiba_ai.races
UNION SELECT 'entries_total',COUNT(*) FROM keiba_ai.race_entries
UNION SELECT 'results_total',COUNT(*) FROM keiba_ai.race_results
UNION SELECT 'horses_total',COUNT(*) FROM keiba_ai.horses
UNION SELECT 'jockeys_total',COUNT(*) FROM keiba_ai.jockeys
UNION SELECT 'trainers_total',COUNT(*) FROM keiba_ai.trainers;
"

echo "OK: purged test data"