-- Zad. 48.* Rozszerzyć relacyjną bazę danych kotów o dodatkowe relacje opisujące elitę, plebs
-- i konta elity (patrz opis z zad. 47) a następnie zdefiniować "nakładkę", w postaci perspektyw
-- obiektowych (bez odpowiedników relacji Funkcje, Bandy, Wrogowie), na tak
-- zmodyfikowaną bazę. Odpowiadające relacjom typy obiektowe mają zawierać przykładowe
-- metody (mogą to być metody z zad. 47). Zamodelować wszystkie powiązania referencyjne z
-- wykorzystaniem identyfikatorów OID i funkcji MAKE_REF. Relacje wypełnić
-- przykładowymi danymi (mogą to być dane z zad. 47). Dla tak przygotowanej bazy wykonać
-- wszystkie zapytania SQL i bloki PL/SQL zrealizowane w ramach zad. 47.
DROP TABLE KONTA_P;
DROP TABLE ELITA_P;
DROP TABLE PLEBS_P;




CREATE TABLE PLEBS_P (
  id_plebejusza NUMBER CONSTRAINT rp_pk PRIMARY KEY,
  czlonek           VARCHAR2(15) CONSTRAINT rp_sc_k REFERENCES Kocury (pseudo)
);

CREATE TABLE ELITA_P (
  nr_prominenta NUMBER CONSTRAINT re_pk PRIMARY KEY,
  czlonek     VARCHAR(15) CONSTRAINT re_sc_k REFERENCES Kocury (pseudo),
  sluga    NUMBER CONSTRAINT re_sc_e REFERENCES PLEBS_P (id_plebejusza)
);

CREATE TABLE KONTA_P (
  nr_konta          NUMBER CONSTRAINT ro_pk PRIMARY KEY,
  wlasciciel        NUMBER CONSTRAINT ro_sc_k REFERENCES ELITA_P (nr_prominenta),
  data_wprowadzenia DATE,
  data_usuniecia    DATE,
  CONSTRAINT ro_dw CHECK (data_wprowadzenia IS NOT NULL),
  CONSTRAINT ro_du CHECK (data_wprowadzenia <= data_usuniecia)
);




--- OBIEKTY

-- CREATE OR REPLACE TYPE TYP_KOT
-- AS OBJECT
-- (
--   imie            VARCHAR2(15),
--   plec            VARCHAR2(1),
--   pseudo          VARCHAR2(15),
--   funkcja         VARCHAR2(10),
--   szef            REF TYP_KOT,
--   w_stadku_od     DATE,
--   przydzial_myszy NUMBER(3),
--   myszy_extra     NUMBER(3),
--   nr_bandy        NUMBER(2),
--
-- MAP MEMBER FUNCTION PorownajPseudo
--   RETURN VARCHAR2,
-- MEMBER FUNCTION calkowiteSpozycieMyszy
--   RETURN NUMBER,
-- MEMBER FUNCTION czyRekrut
--   RETURN BOOLEAN,
-- MEMBER FUNCTION dajSzefa
--   RETURN TYP_KOT
--
-- );
--
-- CREATE OR REPLACE TYPE BODY TYP_KOT AS
--   MAP MEMBER FUNCTION PorownajPseudo
--     RETURN VARCHAR2
--   IS
--     BEGIN
--       RETURN pseudo;
--     END;
--
--   MEMBER FUNCTION calkowiteSpozycieMyszy
--     RETURN NUMBER
--   IS
--     BEGIN
--       RETURN NVL(przydzial_myszy, 0) + NVL(myszy_extra, 0);
--     END;
--
--   MEMBER FUNCTION czyRekrut
--     RETURN BOOLEAN
--   IS
--     BEGIN
--       RETURN (SYSDATE - w_stadku_od) < 90;
--     END;
--
--   MEMBER FUNCTION dajSzefa
--     RETURN TYP_KOT
--   IS
--     szefu TYP_KOT;
--     BEGIN
--       SELECT DEREF(szef)
--       INTO szefu
--       FROM DUAL;
--       RETURN szefu;
--     END;
--
--
-- END;
-- -- PLEBS
--
-- CREATE OR REPLACE TYPE TYP_PLEBS
-- AS OBJECT
-- (
--   id_plebejusza NUMBER,
--   czlonek       REF TYP_KOT,
--
-- MEMBER FUNCTION dajKota
--   RETURN TYP_KOT
-- );
--
-- CREATE OR REPLACE TYPE BODY TYP_PLEBS IS
--   MEMBER FUNCTION dajKota
--     RETURN TYP_KOT IS plebejusz TYP_KOT;
--     BEGIN
--       SELECT DEREF(czlonek)
--       INTO plebejusz
--       FROM DUAL;
--       RETURN plebejusz;
--     END;
-- END;
-- -- ELITA
--
-- CREATE OR REPLACE TYPE TYP_ELITA
-- AS OBJECT
-- (
--   nr_prominenta NUMBER,
--   czlonek       REF TYP_KOT,
--   sluga         REF TYP_PLEBS,
--
-- MEMBER FUNCTION dajSluge
--   RETURN TYP_PLEBS,
-- MEMBER FUNCTION czyMaSluge
--   RETURN BOOLEAN
-- );
--
-- CREATE OR REPLACE TYPE BODY TYP_ELITA IS
--
--   MEMBER FUNCTION dajSluge
--     RETURN TYP_PLEBS IS RESULT TYP_PLEBS;
--     BEGIN
--       SELECT DEREF(sluga)
--       INTO RESULT
--       FROM DUAL;
--       RETURN RESULT;
--     END;
--
--   MEMBER FUNCTION czyMaSluge
--     RETURN BOOLEAN IS
--     BEGIN
--       RETURN sluga IS NOT NULL;
--     END;
-- END;
--
-- -- KONTO
-- CREATE OR REPLACE TYPE TYP_KONTO
-- AS OBJECT
-- (
--   nr_konta          NUMBER,
--   wlasciciel        REF TYP_ELITA,
--   data_wprowadzenia DATE,
--   data_usuniecia    DATE,
--
-- MEMBER FUNCTION czasPrzechowywania
--   RETURN NUMBER,
-- MEMBER FUNCTION czyAktywne
--   RETURN BOOLEAN
-- );
--
-- CREATE OR REPLACE TYPE BODY TYP_KONTO IS
--
--   MEMBER FUNCTION czasPrzechowywania
--     RETURN NUMBER IS dni NUMBER;
--     BEGIN
--       RETURN NVL(data_usuniecia, SYSDATE) - data_wprowadzenia;
--     END;
--
--   MEMBER FUNCTION czyAktywne
--     RETURN BOOLEAN IS
--     BEGIN
--       RETURN data_usuniecia IS NOT NULL;
--     END;
--
-- END;

