-- ============================================
-- COMPREHENSIVE SOLUTION: Try All Methods
-- Run these in order until one works
-- ============================================

-- ============================================
-- METHOD 1: Drop and Recreate in One Command
-- ============================================
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `bids` 
  DROP INDEX `bids_inquiry_id_supplier_id`,
  ADD INDEX `idx_bids_inquiry_supplier` (`inquiryId`, `supplierId`);
SET FOREIGN_KEY_CHECKS = 1;

-- If METHOD 1 fails, try METHOD 2 below:

-- ============================================
-- METHOD 2: Drop Foreign Keys First (Try Common Names)
-- ============================================
-- Run these one by one (some will fail with "doesn't exist" - that's fine):
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_1`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_2`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_3`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_inquiryId_fk`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_supplierId_fk`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_inquiryId_foreign`;
ALTER TABLE `bids` DROP FOREIGN KEY `bids_supplierId_foreign`;

-- Now try dropping the index:
ALTER TABLE `bids` DROP INDEX `bids_inquiry_id_supplier_id`;

-- Recreate foreign keys:
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;

-- Create non-unique index:
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================
-- METHOD 3: If Both Methods Fail
-- ============================================
-- Skip this migration for now. The system will work, but:
-- - Suppliers can only submit ONE bid per inquiry (not multiple options)
-- - All other features work normally
-- 
-- You can add multiple bids feature later when you have:
-- - Database administrator access, OR
-- - A hosting provider who can help with this migration

-- ============================================
-- VERIFICATION (Run after any successful method)
-- ============================================
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');
-- Should see: idx_bids_inquiry_supplier with Non_unique = 1