-- Migration: Replace product_slots junction table with JSON array in delivery_slot_info
-- Step 1: Add product_ids column to delivery_slot_info
ALTER TABLE `delivery_slot_info` ADD COLUMN `product_ids` text DEFAULT '[]';

-- Step 2: Migrate data from product_slots to product_ids JSON array
-- Use a subquery to aggregate product_ids for each slot
UPDATE `delivery_slot_info` 
SET `product_ids` = (
  SELECT CASE 
    WHEN GROUP_CONCAT(`product_id`) IS NULL THEN '[]'
    ELSE '[' || GROUP_CONCAT(`product_id`) || ']'
  END
  FROM `product_slots` 
  WHERE `product_slots`.`slot_id` = `delivery_slot_info`.`id`
);

-- Step 3: Drop the old product_slots table
DROP TABLE IF EXISTS `product_slots`;

-- Note: After running this migration, update the schema.ts file to:
-- 1. Add productIds: jsonText<number[]>('product_ids').$defaultFn(() => []) to deliverySlotInfo
-- 2. Remove the productSlots table definition
-- 3. Remove productSlotsRelations
-- 4. Update deliverySlotInfoRelations to remove productSlots reference
