-- Cleaned schema-only SQL for gemini_v5 (MySQL 5.7 compatible)
-- Generated: cleaned from uploaded gemini_v5.sql
-- This file contains CREATE TABLE and ALTER TABLE statements only (no seed data).
-- Character set: utf8mb4_general_ci (MySQL 5.7 friendly)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- gemini_v4_extended.sql
-- 完整数据库结构与初始化数据（包含文章与落地页生成功能）
-- =========================================================

SET NAMES utf8mb4;

-- =========================================================
-- 0. 清理旧表结构（可选）
-- =========================================================
DROP TABLE IF EXISTS `ai_landing_page_generation_tasks`;

-- =========================================================
-- 1) 公司与用户与审计日志
-- =========================================================
CREATE TABLE `ai_companies` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '公司主键ID',
  `name` VARCHAR(255) NOT NULL COMMENT '公司名称',
  `subdomain` VARCHAR(100) DEFAULT NULL COMMENT '公司子域名，用于多租户访问',
  `plan_type` ENUM('free','basic','premium','enterprise') DEFAULT 'free' COMMENT '订阅计划类型',
  `max_users` INT DEFAULT 5 COMMENT '允许的最大用户数',
  `max_articles_per_month` INT DEFAULT 100 COMMENT '每月最大文章生成数',
  `status` ENUM('active','suspended','trial') DEFAULT 'trial' COMMENT '公司状态',
  `trial_expiry_date` DATE DEFAULT NULL COMMENT '试用到期日',
  `default_settings` JSON DEFAULT NULL COMMENT '企业默认设置（JSON）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_companies_subdomain` (`subdomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='公司表（多租户根）';

CREATE TABLE `ai_users` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '用户主键ID',
  `company_id` INT NOT NULL COMMENT '所属公司ID（外键）',
  `username` VARCHAR(50) NOT NULL COMMENT '登录用户名',
  `email` VARCHAR(100) NOT NULL COMMENT '用户邮箱',
  `password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',
  `full_name` VARCHAR(100) DEFAULT NULL COMMENT '用户全名',
  `avatar_url` VARCHAR(255) DEFAULT NULL COMMENT '用户头像URL',
  `role` ENUM('admin','manager','editor','viewer') DEFAULT 'editor' COMMENT '用户角色',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用（1启用/0禁用）',
  `last_login` TIMESTAMP NULL DEFAULT NULL COMMENT '最近登录时间',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_username` (`username`),
  UNIQUE KEY `uk_users_email` (`email`),
  KEY `idx_users_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

CREATE TABLE `ai_audit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '审计日志主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '操作用户ID（外键）',
  `action` VARCHAR(100) NOT NULL COMMENT '操作类型（create/update/delete/publish等）',
  `target_table` VARCHAR(100) NOT NULL COMMENT '被操作表名',
  `target_id` INT NOT NULL COMMENT '被操作记录ID',
  `details` JSON DEFAULT NULL COMMENT '操作详情（JSON，可记录前后值）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `idx_audit_company` (`company_id`),
  KEY `idx_audit_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='操作审计日志';

-- =========================================================
-- 2) 平台 / 企业平台配置 / 定时发布 / 发布记录
-- =========================================================
CREATE TABLE `ai_publishing_platform_types` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '平台类型主键ID',
  `name` VARCHAR(50) NOT NULL COMMENT '平台类型名称（如 Blog, Social, Video）',
  `description` VARCHAR(255) DEFAULT NULL COMMENT '类型描述',
  `icon` VARCHAR(100) DEFAULT NULL COMMENT '图标标识',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_platform_types_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='平台类型表';

CREATE TABLE `ai_publishing_platforms` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '平台主键ID',
  `type_id` INT NOT NULL COMMENT '平台类型ID（外键）',
  `name` VARCHAR(100) NOT NULL COMMENT '平台名称（如 WordPress, LinkedIn, 小红书）',
  `code` VARCHAR(50) NOT NULL COMMENT '平台代码（唯一）',
  `description` VARCHAR(255) DEFAULT NULL COMMENT '平台说明',
  `icon` VARCHAR(100) DEFAULT NULL COMMENT '平台图标',
  `auth_type` ENUM('oauth','api_key','basic','custom') NOT NULL COMMENT '认证类型',
  `api_config_template` JSON DEFAULT NULL COMMENT 'API 配置模板（JSON schema）',
  `character_limit` INT DEFAULT NULL COMMENT '平台字符限制（如微博）',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_platforms_code` (`code`),
  KEY `idx_platforms_type` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='平台表';

