CREATE OR REPLACE PACKAGE BODY unterricht_pkg_tw04n166 AS

    -- Prozedur, um die Vortragenden eines Faches zu entfernen
    PROCEDURE drop_fachzuordnung(mykbz IN VARCHAR)
    IS
        CURSOR GET_VORTRAGENDE IS   -- Cursor ueber alle Vortragenden des Faches vom Studienjahr
            SELECT personal_tw04n166.personen_id, personal_tw04n166.stunden
            FROM personal_tw04n166
            JOIN unterricht ON personal_tw04n166.personen_id = unterricht.personen_id
            JOIN faecher ON unterricht.fach_id = faecher.fach_id
            WHERE faecher.kbz = mykbz AND unterricht.studienjahr = p_studienjahr;

            myfach_id faecher.fach_id%TYPE := 0;
            mystunden INT := 0;
            result_votragende GET_VORTRAGENDE%ROWTYPE;
            myname VARCHAR(50);
    BEGIN

        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        -- pruefe, ob uebergebenes Fach existiert
        BEGIN
            SELECT fach_id INTO myfach_id
            FROM faecher
            WHERE kbz = mykbz;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('Das Fach "' || mykbz || '" existiert nicht.');
                    RETURN;
        END;

        --DBMS_OUTPUT.PUT_LINE(mykbz);

        OPEN GET_VORTRAGENDE;
        LOOP
            FETCH GET_VORTRAGENDE INTO result_votragende;
            EXIT WHEN GET_VORTRAGENDE%NOTFOUND;

            -- Update der Stunden
            
            update_stunden(result_votragende.personen_id);
            

            -- Ueberpruefung der gefundenen Lektoren:

            /*DBMS_OUTPUT.PUT_LINE(result_votragende.personen_id);
              SELECT personen.name INTO myname
              FROM personen WHERE personen.personen_id = result_votragende.personen_id;
              DBMS_OUTPUT.PUT_LINE(myname);*/

        END LOOP;
        CLOSE GET_VORTRAGENDE;

        -- loeschen der Lektor-Relation in der Unterricht-Tabelle
        DELETE FROM unterricht_tw04n166
        WHERE fach_id = myfach_id AND studienjahr = p_studienjahr;

    END drop_fachzuordnung;

    -- Prozedur, um das Studienjahr zu setzen
    PROCEDURE set_studienjahr(jahr IN INT) IS
    BEGIN
        p_studienjahr := jahr;

        EXCEPTION
            WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Fehler beim Setzen des Studienjahres!');
    END set_studienjahr;

    -- Funktion, die das gesetzte Studienjahr zurueckliefert
    FUNCTION get_studienjahr RETURN INT
    IS
    BEGIN
        RETURN p_studienjahr;
    END get_studienjahr;

    -- Funktion, die ueberprueft, ob das gesetzte Studienjahr verfuegbar ist
    FUNCTION check_studienjahr RETURN BOOLEAN
    IS
        CURSOR GET_STUDIENJAHRE IS
            SELECT studienjahr FROM unterricht;
        mystudienjahr unterricht.studienjahr%TYPE;
    BEGIN

        IF p_studienjahr = 0 THEN     -- studienjahr ist auf default-Wert --> wurde noch nicht gesetzt
            RETURN FALSE;
        END IF;

        OPEN GET_STUDIENJAHRE;
        LOOP

            FETCH GET_STUDIENJAHRE INTO mystudienjahr;
            EXIT WHEN GET_STUDIENJAHRE%NOTFOUND;

            IF mystudienjahr = p_studienjahr THEN     -- gueltiges studienjahr
                CLOSE GET_STUDIENJAHRE;
                RETURN TRUE;
            END IF;

        END LOOP;
        CLOSE GET_STUDIENJAHRE;

        RETURN FALSE;   -- kein gueltiges studienjahr gefunden
    END check_studienjahr;

    -- aktualisiert die Anzahl der unterrichtenden Stunden fuer einen Vortragenden
    PROCEDURE update_stunden(vortragender_id IN INT)
    IS
        mystunden faecher.stunden%TYPE;
        myid personal_tw04n166.personen_id%TYPE;
    BEGIN

        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        -- pruefe, ob zu uebergebener ID ein Vortragender existiert
        BEGIN
            SELECT personen_id INTO myid
            FROM personal_tw04n166
            WHERE personen_id = vortragender_id;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('Ein Vortragender mit der ID: ' || vortragender_id || ' existiert nicht!');
                    RETURN;
        END;

        BEGIN
            -- Stunden berechnen
            SELECT sum(faecher.stunden) INTO mystunden
            FROM personal_tw04n166
            JOIN unterricht_tw04n166 ON personal_tw04n166.personen_id = unterricht_tw04n166.unterricht_id
            JOIN faecher ON unterricht_tw04n166.fach_id = faecher.fach_id
            WHERE unterricht_tw04n166.studienjahr = p_studienjahr AND personal_tw04n166.personen_id = vortragender_id;

            IF mystunden IS NULL THEN
                --DBMS_OUTPUT.PUT_LINE('Stunden fuer den Vortragenden mit der ID: ' || vortragender_id || ' konnten nicht aktualisiert werden!');
                --RETURN;
                mystunden := 0;
            END IF;

            --DBMS_OUTPUT.PUT_LINE('Stunden: ' || mystunden);

            -- Stunden aktualisieren
            UPDATE personal_tw04n166
            SET stunden = mystunden
            WHERE personen_id = vortragender_id;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('Stunden fuer den Vortragenden mit der ID: ' || vortragender_id || ' konnten nicht aktualisiert werden!');
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Fehler bei der Aktualisierung der Stunden des Vortragenden mit der ID: ' || vortragender_id || '!');
        END;

    END update_stunden;

    -- Prozedur, die einen Lektor einem zu unterrichtenden Fach zuordnet (sofern nicht voll besetzt)
    PROCEDURE assign_lektor_to_fach(myname IN VARCHAR, mykbz IN VARCHAR)
    IS
        mylektor_id personal_tw04n166.personen_id%TYPE;
        myfach_id faecher.fach_id%TYPE;
        lektor_summe INT := 0;
    BEGIN

        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        -- ID des Vortragenden ermitteln
        BEGIN
            SELECT personal_tw04n166.personen_id INTO mylektor_id
            FROM personal_tw04n166
            JOIN personen ON personal_tw04n166.personen_id = personen.personen_id
            WHERE personen.name = myname;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('Der Vortragende "' || myname || '" konnte nicht gefunden werden!');
                    RETURN;
        END;

        -- ID des Faches ermitteln
        BEGIN
            SELECT fach_id INTO myfach_id
            FROM faecher
            WHERE kbz = mykbz;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('Das Fach "' || mykbz || '" konnte nicht gefunden werden!');
                    RETURN;
        END;

        -- Ueberpruefung, ob der Vortragende bereits dem Fach zugeordnet ist
        DECLARE
            zuordnung_exists INT := 0;
        BEGIN
            SELECT COUNT(*) INTO zuordnung_exists
            FROM unterricht_tw04n166
            WHERE personen_id = mylektor_id AND fach_id = myfach_id;

            IF zuordnung_exists > 0 THEN
                DBMS_OUTPUT.PUT_LINE('Der Vortragende "' || myname || '" ist dem Fach "' || mykbz || '" bereits zugeteilt!');
                RETURN;
            END IF;

            EXCEPTION
                WHEN NO_DATA_FOUND THEN null;
        END;

        -- Anzahl der Vortragenden ermitteln
        BEGIN
            SELECT COUNT(personen_id) INTO lektor_summe
            FROM unterricht_tw04n166
            WHERE fach_id = myfach_id AND studienjahr = p_studienjahr;

            --DBMS_OUTPUT.PUT_LINE('Summe: ' || lektor_summe);
           
            IF lektor_summe > 3 THEN    -- max. Anzahl kontrollieren
                DBMS_OUTPUT.PUT_LINE('Die maximale Anzahl der Vortragenden (4) vom Fach "' || mykbz || '" ist bereits erreicht!');
                RETURN;
            END IF;

            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Fehler beim Ermitteln der Anzahl der Vortragenden zum Fach "' || mykbz || '"!');
        END;

        -- Tupel in die Unterricht-Tabelle einfuegen
        DECLARE
            max_id INT := 0;--unterricht_tw04n166.unterricht_id%TYPE;
            myreihung unterricht_tw04n166.reihung%TYPE;
        BEGIN
            -- hoechsten PK-Wert in der Tabelle unterricht_tw04n166 fuer das Einfuegen vormerken und dann um eins erhoehen (neuer Index fuer den PK)
            SELECT MAX(unterricht_id) INTO max_id
            FROM unterricht_tw04n166;

            max_id := max_id + 1;
            myreihung := lektor_summe + 1;

            -- Zuordnung erstellen
            INSERT INTO unterricht_tw04n166(unterricht_id, personen_id, fach_id, studienjahr, reihung)
            VALUES (max_id, mylektor_id, myfach_id, p_studienjahr, myreihung);

            -- Stunden updaten
            update_stunden(mylektor_id);

            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Fehler beim Erstellen der Zuordnung zwischen dem Vortragenden "' || myname || '" und dem Fach "' || mykbz || '"!');
        END;

    END assign_lektor_to_fach;

    -- Prozedur, die die Anzahl der im Studienjahr unzugeordneten Faecher ausgibt
    PROCEDURE print_unassigned_faecher
    IS
        summe_gesamt INT := 0;
        summe_assigned INT := 0;
        diff INT := 0;
    BEGIN

        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        SELECT COUNT(*) INTO summe_gesamt   -- alle Faecher
        FROM faecher;

        SELECT DISTINCT COUNT(faecher.kbz) INTO summe_assigned  -- zugeordnete Faecher
        FROM faecher
        JOIN unterricht_tw04n166 ON faecher.fach_id = unterricht_tw04n166.fach_id
        WHERE unterricht_tw04n166.studienjahr = p_studienjahr;

        diff := summe_gesamt - summe_assigned;
        DBMS_OUTPUT.PUT_LINE('Summe der unzugeordneten Faecher im Jahr ' || p_studienjahr || ': ' || diff);

        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Fehler beim ermitteln der unzugeordneten Stunden vom Studienjahr ' || p_studienjahr || '!');

    END print_unassigned_faecher;

    -- Prozedur, um die Arbeitstabelle, mit der die Lehrfaecherverteilung generiert werden kann, mit den notwendigen Daten des aktuelle Studienjahres zu fuellen
    PROCEDURE fill_arbeitstabelle
    IS
    BEGIN
    
        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        -- alte Eintraege des Studienjahres zuerst entfernen
        DELETE FROM arbeit_tw04n166 WHERE studienjahr = p_studienjahr;

        -- Arbeitstabelle fuellen
        INSERT INTO arbeit_tw04n166(personen_id, fach_id, semester, studienjahr, stunden)
        SELECT u.personen_id, u.fach_id, u.semester, u.studienjahr, faecher.stunden
        FROM unterricht_tw04n166 u
        JOIN faecher ON u.fach_id = faecher.fach_id
        WHERE u.studienjahr = p_studienjahr;

        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Fehler beim Fuellen der Arbeitstabelle!');
        
    END fill_arbeitstabelle;

    -- Prozedur, um die Stunden aus der Arbeitstabelle in die Kreuztabelle einzufuegen
    PROCEDURE create_verteilung
    IS
        CURSOR GET_FAECHER IS
            SELECT DISTINCT arbeit_tw04n166.fach_id, faecher.kbz
            FROM arbeit_tw04n166
            JOIN faecher ON arbeit_tw04n166.fach_id = faecher.fach_id
            WHERE studienjahr = p_studienjahr;

         CURSOR GET_VORTRAGENDE IS
            SELECT DISTINCT personen_id
            FROM arbeit_tw04n166;

         result_faecher GET_FAECHER%ROWTYPE;
         mylektor_id arbeit_tw04n166.personen_id%TYPE;
         mystunden arbeit_tw04n166.stunden%TYPE;

         TYPE stunden_array IS TABLE OF FLOAT INDEX BY BINARY_INTEGER;      -- Array mit allen Stunden eines Faches (5 Lektoren)
         lektor_stunden stunden_array;
         i INT := 0;    -- Indexvariable fuer das Array 
    BEGIN

        -- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
        IF NOT check_studienjahr THEN
            DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
            RETURN;
        END IF;

        DELETE FROM kreuztabelle_tw04n166;  -- Tabelle leeren

        -- alle Faecher durchlaufen
        OPEN GET_FAECHER;
        LOOP
            FETCH GET_FAECHER INTO result_faecher;
            EXIT WHEN GET_FAECHER%NOTFOUND;

            i := 0;     -- Indexvariable fuer das Array zuruecksetzen

            -- alle Lektoren durchlaufen
            OPEN GET_VORTRAGENDE;
            LOOP
                FETCH GET_VORTRAGENDE INTO mylektor_id;
                EXIT WHEN GET_VORTRAGENDE%NOTFOUND;

                BEGIN 
                    -- Stunden ermitteln
                    SELECT stunden INTO mystunden
                    FROM arbeit_tw04n166
                    WHERE fach_id = result_faecher.fach_id AND personen_id = mylektor_id;

                    -- wenn beim Fach zu dem Lektor keine Stunden gefunden wurden, Stunden auf 0 setzen
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                            mystunden := 0;
                END;

                -- Stunden des aktuellen Lektors ins Array schreiben
                i := i + 1;
                lektor_stunden(i) := mystunden;
            END LOOP;
            CLOSE GET_VORTRAGENDE;

            -- Werte in die Kreuztabelle einfuegen
            INSERT INTO kreuztabelle_tw04n166
            VALUES(result_faecher.fach_id, result_faecher.kbz, lektor_stunden(1), lektor_stunden(2), lektor_stunden(3), lektor_stunden(4), lektor_stunden(5));

        END LOOP;
        CLOSE GET_FAECHER;

        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Die Lehrfaecherverteilung konnte nicht erstellt werden!');

    END create_verteilung;

END unterricht_pkg_tw04n166;