-- Add the new columns for reservation time limits
ALTER TABLE `openingHoursTemplates`
ADD COLUMN `firstReservationAt` VARCHAR(5) DEFAULT NULL AFTER `delivers`,
ADD COLUMN `lastReservationAt` VARCHAR(5) DEFAULT NULL AFTER `firstReservationAt`;

-- Update firstReservationAt to default to the opening time (from1)
UPDATE `openingHoursTemplates`
SET `firstReservationAt` = `from1`;

-- Update lastReservationAt based on the rules:
-- 1. If kitchenClosedAt is set, 30 minutes before kitchen closes
-- 2. Otherwise, 1 hour before the shop closes (checking to2 first for split shifts, then to1)
UPDATE `openingHoursTemplates`
SET `lastReservationAt` = CASE
    WHEN `kitchenClosedAt` IS NOT NULL AND `kitchenClosedAt` != '' THEN
        TIME_FORMAT(SUBTIME(STR_TO_DATE(`kitchenClosedAt`, '%H:%i'), '00:30:00'), '%H:%i')
    ELSE
        TIME_FORMAT(SUBTIME(STR_TO_DATE(COALESCE(NULLIF(`to2`, ''), `to1`), '%H:%i'), '01:00:00'), '%H:%i')
END;