CREATE TABLE `ai_company_platform_configs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '企业平台配置主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `platform_id` INT NOT NULL COMMENT '平台ID（外键）',
  `config_data` JSON NOT NULL COMMENT '平台API配置信息（如 token、client_id）',
  `account_name` VARCHAR(100) DEFAULT NULL COMMENT '企业在该平台的账号名/展示名',
  `is_enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用该配置',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_company_platform` (`company_id`,`platform_id`),
  KEY `idx_company_platform_company` (`company_id`),
  KEY `idx_company_platform_platform` (`platform_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='企业在各平台的配置（API key/Token等）';

CREATE TABLE `ai_scheduled_publishing_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '定时发布任务主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `publishable_type` VARCHAR(50) DEFAULT 'article' COMMENT '发布内容类型（article, landing_page）',
  `publishable_id` INT NOT NULL COMMENT '发布内容ID',
  `article_id` INT DEFAULT NULL COMMENT '要发布的文章ID（外键） - 兼容旧版',
  `platform_config_id` INT NOT NULL COMMENT '使用的企业平台配置ID（外键）',
  `schedule_time` TIMESTAMP NOT NULL COMMENT '计划发布时间',
  `status` ENUM('scheduled','processing','success','failed','cancelled') DEFAULT 'scheduled' COMMENT '任务状态',
  `retry_count` INT DEFAULT 0 COMMENT '已重试次数',
  `max_retries` INT DEFAULT 3 COMMENT '最大重试次数',
  `last_attempt_at` TIMESTAMP NULL DEFAULT NULL COMMENT '最近一次尝试执行时间',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息（失败时记录）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_schedule_company_time` (`company_id`, `schedule_time`),
  KEY `idx_schedule_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='定时发布任务表';

CREATE TABLE `ai_publishing_records` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '发布记录主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `publishable_type` VARCHAR(50) DEFAULT 'article' COMMENT '发布内容类型（article, landing_page）',
  `publishable_id` INT NOT NULL COMMENT '发布内容ID',
  `article_id` INT DEFAULT NULL COMMENT '文章ID（外键） - 兼容旧版',
  `platform_config_id` INT NOT NULL COMMENT '企业平台配置ID（外键）',
  `scheduled_task_id` INT DEFAULT NULL COMMENT '来源的定时任务ID（如果是定时发布则关联）',
  `status` ENUM('scheduled','published','failed') DEFAULT 'scheduled' COMMENT '发布记录状态',
  `publish_url` VARCHAR(500) DEFAULT NULL COMMENT '发布后平台返回的文章URL',
  `external_post_id` VARCHAR(255) DEFAULT NULL COMMENT '平台返回的外部帖文/文章ID',
  `publish_time` TIMESTAMP NULL DEFAULT NULL COMMENT '实际发布时间',
  `error_message` TEXT DEFAULT NULL COMMENT '失败原因（若失败）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_publishing_company_time` (`company_id`,`publish_time`),
  KEY `idx_publishing_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='发布记录表（执行日志）';

-- =========================================================
-- 3) AI 模型配置 / Prompt 模板 / 文章类型 / 上传文件
-- =========================================================
CREATE TABLE `ai_article_types` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章类型主键ID',
  `name` VARCHAR(50) NOT NULL COMMENT '文章类型名称（如产品介绍）',
  `description` VARCHAR(255) DEFAULT NULL COMMENT '类型说明',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_article_types_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章类型表';

CREATE TABLE `ai_dify_api_configs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'AI配置主键ID',
  `company_id` INT DEFAULT NULL COMMENT '公司ID（NULL 表示共享/通用）',
  `provider` ENUM('dify','openai','anthropic','google','azure_openai','other') DEFAULT 'dify' COMMENT 'AI 提供方',
  `name` VARCHAR(100) NOT NULL COMMENT '配置名称（便于识别）',
  `base_url` VARCHAR(255) DEFAULT NULL COMMENT 'API 基础地址（可选）',
  `api_key` VARCHAR(255) DEFAULT NULL COMMENT 'API Key/Token',
  `model_name` VARCHAR(100) DEFAULT NULL COMMENT '模型名称',
  `temperature` DECIMAL(3,2) DEFAULT 0.70 COMMENT '默认温度值',
  `top_p` DECIMAL(3,2) DEFAULT 1.00 COMMENT 'TopP 值',
  `max_tokens` INT DEFAULT 2048 COMMENT '最大生成 token 数',
  `request_headers` JSON DEFAULT NULL COMMENT '额外请求头（JSON）',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_dify_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='AI 服务配置（Dify/OpenAI 等）';

CREATE TABLE `ai_prompt_templates` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Prompt 模板主键ID',
  `company_id` INT DEFAULT NULL COMMENT '公司ID（NULL 表示系统模板）',
  `name` VARCHAR(100) NOT NULL COMMENT '模板名称',
  `description` VARCHAR(255) DEFAULT NULL COMMENT '模板描述',
  `language` VARCHAR(20) DEFAULT 'zh' COMMENT '默认语言编码（en/zh 等）',
  `content` LONGTEXT NOT NULL COMMENT '模板内容（可含变量占位符）',
  `variables` JSON DEFAULT NULL COMMENT '变量说明（JSON）',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_prompt_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Prompt 模板表';

CREATE TABLE `ai_uploaded_files` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '上传文件主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '上传者用户ID（外键）',
  `file_name` VARCHAR(255) NOT NULL COMMENT '原始文件名',
  `file_path` VARCHAR(500) NOT NULL COMMENT '服务器存储路径或外部URL',
  `file_type` ENUM('knowledge','image','video','document','other') NOT NULL COMMENT '文件类型',
  `file_size` BIGINT DEFAULT 0 COMMENT '文件大小（字节）',
  `mime_type` VARCHAR(100) DEFAULT NULL COMMENT 'MIME 类型',
  `checksum` VARCHAR(64) DEFAULT NULL COMMENT '校验和（可选）',
  `version` INT DEFAULT 1 COMMENT '版本号（用于版本控制）',
  `status` ENUM('active','archived','deleted') DEFAULT 'active' COMMENT '文件状态',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  PRIMARY KEY (`id`),
  KEY `idx_files_company` (`company_id`),
  KEY `idx_files_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='上传文件表（知识库/媒体等）';

-- =========================================================
-- 4) 关键词 / 扩展 / 话题来源 / 话题抓取任务 / 话题
-- =========================================================
CREATE TABLE `ai_keywords` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '关键词主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `keyword` VARCHAR(255) NOT NULL COMMENT '关键词文本',
  `search_volume` INT DEFAULT 0 COMMENT '搜索量估算',
  `cpc` DECIMAL(10,2) DEFAULT 0.00 COMMENT '估计每次点击成本',
  `difficulty` TINYINT DEFAULT 0 COMMENT '关键词难度评分（0-100）',
  `source` ENUM('baidu','google','manual','expansion','import') DEFAULT 'manual' COMMENT '来源类型',
  `status` ENUM('pending','planned','generated','published') DEFAULT 'pending' COMMENT '关键词状态',
  `tags` VARCHAR(255) DEFAULT NULL COMMENT '关键词标签，逗号分隔',
  `usage_count` INT DEFAULT 0 COMMENT '关键词被文章使用次数',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_company_keyword` (`company_id`,`keyword`),
  KEY `idx_keywords_company_status` (`company_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='关键词表';

CREATE TABLE `ai_keyword_expansions` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '关键词扩展主键ID',
  `keyword_id` INT NOT NULL COMMENT '原始关键词ID（外键）',
  `related_keyword` VARCHAR(255) NOT NULL COMMENT '扩展关键词（来自 related_searches 等）',
  `source` ENUM('baidu','google','import') DEFAULT 'google' COMMENT '扩展来源',
  `raw_response` JSON DEFAULT NULL COMMENT 'API 原始返回（便于溯源）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_keyword_exp_keyword` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='关键词扩展表（保存 related_searches）';

CREATE TABLE `ai_topic_sources` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '话题来源配置主键ID',
  `name` VARCHAR(100) NOT NULL COMMENT '来源名称（如 百度前10、Google also ask）',
  `engine` VARCHAR(50) NOT NULL COMMENT '搜索引擎参数（baidu/google等）',
  `query_pattern` VARCHAR(500) NOT NULL COMMENT '查询模式（可包含占位符 {keyword}）',
  `result_field` VARCHAR(100) NOT NULL COMMENT 'API 返回需取的字段（organic_results/related_questions等）',
  `max_results` INT DEFAULT 10 COMMENT '最大获取条数',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='话题来源配置表';

CREATE TABLE `ai_topic_fetch_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '话题抓取任务主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '发起任务的用户ID（外键）',
  `topic_source_id` INT NOT NULL COMMENT '话题来源配置ID（外键）',
  `keywords` TEXT DEFAULT NULL COMMENT '用于本次抓取的关键词快照（逗号分隔）',
  `status` ENUM('pending','processing','completed','failed') DEFAULT 'pending' COMMENT '任务状态',
  `result_count` INT DEFAULT 0 COMMENT '抓取结果数量',
  `raw_response` JSON DEFAULT NULL COMMENT 'API 原始返回（JSON）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT '任务完成时间',
  PRIMARY KEY (`id`),
  KEY `idx_topic_fetch_company` (`company_id`),
  KEY `idx_topic_fetch_source` (`topic_source_id`),
  KEY `idx_topic_fetch_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='话题抓取任务表';

CREATE TABLE `ai_topics` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '话题主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `source_task_id` INT DEFAULT NULL COMMENT '来源的抓取任务ID（外键，可空）',
  `title` VARCHAR(255) NOT NULL COMMENT '话题标题',
  `description` TEXT DEFAULT NULL COMMENT '话题描述或摘要',
  `source_url` VARCHAR(500) DEFAULT NULL COMMENT '原始来源链接（可选）',
  `status` ENUM('raw','curated','rejected') DEFAULT 'raw' COMMENT '话题状态',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_topics_company_status` (`company_id`,`status`),
  KEY `idx_topics_source_task` (`source_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='话题表';

CREATE TABLE `ai_topic_keywords` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '话题-关键词关联主键ID',
  `topic_id` INT NOT NULL COMMENT '话题ID（外键）',
  `keyword_id` INT NOT NULL COMMENT '关键词ID（外键）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_topic_keyword` (`topic_id`,`keyword_id`),
  KEY `idx_topic_keywords_topic` (`topic_id`),
  KEY `idx_topic_keywords_keyword` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='话题与关键词的多对多映射';

-- =========================================================
-- 5) 文章生成配置 / 任务 / 任务引用 / 任务关键词 / 任务话题 / 任务知识库
-- =========================================================
CREATE TABLE `ai_article_generation_configs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章生成配置主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `name` VARCHAR(100) NOT NULL COMMENT '配置名称',
  `platform_id` INT DEFAULT NULL COMMENT '适用发布平台ID（可选）',
  `article_type_id` INT DEFAULT NULL COMMENT '文章类型ID（外键）',
  `writing_language` VARCHAR(20) DEFAULT 'en' COMMENT '写作语言（en/zh）',
  `remove_ai_tone` TINYINT(1) DEFAULT 1 COMMENT '是否去除AI味道',
  `ai_taste_level` ENUM('colloquial','junior_high','senior_high','professional') DEFAULT 'junior_high' COMMENT 'AI风格等级',
  `article_length_min` INT DEFAULT 800 COMMENT '最小长度（字符）',
  `article_length_max` INT DEFAULT 1500 COMMENT '最大长度（字符）',
  `auto_seo_optimization` TINYINT(1) DEFAULT 1 COMMENT '自动SEO优化',
  `keyword_density_min` DECIMAL(5,2) DEFAULT 1.00 COMMENT '关键词密度下限(%)',
  `keyword_density_max` DECIMAL(5,2) DEFAULT 2.00 COMMENT '关键词密度上限(%)',
  `auto_geo_optimization` TINYINT(1) DEFAULT 1 COMMENT '是否自动GEO优化',
  `auto_structured_data` TINYINT(1) DEFAULT 1 COMMENT '是否自动生成结构化数据',
  `multi_version_count` INT DEFAULT 3 COMMENT '生成版本数量',
  `extra_options` JSON DEFAULT NULL COMMENT '额外选项（JSON）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_config_company` (`company_id`),
  KEY `idx_config_platform` (`platform_id`),
  KEY `idx_config_article_type` (`article_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='AI 文章生成配置';

CREATE TABLE `ai_article_generation_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章生成任务主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '发起用户ID（外键）',
  `config_id` INT DEFAULT NULL COMMENT '使用的生成配置ID（外键）',
  `article_theme` VARCHAR(255) DEFAULT NULL COMMENT '文章主题/标题（输入）',
  `topic_ids` TEXT DEFAULT NULL COMMENT '所选话题ID列表（逗号分隔）',
  `reference_urls` TEXT DEFAULT NULL COMMENT '参考URL列表（逗号分隔）',
  `reference_content` LONGTEXT DEFAULT NULL COMMENT '高度参考链接抓取到的内容摘要',
  `status` ENUM('pending','processing','completed','failed') DEFAULT 'pending' COMMENT '任务状态',
  `progress` TINYINT DEFAULT 0 COMMENT '进度（0-100）',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
  `dify_api_config_id` INT DEFAULT NULL COMMENT '调用的AI配置ID（外键）',
  `prompt_template_id` INT DEFAULT NULL COMMENT '使用的 Prompt 模板 ID（外键）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT '完成时间',
  PRIMARY KEY (`id`),
  KEY `idx_task_company_status` (`company_id`,`status`),
  KEY `idx_task_user` (`user_id`),
  KEY `idx_task_config` (`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章生成任务';

CREATE TABLE `ai_task_references` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务参考链接主键ID',
  `task_id` INT NOT NULL COMMENT '对应任务ID（外键）',
  `url` VARCHAR(500) NOT NULL COMMENT '参考链接URL',
  `title` VARCHAR(255) DEFAULT NULL COMMENT '页面标题（可选）',
  `source_engine` VARCHAR(50) DEFAULT NULL COMMENT '来源引擎（google/baidu/zhihu等）',
  `position` INT DEFAULT NULL COMMENT '在搜索结果中的排名位置',
  `is_high_reference` TINYINT(1) DEFAULT 0 COMMENT '是否标记为高度参考（1是）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_task_references_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='任务参考链接明细（可标注高度参考）';

CREATE TABLE `ai_task_keywords` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务-关键词关联主键ID',
  `task_id` INT NOT NULL COMMENT '生成任务ID（外键）',
  `keyword_id` INT NOT NULL COMMENT '关键词ID（外键）',
  `is_primary` TINYINT(1) DEFAULT 0 COMMENT '是否主关键词',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_task_keyword` (`task_id`,`keyword_id`),
  KEY `idx_task_keywords_task` (`task_id`),
  KEY `idx_task_keywords_keyword` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='生成任务与关键词映射（规划用）';

CREATE TABLE `ai_task_topics` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务-话题关联主键ID',
  `task_id` INT NOT NULL COMMENT '生成任务ID（外键）',
  `topic_id` INT NOT NULL COMMENT '话题ID（外键）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_task_topic` (`task_id`,`topic_id`),
  KEY `idx_task_topics_task` (`task_id`),
  KEY `idx_task_topics_topic` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='生成任务与话题映射';

CREATE TABLE `ai_task_knowledge_sources` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务知识来源主键ID',
  `task_id` INT NOT NULL COMMENT '任务ID（外键）',
  `knowledge_type` ENUM('company','uploaded') NOT NULL COMMENT '知识来源类型（公司知识库/上传文件）',
  `knowledge_id` INT DEFAULT NULL COMMENT '当 knowledge_type=uploaded 时，关联 ai_uploaded_files.id',
  `content` LONGTEXT DEFAULT NULL COMMENT '若上传文件被解析，存解析后的文本内容',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_task_knowledge_task` (`task_id`),
  KEY `idx_task_knowledge_knowledge` (`knowledge_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='任务知识库来源（公司库或上传的解析内容）';

-- =========================================================
-- 6) 文章 / 文章关键词 / 文章话题 / FAQ / 结构化数据 / 媒体 / sameAs / 翻译
-- =========================================================
CREATE TABLE `ai_generated_articles` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '生成文章主键ID',
  `task_id` INT DEFAULT NULL COMMENT '来源生成任务ID（外键，可空）',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `version` INT DEFAULT 1 COMMENT '文章版本号（用于多版本）',
  `title` VARCHAR(255) DEFAULT NULL COMMENT '文章标题',
  `content` LONGTEXT DEFAULT NULL COMMENT '文章纯文本内容（不含HTML）',
  `html_content` LONGTEXT DEFAULT NULL COMMENT '文章HTML格式内容（含排版）',
  `faq_section` JSON DEFAULT NULL COMMENT 'FAQ 部分（JSON）',
  `structured_data` JSON DEFAULT NULL COMMENT '结构化数据 JSON-LD（全文）',
  `word_count` INT DEFAULT 0 COMMENT '文章字数统计',
  `keyword_density` JSON DEFAULT NULL COMMENT '关键词密度分析结果（JSON）',
  `is_selected` TINYINT(1) DEFAULT 0 COMMENT '是否被选为最终版本（1是）',
  `status` ENUM('draft','approved','archived','deleted') DEFAULT 'draft' COMMENT '文章状态',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_articles_company_status` (`company_id`,`status`),
  KEY `idx_articles_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='生成的文章表（多版本支持）';

CREATE TABLE `ai_article_keywords` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章关键词关联主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `keyword_id` INT NOT NULL COMMENT '关键词ID（外键）',
  `is_primary` TINYINT(1) DEFAULT 0 COMMENT '是否主关键词（1主/0辅）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_article_keyword` (`article_id`,`keyword_id`),
  KEY `idx_article_keywords_article` (`article_id`),
  KEY `idx_article_keywords_keyword` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章与关键词关联（实际使用）';

CREATE TABLE `ai_article_topics` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章话题关联主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `topic_id` INT NOT NULL COMMENT '话题ID（外键）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_article_topic` (`article_id`,`topic_id`),
  KEY `idx_article_topics_article` (`article_id`),
  KEY `idx_article_topics_topic` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章与话题关联';

CREATE TABLE `ai_article_faqs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章 FAQ 主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `question` VARCHAR(255) NOT NULL COMMENT 'FAQ 问题',
  `answer` TEXT NOT NULL COMMENT 'FAQ 回答',
  PRIMARY KEY (`id`),
  KEY `idx_article_faqs_article` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章 FAQ 列表';

CREATE TABLE `ai_article_structured_data` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '结构化数据主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `json_ld` JSON NOT NULL COMMENT 'JSON-LD 结构化数据内容',
  PRIMARY KEY (`id`),
  KEY `idx_structured_article` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章结构化数据（单独表便于管理/更新）';

CREATE TABLE `ai_article_media` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章媒体资源主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `media_type` ENUM('image','video','audio') NOT NULL COMMENT '媒体类型',
  `url` VARCHAR(500) NOT NULL COMMENT '媒体链接或存储路径',
  `prompt` TEXT DEFAULT NULL COMMENT '生成提示语（AI 配图时记录）',
  `source_type` ENUM('ai_generated','user_provided','external') DEFAULT 'external' COMMENT '资源来源类型',
  `alt_text` VARCHAR(255) DEFAULT NULL COMMENT '图片替代文本（SEO）',
  `caption` VARCHAR(255) DEFAULT NULL COMMENT '图片说明/标题',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_media_article` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章媒体资源（多张图片/多视频）';

CREATE TABLE `ai_article_sameas` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'sameAs 主键ID',
  `article_id` INT NOT NULL COMMENT '文章ID（外键）',
  `label` VARCHAR(100) DEFAULT NULL COMMENT '来源标识标签（如 Wikipedia）',
  `url` VARCHAR(500) NOT NULL COMMENT '权威链接 URL',
  `is_auto` TINYINT(1) DEFAULT 1 COMMENT '是否自动生成（1自动/0自定义）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_sameas_article` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章权威证明 sameAs 链接';

CREATE TABLE `ai_article_translations` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章翻译主键ID',
  `article_id` INT NOT NULL COMMENT '原文章ID（外键）',
  `language` VARCHAR(20) NOT NULL COMMENT '翻译目标语言（如 en/zh）',
  `title` VARCHAR(255) DEFAULT NULL COMMENT '译文标题',
  `content` LONGTEXT DEFAULT NULL COMMENT '译文纯文本内容',
  `html_content` LONGTEXT DEFAULT NULL COMMENT '译文 HTML 内容',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_article_language` (`article_id`,`language`),
  KEY `idx_translations_article` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章多语言翻译表';

-- =========================================================
-- 7) 统计 / 活动日志 / 使用量
-- =========================================================
CREATE TABLE `ai_user_activity_logs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '用户活动日志主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '用户ID（外键）',
  `activity_type` VARCHAR(50) NOT NULL COMMENT '活动类型（如 login/create_article）',
  `activity_details` JSON DEFAULT NULL COMMENT '活动详情（JSON）',
  `ip_address` VARCHAR(45) DEFAULT NULL COMMENT '操作IP',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_activity_company_user` (`company_id`,`user_id`),
  KEY `idx_activity_type` (`activity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户活动日志（审计/分析）';

CREATE TABLE `ai_company_usage_stats` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '公司用量统计主键ID',
  `company_id` INT NOT NULL COMMENT '公司ID（外键）',
  `stat_date` DATE NOT NULL COMMENT '统计日期',
  `article_count` INT DEFAULT 0 COMMENT '当日生成文章数量',
  `ai_token_usage` BIGINT DEFAULT 0 COMMENT '当日 AI 令牌使用量',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_company_date` (`company_id`,`stat_date`),
  KEY `idx_usage_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='公司使用统计';

-- =========================================================
-- START: LANDING PAGE BUILDER EXTENSION
-- =========================================================

-- =========================================================
-- 12) 落地页模板与项目核心表
-- =========================================================

CREATE TABLE `ai_landing_page_templates` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '落地页模板主键ID',
  `name` VARCHAR(100) NOT NULL COMMENT '模板名称（如：单栏布局）',
  `code` VARCHAR(50) NOT NULL COMMENT '模板代码（如：single-column）',
  `description` VARCHAR(255) DEFAULT NULL COMMENT '模板描述',
  `preview_image_url` VARCHAR(255) DEFAULT NULL COMMENT '预览图URL',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lp_templates_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='落地页布局与设计模板';

CREATE TABLE `ai_landing_page_projects` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '落地页项目主键ID',
  `company_id` INT NOT NULL COMMENT '所属公司ID（外键）',
  `user_id` INT NOT NULL COMMENT '创建者用户ID（外键）',
  `name` VARCHAR(255) NOT NULL COMMENT '落地页项目名称（用于内部识别）',
  `status` ENUM('draft','configuring','generated','published','archived') DEFAULT 'draft' COMMENT '项目状态',
  `last_step_completed` INT DEFAULT 0 COMMENT '最后完成的步骤号',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_lp_projects_company` (`company_id`),
  KEY `idx_lp_projects_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='AI落地页构建项目表';

-- =========================================================
-- 13) 各步骤配置信息表
-- =========================================================

