-- ============================================================
-- CS2 Skin Tracker — read-only reporting/BI role
-- Run as a superuser (e.g. postgres) OR as skintracker_app,
-- connected to the skintracker database. Safe to re-run.
--
-- Replace the password placeholder before running.
-- This role can SELECT every table in the skintracker schema
-- (existing and future) and nothing else: no INSERT/UPDATE/
-- DELETE, no DDL, no access to other schemas.
-- ============================================================

-- 1. Login role, guarded so the script is idempotent.
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'skintracker_readonly') THEN
        CREATE ROLE skintracker_readonly WITH LOGIN PASSWORD 'change-me-readonly-password';
    END IF;
END
$$;

-- 2. Allow the role to connect to the database and resolve names
--    in the skintracker schema by default.
GRANT CONNECT ON DATABASE skintracker TO skintracker_readonly;
GRANT USAGE ON SCHEMA skintracker TO skintracker_readonly;
ALTER ROLE skintracker_readonly SET search_path = skintracker;

-- 3. Read access to every existing table/view in the schema.
GRANT SELECT ON ALL TABLES IN SCHEMA skintracker TO skintracker_readonly;

-- 4. Read access to tables the app role creates LATER (e.g. new
--    migrations). Without this, future tables would not be visible.
--    Must name the role that OWNS new objects — that is skintracker_app.
ALTER DEFAULT PRIVILEGES FOR ROLE skintracker_app IN SCHEMA skintracker
    GRANT SELECT ON TABLES TO skintracker_readonly;

-- 5. Belt-and-braces: ensure the role can never write to public either.
REVOKE CREATE ON SCHEMA public FROM skintracker_readonly;