-- Compatibility patch: ensure investments table has columns used by 003_add_investments_table.sql sample inserts
SET @db := DATABASE();

-- Helper to add a column if missing
-- amount_invested
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='amount_invested');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `amount_invested` DECIMAL(18,8) NOT NULL DEFAULT 0.00000000 AFTER `amount`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- profit_earned
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='profit_earned');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `profit_earned` DECIMAL(18,8) NOT NULL DEFAULT 0.00000000 AFTER `roi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- payment_method
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='payment_method');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `payment_method` VARCHAR(50) NULL AFTER `profit_earned`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- transaction_hash
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='transaction_hash');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `transaction_hash` VARCHAR(255) NULL AFTER `payment_method`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- last_payout_date
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='last_payout_date');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `last_payout_date` DATETIME NULL AFTER `transaction_hash`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- next_payout_date
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='next_payout_date');
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `next_payout_date` DATETIME NULL AFTER `last_payout_date`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- payout_frequency
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='payout_frequency');
SET @sql := IF(@exists=0, "ALTER TABLE `investments` ADD COLUMN `payout_frequency` ENUM('daily','weekly','monthly','end_of_term') DEFAULT 'end_of_term' AFTER `next_payout_date`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Ensure start_date exists and is NOT NULL (some schemas used TIMESTAMP NULL)
SET @exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='start_date');
SET @is_null := (SELECT IF(IS_NULLABLE='YES', 1, 0) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='investments' AND COLUMN_NAME='start_date' LIMIT 1);
SET @sql := IF(@exists=0, 'ALTER TABLE `investments` ADD COLUMN `start_date` DATETIME NOT NULL AFTER `status`', IF(@is_null=1, 'ALTER TABLE `investments` MODIFY `start_date` DATETIME NOT NULL', 'SELECT 1'));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
