--- **********************************************************************************
---
--- VORBEREITUNGS-STATEMENTS
--- Originaltabellen werden kopiert
---
---
--- **********************************************************************************
DROP TABLE if11b022_flug;
DROP TABLE if11b022_passagierliste;
DROP TABLE if11b022_gepaeck;

CREATE TABLE if11b022_crew AS SELECT * FROM crew;
CREATE TABLE if11b022_flug AS SELECT * FROM flug;
CREATE TABLE if11b022_passagierliste AS SELECT * FROM passagierliste;
CREATE TABLE if11b022_gepaeck AS SELECT * FROM gepaeck;

--- **********************************************************************************
--- 1)
--- Schreiben Sie ein Package, das Flugänderungen implementiert:
--- Dieses Package soll folgende Funktionalitäten beinhalten: (Überlegen Sie sich jeweils geeignete Parameter!)
--- a. ÄnderungdesAbfluggateseinesFluges(2P.)
--- b. AnlegeneinesneuenFluges(2P.)
--- c. Manuelles Umbuchen von Passagieren (inkl. Gepäck!) (2P.)
--- **********************************************************************************
CREATE OR REPLACE PACKAGE if11b022_pkg_bsp1 AS
    PROCEDURE gate_aendern(a_flugID IN NUMBER, a_gateID IN NUMBER);
  PROCEDURE flug_anlegen(a_flugzeugID IN NUMBER, a_gateID IN NUMBER, a_flughafen_abflug IN NUMBER, a_flughafen_destination IN NUMBER, a_abflugzeit IN TIMESTAMP, a_flugnummer IN VARCHAR2, a_dauer IN NUMBER);
    PROCEDURE passagier_umbuchen(a_flugID IN NUMBER, a_personID IN NUMBER, a_new_flugID IN NUMBER);
END if11b022_pkg_bsp1;
/

CREATE OR REPLACE PACKAGE BODY if11b022_pkg_bsp1 AS
    PROCEDURE gate_aendern(a_flugID IN NUMBER, a_gateID IN NUMBER) IS

    BEGIN
    UPDATE if11b022_flug SET gateID = a_gateID WHERE flugID = a_flugID;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      raise_application_error(-20100, 'FlugID und/oder GateID ungueltig');
    WHEN OTHERS THEN
      raise_application_error(-20101, 'Unerwarteter Fehler ist aufgetreten');
  END;

  PROCEDURE flug_anlegen(a_flugzeugID IN NUMBER, a_gateID IN NUMBER, a_flughafen_abflug IN NUMBER, a_flughafen_destination IN NUMBER, a_abflugzeit IN TIMESTAMP, a_flugnummer IN VARCHAR2, a_dauer IN NUMBER) IS
  v_check NUMBER;
  ex_no_flugzeug EXCEPTION;
  ex_no_gate EXCEPTION;
  ex_no_flughafen_abflug EXCEPTION;
  ex_no_flughafen_destination EXCEPTION;
  BEGIN
    SELECT COUNT(*) INTO v_check FROM flugzeug WHERE flugzeug.flugzeugID = a_flugzeugID;
    IF v_check <= 0 THEN
      RAISE ex_no_flugzeug;
    END IF;

    SELECT COUNT(*) INTO v_check FROM gate WHERE gate.gateID = a_gateID;
    IF v_check <= 0 THEN
      RAISE ex_no_gate;
    END IF;

    SELECT COUNT(*) INTO v_check FROM flughafen WHERE flughafen.flughafenID = a_flughafen_abflug;
    IF v_check <= 0 THEN
      RAISE ex_no_flughafen_abflug;
    END IF;

    SELECT COUNT(*) INTO v_check FROM flughafen WHERE flughafen.flughafenID = a_flughafen_destination;
    IF v_check <= 0 THEN
      RAISE ex_no_flughafen_destination;
    END IF;

    INSERT INTO if11b022_flug (flugID, flugzeugID, gateID, flughafen_abflug, flughafen_destination, abflugzeit, flugnummer, dauer)
    VALUES ((SELECT MAX(flugID)+1 FROM if11b022_flug), a_flugzeugID, a_gateID, a_flughafen_abflug, a_flughafen_destination, a_abflugzeit, a_flugnummer, a_dauer);

    EXCEPTION
      WHEN ex_no_flugzeug THEN
        raise_application_error(-20100, 'Flugzeug existiert nicht!');
      WHEN ex_no_gate THEN
        raise_application_error(-20101, 'Gate existiert nicht!');
      WHEN ex_no_flughafen_abflug THEN
        raise_application_error(-20102, 'Abflug-Flughafen existiert nicht!');
      WHEN ex_no_flughafen_destination THEN
        raise_application_error(-20103, 'Ziel-Flughafen existiert nicht!');
      WHEN OTHERS THEN
        raise_application_error(-20104, 'Unerwarteter Fehler ist aufgetreten');
  END;

  PROCEDURE passagier_umbuchen(a_flugID IN NUMBER, a_personID IN NUMBER, a_new_flugID IN NUMBER) IS
    v_check NUMBER;
    ex_no_flug EXCEPTION;
    ex_no_passagier EXCEPTION;
    ex_no_seats EXCEPTION;
  BEGIN

    SELECT COUNT(*) INTO v_check FROM if11b022_flug f WHERE f.flugID = a_flugID OR f.flugID = a_new_flugID;
    IF v_check <= 1 THEN
      RAISE ex_no_flug;
    END IF;

    SELECT COUNT(*) INTO v_check FROM if11b022_passagierliste pl WHERE pl.personID = a_personID AND pl.flugID = a_flugID;
    IF v_check <= 0 THEN
      RAISE ex_no_passagier;
    END IF;

    SELECT (fzt.sitzplaetze - COUNT(pl.flugID)) INTO v_check
    FROM flugzeugtyp fzt, flugzeug fz, if11b022_flug f, if11b022_passagierliste pl
    WHERE fzt.flugzeugtypID = fz.flugzeugtypID
      AND fz.flugzeugID = f.flugzeugID
      AND pl.flugID = f.flugID
      AND f.flugID = a_new_flugID
    GROUP BY f.flugID, fzt.sitzplaetze;

    IF v_check <= 0 THEN
      RAISE ex_no_seats;
    END IF;

    UPDATE if11b022_passagierliste SET flugID = a_new_flugID
    WHERE flugID = a_flugID AND personID = a_personID;

    UPDATE if11b022_gepaeck SET flugID = a_new_flugID
    WHERE flugID = a_flugID AND personID = a_personID;

    EXCEPTION
      WHEN ex_no_passagier THEN
        raise_application_error(-20100, 'Passagier zu angegebenen Flug existiert nicht');
      WHEN ex_no_flug THEN
        raise_application_error(-20101, 'Alter oder neuer Flug existiert nicht!');
      WHEN ex_no_seats THEN
        raise_application_error(-20102, 'Keine Sitze mehr im neuen Flug verfuegbar!');
      WHEN OTHERS THEN
        raise_application_error(-20104, 'Unerwarteter Fehler ist aufgetreten');
  END;

