数据库设计.md 11 KB
CREATE TABLE hairstylist_user (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '发型师主键ID',
username VARCHAR(50) NOT NULL COMMENT '发型师用户名',
email VARCHAR(100) NOT NULL COMMENT '登录邮箱',
password VARCHAR(100) NOT NULL COMMENT '加密后的登录密码',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0未删除，1已删除',
PRIMARY KEY (id),
UNIQUE KEY uk_username (username,is_deleted) COMMENT '用户名唯一（排除已删除）',
UNIQUE KEY uk_email (email,is_deleted) COMMENT '邮箱唯一（排除已删除）',
KEY idx_is_deleted (is_deleted) COMMENT '删除状态索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='发型师用户信息表';
CREATE TABLE customer (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '顾客主键ID',
hairstylist_id BIGINT UNSIGNED NOT NULL COMMENT '所属发型师ID',
name VARCHAR(30) NOT NULL COMMENT '顾客姓名（必填）',
name_pronunciation VARCHAR(50) DEFAULT '' COMMENT '姓名读音',
phone VARCHAR(20) NOT NULL COMMENT '联系电话（必填）',
email VARCHAR(100) DEFAULT '' COMMENT '邮箱',
birthday DATE NULL COMMENT '生日',
avatar VARCHAR(500) DEFAULT '' COMMENT '顾客头像图片URL地址',
expect_contact ENUM('LINE','PHONE','EMAIL','NONE') NOT NULL DEFAULT 'NONE' COMMENT '期望联系方式：LINE/电话/邮箱/未设置',
hair_length ENUM('SHORT','MEDIUM','LONG','UNCONFIRM') NOT NULL DEFAULT 'UNCONFIRM' COMMENT '当前头发长度：短发/中长发/长发/未确认',
hair_texture ENUM('UNCONFIRM','THIN','NORMAL','HARD_CRUDE','CURLY') NOT NULL DEFAULT 'UNCONFIRM' COMMENT '发质：未确认/细/普通/粗硬/有自然卷',
notice TEXT NULL COMMENT '发型护理注意事项',
allergy VARCHAR(500) DEFAULT '' COMMENT '过敏史/过敏产品',
inner_remark TEXT NULL COMMENT '门店内部备注（仅内部查看）',
next_follow_date DATE NULL COMMENT '下次跟进日期',
recommend_visit_date DATE NULL COMMENT '推荐到店日',
first_suggest_hair ENUM('AUTO','LONG','MEDIUM','SHORT') NOT NULL DEFAULT 'AUTO' COMMENT '首次建议发型模板：自动推荐/长发/中长发/短发',
plan_date DATE NULL COMMENT '到店计划日',
is_first_register TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否首次登记：0否，1是',
visit_purpose ENUM('CUT_DYE','CUT','DYE','CONSULT_SIM') NULL COMMENT '到店目的：剪发+染发/剪发/染发/模拟咨询',
trouble TEXT NULL COMMENT '发质/发型烦恼',
consult_content TEXT NULL COMMENT '详细咨询内容',
expect_look TEXT NULL COMMENT '期望发型形象描述',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 0正常 1删除',
PRIMARY KEY (id),
UNIQUE KEY uk_phone_del (phone,is_deleted) COMMENT '手机号唯一，删除后可重录',
KEY idx_hairstylist_is_deleted (hairstylist_id,is_deleted) COMMENT '按发型师隔离顾客数据',
KEY idx_is_deleted (is_deleted),
KEY idx_next_follow (next_follow_date,is_deleted) COMMENT '跟进日期筛选索引',
KEY idx_recommend_visit (recommend_visit_date,is_deleted) COMMENT '推荐到店日期索引',
KEY idx_plan_date (plan_date,is_deleted) COMMENT '到店计划日筛选索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='顾客信息+咨询登记表（合并单表）';
CREATE TABLE customer_treatment_record (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '施术记录主键ID',
customer_id BIGINT UNSIGNED NOT NULL COMMENT '关联顾客ID，customer表主键',
hairstylist_id BIGINT UNSIGNED NOT NULL COMMENT '操作发型师ID，hairstylist_user表主键',
treatment_time DATETIME NOT NULL COMMENT '施术时间',
treatment_title VARCHAR(200) NOT NULL DEFAULT '' COMMENT '施术标题（如：剪发+漂染护理）',
treatment_detail TEXT NULL COMMENT '施术详细内容、使用产品、操作备注',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1删除',
PRIMARY KEY (id),
KEY idx_customer_id (customer_id,is_deleted) COMMENT '快速查询单个顾客全部施术历史',
KEY idx_hairstylist_id (hairstylist_id,is_deleted) COMMENT '查询发型师负责的所有施术记录',
KEY idx_treatment_time (treatment_time,is_deleted) COMMENT '按施术时间筛选统计'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='顾客施术历史记录表';
CREATE TABLE ai_hair_simulation (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '模拟记录主键ID',
customer_id BIGINT UNSIGNED NULL COMMENT '关联顾客ID，可为NULL（匿名无顾客）',
hairstylist_id BIGINT UNSIGNED NOT NULL COMMENT '操作发型师ID，关联发型师用户表',
origin_image VARCHAR(500) NOT NULL DEFAULT '' COMMENT '原始照片URL地址',
simulate_remark TEXT NULL COMMENT '本次发型模拟备注说明',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1删除',
PRIMARY KEY (id),
KEY idx_customer_id (customer_id,is_deleted) COMMENT '查询指定顾客所有模拟记录',
KEY idx_hairstylist_id (hairstylist_id,is_deleted) COMMENT '查询发型师制作的模拟方案'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI发型模拟记录表';
CREATE TABLE ai_hair_simulation_task (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '任务主键ID',
simulation_id BIGINT UNSIGNED NOT NULL COMMENT '关联AI发型模拟表id',
template_id VARCHAR(100) NOT NULL DEFAULT '' COMMENT '发型模板ID',
customer_id BIGINT UNSIGNED NULL COMMENT '关联顾客ID，可为NULL（匿名模拟）',
task_id VARCHAR(200) NOT NULL COMMENT 'AI服务侧唯一任务ID',
task_status ENUM('PROCESSING','SUCCESSES','FAILED') NOT NULL DEFAULT 'PROCESSING' COMMENT '任务执行状态：处理中/成功/失败',
result_images TEXT NULL COMMENT '任务结果图片URL数组，JSON格式存储',
task_start_time DATETIME NOT NULL COMMENT '任务发起开始时间',
task_finish_time DATETIME NULL COMMENT '任务执行完成时间，未结束为NULL',
error_msg TEXT NULL COMMENT '任务失败错误信息，成功则为空',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1删除',
PRIMARY KEY (id),
UNIQUE KEY uk_task_id (task_id) COMMENT 'AI任务唯一防重复',
KEY idx_simulation_id (simulation_id,is_deleted) COMMENT '根据模拟记录查询所有子任务',
KEY idx_customer_id (customer_id,is_deleted) COMMENT '顾客维度筛选模拟任务',
KEY idx_task_status (task_status,is_deleted) COMMENT '筛选待处理/失败任务重试'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI发型模拟异步任务表';
CREATE TABLE hair_template (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '模板主键ID',
template_name VARCHAR(100) NOT NULL COMMENT '模板名称（必填）',
cover_url VARCHAR(500) DEFAULT '' COMMENT '模板封面图URL',
status ENUM('DRAFT','PUBLIC') NOT NULL DEFAULT 'DRAFT' COMMENT '模板状态：DRAFT草稿 / PUBLIC公开',
category ENUM('LONG','MEDIUM','SHORT','DYE') NOT NULL DEFAULT 'LONG' COMMENT '分类：长发/中长发/短发/染发',
tags VARCHAR(500) DEFAULT '' COMMENT '模板标签，逗号分隔存储',
description TEXT NULL COMMENT '模板详细说明',
model_url VARCHAR(500) DEFAULT '' COMMENT 'AI模特图片URL',
clothes_url VARCHAR(500) DEFAULT '' COMMENT '服装素材URL',
background_url VARCHAR(500) DEFAULT '' COMMENT '背景图片URL',
ref_hair_url VARCHAR(500) DEFAULT '' COMMENT '参考发型图片URL',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1删除',
PRIMARY KEY (id),
KEY idx_status (status,is_deleted) COMMENT '按模板状态筛选',
KEY idx_category (category,is_deleted) COMMENT '按发型分类筛选模板'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI发型模板表';
CREATE TABLE hair_template_make_task (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '本地任务自增主键',
total_task_id VARCHAR(200) NOT NULL COMMENT '总任务唯一ID',
step1_task_id VARCHAR(200) DEFAULT '' COMMENT '第一步AI任务ID',
step2_task_id VARCHAR(200) DEFAULT '' COMMENT '第二步AI任务ID',
task_status ENUM('WAIT','PROCESSING','STEP1_SUCCESS','STEP2_SUCCESS','FAILED') NOT NULL DEFAULT 'WAIT' COMMENT '任务状态：等待处理/处理中/第一步完成/全部完成/失败',
template_id BIGINT UNSIGNED NULL COMMENT '关联发型模板ID，模板生成完成后回填',
step1_resource TEXT NULL COMMENT 'step1任务资源包，JSON字符串存储参数/素材信息',
step2_resource TEXT NULL COMMENT 'step2任务资源包，JSON字符串存储参数/素材信息',
ref_hair_url VARCHAR(500) DEFAULT '' COMMENT '参考发型图片URL',
model_url VARCHAR(500) DEFAULT '' COMMENT 'AI模特图片URL',
clothes_url VARCHAR(500) DEFAULT '' COMMENT '服装素材URL',
background_url VARCHAR(500) DEFAULT '' COMMENT '背景图片URL',
step1_result_img VARCHAR(500) DEFAULT '' COMMENT 'step1输出结果图',
step2_result_img VARCHAR(500) DEFAULT '' COMMENT 'step2最终合成结果图',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常，1删除',
PRIMARY KEY (id),
UNIQUE KEY uk_total_task_id (total_task_id) COMMENT '总任务ID唯一防重复提交',
KEY idx_task_status (task_status,is_deleted) COMMENT '筛选待执行、失败任务',
KEY idx_template_id (template_id,is_deleted) COMMENT '根据模板查询制作任务记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='发型模板制作异步任务表';