Plan: SNAP Federal Reporting — FNS-388 and FNS-7176 QC Universe

canopy-reporting
    │
    ├─ GET /v1/renewals/snap/certifications (canopy-renewals)
    │       → active certifications for the snapshot month
    │
    ├─ GET /v1/persons/households/{id} (canopy-persons)
    │       → household composition, income, expenses
    │
    ├─ GET /v1/applications/{id} (canopy-applications)
    │       → application metadata, categorical eligibility basis
    │
    ├─ GET /v1/enrollments/snap/issuances (canopy-enrollment)
    │       → benefit issuance amounts for the month
    │
    └─ GET /v1/snap/abawd/{household_id} (canopy-snap)
            → ABAWD tracking status, work registration, IEVS match status

CREATE TABLE snap_monthly_reports (
    id UUID PRIMARY KEY,
    report_month DATE NOT NULL,
    generated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    generated_by UUID,
    total_households INTEGER NOT NULL DEFAULT 0,
    total_individuals INTEGER NOT NULL DEFAULT 0,
    total_benefits_issued NUMERIC(12,2) NOT NULL DEFAULT 0,
    expedited_households INTEGER NOT NULL DEFAULT 0,
    elderly_disabled_households INTEGER NOT NULL DEFAULT 0,
    initial_certifications INTEGER NOT NULL DEFAULT 0,
    recertifications INTEGER NOT NULL DEFAULT 0,
    average_household_benefit NUMERIC(10,2),
    submission_status TEXT NOT NULL DEFAULT 'draft',
    submitted_at TIMESTAMPTZ,
    fns_confirmation_number TEXT,
    report_data JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_snap_monthly_reports_month ON snap_monthly_reports(report_month);

CREATE TABLE snap_qc_universe (
    id UUID PRIMARY KEY,
    snapshot_date DATE NOT NULL,
    household_id UUID NOT NULL,
    certification_id UUID NOT NULL,
    household_size INTEGER NOT NULL,
    head_of_household_age INTEGER,
    head_of_household_race TEXT,
    head_of_household_ethnicity TEXT,
    head_of_household_citizenship TEXT,
    cert_start_date DATE NOT NULL,
    cert_end_date DATE NOT NULL,
    cert_type TEXT NOT NULL,
    total_gross_income NUMERIC(10,2),
    total_earned_income NUMERIC(10,2),
    total_unearned_income NUMERIC(10,2),
    earned_income_deduction NUMERIC(10,2),
    standard_deduction NUMERIC(10,2),
    dependent_care_deduction NUMERIC(10,2),
    medical_deduction NUMERIC(10,2),
    shelter_deduction NUMERIC(10,2),
    child_support_deduction NUMERIC(10,2),
    total_deductions NUMERIC(10,2),
    net_income NUMERIC(10,2),
    benefit_amount NUMERIC(10,2) NOT NULL,
    categorical_eligibility TEXT,
    expedited_service BOOLEAN NOT NULL DEFAULT false,
    abawd_household BOOLEAN NOT NULL DEFAULT false,
    work_registration_exempt_count INTEGER NOT NULL DEFAULT 0,
    ievs_match_completed BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_snap_qc_universe_snapshot ON snap_qc_universe(snapshot_date);
CREATE INDEX idx_snap_qc_universe_household ON snap_qc_universe(household_id);
CREATE INDEX idx_snap_qc_universe_cert ON snap_qc_universe(certification_id);
CREATE UNIQUE INDEX idx_snap_qc_universe_household_snapshot
    ON snap_qc_universe(household_id, snapshot_date);

{
  "state_code": "GA",
  "report_month": "2026-03",
  "reporting_period_start": "2026-03-01",
  "reporting_period_end": "2026-03-31",
  "households": {
    "total": 142380,
    "initial_certifications": 8421,
    "recertifications": 7309,
    "elderly_disabled": 31450,
    "expedited": 3211,
    "by_household_size": {
      "1": 42100,
      "2": 31500,
      "3": 24300,
      "4": 19800,
      "5": 12400,
      "6_or_more": 12280
    }
  },
  "individuals": {
    "total": 298741
  },
  "applications": {
    "total_received": 12843,
    "approved": 9210,
    "denied": 2891,
    "pending_end_of_month": 742,
    "withdrawn": 312,
    "denial_reasons": {
      "gross_income_exceeded": 1102,
      "net_income_exceeded": 421,
      "asset_limit_exceeded": 89,
      "failure_to_provide_verification": 634,
      "failure_to_complete_interview": 287,
      "drug_felony_disqualification": 12,
      "fleeing_felony_disqualification": 8,
      "abawd_time_limit": 201,
      "other": 137
    }
  },
  "negative_actions": {
    "terminations": 4211,
    "suspensions": 312,
    "benefit_reductions": 1892,
    "abawd_exhaustions": 421
  },
  "benefits": {
    "total_issued_usd": "98432110.00",
    "average_per_household_usd": "691.47",
    "by_income_source": {
      "earned_income_only": 28400,
      "unearned_income_only": 71100,
      "mixed": 29800,
      "no_income": 13080
    }
  }
}

-- Verify unique constraint
INSERT INTO snap_monthly_reports (id, report_month, report_data) VALUES (gen_random_uuid(), '2026-03-01', '{}');
INSERT INTO snap_monthly_reports (id, report_month, report_data) VALUES (gen_random_uuid(), '2026-03-01', '{}');
-- Second insert should fail with unique constraint violation.

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/store/models.rs

use chrono::{DateTime, NaiveDate, Utc};
use rust_decimal::Decimal;
use serde::{Deserialize, Serialize};
use uuid::Uuid;

#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct SnapMonthlyReport {
    pub id: Uuid,
    pub report_month: NaiveDate,
    pub generated_at: DateTime<Utc>,
    pub generated_by: Option<Uuid>,
    pub total_households: i32,
    pub total_individuals: i32,
    pub total_benefits_issued: Decimal,
    pub expedited_households: i32,
    pub elderly_disabled_households: i32,
    pub initial_certifications: i32,
    pub recertifications: i32,
    pub average_household_benefit: Option<Decimal>,
    pub submission_status: String,
    pub submitted_at: Option<DateTime<Utc>>,
    pub fns_confirmation_number: Option<String>,
    pub report_data: serde_json::Value,
    pub created_at: DateTime<Utc>,
}

#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct SnapQcUniverseRow {
    pub id: Uuid,
    pub snapshot_date: NaiveDate,
    pub household_id: Uuid,
    pub certification_id: Uuid,
    pub household_size: i32,
    pub head_of_household_age: Option<i32>,
    pub head_of_household_race: Option<String>,
    pub head_of_household_ethnicity: Option<String>,
    pub head_of_household_citizenship: Option<String>,
    pub cert_start_date: NaiveDate,
    pub cert_end_date: NaiveDate,
    pub cert_type: String,
    pub total_gross_income: Option<Decimal>,
    pub total_earned_income: Option<Decimal>,
    pub total_unearned_income: Option<Decimal>,
    pub earned_income_deduction: Option<Decimal>,
    pub standard_deduction: Option<Decimal>,
    pub dependent_care_deduction: Option<Decimal>,
    pub medical_deduction: Option<Decimal>,
    pub shelter_deduction: Option<Decimal>,
    pub child_support_deduction: Option<Decimal>,
    pub total_deductions: Option<Decimal>,
    pub net_income: Option<Decimal>,
    pub benefit_amount: Decimal,
    pub categorical_eligibility: Option<String>,
    pub expedited_service: bool,
    pub abawd_household: bool,
    pub work_registration_exempt_count: i32,
    pub ievs_match_completed: bool,
    pub created_at: DateTime<Utc>,
}

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/store/mod.rs

