CREATE OR REPLACE PACKAGE exc4 AS
    currentYear NUMBER;
    PROCEDURE requireValidYear;
    PROCEDURE removeTeachers(subjectDesc VARCHAR2);
    FUNCTION getTeacherCount(subjectID NUMBER) RETURN NUMBER;
    PROCEDURE addTeacherToSubject(subjectID NUMBER, teacherID NUMBER, semester NUMBER, 
                                  semestertype NUMBER, orderd NUMBER);
    FUNCTION subjectsWithoutTeachersCount RETURN NUMBER;
    PROCEDURE createTeacherSubjectsTable;
END exc4;

CREATE OR REPLACE PACKAGE BODY exc4 AS
    PROCEDURE requireValidYear IS
    BEGIN
        IF currentYear < 1990 THEN
            raise_application_error(-20000, 'Invalid year given!');
        END IF;
        -- DBMS_OUTPUT.PUT_LINE('Hello Wolrd = ' || exc4.currentYear);
    END;
    
    PROCEDURE removeTeachers(subjectDesc VARCHAR2) IS
        subject_id NUMBER;
    BEGIN
        -- check if a valid year is defined
        requireValidYear();
        
        -- selecting the subject id belonging to the given subject description
        SELECT fach_id INTO subject_id FROM faecher f
        WHERE f.kbz = subjectDesc;
        
        -- removing all teachers linked to the subject
        DELETE FROM unterricht_tw04n181 u WHERE u.fach_id = subject_id
        AND u.studienjahr = currentYear;
    END;
    
    FUNCTION getTeacherCount(subjectID NUMBER) RETURN NUMBER IS
        teacher_count NUMBER;
    BEGIN
        -- check if a valid year is defined
        requireValidYear();
        
        -- selecting the number of teachers linked with the given
        -- subject
        SELECT COUNT(*) INTO teacher_count FROM unterricht_tw04n181 u
        WHERE u.fach_id = subjectID AND u.studienjahr = currentYear;
        
        RETURN teacher_count;
    END;
    
    PROCEDURE addTeacherToSubject(subjectID NUMBER, teacherID NUMBER, semester NUMBER, 
                                  semestertype NUMBER, orderd NUMBER) IS
    BEGIN
        -- check if a valid year is defined
        requireValidYear();
        
        -- check if there are less than 4 teachers added yet
        IF getTeacherCount(subjectID) >= 4 THEN
            raise_application_error(-20001, '4 teachers already added to the subject! No more allowed.');
        END IF;
        
        -- add a teacher to the subject
        INSERT INTO unterricht_tw04n181 (unterricht_id, personen_id, fach_id, studienjahr, semester, semestertyp, reihung)
        VALUES (unterricht_tw04n181_seq.nextval, subjectID, teacherID, currentYear, semester, semestertype, orderd);
    END;
    
    FUNCTION subjectsWithoutTeachersCount RETURN NUMBER IS
        swnt NUMBER;
    BEGIN
        SELECT COUNT(*) INTO swnt FROM faecher f WHERE (
            SELECT COUNT(*) FROM unterricht_tw04n181 u
            WHERE u.fach_id = f.fach_id AND 
                  u.studienjahr = currentYear) = 0;
        RETURN swnt;
    END;
    
    PROCEDURE createTeacherSubjectsTable AS
        stmt VARCHAR2(1000) := 'CREATE TABLE teachersubjects (teacher VARCHAR2(255), ';
        total NUMBER := 0;
        idx NUMBER := 0;
        id NUMBER;

        CURSOR faecher IS
            SELECT * FROM faecher f;

    BEGIN
        DBMS_OUTPUT.ENABLE(1000000);
        FOR fach IN faecher LOOP
            total := total + 1;
        END LOOP;
        FOR fach IN faecher LOOP
            stmt := stmt || '"' || fach.kbz || '" NUMBER';
            IF idx < total-1  THEN
                stmt := stmt || ',';
            END IF;
            idx := idx + 1;
        END LOOP;
        stmt := stmt || ')';
        -- EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS teachersubjects';
        EXECUTE IMMEDIATE stmt;
        -- DBMS_OUTPUT.PUT_LINE(stmt);

    END;
    
    PROCEDURE
END exc4;