Plan: TANF Federal Reporting — ACF-199, ACF-196, and Work Participation Rate

// SPDX-License-Identifier: AGPL-3.0-or-later

/// The 12 ACF-196 expenditure categories per 45 CFR 265.9.
pub const ACF196_CATEGORIES: &[&str] = &[
    "basic_assistance",
    "child_care_non_transferred",
    "child_care_transferred_ccdf",
    "education_and_training",
    "work_subsidies",
    "transportation",
    "individual_development_accounts",
    "refundable_eitc",
    "non_assistance_two_parent",
    "non_assistance_other",
    "systems",
    "administration",
];

// SPDX-License-Identifier: AGPL-3.0-or-later

/// WPR computation result (in-memory, before DB persistence).
pub struct WprResult {
    pub all_family_numerator: i32,
    pub all_family_denominator: i32,
    pub all_family_rate: Decimal,
    pub all_family_target: Decimal,
    pub all_family_meets_target: bool,
    pub two_parent_numerator: i32,
    pub two_parent_denominator: i32,
    pub two_parent_rate: Decimal,
    pub two_parent_target: Decimal,
    pub two_parent_meets_target: bool,
    pub caseload_reduction_credit: Decimal,
}

/// ACF-196 generation result.
pub struct Acf196GenerationResult {
    pub categories_inserted: i64,
    pub families_served_basic_assistance: Option<i32>,
}

/// List all TANF determinations (most recent first, limit 500).
/// Used by canopy-reporting for ACF-199 extraction.
pub async fn list_determinations(
    pool: &PgPool,
) -> Result<Vec<TanfDetermination>, sqlx::Error> {
    sqlx::query_as::<_, TanfDetermination>(
        "SELECT * FROM tanf_determinations ORDER BY determined_at DESC LIMIT 500",
    )
    .fetch_all(pool)
    .await
}

/// GET /v1/determinations — List all TANF determinations.
/// Used by canopy-reporting for ACF-199 monthly extraction.
#[utoipa::path(
    get,
    path = "/determinations",
    tag = "Determination",
    security(("bearer" = [])),
    responses(
        (status = 200, description = "All TANF determinations", body = Vec<TanfDetermination>),
    )
)]
pub async fn list_determinations(
    Extension(claims): Extension<Claims>,
    Extension(db): Extension<PgPool>,
) -> Result<Json<Vec<TanfDetermination>>, ApiError> {
    claims.require_supervisor_or_above()?;

    let dets = store::list_determinations(&db)
        .await
        .map_err(|e| ApiError::internal("list determinations", e))?;
    Ok(Json(dets))
}

.route("/determinations", get(handlers::list_determinations))

#[derive(Debug, Deserialize)]
pub struct TanfWorkRequirementSummary {
    pub id: uuid::Uuid,
    pub person_id: uuid::Uuid,
    pub required: bool,
    pub exempt: bool,
    pub status: String,
    pub sanction_level: Option<i32>,
    pub exemption_reason: Option<String>,       // NEW
}

/// Fetch all work activities for a work requirement.
/// canopy-tanf does not yet expose this endpoint; returns empty Vec on error.
/// This is a forward-looking stub that will be used when canopy-tanf adds
/// GET /v1/work-requirements/{person_id}/activities.
pub async fn list_tanf_work_activities(
    &self,
    person_id: uuid::Uuid,
) -> anyhow::Result<Vec<TanfWorkActivitySummary>> {
    self.tanf
        .get(&format!("/v1/work-requirements/{person_id}/activities"))
        .await
        .or_else(|_| Ok(Vec::new()))
}

#[derive(Debug, Deserialize)]
pub struct TanfWorkActivitySummary {
    pub id: uuid::Uuid,
    pub activity_type: String,
    pub hours_per_week: Decimal,
    pub effective_date: NaiveDate,
    pub end_date: Option<NaiveDate>,
    pub verified: bool,
}

/// Core work activities per 45 CFR 261.31(b).
const CORE_ACTIVITIES: &[&str] = &[
    "unsubsidized_employment",
    "subsidized_private_employment",
    "subsidized_public_employment",
    "work_experience",
    "on_the_job_training",
    "community_service",
    "providing_child_care",
    "vocational_training",  // 12-month limit
];

