#!/usr/bin/env python3
"""
Housing Crisis Data Collection Script

Collects data on evictions, homelessness, rent burden, and housing affordability
across U.S. counties and metro areas.

Data Sources:
- Census Bureau API: Rent burden, median rents, housing costs
- HUD Fair Market Rents: Section 8 payment standards by metro area
- Census ACS: Housing unit data, renter demographics
- Zillow (if available): Home values and rent index

Manual Downloads Required:
- Eviction Lab: https://evictionlab.org/get-the-data/
- HUD PIT Counts: https://www.hudexchange.info/programs/hdx/pit-hic/

Usage:
    python fetch_housing_data.py

Environment Variables:
    CENSUS_API_KEY: Census Bureau API key
    USE_CACHED_DATA: true/false (default: false)

Output:
    data/housing_crisis_merged.csv - County-level housing metrics
    data/housing_crisis_metadata.json - Collection timestamps and sources
"""

import os
import sys
import json
import time
from datetime import datetime
from pathlib import Path

import pandas as pd
import requests
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Configuration
CENSUS_API_KEY = os.getenv('CENSUS_API_KEY', '')
USE_CACHED_DATA = os.getenv('USE_CACHED_DATA', 'false').lower() == 'true'
CENSUS_YEAR = 2022  # Most recent ACS 5-year estimates

# Directories
BASE_DIR = Path(__file__).parent
DATA_DIR = BASE_DIR / 'data'
CACHE_DIR = BASE_DIR / 'cache'

# Create directories
DATA_DIR.mkdir(exist_ok=True)
CACHE_DIR.mkdir(exist_ok=True)

# Metadata tracking
metadata = {
    'collection_date': datetime.now().isoformat(),
    'sources': {},
    'record_counts': {}
}


def fetch_rent_burden():
    """
    Fetch rent burden data (% of income spent on rent).

    Census Table B25070: Gross rent as percentage of household income
    """
    print("\n💰 Fetching rent burden data...")

    cache_file = CACHE_DIR / 'rent_burden.csv'
    if USE_CACHED_DATA and cache_file.exists():
        print(f"   Using cached data from {cache_file}")
        df = pd.read_csv(cache_file)
        metadata['sources']['rent_burden'] = 'cached'
        return df

    # Get rent burden categories
    url = f"https://api.census.gov/data/{CENSUS_YEAR}/acs/acs5"
    params = {
        'get': 'NAME,B25070_001E,B25070_007E,B25070_008E,B25070_009E,B25070_010E',
        # Total renters, 30-34.9%, 35-39.9%, 40-49.9%, 50%+
        'for': 'county:*',
        'key': CENSUS_API_KEY
    }

    try:
        response = requests.get(url, params=params, timeout=30)
        response.raise_for_status()
        data = response.json()

        df = pd.DataFrame(data[1:], columns=data[0])
        df['fips'] = df['state'] + df['county']

        df = df.rename(columns={
            'NAME': 'county_name',
            'B25070_001E': 'total_renters',
            'B25070_007E': 'rent_burden_30_35',
            'B25070_008E': 'rent_burden_35_40',
            'B25070_009E': 'rent_burden_40_50',
            'B25070_010E': 'rent_burden_50_plus'
        })

        # Convert to numeric
        numeric_cols = ['total_renters', 'rent_burden_30_35', 'rent_burden_35_40',
                       'rent_burden_40_50', 'rent_burden_50_plus']
        for col in numeric_cols:
            df[col] = pd.to_numeric(df[col], errors='coerce')

        # Calculate percentages
        df['rent_burdened_30plus'] = (
            df['rent_burden_30_35'] + df['rent_burden_35_40'] +
            df['rent_burden_40_50'] + df['rent_burden_50_plus']
        )
        df['rent_burdened_50plus'] = df['rent_burden_50_plus']

        df['pct_rent_burdened_30plus'] = (df['rent_burdened_30plus'] / df['total_renters'] * 100).round(2)
        df['pct_rent_burdened_50plus'] = (df['rent_burdened_50plus'] / df['total_renters'] * 100).round(2)

        df = df[['fips', 'county_name', 'total_renters', 'pct_rent_burdened_30plus', 'pct_rent_burdened_50plus']]
        df.to_csv(cache_file, index=False)

        metadata['sources']['rent_burden'] = f"Census ACS {CENSUS_YEAR}, Table B25070"
        metadata['record_counts']['rent_burden'] = len(df)

        print(f"   ✓ Collected rent burden data for {len(df)} counties")
        return df

    except requests.exceptions.RequestException as e:
        print(f"   ✗ Error fetching rent burden data: {e}")
        return pd.DataFrame()


