-- zad 49



DECLARE
BEGIN
  EXECUTE IMMEDIATE
    'CREATE TABLE Myszy(
    nr_myszy          NUMBER GENERATED BY DEFAULT AS IDENTITY constraint myszy_pk primary key,
    lowca             VARCHAR2(15) constraint lowca_myszy_fk references Kocury(pseudo),
    zjadacz           VARCHAR2(15)constraint zjadacz_myszy_fk references Kocury(pseudo),
    waga              NUMBER NOT NULL constraint normy_wg check(waga between 5 AND 20),
    data_zlowienia    DATE NOT NULL,
    data_wydania      DATE
    )';
  COMMIT;
END;


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
DROP TABLE Myszy;

DECLARE
poczatek_miesiaca DATE DEFAULT '2004-01-01';
dzien_wyplaty DATE;
TYPE record_pseudo_przydzial IS RECORD (pseudo Kocury.pseudo%Type, przydzial Kocury.przydzial_myszy%TYPE);
TYPE tk IS TABLE OF record_pseudo_przydzial INDEX BY BINARY_INTEGER;
TYPE tm IS TABLE OF Myszy%ROWTYPE INDEX BY BINARY_INTEGER;
tabMyszy tm;
tabKocurow tk;
myszy_do_dodania NUMBER;
srednie_spozycie_zaokr NUMBER;
wyplacono NUMBER :=0;
aktualnyLowca NUMBER :=1;
aktualnyZjadacz NUMBER :=1;
ile_zjada NUMBER :=0;
numer_myszy NUMBER :=1;
BEGIN
  -- iteracja po wszystkich miesiacach
  WHILE poczatek_miesiaca < '2018-01-15'
  LOOP

    dzien_wyplaty := next_day(last_day(poczatek_miesiaca)-7, 'ŒRODA');

    SELECT pseudo, NVL(przydzial_myszy,0) + NVL(myszy_ekstra,0) BULK COLLECT INTO tabKocurow
    FROM Kocury
    WHERE w_stadku_od <  poczatek_miesiaca;

    select sum(NVL(przydzial_myszy,0) + NVL(myszy_ekstra,0)) into myszy_do_dodania
    from Kocury
    where w_stadku_od < poczatek_miesiaca;

    select floor(avg(NVL(przydzial_myszy,0) + NVL(myszy_ekstra,0))) into srednie_spozycie_zaokr
    from Kocury
    where w_stadku_od < poczatek_miesiaca;


    for i in 1..myszy_do_dodania
    LOOP -- rozdzial myszy w jednym miesiacu
      IF aktualnyLowca <= tabKocurow.COUNT
        THEN
        tabMyszy(numer_myszy).lowca := tabKocurow(aktualnyLowca).pseudo;
        wyplacono := wyplacono +1;
        IF wyplacono = srednie_spozycie_zaokr
          THEN
          aktualnyLowca := aktualnyLowca + 1;
          wyplacono := 0;
        END IF;
      ELSE
        tabMyszy(numer_myszy).lowca := 'TYGRYS';
      END IF;

      tabMyszy(numer_myszy).waga := ROUND(dbms_random.value(5,20));
      tabMyszy(numer_myszy).data_zlowienia := poczatek_miesiaca + dbms_random.value(0,27);

      -- ustawiamy zjadacza
      IF dzien_wyplaty < '2018-01-15'
      THEN
        tabMyszy(numer_myszy).data_wydania := dzien_wyplaty;
        IF ile_zjada != tabKocurow(aktualnyZjadacz).przydzial
        THEN
        tabMyszy(numer_myszy).zjadacz := tabKocurow(aktualnyZjadacz).pseudo;
          ile_zjada := ile_zjada +1;
        ELSE
          aktualnyZjadacz := aktualnyZjadacz +1;
          ile_zjada :=1;
          tabMyszy(numer_myszy).zjadacz:= tabKocurow(aktualnyZjadacz).pseudo;
        END IF;
        ELSE
            tabMyszy(numer_myszy).data_wydania := null;
        END IF;


    numer_myszy := numer_myszy +1;

    END LOOP;       -- koniec petli dla danej myszy
    aktualnyZjadacz:=1;
    aktualnyLowca :=1;
    wyplacono :=0;
    ile_zjada:=0;
    poczatek_miesiaca := last_day(dzien_wyplaty) + 1;

  END LOOP;                 -- koniec petli dla danego miesiaca
  FORALL i IN 1..tabMyszy.COUNT
    insert into Myszy(lowca, zjadacz, waga, data_zlowienia, data_wydania) VALUES
    (tabMyszy(i).lowca, tabMyszy(i).zjadacz, tabMyszy(i).waga, tabMyszy(i).data_zlowienia, tabMyszy(i).data_wydania);
