-- ============================================
-- DROP UNIQUE INDEX WITH FOREIGN KEY FIX
-- This handles the foreign key constraint issue
-- ============================================

-- STEP 1: Find all foreign key constraints on the bids table
-- Run this first to see what foreign keys exist:
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;

-- STEP 2: Drop foreign key constraints
-- Replace CONSTRAINT_NAME_1 and CONSTRAINT_NAME_2 with actual names from Step 1
-- Common names might be: bids_ibfk_1, bids_ibfk_2, bids_inquiryId_fk, bids_supplierId_fk

-- Example (uncomment and modify with actual constraint names):
-- ALTER TABLE `bids` DROP FOREIGN KEY `CONSTRAINT_NAME_1`;
-- ALTER TABLE `bids` DROP FOREIGN KEY `CONSTRAINT_NAME_2`;

-- STEP 3: Drop the unique index
ALTER TABLE `bids` DROP INDEX `bids_inquiry_id_supplier_id`;

-- STEP 4: Recreate foreign keys (if you dropped them in Step 2)
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 (allows multiple bids)
-- Only run this if the index doesn't exist:
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- STEP 6: Verify
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');