"""Scores v5, step 1: pick the general-election rows the scores use out of results_town_long.csv (results v3.14).
Writes gen_1990_2024.pkl (town rows, General elections 1990-2024, President / Governor / U.S. Senate / U.S. House),
state_house_2008_2024.pkl (the threshold check) and president_maine_1980_2024.csv (Maine's two-party presidential
margin by year, every town row plus the UOCAVA (overseas and military) lines, which reproduces the official statewide totals: the Maine side of the Maine baseline, v5_scores.py)."""
import os, sys, pandas as pd
ER = os.environ.get('SCORES_RES', '/home/claude/audit/er')
r = pd.read_csv(f'{ER}/results_town_long.csv', low_memory=False, dtype={'district': str})
r = r[r.row_type.isin(['town', 'uocava']) & (r.election_type == 'General')]
rt = r[r.row_type == 'town']
keep = ['contest_id', 'election_date', 'election_type', 'office', 'district', 'cd', 'unit_id', 'unit_name', 'unit_note', 'sos_label',
        'row_type', 'col_role', 'candidate_display', 'cand_party', 'writein', 'votes']
top = rt[(rt.election_date >= '1990-01-01') & rt.office.isin(['President', 'Governor', 'U.S. Senate', 'U.S. House'])][keep].copy()
top.to_pickle('gen_1990_2024.pkl')
sh = rt[(rt.office == 'State House') & (rt.election_date >= '2008-01-01')][keep].copy()
sh.to_pickle('state_house_2008_2024.pkl')
p = r[(r.office == 'President') & (r.election_date >= '1980-01-01') & (r.col_role == 'candidate') & (r.writein.astype(str).str.lower() != 'true')]
c = p.groupby(['election_date', 'cand_party']).votes.sum().unstack().fillna(0)
pm = pd.DataFrame({'year': c.index.str[:4].astype(int), 'maine_dem': c.DEM.astype(int).values, 'maine_rep': c.REP.astype(int).values})
pm['maine_two_party_margin'] = 100 * (pm.maine_dem - pm.maine_rep) / (pm.maine_dem + pm.maine_rep)
pm.to_csv('president_maine_1980_2024.csv', index=False)
print(len(top), 'top-ticket rows;', len(sh), 'State House rows'); print(pm.round(2).to_string(index=False))