-- ============================================
-- FINAL FIX: Drop Unique Index
-- This should work even with foreign key constraints
-- ============================================

-- STEP 1: Disable foreign key checks completely
SET FOREIGN_KEY_CHECKS = 0;

-- STEP 2: Drop the unique index (this should work now)
ALTER TABLE `bids` DROP INDEX `bids_inquiry_id_supplier_id`;

-- STEP 3: Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- STEP 4: Verify the index is gone
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');
-- You should NOT see bids_inquiry_id_supplier_id anymore

-- STEP 5: Create non-unique index (allows multiple bids)
-- If you get "Duplicate key name" error, the index already exists - that's fine!
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- STEP 6: Final verification
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId`);
-- Should see:
-- - idx_bids_inquiry_supplier with Non_unique = 1 ✅
-- - NO index with Non_unique = 0 ✅

-- STEP 7: Verify foreign keys still work (optional check)
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'bids'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- If Step 7 shows no results, foreign keys might have been removed
-- In that case, recreate them:
-- ALTER TABLE `bids` 
--   ADD CONSTRAINT `bids_inquiryId_fk` FOREIGN KEY (`inquiryId`) REFERENCES `inquiries` (`id`) ON DELETE CASCADE;
-- 
-- ALTER TABLE `bids` 
--   ADD CONSTRAINT `bids_supplierId_fk` FOREIGN KEY (`supplierId`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE;