-- Add wallet_topup to paymentType ENUM in payments table
-- Note: This will only modify if the ENUM doesn't already include 'wallet_topup'
-- Check first if wallet_topup is already in the ENUM
SET @enum_check = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'payments' 
  AND COLUMN_NAME = 'paymentType' 
  AND COLUMN_TYPE LIKE '%wallet_topup%'
);

SET @sql = IF(@enum_check = 0,
  'ALTER TABLE `payments` MODIFY COLUMN `paymentType` ENUM(\'unlock_supplier\', \'order_payment\', \'delivery\', \'workshop_installation\', \'internal_procurement\', \'wallet_topup\') NOT NULL',
  'SELECT "paymentType ENUM already includes wallet_topup" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Make inquiryId nullable in payments table (for wallet top-ups)
-- Only modify if it's currently NOT NULL
SET @is_nullable = (
  SELECT IS_NULLABLE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'payments' 
  AND COLUMN_NAME = 'inquiryId'
);

SET @sql = IF(@is_nullable = 'NO',
  'ALTER TABLE `payments` MODIFY COLUMN `inquiryId` INT NULL',
  'SELECT "inquiryId in payments is already nullable" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Make inquiryId nullable in receipts table (for wallet top-ups)
SET @is_nullable = (
  SELECT IS_NULLABLE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'receipts' 
  AND COLUMN_NAME = 'inquiryId'
);

SET @sql = IF(@is_nullable = 'NO',
  'ALTER TABLE `receipts` MODIFY COLUMN `inquiryId` INT NULL',
  'SELECT "inquiryId in receipts is already nullable" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Make supplierId nullable in receipts table (for wallet top-ups)
SET @is_nullable = (
  SELECT IS_NULLABLE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'receipts' 
  AND COLUMN_NAME = 'supplierId'
);

SET @sql = IF(@is_nullable = 'NO',
  'ALTER TABLE `receipts` MODIFY COLUMN `supplierId` INT NULL',
  'SELECT "supplierId in receipts is already nullable" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Make receiptPhoto nullable in receipts table (for wallet top-ups)
SET @is_nullable = (
  SELECT IS_NULLABLE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'receipts' 
  AND COLUMN_NAME = 'receiptPhoto'
);

SET @sql = IF(@is_nullable = 'NO',
  'ALTER TABLE `receipts` MODIFY COLUMN `receiptPhoto` VARCHAR(500) NULL',
  'SELECT "receiptPhoto in receipts is already nullable" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add receiptType to receipts table (only if it doesn't exist)
SET @column_exists = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'receipts' 
  AND COLUMN_NAME = 'receiptType'
);

SET @sql = IF(@column_exists = 0,
  'ALTER TABLE `receipts` ADD COLUMN `receiptType` ENUM(\'purchase\', \'topup\') DEFAULT \'purchase\' AFTER `supplierId`',
  'SELECT "receiptType column already exists" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