def fetch_median_rents():
    """Fetch median gross rent by county"""
    print("\n🏠 Fetching median rent data...")

    cache_file = CACHE_DIR / 'median_rents.csv'
    if USE_CACHED_DATA and cache_file.exists():
        print(f"   Using cached data from {cache_file}")
        df = pd.read_csv(cache_file)
        metadata['sources']['median_rents'] = 'cached'
        return df

    # Census Table B25064: Median gross rent
    url = f"https://api.census.gov/data/{CENSUS_YEAR}/acs/acs5"
    params = {
        'get': 'NAME,B25064_001E',  # Median gross rent
        'for': 'county:*',
        'key': CENSUS_API_KEY
    }

    try:
        response = requests.get(url, params=params, timeout=30)
        response.raise_for_status()
        data = response.json()

        df = pd.DataFrame(data[1:], columns=data[0])
        df['fips'] = df['state'] + df['county']

        df = df.rename(columns={
            'NAME': 'county_name',
            'B25064_001E': 'median_gross_rent'
        })

        df['median_gross_rent'] = pd.to_numeric(df['median_gross_rent'], errors='coerce')
        df = df[['fips', 'county_name', 'median_gross_rent']]
        df.to_csv(cache_file, index=False)

        metadata['sources']['median_rents'] = f"Census ACS {CENSUS_YEAR}, Table B25064"
        metadata['record_counts']['median_rents'] = len(df)

        print(f"   ✓ Collected median rent data for {len(df)} counties")
        return df

    except requests.exceptions.RequestException as e:
        print(f"   ✗ Error fetching median rent data: {e}")
        return pd.DataFrame()


def fetch_median_income():
    """Fetch median household income by county"""
    print("\n💵 Fetching median income data...")

    cache_file = CACHE_DIR / 'median_income.csv'
    if USE_CACHED_DATA and cache_file.exists():
        print(f"   Using cached data from {cache_file}")
        df = pd.read_csv(cache_file)
        metadata['sources']['median_income'] = 'cached'
        return df

    url = f"https://api.census.gov/data/{CENSUS_YEAR}/acs/acs5"
    params = {
        'get': 'NAME,B19013_001E',  # Median household income
        'for': 'county:*',
        'key': CENSUS_API_KEY
    }

    try:
        response = requests.get(url, params=params, timeout=30)
        response.raise_for_status()
        data = response.json()

        df = pd.DataFrame(data[1:], columns=data[0])
        df['fips'] = df['state'] + df['county']

        df = df.rename(columns={
            'NAME': 'county_name',
            'B19013_001E': 'median_household_income'
        })

        df['median_household_income'] = pd.to_numeric(df['median_household_income'], errors='coerce')
        df = df[['fips', 'county_name', 'median_household_income']]
        df.to_csv(cache_file, index=False)

        metadata['sources']['median_income'] = f"Census ACS {CENSUS_YEAR}, Table B19013"
        metadata['record_counts']['median_income'] = len(df)

        print(f"   ✓ Collected median income data for {len(df)} counties")
        return df

    except requests.exceptions.RequestException as e:
        print(f"   ✗ Error fetching median income data: {e}")
        return pd.DataFrame()


