= Функции, Процедури и Тригери =

Во оваа фаза беа имплементирани три функции, три процедури и три тригери кои ја поддржуваат основната бизнис логика на системот IDontKnow. Тие автоматизираат процеси како пресметка на кредитно-пондериран просек и отворен долг, проверка на слободни места, регистрирање наплата со автоматска алокација кон обврски, упис на студент на курс, сериско генерирање обврски за школарина, како и валидација на оценки, ажурирање на отворената сума и контрола на капацитетот на курсевите преку тригери.

Сите рутини се креирани во базата `advdb_202526l_prj_idontknow` (PostgreSQL 17.9).

[attachment:13_routines.sql 13_routines.sql]


== Функции ==

Функциите се користат за пресметување и враќање на вредности кои се потребни во повеќе делови од системот и може да се вградат директно во прашалник, поглед или друга рутина.

=== 1. fn_prosek_student ===

{{{
CREATE OR REPLACE FUNCTION fn_prosek_student(
    p_student_id INTEGER,
    p_sistem_ocenuvanje_id INTEGER DEFAULT NULL
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT ROUND(SUM(sov.ocena_vrednost::numeric * ak.krediti)
                     / NULLIF(SUM(ak.krediti), 0), 2)
    FROM sm_student_ocena_zapis soz
        JOIN sm_student_upisan_kurs suk ON soz.student_upisan_kurs_id = suk.id
        JOIN sm_student_semestar_upis ssu ON suk.student_semestar_upis_id = ssu.id
        JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
        JOIN sm_kurs_realizacija kr ON suk.kurs_realizacija_id = kr.id
        JOIN sm_akreditacija ak ON kr.akreditacija_id = ak.id
        JOIN sm_sistem_ocenuvanje_vrednost sov ON soz.sistem_ocenuvanje_vrednost_id = sov.id
    WHERE ss.student_id = p_student_id
      AND sov.polozitelna
      AND (p_sistem_ocenuvanje_id IS NULL OR sov.sistem_ocenuvanje_id = p_sistem_ocenuvanje_id);
$$;
}}}

Функцијата `fn_prosek_student` го пресметува кредитно-пондерираниот просек од положените оценки на даден студент. Како влезни параметри прима `p_student_id` и опционално `p_sistem_ocenuvanje_id`, со кој пресметката може да се ограничи на еден систем на оценување. Поминувајќи низ синџирот на табели од `sm_student_ocena_zapis` до `sm_akreditacija`, ја дели сумата на (вредност на оценка × кредити) со вкупните кредити и резултатот го заокружува на две децимали.

Реализирана е како функција бидејќи враќа единечна вредност што директно се вградува во прашалници — на пример листа на студенти со просек над праг, услов за стипендија или уверение (`SELECT indeks, fn_prosek_student(s.id) FROM sm_studenti s`), што не е возможно со процедура. За масовни извештаи постои погледот `vw_studentski_oceni`; функцијата е за поединечна, параметризирана употреба во произволен контекст.

=== 2. fn_otvoren_dolg ===

{{{
CREATE OR REPLACE FUNCTION fn_otvoren_dolg(
    p_user_id INTEGER,
    p_vid_valuta_id INTEGER
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(fo.otvorena_suma), 0)
    FROM fin_finansiska_obvrska fo
    WHERE fo.user_id = p_user_id
      AND fo.vid_valuta_id = p_vid_valuta_id
      AND fo.otvorena_suma > 0
      AND fo.status_finansiska_obvrska_id IN
          (SELECT id FROM fin_status_finansiska_obvrska WHERE ime IN ('Izdadena', 'Delumno platena'));
$$;
}}}

Функцијата `fn_otvoren_dolg` ја враќа вкупната отворена сума на корисник во дадена валута. Прима `p_user_id` и `p_vid_valuta_id`, и собира `otvorena_suma` само од обврските во статус `Izdadena` или `Delumno platena`, враќајќи 0 ако нема такви.

Истата проверка ја користат повеќе места — блокада при упис, опомени и работа на шалтер — па е издвоена како функција со повратна вредност што може да се вгради дури и во услов (`WHERE fn_otvoren_dolg(...) > 0`). Како процедура би морала да враќа преку OUT-параметар и не би можела да се користи на тој начин.

=== 3. fn_slobodni_mesta ===

{{{
CREATE OR REPLACE FUNCTION fn_slobodni_mesta(p_kurs_realizacija_id INTEGER)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
    SELECT kr.kapacitet_studenti
               - (SELECT COUNT(*)::int
                  FROM sm_student_upisan_kurs suk
                  WHERE suk.kurs_realizacija_id = kr.id
                    AND suk.status_slushanje = 'UPISAN')
    FROM sm_kurs_realizacija kr
    WHERE kr.id = p_kurs_realizacija_id;
$$;
}}}

Функцијата `fn_slobodni_mesta` го враќа бројот на слободни места на дадена реализација на курс — `kapacitet_studenti` намалено за бројот на активни (`UPISAN`) уписи. Доколку капацитетот е `NULL`, тоа значи неограничен број места.

Се прикажува при пребарување курсеви и се користи внатре во процедурата `pr_zapisi_student_na_kurs`, па затоа е функција што враќа вредност. Контролата на капацитетот ја спроведува тригерот `trg_kapacitet_kurs`; функцијата само информира, додека тригерот е тој што го гарантира ограничувањето.

== Процедури ==

Процедурите се користат за извршување деловни операции врз базата кои опфаќаат повеќе чекори и табели, се повикуваат експлицитно со `CALL` и единствени смеат да користат `COMMIT` / `ROLLBACK` внатре.

=== 1. pr_registriraj_naplata ===

{{{
CREATE OR REPLACE PROCEDURE pr_registriraj_naplata(
    p_user_id INTEGER,
    p_suma NUMERIC,
    p_vid_valuta_id INTEGER,
    p_organizaciona_edinica_id INTEGER,
    p_referenca VARCHAR DEFAULT NULL,
    p_korisnik_naplata_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_naplata_id INTEGER;
    v_smetka_id  INTEGER;
    v_ostatok    NUMERIC := p_suma;
    v_obvrska    RECORD;
    v_alocirano  NUMERIC;
BEGIN
    IF p_suma <= 0 THEN
        RAISE EXCEPTION 'Сумата мора да биде позитивна (добиено: %)', p_suma;
    END IF;

    -- активна трансакциска сметка на организационата единица (приоритет: иста валута)
    SELECT oes.transakciska_smetka_id
    INTO v_smetka_id
    FROM or_organizaciona_edinica_smetki oes
        JOIN fin_transakciski_smetki ts ON oes.transakciska_smetka_id = ts.id
    WHERE oes.organizaciona_edinica_id = p_organizaciona_edinica_id
      AND oes.aktivna
      AND ts.aktivna
    ORDER BY (ts.vid_valuta_id = p_vid_valuta_id) DESC, ts.id
    LIMIT 1;

    IF v_smetka_id IS NULL THEN
        RAISE EXCEPTION 'Организационата единица % нема активна трансакциска сметка',
            p_organizaciona_edinica_id;
    END IF;

    INSERT INTO fin_naplati (user_id, korisnik_naplata_id, transakciska_smetka_id,
                             organizaciona_edinica_id, naplatena_suma, vid_valuta_id,
                             status_transakcija_id, nadvoresna_referenca,
                             requested_at, confirmed_at, data)
    VALUES (p_user_id, COALESCE(p_korisnik_naplata_id, p_user_id), v_smetka_id,
            p_organizaciona_edinica_id, p_suma, p_vid_valuta_id,
            (SELECT id FROM fin_status_transakcija WHERE ime_status = 'ACCEPTED'),
            p_referenca, now(), now(), now())
    RETURNING id INTO v_naplata_id;

    FOR v_obvrska IN
        SELECT fo.id, fo.otvorena_suma
        FROM fin_finansiska_obvrska fo
        WHERE fo.user_id = p_user_id
          AND fo.vid_valuta_id = p_vid_valuta_id
          AND fo.otvorena_suma > 0
        ORDER BY fo.datum_dospevanje NULLS LAST, fo.id
        FOR UPDATE
    LOOP
        EXIT WHEN v_ostatok <= 0;
        v_alocirano := LEAST(v_ostatok, v_obvrska.otvorena_suma);
        INSERT INTO fin_naplata_alokacija (naplata_id, finansiska_obvrska_id,
                                           alocirana_suma, created_at)
        VALUES (v_naplata_id, v_obvrska.id, v_alocirano, now());
        v_ostatok := v_ostatok - v_alocirano;
    END LOOP;

    IF v_ostatok > 0 THEN
        RAISE NOTICE 'Наплата %: % останува неалоцирано (преплата).', v_naplata_id, v_ostatok;
    END IF;
END;
$$;
}}}

Процедурата `pr_registriraj_naplata` регистрира наплата и автоматски ја алоцира кон отворените обврски на корисникот. Откако ќе валидира дека сумата е позитивна и ќе ја најде активната трансакциска сметка на организационата единица (со приоритет на иста валута), внесува запис во `fin_naplati`, па во циклус ги покрива обврските по редослед на најстаро доспевање, внесувајќи редови во `fin_naplata_alokacija`. Ако остане непокриен дел (преплата), тој останува неалоциран и се пријавува со `NOTICE`.

Ова е деловна операција составена од еден INSERT во `fin_naplati` и повеќе INSERT-и во `fin_naplata_alokacija`, која се извршува атомски и не враќа вредност за прашалник — затоа е процедура, а не функција. Намерно не ја менува `otvorena_suma`: тоа го прави тригерот `trg_azuriraj_otvorena_suma`, така што изведената вредност останува конзистентна без оглед дали алокацијата ја внела оваа процедура, друга апликација или рачен SQL. Процедурата одлучува како се распределува наплатата, а тригерот гарантира книговодствена конзистентност.

=== 2. pr_zapisi_student_na_kurs ===

{{{
CREATE OR REPLACE PROCEDURE pr_zapisi_student_na_kurs(
    p_student_id INTEGER,
    p_kurs_realizacija_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ssu_id   INTEGER;
    v_user_id  INTEGER;
    v_dolg     NUMERIC;
    v_valuta   INTEGER;
    v_slobodni INTEGER;
BEGIN
    SELECT ssu.id
    INTO v_ssu_id
    FROM sm_student_semestar_upis ssu
        JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
        JOIN sm_kurs_realizacija kr ON kr.id = p_kurs_realizacija_id
    WHERE ss.student_id = p_student_id
      AND ssu.akademski_semestar_id = kr.akademski_semestar_id
    ORDER BY ssu.datum_upis DESC
    LIMIT 1;

    IF v_ssu_id IS NULL THEN
        RAISE EXCEPTION 'Студентот % нема упис во семестарот на курсот %',
            p_student_id, p_kurs_realizacija_id;
    END IF;

    IF EXISTS (SELECT 1
               FROM sm_student_upisan_kurs
               WHERE student_semestar_upis_id = v_ssu_id
                 AND kurs_realizacija_id = p_kurs_realizacija_id) THEN
        RAISE EXCEPTION 'Студентот % е веќе запишан на курсот %',
            p_student_id, p_kurs_realizacija_id;
    END IF;

    SELECT s.user_id
    INTO v_user_id
    FROM sm_studenti s
    WHERE s.id = p_student_id;

    FOR v_valuta IN
        SELECT DISTINCT vid_valuta_id
        FROM fin_finansiska_obvrska
        WHERE user_id = v_user_id
          AND otvorena_suma > 0
    LOOP
        v_dolg := fn_otvoren_dolg(v_user_id, v_valuta);
        IF v_dolg > 0 THEN
            RAISE EXCEPTION 'Студентот % има отворен долг % (валута %) — уписот е блокиран',
                p_student_id, v_dolg, v_valuta;
        END IF;
    END LOOP;

    v_slobodni := fn_slobodni_mesta(p_kurs_realizacija_id);
    IF v_slobodni IS NOT NULL AND v_slobodni <= 0 THEN
        RAISE EXCEPTION 'Нема слободни места на курсот %', p_kurs_realizacija_id;
    END IF;

    INSERT INTO sm_student_upisan_kurs (student_semestar_upis_id, kurs_realizacija_id, status_slushanje)
    VALUES (v_ssu_id, p_kurs_realizacija_id, 'UPISAN');
END;
$$;
}}}

Процедурата `pr_zapisi_student_na_kurs` запишува студент на курс. Го наоѓа семестралниот упис на студентот за семестарот на курсот, одбива дупликат упис, одбива ако студентот има отворен долг (преку `fn_otvoren_dolg`) и одбива ако нема слободни места (преку `fn_slobodni_mesta`), а потоа внесува запис во `sm_student_upisan_kurs` со статус `UPISAN`.

Повеќечекорната валидациона секвенца со завршно запишување, повикана експлицитно од акција на корисникот, ја прави процедура; таа воедно демонстрира компонирање бидејќи ги повикува функциите `fn_otvoren_dolg` и `fn_slobodni_mesta`. Проверката на долг е политика на уписот, а не интегритет на податоците (администратор може по исклучок да запише студент со долг преку директен INSERT), па затоа не е тригер; капацитетот, напротив, е физичко ограничување и е заштитен со тригер.

=== 3. pr_generiraj_obvrski_za_semestar ===

{{{
CREATE OR REPLACE PROCEDURE pr_generiraj_obvrski_za_semestar(p_akademski_semestar_id INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    v_cenovnik RECORD;
    v_oe       INTEGER;
    v_opis     VARCHAR;
    v_student  RECORD;
    v_brojac   INTEGER := 0;
BEGIN
    SELECT asem.organizaciona_edinica_id,
           c.id AS cenovnik_id,
           c.iznos,
           c.vid_valuta_id,
           asem.tip_naplata_organizaciona_edinica_id
    INTO v_cenovnik
    FROM sm_akademski_semestar asem
        JOIN fin_tip_naplati_cenovnik c
            ON c.tip_naplata_organizaciona_edinica_id = asem.tip_naplata_organizaciona_edinica_id
                AND c.aktivna
    WHERE asem.id = p_akademski_semestar_id
    ORDER BY c.prioritet
    LIMIT 1;

    IF v_cenovnik IS NULL THEN
        RAISE EXCEPTION 'Нема активен ценовник за семестар %', p_akademski_semestar_id;
    END IF;

    v_oe := v_cenovnik.organizaciona_edinica_id;
    v_opis := 'Школарина семестар ' || p_akademski_semestar_id;

    FOR v_student IN
        SELECT DISTINCT s.user_id
        FROM sm_student_semestar_upis ssu
            JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
            JOIN sm_studenti s ON ss.student_id = s.id
        WHERE ssu.akademski_semestar_id = p_akademski_semestar_id
          AND NOT EXISTS (SELECT 1
                          FROM fin_finansiska_obvrska fo
                          WHERE fo.user_id = s.user_id
                            AND fo.opis = 'Школарина семестар ' || p_akademski_semestar_id)
    LOOP
        INSERT INTO fin_finansiska_obvrska (user_id, organizaciona_edinica_id,
                                            tip_naplata_organizaciona_edinica_id,
                                            tip_naplati_cenovnik_id,
                                            status_finansiska_obvrska_id,
                                            datum_kreiranje, datum_dospevanje,
                                            originalna_suma, otvorena_suma,
                                            vid_valuta_id, opis)
        VALUES (v_student.user_id, v_oe, v_cenovnik.tip_naplata_organizaciona_edinica_id,
                v_cenovnik.cenovnik_id,
                (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Izdadena'),
                now(), CURRENT_DATE + 30, v_cenovnik.iznos, v_cenovnik.iznos,
                v_cenovnik.vid_valuta_id, v_opis);
        v_brojac := v_brojac + 1;
        IF v_brojac % 1000 = 0 THEN
            COMMIT; -- дозволено само во процедура
        END IF;
    END LOOP;

    RAISE NOTICE 'Генерирани % обврски за семестар %', v_brojac, p_akademski_semestar_id;
END;
$$;
}}}

Процедурата `pr_generiraj_obvrski_za_semestar` сериски генерира обврски за школарина за сите студенти запишани во даден академски семестар, според активниот ценовник на семестарот. Прескокнува студенти кои веќе имаат таква обврска (идемпотентно според `opis`), а на секои 1000 внесени редови прави `COMMIT`.

Ова е единствената рутина што мора да биде процедура: периодичниот `COMMIT` врз batch од десетици илјади редови е дозволен само во процедура — функциите и тригерите во PostgreSQL не смеат да завршуваат трансакции.

== Тригери ==

Тригерите се користат за автоматско извршување логика при внесување или промена на податоци во табелите. Тие се единствениот механизам кој гарантира дека правилото важи без разлика кој и како пишува во табелата — апликација, процедура или рачен INSERT.

=== 1. trg_validna_ocena ===

{{{
CREATE OR REPLACE FUNCTION trgf_validna_ocena()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    SELECT status_slushanje
    INTO v_status
    FROM sm_student_upisan_kurs
    WHERE id = NEW.student_upisan_kurs_id;

    IF v_status IS DISTINCT FROM 'UPISAN' THEN
        RAISE EXCEPTION 'Оценка може да се внесе само за активен упис (статус: %)', v_status;
    END IF;

    IF NEW.osvoeni_poeni IS NOT NULL AND (NEW.osvoeni_poeni < 0 OR NEW.osvoeni_poeni > 100) THEN
        RAISE EXCEPTION 'Освоени поени мора да бидат 0–100 (добиено: %)', NEW.osvoeni_poeni;
    END IF;

    IF NEW.datum_zapis > now() THEN
        RAISE EXCEPTION 'Датумот на запис не смее да биде во иднина';
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_validna_ocena ON sm_student_ocena_zapis;
CREATE TRIGGER trg_validna_ocena
    BEFORE INSERT ON sm_student_ocena_zapis
    FOR EACH ROW
    EXECUTE FUNCTION trgf_validna_ocena();
}}}

Тригерот `trg_validna_ocena` се активира пред внесување оценка во `sm_student_ocena_zapis`. Тој одбива оценка ако уписот не е во статус `UPISAN`, ако освоените поени се надвор од опсегот 0–100, или ако датумот на запис е во иднина.

Реализиран е како тригер бидејќи станува збор за интегритет на податоците — невалидна оценка е невалидна без оглед кој ја внесува (апликација, import или рачен INSERT), што процедура не би го гарантирала. CHECK-ограничување не е доволно бидејќи правилото за статусот бара поглед во друга табела (`sm_student_upisan_kurs`).

=== 2. trg_azuriraj_otvorena_suma ===

{{{
CREATE OR REPLACE FUNCTION trgf_azuriraj_otvorena_suma()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_nova NUMERIC;
BEGIN
    SELECT otvorena_suma - NEW.alocirana_suma
    INTO v_nova
    FROM fin_finansiska_obvrska
    WHERE id = NEW.finansiska_obvrska_id
    FOR UPDATE;

    IF v_nova < 0 THEN
        RAISE EXCEPTION 'Алокацијата (%) ја надминува отворената сума на обврската %',
            NEW.alocirana_suma, NEW.finansiska_obvrska_id;
    END IF;

    UPDATE fin_finansiska_obvrska
    SET otvorena_suma                = v_nova,
        status_finansiska_obvrska_id = CASE
                                           WHEN v_nova = 0 THEN
                                               (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Platena')
                                           ELSE
                                               (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Delumno platena')
            END
    WHERE id = NEW.finansiska_obvrska_id;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_azuriraj_otvorena_suma ON fin_naplata_alokacija;
CREATE TRIGGER trg_azuriraj_otvorena_suma
    AFTER INSERT ON fin_naplata_alokacija
    FOR EACH ROW
    EXECUTE FUNCTION trgf_azuriraj_otvorena_suma();
}}}

Тригерот `trg_azuriraj_otvorena_suma` се активира по внесување алокација во `fin_naplata_alokacija`. Тој ја намалува `otvorena_suma` на соодветната обврска за алоцираната сума, одбива алокација што би ја однела сумата под нула, и автоматски го поставува статусот на `Platena` (кога ќе достигне нула) или `Delumno platena`.

`otvorena_suma` е изведена вредност што мора да се ажурира секогаш кога ќе настане алокација, па е врзана за настанот преку тригер, а не за повикувачот. Кога би била дел од процедурата `pr_registriraj_naplata`, секој иден писател на алокации (на пр. модул за поврат или миграција) би морал да не заборави да ја ажурира. Тригерот е `AFTER INSERT` затоа што редот прво мора да постои како валиден, а ажурирањето на другата табела е последица.

=== 3. trg_kapacitet_kurs ===

{{{
CREATE OR REPLACE FUNCTION trgf_kapacitet_kurs()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_kapacitet INTEGER;
    v_upisani   INTEGER;
BEGIN
    IF NEW.status_slushanje <> 'UPISAN' THEN
        RETURN NEW;
    END IF;

    SELECT kapacitet_studenti
    INTO v_kapacitet
    FROM sm_kurs_realizacija
    WHERE id = NEW.kurs_realizacija_id
    FOR UPDATE; -- сериализира конкурентни уписи на иста реализација

    IF v_kapacitet IS NULL THEN
        RETURN NEW; -- неограничен капацитет
    END IF;

    SELECT COUNT(*)
    INTO v_upisani
    FROM sm_student_upisan_kurs
    WHERE kurs_realizacija_id = NEW.kurs_realizacija_id
      AND status_slushanje = 'UPISAN';

    IF v_upisani >= v_kapacitet THEN
        RAISE EXCEPTION 'Курсот % е полн (капацитет %)', NEW.kurs_realizacija_id, v_kapacitet;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_kapacitet_kurs ON sm_student_upisan_kurs;
CREATE TRIGGER trg_kapacitet_kurs
    BEFORE INSERT ON sm_student_upisan_kurs
    FOR EACH ROW
    EXECUTE FUNCTION trgf_kapacitet_kurs();
}}}

Тригерот `trg_kapacitet_kurs` се активира пред внесување упис во `sm_student_upisan_kurs`. За нов упис со статус `UPISAN` ја заклучува реализацијата (`FOR UPDATE`), го брои бројот на активни уписи и одбива ако е достигнат `kapacitet_studenti` (при што `NULL` значи неограничено).

Капацитетот мора да издржи и конкурентни уписи — двајца студенти што истовремено го земаат последното место. Проверка само во процедура би била race-condition бидејќи двете трансакции би виделе слободно место; тригерот со заклучување на редот на реализацијата ги сериализира таквите инсерти. Процедурата `pr_zapisi_student_na_kurs` сепак повикува `fn_slobodni_mesta` однапред за љубезна порака, но тригерот е последната, непробивна одбрана.

== Резиме

|| Тест || Резултат ||
|| `fn_prosek_student(1)` || 8.00 ||
|| `fn_otvoren_dolg(2, 1)` || 74925.00 ||
|| `fn_slobodni_mesta(1)` || 100 ||
|| П1: наплата 5000 за корисник 2 || 1 алокација, долг 74925→69925, статус → Delumno platena (Т2) ||
|| П2: студент со долг || блокирано со јасна порака ||
|| П2: студент без долг || упис креиран (UPISAN) ||
|| П3: непостоечки семестар || „Нема активен ценовник" ||
|| Т1: 150 поени / оценка на POLOZENO || двете одбиени ||
|| Т3: упис над капацитет || одбиен ||