pub mod models;
use models::{SnapMonthlyReport, SnapQcUniverseRow};
use sqlx::PgPool;
use uuid::Uuid;
use chrono::NaiveDate;

pub struct PageRequest {
    pub offset: i64,
    pub limit: i64,
}

pub async fn create_monthly_report(
    pool: &PgPool,
    report: &SnapMonthlyReport,
) -> Result<SnapMonthlyReport, sqlx::Error> {
    sqlx::query_as::<_, SnapMonthlyReport>(
        r#"INSERT INTO snap_monthly_reports
           (id, report_month, generated_by, total_households, total_individuals,
            total_benefits_issued, expedited_households, elderly_disabled_households,
            initial_certifications, recertifications, average_household_benefit,
            submission_status, report_data)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
           RETURNING *"#,
    )
    .bind(report.id)
    .bind(report.report_month)
    .bind(report.generated_by)
    .bind(report.total_households)
    .bind(report.total_individuals)
    .bind(report.total_benefits_issued)
    .bind(report.expedited_households)
    .bind(report.elderly_disabled_households)
    .bind(report.initial_certifications)
    .bind(report.recertifications)
    .bind(report.average_household_benefit)
    .bind(&report.submission_status)
    .bind(&report.report_data)
    .fetch_one(pool)
    .await
}

