#!/usr/bin/env python3
"""旧サーバ（133）で実行: surface または distance が NULL のレース一覧を TSV 出力。

  python3 scripts/export_null_surface_races_old_server.py
  python3 scripts/export_null_surface_races_old_server.py /tmp/races_null_surface_133.tsv

DB 接続は config.py（.env / config/db_local.php）を使用。mysql -uroot は使わない。
"""
from __future__ import annotations

import csv
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from collectors.db_util import connect


def main() -> None:
    out = Path(sys.argv[1] if len(sys.argv) > 1 else "/tmp/races_null_surface_133.tsv")
    conn = connect()
    cur = conn.cursor()
    cur.execute(
        """
        SELECT r.race_date, t.code AS track_code, r.race_number,
               IFNULL(r.surface, '') AS surface,
               IFNULL(r.distance_m, '') AS distance_m
        FROM races r
        JOIN tracks t ON t.id = r.track_id
        WHERE r.circuit = 'JRA'
          AND (
            r.surface IS NULL OR r.surface = ''
            OR r.distance_m IS NULL OR r.distance_m = 0
          )
        ORDER BY r.race_date, t.code, r.race_number
        """
    )
    rows = cur.fetchall()
    cur.close()
    conn.close()

    with out.open("w", encoding="utf-8", newline="") as f:
        w = csv.writer(f, delimiter="\t")
        w.writerow(["race_date", "track_code", "race_number", "surface", "distance_m"])
        for r in rows:
            w.writerow(
                [
                    str(r["race_date"]),
                    str(r["track_code"]),
                    int(r["race_number"]),
                    r["surface"],
                    r["distance_m"],
                ]
            )
    print(f"wrote {out} ({len(rows) + 1} lines incl. header)")


if __name__ == "__main__":
    main()