WikiStart: Validate preorder price trigger.txt

DROP TRIGGER IF EXISTS validate_preorder_price_trigger ON reservation_preordered_items;

CREATE OR REPLACE FUNCTION trg_validate_preorder_price()
RETURNS TRIGGER AS $$
DECLARE
    v_menu_price NUMERIC;
    v_restaurant_id INTEGER;
BEGIN
    SELECT restaurantid INTO v_restaurant_id
    FROM reservations
    WHERE reservationid = NEW.reservation_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Reservation ID % not found', NEW.reservation_id;
    END IF;

    SELECT price INTO v_menu_price
    FROM menus
    WHERE item_name = NEW.preordered_item_name AND restaurant_id = v_restaurant_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Menu item "%" not found for restaurant ID %', NEW.preordered_item_name, v_restaurant_id;
    END IF;

    IF NEW.quantity <= 0 THEN
        RAISE EXCEPTION 'Invalid quantity for item "%": must be greater than 0', NEW.preordered_item_name;
    END IF;

    IF NEW.price_at_order <> v_menu_price THEN
        RAISE EXCEPTION 'Price mismatch for item "%": expected %, got %',
            NEW.preordered_item_name, v_menu_price, NEW.price_at_order;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_preorder_price_trigger
BEFORE INSERT OR UPDATE ON reservation_preordered_items
FOR EACH ROW
EXECUTE FUNCTION trg_validate_preorder_price();

--Test cases

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (22, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'Roll', 1, 8.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 0, 8.99);