ajy/openspec/aijiuyiBackend/data.sql

-- =============================================================================
-- 智能穴位定位系统 - 数据库重建脚本
-- 说明：DROP 所有旧表，全新创建 17 张表，插入种子数据。
-- ID 策略：应用层使用雪花算法生成，种子数据使用固定小 ID（不会冲突）。
-- =============================================================================

CREATE DATABASE IF NOT EXISTS `aijiuyi_admin`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE `aijiuyi_admin`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------------------------
-- DROP 所有旧表
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `scheme_target_user`;
DROP TABLE IF EXISTS `moxa_session_record`;
DROP TABLE IF EXISTS `moxa_process`;
DROP TABLE IF EXISTS `device_hotkey_mapping`;
DROP TABLE IF EXISTS `user_acupoint_item`;
DROP TABLE IF EXISTS `user_acupoint_table`;
DROP TABLE IF EXISTS `user_scheme`;
DROP TABLE IF EXISTS `scheme_step`;
DROP TABLE IF EXISTS `scheme`;
DROP TABLE IF EXISTS `moxa_technique`;
DROP TABLE IF EXISTS `acupoint`;
DROP TABLE IF EXISTS `app_user_device`;
DROP TABLE IF EXISTS `app_device`;
DROP TABLE IF EXISTS `app_user_fingerprint`;
DROP TABLE IF EXISTS `app_user`;
DROP TABLE IF EXISTS `sys_operation_log`;
DROP TABLE IF EXISTS `sys_admin_user`;

-- 旧表清理
DROP TABLE IF EXISTS `admin_user_role`;
DROP TABLE IF EXISTS `admin_user`;
DROP TABLE IF EXISTS `admin_role`;
DROP TABLE IF EXISTS `operation_log`;
DROP TABLE IF EXISTS `end_user`;
DROP TABLE IF EXISTS `user_fingerprint`;
DROP TABLE IF EXISTS `account`;
DROP TABLE IF EXISTS `moxa_session_record`;
DROP TABLE IF EXISTS `algorithm_version_record`;
DROP TABLE IF EXISTS `algorithm_parameter_set`;
DROP TABLE IF EXISTS `device_model`;
DROP TABLE IF EXISTS `technique_combo_rule`;
DROP TABLE IF EXISTS `technique`;
DROP TABLE IF EXISTS `efficacy_version`;
DROP TABLE IF EXISTS `efficacy`;
DROP TABLE IF EXISTS `body_region`;
DROP TABLE IF EXISTS `symptom`;

-- =============================================================================
-- 一、系统管理表
-- =============================================================================

