-- Chattyness Scene Schema Tables
-- PostgreSQL 18 with PostGIS
--
-- Scene-level runtime state: instances, members, spots, loose props, decorations
-- Load via: psql -f schema/tables/045_scene.sql

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Instances (renamed from realm.channels)
-- =============================================================================
-- Instances are ephemeral scene rooms where users can interact.
-- Each scene can have multiple instances (public default + private rooms).
-- =============================================================================

CREATE TABLE scene.instances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,

    instance_type scene.instance_type NOT NULL DEFAULT 'public',
    name public.display_name,

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

    max_users INTEGER NOT NULL DEFAULT 50 CHECK (max_users > 0 AND max_users <= 1000),
    current_user_count INTEGER NOT NULL DEFAULT 0 CHECK (current_user_count >= 0),

    expires_at TIMESTAMPTZ,

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

COMMENT ON TABLE scene.instances IS 'Ephemeral scene rooms where users interact';

CREATE INDEX idx_scene_instances_scene ON scene.instances (scene_id);
CREATE INDEX idx_scene_instances_type ON scene.instances (scene_id, instance_type);
CREATE INDEX idx_scene_instances_expires ON scene.instances (expires_at) WHERE expires_at IS NOT NULL;

-- =============================================================================
-- Instance Members (renamed from realm.channel_members)
-- =============================================================================
-- Users currently present in an instance with their positions.
-- Note: instance_id is actually scene_id in this system (scenes are used directly as instances).
-- Guests are regular users with the 'guest' tag in auth.users.
-- =============================================================================

CREATE TABLE scene.instance_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

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

    position public.virtual_point NOT NULL DEFAULT ST_SetSRID(ST_MakePoint(400, 300), 0),

    facing_direction SMALLINT NOT NULL DEFAULT 0 CHECK (facing_direction >= 0 AND facing_direction < 360),
    is_moving BOOLEAN NOT NULL DEFAULT false,
    is_afk BOOLEAN NOT NULL DEFAULT false,

    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_moved_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT uq_scene_instance_members_user UNIQUE (instance_id, user_id)
);

COMMENT ON TABLE scene.instance_members IS 'Users in an instance with positions (guests are users with guest tag)';

CREATE INDEX idx_scene_instance_members_instance ON scene.instance_members (instance_id);
CREATE INDEX idx_scene_instance_members_user ON scene.instance_members (user_id);
CREATE INDEX idx_scene_instance_members_position ON scene.instance_members USING GIST (position);

-- =============================================================================
-- Instance Invites (renamed from realm.channel_invites)
-- =============================================================================

CREATE TABLE scene.instance_invites (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,
    invited_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    invited_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    accepted_at TIMESTAMPTZ,
    declined_at TIMESTAMPTZ,

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

    CONSTRAINT uq_scene_instance_invites UNIQUE (instance_id, invited_user_id),
    CONSTRAINT chk_scene_instance_invites_not_self CHECK (invited_by != invited_user_id)
);

COMMENT ON TABLE scene.instance_invites IS 'Private instance invitations';

CREATE INDEX idx_scene_instance_invites_instance ON scene.instance_invites (instance_id);
CREATE INDEX idx_scene_instance_invites_user ON scene.instance_invites (invited_user_id);
CREATE INDEX idx_scene_instance_invites_pending ON scene.instance_invites (invited_user_id, expires_at)
    WHERE accepted_at IS NULL AND declined_at IS NULL;

-- =============================================================================
-- Spots (moved from realm.spots)
-- =============================================================================

CREATE TABLE scene.spots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,

    name TEXT,
    slug public.slug,

    region GEOMETRY(GEOMETRY, 0) NOT NULL,

    spot_type scene.spot_type NOT NULL DEFAULT 'normal',

    destination_scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
    destination_position GEOMETRY(POINT, 0),

    current_state SMALLINT NOT NULL DEFAULT 0,
    sort_order INTEGER NOT NULL DEFAULT 0,

    is_visible BOOLEAN NOT NULL DEFAULT true,
    is_active BOOLEAN NOT NULL DEFAULT true,

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

    CONSTRAINT uq_scene_spots_slug UNIQUE (scene_id, slug)
);

COMMENT ON TABLE scene.spots IS 'Interactive regions in scenes';

CREATE INDEX idx_scene_spots_scene ON scene.spots (scene_id);
CREATE INDEX idx_scene_spots_region ON scene.spots USING GIST (region);
CREATE INDEX idx_scene_spots_active ON scene.spots (scene_id, is_active) WHERE is_active = true;

-- =============================================================================
-- Spot States (moved from realm.spot_states)
-- =============================================================================

