CREATE OR REPLACE PACKAGE pkg_uni IS
    PROCEDURE proc_add_stud (stud_args students%ROWTYPE);
    PROCEDURE proc_add_lect (lect_args lecturers%ROWTYPE);
    PROCEDURE proc_add_course (course_args courses%ROWTYPE);
    PROCEDURE proc_add_faculty (fac_args faculties%ROWTYPE);
    PROCEDURE proc_choose_course (chosen_args chosen_courses%ROWTYPE);
    PROCEDURE proc_add_grade (grades_args grades%ROWTYPE);
    FUNCTION grade_in_course (v_chosen_id NUMBER) RETURN NUMBER;
    FUNCTION stud_total_credits (stud_id CHAR) RETURN NUMBER;
    PROCEDURE change_course_status (v_chosen_id NUMBER, v_status CHAR) RETURN NUMBER;
    FUNCTION get_lect (v_email VARCHAR2) RETURN NUMBER;
    FUNCTION get_stud_id (v_email VARCHAR2) RETURN NUMBER;
    FUNCTION get_chosen_course_id (v_course_id NUMBER, v_stud_id NUMBER, v_num NUMBER) RETURN NUMBER;
    FUNCTION get_course_id (v_name VARCHAR2) RETURN NUMBER;
END pkg_uni;

