"""One-off: dedupe FT/AET/PEN duplicate rows in live_data.csv.

Keeps earliest timestamp for each (game_date, home, away, status) where status is terminal.
Preserves all non-terminal rows.
"""
import pandas as pd
import shutil
from pathlib import Path

CSV = Path(r'E:\soccer\live_data.csv')
BAK = Path(r'E:\soccer\live_data.csv.bak')

if not CSV.exists():
    print(f"No file at {CSV}")
    raise SystemExit

shutil.copy2(CSV, BAK)
print(f"Backed up to {BAK}")

df = pd.read_csv(CSV, encoding='latin-1')
print(f"Loaded {len(df)} rows")

terminal = df['status'].isin(['FT', 'AET', 'PEN'])
term_df = df[terminal].copy()
nonterm_df = df[~terminal].copy()

print(f"  Terminal rows: {len(term_df)}")
print(f"  Non-terminal rows: {len(nonterm_df)}")

# Sort by timestamp, keep first per (game_date, home, away, status)
term_df = term_df.sort_values('timestamp')
term_dedup = term_df.drop_duplicates(
    subset=['game_date', 'home', 'away', 'status'],
    keep='first'
)
print(f"  After dedup: {len(term_dedup)} terminal rows (removed {len(term_df) - len(term_dedup)})")

out = pd.concat([nonterm_df, term_dedup], ignore_index=True)
out = out.sort_values('timestamp').reset_index(drop=True)
out.to_csv(CSV, index=False, encoding='latin-1')
print(f"Wrote {len(out)} rows to {CSV}")