CREATE TYPE notification_type AS ENUM (
    'mention',
    'invitation',
    'role_change',
    'room_created',
    'room_deleted',
    'system_announcement'
);

CREATE TABLE IF NOT EXISTS room_notifications (
    id UUID PRIMARY KEY,
    room UUID,
    project UUID,
    user_id UUID,
    notification_type VARCHAR(255) NOT NULL,
    related_message_id UUID,
    related_user_id UUID,
    related_room_id UUID,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    metadata JSONB,
    is_read BOOLEAN NOT NULL DEFAULT false,
    is_archived BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL,
    read_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ
);

CREATE INDEX idx_room_notifications_user_id_is_read ON room_notifications (user_id, is_read);
CREATE INDEX idx_room_notifications_user_id_created_at ON room_notifications (user_id, created_at);
CREATE INDEX idx_room_notifications_expires_at ON room_notifications (expires_at);