pub async fn extract_acf199(
    db: &PgPool,
    clients: &ServiceClients,
    report_month: NaiveDate,
) -> anyhow::Result<Acf199ExtractionResult> {
    let determinations = clients.list_tanf_determinations().await?;
    let mut inserted: i64 = 0;

    for det in &determinations {
        if det.status != "approved" && det.status != "sanctioned" {
            continue; // Include active and sanctioned cases in ACF-199
        }

        // (a) Household composition
        let hh = match clients.get_household(det.household_id.into()).await {
            Ok(hh) => hh,
            Err(e) => {
                warn!(household_id = %det.household_id, error = %e, "skipping — household fetch failed");
                continue;
            }
        };

        let member_count = hh.members.len() as i32;
        let adult_count = hh.members.iter().filter(|m| m.relationship != "child").count() as i32;
        let child_count = member_count - adult_count;
        let family_type = if adult_count >= 2 {
            "two_parent"
        } else if child_count > 0 && adult_count > 0 {
            "single_parent"
        } else {
            "child_only"
        };

        // (b) Work requirements and sanction status — aggregate across adult members
        let mut total_work_hours = Decimal::ZERO;
        let mut core_activity_hours = Decimal::ZERO;
        let mut max_sanction_level: Option<i32> = None;
        let mut any_exempt = false;

        for member in &hh.members {
            if member.relationship == "child" {
                continue;
            }
            if let Ok(Some(wr)) = clients.get_tanf_work_requirements(member.person_id).await {
                if wr.exempt {
                    any_exempt = true;
                }
                if let Some(sl) = wr.sanction_level {
                    max_sanction_level = Some(max_sanction_level.map_or(sl, |prev| prev.max(sl)));
                }
            }
            // Fetch activities for this person (returns empty if endpoint absent)
            let activities = clients.list_tanf_work_activities(member.person_id).await.unwrap_or_default();
            for act in &activities {
                total_work_hours += act.hours_per_week;
                if CORE_ACTIVITIES.contains(&act.activity_type.as_str()) {
                    core_activity_hours += act.hours_per_week;
                }
            }
        }

        // (c) Time limits — use first adult's months_used
        let mut months_this_state: i32 = 0;
        for member in &hh.members {
            if member.relationship == "child" {
                continue;
            }
            if let Ok(Some(tl)) = clients.get_tanf_time_limits(member.person_id).await {
                months_this_state = months_this_state.max(tl.months_used);
            }
            break; // Use first (head-of-household) adult's count
        }

        // (d) Case status and closure reason
        let sanction_status = max_sanction_level.map(|sl| format!("level_{sl}"));
        let case_status = if max_sanction_level.is_some() {
            "sanctioned"
        } else {
            "active"
        };
        let closure_reason: Option<String> = None; // Only populated for closed cases — approved/sanctioned are still open

        // (e) Upsert snapshot
        sqlx::query(
            r#"INSERT INTO tanf_acf199_snapshots
               (id, report_month, case_id, family_type, household_size, adult_count, child_count,
                benefit_amount, sanction_status, case_status, closure_reason,
                months_this_state, months_other_states,
                total_work_hours, core_activity_hours, extracted_at)
               VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, now())
               ON CONFLICT (report_month, case_id) DO UPDATE
               SET family_type = EXCLUDED.family_type, household_size = EXCLUDED.household_size,
                   adult_count = EXCLUDED.adult_count, child_count = EXCLUDED.child_count,
                   benefit_amount = EXCLUDED.benefit_amount, sanction_status = EXCLUDED.sanction_status,
                   case_status = EXCLUDED.case_status, closure_reason = EXCLUDED.closure_reason,
                   months_this_state = EXCLUDED.months_this_state, months_other_states = EXCLUDED.months_other_states,
                   total_work_hours = EXCLUDED.total_work_hours, core_activity_hours = EXCLUDED.core_activity_hours,
                   extracted_at = now()"#,
        )
        .bind(report_month)                                       // $1
        .bind(det.id)                                             // $2
        .bind(family_type)                                        // $3
        .bind(member_count)                                       // $4
        .bind(adult_count)                                        // $5
        .bind(child_count)                                        // $6
        .bind(det.benefit_amount.unwrap_or(Decimal::ZERO))        // $7
        .bind(&sanction_status)                                   // $8
        .bind(case_status)                                        // $9
        .bind(&closure_reason)                                    // $10
        .bind(months_this_state)                                  // $11
        .bind(0i32)                                               // $12 months_other_states (no cross-state source)
        .bind(total_work_hours)                                   // $13
        .bind(core_activity_hours)                                // $14
        .execute(db)
        .await?;

        inserted += 1;
    }

    info!(report_month = %report_month, snapshots = inserted, "ACF-199 extraction complete");
    Ok(Acf199ExtractionResult { snapshots_inserted: inserted })
}

