-- ============================================
-- DROP UNIQUE INDEX - Using CREATE TABLE Output
-- ============================================
-- 
-- INSTRUCTIONS:
-- 1. Run: SHOW CREATE TABLE `bids`;
-- 2. Look at the output and find lines with "CONSTRAINT" and "FOREIGN KEY"
-- 3. Note the constraint names (the part between CONSTRAINT and FOREIGN KEY)
-- 4. Replace CONSTRAINT_NAME_1 and CONSTRAINT_NAME_2 below with actual names
-- 5. Run the commands below
-- ============================================

-- STEP 1: Get the CREATE TABLE statement
-- Run this first:
SHOW CREATE TABLE `bids`;

-- STEP 2: From the output, find lines like:
-- CONSTRAINT `something` FOREIGN KEY (`inquiryId`) REFERENCES `inquiries` (`id`)
-- CONSTRAINT `something_else` FOREIGN KEY (`supplierId`) REFERENCES `suppliers` (`id`)
--
-- The constraint names are: `something` and `something_else`
-- Common names: bids_ibfk_1, bids_ibfk_2, bids_inquiryId_fk, bids_supplierId_fk

-- STEP 3: Drop the foreign key constraints
-- Replace CONSTRAINT_NAME_1 and CONSTRAINT_NAME_2 with actual names from Step 2
-- Example:
-- ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_1`;
-- ALTER TABLE `bids` DROP FOREIGN KEY `bids_ibfk_2`;

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

-- STEP 5: Recreate foreign keys (optional but recommended for 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 6: Create non-unique index (allows multiple bids)
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

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