-- 企业微信获客助手：单官方直链的成员规则与企业微信原生多人路由

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_pool_member` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` int unsigned NOT NULL DEFAULT 0,
  `admin_id` int unsigned NOT NULL DEFAULT 0,
  `userid` varchar(128) NOT NULL DEFAULT '' COMMENT '企业微信 userid 快照',
  `enabled` tinyint unsigned NOT NULL DEFAULT 1,
  `weight` smallint unsigned NOT NULL DEFAULT 1 COMMENT '兼容字段；企业微信原生多人路由不支持逐成员权重',
  `current_weight` bigint NOT NULL DEFAULT 0 COMMENT '旧版调度游标，原生多人路由模式固定归零',
  `daily_limit` int unsigned NOT NULL DEFAULT 0 COMMENT '0为不限；按回调确认的实际获客计数',
  `today_count` int unsigned NOT NULL DEFAULT 0,
  `today_date` date NULL DEFAULT NULL,
  `total_count` bigint unsigned NOT NULL DEFAULT 0,
  `active_start` int unsigned NOT NULL DEFAULT 0,
  `active_end` int unsigned NOT NULL DEFAULT 0,
  `last_assigned_time` int unsigned NOT NULL DEFAULT 0,
  `remark` varchar(255) NOT NULL DEFAULT '',
  `create_time` int unsigned NOT NULL DEFAULT 0,
  `update_time` int unsigned NOT NULL DEFAULT 0,
  `delete_time` int unsigned NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pool_userid` (`pool_id`,`userid`),
  KEY `idx_pool_enabled` (`pool_id`,`enabled`,`delete_time`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_today` (`today_date`,`today_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='获客分流方案成员规则';

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_dispatch_event` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_key` char(64) NOT NULL COMMENT '方案+客户的幂等键，成员变更不重复计数',
  `pool_id` int unsigned NOT NULL DEFAULT 0,
  `member_id` bigint unsigned NOT NULL DEFAULT 0,
  `userid` varchar(128) NOT NULL DEFAULT '',
  `external_userid` varchar(128) NOT NULL DEFAULT '',
  `source` varchar(32) NOT NULL DEFAULT '',
  `event_time` int unsigned NOT NULL DEFAULT 0,
  `create_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_event_key` (`event_key`),
  KEY `idx_pool_time` (`pool_id`,`event_time`),
  KEY `idx_member_time` (`member_id`,`event_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='获客成员实际承接幂等流水';

CREATE TABLE IF NOT EXISTS `zyt_qywx_promotion_range_sync` (
  `pool_id` int unsigned NOT NULL,
  `promotion_link_id` int unsigned NOT NULL DEFAULT 0,
  `desired_member_id` bigint unsigned NOT NULL DEFAULT 0,
  `desired_version` bigint unsigned NOT NULL DEFAULT 0,
  `applied_member_id` bigint unsigned NOT NULL DEFAULT 0,
  `applied_version` bigint unsigned NOT NULL DEFAULT 0,
  `status` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0已同步 1待同步 2同步中 3失败待重试 4阻塞 5正在永久删除',
  `attempts` int unsigned NOT NULL DEFAULT 0,
  `next_retry` int unsigned NOT NULL DEFAULT 0,
  `lock_token` char(32) NOT NULL DEFAULT '',
  `lock_until` int unsigned NOT NULL DEFAULT 0,
  `last_error` varchar(500) NOT NULL DEFAULT '',
  `create_time` int unsigned NOT NULL DEFAULT 0,
  `update_time` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`pool_id`),
  KEY `idx_pending` (`status`,`next_retry`,`lock_until`),
  KEY `idx_link` (`promotion_link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='获客官方链接多人路由范围同步队列';

INSERT INTO `zyt_dev_crontab` (
  `name`, `type`, `system`, `remark`, `command`, `params`,
  `status`, `expression`, `create_time`, `update_time`
)
SELECT
  '同步企微获客分流成员', 1, 0,
  '按禁用、有效期与每日上限更新同一条企业微信官方获客链接的多人范围',
  'qywx:sync-promotion-ranges', '', 1, '* * * * *', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `zyt_dev_crontab`
  WHERE `command` = 'qywx:sync-promotion-ranges' AND `delete_time` IS NULL
);

COMMIT;