-- Chattyness Database Schema Loader
-- PostgreSQL 18 with PostGIS
--
-- Master file to load all schema components in correct order.
--
-- Usage:
--   psql -d your_database -f schema/load.sql
--
-- Or from psql:
--   \i schema/load.sql
--
-- Prerequisites:
--   - PostgreSQL 18 with PostGIS extension available
--   - Database already created
--   - Superuser or appropriate privileges

\set ON_ERROR_STOP on
\timing on

\echo '=============================================='
\echo 'Chattyness Database Schema Loader'
\echo '=============================================='
\echo ''

-- =============================================================================
-- Phase 1: Initialization (schemas, extensions, roles)
-- =============================================================================
\echo 'Phase 1: Initialization...'
\ir 000_init.sql
\echo ''

-- =============================================================================
-- Phase 2: Types (ENUMs, domains)
-- =============================================================================
\echo 'Phase 2: Creating types...'
\ir types/001_enums.sql
\ir types/002_domains.sql
\echo ''

-- =============================================================================
-- Phase 3: Tables (in dependency order)
-- =============================================================================
-- Schema: server (010) → auth (020) → realm (030) → scene (045) → chat (050) → audit (080)
-- Note: props and moderation schemas removed; tables distributed to server/auth/realm/scene
\echo 'Phase 3: Creating tables...'
\ir tables/010_server.sql
\ir tables/020_auth.sql
\ir tables/025_server_avatars.sql
\ir tables/030_realm.sql
\ir tables/035_realm_avatars.sql
\ir tables/045_scene.sql
\ir tables/050_chat.sql
\ir tables/080_audit.sql
\echo ''

-- =============================================================================
-- Phase 4: Functions
-- =============================================================================
\echo 'Phase 4: Creating functions...'
\ir functions/001_helpers.sql
\echo ''

-- =============================================================================
-- Phase 5: Triggers
-- =============================================================================
\echo 'Phase 5: Creating triggers...'
\ir triggers/001_updated_at.sql
\echo ''

-- =============================================================================
-- Phase 6: Row-Level Security Policies
-- =============================================================================
\echo 'Phase 6: Enabling Row-Level Security...'
\ir policies/001_rls.sql
\echo ''

-- =============================================================================
-- Complete
-- =============================================================================
\echo '=============================================='
\echo 'Schema loaded successfully!'
\echo '=============================================='
\echo ''

-- =============================================================================
-- Phase 7: Set Passwords for Application Roles
-- =============================================================================
\echo 'Phase 7: Setting up application credentials...'

-- Generate secure passwords
\set app_password `pwgen -s 80 1`
\set owner_password `pwgen -s 80 1`

-- Set passwords for roles (created in init.sql)
ALTER ROLE chattyness_app WITH PASSWORD :'app_password';
ALTER ROLE chattyness_owner WITH PASSWORD :'owner_password';

\echo ''
\echo '=============================================='
\echo 'Application Credentials Set'
\echo '=============================================='
\echo ''
\echo 'chattyness_app (application role, subject to RLS):'
\echo '  Password: ' :app_password
\echo ''
\echo 'chattyness_owner (owner role, bypasses RLS):'
\echo '  Password: ' :owner_password
\echo ''

-- Write .env file
\! echo "# Chattyness Database Credentials" > .env
\! echo "# Generated by load.sql" >> .env
\! echo "" >> .env
\set write_app `echo "export DB_CHATTYNESS_APP=":app_password >> .env`
\set write_owner `echo "export DB_CHATTYNESS_OWNER=":owner_password >> .env`

\echo 'Credentials written to: .env'
\echo ''
\echo 'Login roles:'
\echo '  chattyness_app   - Application operations (subject to RLS)'
\echo '  chattyness_owner - Owner operations (bypasses RLS)'
\echo ''