-- Chattyness Database Schema Initialization
-- PostgreSQL 18
--
-- This file drops all existing schema and recreates from scratch.
-- Load via: psql -d chattyness -f schema/000_init.sql
--
-- WARNING: This destroys all data!

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Drop Everything (CASCADE removes all dependent objects)
-- =============================================================================

DROP SCHEMA IF EXISTS audit CASCADE;
DROP SCHEMA IF EXISTS chat CASCADE;
DROP SCHEMA IF EXISTS scene CASCADE;
DROP SCHEMA IF EXISTS realm CASCADE;
DROP SCHEMA IF EXISTS auth CASCADE;
DROP SCHEMA IF EXISTS server CASCADE;

-- Drop public domains we create (not removed by schema drops)
DROP DOMAIN IF EXISTS public.scene_bounds CASCADE;
DROP DOMAIN IF EXISTS public.virtual_point CASCADE;
DROP DOMAIN IF EXISTS public.asset_path CASCADE;
DROP DOMAIN IF EXISTS public.url CASCADE;
DROP DOMAIN IF EXISTS public.hex_color CASCADE;
DROP DOMAIN IF EXISTS public.slug CASCADE;
DROP DOMAIN IF EXISTS public.display_name CASCADE;
DROP DOMAIN IF EXISTS public.nonempty_text CASCADE;
DROP DOMAIN IF EXISTS public.percentage CASCADE;

-- Drop all objects owned by application roles (required to avoid dependency errors)
DO $$
BEGIN
    -- Drop objects owned by chattyness_owner
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'chattyness_owner') THEN
        DROP OWNED BY chattyness_owner CASCADE;
    END IF;
    -- Drop objects owned by chattyness_app
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'chattyness_app') THEN
        DROP OWNED BY chattyness_app CASCADE;
    END IF;
END $$;

-- Drop all application roles
DROP ROLE IF EXISTS chattyness_app;
DROP ROLE IF EXISTS chattyness_owner;

-- =============================================================================
-- Extensions
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "postgis";

-- =============================================================================
-- Schemas
-- =============================================================================

CREATE SCHEMA server;
COMMENT ON SCHEMA server IS 'Server-wide configuration, global props, shared resources, and server-level moderation';

CREATE SCHEMA auth;
COMMENT ON SCHEMA auth IS 'User authentication, accounts, identity management, inventory, and avatars';

CREATE SCHEMA realm;
COMMENT ON SCHEMA realm IS 'Realms, scenes, memberships, realm props, and realm-level moderation';

CREATE SCHEMA scene;
COMMENT ON SCHEMA scene IS 'Scene instances, members, spots, loose props, and decorations';

CREATE SCHEMA chat;
COMMENT ON SCHEMA chat IS 'Messages, whispers, shouts, and reactions';

CREATE SCHEMA audit;
COMMENT ON SCHEMA audit IS 'Audit trails and activity logging';

-- =============================================================================
-- Application Roles
-- =============================================================================

-- Application login role for normal operations (subject to RLS)
-- Password is set in load.sql
CREATE ROLE chattyness_app LOGIN;
COMMENT ON ROLE chattyness_app IS 'Application login role - subject to RLS, user context passed via session variables';

-- Owner role with full access (bypasses RLS)
-- Password is set in load.sql
CREATE ROLE chattyness_owner LOGIN BYPASSRLS;
COMMENT ON ROLE chattyness_owner IS 'Owner role - bypasses RLS for server management';

-- =============================================================================
-- Grant Full Access to Owner Role
-- =============================================================================

-- Grant usage on all schemas
GRANT USAGE ON SCHEMA public TO chattyness_owner;
GRANT USAGE ON SCHEMA server TO chattyness_owner;
GRANT USAGE ON SCHEMA auth TO chattyness_owner;
GRANT USAGE ON SCHEMA realm TO chattyness_owner;
GRANT USAGE ON SCHEMA scene TO chattyness_owner;
GRANT USAGE ON SCHEMA chat TO chattyness_owner;
GRANT USAGE ON SCHEMA audit TO chattyness_owner;

-- Grant all privileges on all tables in each schema
-- These will apply to tables created later in the load sequence
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT ALL ON TABLES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT ALL ON TABLES TO chattyness_owner;

-- Grant all privileges on sequences (for identity columns)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT ALL ON SEQUENCES TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT ALL ON SEQUENCES TO chattyness_owner;

-- Grant execute on all functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA server GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA realm GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA scene GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA chat GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT EXECUTE ON FUNCTIONS TO chattyness_owner;

-- =============================================================================
-- Schema Search Path
-- =============================================================================

DO $$
BEGIN
    EXECUTE format('ALTER DATABASE %I SET search_path TO public, server, auth, realm, scene, chat, audit', current_database());
END
$$;

COMMIT;