-- Step 19: Add reservation preorder tables and columns
-- Feature: Reservation Preorder System
-- Date: 2026-02-20

-- 1. New table for storing preorder items linked to reservations
CREATE TABLE `reservationPreorderItems` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) NOT NULL,
  `reservationId` int(11) UNSIGNED NOT NULL,
  `menuItemId` int(11) UNSIGNED NOT NULL,
  `quantity` smallint(5) UNSIGNED NOT NULL DEFAULT 1,
  `selectedOptions` varchar(500) DEFAULT NULL COMMENT 'JSON array of option IDs, e.g. [12, 45]',
  `selectedAddons` varchar(500) DEFAULT NULL COMMENT 'JSON array of {id, quantity} objects',
  `selectedModifications` varchar(500) DEFAULT NULL COMMENT 'JSON array of modification IDs',
  `courseNumber` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=Starter, 2=Main, 3=Dessert',
  `sortOrder` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `kitchenHandled` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `vendorId` (`vendorId`),
  KEY `reservationId` (`reservationId`),
  CONSTRAINT `fk_preorder_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_preorder_reservation` FOREIGN KEY (`reservationId`) REFERENCES `reservations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 2. Flag on reservations for visual warning when preorder items become unavailable
ALTER TABLE `reservations` ADD COLUMN `preorderItemsUnavailable` tinyint(1) NOT NULL DEFAULT 0
  COMMENT 'Flag set when any preorder item becomes unavailable';

-- 3. Link virtual sessions to their source reservation
ALTER TABLE `sessions_virtual` ADD COLUMN `reservationId` int(11) UNSIGNED DEFAULT NULL
  COMMENT 'Links virtual session to its source reservation for preorder activation';

-- 4. Track course number on session items for preorder course management
ALTER TABLE `sessionItems` ADD COLUMN `courseNumber` tinyint(3) UNSIGNED DEFAULT NULL
  COMMENT 'Course number for preorder items (1=Starter, 2=Main, 3=Dessert). NULL for non-preorder items';
