-- Chattyness Realm Schema Tables
-- PostgreSQL 18 with PostGIS
--
-- Realms, scenes, memberships, realm props, and realm-level moderation

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Realms
-- =============================================================================

CREATE TABLE realm.realms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name public.nonempty_text NOT NULL,
    slug public.slug NOT NULL,
    description TEXT,
    tagline TEXT,

    owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,

    privacy realm.realm_privacy NOT NULL DEFAULT 'public',
    is_nsfw BOOLEAN NOT NULL DEFAULT false,
    min_reputation_tier server.reputation_tier NOT NULL DEFAULT 'guest',

    theme_color public.hex_color,
    banner_image_path public.asset_path,
    thumbnail_path public.asset_path,

    max_users INTEGER NOT NULL DEFAULT 100 CHECK (max_users > 0 AND max_users <= 10000),
    allow_guest_access BOOLEAN NOT NULL DEFAULT true,

    default_scene_id UUID,

    member_count INTEGER NOT NULL DEFAULT 0 CHECK (member_count >= 0),
    current_user_count INTEGER NOT NULL DEFAULT 0 CHECK (current_user_count >= 0),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_realm_realms_slug UNIQUE (slug)
);

COMMENT ON TABLE realm.realms IS 'Themed virtual spaces';

CREATE INDEX idx_realm_realms_owner ON realm.realms (owner_id);
CREATE INDEX idx_realm_realms_privacy ON realm.realms (privacy);
CREATE INDEX idx_realm_realms_public ON realm.realms (privacy, is_nsfw) WHERE privacy = 'public';

-- =============================================================================
-- Scenes
-- =============================================================================

CREATE TABLE realm.scenes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    name public.nonempty_text NOT NULL,
    slug public.slug NOT NULL,
    description TEXT,

    background_image_path public.asset_path,
    background_image_url public.url,
    background_color public.hex_color DEFAULT '#1a1a2e',
    bounds public.scene_bounds NOT NULL DEFAULT ST_MakeEnvelope(0, 0, 800, 600, 0),
    dimension_mode realm.dimension_mode NOT NULL DEFAULT 'fixed',

    ambient_audio_id UUID REFERENCES server.audio(id) ON DELETE SET NULL,
    ambient_volume public.percentage DEFAULT 0.5,

    sort_order INTEGER NOT NULL DEFAULT 0,

    is_entry_point BOOLEAN NOT NULL DEFAULT false,
    is_hidden BOOLEAN NOT NULL DEFAULT false,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_realm_scenes_slug UNIQUE (realm_id, slug)
);

COMMENT ON TABLE realm.scenes IS 'Rooms within a realm';

CREATE INDEX idx_realm_scenes_realm ON realm.scenes (realm_id);
CREATE INDEX idx_realm_scenes_realm_order ON realm.scenes (realm_id, sort_order);

-- Add FK for default_scene_id
ALTER TABLE realm.realms
    ADD CONSTRAINT fk_realm_realms_default_scene
    FOREIGN KEY (default_scene_id) REFERENCES realm.scenes(id) ON DELETE SET NULL;

-- =============================================================================
-- Guest Sessions (created here since it references realm tables)
-- =============================================================================
-- Note: current_instance_id FK is added in 045_scene.sql after scene.instances exists

CREATE TABLE auth.guest_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    guest_name public.display_name NOT NULL,
    token_hash TEXT NOT NULL,

    user_agent TEXT,
    ip_address INET,

    current_realm_id UUID REFERENCES realm.realms(id) ON DELETE SET NULL,
    current_instance_id UUID,  -- FK added in 045_scene.sql

    expires_at TIMESTAMPTZ NOT NULL,
    last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_guest_sessions_token UNIQUE (token_hash)
);

COMMENT ON TABLE auth.guest_sessions IS 'Anonymous guest sessions';

CREATE INDEX idx_auth_guest_sessions_expires ON auth.guest_sessions (expires_at);
CREATE INDEX idx_auth_guest_sessions_ip ON auth.guest_sessions (ip_address);

-- =============================================================================
-- Realm Memberships
-- =============================================================================

CREATE TABLE realm.memberships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    nickname public.display_name,

    role realm.realm_role NOT NULL DEFAULT 'member',
    role_granted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    role_granted_at TIMESTAMPTZ,

    exp_score BIGINT NOT NULL DEFAULT 0 CHECK (exp_score >= 0),

    last_scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
    last_position public.virtual_point,

    last_visited_at TIMESTAMPTZ,
    total_time_seconds BIGINT NOT NULL DEFAULT 0 CHECK (total_time_seconds >= 0),

    script_state JSONB NOT NULL DEFAULT '{}',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_realm_memberships UNIQUE (realm_id, user_id)
);

