backend work for the events Feature

[guillermoscript]

SQL Definitions

1.1. Creating Enum Types and Tables

-- Enum for event sources/platforms
CREATE TYPE public.event_source AS ENUM ('twitch', 'youtube', 'webinar', 'other');

-- Enum for event status
CREATE TYPE public.event_status AS ENUM ('scheduled', 'ongoing', 'completed', 'canceled');

-- Table to store event types or categories
CREATE TABLE public.event_types (
    event_type_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
) TABLESPACE pg_default;

-- Events Table
CREATE TABLE public.events (
    event_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    event_source public.event_source NOT NULL,
    stream_url TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE,
    status public.event_status NOT NULL DEFAULT 'scheduled'::event_status,
    category_id INTEGER,
    associated_course_id INTEGER,

    CONSTRAINT events_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.event_types (event_type_id) ON DELETE SET NULL,
    CONSTRAINT events_associated_course_id_fkey FOREIGN KEY (associated_course_id) REFERENCES public.courses (course_id) ON DELETE SET NULL,
    CONSTRAINT events_time_check CHECK (end_time > start_time)
) TABLESPACE pg_default;

-- Event Subscriptions Table
CREATE TABLE public.event_subscriptions (
    subscription_id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    event_type_id INTEGER,
    subscribe_all BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT event_subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE CASCADE,
    CONSTRAINT event_subscriptions_event_type_id_fkey FOREIGN KEY (event_type_id) REFERENCES public.event_types (event_type_id) ON DELETE CASCADE,
    CONSTRAINT unique_subscription UNIQUE (user_id, event_type_id)
) TABLESPACE pg_default;

1.2. Triggers and Functions

To automate notifications for upcoming events, we can create a scheduled job using PostgreSQL's pg_cron extension or utilize an external scheduling system. However, for illustration, here's a simplified trigger-based approach that notifies users when a new event is inserted.

1.2.1. Function to Manage Event Notifications

CREATE OR REPLACE FUNCTION public.notify_users_of_new_event()
RETURNS TRIGGER AS $$
BEGIN
    -- Insert notification for users subscribed to the event's type
    INSERT INTO public.notifications (user_id, notification_type, message, link, created_at)
    SELECT 
        es.user_id,
        'event_notification',
        CONCAT('New Event: ', NEW.title, ' is scheduled on ', NEW.event_source, ' starting at ', NEW.start_time),
        NEW.stream_url,
        NOW()
    FROM public.event_subscriptions es
    WHERE es.event_type_id = NEW.category_id OR es.subscribe_all = TRUE;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1.2.2. Trigger for New Events

CREATE TRIGGER trigger_notify_users_on_event_insert
AFTER INSERT ON public.events
FOR EACH ROW
EXECUTE FUNCTION public.notify_users_of_new_event();