-- Grant Full Permissions to songlyric_user
-- This ensures the application user can perform all operations
-- Run as: psql -h 192.168.10.130 -U postgres -d church_songlyric -f grant_full_permissions.sql

\echo '============================================================'
\echo 'GRANTING FULL PERMISSIONS TO songlyric_user'
\echo '============================================================'

-- Grant all privileges on database
GRANT ALL PRIVILEGES ON DATABASE church_songlyric TO songlyric_user;
\echo '✅ Database privileges granted'

-- Grant all privileges on schema
GRANT ALL PRIVILEGES ON SCHEMA public TO songlyric_user;
GRANT USAGE ON SCHEMA public TO songlyric_user;
\echo '✅ Schema privileges granted'

-- Grant all privileges on all existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO songlyric_user;
\echo '✅ Table privileges granted'

-- Grant all privileges on all sequences
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO songlyric_user;
\echo '✅ Sequence privileges granted'

-- Grant all privileges on all functions
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO songlyric_user;
\echo '✅ Function privileges granted'

-- Make songlyric_user the owner of all tables (this ensures full control)
ALTER TABLE songs OWNER TO songlyric_user;
ALTER TABLE profiles OWNER TO songlyric_user;
ALTER TABLE plans OWNER TO songlyric_user;
ALTER TABLE plan_songs OWNER TO songlyric_user;
ALTER TABLE profile_songs OWNER TO songlyric_user;
ALTER TABLE profile_song_keys OWNER TO songlyric_user;
\echo '✅ Table ownership transferred'

-- Set default privileges for future objects (IMPORTANT: persists after restart)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO songlyric_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO songlyric_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO songlyric_user;
\echo '✅ Default privileges set for future objects'

-- Grant CREATE privilege on schema (for creating new tables/indexes)
GRANT CREATE ON SCHEMA public TO songlyric_user;
\echo '✅ CREATE privilege granted'

-- Ensure songlyric_user can connect
GRANT CONNECT ON DATABASE church_songlyric TO songlyric_user;
\echo '✅ CONNECT privilege granted'

\echo ''
\echo '============================================================'
\echo '✅ PERMISSIONS GRANTED SUCCESSFULLY'
\echo '============================================================'
\echo ''
\echo 'songlyric_user now has:'
\echo '  ✅ Full ownership of all tables'
\echo '  ✅ All privileges on existing objects'
\echo '  ✅ Default privileges for future objects'
\echo '  ✅ CREATE privileges on schema'
\echo '  ✅ Permissions persist after restart'
\echo ''
\echo 'Verify permissions:'
\echo '  psql -h 192.168.10.130 -U songlyric_user -d church_songlyric -c "\\dt"'
\echo ''