-- ============================================
-- ULTIMATE SOLUTION: Drop Unique Index
-- This method tries multiple approaches
-- ============================================

-- ============================================
-- STEP 1: Try to find constraint names using different methods
-- ============================================

-- Method 1A: Query KEY_COLUMN_USAGE
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;

-- Method 1B: Query REFERENTIAL_CONSTRAINTS
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME = 'bids';

-- ============================================
-- STEP 2: If Step 1 returns results, use those constraint names
-- If Step 1 is empty, try common names below
-- ============================================

-- Try these common constraint names (run one at a time):
-- 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_inquiryId_fk`;
-- ALTER TABLE `bids` DROP FOREIGN KEY `bids_supplierId_fk`;

-- ============================================
-- STEP 3: Disable foreign key checks and drop index
-- ============================================
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `bids` DROP INDEX `bids_inquiry_id_supplier_id`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================
-- STEP 4: Verify foreign keys still exist
-- ============================================
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;

-- ============================================
-- STEP 5: If foreign keys are missing, recreate them
-- ============================================
-- Only run these if Step 4 shows no 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;

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

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