-- Create read-only user for PunimTag Photo Viewer
-- Run this script as a PostgreSQL superuser (e.g., postgres user)

-- Check if user exists, create if not
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = 'viewer_readonly') THEN
        CREATE USER viewer_readonly WITH PASSWORD 'your_secure_password';
        RAISE NOTICE 'User viewer_readonly created';
    ELSE
        RAISE NOTICE 'User viewer_readonly already exists';
    END IF;
END
$$;

-- Grant permissions
GRANT CONNECT ON DATABASE punimtag TO viewer_readonly;
GRANT USAGE ON SCHEMA public TO viewer_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer_readonly;

-- Grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO viewer_readonly;

-- Verify no write permissions
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM viewer_readonly;

-- Display success message
\echo 'User viewer_readonly setup complete!'
\echo 'IMPORTANT: Update the password in .env file to match the password set above'

