DatabaseSchema.sql

-- ============================================================================
-- Voxel Game Database Schema for Supabase
-- ============================================================================
-- This SQL file creates the database schema for the multiplayer voxel game
-- using Supabase/PostgreSQL. Run this in your Supabase SQL editor or via
-- the Supabase CLI.
--
-- Tables:
-- - rooms: Game sessions/rooms
-- - players: Players connected to rooms
-- - chunks: World chunk data and updates
-- - events: Game events and messages
-- - player_skills: Player skill progression (optional extension)
-- - player_inventory: Player inventory data (optional extension)
-- ============================================================================

-- Enable Row Level Security
ALTER DATABASE postgres SET "app.jwt_secret" TO 'your-super-secret-jwt-token-with-at-least-32-characters-long';

-- ============================================================================
-- ROOMS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.rooms (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(6) UNIQUE NOT NULL, -- 6-character room code
    created_by UUID NOT NULL,        -- Host player ID
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    max_players INTEGER NOT NULL DEFAULT 8,
    current_players INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'waiting', -- waiting, active, ended
    world_seed VARCHAR(255) NOT NULL DEFAULT 'default',
    
    -- Constraints
    CONSTRAINT valid_room_code CHECK (code ~ '^[A-Z0-9]{6}$'),
    CONSTRAINT valid_max_players CHECK (max_players >= 1 AND max_players <= 16),
    CONSTRAINT valid_status CHECK (status IN ('waiting', 'active', 'ended'))
);

-- Indexes for rooms
CREATE INDEX IF NOT EXISTS idx_rooms_code ON public.rooms(code);
CREATE INDEX IF NOT EXISTS idx_rooms_created_by ON public.rooms(created_by);
CREATE INDEX IF NOT EXISTS idx_rooms_status ON public.rooms(status);

-- ============================================================================
-- PLAYERS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.players (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,            -- Unique player identifier
    name VARCHAR(50) NOT NULL,        -- Player display name
    pos JSONB NOT NULL DEFAULT '{"x": 0, "y": 0, "z": 0}', -- Player position
    rot JSONB NOT NULL DEFAULT '{"x": 0, "y": 0, "z": 0}', -- Player rotation
    last_active TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    connected BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Unique constraint to prevent duplicate players in same room
    CONSTRAINT unique_player_in_room UNIQUE (room_id, user_id),
    CONSTRAINT valid_name_length CHECK (length(name) >= 1 AND length(name) <= 50),
    CONSTRAINT valid_position CHECK (
        pos ? 'x' AND pos ? 'y' AND pos ? 'z' AND
        CAST(pos->>'x' AS NUMERIC) BETWEEN -10000 AND 10000 AND
        CAST(pos->>'y' AS NUMERIC) BETWEEN -1000 AND 2000 AND
        CAST(pos->>'z' AS NUMERIC) BETWEEN -10000 AND 10000
    ),
    CONSTRAINT valid_rotation CHECK (
        rot ? 'x' AND rot ? 'y' AND rot ? 'z'
    )
);

-- Indexes for players
CREATE INDEX IF NOT EXISTS idx_players_room_id ON public.players(room_id);
CREATE INDEX IF NOT EXISTS idx_players_user_id ON public.players(user_id);
CREATE INDEX IF NOT EXISTS idx_players_last_active ON public.players(last_active);
CREATE INDEX IF NOT EXISTS idx_players_connected ON public.players(connected);

-- ============================================================================
-- CHUNKS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.chunks (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    coord_x INTEGER NOT NULL,         -- Chunk X coordinate
    coord_z INTEGER NOT NULL,         -- Chunk Z coordinate
    data JSONB NOT NULL,              -- Chunk block data (RLE or full grid)
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    version INTEGER NOT NULL DEFAULT 1, -- Schema version for migrations
    
    -- Unique chunk per room
    CONSTRAINT unique_chunk_per_room UNIQUE (room_id, coord_x, coord_z),
    CONSTRAINT valid_chunk_coords CHECK (coord_x BETWEEN -1000 AND 1000 AND coord_z BETWEEN -1000 AND 1000),
    CONSTRAINT valid_version CHECK (version >= 1)
);

-- Indexes for chunks
CREATE INDEX IF NOT EXISTS idx_chunks_room_id ON public.chunks(room_id);
CREATE INDEX IF NOT EXISTS idx_chunks_coords ON public.chunks(coord_x, coord_z);
CREATE INDEX IF NOT EXISTS idx_chunks_updated_at ON public.chunks(updated_at);

-- ============================================================================
-- EVENTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.events (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,        -- Event type: block_delta, xp_event, chat, etc.
    payload JSONB NOT NULL,           -- Event data
    ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Constraints
    CONSTRAINT valid_event_type CHECK (type IN ('block_delta', 'xp_event', 'chat', 'player_join', 'player_leave')),
    CONSTRAINT payload_not_empty CHECK (jsonb_typeof(payload) = 'object')
);

