source: sql/advanced.sql@ e7bafc0

-- ============================================================
--  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;
$$;