CREATE OR REPLACE TYPE OSOBA AS OBJECT
(
    ID_WYCIECZKI INT,
    NAZWA        VARCHAR2(100),
    KRAJ         VARCHAR2(50),
    DATA         DATE,
    IMIE         VARCHAR2(50),
    NAZWISKO     VARCHAR2(50),
    STATUS       CHAR(1)
);
CREATE OR REPLACE TYPE WYCIECZKA AS OBJECT
(
    ID_WYCIECZKI  INT,
    NAZWA         VARCHAR2(100),
    KRAJ          VARCHAR2(50),
    DATA          DATE,
    OPIS          VARCHAR2(200),
    LICZBA_MIEJSC INT
);
CREATE OR REPLACE TYPE OSOBY_TABLE AS TABLE OF OSOBA;
CREATE OR REPLACE TYPE WYCIECZKA_TABLE AS TABLE OF WYCIECZKA;

CREATE FUNCTION uczestnicy_wycieczki(idWycieczki int) RETURN OSOBY_TABLE
AS
    result OSOBY_TABLE;
        exist INTEGER;
BEGIN
    SELECT COUNT(*) INTO exist FROM WYCIECZKI WHERE ID_WYCIECZKI = idWycieczki;
    IF exist = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Nie znaleziono wycieczki o id =' || idWycieczki);
    END IF;

    SELECT OSOBA(w.KRAJ, w.DATA, w.NAZWA, o.IMIE,
                             o.NAZWISKO, r.STATUS)
        BULK COLLECT INTO result
    FROM WYCIECZKI w
           JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
           JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
    WHERE w.ID_WYCIECZKI = idWycieczki AND (r.STATUS = 'P' OR r.STATUS = 'Z');
    RETURN result;
END uczestnicy_wycieczki;

CREATE OR REPLACE
FUNCTION rezerwacje_osoby(id_osoby OSOBY.ID_OSOBY%TYPE)
  RETURN OSOBY_TABLE AS result OSOBY_TABLE;
  exist INTEGER;
  BEGIN
    SELECT COUNT(*) INTO exist FROM OSOBY WHERE OSOBY.ID_OSOBY = rezerwacje_osoby.id_osoby;

    IF exist = 0 THEN
      raise_application_error(-20002, 'Nie znaleziono osoby o podanym id:');
    END IF;

    SELECT OSOBY(w.KRAJ, w.DATA, w.NAZWA, o.IMIE,
                             o.NAZWISKO, r.STATUS)
        BULK COLLECT INTO result
    FROM WYCIECZKI w
           JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
           JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
    WHERE o.ID_OSOBY = rezerwacje_osoby.id_osoby AND r.STATUS <> 'A';
    RETURN result;
  END rezerwacje_osoby;


CREATE OR REPLACE
FUNCTION przyszle_rezerwacje_osoby(id_osoby OSOBY.ID_OSOBY%TYPE)
  RETURN OSOBY_TABLE AS result OSOBY_TABLE;
  exist INTEGER;
  BEGIN
    SELECT COUNT(*) INTO exist FROM OSOBY WHERE OSOBY.ID_OSOBY = przyszle_rezerwacje_osoby.id_osoby;

    IF exist = 0
    THEN
      raise_application_error(-20002, 'Brak osoby o podanym id');
    END IF;

    SELECT OSOBA(w.KRAJ, w.DATA, w.NAZWA, o.IMIE,
                             o.NAZWISKO, r.STATUS)
        BULK COLLECT INTO result
    FROM WYCIECZKI w
           JOIN REZERWACJE r ON w.ID_WYCIECZKI = r.ID_WYCIECZKI
           JOIN OSOBY o ON r.ID_OSOBY = o.ID_OSOBY
    WHERE o.ID_OSOBY = przyszle_rezerwacje_osoby.id_osoby
      AND r.STATUS = 'P' OR r.STATUS = 'Z'
      AND w.DATA > CURRENT_DATE;
    RETURN result;
  END przyszle_rezerwacje_osoby;

CREATE OR REPLACE
FUNCTION dostepne_wycieczki(kraj WYCIECZKI.KRAJ%TYPE, data_od DATE,
                            data_do DATE)
  RETURN WYCIECZKA_TABLE AS result WYCIECZKA_TABLE;
  BEGIN
    IF data_do < data_od
    THEN
      raise_application_error(-20003, 'Nieprawidłowy przedział dat');
    END IF;

    SELECT WYCIECZKA(w.ID_WYCIECZKI, w.NAZWA, w.KRAJ, w.DATA, w.OPIS,
                       w.LICZBA_MIEJSC)
        BULK COLLECT INTO result
    FROM WYCIECZKI w
    WHERE w.KRAJ = dostepne_wycieczki.kraj
      AND w.DATA >= data_od
      AND w.DATA <= data_do
      AND w.LICZBA_MIEJSC > (SELECT COUNT(*)
                             FROM REZERWACJE r
                             WHERE r.status <> 'A'
                               AND r.ID_WYCIECZKI = w.ID_WYCIECZKI);
    RETURN result;
  END dostepne_wycieczki;