END;
/

--- Testen der Proceduren im Package
EXECUTE if11b022_pkg_bsp1.gate_aendern(1, 1);
EXECUTE if11b022_pkg_bsp1.flug_anlegen(12,1,1,115,SYSDATE,'NEW123', 5);
EXECUTE if11b022_pkg_bsp1.passagier_umbuchen(1,5,16);

--- **********************************************************************************
--- 2)
--- Schreiben Sie einen Datenbanktrigger, der im Falle einer Stornierung eines Passagiers
--- zu einem Flug dafür sorgt, dass alle Gepäckstücke des Passagiers aus der Gepäcktabelle entfernt werden.
--- **********************************************************************************

CREATE OR REPLACE TRIGGER if11b022_passagierstornierung BEFORE DELETE
ON if11b022_passagierliste FOR EACH ROW
DECLARE

BEGIN
  DELETE FROM if11b022_gepaeck WHERE personID = :OLD.personID AND flugID = :OLD.flugID;
END;
/

-- Testen des Triggers
DELETE FROM if11b022_passagierliste WHERE flugID = 16 AND personID = 5;


--- **********************************************************************************
--- 3)
--- Schreiben Sie einen Datenbanktrigger, der im Falle einer Flugstornierung alle gebuchten Passagiere
--- auf andere Flüge mit gleicher Destination umbucht. Vergessen Sie nicht auf das Gepäck!
--- Nicht verbuchbare Passagiere geben Sie auf der Konsole aus.
--- **********************************************************************************
CREATE OR REPLACE TRIGGER if11b022_flugstornierung BEFORE DELETE
ON if11b022_flug FOR EACH ROW
DECLARE

v_freiePlaetze NUMBER;

CURSOR cur_fluege IS
  SELECT f.flugID FROM flug f
    WHERE f.flughafen_destination = :OLD.flughafen_destination;

CURSOR cur_personen IS
  SELECT p.personID FROM person p
  JOIN passagierliste pl ON pl.personID = p.personID
  JOIN flug f ON f.flugID = pl.flugID
  WHERE f.flugID = :OLD.flugID;

v_person cur_personen%ROWTYPE;
v_flug cur_fluege%ROWTYPE;

