-- ============================================
-- STEP-BY-STEP: Drop Unique Index with Foreign Key
-- Follow these steps EXACTLY in order
-- ============================================

-- ============================================
-- STEP 1: Find Foreign Key Constraint Names
-- ============================================
-- Run this query and note the CONSTRAINT_NAME values:
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'bids'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- Common constraint names might be:
-- - bids_ibfk_1
-- - bids_ibfk_2
-- - bids_inquiryId_fk
-- - bids_supplierId_fk
-- - Or something else

-- ============================================
-- STEP 2: Drop Foreign Key Constraints
-- ============================================
-- Replace CONSTRAINT_NAME_1 and CONSTRAINT_NAME_2 with actual names from Step 1
-- Run these one at a time:

-- ALTER TABLE `bids` DROP FOREIGN KEY `CONSTRAINT_NAME_1`;
-- ALTER TABLE `bids` DROP FOREIGN KEY `CONSTRAINT_NAME_2`;

-- Example:
-- ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_1`;
-- ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_2`;

-- ============================================
-- STEP 3: Drop the Unique Index
-- ============================================
ALTER TABLE `bids` DROP INDEX `bids_inquiry_id_supplier_id`;

-- ============================================
-- STEP 4: Recreate Foreign Keys (Optional but Recommended)
-- ============================================
-- These ensure data integrity:
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;

-- ============================================
-- STEP 5: Create Non-Unique Index
-- ============================================
-- Only run this if the index doesn't exist (you'll get an error if it exists, that's fine):
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- ============================================
-- STEP 6: Verify
-- ============================================
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 ✅