= Advanced Database Development =

Во оваа фаза се имплементирани напредни механизми на ниво на базата на податоци за одржување на конзистентноста на податоците, автоматска контрола на залихата и поддршка на препораките и аналитичките функционалности во системот !BiblioPremium.

Имплементацијата вклучува:
- тригер функција и тригер за автоматска проверка и намалување на залихата;
- аналитички поглед за продажба по книга;
- поглед за следење на состојбата на залихата;
- функција за препорачување книги според избрано расположение.

== Автоматска проверка и намалување на залихата при завршување на нарачка ==

=== Data requirements description ===

При завршување на нарачка потребно е автоматски да се провери дали има доволно примероци на залиха за сите книги што се наоѓаат во нарачката.

Нарачката не смее да премине во статус ''ZAVRSENA'' доколку за некоја од книгите нема доволно примероци на залиха.

Доколку има доволно залиха за сите ставки, количината на залиха на секоја книга автоматски се намалува за количината што е нарачана.

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

=== Trigger function ===

Креирана е функцијата ''project.proveri_i_namali_zaliha()''.

{{{
CREATE OR REPLACE FUNCTION project.proveri_i_namali_zaliha()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    stavka RECORD;
BEGIN
    IF OLD.status <> 'ZAVRSENA'
       AND NEW.status = 'ZAVRSENA' THEN

        FOR stavka IN
            SELECT
                s.kniga_id,
                s.kolicina,
                k.naslov,
                k.kolicina_na_zaliha
            FROM project.sodrzi s
            JOIN project.knigi k
                ON s.kniga_id = k.kniga_id
            WHERE s.naracka_id = NEW.naracka_id
        LOOP
            IF stavka.kolicina_na_zaliha < stavka.kolicina THEN
                RAISE EXCEPTION
                    'Нема доволно залиха за книгата "%". Достапно: %, потребно: %',
                    stavka.naslov,
                    stavka.kolicina_na_zaliha,
                    stavka.kolicina;
            END IF;
        END LOOP;

        UPDATE project.knigi k
        SET kolicina_na_zaliha =
            k.kolicina_na_zaliha - s.kolicina
        FROM project.sodrzi s
        WHERE s.naracka_id = NEW.naracka_id
          AND k.kniga_id = s.kniga_id;

    END IF;

    RETURN NEW;
END;
$$;
}}}

Функцијата прво ги проверува сите ставки од нарачката. Доколку барем една книга нема доволно залиха, се генерира исклучок и промената на статусот не се извршува.

Само доколку сите ставки ја поминат проверката, се извршува автоматско намалување на залихата.

=== Trigger ===

{{{
CREATE TRIGGER trg_proveri_i_namali_zaliha
BEFORE UPDATE OF status
ON project.naracki
FOR EACH ROW
EXECUTE FUNCTION project.proveri_i_namali_zaliha();
}}}

Тригерот се активира при промена на атрибутот ''status'' на нарачката. Функцијата ја извршува проверката само кога статусот преминува од статус различен од ''ZAVRSENA'' во ''ZAVRSENA''.

=== Тестирање со доволна залиха ===

За тестирање беше употребена нарачка со идентификатор 4 која се наоѓаше во статус ''VO_OBRABOTKA''.

{{{
UPDATE project.naracki
SET status = 'ZAVRSENA'
WHERE naracka_id = 4;
}}}

Нарачката содржеше еден примерок од книгата ''Think and Grow Rich''. Пред завршувањето на нарачката книгата имаше 11 примероци на залиха.

По успешно завршување на нарачката залихата автоматски беше намалена на 10 примероци.

Со ова е потврдено дека тригерот правилно ја намалува залихата при успешно завршување на нарачката.

=== Тестирање со недоволна залиха ===

За проверка на ограничувањето беше поставена залиха 0 за книгата ''Atomic Habits'':

{{{
UPDATE project.knigi
SET kolicina_na_zaliha = 0
WHERE kniga_id = 1;
}}}

Потоа беше направен обид нарачката со идентификатор 5, која содржи еден примерок од оваа книга, да се постави во статус ''ZAVRSENA'':

{{{
UPDATE project.naracki
SET status = 'ZAVRSENA'
WHERE naracka_id = 5;
}}}

Базата на податоци ја одби операцијата и ја прикажа пораката:

{{{
Нема доволно залиха за книгата "Atomic Habits". Достапно: 0, потребно: 1.
}}}

Со ова е потврдено дека нарачка не може да биде означена како завршена доколку нема доволно залиха.

По тестирањето залихата беше вратена:

{{{
UPDATE project.knigi
SET kolicina_na_zaliha = 14
WHERE kniga_id = 1;
}}}


== Аналитички поглед за продажба по книга ==

=== Data requirements description ===

За потребите на администраторот и аналитичките извештаи потребно е на едно место да бидат достапни основните информации за продажбата на секоја книга.

Потребни се информации за моменталната залиха, бројот на продадени примероци, остварениот приход и бројот на реализирани нарачки.