[tanf.wpr]
# Work Participation Rate targets per 42 USC §607 / 45 CFR 261.21
all_family_target_pct = 50                # 50% all-family rate
two_parent_target_pct = 90                # 90% two-parent rate
single_parent_hours_per_week = 30         # 45 CFR 261.31(a)
single_parent_child_under_6_hours = 20    # 45 CFR 261.31(a)(2)
two_parent_hours_per_week = 35            # 45 CFR 261.31(b)
two_parent_fed_child_care_hours = 55      # 45 CFR 261.31(b)(2)
core_activity_minimum_hours = 20          # 45 CFR 261.31(a) and (b)
fy2005_baseline_caseload = 52000          # Georgia FY2005 TANF caseload (for CRC calculation)

[tanf_wpr_all_family_target_pct]
value = "50"
source = "45 CFR 261.21(a)"
note = "Federal all-family work participation rate target"

[tanf_wpr_two_parent_target_pct]
value = "90"
source = "45 CFR 261.21(b)"
note = "Federal two-parent work participation rate target"

[tanf_wpr_fy2005_baseline_caseload]
value = "52000"
source = "ACF TANF caseload data, Georgia FY2005"
note = "Baseline for caseload reduction credit per 45 CFR 261.41"

// SPDX-License-Identifier: AGPL-3.0-or-later

//! Work Participation Rate calculation per 45 CFR 261.22.

use chrono::NaiveDate;
use rust_decimal::Decimal;
use rust_decimal_macros::dec;
use sqlx::PgPool;
use tracing::info;

use crate::domain::TanfAcf199Snapshot;

/// WPR targets loaded from jurisdiction.toml.
pub struct WprConfig {
    pub all_family_target_pct: Decimal,
    pub two_parent_target_pct: Decimal,
    pub single_parent_hours: Decimal,
    pub single_parent_child_under_6_hours: Decimal,
    pub two_parent_hours: Decimal,
    pub core_activity_minimum_hours: Decimal,
    pub fy2005_baseline_caseload: i32,
}

impl Default for WprConfig {
    /// Default values — MUST be overridden by jurisdiction.toml in production.
    fn default() -> Self {
        Self {
            all_family_target_pct: dec!(50),
            two_parent_target_pct: dec!(90),
            single_parent_hours: dec!(30),
            single_parent_child_under_6_hours: dec!(20),
            two_parent_hours: dec!(35),
            core_activity_minimum_hours: dec!(20),
            fy2005_baseline_caseload: 52_000,
        }
    }
}

pub struct WprResult {
    pub all_family_numerator: i32,
    pub all_family_denominator: i32,
    pub all_family_rate: Decimal,
    pub all_family_target: Decimal,
    pub all_family_meets_target: bool,
    pub two_parent_numerator: i32,
    pub two_parent_denominator: i32,
    pub two_parent_rate: Decimal,
    pub two_parent_target: Decimal,
    pub two_parent_meets_target: bool,
    pub caseload_reduction_credit: Decimal,
}

