SET AUTOCOMMIT OFF;

CREATE OR REPLACE PACKAGE Podwyzka AS
przydzial NUMBER DEFAULT 0;
nagroda NUMBER DEFAULT 0;
kara NUMBER DEFAULT 0;
END Podwyzka;

CREATE OR REPLACE TRIGGER przydzial_tygrysa
BEFORE UPDATE ON Kocury
BEGIN
SELECT przydzial_myszy INTO Podwyzka.przydzial FROM Kocury WHERE pseudo='TYGRYS';
END;

CREATE OR REPLACE TRIGGER Nowy_przydzial
BEFORE UPDATE ON Kocury
FOR EACH ROW
DECLARE
 max_m NUMBER DEFAULT 0;
 przydzial_m NUMBER DEFAULT 0;
BEGIN
 SELECT max_myszy INTO max_m
 FROM Funkcje WHERE funkcja =:NEW.funkcja;

 IF :NEW.funkcja = 'MILUSIA' THEN
    IF :NEW.przydzial_myszy < :OLD.przydzial_myszy THEN
        :NEW.przydzial_myszy := :OLD.przydzial_myszy;
        DBMS_OUTPUT.PUT_LINE('BLOKADA obnizania przydzialu kota ' || :NEW.pseudo);
    END IF;
    przydzial_m := :NEW.przydzial_myszy - :OLD.przydzial_myszy;
    IF(przydzial_m < 0.1*Podwyzka.przydzial) AND (przydzial_m >0) THEN
        DBMS_OUTPUT.PUT_LINE('KARA dla Tygrysa ');
        Podwyzka.kara := Podwyzka.kara + 1;
        :NEW.przydzial_myszy := :NEW.przydzial_myszy + Podwyzka.przydzial*0.1;
        :NEW.myszy_extra := :NEW.myszy_extra +5;
    ELSIF (przydzial_m < 0.1*Podwyzka.przydzial) AND (przydzial_m>0) THEN
        DBMS_OUTPUT.PUT_LINE('NAGRODA dla Tygrysa ');
        Podwyzka.nagroda := Podwyzka.nagroda+1;
    END IF;
 END IF;

 IF :NEW.przydzial_myszy > max_m THEN
    :NEW.przydzial_myszy := max_m;
 END IF;
END;

CREATE OR REPLACE TRIGGER Rozliczenie_Tygrysa
AFTER UPDATE ON Kocury
DECLARE
 n NUMBER DEFAULT 0;
BEGIN
    IF Podwyzka.kara > 0 THEN
        n := Podwyzka.kara;
        Podwyzka.kara := 0;
        UPDATE Kocury SET
        przydzial_myszy = przydzial_myszy -((0.1*n)*przydzial_myszy)
        WHERE pseudo = 'TYGRYS';
    END IF;
    IF Podwyzka.nagroda>0 THEN
        n := Podwyzka.nagroda;
        Podwyzka.nagroda :=0;
        UPDATE Kocury SET
        myszy_extra = myszy_extra+(n*5)
        WHERE pseudo = 'TYGRYS';
    END IF;
END;

SELECT *  FROM Kocury;
UPDATE Kocury SET przydzial_myszy = przydzial_myszy +1 ;
ROLLBACK;

--42b)

CREATE OR REPLACE TRIGGER Podwyzka_wyzwalacz
FOR UPDATE ON  Kocury
COMPOUND TRIGGER
    przydzial NUMBER DEFAULT 0;
    nagroda NUMBER DEFAULT 0;
    kara NUMBER DEFAULT 0;
    max_m NUMBER DEFAULT 0;
    przydzial_m NUMBER DEFAULT 0;
    n NUMBER DEFAULT 0;
BEFORE STATEMENT IS BEGIN
    SELECT przydzial_myszy INTO przydzial FROM Kocury WHERE pseudo='TYGRYS';
END BEFORE STATEMENT;