-- Indexes for events
CREATE INDEX IF NOT EXISTS idx_events_room_id ON public.events(room_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON public.events(type);
CREATE INDEX IF NOT EXISTS idx_events_ts ON public.events(ts);
CREATE INDEX IF NOT EXISTS idx_events_processed ON public.events(processed);

-- ============================================================================
-- PLAYER SKILLS TABLE (Optional Extension)
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.player_skills (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,            -- Player ID
    skill VARCHAR(50) NOT NULL,       -- Skill name: mining, woodcutting, fishing, etc.
    level INTEGER NOT NULL DEFAULT 1,
    experience BIGINT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT unique_player_skill UNIQUE (room_id, user_id, skill),
    CONSTRAINT valid_level CHECK (level >= 1 AND level <= 99),
    CONSTRAINT valid_experience CHECK (experience >= 0 AND experience <= 13034431), -- OSRS max XP
    CONSTRAINT valid_skill_name CHECK (skill IN ('mining', 'woodcutting', 'fishing', 'smithing', 'crafting', 'combat'))
);

-- Indexes for player skills
CREATE INDEX IF NOT EXISTS idx_player_skills_room_id ON public.player_skills(room_id);
CREATE INDEX IF NOT EXISTS idx_player_skills_user_id ON public.player_skills(user_id);
CREATE INDEX IF NOT EXISTS idx_player_skills_skill ON public.player_skills(skill);

-- ============================================================================
-- PLAYER INVENTORY TABLE (Optional Extension)
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.player_inventory (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,            -- Player ID
    slot INTEGER NOT NULL,            -- Inventory slot (0-35 for basic inventory)
    item_id INTEGER NOT NULL,         -- Item type ID
    quantity INTEGER NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT unique_inventory_slot UNIQUE (room_id, user_id, slot),
    CONSTRAINT valid_slot CHECK (slot >= 0 AND slot <= 99), -- Extended inventory
    CONSTRAINT valid_quantity CHECK (quantity >= 1),
    CONSTRAINT valid_item_id CHECK (item_id >= 0)
);

-- Indexes for inventory
CREATE INDEX IF NOT EXISTS idx_inventory_room_id ON public.player_inventory(room_id);
CREATE INDEX IF NOT EXISTS idx_inventory_user_id ON public.player_inventory(user_id);
CREATE INDEX IF NOT EXISTS idx_inventory_slot ON public.player_inventory(slot);

-- ============================================================================
-- REALTIME SUBSCRIPTIONS
-- ============================================================================
-- Enable realtime for relevant tables
ALTER PUBLICATION supabase_realtime ADD TABLE public.rooms;
ALTER PUBLICATION supabase_realtime ADD TABLE public.players;
ALTER PUBLICATION supabase_realtime ADD TABLE public.chunks;
ALTER PUBLICATION supabase_realtime ADD TABLE public.events;

-- ============================================================================
-- ROW LEVEL SECURITY (RLS) POLICIES
-- ============================================================================
-- Enable RLS on all tables
ALTER TABLE public.rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.players ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.player_skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.player_inventory ENABLE ROW LEVEL SECURITY;

-- Rooms policies
CREATE POLICY "Anyone can view active rooms" ON public.rooms
    FOR SELECT USING (status IN ('waiting', 'active'));

CREATE POLICY "Authenticated users can create rooms" ON public.rooms
    FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

CREATE POLICY "Room creators can update their rooms" ON public.rooms
    FOR UPDATE USING (auth.uid() = created_by);

-- Players policies
CREATE POLICY "Players can view players in rooms they're in" ON public.players
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM public.rooms r1, public.rooms r2 
            WHERE r1.id = players.room_id 
            AND r2.id = players.room_id 
            AND r2.id IN (
                SELECT room_id FROM public.players WHERE user_id = auth.uid()
            )
        )
    );

CREATE POLICY "Players can insert themselves into rooms" ON public.players
    FOR INSERT WITH CHECK (user_id = auth.uid());

CREATE POLICY "Players can update their own data" ON public.players
    FOR UPDATE USING (user_id = auth.uid());

-- Chunks policies
CREATE POLICY "Players can view chunks in rooms they're in" ON public.chunks
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM public.players 
            WHERE players.room_id = chunks.room_id 
            AND players.user_id = auth.uid()
        )
    );

CREATE POLICY "Players can update chunks in their rooms" ON public.chunks
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM public.players 
            WHERE players.room_id = chunks.room_id 
            AND players.user_id = auth.uid()
        )
    );

-- Events policies
CREATE POLICY "Players can view events in rooms they're in" ON public.events
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM public.players 
            WHERE players.room_id = events.room_id 
            AND players.user_id = auth.uid()
        )
    );

CREATE POLICY "Authenticated users can insert events" ON public.events
    FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- ============================================================================
-- FUNCTIONS AND TRIGGERS
-- ============================================================================

