acquire-lead/sql/schema.sql

-- AcquireLead 数据库表结构（与当前库表字段对齐）
-- 表名前缀：douyin_
-- 整体拆分：douyin_videos -> douyin_comments -> douyin_leads -> douyin_conversations -> douyin_messages

CREATE TABLE IF NOT EXISTS `douyin_videos` (
    `id`               BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `video_id`         VARCHAR(255) NOT NULL COMMENT '平台视频ID（如 modal_id / aweme_id）',
    `keyword`          VARCHAR(255) DEFAULT NULL COMMENT '采集时搜索关键词',
    `title`            VARCHAR(255) DEFAULT NULL COMMENT '视频标题/描述',
    `author_nickname`  VARCHAR(128) DEFAULT NULL COMMENT '作者昵称',
    `author_douyin_id` VARCHAR(255) DEFAULT NULL COMMENT '作者抖音号',
    `author_link`      VARCHAR(512) DEFAULT NULL COMMENT '作者主页链接',
    `video_url`        VARCHAR(512) DEFAULT NULL COMMENT '视频链接',
    `summary`          VARCHAR(512) DEFAULT NULL COMMENT 'AI抖音视频内容总结',
    `create_at`        DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_at`        DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_douyin_videos_video_id` (`video_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='来源视频';

CREATE TABLE IF NOT EXISTS `douyin_leads` (
    `id`           BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `douyin_id`    VARCHAR(255) NOT NULL COMMENT '抖音号（唯一身份）',
    `nick_name`    VARCHAR(255) DEFAULT NULL COMMENT '用户昵称',
    `user_link`    VARCHAR(512) DEFAULT NULL COMMENT '用户主页链接',
    `status`       TINYINT      DEFAULT 1 COMMENT '1已采集 -1低价值 2高价值待私信 3已私信待回复 -2私信失败 4已人工对接',
    `ai_score`     INT          DEFAULT NULL COMMENT 'AI意向分',
    `ai_reason`    VARCHAR(255) DEFAULT NULL COMMENT '评分原因',
    `score_create` DATETIME     DEFAULT NULL COMMENT '评分时间',
    `create_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_douyin_leads_douyin_id` (`douyin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='线索用户';

CREATE TABLE IF NOT EXISTS `douyin_comments` (
    `id`           BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键',
    `lead_id`      BIGINT        DEFAULT NULL COMMENT '关联 douyin_leads.id，入库后可回填',
    `douyin_id`    VARCHAR(255)  DEFAULT NULL COMMENT '评论者抖音号',
    `video_id`     BIGINT        NOT NULL COMMENT '关联 douyin_videos.id',
    `nick_name`    VARCHAR(255)  DEFAULT NULL COMMENT '评论时昵称',
    `content`      VARCHAR(1024) DEFAULT NULL COMMENT '评论内容',
    `comment_time` VARCHAR(64)   DEFAULT NULL COMMENT '页面显示时间（如 1天前·上海）',
    `type`         TINYINT       DEFAULT 1 COMMENT '1=一级评论，0=回复',
    `create_at`    DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '系统入库时间',
    PRIMARY KEY (`id`),
    KEY `idx_douyin_comments_video_id` (`video_id`),
    KEY `idx_douyin_comments_douyin_id` (`douyin_id`),
    KEY `idx_douyin_comments_lead_id` (`lead_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='视频评论';

CREATE TABLE IF NOT EXISTS `douyin_conversations` (
    `id`                BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `lead_id`           BIGINT       NOT NULL COMMENT '关联 douyin_leads.id',
    `douyin_id`         VARCHAR(255) NOT NULL COMMENT '对方抖音号（冗余，便于校验）',
    `nickname`          VARCHAR(128) DEFAULT NULL COMMENT '对方昵称',
    `last_message_time` DATETIME     DEFAULT NULL COMMENT '最近消息时间',
    `create_at`         DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_at`         DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_douyin_conversations_lead_id` (`lead_id`),
    KEY `idx_douyin_conversations_douyin_id` (`douyin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='私信会话';

CREATE TABLE IF NOT EXISTS `douyin_messages` (
    `id`              BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键',
    `conversation_id` BIGINT        NOT NULL COMMENT '关联 douyin_conversations.id',
    `lead_id`         BIGINT        DEFAULT NULL COMMENT '关联 douyin_leads.id',
    `douyin_id`       VARCHAR(255)  DEFAULT NULL COMMENT '对方抖音号',
    `direction`       VARCHAR(16)   DEFAULT NULL COMMENT 'in=对方 out=我方',
    `content`         VARCHAR(1024) DEFAULT NULL COMMENT '消息文本',
    `create_at`       DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '系统抓取入库时间',
    PRIMARY KEY (`id`),
    KEY `idx_douyin_messages_conversation_id` (`conversation_id`),
    KEY `idx_douyin_messages_lead_id` (`lead_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='私信消息';