BEFORE EACH ROW IS BEGIN
    SELECT max_myszy INTO max_m
    FROM Funkcje WHERE funkcja =:NEW.funkcja;

    IF :NEW.funkcja = 'MILUSIA' THEN
        IF :NEW.przydzial_myszy < :OLD.przydzial_myszy THEN
            :NEW.przydzial_myszy := :OLD.przydzial_myszy;
            DBMS_OUTPUT.PUT_LINE('BLOKADA obnizania przydzialu kota ' || :NEW.pseudo);
        END IF;
        przydzial_m := :NEW.przydzial_myszy - :OLD.przydzial_myszy;
        IF(przydzial_m < 0.1*przydzial) AND (przydzial_m >0) THEN
            DBMS_OUTPUT.PUT_LINE('KARA dla Tygrysa ');
            kara := kara + 1;
            :NEW.przydzial_myszy := :NEW.przydzial_myszy + przydzial*0.1;
            :NEW.myszy_extra := :NEW.myszy_extra +5;
        ELSIF (przydzial_m < 0.1*przydzial) AND (przydzial_m>0) THEN
            DBMS_OUTPUT.PUT_LINE('NAGRODA dla Tygrysa ');
            nagroda := nagroda+1;
        END IF;
    END IF;

    IF :NEW.przydzial_myszy > max_m THEN
        :NEW.przydzial_myszy := max_m;
    END IF;
END BEFORE EACH ROW;

AFTER STATEMENT IS BEGIN
    IF kara > 0 THEN
        n := kara;
        kara := 0;
        UPDATE Kocury SET
        przydzial_myszy = przydzial_myszy -((0.1*n)*przydzial_myszy)
        WHERE pseudo = 'TYGRYS';
    END IF;
    IF nagroda>0 THEN
        n := nagroda;
        nagroda :=0;
        UPDATE Kocury SET
        myszy_extra = myszy_extra+(n*5)
        WHERE pseudo = 'TYGRYS';
    END IF;
END AFTER STATEMENT;
END Podwyzka_wyzwalacz;


SELECT * FROM Kocury;
UPDATE Kocury SET przydzial_myszy = przydzial_myszy + 5;
SELECT * FROM Kocury;
ROLLBACK;

--43)

SET SERVEROUTPUT ON;

DECLARE
    CURSOR kursorFunkcji IS
        SELECT DISTINCT Funkcje.funkcja
        FROM Kocury
        LEFT JOIN Funkcje ON Kocury.funkcja= Funkcje.funkcja;
    CURSOR kursorBand IS
        SELECT DISTINCT Bandy.nr_bandy , Bandy.nazwa
        FROM Kocury
        LEFT JOIN Bandy ON Kocury.nr_bandy = Bandy.nr_bandy;
    CURSOR kursorKocurow IS
        SELECT * FROM Kocury;
    plecc Kocury.plec%TYPE;
    ile NUMBER DEFAULT 0;
BEGIN
    DBMS_OUTPUT.PUT(RPAD('NAZWA BANDY ',16) || RPAD('PLEC',10) || 'ILE ' );
    FOR funk IN kursorFunkcji LOOP
        DBMS_OUTPUT.PUT(LPAD(funk.funkcja,10) || ' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(LPAD('SUMA',5));
    FOR banda IN kursorBand LOOP
    DBMS_OUTPUT.PUT(RPAD(banda.nazwa,15)|| ' ');
        FOR i IN 1..2 LOOP
            IF i = 1 THEN
                plecc := 'D';
                DBMS_OUTPUT.PUT(RPAD('Kotka',11));
            ELSE
                plecc := 'M';
                DBMS_OUTPUT.PUT(RPAD(' ',16));
                DBMS_OUTPUT.PUT(RPAD('Kocur',11));
            END IF;
            ile:=0;
            FOR kot IN kursorKocurow LOOP
                     IF kot.nr_bandy = banda.nr_bandy AND kot.plec =plecc  THEN
                     ile := ile + 1;
                END IF;
            END LOOP;
            DBMS_OUTPUT.PUT(ile ||' ');
            FOR funk IN kursorFunkcji LOOP
                SELECT SUM(CASE WHEN Kocury.funkcja = funk.funkcja THEN NVL(przydzial_myszy,0)+NVL(myszy_extra,0)
                ELSE 0
                END ) INTO ile
                    FROM Kocury
                    WHERE Kocury.nr_bandy = banda.nr_bandy AND Kocury.plec = plecc;
                DBMS_OUTPUT.PUT(LPAD(ile, 10) || ' ');
            END LOOP;
                ile := 0;
                FOR kot IN kursorKocurow LOOP
                     IF kot.nr_bandy = banda.nr_bandy AND kot.plec = plecc THEN
                     ile := ile + NVL(kot.przydzial_myszy , 0) + NVL(kot.myszy_extra,0);
                END IF;
                END LOOP;
                DBMS_OUTPUT.PUT(LPAD(ile,6) || ' ');
                DBMS_OUTPUT.PUT_LINE(' ');
            END LOOP;
        END LOOP;
         DBMS_OUTPUT.PUT(RPAD('ZJADA RAZEM ',15));
         DBMS_OUTPUT.PUT(RPAD(' ', 14));
         FOR funk IN kursorFunkcji LOOP
            ile := 0;
             FOR kot IN kursorKocurow LOOP
                     IF kot.funkcja = funk.funkcja  THEN
                     ile := ile + NVL(kot.przydzial_myszy , 0) + NVL(kot.myszy_extra,0);
                END IF;
                END LOOP;
                DBMS_OUTPUT.PUT(LPAD(ile,10) || ' ');
        END LOOP;
        ile := 0;
        FOR kot IN kursorKocurow LOOP
            ile := ile + NVL(kot.przydzial_myszy , 0) + NVL(kot.myszy_extra,0);
        END LOOP;
            DBMS_OUTPUT.PUT(LPAD(ile,6) || ' ');

        DBMS_OUTPUT.PUT_LINE(' ');
END;

--44)
SET AUTOCOMMIT OFF;


