DatabaseProgramming: FAZA4.sql

-- TRIGGER 1 FUNCTION: fn_guard_closed_order

--Prevents adding or modifying OrderItem rows on orders
--that are already Completed or Cancelled.
 
CREATE OR REPLACE FUNCTION fn_guard_closed_order()
RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
    v_status TEXT;
BEGIN
    SELECT os.Status
      INTO v_status
      FROM "Order" o
      JOIN OrderStatus  os ON os.Id = o.StatusId
     WHERE o.Id = NEW.OrderId;
 
    IF v_status IN ('Completed', 'Cancelled') THEN
        RAISE EXCEPTION
            '[OrderItem] Order #% is "%". Cannot be modified.',
            NEW.OrderId, v_status;
    END IF;
 
    RETURN NEW;
END;
$$;
 
-- TRIGGER 1: trg_prevent_closed_order_edit
 
CREATE OR REPLACE TRIGGER trg_prevent_closed_order_edit
BEFORE INSERT OR UPDATE ON OrderItem
FOR EACH ROW
EXECUTE FUNCTION fn_guard_closed_order();
 
 
-- TRIGGER 2 FUNCTION: fn_deduct_inventory_on_finish
--When the kitchen marks an OrderItem as Finished = TRUE,
--automatically deducts the ingredients (preku consistOF) from StoredProduct inventory.
 
CREATE OR REPLACE FUNCTION fn_deduct_inventory_on_finish()
RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
    v_product_id   INT4;
    v_ingredient   RECORD;
    v_needed       INT4;
    v_available    INT4;
BEGIN
    IF OLD.Finished IS DISTINCT FROM NEW.Finished
       AND NEW.Finished = TRUE
    THEN
        v_product_id := NEW.MenuItemId;
 
        FOR v_ingredient IN
            SELECT co.Component  AS IngId,
                   co.Amount     AS AmtPerUnit
              FROM ConsistsOf co
             WHERE co.Parent = v_product_id
        LOOP
            v_needed := NEW.Quantity * v_ingredient.AmtPerUnit;
 
            SELECT COALESCE(SUM(sp.Quantity), 0)
              INTO v_available
              FROM StoredProduct sp
             WHERE sp.ProductId = v_ingredient.IngId;
 
            IF v_available < v_needed THEN
                RAISE WARNING
                    '[Inventory] ProductId % - needed: %, available: %. Stock will be set to 0.',
                    v_ingredient.IngId, v_needed, v_available;
            END IF;
 
            UPDATE StoredProduct
               SET Quantity = GREATEST(0, Quantity - v_needed)
             WHERE ProductId = v_ingredient.IngId
               AND Id = (
                     SELECT Id FROM StoredProduct
                      WHERE ProductId = v_ingredient.IngId
                      ORDER BY Id
                      LIMIT 1
                   );
        END LOOP;
    END IF;
 
    RETURN NEW;
END;
$$;
 
-- TRIGGER 2: trg_deduct_inventory_on_item_finish
 
CREATE OR REPLACE TRIGGER trg_deduct_inventory_on_item_finish
AFTER UPDATE OF Finished ON OrderItem
FOR EACH ROW
EXECUTE FUNCTION fn_deduct_inventory_on_finish();
 
 
-- TRIGGER 3 FUNCTION: fn_log_stock_change
--On every change to StoredProduct.Quantity,
-- automatically writes a row into ProductUsageLog.
 
CREATE OR REPLACE FUNCTION fn_log_stock_change()
RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
    v_diff           INT4;
    v_change_type_id INT4;
    v_base_unit_id   INT4;
BEGIN
    v_diff := NEW.Quantity - OLD.Quantity;
 
    IF v_diff = 0 THEN
        RETURN NEW;
    END IF;
 
    SELECT Id INTO v_change_type_id
      FROM ChangeType
     WHERE Sign = (v_diff > 0)
     LIMIT 1;
 
    SELECT p.BaseUnitId INTO v_base_unit_id
      FROM Product p
     WHERE p.Id = NEW.ProductId;
 
    INSERT INTO ProductUsageLog
        (ProductId, ChangeAmount, InputAmount,
         Timestamp, ChangeTypeId, BaseUnitId, InputUnitId)
    VALUES
        (NEW.ProductId,
         ABS(v_diff),
         ABS(v_diff),
         CURRENT_TIMESTAMP,
         v_change_type_id,
         v_base_unit_id,
         v_base_unit_id);
 
    RETURN NEW;
