-- pg-checkup read-only collector  ·  pgcheckup.aiskillhub.info
--
-- This script ONLY reads catalog + statistics views. It never reads your
-- application data, never writes, and never changes a setting. Review it before
-- running — that is the point of shipping it to you instead of asking for your
-- connection string.
--
-- Run it against the database you want graded:
--
--   psql "postgresql://readonly_user@host:5432/dbname" -X -q -At \
--        -f pgcheckup-collect.sql > pgcheckup.json
--
-- Then upload / paste pgcheckup.json at pgcheckup.aiskillhub.info/scan
--
-- A read-only role is enough:
--   CREATE ROLE pgcheckup LOGIN PASSWORD '...';
--   GRANT pg_read_all_stats TO pgcheckup;   -- PG 10+
--   GRANT USAGE ON SCHEMA public TO pgcheckup;

\pset pager off
\set QUIET on
\t on
\pset format unaligned

-- Is pg_stat_statements available?
SELECT CASE WHEN to_regclass('pg_stat_statements') IS NOT NULL
            THEN 'true' ELSE 'false' END AS has_pgss \gset

\if :has_pgss
SELECT COALESCE(json_agg(s)::text, '[]') AS stmts FROM (
  SELECT query, calls,
         total_exec_time, mean_exec_time, rows,
         shared_blks_hit, shared_blks_read, stddev_exec_time
  FROM pg_stat_statements
  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  ORDER BY total_exec_time DESC
  LIMIT 50
) s \gset
\else
\set stmts '[]'
\endif

SELECT json_build_object(
  'meta', json_build_object(
    'pg_version', current_setting('server_version'),
    'database', current_database(),
    'collected_at', now()
  ),
  'settings', (
    SELECT COALESCE(json_agg(json_build_object('name', name, 'value', setting, 'unit', unit)), '[]')
    FROM pg_settings
    WHERE name IN (
      'shared_buffers','effective_cache_size','work_mem','maintenance_work_mem',
      'max_connections','random_page_cost','effective_io_concurrency',
      'max_wal_size','checkpoint_completion_target','default_statistics_target',
      'autovacuum','autovacuum_vacuum_scale_factor','jit'
    )
  ),
  'database_stats', (
    SELECT json_build_object(
      'size_bytes', pg_database_size(current_database()),
      'blks_hit', blks_hit,
      'blks_read', blks_read,
      'cache_hit_ratio', CASE WHEN (blks_hit + blks_read) > 0
                              THEN blks_hit::float / (blks_hit + blks_read) ELSE 1 END,
      'xact_commit', xact_commit,
      'xact_rollback', xact_rollback,
      'deadlocks', deadlocks,
      'temp_files', temp_files,
      'temp_bytes', temp_bytes
    )
    FROM pg_stat_database WHERE datname = current_database()
  ),
  'tables', (
    SELECT COALESCE(json_agg(t), '[]') FROM (
      SELECT schemaname AS schema, relname AS name,
             n_live_tup, n_dead_tup, seq_scan, seq_tup_read,
             COALESCE(idx_scan, 0) AS idx_scan,
             pg_total_relation_size(relid) AS size_bytes,
             autovacuum_count, last_autovacuum
      FROM pg_stat_user_tables
      ORDER BY pg_total_relation_size(relid) DESC
      LIMIT 200
    ) t
  ),
  'indexes', (
    SELECT COALESCE(json_agg(i), '[]') FROM (
      SELECT s.schemaname AS schema, s.relname AS table, s.indexrelname AS name,
             COALESCE(s.idx_scan, 0) AS idx_scan,
             pg_relation_size(s.indexrelid) AS size_bytes,
             ix.indisunique AS is_unique, ix.indisprimary AS is_primary,
             (
               SELECT string_agg(a.attname, ',' ORDER BY k.ord)
               FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, ord)
               JOIN pg_attribute a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
             ) AS columns,
             pg_get_indexdef(s.indexrelid) AS definition
      FROM pg_stat_user_indexes s
      JOIN pg_index ix ON ix.indexrelid = s.indexrelid
      ORDER BY pg_relation_size(s.indexrelid) DESC
      LIMIT 300
    ) i
  ),
  'unindexed_fks', (
    SELECT COALESCE(json_agg(f), '[]') FROM (
      SELECT c.conrelid::regclass::text AS table,
             a.attname AS column,
             c.conname AS constraint,
             c.confrelid::regclass::text AS references
      FROM pg_constraint c
      JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
      WHERE c.contype = 'f'
        AND NOT EXISTS (
          SELECT 1 FROM pg_index i
          WHERE i.indrelid = c.conrelid
            AND i.indkey[0] = c.conkey[1]
        )
    ) f
  ),
  'statements', :'stmts'::json,
  'missing_pg_stat_statements', NOT :has_pgss
);
