CREATE OR REPLACE FUNCTION fn_low_enrollment(threshold INTEGER)
RETURNS TABLE(
    discipline_id INTEGER,
    discipline_name VARCHAR,
    department_name VARCHAR,
    enrolled BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT d.id,
         d.name AS discipline_name,
         dept.name AS department_name,
         COUNT(st.id) AS enrolled
  FROM Disciplines d
  LEFT JOIN Departments dept ON d.department_id = dept.id
  LEFT JOIN Statements st ON st.discipline_id = d.id
  GROUP BY d.id, d.name, dept.name
  HAVING COUNT(st.id) < threshold;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_generate_transcript(p_student INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  student_info RECORD;
  transcript TEXT;
BEGIN
  SELECT p.name || ' ' || p.surname AS full_name,
         s.group_id,
         s.status
    INTO student_info
    FROM Students s
    JOIN Physicals p ON s.person = p.passport_no
   WHERE s.id = p_student;

  transcript := 'Transcript for student ' || p_student || E'\n'
             || 'Name: ' || student_info.full_name || E'\n'
             || 'Group: ' || student_info.group_id || E'\n'
             || 'Status: ' || student_info.status || E'\n' || E'\n';

  FOR rec IN
    SELECT d.name AS discipline_name,
           st.grade,
           to_char(st.conducted_at,'YYYY-MM-DD') AS date
      FROM Statements st
      JOIN Disciplines d ON st.discipline_id = d.id
     WHERE st.student_id = p_student
     ORDER BY st.conducted_at
  LOOP
    transcript := transcript || rec.date || ' - ' || rec.discipline_name || ': ' || rec.grade || E'\n';
  END LOOP;

  RETURN transcript;
END;
$$;

CREATE OR REPLACE PROCEDURE sp_graduate_by_admission_year(p_year INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT s.id, g.study_starts
      FROM Students s
      JOIN Groups g ON s.group_id = g.group_id
     WHERE EXTRACT(YEAR FROM g.study_starts) = p_year
       AND s.status = 'УЧИТСЯ'
  LOOP
    INSERT INTO Movement(student_id, type, new_status, issued_at)
    VALUES (rec.id, 'ОТЧИСЛЕН', 'ОТЧИСЛЕН', NOW());
  END LOOP;
END;
$$;