-- ============================================
-- QUICK PRODUCTION MIGRATIONS
-- Run these one by one in phpMyAdmin
-- If you get "Duplicate column" error, that column already exists - skip it
-- ============================================

-- ============================================
-- 1. Add isUrgent to inquiries
-- ============================================
ALTER TABLE `inquiries` ADD COLUMN `isUrgent` BOOLEAN DEFAULT FALSE;
UPDATE `inquiries` SET `isUrgent` = FALSE WHERE `isUrgent` IS NULL;

-- ============================================
-- 2. Add bank details to suppliers
-- ============================================
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`;

-- ============================================
-- 3. Add warranty and condition to bids
-- ============================================
-- Run these separately (one at a time):

ALTER TABLE `bids` ADD COLUMN `warrantyAvailable` BOOLEAN DEFAULT FALSE NULL AFTER `notes`;

ALTER TABLE `bids` ADD COLUMN `warrantyPeriod` INT NULL AFTER `warrantyAvailable`;

ALTER TABLE `bids` ADD COLUMN `condition` ENUM('brand_new', 'used') NULL AFTER `warrantyPeriod`;

-- ============================================
-- 4. Add warranty mileage to bids
-- ============================================
ALTER TABLE `bids` ADD COLUMN `warrantyMileage` INT NULL AFTER `warrantyPeriod`;

-- ============================================
-- 5. Allow multiple bids (REQUIRES MANUAL STEPS)
-- ============================================
-- Step 5.1: Find unique index
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');

-- Step 5.2: Drop unique index (replace INDEX_NAME with actual name from Step 5.1)
-- SET FOREIGN_KEY_CHECKS = 0;
-- ALTER TABLE `bids` DROP INDEX `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`);

-- ============================================
-- VERIFY ALL MIGRATIONS
-- ============================================
SHOW COLUMNS FROM `inquiries` LIKE 'isUrgent';
SHOW COLUMNS FROM `suppliers` LIKE 'bank%';
SHOW COLUMNS FROM `bids` LIKE 'warranty%';
SHOW COLUMNS FROM `bids` LIKE 'condition%';
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');