DatabaseProgramming: programming.sql

---Programming

--Trigger1: avtomatska presmetka na vkupnata suma na site kazni za eden zapisnik vo Uplata

CREATE OR REPLACE FUNCTION azuriraj_iznos_uplata()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    v_id_zapisnik int;
    v_nov_iznos   numeric(10,2);
BEGIN
 
    IF TG_OP = 'DELETE' THEN
        v_id_zapisnik := OLD.id_na_zapisnik;
    ELSE
        v_id_zapisnik := NEW.id_na_zapisnik;
    END IF;
 
 
    SELECT COALESCE(SUM(k.iznos_kazna), 0)
    INTO v_nov_iznos
    FROM Stavka_Zapisnik sz
    JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    JOIN Kazna    k ON p.id_kazna        = k.id_kazna
    WHERE sz.id_na_zapisnik = v_id_zapisnik;
 
 
    UPDATE Uplata
    SET iznos = v_nov_iznos
    WHERE id_zapisnik = v_id_zapisnik
      AND status = 'Neplateno';
 
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;
 


CREATE OR REPLACE TRIGGER trigger_azuriraj_iznos_uplata
    AFTER INSERT OR UPDATE OR DELETE ON Stavka_Zapisnik
    FOR EACH ROW
    EXECUTE FUNCTION azuriraj_iznos_uplata();




--Trigger 2: Proverka na podatoci i zastita na dupli aktivni stanici vo Raboti_vo

 
CREATE OR REPLACE FUNCTION proverka_raboti_vo_integritet()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = NEW.EMBG_P) THEN
        RAISE EXCEPTION 'Policaecot so EMBG % ne postoi vo sistemot!', NEW.EMBG_P;
    END IF;
 
    IF NEW.datum_od IS NULL THEN
        RAISE EXCEPTION 'Pocetokot na rabotniot odnos (datum_od) mora da bide definiran!';
    END IF;
 
    IF NEW.datum_do IS NULL THEN
        IF EXISTS (
            SELECT 1 FROM Raboti_vo 
            WHERE EMBG_P = NEW.EMBG_P AND datum_do IS NULL AND id_stanica <> NEW.id_stanica
        ) THEN
            RAISE EXCEPTION 'Policaecot so EMBG % vekje ima aktiven angazhman vo druga stanica!', NEW.EMBG_P;
        END IF;
    END IF;
 
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trigger_proverka_raboti_vo
    BEFORE INSERT OR UPDATE ON Raboti_vo
    FOR EACH ROW
    EXECUTE FUNCTION proverka_raboti_vo_integritet();

--Trigger 3: Proverka na EMBG,broj na shasija i format na tablica vo Registracija

CREATE OR REPLACE FUNCTION proverka_registracija_integritet()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    v_tablica text;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Sopstvenost WHERE id_sopstvenost = NEW.id_sopstvenost) THEN
        RAISE EXCEPTION 'Sopstvenosta so id % ne postoi vo sistemot!', NEW.id_sopstvenost;
    END IF;
 
    IF NEW.id_tablica IS NOT NULL THEN
        SELECT region || '-' || broj || '-' || kod
        INTO v_tablica
        FROM Registerska_tablica
        WHERE id_tablica = NEW.id_tablica;
 
        IF v_tablica IS NULL THEN
            RAISE EXCEPTION 'Registerskata tablica so id % ne postoi!', NEW.id_tablica;
        END IF;
 
        IF NOT (UPPER(v_tablica) ~ '^[A-Z]{2,3}-[0-9]{3,4}-[A-Z]{2}$') THEN
            RAISE EXCEPTION 'Registarskata tablica  "%" ne e vo validen format! Ocekuvan format: ZZ-0000-ZZ', v_tablica;
        END IF;
    END IF;
 
    RETURN NEW;
END;
$$;


-----------------------------------------------------------------------------------------------

--Procedure 1:Kreiranje na nov zapisnik i insert na soodvetni podatoci vo Stavka_Zapisnik i Uplata 

