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.name AND restaurant_id = v_restaurant_id;

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

    IF NEW.quantity <= 0 THEN
        RAISE EXCEPTION 'Invalid quantity for item "%": must be greater than 0', NEW.name;
    END IF;
   
    IF NEW.price <> v_menu_price THEN
        RAISE EXCEPTION 'Price mismatch for item "%": expected %, got %',
            NEW.name, v_menu_price, NEW.price;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS validate_preorder_price_trigger ON reservation_preordered_items;

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

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

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (21, 'California Roll', 1, 5.99); --price mismatch

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (21, 'Roll', 1, 8.99); --name mismatch

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

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (21, 'California Roll', 1, 8.99); --works