Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

05/26/26 16:22:26 (6 hours ago)

Author:

232026

Comment:

--

DatabaseProgramming

@@ -141 +141 @@
 JOIN Store s ON pr.store_id = s.store_id;
 Овој поглед е примарен за пребарувачот (Search Bar) и менијата на рестораните во апликацијата. Овозможува кога корисникот пребарува одреден продукт (на пример: „бургер“), веднаш до името и цената на продуктот да се прикаже и името на брендот/ресторанот кој го нуди тој продукт.
+
+== Functions/Procedures/Triggers: ==
+
+1.is_zone_overloaded (Функција)
+CREATE OR REPLACE FUNCTION is_zone_overloaded(p_zone_id INT)
+RETURNS BOOLEAN AS $$
+DECLARE
+    available_drivers_count INT;
+BEGIN
+    -- Броиме колку возачи во таа зона денес имаат помалку од 10 нарачки
+    SELECT COUNT(DISTINCT driver_id) INTO available_drivers_count
+    FROM "Order"
+    WHERE delivery_zone_id = p_zone_id
+      AND order_date = CURRENT_DATE
+    GROUP BY driver_id
+    HAVING COUNT(order_id) < 10;
+
+    -- Ако има помалку од 3 слободни возачи, зоната е преоптоварена
+    RETURN COALESCE(available_drivers_count, 0) < 3;
+END;
+Ја проверува оптовареноста на одредена зона во реално време. Бизнис логиката ја користи за да изброи колку активни возачи имаат помалку од 10 нарачки во денот, па доколку таа бројка е под 3, системот сигнализира дека зоната е преоптоварена и апликацијата може да го зголеми времето на чекање или цената на доставата.
+
+2.trg_prevent_delivered_update & lock_delivered_orders (Тригер)
+CREATE OR REPLACE FUNCTION trg_prevent_delivered_update()
+RETURNS TRIGGER AS $$
+BEGIN
+    -- Ако старата вредност на статусот била 'delivered', прекини ја операцијата
+    IF OLD.status = 'delivered' THEN
+        RAISE EXCEPTION 'Не може да се менува нарачка која е веќе доставена!';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER lock_delivered_orders
+BEFORE UPDATE ON "Order"
+FOR EACH ROW
+EXECUTE FUNCTION trg_prevent_delivered_update();
+
+CREATE OR REPLACE PROCEDURE cancel_stale_orders(p_days_old INT)
+AS $$
+BEGIN
+    -- Ги наоѓаме сите pending нарачки постари од X дена
+    UPDATE "Order"
+    SET status = 'canceled'
+    WHERE status = 'pending'
+      AND order_date < (CURRENT_DATE - p_days_old);
+
+    RAISE NOTICE 'Старите нарачки се успешно откажани.';
+END;
+$$ LANGUAGE plpgsql;
+Обезбедува строг интегритет на податоците во базата со тоа што спречува какви било последователни промени на веќе доставена нарачка (status = 'delivered'). Ова е клучен безбедносен механизам што оневозможува манипулација со старите финансиски и архивски записи од страна на корисници или администратори.
+
+3. cancel_stale_orders (Процедура)
+CREATE OR REPLACE PROCEDURE cancel_stale_orders(p_days_old INT)
+AS $$
+BEGIN
+    -- Ги наоѓаме сите pending нарачки постари од X дена
+    UPDATE "Order"
+    SET status = 'canceled'
+    WHERE status = 'pending'
+      AND order_date < (CURRENT_DATE - p_days_old);
+
+    RAISE NOTICE 'Старите нарачки се успешно откажани.';
+END;
+Автоматски системски чистач на „заглавени“ нарачки. Бизнис логиката ја извршува оваа процедура во позадина (на пример, на крајот од денот) за автоматски да ги откаже (canceled) сите нарачки кои со денови останале со статус pending, со цел да се исчисти оперативната меморија на базата.
+
+4. trg_limit_driver_daily_orders & check_driver_limit (Тригер)
+CREATE OR REPLACE FUNCTION trg_limit_driver_daily_orders()
+RETURNS TRIGGER AS $$
+DECLARE
+    daily_count INT;
+BEGIN
+    -- Броиме колку нарачки има возачот денес
+    SELECT COUNT(*) INTO daily_count
+    FROM "Order"
+    WHERE driver_id = NEW.driver_id
+      AND order_date = CURRENT_DATE;
+
+    IF daily_count >= 20 THEN
+        RAISE EXCEPTION 'Возачот го достигна дневниот лимит од 20 нарачки!';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE TRIGGER check_driver_limit
+BEFORE INSERT ON "Order"
+FOR EACH ROW
+WHEN (NEW.driver_id IS NOT NULL)
+EXECUTE FUNCTION trg_limit_driver_daily_orders();
+Го контролира дневниот капацитет и безбедноста на возачите на ниво на база. Пред да се додели нова нарачка на возач, тригерот проверува дали тој веќе ја достигнал границата од 20 достави во денот, и доколку е така, фрла грешка и спречува преоптоварување со работа.
+
+5.reassign_driver_orders (Процедура)
+CREATE OR REPLACE PROCEDURE reassign_driver_orders(old_driver_id INT, new_driver_id INT)
+AS $$
+BEGIN
+    UPDATE "Order"
+    SET driver_id = new_driver_id
+    WHERE driver_id = old_driver_id
+      AND status = 'pending';
+    RAISE NOTICE 'Нарачките се успешно префрлени на новиот возач.';
+END;
+Служи за итна реакција при непредвидени ситуации на терен (дефект на возило, сообраќајна несреќа). Администраторот или диспечерот ја повикува оваа процедура за инстантно и безбедно да ги префрли сите активни (pending) нарачки од еден возач кај друг слободен колега.
+
+6. trg_check_driver_performance & after_order_delivered_performance (Тригер)
+CREATE OR REPLACE FUNCTION trg_check_driver_performance()
+RETURNS TRIGGER AS $$
+DECLARE
+    cancel_count INT;
+BEGIN
+    -- Броиме колку откажани нарачки има возачот во последните 30 дена
+    SELECT COUNT(*) INTO cancel_count
+    FROM "Order"
+    WHERE driver_id = NEW.driver_id
+      AND status = 'canceled'
+      AND order_date > CURRENT_DATE - INTERVAL '30 days';
+
+    -- Ако има над 5 откажани, се логира предупредување (или се врши акција)
+    IF cancel_count > 5 THEN
+        RAISE NOTICE 'Возачот % има висок степен на откажани нарачки (%)!', NEW.driver_id, cancel_count;
+        -- Тука може да додадеш UPDATE Driver SET rating = rating - 1 ...
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_order_delivered_performance
+AFTER UPDATE OF status ON "Order"
+FOR EACH ROW
+WHEN (NEW.status = 'delivered')
+EXECUTE FUNCTION trg_check_driver_performance();
+Ова е автоматизиран систем за контрола на квалитетот кој се активира по секоја успешна достава. Ја пресметува доверливоста на возачот со тоа што брои колку пати откажал нарачка во последните 30 дена, и доколку тој број е критичен (над 5), веднаш испраќа системско предупредување до менаџментот.
+
+7. suspend_inactive_drivers (Процедура)
+CREATE OR REPLACE PROCEDURE suspend_inactive_drivers()
+AS $$
+BEGIN
+    UPDATE Driver
+    SET status = 'suspended'
+    WHERE driver_id NOT IN (
+        SELECT DISTINCT driver_id
+        FROM "Order"
+        WHERE status = 'delivered'
+          AND order_date > CURRENT_DATE - INTERVAL '90 days'
+    );
+
+    RAISE NOTICE 'Неактивните возачи се успешно суспендирани.';
+END;
+Служи за филтрирање и одржување на листата на вработени. Процедурата ја пребарува базата и автоматски ги суспендира (suspended) сите возачи кои биле комплетно неактивни (не доставиле ниту една нарачка) во изминатите 90 дена, со што се спречува „зашумување“ на податоците.
+
+8. trg_check_minimum_order_amount & check_min_amount (Тригер)
+CREATE OR REPLACE FUNCTION trg_check_minimum_order_amount()
+RETURNS TRIGGER AS $$
+BEGIN
+    -- Проверуваме дали вкупната сума е помала од 300 (на пример)
+    IF NEW.total_price < 300 THEN
+        RAISE EXCEPTION 'Нарачката мора да биде најмалку 300 денари за да биде прифатена.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER check_min_amount
+BEFORE INSERT ON "Order"
+FOR EACH ROW
+EXECUTE FUNCTION trg_check_minimum_order_amount();
+Ја спроведува бизнис политиката за рентабилност директно во базата пред да се запише нарачката. Ако корисникот се обиде да направи нарачка чија вкупна вредност е помала од 300 денари, тригерот ја прекинува трансакцијата и враќа порака дека сумата е под дозволениот минимум.
+
+9. update_store_prices_percent (Процедура)
+CREATE OR REPLACE PROCEDURE update_store_prices_percent(p_store_id INT, p_percentage DECIMAL)
+AS $$
+BEGIN
+    -- Ги ажурираме сите продукти кои припаѓаат на таа продавница
+    UPDATE Product
+    SET price = ROUND(price * (1 + p_percentage / 100))
+    WHERE store_id = p_store_id;
+
+    RAISE NOTICE 'Цените за продавницата % се зголемени за % проценти.', p_store_id, p_percentage;
+
+    COMMIT;
+END;
+Овозможува масовно менаџирање со цените во услови на инфлација или промотивни периоди. Со едноставен повик, процедурата ги ажурира и ги заокружува цените на сите продукти кои припаѓаат на една специфична продавница за одреден процент, заштедувајќи време и ресурси.
+
+10. register_new_customer (Процедура)
+CREATE OR REPLACE PROCEDURE register_new_customer(
+    p_username VARCHAR(255),
+    p_email VARCHAR(255),
+    p_password_plain VARCHAR(255), -- Ова доаѓа од фронтендот (React)
+    p_phone VARCHAR(255)
+)
+AS $$
+DECLARE
+    v_email_exists INT;
+    v_password_hash VARCHAR(255);
+BEGIN
+    -- 1. Проверка дали е-маилот е веќе зафатен
+    SELECT COUNT(*) INTO v_email_exists
+    FROM "User" -- Името на табелата прилагоди го ако ти е Customer или AppUser
+    WHERE email = p_email;
+
+    IF v_email_exists > 0 THEN
+        RAISE EXCEPTION 'Корисник со е-маил адресата % веќе постои во системот!', p_email;
+    END IF;
+
+    -- 2. Симулација на хаширање на лозинката за безбедност
+    -- Во реален Postgres би користеле crypt(), тука правиме едноставен MD5 за факултет
+    v_password_hash := md5(p_password_plain);
+
+    -- 3. Вметнување на новиот корисник
+    INSERT INTO "User" (username, email, password, phone, created_at)
+    VALUES (p_username, p_email, v_password_hash, p_phone, CURRENT_TIMESTAMP);
+
+    RAISE NOTICE 'Корисникот % е успешно регистриран со хаширана лозинка.', p_username;
+
+    COMMIT;
+END;
+Ја презема одговорноста за безбедно и правилно креирање на кориснички профил. Бизнис логиката ја користи за да провери дали е-маилот е веќе зафатен, да ја трансформира чистата лозинка во безбеден md5 хаш запис и да го внесе корисникот во системот како една сигурна целина.
+
+11. create_new_order (Процедура)
+CREATE OR REPLACE PROCEDURE create_new_order(
+    p_user_id INT,
+    p_store_id INT,
+    p_address_id INT,
+    p_zone_id INT,
+    p_delivery_fee INT
+)
+AS $$
+DECLARE
+    v_order_id INT;
+BEGIN
+    -- 1. Валидација: Проверка дали адресата на достава е во точната зона
+    -- (Претпоставуваме дека во StoreInstance или Address чуваш zone_id)
+    IF p_zone_id IS NULL THEN
+        RAISE EXCEPTION 'Невалидна зона за достава!';
+    END IF;
+
+    -- 2. Вметнување на главниот запис за нарачката
+    -- Почетниот статус секогаш е 'pending', а total_price почнува од 0
+    -- (подоцна се ажурира кога ќе се додадат продуктите во OrderItem)
+    INSERT INTO "Order" (
+        user_id,
+        store_id,
+        address_id,
+        zone_id,
+        delivery_fee,
+        total_price,
+        status,
+        order_date
+    )
+    VALUES (
+        p_user_id,
+        p_store_id,
+        p_address_id,
+        p_zone_id,
+        p_delivery_fee,
+        0, -- почетна цена
+        'pending',
+        CURRENT_DATE
+    )
+    RETURNING order_id INTO v_order_id;
+
+    -- 3. Логирање на успешно креирана празна нарачка во конзола
+    RAISE NOTICE 'Успешно креирана нарачка со ID: %. Сега апликацијата може да додава продукти во OrderItem.', v_order_id;
+
+    COMMIT;
+END;
+Го отвора процесот на купување (Checkout) во системот. Процедурата ја валидира зоната на достава, креира нов запис со почетен статус pending, ја зачувува цената за достава и на крајот го враќа новото order_id назад до апликацијата за таа да може да почне да ги полни ставките од кошничката.
+
+12. cancel_order (Процедура)
+CREATE OR REPLACE PROCEDURE cancel_order(p_order_id INT)
+AS $$
+DECLARE
+    current_order_status VARCHAR(50);
+BEGIN
+    -- 1. Го земаме моменталниот статус на нарачката
+    SELECT status INTO current_order_status
+    FROM "Order"
+    WHERE order_id = p_order_id;
+
+    -- 2. Проверка дали нарачката воопшто постои
+    IF current_order_status IS NULL THEN
+        RAISE EXCEPTION 'Нарачката со ID % не постои во системот!', p_order_id;
+    END IF;
+
+    -- 3. Проверка дали статусот е 'pending'
+    -- Ако е веќе 'delivered' или 'canceled', не дозволуваме промена
+    IF current_order_status != 'pending' THEN
+        RAISE EXCEPTION 'Не може да се откаже нарачката бидејќи нејзиниот моментален статус е "%".', current_order_status;
+    END IF;
+
+    -- 4. Ја правиме промената во 'canceled'
+    UPDATE "Order"
+    SET status = 'canceled'
+    WHERE order_id = p_order_id;
+
+    RAISE NOTICE 'Нарачката со ID % е успешно откажана.', p_order_id;
+
+    COMMIT;
+END;
+Ја содржи целата бизнис логика за откажување на нарачка од страна на купувачот. Овозможува трансакцијата безбедно да се стопира и да се префрли во статус canceled, но само под услов нарачката сè уште да е во почетна фаза (pending) и да не е веќе процесирана или доставена.