DROP SCHEMA IF EXISTS BANKA CASCADE;
CREATE SCHEMA BANKA;

SET SEARCH_PATH TO BANKA;

CREATE TABLE RACUN (
    BROJ_RACUNA INT PRIMARY KEY,
    STANJE_NA_RACUNU REAL NOT NULL DEFAULT 0.00, -- Namerno nije dodat CHECK za nenegativnu vrednost radi ilustracije resenja sa okidacima
    DNEVNI_LIMIT REAL NOT NULL DEFAULT 5000.00
);

CREATE TABLE TRANSAKCIJA (
    SIFRA SERIAL PRIMARY KEY,
    BROJ_RACUNA_POSILJAOCA INT NOT NULL,
    BROJ_RACUNA_PRIMAOCA INT NOT NULL,
    IZNOS REAL NOT NULL CHECK (IZNOS > 0.00),
    DATUM DATE NOT NULL DEFAULT CURRENT_DATE,

    FOREIGN KEY (BROJ_RACUNA_POSILJAOCA) REFERENCES RACUN(BROJ_RACUNA)
                         ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (BROJ_RACUNA_PRIMAOCA) REFERENCES RACUN(BROJ_RACUNA)
                         ON DELETE RESTRICT ON UPDATE RESTRICT,

    CHECK (BROJ_RACUNA_POSILJAOCA <> BROJ_RACUNA_PRIMAOCA)
);

INSERT INTO RACUN(BROJ_RACUNA, STANJE_NA_RACUNU)
VALUES (123, 10000.00),
       (456, 20000.00);

/*
 * a) Trenutno se, prilikom kreiranja novih transakcija, stanja na racunima ne azuriraju.
 *    Implementirati okidac kojim se, nakon svake transakcije, shodno njoj azuriraju stanja
 *    racuna posiljaoca i primaoca.
 */

CREATE FUNCTION PRI_NOVOJ_TRANSAKCIJI_AZURIRAJ_STANJA_RACUNA()
RETURNS TRIGGER AS $$
BEGIN
    -- INSERT okidaci nam daju pristup specijalnoj promenljivoj NEW
    -- koja referise na red koji se dodaje (za BEFORE okidace) ili je
    -- dodat (za AFTER okidace) u odgovarajucu tabelu (u ovom slucaju,
    -- to je tabela TRANSAKCIJA).
    UPDATE RACUN
    SET STANJE_NA_RACUNU = STANJE_NA_RACUNU - NEW.IZNOS
    WHERE BROJ_RACUNA = NEW.BROJ_RACUNA_POSILJAOCA;

    UPDATE RACUN
    SET STANJE_NA_RACUNU = STANJE_NA_RACUNU + NEW.IZNOS
    WHERE BROJ_RACUNA = NEW.BROJ_RACUNA_PRIMAOCA;

    RETURN NULL; -- AFTER okidaci mogu vratiti i NULL vrednost, to nece uticati na rezultat izvrsavanja operacije
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER PRI_NOVOJ_TRANSAKCIJI_AZURIRAJ_STANJA_RACUNA
AFTER INSERT ON TRANSAKCIJA
FOR EACH ROW
EXECUTE FUNCTION PRI_NOVOJ_TRANSAKCIJI_AZURIRAJ_STANJA_RACUNA();

SELECT * FROM RACUN;

INSERT INTO TRANSAKCIJA(BROJ_RACUNA_POSILJAOCA, BROJ_RACUNA_PRIMAOCA, IZNOS)
VALUES (123, 456, 2000.00),
       (456, 123, 1000.00);

SELECT * FROM RACUN;

/*
 * b) Trenutno je moguce kreirati transakciju kojom bi stanje na racunu posiljaoca
 *    otislo u negativan iznos. Implementirati okidac kojim se onemogucava kreiranje
 *    ovakvih transakcija.
 */

-- Ovo smo mogli resiti i dodavanjem jednostavnog CHECK-a za RACUN.STANJE_NA_RACUNU,
-- tj. CHECK (STANJE_NA_RACUNU >= 0), ali pokazimo kako bi isto mogli da izvedemo
-- i pomocu okidaca:

CREATE FUNCTION PROVERI_VALIDNOST_TRANSAKCIJE()
RETURNS TRIGGER AS $$
DECLARE
    STANJE_RACUNA_POSILJAOCA REAL; -- Deklaracija promenljive
BEGIN
    STANJE_RACUNA_POSILJAOCA := (
        SELECT STANJE_NA_RACUNU
        FROM RACUN
        WHERE BROJ_RACUNA = NEW.BROJ_RACUNA_POSILJAOCA
    );

    IF NEW.IZNOS > STANJE_RACUNA_POSILJAOCA THEN
        RAISE EXCEPTION 'Transakcija % premasuje stanje racuna %!', NEW.SIFRA, NEW.BROJ_RACUNA_POSILJAOCA;
        -- Ukoliko bi zeleli da obustavimo izvrsavanje operacije bez prijavljivanja greske,
        -- mogli bismo da uradimo RETURN NULL umesto RAISE EXCEPTION.
        -- Vracanjem NULL vrednosti iz funkcije BEFORE okidaca (tiho) obustavlja izvrsavanje
        -- operacije za koju je okidac vezan.
    END IF;

    -- U BEFORE INSERT okidacima, potrebno je vratiti NEW kao povratnu vrednost ukoliko
    -- zelimo normalno izvrsavanje INSERT operacije.
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER PROVERI_VALIDNOST_TRANSAKCIJE
BEFORE INSERT ON TRANSAKCIJA
FOR EACH ROW
EXECUTE FUNCTION PROVERI_VALIDNOST_TRANSAKCIJE();