END;
$$;
 
-- TRIGGER 3: trg_log_stock_change
 
CREATE OR REPLACE TRIGGER trg_log_stock_change
AFTER UPDATE OF Quantity ON StoredProduct
FOR EACH ROW
EXECUTE FUNCTION fn_log_stock_change();
 
 



-- FUNCTION 4: fn_calculate_order_total
--Calculates the total amount of an order.
--       Automatically applies active discounts (DiscountItem)
--       for the current date if they exist.
 
CREATE OR REPLACE FUNCTION fn_calculate_order_total(p_order_id INT4)
RETURNS NUMERIC(10,2)
LANGUAGE plpgsql AS
$$
DECLARE
    v_total      NUMERIC(10,2) := 0;
    v_item       RECORD;
    v_disc_price NUMERIC(10,2);
    v_unit_price NUMERIC(10,2);
BEGIN
    FOR v_item IN
        SELECT oi.Id          AS OiId,
               oi.Quantity,
               oi.MenuMemberId,
               oi.MenuItemId,
               mm.Price       AS BasePrice
          FROM OrderItem  oi
          JOIN MenuMember mm
            ON mm.Id        = oi.MenuMemberId
           AND mm.MenuItemId = oi.MenuItemId
         WHERE oi.OrderId = p_order_id
    LOOP
        SELECT di.NewPrice INTO v_disc_price
          FROM DiscountItem di
          JOIN Discount     d  ON d.Id = di.DiscountId
         WHERE di.MenuMemberId         = v_item.MenuMemberId
           AND di.MenuMemberMenuItemId = v_item.MenuItemId
           AND d.Status                = TRUE
           AND CURRENT_DATE BETWEEN d."From" AND d."To"
         ORDER BY di.NewPrice ASC
         LIMIT 1;
 
        v_unit_price := COALESCE(v_disc_price, v_item.BasePrice);
        v_total      := v_total + (v_unit_price * v_item.Quantity);
 
        v_disc_price := NULL;
    END LOOP;
 
    RETURN v_total;
END;
$$;
 
 
-- FUNCTION 5: fn_get_available_tables
--Returns all tables that are free for a given date,time range and minimum capacity.
-- Also checks the Reservation table for overlaps.
 
CREATE OR REPLACE FUNCTION fn_get_available_tables(
    p_date   DATE,
    p_start  TIME,
    p_end    TIME,
    p_guests INT4 DEFAULT 1
)
RETURNS TABLE(
    TableNumber INT4,
    Capacity    INT4,
    TableType   TEXT
)
LANGUAGE plpgsql AS
$$
BEGIN
    RETURN QUERY
    SELECT  rt.TableNumber,
            rt.Capacity,
            tt.Type::TEXT AS TableType
      FROM  RestaurantTable rt
      JOIN  TableType        tt ON tt.Id = rt.TableTypeId
     WHERE  rt.Status   = TRUE
       AND  rt.Capacity >= p_guests
       AND  rt.TableNumber NOT IN (
               SELECT r.TableNumber
                 FROM Reservation r
                WHERE r."Date"        = p_date
                  AND r.TableNumber  IS NOT NULL
                  AND tsrange(
                        (p_date + p_start)::TIMESTAMP,
                        (p_date + p_end  )::TIMESTAMP
                      ) &&
                      tsrange(
                        (r."Date" + r.StartTime)::TIMESTAMP,
                        (r."Date" + r.EndTime  )::TIMESTAMP
                      )
           )
     ORDER BY rt.Capacity ASC;
END;
$$;
 
 
-- FUNCTION 6: fn_low_stock_report
--Returns all products whose current stock is below a given threshold.
-- Used by the owner to know when a restock is needed.
 
