-- Canonical production re-derivation for /research/hh-quality-analysis.
--
-- Execute this one WITH statement through
-- scripts/research/study-figure-audit/sql.ts::executeReadOnly. That helper
-- pins Supabase project rfabugntpwelbpbexgcz and wraps the statement in:
--   BEGIN TRANSACTION READ ONLY;
--   SET LOCAL statement_timeout = '120s';
--   ...this statement...
--   ROLLBACK;
--
-- Every count and percentage is aggregated by PostgreSQL. No PostgREST result
-- page or client-side row count participates in the published figures.
WITH source_versions AS MATERIALIZED (
  SELECT
    ps.id,
    ps.source_name,
    substring(
      ps.source_name FROM '([0-9]{4}-[0-9]{2}-[0-9]{2})$'
    )::date AS source_release_date
  FROM public.provenance_sources AS ps
  WHERE ps.source_name LIKE 'CMS Nursing Home Provider Information %'
    AND EXISTS (
      SELECT 1
      FROM public.provenance_claims AS pc
      WHERE pc.source_id = ps.id
        AND pc.subject_type = 'facility'
        AND pc.metric = 'nursing_overall_rating'
        AND pc.superseded_at IS NULL
    )
),
latest_nursing_period AS (
  SELECT
    max(
      to_char(source_release_date, 'YYYY') || 'q' ||
      extract(quarter FROM source_release_date)::integer
    ) AS claim_period
  FROM source_versions
),
nursing_period_sources AS MATERIALIZED (
  SELECT sv.*
  FROM source_versions AS sv
  CROSS JOIN latest_nursing_period AS lp
  WHERE to_char(sv.source_release_date, 'YYYY') || 'q' ||
        extract(quarter FROM sv.source_release_date)::integer = lp.claim_period
),
nursing_claims AS MATERIALIZED (
  SELECT pc.*
  FROM public.provenance_claims AS pc
  JOIN nursing_period_sources AS ps ON ps.id = pc.source_id
  CROSS JOIN latest_nursing_period AS lp
  WHERE pc.subject_type = 'facility'
    AND pc.superseded_at IS NULL
    AND right(pc.claim_key, length(lp.claim_period) + 1) = '.' || lp.claim_period
),
nursing_universe AS MATERIALIZED (
  SELECT DISTINCT subject_id AS ccn
  FROM nursing_claims
),
nursing_ratings AS MATERIALIZED (
  SELECT
    subject_id AS ccn,
    max(value_numeric)::numeric AS rating,
    count(*)::integer AS row_count
  FROM nursing_claims
  WHERE metric = 'nursing_overall_rating'
    AND value_numeric IS NOT NULL
  GROUP BY subject_id
),
hh_summary AS MATERIALIZED (
  SELECT
    count(*)::bigint AS total,
    count(*) FILTER (WHERE quality_star_rating IS NOT NULL)::bigint AS rated,
    count(*) FILTER (WHERE quality_star_rating IS NULL)::bigint AS unrated,
    count(*) FILTER (WHERE quality_star_rating = 5)::bigint AS five_star,
    count(*) FILTER (WHERE quality_star_rating >= 4)::bigint AS four_plus,
    count(*) FILTER (WHERE quality_star_rating <= 2)::bigint AS two_or_below,
    min(source_release_date)::date AS source_release_date,
    max(source_release_date)::date AS max_source_release_date,
    min(ingested_at) AS first_ingested_at,
    max(ingested_at) AS last_ingested_at,
    count(DISTINCT source_id)::integer AS source_id_count,
    jsonb_agg(DISTINCT source_id) AS source_ids,
    jsonb_agg(DISTINCT methodology_version) AS methodology_versions,
    count(DISTINCT ccn)::bigint AS distinct_ccns
  FROM public.cms_care_compare_hh
),
star_distribution AS (
  SELECT
    quality_star_rating AS star,
    count(*)::bigint AS agency_count
  FROM public.cms_care_compare_hh
  WHERE quality_star_rating IS NOT NULL
  GROUP BY quality_star_rating
),
ownership_distribution AS (
  SELECT
    CASE type_of_ownership
      WHEN 'PROPRIETARY' THEN 'Proprietary'
      WHEN 'NON-PROFIT' THEN 'Non-profit'
      WHEN 'GOVERNMENT OPERATED' THEN 'Government'
      ELSE 'Unknown'
    END AS ownership,
    count(*)::bigint AS total,
    count(*) FILTER (WHERE quality_star_rating IS NOT NULL)::bigint AS rated,
    count(*) FILTER (WHERE quality_star_rating IS NULL)::bigint AS unrated
  FROM public.cms_care_compare_hh
  GROUP BY 1
),
unrated_footnotes AS (
  SELECT
    quality_footnote AS footnote,
    count(*)::bigint AS agency_count
  FROM public.cms_care_compare_hh
  WHERE quality_star_rating IS NULL
  GROUP BY quality_footnote
),
nursing_comparison AS (
  SELECT
    (SELECT count(*) FROM nursing_universe)::bigint AS total,
    count(*)::bigint AS rated,
    (
      (SELECT count(*) FROM nursing_universe) - count(*)
    )::bigint AS unrated,
    count(*) FILTER (WHERE row_count <> 1)::bigint AS duplicate_rating_subjects,
    count(*) FILTER (WHERE rating NOT BETWEEN 1 AND 5)::bigint AS invalid_ratings,
    max((SELECT max(source_release_date) FROM nursing_period_sources)) AS source_release_date
  FROM nursing_ratings
),
dialysis_comparison AS (
  SELECT
    count(*)::bigint AS total,
    count(*) FILTER (WHERE five_star IS NOT NULL)::bigint AS rated,
    count(*) FILTER (WHERE five_star IS NULL)::bigint AS unrated,
    min(source_release_date)::date AS source_release_date,
    max(source_release_date)::date AS max_source_release_date,
    count(DISTINCT ccn)::bigint AS distinct_ccns
  FROM public.cms_care_compare_dialysis
)
SELECT jsonb_build_object(
  'projectRef', 'rfabugntpwelbpbexgcz',
  'observedAt', current_date,
  'source', (
    SELECT jsonb_build_object(
      'releaseDate', source_release_date,
      'maxReleaseDate', max_source_release_date,
      'firstIngestedAt', first_ingested_at,
      'lastIngestedAt', last_ingested_at,
      'sourceIdCount', source_id_count,
      'sourceIds', source_ids,
      'methodologyVersions', methodology_versions
    )
    FROM hh_summary
  ),
  'integrity', (
    SELECT jsonb_build_object(
      'rows', total,
      'distinctCcns', distinct_ccns,
      'releaseDateConsistent', source_release_date = max_source_release_date,
      'allRatingsInRange', NOT EXISTS (
        SELECT 1
        FROM public.cms_care_compare_hh
        WHERE quality_star_rating IS NOT NULL
          AND (
            quality_star_rating NOT BETWEEN 1 AND 5 OR
            quality_star_rating * 2 <> trunc(quality_star_rating * 2)
          )
      )
    )
    FROM hh_summary
  ),
  'headline', (
    SELECT jsonb_build_object(
      'totalAgencies', total,
      'ratedAgencies', rated,
      'unratedAgencies', unrated,
      'pctRated', round(100.0 * rated / nullif(total, 0), 2),
      'pctUnrated', round(100.0 * unrated / nullif(total, 0), 2),
      'fiveStarAgencies', five_star,
      'pctFiveStarOfRated', round(100.0 * five_star / nullif(rated, 0), 2),
      'fourPlusAgencies', four_plus,
      'pctFourPlusOfRated', round(100.0 * four_plus / nullif(rated, 0), 2),
      'twoOrBelowAgencies', two_or_below,
      'pctTwoOrBelowOfRated', round(100.0 * two_or_below / nullif(rated, 0), 2)
    )
    FROM hh_summary
  ),
  'starDistribution', (
    SELECT jsonb_agg(
      jsonb_build_object(
        'star', sd.star,
        'agencies', sd.agency_count,
        'pctOfRated', round(
          100.0 * sd.agency_count / nullif(hs.rated, 0),
          2
        )
      )
      ORDER BY sd.star DESC
    )
    FROM star_distribution AS sd
    CROSS JOIN hh_summary AS hs
  ),
  'ownership', (
    SELECT jsonb_agg(
      jsonb_build_object(
        'ownership', od.ownership,
        'total', od.total,
        'rated', od.rated,
        'unrated', od.unrated,
        'pctOfAll', round(100.0 * od.total / nullif(hs.total, 0), 2),
        'pctOfRated', round(100.0 * od.rated / nullif(hs.rated, 0), 2),
        'pctOfUnrated', round(100.0 * od.unrated / nullif(hs.unrated, 0), 2)
      )
      ORDER BY od.total DESC, od.ownership ASC
    )
    FROM ownership_distribution AS od
    CROSS JOIN hh_summary AS hs
  ),
  'unratedFootnotes', (
    SELECT jsonb_agg(
      jsonb_build_object(
        'footnote', footnote,
        'agencies', agency_count
      )
      ORDER BY agency_count DESC, footnote ASC
    )
    FROM unrated_footnotes
  ),
  'comparisons', jsonb_build_object(
    'nursingHomes', (
      SELECT jsonb_build_object(
        'total', total,
        'rated', rated,
        'unrated', unrated,
        'pctUnrated', round(100.0 * unrated / nullif(total, 0), 2),
        'sourceReleaseDate', source_release_date,
        'duplicateRatingSubjects', duplicate_rating_subjects,
        'invalidRatings', invalid_ratings
      )
      FROM nursing_comparison
    ),
    'dialysisFacilities', (
      SELECT jsonb_build_object(
        'total', total,
        'rated', rated,
        'unrated', unrated,
        'pctUnrated', round(100.0 * unrated / nullif(total, 0), 2),
        'sourceReleaseDate', source_release_date,
        'releaseDateConsistent', source_release_date = max_source_release_date,
        'distinctCcns', distinct_ccns
      )
      FROM dialysis_comparison
    )
  )
) AS result;