CREATE OR REPLACE PACKAGE pkg_uni IS
    PROCEDURE proc_add_stud (stud_args students%ROWTYPE) IS
        BEGIN
            INSERT INTO students
            VALUES (stud_args.id, --student's id is 11 cifriani piradi nomeri piradobashi rac weria, amitom me ver davugenerireb sequencit ;)
                    stud_args.first_name,
                    stud_args.last_name,
                    stud_args.email,
                    stud_args.birth_date,
                    stud_args.grant_percentage,
                    stud_args.major_id,
                    stud_args.minor_id,
                    stud_args.stud_status);
        END;

    PROCEDURE proc_add_lect (lect_args lecturers%ROWTYPE) IS
        BEGIN
            INSERT INTO lecturers
            VALUES (lect_args.id,
                    lect_aegs.first_name,
                    lect_args.last_name,
                    lect_args.email,
                    lect_args.hire_date,
                    lect_args.salary);
        END;

    PROCEDURE proc_add_course (course_args courses%ROWTYPE) IS
        BEGIN
            INSERT INTO courses
            VALUES (courses_id_seq.NEXTVAL,
                    course_args.course_name,
                    course_args.lecturer_id,
                    course_args.seminarist_id,
                    course_args.credits,
                    course_args.faculty_id);
        END;

    PROCEDURE proc_add_faculty (fac_args faculties%ROWTYPE) IS
        BEGIN
            INSERT INTO faculties
            VALUES (facs_id_seq.NEXTVAL,
                    fac_args.faculty_name
                    fac_args.dean_id,
                    fac_args.price_per_semester);
        END;

    PROCEDURE proc_choose_course (chosen_args chosen_courses%ROWTYPE)
        IS
            x_not_active EXCEPTION;
            v_stud_idnum NUMBER;
        BEGIN
            SELECT students.id INTO v_stud_idnum
            FROM students

            IF v_stud_idnum = chosen_args.id_num THEN
                RAISE x_not_active;
            END IF;

            INSERT INTO chosen_courses
            VALUES (chos_id_seq.NEXTVAL,
                    chosen_args.course,
                    chosen_args.students
                    chosen_args.passed);

            EXCEPTION
                WHEN x_not_active THEN
                    DBMS_OUTPUT.PUT_LINE('cannot choose new course for an inactive student');
        END;

    PROCEDURE proc_add_grade (grades_args grades%ROWTYPE)
        IS
            x_invalid_grade EXCEPTION; -- თუ ჯამურად 100-ზე მეტი ჰყავს ვერ ჩაამატებს ქულას
            x_inactive_course EXCEPTION; -- თუ კურსი უკვე გავლილი აქვს ვერ ჩაამატებს ქულას
            v_course_status chosen_courses.passed%TYPE;
        BEGIN
            SELECT passed INTO v_course_status
            FROM chosen_courses
            WHERE id = grades.chosen_id;

            IF passed IS NOT NULL THEN
                RAISE x_inactive_course;
            END IF;

            IF grade_in_course(grades_args.chosen_id) + grades_args.score_grade > 100 THEN
                RAISE x_invalid_grade;
            END IF;

            INSERT INTO grades
            VALUES (grades_args.chosen_id,
                    grades_args.score_name,
                    grades_args.score_grade);

            EXCEPTION
                WHEN x_invalid_grade THEN
                    DBMS_OUTPUT.PUT_LINE('cannot submit new grade, because the sum exceeds 100.');

            EXCEPTION
                WHEN x_inactive_course THEN
                    DBMS_OUTPUT.PUT_LINE('cannot submit a new grade to the passed or failed course');

        END;

    -- am funqcias gamoidzaxebs leqtori da chauwers students archeuli kursi chaabara tu vera, tavidan arafera chawers da null iqneba:
    -- (chosen_courses table, passed column, yes or no)
    PROCEDURE passed_or_failed_course(v_chosen_id NUMBER, v_done VARCHAR2) IS
        BEGIN
            INSERT INTO chosen_courses (passed)
            VALUES v_done
            WHERE id = v_chosen_id;
        END;

    FUNCTION grade_in_course (v_chosen_id NUMBER) RETURN NUMBER
        IS
            v_grade NUMBER;
        BEGIN
            SELECT SUM(gr.score_grade) INTO v_grade
            FROM grades gr
            WHERE gr.chosen_id = v_chosen_id
            RETURN v_grade
        END;

    FUNCTION stud_total_credits (stud_id CHAR) RETURN NUMBER
        IS
            v_credits course.credits%TYPE;
        BEGIN
            SELECT SUM(co.credits) INTO v_credits
            FROM course co join chosen_courses ch
            ON co.id = ch.course
            WHERE ch.student = stud_id;
            RETURN v_credits;
        END;

    --student passed or failed the course
    PROCEDURE change_course_status (v_chosen_id NUMBER, v_status CHAR) RETURN NUMBER
        IS
            x_insufficient_score EXCEPTION;
        BEGIN
            IF grade_in_course(v_chosen_id) < 51 THEN
                RAISE x_insufficient_score;
            END IF;

            UPDATE chosen_courses
            SET passed = v_status
            WHERE id = v_chosen_id

            EXCEPTION
                WHEN x_insufficient_score THEN
                    DBMS_OUTPUT.PUT_LINE('cannot pass a course with less than 51 points');
        END;

    FUNCTION get_lect_id (v_email VARCHAR2) RETURN NUMBER
        IS
            v_lect_id lecturers.id&TYPE;
        BEGIN
            SELECT id INTO v_lect_id
            FROM lecturers
            WHERE email = v_email;
            RETURN v_lect_id;
        END;

    FUNCTION get_stud_id (v_email VARCHAR2) RETURN NUMBER
        IS
            v_stud_id students.id&TYPE;
        BEGIN
            SELECT id INTO v_stud_id
            FROM students
            WHERE email = v_email;
            RETURN v_stud_id;
        END;

    --რადგან ამ სტუდენტ აიდის ადამიანმა ეს საგანი შეიძლება რამდენჯერმე გაიაროს, ამიტომ გადმოგვეცემა რიცხვი: მერამდენედ გავლილის ამოღება გვინდა
    FUNCTION get_chosen_course_id (v_course_id NUMBER, v_stud_id NUMBER, v_num NUMBER) RETURN NUMBER
        IS
            v_chosen_course_id chosen_courses.id&TYPE;
        BEGIN
            SELECT id INTO v_chosen_course_id
            FROM chosen_course
            WHERE course = v_course_id AND student = v_stud_id AND ROWNUM = v_num
            ORDER BY id;
            RETURN v_chosen_course_id;
        END;

    FUNCTION get_course_id (v_name VARCHAR2) RETURN NUMBER
        IS
            v_name course.id&TYPE;
        BEGIN
            SELECT id INTO v_name
            FROM courses
            WHERE course_name = v_name;
            RETURN v_name;
        END;

END pkg_uni;