def fetch_housing_units():
    """Fetch housing unit data (vacancy rates, owner vs renter)"""
    print("\n🏘️  Fetching housing unit data...")

    cache_file = CACHE_DIR / 'housing_units.csv'
    if USE_CACHED_DATA and cache_file.exists():
        print(f"   Using cached data from {cache_file}")
        df = pd.read_csv(cache_file)
        metadata['sources']['housing_units'] = 'cached'
        return df

    # Census Table B25003: Tenure (owner vs renter)
    url = f"https://api.census.gov/data/{CENSUS_YEAR}/acs/acs5"
    params = {
        'get': 'NAME,B25003_001E,B25003_002E,B25003_003E',  # Total, owner, renter
        'for': 'county:*',
        'key': CENSUS_API_KEY
    }

    try:
        response = requests.get(url, params=params, timeout=30)
        response.raise_for_status()
        data = response.json()

        df = pd.DataFrame(data[1:], columns=data[0])
        df['fips'] = df['state'] + df['county']

        df = df.rename(columns={
            'NAME': 'county_name',
            'B25003_001E': 'total_housing_units',
            'B25003_002E': 'owner_occupied',
            'B25003_003E': 'renter_occupied'
        })

        for col in ['total_housing_units', 'owner_occupied', 'renter_occupied']:
            df[col] = pd.to_numeric(df[col], errors='coerce')

        df['pct_renter'] = (df['renter_occupied'] / df['total_housing_units'] * 100).round(2)
        df = df[['fips', 'county_name', 'total_housing_units', 'owner_occupied', 'renter_occupied', 'pct_renter']]
        df.to_csv(cache_file, index=False)

        metadata['sources']['housing_units'] = f"Census ACS {CENSUS_YEAR}, Table B25003"
        metadata['record_counts']['housing_units'] = len(df)

        print(f"   ✓ Collected housing unit data for {len(df)} counties")
        return df

    except requests.exceptions.RequestException as e:
        print(f"   ✗ Error fetching housing unit data: {e}")
        return pd.DataFrame()


def calculate_affordability_metrics(merged_df):
    """
    Calculate housing affordability metrics.

    Standard: Housing is affordable if it costs ≤30% of household income
    """
    print("\n🧮 Calculating affordability metrics...")

    if 'median_gross_rent' not in merged_df.columns or 'median_household_income' not in merged_df.columns:
        print("   ✗ Cannot calculate: missing rent or income data")
        return merged_df

    # Annual rent
    merged_df['annual_rent'] = merged_df['median_gross_rent'] * 12

    # Rent as % of income
    merged_df['rent_to_income_ratio'] = (
        merged_df['annual_rent'] / merged_df['median_household_income'] * 100
    ).round(2)

    # Affordability categories
    def affordability_category(ratio):
        if pd.isna(ratio):
            return 'Unknown'
        elif ratio <= 30:
            return 'Affordable'
        elif ratio <= 40:
            return 'Moderately Burdened'
        elif ratio <= 50:
            return 'Severely Burdened'
        else:
            return 'Extremely Burdened'

    merged_df['affordability_category'] = merged_df['rent_to_income_ratio'].apply(affordability_category)

    # Hours of minimum wage work to afford rent
    # Federal minimum wage: $7.25/hr
    # 2BR Fair Market Rent equivalent: use median gross rent
    merged_df['hours_at_min_wage_for_rent'] = (merged_df['median_gross_rent'] / 7.25).round(0)

    # Weekly work hours needed (40 hrs = full time)
    merged_df['weeks_at_min_wage_for_rent'] = (merged_df['hours_at_min_wage_for_rent'] / 40).round(1)

    print(f"   ✓ Calculated affordability metrics")
    return merged_df


def fetch_hud_homelessness():
    """
    Fetch HUD Point-in-Time (PIT) homelessness counts by CoC.
    Note: CoC (Continuum of Care) boundaries don't perfectly align with counties.
    This function uses a mapping proxy or manual download placeholder.
    """
    print("\n🏚️  Fetching HUD homelessness data...")
    
    cache_file = CACHE_DIR / 'hud_homelessness.csv'
    if USE_CACHED_DATA and cache_file.exists():
        print(f"   Using cached data from {cache_file}")
        return pd.read_csv(cache_file)

    # 2024 PIT Estimates (Sample URL - usually xlsx)
    # Since we can't easily parse CoC-to-County without a crosswalk file, 
    # we will placeholder this for now or output a warning.
    print("   ⚠️  Automatic download of PIT data requires CoC-to-County crosswalk.")
    print("   Please download '2007-2024-PIT-Counts-by-State.xlsx' from HUD Exchange manually.")
    
    # Return empty DF for now to not break pipeline
    return pd.DataFrame()