CREATE OR REPLACE PACKAGE podatek_kocurow AS
    PROCEDURE nowa_banda(
        numer_bandy Bandy.nr_bandy%TYPE,
        nazwa_bandy Bandy.nazwa%TYPE,
        teren_polowan Bandy.teren%TYPE);
    FUNCTION podatek_kota(
    pseudo_kota Kocury.pseudo%TYPE)
    RETURN NUMBER;
END podatek_kocurow;

CREATE OR REPLACE PACKAGE BODY podatek_kocurow AS
    PROCEDURE nowa_banda(
    numer_bandy bandy.nr_bandy%TYPE,
    nazwa_bandy bandy.nazwa%TYPE,
    teren_polowan bandy.teren%TYPE)
    AS
    ZLY_NUMER EXCEPTION;
    ZLA_WARTOSC EXCEPTION;
    blad STRING(256);
    ilosc NUMBER DEFAULT 0;
    BEGIN
     IF numer_bandy<=0 THEN
      RAISE ZLY_NUMER;
     END IF;
     SELECT COUNT(nr_bandy) INTO ilosc FROM Bandy WHERE nr_bandy= numer_bandy;
     IF ilosc > 0 THEN
        blad := TO_CHAR(numer_bandy);
     END IF;
     SELECT COUNT(nazwa_bandy) INTO ilosc FROM Bandy WHERE Bandy.nazwa = nazwa_bandy;
     IF ilosc >0 THEN
        blad := blad||', '||nazwa_bandy;
     END IF;
     SELECT COUNT(teren) INTO ilosc FROM Bandy WHERE Bandy.teren = teren_polowan;
     IF ilosc>0 THEN
        blad := blad||', '||teren_polowan;
     END IF;
      IF LENGTH(blad) > 0 THEN
        RAISE ZLA_WARTOSC;
      END IF;

     INSERT INTO Bandy( nr_bandy, nazwa, teren)
     VALUES (numer_bandy, nazwa_bandy, teren_polowan);
     EXCEPTION
      WHEN ZLY_NUMER THEN DBMS_OUTPUT.PUT_LINE('Wrowadzono blędny numer mniejszy od 1!!');
      WHEN ZLA_WARTOSC THEN DBMS_OUTPUT.PUT_LINE(blad||': juz istnieje');

    END;

    FUNCTION podatek_kota(
    pseudo_kota Kocury.pseudo%TYPE)
    RETURN NUMBER IS
    licznik NUMBER DEFAULT 0;
    podatek NUMBER DEFAULT 0;
    BEGIN
        SELECT CEIL((NVL(przydzial_myszy,1)+NVL(myszy_extra,1))*0.05) INTO podatek
        FROM Kocury WHERE pseudo = pseudo_kota;

        SELECT COUNT(pseudo) INTO licznik
        FROM Kocury WHERE szef = pseudo_kota;
        IF licznik = 0 THEN
            podatek := podatek + 2;
        END IF;

        SELECT COUNT(pseudo) INTO licznik
        FROM wrogowie_kocurow WHERE pseudo  = pseudo_kota;
        IF licznik = 0 THEN
           podatek := podatek + 1;
        END IF;

        SELECT COUNT(pseudo) INTO licznik
        FROM Kocury WHERE  pseudo=pseudo_kota AND przydzial_myszy > 50;
        IF licznik > 0 THEN
        podatek := podatek + 8;
        END IF;
        RETURN podatek;
    END podatek_kota;
