-- Chattyness Auth Schema Tables
-- PostgreSQL 18
--
-- User authentication, accounts, and identity management

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- User Accounts
-- =============================================================================

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

    username auth.username NOT NULL,
    email auth.email,
    phone TEXT,  -- TODO: migrate to auth.phone_number domain
    password_hash TEXT,
    auth_provider auth.auth_provider NOT NULL DEFAULT 'local',
    oauth_id TEXT,

    display_name public.display_name NOT NULL,
    bio TEXT,
    avatar_url public.url,

    -- HOSS membership profile fields
    name_first TEXT,
    name_last TEXT,
    summary TEXT,                                          -- Brief one-liner tagline
    homepage public.url,
    avatar_source auth.avatar_source NOT NULL DEFAULT 'local',
    profile_visibility auth.profile_visibility NOT NULL DEFAULT 'public',
    contacts_visibility auth.profile_visibility NOT NULL DEFAULT 'members',
    organizations_visibility auth.profile_visibility NOT NULL DEFAULT 'members',

    -- User preferences for default avatar selection
    birthday DATE,
    gender_preference auth.gender_preference NOT NULL DEFAULT 'gender_neutral',
    age_category auth.age_category NOT NULL DEFAULT 'adult',

    reputation_tier server.reputation_tier NOT NULL DEFAULT 'member',
    reputation_promoted_at TIMESTAMPTZ,

    status auth.account_status NOT NULL DEFAULT 'active',
    email_verified BOOLEAN NOT NULL DEFAULT false,
    email_verified_at TIMESTAMPTZ,
    force_pw_reset BOOLEAN NOT NULL DEFAULT false,

    last_seen_at TIMESTAMPTZ,
    total_time_online_seconds BIGINT NOT NULL DEFAULT 0,

    script_state JSONB NOT NULL DEFAULT '{}',

    -- User tags for feature gating and access control
    tags auth.user_tag[] NOT NULL DEFAULT '{}',

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

    CONSTRAINT uq_auth_users_username UNIQUE (username),
    CONSTRAINT uq_auth_users_email UNIQUE (email),
    CONSTRAINT uq_auth_users_oauth UNIQUE (auth_provider, oauth_id),
    CONSTRAINT chk_auth_users_password_or_oauth_or_guest
        CHECK (password_hash IS NOT NULL OR oauth_id IS NOT NULL OR 'guest' = ANY(tags))
);

COMMENT ON TABLE auth.users IS 'User accounts and authentication';

CREATE INDEX idx_auth_users_email ON auth.users (lower(email));
CREATE INDEX idx_auth_users_status ON auth.users (status);
CREATE INDEX idx_auth_users_reputation ON auth.users (reputation_tier);
CREATE INDEX idx_auth_users_last_seen ON auth.users (last_seen_at DESC NULLS LAST);
CREATE INDEX idx_auth_users_tags ON auth.users USING GIN (tags);

-- =============================================================================
-- User Sessions
-- =============================================================================

CREATE TABLE auth.sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    token_hash TEXT NOT NULL,
    user_agent TEXT,
    ip_address INET,

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

    CONSTRAINT uq_auth_sessions_token UNIQUE (token_hash)
);

COMMENT ON TABLE auth.sessions IS 'Active user sessions';

CREATE INDEX idx_auth_sessions_user ON auth.sessions (user_id);
CREATE INDEX idx_auth_sessions_expires ON auth.sessions (expires_at);

-- =============================================================================
-- Tower Sessions (tower-sessions crate)
-- =============================================================================

CREATE TABLE auth.tower_sessions (
    id TEXT PRIMARY KEY,
    data BYTEA NOT NULL,
    expiry_date TIMESTAMPTZ NOT NULL
);

COMMENT ON TABLE auth.tower_sessions IS 'Session storage for tower-sessions crate';

CREATE INDEX idx_auth_tower_sessions_expiry ON auth.tower_sessions (expiry_date);

-- =============================================================================
-- Friends List
-- =============================================================================

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

    friend_a UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    friend_b UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    initiated_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    is_accepted BOOLEAN NOT NULL DEFAULT false,
    accepted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_friendships UNIQUE (friend_a, friend_b),
    CONSTRAINT chk_auth_friendships_ordered CHECK (friend_a < friend_b),
    CONSTRAINT chk_auth_friendships_initiator CHECK (initiated_by = friend_a OR initiated_by = friend_b)
);

COMMENT ON TABLE auth.friendships IS 'Friend relationships (normalized: friend_a < friend_b)';

