-- Grant read-only permissions on main punimtag database tables
-- Run this script as a PostgreSQL superuser (e.g., postgres user)
-- 
-- This script grants SELECT permissions on all main tables needed for the photo viewer
-- Replace 'viewer_readonly' with your actual read-only username from DATABASE_URL
--
-- WORKING METHOD (tested and confirmed):
--   PGPASSWORD=punimtag_password psql -h localhost -U punimtag -d punimtag -f grant_readonly_permissions.sql
--
-- Alternative methods if punimtag user doesn't work:
--   1. Using postgres user: PGPASSWORD=postgres_password psql -h localhost -U postgres -d punimtag -f grant_readonly_permissions.sql
--   2. Using sudo: sudo -u postgres psql -d punimtag -f grant_readonly_permissions.sql
--   3. Manual connection: psql -U punimtag -d punimtag (then paste the GRANT commands)

-- Step 1: Grant connect permission to the database
GRANT CONNECT ON DATABASE punimtag TO viewer_readonly;

-- Step 2: Grant usage on schema
GRANT USAGE ON SCHEMA public TO viewer_readonly;

-- Step 3: Grant SELECT on all main tables
GRANT SELECT ON TABLE photos TO viewer_readonly;
GRANT SELECT ON TABLE people TO viewer_readonly;
GRANT SELECT ON TABLE faces TO viewer_readonly;
GRANT SELECT ON TABLE person_encodings TO viewer_readonly;
GRANT SELECT ON TABLE tags TO viewer_readonly;
GRANT SELECT ON TABLE phototaglinkage TO viewer_readonly;
GRANT SELECT ON TABLE photo_favorites TO viewer_readonly;

-- Step 4: Grant USAGE on sequences (needed for some queries)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO viewer_readonly;

-- Step 5: Grant on future tables (optional, for tables created later)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO viewer_readonly;

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

-- Display success message
\echo '✅ Read-only permissions granted to viewer_readonly user!'
\echo ''
\echo 'The following permissions were granted:'
\echo '  - SELECT on photos, people, faces, person_encodings, tags, phototaglinkage, photo_favorites'
\echo '  - USAGE on all sequences'
\echo ''
\echo 'Your DATABASE_URL should be:'
\echo 'DATABASE_URL="postgresql://viewer_readonly:your_password@localhost:5432/punimtag"'

