DatabaseProgramming: 13_routines.sql

-- ==============================================================================
-- 13. ФУНКЦИИ / ПРОЦЕДУРИ / ТРИГЕРИ (3 + 3 + 3)
-- ==============================================================================

-- ============================== ФУНКЦИИ =======================================

-- F1: Кредитно-пондериран просек на студент (опционално по систем на оценување).
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)
    $$;

-- F2: Вкупен отворен долг на корисник во дадена валута.
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'))
    $$;

-- F3: Слободни места на реализација на курс (NULL капацитет = неограничено).
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
    $$;

-- ============================== ПРОЦЕДУРИ =====================================

-- P1: Регистрирање наплата + автоматска алокација кон отворени обврски
--     (најстаро доспевање прво). Ажурирањето на otvorena_suma го врши тригерот T2.
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;
$$;

-- P2: Запишување студент на курс — проверува долг и слободни места,
--     па внесува во sm_student_upisan_kurs (тригерот T3 е последна одбрана).
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;
$$;

-- P3: Сериско генерирање обврски за школарина за сите студенти запишани
--     во даден академски семестар, според активниот ценовник. COMMIT на
--     секои 1000 студенти — можно само во процедура.
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;
$$;

-- ============================== ТРИГЕРИ =======================================

-- T1: Валидација при внес на оценка — уписот мора да е активен (UPISAN),
--     поените во опсег 0–100, датумот не во иднина.
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();

-- T2: По алокација на наплата — намали otvorena_suma и постави статус
--     (Delumno platena / Platena). Изведената вредност ја одржува тригерот,
--     без разлика кој ја внел алокацијата.
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();

-- T3: Капацитет на курс — одбиј упис над kapacitet_studenti, и при
--     конкурентни инсерти (FOR UPDATE врз реализацијата).
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();