pub async fn get_monthly_report(
    pool: &PgPool,
    id: Uuid,
) -> Result<Option<SnapMonthlyReport>, sqlx::Error> {
    sqlx::query_as::<_, SnapMonthlyReport>(
        "SELECT * FROM snap_monthly_reports WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await
}

pub async fn get_monthly_report_by_month(
    pool: &PgPool,
    report_month: NaiveDate,
) -> Result<Option<SnapMonthlyReport>, sqlx::Error> {
    sqlx::query_as::<_, SnapMonthlyReport>(
        "SELECT * FROM snap_monthly_reports WHERE report_month = $1",
    )
    .bind(report_month)
    .fetch_optional(pool)
    .await
}

pub async fn insert_qc_universe_row(
    pool: &PgPool,
    row: &SnapQcUniverseRow,
) -> Result<SnapQcUniverseRow, sqlx::Error> {
    sqlx::query_as::<_, SnapQcUniverseRow>(
        r#"INSERT INTO snap_qc_universe
           (id, snapshot_date, household_id, certification_id,
            household_size, head_of_household_age, head_of_household_race,
            head_of_household_ethnicity, head_of_household_citizenship,
            cert_start_date, cert_end_date, cert_type,
            total_gross_income, total_earned_income, total_unearned_income,
            earned_income_deduction, standard_deduction, dependent_care_deduction,
            medical_deduction, shelter_deduction, child_support_deduction,
            total_deductions, net_income, benefit_amount,
            categorical_eligibility, expedited_service,
            abawd_household, work_registration_exempt_count, ievs_match_completed)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,
                   $16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29)
           ON CONFLICT (household_id, snapshot_date) DO UPDATE
             SET benefit_amount = EXCLUDED.benefit_amount,
                 total_gross_income = EXCLUDED.total_gross_income,
                 net_income = EXCLUDED.net_income,
                 ievs_match_completed = EXCLUDED.ievs_match_completed
           RETURNING *"#,
    )
    // ... bind all 29 parameters in order
    .fetch_one(pool)
    .await
}

pub async fn list_qc_universe(
    pool: &PgPool,
    snapshot_date: NaiveDate,
    page: &PageRequest,
) -> Result<Vec<SnapQcUniverseRow>, sqlx::Error> {
    sqlx::query_as::<_, SnapQcUniverseRow>(
        "SELECT * FROM snap_qc_universe WHERE snapshot_date = $1 ORDER BY household_id LIMIT $2 OFFSET $3",
    )
    .bind(snapshot_date)
    .bind(page.limit)
    .bind(page.offset)
    .fetch_all(pool)
    .await
}

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/clients/mod.rs

pub mod applications_client;
pub mod enrollment_client;
pub mod persons_client;
pub mod renewals_client;
pub mod snap_client;

use reqwest::Client;

pub struct ServiceClients {
    pub renewals: renewals_client::RenewalsClient,
    pub persons: persons_client::PersonsClient,
    pub applications: applications_client::ApplicationsClient,
    pub enrollment: enrollment_client::EnrollmentClient,
    pub snap: snap_client::SnapClient,
}

impl ServiceClients {
    pub fn from_env() -> Self {
        let client = Client::builder()
            .timeout(std::time::Duration::from_secs(30))
            .build()
            .expect("failed to build HTTP client");
        // Ports match docker-compose.yml. Env var names follow CANOPY_REPORTING__ prefix convention.
        Self {
            renewals: renewals_client::RenewalsClient::new(
                client.clone(),
                std::env::var("CANOPY_REPORTING__RENEWALS_URL").unwrap_or_else(|_| "http://localhost:8007".into()),
            ),
            persons: persons_client::PersonsClient::new(
                client.clone(),
                std::env::var("CANOPY_REPORTING__PERSONS_URL").unwrap_or_else(|_| "http://localhost:8002".into()),
            ),
            applications: applications_client::ApplicationsClient::new(
                client.clone(),
                std::env::var("CANOPY_REPORTING__APPLICATIONS_URL").unwrap_or_else(|_| "http://localhost:8003".into()),
            ),
            enrollment: enrollment_client::EnrollmentClient::new(
                client.clone(),
                std::env::var("CANOPY_REPORTING__ENROLLMENT_URL").unwrap_or_else(|_| "http://localhost:8006".into()),
            ),
            snap: snap_client::SnapClient::new(
                client,
                std::env::var("CANOPY_REPORTING__SNAP_URL").unwrap_or_else(|_| "http://localhost:8013".into()),
            ),
        }
    }
}

