DatabaseProgramming: blinkbuy_phase4.sql

-- ============================================================
-- 1. fn_create_order
-- Креира CART order и враќа order_id.
CREATE OR REPLACE FUNCTION fn_create_order(
    p_user_id BIGINT
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id BIGINT;
    v_shipping_method_id BIGINT;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM users u
        JOIN user_roles ur ON ur.user_id = u.user_id
        JOIN roles r ON r.role_id = ur.role_id
        WHERE u.user_id = p_user_id
          AND u.status = 'ACTIVE'
          AND r.role_name = 'CUSTOMER'
    ) THEN
        RAISE EXCEPTION 'User % does not exist, is not ACTIVE, or is not CUSTOMER.', p_user_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.user_id = p_user_id
          AND o.status = 'CART'
    ) THEN
        RAISE EXCEPTION 'User % already has an active CART order.', p_user_id;
    END IF;

    SELECT sm.method_id
    INTO v_shipping_method_id
    FROM shipping_methods sm
    ORDER BY sm.cost ASC, sm.method_id ASC
    LIMIT 1;

    IF v_shipping_method_id IS NULL THEN
        RAISE EXCEPTION 'No shipping method exists. Cannot create order.';
    END IF;

    INSERT INTO orders (
        user_id,
        shipping_method_id,
        order_date,
        total_amount,
        status
    )
    VALUES (
        p_user_id,
        v_shipping_method_id,
        NULL,
        NULL,
        'CART'
    )
    RETURNING order_id INTO v_order_id;

    RETURN v_order_id;
END;
$$;


-- ============================================================
-- 2. pr_add_order_item
-- Додава или зголемува item во customer cart.
CREATE OR REPLACE PROCEDURE pr_add_order_item(
    p_user_id BIGINT,
    p_variant_id BIGINT,
    p_quantity INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id BIGINT;
    v_current_price NUMERIC(12, 2);
    v_stock_total INT;
    v_existing_cart_quantity INT;
    v_price_history_id BIGINT;
BEGIN
    IF p_quantity IS NULL OR p_quantity <= 0 THEN
        RAISE EXCEPTION 'Quantity must be greater than 0.';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM users u
        JOIN user_roles ur ON ur.user_id = u.user_id
        JOIN roles r ON r.role_id = ur.role_id
        WHERE u.user_id = p_user_id
          AND u.status = 'ACTIVE'
          AND r.role_name = 'CUSTOMER'
    ) THEN
        RAISE EXCEPTION 'User % does not exist, is not ACTIVE, or is not CUSTOMER.', p_user_id;
    END IF;

    SELECT
        pv.price,
        pv.stock_total
    INTO
        v_current_price,
        v_stock_total
    FROM product_variants pv
    WHERE pv.variant_id = p_variant_id;

    IF v_current_price IS NULL THEN
        RAISE EXCEPTION 'Product variant % does not exist.', p_variant_id;
    END IF;

    SELECT o.order_id
    INTO v_order_id
    FROM orders o
    WHERE o.user_id = p_user_id
      AND o.status = 'CART'
    ORDER BY o.order_id DESC
    LIMIT 1;

    IF v_order_id IS NULL THEN
        v_order_id := fn_create_order(p_user_id);
    END IF;

    SELECT COALESCE(SUM(oi.quantity), 0)
    INTO v_existing_cart_quantity
    FROM order_items oi
    WHERE oi.order_id = v_order_id
      AND oi.variant_id = p_variant_id;

    IF v_existing_cart_quantity + p_quantity > v_stock_total THEN
        RAISE EXCEPTION
            'Not enough stock for variant %. Requested total %, available %.',
            p_variant_id,
            v_existing_cart_quantity + p_quantity,
            v_stock_total;
    END IF;

    SELECT pph.history_id
    INTO v_price_history_id
    FROM product_price_history pph
    WHERE pph.variant_id = p_variant_id
    ORDER BY pph.history_id DESC
    LIMIT 1;

    IF v_price_history_id IS NULL THEN
        INSERT INTO product_price_history (
            variant_id,
            old_price,
            new_price,
            change_date
        )
        VALUES (
            p_variant_id,
            v_current_price,
            v_current_price,
            NOW()
        )
        RETURNING history_id INTO v_price_history_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM order_items oi
        WHERE oi.order_id = v_order_id
          AND oi.variant_id = p_variant_id
    ) THEN
        UPDATE order_items
        SET quantity = quantity + p_quantity,
            unit_price = v_current_price,
            price_history_id = v_price_history_id
        WHERE order_item_id = (
            SELECT MIN(oi.order_item_id)
            FROM order_items oi
            WHERE oi.order_id = v_order_id
              AND oi.variant_id = p_variant_id
        );
    ELSE
        INSERT INTO order_items (
            order_id,
            price_history_id,
            variant_id,
            quantity,
            unit_price
        )
        VALUES (
            v_order_id,
            v_price_history_id,
            p_variant_id,
            p_quantity,
            v_current_price
        );
    END IF;
