-- ============================================
-- PRODUCTION MIGRATIONS - COMPLETE SCRIPT
-- Run these migrations in order on production
-- ============================================

-- ============================================
-- MIGRATION 1: Add isUrgent Column to Inquiries
-- ============================================
-- Skip if column already exists (you'll get an error, that's fine)
ALTER TABLE `inquiries` 
ADD COLUMN `isUrgent` BOOLEAN DEFAULT FALSE;

UPDATE `inquiries` SET `isUrgent` = FALSE WHERE `isUrgent` IS NULL;

-- ============================================
-- MIGRATION 2: Add Bank Details to Suppliers
-- ============================================
-- Skip any column that already exists (you'll get an error, that's fine)
ALTER TABLE `suppliers` 
ADD COLUMN `bankName` VARCHAR(200) NULL AFTER `mapLink`,
ADD COLUMN `accountHolderName` VARCHAR(200) NULL AFTER `bankName`,
ADD COLUMN `accountNumber` VARCHAR(50) NULL AFTER `accountHolderName`,
ADD COLUMN `branchName` VARCHAR(200) NULL AFTER `accountNumber`,
ADD COLUMN `bankCode` VARCHAR(20) NULL AFTER `branchName`;

-- ============================================
-- MIGRATION 3: Add Warranty and Condition to Bids
-- ============================================
-- Run each ALTER TABLE separately
-- Skip any column that already exists

-- Step 3.1: Add warrantyAvailable
ALTER TABLE `bids` 
ADD COLUMN `warrantyAvailable` BOOLEAN DEFAULT FALSE NULL AFTER `notes`;

-- Step 3.2: Add warrantyPeriod
ALTER TABLE `bids` 
ADD COLUMN `warrantyPeriod` INT NULL AFTER `warrantyAvailable`;

-- Step 3.3: Add condition
ALTER TABLE `bids` 
ADD COLUMN `condition` ENUM('brand_new', 'used') NULL AFTER `warrantyPeriod`;

-- ============================================
-- MIGRATION 4: Add Warranty Mileage to Bids
-- ============================================
ALTER TABLE `bids` 
ADD COLUMN `warrantyMileage` INT NULL COMMENT 'Warranty period in kilometers (if warrantyAvailable is true)' AFTER `warrantyPeriod`;

-- ============================================
-- MIGRATION 5: Allow Multiple Bids Per Supplier
-- ============================================
-- IMPORTANT: This requires manual steps - see instructions below

-- Step 5.1: Find the unique index
-- Run this first:
-- SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');
-- Look for Non_unique = 0, note the Key_name

-- Step 5.2: Drop the unique index (replace YOUR_INDEX_NAME with actual name)
-- SET FOREIGN_KEY_CHECKS = 0;
-- ALTER TABLE `bids` DROP INDEX `YOUR_INDEX_NAME`;
-- SET FOREIGN_KEY_CHECKS = 1;

-- Step 5.3: Create non-unique index (only if it doesn't exist)
-- CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- ============================================
-- VERIFICATION QUERIES
-- ============================================
-- Run these to verify all migrations completed:

-- Check isUrgent
SHOW COLUMNS FROM `inquiries` LIKE 'isUrgent';

-- Check bank details
SHOW COLUMNS FROM `suppliers` LIKE 'bank%';

-- Check bid columns
SHOW COLUMNS FROM `bids` LIKE 'warranty%';
SHOW COLUMNS FROM `bids` LIKE 'condition%';

-- Check bid indexes (should show Non_unique = 1)
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');