-- 发布管理与浏览器扩展任务表，可重复执行。
CREATE TABLE IF NOT EXISTS `tt_publish_task` (
  `publish_id` bigint NOT NULL AUTO_INCREMENT COMMENT '发布记录ID',
  `task_id` varchar(64) NOT NULL COMMENT '公开任务编号',
  `code_id` bigint NOT NULL COMMENT '源码项目ID',
  `platform` varchar(32) NOT NULL COMMENT 'xiaohongshu/douyin/wechat_channels/bilibili/xianyu',
  `title` varchar(100) NOT NULL COMMENT '发布标题',
  `content` text COMMENT '发布文案',
  `tags` varchar(500) DEFAULT NULL COMMENT '标签，逗号分隔',
  `video_url` varchar(1000) DEFAULT NULL COMMENT '视频地址',
  `image_urls` text COMMENT '图片地址JSON数组',
  `cover_url` varchar(1000) DEFAULT NULL COMMENT '封面地址',
  `status` varchar(32) NOT NULL DEFAULT 'PENDING' COMMENT '发布状态',
  `status_message` varchar(255) DEFAULT NULL COMMENT '状态说明',
  `last_error` varchar(1000) DEFAULT NULL COMMENT '最近错误',
  `bridge_token` varchar(64) NOT NULL COMMENT '扩展短期访问凭证',
  `bridge_expires_at` datetime NOT NULL COMMENT '扩展凭证失效时间',
  `started_at` datetime DEFAULT NULL COMMENT '扩展开始执行时间',
  `ready_at` datetime DEFAULT NULL COMMENT '等待人工发布时间',
  `finished_at` datetime DEFAULT NULL COMMENT '任务结束时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`publish_id`),
  UNIQUE KEY `uk_publish_task_id` (`task_id`),
  KEY `idx_publish_code_platform` (`code_id`, `platform`, `publish_id`),
  KEY `idx_publish_status` (`status`),
  KEY `idx_publish_update_time` (`update_time`),
  KEY `idx_publish_bridge` (`task_id`, `bridge_token`, `bridge_expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多平台发布任务';

-- 兼容已有的小红书、抖音、视频号通用模板，使首次部署即可自动生成发布文案。
-- 闲鱼使用“闲鱼”分组中的启用模板；一个模板时直接使用，多个模板时随机选择。
-- B站使用“B站”分组中的启用模板；用户可在文案模板管理中单独创建。
-- 用户后续可在文案模板管理中分别创建“小红书”“抖音”“视频号”“B站”分组；
-- 平台专用分组存在时，会优先于这个共享分组。
UPDATE `tt_copy_template`
SET `template_group` = '抖音、视频号、小红书'
WHERE `template_name` = '复制抖音、视频号、小红书文案'
  AND (`template_group` IS NULL OR TRIM(`template_group`) = '' OR `template_group` = '默认分组');

-- 将发布管理放到源码管理所在目录。
SET @publish_parent_id := COALESCE(
  (SELECT parent_id FROM sys_menu WHERE perms = 'office:code:list' LIMIT 1),
  0
);
SET @publish_order_num := COALESCE(
  (SELECT MAX(order_num) + 1 FROM sys_menu WHERE parent_id = @publish_parent_id),
  1
);

INSERT INTO sys_menu
  (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache,
   menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT '发布管理', @publish_parent_id, @publish_order_num,
       'publish', 'office/publish/index', '', 1, 0,
       'C', '0', '0', 'office:publish:list', 'documentation', 'admin', NOW(), '', NULL,
       '浏览器扩展辅助发布管理'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'office:publish:list');

SET @publish_menu_id := (
  SELECT menu_id FROM sys_menu WHERE perms = 'office:publish:list' LIMIT 1
);

INSERT INTO sys_menu
  (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache,
   menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT '发布资料查询', @publish_menu_id, 1, '', '', '', 1, 0,
       'F', '0', '0', 'office:publish:query', '#', 'admin', NOW(), '', NULL, ''
WHERE @publish_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'office:publish:query');

INSERT INTO sys_menu
  (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache,
   menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT '创建发布任务', @publish_menu_id, 2, '', '', '', 1, 0,
       'F', '0', '0', 'office:publish:add', '#', 'admin', NOW(), '', NULL, ''
WHERE @publish_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'office:publish:add');