CREATE TABLE scene.spot_states (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    spot_id UUID NOT NULL REFERENCES scene.spots(id) ON DELETE CASCADE,

    state_number SMALLINT NOT NULL,

    asset_path public.asset_path,
    offset_x REAL NOT NULL DEFAULT 0,
    offset_y REAL NOT NULL DEFAULT 0,

    audio_id UUID REFERENCES server.audio(id) ON DELETE SET NULL,

    CONSTRAINT uq_scene_spot_states UNIQUE (spot_id, state_number)
);

COMMENT ON TABLE scene.spot_states IS 'Visual configurations for spot states';

CREATE INDEX idx_scene_spot_states_spot ON scene.spot_states (spot_id);

-- =============================================================================
-- Scene Scripts (moved from realm.scene_scripts)
-- =============================================================================

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

    scene_id UUID NOT NULL REFERENCES realm.scenes(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_enter BOOLEAN NOT NULL DEFAULT false,
    handle_private_instances 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 scene.scripts IS 'Rhai scripts for scenes';

CREATE INDEX idx_scene_scripts_scene ON scene.scripts (scene_id);
CREATE INDEX idx_scene_scripts_enabled ON scene.scripts (scene_id, is_enabled) WHERE is_enabled = true;

-- =============================================================================
-- Loose Props (moved from props.loose_props)
-- =============================================================================
-- Props that exist at a position in an instance, not worn by anyone.
-- Can be picked up by users.
-- =============================================================================

CREATE TABLE scene.loose_props (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,

    -- Source of the prop (either server or realm library)
    server_prop_id UUID REFERENCES server.props(id) ON DELETE CASCADE,
    realm_prop_id UUID REFERENCES realm.props(id) ON DELETE CASCADE,

    -- Position in scene (PostGIS point, SRID 0)
    position public.virtual_point NOT NULL,

    -- Scale factor (10% - 1000%), inherited from prop definition at drop time
    scale REAL NOT NULL DEFAULT 1.0 CHECK (scale >= 0.1 AND scale <= 10.0),

    -- Who dropped it (NULL = spawned by system/script)
    dropped_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,

    -- Auto-decay
    expires_at TIMESTAMPTZ,  -- NULL = permanent

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Must reference exactly one source
    CONSTRAINT chk_scene_loose_props_source CHECK (
        (server_prop_id IS NOT NULL AND realm_prop_id IS NULL) OR
        (server_prop_id IS NULL AND realm_prop_id IS NOT NULL)
    )
);

COMMENT ON TABLE scene.loose_props IS 'Props dropped in instances that can be picked up';
COMMENT ON COLUMN scene.loose_props.position IS 'Location in scene as PostGIS point (SRID 0)';
COMMENT ON COLUMN scene.loose_props.expires_at IS 'When prop auto-decays (NULL = permanent)';

CREATE INDEX idx_scene_loose_props_instance ON scene.loose_props (instance_id);
CREATE INDEX idx_scene_loose_props_expires ON scene.loose_props (expires_at)
    WHERE expires_at IS NOT NULL;

-- Spatial index for finding props near a position
CREATE INDEX idx_scene_loose_props_position ON scene.loose_props
    USING GIST (position);

-- =============================================================================
-- Scene Decorations (moved from props.scene_decorations)
-- =============================================================================
-- Props placed in scenes by builders/owners as permanent decoration.
-- Can optionally be copied by users into their inventory.
-- =============================================================================

CREATE TABLE scene.decorations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,

    -- Source of the prop
    server_prop_id UUID REFERENCES server.props(id) ON DELETE CASCADE,
    realm_prop_id UUID REFERENCES realm.props(id) ON DELETE CASCADE,

    -- Position and display
    position public.virtual_point NOT NULL,
    z_index INTEGER NOT NULL DEFAULT 0,
    scale REAL NOT NULL DEFAULT 1.0 CHECK (scale > 0 AND scale <= 10),
    rotation SMALLINT NOT NULL DEFAULT 0 CHECK (rotation >= 0 AND rotation < 360),
    opacity public.percentage NOT NULL DEFAULT 1.0,

    -- Interaction
    is_copyable BOOLEAN NOT NULL DEFAULT false,  -- Users can copy to inventory
    click_action JSONB,  -- Optional click behavior

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

    -- Must reference exactly one source
    CONSTRAINT chk_scene_decorations_source CHECK (
        (server_prop_id IS NOT NULL AND realm_prop_id IS NULL) OR
        (server_prop_id IS NULL AND realm_prop_id IS NOT NULL)
    )
);

COMMENT ON TABLE scene.decorations IS 'Permanent prop decorations placed in scenes';
COMMENT ON COLUMN scene.decorations.is_copyable IS 'If true, users can copy this prop to their inventory';
COMMENT ON COLUMN scene.decorations.click_action IS 'Optional JSON action config for click behavior';

CREATE INDEX idx_scene_decorations_scene ON scene.decorations (scene_id);

-- Spatial index for rendering props in view
CREATE INDEX idx_scene_decorations_position ON scene.decorations
    USING GIST (position);

COMMIT;