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

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 stock_management.prevent_self_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.username = current_setting('app.current_user', true) THEN
        RAISE EXCEPTION 'You cannot delete your own account.';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_prevent_self_delete
BEFORE DELETE ON stock_management.users
FOR EACH ROW EXECUTE FUNCTION stock_management.prevent_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;

3. Автоматско логирање и репортирање на промените на цените на производите

3.1 Лог табела

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

CREATE TABLE stock_management.product_price_log (
    log_id       SERIAL PRIMARY KEY,
    product_id   INT NOT NULL,
    product_name VARCHAR(100),
    old_price    DECIMAL(12,2),
    new_price    DECIMAL(12,2),
    changed_by   VARCHAR(50),
    changed_at   TIMESTAMP DEFAULT NOW()
);

3.2 Функција на тригер

Функција која проверува дали полето unit_price се променила при секое ажурирање во табелата product и ако има промена ги запишува релевантните информации во лог табелата.

CREATE OR REPLACE FUNCTION stock_management.log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.unit_price <> OLD.unit_price THEN
        INSERT INTO stock_management.product_price_log
            (product_id, product_name, old_price, new_price, changed_by)
        VALUES
            (OLD.product_id, OLD.name, OLD.unit_price, NEW.unit_price,
             current_setting('app.current_user', true));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3.3 Тригер

Тригер која обезбедува функцијата да се активира автоматски пред секое UPDATE на табелата product.

CREATE OR REPLACE TRIGGER trg_price_change
BEFORE UPDATE ON stock_management.product
FOR EACH ROW EXECUTE FUNCTION stock_management.log_price_change();