Додавање на продукт

Оваа функционалност овозможува додавање на нов продукт во системот.

Функција: fn_validate_seller_package

Проверува дали продавачот има право да објавува продукти.

• Проверува дали корисникот постои и е активен.
• Проверува дали корисникот е верифициран.
• Проверува дали корисникот има активен пакет чиј период на важност не е истечен.

CREATE OR REPLACE FUNCTION fn_validate_seller_package(p_seller_id INT)
RETURNS VOID LANGUAGE plpgsql AS $$
begin
	IF NOT EXISTS (
        SELECT 1 FROM appuser WHERE user_id = p_seller_id AND is_active = 1
    ) THEN
        RAISE EXCEPTION 'Sellerot ne postoi ili ne e aktiven';
    END IF;
   
    IF NOT EXISTS (
        SELECT 1 FROM appuser
        WHERE user_id = p_seller_id AND is_verified = 1
    ) THEN
        RAISE EXCEPTION 'Sellerot ne e verificiran';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM userpackages
        WHERE seller_id = p_seller_id
          AND CURRENT_TIMESTAMP BETWEEN start_date AND end_date
    ) THEN
        RAISE EXCEPTION 'Sellerot nema aktiven paket (istecen ili ne postoi)';
    END IF;
END;
$$;

Функција fn_validate_product_attribute

Валидира парови атрибут:вредност

• Проверува дали атрибутот е дефиниран за категоријата во која припаѓа продуктот.
• Проверува дали внесената вредност е една од дозволените вредности за атрибутот.

CREATE OR REPLACE FUNCTION fn_validate_product_attribute(
    p_category_id           INT,
    p_category_attribute_id INT,
    p_attribute_value       VARCHAR
)
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM categoryattributes
        WHERE category_attribute_id = p_category_attribute_id
          AND category_id = p_category_id
    ) THEN
        RAISE EXCEPTION 'Atributot % ne pripagja na kategorijata', p_category_attribute_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM categoryattributesvalues
        WHERE category_attribute_id = p_category_attribute_id
          AND allowed_value = p_attribute_value
    ) THEN
        RAISE EXCEPTION 'Vrednosta % ne pripagja na atributot', p_attribute_value;
    END IF;
END;
$$;

Процедура add_product

Додава нов продукт во системот заедно со неговите атрибути, статистики и слики.

