-- top-contractors.sql — reproducible SQL for the Top Federal Contractors ranking
-- (/gov/top-federal-contractors). Run against the Fonteum `fonteum-platform`
-- Supabase project; every figure on the page is re-derivable from these queries.
--
-- The set is the ingested USASpending.gov prime-award sample held in
-- public.procurement_awards (current rows only: valid_to IS NULL). It is a
-- bounded slice of the federal universe, not a completeness claim; the
-- authoritative full ranking lives at usaspending.gov. Aggregate counts and
-- obligated-dollar sums only — no derived score, no determination about any party.

-- 1. Totals over the ingested set --------------------------------------------
--    expected (queried 2026-06-20):
--      award_actions=16779  distinct_recipients=443
--      obligated=616530005960  earliest=1978-09-30  latest=2026-06-02
SELECT
  count(*)                              AS award_actions,
  count(DISTINCT recipient_uei)         AS distinct_recipients,
  round(sum(obligation_amount))         AS obligated,
  min(action_date)                      AS earliest_action_date,
  max(action_date)                      AS latest_action_date
FROM public.procurement_awards
WHERE valid_to IS NULL;

-- 2. Top recipients by total obligated dollars (the ranking) -----------------
--    share_pct = obligated / (total obligated) * 100, rounded to 2 decimals.
--    expected leader: LOCKHEED MARTIN CORP (UEI FYHNA5WC8XD7) = 65,637,073,151.
WITH tot AS (
  SELECT sum(obligation_amount) AS obl
  FROM public.procurement_awards WHERE valid_to IS NULL
)
SELECT
  row_number() OVER (ORDER BY sum(a.obligation_amount) DESC)         AS rank,
  a.recipient_name                                                   AS name,
  a.recipient_uei                                                    AS uei,
  count(*)                                                           AS actions,
  round(sum(a.obligation_amount))                                    AS obligated,
  round(sum(a.obligation_amount) / (SELECT obl FROM tot) * 100, 2)   AS share_pct
FROM public.procurement_awards a
WHERE a.valid_to IS NULL AND a.recipient_uei IS NOT NULL
GROUP BY a.recipient_name, a.recipient_uei
ORDER BY obligated DESC
LIMIT 20;

-- 3. Top awarding agencies by total obligated dollars ------------------------
--    expected leader: Department of Energy ≈ 515,648,758,394 (the national-lab
--    management & operating contracts dominate the ingested obligated total).
WITH tot AS (
  SELECT sum(obligation_amount) AS obl
  FROM public.procurement_awards WHERE valid_to IS NULL
)
SELECT
  coalesce(nullif(a.awarding_agency, ''), '(unattributed)')         AS agency,
  count(*)                                                          AS actions,
  round(sum(a.obligation_amount))                                   AS obligated,
  round(sum(a.obligation_amount) / (SELECT obl FROM tot) * 100, 2)  AS share_pct
FROM public.procurement_awards a
WHERE a.valid_to IS NULL
GROUP BY 1
ORDER BY obligated DESC
LIMIT 10;
