"""Build the supportive-housing cost-per-apartment table from figures transcribed
from primary documents (see ../notes.md for every source). Run: python -I build.py
Writes ../dataset/psh_projects.csv and ../dataset/analyses.json."""
import csv, json, statistics, os

here = os.path.dirname(os.path.abspath(__file__))
out = os.path.join(here, '..', 'dataset')
os.makedirs(out, exist_ok=True)

# (id, name as listed, sponsor, city, category, year, units, total_cost, cost_per_sqft, source, note)
P = [
    # Planned new buildings where every apartment is supportive housing.
    # Source: Commerce Housing Trust Fund application lists ("anticipated" total residential development cost).
    ('desc-greenlake-2021', 'DESC Greenlake', 'DESC', 'Seattle', 'planned_new', 2021, 124, 47208490, None, 'htf2021',
     'Application estimate; 124 of 124 units supportive; activity NC'),
    ('desc-woodland-2021', 'DESC Woodland (2021 application)', 'DESC', 'Seattle', 'planned_new_superseded', 2021, 100, 37921206, None, 'htf2021',
     'Earlier estimate for the project that re-applied in 2023; not used in medians'),
    ('desc-15th-ave-2022', 'DESC 15th Avenue', 'DESC', 'Seattle', 'planned_new', 2022, 105, 42043088, None, 'htf2022',
     'Application estimate; 105 of 105 units supportive; activity A,NC,RED'),
    ('desc-lake-city-2023', 'DESC Lake City', 'DESC', 'Seattle', 'planned_new', 2023, 120, 48193297, None, 'htf2023',
     'Application estimate; 120 of 120 units supportive; activity A,NC'),
    ('desc-woodland-2023', 'DESC Woodland', 'DESC', 'Seattle', 'planned_new', 2023, 95, 49825258, None, 'htf2023',
     'Application estimate; 95 of 95 units supportive; activity NC,RED'),
    ('desc-belmont-2024', 'DESC Belmont', 'DESC', 'Seattle', 'planned_new', 2024, 120, 59344428, None, 'htf2024',
     'Application estimate; 120 of 120 units supportive; City 2024 report: new construction of PSH'),
    ('plymouth-whittier-2025', 'Whittier Housing', 'Plymouth Housing', 'Seattle', 'planned_new', 2025, 90, 46054745, None, 'htf2025',
     'Application estimate; 90 of 90 units supportive'),
    ('plymouth-kenmore-2022', 'Kenmore Supportive Housing', 'Plymouth Housing', 'Kenmore', 'planned_new_suburb', 2022, 100, 42683529, None, 'htf2022',
     'Application estimate; outside Seattle'),
    ('plymouth-norkirk-2024', 'Norkirk Supportive Housing', 'Plymouth Housing', 'Kirkland', 'planned_new_suburb', 2024, 100, 58505548, None, 'htf2024',
     'Application estimate; outside Seattle'),
    ('plymouth-redmond-2024', 'Redmond PSH', 'Plymouth Housing', 'Redmond', 'planned_new_suburb', 2024, 100, 54843062, None, 'htf2024',
     'Application estimate; outside Seattle'),
    # Completed buildings: final costs reported to Commerce (cost data reports, residential cost incl. land and reserves).
    ('desc-22nd-ave-final', 'DESC 22nd Avenue PSH', 'DESC', 'Seattle', 'completed_new', 2022, 85, None, 537, 'cost2022',
     'Final cost, fiscal 2022 report; population Homeless'),
    ('hobson-place-final', 'Hobson Place', 'DESC', 'Seattle', 'completed_new', 2023, 92, None, 511, 'cost2023',
     'Final cost, fiscal 2023 report; population CMI, MSN'),
    ('csc-housing-final', 'CSC Housing', 'Chief Seattle Club', 'Seattle', 'completed_new', 2023, 80, None, 778, 'cost2023',
     'Final cost, fiscal 2023 report; population H (homeless)'),
    ('desc-bitter-lake-final', 'DESC Bitter Lake', 'DESC', 'Seattle', 'completed_new', 2023, 100, None, 551, 'cost2023',
     'Final cost, fiscal 2023 report; population H (homeless)'),
    ('yancy-street-final', 'Yancy Street PSH', 'Transitional Resources', 'Seattle', 'completed_new', 2023, 44, None, 709, 'cost2023',
     'Final cost, fiscal 2023 report; population H (homeless); report gives county only; 2800 block of SW Yancy St, West Seattle (Transitional Resources), 44 studios'),
    ('kent-psh-final', 'Kent Permanent Supportive Housing', None, 'Kent', 'completed_new_suburb', 2022, 80, None, 511, 'cost2022',
     'Final cost, fiscal 2022 report; outside Seattle'),
]
final_cpu = {'desc-22nd-ave-final': 325422, 'hobson-place-final': 583520, 'csc-housing-final': 356813,
             'desc-bitter-lake-final': 301744, 'yancy-street-final': 365601, 'kent-psh-final': 308082}