CREATE INDEX idx_auth_friendships_friend_a ON auth.friendships (friend_a);
CREATE INDEX idx_auth_friendships_friend_b ON auth.friendships (friend_b);
CREATE INDEX idx_auth_friendships_pending ON auth.friendships (is_accepted) WHERE is_accepted = false;

-- =============================================================================
-- User Contacts (Social/Contact Links)
-- =============================================================================

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

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    platform auth.contact_platform NOT NULL,
    value TEXT NOT NULL,
    label TEXT,                                            -- Optional display label
    sort_order SMALLINT NOT NULL DEFAULT 0,

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

    CONSTRAINT uq_auth_user_contacts_platform UNIQUE (user_id, platform, value),
    CONSTRAINT chk_auth_user_contacts_value_nonempty CHECK (length(trim(value)) > 0)
);

COMMENT ON TABLE auth.user_contacts IS 'User social/contact platform links';
COMMENT ON COLUMN auth.user_contacts.platform IS 'Type of contact (discord, github, linkedin, etc.)';
COMMENT ON COLUMN auth.user_contacts.value IS 'Platform-specific identifier (handle, URL, phone, etc.)';
COMMENT ON COLUMN auth.user_contacts.label IS 'Optional user-friendly display label';
COMMENT ON COLUMN auth.user_contacts.sort_order IS 'Display order (lower = first)';

CREATE INDEX idx_auth_user_contacts_user ON auth.user_contacts (user_id);
CREATE INDEX idx_auth_user_contacts_platform ON auth.user_contacts (platform);

-- =============================================================================
-- User Organizations (Affiliations)
-- =============================================================================

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

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    wikidata_qid public.wikidata_qid,                      -- Wikidata Q-number for org
    name TEXT NOT NULL,                                    -- Display name (may differ from Wikidata)
    role TEXT,                                             -- User's role/title
    department TEXT,                                       -- Department/division
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true,
    sort_order SMALLINT NOT NULL DEFAULT 0,

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

    CONSTRAINT chk_auth_user_orgs_name_nonempty CHECK (length(trim(name)) > 0),
    CONSTRAINT chk_auth_user_orgs_dates CHECK (start_date IS NULL OR end_date IS NULL OR start_date <= end_date)
);

COMMENT ON TABLE auth.user_organizations IS 'User organizational affiliations';
COMMENT ON COLUMN auth.user_organizations.wikidata_qid IS 'Wikidata Q-number for standardized organization lookup';
COMMENT ON COLUMN auth.user_organizations.name IS 'Display name for the organization';
COMMENT ON COLUMN auth.user_organizations.role IS 'User role/title at the organization';
COMMENT ON COLUMN auth.user_organizations.is_current IS 'Whether this is a current affiliation';

CREATE INDEX idx_auth_user_organizations_user ON auth.user_organizations (user_id);
CREATE INDEX idx_auth_user_organizations_wikidata ON auth.user_organizations (wikidata_qid)
    WHERE wikidata_qid IS NOT NULL;
CREATE INDEX idx_auth_user_organizations_current ON auth.user_organizations (user_id, is_current)
    WHERE is_current = true;

-- =============================================================================
-- Block List
-- =============================================================================

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

    blocker_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    blocked_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    reason TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_blocks UNIQUE (blocker_id, blocked_id),
    CONSTRAINT chk_auth_blocks_not_self CHECK (blocker_id != blocked_id)
);

COMMENT ON TABLE auth.blocks IS 'User block list';

CREATE INDEX idx_auth_blocks_blocker ON auth.blocks (blocker_id);
CREATE INDEX idx_auth_blocks_blocked ON auth.blocks (blocked_id);

-- =============================================================================
-- Mute List
-- =============================================================================

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

    muter_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    muted_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_mutes UNIQUE (muter_id, muted_id),
    CONSTRAINT chk_auth_mutes_not_self CHECK (muter_id != muted_id)
);

COMMENT ON TABLE auth.mutes IS 'User mute list';

CREATE INDEX idx_auth_mutes_muter ON auth.mutes (muter_id);
CREATE INDEX idx_auth_mutes_muted ON auth.mutes (muted_id);

-- =============================================================================
-- Password Reset Tokens
-- =============================================================================

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

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_password_resets_token UNIQUE (token_hash)
);

COMMENT ON TABLE auth.password_resets IS 'Password reset tokens';

CREATE INDEX idx_auth_password_resets_user ON auth.password_resets (user_id);
CREATE INDEX idx_auth_password_resets_expires ON auth.password_resets (expires_at) WHERE used_at IS NULL;