CREATE OR REPLACE PROCEDURE add_product(
    p_title VARCHAR,
    p_description VARCHAR,
    p_price NUMERIC,
    p_location VARCHAR,
    p_currency VARCHAR,
    p_category_id INT,
    p_seller_id INT,
    p_quantity INT,
    p_attributes JSONB,   -- ex. '[{"category_attribute_id": 11, "attribute_value": "M"}, ...]'
    p_images TEXT[]       -- ex. ARRAY['url1.jpg', 'url2.jpg']
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_product_id INT;
    v_attr JSONB;
    v_image TEXT;
begin
    -- Validacii
    IF p_price <= 0 THEN
        RAISE EXCEPTION 'Cenata mora da e pogolema od 0';
    END IF;

    IF p_quantity < 0 THEN
        RAISE EXCEPTION 'Kolichinata ne moze da e negativna';
    END IF;

   
    IF EXISTS (
        SELECT 1 FROM category WHERE parent_category = p_category_id
    ) THEN
        RAISE EXCEPTION 'Kategorijata mora da e leaf kategorija';
    END IF;

    
    --FUNKCIJA KORISNIK
   perform fn_validate_seller_package(p_seller_id);
   

    -- Insert produkt
    INSERT INTO product (title, description, price, "location" , category_id, seller_id, created_at, is_active, currency, quantity)
	VALUES (
	    p_title,
	    p_description,
	    p_price,
	    p_location,
	    p_category_id,
	    p_seller_id,
	    CURRENT_TIMESTAMP,
	    CASE WHEN p_quantity > 0 THEN TRUE ELSE FALSE END,
	    p_currency,
	    p_quantity
	)
	RETURNING product_id INTO v_product_id;

    -- Insert productstats (pochetni vrednosti)
    INSERT INTO productstats (product_id, views_count, favorites_count, sells_count)
    VALUES (v_product_id, 0, 0, 0);

    -- Insert atributi funkcija
   IF p_attributes IS NOT NULL then
	   FOR v_attr IN SELECT value FROM jsonb_array_elements(p_attributes) LOOP
	    PERFORM fn_validate_product_attribute(
	        p_category_id,
	        (v_attr->>'category_attribute_id')::INT,
	        v_attr->>'attribute_value'
	    );
	    INSERT INTO productattributes (product_id, category_attribute_id, attribute_value)
	    VALUES (v_product_id, (v_attr->>'category_attribute_id')::INT, v_attr->>'attribute_value');
		END LOOP;
	END IF;

    -- Insert sliki
    IF p_images IS NOT NULL THEN
        FOREACH v_image IN ARRAY p_images
        LOOP
            INSERT INTO productimages (product_id, image_url)
            VALUES (v_product_id, v_image);
        END LOOP;
    END IF;

    RAISE NOTICE 'Produktot e dodaden so ID: %', v_product_id;
end;
$$;

Управување со кошничка

Оваа функционалност овозможува додавање, бришење и ажурирање на продукти во кошничката.

Функција fn_validate_product_stock

Проверува дали постои доволна количина од избраниот продукт за извршување на бараната операција.

CREATE OR REPLACE FUNCTION fn_validate_product_stock(
    p_product_id INT,
    p_quantity   INT
)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE all_stock INT;
BEGIN
    SELECT quantity INTO all_stock
    FROM product
    WHERE product_id = p_product_id AND is_active = TRUE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produktot ne postoi ili ne e aktiven';
    END IF;

    IF p_quantity > all_stock THEN
        RAISE EXCEPTION 'Nema dovolno zaliha. Barate: %, dostapno: %', p_quantity, all_stock;
    END IF;
END;
$$;

Функција fn_calc_cart_total

Ја пресметува вкупната вредност на кошничката.

CREATE OR REPLACE FUNCTION fn_calc_cart_total(p_cart_id INT)
RETURNS INT LANGUAGE plpgsql AS $$
DECLARE v_total INT;
BEGIN
    SELECT COALESCE(SUM(quantity * price_at_time), 0)
    INTO v_total
    FROM cartitems
    WHERE cart_id = p_cart_id;

    RETURN v_total;
END;
$$;

Процедура add_to_cart

Додава продукт во кошничката.

CREATE OR REPLACE PROCEDURE add_to_cart(
    p_cart_id    INT,
    p_product_id INT,
    p_quantity   INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_price NUMERIC;
BEGIN
    IF p_quantity <= 0 THEN
        RAISE EXCEPTION 'Kolicinata mora da e pogolema od 0';
    END IF;

    SELECT price INTO v_price
    FROM product
    WHERE product_id = p_product_id AND is_active = 1;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produktot ne postoi ili ne e aktiven';
    END IF;
   
   PERFORM fn_validate_product_stock(p_product_id, p_quantity);

    INSERT INTO cartitems (cart_id, product_id, quantity, price_at_time)
    VALUES (p_cart_id, p_product_id, p_quantity, v_price);
   
   --azhurirame total price
   UPDATE cart
    SET total_price = fn_calc_cart_total(p_cart_id)
    WHERE cart_id = p_cart_id;
END;
$$;

Процедура remove_from_cart

Отстранува продукт од кошничката.

CREATE OR REPLACE PROCEDURE remove_from_cart(
    p_cart_id    INT,
    p_product_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM cartitems
    WHERE cart_id = p_cart_id
      AND product_id = p_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produktot ne postoi vo kosnickata';
    END IF;
   
   --azhurirame total price
   UPDATE cart
    SET total_price = fn_calc_cart_total(p_cart_id)
    WHERE cart_id = p_cart_id;
END;
$$;

Процедура update_cart_quantity

Ја менува количината на постоечки продукт во кошничката.

CREATE OR REPLACE PROCEDURE update_cart_quantity(
    p_cart_id    INT,
    p_product_id INT,
    p_quantity   INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock INT;
BEGIN
    IF p_quantity <= 0 THEN
        RAISE EXCEPTION 'Kolicinata mora da e pogolema od 0';
    END IF;
   
   PERFORM fn_validate_product_stock(p_product_id, p_quantity);

    UPDATE cartitems
    SET quantity = p_quantity
    WHERE cart_id = p_cart_id
      AND product_id = p_product_id;
     
    --azhurirame total price
    UPDATE cart
    SET total_price = fn_calc_cart_total(p_cart_id)
    WHERE cart_id = p_cart_id;
END;
$$;

Купување на производи

Оваа функционалност овозможува корисникот да ја претвори содржината на кошничката во нарачка.

Функција fn_reduce_product_stock

Ја намалува количината на залиха на продукт по успешно купување.

CREATE OR REPLACE FUNCTION fn_reduce_product_stock(
    p_product_id INT,
    p_quantity INT
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN

    UPDATE product
    SET quantity = quantity - p_quantity
    WHERE product_id = p_product_id;
   --ako e 0 da go deaktivira

END;
$$;

Процедура buy_from_cart

Го реализира процесот на купување на сите производи кои се наоѓаат во кошничката на корисникот.

• Намалување на залиха - за секој производ во кошничката се повикува: fn_reduce_product_stock(...)
• Креирање на нарачка - во табелата Order се внесува нов запис
• Креирање на ставки на нарачка - сите производи од кошничката(CartItems) се префрлаат во табелата OrderItems.
• Ажурирање на статистика - во табелата ProductStats се зголемува полето: sells_count
• Евидентирање на плаќање - се креира запис во табелата Payment
• Креирање трансакции
• За секој продавач кој има продадени производи во нарачката се креира запис во табелата Transactions.
• Reset на кошничка

CREATE OR REPLACE PROCEDURE buy_from_cart(
    p_buyer_id       INT,
    p_payment_method VARCHAR,
    OUT p_order_id   INT,
    OUT p_payment_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_cart_id      INT;
    v_total        DECIMAL(10,2);
    v_item_count   INT;
    v_updated_rows INT;
BEGIN
    -- Najdi kosnicka
    SELECT cart_id INTO v_cart_id
    FROM cart
    WHERE user_id = p_buyer_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Kosnickata ne postoi';
    END IF;

    -- Dali ima stavki?
    SELECT COUNT(*) INTO v_item_count
    FROM cartitems
    WHERE cart_id = v_cart_id;

    IF v_item_count = 0 THEN
        RAISE EXCEPTION 'Kosnickata e prazna';
    END IF;
   
   --ja prezemame total price
   	select total_price INTO v_total
    from cart c
    where c.cart_id = v_cart_id;
    


	--1. Namali kolichina
	PERFORM fn_reduce_product_stock(
    ci.product_id,
    ci.quantity
	)
	FROM cartitems ci
	WHERE ci.cart_id = v_cart_id;




    -- 2. Kreiraj narachka
    INSERT INTO "Order" (buyer_id, cart_id, total_price, status, created_at)
    VALUES (p_buyer_id, v_cart_id, v_total, 'CONFIRMED', CURRENT_TIMESTAMP)
    RETURNING order_id INTO p_order_id;

    -- 3. Prefrli stavki vo OrderItems
    INSERT INTO orderitems (order_id, product_id, seller_id, price_at_time, quantity)
    SELECT
        p_order_id,
        ci.product_id,
        p.seller_id,
        ci.price_at_time,
        ci.quantity
    FROM cartitems ci
    JOIN product p ON p.product_id = ci.product_id
    WHERE ci.cart_id = v_cart_id;


    -- 4. Azuriraj sells_count
    UPDATE productstats ps
    SET sells_count = ps.sells_count + ci.quantity
    FROM cartitems ci
    WHERE ci.cart_id = v_cart_id
      AND ps.product_id = ci.product_id;

    -- 5. Plakjanje
    INSERT INTO payment (user_id, order_id, package_id, amount, payment_method, transaction_date)
    VALUES (p_buyer_id, p_order_id, NULL, v_total, p_payment_method, CURRENT_DATE)
    RETURNING payment_id INTO p_payment_id;

    -- 6. Transactions po prodavach
    INSERT INTO transactions (payment_id, seller_id, amount, status, transaction_date)
    SELECT
        p_payment_id,
        oi.seller_id,
        SUM(oi.price_at_time * oi.quantity),
        'COMPLETED',
        CURRENT_DATE
    FROM orderitems oi
    WHERE oi.order_id = p_order_id
    GROUP BY oi.seller_id;

    -- 7. Isprazni kosnicka 
    DELETE FROM cartitems
    WHERE cart_id = v_cart_id;
   
   UPDATE cart
    SET total_price = fn_calc_cart_total(p_cart_id)
    WHERE cart_id = v_cart_id;

END;
$$;

Оставање рецензија за купен производ

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

Функција: fn_can_user_review_product

Функцијата проверува дали корисникот има право да остави рецензија за одреден производ.

Корисник може да остави рецензија на производ ако ги исполнува следните услови:

• Нема веќе има оставено рецензија за истиот производ.
• Има успешна нарачка што содржи го производот.
• Статусот на нарачката е DELIVERED, односно производот е испорачан.

CREATE OR REPLACE FUNCTION fn_can_user_review_product(
    p_user_id INT,
    p_product_id INT
)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN

    IF EXISTS (
        SELECT 1
        FROM review
        WHERE buyer_id = p_user_id
          AND product_id = p_product_id
    ) THEN
        RAISE EXCEPTION 'You have already reviewed this product.';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM orderitems oi
        JOIN "order" o ON o.order_id = oi.order_id
        WHERE o.buyer_id = p_user_id
          AND oi.product_id = p_product_id
          AND o.status = 'DELIVERED'
    ) THEN
        RAISE EXCEPTION 'Only users who purchased and received the product can leave a review.';
    END IF;

    RETURN 1;

END;
$$;

Процедура: sp_add_review

Процедурата се користи за додавање нова рецензија за производ.

CREATE OR REPLACE PROCEDURE sp_add_review(
    p_user_id INT,
    p_product_id INT,
    p_rating INT,
    p_comment TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_seller_id INT;
BEGIN

    PERFORM fn_can_user_review_product(p_user_id, p_product_id);

    IF p_rating NOT BETWEEN 1 AND 5 THEN
        RAISE EXCEPTION 'Rating must be between 1 and 5.';
    END IF;

    SELECT seller_id
    INTO v_seller_id
    FROM product
    WHERE product_id = p_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product with id % does not exist.', p_product_id;
    END IF;

    INSERT INTO review (product_id, buyer_id, seller_id, rating, comment, created_at)
    VALUES (p_product_id, p_user_id,v_seller_id, p_rating, p_comment, NOW());

END;
$$;

Тригери

trg_notify_payment_success

Цел: автоматски после успешно плаќање, генерира нотификација до корисникот дека плаќањето е успешно завршено.

{{{CREATE OR REPLACE FUNCTION fn_notify_payment_success() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN

INSERT INTO notifications (user_id, type, title, message, order_id) VALUES (

NEW.user_id, 'PAYMENT', 'Uspeshno plakjanje',

'Vashata narachka #'

NEW.order_id

' e uspeshno platena.',

NEW.order_id

); RETURN NEW;

END; $$;

CREATE TRIGGER trg_notify_payment_success AFTER INSERT ON payment FOR EACH ROW EXECUTE FUNCTION fn_notify_payment_success(); }}}

trg_notify_order_status

Цел: известување на купувачот кога ќе се промени статусот на нарачката.

CREATE OR REPLACE FUNCTION fn_notify_order_status_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.status IS NOT DISTINCT FROM NEW.status THEN
        RETURN NEW;
    END IF;

    INSERT INTO notifications (user_id, type, title, message, order_id)
    VALUES (
        NEW.buyer_id,
        'ORDER_STATUS',
        'Promena na status',
        'Narachkata #' || NEW.order_id || ' e sega: ' || NEW.status || '.',
        NEW.order_id
    );

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_notify_order_status
AFTER UPDATE OF status ON "order"
FOR EACH ROW
EXECUTE FUNCTION fn_notify_order_status_change();

trg_prevent_over_selling

Цел: спречување на продажба на производи за кои нема доволно залиха(двојна нарачка).

CREATE OR REPLACE FUNCTION trg_prevent_over_selling()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock INT;
BEGIN

    -- земи тековна залиха
    SELECT quantity
    INTO v_stock
    FROM product
    WHERE product_id = NEW.product_id;

    -- ако нема продукт
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % does not exist', NEW.product_id;
    END IF;

    -- ако нема доволно залиха
    IF v_stock < NEW.quantity THEN
        RAISE EXCEPTION
        'Not enough stock for product %. Available: %, Requested: %',
        NEW.product_id, v_stock, NEW.quantity;
    END IF;

    RETURN NEW;
END;
$$;