-- AI文章内容表
-- 存储由AI生成的文章内容及其生命周期状态
-- 支持多渠道发布（百度百家号、头条、微信等）
-- 记录文章从选题、生成、审核到发布的完整流程

CREATE TABLE `ai_articles`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `batch_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '批次ID，用于批量生成文章的分组',
  `topic_type_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '选题类型ID',
  `prompt_workflow_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '提示词工作流ID，关联AI生成模板',
  `topic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章选题/主题',
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章标题',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章正文内容',
  `department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '部门名称（遗留字段）',
  `departmentids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '部门ID列表（遗留字段）',
  `author_id` int NULL DEFAULT NULL COMMENT '作者ID，关联ai_authors.id（百家号账号）',
  `author_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '作者名称（百家号账号名）',
  `department_id` int NULL DEFAULT NULL COMMENT '部门ID',
  `department_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `created_user_id` int NOT NULL DEFAULT 0 COMMENT '创建用户ID，关联ai_users.id',
  `review_user_id` int NULL DEFAULT NULL COMMENT '审核用户ID，关联ai_users.id',
  `publish_user_id` int NULL DEFAULT NULL COMMENT '发布用户ID，关联ai_users.id',
  `status` enum('topic','cover_image','generate','generate_failed','draft','pending_review','approved','rejected','published_review','published','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'draft' COMMENT '文章状态：topic=选题|cover_image=封面图|generate=生成中|generate_failed=生成失败|draft=草稿|pending_review=待审核(文章已生成)|approved=审核通过|rejected=审核拒绝|published_review=发布审核中|published=已发布|failed=发布失败',
  `channel` tinyint(1) NOT NULL DEFAULT 1 COMMENT '发布渠道：1=百度百家号|2=今日头条|3=微信公众号',
  `review_comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '审核意见/备注',
  `publish_time` timestamp NULL DEFAULT NULL COMMENT '发布时间',
  `baijiahao_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '百家号文章ID',
  `baijiahao_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '百家号平台状态',
  `word_count` int NULL DEFAULT 0 COMMENT '文章字数',
  `image_count` int NULL DEFAULT 0 COMMENT '文章配图数量',
  `coze_tag` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Coze生成的标签',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  
  -- 索引定义
  INDEX `created_user_id`(`created_user_id` ASC) USING BTREE COMMENT '创建用户索引',
  INDEX `review_user_id`(`review_user_id` ASC) USING BTREE COMMENT '审核用户索引',
  INDEX `publish_user_id`(`publish_user_id` ASC) USING BTREE COMMENT '发布用户索引',
  INDEX `idx_articles_status_user_created`(`status` ASC, `created_user_id` ASC, `created_at` DESC) USING BTREE COMMENT '状态+创建用户+创建时间组合索引',
  INDEX `idx_articles_status_created`(`status` ASC, `created_at` DESC) USING BTREE COMMENT '状态+创建时间索引',
  INDEX `idx_articles_status`(`status` ASC) USING BTREE COMMENT '状态索引',
  INDEX `idx_articles_created_at`(`created_at` DESC) USING BTREE COMMENT '创建时间索引',
  INDEX `idx_status_id_author`(`status` ASC, `id` ASC, `author_id` ASC) USING BTREE COMMENT '状态+ID+作者组合索引',
  INDEX `idx_articles_updated_at`(`updated_at` DESC) USING BTREE COMMENT '更新时间索引',
  INDEX `idx_articles_status_prompt_topic_id`(`status` ASC, `prompt_workflow_id` ASC, `topic` ASC, `id` ASC) USING BTREE COMMENT '状态+工作流+选题+ID组合索引',
  INDEX `idx_status_author_updated_id`(`status` ASC, `author_id` ASC, `updated_at` ASC, `id` ASC) USING BTREE COMMENT '状态+作者+更新时间+ID组合索引',
  INDEX `idx_author_status_updated_id`(`author_id` ASC, `status` ASC, `updated_at` ASC, `id` ASC) USING BTREE COMMENT '作者+状态+更新时间+ID组合索引',
  
  -- 外键约束
  CONSTRAINT `ai_articles_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `ai_authors` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ai_articles_ibfk_2` FOREIGN KEY (`created_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ai_articles_ibfk_3` FOREIGN KEY (`review_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ai_articles_ibfk_4` FOREIGN KEY (`publish_user_id`) REFERENCES `ai_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1115 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;