let reporting_clients = std::sync::Arc::new(clients::ServiceClients::from_env());

// ... router setup ...

let router = router
    .layer(axum::Extension(reporting_clients))
    .layer(axum::Extension(boot.mq_health));

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/reports/fns388.rs

use chrono::NaiveDate;
use rust_decimal::Decimal;
use uuid::Uuid;

use crate::clients::ServiceClients;
use crate::errors::ApiError;
use crate::store::models::SnapMonthlyReport;

pub struct Fns388Counts {
    pub total_households: i32,
    pub total_individuals: i32,
    pub total_benefits_issued: Decimal,
    pub expedited_households: i32,
    pub elderly_disabled_households: i32,
    pub initial_certifications: i32,
    pub recertifications: i32,
}

pub async fn generate_fns388(
    clients: &ServiceClients,
    report_month: NaiveDate,
    generated_by: Option<Uuid>,
) -> Result<SnapMonthlyReport, ApiError> {
    // 1. Fetch all active certifications for the month from canopy-renewals.
    let certifications = clients.renewals
        .get_active_certifications_for_month(report_month)
        .await?;

    // 2. Fetch issuance totals for the month from canopy-enrollment.
    let issuances = clients.enrollment
        .get_snap_issuances_for_month(report_month)
        .await?;

    // Build a lookup map: household_id → issuance amount.
    let issuance_map: std::collections::HashMap<Uuid, Decimal> = issuances
        .into_iter()
        .map(|i| (i.household_id, i.benefit_amount))
        .collect();

    // 3. Tally aggregate counts.
    let mut counts = Fns388Counts {
        total_households: 0,
        total_individuals: 0,
        total_benefits_issued: Decimal::ZERO,
        expedited_households: 0,
        elderly_disabled_households: 0,
        initial_certifications: 0,
        recertifications: 0,
    };

    for cert in &certifications {
        counts.total_households += 1;

        let household = clients.persons.get_household(cert.household_id).await?;
        counts.total_individuals += household.members.len() as i32;

        if let Some(&amount) = issuance_map.get(&cert.household_id) {
            counts.total_benefits_issued += amount;
        }

        if cert.certification_type == "elderly_disabled" {
            counts.elderly_disabled_households += 1;
        }
        // Expedited flag and initial/recertification come from application metadata.
        let app = clients.applications.get_application(cert.application_id).await?;
        if app.expedited_eligible.unwrap_or(false) {
            counts.expedited_households += 1;
        }
        // Infer initial vs recertification: if cert_start_date matches the original
        // application received_at month, it's initial; otherwise recertification.
        // This is an approximation — a dedicated field would be more reliable.
        if cert.certification_start_date.year() == app.received_at.year()
            && cert.certification_start_date.month() == app.received_at.month() {
            counts.initial_certifications += 1;
        } else {
            counts.recertifications += 1;
        }
    }

    let avg = if counts.total_households > 0 {
        Some(counts.total_benefits_issued / Decimal::from(counts.total_households))
    } else {
        None
    };

    let report_data = build_report_data_json(report_month, &counts, avg);

    Ok(SnapMonthlyReport {
        id: Uuid::now_v7(),
        report_month,
        generated_at: chrono::Utc::now(),
        generated_by,
        total_households: counts.total_households,
        total_individuals: counts.total_individuals,
        total_benefits_issued: counts.total_benefits_issued,
        expedited_households: counts.expedited_households,
        elderly_disabled_households: counts.elderly_disabled_households,
        initial_certifications: counts.initial_certifications,
        recertifications: counts.recertifications,
        average_household_benefit: avg,
        submission_status: "draft".to_string(),
        submitted_at: None,
        fns_confirmation_number: None,
        report_data,
        created_at: chrono::Utc::now(),
    })
}

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/export/fns388_export.rs

use axum::response::Response;
use axum::http::{header, HeaderValue, StatusCode};
use axum::body::Body;
use crate::store::models::SnapMonthlyReport;

