-- License Transparency 2026 — re-derivation SQL
-- Run against: fonteum-platform (public schema), aggregated 2026-06-27
-- Sources: public.state_provider_licenses (consolidated state license rolls),
--          public.state_disciplinary_actions (published board disciplinary actions)
-- §187 contractor-licensing buckets are NOT a SQL query — they are the committed
--   primary-source recon CSV: src/lib/research/contractor-licensing-matrix-2026-05-06.csv
-- All figures are aggregate counts / field-population totals only. No licensee is named.
--
-- NOTE: public.state_provider_licenses holds ~8.14M rows (≈5.8 GB). A bare
-- COUNT(*) or per-state GROUP BY with DISTINCT can exceed the statement timeout.
-- The per-state record counts use the indexed (state, license_number) path; the
-- per-field population counts below are computed by scanning primary-key id ranges
-- (the table is loaded one state per contiguous id block) and summing the parts.
-- Every sum reconciles to the per-state record total exactly.

-- 1. License rolls obtained — records and publishing source, per state.
--    (one consolidated umbrella source per state; board count = 1 each)
SELECT state, source_key, COUNT(*) AS records
FROM public.state_provider_licenses
GROUP BY state, source_key
ORDER BY records DESC;
-- expected: il-idfpr 3,845,349 · wa-doh 2,422,615 · co-dora 1,566,603 ·
--           mi-lara 289,444 · ct-dph 18,994  (total 8,143,005)

-- 2. Profession breadth — distinct license-type labels per state.
SELECT state, COUNT(DISTINCT license_type) AS license_types
FROM public.state_provider_licenses
GROUP BY state;
-- expected: IL 63 · WA 310 · CO 310 · MI 15 · CT 1

-- 3. Field exposure — populated (non-null) count per field, per state.
--    Computed over primary-key id ranges and summed; each sum equals the
--    state's record total. Example shape for one id window:
SELECT state,
  COUNT(*)                  AS n,
  COUNT(license_number)     AS license_number,
  COUNT(license_type)       AS license_type,
  COUNT(status)             AS status,
  COUNT(full_name)          AS full_name,
  COUNT(issue_date)         AS issue_date,
  COUNT(expiry_date)        AS expiry_date,
  COUNT(city)               AS city,
  COUNT(specialty)          AS specialty,
  COUNT(address)            AS address
FROM public.state_provider_licenses
WHERE id BETWEEN :lo AND :hi   -- sweep [1 .. 10,038,005] in ~1.25M-id windows
GROUP BY state;
-- expected (full population, summed across windows):
--   IL: issue 3,830,762 · expiry 3,673,240 · city 3,844,103 · specialty 195,518 · address 0
--   WA: issue 2,175,892 · expiry 2,228,882 · city 0 · specialty 0 · address 0
--   CO: issue 1,563,308 · expiry 1,442,960 · city 1,564,191 · specialty 0 · address 0
--   MI: issue 0 · expiry 0 · city 288,848 · specialty 24,464 · address 0
--   CT: issue 17,614 · expiry 17,612 · city 18,991 · specialty 0 · address 0
--   (license_number, license_type, status, full_name ≈ 100% in every state)

-- 4. Published disciplinary actions obtained — per state and source.
SELECT state, source_key, COUNT(*) AS actions
FROM public.state_disciplinary_actions
GROUP BY state, source_key
ORDER BY actions DESC;
-- expected: il-idfpr 89,120 · wa-doh 21,640 · ny-opmc 8,997 · co-dora 8,585
--           (total 128,342; only 4 states publish an obtainable feed)

-- 5. Disciplinary-record field exposure + range (whole table).
SELECT
  COUNT(*)                       AS total,
  COUNT(basis)                   AS basis_populated,
  COUNT(action_date)             AS action_date_populated,
  COUNT(license_number)          AS license_number_populated,
  COUNT(DISTINCT license_type)   AS license_types,
  MIN(action_date)               AS earliest_action
FROM public.state_disciplinary_actions;
-- expected: 128,342 total · basis 86,262 (67.2%) · action_date 97,832 (76.2%) ·
--           license_number 128,342 (100%) · 293 license types · earliest 1935-03-05
--           (a small number of records carry malformed future action dates)