BEGIN
  -- Passagiere auf alle moeglichen Alternativfluege verteilen
  FOR v_flug IN cur_fluege
  LOOP
    --Anzahl der Sitzplaetze ermitteln
    SELECT (fzt.sitzplaetze - COUNT(pl.flugID)) INTO v_freiePlaetze
    FROM flugzeugtyp fzt, flugzeug fz, if11b022_flug f, if11b022_passagierliste pl
    WHERE fzt.flugzeugtypID = fz.flugzeugtypID
      AND fz.flugzeugID = f.flugzeugID
      AND pl.flugID = f.flugID
      AND f.flugID = v_flug.flugID
    GROUP BY f.flugID, fzt.sitzplaetze;

    -- Fuer jeden freien Sitzplatz einen Passagier zuweisen
    FOR v_i IN 1..v_freiePlaetze
    LOOP
      FOR v_person IN cur_personen
      LOOP
        UPDATE if11b022_passagierliste pl SET pl.flugID = v_flug.flugID
        WHERE flugID = :OLD.flugID AND pl.personID = v_person.personid;

        UPDATE if11b022_gepaeck g SET g.flugID = v_flug.flugID
        WHERE flugID = :OLD.flugID AND g.personID = v_person.personID;
      END LOOP;
    END LOOP;


  END LOOP;

  -- Nicht umbuchbaren Passagiere ausgeben
  FOR v_person IN cur_personen
  LOOP
    dbms_output.enable;
    dbms_output.put_line('Passagier nicht umgebucht! ID: ' || v_person.personid);
  END LOOP;

END;
/


EXECUTE if11b022_pkg_bsp1.flug_anlegen(12,1,1,120,SYSDATE,'ER60', 5);
SET serveroutput ON;
DELETE FROM if11b022_flug WHERE flugID = 16;
SELECT * FROM if11b022_passagierliste WHERE flugID = 16;

--- **********************************************************************************
--- 4)
--- Schreiben Sie einen Datenbanktrigger, der bei der Zuteilung von Personal zu einem Flug prüft,
--- dass pro Flug nicht mehr als ein Pilot od. Chefpilot, zwei Co- Piloten und
--- 6 Flugbegleiter zugeordnet werden.
--- **********************************************************************************
CREATE OR REPLACE TRIGGER if11b022_flugpersonalcheck BEFORE INSERT OR UPDATE
ON if11b022_crew FOR EACH ROW
DECLARE
  v_piloten NUMBER;
  v_copiloten NUMBER;
  v_flugbegleiter NUMBER;
  v_kbz VARCHAR2(5);

BEGIN
  SELECT d.kbz INTO v_kbz FROM flugpersonal fp
  JOIN dienstrang d ON d.dienstrangID = fp.dienstrangID
  WHERE fp.personID = :NEW.personID;

  SELECT COUNT(d.dienstrangID) INTO v_piloten FROM if11b022_crew c
  JOIN flugpersonal fp ON fp.personID = c.personID
  JOIN dienstrang d ON d.dienstrangID = fp.dienstrangID
  WHERE c.flugID = :NEW.flugID AND (d.kbz = 'Cp' OR d.kbz = 'Pl');

  SELECT COUNT(d.dienstrangID) INTO v_copiloten FROM if11b022_crew c
  JOIN flugpersonal fp ON fp.personID = c.personID
  JOIN dienstrang d ON d.dienstrangID = fp.dienstrangID
  WHERE c.flugID = :NEW.flugID AND d.kbz = 'Co';

  SELECT COUNT(d.dienstrangID) INTO v_flugbegleiter FROM if11b022_crew c
  JOIN flugpersonal fp ON fp.personID = c.personID
  JOIN dienstrang d ON d.dienstrangID = fp.dienstrangID
  WHERE c.flugID = :NEW.flugID AND d.kbz LIKE '%Bp%';

  IF v_piloten >= 1 AND (v_kbz = 'Cp' OR v_kbz = 'Pl') THEN
    raise_application_error(-20100, 'Es duerfen nicht mehr als 1 Pilot oder Chefpilot an Board sein');
  END IF;

  IF v_copiloten >= 2 AND v_kbz = 'Co' THEN
    raise_application_error(-20101, 'Es duerfen nicht mehr als 2 Co-Piloten an Board sein');
  END IF;

  IF v_flugbegleiter >= 6 AND v_kbz = 'Bp' THEN
    raise_application_error(-20102, 'Es fuerfen nicht mehr als 6 Flugbegleiter an Board sein');
  END IF;

END;

-- Test
INSERT INTO if11b022_crew (personID, flugID) VALUES ( 3, 16);
INSERT INTO if11b022_crew (personID, flugID) VALUES ( 88, 16);