# King County Health Through Housing purchases: per-unit acquisition + rehabilitation, life to date through 2024
# (2024 annual report, Figure 20; city and status from Appendix B).
H = [
    ('hth-dons-place', "Don's Place", 'Compass Housing Alliance', 'Auburn', 148498, 32253, 'Open and occupied'),
    ('hth-sharyn-grayson', 'Sharyn Grayson House', 'Lavender Rights Project', 'Seattle', 363611, 7854, 'Pre-occupancy'),
    ('hth-booker-house', 'The Booker House', 'The Urban League', 'Federal Way', 270381, 3719, 'Pre-occupancy'),
    ('hth-kirkland', 'HTH Kirkland', 'Plymouth Housing', 'Kirkland', 278198, 17492, 'Pre-occupancy'),
    ('hth-haven-heights', 'Haven Heights', 'Salvation Army', 'Redmond', 280763, 17762, 'Pre-occupancy'),
    ('hth-mary-pilgrim', 'Mary Pilgrim Inn', 'DESC', 'Seattle', 206913, 16020, 'Open and occupied'),
    ('hth-salmonberry', 'Salmonberry Lofts', 'Chief Seattle Club', 'Seattle', 314809, 41727, 'Open and occupied'),
    ('hth-sidney-wilson', 'Sidney Wilson House', 'Catholic Community Services', 'Renton', 267403, 19801, 'Open and occupied'),
    ('hth-argyle', 'The Argyle', 'TBD', 'Seattle', 305240, 0, 'Project scoping'),
    ('hth-bob-g', 'The Bob G.', 'Catholic Community Services', 'Seattle', 206408, 0, 'Major rehabilitation'),
    ('hth-gateway', 'The Gateway', 'DESC', 'Seattle', 362643, 15479, 'Open and occupied'),
]

rows = []
for (i, n, s, c, cat, y, u, tc, sf, src, note) in P:
    if tc:
        cpu, kind = round(tc / u), 'estimated'
    else:
        cpu, kind = final_cpu[i], 'observed'
    rows.append(dict(project_id=i, name_as_listed=n, sponsor=s, city=c, category=cat, year=y, units=u,
                     total_cost_usd=tc, acquisition_per_unit_usd=None, rehab_per_unit_usd=None,
                     cost_per_apartment_usd=cpu, cost_per_residential_sqft_usd=sf, value_kind=kind,
                     status=None, source_id=src, note=note))
for (i, n, s, c, a, r, st) in H:
    rows.append(dict(project_id=i, name_as_listed=n, sponsor=s, city=c, category='county_purchase', year=2024, units=None,
                     total_cost_usd=None, acquisition_per_unit_usd=a, rehab_per_unit_usd=r,
                     cost_per_apartment_usd=a + r, cost_per_residential_sqft_usd=None, value_kind='observed',
                     status=st, source_id='hth2024', note='Acquisition plus rehabilitation per unit, life to date through 2024'))

