-- Login role for Annie
CREATE ROLE annie LOGIN PASSWORD 'change-me-to-a-strong-password';
GRANT CONNECT ON DATABASE app_db TO annie;

-- Health and statistics views, without access to table data
GRANT pg_monitor TO annie;  -- includes pg_read_all_stats and pg_read_all_settings

-- Every session starts read-only
ALTER ROLE annie SET default_transaction_read_only = on;

-- Run as the master user (on RDS: a member of rds_superuser, which includes pg_monitor).

-- 1. Role: login, read-only, short timeouts, no default access to app tables.
CREATE ROLE annie LOGIN PASSWORD '<strong-random-password>' CONNECTION LIMIT 5;
ALTER ROLE annie SET default_transaction_read_only = on;
ALTER ROLE annie SET statement_timeout = '10s';
ALTER ROLE annie SET idle_in_transaction_session_timeout = '30s';
GRANT CONNECT ON DATABASE <your_db> TO annie;
-- Deliberately no pg_monitor / pg_read_all_stats: annie only reads the views below.

-- 2. Schema owned by the master user; only annie may use it.
CREATE SCHEMA diagnostics;  -- owned by the user running this script
REVOKE ALL ON SCHEMA diagnostics FROM PUBLIC;
GRANT USAGE ON SCHEMA diagnostics TO annie;

-- 3. pg_stat_activity / pg_stat_statements / pg_stat_replication filter rows by the
--    CALLING role, so plain views are not enough. These SECURITY DEFINER functions
--    (owned by the master user, fixed search_path) return sanitized columns only:
--    no query text and no client address.
CREATE FUNCTION diagnostics.activity_snapshot()
  RETURNS TABLE (pid int, datname name, role_name name, backend_type text, state text,
                 wait_event_type text, wait_event text, query_start timestamptz,
                 xact_start timestamptz, blocked_by_count int)
  LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
  SELECT a.pid, a.datname, a.usename, a.backend_type, a.state, a.wait_event_type, a.wait_event,
         a.query_start, a.xact_start, cardinality(pg_blocking_pids(a.pid))
  FROM pg_stat_activity a $$;

CREATE FUNCTION diagnostics.replication_snapshot()
  RETURNS TABLE (application_name text, state text, sync_state text, write_lag interval,
                 flush_lag interval, replay_lag interval, replay_lag_bytes numeric)
  LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
  SELECT r.application_name, r.state, r.sync_state, r.write_lag, r.flush_lag, r.replay_lag,
         pg_wal_lsn_diff(pg_current_wal_lsn(), r.replay_lsn)
  FROM pg_stat_replication r $$;

REVOKE ALL ON FUNCTION diagnostics.activity_snapshot(),
  diagnostics.replication_snapshot() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION diagnostics.activity_snapshot(),
  diagnostics.replication_snapshot() TO annie;

-- 4. Health views (owned by the master user). No query text, no table data.
CREATE VIEW diagnostics.db_size AS
  SELECT datname, pg_database_size(oid) AS size_bytes, pg_size_pretty(pg_database_size(oid)) AS size
  FROM pg_catalog.pg_database WHERE NOT datistemplate AND has_database_privilege(oid, 'CONNECT');

CREATE VIEW diagnostics.transactions AS
  SELECT datname, xact_commit, xact_rollback,
         round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 3) AS rollback_pct, stats_reset
  FROM pg_catalog.pg_stat_database WHERE datname IS NOT NULL;

CREATE VIEW diagnostics.conflicts_deadlocks_temp AS
  SELECT datname, deadlocks, conflicts, temp_files, temp_bytes, pg_size_pretty(temp_bytes) AS temp_size,
         checksum_failures, stats_reset
  FROM pg_catalog.pg_stat_database WHERE datname IS NOT NULL;

CREATE VIEW diagnostics.cache_hit_ratio AS
  SELECT datname, blks_hit, blks_read,
         round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 3) AS cache_hit_pct
  FROM pg_catalog.pg_stat_database WHERE datname IS NOT NULL;