-- INSERT INTO TRANSAKCIJA(BROJ_RACUNA_POSILJAOCA, BROJ_RACUNA_PRIMAOCA, IZNOS)
-- VALUES (123, 456, 50000.00);

/*
 * c) Svaki racun ima svoj dnevni limit. Ukoliko neka transakcija premasuje
 *    dnevni limit racuna posiljaoca, potrebno je izmeniti, tj. ograniciti
 *    njen iznos do preostalog dnevnog limita. Ukoliko je dnevni limit potpuno
 *    istrosen, u potpunosti zabraniti izvrsavanje transakcije.
 */

CREATE FUNCTION OGRANICI_NA_DNEVNI_LIMIT_PRI_NOVOJ_TRANSAKCIJI()
RETURNS TRIGGER AS $$
DECLARE
    DNEVNI_LIMIT_POSILJAOCA REAL;
    POTROSENO_U_DANU REAL;
BEGIN
    DNEVNI_LIMIT_POSILJAOCA := (
        SELECT DNEVNI_LIMIT
        FROM RACUN
        WHERE BROJ_RACUNA = NEW.BROJ_RACUNA_POSILJAOCA
    );

    POTROSENO_U_DANU := (
        SELECT SUM(IZNOS)
        FROM TRANSAKCIJA
        WHERE BROJ_RACUNA_POSILJAOCA = NEW.BROJ_RACUNA_POSILJAOCA AND
              DATUM = NEW.DATUM
    );

    IF POTROSENO_U_DANU + NEW.IZNOS > DNEVNI_LIMIT_POSILJAOCA THEN
        RAISE NOTICE 'Transakcija % premasuje dnevni limit racuna % ::: '
                     'Iznos je umanjen sa % na %!',
                     NEW.SIFRA, NEW.BROJ_RACUNA_POSILJAOCA,
                     NEW.IZNOS, DNEVNI_LIMIT_POSILJAOCA - POTROSENO_U_DANU;
        -- Ovako mozemo izmeniti odgovarajuce atribute u redu koji dodajemo,
        -- ali samo ako je u pitanju BEFORE okidac.
        NEW.IZNOS := DNEVNI_LIMIT_POSILJAOCA - POTROSENO_U_DANU;
    END IF;

    IF POTROSENO_U_DANU = DNEVNI_LIMIT_POSILJAOCA THEN
        RAISE EXCEPTION 'Racun % je potpuno potrosio svoj dnevni limit ::: '
                        'Transakcija obustavljena!', NEW.BROJ_RACUNA_POSILJAOCA;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER OGRANICI_NA_DNEVNI_LIMIT_PRI_NOVOJ_TRANSAKCIJI
BEFORE INSERT ON TRANSAKCIJA
FOR EACH ROW
EXECUTE FUNCTION OGRANICI_NA_DNEVNI_LIMIT_PRI_NOVOJ_TRANSAKCIJI();

-- Iznos ce biti umanjen sa 4000 na 3000!
INSERT INTO TRANSAKCIJA(BROJ_RACUNA_POSILJAOCA, BROJ_RACUNA_PRIMAOCA, IZNOS)
VALUES (123, 456, 4000.00);

-- Dnevni limit potrosen!
-- INSERT INTO TRANSAKCIJA(BROJ_RACUNA_POSILJAOCA, BROJ_RACUNA_PRIMAOCA, IZNOS)
-- VALUES (123, 456, 1000.00);

/*
 * d) Potrebno je onemoguciti brisanje ili azuriranje vec kreiranih transakcija.
 */

CREATE FUNCTION ZABRANI_AZURIRANJE_I_BRISANJE_TRANSAKCIJA()
RETURNS TRIGGER AS $$
BEGIN
    -- Funkcije okidaca implicitno daju pristup specijalnim promenljivama poput
    -- promenljive TG_OP koja sadrzi (u vidu niske) naziv operacije za koju je
    -- okidac izvrsen ('INSERT', 'DELETE' ili 'UPDATE').
    RAISE EXCEPTION 'Izvrsavanje operacije % nad tabelom TRANSAKCIJA nije dozvoljeno!', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ZABRANI_AZURIRANJE_I_BRISANJE_TRANSAKCIJA
BEFORE UPDATE OR DELETE ON TRANSAKCIJA
FOR EACH STATEMENT
EXECUTE FUNCTION ZABRANI_AZURIRANJE_I_BRISANJE_TRANSAKCIJA();

-- UPDATE TRANSAKCIJA
-- SET IZNOS = IZNOS + 100.00
-- WHERE SIFRA = -1; -- Sa FOR EACH ROW, ova naredba ne bi izbacila izuzetak jer ne gadja nijedan red.
--
-- UPDATE TRANSAKCIJA
-- SET IZNOS = IZNOS + 100.00
-- WHERE SIFRA = 1;
--
-- DELETE FROM TRANSAKCIJA
-- WHERE SIFRA = 1;