with open(os.path.join(out, 'psh_projects.csv'), 'w', newline='', encoding='utf8') as f:
    w = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
    w.writeheader()
    w.writerows(rows)


def pick(cat, years=None, city=None):
    return [r for r in rows if r['category'] == cat and (years is None or r['year'] in years)
            and (city is None or r['city'] == city)]


def summary(rs):
    v = [r['cost_per_apartment_usd'] for r in rs]
    return dict(n=len(v), median=statistics.median(v), min=min(v), max=max(v))


# Same-round comparison: Seattle non-supportive affordable new construction, 2023 HTF applications
# (activity includes NC, zero supportive units). Cost per unit = residential development cost / total low-income units.
AFF2023 = [('Keiro/Benu', 173, 95914234), ('Aspire King Street Apartments', 20, 8662982),
           ('Bellwether Greenwood', 58, 31110004), ('Bellwether UW Tower', 204, 172535923),
           ('Seattle Mennonite Church Lake City', 200, 105298537), ('Beacon Hill Affordable TOD Development', 84, 44481462),
           ('Filipino Community Village Phase 2', 56, 41431229), ('Bryant Manor Redevelopment Phase II', 148, 96724848),
           ('New Hope Family Housing', 91, 52402476), ('Trenton Apartments', 182, 115946559),
           ('Rainier Court V', 49, 34611415), ('Beacon Crossing', 150, 86384272), ('Via7', 220, 93069183),
           ("Azani's Garden Townhomes", 18, 6303660), ('Little Saigon Landmark', 71, 53538764)]
aff_rows = [dict(project=n, units=u, residential_development_cost_usd=c, cost_per_apartment_usd=round(c / u))
            for n, u, c in AFF2023]
with open(os.path.join(out, 'seattle_affordable_2023_applications.csv'), 'w', newline='', encoding='utf8') as f:
    w = csv.DictWriter(f, fieldnames=list(aff_rows[0].keys()))
    w.writeheader()
    w.writerows(aff_rows)

BENCH = [
    dict(benchmark_id='typical-new-apartment-seattle', description='Typical new six-story apartment building in Seattle, about 143 units, estimated full cost per unit (spring 2023 interviews)', value_usd=490000, unit='USD per apartment', source_id='bae2024', note='Report says "nearly $490,000 per unit"'),
    dict(benchmark_id='hth-average-capital', description='King County Health Through Housing average capital cost per unit among bought buildings, 2021-2024', value_usd=285772, unit='USD per unit', source_id='hth2024', note='County-reported average'),
    dict(benchmark_id='hth-operating-2024', description='King County Health Through Housing average operating cost per unit, 2024', value_usd=33718, unit='USD per unit per year', source_id='hth2024', note='Program operations and building maintenance'),
    dict(benchmark_id='commerce-king-median-fy2023', description='Median final cost per unit, all new state-funded multifamily buildings finished in King County, fiscal 2023', value_usd=356813, unit='USD per apartment', source_id='cost2023', note='Five projects; all populations'),
]
with open(os.path.join(out, 'benchmarks.csv'), 'w', newline='', encoding='utf8') as f:
    w = csv.DictWriter(f, fieldnames=list(BENCH[0].keys()))
    w.writeheader()
    w.writerows(BENCH)

res = {
    'seattle_affordable_nonsupportive_2023': summary(aff_rows),
    'seattle_planned_2023_2025': summary(pick('planned_new', {2023, 2024, 2025})),
    'seattle_planned_2021_2022': summary(pick('planned_new', {2021, 2022})),
    'seattle_planned_all': summary(pick('planned_new')),
    'suburb_planned': summary(pick('planned_new_suburb')),
    'seattle_completed': summary(pick('completed_new')),
    'county_purchase_all': summary(pick('county_purchase')),
    'county_purchase_seattle': summary(pick('county_purchase', city='Seattle')),
    'woodland_change_2021_to_2023': round(524477 / 379212 - 1, 4),
}
json.dump(res, open(os.path.join(out, 'analyses.json'), 'w'), indent=1)
for k, v in res.items():
    print(k, v)