-- Function to update player last_active timestamp
CREATE OR REPLACE FUNCTION update_player_last_active()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_active = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update last_active
DROP TRIGGER IF EXISTS trigger_update_player_last_active ON public.players;
CREATE TRIGGER trigger_update_player_last_active
    BEFORE UPDATE ON public.players
    FOR EACH ROW
    EXECUTE FUNCTION update_player_last_active();

-- Function to increment/decrement player count in rooms
CREATE OR REPLACE FUNCTION update_room_player_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE public.rooms 
        SET current_players = current_players + 1 
        WHERE id = NEW.room_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE public.rooms 
        SET current_players = current_players - 1 
        WHERE id = OLD.room_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Triggers for player count management
DROP TRIGGER IF EXISTS trigger_insert_player_count ON public.players;
CREATE TRIGGER trigger_insert_player_count
    AFTER INSERT ON public.players
    FOR EACH ROW
    EXECUTE FUNCTION update_room_player_count();

DROP TRIGGER IF EXISTS trigger_delete_player_count ON public.players;
CREATE TRIGGER trigger_delete_player_count
    AFTER DELETE ON public.players
    FOR EACH ROW
    EXECUTE FUNCTION update_room_player_count();

-- ============================================================================
-- SAMPLE DATA AND TESTING
-- ============================================================================

-- Insert a sample room for testing
INSERT INTO public.rooms (code, created_by, status, world_seed) 
VALUES ('TEST01', gen_random_uuid(), 'waiting', 'test-seed-123')
ON CONFLICT (code) DO NOTHING;

-- Function to generate sample data (for development/testing only)
CREATE OR REPLACE FUNCTION create_sample_data()
RETURNS VOID AS $$
DECLARE
    test_room_id BIGINT;
    test_user_id UUID := gen_random_uuid();
BEGIN
    -- Create test room
    INSERT INTO public.rooms (code, created_by, status)
    VALUES ('DEMO' || LPAD((RANDOM() * 999999)::INTEGER::TEXT, 6, '0'), test_user_id, 'waiting')
    RETURNING id INTO test_room_id;
    
    -- Add test player
    INSERT INTO public.players (room_id, user_id, name, pos, rot)
    VALUES (test_room_id, test_user_id, 'TestPlayer', '{"x": 0, "y": 64, "z": 0}', '{"x": 0, "y": 0, "z": 0}');
    
    -- Add some sample chunks
    FOR x IN -1..1 LOOP
        FOR z IN -1..1 LOOP
            INSERT INTO public.chunks (room_id, coord_x, coord_z, data)
            VALUES (test_room_id, x, z, '{"blocks": [], "version": 1}');
        END LOOP;
    END LOOP;
    
    RAISE NOTICE 'Sample data created with room_id: %', test_room_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- MAINTENANCE AND CLEANUP
-- ============================================================================

-- Function to clean up old events (keep last 7 days)
CREATE OR REPLACE FUNCTION cleanup_old_events()
RETURNS VOID AS $$
BEGIN
    DELETE FROM public.events 
    WHERE ts < NOW() - INTERVAL '7 days' 
    AND processed = TRUE;
END;
$$ LANGUAGE plpgsql;

-- Function to clean up inactive rooms (older than 1 hour)
CREATE OR REPLACE FUNCTION cleanup_inactive_rooms()
RETURNS VOID AS $$
BEGIN
    DELETE FROM public.rooms 
    WHERE created_at < NOW() - INTERVAL '1 hour' 
    AND current_players = 0 
    AND status != 'active';
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- COMMENTS FOR DOCUMENTATION
-- ============================================================================

COMMENT ON TABLE public.rooms IS 'Game rooms/sessions that players can join';
COMMENT ON TABLE public.players IS 'Players connected to specific rooms with position and status';
COMMENT ON TABLE public.chunks IS 'World chunk data stored as JSON for fast updates';
COMMENT ON TABLE public.events IS 'Game events log for debugging and replay';
COMMENT ON TABLE public.player_skills IS 'Player skill progression for OSRS-style leveling';
COMMENT ON TABLE public.player_inventory IS 'Player inventory data with item slots';

COMMENT ON COLUMN public.rooms.code IS '6-character uppercase room code for easy joining';
COMMENT ON COLUMN public.chunks.data IS 'JSON object containing block data, RLE compressed or full grid';
COMMENT ON COLUMN public.events.payload IS 'Event-specific JSON data (BlockDelta, XpEvent, chat message, etc.)';

-- ============================================================================
-- DEPLOYMENT NOTES
-- ============================================================================
/*
To deploy this schema:

1. In Supabase Dashboard:
   - Go to SQL Editor
   - Copy and paste this entire file
   - Click Run

2. Via Supabase CLI:
   supabase db reset
   supabase db push

3. Set up environment variables:
   - Ensure anon and service role keys are configured
   - Update RLS policies if using different authentication

4. Test the setup:
   SELECT create_sample_data();
   SELECT * FROM rooms;
   SELECT * FROM players;
   
5. Set up realtime subscriptions in your Unity client using the table names above

6. Create database backup before making schema changes
*/