pub fn export_json(report: &SnapMonthlyReport) -> Response {
    let body = serde_json::to_string_pretty(&report.report_data)
        .unwrap_or_else(|_| "{}".to_string());
    Response::builder()
        .status(StatusCode::OK)
        .header(header::CONTENT_TYPE, "application/json")
        .body(Body::from(body))
        .expect("failed to build response")
}

pub fn export_csv(report: &SnapMonthlyReport) -> Response {
    let filename = format!(
        "fns388-{}.csv",
        report.report_month.format("%Y-%m")
    );
    let csv = build_fns388_csv(report);
    Response::builder()
        .status(StatusCode::OK)
        .header(header::CONTENT_TYPE, "text/csv; charset=utf-8")
        .header(
            header::CONTENT_DISPOSITION,
            HeaderValue::from_str(&format!("attachment; filename=\"{filename}\""))
                .expect("valid header value"),
        )
        .body(Body::from(csv))
        .expect("failed to build response")
}

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/reports/qc_universe.rs

pub async fn assemble_qc_snapshot(
    pool: PgPool,
    clients: Arc<ServiceClients>,
    snapshot_date: NaiveDate,
) -> Result<u32, ApiError> {
    // Get all active certifications as of snapshot_date.
    let certifications = clients.renewals
        .get_active_certifications_for_month(snapshot_date)
        .await?;

    let mut count = 0u32;

    for cert in certifications {
        match assemble_qc_row(&pool, &clients, snapshot_date, &cert).await {
            Ok(()) => count += 1,
            Err(e) => {
                // Log and continue — a single missing household should not abort the whole snapshot.
                tracing::warn!(
                    household_id = %cert.household_id,
                    error = %e,
                    "failed to assemble QC row; skipping"
                );
            }
        }
    }

    tracing::info!(snapshot_date = %snapshot_date, rows_assembled = count, "QC universe snapshot complete");
    Ok(count)
}

async fn assemble_qc_row(
    pool: &PgPool,
    clients: &ServiceClients,
    snapshot_date: NaiveDate,
    cert: &CertificationSummary,
) -> Result<(), ApiError> {
    let household = clients.persons.get_household(cert.household_id).await?;
    let app = clients.applications.get_application(cert.application_id).await?;
    let issuance = clients.enrollment
        .get_snap_issuance(cert.household_id, snapshot_date)
        .await?;
    let abawd = clients.snap.get_abawd_status(cert.household_id).await?;

    // Compute deductions from expense records returned by canopy-persons.
    let deductions = compute_deductions(&household);
    let gross = sum_gross_income(&household);
    let earned = sum_earned_income(&household);
    let unearned = gross - earned;
    let net = gross - deductions.total;

    let hoh = household.members.iter().find(|m| m.is_head_of_household);

    let row = SnapQcUniverseRow {
        id: Uuid::now_v7(),
        snapshot_date,
        household_id: cert.household_id,
        certification_id: cert.certification_id,
        household_size: household.members.len() as i32,
        head_of_household_age: hoh.map(|m| m.age),
        head_of_household_race: hoh.and_then(|m| m.race.clone()),
        head_of_household_ethnicity: hoh.and_then(|m| m.ethnicity.clone()),
        head_of_household_citizenship: hoh.and_then(|m| m.citizenship_status.clone()),
        cert_start_date: cert.cert_start_date,
        cert_end_date: cert.cert_end_date,
        cert_type: cert.cert_type.clone(),
        total_gross_income: Some(gross),
        total_earned_income: Some(earned),
        total_unearned_income: Some(unearned),
        earned_income_deduction: Some(deductions.earned_income),
        standard_deduction: Some(deductions.standard),
        dependent_care_deduction: Some(deductions.dependent_care),
        medical_deduction: Some(deductions.medical),
        shelter_deduction: Some(deductions.shelter),
        child_support_deduction: Some(deductions.child_support),
        total_deductions: Some(deductions.total),
        net_income: Some(net),
        benefit_amount: issuance.benefit_amount,
        categorical_eligibility: app.categorical_eligibility_basis.clone(),
        expedited_service: app.expedited_service,
        abawd_household: abawd.is_abawd_household,
        work_registration_exempt_count: abawd.work_registration_exempt_count,
        ievs_match_completed: abawd.ievs_match_completed,
        created_at: chrono::Utc::now(),
    };

    store::insert_qc_universe_row(pool, &row).await
        .map_err(|e| ApiError::Internal(format!("store error: {e}")))?;

    Ok(())
}

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/export/fns7176_export.rs

