-- Validate that each leaf partition has a UNIQUE, valid index
-- matching the parent table's PRIMARY KEY columns.
WITH RECURSIVE t AS (
  SELECT 'redacted_table'::regclass AS relid
  UNION ALL
  SELECT i.inhparent
  FROM t
  JOIN pg_inherits i ON i.inhrelid = t.relid
),
parent AS (
  SELECT relid
  FROM t
  WHERE NOT EXISTS (SELECT 1 FROM pg_inherits i WHERE i.inhrelid = t.relid)
  LIMIT 1
),
-- Parent PK columns (0 rows if no PK)
pk AS (
  SELECT con.conname AS pk_name,
         array_agg(a.attname ORDER BY x.n) AS pk_cols
  FROM pg_constraint con
  JOIN unnest(con.conkey) WITH ORDINALITY AS x(attnum, n) ON true
  JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = x.attnum
  WHERE con.contype = 'p' AND con.conrelid = (SELECT relid FROM parent)
  GROUP BY con.conname
),
-- Leaf partitions
leaves AS (
  SELECT c.oid AS relid, c.oid::regclass::text AS relname
  FROM pg_partition_tree((SELECT relid FROM parent)) pt
  JOIN pg_class c ON c.oid = pt.relid
  WHERE pt.isleaf
)

SELECT * FROM (
  -- 1) Parent PK summary
  SELECT
    'parent_pk'::text         AS section,
    (SELECT (SELECT relid FROM parent)::regclass::text) AS relname,
    pk.pk_name                AS pk_name,
    pk.pk_cols                AS details            -- text[]: PK column list
  FROM pk

  UNION ALL

  -- 2) Per-leaf status (does a UNIQUE, valid index exist on the PK cols?)
  SELECT
    'leaf_check'::text        AS section,
    l.relname                 AS relname,
    NULL::text                AS pk_name,
    (CASE WHEN EXISTS (
        SELECT 1
        FROM pg_index ix
        WHERE ix.indrelid = l.relid
          AND ix.indisunique
          AND ix.indisvalid
          AND ARRAY(
                SELECT a.attname
                FROM unnest(ix.indkey) WITH ORDINALITY k(attnum, ord)
                JOIN pg_attribute a
                  ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
                WHERE ord <= ix.indnkeyatts        -- exclude INCLUDE columns
                ORDER BY ord
              ) = (SELECT pk_cols FROM pk)
      )
      THEN ARRAY['OK']::text[]
      ELSE ARRAY['MISSING']::text[]
     END)                      AS details           -- text[]: OK/MISSING
  FROM leaves l
) AS u(section, relname, pk_name, details)
ORDER BY 1, 2;   -- section, relname