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

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

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

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

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

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

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();

2.Views

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

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

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

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

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;