use crate::store::models::SnapQcUniverseRow;
use axum::response::Response;
use axum::http::{header, HeaderValue, StatusCode};
use axum::body::Body;

pub fn export_qc_universe_csv(
    rows: Vec<SnapQcUniverseRow>,
    snapshot_date: chrono::NaiveDate,
) -> Response {
    let mut wtr = csv::Writer::from_writer(vec![]);

    // Write header row — must match FNS-7176 column names exactly.
    wtr.write_record(&[
        "CASE_ID", "CERT_ID", "SNAPSHOT_DATE", "HH_SIZE",
        "CERT_START", "CERT_END", "CERT_TYPE", "EXPEDITED",
        "GROSS_INCOME", "EARNED_INCOME", "UNEARNED_INCOME",
        "EI_DEDUCTION", "STD_DEDUCTION", "DEP_CARE_DED",
        "MED_DED", "SHELTER_DED", "CS_DED", "TOTAL_DEDS",
        "NET_INCOME", "BENEFIT_AMT", "CAT_ELIG",
        "ABAWD_HH", "WR_EXEMPT_CT", "IEVS_MATCH",
        "HOH_AGE", "HOH_RACE", "HOH_ETHNICITY", "HOH_CITIZENSHIP",
    ]).expect("csv write error");

    for row in &rows {
        wtr.write_record(&[
            row.household_id.to_string(),
            row.certification_id.to_string(),
            row.snapshot_date.to_string(),
            row.household_size.to_string(),
            row.cert_start_date.to_string(),
            row.cert_end_date.to_string(),
            row.cert_type.clone(),
            if row.expedited_service { "Y".into() } else { "N".into() },
            decimal_or_empty(row.total_gross_income),
            decimal_or_empty(row.total_earned_income),
            decimal_or_empty(row.total_unearned_income),
            decimal_or_empty(row.earned_income_deduction),
            decimal_or_empty(row.standard_deduction),
            decimal_or_empty(row.dependent_care_deduction),
            decimal_or_empty(row.medical_deduction),
            decimal_or_empty(row.shelter_deduction),
            decimal_or_empty(row.child_support_deduction),
            decimal_or_empty(row.total_deductions),
            decimal_or_empty(row.net_income),
            row.benefit_amount.to_string(),
            row.categorical_eligibility.clone().unwrap_or_default(),
            if row.abawd_household { "Y".into() } else { "N".into() },
            row.work_registration_exempt_count.to_string(),
            if row.ievs_match_completed { "Y".into() } else { "N".into() },
            row.head_of_household_age.map(|a| a.to_string()).unwrap_or_default(),
            row.head_of_household_race.clone().unwrap_or_default(),
            row.head_of_household_ethnicity.clone().unwrap_or_default(),
            row.head_of_household_citizenship.clone().unwrap_or_default(),
        ]).expect("csv write error");
    }

    let csv_bytes = wtr.into_inner().expect("csv flush error");
    let filename = format!("fns7176-qc-universe-{snapshot_date}.csv");

    Response::builder()
        .status(StatusCode::OK)
        .header(header::CONTENT_TYPE, "text/csv; charset=utf-8")
        .header(
            header::CONTENT_DISPOSITION,
            HeaderValue::from_str(&format!("attachment; filename=\"{filename}\""))
                .expect("valid header value"),
        )
        .body(Body::from(csv_bytes))
        .expect("failed to build response")
}

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

// SPDX-License-Identifier: AGPL-3.0-or-later
// services/canopy-reporting/src/handlers/snap_reporting.rs

