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

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

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

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

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 Спречување на паѓање на залихата во негативна вредност

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

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 Корисникот не може сам да го избрише својот акаунт

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

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

CREATE TRIGGER trg_user_self_delete BEFORE DELETE ON users
FOR EACH ROW EXECUTE FUNCTION trg_func_no_self_delete();

2.Views

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

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

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 Кој продавач продава повеќе?

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

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;