COMMENT ON TABLE realm.memberships IS 'User membership within a realm';

CREATE UNIQUE INDEX uq_realm_memberships_nickname ON realm.memberships (realm_id, lower(nickname)) WHERE nickname IS NOT NULL;
CREATE INDEX idx_realm_memberships_realm ON realm.memberships (realm_id);
CREATE INDEX idx_realm_memberships_user ON realm.memberships (user_id);
CREATE INDEX idx_realm_memberships_role ON realm.memberships (realm_id, role) WHERE role IN ('owner', 'moderator', 'builder');

-- =============================================================================
-- Realm Scripts
-- =============================================================================

CREATE TABLE realm.realm_scripts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    name public.nonempty_text NOT NULL,
    description TEXT,

    source TEXT NOT NULL,
    config JSONB NOT NULL DEFAULT '{}',
    state JSONB NOT NULL DEFAULT '{}',

    is_enabled BOOLEAN NOT NULL DEFAULT true,
    run_on_realm_enter BOOLEAN NOT NULL DEFAULT false,

    created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE realm.realm_scripts IS 'Rhai scripts for realms';

CREATE INDEX idx_realm_realm_scripts_realm ON realm.realm_scripts (realm_id);
CREATE INDEX idx_realm_realm_scripts_enabled ON realm.realm_scripts (realm_id, is_enabled) WHERE is_enabled = true;

-- =============================================================================
-- Realm Prop Library (moved from props.realm_props)
-- =============================================================================
-- Custom props specific to a realm (can only be used in that realm)
-- =============================================================================

CREATE TABLE realm.props (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    name public.nonempty_text NOT NULL,
    slug public.slug NOT NULL,
    description TEXT,
    tags TEXT[] NOT NULL DEFAULT '{}',

    asset_path public.asset_path NOT NULL,
    thumbnail_path public.asset_path,

    -- Default avatar positioning
    default_layer server.avatar_layer,
    default_emotion server.emotion_state,
    default_position SMALLINT CHECK (default_position IS NULL OR default_position BETWEEN 0 AND 8),

    is_unique BOOLEAN NOT NULL DEFAULT false,
    is_transferable BOOLEAN NOT NULL DEFAULT true,
    is_droppable BOOLEAN NOT NULL DEFAULT true,

    is_active BOOLEAN NOT NULL DEFAULT true,
    available_from TIMESTAMPTZ,
    available_until TIMESTAMPTZ,

    created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_realm_props_slug UNIQUE (realm_id, slug),
    CONSTRAINT chk_realm_props_availability CHECK (
        available_from IS NULL OR available_until IS NULL OR available_from < available_until
    ),
    -- Props can be: non-avatar (all NULL), content layer, OR emotion layer
    CONSTRAINT chk_realm_props_positioning CHECK (
        (default_layer IS NULL AND default_emotion IS NULL AND default_position IS NULL) OR
        (default_layer IS NOT NULL AND default_emotion IS NULL AND default_position IS NOT NULL) OR
        (default_layer IS NULL AND default_emotion IS NOT NULL AND default_position IS NOT NULL)
    )
);

COMMENT ON TABLE realm.props IS 'Realm-specific prop library';

CREATE INDEX idx_realm_props_realm ON realm.props (realm_id);
CREATE INDEX idx_realm_props_tags ON realm.props USING GIN (tags);
CREATE INDEX idx_realm_props_active ON realm.props (realm_id, is_active) WHERE is_active = true;

-- =============================================================================
-- Add Foreign Keys to auth tables (now that realm.realms and realm.props exist)
-- =============================================================================

-- Add FK for auth.inventory.realm_prop_id
ALTER TABLE auth.inventory
    ADD CONSTRAINT fk_auth_inventory_realm_prop
    FOREIGN KEY (realm_prop_id) REFERENCES realm.props(id) ON DELETE SET NULL;

-- Add FK for auth.active_avatars.realm_id
ALTER TABLE auth.active_avatars
    ADD CONSTRAINT fk_auth_active_avatars_realm
    FOREIGN KEY (realm_id) REFERENCES realm.realms(id) ON DELETE CASCADE;

-- =============================================================================
-- Realm-Level Moderation: Reports
-- =============================================================================
-- Reports are always realm-scoped (but server admins can resolve them)
-- =============================================================================

