-- 1.0.304：WP10 S4b / D12 — Outbox 退避列 + 待推索引
-- 退避序列：1m,5m,15m,30m,60m,120m；MaxRetry=6；告警=LogWarning+SysNotice
-- 幂等：ADD COLUMN / INDEX 前用 information_schema 判存在。

SET @ddl := (SELECT IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_outbox' AND COLUMN_NAME='next_retry_time'),
  'SELECT 1',
  'ALTER TABLE mdp_outbox ADD COLUMN next_retry_time datetime NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := (SELECT IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_outbox' AND COLUMN_NAME='last_error_code'),
  'SELECT 1',
  'ALTER TABLE mdp_outbox ADD COLUMN last_error_code varchar(64) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name='mdp_outbox' AND index_name='ix_mdp_outbox_pending'
);
SET @sql := IF(@idx_exists=0,
  'CREATE INDEX ix_mdp_outbox_pending ON mdp_outbox(status, next_retry_time, id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