CREATE OR REPLACE FORCE VIEW KOTY_V
    OF TYP_KOT
  WITH OBJECT IDENTIFIER (pseudo) AS
  SELECT
    imie,
    plec,
    pseudo,
    funkcja,
    MAKE_REF(KOTY_V, szef) szef,
    w_stadku_od,
    przydzial_myszy,
    myszy_extra,
    nr_bandy
  FROM KOCURY;

CREATE OR REPLACE VIEW PLEBS_V
    OF TYP_PLEBS
  WITH OBJECT IDENTIFIER (id_plebejusza) AS
  SELECT
    id_plebejusza,
    MAKE_REF(KOTY_V, czlonek) czlonek
  FROM PLEBS_P;

CREATE OR REPLACE VIEW ELITA_V
    OF TYP_ELITA
  WITH OBJECT IDENTIFIER (nr_prominenta) AS
  SELECT
    nr_prominenta,
    MAKE_REF(KOTY_V, czlonek) czlonek,
    MAKE_REF(PLEBS_V, sluga)  sluga
  FROM ELITA_P;

CREATE OR REPLACE VIEW KONTA_V
    OF TYP_KONTO
  WITH OBJECT IDENTIFIER (nr_konta) AS
  SELECT
    nr_konta,
    MAKE_REF(ELITA_V, wlasciciel) wlasciciel,
    data_wprowadzenia,
    data_usuniecia
  FROM KONTA_P;

----------------------------------------
--------ZADANIA-------------------------
-- ZAPYTANIA

--referencje
SELECT DEREF(WLASCICIEL).CZLONEK.PSEUDO "WLASCICIEL KONTA NR 6"
FROM
  KONTA_V KV
WHERE KV.NR_KONTA = '6';

--podzapytanie
-- PSEUDO PROMINENTOW
SELECT PSEUDO
FROM KOTY_V
WHERE PSEUDO IN (
  SELECT DEREF(CZLONEK).PSEUDO
  FROM ELITA_V
);

--grupowanie - ILOSC ZDEPONOWANYCH MYSZY
SELECT
  DEREF(K.WLASCICIEL).CZLONEK.pseudo,
  COUNT(*)
FROM Konta_V k
GROUP BY DEREF(K.WLASCICIEL).CZLONEK.pseudo;

--metody
SELECT
  PSEUDO,
  MYSZY_EXTRA,
  K.CALKOWITESPOZYCIEMYSZY()