/// POST /reporting/snap/fns-388?month=YYYY-MM
pub async fn generate_fns388(
    State(state): State<AppState>,
    Query(params): Query<MonthQuery>,
) -> Result<(StatusCode, Json<SnapMonthlyReport>), ApiError> {
    let report_month = parse_report_month(&params.month)?;

    // Idempotency: if a submitted/accepted report already exists, return 409.
    if let Some(existing) = store::get_monthly_report_by_month(&state.pool, report_month).await
        .map_err(|e| ApiError::Internal(format!("{e}")))?
    {
        if existing.submission_status != "draft" {
            return Err(ApiError::Conflict(
                "a submitted or accepted report already exists for this month".into()
            ));
        }
        // Draft reports may be regenerated — delete the old one first.
        store::delete_monthly_report(&state.pool, existing.id).await
            .map_err(|e| ApiError::Internal(format!("{e}")))?;
    }

    let report = fns388::generate_fns388(&state.clients, report_month, None).await?;
    let saved = store::create_monthly_report(&state.pool, &report).await
        .map_err(|e| ApiError::Internal(format!("{e}")))?;

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

/// POST /reporting/snap/qc-universe
pub async fn trigger_qc_snapshot(
    State(state): State<AppState>,
    Json(body): Json<QcSnapshotRequest>,
) -> Result<(StatusCode, Json<QcSnapshotAccepted>), ApiError> {
    let snapshot_date = body.snapshot_date;

    // Spawn the assembly as a background task; return 202 Accepted.
    let pool = state.pool.clone();
    let clients = state.clients.clone();
    tokio::spawn(async move {
        if let Err(e) = qc_universe::assemble_qc_snapshot(pool, clients, snapshot_date).await {
            tracing::error!(error = %e, snapshot_date = %snapshot_date, "QC snapshot failed");
        }
    });

    Ok((StatusCode::ACCEPTED, Json(QcSnapshotAccepted {
        snapshot_date,
        message: "QC universe snapshot queued".to_string(),
    })))
}

pub fn router(state: AppState) -> Router {
    Router::new()
        .route("/reporting/snap/fns-388", post(snap_reporting::generate_fns_388))
        .route("/reporting/snap/fns-388", get(snap_reporting::list_reports))
        .route("/reporting/snap/fns-388/{month}", get(snap_reporting::get_report))
        .route("/reporting/snap/qc-universe", post(snap_reporting::generate_qc_snapshot))
        .route("/reporting/snap/qc-universe/{date}", get(snap_reporting::get_qc_universe))
        .route("/reporting/snap/qc-universe/{date}/csv", get(snap_reporting::export_qc_csv))
        .route("/healthz", get(health::healthz))
        .route("/metrics", get(metrics::metrics))
        .with_state(state)
}

// fns388.rs

#[tokio::test]
async fn test_generate_fns388_aggregates_correctly() {
    // Mock canopy-renewals: return 3 certifications (2 standard, 1 elderly_disabled)
    // Mock canopy-enrollment: return benefit amounts for each household
    // Mock canopy-persons: return household sizes
    // Mock canopy-applications: 2 initial, 1 recertification; 1 expedited
    // Assert: generated report has total_households=3, elderly_disabled=1, initial=2, recerts=1, expedited=1
}

#[tokio::test]
async fn test_generate_fns388_duplicate_non_draft_returns_409() {
    // Insert a report with submission_status='submitted' for the same month.
    // Call generate_fns388 for the same month.
    // Assert: returns 409 Conflict.
}

#[tokio::test]
async fn test_generate_fns388_duplicate_draft_regenerates() {
    // Insert a draft report.
    // Call generate again.
    // Assert: old draft deleted, new report created.
}

#[tokio::test]
async fn test_fns388_csv_export_format() {
    // Generate a report; call export with format=csv.
    // Assert: Content-Type is text/csv, Content-Disposition has filename.
    // Assert: CSV is parseable; header row matches expected columns.
}

// qc_universe.rs

#[tokio::test]
async fn test_qc_snapshot_assembles_all_active_certifications() {
    // Mock all 5 upstream services with 10 households.
    // Trigger snapshot.
    // Assert: 10 rows in snap_qc_universe.
}

#[tokio::test]
async fn test_qc_snapshot_skips_and_logs_on_upstream_404() {
    // Mock canopy-persons to return 404 for one household.
    // Trigger snapshot.
    // Assert: 9 rows assembled (not 10); no panic.
}

#[tokio::test]
async fn test_fns7176_csv_column_order_and_headers() {
    // Insert a known snap_qc_universe row.
    // Call export endpoint.
    // Parse CSV; assert header columns match FNS-7176 spec order.
    // Assert benefit_amount value matches inserted row.
}

#[tokio::test]
async fn test_qc_aggregate_matches_fns388_total() {
    // Generate FNS-388 for March 2026.
    // Generate QC universe for a date in March 2026.
    // Assert: COUNT(*) in snap_qc_universe for snapshot_date = total_households in snap_monthly_reports.
}