END podatek_kocurow;

SELECT pseudo , podatek_kocurow.podatek_kota(pseudo) "Podatek" FROM Kocury ;

--45)

CREATE TABLE Dodatki_extra(
    id_dodatku NUMBER(2) GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT id_pk PRIMARY KEY,
    pseudo VARCHAR2(15),
    dodatek_extra NUMBER(3) NOT NULL
);
DROP TABLE Dodatki_extra;
CREATE OR REPLACE TRIGGER blokada_Tygrysa
AFTER UPDATE OF przydzial_myszy ON Kocury
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF  :NEW.przydzial_myszy > :old.przydzial_myszy
  AND :NEW.funkcja = 'MILUSIA'
  AND LOGIN_USER != 'TYGRYS' THEN
    EXECUTE IMMEDIATE '
      DECLARE
        CURSOR kursorMilusie IS
          SELECT pseudo FROM Kocury WHERE funkcja = ''MILUSIA'';
      BEGIN
        FOR kot IN kursorMilusie LOOP
          INSERT INTO dodatki_extra(pseudo, dodatek_extra)
          VALUES (kot.pseudo, -10);
        END LOOP;
      END;';
    COMMIT;
  END IF;
END;

SELECT * FROM dodatki_extra;
UPDATE Kocury SET przydzial_myszy = przydzial_myszy + 5;
SELECT * FROM Kocury;
ROLLBACK;
DROP TRIGGER blokada_Tygrysa;
DROP TABLE Dodatki_extra;

--46)

CREATE TABLE Monitor(
id_proby NUMBER(2) GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT id_pk PRIMARY KEY,
    pseudo_wprowadzajacego VARCHAR2(15),
    data_zmiany DATE DEFAULT SYSDATE,
    pseudo VARCHAR2(15),
    transakcja VARCHAR2(8)
) ;

DROP TABLE Monitor;
DROP TRIGGER blokada_przydzialu;
CREATE OR REPLACE TRIGGER blokada_przydzialu
BEFORE UPDATE OF przydzial_myszy OR INSERT ON Kocury
FOR EACH ROW
DECLARE
    max_m Funkcje.max_myszy%TYPE;
    min_m Funkcje.min_myszy%TYPE;
    login Monitor.pseudo_wprowadzajacego%TYPE;
    dat  Monitor.data_Zmiany%TYPE;
    pseudo Kocury.pseudo%TYPE;
    trans VARCHAR2(8);
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
CASE
WHEN inserting THEN trans:='INSERT';
WHEN updating THEN trans:='UPDATE';
END CASE;
    login := LOGIN_USER;
    dat :=SYSDATE;
    SELECT max_myszy INTO max_m
    FROM Funkcje WHERE :NEW.funkcja = Funkcje.funkcja;
    SELECT min_myszy INTO min_m
    FROM Funkcje WHERE :NEW.funkcja = Funkcje.funkcja;

    IF :NEW.przydzial_myszy > max_m OR  :NEW.przydzial_myszy < min_m THEN
        :NEW.przydzial_myszy := :OLD.przydzial_myszy;
        pseudo := :NEW.pseudo;
        INSERT INTO Monitor(pseudo_wprowadzajacego, data_zmiany, pseudo, transakcja)
        VALUES(login,dat,pseudo, trans);
        DBMS_OUTPUT.PUT('ZAKAZ !!!');

    END IF;

END;

SELECT * FROM Monitor;
UPDATE Kocury SET przydzial_myszy = przydzial_myszy + 10 WHERE pseudo = 'PLACEK';
ROLLBACK;
SELECT * FROM Kocury;
SET AUTOCOMMIT OFF;
SET SERVEROUTPUT ON;