FROM
  KOTY_V K
WHERE K.CALKOWITESPOZYCIEMYSZY() > 50 AND MYSZY_EXTRA IS NULL;


 ---ILE MYSZY NA KONCIE KOT Z ELITY PLCI ZE

SELECT




-----------------------------
--ZADANIA Z LISTY 2
--ZADANIE 18

SELECT
  K1.imie        "IMIE",
  K1.w_stadku_od "POLUJE OD"
FROM
  KOTY_V K1,
  KOTY_V K2
WHERE
  K2.IMIE = 'JACEK' AND
  K1.WstapilPrzed(value(K2)) = 'TRUE'
ORDER BY K1.w_stadku_od DESC;

-- ZAD 26
SELECT
  FUNKCJA,
  ROUND(AVG(kocur.calkowitespozyciemyszy()))
FROM koty_V kocur
WHERE FUNKCJA <> 'SZEFUNIO'
GROUP BY FUNKCJA
HAVING ROUND(AVG(NVL(PRZYDZIAL_MYSZY, 0) + NVL(MYSZY_EXTRA, 0))) >= ALL (
  SELECT ROUND(AVG(kocur.calkowitespozyciemyszy()))
  FROM koty_V kocur
  WHERE funkcja != 'SZEFUNIO'
  GROUP BY funkcja
)
       OR ROUND(AVG(NVL(PRZYDZIAL_MYSZY, 0) + NVL(MYSZY_EXTRA, 0))) <= ALL (
  SELECT ROUND(AVG(kocur.calkowitespozyciemyszy()))
  FROM koty_V kocur
  WHERE funkcja != 'SZEFUNIO'
  GROUP BY funkcja
);

--ZADANIA Z LISTY 3

--ZADANIE 35

DECLARE
  PSEUDO_KOTA      KOTY.PSEUDO%TYPE;
  ROCZNY_PRZYDZIAL NUMBER(4);
  IMIE_KOTA        KOTY.IMIE%TYPE;
  DATA_PRZYST      KOTY.W_STADKU_OD%TYPE;


BEGIN
  SELECT
    K.PSEUDO,
    K.CALKOWITESPOZYCIEMYSZY()*12,
    K.IMIE,
    K.W_STADKU_OD
  INTO
    PSEUDO_KOTA,
    ROCZNY_PRZYDZIAL,
    IMIE_KOTA,
    DATA_PRZYST
  FROM
    KOTY_V K
  WHERE
    K.PSEUDO = :PSEUDO;

  IF ROCZNY_PRZYDZIAL > 700
  THEN
    DBMS_OUTPUT.PUT_LINE(IMIE_KOTA || ' - calkowity roczny przydzial myszy > 700');
  ELSE
    IF IMIE_KOTA LIKE '%A%'
    THEN
      DBMS_OUTPUT.PUT_LINE(IMIE_KOTA || ' - imie zawiera litere A');
    ELSE
      IF EXTRACT(MONTH FROM DATA_PRZYST) = 01
      THEN
        DBMS_OUTPUT.PUT_LINE(IMIE_KOTA || ' - styczen jest miesiacem przystapienia do stada');
      ELSE
        DBMS_OUTPUT.PUT_LINE(IMIE_KOTA || ' nie odpowiada kryteriom');
      END IF;
    END IF;
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN DBMS_OUTPUT.PUT_LINE('NIE MA TAKIEGO KOTKA');
END;

--ZADANIE 37
DECLARE
  SA_WIERSZE BOOLEAN := FALSE;
  BRAK_KOTOW EXCEPTION;
  NUMER      NUMBER DEFAULT 1;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Nr    Pseudonim    Zjada');
  DBMS_OUTPUT.PUT_LINE('------------------------');

  FOR NR IN (SELECT
               K.PSEUDO,
               K.CALKOWITESPOZYCIEMYSZY() "ZJADA"
             FROM KOTY_V K
             ORDER BY ZJADA DESC
             FETCH FIRST 5 ROWS ONLY
  )

  LOOP
    SA_WIERSZE := TRUE;
    DBMS_OUTPUT.PUT_LINE(LPAD(NUMER, 4) || '  ' || RPAD(NR.pseudo, 9) || '    ' || LPAD(NR.zjada, 5));
    NUMER := NUMER + 1;
  END LOOP;

  IF NOT sa_wiersze
  THEN RAISE brak_kotow;
  END IF;

  EXCEPTION
  WHEN brak_kotow THEN DBMS_OUTPUT.PUT_LINE('Brak kotow');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

SELECT *
FROM KOTY_V;