-- Session scheduler: recurring session templates and occurrence exceptions
-- Adds schedule_id + notify_at to sessions table

-- ─── Tables ──────────────────────────────────────────────────────────────────

CREATE TABLE public.session_schedules (
  schedule_id           serial PRIMARY KEY,
  course_id             integer     NOT NULL REFERENCES public.courses(course_id),
  title                 text        NOT NULL,
  -- recurrence: { freq: 'weekly'|'biweekly'|'monthly', day_of_week: 0-6 (Mon=0), day_of_month: 1-31, time: 'HH:MM' }
  recurrence            jsonb       NOT NULL,
  duration_min          integer     NOT NULL DEFAULT 90,
  participant_roles     text[]      NOT NULL DEFAULT '{STUDENT,TUTOR}',
  create_breakout_rooms boolean     NOT NULL DEFAULT true,
  advance_hours         integer     NOT NULL DEFAULT 48,   -- create Zoom meeting X hours before session
  notify_hours_before   integer     NOT NULL DEFAULT 24,   -- send CLASS_REMINDER X hours before session
  active                boolean     NOT NULL DEFAULT true,
  created_by            bigint      NOT NULL REFERENCES public.partners(partner_id),
  created_at            timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE public.session_schedule_exceptions (
  exception_id      serial PRIMARY KEY,
  schedule_id       integer     NOT NULL REFERENCES public.session_schedules(schedule_id) ON DELETE CASCADE,
  occurrence_date   date        NOT NULL,  -- original date this exception replaces
  action            text        NOT NULL CHECK (action IN ('SKIP', 'RESCHEDULE')),
  new_scheduled_at  timestamptz,           -- only for RESCHEDULE
  new_title         text,                  -- optional title override
  reason            text,
  created_at        timestamptz NOT NULL DEFAULT NOW(),
  UNIQUE(schedule_id, occurrence_date)
);

-- ─── Extend sessions table ────────────────────────────────────────────────────

ALTER TABLE public.sessions
  ADD COLUMN schedule_id integer REFERENCES public.session_schedules(schedule_id),
  ADD COLUMN notify_at   timestamptz;  -- NULL = send immediately (manual); set = cron sends at this time

-- ─── Functions ───────────────────────────────────────────────────────────────

-- List all schedules for a course with next occurrence date
CREATE OR REPLACE FUNCTION public.fn_schedule_list(p_course_id integer)
RETURNS jsonb
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(jsonb_agg(row_to_json(s.*) ORDER BY s.created_at), '[]'::jsonb)
  FROM public.session_schedules s
  WHERE s.course_id = p_course_id;
$$;

-- Create or update a schedule
CREATE OR REPLACE FUNCTION public.fn_schedule_upsert(p_data jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_id integer := (p_data->>'schedule_id')::integer;
  v_result jsonb;
BEGIN
  IF v_id IS NULL THEN
    INSERT INTO public.session_schedules
      (course_id, title, recurrence, duration_min, participant_roles, create_breakout_rooms, advance_hours, notify_hours_before, created_by)
    VALUES (
      (p_data->>'course_id')::integer,
      p_data->>'title',
      p_data->'recurrence',
      (p_data->>'duration_min')::integer,
      ARRAY(SELECT jsonb_array_elements_text(p_data->'participant_roles')),
      (p_data->>'create_breakout_rooms')::boolean,
      (p_data->>'advance_hours')::integer,
      (p_data->>'notify_hours_before')::integer,
      (p_data->>'created_by')::bigint
    )
    RETURNING jsonb_build_object('schedule_id', schedule_id) INTO v_result;
  ELSE
    UPDATE public.session_schedules SET
      title                 = p_data->>'title',
      recurrence            = p_data->'recurrence',
      duration_min          = (p_data->>'duration_min')::integer,
      participant_roles     = ARRAY(SELECT jsonb_array_elements_text(p_data->'participant_roles')),
      create_breakout_rooms = (p_data->>'create_breakout_rooms')::boolean,
      advance_hours         = (p_data->>'advance_hours')::integer,
      notify_hours_before   = (p_data->>'notify_hours_before')::integer
    WHERE schedule_id = v_id
    RETURNING jsonb_build_object('schedule_id', schedule_id) INTO v_result;
  END IF;
  RETURN v_result;
END;
$$;

-- Toggle active/paused
CREATE OR REPLACE FUNCTION public.fn_schedule_toggle(p_schedule_id integer)
RETURNS jsonb
LANGUAGE sql AS $$
  UPDATE public.session_schedules
  SET active = NOT active
  WHERE schedule_id = p_schedule_id
  RETURNING jsonb_build_object('schedule_id', schedule_id, 'active', active);
$$;

-- Delete schedule (only if no sessions created from it)
CREATE OR REPLACE FUNCTION public.fn_schedule_delete(p_schedule_id integer)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_count integer;
BEGIN
  SELECT COUNT(*) INTO v_count FROM public.sessions WHERE schedule_id = p_schedule_id;
  IF v_count > 0 THEN
    RETURN jsonb_build_object('error', 'Cannot delete: schedule has ' || v_count || ' sessions created from it. Pause it instead.');
  END IF;
  DELETE FROM public.session_schedules WHERE schedule_id = p_schedule_id;
  RETURN jsonb_build_object('deleted', true);
END;
$$;

-- Upsert exception (skip or reschedule a specific occurrence)
CREATE OR REPLACE FUNCTION public.fn_schedule_upsert_exception(p_data jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_id integer;
BEGIN
  INSERT INTO public.session_schedule_exceptions
    (schedule_id, occurrence_date, action, new_scheduled_at, new_title, reason)
  VALUES (
    (p_data->>'schedule_id')::integer,
    (p_data->>'occurrence_date')::date,
    p_data->>'action',
    (p_data->>'new_scheduled_at')::timestamptz,
    p_data->>'new_title',
    p_data->>'reason'
  )
  ON CONFLICT (schedule_id, occurrence_date) DO UPDATE SET
    action           = EXCLUDED.action,
    new_scheduled_at = EXCLUDED.new_scheduled_at,
    new_title        = EXCLUDED.new_title,
    reason           = EXCLUDED.reason
  RETURNING exception_id INTO v_id;
  RETURN jsonb_build_object('exception_id', v_id);
END;
$$;

-- Delete exception (restore normal occurrence)
CREATE OR REPLACE FUNCTION public.fn_schedule_delete_exception(p_exception_id integer)
RETURNS jsonb
LANGUAGE sql AS $$
  DELETE FROM public.session_schedule_exceptions WHERE exception_id = p_exception_id
  RETURNING jsonb_build_object('deleted', true);
$$;

-- ─── Grants ──────────────────────────────────────────────────────────────────

GRANT SELECT, INSERT, UPDATE, DELETE ON public.session_schedules           TO "elfi1953_stc-eu";
GRANT SELECT, INSERT, UPDATE, DELETE ON public.session_schedule_exceptions TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.session_schedules_schedule_id_seq           TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.session_schedule_exceptions_exception_id_seq TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_list(integer)            TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_upsert(jsonb)            TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_toggle(integer)          TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_delete(integer)          TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_upsert_exception(jsonb)  TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_schedule_delete_exception(integer) TO "elfi1953_stc-eu";