CREATE OR REPLACE PROCEDURE kreiraj_zapisnik_so_prekrsok(
    p_embg_prekrsuvac char(13),
    p_broj_sasija     varchar(17),
    p_embg_policaec   char(13),
    p_lokacija        varchar(100),
    p_id_slucaj       int,
    p_id_prekrsok     int,
    p_vreme           time    DEFAULT CURRENT_TIME,
    p_datum           date    DEFAULT CURRENT_DATE,
    p_potpis          boolean DEFAULT false
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_id_zapisnik int;
BEGIN
 
    IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = p_embg_prekrsuvac) THEN
        RAISE EXCEPTION 'Gragjaninot so EMBG % ne postoi!', p_embg_prekrsuvac;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = p_embg_policaec) THEN
        RAISE EXCEPTION 'Policaecot so EMBG % ne postoi!', p_embg_policaec;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = p_broj_sasija) THEN
        RAISE EXCEPTION 'Voziloto so broj na sasija % ne postoi!', p_broj_sasija;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
        RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Slucaj WHERE id_slucaj = p_id_slucaj) THEN
        RAISE EXCEPTION 'Slucajot so id % ne postoi!', p_id_slucaj;
    END IF;
 
 
    INSERT INTO Zapisnik (
        vreme, datum, lokacija, Potpis,
        id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec
    )
    VALUES (
        p_vreme, p_datum, p_lokacija, p_potpis,
        p_id_slucaj, p_embg_prekrsuvac, p_broj_sasija, p_embg_policaec
    )
    RETURNING id_na_zapisnik INTO v_id_zapisnik;
 
 
    INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik)
    VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik);
 
 
    INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    VALUES (1, v_id_zapisnik, p_id_prekrsok);
 
    RAISE NOTICE 'Kreiran zapisnik so id % so prv prekrsok %.', v_id_zapisnik, p_id_prekrsok;
END;
$$;

-----------------------------------------------------------------------------------------------

--Procedure 2:Kreiranje na nova stavka na veke postoecki zapisnik i proverki vo validnost na podatoci

