-- Least-privilege runtime role for the shared dev DB (structural wipe-prevention).
-- Run as the schema owner (neondb_owner) on a throwaway Neon branch FIRST, prove it, then rotate DATABASE_URL.
--
-- Design (proven on branch, applied live 2026-07-10):
--   * neondb_owner stays the OWNER + migrate role. It already owns every table/sequence and can DROP/TRUNCATE/CREATE.
--     -> MIGRATE_DATABASE_URL points at neondb_owner (direct, non-pooled). Migrations + reset-demo run under it.
--   * multideal_app is a NON-owner LOGIN role with DML only (SELECT/INSERT/UPDATE/DELETE) and NO CREATE/TRUNCATE.
--     -> DATABASE_URL (the deployed worker + .dev.vars) points at multideal_app. It CANNOT drop or truncate anything.
--
-- Why NOT a separate multideal_migrator that owns the schema:
--   neondb_owner cannot `ALTER ... OWNER TO multideal_migrator` without membership in that role
--   ("must be able to SET ROLE"), and `ALTER DEFAULT PRIVILEGES FOR ROLE multideal_migrator` is denied.
--   neondb_owner already IS the owner, so it is the migrate role. No ownership transfer needed.

CREATE ROLE multideal_app LOGIN PASSWORD '<<REPLACE_APP_PW>>';

GRANT USAGE ON SCHEMA public TO multideal_app;

-- No CREATE: app role cannot add objects (and cannot own -> cannot DROP/TRUNCATE).
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM multideal_app;

-- DML on all current tables/sequences. TRUNCATE is deliberately NOT granted.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO multideal_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO multideal_app;

-- Future objects created by neondb_owner (migrations) auto-grant DML to the app role.
-- Run as neondb_owner => current-role default privileges; no FOR ROLE clause (that path is denied on Neon).
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO multideal_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO multideal_app;