END;

SELECT * from myszy where data_wydania IS NULL
SELECT COUNT(*) from myszy
DROP TABLE myszy;

DROP TABLE MYSZY_ZOMBI

CREATE TABLE MYSZY_ZOMBI (
nr_myszy          NUMBER GENERATED BY DEFAULT AS IDENTITY constraint myszy_tyg_pk primary key,
waga              NUMBER not null constraint normy_wagowe check(waga between 5 AND 20),
data_zlowienia    DATE not null
);

INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(7, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(15, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(10, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(9, '2018-01-16');


CREATE OR REPLACE PROCEDURE dodaj_myszy (pseudo_kota Kocury.pseudo%TYPE, data_lowow DATE ) AS
TYPE record_lowy_myszy IS RECORD (nr NUMBER, waga NUMBER, data DATE);
TYPE tab_lowy_myszy_typ IS TABLE OF record_lowy_myszy INDEX BY BINARY_INTEGER;
tab_lowy_myszy tab_lowy_myszy_typ;
sprawdz_koty NUMBER;
BRAK_KOTA_EXCEPTION EXCEPTION;
BEGIN

  SELECT COUNT(*) INTO sprawdz_koty
  FROM Kocury
  WHERE pseudo = pseudo_kota;


  IF sprawdz_koty = 0
    THEN RAISE BRAK_KOTA_EXCEPTION;
  END IF;

EXECUTE IMMEDIATE 'SELECT * FROM MYSZY_' || pseudo_kota ||
                  ' WHERE data_zlowienia = '''|| data_lowow||''''
BULK COLLECT INTO tab_lowy_myszy;


FORALL i in 1..tab_lowy_myszy.COUNT
  INSERT INTO MYSZY(lowca,zjadacz,waga,data_zlowienia,data_wydania)
  VALUES (pseudo_kota,null, tab_lowy_myszy(i).waga, data_lowow,null);

  EXCEPTION
  WHEN BRAK_KOTA_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE('Zly pseudonim kota');
END;


EXECUTE dodaj_myszy ('ZOMBI', TO_DATE('2018-01-16'));


SELECT * FROM MYSZY WHERE data_wydania IS NULL;


CREATE OR REPLACE PROCEDURE wyplata (data DATE) IS
dzien_wyplaty DATE;
TYPE record_myszy IS RECORD (nr_myszy NUMBER, zjadacz Myszy.zjadacz%TYPE);
TYPE record_kocury IS RECORD (pseudo Kocury.pseudo%Type, przydzial NUMBER, nie_ma_MAX NUMBER);

TYPE tab_typ_myszy IS TABLE OF record_myszy INDEX BY BINARY_INTEGER;
TYPE tab_typ_kocury IS TABLE OF record_kocury INDEX BY BINARY_INTEGER;

tab_kocury tab_typ_kocury;
tab_myszy tab_typ_myszy;

do_wyplaty NUMBER:=0;
ile_ma_MAX NUMBER:=0;
wyplacono_myszy NUMBER:=0;
BEGIN

  dzien_wyplaty := next_day(last_day(data)-7, 'œroda');

  SELECT nr_myszy, zjadacz BULK COLLECT INTO tab_myszy
  FROM Myszy
  WHERE data_wydania IS NULL;

  -- nie ma MAX - flaga: 0 - false, 1 - true
  SELECT pseudo, NVL(przydzial_myszy,0)+NVL(myszy_ekstra,0), 0 BULK COLLECT INTO tab_kocury
  FROM Kocury
  WHERE w_stadku_od <= dzien_wyplaty
  START WITH szef IS NULL
  CONNECT BY PRIOR pseudo = szef
  ORDER BY level ASC;

  do_wyplaty:= tab_myszy.COUNT();

  while do_wyplaty > wyplacono_myszy
  LOOP
    for i in 1..tab_kocury.COUNT()
    LOOP
      IF do_wyplaty > wyplacono_myszy AND ile_ma_MAX != tab_kocury.COUNT()
      THEN
        IF tab_kocury(i).nie_ma_MAX = 0
        THEN
          tab_kocury(i).przydzial := tab_kocury(i).przydzial -1;
          wyplacono_myszy:= wyplacono_myszy +1;
          tab_myszy(wyplacono_myszy).zjadacz := tab_kocury(i).pseudo;
          IF  tab_kocury(i).przydzial = 0
            THEN
            tab_kocury(i).nie_ma_MAX := 1;
            ile_ma_MAX := ile_ma_MAX + 1;
          END IF;
        ELSE IF do_wyplaty > wyplacono_myszy
          THEN
          wyplacono_myszy:= wyplacono_myszy +1;
          tab_myszy(wyplacono_myszy).zjadacz := 'TYGRYS';
          END IF;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  FORALL i in 1..tab_myszy.COUNT
  UPDATE Myszy
  SET  zjadacz = tab_myszy(i).zjadacz , data_wydania = dzien_wyplaty
  WHERE nr_myszy = tab_myszy(i).nr_myszy;
END;


DROP TABLE MYSZY_ZOMBI

CREATE TABLE MYSZY_ZOMBI (
nr_myszy          NUMBER GENERATED BY DEFAULT AS IDENTITY constraint myszy_tyg_pk primary key,
waga              NUMBER not null constraint normy_wagowe check(waga between 5 AND 20),
data_zlowienia    DATE not null
);

INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(7, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(5, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(10, '2018-01-16');
INSERT INTO MYSZY_ZOMBI(waga, data_zlowienia) VALUES(9, '2018-01-16');

DROP TABLE MYSZY_TYGRYS

CREATE TABLE MYSZY_TYGRYS (
nr_myszy          NUMBER GENERATED BY DEFAULT AS IDENTITY constraint myszy_tyg_pk1 primary key,
waga              NUMBER not null constraint normy_wagowe1 check(waga between 5 AND 20),
data_zlowienia    DATE not null
);

INSERT INTO MYSZY_TYGRYS(waga, data_zlowienia) VALUES(7, '2018-01-16');
INSERT INTO MYSZY_TYGRYS(waga, data_zlowienia) VALUES(5, '2018-01-16');
INSERT INTO MYSZY_TYGRYS(waga, data_zlowienia) VALUES(10, '2018-01-16');
INSERT INTO MYSZY_TYGRYS(waga, data_zlowienia) VALUES(9, '2018-01-16');

DROP TABLE MYSZY_LYSY

CREATE TABLE MYSZY_LYSY (
nr_myszy          NUMBER GENERATED BY DEFAULT AS IDENTITY constraint myszy_tyg_pk2 primary key,
waga              NUMBER not null constraint normy_wagowe2 check(waga between 5 AND 20),
data_zlowienia    DATE not null
);

INSERT INTO MYSZY_LYSY(waga, data_zlowienia) VALUES(8, '2018-01-16');
INSERT INTO MYSZY_LYSY(waga, data_zlowienia) VALUES(9, '2018-01-16');
INSERT INTO MYSZY_LYSY(waga, data_zlowienia) VALUES(10, '2018-01-16');
INSERT INTO MYSZY_LYSY(waga, data_zlowienia) VALUES(9, '2018-01-16');


EXECUTE dodaj_myszy ('TYGRYS', TO_DATE('2018-01-16'));
EXECUTE dodaj_myszy ('LYSY', TO_DATE('2018-01-16'));
EXECUTE dodaj_myszy ('ZOMBI', TO_DATE('2018-01-16'));


Execute wyplata('2018-1-20');

SELECT * FROM MYSZY
ORDER BY data_wydania

Execute wyplata('2020-2-1');
select * from myszy order by 6 DESC;