CREATE TABLE `sys_admin_user` (
    `id`             BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `username`       VARCHAR(64)  NOT NULL COMMENT '登录用户名',
    `password_hash`  VARCHAR(255) NOT NULL COMMENT 'BCrypt 密码哈希',
    `display_name`   VARCHAR(128) NULL     COMMENT '姓名',
    `role`           VARCHAR(32)  NOT NULL DEFAULT 'OPERATOR' COMMENT '角色：SUPER_ADMIN / OPERATOR / READONLY',
    `status`         VARCHAR(32)  NOT NULL DEFAULT 'ACTIVE'   COMMENT '状态：ACTIVE / DISABLED',
    `last_login_at`  DATETIME     NULL     COMMENT '最后登录时间',
    `deleted`        TINYINT      NOT NULL DEFAULT 0,
    `create_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台管理员表';

CREATE TABLE `sys_operation_log` (
    `id`             BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `operator_id`    BIGINT       NULL     COMMENT '操作人 ID',
    `operator_name`  VARCHAR(64)  NULL     COMMENT '操作人姓名',
    `action_type`    VARCHAR(32)  NOT NULL COMMENT '操作类型：CREATE / UPDATE / DELETE / IMPORT / EXPORT',
    `module`         VARCHAR(64)  NOT NULL COMMENT '操作模块：USER / ACUPOINT / SCHEME / DEVICE / SYSTEM',
    `content`        VARCHAR(512) NULL     COMMENT '操作内容',
    `ip`             VARCHAR(64)  NULL     COMMENT '客户端 IP',
    `create_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_operator` (`operator_id`),
    KEY `idx_time` (`create_time`),
    KEY `idx_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作审计日志表';

-- =============================================================================
-- 二、用户管理表
-- =============================================================================

CREATE TABLE `app_user` (
    `id`                     BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `name`                   VARCHAR(128) NOT NULL COMMENT '用户姓名',
    `gender`                 VARCHAR(16)  NOT NULL COMMENT '性别：male / female',
    `age`                    INT          NOT NULL COMMENT '年龄',
    `phone`                  VARCHAR(32)  NOT NULL COMMENT '手机号（唯一）',
    `shoulder_width`         DECIMAL(6,2) NOT NULL COMMENT '肩宽（cm）',
    `body_length`            DECIMAL(6,2) NOT NULL COMMENT '身长/坐高（cm）',
    `finger_cun1`            DECIMAL(6,2) NULL     COMMENT '一寸（cm），选填',
    `finger_cun1_5`          DECIMAL(6,2) NULL     COMMENT '1.5寸（cm），选填',
    `finger_cun3`            DECIMAL(6,2) NULL     COMMENT '三寸（cm），选填',
    `acupoint_table_status`  VARCHAR(16)  NOT NULL DEFAULT 'NOT_GENERATED' COMMENT '穴位表状态：GENERATED / NOT_GENERATED',
    `deleted`                TINYINT      NOT NULL DEFAULT 0,
    `create_time`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='APP 用户表';

CREATE TABLE `app_user_fingerprint` (
    `id`              BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `user_id`         BIGINT       NOT NULL COMMENT '用户 ID',
    `fingerprint_id`  VARCHAR(128) NOT NULL COMMENT '设备侧指纹标识',
    `deleted`         TINYINT      NOT NULL DEFAULT 0,
    `create_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户指纹表';

-- =============================================================================
-- 三、设备管理表
-- =============================================================================

CREATE TABLE `app_device` (
    `id`                BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `device_code`       VARCHAR(64)  NOT NULL COMMENT '设备编号（如 AJY001）',
    `device_name`       VARCHAR(128) NOT NULL COMMENT '设备名称',
    `model`             VARCHAR(64)  NOT NULL COMMENT '设备型号',
    `serial_number`     VARCHAR(128) NOT NULL COMMENT '设备序列号（唯一标识）',
    `firmware_version`  VARCHAR(64)  NULL     COMMENT '固件版本',
    `status`            VARCHAR(16)  NOT NULL DEFAULT 'offline' COMMENT '在线状态：online / offline',
    `last_online_at`    DATETIME     NULL     COMMENT '最后在线时间',
    `remark`            VARCHAR(512) NULL     COMMENT '备注',
    `deleted`           TINYINT      NOT NULL DEFAULT 0,
    `create_time`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_device_code` (`device_code`),
    UNIQUE KEY `uk_serial_number` (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备实例表';

CREATE TABLE `app_user_device` (
    `id`          BIGINT   NOT NULL COMMENT '主键（雪花算法）',
    `user_id`     BIGINT   NOT NULL COMMENT '用户 ID',
    `device_id`   BIGINT   NOT NULL COMMENT '设备 ID',
    `is_primary`  TINYINT  NOT NULL DEFAULT 0 COMMENT '是否主设备：1 是 0 否',
    `bound_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
    `deleted`     TINYINT  NOT NULL DEFAULT 0,
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_device` (`user_id`, `device_id`),
    KEY `idx_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户-设备绑定关系表（多对多）';

-- =============================================================================
-- 四、穴位管理表
-- =============================================================================

CREATE TABLE `acupoint` (
    `id`              BIGINT        NOT NULL COMMENT '主键（雪花算法）',
    `code`            VARCHAR(64)   NOT NULL COMMENT '穴位编码（如 dazhui）',
    `name`            VARCHAR(128)  NOT NULL COMMENT '穴位名称（如 大椎穴）',
    `meridian`        VARCHAR(128)  NULL     COMMENT '所属经络',
    `efficacy`        VARCHAR(512)  NULL     COMMENT '功效（逗号分隔）',
    `positioning`     VARCHAR(16)   NOT NULL DEFAULT 'relative' COMMENT '定位方式：relative / absolute',
    `offset_cun`      DECIMAL(6,2)  NULL     COMMENT '相对大椎穴纵向偏移（寸数）',
    `cun_type`        VARCHAR(16)   NULL     COMMENT '指寸类型：cun1 / cun1_5 / cun3',
    `side`            VARCHAR(16)   NOT NULL DEFAULT 'center'  COMMENT '侧别：center / both',
    `lateral_offset`  DECIMAL(6,2)  NULL     COMMENT '横向偏移（寸数），双侧穴位使用',
    `absolute_x`      DECIMAL(12,4) NULL     COMMENT '绝对坐标 X（mm）',
    `absolute_y`      DECIMAL(12,4) NULL     COMMENT '绝对坐标 Y（mm）',
    `absolute_z`      DECIMAL(12,4) NULL     COMMENT '绝对坐标 Z（mm）',
    `target_gender`   VARCHAR(16)   NOT NULL DEFAULT 'all' COMMENT '适用性别：all / male / female',
    `status`          VARCHAR(16)   NOT NULL DEFAULT 'active'  COMMENT '状态：active / pending',
    `source`          VARCHAR(16)   NOT NULL DEFAULT 'preset'  COMMENT '数据来源：preset / expert',
    `deleted`         TINYINT       NOT NULL DEFAULT 0,
    `create_time`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_status` (`status`),
    KEY `idx_meridian` (`meridian`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='穴位基础库表';

CREATE TABLE `moxa_technique` (
    `id`                  BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `code`                VARCHAR(64)  NOT NULL COMMENT '手法编码（如 gentle）',
    `name`                VARCHAR(128) NOT NULL COMMENT '手法名称（如 温和灸）',
    `is_default`          TINYINT      NOT NULL DEFAULT 0 COMMENT '是否默认手法：1 是 0 否',
    `description`         VARCHAR(512) NULL     COMMENT '手法描述',
    `movement_pattern`    VARCHAR(32)  NOT NULL COMMENT '运动模式：fixed / vertical / circular / point / oneclick',
    `temp_min`            DECIMAL(4,1) NOT NULL DEFAULT 35.0 COMMENT '温度范围最小值（°C）',
    `temp_max`            DECIMAL(4,1) NOT NULL DEFAULT 50.0 COMMENT '温度范围最大值（°C）',
    `duration_min`        INT          NOT NULL DEFAULT 60   COMMENT '时间范围最小值（秒）',
    `duration_max`        INT          NOT NULL DEFAULT 1800 COMMENT '时间范围最大值（秒）',
    `movement_amplitude`  DECIMAL(6,2) NULL     COMMENT '运动幅度（mm）',
    `movement_speed`      DECIMAL(6,2) NULL     COMMENT '运动速度（mm/s）',
    `status`              VARCHAR(16)  NOT NULL DEFAULT 'active' COMMENT '状态：active / disabled',
    `remark`              VARCHAR(512) NULL     COMMENT '备注',
    `deleted`             TINYINT      NOT NULL DEFAULT 0,
    `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='艾灸手法表';

-- =============================================================================
-- 五、方案管理表
-- =============================================================================

CREATE TABLE `scheme` (
    `id`             BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `scheme_name`    VARCHAR(128) NOT NULL COMMENT '方案名称',
    `mode`           VARCHAR(32)  NOT NULL COMMENT '模式：ONE_CLICK / PROFESSIONAL / CUSTOM / EXPERT',
    `efficacy_type`  VARCHAR(32)  NULL     COMMENT '功效类型：驱寒/祛湿/祛风/化瘀/活血/化痰/养颜/扶阳',
    `creator_type`   VARCHAR(16)  NOT NULL COMMENT '创作人类型：admin / user / expert',
    `creator_id`     VARCHAR(64)  NULL     COMMENT '创作人 ID',
    `creator_name`   VARCHAR(128) NULL     COMMENT '创作人显示名称',
    `symptoms`       VARCHAR(512) NULL     COMMENT '适用症状（逗号分隔）',
    `target_gender`  VARCHAR(16)  NOT NULL DEFAULT 'all' COMMENT '适用性别：all / male / female',
    `age_min`        INT          NOT NULL DEFAULT 1   COMMENT '适用年龄下限',
    `age_max`        INT          NOT NULL DEFAULT 120 COMMENT '适用年龄上限',
    `description`    VARCHAR(512) NULL     COMMENT '方案描述',
    `is_default`     TINYINT      NOT NULL DEFAULT 0 COMMENT '是否默认方案',
    `expert_id`      VARCHAR(64)  NULL     COMMENT '专家 ID（延年圣手模式）',
    `expert_name`    VARCHAR(128) NULL     COMMENT '专家名称（延年圣手模式）',
    `publish_type`   VARCHAR(16)  NULL     COMMENT '发布类型：public / private（延年圣手模式）',
    `process_id`     BIGINT       NULL     COMMENT '关联工艺语言 ID',
    `status`         VARCHAR(16)  NOT NULL DEFAULT 'draft' COMMENT '状态：draft / published / archived',
    `deleted`        TINYINT      NOT NULL DEFAULT 0,
    `create_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_mode` (`mode`),
    KEY `idx_status` (`status`),
    KEY `idx_efficacy` (`efficacy_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='艾灸方案表（四种模式统一存储）';

CREATE TABLE `scheme_step` (
    `id`              BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `scheme_id`       BIGINT       NOT NULL COMMENT '方案 ID',
    `step_order`      INT          NOT NULL COMMENT '步骤序号',
    `acupoint_id`     BIGINT       NOT NULL COMMENT '穴位 ID',
    `acupoint_name`   VARCHAR(128) NOT NULL COMMENT '穴位名称（冗余）',
    `side`            VARCHAR(16)  NOT NULL DEFAULT 'center' COMMENT '侧别：center / both / left / right',
    `temperature`     DECIMAL(4,1) NOT NULL COMMENT '温度（°C）',
    `duration`        INT          NOT NULL COMMENT '时间（秒）',
    `technique_id`    BIGINT       NOT NULL COMMENT '手法 ID',
    `technique_name`  VARCHAR(128) NOT NULL COMMENT '手法名称（冗余）',
    `remark`          VARCHAR(256) NULL     COMMENT '备注',
    `deleted`         TINYINT      NOT NULL DEFAULT 0,
    `create_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_scheme` (`scheme_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='方案治疗步骤表';

CREATE TABLE `user_scheme` (
    `id`          BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `user_id`     BIGINT       NOT NULL COMMENT '用户 ID',
    `scheme_id`   BIGINT       NOT NULL COMMENT '方案 ID',
    `mode`        VARCHAR(32)  NOT NULL COMMENT '模式',
    `selected_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选择时间',
    `deleted`     TINYINT      NOT NULL DEFAULT 0,
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_scheme` (`scheme_id`),
    KEY `idx_user_mode` (`user_id`, `mode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户-方案关联表';

CREATE TABLE `scheme_target_user` (
    `id`          BIGINT   NOT NULL COMMENT '主键（雪花算法）',
    `scheme_id`   BIGINT   NOT NULL COMMENT '方案 ID',
    `user_id`     BIGINT   NOT NULL COMMENT '目标用户 ID',
    `deleted`     TINYINT  NOT NULL DEFAULT 0,
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_scheme` (`scheme_id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='方案指定用户表（延年圣手私有发布）';

-- =============================================================================
-- 六、穴位坐标数据表（用户穴位对照表）
-- =============================================================================

CREATE TABLE `user_acupoint_table` (
    `id`              BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `user_id`         BIGINT       NOT NULL COMMENT '用户 ID',
    `shoulder_width`  DECIMAL(6,2) NOT NULL COMMENT '生成时肩宽（cm）',
    `body_length`     DECIMAL(6,2) NOT NULL COMMENT '生成时身长（cm）',
    `finger_cun1`     DECIMAL(6,2) NOT NULL COMMENT '使用的一寸值（cm）',
    `finger_cun1_5`   DECIMAL(6,2) NOT NULL COMMENT '使用的 1.5 寸值（cm）',
    `finger_cun3`     DECIMAL(6,2) NOT NULL COMMENT '使用的三寸值（cm）',
    `is_estimated`    TINYINT      NOT NULL DEFAULT 0 COMMENT '指寸是否为估算：1 是 0 否',
    `confidence`      DECIMAL(5,2) NULL     COMMENT '置信度（%）',
    `precision_cm`    DECIMAL(4,1) NULL     COMMENT '预估精度（±cm）',
    `deleted`         TINYINT      NOT NULL DEFAULT 0,
    `create_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户穴位对照表';

CREATE TABLE `user_acupoint_item` (
    `id`              BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `table_id`        BIGINT       NOT NULL COMMENT '对照表 ID',
    `user_id`         BIGINT       NOT NULL COMMENT '用户 ID',
    `acupoint_id`     BIGINT       NOT NULL COMMENT '穴位 ID',
    `acupoint_code`   VARCHAR(64)  NOT NULL COMMENT '穴位编码',
    `acupoint_name`   VARCHAR(128) NOT NULL COMMENT '穴位名称',
    `side`            VARCHAR(16)  NOT NULL DEFAULT 'center' COMMENT '侧别：center / left / right',
    `offset_x`        DECIMAL(8,2) NOT NULL COMMENT '相对大椎穴横向偏移（cm）',
    `offset_y`        DECIMAL(8,2) NOT NULL COMMENT '相对大椎穴纵向偏移（cm）',
    `offset_in_cun`   DECIMAL(6,2) NOT NULL COMMENT '偏移寸数',
    `cun_type`        VARCHAR(16)  NOT NULL COMMENT '指寸类型：cun1 / cun1_5 / cun3',
    `deleted`         TINYINT      NOT NULL DEFAULT 0,
    `create_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_table` (`table_id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户穴位坐标明细表';

-- =============================================================================
-- 七、设备同步表（延年圣手快捷键）
-- =============================================================================

CREATE TABLE `device_hotkey_mapping` (
    `id`           BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `user_id`      BIGINT       NOT NULL COMMENT '用户 ID',
    `device_id`    BIGINT       NOT NULL COMMENT '设备 ID',
    `slot`         INT          NOT NULL COMMENT '快捷键槽位（1-8）',
    `scheme_id`    BIGINT       NOT NULL COMMENT '方案 ID',
    `scheme_name`  VARCHAR(128) NOT NULL COMMENT '方案名称（冗余）',
    `process_id`   BIGINT       NULL     COMMENT '工艺语言 ID',
    `synced_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '同步时间',
    `deleted`      TINYINT      NOT NULL DEFAULT 0,
    `create_time`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_device_slot` (`user_id`, `device_id`, `slot`),
    KEY `idx_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备快捷键映射表（延年圣手 1-8）';

-- =============================================================================
-- 八、工艺语言表
-- =============================================================================

CREATE TABLE `moxa_process` (
    `id`           BIGINT   NOT NULL COMMENT '主键（雪花算法）',
    `scheme_id`    BIGINT   NOT NULL COMMENT '方案 ID',
    `process_data` JSON     NOT NULL COMMENT '工艺语言数据（JSON）',
    `version`      INT      NOT NULL DEFAULT 1 COMMENT '版本号',
    `deleted`      TINYINT  NOT NULL DEFAULT 0,
    `create_time`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_scheme` (`scheme_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='艾灸工艺语言表';

-- =============================================================================
-- 九、艾灸记录表
-- =============================================================================

CREATE TABLE `moxa_session_record` (
    `id`          BIGINT       NOT NULL COMMENT '主键（雪花算法）',
    `user_id`     BIGINT       NOT NULL COMMENT '用户 ID',
    `device_id`   BIGINT       NULL     COMMENT '设备 ID',
    `scheme_id`   BIGINT       NULL     COMMENT '方案 ID',
    `started_at`  DATETIME     NULL     COMMENT '开始时间',
    `ended_at`    DATETIME     NULL     COMMENT '结束时间',
    `duration`    INT          NULL     COMMENT '时长（秒）',
    `rating`      INT          NULL     COMMENT '评分（1-5）',
    `remark`      VARCHAR(512) NULL     COMMENT '备注',
    `deleted`     TINYINT      NOT NULL DEFAULT 0,
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_scheme` (`scheme_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='艾灸会话记录表';

-- =============================================================================
-- 种子数据
-- =============================================================================

-- --------------- 1. 管理员账号 ---------------
-- 默认管理员密码: admin
INSERT INTO `sys_admin_user` (`id`, `username`, `password_hash`, `display_name`, `role`, `status`) VALUES
(1, 'admin', '$2b$10$yW9CwM2u/vqz4CGZsQXPyeiKYtsu4PbAj.vKstKoY4hbFT6I0LiNm', '系统管理员', 'SUPER_ADMIN', 'ACTIVE');

-- --------------- 2. 基础穴位库（12 个） ---------------
INSERT INTO `acupoint` (`id`, `code`, `name`, `meridian`, `efficacy`, `positioning`, `offset_cun`, `cun_type`, `side`, `lateral_offset`, `target_gender`, `status`, `source`) VALUES
(101, 'dazhui',      '大椎穴',   '督脉',           '驱寒,祛湿,解表',   'relative', 0,    'cun1', 'center', NULL, 'all', 'active', 'preset'),
(102, 'shenzhu',     '身柱穴',   '督脉',           '止咳平喘,安神',    'relative', 3,    'cun1', 'center', NULL, 'all', 'active', 'preset'),
(103, 'zhiyang',     '至阳穴',   '督脉',           '宽胸理气',         'relative', 7,    'cun1', 'center', NULL, 'all', 'active', 'preset'),
(104, 'mingmen',     '命门穴',   '督脉',           '补肾壮阳',         'relative', 14,   'cun1', 'center', NULL, 'all', 'active', 'preset'),
(105, 'yaoyangquan', '腰阳关穴', '督脉',           '强腰健肾',         'relative', 17,   'cun1', 'center', NULL, 'all', 'active', 'preset'),
(106, 'fengmen',     '风门穴',   '足太阳膀胱经',   '祛风解表',         'relative', 1.5,  'cun1', 'both',   1.5,  'all', 'active', 'preset'),
(107, 'bingfeng',    '秉风穴',   '手太阳小肠经',   '祛风散寒',         'relative', 3,    'cun1', 'both',   3,    'all', 'active', 'preset'),
(108, 'feishu',      '肺俞穴',   '足太阳膀胱经',   '化痰止咳',         'relative', 3,    'cun1', 'both',   1.5,  'all', 'active', 'preset'),
(109, 'geshu',       '膈俞穴',   '足太阳膀胱经',   '活血化瘀',         'relative', 7,    'cun1', 'both',   1.5,  'all', 'active', 'preset'),
(110, 'pishu',       '脾俞穴',   '足太阳膀胱经',   '健脾养血',         'relative', 11,   'cun1', 'both',   1.5,  'all', 'active', 'preset'),
(111, 'shenshu',     '肾俞穴',   '足太阳膀胱经',   '补肾益气',         'relative', 14,   'cun1', 'both',   1.5,  'all', 'active', 'preset'),
(112, 'baliao',      '八髎穴',   '足太阳膀胱经',   '调理下焦',         'relative', 16,   'cun1', 'center', NULL, 'all', 'active', 'preset');

-- --------------- 3. 艾灸手法（5 种） ---------------
INSERT INTO `moxa_technique` (`id`, `code`, `name`, `is_default`, `description`, `movement_pattern`, `temp_min`, `temp_max`, `duration_min`, `duration_max`, `status`) VALUES
(201, 'oneclick', '一键艾灸', 1, '系统默认工艺，督脉5穴温和灸',       'oneclick', 42.0, 45.0, 1800, 1800, 'active'),
(202, 'gentle',   '温和灸',   0, '固定不动，常规艾灸，温补',           'fixed',    35.0, 50.0, 60,   1800, 'active'),
(203, 'pecking',  '雀啄灸',   0, '上下移动，强刺激，祛邪',             'vertical', 35.0, 50.0, 60,   1800, 'active'),
(204, 'circular', '回旋灸',   0, '圆周运动，温经散寒',                 'circular', 35.0, 50.0, 60,   1800, 'active'),
(205, 'sparrow',  '麻雀灸',   0, '点状移动，局部强刺激',               'point',    35.0, 50.0, 60,   1800, 'active');

-- --------------- 4. 一键艾灸默认方案 ---------------
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `age_min`, `age_max`, `description`, `is_default`, `status`) VALUES
(301, '一键艾灸（默认）', 'ONE_CLICK', NULL, 'admin', '1', 'ADMIN', NULL, 'all', 1, 120, '系统默认一键艾灸方案，督脉5穴温和灸，总时长约30分钟', 1, 'published');

INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`) VALUES
(401, 301, 1, 101, '大椎穴',   'center', 42.0, 300, 202, '温和灸', '基准穴位'),
(402, 301, 2, 102, '身柱穴',   'center', 43.0, 300, 202, '温和灸', NULL),
(403, 301, 3, 103, '至阳穴',   'center', 44.0, 360, 202, '温和灸', NULL),
(404, 301, 4, 104, '命门穴',   'center', 44.0, 420, 202, '温和灸', NULL),
(405, 301, 5, 105, '腰阳关穴', 'center', 45.0, 420, 202, '温和灸', NULL);

-- --------------- 5. 8 种专业模式默认方案 ---------------

-- 5.1 驱寒模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(302, '驱寒模式', 'PROFESSIONAL', '驱寒', 'admin', '1', 'ADMIN', '怕冷,手脚冰凉', 'all', '温阳散寒方案，针对怕冷、手脚冰凉等寒证', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(411, 302, 1, 101, '大椎穴', 'center', 46.0, 540, 202, '温和灸'),
(412, 302, 2, 104, '命门穴', 'center', 47.0, 600, 202, '温和灸');

-- 5.2 祛湿模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(303, '祛湿模式', 'PROFESSIONAL', '祛湿', 'admin', '1', 'ADMIN', '身体困重,大便黏腻', 'all', '健脾祛湿方案，针对身体困重、大便黏腻等湿证', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(421, 303, 1, 101, '大椎穴', 'center', 43.0, 360, 202, '温和灸'),
(422, 303, 2, 110, '脾俞穴', 'both',   44.0, 420, 202, '温和灸'),
(423, 303, 3, 111, '肾俞穴', 'both',   44.0, 420, 202, '温和灸');

-- 5.3 祛风模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(304, '祛风模式', 'PROFESSIONAL', '祛风', 'admin', '1', 'ADMIN', '感冒,头痛', 'all', '祛风解表方案，针对感冒、头痛等风证', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(431, 304, 1, 101, '大椎穴', 'center', 41.0, 330, 203, '雀啄灸'),
(432, 304, 2, 106, '风门穴', 'both',   41.0, 330, 203, '雀啄灸'),
(433, 304, 3, 107, '秉风穴', 'both',   41.0, 330, 203, '雀啄灸');

-- 5.4 化瘀模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(305, '化瘀模式', 'PROFESSIONAL', '化瘀', 'admin', '1', 'ADMIN', '疼痛,淤青', 'all', '活血化瘀方案，针对疼痛、淤青等血瘀证', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(441, 305, 1, 101, '大椎穴', 'center', 44.0, 420, 204, '回旋灸'),
(442, 305, 2, 109, '膈俞穴', 'both',   44.0, 420, 204, '回旋灸'),
(443, 305, 3, 112, '八髎穴', 'center', 44.0, 420, 204, '回旋灸');

-- 5.5 活血模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(306, '活血模式', 'PROFESSIONAL', '活血', 'admin', '1', 'ADMIN', '血液循环不畅', 'all', '活血通络方案，针对血液循环不畅', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(451, 306, 1, 101, '大椎穴', 'center', 43.0, 420, 202, '温和灸'),
(452, 306, 2, 109, '膈俞穴', 'both',   44.0, 420, 202, '温和灸'),
(453, 306, 3, 104, '命门穴', 'center', 44.0, 420, 202, '温和灸');

-- 5.6 化痰模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(307, '化痰模式', 'PROFESSIONAL', '化痰', 'admin', '1', 'ADMIN', '咳嗽,痰多', 'all', '化痰止咳方案，针对咳嗽、痰多', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(461, 307, 1, 101, '大椎穴', 'center', 41.0, 360, 202, '温和灸'),
(462, 307, 2, 108, '肺俞穴', 'both',   42.0, 360, 202, '温和灸'),
(463, 307, 3, 110, '脾俞穴', 'both',   42.0, 360, 202, '温和灸');

-- 5.7 养颜模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(308, '养颜模式', 'PROFESSIONAL', '养颜', 'admin', '1', 'ADMIN', '皮肤暗沉', 'all', '美容养颜方案，针对皮肤暗沉', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(471, 308, 1, 101, '大椎穴', 'center', 41.0, 330, 202, '温和灸'),
(472, 308, 2, 108, '肺俞穴', 'both',   41.0, 330, 202, '温和灸'),
(473, 308, 3, 110, '脾俞穴', 'both',   41.0, 330, 202, '温和灸'),
(474, 308, 4, 111, '肾俞穴', 'both',   41.0, 330, 202, '温和灸');

-- 5.8 扶阳模式
INSERT INTO `scheme` (`id`, `scheme_name`, `mode`, `efficacy_type`, `creator_type`, `creator_id`, `creator_name`, `symptoms`, `target_gender`, `description`, `is_default`, `status`) VALUES
(309, '扶阳模式', 'PROFESSIONAL', '扶阳', 'admin', '1', 'ADMIN', '腰膝酸软', 'all', '温补肾阳方案，针对腰膝酸软', 1, 'published');
INSERT INTO `scheme_step` (`id`, `scheme_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`) VALUES
(481, 309, 1, 101, '大椎穴', 'center', 46.0, 540, 202, '温和灸'),
(482, 309, 2, 104, '命门穴', 'center', 47.0, 540, 202, '温和灸'),
(483, 309, 3, 103, '至阳穴', 'center', 46.0, 540, 202, '温和灸');

-- --------------- 6. 模拟用户数据 ---------------
INSERT INTO `app_user` (`id`, `name`, `gender`, `age`, `phone`, `shoulder_width`, `body_length`, `finger_cun1`, `finger_cun1_5`, `finger_cun3`, `acupoint_table_status`) VALUES
(501, '张三', 'male',   35, '13800001234', 42.00, 85.00, 2.1, 3.2, 6.3, 'GENERATED'),
(502, '李四', 'female', 33, '13900005678', 36.00, 78.00, NULL, NULL, NULL, 'NOT_GENERATED'),
(503, '王五', 'male',   62, '13700009012', 40.00, 82.00, NULL, NULL, NULL, 'NOT_GENERATED');

-- --------------- 7. 模拟设备数据 ---------------
INSERT INTO `app_device` (`id`, `device_code`, `device_name`, `model`, `serial_number`, `firmware_version`, `status`) VALUES
(601, 'AJY001', '艾灸椅-001', 'AJY-2026', 'SN20260001', 'v1.2.0', 'online'),
(602, 'AJY002', '艾灸椅-002', 'AJY-2026', 'SN20260002', 'v1.1.0', 'offline');

-- --------------- 8. 用户-设备绑定 ---------------
INSERT INTO `app_user_device` (`id`, `user_id`, `device_id`, `is_primary`, `bound_at`) VALUES
(701, 501, 601, 1, '2026-03-15 10:00:00'),
(702, 501, 602, 0, '2026-03-18 14:00:00'),
(703, 502, 601, 0, '2026-03-18 14:00:00');

SET FOREIGN_KEY_CHECKS = 1;