= Напреден развој на база =

== 1.Тригери и Функции ==

=== 1.1 Автоматско пресметување на вкупниот износ на продажба ===

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

{{{#!sql
CREATE OR REPLACE FUNCTION trg_fn_recalculate_sale_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_sale_id INT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_sale_id := OLD.sale_id;
    ELSE
        v_sale_id := NEW.sale_id;
    END IF;

    UPDATE sale
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price_at_sale), 0)
        FROM sale_item
        WHERE sale_id = v_sale_id
    )
    WHERE sale_id = v_sale_id;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_recalculate_sale_total
AFTER INSERT OR UPDATE OR DELETE ON sale_item
FOR EACH ROW
EXECUTE FUNCTION trg_fn_recalculate_sale_total();
}}}

=== 1.2 Спречување на паѓање на залихата во негативна вредност ===

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

{{{#!sql
CREATE OR REPLACE FUNCTION trg_fn_prevent_negative_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.quantity_on_hand < 0 THEN
        RAISE EXCEPTION
            'Stock cannot be negative! Warehouse: %, Product: %, Quantity: %',
            NEW.warehouse_id, NEW.product_id, NEW.quantity_on_hand;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_prevent_negative_stock
BEFORE INSERT OR UPDATE ON warehouse_stock
FOR EACH ROW
EXECUTE FUNCTION trg_fn_prevent_negative_stock();
}}}

=== 1.3 Корисникот не може сам да го избрише својот акаунт ===

Ако најавен корисник се обиде да го избрише својот сопствен акаунт, тоа се спречува.

{{{#!sql
CREATE OR REPLACE FUNCTION trg_fn_no_self_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.username = CURRENT_USER THEN
        RAISE EXCEPTION 'You cannot delete your own account.';
    END IF;
    RETURN OLD;
END;
$$;

CREATE TRIGGER trg_no_self_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION trg_fn_no_self_delete();
}}}

== 2.Views ==

=== 2.1 Кој ден од неделата се продава најмногу? ===

Кои денови се зафатени, а кои мирни за планирање на персоналот.

{{{#!sql
CREATE OR REPLACE VIEW vw_sales_by_day_of_week AS
SELECT
    EXTRACT(ISODOW FROM s.date_time)::INT AS day_number,
    CASE EXTRACT(ISODOW FROM s.date_time)::INT
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
        WHEN 7 THEN 'Sunday'
    END AS day_name,
    COUNT(DISTINCT s.sale_id) AS total_sales,
    SUM(s.total_amount)       AS total_revenue,
    ROUND(AVG(s.total_amount), 2) AS avg_sale_value,
    SUM(si.quantity)          AS total_items_sold
FROM sale s
JOIN sale_item si ON s.sale_id = si.sale_id
GROUP BY EXTRACT(ISODOW FROM s.date_time)
ORDER BY day_number;
}}}

=== 2.2 Кој продавач продава повеќе? ===

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

{{{#!sql
CREATE OR REPLACE VIEW vw_employee_sales_ranking AS
SELECT
    u.user_id,
    u.full_name,
    u.role,
    COUNT(DISTINCT s.sale_id) AS total_sales,
    COALESCE(SUM(s.total_amount), 0) AS total_revenue,
    ROUND(COALESCE(AVG(s.total_amount), 0), 2) AS avg_sale_value,
    COUNT(DISTINCT s.customer_id) AS unique_customers,
    RANK() OVER (ORDER BY COALESCE(SUM(s.total_amount), 0) DESC)
        AS revenue_rank
FROM users u
LEFT JOIN sale s ON u.user_id = s.user_id
GROUP BY u.user_id, u.full_name, u.role
HAVING COUNT(s.sale_id) > 0
ORDER BY total_revenue DESC;
}}}