-- PostgreSQL User Setup Script for pg-api
-- This script creates the necessary users and grants appropriate permissions
-- Run as postgres superuser

-- Create databases if they don't exist
CREATE DATABASE IF NOT EXISTS camera;
CREATE DATABASE IF NOT EXISTS sentric;

-- Create application users
-- Camera application user
CREATE USER camera_app WITH PASSWORD 'camera_app_pwd_2025';
GRANT CONNECT ON DATABASE camera TO camera_app;
\c camera
GRANT USAGE ON SCHEMA public TO camera_app;
GRANT CREATE ON SCHEMA public TO camera_app;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO camera_app;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO camera_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO camera_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO camera_app;

-- Sentric application user
\c postgres
CREATE USER sentric_app WITH PASSWORD 'sentric_app_pwd_2025';
GRANT CONNECT ON DATABASE sentric TO sentric_app;
\c sentric
GRANT USAGE ON SCHEMA public TO sentric_app;
GRANT CREATE ON SCHEMA public TO sentric_app;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sentric_app;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sentric_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sentric_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sentric_app;

-- Analytics read-only user (access to both databases)
\c postgres
CREATE USER analytics_user WITH PASSWORD 'analytics_pwd_2025';

-- Grant read-only access to camera database
GRANT CONNECT ON DATABASE camera TO analytics_user;
\c camera
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_user;

-- Grant read-only access to sentric database
\c postgres
GRANT CONNECT ON DATABASE sentric TO analytics_user;
\c sentric
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_user;

-- Migration tool user (DDL permissions)
\c postgres
CREATE USER migration_user WITH PASSWORD 'migration_pwd_2025';
GRANT CONNECT ON DATABASE postgres TO migration_user;
GRANT CONNECT ON DATABASE camera TO migration_user;
GRANT CONNECT ON DATABASE sentric TO migration_user;

-- Grant full schema permissions for migrations
\c camera
GRANT ALL ON SCHEMA public TO migration_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO migration_user;

\c sentric
GRANT ALL ON SCHEMA public TO migration_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO migration_user;

-- List all users and their permissions
\c postgres
\du

-- Show database access
SELECT 
    u.usename AS username,
    d.datname AS database,
    HAS_DATABASE_PRIVILEGE(u.usename, d.datname, 'CONNECT') AS can_connect
FROM 
    pg_user u
    CROSS JOIN pg_database d
WHERE 
    d.datname NOT IN ('template0', 'template1')
    AND u.usename IN ('camera_app', 'sentric_app', 'analytics_user', 'migration_user')
ORDER BY 
    u.usename, d.datname;