-- COMPLETE Migration: Allow multiple bids from the same supplier
-- This script checks the current state and makes necessary changes

-- STEP 1: Check current indexes
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');

-- Look at the output above:
-- - If you see an index with Non_unique = 0, that's the UNIQUE index we need to drop
-- - If you see an index with Non_unique = 1, duplicates are already allowed!

-- STEP 2: Find and drop the unique index (if it exists)
-- The unique index might have a different name. Check Step 1 output for Key_name where Non_unique = 0
-- Then run one of these (replace with actual index name from Step 1):

-- Common index names to try (run one at a time until one works):
ALTER TABLE `bids` DROP INDEX `bids_inquiryId_supplierId`;
ALTER TABLE `bids` DROP INDEX `inquiryId_supplierId`;
ALTER TABLE `bids` DROP INDEX `bids_inquiryId_supplierId_1`;

-- If all of the above fail, check Step 1 output and use the actual Key_name

-- STEP 3: Ensure non-unique index exists
-- If idx_bids_inquiry_supplier already exists, skip this step
-- If it doesn't exist, this will create it:
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- If you get "Duplicate key name" error, that's fine - the index already exists!

-- STEP 4: Final verification
-- Run this to confirm:
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');

-- You should see:
-- - Non_unique = 1 for the idx_bids_inquiry_supplier index (allows duplicates)
-- - No index with Non_unique = 0 (no unique constraint)