-- 推广自动化：仅新增独立表，不改业务表；需在部署代码/启用worker之前执行。
CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_config` (
  `pool_id` int unsigned NOT NULL,
  `config_json` mediumtext NOT NULL,
  `create_time` int unsigned NOT NULL DEFAULT 0,
  `update_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`pool_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广方案扩展配置';

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_media` (
  `asset_id` char(48) NOT NULL,
  `admin_id` int unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(16) NOT NULL,
  `mime` varchar(128) NOT NULL,
  `size` int unsigned NOT NULL,
  `sha256` char(64) NOT NULL,
  `storage_name` varchar(80) NOT NULL,
  `media_id` varchar(512) NOT NULL DEFAULT '',
  `media_expires_at` int unsigned NOT NULL DEFAULT 0,
  `credential_hash` char(64) NOT NULL DEFAULT '',
  `last_error` varchar(255) NOT NULL DEFAULT '',
  `create_time` int unsigned NOT NULL DEFAULT 0,
  `update_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`asset_id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_media_expiry` (`media_expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='私有欢迎语源素材与企微临时素材缓存';

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_automation_task` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_key` char(64) NOT NULL,
  `pool_id` int unsigned NOT NULL,
  `member_admin_id` int unsigned NOT NULL DEFAULT 0,
  `change_type` varchar(40) NOT NULL,
  `userid` varchar(128) NOT NULL,
  `external_userid` varchar(128) NOT NULL,
  `event_time` int unsigned NOT NULL DEFAULT 0,
  `received_at` int unsigned NOT NULL DEFAULT 0,
  `config_json` mediumtext NOT NULL,
  `actions_json` mediumtext NOT NULL,
  `welcome_cipher` text NOT NULL COMMENT 'AES-GCM短期密文，动作终态即清理',
  `welcome_code_hash` char(64) DEFAULT NULL,
  `welcome_expires_at` int unsigned NOT NULL DEFAULT 0,
  `welcome_status` varchar(20) NOT NULL DEFAULT 'pending',
  `welcome_next_retry` int unsigned NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `next_retry` int unsigned NOT NULL DEFAULT 0,
  `lock_token` char(32) NOT NULL DEFAULT '',
  `lock_until` int unsigned NOT NULL DEFAULT 0,
  `create_time` int unsigned NOT NULL DEFAULT 0,
  `update_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_event` (`event_key`),
  KEY `idx_welcome_due` (`welcome_status`,`welcome_next_retry`,`welcome_expires_at`),
  KEY `idx_metadata_due` (`status`,`next_retry`,`lock_until`),
  KEY `idx_pool_event` (`pool_id`,`event_time`),
  UNIQUE KEY `uk_code_hash` (`welcome_code_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广事件幂等任务与分动作状态快照';

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_automation_action_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `task_id` bigint unsigned NOT NULL,
  `action` varchar(24) NOT NULL,
  `status` varchar(20) NOT NULL,
  `attempt` int unsigned NOT NULL DEFAULT 0,
  `reason` varchar(160) NOT NULL DEFAULT '',
  `error_code` int NOT NULL DEFAULT 0,
  `create_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_task_action` (`task_id`,`action`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自动化动作审计，无token/欢迎码/HTTP原始内容';

INSERT INTO `zyt_dev_crontab` (
  `name`, `type`, `system`, `remark`, `command`, `params`,
  `status`, `expression`, `create_time`, `update_time`
)
SELECT
  '补偿企微推广自动化', 1, 0,
  '补偿客户标签、备注、成员记账、范围与客户资料同步，并清理过期欢迎码',
  'qywx:retry-promotion-automation', '', 1, '* * * * *', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `zyt_dev_crontab`
  WHERE `command` = 'qywx:retry-promotion-automation' AND `delete_time` IS NULL
);

INSERT INTO `zyt_dev_crontab` (
  `name`, `type`, `system`, `remark`, `command`, `params`,
  `status`, `expression`, `create_time`, `update_time`
)
SELECT
  '刷新企微推广欢迎语素材', 1, 0,
  '预热并刷新渠道欢迎语使用的企业微信临时素材',
  'qywx:refresh-promotion-media', '', 1, '*/5 * * * *', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `zyt_dev_crontab`
  WHERE `command` = 'qywx:refresh-promotion-media' AND `delete_time` IS NULL
);