-- Step 22: Add FK from reservationPreorderItems.sessionItemId to sessionItems.id
-- Enforces referential integrity: when a sessionItem is deleted (session ended),
-- the corresponding preorder item reference is automatically cleared (SET NULL).
--
-- Also corrects the column type from UNSIGNED to signed to match sessionItems.id.
-- Also removes stale pre-inserted "scheduled" course items from sessions,
-- which were created by the old architecture that inserted ALL courses upfront.

-- 1. Clear dangling references (sessionItemId pointing to a sessionItem that no longer exists)
UPDATE `reservationPreorderItems` rpi
LEFT JOIN `sessionItems` si ON rpi.sessionItemId = si.id
SET rpi.sessionItemId = NULL
WHERE rpi.sessionItemId IS NOT NULL AND si.id IS NULL;

-- 2. Fix column type and add the FK constraint.
ALTER TABLE `reservationPreorderItems`
  MODIFY COLUMN `sessionItemId` int(11) DEFAULT NULL,
  ADD CONSTRAINT `fk_preorder_sessionItem`
    FOREIGN KEY (`sessionItemId`) REFERENCES `sessionItems` (`id`) ON DELETE SET NULL;

-- 3. Remove pre-inserted future-course session items left over from the old architecture
--    (courseNumber > 1, state = 'scheduled'). The FK ON DELETE SET NULL above will
--    automatically clear the corresponding reservationPreorderItems.sessionItemId values.
--    Non-preorder items are excluded because their courseNumber is NULL.
DELETE FROM `sessionItems`
WHERE `state` = 'scheduled'
  AND `courseNumber` IS NOT NULL
  AND `courseNumber` > 1;
