Views:

1. order_store_details

CREATE OR REPLACE VIEW order_store_details AS SELECT

o.order_id, o.customer_id, o.order_date, o.total_price, o.status, si.store_instance_id, si.address_id AS store_address, s.store_name AS store_name, s.store_type_id AS store_type, si.instance_name AS instance_name

FROM "Order" o JOIN StoreInstance si ON o.store_instance_id = si.store_instance_id JOIN Store s ON si.store_id = s.store_id; Овој поглед ги прикажува сите нарачки направени во историјата на апликацијата. Ја гледа само табелата Order без никакви joinovi.

2.available_stores_list CREATE VIEW available_stores_list AS SELECT

s.store_id, s.store_name AS store_name, s.store_type_id store_type, si.instance_name instance_name, si.store_instance_id, si.address_id AS store_location

FROM Store s JOIN StoreInstance si ON s.store_id = si.store_id; Овој поглед е наменет за корисничкиот интерфејс за да види корисникот од кои продавници може да направи нарачка. Има JOIN помеѓу Store и StoreInstance

3.driver_performance_metrics CREATE OR REPLACE VIEW driver_performance_metrics AS SELECT

d.driver_id, u.name, u.surname, vt.vehicle_type, ROUND(AVG(r.rating), 2) AS average_rating, COUNT(dr.review_id) AS total_reviews

FROM Driver d JOIN "User" u ON d.driver_id = u.user_id JOIN Vehicle v ON d.vehicle_id = v.vehicle_id JOIN VehicleType vt ON v.vehicle_type_id = vt.vehicle_type_id JOIN Delivery_review dr ON d.driver_id = dr.driver_id JOIN Review r ON dr.review_id = r.review_id GROUP BY d.driver_id, u.name, u.surname, vt.vehicle_type; Овој поглед е наменет за менаџментот и за следење на квалитетот на работа на возачите. Врз основа на просечниот рејтинг и бројот на рецензии добиени од корисниците, системот автоматски може да пресметува бонуси за брзина и љубезност или да ги суспендира возачите со лоши перформанси.

4.store_performance_metrics CREATE VIEW store_performance_metrics AS SELECT

si.store_instance_id, s.store_name AS brand_name, si.address_id AS store_address, ROUND(AVG(r.rating), 2) AS average_product_rating, COUNT(pr.review_id) AS total_product_reviews

FROM StoreInstance si JOIN Store s ON si.store_id = s.store_id JOIN Products_review pr ON si.store_instance_id = pr.store_instance_id JOIN Review r ON pr.review_id = r.review_id GROUP BY si.store_instance_id, s.store_name, si.address_id; Служи за аналитика на партнерските продавници и ресторани, овозможувајќи им на сопствениците да видат како се оценети нивните локали и продукти од страна на купувачите. Овие податоци помагаат за рангирање на продавниците (на пример, „Најдобро оценети“) на самата платформа за достава.

5.customer_order_history_summary CREATE VIEW customer_order_history_summary AS SELECT

u.user_id as customer_id, u.name, u.surname, COUNT(o.order_id) as total_orders, SUM(o.total_price) as total_spent, ROUND(AVG(o.total_price), 2) as average_order_value, MAX(p.payment_date) as last_order_date

FROM "User" u JOIN Customer c ON u.user_id = c.customer_id LEFT JOIN "Order" o ON c.customer_id = o.customer_id LEFT JOIN Payment p ON o.order_id = p.order_id GROUP BY u.user_id, u.name, u.surname; Оваа логика се користи во профилот на корисникот (User Profile) за да му ја прикаже неговата историја на купување, но уште поважно, се користи за маркетинг и лојалност програми. Маркетингот го користи за да идентификува VIP корисници (кои потрошиле најмногу) и да им прати таргетирани купони со попусти.

6.top_trending_products_weekly CREATE OR REPLACE VIEW top_trending_products_weekly AS SELECT

pr.product_id, pr.name as product_name, s.store_name, SUM(oi.quantity) as total_sold, COUNT(DISTINCT o.order_id) as appearing_in_orders

FROM "Order" o JOIN OrderItem oi ON o.order_id = oi.order_id JOIN Product pr ON oi.product_id = pr.product_id JOIN Store s ON pr.store_id = s.store_id ct_id, pr.name, s.store_name ORDER BY total_sold DESC LIMIT 20; Директно ја имплементира бизнис секцијата „Најпродавани / Трендинг продукти“ на главниот екран. Со пребројување на количините во милионската табела OrderItem, системот знае кои 20 продукти се хит неделава и автоматски ги турка напред во апликацијата за да ја зголеми продажбата.

7.delivery_performance_by_city CREATE OR REPLACE VIEW delivery_performance_by_city AS SELECT

c.city_name, COUNT(o.order_id) AS total_orders_delivered, SUM(o.delivery_fee) AS total_delivery_revenue, ROUND(AVG(o.delivery_fee), 2) AS avg_delivery_fee_per_order, COUNT(DISTINCT o.driver_id) AS active_drivers_in_city, ROUND(AVG(o.delivery_fee::numeric / NULLIF(o.total_price, 0) * 100), 2) AS delivery_cost_percentage

FROM City c JOIN DeliveryZone dz ON c.city_id = dz.city_id JOIN "Order" o ON dz.zone_id = o.delivery_zone_id WHERE o.status = 'delivered' -- Ги земаме само успешните достави GROUP BY c.city_name ORDER BY total_orders_delivered DESC; Ова е суштински поглед за стратешко планирање и ширење на бизнисот. Менаџментот гледа во кои градови има најголем профит од достава, колку се активни возачите таму и кој е процентот на заработка од самата такса за достава, со цел да знаат каде да инвестираат во нови маркетинг кампањи.

8.order_counts_by_payment_type CREATE VIEW order_counts_by_payment_type AS SELECT

pm.payment AS payment_type, COUNT(o.order_id) AS total_orders, SUM(o.total_price) AS total_revenue

FROM "Order" o JOIN Payment p ON o.order_id = p.order_id JOIN Payment_method pm ON p.payment_method_id = pm.payment_method_id WHERE o.status = 'delivered' GROUP BY pm.payment ORDER BY total_orders DESC; Се користи од страна на финансискиот сектор за следење на преференциите за плаќање (картичка, во готово, крипто итн.). Логиката помага да се анализира каде има најголем проток на кеш и дали треба да се оптимизираат договорите со банките за провизиите од картички.

9.products_with_store CREATE or replace VIEW products_with_store AS SELECT

pr.product_id, pr.name AS product_name, pr.price, pr.store_id, s.store_name

FROM Product pr 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) и да не е веќе процесирана или доставена.