Week 15 — Advanced Project Implementation & Capstone Review¶

-- ============================================================
-- FCRS: Frostburg Course Registration System
-- MySQL 8.0+
-- ============================================================
CREATE DATABASE IF NOT EXISTS fcrs
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE fcrs;

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- TABLE: departments
-- ============================================================
CREATE TABLE IF NOT EXISTS departments (
    dept_id     TINYINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    dept_code   CHAR(4)            NOT NULL COMMENT 'e.g. ITEC, MATH, ENGL',
    dept_name   VARCHAR(80)        NOT NULL,
    building    VARCHAR(40)        NULL,
    chair_id    INT                NULL     COMMENT 'FK -> instructors; set after instructors loaded',
    created_at  DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dept_id),
    UNIQUE KEY uq_dept_code (dept_code)
) ENGINE=InnoDB COMMENT='Academic departments';

-- ============================================================
-- TABLE: instructors
-- ============================================================
CREATE TABLE IF NOT EXISTS instructors (
    instructor_id   INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    first_name      VARCHAR(50)        NOT NULL,
    last_name       VARCHAR(50)        NOT NULL,
    email           VARCHAR(120)       NOT NULL,
    dept_id         TINYINT UNSIGNED   NOT NULL,
    title           ENUM('Instructor','Asst Professor','Assoc Professor',
                         'Professor','Adjunct','Lecturer')
                                       NOT NULL DEFAULT 'Instructor',
    hired_date      DATE               NOT NULL,
    is_active       TINYINT(1)         NOT NULL DEFAULT 1,
    PRIMARY KEY (instructor_id),
    UNIQUE KEY uq_instructor_email (email),
    KEY idx_instructor_dept (dept_id),
    CONSTRAINT fk_instr_dept FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id) ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Faculty and instructors';

-- Now set chair FK (deferred to avoid circular ref)
ALTER TABLE departments
    ADD CONSTRAINT fk_dept_chair FOREIGN KEY (chair_id)
        REFERENCES instructors(instructor_id) ON DELETE SET NULL;