/// Compute WPR for a given month from ACF-199 snapshots already in the DB.
pub async fn compute_wpr(
    db: &PgPool,
    report_month: NaiveDate,
    config: &WprConfig,
) -> anyhow::Result<WprResult> {
    let snapshots: Vec<TanfAcf199Snapshot> = sqlx::query_as(
        "SELECT * FROM tanf_acf199_snapshots WHERE report_month = $1",
    )
    .bind(report_month)
    .fetch_all(db)
    .await?;

    let mut all_num = 0i32;
    let mut all_den = 0i32;
    let mut tp_num = 0i32;
    let mut tp_den = 0i32;

    for snap in &snapshots {
        if snap.family_type == "child_only" {
            continue; // Excluded from WPR per 45 CFR 261.22
        }

        let total_hours = snap.total_work_hours.unwrap_or(Decimal::ZERO);
        let core_hours = snap.core_activity_hours.unwrap_or(Decimal::ZERO);

        let required_hours = if snap.family_type == "two_parent" {
            config.two_parent_hours
        } else if snap.child_count > 0 {
            // Approximate child-under-6 with any child present
            config.single_parent_child_under_6_hours
        } else {
            config.single_parent_hours
        };

        let meets_hours = total_hours >= required_hours
            && core_hours >= config.core_activity_minimum_hours;

        // All-family rate
        all_den += 1;
        if meets_hours {
            all_num += 1;
        }

        // Two-parent rate
        if snap.family_type == "two_parent" {
            tp_den += 1;
            if meets_hours {
                tp_num += 1;
            }
        }
    }

    // Caseload reduction credit (45 CFR 261.41)
    let current_caseload = all_den;
    let crc = if config.fy2005_baseline_caseload > 0 && current_caseload < config.fy2005_baseline_caseload {
        let decline = Decimal::from(config.fy2005_baseline_caseload - current_caseload);
        let baseline = Decimal::from(config.fy2005_baseline_caseload);
        (decline / baseline * dec!(100)).round_dp(2)
    } else {
        Decimal::ZERO
    };

    let all_family_target = (config.all_family_target_pct - crc).max(Decimal::ZERO);
    let two_parent_target = (config.two_parent_target_pct - crc).max(Decimal::ZERO);

    let all_family_rate = if all_den > 0 {
        (Decimal::from(all_num) / Decimal::from(all_den) * dec!(100)).round_dp(2)
    } else {
        Decimal::ZERO
    };

    let two_parent_rate = if tp_den > 0 {
        (Decimal::from(tp_num) / Decimal::from(tp_den) * dec!(100)).round_dp(2)
    } else {
        Decimal::ZERO
    };

    let result = WprResult {
        all_family_numerator: all_num,
        all_family_denominator: all_den,
        all_family_rate,
        all_family_target,
        all_family_meets_target: all_family_rate >= all_family_target,
        two_parent_numerator: tp_num,
        two_parent_denominator: tp_den,
        two_parent_rate,
        two_parent_target,
        two_parent_meets_target: two_parent_rate >= two_parent_target,
        caseload_reduction_credit: crc,
    };

    info!(
        report_month = %report_month,
        all_rate = %all_family_rate,
        tp_rate = %two_parent_rate,
        crc = %crc,
        "WPR calculation complete"
    );

    Ok(result)
}

/// Insert or update a WPR calculation for a month.
pub async fn upsert_tanf_wpr(
    pool: &PgPool,
    report_month: NaiveDate,
    r: &crate::reporting::wpr::WprResult,
) -> Result<crate::domain::TanfWprCalculation, sqlx::Error> {
    sqlx::query_as(
        r#"INSERT INTO tanf_wpr_calculations
           (id, report_month, all_family_numerator, all_family_denominator,
            all_family_rate, all_family_target, all_family_meets_target,
            two_parent_numerator, two_parent_denominator,
            two_parent_rate, two_parent_target, two_parent_meets_target,
            caseload_reduction_credit, calculated_at)
           VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, now())
           ON CONFLICT (report_month) DO UPDATE
           SET all_family_numerator = EXCLUDED.all_family_numerator,
               all_family_denominator = EXCLUDED.all_family_denominator,
               all_family_rate = EXCLUDED.all_family_rate,
               all_family_target = EXCLUDED.all_family_target,
               all_family_meets_target = EXCLUDED.all_family_meets_target,
               two_parent_numerator = EXCLUDED.two_parent_numerator,
               two_parent_denominator = EXCLUDED.two_parent_denominator,
               two_parent_rate = EXCLUDED.two_parent_rate,
               two_parent_target = EXCLUDED.two_parent_target,
               two_parent_meets_target = EXCLUDED.two_parent_meets_target,
               caseload_reduction_credit = EXCLUDED.caseload_reduction_credit,
               calculated_at = now()
           RETURNING *"#,
    )
    .bind(report_month)
    .bind(r.all_family_numerator)
    .bind(r.all_family_denominator)
    .bind(r.all_family_rate)
    .bind(r.all_family_target)
    .bind(r.all_family_meets_target)
    .bind(r.two_parent_numerator)
    .bind(r.two_parent_denominator)
    .bind(r.two_parent_rate)
    .bind(r.two_parent_target)
    .bind(r.two_parent_meets_target)
    .bind(r.caseload_reduction_credit)
    .fetch_one(pool)
    .await
}

