DatabaseProgramming: phase04.sql

CREATE OR REPLACE FUNCTION fn_status_kazna(p_kazna_id INTEGER)
RETURNS VARCHAR
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    SELECT sk.ime
    INTO v_status
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = p_kazna_id;

    IF v_status IS NULL THEN
        RETURN 'Kaznata ne postoi';
    ELSIF LOWER(v_status) = 'platena' THEN
        RETURN 'Kaznata e platena';
    ELSIF LOWER(v_status) = 'neplatena' THEN
        RETURN 'Kaznata ne e platena';
    ELSIF LOWER(v_status) = 'ponishtena' THEN
        RETURN 'Kaznata e ponishtena';
    ELSE
        RETURN 'Nepoznat status na kazna';
    END IF;
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION fn_presmetaj_iznos_kazna(p_tip_prekrsok_id INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    v_iznos INTEGER;
BEGIN
    SELECT iznos
    INTO v_iznos
    FROM TipPrekrsok
    WHERE tip_prekrsok_id = p_tip_prekrsok_id;

    IF v_iznos IS NULL THEN
        RAISE EXCEPTION 'Ne postoi tip na prekrsok so id %', p_tip_prekrsok_id;
    ELSIF v_iznos <= 0 THEN
        RAISE EXCEPTION 'Iznosot na kaznata mora da bide pogolem od 0';
    ELSE
        RETURN v_iznos;
    END IF;
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE PROCEDURE sp_evidentiraj_plakanje(
    p_kazna_id INTEGER,
    p_metod_plakanje_id INTEGER
)
AS $$
DECLARE
    v_postoi_kazna INTEGER;
    v_postoi_metod INTEGER;
    v_status VARCHAR;
BEGIN
    SELECT COUNT(*)
    INTO v_postoi_kazna
    FROM Kazna
    WHERE kazna_id = p_kazna_id;

    IF v_postoi_kazna = 0 THEN
        RAISE EXCEPTION 'Ne postoi kazna so id %', p_kazna_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_metod
    FROM MetodPlakanje
    WHERE metod_plakanje_id = p_metod_plakanje_id;

    IF v_postoi_metod = 0 THEN
        RAISE EXCEPTION 'Ne postoi metod na plakanje so id %', p_metod_plakanje_id;
    END IF;


    SELECT sk.ime
    INTO v_status
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = p_kazna_id;


    IF LOWER(v_status) = 'platena' THEN
        RAISE EXCEPTION 'Kaznata e vekje platena';
    ELSIF LOWER(v_status) = 'ponishtena' THEN
        RAISE EXCEPTION 'Ponisthena kazna ne moze da se plati';
    ELSE
        INSERT INTO Plakanje(
            metod_plakanje_id,
            datum,
            kazna_id
        )
        VALUES(
            p_metod_plakanje_id,
            CURRENT_DATE,
            p_kazna_id
        );
    END IF;
END;
$$
LANGUAGE plpgsql;




CREATE OR REPLACE PROCEDURE sp_podnesi_zalba(
    p_sodrzina VARCHAR,
    p_korisnik_id INTEGER,
    p_prekrsok_id INTEGER
)
AS $$
DECLARE
    v_status_podnesena INTEGER;
    v_postoi_korisnik INTEGER;
    v_postoi_prekrsok INTEGER;
BEGIN
    IF p_sodrzina IS NULL OR LENGTH(TRIM(p_sodrzina)) = 0 THEN
        RAISE EXCEPTION 'Zalbata mora da ima sodrzina';
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_korisnik
    FROM Korisnik
    WHERE korisnik_id = p_korisnik_id;

    IF v_postoi_korisnik = 0 THEN
        RAISE EXCEPTION 'Ne postoi korisnik so id %', p_korisnik_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_prekrsok
    FROM Prekrsok
    WHERE prekrsok_id = p_prekrsok_id;

    IF v_postoi_prekrsok = 0 THEN
        RAISE EXCEPTION 'Ne postoi prekrsok so id %', p_prekrsok_id;
    END IF;


    SELECT status_zalba_id
    INTO v_status_podnesena
    FROM StatusZalba
    WHERE LOWER(ime) = 'podnesena';

    IF v_status_podnesena IS NULL THEN
        RAISE EXCEPTION 'Vo StatusZalba ne postoi status podnesena';
    ELSE
        INSERT INTO Zalba(
            sodrzina,
            datum_na_podnesuvanje,
            status_zalba_id,
            korisnik_id,
            prekrsok_id
        )
        VALUES(
            p_sodrzina,
            CURRENT_DATE,
            v_status_podnesena,
            p_korisnik_id,
            p_prekrsok_id
        );
    END IF;
END;
$$
LANGUAGE plpgsql;





CREATE OR REPLACE PROCEDURE sp_razgledaj_zalba(
    p_zalba_id INTEGER,
    p_administrator_id INTEGER,
    p_nov_status VARCHAR
)
AS $$
DECLARE
    v_status_id INTEGER;
    v_postoi_zalba INTEGER;
    v_postoi_admin INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_postoi_zalba
    FROM Zalba
    WHERE zalba_id = p_zalba_id;

    IF v_postoi_zalba = 0 THEN
        RAISE EXCEPTION 'Ne postoi zalba so id %', p_zalba_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_admin
    FROM Administrator
    WHERE administrator_id = p_administrator_id;

    IF v_postoi_admin = 0 THEN
        RAISE EXCEPTION 'Ne postoi administrator so id %', p_administrator_id;
    END IF;


    SELECT status_zalba_id
    INTO v_status_id
    FROM StatusZalba
    WHERE LOWER(ime) = LOWER(p_nov_status);

    IF v_status_id IS NULL THEN
        RAISE EXCEPTION 'Ne postoi status na zalba so ime %', p_nov_status;
    ELSE
        UPDATE Zalba
        SET administrator_id = p_administrator_id,
            status_zalba_id = v_status_id
        WHERE zalba_id = p_zalba_id;
    END IF;
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE PROCEDURE sp_arhiviraj_stari_snimki()
AS $$
DECLARE
    v_broj_snimki INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_broj_snimki
    FROM Snimka
    WHERE datum < CURRENT_DATE - INTERVAL '365 days'
      AND arhivirana = FALSE;

    IF v_broj_snimki = 0 THEN
        RAISE NOTICE 'Nema snimki za arhiviranje';
    ELSE
        UPDATE Snimka
        SET arhivirana = TRUE,
            datum_arhiviranje = CURRENT_DATE
        WHERE datum < CURRENT_DATE - INTERVAL '365 days'
          AND arhivirana = FALSE;

        RAISE NOTICE 'Arhivirani se % snimki', v_broj_snimki;
    END IF;
END;
$$
LANGUAGE plpgsql;





CREATE OR REPLACE PROCEDURE sp_kreiraj_prekrsok_so_kazna(
    p_opis VARCHAR,
    p_vreme TIME,
    p_datum DATE,
    p_detektirana_brzina INTEGER,
    p_tip_prekrsok_id INTEGER,
    p_kamera_id INTEGER,
    p_storitel_embg VARCHAR
)
AS $$
DECLARE
    v_kazna_id INTEGER;
    v_prekrsok_id INTEGER;
    v_iznos INTEGER;
    v_status_kazna_id INTEGER;
    v_status_prekrsok_id INTEGER;
    v_postoi_tip INTEGER;
    v_postoi_kamera INTEGER;
    v_postoi_gragjanin INTEGER;
BEGIN
    IF p_opis IS NULL OR LENGTH(TRIM(p_opis)) = 0 THEN
        RAISE EXCEPTION 'Opisot na prekrsokot ne smee da bide prazen';
    END IF;


    IF p_datum IS NULL THEN
        p_datum := CURRENT_DATE;
    END IF;


    IF p_detektirana_brzina IS NULL OR p_detektirana_brzina <= 0 THEN
        RAISE EXCEPTION 'Detektiranata brzina mora da bide pogolema od 0';
    END IF;


    IF p_storitel_embg IS NULL OR LENGTH(TRIM(p_storitel_embg)) <> 13 THEN
        RAISE EXCEPTION 'EMBG na storitelot mora da ima 13 karakteri';
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_gragjanin
    FROM Gragjanin
    WHERE embg = p_storitel_embg;

    IF v_postoi_gragjanin = 0 THEN
        RAISE EXCEPTION 'Ne postoi gragjanin so EMBG %', p_storitel_embg;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_tip
    FROM TipPrekrsok
    WHERE tip_prekrsok_id = p_tip_prekrsok_id;

    IF v_postoi_tip = 0 THEN
        RAISE EXCEPTION 'Ne postoi tip prekrsok so id %', p_tip_prekrsok_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_kamera
    FROM Kamera
    WHERE kamera_id = p_kamera_id;

    IF v_postoi_kamera = 0 THEN
        RAISE EXCEPTION 'Ne postoi kamera so id %', p_kamera_id;
    END IF;


    SELECT status_kazna_id
    INTO v_status_kazna_id
    FROM StatusKazna
    WHERE LOWER(ime) = 'neplatena';

    IF v_status_kazna_id IS NULL THEN
        RAISE EXCEPTION 'Vo StatusKazna ne postoi status neplatena';
    END IF;


    SELECT status_prekrsok_id
    INTO v_status_prekrsok_id
    FROM StatusPrekrsok
    WHERE LOWER(ime) = 'aktiven';

    IF v_status_prekrsok_id IS NULL THEN
        RAISE EXCEPTION 'Vo StatusPrekrsok ne postoi status aktiven';
    END IF;


    v_iznos := fn_presmetaj_iznos_kazna(p_tip_prekrsok_id);


    INSERT INTO Kazna(
        datum,
        status_kazna_id,
        iznos_za_plakanje
    )
    VALUES(
        CURRENT_DATE,
        v_status_kazna_id,
        v_iznos
    )
    RETURNING kazna_id INTO v_kazna_id;


    INSERT INTO Prekrsok(
        opis,
        vreme,
        status_prekrsok_id,
        datum,
        detektirana_brzina,
        tip_prekrsok_id,
        kamera_id,
        kazna_id
    )
    VALUES(
        p_opis,
        p_vreme,
        v_status_prekrsok_id,
        p_datum,
        p_detektirana_brzina,
        p_tip_prekrsok_id,
        p_kamera_id,
        v_kazna_id
    )
    RETURNING prekrsok_id INTO v_prekrsok_id;


    INSERT INTO Prekrsok_Storitel(
        prekrsok_id,
        storitel_embg
    )
    VALUES(
        v_prekrsok_id,
        p_storitel_embg
    );
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION trg_fn_auto_rok_kazna()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.datum IS NULL THEN
        NEW.datum := CURRENT_DATE;
    END IF;


    IF NEW.rok_na_plakanje IS NULL THEN
        NEW.rok_na_plakanje := NEW.datum + INTERVAL '30 days';
    ELSIF NEW.rok_na_plakanje < NEW.datum THEN
        RAISE EXCEPTION 'Rokot za plakanje ne moze da bide pred datumot na kaznata';
    ELSE
        RETURN NEW;
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_auto_rok_kazna ON Kazna;

CREATE TRIGGER trg_auto_rok_kazna
BEFORE INSERT ON Kazna
FOR EACH ROW
EXECUTE FUNCTION trg_fn_auto_rok_kazna();




CREATE OR REPLACE FUNCTION trg_fn_kazna_platena()
RETURNS TRIGGER
AS $$
DECLARE
    v_status_platena_id INTEGER;
    v_status_momentalen VARCHAR;
BEGIN
    SELECT sk.ime
    INTO v_status_momentalen
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = NEW.kazna_id;


    IF v_status_momentalen IS NULL THEN
        RAISE EXCEPTION 'Kaznata ne postoi';
    ELSIF LOWER(v_status_momentalen) = 'ponishtena' THEN
        RAISE EXCEPTION 'Ponisthena kazna ne moze da se oznaci kako platena';
    ELSE
        SELECT status_kazna_id
        INTO v_status_platena_id
        FROM StatusKazna
        WHERE LOWER(ime) = 'platena';

        IF v_status_platena_id IS NULL THEN
            RAISE EXCEPTION 'Vo StatusKazna ne postoi status platena';
        ELSE
            UPDATE Kazna
            SET status_kazna_id = v_status_platena_id
            WHERE kazna_id = NEW.kazna_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_kazna_platena ON Plakanje;

CREATE TRIGGER trg_kazna_platena
AFTER INSERT ON Plakanje
FOR EACH ROW
EXECUTE FUNCTION trg_fn_kazna_platena();





CREATE OR REPLACE FUNCTION trg_fn_validacija_zalba()
RETURNS TRIGGER
AS $$
DECLARE
    v_status_prekrsok VARCHAR;
BEGIN
    IF NEW.sodrzina IS NULL OR LENGTH(TRIM(NEW.sodrzina)) = 0 THEN
        RAISE EXCEPTION 'Zalbata mora da ima sodrzina';
    END IF;


    IF NEW.datum_na_podnesuvanje IS NULL THEN
        NEW.datum_na_podnesuvanje := CURRENT_DATE;
    END IF;


    SELECT sp.ime
    INTO v_status_prekrsok
    FROM Prekrsok p
    JOIN StatusPrekrsok sp ON sp.status_prekrsok_id = p.status_prekrsok_id
    WHERE p.prekrsok_id = NEW.prekrsok_id;


    IF v_status_prekrsok IS NULL THEN
        RAISE EXCEPTION 'Ne postoi prekrsok za koj se podnesuva zalbata';
    ELSIF LOWER(v_status_prekrsok) = 'ponishten' THEN
        RAISE EXCEPTION 'Ne moze da se podnese zalba za ponishten prekrsok';
    ELSE
        RETURN NEW;
    END IF;
END;
$$
LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_validacija_zalba ON Zalba;

CREATE TRIGGER trg_validacija_zalba
BEFORE INSERT ON Zalba
FOR EACH ROW
EXECUTE FUNCTION trg_fn_validacija_zalba();