get_syllabus_details()#

CREATE OR REPLACE FUNCTION get_syllabus_details(p_syllabus_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",
  "topics": [
    {
      "id": 101,
      "topic_name": "Trigonometri",
      "description": "Trigonometrik fonksiyonlar ve uygulamalar",
      "expected_week": 1,
      "parent_topic_id": null
    },
    {
      "id": 102,
      "topic_name": "Sinüs Teoremi",
      "description": "Sinüs teoremi ve çözüm yöntemleri",
      "expected_week": 2,
      "parent_topic_id": 101
    },
    {
      "id": 103,
      "topic_name": "Fonksiyonlar",
      "description": "Fonksiyon kavramı ve türleri",
      "expected_week": 5,
      "parent_topic_id": null
    }
  ]
}

CREATE OR REPLACE FUNCTION public.get_syllabus_details(p_syllabus_id integer)
 RETURNS json
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  v_result json;
  v_user_id integer;
  v_user_role text;
  v_class_id integer;
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;

  SELECT s.class_id INTO v_class_id
  FROM public.syllabi s
  WHERE s.id = p_syllabus_id;

  IF v_class_id IS NULL THEN
    RAISE EXCEPTION 'Syllabus not found';
  END IF;

  IF v_user_role = 'teacher' THEN
    IF NOT EXISTS (
      SELECT 1 FROM public.classes 
      WHERE id = v_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 = v_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_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,
    'topics', COALESCE(
      (
        SELECT json_agg(
          json_build_object(
            'id', st.id,
            'topic_name', st.topic_name,
            'description', st.description,
            'expected_week', st.expected_week,
            'parent_topic_id', st.parent_topic_id
          )
          ORDER BY st.expected_week NULLS LAST, st.id
        )
        FROM public.syllabus_topics st
        WHERE st.syllabus_id = s.id
      ),
      '[]'::json
    )
  )
  INTO v_result
  FROM public.syllabi s
  WHERE s.id = p_syllabus_id;

  RETURN v_result;

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

BaseApiService.logApiCall('RPC', 'get_syllabus_details', params: {'p_syllabus_id': syllabusId});

final response = await _supabase
    .rpc('get_syllabus_details', params: {'p_syllabus_id': syllabusId});

-- Müfredat detaylarını getir
SELECT get_syllabus_details(1);

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

-- Sadece başlık ve konu sayısını al
SELECT 
  (get_syllabus_details(1)::jsonb)->>'title' as title,
  jsonb_array_length((get_syllabus_details(1)::jsonb)->'topics') as topic_count;

parent_topic_id = NULL

parent_topic_id = [Ana Konu ID]

ORDER BY st.expected_week NULLS LAST, st.id

interface SyllabusDetails {
  id: number;
  class_id: number;
  title: string;
  description: string | null;
  original_file_path: string | null;
  created_at: string; // ISO 8601
  topics: Topic[];
}

interface Topic {
  id: number;
  topic_name: string;
  description: string | null;
  expected_week: number | null;
  parent_topic_id: number | null; // Ana konularda null, alt konularda parent ID
}

// Flutter/Dart örneği
final response = await supabase
    .rpc('get_syllabus_details', params: {'p_syllabus_id': 1})
    .execute();

final syllabus = response.data;
print('Müfredat: ${syllabus['title']}');
print('Konu Sayısı: ${syllabus['topics'].length}');

// Ana konuları filtrele
final mainTopics = (syllabus['topics'] as List)
    .where((topic) => topic['parent_topic_id'] == null)
    .toList();

// Alt konuları bul
final subTopics = (syllabus['topics'] as List)
    .where((topic) => topic['parent_topic_id'] == mainTopicId)
    .toList();

CREATE INDEX idx_syllabus_topics_syllabus_id ON syllabus_topics(syllabus_id);
CREATE INDEX idx_syllabus_topics_parent_id ON syllabus_topics(parent_topic_id);