/// POST /v1/reporting/tanf/wpr — Calculate WPR for a month.
/// Requires ACF-199 snapshots for the month to already exist.
async fn generate_tanf_wpr(
    Extension(claims): Extension<Claims>,
    State(state): State<AppState>,
    Json(req): Json<GenerateTanfReportRequest>,
) -> Result<(StatusCode, Json<TanfWprCalculation>), ApiError> {
    claims.require_supervisor_or_above()?;

    let config = crate::reporting::wpr::WprConfig::default(); // TODO: load from jurisdiction.toml
    let result = crate::reporting::wpr::compute_wpr(state.db.inner(), req.report_month, &config)
        .await
        .map_err(|e| ApiError::internal("WPR calculation failed", e))?;

    let row = store::upsert_tanf_wpr(state.db.inner(), req.report_month, &result)
        .await
        .map_err(ApiError::from)?;

    Ok((StatusCode::CREATED, Json(row)))
}

.route("/reporting/tanf/wpr", post(generate_tanf_wpr).get(list_tanf_wpr))

// SPDX-License-Identifier: AGPL-3.0-or-later

//! ACF-196 quarterly financial report generation.
//! Per 45 CFR 265.9: quarterly expenditure data across 12 categories.
//!
//! NOTE: Actual expenditure amounts require integration with the state
//! accounting system (SAP/PeopleSoft), which is out of scope. All
//! financial amounts are set to 0. Only `families_served` for the
//! `basic_assistance` category is derived from ACF-199 snapshots.

use chrono::NaiveDate;
use rust_decimal::Decimal;
use sqlx::PgPool;
use tracing::info;

/// The 12 ACF-196 expenditure categories per 45 CFR 265.9.
pub const ACF196_CATEGORIES: &[&str] = &[
    "basic_assistance",
    "child_care_non_transferred",
    "child_care_transferred_ccdf",
    "education_and_training",
    "work_subsidies",
    "transportation",
    "individual_development_accounts",
    "refundable_eitc",
    "non_assistance_two_parent",
    "non_assistance_other",
    "systems",
    "administration",
];

pub struct Acf196GenerationResult {
    pub categories_inserted: i64,
    pub families_served_basic_assistance: Option<i32>,
}

/// Generate ACF-196 stub rows for a fiscal quarter.
/// Returns the quarter's months as (month1, month2, month3).
fn quarter_months(fiscal_year: i32, fiscal_quarter: i32) -> (NaiveDate, NaiveDate, NaiveDate) {
    let start_month = (fiscal_quarter - 1) * 3 + 1;
    (
        NaiveDate::from_ymd_opt(fiscal_year, start_month as u32, 1).expect("valid date"),
        NaiveDate::from_ymd_opt(fiscal_year, (start_month + 1) as u32, 1).expect("valid date"),
        NaiveDate::from_ymd_opt(fiscal_year, (start_month + 2) as u32, 1).expect("valid date"),
    )
}

pub async fn generate_acf196(
    db: &PgPool,
    fiscal_year: i32,
    fiscal_quarter: i32,
) -> anyhow::Result<Acf196GenerationResult> {
    let (m1, m2, m3) = quarter_months(fiscal_year, fiscal_quarter);

    // Count distinct cases from ACF-199 snapshots for basic_assistance families_served
    let families_served: Option<i32> = sqlx::query_scalar(
        "SELECT COUNT(DISTINCT case_id)::INT FROM tanf_acf199_snapshots WHERE report_month IN ($1, $2, $3)",
    )
    .bind(m1)
    .bind(m2)
    .bind(m3)
    .fetch_one(db)
    .await
    .ok();

    let mut inserted: i64 = 0;
    for category in ACF196_CATEGORIES {
        let cat_families = if *category == "basic_assistance" {
            families_served
        } else {
            None
        };

        sqlx::query(
            r#"INSERT INTO tanf_acf196_reports
               (id, fiscal_year, fiscal_quarter, category,
                federal_amount, state_amount, total_amount, families_served, generated_at)
               VALUES (gen_random_uuid(), $1, $2, $3, 0, 0, 0, $4, now())
               ON CONFLICT (fiscal_year, fiscal_quarter, category) DO UPDATE
               SET families_served = EXCLUDED.families_served,
                   generated_at = now()"#,
        )
        .bind(fiscal_year)
        .bind(fiscal_quarter)
        .bind(category)
        .bind(cat_families)
        .execute(db)
        .await?;

        inserted += 1;
    }

    info!(
        fiscal_year,
        fiscal_quarter,
        categories = inserted,
        "ACF-196 generation complete (expenditure amounts are zero — no accounting integration)"
    );

    Ok(Acf196GenerationResult {
        categories_inserted: inserted,
        families_served_basic_assistance: families_served,
    })
}