-- =============================================================================
-- Email Verification Tokens
-- =============================================================================

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

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    email auth.email NOT NULL,
    token_hash TEXT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_auth_email_verifications_token UNIQUE (token_hash)
);

COMMENT ON TABLE auth.email_verifications IS 'Email verification tokens';

CREATE INDEX idx_auth_email_verifications_user ON auth.email_verifications (user_id);

-- =============================================================================
-- User Scripts
-- =============================================================================

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

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

    name public.nonempty_text NOT NULL,
    slug public.slug 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,

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

    CONSTRAINT uq_auth_user_scripts_slug UNIQUE (user_id, slug)
);

COMMENT ON TABLE auth.user_scripts IS 'Per-user Rhai scripts';

CREATE INDEX idx_auth_user_scripts_user ON auth.user_scripts (user_id);
CREATE INDEX idx_auth_user_scripts_enabled ON auth.user_scripts (user_id, is_enabled) WHERE is_enabled = true;

-- =============================================================================
-- Server Staff (created here since it references auth.users)
-- =============================================================================

CREATE TABLE server.staff (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    role server.server_role NOT NULL,
    appointed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    appointed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_server_staff_user UNIQUE (user_id)
);

COMMENT ON TABLE server.staff IS 'Server-level administrative staff';

-- =============================================================================
-- Server Prop Library (Global Props)
-- =============================================================================

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

    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 (content layer OR emotion layer, mutually exclusive)
    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),

    -- Default scale factor for dropped props (10% - 1000%)
    default_scale REAL NOT NULL DEFAULT 1.0 CHECK (default_scale >= 0.1 AND default_scale <= 10.0),

    -- Optional JSON Schema for validating state structure
    state_schema JSONB,

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

    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_server_props_slug UNIQUE (slug),
    CONSTRAINT chk_server_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 (mutually exclusive)
    CONSTRAINT chk_server_props_positioning CHECK (
        -- Nothing set (non-avatar prop)
        (default_layer IS NULL AND default_emotion IS NULL AND default_position IS NULL) OR
        -- Content layer prop (skin/clothes/accessories at position 0-8)
        (default_layer IS NOT NULL AND default_emotion IS NULL AND default_position IS NOT NULL) OR
        -- Emotion layer prop (neutral/happy/sad/etc at position 0-8)
        (default_layer IS NULL AND default_emotion IS NOT NULL AND default_position IS NOT NULL)
    )
);

COMMENT ON TABLE server.props IS 'Global prop library (64x64 pixels, center-anchored)';
COMMENT ON COLUMN server.props.state_schema IS 'Optional JSON Schema defining valid state structure for this prop';

CREATE INDEX idx_server_props_tags ON server.props USING GIN (tags);
CREATE INDEX idx_server_props_active ON server.props (is_active) WHERE is_active = true;
CREATE INDEX idx_server_props_public ON server.props (is_public) WHERE is_public = true;

COMMENT ON COLUMN server.props.is_public IS
    'When true, prop appears in the public Server inventory tab. Uses filtered index idx_server_props_public.';

-- =============================================================================
-- Audio Library
-- =============================================================================

CREATE TABLE server.audio (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

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

    asset_path public.asset_path NOT NULL,
    duration_seconds REAL NOT NULL CHECK (duration_seconds > 0),
    is_loopable BOOLEAN NOT NULL DEFAULT false,

    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(),

    CONSTRAINT uq_server_audio_slug UNIQUE (slug)
);

COMMENT ON TABLE server.audio IS 'Global audio library';

CREATE INDEX idx_server_audio_category ON server.audio (category);
CREATE INDEX idx_server_audio_tags ON server.audio USING GIN (tags);

-- =============================================================================
-- Reserved Names
-- =============================================================================

CREATE TABLE server.reserved_names (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name TEXT NOT NULL,
    name_type server.reserved_name_type NOT NULL,
    reason TEXT,

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

COMMENT ON TABLE server.reserved_names IS 'Names reserved from use by users';

CREATE UNIQUE INDEX uq_server_reserved_names ON server.reserved_names (lower(name), name_type);

-- =============================================================================
-- Server Scripts
-- =============================================================================

CREATE TABLE server.scripts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name public.nonempty_text NOT NULL,
    slug public.slug 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_user_login BOOLEAN NOT NULL DEFAULT false,
    run_on_user_logout BOOLEAN NOT NULL DEFAULT false,
    run_on_registration BOOLEAN NOT NULL DEFAULT false,
    run_on_server_shutdown 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(),

    CONSTRAINT uq_server_scripts_slug UNIQUE (slug)
);

