get_class_syllabi()#

CREATE OR REPLACE FUNCTION get_class_syllabi(p_class_id INTEGER)
RETURNS JSON
SECURITY DEFINER

[
  {
    "id": 1,
    "class_id": 15,
    "title": "2024-2025 Matematik Müfredatı",
    "description": "10. Sınıf Matematik Yıllık Planı",
    "original_file_path": "syllabi/math_2024.pdf",
    "created_at": "2024-09-01T08:00:00Z",
    "topic_count": 12
  },
  {
    "id": 2,
    "class_id": 15,
    "title": "Ek Konular - Trigonometri",
    "description": "İleri seviye trigonometri konuları",
    "original_file_path": "syllabi/trig_extra.pdf",
    "created_at": "2024-10-15T10:30:00Z",
    "topic_count": 5
  }
]

[]

CREATE OR REPLACE FUNCTION public.get_class_syllabi(p_class_id integer)
 RETURNS json
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  v_result json;
  v_user_id integer;
  v_user_role text;
BEGIN
  SELECT u.id, r.name INTO v_user_id, v_user_role
  FROM public.users u
  JOIN public.roles r ON u.role_id = r.id
  WHERE u.auth_user_id = auth.uid();

  IF v_user_id IS NULL THEN
    RAISE EXCEPTION 'User not found';
  END IF;

  IF v_user_role = 'teacher' THEN
    IF NOT EXISTS (
      SELECT 1 FROM public.classes 
      WHERE id = p_class_id AND teacher_id = v_user_id
    ) THEN
      RAISE EXCEPTION 'Access denied: You can only view syllabi from your own classes';
    END IF;
  ELSIF v_user_role = 'student' THEN
    IF NOT EXISTS (
      SELECT 1 FROM public.class_members 
      WHERE class_id = p_class_id 
      AND student_id = v_user_id 
      AND deleted_at IS NULL
    ) THEN
      RAISE EXCEPTION 'Access denied: You can only view syllabi from classes you are enrolled in';
    END IF;
  ELSE
    RAISE EXCEPTION 'Access denied: Invalid role';
  END IF;

  SELECT json_agg(
    json_build_object(
      'id', s.id,
      'class_id', s.class_id,
      'title', s.title,
      'description', s.description,
      'original_file_path', s.original_file_path,
      'created_at', s.created_at,
      'topic_count', COALESCE(topic_counts.count, 0)
    )
    ORDER BY s.created_at DESC
  )
  INTO v_result
  FROM public.syllabi s
  LEFT JOIN (
    SELECT st.syllabus_id, COUNT(*) as count
    FROM public.syllabus_topics st
    WHERE st.parent_topic_id IS NULL
    GROUP BY st.syllabus_id
  ) topic_counts ON s.id = topic_counts.syllabus_id
  WHERE s.class_id = p_class_id;

  IF v_result IS NULL THEN
    v_result := '[]'::json;
  END IF;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Error fetching syllabi: %', SQLERRM;
END;
$function$

BaseApiService.logApiCall('RPC', 'get_class_syllabi', params: {'p_class_id': classId});

final response = await _supabase
    .rpc('get_class_syllabi', params: {'p_class_id': classId});

-- Öğretmen olarak kendi sınıfının müfredatlarını listele
SELECT get_class_syllabi(15);

-- Öğrenci olarak kayıtlı olduğu sınıfın müfredatlarını görüntüle
SELECT get_class_syllabi(15);

-- JSON formatını güzelleştirerek göster
SELECT jsonb_pretty(get_class_syllabi(15)::jsonb);

interface Syllabus {
  id: number;
  class_id: number;
  title: string;
  description: string | null;
  original_file_path: string | null;
  created_at: string; // ISO 8601
  topic_count: number; // Ana konu sayısı
}

type SyllabiResponse = Syllabus[];