-- Corrige tipos integer → bigint en columnas que referencian partner_id (bigint en partners)

ALTER TABLE public.sessions          ALTER COLUMN created_by   TYPE bigint;
ALTER TABLE public.session_log       ALTER COLUMN performed_by TYPE bigint;
ALTER TABLE public.session_participants ALTER COLUMN partner_id TYPE bigint;

-- Elimina casts ::int innecesarios en fn_professor_create_session
CREATE OR REPLACE FUNCTION public.fn_professor_create_session(
  p_professor_id bigint,
  p_course_id    integer,
  p_title        text,
  p_scheduled_at timestamptz,
  p_duration_min integer DEFAULT 90
)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_session_id        integer;
  v_participants      jsonb;
  v_alternative_hosts text;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM public.professor_profiles
    WHERE partner_id = p_professor_id AND status = 'ACTIVE'
  ) THEN
    RETURN jsonb_build_object('error', 'No tienes perfil de profesor activo.');
  END IF;

  INSERT INTO public.sessions (course_id, created_by, title, scheduled_at, duration_min, status)
  VALUES (p_course_id, p_professor_id, p_title, p_scheduled_at, p_duration_min, 'SCHEDULED')
  RETURNING session_id INTO v_session_id;

  INSERT INTO public.session_log (session_id, action, performed_by)
  VALUES (v_session_id, 'CREATED', p_professor_id);

  -- Participantes: alumnos ACTIVE del curso + sus tutores, con breakout_room del nivel
  SELECT jsonb_agg(
    jsonb_build_object(
      'partner_id',   p.partner_id,
      'email',        p.email,
      'full_name',    p.name || ' ' || p.surname,
      'breakout_room',    l.zoom_room_name,
      'participant_type', 'STUDENT'
    )
  )
  INTO v_participants
  FROM public.student_profiles sp
  JOIN public.partners         p  ON p.partner_id  = sp.partner_id
  JOIN public.levels           l  ON l.level_id    = sp.level_id
  WHERE sp.course_id = p_course_id
    AND sp.status    = 'ACTIVE';

  -- Alternative hosts: profesores activos del curso distintos del creador
  SELECT string_agg(DISTINCT p.email, ',')
  INTO v_alternative_hosts
  FROM public.course_professor_levels cpl
  JOIN public.professor_profiles pp ON pp.partner_id = cpl.professor_id
  JOIN public.partners           p  ON p.partner_id  = cpl.professor_id
  WHERE cpl.course_id      = p_course_id
    AND pp.status          = 'ACTIVE'
    AND cpl.professor_id  != p_professor_id;

  RETURN jsonb_build_object(
    'session_id',        v_session_id,
    'participants',      COALESCE(v_participants, '[]'::jsonb),
    'alternative_hosts', COALESCE(v_alternative_hosts, '')
  );
END;
$$;

-- Elimina cast ::int en fn_professor_cancel_session
CREATE OR REPLACE FUNCTION public.fn_professor_cancel_session(
  p_professor_id bigint,
  p_session_id   integer,
  p_reason       text DEFAULT ''
)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_zoom_meeting_id text;
  v_scheduled_at    timestamptz;
  v_course_id       integer;
  v_course_name     text;
  v_course_language text;
  v_participants    jsonb;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM public.professor_profiles
    WHERE partner_id = p_professor_id AND status = 'ACTIVE'
  ) THEN
    RETURN jsonb_build_object('error', 'No tienes perfil de profesor activo.');
  END IF;

  UPDATE public.sessions
  SET status = 'CANCELLED'
  WHERE session_id = p_session_id
    AND status    != 'CANCELLED'
  RETURNING zoom_meeting_id, scheduled_at, course_id
  INTO v_zoom_meeting_id, v_scheduled_at, v_course_id;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('error', 'Sesión no encontrada o ya cancelada.');
  END IF;

  INSERT INTO public.session_log (session_id, action, performed_by, reason)
  VALUES (p_session_id, 'CANCELLED', p_professor_id, NULLIF(trim(p_reason), ''));

  SELECT name, language INTO v_course_name, v_course_language
  FROM public.courses WHERE course_id = v_course_id;

  SELECT jsonb_agg(jsonb_build_object('email', email, 'full_name', full_name))
  INTO v_participants
  FROM public.session_participants
  WHERE session_id = p_session_id;

  RETURN jsonb_build_object(
    'success',         true,
    'zoom_meeting_id', v_zoom_meeting_id,
    'scheduled_at',    v_scheduled_at,
    'course_name',     v_course_name,
    'course_language', v_course_language,
    'participants',    COALESCE(v_participants, '[]'::jsonb)
  );
END;
$$;