За таа цел е креиран погледот ''project.v_prodazba_po_kniga''.

=== View ===

{{{
CREATE OR REPLACE VIEW project.v_prodazba_po_kniga AS
SELECT
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha,
    COALESCE(SUM(s.kolicina), 0) AS vkupno_prodadeni,
    COALESCE(
        SUM(s.kolicina * s.edinechna_cena),
        0
    ) AS vkupen_prihod,
    COUNT(DISTINCT n.naracka_id) AS broj_naracki
FROM project.knigi k
LEFT JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
LEFT JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
    AND n.status = 'ZAVRSENA'
WHERE n.naracka_id IS NOT NULL
   OR s.naracka_id IS NULL
GROUP BY
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha;
}}}

Погледот ги обединува податоците од релациите ''knigi'', ''sodrzi'' и ''naracki'' и обезбедува повторно употреблив извор на агрегирани информации за продажбата.

=== Тестирање ===

{{{
SELECT *
FROM project.v_prodazba_po_kniga
ORDER BY vkupen_prihod DESC;
}}}

Погледот успешно ги прикажува податоците за продажбата на книгите подредени според вкупниот приход.


== Следење на залихата на книгите ==

=== Data requirements description ===

За администраторот е важно навремено да може да ги забележи книгите со мала или целосно потрошена залиха.

За таа цел е креиран поглед кој покрај моменталната количина на залиха го определува и статусот на залихата.

Книгите се класифицираат во три групи:
- ''NEMA_ZALIHA'' – кога количината е 0;
- ''NISKA_ZALIHA'' – кога количината е од 1 до 5;
- ''DOVOLNA_ZALIHA'' – кога количината е поголема од 5.

=== View ===

{{{
CREATE OR REPLACE VIEW project.v_niska_zaliha AS
SELECT
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha,
    COALESCE(SUM(s.kolicina), 0) AS vkupno_prodadeni,
    CASE
        WHEN k.kolicina_na_zaliha = 0 THEN 'NEMA_ZALIHA'
        WHEN k.kolicina_na_zaliha <= 5 THEN 'NISKA_ZALIHA'
        ELSE 'DOVOLNA_ZALIHA'
    END AS status_zaliha
FROM project.knigi k
LEFT JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
LEFT JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
    AND n.status = 'ZAVRSENA'
GROUP BY
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha;
}}}

=== Тестирање ===

{{{
SELECT *
FROM project.v_niska_zaliha
ORDER BY kolicina_na_zaliha ASC;
}}}

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

Овој поглед може да му помогне на администраторот полесно да идентификува книги за кои е потребно дополнување на залихата.


== Препорачување книги според расположение ==

=== Data requirements description ===

Една од главните функционалности на !BiblioPremium е препорачување книги според моменталното расположение или потреба на корисникот.

Бидејќи книгите се поврзани со расположенија преку релацијата ''povrzana_so'', креирана е функција која за избрано расположение ги враќа соодветните книги кои моментално се достапни на залиха.

=== Stored function ===

{{{
CREATE OR REPLACE FUNCTION project.preporacaj_knigi_po_raspolozenie(
    p_raspolozenie_id INTEGER
)
RETURNS TABLE(
    kniga_id BIGINT,
    naslov VARCHAR,
    cena NUMERIC,
    kolicina_na_zaliha INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT
            k.kniga_id,
            k.naslov,
            k.cena,
            k.kolicina_na_zaliha
        FROM project.knigi k
        JOIN project.povrzana_so ps
            ON k.kniga_id = ps.kniga_id
        WHERE ps.raspolozenie_id = p_raspolozenie_id
          AND k.kolicina_na_zaliha > 0
        ORDER BY k.naslov;
END;
$$;
}}}

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

Дополнително, условот ''k.kolicina_na_zaliha > 0'' спречува да се препорачуваат книги кои моментално не се достапни.

=== Тестирање ===

Функцијата беше тестирана за расположението со идентификатор 1:

{{{
SELECT *
FROM project.preporacaj_knigi_po_raspolozenie(1);
}}}

Како резултат беа добиени книгите:

- ''Atomic Habits''
- ''Mindset''
- ''Think and Grow Rich''

Со ова е потврдено дека функцијата правилно ги користи врските помеѓу книгите и расположенијата и ги враќа само книгите кои имаат достапна залиха.


== Заклучок ==

Во оваа фаза се имплементирани повеќе механизми на ниво на базата на податоци кои се директно поврзани со деловната логика на !BiblioPremium.

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

Погледот ''v_prodazba_po_kniga'' обезбедува агрегирани информации за продажбата, додека ''v_niska_zaliha'' овозможува полесно следење на состојбата на залихите.

Функцијата ''preporacaj_knigi_po_raspolozenie()'' ја поддржува една од главните функционалности на системот – препорачување достапни книги според избраното расположение.

Со овие механизми се воведува дополнителна логика директно во базата на податоци над основните ограничувања и референцијалниот интегритет дефинирани во претходните фази.