COMMENT ON TABLE server.scripts IS 'Server-wide Rhai scripts';

CREATE INDEX idx_server_scripts_enabled ON server.scripts (is_enabled) WHERE is_enabled = true;

-- =============================================================================
-- User Inventory (moved from props.inventory)
-- =============================================================================
-- User inventory stores props owned by users (worn on avatar or in bag).
-- Inventory items reference a source: server prop, realm prop, or user upload.
-- The denormalized prop_name/prop_asset_path are cached at acquisition time.
-- =============================================================================

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

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

    -- Source of the prop (at least one required)
    server_prop_id UUID REFERENCES server.props(id) ON DELETE SET NULL,
    realm_prop_id UUID,  -- FK added in 030_realm.sql
    upload_id UUID,      -- Future: user uploads

    -- Denormalized display info (cached at acquisition)
    prop_name TEXT NOT NULL,
    prop_asset_path public.asset_path NOT NULL,
    layer server.avatar_layer,
    position SMALLINT CHECK (position IS NULL OR position BETWEEN 0 AND 8),

    -- Provenance chain for tracking history
    provenance JSONB NOT NULL DEFAULT '[]',
    origin server.prop_origin NOT NULL,

    -- Behavioral flags (cached from source at acquisition)
    is_transferable BOOLEAN NOT NULL DEFAULT true,
    is_portable BOOLEAN NOT NULL DEFAULT true,
    is_droppable BOOLEAN NOT NULL DEFAULT true,

    -- Public state columns (persist through transfer)
    server_state JSONB NOT NULL DEFAULT '{}',
    realm_state JSONB NOT NULL DEFAULT '{}',
    user_state JSONB NOT NULL DEFAULT '{}',

    -- Private state columns (cleared on transfer)
    server_private_state JSONB NOT NULL DEFAULT '{}',
    realm_private_state JSONB NOT NULL DEFAULT '{}',
    user_private_state JSONB NOT NULL DEFAULT '{}',

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

    -- At least one source must be present
    CONSTRAINT chk_auth_inventory_has_source CHECK (
        server_prop_id IS NOT NULL OR realm_prop_id IS NOT NULL OR upload_id IS NOT NULL
    )
);

COMMENT ON TABLE auth.inventory IS 'User-owned props (denormalized for performance)';
COMMENT ON COLUMN auth.inventory.provenance IS 'Array of {from_user, timestamp, method} objects';
COMMENT ON COLUMN auth.inventory.server_state IS 'Public state visible to everyone, persists through transfer';
COMMENT ON COLUMN auth.inventory.realm_state IS 'Public state visible to realm members, persists through transfer';
COMMENT ON COLUMN auth.inventory.user_state IS 'Public state visible to item inspectors, persists through transfer';
COMMENT ON COLUMN auth.inventory.server_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.realm_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.user_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.updated_at IS 'Timestamp of last modification (auto-updated by trigger)';

CREATE INDEX idx_auth_inventory_user ON auth.inventory (user_id);
CREATE INDEX idx_auth_inventory_server_prop ON auth.inventory (server_prop_id)
    WHERE server_prop_id IS NOT NULL;
CREATE INDEX idx_auth_inventory_realm_prop ON auth.inventory (realm_prop_id)
    WHERE realm_prop_id IS NOT NULL;
CREATE INDEX idx_inventory_server_state ON auth.inventory USING GIN (server_state)
    WHERE server_state != '{}';
CREATE INDEX idx_inventory_realm_state ON auth.inventory USING GIN (realm_state)
    WHERE realm_state != '{}';

-- =============================================================================
-- User Avatars (moved from props.avatars)
-- =============================================================================
-- Avatar configurations per user (up to 10 slots: 0-9).
-- Uses 15 columns per layer, 135 total for the 9-position grid system.
-- =============================================================================

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

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    slot_number SMALLINT NOT NULL CHECK (slot_number >= 0 AND slot_number <= 9),

    name public.display_name,
    is_default BOOLEAN NOT NULL DEFAULT false,
    last_emotion SMALLINT NOT NULL DEFAULT 0 CHECK (last_emotion >= 0 AND last_emotion <= 11),

    -- Content layers: skin (3), clothes (3), accessories (3) = 9 layers x 9 positions = 81 potential slots
    -- But we use 3 layers x 9 positions = 27 content slots
    l_skin_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_skin_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    l_clothes_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_clothes_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    l_accessories_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    l_accessories_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    -- Emotion layers: 12 emotions x 9 positions = 108 slots
    e_neutral_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_neutral_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_happy_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_happy_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_sad_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sad_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_angry_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_angry_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_surprised_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_surprised_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_thinking_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_thinking_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_laughing_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_laughing_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_crying_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_crying_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_love_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_love_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_confused_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_confused_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_sleeping_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_sleeping_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

    e_wink_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
    e_wink_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,

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

    CONSTRAINT uq_auth_avatars_slot UNIQUE (user_id, slot_number)
);

