DROP SCHEMA IF EXISTS AUTO_SKOLA CASCADE;
CREATE SCHEMA AUTO_SKOLA;

SET SEARCH_PATH TO AUTO_SKOLA;

CREATE TABLE POLAZNIK (
    SIFRA SERIAL PRIMARY KEY,
    IME VARCHAR(50) NOT NULL,
    PREZIME VARCHAR(50) NOT NULL,
    IZDATA_DOZVOLA BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE ISPIT (
    SIFRA_POLAZNIKA INT NOT NULL,
    DATUM DATE NOT NULL DEFAULT CURRENT_DATE,
    TIP_ISPITA VARCHAR(10) NOT NULL CHECK (TIP_ISPITA IN ('teorijski', 'prakticni')),
    BODOVI INT NOT NULL CHECK (BODOVI BETWEEN 0 AND 100),
    ISHOD VARCHAR(10),

    FOREIGN KEY (SIFRA_POLAZNIKA) REFERENCES POLAZNIK(SIFRA)
                   ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE STATISTIKE (
    TIP_ISPITA VARCHAR(10) PRIMARY KEY,
    BROJ_POLAGANJA INT NOT NULL DEFAULT 0,
    BROJ_POLOZENIH INT NOT NULL DEFAULT 0
);

INSERT INTO POLAZNIK(IME, PREZIME)
VALUES ('Pera', 'Peric'),
       ('Mika', 'Mikic'),
       ('Zika', 'Zikic');

SELECT * FROM POLAZNIK;

/*
 * a) Prilikom umetanja ili azuriranja novog ispita, potrebno je automatski odrediti
 *    njegov ishod. Ishod moze biti 'polozio' ili 'pao', pri cemu je polaznik:
 *    - polozio TEORIJSKI ispit ukoliko je osvojio bar 85 bodova;
 *    - polozio PRAKTICNI ispit ukoliko je osvojio manje od 15 bodova (kazneni poeni).
 */

CREATE FUNCTION ODREDI_I_POSTAVI_ISHOD_ISPITA()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.TIP_ISPITA = 'teorijski' AND NEW.BODOVI >= 85 THEN
        NEW.ISHOD := 'polozio';
    ELSIF NEW.TIP_ISPITA = 'prakticni' AND NEW.BODOVI < 15 THEN
        NEW.ISHOD := 'polozio';
    ELSE
        NEW.ISHOD := 'pao';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ODREDI_I_POSTAVI_ISHOD_ISPITA
BEFORE INSERT OR UPDATE ON ISPIT
FOR EACH ROW
EXECUTE FUNCTION ODREDI_I_POSTAVI_ISHOD_ISPITA();

INSERT INTO ISPIT(SIFRA_POLAZNIKA, DATUM, TIP_ISPITA, BODOVI)
VALUES (1, '2025-10-10', 'teorijski', 92);

INSERT INTO ISPIT(SIFRA_POLAZNIKA, DATUM, TIP_ISPITA, BODOVI)
VALUES (1, '2025-10-10', 'prakticni', 17);

SELECT * FROM ISPIT;

UPDATE ISPIT
SET BODOVI = 14
WHERE SIFRA_POLAZNIKA = 1 AND TIP_ISPITA = 'prakticni';

SELECT * FROM ISPIT;

/*
 * b) Potrebno je validirati izdavanje vozackih dozvola. Polazniku se dozvola moze
 *    izdati samo ukoliko je prethodno polozio i teorijski, i prakticni ispit. U
 *    suprotnom, potrebno je prijaviti gresku prilikom pokusaja izdavanja dozvole.
 */

CREATE FUNCTION VALIDIRAJ_IZDAVANJE_DOZVOLE()
RETURNS TRIGGER AS $$
DECLARE
    POLOZIO_TEORIJSKI BOOLEAN;
    POLOZIO_PRAKTICNI BOOLEAN;
BEGIN
    POLOZIO_TEORIJSKI := EXISTS (
        SELECT *
        FROM ISPIT
        WHERE SIFRA_POLAZNIKA = NEW.SIFRA AND
              TIP_ISPITA = 'teorijski' AND
              ISHOD = 'polozio'
    );
    POLOZIO_PRAKTICNI := EXISTS (
        SELECT *
        FROM ISPIT
        WHERE SIFRA_POLAZNIKA = NEW.SIFRA AND
              TIP_ISPITA = 'prakticni' AND
              ISHOD = 'polozio'
    );
    IF NEW.IZDATA_DOZVOLA = TRUE AND (NOT POLOZIO_TEORIJSKI OR NOT POLOZIO_PRAKTICNI) THEN
        RAISE EXCEPTION 'Da bi se izdala dozvola, polaznik % % prvo mora poloziti teorijski i prakticni ispit!',
                        NEW.IME, NEW.PREZIME;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER VALIDIRAJ_IZDAVANJE_DOZVOLE
BEFORE INSERT OR UPDATE ON POLAZNIK
FOR EACH ROW
EXECUTE FUNCTION VALIDIRAJ_IZDAVANJE_DOZVOLE();

UPDATE POLAZNIK
SET IZDATA_DOZVOLA = TRUE
WHERE SIFRA = 1;

SELECT * FROM POLAZNIK;

-- UPDATE POLAZNIK
-- SET IZDATA_DOZVOLA = TRUE
-- WHERE SIFRA = 2;

/*
 * c) U posebnoj tabeli STATISTIKE se prate statistike o polaganjima teorijskih
 *    i prakticnih ispita (konkretno, prati se broj polaganja i broj polozenih
 *    ispita). Prilikom bilo kakvih operacija nad tabelom ISPIT, potrebno je
 *    azurirati i odgovarajuce statistike.
 *
 *    Pretpostaviti da tabela STATISTIKE vec sadrzi odgovarajuce redove za
 *    teorijske i prakticne ispite.
 */

DELETE
FROM ISPIT
WHERE TRUE;

INSERT INTO STATISTIKE(TIP_ISPITA)
VALUES ('teorijski'), ('prakticni');

CREATE FUNCTION AZURIRAJ_STATISTIKE_ISPITA()
RETURNS TRIGGER AS $$
BEGIN
    -- Kao sto INSERT okidaci imaju pristup specijalnom redu NEW,
    -- tako DELETE okidaci imaju pristup specijalnom redu OLD (red koji se brise/je obrisan),
    -- a UPDATE okidaci imaju pristup i NEW i OLD (stanje reda pre azuriranja i stanje reda
    -- nakon azuriranja).
    IF TG_OP = 'INSERT' THEN
        UPDATE STATISTIKE
        SET BROJ_POLAGANJA = BROJ_POLAGANJA + 1,
            BROJ_POLOZENIH = BROJ_POLOZENIH + CASE WHEN NEW.ISHOD = 'polozio' THEN 1 ELSE 0 END
        WHERE TIP_ISPITA = NEW.TIP_ISPITA;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE STATISTIKE
        SET BROJ_POLAGANJA = BROJ_POLAGANJA - 1,
            BROJ_POLOZENIH = BROJ_POLOZENIH - CASE WHEN OLD.ISHOD = 'polozio' THEN 1 ELSE 0 END
        WHERE TIP_ISPITA = OLD.TIP_ISPITA;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE STATISTIKE
        SET BROJ_POLAGANJA = BROJ_POLAGANJA - 1,
            BROJ_POLOZENIH = BROJ_POLOZENIH - CASE WHEN OLD.ISHOD = 'polozio' THEN 1 ELSE 0 END
        WHERE TIP_ISPITA = OLD.TIP_ISPITA;
        UPDATE STATISTIKE
        SET BROJ_POLAGANJA = BROJ_POLAGANJA + 1,
            BROJ_POLOZENIH = BROJ_POLOZENIH + CASE WHEN NEW.ISHOD = 'polozio' THEN 1 ELSE 0 END
        WHERE TIP_ISPITA = NEW.TIP_ISPITA;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER AZURIRAJ_STATISTIKE_ISPITA
AFTER INSERT OR UPDATE OR DELETE ON ISPIT
FOR EACH ROW
EXECUTE FUNCTION AZURIRAJ_STATISTIKE_ISPITA();

SELECT * FROM ISPIT;

SELECT * FROM STATISTIKE;

INSERT INTO ISPIT(SIFRA_POLAZNIKA, DATUM, TIP_ISPITA, BODOVI)
VALUES (1, '2025-10-10', 'teorijski', 92),
       (2, '2025-10-10', 'teorijski', 43);

SELECT * FROM STATISTIKE;

UPDATE ISPIT
SET TIP_ISPITA = 'prakticni'
WHERE SIFRA_POLAZNIKA = 2 AND TIP_ISPITA = 'teorijski';

SELECT * FROM STATISTIKE;

DELETE
FROM ISPIT
WHERE SIFRA_POLAZNIKA = 2;

SELECT * FROM STATISTIKE;
