-- Federal Award Concentration 2026 (federal-award-concentration/v2)
-- ---------------------------------------------------------------------------
-- Reproducible aggregate generator over live production USAspending tables:
--   public.usaspending_award_transactions - full FY2026 award transactions
--   public.procurement_awards             - contract-focused set_aside_type slice
--
-- The public page renders from the committed TypeScript constants produced by
-- this SQL; it does not execute SQL at request time.
--
-- Method:
--   * Totals use net federal_action_obligation, including de-obligations.
--   * Concentration shares, HHI, and Gini use positive net recipient obligations.
--   * HHI = 10,000 * sum(squared recipient shares).
--   * Gini = Lorenz-style inequality over positive net recipient obligations.
--   * Top 1% = ceil(positive recipient count * 0.01).
--   * Set-aside status uses procurement_awards.set_aside_type because the full
--     transaction table does not expose that field.

WITH current_awards AS (
  SELECT
    fiscal_year,
    award_category,
    recipient_uei,
    recipient_name,
    awarding_agency_name,
    naics_code,
    federal_action_obligation::numeric AS obligation,
    action_date,
    snapshot_date,
    source_release_date
  FROM public.usaspending_award_transactions
  WHERE valid_to IS NULL
    AND federal_action_obligation IS NOT NULL
),
snapshot AS (
  SELECT
    count(*)::bigint AS transactions,
    count(DISTINCT recipient_uei)::bigint AS distinct_recipients,
    sum(obligation)::numeric(18,2) AS total_obligation,
    min(action_date) AS min_action_date,
    max(action_date) AS max_action_date,
    max(snapshot_date) AS as_of,
    max(source_release_date) AS source_release_date
  FROM current_awards
),
by_year AS (
  SELECT
    fiscal_year,
    count(*)::bigint AS transactions,
    count(DISTINCT recipient_uei)::bigint AS distinct_recipients,
    sum(obligation)::numeric(18,2) AS obligation
  FROM current_awards
  GROUP BY fiscal_year
),
recipient_totals AS (
  SELECT
    recipient_uei,
    max(recipient_name) AS recipient_name,
    count(*)::bigint AS transactions,
    sum(obligation)::numeric(18,2) AS obligation
  FROM current_awards
  WHERE recipient_uei IS NOT NULL
  GROUP BY recipient_uei
),
recipient_positive AS (
  SELECT *
  FROM recipient_totals
  WHERE obligation > 0
),
recipient_ranked AS (
  SELECT
    row_number() OVER (ORDER BY obligation DESC, recipient_name, recipient_uei) AS rank,
    recipient_uei,
    recipient_name,
    transactions,
    obligation,
    sum(obligation) OVER () AS total_positive_obligation,
    count(*) OVER () AS positive_recipients
  FROM recipient_positive
),
recipient_meta AS (
  SELECT
    max(positive_recipients)::bigint AS positive_recipients,
    max(total_positive_obligation)::numeric(18,2) AS total_positive_obligation,
    greatest(1, ceil(max(positive_recipients)::numeric * 0.01))::bigint AS top1pct_count
  FROM recipient_ranked
),
recipient_gini AS (
  SELECT
    round(
      (
        (2 * sum(rn_asc * obligation))
        / nullif(count(*)::numeric * sum(obligation), 0)
      ) - ((count(*)::numeric + 1) / nullif(count(*)::numeric, 0)),
      4
    ) AS gini
  FROM (
    SELECT obligation, row_number() OVER (ORDER BY obligation ASC, recipient_uei) AS rn_asc
    FROM recipient_positive
  ) g
),
overall_concentration AS (
  SELECT
    'all_recipients'::text AS segment,
    (SELECT positive_recipients FROM recipient_meta) AS recipients,
    (SELECT total_positive_obligation FROM recipient_meta) AS obligation,
    round(100 * sum(obligation) FILTER (WHERE rank <= 10) / nullif(max(total_positive_obligation), 0), 2) AS top10_share_pct,
    round(100 * sum(obligation) FILTER (WHERE rank <= 100) / nullif(max(total_positive_obligation), 0), 2) AS top100_share_pct,
    round(100 * sum(obligation) FILTER (WHERE rank <= (SELECT top1pct_count FROM recipient_meta)) / nullif(max(total_positive_obligation), 0), 2) AS top1pct_share_pct,
    (SELECT top1pct_count FROM recipient_meta) AS top1pct_recipient_count,
    round(10000 * sum(power(obligation / nullif(total_positive_obligation, 0), 2)), 2) AS hhi,
    (SELECT gini FROM recipient_gini) AS gini
  FROM recipient_ranked
),
top_recipients AS (
  SELECT
    rank,
    recipient_name,
    recipient_uei,
    transactions,
    obligation,
    round(100 * obligation / nullif(total_positive_obligation, 0), 2) AS share_pct
  FROM recipient_ranked
  WHERE rank <= 10
),
agency_recipient AS (
  SELECT
    coalesce(nullif(trim(awarding_agency_name), ''), '(not reported)') AS agency,
    recipient_uei,
    count(*)::bigint AS transactions,
    sum(obligation)::numeric(18,2) AS obligation
  FROM current_awards
  WHERE recipient_uei IS NOT NULL
  GROUP BY 1, 2
  HAVING sum(obligation) > 0
),
agency_ranked AS (
  SELECT
    agency,
    recipient_uei,
    transactions,
    obligation,
    row_number() OVER (PARTITION BY agency ORDER BY obligation DESC, recipient_uei) AS rank,
    sum(obligation) OVER (PARTITION BY agency) AS agency_obligation,
    count(*) OVER (PARTITION BY agency) AS recipients
  FROM agency_recipient
),
agency_concentration AS (
  SELECT
    agency,
    sum(transactions)::bigint AS transactions,
    max(recipients)::bigint AS recipients,
    max(agency_obligation)::numeric(18,2) AS obligation,
    round(100 * sum(obligation) FILTER (WHERE rank <= 10) / nullif(max(agency_obligation), 0), 2) AS top10_share_pct,
    round(100 * sum(obligation) FILTER (WHERE rank <= 100) / nullif(max(agency_obligation), 0), 2) AS top100_share_pct,
    round(10000 * sum(power(obligation / nullif(agency_obligation, 0), 2)), 2) AS hhi
  FROM agency_ranked
  GROUP BY agency
),
naics_recipient AS (
  SELECT
    naics_code,
    recipient_uei,
    count(*)::bigint AS transactions,
    sum(obligation)::numeric(18,2) AS obligation
  FROM current_awards
  WHERE recipient_uei IS NOT NULL
    AND naics_code ~ '^[0-9]{6}$'
  GROUP BY 1, 2
  HAVING sum(obligation) > 0
),
naics_ranked AS (
  SELECT
    naics_code,
    recipient_uei,
    transactions,
    obligation,
    row_number() OVER (PARTITION BY naics_code ORDER BY obligation DESC, recipient_uei) AS rank,
    sum(obligation) OVER (PARTITION BY naics_code) AS naics_obligation,
    count(*) OVER (PARTITION BY naics_code) AS recipients
  FROM naics_recipient
),
naics_concentration AS (
  SELECT
    naics_code,
    sum(transactions)::bigint AS transactions,
    max(recipients)::bigint AS recipients,
    max(naics_obligation)::numeric(18,2) AS obligation,
    round(100 * sum(obligation) FILTER (WHERE rank <= 10) / nullif(max(naics_obligation), 0), 2) AS top10_share_pct,
    round(100 * sum(obligation) FILTER (WHERE rank <= 100) / nullif(max(naics_obligation), 0), 2) AS top100_share_pct,
    round(10000 * sum(power(obligation / nullif(naics_obligation, 0), 2)), 2) AS hhi
  FROM naics_ranked
  GROUP BY naics_code
),
procurement_current AS (
  SELECT
    recipient_uei,
    recipient_name,
    coalesce(nullif(trim(set_aside_type), ''), '(not reported)') AS set_aside_type,
    obligation_amount::numeric AS obligation
  FROM public.procurement_awards
  WHERE valid_to IS NULL
    AND obligation_amount IS NOT NULL
),
set_aside_labeled AS (
  SELECT
    *,
    CASE
      WHEN set_aside_type = '(not reported)' OR set_aside_type ILIKE 'NO SET ASIDE USED%'
        THEN 'not_set_aside_or_unspecified'
      ELSE 'set_aside'
    END AS set_aside_status
  FROM procurement_current
),
sba_confirmed AS (
  SELECT DISTINCT uei
  FROM public.procurement_sba_certifications
  WHERE valid_to IS NULL
    AND match_status = 'confirmed_uei'
    AND uei IS NOT NULL
),
set_aside_total AS (
  SELECT sum(obligation) AS total_obligation
  FROM set_aside_labeled
),
set_aside AS (
  SELECT
    s.set_aside_status,
    count(*)::bigint AS award_records,
    count(DISTINCT s.recipient_uei)::bigint AS recipients,
    sum(s.obligation)::numeric(18,2) AS obligation,
    round(100 * sum(s.obligation) / nullif((SELECT total_obligation FROM set_aside_total), 0), 2) AS share_pct,
    count(DISTINCT s.recipient_uei) FILTER (WHERE c.uei IS NOT NULL)::bigint AS confirmed_sba_certified_recipients,
    coalesce(sum(s.obligation) FILTER (WHERE c.uei IS NOT NULL), 0)::numeric(18,2) AS confirmed_sba_certified_obligation
  FROM set_aside_labeled s
  LEFT JOIN sba_confirmed c ON c.uei = s.recipient_uei
  GROUP BY s.set_aside_status
),
set_aside_types AS (
  SELECT
    set_aside_type,
    count(*)::bigint AS award_records,
    count(DISTINCT recipient_uei)::bigint AS recipients,
    sum(obligation)::numeric(18,2) AS obligation
  FROM set_aside_labeled
  GROUP BY set_aside_type
  ORDER BY obligation DESC NULLS LAST
  LIMIT 12
)
SELECT jsonb_build_object(
  'snapshot', (SELECT to_jsonb(snapshot) FROM snapshot),
  'by_year', (SELECT jsonb_agg(to_jsonb(by_year) ORDER BY fiscal_year) FROM by_year),
  'overall_concentration', (SELECT to_jsonb(overall_concentration) FROM overall_concentration),
  'top_recipients', (SELECT jsonb_agg(to_jsonb(top_recipients) ORDER BY rank) FROM top_recipients),
  'by_agency', (SELECT jsonb_agg(to_jsonb(t) ORDER BY obligation DESC) FROM (SELECT * FROM agency_concentration ORDER BY obligation DESC LIMIT 12) t),
  'by_naics', (SELECT jsonb_agg(to_jsonb(t) ORDER BY obligation DESC) FROM (SELECT * FROM naics_concentration ORDER BY obligation DESC LIMIT 12) t),
  'by_set_aside', (SELECT jsonb_agg(to_jsonb(set_aside) ORDER BY set_aside_status) FROM set_aside),
  'set_aside_types', (SELECT jsonb_agg(to_jsonb(set_aside_types)) FROM set_aside_types)
) AS snapshot_json;