/// POST /v1/reporting/tanf/acf-196 — Generate ACF-196 quarterly report.
async fn generate_tanf_acf196(
    Extension(claims): Extension<Claims>,
    State(state): State<AppState>,
    Json(req): Json<GenerateTanfQuarterlyRequest>,
) -> Result<(StatusCode, Json<serde_json::Value>), ApiError> {
    claims.require_supervisor_or_above()?;

    let result = crate::reporting::acf196::generate_acf196(
        state.db.inner(),
        req.fiscal_year,
        req.fiscal_quarter,
    )
    .await
    .map_err(|e| ApiError::internal("ACF-196 generation failed", e))?;

    Ok((
        StatusCode::CREATED,
        Json(serde_json::json!({
            "fiscal_year": req.fiscal_year,
            "fiscal_quarter": req.fiscal_quarter,
            "categories_generated": result.categories_inserted,
            "families_served_basic_assistance": result.families_served_basic_assistance,
            "note": "Expenditure amounts are zero. Actual financial data requires state accounting system integration."
        })),
    ))
}

.route("/reporting/tanf/acf-196", post(generate_tanf_acf196).get(list_tanf_acf196))

// SPDX-License-Identifier: AGPL-3.0-or-later

//! CSV export functions for TANF federal reports.

use crate::domain::{TanfAcf196Report, TanfAcf199Snapshot, TanfWprCalculation};

/// Generate ACF-199 case-level CSV.
pub fn generate_acf199_csv(entries: &[TanfAcf199Snapshot]) -> String {
    let mut csv = String::new();
    csv.push_str("report_month,case_id,family_type,household_size,adult_count,child_count,");
    csv.push_str("benefit_amount,sanction_status,case_status,closure_reason,");
    csv.push_str("months_this_state,months_other_states,total_work_hours,core_activity_hours\n");

    for e in entries {
        csv.push_str(&format!(
            "{},{},{},{},{},{},{},{},{},{},{},{},{},{}\n",
            e.report_month,
            e.case_id,
            e.family_type,
            e.household_size,
            e.adult_count,
            e.child_count,
            e.benefit_amount,
            e.sanction_status.as_deref().unwrap_or(""),
            e.case_status,
            e.closure_reason.as_deref().unwrap_or(""),
            e.months_this_state,
            e.months_other_states,
            fmt_dec(e.total_work_hours),
            fmt_dec(e.core_activity_hours),
        ));
    }
    csv
}

/// Generate ACF-196 quarterly financial CSV.
pub fn generate_acf196_csv(entries: &[TanfAcf196Report]) -> String {
    let mut csv = String::new();
    csv.push_str("fiscal_year,fiscal_quarter,category,federal_amount,state_amount,total_amount,families_served\n");

    for e in entries {
        csv.push_str(&format!(
            "{},{},{},{},{},{},{}\n",
            e.fiscal_year,
            e.fiscal_quarter,
            e.category,
            e.federal_amount,
            e.state_amount,
            e.total_amount,
            e.families_served.map(|v| v.to_string()).unwrap_or_default(),
        ));
    }
    csv
}

/// Generate WPR monthly calculation CSV.
pub fn generate_wpr_csv(entries: &[TanfWprCalculation]) -> String {
    let mut csv = String::new();
    csv.push_str("report_month,all_family_numerator,all_family_denominator,all_family_rate,");
    csv.push_str("all_family_target,all_family_meets_target,");
    csv.push_str("two_parent_numerator,two_parent_denominator,two_parent_rate,");
    csv.push_str("two_parent_target,two_parent_meets_target,caseload_reduction_credit\n");

    for e in entries {
        csv.push_str(&format!(
            "{},{},{},{},{},{},{},{},{},{},{},{}\n",
            e.report_month,
            e.all_family_numerator,
            e.all_family_denominator,
            e.all_family_rate,
            e.all_family_target,
            e.all_family_meets_target,
            e.two_parent_numerator,
            e.two_parent_denominator,
            e.two_parent_rate,
            e.two_parent_target,
            e.two_parent_meets_target,
            e.caseload_reduction_credit,
        ));
    }
    csv
}

