-- Create a separate write-capable user for PunimTag Photo Viewer
-- Run this script as a PostgreSQL superuser (e.g., postgres user)
--
-- Option 2: Create a separate user with write permissions
-- (Use this if you want to keep viewer_readonly as read-only)

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

-- Grant connect permission
GRANT CONNECT ON DATABASE punimtag TO viewer_write;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO viewer_write;

-- Grant SELECT on all tables (for reading existing data)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer_write;

-- Grant INSERT and UPDATE on specific tables needed for face identification
GRANT INSERT, UPDATE ON TABLE people TO viewer_write;
GRANT INSERT, UPDATE ON TABLE faces TO viewer_write;

-- Grant usage on sequences (needed for auto-increment IDs)
GRANT USAGE, SELECT ON SEQUENCE people_id_seq TO viewer_write;

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

-- Display success message
\echo 'User viewer_write setup complete!'
\echo 'IMPORTANT: Update the password in .env file:'
\echo 'DATABASE_URL_WRITE="postgresql://viewer_write:your_secure_password_here@localhost:5432/punimtag"'