COMMENT ON TABLE auth.avatars IS 'User avatar configurations with 135 prop slots';

CREATE INDEX idx_auth_avatars_user ON auth.avatars (user_id);
CREATE INDEX idx_auth_avatars_default ON auth.avatars (user_id, is_default) WHERE is_default = true;

-- =============================================================================
-- Active Avatars (moved from props.active_avatars)
-- =============================================================================
-- Tracks which avatar a user is currently using in each realm.
-- =============================================================================

CREATE TABLE auth.active_avatars (
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    realm_id UUID NOT NULL,  -- FK added in 030_realm.sql after realm.realms exists
    avatar_id UUID REFERENCES auth.avatars(id) ON DELETE SET NULL,

    -- User-selected avatars from avatar stores (lower priority than custom avatar)
    selected_server_avatar_id UUID,  -- FK added in 025_server_avatars.sql
    selected_realm_avatar_id UUID,   -- FK added in 035_realm_avatars.sql

    current_emotion server.emotion_state NOT NULL DEFAULT 'happy',

    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY (user_id, realm_id)
);

COMMENT ON TABLE auth.active_avatars IS 'Current avatar per user per realm';
COMMENT ON COLUMN auth.active_avatars.avatar_id IS
    'User custom avatar (highest priority, nullable for users without custom avatars)';
COMMENT ON COLUMN auth.active_avatars.selected_server_avatar_id IS
    'User-selected server avatar (from avatar store), lower priority than custom avatar';
COMMENT ON COLUMN auth.active_avatars.selected_realm_avatar_id IS
    'User-selected realm avatar (from avatar store), higher priority than server selection';

-- =============================================================================
-- Server-Level Moderation: IP Bans
-- =============================================================================

CREATE TABLE server.ip_bans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    ip_address INET NOT NULL,
    ip_range CIDR,  -- Optional CIDR range for subnet bans

    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

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

    CONSTRAINT uq_server_ip_bans_address UNIQUE (ip_address)
);

COMMENT ON TABLE server.ip_bans IS 'Server-wide IP address bans';

CREATE INDEX idx_server_ip_bans_range ON server.ip_bans USING GIST (ip_range inet_ops)
    WHERE ip_range IS NOT NULL;
CREATE INDEX idx_server_ip_bans_expires ON server.ip_bans (expires_at)
    WHERE expires_at IS NOT NULL;

-- =============================================================================
-- Server-Level Moderation: User Bans
-- =============================================================================

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

    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()
);

COMMENT ON TABLE server.bans IS 'Server-wide user bans';

CREATE INDEX idx_server_bans_user ON server.bans (user_id);
CREATE INDEX idx_server_bans_active ON server.bans (user_id, is_active) WHERE is_active = true;
CREATE INDEX idx_server_bans_expires ON server.bans (expires_at) WHERE expires_at IS NOT NULL AND is_active = true;

-- =============================================================================
-- Server-Level Moderation: Server Mutes
-- =============================================================================

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

    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 server.mutes IS 'Server-wide user mutes (cannot send messages anywhere)';

CREATE INDEX idx_server_mutes_user ON server.mutes (user_id);
CREATE INDEX idx_server_mutes_active ON server.mutes (user_id, is_active) WHERE is_active = true;

-- =============================================================================
-- Server-Level Moderation: Content Filters
-- =============================================================================

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

    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 server.content_filters IS 'Server-wide content filtering rules';

CREATE INDEX idx_server_content_filters_active ON server.content_filters (is_active) WHERE is_active = true;

-- =============================================================================
-- Server-Level Moderation: Action Log
-- =============================================================================

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

    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 server.moderation_actions IS 'Log of server-level moderation actions';

CREATE INDEX idx_server_moderation_actions_target ON server.moderation_actions (target_user_id);
CREATE INDEX idx_server_moderation_actions_moderator ON server.moderation_actions (moderator_id);
CREATE INDEX idx_server_moderation_actions_type ON server.moderation_actions (action_type);
CREATE INDEX idx_server_moderation_actions_created ON server.moderation_actions (created_at DESC);

COMMIT;