def merge_all_data():
    """Merge all datasets on FIPS code"""
    print("\n🔗 Merging all datasets...")

    # Fetch all datasets
    rent_burden_df = fetch_rent_burden()
    median_rent_df = fetch_median_rents()
    income_df = fetch_median_income()
    housing_df = fetch_housing_units()
    # hud_df = fetch_hud_homelessness()  # Todo: Integrate once crosswalk available

    if rent_burden_df.empty:
        print("   ✗ Cannot merge: rent burden data missing")
        return None

    # Start with rent burden data
    merged = rent_burden_df.copy()

    # Merge median rents
    if not median_rent_df.empty:
        merged = merged.merge(median_rent_df[['fips', 'median_gross_rent']], on='fips', how='left')

    # Merge income
    if not income_df.empty:
        merged = merged.merge(income_df[['fips', 'median_household_income']], on='fips', how='left')

    # Merge housing units
    if not housing_df.empty:
        merged = merged.merge(
            housing_df[['fips', 'total_housing_units', 'owner_occupied', 'renter_occupied', 'pct_renter']],
            on='fips',
            how='left'
        )

    # Calculate affordability metrics
    merged = calculate_affordability_metrics(merged)

    print(f"   ✓ Merged {len(merged)} counties")
    return merged


def main():
    """Main execution"""
    print("=" * 70)
    print("Housing Crisis Data Collection")
    print("=" * 70)

    if not CENSUS_API_KEY:
        print("\n⚠️  WARNING: No Census API key found")
        print("   Set CENSUS_API_KEY in .env file")
        print("   Data collection will fail without an API key")
        return

    print(f"\n📅 Census Year: {CENSUS_YEAR}")
    print(f"🗄️  Use Cached Data: {USE_CACHED_DATA}")

    # Merge all data
    merged_df = merge_all_data()

    if merged_df is None or merged_df.empty:
        print("\n✗ FAILED: No data collected")
        return

    # Save merged data
    output_file = DATA_DIR / 'housing_crisis_merged.csv'
    merged_df.to_csv(output_file, index=False)

    # Save metadata
    metadata_file = DATA_DIR / 'housing_crisis_metadata.json'
    with open(metadata_file, 'w') as f:
        json.dump(metadata, f, indent=2)

    # Summary statistics
    print("\n" + "=" * 70)
    print("📊 SUMMARY STATISTICS")
    print("=" * 70)
    print(f"Total counties: {len(merged_df)}")

    if 'pct_rent_burdened_30plus' in merged_df.columns:
        avg_burden_30 = merged_df['pct_rent_burdened_30plus'].mean()
        print(f"\nAverage % of renters paying >30% of income: {avg_burden_30:.1f}%")

    if 'pct_rent_burdened_50plus' in merged_df.columns:
        avg_burden_50 = merged_df['pct_rent_burdened_50plus'].mean()
        print(f"Average % of renters paying >50% of income: {avg_burden_50:.1f}%")

    if 'median_gross_rent' in merged_df.columns:
        avg_rent = merged_df['median_gross_rent'].median()
        print(f"\nMedian gross rent (national median of counties): ${avg_rent:.0f}/month")

    if 'median_household_income' in merged_df.columns:
        avg_income = merged_df['median_household_income'].median()
        print(f"Median household income (national median of counties): ${avg_income:,.0f}/year")

    if 'rent_to_income_ratio' in merged_df.columns:
        avg_ratio = merged_df['rent_to_income_ratio'].mean()
        print(f"\nAverage rent-to-income ratio: {avg_ratio:.1f}%")
        print(f"(30% is considered the affordability threshold)")

    if 'affordability_category' in merged_df.columns:
        print(f"\nAffordability breakdown:")
        print(merged_df['affordability_category'].value_counts())

    if 'weeks_at_min_wage_for_rent' in merged_df.columns:
        avg_weeks = merged_df['weeks_at_min_wage_for_rent'].median()
        print(f"\nMedian weeks of full-time minimum wage work to afford rent: {avg_weeks:.1f} weeks")
        print(f"(4.3 weeks per month means rent is affordable at minimum wage)")

    print(f"\n📁 Output files:")
    print(f"   {output_file}")
    print(f"   {metadata_file}")

    print("\n" + "=" * 70)
    print("✓ SUCCESS: Data collection complete!")
    print("=" * 70)

    print("\n📋 NEXT STEPS:")
    print("1. Manual data collection still needed:")
    print("   - Eviction Lab data: https://evictionlab.org/get-the-data/")
    print("   - HUD PIT homelessness counts: https://www.hudexchange.info/programs/hdx/pit-hic/")
    print("   - Zillow Research Data: https://www.zillow.com/research/data/")
    print("2. Create HTML story page with D3 visualizations")
    print("3. Build eviction choropleth, affordability calculator, and timeline visualizations")


if __name__ == '__main__':
    main()