-- PostgreSQL permissions setup
-- Date: 06.apr.2026
--
-- This file sets up permissions for the Clawdie application database user.
-- Must be run AFTER all schema migrations.
--
-- Usage:
--   psql -v db_user=<app_user> -d <database> -f permissions.sql
--
-- What it does:
--   1. GRANT ALL on ALL existing tables and sequences
--   2. ALTER DEFAULT PRIVILEGES for future tables/sequences
--
-- Why DEFAULT PRIVILEGES:
--   - Future tables created by postgres superuser automatically get permissions
--   - No need to remember GRANT when adding new tables
--   - Idempotent and safe to re-run

--------------------------------------------------------------------------------
-- GRANT ON EXISTING TABLES AND SEQUENCES
--------------------------------------------------------------------------------

-- Grant permissions on all existing tables
DO $$
DECLARE
  tbl record;
  app_user text := current_setting('db_user', true);
BEGIN
  IF app_user IS NULL OR app_user = '' THEN
    RAISE EXCEPTION 'db_user variable not set. Use: psql -v db_user=<username>';
  END IF;

  FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
  LOOP
    EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I TO %I', tbl.tablename, app_user);
    RAISE NOTICE 'Granted ALL on table % to %', tbl.tablename, app_user;
  END LOOP;
END $$;

-- Grant permissions on all existing sequences
DO $$
DECLARE
  seq record;
  app_user text := current_setting('db_user', true);
BEGIN
  FOR seq IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public'
  LOOP
    EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I TO %I', seq.sequencename, app_user);
    RAISE NOTICE 'Granted ALL on sequence % to %', seq.sequencename, app_user;
  END LOOP;
END $$;

--------------------------------------------------------------------------------
-- ALTER DEFAULT PRIVILEGES FOR FUTURE TABLES
--------------------------------------------------------------------------------

-- Auto-grant permissions on future tables created by postgres superuser
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public
  GRANT ALL PRIVILEGES ON TABLES TO CURRENT_SETTING('db_user');

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public
  GRANT ALL PRIVILEGES ON SEQUENCES TO CURRENT_SETTING('db_user');

-- Also set default privileges for the current role (in case migrations run as app user)
ALTER DEFAULT PRIVILEGES
  IN SCHEMA public
  GRANT ALL PRIVILEGES ON TABLES TO CURRENT_SETTING('db_user');

ALTER DEFAULT PRIVILEGES
  IN SCHEMA public
  GRANT ALL PRIVILEGES ON SEQUENCES TO CURRENT_SETTING('db_user');

--------------------------------------------------------------------------------
-- VERIFICATION
--------------------------------------------------------------------------------

-- Log what was granted
DO $$
DECLARE
  app_user text := current_setting('db_user', true);
  table_count int;
  seq_count int;
BEGIN
  SELECT count(*) INTO table_count
  FROM pg_tables WHERE schemaname = 'public';

  SELECT count(*) INTO seq_count
  FROM pg_sequences WHERE schemaname = 'public';

  RAISE NOTICE 'Permissions setup complete for user: %', app_user;
  RAISE NOTICE '  Tables in public schema: %', table_count;
  RAISE NOTICE '  Sequences in public schema: %', seq_count;
  RAISE NOTICE '  Default privileges set for future objects';
END $$;