-- Idempotent soft-delete patch for commission_rules
SET @has_deleted_at := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND COLUMN_NAME = 'deleted_at'
);
SET @sql_deleted_at := IF(
    @has_deleted_at = 0,
    'ALTER TABLE `commission_rules` ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `updated_at`',
    'SELECT 1'
);
PREPARE stmt_deleted_at FROM @sql_deleted_at;
EXECUTE stmt_deleted_at;
DEALLOCATE PREPARE stmt_deleted_at;

SET @has_deleted_idx := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND INDEX_NAME = 'idx_commission_rules_deleted_at'
);
SET @sql_deleted_idx := IF(
    @has_deleted_idx = 0,
    'ALTER TABLE `commission_rules` ADD INDEX `idx_commission_rules_deleted_at` (`deleted_at`)',
    'SELECT 1'
);
PREPARE stmt_deleted_idx FROM @sql_deleted_idx;
EXECUTE stmt_deleted_idx;
DEALLOCATE PREPARE stmt_deleted_idx;
