Index: sql/advanced.sql =================================================================== --- sql/advanced.sql (revision f7c8acff5e03bf7cd407218b098c5136c2ff2173) +++ sql/advanced.sql (revision f7c8acff5e03bf7cd407218b098c5136c2ff2173) @@ -0,0 +1,636 @@ +-- ============================================================ +-- IKnow / FINKI - Phase 7: advanced database development +-- +-- Run after schema_creation.sql and data_load.sql: +-- schema_creation.sql -> tables, enums +-- data_load.sql -> sample data +-- advanced.sql -> this file +-- +-- Re-runnable: every object is created with OR REPLACE or dropped first. +-- ============================================================ + +SET search_path TO project; + +-- ============================================================ +-- 1. CUSTOM DOMAINS +-- One place to define what a valid value looks like, reused by +-- every column of that kind. +-- ============================================================ + +DROP DOMAIN IF EXISTS email_address CASCADE; +CREATE DOMAIN email_address AS VARCHAR(150) + CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); + +DROP DOMAIN IF EXISTS embg_number CASCADE; +CREATE DOMAIN embg_number AS VARCHAR(13) + CHECK (VALUE ~ '^[0-9]{13}$'); + +DROP DOMAIN IF EXISTS student_index CASCADE; +CREATE DOMAIN student_index AS VARCHAR(20) + CHECK (VALUE ~ '^[0-9]{6}$'); + +DROP DOMAIN IF EXISTS phone_number CASCADE; +CREATE DOMAIN phone_number AS VARCHAR(20) + CHECK (VALUE ~ '^[0-9+][0-9 /-]{5,19}$'); + +DROP DOMAIN IF EXISTS money_amount CASCADE; +CREATE DOMAIN money_amount AS INTEGER + CHECK (VALUE >= 0); + +DROP DOMAIN IF EXISTS gpa_value CASCADE; +CREATE DOMAIN gpa_value AS REAL + CHECK (VALUE >= 2.0 AND VALUE <= 5.0); + +DROP DOMAIN IF EXISTS credit_points CASCADE; +CREATE DOMAIN credit_points AS INTEGER + CHECK (VALUE > 0 AND VALUE <= 30); + +ALTER TABLE users ALTER COLUMN email TYPE email_address; +ALTER TABLE users ALTER COLUMN embg TYPE embg_number; +ALTER TABLE users ALTER COLUMN "index" TYPE student_index; +ALTER TABLE contact ALTER COLUMN microsoft_email TYPE email_address; +ALTER TABLE contact ALTER COLUMN number TYPE phone_number; +ALTER TABLE payment ALTER COLUMN amount TYPE money_amount; +ALTER TABLE documents ALTER COLUMN cost TYPE money_amount; +ALTER TABLE high_school ALTER COLUMN gpa TYPE gpa_value; +ALTER TABLE subjects ALTER COLUMN awarded_credits TYPE credit_points; + +-- ============================================================ +-- 2. MULTI-TENANCY: several faculties in one database +-- ============================================================ + +CREATE TABLE IF NOT EXISTS faculty ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + short_name VARCHAR(20) NOT NULL UNIQUE, + full_name VARCHAR(200) NOT NULL, + university VARCHAR(200) NOT NULL +); + +INSERT INTO faculty (id, short_name, full_name, university) +VALUES (1, 'FINKI', 'Факултет за информатички науки и компјутерско инженерство', + 'Универзитет „Св. Кирил и Методиј“ - Скопје') +ON CONFLICT (short_name) DO NOTHING; + +-- The tenant key goes on the root entities. Everything else inherits its +-- faculty through them, so the column is not repeated on every table. +ALTER TABLE users ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); +ALTER TABLE major ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); +ALTER TABLE subjects ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); +ALTER TABLE active_semesters ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); +ALTER TABLE documents ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); + +-- subjects.code and subjects.name were globally unique; with several +-- faculties they only have to be unique inside one faculty. +ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_code_key; +ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_name_key; +DROP INDEX IF EXISTS subjects_faculty_code_key; +DROP INDEX IF EXISTS subjects_faculty_name_key; +CREATE UNIQUE INDEX subjects_faculty_code_key ON subjects (faculty_id, code); +CREATE UNIQUE INDEX subjects_faculty_name_key ON subjects (faculty_id, name); + +-- The faculty of the current session. Returns NULL when nothing is set, +-- which the policies below read as "no tenant filter". +CREATE OR REPLACE FUNCTION current_faculty() + RETURNS INTEGER + LANGUAGE plpgsql STABLE +AS $$ +BEGIN + RETURN nullif(current_setting('app.current_faculty', TRUE), '')::INTEGER; +EXCEPTION + WHEN others THEN RETURN NULL; +END; +$$; + +CREATE OR REPLACE PROCEDURE set_current_faculty(p_faculty INTEGER) + LANGUAGE plpgsql +AS $$ +BEGIN + IF p_faculty IS NOT NULL AND NOT EXISTS (SELECT 1 FROM faculty WHERE id = p_faculty) THEN + RAISE EXCEPTION 'Faculty % does not exist', p_faculty; + END IF; + PERFORM set_config('app.current_faculty', coalesce(p_faculty::TEXT, ''), FALSE); +END; +$$; + +-- Row level security. The table owner bypasses these policies unless +-- FORCE ROW LEVEL SECURITY is used, so in production the application +-- connects with a separate, non-owner role. +ALTER TABLE users ENABLE ROW LEVEL SECURITY; +ALTER TABLE major ENABLE ROW LEVEL SECURITY; +ALTER TABLE subjects ENABLE ROW LEVEL SECURITY; +ALTER TABLE active_semesters ENABLE ROW LEVEL SECURITY; +ALTER TABLE documents ENABLE ROW LEVEL SECURITY; + +DROP POLICY IF EXISTS tenant_isolation ON users; +DROP POLICY IF EXISTS tenant_isolation ON major; +DROP POLICY IF EXISTS tenant_isolation ON subjects; +DROP POLICY IF EXISTS tenant_isolation ON active_semesters; +DROP POLICY IF EXISTS tenant_isolation ON documents; + +CREATE POLICY tenant_isolation ON users + USING (current_faculty() IS NULL OR faculty_id = current_faculty()); +CREATE POLICY tenant_isolation ON major + USING (current_faculty() IS NULL OR faculty_id = current_faculty()); +CREATE POLICY tenant_isolation ON subjects + USING (current_faculty() IS NULL OR faculty_id = current_faculty()); +CREATE POLICY tenant_isolation ON active_semesters + USING (current_faculty() IS NULL OR faculty_id = current_faculty()); +CREATE POLICY tenant_isolation ON documents + USING (current_faculty() IS NULL OR faculty_id = current_faculty()); + +-- A foreign key cannot express "these three rows must belong to the same +-- faculty", so it is enforced with a trigger. +CREATE OR REPLACE FUNCTION check_enrolment_same_faculty() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + f_student INTEGER; + f_major INTEGER; + f_semester INTEGER; +BEGIN + SELECT faculty_id INTO f_student FROM users WHERE id = NEW.user_id; + SELECT faculty_id INTO f_major FROM major WHERE id = NEW.major_id; + SELECT faculty_id INTO f_semester FROM active_semesters WHERE id = NEW.semester_id; + + IF f_student IS DISTINCT FROM f_major OR f_student IS DISTINCT FROM f_semester THEN + RAISE EXCEPTION + 'Cross-faculty enrolment: student belongs to faculty %, major to %, semester to %', + f_student, f_major, f_semester; + END IF; + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_enrolment_same_faculty ON enrolled_semesters; +CREATE TRIGGER trg_enrolment_same_faculty + BEFORE INSERT OR UPDATE ON enrolled_semesters + FOR EACH ROW EXECUTE FUNCTION check_enrolment_same_faculty(); + +-- ============================================================ +-- 3. NOTIFICATIONS +-- ============================================================ + +CREATE TABLE IF NOT EXISTS notification ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + user_id INTEGER NOT NULL REFERENCES users (id), + kind VARCHAR(40) NOT NULL, + message TEXT NOT NULL, + is_read BOOLEAN NOT NULL DEFAULT FALSE, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX IF NOT EXISTS notification_user_unread_idx + ON notification (user_id) WHERE is_read = FALSE; + +-- ============================================================ +-- 4. ENROLMENT RULES +-- ============================================================ + +-- 4a. A subject must be offered by the study programme of the enrolment. +CREATE OR REPLACE FUNCTION check_subject_in_major() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_major INTEGER; +BEGIN + SELECT major_id INTO v_major FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; + + IF NOT EXISTS (SELECT 1 FROM major_subjects + WHERE major_id = v_major AND subject_id = NEW.subjects_id) THEN + RAISE EXCEPTION 'Subject % is not offered by study programme %', + NEW.subjects_id, v_major; + END IF; + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_subject_in_major ON semesters_subjects; +CREATE TRIGGER trg_subject_in_major + BEFORE INSERT OR UPDATE ON semesters_subjects + FOR EACH ROW EXECUTE FUNCTION check_subject_in_major(); + +-- 4b. The professor must actually teach that subject in that semester. +CREATE OR REPLACE FUNCTION check_professor_teaches() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_semester INTEGER; + v_role user_role; +BEGIN + SELECT semester_id INTO v_semester FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; + SELECT role INTO v_role FROM users WHERE id = NEW.professor_id; + + IF v_role <> 'prof' THEN + RAISE EXCEPTION 'User % is not a professor', NEW.professor_id; + END IF; + + IF NOT EXISTS (SELECT 1 FROM professour_subjects + WHERE prof_id = NEW.professor_id + AND subject_id = NEW.subjects_id + AND active_semester_id = v_semester) THEN + RAISE EXCEPTION 'Professor % does not teach subject % in semester %', + NEW.professor_id, NEW.subjects_id, v_semester; + END IF; + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_professor_teaches ON semesters_subjects; +CREATE TRIGGER trg_professor_teaches + BEFORE INSERT OR UPDATE ON semesters_subjects + FOR EACH ROW EXECUTE FUNCTION check_professor_teaches(); + +-- 4c. Prerequisites must already be passed. +CREATE OR REPLACE FUNCTION check_prerequisites_passed() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_user INTEGER; + v_missing TEXT; +BEGIN + SELECT user_id INTO v_user FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; + + SELECT string_agg(s.code, ', ' ORDER BY s.code) INTO v_missing + FROM dependency_subject d + JOIN subjects s ON s.id = d.dependency_id + WHERE d.subject_id = NEW.subjects_id + AND NOT EXISTS ( + SELECT 1 + FROM passed_subjects ps + JOIN semesters_subjects ss ON ss.id = ps.enrolled_id + JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id + WHERE es.user_id = v_user AND ss.subjects_id = d.dependency_id); + + IF v_missing IS NOT NULL THEN + RAISE EXCEPTION 'Prerequisite(s) not passed for subject %: %', + NEW.subjects_id, v_missing; + END IF; + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_prerequisites_passed ON semesters_subjects; +CREATE TRIGGER trg_prerequisites_passed + BEFORE INSERT ON semesters_subjects + FOR EACH ROW EXECUTE FUNCTION check_prerequisites_passed(); + +-- 4d. At most 5 subjects per enrolment, and at most 30 credits. +-- Deferred to commit, so a transaction may insert the five rows in any +-- order, or swap one subject for another, without tripping the rule +-- half-way through. +CREATE OR REPLACE FUNCTION check_enrolment_size() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_enrolment INTEGER := coalesce(NEW.enrolled_semesters_id, OLD.enrolled_semesters_id); + v_count INTEGER; + v_credits INTEGER; +BEGIN + SELECT count(*), coalesce(sum(s.awarded_credits), 0) + INTO v_count, v_credits + FROM semesters_subjects ss + JOIN subjects s ON s.id = ss.subjects_id + WHERE ss.enrolled_semesters_id = v_enrolment; + + IF v_count > 5 THEN + RAISE EXCEPTION 'Enrolment % has % subjects; the maximum is 5', v_enrolment, v_count; + END IF; + + IF v_credits > 30 THEN + RAISE EXCEPTION 'Enrolment % has % credits; the maximum is 30', v_enrolment, v_credits; + END IF; + + RETURN NULL; +END; +$$; + +DROP TRIGGER IF EXISTS trg_enrolment_size ON semesters_subjects; +CREATE CONSTRAINT TRIGGER trg_enrolment_size + AFTER INSERT OR UPDATE OR DELETE ON semesters_subjects + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION check_enrolment_size(); + +-- ============================================================ +-- 5. GRADING RULES +-- ============================================================ + +CREATE OR REPLACE FUNCTION check_grade_rules() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_professor INTEGER; + v_actor INTEGER; +BEGIN + IF NEW.date_passed > now() THEN + RAISE EXCEPTION 'A grade cannot be dated in the future (%)', NEW.date_passed; + END IF; + + SELECT professor_id INTO v_professor + FROM semesters_subjects WHERE id = NEW.enrolled_id; + + -- Enforced only when the application tells the database who is acting, + -- so scripts and migrations are not blocked. + v_actor := nullif(current_setting('app.current_user_id', TRUE), '')::INTEGER; + IF v_actor IS NOT NULL AND v_actor <> v_professor THEN + RAISE EXCEPTION 'User % may not grade this subject; it is taught by %', + v_actor, v_professor; + END IF; + + -- Passing a subject implies the professor signed it off. + UPDATE semesters_subjects SET signature = TRUE WHERE id = NEW.enrolled_id; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_grade_rules ON passed_subjects; +CREATE TRIGGER trg_grade_rules + BEFORE INSERT OR UPDATE ON passed_subjects + FOR EACH ROW EXECUTE FUNCTION check_grade_rules(); + +-- Tell the student, in their own language of record, that they were graded. +CREATE OR REPLACE FUNCTION notify_student_graded() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +DECLARE + v_user INTEGER; + v_subject TEXT; +BEGIN + SELECT es.user_id, s.name + INTO v_user, v_subject + FROM semesters_subjects ss + JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id + JOIN subjects s ON s.id = ss.subjects_id + WHERE ss.id = NEW.enrolled_id; + + INSERT INTO notification (user_id, kind, message) + VALUES (v_user, 'grade', + format('Добивте оценка %s по предметот %s.', NEW.grade, v_subject)); + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trg_notify_graded ON passed_subjects; +CREATE TRIGGER trg_notify_graded + AFTER INSERT ON passed_subjects + FOR EACH ROW EXECUTE FUNCTION notify_student_graded(); + +-- ============================================================ +-- 6. VIEWS +-- ============================================================ + +CREATE OR REPLACE VIEW v_student_transcript AS +SELECT es.user_id, + u."index" AS student_index, + u.name || ' ' || u.surname AS student, + s.code AS subject_code, + s.name AS subject, + s.awarded_credits, + ps.grade, + ps.date_passed, + p.name || ' ' || p.surname AS professor, + a.year, + a.type AS semester_type +FROM passed_subjects ps +JOIN semesters_subjects ss ON ss.id = ps.enrolled_id +JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id +JOIN users u ON u.id = es.user_id +JOIN subjects s ON s.id = ss.subjects_id +JOIN users p ON p.id = ss.professor_id +JOIN active_semesters a ON a.id = es.semester_id; + +CREATE OR REPLACE VIEW v_student_standing AS +SELECT u.id AS user_id, + u."index" AS student_index, + u.name || ' ' || u.surname AS student, + count(ps.id) AS passed_subjects, + coalesce(sum(s.awarded_credits), 0) AS credits, + round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade +FROM users u +LEFT JOIN enrolled_semesters es ON es.user_id = u.id +LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id +LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id +LEFT JOIN subjects s ON s.id = ss.subjects_id AND ps.id IS NOT NULL +WHERE u.role = 'student' +GROUP BY u.id, u."index", u.name, u.surname; + +CREATE OR REPLACE VIEW v_professor_gradebook AS +SELECT ss.professor_id, + p.name || ' ' || p.surname AS professor, + s.code AS subject_code, + s.name AS subject, + a.year, + a.type AS semester_type, + u.id AS student_id, + u."index" AS student_index, + u.name || ' ' || u.surname AS student, + ss.signature, + ps.grade +FROM semesters_subjects ss +JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id +JOIN users u ON u.id = es.user_id +JOIN users p ON p.id = ss.professor_id +JOIN subjects s ON s.id = ss.subjects_id +JOIN active_semesters a ON a.id = es.semester_id +LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id; + +-- Which subjects nobody teaches in a given semester. Students cannot enrol +-- in these, so this is the list the administrator has to clear. +CREATE OR REPLACE VIEW v_semester_coverage AS +SELECT a.id AS semester_id, + a.year, + a.type AS semester_type, + s.id AS subject_id, + s.code AS subject_code, + s.name AS subject +FROM active_semesters a +CROSS JOIN subjects s +WHERE NOT EXISTS (SELECT 1 FROM professour_subjects ps + WHERE ps.active_semester_id = a.id AND ps.subject_id = s.id); + +-- ============================================================ +-- 7. MATERIALIZED VIEW: subject statistics +-- ============================================================ + +DROP MATERIALIZED VIEW IF EXISTS mv_subject_statistics; +CREATE MATERIALIZED VIEW mv_subject_statistics AS +SELECT s.id AS subject_id, + s.code AS subject_code, + s.name AS subject, + count(ss.id) AS enrolled_count, + count(ps.id) AS passed_count, + round(100.0 * count(ps.id) / nullif(count(ss.id), 0), 1) AS pass_rate, + round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade +FROM subjects s +LEFT JOIN semesters_subjects ss ON ss.subjects_id = s.id +LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id +GROUP BY s.id, s.code, s.name; + +CREATE UNIQUE INDEX mv_subject_statistics_pk ON mv_subject_statistics (subject_id); + +-- ============================================================ +-- 8. BACKGROUND JOBS +-- ============================================================ + +-- Marks an enrolment finished once every subject in it is graded. +CREATE OR REPLACE PROCEDURE close_completed_enrolments() + LANGUAGE plpgsql +AS $$ +DECLARE + v_closed INTEGER; +BEGIN + WITH finished AS ( + SELECT es.id + FROM enrolled_semesters es + JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id + LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id + WHERE es.completed IS NULL + GROUP BY es.id + HAVING count(ss.id) > 0 AND count(ss.id) = count(ps.id) + ) + UPDATE enrolled_semesters es + SET completed = now(), + last_change = now() + FROM finished f + WHERE es.id = f.id; + + GET DIAGNOSTICS v_closed = ROW_COUNT; + RAISE NOTICE 'close_completed_enrolments: closed % enrolment(s)', v_closed; +END; +$$; + +-- Invalidates refresh tokens past their expiry. +CREATE OR REPLACE PROCEDURE expire_old_tokens() + LANGUAGE plpgsql +AS $$ +DECLARE + v_expired INTEGER; +BEGIN + UPDATE token + SET is_valid = FALSE + WHERE is_valid = TRUE AND expires_at < now(); + + GET DIAGNOSTICS v_expired = ROW_COUNT; + RAISE NOTICE 'expire_old_tokens: invalidated % token(s)', v_expired; +END; +$$; + +-- Reminds students about subjects they are enrolled in without a signature. +CREATE OR REPLACE PROCEDURE notify_missing_signatures() + LANGUAGE plpgsql +AS $$ +DECLARE + v_sent INTEGER; +BEGIN + INSERT INTO notification (user_id, kind, message) + SELECT es.user_id, + 'signature', + format('Немате потпис по предметот %s за семестарот %s/%s.', + s.name, a.year, a.year + 1) + FROM semesters_subjects ss + JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id + JOIN subjects s ON s.id = ss.subjects_id + JOIN active_semesters a ON a.id = es.semester_id + WHERE ss.signature = FALSE + AND es.completed IS NULL + AND NOT EXISTS ( + SELECT 1 FROM notification n + WHERE n.user_id = es.user_id + AND n.kind = 'signature' + AND n.message LIKE '%' || s.name || '%'); + + GET DIAGNOSTICS v_sent = ROW_COUNT; + RAISE NOTICE 'notify_missing_signatures: created % notification(s)', v_sent; +END; +$$; + +CREATE OR REPLACE PROCEDURE refresh_statistics() + LANGUAGE plpgsql +AS $$ +BEGIN + REFRESH MATERIALIZED VIEW CONCURRENTLY mv_subject_statistics; + RAISE NOTICE 'refresh_statistics: mv_subject_statistics refreshed'; +END; +$$; + +-- One entry point for the scheduler (pg_cron, or the operating system). +CREATE OR REPLACE PROCEDURE run_nightly_maintenance() + LANGUAGE plpgsql +AS $$ +BEGIN + CALL expire_old_tokens(); + CALL close_completed_enrolments(); + CALL notify_missing_signatures(); + CALL refresh_statistics(); + RAISE NOTICE 'run_nightly_maintenance: done'; +END; +$$; + +-- ============================================================ +-- 9. REPORTING FUNCTIONS +-- ============================================================ + +-- Everything the enrolment screen has to know about one student, in one call. +CREATE OR REPLACE FUNCTION student_eligible_subjects(p_user_id INTEGER, p_semester_id INTEGER) + RETURNS TABLE ( + subject_id INTEGER, + subject_code VARCHAR, + subject_name VARCHAR, + awarded_credits INTEGER, + already_passed BOOLEAN, + prerequisites_ok BOOLEAN, + has_professor BOOLEAN + ) + LANGUAGE sql STABLE +AS $$ + SELECT s.id, + s.code, + s.name, + s.awarded_credits::INTEGER, + EXISTS (SELECT 1 + FROM passed_subjects ps + JOIN semesters_subjects ss2 ON ss2.id = ps.enrolled_id + JOIN enrolled_semesters es2 ON es2.id = ss2.enrolled_semesters_id + WHERE es2.user_id = p_user_id AND ss2.subjects_id = s.id), + NOT EXISTS (SELECT 1 + FROM dependency_subject d + WHERE d.subject_id = s.id + AND NOT EXISTS ( + SELECT 1 + FROM passed_subjects ps + JOIN semesters_subjects ss3 ON ss3.id = ps.enrolled_id + JOIN enrolled_semesters es3 ON es3.id = ss3.enrolled_semesters_id + WHERE es3.user_id = p_user_id + AND ss3.subjects_id = d.dependency_id)), + EXISTS (SELECT 1 FROM professour_subjects pr + WHERE pr.subject_id = s.id AND pr.active_semester_id = p_semester_id) + FROM subjects s + WHERE EXISTS ( + SELECT 1 + FROM major_subjects ms + JOIN enrolled_semesters es ON es.major_id = ms.major_id + WHERE ms.subject_id = s.id AND es.user_id = p_user_id) + ORDER BY s.code; +$$; + +-- Average grade and credits of a student, as one row. +CREATE OR REPLACE FUNCTION student_summary(p_user_id INTEGER) + RETURNS TABLE (passed_subjects BIGINT, credits BIGINT, average_grade NUMERIC) + LANGUAGE sql STABLE +AS $$ + SELECT count(ps.id), + coalesce(sum(s.awarded_credits), 0)::BIGINT, + round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) + FROM passed_subjects ps + JOIN semesters_subjects ss ON ss.id = ps.enrolled_id + JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id + JOIN subjects s ON s.id = ss.subjects_id + WHERE es.user_id = p_user_id; +$$;