-- ============================================================
-- TABLE: students
-- ============================================================
CREATE TABLE IF NOT EXISTS students (
    student_id      INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    fsuid           CHAR(9)            NOT NULL COMMENT 'FSU student ID e.g. B00123456',
    first_name      VARCHAR(50)        NOT NULL,
    last_name       VARCHAR(50)        NOT NULL,
    preferred_name  VARCHAR(50)        NULL,
    email           VARCHAR(120)       NOT NULL,
    dept_id         TINYINT UNSIGNED   NULL     COMMENT 'Declared major department',
    gpa             DECIMAL(4,3)       NOT NULL DEFAULT 0.000,
    credit_hours    SMALLINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'Total completed credit hours',
    enrollment_status ENUM('active','suspended','withdrawn','graduated','applicant')
                                       NOT NULL DEFAULT 'active',
    admit_date      DATE               NOT NULL,
    grad_date       DATE               NULL,
    PRIMARY KEY (student_id),
    UNIQUE KEY uq_student_fsuid (fsuid),
    UNIQUE KEY uq_student_email (email),
    KEY idx_student_dept (dept_id),
    KEY idx_student_status (enrollment_status),
    CONSTRAINT fk_student_dept FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='Registered students';

-- ============================================================
-- TABLE: courses
-- ============================================================
CREATE TABLE IF NOT EXISTS courses (
    course_id       INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    dept_id         TINYINT UNSIGNED   NOT NULL,
    course_code     VARCHAR(10)        NOT NULL COMMENT 'e.g. ITEC445',
    title           VARCHAR(120)       NOT NULL,
    description     TEXT               NULL,
    credit_hours    TINYINT UNSIGNED   NOT NULL DEFAULT 3,
    level           ENUM('100','200','300','400','500','600')
                                       NOT NULL DEFAULT '400',
    is_active       TINYINT(1)         NOT NULL DEFAULT 1,
    PRIMARY KEY (course_id),
    UNIQUE KEY uq_course_code (course_code),
    KEY idx_course_dept (dept_id),
    CONSTRAINT fk_course_dept FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id) ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Course catalogue';

-- ============================================================
-- TABLE: prerequisites (self-referential many-to-many)
-- ============================================================
CREATE TABLE IF NOT EXISTS prerequisites (
    course_id       INT UNSIGNED       NOT NULL COMMENT 'Course that has prereqs',
    prereq_id       INT UNSIGNED       NOT NULL COMMENT 'Required prerequisite course',
    min_grade       CHAR(2)            NOT NULL DEFAULT 'D' COMMENT 'Minimum passing grade',
    PRIMARY KEY (course_id, prereq_id),
    CONSTRAINT fk_prereq_course FOREIGN KEY (course_id)
        REFERENCES courses(course_id) ON DELETE CASCADE,
    CONSTRAINT fk_prereq_prereq FOREIGN KEY (prereq_id)
        REFERENCES courses(course_id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Course prerequisite relationships';

-- ============================================================
-- TABLE: sections
-- ============================================================
CREATE TABLE IF NOT EXISTS sections (
    section_id      INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    course_id       INT UNSIGNED       NOT NULL,
    instructor_id   INT UNSIGNED       NULL,
    section_num     TINYINT UNSIGNED   NOT NULL DEFAULT 1,
    semester        VARCHAR(10)        NOT NULL COMMENT 'e.g. Fall2025',
    schedule_days   SET('M','T','W','R','F','S')
                                       NOT NULL,
    start_time      TIME               NOT NULL,
    end_time        TIME               NOT NULL,
    room            VARCHAR(20)        NULL,
    capacity        TINYINT UNSIGNED   NOT NULL DEFAULT 30,
    enrolled_count  TINYINT UNSIGNED   NOT NULL DEFAULT 0,
    delivery_mode   ENUM('in-person','online','hybrid')
                                       NOT NULL DEFAULT 'in-person',
    status          ENUM('open','closed','cancelled','waitlist')
                                       NOT NULL DEFAULT 'open',
    PRIMARY KEY (section_id),
    UNIQUE KEY uq_section (course_id, section_num, semester),
    KEY idx_section_semester (semester),
    KEY idx_section_instructor (instructor_id),
    CONSTRAINT fk_section_course FOREIGN KEY (course_id)
        REFERENCES courses(course_id) ON UPDATE CASCADE,
    CONSTRAINT fk_section_instructor FOREIGN KEY (instructor_id)
        REFERENCES instructors(instructor_id) ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='Scheduled course sections';

-- ============================================================
-- TABLE: enrollments
-- ============================================================
CREATE TABLE IF NOT EXISTS enrollments (
    enrollment_id   INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    student_id      INT UNSIGNED       NOT NULL,
    section_id      INT UNSIGNED       NOT NULL,
    enrolled_at     DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          ENUM('enrolled','dropped','withdrawn','waitlisted','completed')
                                       NOT NULL DEFAULT 'enrolled',
    drop_date       DATE               NULL,
    PRIMARY KEY (enrollment_id),
    UNIQUE KEY uq_enrollment (student_id, section_id),
    KEY idx_enrollment_section   (section_id),
    KEY idx_enrollment_student   (student_id),
    KEY idx_enrollment_status    (status),
    KEY idx_enrollment_semester  (section_id, status),
    CONSTRAINT fk_enroll_student FOREIGN KEY (student_id)
        REFERENCES students(student_id) ON UPDATE CASCADE,
    CONSTRAINT fk_enroll_section FOREIGN KEY (section_id)
        REFERENCES sections(section_id) ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Student section enrollments';

-- ============================================================
-- TABLE: grades
-- ============================================================
CREATE TABLE IF NOT EXISTS grades (
    grade_id        INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    enrollment_id   INT UNSIGNED       NOT NULL,
    numeric_grade   DECIMAL(5,2)       NULL     COMMENT '0.00 - 100.00',
    letter_grade    CHAR(2)            NULL,
    grade_points    DECIMAL(3,2)       NULL     COMMENT 'GPA points: 4.0 scale',
    graded_at       DATETIME           NULL,
    graded_by       INT UNSIGNED       NULL     COMMENT 'FK -> instructors',
    is_final        TINYINT(1)         NOT NULL DEFAULT 0,
    PRIMARY KEY (grade_id),
    UNIQUE KEY uq_grade_enrollment (enrollment_id),
    KEY idx_grade_instructor (graded_by),
    CONSTRAINT fk_grade_enrollment FOREIGN KEY (enrollment_id)
        REFERENCES enrollments(enrollment_id) ON DELETE CASCADE,
    CONSTRAINT fk_grade_instructor FOREIGN KEY (graded_by)
        REFERENCES instructors(instructor_id) ON DELETE SET NULL,
    CONSTRAINT chk_numeric_grade CHECK (numeric_grade BETWEEN 0.00 AND 100.00)
) ENGINE=InnoDB COMMENT='Grades for enrollments';

-- ============================================================
-- TABLE: audit_log
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_log (
    log_id          BIGINT UNSIGNED    NOT NULL AUTO_INCREMENT,
    event_ts        DATETIME(6)        NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    event_type      VARCHAR(40)        NOT NULL,
    table_name      VARCHAR(64)        NOT NULL,
    record_id       INT UNSIGNED       NULL,
    user_name       VARCHAR(80)        NOT NULL DEFAULT (USER()),
    old_value       JSON               NULL,
    new_value       JSON               NULL,
    ip_address      VARCHAR(45)        NULL,
    PRIMARY KEY (log_id),
    KEY idx_audit_ts    (event_ts),
    KEY idx_audit_table (table_name, event_ts),
    KEY idx_audit_user  (user_name)
) ENGINE=InnoDB COMMENT='Immutable audit trail for sensitive changes';

SET FOREIGN_KEY_CHECKS = 1;

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_enroll_student$$
CREATE PROCEDURE sp_enroll_student(
    IN  p_student_id  INT UNSIGNED,
    IN  p_section_id  INT UNSIGNED,
    OUT p_status_code TINYINT,      -- 0=success, 1-n=error codes
    OUT p_message     VARCHAR(255)
)
sp_block: BEGIN
    DECLARE v_enrolled_count  TINYINT UNSIGNED DEFAULT 0;
    DECLARE v_capacity        TINYINT UNSIGNED DEFAULT 0;
    DECLARE v_course_id       INT UNSIGNED;
    DECLARE v_semester        VARCHAR(10);
    DECLARE v_student_status  VARCHAR(20);
    DECLARE v_prereq_id       INT UNSIGNED;
    DECLARE v_prereq_met      TINYINT DEFAULT 0;
    DECLARE v_conflict_count  INT DEFAULT 0;
    DECLARE v_enrollment_id   INT UNSIGNED;
    DECLARE done              INT DEFAULT FALSE;

    -- Cursor: iterate all prerequisites for this course
    DECLARE prereq_cur CURSOR FOR
        SELECT p.prereq_id
        FROM   prerequisites p
        WHERE  p.course_id = v_course_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status_code = 99;
        SET p_message     = 'Unexpected database error — enrollment rolled back';
    END;

    -- ---- Step 1: Validate student is active -----------------
    SELECT enrollment_status INTO v_student_status
    FROM   students
    WHERE  student_id = p_student_id;

    IF v_student_status IS NULL THEN
        SET p_status_code = 1;
        SET p_message = 'Student not found';
        LEAVE sp_block;
    END IF;

    IF v_student_status != 'active' THEN
        SET p_status_code = 2;
        SET p_message = CONCAT('Student status is ', v_student_status,
                               '; must be active to enroll');
        LEAVE sp_block;
    END IF;

    -- ---- Step 2: Get section info ----------------------------
    SELECT s.capacity, s.enrolled_count, s.course_id, s.semester
    INTO   v_capacity, v_enrolled_count, v_course_id, v_semester
    FROM   sections s
    WHERE  s.section_id = p_section_id
      AND  s.status IN ('open', 'waitlist')
    FOR UPDATE;

    IF v_course_id IS NULL THEN
        SET p_status_code = 3;
        SET p_message = 'Section not found or not open for enrollment';
        LEAVE sp_block;
    END IF;

    -- ---- Step 3: Check capacity ------------------------------
    IF v_enrolled_count >= v_capacity THEN
        -- Add to waitlist instead
        INSERT INTO enrollments (student_id, section_id, status)
        VALUES (p_student_id, p_section_id, 'waitlisted')
        ON DUPLICATE KEY UPDATE status = 'waitlisted';

        SET p_status_code = 4;
        SET p_message = 'Section full — added to waitlist';
        LEAVE sp_block;
    END IF;

    -- ---- Step 4: Check prerequisites ------------------------
    OPEN prereq_cur;
    prereq_loop: LOOP
        FETCH prereq_cur INTO v_prereq_id;
        IF done THEN LEAVE prereq_loop; END IF;

        SET v_prereq_met = 0;
        SELECT COUNT(*) INTO v_prereq_met
        FROM   enrollments e
        JOIN   grades      g ON g.enrollment_id = e.enrollment_id
        JOIN   sections    s ON s.section_id    = e.section_id
        WHERE  e.student_id = p_student_id
          AND  s.course_id  = v_prereq_id
          AND  g.is_final   = 1
          AND  g.letter_grade NOT IN ('F','W','I');

        IF v_prereq_met = 0 THEN
            CLOSE prereq_cur;
            SET p_status_code = 5;
            SET p_message = CONCAT('Prerequisite not satisfied: course_id = ',
                                   v_prereq_id);
            LEAVE sp_block;
        END IF;
    END LOOP prereq_loop;
    CLOSE prereq_cur;

    -- ---- Step 5: Check schedule conflicts -------------------
    SELECT COUNT(*) INTO v_conflict_count
    FROM   enrollments  e2
    JOIN   sections     s2 ON s2.section_id = e2.section_id
    JOIN   sections     s1 ON s1.section_id = p_section_id
    WHERE  e2.student_id = p_student_id
      AND  e2.status     = 'enrolled'
      AND  s2.semester   = v_semester
      AND  s2.section_id != p_section_id
      AND  FIND_IN_SET(SUBSTRING_INDEX(s2.schedule_days, ',', 1),
                       s1.schedule_days) > 0
      AND  s2.start_time < s1.end_time
      AND  s2.end_time   > s1.start_time;

    IF v_conflict_count > 0 THEN
        SET p_status_code = 6;
        SET p_message = 'Schedule conflict with an existing enrollment';
        LEAVE sp_block;
    END IF;

    -- ---- Step 6: Perform enrollment -------------------------
    START TRANSACTION;

    INSERT INTO enrollments (student_id, section_id, status)
    VALUES (p_student_id, p_section_id, 'enrolled')
    ON DUPLICATE KEY UPDATE status = 'enrolled', drop_date = NULL;

    SET v_enrollment_id = LAST_INSERT_ID();

    UPDATE sections
    SET    enrolled_count = enrolled_count + 1
    WHERE  section_id = p_section_id;

    -- Create placeholder grade record
    INSERT IGNORE INTO grades (enrollment_id)
    VALUES (v_enrollment_id);

    COMMIT;

    SET p_status_code = 0;
    SET p_message = CONCAT('Successfully enrolled in section ', p_section_id);

END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_calculate_gpa$$
CREATE PROCEDURE sp_calculate_gpa(
    IN  p_student_id INT UNSIGNED,
    IN  p_semester   VARCHAR(10),    -- NULL for cumulative
    OUT p_gpa        DECIMAL(4,3),
    OUT p_credits    SMALLINT UNSIGNED
)
BEGIN
    SELECT
        ROUND(
            SUM(g.grade_points * c.credit_hours)
            / NULLIF(SUM(c.credit_hours), 0),
        3) AS gpa,
        SUM(c.credit_hours) AS total_credits
    INTO p_gpa, p_credits
    FROM   enrollments e
    JOIN   grades      g  ON g.enrollment_id = e.enrollment_id
    JOIN   sections    s  ON s.section_id    = e.section_id
    JOIN   courses     c  ON c.course_id     = s.course_id
    WHERE  e.student_id = p_student_id
      AND  e.status     IN ('enrolled', 'completed')
      AND  g.is_final   = 1
      AND  g.grade_points IS NOT NULL
      AND  (p_semester IS NULL OR s.semester = p_semester);

    IF p_gpa IS NULL THEN
        SET p_gpa     = 0.000;
        SET p_credits = 0;
    END IF;

    -- Update students table if cumulative
    IF p_semester IS NULL THEN
        UPDATE students
        SET    gpa          = p_gpa,
               credit_hours = p_credits
        WHERE  student_id   = p_student_id;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_generate_transcript$$
CREATE PROCEDURE sp_generate_transcript(
    IN p_student_id INT UNSIGNED
)
BEGIN
    DECLARE v_semester      VARCHAR(10);
    DECLARE v_course_code   VARCHAR(10);
    DECLARE v_title         VARCHAR(120);
    DECLARE v_credits       TINYINT UNSIGNED;
    DECLARE v_letter        CHAR(2);
    DECLARE v_points        DECIMAL(3,2);
    DECLARE v_sem_gpa       DECIMAL(4,3);
    DECLARE v_sem_credits   SMALLINT;
    DECLARE v_cum_points    DECIMAL(8,3) DEFAULT 0;
    DECLARE v_cum_credits   SMALLINT DEFAULT 0;
    DECLARE done            INT DEFAULT FALSE;

    -- Result set delivered via temp table
    DROP TEMPORARY TABLE IF EXISTS tmp_transcript;
    CREATE TEMPORARY TABLE tmp_transcript (
        semester        VARCHAR(10),
        course_code     VARCHAR(10),
        course_title    VARCHAR(120),
        credit_hours    TINYINT,
        letter_grade    CHAR(2),
        grade_points    DECIMAL(3,2),
        sem_gpa         DECIMAL(4,3),
        cum_gpa         DECIMAL(4,3)
    );

    DECLARE transcript_cur CURSOR FOR
        SELECT  s.semester, c.course_code, c.title,
                c.credit_hours, g.letter_grade, g.grade_points
        FROM    enrollments e
        JOIN    sections    s ON s.section_id    = e.section_id
        JOIN    courses     c ON c.course_id     = s.course_id
        JOIN    grades      g ON g.enrollment_id = e.enrollment_id
        WHERE   e.student_id = p_student_id
          AND   g.is_final   = 1
        ORDER BY s.semester, c.course_code;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN transcript_cur;
    trans_loop: LOOP
        FETCH transcript_cur INTO v_semester, v_course_code, v_title,
                                  v_credits, v_letter, v_points;
        IF done THEN LEAVE trans_loop; END IF;

        SET v_cum_points  = v_cum_points  + (v_points * v_credits);
        SET v_cum_credits = v_cum_credits + v_credits;

        CALL sp_calculate_gpa(p_student_id, v_semester, v_sem_gpa, v_sem_credits);

        INSERT INTO tmp_transcript VALUES (
            v_semester, v_course_code, v_title, v_credits,
            v_letter, v_points,
            v_sem_gpa,
            ROUND(v_cum_points / NULLIF(v_cum_credits, 0), 3)
        );
    END LOOP;
    CLOSE transcript_cur;

    SELECT * FROM tmp_transcript ORDER BY semester, course_code;
    DROP TEMPORARY TABLE IF EXISTS tmp_transcript;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS trg_enforce_capacity$$
CREATE TRIGGER trg_enforce_capacity
BEFORE INSERT ON enrollments
FOR EACH ROW
BEGIN
    DECLARE v_enrolled  TINYINT UNSIGNED;
    DECLARE v_capacity  TINYINT UNSIGNED;
    DECLARE v_status    VARCHAR(10);

    SELECT enrolled_count, capacity, status
    INTO   v_enrolled, v_capacity, v_status
    FROM   sections
    WHERE  section_id = NEW.section_id;

    IF v_status = 'cancelled' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot enroll: section has been cancelled';
    END IF;

    IF v_enrolled >= v_capacity AND NEW.status = 'enrolled' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot enroll: section is at capacity';
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS trg_audit_grade_change$$
CREATE TRIGGER trg_audit_grade_change
AFTER UPDATE ON grades
FOR EACH ROW
BEGIN
    -- Only log when numeric_grade or letter_grade actually changes
    IF (OLD.numeric_grade != NEW.numeric_grade
        OR OLD.letter_grade != NEW.letter_grade
        OR OLD.is_final     != NEW.is_final)
    THEN
        INSERT INTO audit_log (
            event_type, table_name, record_id,
            user_name, old_value, new_value
        )
        VALUES (
            'GRADE_CHANGE',
            'grades',
            NEW.grade_id,
            USER(),
            JSON_OBJECT(
                'numeric_grade', OLD.numeric_grade,
                'letter_grade',  OLD.letter_grade,
                'grade_points',  OLD.grade_points,
                'is_final',      OLD.is_final
            ),
            JSON_OBJECT(
                'numeric_grade', NEW.numeric_grade,
                'letter_grade',  NEW.letter_grade,
                'grade_points',  NEW.grade_points,
                'is_final',      NEW.is_final,
                'graded_by',     NEW.graded_by
            )
        );
    END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS trg_update_enrollment_count$$
CREATE TRIGGER trg_update_enrollment_count
AFTER UPDATE ON enrollments
FOR EACH ROW
BEGIN
    -- Student dropped: decrement count
    IF OLD.status = 'enrolled' AND NEW.status IN ('dropped','withdrawn') THEN
        UPDATE sections
        SET    enrolled_count = GREATEST(0, enrolled_count - 1),
               status = CASE
                            WHEN enrolled_count - 1 < capacity THEN 'open'
                            ELSE status
                        END
        WHERE  section_id = NEW.section_id;

    -- Student re-enrolled from waitlist or dropped state
    ELSEIF OLD.status != 'enrolled' AND NEW.status = 'enrolled' THEN
        UPDATE sections
        SET    enrolled_count = enrolled_count + 1,
               status = CASE
                            WHEN enrolled_count + 1 >= capacity THEN 'closed'
                            ELSE 'open'
                        END
        WHERE  section_id = NEW.section_id;
    END IF;
END$$
DELIMITER ;

DELIMITER $$

-- ============================================================
-- fn_letter_grade: Convert numeric score to letter grade
-- ============================================================
DROP FUNCTION IF EXISTS fn_letter_grade$$
CREATE FUNCTION fn_letter_grade(p_score DECIMAL(5,2))
RETURNS CHAR(2)
DETERMINISTIC NO SQL
BEGIN
    RETURN CASE
        WHEN p_score >= 93 THEN 'A'
        WHEN p_score >= 90 THEN 'A-'
        WHEN p_score >= 87 THEN 'B+'
        WHEN p_score >= 83 THEN 'B'
        WHEN p_score >= 80 THEN 'B-'
        WHEN p_score >= 77 THEN 'C+'
        WHEN p_score >= 73 THEN 'C'
        WHEN p_score >= 70 THEN 'C-'
        WHEN p_score >= 67 THEN 'D+'
        WHEN p_score >= 60 THEN 'D'
        ELSE 'F'
    END;
END$$

-- ============================================================
-- fn_student_gpa: Calculate current cumulative GPA
-- ============================================================
DROP FUNCTION IF EXISTS fn_student_gpa$$
CREATE FUNCTION fn_student_gpa(p_student_id INT UNSIGNED)
RETURNS DECIMAL(4,3)
READS SQL DATA
BEGIN
    DECLARE v_gpa DECIMAL(4,3) DEFAULT 0.000;

    SELECT ROUND(
               SUM(g.grade_points * c.credit_hours)
               / NULLIF(SUM(c.credit_hours), 0),
           3)
    INTO   v_gpa
    FROM   enrollments e
    JOIN   grades      g ON g.enrollment_id = e.enrollment_id
    JOIN   sections    s ON s.section_id    = e.section_id
    JOIN   courses     c ON c.course_id     = s.course_id
    WHERE  e.student_id = p_student_id
      AND  g.is_final   = 1
      AND  g.grade_points IS NOT NULL;

    RETURN COALESCE(v_gpa, 0.000);
END$$

-- ============================================================
-- fn_credit_hours_completed: Credits earned in a semester
-- ============================================================
DROP FUNCTION IF EXISTS fn_credit_hours_completed$$
CREATE FUNCTION fn_credit_hours_completed(
    p_student_id INT UNSIGNED,
    p_semester   VARCHAR(10)
)
RETURNS SMALLINT UNSIGNED
READS SQL DATA
BEGIN
    DECLARE v_hours SMALLINT UNSIGNED DEFAULT 0;

    SELECT COALESCE(SUM(c.credit_hours), 0) INTO v_hours
    FROM   enrollments e
    JOIN   sections    s ON s.section_id    = e.section_id
    JOIN   courses     c ON c.course_id     = s.course_id
    JOIN   grades      g ON g.enrollment_id = e.enrollment_id
    WHERE  e.student_id = p_student_id
      AND  s.semester   = p_semester
      AND  g.is_final   = 1
      AND  g.letter_grade NOT IN ('F','W','I');

    RETURN v_hours;
END$$

DELIMITER ;

-- ============================================================
-- Step 1: Create Roles
-- ============================================================
CREATE ROLE IF NOT EXISTS student_portal;
CREATE ROLE IF NOT EXISTS faculty_role;
CREATE ROLE IF NOT EXISTS registrar_role;
CREATE ROLE IF NOT EXISTS admin_role;

-- ============================================================
-- Step 2: Grant Privileges to Roles
-- ============================================================

-- Student Portal: Read own data + enroll via procedure
GRANT SELECT ON fcrs.courses         TO student_portal;
GRANT SELECT ON fcrs.sections        TO student_portal;
GRANT SELECT ON fcrs.departments     TO student_portal;
GRANT SELECT ON fcrs.prerequisites   TO student_portal;
-- Students can only see their own rows via views (not direct table access)
GRANT SELECT ON fcrs.v_student_transcript   TO student_portal;
GRANT SELECT ON fcrs.v_course_availability  TO student_portal;
GRANT EXECUTE ON PROCEDURE fcrs.sp_enroll_student TO student_portal;
GRANT EXECUTE ON FUNCTION  fcrs.fn_letter_grade   TO student_portal;

-- Faculty Role: Grade entry + view own schedule
GRANT SELECT ON fcrs.courses      TO faculty_role;
GRANT SELECT ON fcrs.sections     TO faculty_role;
GRANT SELECT ON fcrs.enrollments  TO faculty_role;
GRANT SELECT ON fcrs.students     TO faculty_role;
GRANT SELECT, UPDATE (
    numeric_grade, letter_grade, grade_points, graded_at, graded_by, is_final
)                                  ON fcrs.grades TO faculty_role;
GRANT SELECT ON fcrs.v_faculty_schedule   TO faculty_role;
GRANT EXECUTE ON PROCEDURE fcrs.sp_calculate_gpa TO faculty_role;
GRANT EXECUTE ON FUNCTION  fcrs.fn_letter_grade  TO faculty_role;

-- Registrar Role: Full data + enrollment management
GRANT SELECT, INSERT, UPDATE ON fcrs.students     TO registrar_role;
GRANT SELECT, INSERT, UPDATE ON fcrs.enrollments  TO registrar_role;
GRANT SELECT, INSERT, UPDATE ON fcrs.grades       TO registrar_role;
GRANT SELECT, INSERT, UPDATE ON fcrs.sections     TO registrar_role;
GRANT SELECT ON fcrs.audit_log                    TO registrar_role;
GRANT SELECT ON fcrs.v_student_transcript         TO registrar_role;
GRANT SELECT ON fcrs.v_department_stats           TO registrar_role;
GRANT EXECUTE ON PROCEDURE fcrs.sp_enroll_student    TO registrar_role;
GRANT EXECUTE ON PROCEDURE fcrs.sp_generate_transcript TO registrar_role;
GRANT EXECUTE ON PROCEDURE fcrs.sp_calculate_gpa     TO registrar_role;

-- Admin Role: Full schema access
GRANT ALL PRIVILEGES ON fcrs.* TO admin_role;

-- ============================================================
-- Step 3: Create Application Users and Assign Roles
-- ============================================================
CREATE USER IF NOT EXISTS 'portal_app'@'10.0.1.%'
    IDENTIFIED WITH caching_sha2_password BY 'Portal@2025!Secure'
    REQUIRE SSL
    WITH MAX_USER_CONNECTIONS 200;
GRANT student_portal TO 'portal_app'@'10.0.1.%';
SET DEFAULT ROLE student_portal FOR 'portal_app'@'10.0.1.%';

CREATE USER IF NOT EXISTS 'faculty_app'@'10.0.1.%'
    IDENTIFIED WITH caching_sha2_password BY 'Faculty@2025!Secure'
    REQUIRE SSL;
GRANT faculty_role TO 'faculty_app'@'10.0.1.%';
SET DEFAULT ROLE faculty_role FOR 'faculty_app'@'10.0.1.%';

CREATE USER IF NOT EXISTS 'registrar_app'@'10.0.0.%'
    IDENTIFIED WITH caching_sha2_password BY 'Registrar@2025!Secure'
    REQUIRE SSL;
GRANT registrar_role TO 'registrar_app'@'10.0.0.%';
SET DEFAULT ROLE registrar_role FOR 'registrar_app'@'10.0.0.%';

-- ============================================================
-- v_student_transcript
-- ============================================================
CREATE OR REPLACE VIEW v_student_transcript AS
SELECT
    s.student_id,
    s.fsuid,
    CONCAT(s.first_name, ' ', s.last_name)  AS student_name,
    sec.semester,
    c.course_code,
    c.title                                  AS course_title,
    c.credit_hours,
    g.numeric_grade,
    g.letter_grade,
    g.grade_points,
    g.is_final,
    i.last_name                              AS instructor_last
FROM students    s
JOIN enrollments e   ON e.student_id    = s.student_id
JOIN sections    sec ON sec.section_id  = e.section_id
JOIN courses     c   ON c.course_id     = sec.course_id
LEFT JOIN grades g   ON g.enrollment_id = e.enrollment_id
LEFT JOIN instructors i ON i.instructor_id = sec.instructor_id
WHERE  e.status IN ('enrolled','completed');

-- ============================================================
-- v_course_availability
-- ============================================================
CREATE OR REPLACE VIEW v_course_availability AS
SELECT
    sec.section_id,
    c.course_code,
    c.title,
    c.credit_hours,
    sec.semester,
    sec.section_num,
    sec.schedule_days,
    SEC_TO_TIME(TIME_TO_SEC(sec.start_time)) AS start_time,
    SEC_TO_TIME(TIME_TO_SEC(sec.end_time))   AS end_time,
    sec.room,
    sec.delivery_mode,
    sec.capacity,
    sec.enrolled_count,
    sec.capacity - sec.enrolled_count        AS seats_available,
    sec.status,
    CONCAT(i.first_name, ' ', i.last_name)   AS instructor_name
FROM sections   sec
JOIN courses    c ON c.course_id      = sec.course_id
LEFT JOIN instructors i ON i.instructor_id = sec.instructor_id
WHERE  sec.status != 'cancelled';

-- ============================================================
-- v_faculty_schedule
-- ============================================================
CREATE OR REPLACE VIEW v_faculty_schedule AS
SELECT
    i.instructor_id,
    CONCAT(i.first_name, ' ', i.last_name) AS instructor_name,
    i.email,
    d.dept_name,
    sec.semester,
    c.course_code,
    c.title,
    sec.section_num,
    sec.schedule_days,
    sec.start_time,
    sec.end_time,
    sec.room,
    sec.enrolled_count,
    sec.capacity,
    sec.delivery_mode
FROM instructors i
JOIN departments d   ON d.dept_id      = i.dept_id
JOIN sections    sec ON sec.instructor_id = i.instructor_id
JOIN courses     c   ON c.course_id    = sec.course_id
WHERE sec.status != 'cancelled';

-- ============================================================
-- v_department_stats
-- ============================================================
CREATE OR REPLACE VIEW v_department_stats AS
SELECT
    d.dept_id,
    d.dept_code,
    d.dept_name,
    COUNT(DISTINCT s.student_id)    AS declared_majors,
    COUNT(DISTINCT c.course_id)     AS active_courses,
    COUNT(DISTINCT i.instructor_id) AS instructors,
    ROUND(AVG(s.gpa), 3)            AS avg_student_gpa,
    SUM(CASE WHEN s.enrollment_status = 'active' THEN 1 ELSE 0 END) AS active_students
FROM   departments d
LEFT JOIN students     s ON s.dept_id = d.dept_id
LEFT JOIN courses      c ON c.dept_id = d.dept_id AND c.is_active = 1
LEFT JOIN instructors  i ON i.dept_id = d.dept_id AND i.is_active = 1
GROUP BY d.dept_id, d.dept_code, d.dept_name;