-- =============================================================================
-- MIGRATION 013: Zoom sessions infrastructure
-- PURPOSE:
--   (1) course_config          — per-course credentials and settings (key-value)
--   (2) course_professor_levels — which professor is responsible for which level
--   (3) sessions               — convocatorias created by professors
--   (4) session_participants   — pre-registered participants with unique Zoom links
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. COURSE_CONFIG
--    Generic key-value store for per-course configuration.
--    Zoom keys: zoom_account_id, zoom_client_id, zoom_client_secret
--    SMTP keys: smtp_host, smtp_user, smtp_password, smtp_from
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.course_config (
    course_id    INT          NOT NULL REFERENCES public.courses(course_id) ON DELETE CASCADE,
    config_key   VARCHAR(100) NOT NULL,
    config_value TEXT,
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (course_id, config_key)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.course_config TO "elfi1953_stc-eu";


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. COURSE_PROFESSOR_LEVELS
--    Maps each professor to the level(s) they are responsible for in a course.
--    Used to assign Zoom breakout rooms and set alternative_hosts.
--    level_id NULL means the professor covers all levels (general co-host).
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.course_professor_levels (
    id           SERIAL PRIMARY KEY,
    course_id    INT NOT NULL REFERENCES public.courses(course_id)  ON DELETE CASCADE,
    professor_id INT NOT NULL REFERENCES public.partners(partner_id) ON DELETE CASCADE,
    level_id     INT REFERENCES public.levels(level_id) ON DELETE SET NULL,
    UNIQUE (course_id, professor_id, level_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.course_professor_levels TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.course_professor_levels_id_seq TO "elfi1953_stc-eu";


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. SESSIONS
--    A session is a Zoom meeting convocatoria created by a professor.
--    zoom_meeting_id and zoom_join_url are populated after Zoom API call.
-- ─────────────────────────────────────────────────────────────────────────────
DO $$ BEGIN
    CREATE TYPE session_status AS ENUM ('SCHEDULED', 'ACTIVE', 'COMPLETED', 'CANCELLED');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS public.sessions (
    session_id      SERIAL       PRIMARY KEY,
    course_id       INT          NOT NULL REFERENCES public.courses(course_id),
    created_by      INT          NOT NULL REFERENCES public.partners(partner_id),
    title           VARCHAR(200) NOT NULL,
    scheduled_at    TIMESTAMPTZ  NOT NULL,
    duration_min    INT          NOT NULL DEFAULT 90,
    zoom_meeting_id VARCHAR(50),
    zoom_join_url   TEXT,
    status          session_status NOT NULL DEFAULT 'SCHEDULED',
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

GRANT SELECT, INSERT, UPDATE ON public.sessions TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.sessions_session_id_seq TO "elfi1953_stc-eu";


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. SESSION_PARTICIPANTS
--    One row per participant per session.
--    breakout_room = level name (NULL = main room, i.e. level 0).
--    zoom_join_url is the unique per-registrant link returned by Zoom API.
-- ─────────────────────────────────────────────────────────────────────────────
DO $$ BEGIN
    CREATE TYPE participant_type AS ENUM ('STUDENT', 'TUTOR', 'PROFESSOR');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS public.session_participants (
    id                 SERIAL           PRIMARY KEY,
    session_id         INT              NOT NULL REFERENCES public.sessions(session_id) ON DELETE CASCADE,
    participant_type   participant_type NOT NULL,
    partner_id         INT              REFERENCES public.partners(partner_id),
    email              TEXT             NOT NULL,
    full_name          TEXT             NOT NULL,
    breakout_room      VARCHAR(100),
    zoom_registrant_id VARCHAR(100),
    zoom_join_url      TEXT,
    notification_sent  BOOLEAN          NOT NULL DEFAULT false,
    UNIQUE (session_id, email)
);

GRANT SELECT, INSERT, UPDATE ON public.session_participants TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.session_participants_id_seq TO "elfi1953_stc-eu";