CREATE OR REPLACE FUNCTION fn_low_stock_report(p_threshold INT4 DEFAULT 10)
RETURNS TABLE(
    Product     TEXT,
    ProductType TEXT,
    Quantity    INT4,
    BaseUnit    TEXT
)
LANGUAGE plpgsql AS
$$
BEGIN
    RETURN QUERY
    SELECT  p.Name::TEXT        AS Product,
            pt.Type::TEXT       AS ProductType,
            sp.Quantity,
            u.Name::TEXT        AS BaseUnit
      FROM  StoredProduct sp
      JOIN  Product       p   ON p.Id    = sp.ProductId
      JOIN  ProductType   pt  ON pt.Id   = p.TypeId
      JOIN  Unit          u   ON u.Id    = p.BaseUnitId
     WHERE  sp.Quantity < p_threshold
     ORDER  BY sp.Quantity ASC;
END;
$$;
 
-- PROCEDURE 7: sp_add_order_item
-- Adds a menu item to an existing open order.
-- Validates that the order is open, the MenuMember exists
-- in an active menu, and the quantity is positive.
-- If the same item already exists on the order, it increases
-- the quantity instead of inserting a duplicate row.
-- Usage:
--   CALL sp_add_order_item(5, 3, 3, 2, 1);
 
CREATE OR REPLACE PROCEDURE sp_add_order_item(
    p_order_id       INT4,
    p_menu_member_id INT4,
    p_menu_item_id   INT4,
    p_quantity       INT4,
    p_created_by     INT4 DEFAULT NULL
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_order_status TEXT;
    v_existing_id  INT4;
BEGIN
    IF p_quantity <= 0 THEN
        RAISE EXCEPTION '[AddItem] Quantity must be > 0. Provided: %', p_quantity;
    END IF;
 
    -- Check order exists and is open
    SELECT os.Status INTO v_order_status
      FROM "Order"     o
      JOIN OrderStatus os ON os.Id = o.StatusId
     WHERE o.Id = p_order_id;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[AddItem] Order #% does not exist.', p_order_id;
    END IF;
 
    IF v_order_status IN ('Completed', 'Cancelled') THEN
        RAISE EXCEPTION '[AddItem] Order #% is "%". Cannot add items.', p_order_id, v_order_status;
    END IF;
 
    -- Check the MenuMember exists
    IF NOT EXISTS (
        SELECT 1 FROM MenuMember
         WHERE Id = p_menu_member_id
           AND MenuItemId = p_menu_item_id
    ) THEN
        RAISE EXCEPTION '[AddItem] MenuMember (%, %) does not exist.',
            p_menu_member_id, p_menu_item_id;
    END IF;
 
    -- If the same item is already on the order, just increase quantity
    SELECT Id INTO v_existing_id
      FROM OrderItem
     WHERE OrderId      = p_order_id
       AND MenuMemberId = p_menu_member_id
       AND MenuItemId   = p_menu_item_id
       AND Finished     = FALSE
     LIMIT 1;
 
    IF FOUND THEN
        UPDATE OrderItem
           SET Quantity = Quantity + p_quantity
         WHERE Id = v_existing_id;
 
        RAISE NOTICE '[AddItem] Item (MenuMember %) quantity increased by % on Order #%.',
            p_menu_member_id, p_quantity, p_order_id;
    ELSE
        INSERT INTO OrderItem
            (Quantity, OrderId, CreatedBy, Finished, MenuMemberId, MenuItemId)
        VALUES
            (p_quantity, p_order_id, p_created_by, FALSE, p_menu_member_id, p_menu_item_id);
 
        RAISE NOTICE '[AddItem] Item (MenuMember %) x% added to Order #%.',
            p_menu_member_id, p_quantity, p_order_id;
    END IF;
END;
$$;

 -- PROCEDURE 8: sp_create_order
-- Creates a new order for a waiter, order type and optionally
-- assigns a table. Sets the initial status to the first
-- non-terminal status found (e.g. 'Pending' or 'Open').
-- If a table is given it marks it as occupied (Status = FALSE).
-- Usage:
--   CALL sp_create_order(1, 1, 3);   -- dine-in table 3
--   CALL sp_create_order(1, 2, NULL); -- takeaway, no table
 
CREATE OR REPLACE PROCEDURE sp_create_order(
    p_waiter_id      INT4,
    p_order_type_id  INT4,
    p_table_number   INT4 DEFAULT NULL
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_status_id  INT4;
    v_order_id   INT4;
BEGIN
    -- Validate waiter exists and is still active
    IF NOT EXISTS (
        SELECT 1 FROM Employee
         WHERE Id = p_waiter_id
           AND (DateResignation IS NULL OR DateResignation > CURRENT_DATE)
    ) THEN
        RAISE EXCEPTION '[Order] Employee #% does not exist or has resigned.', p_waiter_id;
    END IF;
 
    -- Validate order type
    IF NOT EXISTS (SELECT 1 FROM OrderType WHERE Id = p_order_type_id) THEN
        RAISE EXCEPTION '[Order] OrderType #% does not exist.', p_order_type_id;
    END IF;
 
    -- Validate table if provided
    IF p_table_number IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM RestaurantTable
             WHERE TableNumber = p_table_number
               AND Status = TRUE
        ) THEN
            RAISE EXCEPTION '[Order] Table #% does not exist or is already occupied.', p_table_number;
        END IF;
    END IF;
 
    -- Get the initial order status (Pending or first available)
    SELECT Id INTO v_status_id
      FROM OrderStatus
     WHERE Status NOT IN ('Completed', 'Cancelled')
     ORDER BY Id
     LIMIT 1;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[Order] No valid initial OrderStatus found. Check the OrderStatus table.';
    END IF;
 
    -- Create the order
    INSERT INTO "Order" (WaiterId, DateCreated, TypeId, StatusId, TableNumber)
    VALUES (p_waiter_id, CURRENT_TIMESTAMP, p_order_type_id, v_status_id, p_table_number)
    RETURNING Id INTO v_order_id;
 
    -- Mark the table as occupied
    IF p_table_number IS NOT NULL THEN
        UPDATE RestaurantTable
           SET Status = FALSE
         WHERE TableNumber = p_table_number;
    END IF;
 
    RAISE NOTICE '[Order] Order #% created. Waiter: %, Table: %, Status: %.',
        v_order_id, p_waiter_id, COALESCE(p_table_number::TEXT, 'N/A'), v_status_id;
END;
$$;
 



-- PROCEDURE 9: sp_create_reservation
-- Creates a reservation for a guest after validating that
-- the requested table is free for that date and time slot.
-- Uses fn_get_available_tables to check availability.

--   CALL sp_create_reservation('John Smith', '071234567',
--       '2024-12-31', '19:00', '21:00', 3, 2);

CREATE OR REPLACE PROCEDURE sp_create_reservation(
    p_guest_name   VARCHAR(50),
    p_guest_phone  VARCHAR(50),
    p_date         DATE,
    p_start        TIME,
    p_end          TIME,
    p_table_number INT4,
    p_employee_id  INT4 DEFAULT NULL
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_reservation_id INT4;
BEGIN
    -- Basic time validation
    IF p_start >= p_end THEN
        RAISE EXCEPTION '[Reservation] Start time must be before end time.';
    END IF;
 
    IF p_date < CURRENT_DATE THEN
        RAISE EXCEPTION '[Reservation] Cannot make a reservation in the past.';
    END IF;
 
    -- Check the table exists
    IF NOT EXISTS (
        SELECT 1 FROM RestaurantTable WHERE TableNumber = p_table_number
    ) THEN
        RAISE EXCEPTION '[Reservation] Table #% does not exist.', p_table_number;
    END IF;
 
    -- Check the table is available for this slot using our function
    IF NOT EXISTS (
        SELECT 1 FROM fn_get_available_tables(p_date, p_start, p_end, 1)
         WHERE TableNumber = p_table_number
    ) THEN
        RAISE EXCEPTION
            '[Reservation] Table #% is not available on % from % to %.',
            p_table_number, p_date, p_start, p_end;
    END IF;
 
    -- Validate employee if provided
    IF p_employee_id IS NOT NULL AND NOT EXISTS (
        SELECT 1 FROM Employee WHERE Id = p_employee_id
    ) THEN
        RAISE EXCEPTION '[Reservation] Employee #% does not exist.', p_employee_id;
    END IF;
 
    -- Create the reservation
    INSERT INTO Reservation
        (GuestName, GuestPhone, CreatedAt, StartTime, EndTime,
         "Date", EmployeeId, TableNumber)
    VALUES
        (p_guest_name, p_guest_phone, CURRENT_TIMESTAMP,
         p_start, p_end, p_date, p_employee_id, p_table_number)
    RETURNING Id INTO v_reservation_id;
 
    RAISE NOTICE '[Reservation] #% created for "%" on % % - %, Table #%.',
        v_reservation_id, p_guest_name, p_date, p_start, p_end, p_table_number;
END;
$$;
 


-- PROCEDURE 10: sp_cancel_order
-- Safely cancels an open order.
-- Sets status to Cancelled, sets DateFinished, and frees
-- the table if no other active order is using it.
-- Does NOT restock inventory (items already consumed by kitchen
-- are not reversed - sp_restock_product for manual correction).
-- Usage:
--   CALL sp_cancel_order(7, 'Guest left before food was ready');
 
CREATE OR REPLACE PROCEDURE sp_cancel_order(
    p_order_id INT4,
    p_reason   TEXT DEFAULT 'No reason provided'
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_cancelled_id  INT4;
    v_cur_status    TEXT;
    v_table_number  INT4;
    v_other_open    INT4;
BEGIN
    -- Get current order state
    SELECT os.Status, o.TableNumber
      INTO v_cur_status, v_table_number
      FROM "Order"     o
      JOIN OrderStatus os ON os.Id = o.StatusId
     WHERE o.Id = p_order_id;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[Cancel] Order #% does not exist.', p_order_id;
    END IF;
 
    IF v_cur_status = 'Cancelled' THEN
        RAISE EXCEPTION '[Cancel] Order #% is already cancelled.', p_order_id;
    END IF;
 
    IF v_cur_status = 'Completed' THEN
        RAISE EXCEPTION '[Cancel] Cannot cancel a completed order (#%).', p_order_id;
    END IF;
 
    -- Get Cancelled status Id
    SELECT Id INTO v_cancelled_id
      FROM OrderStatus
     WHERE Status = 'Cancelled';
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[Cancel] OrderStatus "Cancelled" missing from lookup table.';
    END IF;
 
    -- Cancel the order
    UPDATE "Order"
       SET StatusId     = v_cancelled_id,
           DateFinished = CURRENT_TIMESTAMP
     WHERE Id = p_order_id;
 
    -- Free the table if no other active order is using it
    IF v_table_number IS NOT NULL THEN
        SELECT COUNT(*) INTO v_other_open
          FROM "Order"     o
          JOIN OrderStatus os ON os.Id = o.StatusId
         WHERE o.TableNumber = v_table_number
           AND o.Id          <> p_order_id
           AND os.Status NOT IN ('Completed', 'Cancelled');
 
        IF v_other_open = 0 THEN
            UPDATE RestaurantTable
               SET Status = TRUE
             WHERE TableNumber = v_table_number;
 
            RAISE NOTICE '[Cancel] Table #% has been freed.', v_table_number;
        END IF;
    END IF;
 
    RAISE NOTICE '[Cancel] Order #% cancelled. Reason: %', p_order_id, p_reason;
END;
$$;


-- PROCEDURE 11: sp_close_order_and_invoice
--Central operation - closing an order and automatically generating a full invoice:
--         - Invoice    - total amount and tax
--         - InvoiceItem - per line item, with original and discounted price
 
CREATE OR REPLACE PROCEDURE sp_close_order_and_invoice(
    p_order_id INT4,
    p_tax_rate NUMERIC(5,4) DEFAULT 0.18
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_status_id    INT4;
    v_cur_status   TEXT;
    v_total        NUMERIC(10,2);
    v_tax          NUMERIC(10,2);
    v_invoice_id   INT4;
    v_disc_price   NUMERIC(10,2);
    v_item         RECORD;
BEGIN
    SELECT os.Status, os.Id
      INTO v_cur_status, v_status_id
      FROM "Order"     o
      JOIN OrderStatus os ON os.Id = o.StatusId
     WHERE o.Id = p_order_id;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[Invoice] Order #% does not exist.', p_order_id;
    END IF;
 
    IF v_cur_status IN ('Completed', 'Cancelled') THEN
        RAISE EXCEPTION
            '[Invoice] Order #% is already "%". Cannot close again.',
            p_order_id, v_cur_status;
    END IF;
 
    SELECT Id INTO v_status_id
      FROM OrderStatus
     WHERE Status = 'Completed';
 
    IF NOT FOUND THEN
        RAISE EXCEPTION
            '[Invoice] OrderStatus "Completed" does not exist. Add it to the lookup table.';
    END IF;
 
    v_total := fn_calculate_order_total(p_order_id);
    v_tax   := ROUND(v_total * p_tax_rate, 2);
 
    INSERT INTO Invoice (OrderId, InvoiceDate, TotalAmount, TaxAmount)
    VALUES (p_order_id, CURRENT_TIMESTAMP, v_total, v_tax)
    RETURNING Id INTO v_invoice_id;
 
    FOR v_item IN
        SELECT oi.Id          AS OiId,
               oi.Quantity,
               oi.MenuMemberId,
               oi.MenuItemId,
               mm.Price       AS OrigPrice
          FROM OrderItem  oi
          JOIN MenuMember mm
            ON mm.Id         = oi.MenuMemberId
           AND mm.MenuItemId  = oi.MenuItemId
         WHERE oi.OrderId = p_order_id
    LOOP
        SELECT di.NewPrice INTO v_disc_price
          FROM DiscountItem di
          JOIN Discount     d  ON d.Id = di.DiscountId
         WHERE di.MenuMemberId         = v_item.MenuMemberId
           AND di.MenuMemberMenuItemId = v_item.MenuItemId
           AND d.Status                = TRUE
           AND CURRENT_DATE BETWEEN d."From" AND d."To"
         ORDER BY di.NewPrice ASC
         LIMIT 1;
 
        INSERT INTO InvoiceItem
            (InvoiceId, OrderItemId, OriginalPrice, DiscountedPrice, Quantity)
        VALUES
            (v_invoice_id,
             v_item.OiId,
             v_item.OrigPrice,
             v_disc_price,
             v_item.Quantity);
 
        v_disc_price := NULL;
    END LOOP;
 
    UPDATE "Order"
       SET StatusId     = v_status_id,
           DateFinished = CURRENT_TIMESTAMP
     WHERE Id = p_order_id;
 
    RAISE NOTICE
        '[Invoice] Order #% closed. Invoice #% generated. Total: % (tax: %).',
        p_order_id, v_invoice_id, v_total, v_tax;
END;
$$;
 
 
-- PROCEDURE 12: sp_restock_product
--Restock inventory for a product.
-- If no row exists in StoredProduct it creates one.
-- The log is written automatically via trg_log_stock_change.
 
CREATE OR REPLACE PROCEDURE sp_restock_product(
    p_product_id INT4,
    p_quantity   INT4
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_name     TEXT;
    v_old_qty  INT4;
BEGIN
    IF p_quantity <= 0 THEN
        RAISE EXCEPTION
            '[Restock] Quantity must be > 0. Provided: %', p_quantity;
    END IF;
 
    SELECT Name INTO v_name
      FROM Product
     WHERE Id = p_product_id;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION '[Restock] Product #% does not exist.', p_product_id;
    END IF;
 
    SELECT Quantity INTO v_old_qty
      FROM StoredProduct
     WHERE ProductId = p_product_id
     ORDER BY Id
     LIMIT 1;
 
    IF NOT FOUND THEN
        INSERT INTO StoredProduct (Quantity, ProductId)
        VALUES (p_quantity, p_product_id);
        RAISE NOTICE '[Restock] "%" - new record: 0 -> % units.', v_name, p_quantity;
    ELSE
        UPDATE StoredProduct
           SET Quantity = Quantity + p_quantity
         WHERE ProductId = p_product_id
           AND Id = (SELECT Id FROM StoredProduct
                      WHERE ProductId = p_product_id ORDER BY Id LIMIT 1);
 
        RAISE NOTICE '[Restock] "%" - % -> % units.',
            v_name, v_old_qty, v_old_qty + p_quantity;
    END IF;
END;
$$;
 
 
-- PROCEDURE 13: sp_apply_discount_to_menu
--The owner can apply a percentage discount to all items in a given menu for a set period.
--  Automatically calculates the new prices and inserts rows into the Discount and DiscountItem tables.
--
 
CREATE OR REPLACE PROCEDURE sp_apply_discount_to_menu(
    p_menu_id INT4,
    p_name    VARCHAR(30),
    p_pct     NUMERIC(5,2),
    p_from    DATE,
    p_to      DATE
)
LANGUAGE plpgsql AS
$$
DECLARE
    v_disc_id  INT4;
    v_item     RECORD;
    v_new_prc  NUMERIC(10,2);
    v_count    INT4 := 0;
BEGIN
    IF p_pct <= 0 OR p_pct >= 100 THEN
        RAISE EXCEPTION
            '[Discount] Percentage must be 0 < p_pct < 100. Provided: %', p_pct;
    END IF;
 
    IF p_from > p_to THEN
        RAISE EXCEPTION '[Discount] "From" date must be before "To" date.';
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Menu WHERE Id = p_menu_id) THEN
        RAISE EXCEPTION '[Discount] Menu #% does not exist.', p_menu_id;
    END IF;
 
    INSERT INTO Discount ("Name", "From", "To", MenuId, Status)
    VALUES (p_name, p_from, p_to, p_menu_id, TRUE)
    RETURNING Id INTO v_disc_id;
 
    FOR v_item IN
        SELECT mm.Id AS MemberId, mm.MenuItemId, mm.Price
          FROM MenuMember mm
         WHERE mm.MenuId = p_menu_id
    LOOP
        v_new_prc := ROUND(v_item.Price * (1.0 - p_pct / 100.0), 2);
 
        INSERT INTO DiscountItem
            (NewPrice, MenuMemberId, MenuMemberMenuItemId, DiscountId)
        VALUES
            (v_new_prc, v_item.MemberId, v_item.MenuItemId, v_disc_id);
 
        v_count := v_count + 1;
    END LOOP;
 
    IF v_count = 0 THEN
        RAISE WARNING
            '[Discount] Menu #% has no items. Discount created but is empty.',
            p_menu_id;
    ELSE
        RAISE NOTICE
            '[Discount] "%" (% %%) applied to % items. Valid: % to %.',
            p_name, p_pct, v_count, p_from, p_to;
    END IF;
END;
$$;



SELECT routine_name, routine_type
  FROM information_schema.routines
 WHERE routine_schema = 'public'
   AND routine_name IN (
       'fn_calculate_order_total',
       'fn_get_available_tables',
       'fn_low_stock_report',
       'sp_create_order',
       'sp_add_order_item',
       'sp_create_reservation',
       'sp_cancel_order',
       'sp_close_order_and_invoice',
       'sp_restock_product',
       'sp_apply_discount_to_menu'
   )
 ORDER BY routine_type, routine_name;


 
SELECT trigger_name, event_manipulation, event_object_table, action_timing
  FROM information_schema.triggers
 WHERE trigger_schema = 'public'
   AND trigger_name IN (
       'trg_prevent_closed_order_edit',
       'trg_deduct_inventory_on_item_finish',
       'trg_log_stock_change'
   )
 ORDER BY trigger_name;