CREATE TABLE realm.reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    reporter_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    reported_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    category TEXT NOT NULL,
    description TEXT NOT NULL,
    evidence JSONB NOT NULL DEFAULT '[]',

    -- Context
    scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
    message_id UUID,  -- FK added when chat schema loads

    status server.report_status NOT NULL DEFAULT 'pending',
    resolved_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    resolved_at TIMESTAMPTZ,
    resolution_notes TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT chk_realm_reports_not_self CHECK (reporter_id != reported_user_id)
);

COMMENT ON TABLE realm.reports IS 'User reports within a realm';

CREATE INDEX idx_realm_reports_realm ON realm.reports (realm_id);
CREATE INDEX idx_realm_reports_reporter ON realm.reports (reporter_id);
CREATE INDEX idx_realm_reports_reported ON realm.reports (reported_user_id);
CREATE INDEX idx_realm_reports_status ON realm.reports (realm_id, status) WHERE status = 'pending';

-- =============================================================================
-- Realm-Level Moderation: Realm Bans
-- =============================================================================

CREATE TABLE realm.bans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    reason TEXT NOT NULL,
    evidence JSONB NOT NULL DEFAULT '[]',

    banned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    expires_at TIMESTAMPTZ,  -- NULL = permanent

    is_active BOOLEAN NOT NULL DEFAULT true,
    unbanned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    unbanned_at TIMESTAMPTZ,
    unban_reason TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_realm_bans_active UNIQUE (realm_id, user_id) -- Only one active ban per user per realm
);

COMMENT ON TABLE realm.bans IS 'Realm-specific user bans';

CREATE INDEX idx_realm_bans_realm ON realm.bans (realm_id);
CREATE INDEX idx_realm_bans_user ON realm.bans (user_id);
CREATE INDEX idx_realm_bans_active ON realm.bans (realm_id, user_id, is_active) WHERE is_active = true;
CREATE INDEX idx_realm_bans_expires ON realm.bans (expires_at) WHERE expires_at IS NOT NULL AND is_active = true;

-- =============================================================================
-- Realm-Level Moderation: Realm Mutes
-- =============================================================================

CREATE TABLE realm.mutes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    reason TEXT NOT NULL,

    muted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    expires_at TIMESTAMPTZ,  -- NULL = permanent

    is_active BOOLEAN NOT NULL DEFAULT true,
    unmuted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    unmuted_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE realm.mutes IS 'Realm-specific user mutes (cannot send messages in this realm)';

CREATE INDEX idx_realm_mutes_realm ON realm.mutes (realm_id);
CREATE INDEX idx_realm_mutes_user ON realm.mutes (user_id);
CREATE INDEX idx_realm_mutes_active ON realm.mutes (realm_id, user_id, is_active) WHERE is_active = true;

-- =============================================================================
-- Realm-Level Moderation: Content Filters
-- =============================================================================

CREATE TABLE realm.content_filters (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    pattern TEXT NOT NULL,
    is_regex BOOLEAN NOT NULL DEFAULT false,
    is_case_sensitive BOOLEAN NOT NULL DEFAULT false,

    action server.filter_action NOT NULL DEFAULT 'block',
    replacement TEXT,

    reason TEXT,

    is_active BOOLEAN NOT NULL DEFAULT true,

    created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE realm.content_filters IS 'Realm-specific content filtering rules';

CREATE INDEX idx_realm_content_filters_realm ON realm.content_filters (realm_id);
CREATE INDEX idx_realm_content_filters_active ON realm.content_filters (realm_id, is_active) WHERE is_active = true;

-- =============================================================================
-- Realm-Level Moderation: Action Log
-- =============================================================================

CREATE TABLE realm.moderation_actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,

    action_type server.action_type NOT NULL,

    target_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    moderator_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,

    reason TEXT NOT NULL,
    evidence JSONB NOT NULL DEFAULT '[]',
    metadata JSONB NOT NULL DEFAULT '{}',

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE realm.moderation_actions IS 'Log of realm-level moderation actions';

CREATE INDEX idx_realm_moderation_actions_realm ON realm.moderation_actions (realm_id);
CREATE INDEX idx_realm_moderation_actions_target ON realm.moderation_actions (target_user_id);
CREATE INDEX idx_realm_moderation_actions_moderator ON realm.moderation_actions (moderator_id);
CREATE INDEX idx_realm_moderation_actions_type ON realm.moderation_actions (realm_id, action_type);
CREATE INDEX idx_realm_moderation_actions_created ON realm.moderation_actions (realm_id, created_at DESC);

COMMIT;