-- 124_room_invitations.sql
-- v0.9.7: Room invitations by link (token-based, expires 7 days)

CREATE TABLE IF NOT EXISTS public.room_invitations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL REFERENCES public.rooms(id) ON DELETE CASCADE,
    inviter_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    invitee_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
    token UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_room_invitations_room_id ON public.room_invitations(room_id);
CREATE INDEX idx_room_invitations_token ON public.room_invitations(token) WHERE status = 'pending';
CREATE INDEX idx_room_invitations_expires_at ON public.room_invitations(expires_at) WHERE status = 'pending';