#!/usr/bin/env python3
"""Build the official ACS local-context supplement used by the vacation-home statistics page.

Inputs: 2020-2024 ACS 5-year Detailed Tables B25002, B01002, B25077, and geography labels.
Outputs: nation, Idaho, Fremont County, Island Park CCD, and Island Park city rows.
Verified: 2026-07-29.
"""
from __future__ import annotations
import os
from pathlib import Path
import pandas as pd

OUT = Path(os.environ.get('VACATION_HOME_DATA_DIR', Path(__file__).resolve().parent))
VINTAGE = '2020-2024 ACS 5-year estimates'
VERIFIED = '2026-07-29'
FILES = {
    'b25002': OUT / 'acsdt5y2024-b25002.dat',
    'b01002': OUT / 'acsdt5y2024-b01002.dat',
    'b25077': OUT / 'acsdt5y2024-b25077.dat',
    'geos': OUT / 'Geos20245YR.txt',
}
URLS = {
    'b25002': 'https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25002.dat',
    'b01002': 'https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b01002.dat',
    'b25077': 'https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25077.dat',
    'geos': 'https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/documentation/Geos20245YR.txt',
}

def read_table(path, cols):
    df = pd.read_csv(path, sep='|', usecols=['GEO_ID'] + cols, dtype={'GEO_ID':'string'}, low_memory=False)
    for c in cols:
        df[c] = pd.to_numeric(df[c], errors='coerce')
    return df

def main():
    missing = [f'{k}: {URLS[k]}' for k,p in FILES.items() if not p.exists()]
    if missing:
        raise FileNotFoundError('Download these official files into the script directory:\n' + '\n'.join(missing))
    geos = pd.read_csv(FILES['geos'], sep='|', dtype='string', usecols=['STUSAB','SUMLEVEL','COMPONENT','STATE','COUNTY','COUSUB','PLACE','GEO_ID','NAME'], low_memory=False)
    geos = geos[geos['COMPONENT'].eq('00')]
    b25002 = read_table(FILES['b25002'], ['B25002_E001','B25002_M001','B25002_E002','B25002_M002','B25002_E003','B25002_M003'])
    b01002 = read_table(FILES['b01002'], ['B01002_E001','B01002_M001'])
    b25077 = read_table(FILES['b25077'], ['B25077_E001','B25077_M001'])
    df = geos.merge(b25002, on='GEO_ID').merge(b01002, on='GEO_ID').merge(b25077, on='GEO_ID')
    wanted = {'0100000US','0400000US16','0500000US16043','0600000US1604391725','1600000US1640600'}
    df = df[df['GEO_ID'].isin(wanted)].copy()
    types = {'010':'nation','040':'state','050':'county','060':'county subdivision','160':'place'}
    out = pd.DataFrame({
        'verification_tier':'★', 'geo_id':df['GEO_ID'], 'geography_type':df['SUMLEVEL'].map(types), 'name':df['NAME'],
        'state_abbreviation':df['STUSAB'], 'state_fips':df['STATE'], 'county_fips':df['COUNTY'], 'county_subdivision_fips':df['COUSUB'], 'place_fips':df['PLACE'],
        'total_housing_units_est':df['B25002_E001'], 'total_housing_units_moe_90':df['B25002_M001'],
        'occupied_housing_units_est':df['B25002_E002'], 'occupied_housing_units_moe_90':df['B25002_M002'],
        'vacant_housing_units_est':df['B25002_E003'], 'vacant_housing_units_moe_90':df['B25002_M003'],
        'occupied_share_pct':df['B25002_E002']/df['B25002_E001']*100, 'vacant_share_pct':df['B25002_E003']/df['B25002_E001']*100,
        'median_age_est':df['B01002_E001'], 'median_age_moe_90':df['B01002_M001'],
        'median_owner_occupied_home_value_est':df['B25077_E001'], 'median_owner_occupied_home_value_moe_90':df['B25077_M001'],
        'acs_vintage':VINTAGE, 'verified_date':VERIFIED,
        'source_b25002_url':URLS['b25002'], 'source_b01002_url':URLS['b01002'], 'source_b25077_url':URLS['b25077'], 'source_geography_url':URLS['geos'],
    })
    order = ['0100000US','0400000US16','0500000US16043','0600000US1604391725','1600000US1640600']
    out['_order'] = out['geo_id'].map({v:i for i,v in enumerate(order)})
    out.sort_values('_order').drop(columns='_order').to_csv(OUT/'vacation-home-statistics-2020-2024-acs-island-park-context.csv', index=False, encoding='utf-8-sig')
    print('Wrote official Island Park context supplement.')

if __name__ == '__main__':
    main()