fn fmt_dec(d: Option<rust_decimal::Decimal>) -> String {
    d.map(|v| v.to_string()).unwrap_or_default()
}

/// List ACF-199 snapshots for a specific month.
pub async fn list_tanf_acf199_by_month(
    pool: &PgPool,
    report_month: NaiveDate,
) -> Result<Vec<crate::domain::TanfAcf199Snapshot>, sqlx::Error> {
    sqlx::query_as(
        "SELECT * FROM tanf_acf199_snapshots WHERE report_month = $1 ORDER BY case_id",
    )
    .bind(report_month)
    .fetch_all(pool)
    .await
}

/// List ACF-196 reports for a fiscal year and quarter.
pub async fn list_tanf_acf196_by_quarter(
    pool: &PgPool,
    fiscal_year: i32,
    fiscal_quarter: i32,
) -> Result<Vec<crate::domain::TanfAcf196Report>, sqlx::Error> {
    sqlx::query_as(
        "SELECT * FROM tanf_acf196_reports WHERE fiscal_year = $1 AND fiscal_quarter = $2 ORDER BY category",
    )
    .bind(fiscal_year)
    .bind(fiscal_quarter)
    .fetch_all(pool)
    .await
}

.route("/reporting/tanf/acf-199/{month}/csv", get(export_tanf_acf199_csv))
.route("/reporting/tanf/acf-196/{year}/{quarter}/csv", get(export_tanf_acf196_csv))
.route("/reporting/tanf/wpr/csv", get(export_tanf_wpr_csv))

/// GET /v1/reporting/tanf/acf-199/{month}/csv
async fn export_tanf_acf199_csv(
    Extension(claims): Extension<Claims>,
    State(state): State<AppState>,
    Path(month): Path<String>,
) -> Result<impl IntoResponse, ApiError> {
    claims.require_supervisor_or_above()?;
    let report_month = NaiveDate::parse_from_str(&format!("{month}-01"), "%Y-%m-%d")
        .map_err(|_| ApiError::BadRequest(format!("invalid month: {month}. Expected YYYY-MM")))?;

    let entries = store::list_tanf_acf199_by_month(state.db.inner(), report_month)
        .await
        .map_err(ApiError::from)?;

    let csv = crate::reporting::tanf_csv::generate_acf199_csv(&entries);
    let filename = format!("acf-199-{month}.csv");

    Ok((
        StatusCode::OK,
        [
            ("content-type".to_owned(), "text/csv".to_owned()),
            ("content-disposition".to_owned(), format!("attachment; filename=\"{filename}\"")),
        ],
        csv,
    ))
}

#[tokio::test]
async fn tanf_acf199_extraction_populates_enriched_fields() {
    if !canopy_test_lib::infrastructure_available().await {
        return;
    }
    let Some(c) = TestClient::authenticated("http://localhost:8011").await else {
        return;
    };
    if !c.is_healthy().await {
        return;
    }

    // Generate ACF-199 snapshot
    let body = serde_json::json!({ "report_month": "2026-05-01" });
    let gen_resp = c.post_json("/v1/reporting/tanf/acf-199", &body).await;
    assert!(
        gen_resp.status == 201 || gen_resp.status == 200,
        "ACF-199 extraction: expected 201/200, got {} — {}",
        gen_resp.status,
        gen_resp.text()
    );

    // List snapshots and verify enriched fields
    let list_resp = c.get("/v1/reporting/tanf/acf-199").await;
    list_resp.assert_status(200);
    let data = list_resp.json::<serde_json::Value>();
    let arr = data.as_array().expect("should be array");

    // May be empty if no TANF determinations exist in devstack seed data
    for snap in arr {
        assert!(snap["family_type"].is_string(), "family_type should be string");
        assert!(snap["household_size"].as_i64().unwrap_or(0) > 0, "household_size > 0");
        assert!(snap["case_status"].is_string(), "case_status should be string");
        assert!(snap["months_this_state"].as_i64().is_some(), "months_this_state should be present");
        // These may be null when work activities endpoint is not available yet
        // but the column itself must exist in the response
        assert!(snap.get("total_work_hours").is_some(), "total_work_hours field must exist");
        assert!(snap.get("core_activity_hours").is_some(), "core_activity_hours field must exist");
    }
}