-- 用户项目重粉中间表：上线后看课/绑定时写入，列表按项目筛重粉走索引查询，不扫 fs_user_company_bind 流水

CREATE TABLE IF NOT EXISTS `fs_user_project_qw` (
    `id`              BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `fs_user_id`      BIGINT       NOT NULL COMMENT '小程序用户ID',
    `project_id`      BIGINT       NOT NULL COMMENT '课程项目ID（原始）',
    `qw_user_id`      BIGINT       NOT NULL COMMENT '企微销售ID',
    `company_id`      BIGINT       DEFAULT NULL COMMENT '公司ID',
    `first_bind_time` DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '首次绑定时间',
    `update_time`     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uk_user_project_qw` (`fs_user_id`, `project_id`, `qw_user_id`),
    KEY `idx_user_project` (`fs_user_id`, `project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='用户-项目-企微销售去重表（重粉判重维度）';

CREATE TABLE IF NOT EXISTS `fs_user_project_repeat` (
    `id`             BIGINT   NOT NULL AUTO_INCREMENT COMMENT '主键',
    `fs_user_id`     BIGINT   NOT NULL COMMENT '小程序用户ID',
    `project_id`     BIGINT   NOT NULL COMMENT '等价项目组 canonical 项目ID',
    `company_id`     BIGINT   DEFAULT NULL COMMENT '最近写入时的公司ID',
    `qw_user_count`  INT      NOT NULL DEFAULT 1 COMMENT '等价项目组内不同企微销售数',
    `is_repeat`      TINYINT  NOT NULL DEFAULT 0 COMMENT '是否重粉 0否 1是',
    `create_time`    DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uk_user_project` (`fs_user_id`, `project_id`),
    KEY `idx_project_repeat` (`project_id`, `is_repeat`, `fs_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='用户-项目重粉标记表（列表筛选）';