CREATE VIEW diagnostics.connections AS
  SELECT coalesce(a.state, a.backend_type) AS state, count(*) AS connections,
         (SELECT setting::int FROM pg_catalog.pg_settings WHERE name = 'max_connections') AS max_connections,
         (SELECT setting::int FROM pg_catalog.pg_settings WHERE name = 'superuser_reserved_connections') AS superuser_reserved_connections,
         (SELECT setting::int FROM pg_catalog.pg_settings WHERE name = 'rds.rds_superuser_reserved_connections') AS rds_superuser_reserved_connections
  FROM diagnostics.activity_snapshot() a GROUP BY 1;

CREATE VIEW diagnostics.long_running_queries AS
  SELECT pid, datname, role_name, backend_type, state, wait_event_type, wait_event,
         now() - query_start AS query_duration, now() - xact_start AS transaction_age, blocked_by_count
  FROM diagnostics.activity_snapshot()
  WHERE state IS DISTINCT FROM 'idle' AND backend_type = 'client backend' AND pid <> pg_backend_pid();

CREATE VIEW diagnostics.locks_waiting AS
  SELECT locktype, mode, count(*) AS waiting_locks, count(DISTINCT pid) AS waiting_backends
  FROM pg_catalog.pg_locks WHERE NOT granted GROUP BY 1, 2;

CREATE VIEW diagnostics.vacuum_stats AS
  SELECT schemaname, relname, n_live_tup, n_dead_tup,
         round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
         last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
         vacuum_count, autovacuum_count, analyze_count, autoanalyze_count,
         pg_catalog.pg_total_relation_size(relid) AS total_size_bytes
  FROM pg_catalog.pg_stat_user_tables;

CREATE VIEW diagnostics.replication_lag AS SELECT * FROM diagnostics.replication_snapshot();

GRANT SELECT ON diagnostics.db_size, diagnostics.transactions, diagnostics.conflicts_deadlocks_temp,
  diagnostics.cache_hit_ratio, diagnostics.connections, diagnostics.long_running_queries,
  diagnostics.locks_waiting, diagnostics.vacuum_stats, diagnostics.replication_lag TO annie;

-- Cleanup:
-- DROP SCHEMA diagnostics CASCADE; DROP OWNED BY annie;
-- REVOKE CONNECT ON DATABASE <your_db> FROM annie; DROP ROLE annie;

-- New install:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA diagnostics;
-- Already installed in another schema (for example public)? Move it instead:
-- ALTER EXTENSION pg_stat_statements SET SCHEMA diagnostics;
REVOKE ALL ON diagnostics.pg_stat_statements, diagnostics.pg_stat_statements_info FROM PUBLIC;

CREATE FUNCTION diagnostics.statement_stats()
  RETURNS TABLE (queryid bigint, calls bigint, total_exec_ms numeric, mean_exec_ms numeric,
                 max_exec_ms numeric, rows bigint, shared_blks_hit bigint, shared_blks_read bigint,
                 temp_blks_written bigint)
  LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
  SELECT s.queryid, s.calls, round(s.total_exec_time::numeric, 1), round(s.mean_exec_time::numeric, 2),
         round(s.max_exec_time::numeric, 1), s.rows, s.shared_blks_hit, s.shared_blks_read, s.temp_blks_written
  FROM diagnostics.pg_stat_statements s $$;

REVOKE ALL ON FUNCTION diagnostics.statement_stats() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION diagnostics.statement_stats() TO annie;

CREATE VIEW diagnostics.slow_query_stats AS SELECT * FROM diagnostics.statement_stats();
GRANT SELECT ON diagnostics.slow_query_stats TO annie;

CREATE VIEW diagnostics.failed_orders_per_hour AS
SELECT date_trunc('hour', created_at) AS hour, count(*) AS failed_orders
FROM public.orders WHERE status = 'failed' GROUP BY 1;
GRANT SELECT ON diagnostics.failed_orders_per_hour TO annie;

-- Read access to existing objects in the schema
GRANT USAGE ON SCHEMA public TO annie;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO annie;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO annie;

-- Read access to tables and sequences created later.
-- Run as the role that creates the tables, or add FOR ROLE <owner>.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO annie;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO annie;