END;
$$;


-- ============================================================
-- 3. pr_record_loyalty_transaction
-- Запишува loyalty трансакција и ажурира balance.
CREATE OR REPLACE PROCEDURE pr_record_loyalty_transaction(
    p_loyalty_account_id BIGINT,
    p_order_id BIGINT,
    p_payment_id BIGINT,
    p_points_change INT,
    p_transaction_type VARCHAR,
    p_description TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_loyalty_account_id IS NULL OR p_points_change = 0 THEN
        RETURN;
    END IF;

    UPDATE loyalty_accounts
    SET current_balance = GREATEST(current_balance + p_points_change, 0),
        last_updated = NOW()
    WHERE loyalty_account_id = p_loyalty_account_id;

    INSERT INTO loyalty_history (
        loyalty_account_id,
        order_id,
        payment_id,
        points_change,
        transaction_type,
        description,
        created_at
    )
    VALUES (
        p_loyalty_account_id,
        p_order_id,
        p_payment_id,
        p_points_change,
        p_transaction_type,
        p_description,
        NOW()
    );
END;
$$;


-- ============================================================
-- 4. pr_recalculate_variant_stock
-- Го усогласува stock-от според physical product_instances.
CREATE OR REPLACE PROCEDURE pr_recalculate_variant_stock(
    p_variant_ids BIGINT[]
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_variant_ids IS NULL OR cardinality(p_variant_ids) = 0 THEN
        RETURN;
    END IF;

    WITH affected_inventory AS (
        SELECT
            ii.variant_id,
            ii.warehouse_id
        FROM inventory_items ii
        WHERE ii.variant_id = ANY(p_variant_ids)
    ),
    counts AS (
        SELECT
            ai.variant_id,
            ai.warehouse_id,
            COALESCE(COUNT(pi.instance_id), 0)::int AS available_quantity
        FROM affected_inventory ai
        LEFT JOIN product_instances pi
          ON pi.variant_id = ai.variant_id
         AND pi.warehouse_id = ai.warehouse_id
         AND pi.status = 'AVAILABLE'
        GROUP BY ai.variant_id, ai.warehouse_id
    )
    UPDATE inventory_items ii
    SET quantity = c.available_quantity
    FROM counts c
    WHERE ii.variant_id = c.variant_id
      AND ii.warehouse_id = c.warehouse_id;

    WITH totals AS (
        SELECT
            ii.variant_id,
            COALESCE(SUM(ii.quantity), 0)::int AS total_stock
        FROM inventory_items ii
        WHERE ii.variant_id = ANY(p_variant_ids)
        GROUP BY ii.variant_id
    )
    UPDATE product_variants pv
    SET stock_total = t.total_stock
    FROM totals t
    WHERE pv.variant_id = t.variant_id;
END;
$$;


-- ============================================================
-- 5. pr_pay_order
-- Извршува payment и loyalty пресметка за PLACED order.
CREATE OR REPLACE PROCEDURE pr_pay_order(
    p_order_id BIGINT,
    p_payment_method VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id BIGINT;
    v_order_status VARCHAR;
    v_total_amount NUMERIC(12,2);

    v_coupon_discount NUMERIC(12,2);
    v_amount_after_coupon NUMERIC(12,2);

    v_loyalty_account_id BIGINT;
    v_current_balance INT;
    v_calculated_redeem_points INT;
    v_actual_redeem_points INT := 0;

    v_payment_amount NUMERIC(12,2);
    v_payment_id BIGINT;
    v_payment_status VARCHAR;
    v_earn_points INT;
BEGIN
    SELECT
        user_id,
        status,
        total_amount
    INTO
        v_user_id,
        v_order_status,
        v_total_amount
    FROM orders
    WHERE order_id = p_order_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist.', p_order_id;
    END IF;

    IF v_order_status <> 'PLACED' THEN
        RAISE EXCEPTION 'Order % cannot be paid from status %.', p_order_id, v_order_status;
    END IF;

    IF v_total_amount IS NULL OR v_total_amount <= 0 THEN
        RAISE EXCEPTION 'Order % has invalid total_amount: %.', p_order_id, v_total_amount;
    END IF;

    SELECT COALESCE(SUM(amount_saved), 0)::numeric(12,2)
    INTO v_coupon_discount
    FROM order_discounts
    WHERE order_id = p_order_id;

    v_amount_after_coupon := GREATEST(v_total_amount - v_coupon_discount, 0)::numeric(12,2);

    IF v_amount_after_coupon <= 0 THEN
        RAISE EXCEPTION 'Order % has invalid amount after coupon discount: %.',
            p_order_id, v_amount_after_coupon;
    END IF;

    SELECT
        loyalty_account_id,
        current_balance
    INTO
        v_loyalty_account_id,
        v_current_balance
    FROM loyalty_accounts
    WHERE user_id = v_user_id
    FOR UPDATE;

    IF v_loyalty_account_id IS NOT NULL AND COALESCE(v_current_balance, 0) > 0 THEN
        v_calculated_redeem_points := FLOOR(v_amount_after_coupon / 500);

        IF v_calculated_redeem_points >= 1 THEN
            v_actual_redeem_points := LEAST(v_current_balance, v_calculated_redeem_points);
        END IF;
    END IF;

    v_payment_amount := GREATEST(v_amount_after_coupon - v_actual_redeem_points, 1)::numeric(12,2);

    SELECT
        payment_id,
        payment_status
    INTO
        v_payment_id,
        v_payment_status
    FROM payments
    WHERE transaction_id = 'TXN-' || p_order_id || '-MAIN'
    LIMIT 1
    FOR UPDATE;

    IF v_payment_id IS NOT NULL AND v_payment_status = 'SUCCESS' THEN
        RAISE EXCEPTION 'Order % already has a successful main payment.', p_order_id;
    ELSIF v_payment_id IS NOT NULL THEN
        UPDATE payments
        SET payment_method = p_payment_method,
            amount = v_payment_amount,
            payment_status = 'SUCCESS',
            payment_date = NOW()
        WHERE payment_id = v_payment_id;
    ELSE
        INSERT INTO payments (
            order_id,
            payment_method,
            amount,
            payment_status,
            transaction_id,
            payment_date
        )
        VALUES (
            p_order_id,
            p_payment_method,
            v_payment_amount,
            'SUCCESS',
            'TXN-' || p_order_id || '-MAIN',
            NOW()
        )
        RETURNING payment_id INTO v_payment_id;
    END IF;

    UPDATE orders
    SET status = 'PAID'
    WHERE order_id = p_order_id;

    IF v_loyalty_account_id IS NULL THEN
        INSERT INTO loyalty_accounts (
            user_id,
            current_balance,
            last_updated
        )
        VALUES (
            v_user_id,
            0,
            NOW()
        )
        RETURNING loyalty_account_id INTO v_loyalty_account_id;
    END IF;

    IF v_actual_redeem_points > 0 THEN
        CALL pr_record_loyalty_transaction(
            v_loyalty_account_id,
            p_order_id,
            v_payment_id,
            -v_actual_redeem_points,
            'REDEEM',
            'Loyalty points redeemed for payment of order #' || p_order_id
        );
    END IF;

    v_earn_points := FLOOR(v_payment_amount / 100);

    IF v_earn_points > 0 THEN
        CALL pr_record_loyalty_transaction(
            v_loyalty_account_id,
            p_order_id,
            v_payment_id,
            v_earn_points,
            'EARN',
            'Loyalty points earned from payment for order #' || p_order_id
        );
    END IF;

    RAISE NOTICE 'Order % paid successfully. Coupon discount: %, redeemed points: %, payment amount: %, earned points: %.',
        p_order_id, v_coupon_discount, v_actual_redeem_points, v_payment_amount, v_earn_points;
END;
$$;


-- ============================================================
-- 6. pr_cancel_order
-- Откажува order пред да заврши physical delivery flow.
CREATE OR REPLACE PROCEDURE pr_cancel_order(
    p_order_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id BIGINT;
    v_order_status VARCHAR;

    v_payment_id BIGINT;
    v_payment_status VARCHAR;

    v_loyalty_account_id BIGINT;
    v_earned_points INT := 0;
    v_redeemed_points INT := 0;

    v_released_instances INT := 0;
BEGIN
    SELECT
        user_id,
        status
    INTO
        v_user_id,
        v_order_status
    FROM orders
    WHERE order_id = p_order_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist.', p_order_id;
    END IF;

    IF v_order_status NOT IN ('CART', 'PLACED', 'PAID') THEN
        RAISE EXCEPTION 'Order % cannot be cancelled from status %.', p_order_id, v_order_status;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM shipments
        WHERE order_id = p_order_id
          AND status IN ('SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'RETURNED')
    ) THEN
        RAISE EXCEPTION 'Order % cannot be cancelled because shipment is already physically shipped or returned.',
            p_order_id;
    END IF;

    DROP TABLE IF EXISTS tmp_cancel_instances;

    CREATE TEMP TABLE tmp_cancel_instances ON COMMIT DROP AS
    SELECT DISTINCT
        pi.instance_id,
        pi.variant_id,
        pi.warehouse_id
    FROM shipments s
    JOIN shipment_items si ON si.shipment_id = s.shipment_id
    JOIN product_instances pi ON pi.instance_id = si.instance_id
    WHERE s.order_id = p_order_id;

    SELECT COUNT(*)
    INTO v_released_instances
    FROM tmp_cancel_instances;

    UPDATE product_instances pi
    SET status = 'AVAILABLE'
    FROM tmp_cancel_instances tci
    WHERE pi.instance_id = tci.instance_id;

    DELETE FROM shipment_items si
    USING shipments s
    WHERE si.shipment_id = s.shipment_id
      AND s.order_id = p_order_id;

    UPDATE shipments
    SET
        status = 'FAILED',
        tracking_number = COALESCE(tracking_number, 'CANCEL-TRK-' || shipment_id),
        shipped_date = COALESCE(shipped_date, NOW()),
        estimated_arrival = COALESCE(estimated_arrival, CURRENT_DATE),
        delivered_date = NULL
    WHERE order_id = p_order_id
      AND status IN ('PENDING', 'PROCESSING');

    SELECT
        payment_id,
        payment_status
    INTO
        v_payment_id,
        v_payment_status
    FROM payments
    WHERE transaction_id = 'TXN-' || p_order_id || '-MAIN'
    LIMIT 1
    FOR UPDATE;

    IF v_order_status = 'PAID' THEN
        IF v_payment_id IS NULL OR v_payment_status <> 'SUCCESS' THEN
            RAISE EXCEPTION 'Paid order % does not have a SUCCESS main payment to refund.', p_order_id;
        END IF;

        UPDATE payments
        SET payment_status = 'REFUNDED',
            payment_date = NOW()
        WHERE payment_id = v_payment_id;

        SELECT loyalty_account_id
        INTO v_loyalty_account_id
        FROM loyalty_accounts
        WHERE user_id = v_user_id
        FOR UPDATE;

        IF v_loyalty_account_id IS NOT NULL THEN
            SELECT COALESCE(SUM(points_change), 0)::int
            INTO v_earned_points
            FROM loyalty_history
            WHERE order_id = p_order_id
              AND payment_id = v_payment_id
              AND transaction_type = 'EARN';

            SELECT COALESCE(SUM(ABS(points_change)), 0)::int
            INTO v_redeemed_points
            FROM loyalty_history
            WHERE order_id = p_order_id
              AND payment_id = v_payment_id
              AND transaction_type = 'REDEEM';

            IF v_earned_points > 0 THEN
                CALL pr_record_loyalty_transaction(
                    v_loyalty_account_id,
                    p_order_id,
                    v_payment_id,
                    -v_earned_points,
                    'REFUND',
                    'Loyalty points removed because order #' || p_order_id || ' was cancelled and refunded.'
                );
            END IF;

            IF v_redeemed_points > 0 THEN
                CALL pr_record_loyalty_transaction(
                    v_loyalty_account_id,
                    p_order_id,
                    v_payment_id,
                    v_redeemed_points,
                    'ADJUSTMENT',
                    'Redeemed loyalty points returned because order #' || p_order_id || ' was cancelled.'
                );
            END IF;
        END IF;
    ELSIF v_order_status = 'PLACED'
       AND v_payment_id IS NOT NULL
       AND v_payment_status = 'PENDING' THEN

        UPDATE payments
        SET payment_status = 'FAILED',
            payment_date = NOW()
        WHERE payment_id = v_payment_id;
    END IF;

    UPDATE orders
    SET status = 'CANCELLED'
    WHERE order_id = p_order_id;

    IF v_released_instances > 0 THEN
        CALL pr_recalculate_variant_stock(
            ARRAY(
                SELECT DISTINCT variant_id
                FROM tmp_cancel_instances
            )
        );
    END IF;

    RAISE NOTICE 'Order % cancelled successfully. Released instances: %, refunded payment: %, removed earned points: %, returned redeemed points: %.',
        p_order_id,
        v_released_instances,
        CASE WHEN v_order_status = 'PAID' THEN 'YES' ELSE 'NO' END,
        v_earned_points,
        v_redeemed_points;
END;
$$;


-- ============================================================
-- 7. pr_courier_accept_shipment
-- Го префрла accepted shipment од PENDING во PROCESSING.
CREATE OR REPLACE PROCEDURE pr_courier_accept_shipment(
    p_shipment_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id BIGINT;
    v_order_status VARCHAR(255);
    v_shipment_status VARCHAR(20);
    v_initial_courier_id BIGINT;
    v_accepted_courier_id BIGINT;
BEGIN
    SELECT
        s.order_id,
        s.status,
        s.courier_id,
        o.status
    INTO
        v_order_id,
        v_shipment_status,
        v_initial_courier_id,
        v_order_status
    FROM shipments s
    JOIN orders o ON o.order_id = s.order_id
    WHERE s.shipment_id = p_shipment_id
    FOR UPDATE OF s;

    IF v_order_id IS NULL THEN
        RAISE EXCEPTION 'Shipment % does not exist.', p_shipment_id;
    END IF;

    IF v_shipment_status <> 'PENDING' THEN
        RAISE EXCEPTION 'Shipment % must be PENDING. Current status: %.',
            p_shipment_id, v_shipment_status;
    END IF;

    IF v_order_status <> 'PAID' THEN
        RAISE EXCEPTION 'Order % must be PAID before courier acceptance. Current status: %.',
            v_order_id, v_order_status;
    END IF;

    WITH courier_candidates AS (
        SELECT
            u.user_id AS courier_id,
            random() AS acceptance_roll,
            CASE
                WHEN u.user_id = v_initial_courier_id THEN 0
                WHEN u.user_id > v_initial_courier_id THEN 1
                ELSE 2
            END AS priority_group
        FROM users u
        JOIN user_roles ur ON ur.user_id = u.user_id
        JOIN roles r ON r.role_id = ur.role_id
        WHERE r.role_name = 'COURIER'
          AND u.status = 'ACTIVE'
    )
    SELECT courier_id
    INTO v_accepted_courier_id
    FROM courier_candidates
    WHERE acceptance_roll < 0.80
    ORDER BY
        priority_group,
        courier_id
    LIMIT 1;

    IF v_accepted_courier_id IS NULL THEN
        RAISE EXCEPTION 'No courier accepted shipment %.', p_shipment_id;
    END IF;

    UPDATE shipments
    SET
        courier_id = v_accepted_courier_id,
        status = 'PROCESSING'
    WHERE shipment_id = p_shipment_id;
END;
$$;


-- ============================================================
-- 8. pr_process_shipment
-- Алоцира physical product_instances и го испраќа order-от.
CREATE OR REPLACE PROCEDURE pr_process_shipment(
    p_shipment_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id BIGINT;
    v_order_status VARCHAR;
    v_shipping_method_id BIGINT;
    v_shipment_status VARCHAR;
    v_tracking_number VARCHAR;
    v_estimated_arrival DATE;
    v_allocated_count INT := 0;
BEGIN
    SELECT
        s.order_id,
        s.status,
        o.status,
        o.shipping_method_id
    INTO
        v_order_id,
        v_shipment_status,
        v_order_status,
        v_shipping_method_id
    FROM shipments s
    JOIN orders o ON o.order_id = s.order_id
    WHERE s.shipment_id = p_shipment_id
    FOR UPDATE OF s;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Shipment % does not exist.', p_shipment_id;
    END IF;

    IF v_shipment_status <> 'PROCESSING' THEN
        RAISE EXCEPTION 'Shipment % cannot be processed from status %.',
            p_shipment_id, v_shipment_status;
    END IF;

    IF v_order_status <> 'PAID' THEN
        RAISE EXCEPTION 'Shipment % cannot be processed because order % has status %.',
            p_shipment_id, v_order_id, v_order_status;
    END IF;

    IF v_shipping_method_id IS NULL THEN
        RAISE EXCEPTION 'Order % has no shipping_method_id.', v_order_id;
    END IF;

    DROP TABLE IF EXISTS tmp_needed_units;
    DROP TABLE IF EXISTS tmp_available_instances;
    DROP TABLE IF EXISTS tmp_matched_instances;

    CREATE TEMP TABLE tmp_needed_units ON COMMIT DROP AS
    WITH shipped_counts AS (
        SELECT
            order_item_id,
            COUNT(*) AS shipped_count
        FROM shipment_items
        GROUP BY order_item_id
    ),
    expanded_units AS (
        SELECT
            oi.order_item_id,
            oi.variant_id,
            gs.unit_no
        FROM order_items oi
        LEFT JOIN shipped_counts sc ON sc.order_item_id = oi.order_item_id
        CROSS JOIN LATERAL generate_series(
            1,
            GREATEST(oi.quantity - COALESCE(sc.shipped_count, 0), 0)
        ) AS gs(unit_no)
        WHERE oi.order_id = v_order_id
    )
    SELECT
        order_item_id,
        variant_id,
        ROW_NUMBER() OVER (
            PARTITION BY variant_id
            ORDER BY order_item_id, unit_no
        ) AS variant_unit_rn
    FROM expanded_units;

    IF NOT EXISTS (SELECT 1 FROM tmp_needed_units) THEN
        RAISE EXCEPTION 'Shipment % has no remaining order items to process.', p_shipment_id;
    END IF;

    CREATE TEMP TABLE tmp_available_instances ON COMMIT DROP AS
    SELECT
        pi.instance_id,
        pi.variant_id,
        pi.warehouse_id,
        ROW_NUMBER() OVER (
            PARTITION BY pi.variant_id
            ORDER BY pi.instance_id
        ) AS variant_unit_rn
    FROM product_instances pi
    WHERE pi.status = 'AVAILABLE'
      AND EXISTS (
          SELECT 1
          FROM tmp_needed_units nu
          WHERE nu.variant_id = pi.variant_id
      )
      AND NOT EXISTS (
          SELECT 1
          FROM shipment_items si
          WHERE si.instance_id = pi.instance_id
      );

    CREATE TEMP TABLE tmp_matched_instances ON COMMIT DROP AS
    SELECT
        nu.order_item_id,
        ai.instance_id,
        ai.variant_id,
        ai.warehouse_id
    FROM tmp_needed_units nu
    JOIN tmp_available_instances ai
      ON ai.variant_id = nu.variant_id
     AND ai.variant_unit_rn = nu.variant_unit_rn;

    IF (SELECT COUNT(*) FROM tmp_matched_instances) < (SELECT COUNT(*) FROM tmp_needed_units) THEN
        RAISE EXCEPTION 'Not enough available product instances to fully process shipment %.', p_shipment_id;
    END IF;

    INSERT INTO shipment_items (
        shipment_id,
        order_item_id,
        instance_id
    )
    SELECT
        p_shipment_id,
        order_item_id,
        instance_id
    FROM tmp_matched_instances;

    UPDATE product_instances pi
    SET status = 'SHIPPED'
    FROM tmp_matched_instances mi
    WHERE pi.instance_id = mi.instance_id;

    SELECT COUNT(*)
    INTO v_allocated_count
    FROM tmp_matched_instances;

    v_tracking_number := 'TRK-' || p_shipment_id || '-' || EXTRACT(EPOCH FROM NOW())::BIGINT;

    SELECT CURRENT_DATE + sm.estimated_days
    INTO v_estimated_arrival
    FROM shipping_methods sm
    WHERE sm.method_id = v_shipping_method_id;

    IF v_estimated_arrival IS NULL THEN
        RAISE EXCEPTION 'Shipping method % does not exist.', v_shipping_method_id;
    END IF;

    UPDATE shipments
    SET
        tracking_number = v_tracking_number,
        shipped_date = NOW(),
        estimated_arrival = v_estimated_arrival,
        delivered_date = NULL,
        status = 'SHIPPED'
    WHERE shipment_id = p_shipment_id;

    UPDATE orders
    SET status = 'SHIPPED'
    WHERE order_id = v_order_id;

    CALL pr_recalculate_variant_stock(
        ARRAY(
            SELECT DISTINCT variant_id
            FROM tmp_matched_instances
        )
    );

    RAISE NOTICE 'Shipment % processed successfully. Allocated % physical instances. Shipment and order are now SHIPPED.',
        p_shipment_id, v_allocated_count;
END;
$$;


-- ============================================================
-- 9. fn_is_order_returnable
-- Проверува дали order ги исполнува return условите.
CREATE OR REPLACE FUNCTION fn_is_order_returnable(
    p_order_id BIGINT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.order_id = p_order_id
          AND o.status = 'COMPLETED'
          AND EXISTS (
              SELECT 1
              FROM shipments s
              WHERE s.order_id = o.order_id
                AND s.status = 'DELIVERED'
          )
          AND EXISTS (
              SELECT 1
              FROM shipments s
              JOIN shipment_items si ON si.shipment_id = s.shipment_id
              WHERE s.order_id = o.order_id
          )
          AND EXISTS (
              SELECT 1
              FROM payments p
              WHERE p.order_id = o.order_id
                AND p.transaction_id = 'TXN-' || o.order_id || '-MAIN'
                AND p.payment_status = 'SUCCESS'
          )
    );
END;
$$;


-- ============================================================
-- 10. pr_process_return
-- Процесира return и refund за completed order.
CREATE OR REPLACE PROCEDURE pr_process_return(
    p_order_id BIGINT,
    p_restock BOOLEAN DEFAULT TRUE
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id BIGINT;
    v_payment_id BIGINT;
    v_loyalty_account_id BIGINT;
    v_earned_points INT := 0;
    v_redeemed_points INT := 0;
    v_returned_instances INT := 0;
BEGIN
    IF fn_is_order_returnable(p_order_id) = FALSE THEN
        RAISE EXCEPTION 'Order % is not returnable. It must be COMPLETED, delivered, physically shipped, and successfully paid.',
            p_order_id;
    END IF;

    SELECT user_id
    INTO v_user_id
    FROM orders
    WHERE order_id = p_order_id
    FOR UPDATE;

    SELECT payment_id
    INTO v_payment_id
    FROM payments
    WHERE order_id = p_order_id
      AND transaction_id = 'TXN-' || p_order_id || '-MAIN'
      AND payment_status = 'SUCCESS'
    FOR UPDATE;

    DROP TABLE IF EXISTS tmp_return_instances;

    CREATE TEMP TABLE tmp_return_instances ON COMMIT DROP AS
    SELECT DISTINCT
        pi.instance_id,
        pi.variant_id,
        pi.warehouse_id
    FROM shipments s
    JOIN shipment_items si ON si.shipment_id = s.shipment_id
    JOIN product_instances pi ON pi.instance_id = si.instance_id
    WHERE s.order_id = p_order_id
      AND s.status = 'DELIVERED';

    SELECT COUNT(*)
    INTO v_returned_instances
    FROM tmp_return_instances;

    IF v_returned_instances = 0 THEN
        RAISE EXCEPTION 'Order % has no physical product instances to return.', p_order_id;
    END IF;

    UPDATE shipments
    SET status = 'RETURNED'
    WHERE order_id = p_order_id
      AND status = 'DELIVERED';

    UPDATE payments
    SET payment_status = 'REFUNDED',
        payment_date = NOW()
    WHERE payment_id = v_payment_id;

    SELECT loyalty_account_id
    INTO v_loyalty_account_id
    FROM loyalty_accounts
    WHERE user_id = v_user_id
    FOR UPDATE;

    IF v_loyalty_account_id IS NOT NULL THEN
        SELECT COALESCE(SUM(points_change), 0)::int
        INTO v_earned_points
        FROM loyalty_history
        WHERE order_id = p_order_id
          AND payment_id = v_payment_id
          AND transaction_type = 'EARN';

        SELECT COALESCE(SUM(ABS(points_change)), 0)::int
        INTO v_redeemed_points
        FROM loyalty_history
        WHERE order_id = p_order_id
          AND payment_id = v_payment_id
          AND transaction_type = 'REDEEM';

        IF v_earned_points > 0 THEN
            CALL pr_record_loyalty_transaction(
                v_loyalty_account_id,
                p_order_id,
                v_payment_id,
                -v_earned_points,
                'REFUND',
                'Loyalty points removed because order #' || p_order_id || ' was returned and refunded.'
            );
        END IF;

        IF v_redeemed_points > 0 THEN
            CALL pr_record_loyalty_transaction(
                v_loyalty_account_id,
                p_order_id,
                v_payment_id,
                v_redeemed_points,
                'ADJUSTMENT',
                'Redeemed loyalty points returned because order #' || p_order_id || ' was returned.'
            );
        END IF;
    END IF;

    UPDATE product_instances pi
    SET status = CASE
        WHEN p_restock = TRUE THEN 'AVAILABLE'
        ELSE 'RETURNED'
    END
    FROM tmp_return_instances tri
    WHERE pi.instance_id = tri.instance_id;

    IF p_restock = TRUE THEN
        CALL pr_recalculate_variant_stock(
            ARRAY(
                SELECT DISTINCT variant_id
                FROM tmp_return_instances
            )
        );
    END IF;

    RAISE NOTICE 'Return processed for order %. Returned instances: %, restocked: %, refunded payment: %, removed earned points: %, returned redeemed points: %.',
        p_order_id,
        v_returned_instances,
        p_restock,
        v_payment_id,
        v_earned_points,
        v_redeemed_points;
END;
$$;


-- ============================================================
-- 11. trg_order_total_sync
-- Го синхронизира order total со order_items.
CREATE OR REPLACE FUNCTION fn_order_total_sync()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_order_id BIGINT;
    v_new_order_id BIGINT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_old_order_id := OLD.order_id;
        v_new_order_id := NULL;

    ELSIF TG_OP = 'INSERT' THEN
        v_old_order_id := NULL;
        v_new_order_id := NEW.order_id;

    ELSE
        v_old_order_id := OLD.order_id;
        v_new_order_id := NEW.order_id;
    END IF;

    IF v_old_order_id IS NOT NULL THEN
        UPDATE orders
        SET total_amount = CASE
            WHEN status = 'CART' THEN NULL
            ELSE (
                SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)::numeric(12,2)
                FROM order_items oi
                WHERE oi.order_id = v_old_order_id
            )
        END
        WHERE order_id = v_old_order_id;
    END IF;

    IF v_new_order_id IS NOT NULL
       AND (v_old_order_id IS NULL OR v_new_order_id <> v_old_order_id) THEN

        UPDATE orders
        SET total_amount = CASE
            WHEN status = 'CART' THEN NULL
            ELSE (
                SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)::numeric(12,2)
                FROM order_items oi
                WHERE oi.order_id = v_new_order_id
            )
        END
        WHERE order_id = v_new_order_id;
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

DROP FUNCTION IF EXISTS fn_sync_single_order_total(BIGINT);

DROP TRIGGER IF EXISTS trg_order_total_sync ON order_items;

CREATE TRIGGER trg_order_total_sync
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_order_total_sync();


-- ============================================================
-- 12. trg_log_order_status_change
-- Автоматски запишува order status промени.
CREATE OR REPLACE FUNCTION fn_log_order_status_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN

        INSERT INTO order_status_history (
            order_id,
            old_status,
            new_status,
            change_date
        )
        VALUES (
            OLD.order_id,
            OLD.status,
            NEW.status,
            NOW()
        );

    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_log_order_status_change ON orders;

CREATE TRIGGER trg_log_order_status_change
AFTER UPDATE OF status ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_log_order_status_change();


-- ============================================================
-- 13. trg_prevent_negative_inventory
-- Спречува негативна inventory quantity.
CREATE OR REPLACE FUNCTION fn_prevent_negative_inventory()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.quantity < 0 THEN
        RAISE EXCEPTION 'Inventory quantity cannot be negative. Variant %, warehouse %, attempted quantity %.',
            NEW.variant_id,
            NEW.warehouse_id,
            NEW.quantity;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_prevent_negative_inventory ON inventory_items;

CREATE TRIGGER trg_prevent_negative_inventory
BEFORE INSERT OR UPDATE OF quantity ON inventory_items
FOR EACH ROW
EXECUTE FUNCTION fn_prevent_negative_inventory();


-- ============================================================
-- 14. trg_audit_price_change
-- Запишува price changes во price history.
CREATE OR REPLACE FUNCTION fn_audit_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.price IS DISTINCT FROM NEW.price THEN
        INSERT INTO product_price_history (
            variant_id,
            old_price,
            new_price,
            change_date
        )
        VALUES (
            OLD.variant_id,
            OLD.price,
            NEW.price,
            NOW()
        );
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_audit_price_change ON product_variants;

CREATE TRIGGER trg_audit_price_change
AFTER UPDATE OF price ON product_variants
FOR EACH ROW
EXECUTE FUNCTION fn_audit_price_change();