CREATE OR REPLACE PROCEDURE dodadi_stavka_zapisnik(
    p_id_zapisnik int,
    p_id_prekrsok int
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_reden_broj     int;
    v_status_uplata  varchar(30);
BEGIN
 
    IF NOT EXISTS (SELECT 1 FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik) THEN
        RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
        RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    END IF;
 
 
    SELECT status INTO v_status_uplata
    FROM Uplata
    WHERE id_zapisnik = p_id_zapisnik;
 
    IF v_status_uplata = 'Plateno' THEN
        RAISE EXCEPTION 'Zapisnik % e vekje platen, ne moze da se dodavaat novi stavki!', p_id_zapisnik;
    END IF;
 
 
    SELECT COALESCE(MAX(reden_broj), 0) + 1 INTO v_reden_broj
    FROM Stavka_Zapisnik
    WHERE id_na_zapisnik = p_id_zapisnik;
 
    INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    VALUES (v_reden_broj, p_id_zapisnik, p_id_prekrsok);
 
    RAISE NOTICE 'Dodadena stavka so reden_broj % vo zapisnik %.', v_reden_broj, p_id_zapisnik;
END;
$$;

-----------------------------------------------------------------------------------------------
--Procedure 3: Naplata na kazna vrzana za daden zapisnik, go menjava statusot na uplatata od 'Neplateno' vo 'Plateno'
 
CREATE OR REPLACE PROCEDURE plati_kazna(
    p_id_zapisnik    int,
    p_nacin_plakanje varchar(30)
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_status varchar(30);
    v_iznos  numeric(10,2);
BEGIN
 
    SELECT status, iznos INTO v_status, v_iznos
    FROM Uplata
    WHERE id_zapisnik = p_id_zapisnik;
 
    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    END IF;
 
    IF v_status = 'Plateno' THEN
        RAISE EXCEPTION 'Kaznata za zapisnik % e vekje platena!', p_id_zapisnik;
    END IF;
 
    IF v_iznos <= 0 THEN
        RAISE EXCEPTION 'Zapisnikot % nema stavki/kazni za naplata!', p_id_zapisnik;
    END IF;
 
    IF p_nacin_plakanje NOT IN ('E-bankarstvo', 'Platezna karticka', 'Gotovo/Uplatnica') THEN
        RAISE EXCEPTION 'Nepoznat nacin na plakanje: %. Dozvoleni: E-bankarstvo, Platezna karticka, Gotovo/Uplatnica.', p_nacin_plakanje;
    END IF;
 
 
    UPDATE Uplata
    SET status         = 'Plateno',
        datum_uplata   = CURRENT_DATE,
        nacin_plakanje = p_nacin_plakanje
    WHERE id_zapisnik = p_id_zapisnik;
 
    RAISE NOTICE 'Uspesno naplatena kazna od % za zapisnik % (%).', v_iznos, p_id_zapisnik, p_nacin_plakanje;
END;
$$;



--Procedure 4: Dodavanje na nova kategorija na postoecka vozacka 

REATE OR REPLACE PROCEDURE dodadi_kategorija_na_vozacka(
    p_broj_dozvola    varchar(30),
    p_id_kategorija   int,
    p_datum_polaganje date DEFAULT CURRENT_DATE
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_datum_vaznost_do date;
    v_datum_izdavanje  date;
    v_kod_kategorija   varchar(3);
BEGIN
 
    SELECT datum_vaznost_do, datum_izdavanje
    INTO v_datum_vaznost_do, v_datum_izdavanje
    FROM Vozacka_dozvola
    WHERE broj_dozvola = p_broj_dozvola;
 
    IF v_datum_vaznost_do IS NULL THEN
        RAISE EXCEPTION 'Vozackata dozvola so broj % ne postoi!', p_broj_dozvola;
    END IF;
 
 
    IF v_datum_vaznost_do < CURRENT_DATE THEN
        RAISE EXCEPTION 'Vozackata dozvola % e istechena (vaznost do %)! Mora prvo da se obnovi.',
            p_broj_dozvola, v_datum_vaznost_do;
    END IF;
 
 
    SELECT kod INTO v_kod_kategorija
    FROM Kategorija
    WHERE id_kategorija = p_id_kategorija;
 
    IF v_kod_kategorija IS NULL THEN
        RAISE EXCEPTION 'Kategorijata so id % ne postoi!', p_id_kategorija;
    END IF;
 
 
    IF EXISTS (
        SELECT 1 FROM Kategorija_Vozacka_dozvola
        WHERE broj_dozvola = p_broj_dozvola
          AND id_kategorija = p_id_kategorija
    ) THEN
        RAISE EXCEPTION 'Kategorijata % vekje e dodadena na vozackata %!',
            v_kod_kategorija, p_broj_dozvola;
    END IF;
 
 
    IF p_datum_polaganje > CURRENT_DATE THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide vo idnina!', p_datum_polaganje;
    END IF;
 
    IF p_datum_polaganje < v_datum_izdavanje THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide pred izdavanjeto na vozackata (%).',
            p_datum_polaganje, v_datum_izdavanje;
    END IF;
 
 
    INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
    VALUES (p_id_kategorija, p_broj_dozvola, p_datum_polaganje);
 
    RAISE NOTICE 'Uspesno dodadena kategorija % na vozackata % (polagano na %).',
        v_kod_kategorija, p_broj_dozvola, p_datum_polaganje;
END;
$$;
 

--Procedure 5: Zatvoranje na zapisnik po izvrsena uplata 
 
CREATE OR REPLACE PROCEDURE zatvori_zapisnik_po_uplata(
    p_id_zapisnik int
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_status_uplata    varchar(30);
    v_status_zapisnik  varchar(20);
BEGIN
 
    SELECT status_zapisnik INTO v_status_zapisnik
    FROM Zapisnik
    WHERE id_na_zapisnik = p_id_zapisnik;
 
    IF v_status_zapisnik IS NULL THEN
        RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
    END IF;
 
    IF v_status_zapisnik = 'Zatvoren' THEN
        RAISE EXCEPTION 'Zapisnikot % e vekje zatvoren!', p_id_zapisnik;
    END IF;
 
 
    SELECT status INTO v_status_uplata
    FROM Uplata
    WHERE id_zapisnik = p_id_zapisnik;
 
    IF v_status_uplata IS NULL THEN
        RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    END IF;
 
    IF v_status_uplata <> 'Plateno' THEN
        RAISE EXCEPTION 'Zapisnikot % ne moze da se zatvori bidejki uplatata e so status "%" (mora da bide Plateno)!',
            p_id_zapisnik, v_status_uplata;
    END IF;
 
 
    UPDATE Zapisnik
    SET status_zapisnik = 'Zatvoren'
    WHERE id_na_zapisnik = p_id_zapisnik;
 
    RAISE NOTICE 'Zapisnik % e zatvoren po izvrshena uplata.', p_id_zapisnik;
END;
$$;



--test za proceduri, trigeri i funckija
--proverka za procedura 1: kreiranje zapisnik
CALL kreiraj_zapisnik_so_prekrsok(
    '1508004480145',        
    'CE8C5F1D5AA4C0871',    
    '1609966470237',        
    'Bulevar Partizanski Odredi',
    1,                      
    1                       
);

select * 
from  zapisnik  vsz 
where vsz.embg_prekrsuvach='1508004480145';

--proverka za procedura 2: dodavannje stavka na zapisnikot
CALL dodadi_stavka_zapisnik(30000005, 3);

select * from stavka_zapisnik sz 
where sz.id_na_zapisnik =30000005;

--proverka za procedura 3: plakanje na kazna
CALL plati_kazna(30000005, 'E-bankarstvo');

select * from uplata u   
where u.id_zapisnik  =30000005;


--proverka za procedura 4:Dodavanje nova kategorija na postoechka vozacka dozvola
--    (npr. vozacot polozhi kategorija C - id_kategorija = 7)
CALL dodadi_kategorija_na_vozacka('L8207294', 7);
--proverka za procedura 5: zatvoranje na zapisnik po plakanje
CALL zatvori_zapisnik_po_uplata(30000005)
 
-- so naveduvanje na datumot na polaganje:
CALL dodadi_kategorija_na_vozacka('L8207294', 9, '2025-11-15');


select * from kategorija_vozacka_dozvola kvd 
where kvd.broj_dozvola ='L8207294'





-----------------Funkcija za scheduled update na kazni so pominat rok za 50 %


CREATE OR REPLACE FUNCTION azuriraj_kazna_plakanja()
    RETURNS text
    LANGUAGE plpgsql
AS $$
BEGIN
 
    UPDATE Uplata u
    SET iznos = u.iznos + (u.iznos * 0.50),
        kazna_zgolemena = true
    FROM Zapisnik z
    WHERE u.id_zapisnik = z.id_na_zapisnik
      AND u.status = 'Neplateno'
      AND u.kazna_zgolemena = false
      AND z.datum < CURRENT_DATE - INTERVAL '8 days';
 
 
    UPDATE Uplata u
    SET status = 'Sudska_postapka'
    FROM Zapisnik z
    WHERE u.id_zapisnik = z.id_na_zapisnik
      AND u.status = 'Neplateno'
      AND z.datum < CURRENT_DATE - INTERVAL '2 months'; 
 
 
    RETURN 'Постапката за автоматско ажурирање на неплатените казни е успешно извршена.';
END;
$$;



-------------test za funkcija 2----


SELECT azuriraj_kazna_plakanja();
select * from Uplata;