CREATE TABLE `ai_landing_page_step_configs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '配置主键ID',
  `project_id` INT NOT NULL COMMENT '落地页项目ID（外键）',
  -- Step 1: 目标用户
  `target_audience_desc` TEXT COMMENT '目标用户描述',
  `user_pain_points` TEXT COMMENT '用户痛点（可JSON或换行分隔）',
  `user_expectations` TEXT COMMENT '用户期望结果',
  `age_groups` VARCHAR(255) DEFAULT NULL COMMENT '年龄段（逗号分隔）',
  `gender_preference` ENUM('male','female','balanced') DEFAULT 'balanced' COMMENT '性别倾向',
  `behavior_characteristics` VARCHAR(255) DEFAULT NULL COMMENT '用户行为特征（逗号分隔）',
  `decision_making_styles` VARCHAR(255) DEFAULT NULL COMMENT '用户决策方式（逗号分隔）',
  -- Step 2: 落地页目标
  `industry_primary` VARCHAR(100) DEFAULT NULL COMMENT '一级行业',
  `industry_secondary` VARCHAR(100) DEFAULT NULL COMMENT '二级行业',
  `industry_tertiary` VARCHAR(100) DEFAULT NULL COMMENT '子分类',
  `marketing_goal` VARCHAR(50) DEFAULT NULL COMMENT '营销目标（lead-collection, product-sales等）',
  -- Step 3: 风格与配色
  `design_style` VARCHAR(50) DEFAULT NULL COMMENT '设计风格（modern, professional等）',
  `primary_color` VARCHAR(20) DEFAULT NULL COMMENT '主色调',
  `accent_color` VARCHAR(20) DEFAULT NULL COMMENT '辅助色调',
  `template_id` INT DEFAULT NULL COMMENT '布局模板ID（外键）',
  -- Step 4: 核心卖点
  `unique_value_proposition` TEXT COMMENT '独特价值主张',
  `core_advantages` JSON DEFAULT NULL COMMENT '核心优势列表（JSON数组）',
  `primary_keyword` VARCHAR(255) DEFAULT NULL COMMENT '主要关键词',
  `secondary_keywords` JSON DEFAULT NULL COMMENT '次要关键词列表（JSON数组）',
  -- Step 5: 页面内容
  `content_generation_type` ENUM('ai','custom','upload') DEFAULT 'ai' COMMENT '内容生成方式',
  `company_name` VARCHAR(255) DEFAULT NULL COMMENT '公司全称',
  `brand_name` VARCHAR(255) DEFAULT NULL COMMENT '品牌名称',
  `company_description` TEXT COMMENT '公司介绍',
  `video_url` VARCHAR(500) DEFAULT NULL COMMENT '视频链接',
  `logo_file_id` INT DEFAULT NULL COMMENT '公司Logo文件ID（关联ai_uploaded_files）',
  `contact_info` JSON DEFAULT NULL COMMENT '联系信息（地址、电话、邮箱、工作时间）',
  `social_media_links` JSON DEFAULT NULL COMMENT '社交媒体链接（JSON数组）',
  -- Step 6: CTA
  `primary_cta_text` VARCHAR(100) DEFAULT NULL COMMENT '主要CTA按钮文案',
  `secondary_cta_texts` JSON DEFAULT NULL COMMENT '次要CTA按钮文案（JSON数组）',
  -- Step 7: 信任元素
  `testimonials` JSON DEFAULT NULL COMMENT '客户评价/推荐语（JSON数组，含姓名、职位、内容）',
  `social_proofs` JSON DEFAULT NULL COMMENT '社会证明数据（JSON数组，含标签、数值）',
  -- Step 8: 表单字段
  `form_fields` JSON DEFAULT NULL COMMENT '表单字段配置（JSON对象，key为字段名，value为是否启用）',
  -- Step 9: 生成与部署
  `page_title` VARCHAR(255) DEFAULT NULL COMMENT '页面SEO标题',
  `page_description` TEXT COMMENT '页面SEO描述',
  `ga_tracking_code` TEXT COMMENT '谷歌广告跟踪代码',
  `deployment_method` ENUM('ftp','link') DEFAULT 'link' COMMENT '部署方式',
  `deployment_config` JSON DEFAULT NULL COMMENT '部署配置（FTP信息或子域名）',
  `pricing_plan` VARCHAR(50) DEFAULT NULL COMMENT '选择的套餐（basic, pro, enterprise）',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lp_config_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='落地页各步骤配置数据';

CREATE TABLE `ai_landing_page_assets` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '资产主键ID',
  `project_id` INT NOT NULL COMMENT '落地页项目ID（外键）',
  `asset_type` ENUM('product_image','certification_logo','testimonial_avatar') NOT NULL COMMENT '资产类型',
  `uploaded_file_id` INT NOT NULL COMMENT '上传文件ID（外键）',
  `related_data` JSON DEFAULT NULL COMMENT '相关数据（如关联的产品名或评价人）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_lp_assets_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='落地页项目关联的媒体资产';

-- =========================================================
-- 14) 最终生成的落地页
-- =========================================================

CREATE TABLE `ai_generated_landing_pages` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '生成落地页主键ID',
  `project_id` INT NOT NULL COMMENT '来源项目ID（外键）',
  `version_code` VARCHAR(20) NOT NULL DEFAULT 'A' COMMENT '版本标识（用于A/B测试，如A, B）',
  `html_content` LONGTEXT COMMENT '落地页HTML内容',
  `status` ENUM('draft','final','published') DEFAULT 'draft' COMMENT '版本状态',
  `publish_url` VARCHAR(500) DEFAULT NULL COMMENT '发布后的URL（使用link方式时）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lp_project_version` (`project_id`, `version_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='最终生成的落地页（支持多版本）';

-- =========================================================
-- 15) 落地页生成任务表（连接配置与AI模型）
-- =========================================================

CREATE TABLE `ai_landing_page_generation_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务主键ID',
  `project_id` INT NOT NULL COMMENT '落地页项目ID (外键)',
  `user_id` INT NOT NULL COMMENT '发起用户ID (外键)',
  `status` ENUM('pending','processing','completed','failed') DEFAULT 'pending' COMMENT '任务状态',
  `progress` TINYINT DEFAULT 0 COMMENT '进度 (0-100)',
  `dify_api_config_id` INT DEFAULT NULL COMMENT '调用的AI配置ID (外键, 关联 ai_dify_api_configs)',
  `prompt_template_id` INT DEFAULT NULL COMMENT '使用的Prompt模板ID (外键, 关联 ai_prompt_templates)',
  `final_prompt_snapshot` LONGTEXT DEFAULT NULL COMMENT '发送给AI的最终Prompt快照（用于调试和记录）',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT '完成时间',
  PRIMARY KEY (`id`),
  KEY `idx_lp_task_project` (`project_id`),
  KEY `idx_lp_task_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='落地页AI生成任务表';

-- 18) 网站项目与核心结构表 (高级版)
-- =========================================================

CREATE TABLE `ai_website_projects` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '网站项目主键ID',
  `company_id` INT NOT NULL COMMENT '所属公司ID (外键)',
  `user_id` INT NOT NULL COMMENT '创建者用户ID (外键)',
  `project_name` VARCHAR(255) NOT NULL COMMENT '项目名称 (用于内部识别)',
  `site_name` VARCHAR(255) DEFAULT NULL COMMENT '最终生成的网站名称',
  `status` ENUM('draft','configuring','generating','completed','published') DEFAULT 'draft' COMMENT '项目状态',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_website_projects_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='AI网站构建项目总表';

CREATE TABLE `ai_website_build_configs` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '配置主键ID',
  `project_id` INT NOT NULL COMMENT '网站项目ID (外键)',
  `website_type` VARCHAR(50) DEFAULT NULL COMMENT '网站类型 (business, portfolio等)',
  `site_identity` JSON DEFAULT NULL COMMENT '网站身份信息 (名称, 标语, 描述, 关键词)',
  `design_preferences` JSON DEFAULT NULL COMMENT '设计偏好 (风格, 主色调)',
  `sitemap_structure` JSON DEFAULT NULL COMMENT '站点地图结构定义 (JSON, 描述栏目层级和类型)',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_website_config_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='AI网站构建器配置(存储站点地图)';

CREATE TABLE `ai_website_channels` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '网站栏目主键ID',
  `project_id` INT NOT NULL COMMENT '所属网站项目ID (外键)',
  `parent_id` INT DEFAULT NULL COMMENT '父栏目ID (用于实现层级结构)',
  `name` VARCHAR(100) NOT NULL COMMENT '栏目名称 (如: 公司介绍, 新闻中心)',
  `path` VARCHAR(100) NOT NULL COMMENT 'URL路径 (如: /about, /news)',
  `channel_type` ENUM('single_page', 'article_list', 'product_list', 'custom') NOT NULL COMMENT '栏目类型',
  `display_order` INT DEFAULT 0 COMMENT '显示顺序',
  `is_visible_in_nav` TINYINT(1) DEFAULT 1 COMMENT '是否在主导航中可见',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_channel_project` (`project_id`),
  KEY `idx_channel_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站栏目结构表 (支持无限层级)';

-- =========================================================
-- 19) 网站内容模型表 (文章、产品等)
-- =========================================================

CREATE TABLE `ai_website_articles` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '文章主键ID',
  `project_id` INT NOT NULL COMMENT '所属网站项目ID (外键)',
  `channel_id` INT NOT NULL COMMENT '所属文章栏目ID (外键)',
  `title` VARCHAR(255) NOT NULL COMMENT '文章标题',
  `summary` TEXT DEFAULT NULL COMMENT '文章摘要',
  `content` LONGTEXT COMMENT '文章主体内容 (Markdown或HTML)',
  `status` ENUM('draft', 'published') DEFAULT 'draft' COMMENT '发布状态',
  `published_at` TIMESTAMP NULL DEFAULT NULL COMMENT '发布时间',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_article_project_channel` (`project_id`, `channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站文章内容表';

CREATE TABLE `ai_website_products` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '产品主键ID',
  `project_id` INT NOT NULL COMMENT '所属网站项目ID (外键)',
  `channel_id` INT NOT NULL COMMENT '所属产品栏目ID (外键)',
  `name` VARCHAR(255) NOT NULL COMMENT '产品名称',
  `description` LONGTEXT COMMENT '产品详细描述',
  `specifications` JSON DEFAULT NULL COMMENT '产品规格 (JSON格式)',
  `main_image_url` VARCHAR(255) DEFAULT NULL COMMENT '产品主图URL',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_product_project_channel` (`project_id`, `channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站产品内容表';

-- =========================================================
-- 20) AI建站生成任务与最终产物 (高级版)
-- =========================================================

CREATE TABLE `ai_website_generation_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '任务主键ID',
  `project_id` INT NOT NULL COMMENT '网站项目ID (外键)',
  `user_id` INT NOT NULL COMMENT '发起用户ID (外键)',
  `parent_task_id` INT DEFAULT NULL COMMENT '父任务ID (用于父子任务)',
  `task_type` ENUM('full_site', 'generate_shell', 'generate_page_content', 'regenerate_channel') NOT NULL COMMENT '任务类型',
  `target_id` INT DEFAULT NULL COMMENT '任务目标ID (如特定页面或栏目ID)',
  `status` ENUM('pending','processing','completed','failed', 'waiting_for_children') DEFAULT 'pending' COMMENT '任务状态',
  `dify_api_config_id` INT DEFAULT NULL COMMENT '调用的AI配置ID (外键)',
  `config_snapshot` JSON DEFAULT NULL COMMENT '生成时刻的配置快照',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT '完成时间',
  PRIMARY KEY (`id`),
  KEY `idx_website_task_project` (`project_id`),
  KEY `idx_website_task_parent` (`parent_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站AI生成任务表(支持父子任务)';

CREATE TABLE `ai_generated_website_pages` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '生成页面主键ID',
  `project_id` INT NOT NULL COMMENT '来源网站项目ID (外键)',
  `task_id` INT NOT NULL COMMENT '来源生成任务ID (外键)',
  `pageable_type` VARCHAR(100) NOT NULL COMMENT '关联类型 (Channel, Article, Product)',
  `pageable_id` INT NOT NULL COMMENT '关联类型ID',
  `file_name` VARCHAR(100) NOT NULL COMMENT '文件名 (如: index.html, news/article-1.html)',
  `html_content` LONGTEXT COMMENT '页面的完整HTML内容',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
  PRIMARY KEY (`id`),
  KEY `idx_pageable` (`pageable_type`, `pageable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='生成的网站页面(映射到栏目或内容项)';

-- =========================================================
-- 21) 为新增的表添加外键约束
-- =========================================================
ALTER TABLE `ai_website_projects`

ALTER TABLE `ai_website_build_configs`
  ADD CONSTRAINT `fk_website_build_config_project` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE;

ALTER TABLE `ai_website_channels`
  ADD CONSTRAINT `fk_channel_project` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_channel_parent` FOREIGN KEY (`parent_id`) REFERENCES `ai_website_channels` (`id`) ON DELETE CASCADE;

ALTER TABLE `ai_website_articles`
  ADD CONSTRAINT `fk_article_project` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_article_channel` FOREIGN KEY (`channel_id`) REFERENCES `ai_website_channels` (`id`) ON DELETE CASCADE;

ALTER TABLE `ai_website_products`
  ADD CONSTRAINT `fk_product_project` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_product_channel` FOREIGN KEY (`channel_id`) REFERENCES `ai_website_channels` (`id`) ON DELETE CASCADE;

ALTER TABLE `ai_website_generation_tasks`
  ADD CONSTRAINT `fk_website_gen_task_project` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_website_task_parent` FOREIGN KEY (`parent_task_id`) REFERENCES `ai_website_generation_tasks` (`id`) ON DELETE CASCADE;

ALTER TABLE `ai_generated_website_pages`
  ADD CONSTRAINT `fk_gen_page_project_adv` FOREIGN KEY (`project_id`) REFERENCES `ai_website_projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_gen_page_task_adv` FOREIGN KEY (`task_id`) REFERENCES `ai_website_generation_tasks` (`id`) ON DELETE CASCADE;

-- =========================================================
-- 22) 升级现有表以支持新功能
-- =========================================================
ALTER TABLE `ai_company_usage_stats` 
ADD COLUMN `website_count` INT DEFAULT 0 COMMENT '当日生成网站数量' AFTER `article_count`;

-- 为发布记录和计划任务的多态关联增加新的类型
-- 注意: 如果您已插入数据, 可能需要手动更新ENUM定义
-- ALTER TABLE `ai_scheduled_publishing_tasks` MODIFY `publishable_type` VARCHAR(50) ...
-- ALTER TABLE `ai_publishing_records` MODIFY `publishable_type` VARCHAR(50) ...
-- 这里仅作演示, 假设尚未有冲突数据

-- =========================================================
-- END: LANDING PAGE BUILDER EXTENSION
-- =========================================================

-- =========================================================
-- 8) 外键约束（在表全部创建后添加，避免循环依赖）
-- =========================================================

-- 公司/用户/审计
ALTER TABLE `ai_users`

ALTER TABLE `ai_audit_logs`

-- 平台相关
ALTER TABLE `ai_publishing_platforms`
  ADD CONSTRAINT `fk_platforms_type` FOREIGN KEY (`type_id`) REFERENCES `ai_publishing_platform_types` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `ai_company_platform_configs`
  ADD CONSTRAINT `fk_company_configs_company` FOREIGN KEY (`company_id`) REFERENCES `ai_companies` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_company_configs_platform` FOREIGN KEY (`platform_id`) REFERENCES `ai_publishing_platforms` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_scheduled_publishing_tasks`
  -- ADD CONSTRAINT `fk_sched_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, -- 改为多态关联，此约束移除
  ADD CONSTRAINT `fk_sched_platform_cfg` FOREIGN KEY (`platform_config_id`) REFERENCES `ai_company_platform_configs` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_publishing_records`
  -- ADD CONSTRAINT `fk_publish_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, -- 改为多态关联，此约束移除
  ADD CONSTRAINT `fk_publish_platform_cfg` FOREIGN KEY (`platform_config_id`) REFERENCES `ai_company_platform_configs` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_publish_scheduled_task` FOREIGN KEY (`scheduled_task_id`) REFERENCES `ai_scheduled_publishing_tasks` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

-- AI / Prompt / Uploaded files
ALTER TABLE `ai_dify_api_configs`

ALTER TABLE `ai_prompt_templates`

ALTER TABLE `ai_uploaded_files`

-- 关键词 / 扩展 / 话题抓取
ALTER TABLE `ai_keywords`

ALTER TABLE `ai_keyword_expansions`
  ADD CONSTRAINT `fk_keyword_expansion_keyword` FOREIGN KEY (`keyword_id`) REFERENCES `ai_keywords` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_topic_sources`
  -- no external FK;

ALTER TABLE `ai_topic_fetch_tasks`
  ADD CONSTRAINT `fk_topic_fetch_source` FOREIGN KEY (`topic_source_id`) REFERENCES `ai_topic_sources` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `ai_topics`
  ADD CONSTRAINT `fk_topics_source_task` FOREIGN KEY (`source_task_id`) REFERENCES `ai_topic_fetch_tasks` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

ALTER TABLE `ai_topic_keywords`
  ADD CONSTRAINT `fk_topic_keywords_topic` FOREIGN KEY (`topic_id`) REFERENCES `ai_topics` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_topic_keywords_keyword` FOREIGN KEY (`keyword_id`) REFERENCES `ai_keywords` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

-- 生成配置 / 任务 / references / task-关联
ALTER TABLE `ai_article_generation_configs`
  ADD CONSTRAINT `fk_config_article_type` FOREIGN KEY (`article_type_id`) REFERENCES `ai_article_types` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_config_platform` FOREIGN KEY (`platform_id`) REFERENCES `ai_publishing_platforms` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

ALTER TABLE `ai_article_generation_tasks`
  ADD CONSTRAINT `fk_task_config` FOREIGN KEY (`config_id`) REFERENCES `ai_article_generation_configs` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_task_dify_config` FOREIGN KEY (`dify_api_config_id`) REFERENCES `ai_dify_api_configs` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_task_prompt_tpl` FOREIGN KEY (`prompt_template_id`) REFERENCES `ai_prompt_templates` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

ALTER TABLE `ai_task_references`
  ADD CONSTRAINT `fk_task_references_task` FOREIGN KEY (`task_id`) REFERENCES `ai_article_generation_tasks` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_task_keywords`
  ADD CONSTRAINT `fk_task_keywords_task` FOREIGN KEY (`task_id`) REFERENCES `ai_article_generation_tasks` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_task_keywords_keyword` FOREIGN KEY (`keyword_id`) REFERENCES `ai_keywords` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_task_topics`
  ADD CONSTRAINT `fk_task_topics_task` FOREIGN KEY (`task_id`) REFERENCES `ai_article_generation_tasks` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_task_topics_topic` FOREIGN KEY (`topic_id`) REFERENCES `ai_topics` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_task_knowledge_sources`
  ADD CONSTRAINT `fk_task_knowledge_task` FOREIGN KEY (`task_id`) REFERENCES `ai_article_generation_tasks` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_task_knowledge_upload` FOREIGN KEY (`knowledge_id`) REFERENCES `ai_uploaded_files` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

-- 文章相关外键
ALTER TABLE `ai_generated_articles`
  ADD CONSTRAINT `fk_articles_task` FOREIGN KEY (`task_id`) REFERENCES `ai_article_generation_tasks` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,

ALTER TABLE `ai_article_keywords`
  ADD CONSTRAINT `fk_article_keywords_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_article_keywords_keyword` FOREIGN KEY (`keyword_id`) REFERENCES `ai_keywords` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_topics`
  ADD CONSTRAINT `fk_article_topics_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_article_topics_topic` FOREIGN KEY (`topic_id`) REFERENCES `ai_topics` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_faqs`
  ADD CONSTRAINT `fk_faq_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_structured_data`
  ADD CONSTRAINT `fk_structured_data_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_media`
  ADD CONSTRAINT `fk_media_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_sameas`
  ADD CONSTRAINT `fk_sameas_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_article_translations`
  ADD CONSTRAINT `fk_translation_article` FOREIGN KEY (`article_id`) REFERENCES `ai_generated_articles` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

-- 统计/日志外键
ALTER TABLE `ai_user_activity_logs`

ALTER TABLE `ai_company_usage_stats`

-- 落地页扩展外键
ALTER TABLE `ai_landing_page_projects`

ALTER TABLE `ai_landing_page_step_configs`
  ADD CONSTRAINT `fk_lp_config_project` FOREIGN KEY (`project_id`) REFERENCES `ai_landing_page_projects` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_lp_config_template` FOREIGN KEY (`template_id`) REFERENCES `ai_landing_page_templates` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_lp_config_logo_file` FOREIGN KEY (`logo_file_id`) REFERENCES `ai_uploaded_files` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

ALTER TABLE `ai_landing_page_assets`
  ADD CONSTRAINT `fk_lp_assets_project` FOREIGN KEY (`project_id`) REFERENCES `ai_landing_page_projects` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_lp_assets_file` FOREIGN KEY (`uploaded_file_id`) REFERENCES `ai_uploaded_files` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_generated_landing_pages`
  ADD CONSTRAINT `fk_gen_lp_project` FOREIGN KEY (`project_id`) REFERENCES `ai_landing_page_projects` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `ai_landing_page_generation_tasks`
  ADD CONSTRAINT `fk_lp_task_project` FOREIGN KEY (`project_id`) REFERENCES `ai_landing_page_projects` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_lp_task_dify_config` FOREIGN KEY (`dify_api_config_id`) REFERENCES `ai_dify_api_configs` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_lp_task_prompt_tpl` FOREIGN KEY (`prompt_template_id`) REFERENCES `ai_prompt_templates` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;

-- =========================================================
-- 9) 索引/唯一约束补充（帮助查询与幂等）
-- =========================================================
ALTER TABLE `ai_publishing_records`
  ADD UNIQUE KEY `uk_publish_sched` (`scheduled_task_id`);

ALTER TABLE `ai_keywords`
  ADD INDEX `idx_keywords_company_keyword` (`company_id`,`keyword`);

-- =========================================================
-- 10) 示例初始化数据（便于导入后直接测试）
-- =========================================================

-- 公司
INSERT INTO `ai_companies` (`id`,`name`,`subdomain`,`plan_type`,`max_users`,`max_articles_per_month`,`status`,`default_settings`)
VALUES
(1,'宝利云科技','baolinyun','premium',50,1000,'active', '{"default_language": "zh", "remove_ai_taste": 1}'),
(2,'测试公司','testco','trial',5,100,'trial', NULL);

-- 用户
INSERT INTO `ai_users` (`id`,`company_id`,`username`,`email`,`password_hash`,`full_name`,`role`,`is_active`)
VALUES
(1,1,'admin','admin@baolinyun.com','hash_admin','管理员','admin',1),
(2,1,'editor','editor@baolinyun.com','hash_editor','小编','editor',1),
(3,2,'tester','tester@testco.com','hash_test','测试员','manager',1);

-- 审计日志（示例）
INSERT INTO `ai_audit_logs` (`company_id`,`user_id`,`action`,`target_table`,`target_id`,`details`)
VALUES
(1,1,'init','ai_companies',1, '{"note": "初始化公司"}');

-- 平台类型
INSERT INTO `ai_publishing_platform_types` (`id`,`name`,`description`)
VALUES
(1,'Blog','博客类平台'),
(2,'Social','社交媒体平台'),
(3,'Video','视频平台'),
(4,'LandingPage','落地页托管平台');

-- 平台（含小红书/百家号/LinkedIn）
INSERT INTO `ai_publishing_platforms` (`id`,`type_id`,`name`,`code`,`description`,`auth_type`)
VALUES
(1,1,'WordPress','wordpress','WordPress 博客平台','basic'),
(2,2,'小红书','xiaohongshu','小红书平台','oauth'),
(3,2,'百家号','baijiahao','百度百家号','oauth'),
(4,2,'LinkedIn','linkedin','LinkedIn 公司主页','oauth'),
(5,3,'YouTube','youtube','YouTube 频道','oauth'),
(6,4,'SystemHost','system_host','系统内置托管','custom');

-- 企业平台配置（宝利云的示例配置）
INSERT INTO `ai_company_platform_configs` (`company_id`,`platform_id`,`config_data`,`account_name`)
VALUES
(1,2, '{"app_id": "xh123", "token": "tok_xh_abc"}', '宝利云小红书'),
(1,3, '{"app_id": "bj123", "token": "tok_bj_abc"}', '宝利云百家号'),
(1,4, '{"client_id": "lk123", "client_secret": "sec_lk_xxx"}', '宝利云LinkedIn'),
(1,1, '{"url": "https://blog.baolinyun.com", "username": "wp_admin"}', '宝利云博客');

-- 文章类型
INSERT INTO `ai_article_types` (`id`,`name`,`description`)
VALUES
(1,'产品介绍','产品功能与卖点介绍'),
(2,'行业新闻','行业动态/新闻类文章'),
(3,'使用教程','操作指南与教程');

-- AI 配置（示例：共享 OpenAI）
INSERT INTO `ai_dify_api_configs` (`id`,`company_id`,`provider`,`name`,`base_url`,`api_key`,`model_name`,`temperature`)
VALUES
(1,NULL,'openai','默认OpenAI','https://api.openai.com','sk-demo','gpt-4.1',0.7),
(2,1,'dify','企业Dify','https://dify.example.com','dify-key-123','dify-model-1',0.7);

-- Prompt 模板（示例）
INSERT INTO `ai_prompt_templates` (`id`,`company_id`,`name`,`description`,`language`,`content`)
VALUES
(1,NULL,'基础写作模板','通用写作模板','zh','请根据主题{{article_theme}}，以{{writing_language}}写一篇{{article_type}}，长度在{{article_length_min}}到{{article_length_max}}字符，风格：口语化、自然。'),
(2,1,'宝利云产品模板','宝利云产品介绍专用模板','zh','请写一篇面向企业客户的产品介绍，包含主要功能、使用场景、客户收益。'),
(3,NULL, '通用落地页生成模板', '用于生成落地页的系统模板', 'zh', '请根据以下信息生成一个完整的HTML落地页: {{all_config_data_json}}');

-- 上传文件（知识库示例）
INSERT INTO `ai_uploaded_files` (`company_id`,`user_id`,`file_name`,`file_path`,`file_type`,`file_size`,`mime_type`)
VALUES
(1,1,'baolinyun_guide.pdf','/files/baolinyun_guide.pdf','knowledge',20480,'application/pdf');

-- 关键词与扩展示例
INSERT INTO `ai_keywords` (`company_id`,`keyword`,`search_volume`,`source`,`status`)
VALUES
(1,'深圳网络公司',500,'baidu','planned'),
(1,'深圳做网站',300,'baidu','planned'),
(1,'网站建设公司',800,'google','planned'),
(2,'AI写作工具',200,'manual','pending');

-- 话题来源配置（示例）
INSERT INTO `ai_topic_sources` (`name`,`engine`,`query_pattern`,`result_field`,`max_results`)
VALUES
('百度前10','baidu','q={{keyword}}','organic_results',10),
('谷歌前10','google','q={{keyword}}','organic_results',10),
('谷歌 also ask','google','q={{keyword}}','related_questions',10),
('知乎前10','baidu','q=site:zhihu.com {{keyword}}','organic_results',10);

-- 话题抓取任务（示例）
INSERT INTO `ai_topic_fetch_tasks` (`company_id`,`user_id`,`topic_source_id`,`keywords`,`status`,`raw_response`)
VALUES
(1,2,1,'深圳网络公司,网站建设','completed', '[{"title": "示例结果"}]');

-- 话题（示例）
INSERT INTO `ai_topics` (`company_id`,`source_task_id`,`title`,`description`,`source_url`,`status`)
VALUES
(1,1,'深圳网络公司发展趋势','热点话题：深圳网络公司行业发展','https://example.com/topic1','raw');

-- 话题-关键词关联（示例）
INSERT INTO `ai_topic_keywords` (`topic_id`,`keyword_id`)
VALUES
(1,1);

-- 生成配置（示例）
INSERT INTO `ai_article_generation_configs` (`company_id`,`name`,`platform_id`,`article_type_id`,`writing_language`,`article_length_min`,`article_length_max`,`remove_ai_tone`,`multi_version_count`)
VALUES
(1,'宝利云默认配置',1,1,'zh',800,1500,1,3);

-- 文章生成任务（示例）
INSERT INTO `ai_article_generation_tasks` (`company_id`,`user_id`,`config_id`,`article_theme`,`topic_ids`,`reference_urls`,`status`,`dify_api_config_id`,`prompt_template_id`)
VALUES
(1,2,1,'深圳网络公司发展趋势','1','https://zhihu.com/question/123456','pending',1,1);

-- 任务参考链接
INSERT INTO `ai_task_references` (`task_id`,`url`,`title`,`source_engine`,`is_high_reference`)
VALUES
(1,'https://zhihu.com/question/123456','知乎讨论：深圳网络公司','zhihu',1);

-- 任务关键词（示例）
INSERT INTO `ai_task_keywords` (`task_id`,`keyword_id`,`is_primary`)
VALUES
(1,1,1),(1,3,0);

-- 任务话题（示例）
INSERT INTO `ai_task_topics` (`task_id`,`topic_id`)
VALUES
(1,1);

-- 任务知识来源（示例：使用上传的公司知识库）
INSERT INTO `ai_task_knowledge_sources` (`task_id`,`knowledge_type`,`knowledge_id`,`content`)
VALUES
(1,'uploaded',1,NULL);

-- 生成文章（示例草稿，版本1）
INSERT INTO `ai_generated_articles` (`id`, `task_id`,`company_id`,`version`,`title`,`content`,`html_content`,`word_count`,`status`)
VALUES
(1, 1,1,1,'深圳网络公司发展趋势分析','这是 AI 生成的示例文章正文...','<p>这是 AI 生成的示例文章正文...</p>',350,'draft');

-- 文章关键词映射（示例）
INSERT INTO `ai_article_keywords` (`article_id`,`keyword_id`,`is_primary`)
VALUES
(1,1,1),(1,3,0);

-- 文章话题映射（示例）
INSERT INTO `ai_article_topics` (`article_id`,`topic_id`)
VALUES
(1,1);

-- 文章 FAQ（示例）
INSERT INTO `ai_article_faqs` (`article_id`,`question`,`answer`)
VALUES
(1,'深圳网络公司主要提供哪些服务？','网站开发、SEO、网络推广、运维等。');

-- 文章结构化数据（示例）
INSERT INTO `ai_article_structured_data` (`article_id`,`json_ld`)
VALUES
(1, '{"@context": "https://schema.org", "@type": "Article", "headline": "深圳网络公司发展趋势分析"}');

-- 文章媒体（示例）
INSERT INTO `ai_article_media` (`article_id`,`media_type`,`url`,`prompt`,`source_type`,`alt_text`)
VALUES
(1,'image','https://picsum.photos/800/400','生成一张现代办公场景的照片','ai_generated','现代办公场景');

-- sameAs 示例
INSERT INTO `ai_article_sameas` (`article_id`,`label`,`url`,`is_auto`)
VALUES
(1,'公司官网','https://www.baolinyun.com',1);

-- 文章翻译（示例）
INSERT INTO `ai_article_translations` (`article_id`,`language`,`title`,`content`)
VALUES
(1,'en','Shenzhen Web Company Trend Analysis','This is a sample translation...');

-- 插入发布记录（示例：手动发布）
INSERT INTO `ai_publishing_records` (`company_id`,`publishable_type`,`publishable_id`,`platform_config_id`,`status`,`publish_url`,`publish_time`)
VALUES
(1,'article',1,(SELECT id FROM ai_company_platform_configs WHERE account_name='宝利云LinkedIn'),'published','https://www.linkedin.com/posts/xxx',NOW());

-- 定时发布任务（示例：明天 10:00 发布到 LinkedIn）
INSERT INTO `ai_scheduled_publishing_tasks` (`company_id`,`publishable_type`,`publishable_id`,`platform_config_id`,`schedule_time`,`status`)
VALUES
(1,'article',1,(SELECT id FROM ai_company_platform_configs WHERE account_name='宝利云LinkedIn'), DATE_ADD(NOW(), INTERVAL 1 DAY), 'scheduled');

-- 用户活动日志示例
INSERT INTO `ai_user_activity_logs` (`company_id`,`user_id`,`activity_type`,`activity_details`,`ip_address`)
VALUES
(1,2,'create_task', '{"task_id": 1, "note": "创建文章生成任务"}', '203.0.113.5');

-- 公司使用统计（示例）
INSERT INTO `ai_company_usage_stats` (`company_id`,`stat_date`,`article_count`,`ai_token_usage`)
VALUES
(1, CURDATE(), 1, 12345);

-- =========================================================
-- 11) 最后：确保外键完整性检查恢复
-- =========================================================
SET FOREIGN_KEY_CHECKS = 1;


SET FOREIGN_KEY_CHECKS = 1;
-- End of schema


-- ==========================================
-- 补充索引 (company_id, user_id)
-- ==========================================

ALTER TABLE ai_users ADD INDEX idx_users_company (company_id);

ALTER TABLE ai_audit_logs ADD INDEX idx_audit_company (company_id);
ALTER TABLE ai_audit_logs ADD INDEX idx_audit_user (user_id);

ALTER TABLE ai_scheduled_publishing_tasks ADD INDEX idx_sched_company (company_id);
ALTER TABLE ai_publishing_records ADD INDEX idx_publish_company (company_id);

ALTER TABLE ai_dify_api_configs ADD INDEX idx_dify_company (company_id);
ALTER TABLE ai_prompt_templates ADD INDEX idx_prompt_company (company_id);
ALTER TABLE ai_uploaded_files ADD INDEX idx_files_company (company_id);
ALTER TABLE ai_uploaded_files ADD INDEX idx_files_user (user_id);

ALTER TABLE ai_keywords ADD INDEX idx_keywords_company (company_id);
ALTER TABLE ai_topic_fetch_tasks ADD INDEX idx_topic_fetch_company (company_id);
ALTER TABLE ai_topic_fetch_tasks ADD INDEX idx_topic_fetch_user (user_id);
ALTER TABLE ai_topics ADD INDEX idx_topics_company (company_id);

ALTER TABLE ai_article_generation_configs ADD INDEX idx_config_company (company_id);
ALTER TABLE ai_article_generation_tasks ADD INDEX idx_task_company (company_id);
ALTER TABLE ai_article_generation_tasks ADD INDEX idx_task_user (user_id);
ALTER TABLE ai_generated_articles ADD INDEX idx_articles_company (company_id);

ALTER TABLE ai_user_activity_logs ADD INDEX idx_activity_company (company_id);
ALTER TABLE ai_user_activity_logs ADD INDEX idx_activity_user (user_id);
ALTER TABLE ai_company_usage_stats ADD INDEX idx_usage_company (company_id);

ALTER TABLE ai_landing_page_projects ADD INDEX idx_lp_projects_company (company_id);
ALTER TABLE ai_landing_page_projects ADD INDEX idx_lp_projects_user (user_id);

ALTER TABLE ai_landing_page_generation_tasks ADD INDEX idx_lp_task_user (user_id);

ALTER TABLE ai_website_projects ADD INDEX idx_website_project_company (company_id);
ALTER TABLE ai_website_projects ADD INDEX idx_website_project_user (user_id);

ALTER TABLE ai_website_generation_tasks ADD INDEX idx_website_task_user (user_id);