-- Link profit bonus pool to auto-created expense row (for reversal / traceability)
-- Idempotent: safe if column / index already exists (re-run migrations)

SET @dbname = DATABASE();

SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'profit_bonus_pools' AND COLUMN_NAME = 'expense_id') > 0,
  'SELECT 1',
  'ALTER TABLE profit_bonus_pools ADD COLUMN expense_id INT NULL DEFAULT NULL COMMENT ''expenses.id when bonus posted as expense'' AFTER status'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'profit_bonus_pools' AND INDEX_NAME = 'idx_profit_bonus_expense') > 0,
  'SELECT 1',
  'ALTER TABLE profit_bonus_pools ADD KEY idx_profit_bonus_expense (expense_id)'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;