-- Migration script to update shops table structure
-- Run this if you already have an existing shops table with data

-- Step 1: Add new columns
ALTER TABLE `shops` 
ADD COLUMN `shopkeeperIds` json DEFAULT ('[]'),
ADD COLUMN `shopkeeperNames` json DEFAULT ('[]');

-- Step 2: Migrate existing assignedUserId data to new structure (if you have existing data)
-- This will convert single assignedUserId to array format
UPDATE `shops` 
SET 
  `shopkeeperIds` = JSON_ARRAY(`assignedUserId`),
  `shopkeeperNames` = JSON_ARRAY(
    (SELECT `name` FROM `users` WHERE `users`.`id` = `shops`.`assignedUserId`)
  )
WHERE `assignedUserId` IS NOT NULL;

-- Step 3: Drop old foreign key constraint and column
ALTER TABLE `shops` DROP FOREIGN KEY `shops_assignedUserId_fk`;
ALTER TABLE `shops` DROP COLUMN `assignedUserId`;

-- Verify the changes
SELECT * FROM `shops` LIMIT 5;