ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

DROP TABLE Myszy;

DECLARE
    dmlCommand VARCHAR2(1024);
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    dmlCommand :=
    'CREATE TABLE Myszy
    (
        nr_myszy NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT my_pk PRIMARY KEY,
        lowca VARCHAR2(15) CONSTRAINT my_lo_fk REFERENCES Kocury(pseudo),
        zjadacz VARCHAR2(15) CONSTRAINT my_zj_fk REFERENCES Kocury(pseudo),
        waga_myszy NUMBER(2) CONSTRAINT my_wm_ck CHECK(waga_myszy BETWEEN 2 AND 10),
        data_zlowienia DATE,
        data_wydania DATE
    )';
    EXECUTE IMMEDIATE dmlCommand;
    COMMIT;
END;


CREATE OR REPLACE PROCEDURE dopiszMyszy(kot Kocury.PSEUDO%TYPE, ile NUMBER) AS
    TYPE TabelaMyszyNowe IS TABLE OF Myszy%ROWTYPE INDEX BY BINARY_INTEGER;
    myszyNowe TabelaMyszyNowe;
    aktualnaOstatniaSroda DATE := NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'WED');
BEGIN
    FOR i IN 1..ile
        LOOP
            myszyNowe(i).lowca := kot;
            myszyNowe(i).zjadacz := NULL;
            myszyNowe(i).waga_myszy := DBMS_RANDOM.VALUE(2, 10);
            myszyNowe(i).data_zlowienia := SYSDATE;
            myszyNowe(i).data_wydania := aktualnaOstatniaSroda;
        END LOOP;

        FORALL i IN 1..myszyNowe.COUNT
            INSERT INTO Myszy(lowca, zjadacz, waga_myszy, data_zlowienia, data_wydania)
            VALUES (myszyNowe(i).lowca, myszyNowe(i).zjadacz, myszyNowe(i).waga_myszy, myszyNowe(i).data_zlowienia, myszyNowe(i).data_wydania);
END dopiszMyszy;

CREATE OR REPLACE PROCEDURE przygotujWyplate(dataWyplaty IN DATE DEFAULT SYSDATE) AS
    i NUMBER := 1;
    TYPE kotZWyplata IS RECORD (
        pseudo Kocury.PSEUDO%TYPE,
        zjada NUMBER
    );
    TYPE TabelaWyplatyDlaKotow IS TABLE OF kotZWyplata INDEX BY BINARY_INTEGER;
    wyplaty TabelaWyplatyDlaKotow;
BEGIN
    FOR kot IN (SELECT pseudo, NVL(przydzial_myszy, 0) + NVL(myszy_extra, 0) zjada
                FROM kocury
                WHERE w_stadku_od < dataWyplaty
                START WITH szef IS NULL
                CONNECT BY PRIOR pseudo = szef
                ORDER BY LEVEL DESC)
    LOOP
        wyplaty(i).pseudo := kot.pseudo;
        wyplaty(i).zjada := kot.zjada;
        i := i + 1;
    END LOOP;

    FORALL i IN 1..wyplaty.COUNT
        UPDATE Myszy
        SET zjadacz = wyplaty(i).pseudo
        WHERE zjadacz IS NULL AND ROWNUM <= wyplaty(i).zjada;
END przygotujWyplate;


DECLARE
    dataObecna DATE := '2004-01-01';
    poczatekMiesiaca DATE := dataObecna;
    dataKoncowa DATE := '2017-01-15';
    ileWpisanychMyszy NUMBER;
    maxUpolowan NUMBER;
    aktualnaOstatniaSroda DATE := NEXT_DAY(LAST_DAY(poczatekMiesiaca) - 7, 'WED');
    TYPE TabelaMyszyNowe IS TABLE OF Myszy%ROWTYPE INDEX BY BINARY_INTEGER;
    myszyNowe TabelaMyszyNowe;
    TYPE kotIleUpolowal IS RECORD (
        pseudo Kocury.PSEUDO%TYPE,
        upolowal NUMBER
    );
    TYPE TabelaKotyIleUpolowali IS TABLE OF kotIleUpolowal INDEX BY BINARY_INTEGER;
    kotyIPolowania TabelaKotyIleUpolowali;
    tmp NUMBER;
BEGIN
    WHILE aktualnaOstatniaSroda <= dataKoncowa
    LOOP
        SELECT SUM(NVL(przydzial_myszy,0) + NVL(myszy_extra,0)), AVG(NVL(przydzial_myszy,0) + NVL(myszy_extra,0))
        INTO ileWpisanychMyszy, maxUpolowan
        FROM Kocury
        WHERE w_stadku_od < aktualnaOstatniaSroda;

        EXECUTE IMMEDIATE 'SELECT pseudo, 0 FROM Kocury WHERE w_stadku_od <= :aktualnaOstatniaSroda' BULK COLLECT INTO kotyIPolowania
        USING aktualnaOstatniaSroda;

        FOR i IN 1..ileWpisanychMyszy
        LOOP
            tmp := DBMS_RANDOM.VALUE(1, kotyIPolowania.COUNT);
            IF kotyIPolowania.NEXT(tmp) IS NOT NULL
                THEN tmp := kotyIPolowania.NEXT(tmp);
            ELSE
                tmp := kotyIPolowania.FIRST;
            END IF;
            myszyNowe(i).lowca := kotyIPolowania(tmp).pseudo;
            myszyNowe(i).zjadacz := NULL;
            myszyNowe(i).waga_myszy := DBMS_RANDOM.VALUE(2, 10);
            myszyNowe(i).data_zlowienia := poczatekMiesiaca + DBMS_RANDOM.VALUE(0, EXTRACT(DAY FROM aktualnaOstatniaSroda));
            myszyNowe(i).data_wydania := aktualnaOstatniaSroda;
            IF kotyIPolowania(tmp).upolowal + 1 > maxUpolowan
                THEN IF kotyIPolowania.COUNT > 1
                        THEN kotyIPolowania.DELETE(tmp);
                     END IF;
                ELSE kotyIPolowania(tmp).upolowal := kotyIPolowania(tmp).upolowal + 1;
            END IF;
        END LOOP;

        FORALL i IN 1..myszyNowe.COUNT
            INSERT INTO Myszy(lowca, zjadacz, waga_myszy, data_zlowienia, data_wydania)
            VALUES (myszyNowe(i).lowca, myszyNowe(i).zjadacz, myszyNowe(i).waga_myszy, myszyNowe(i).data_zlowienia, myszyNowe(i).data_wydania);

        przygotujWyplate(aktualnaOstatniaSroda);

        poczatekMiesiaca := ADD_MONTHS(poczatekMiesiaca, 1);
        aktualnaOstatniaSroda := NEXT_DAY(LAST_DAY(poczatekMiesiaca) - 7, 'WED');
    END LOOP;
END;