| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614 |
- -- ==================== 艾灸椅管理后台初始化 SQL ====================
- -- 执行前请确保已创建数据库:CREATE DATABASE aijiuyi_admin DEFAULT CHARACTER SET utf8mb4;
- -- 数据库连接:jdbc:mysql://localhost:3306/aijiuyi_admin
- SET NAMES utf8mb4;
- SET FOREIGN_KEY_CHECKS = 0;
- -- ----------------------------
- -- 系统操作日志表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `sys_log` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `user_id` BIGINT COMMENT '请求用户ID',
- `user_name` VARCHAR(100) COMMENT '请求用户名',
- `user_type` TINYINT COMMENT '操作者类型:1=普通用户,2=专家,3=管理员(统一使用app_user表)',
- `request_url` VARCHAR(500) COMMENT '请求接口路径',
- `request_params` TEXT COMMENT '请求参数(JSON格式)',
- `request_result` TEXT COMMENT '请求结果(JSON格式,超2000字符截断)',
- `api_desc` VARCHAR(200) COMMENT '接口说明(来自@Log注解)',
- `operation_type` VARCHAR(20) COMMENT '操作方式(查询/新增/更新/删除/其他)',
- `request_ip` VARCHAR(50) COMMENT '请求客户端IP',
- `operation_module` VARCHAR(100) COMMENT '操作模块(来自@Log注解)',
- `request_id` VARCHAR(50) COMMENT '请求唯一ID(同一请求的所有日志共享)',
- `create_time` DATETIME COMMENT '创建时间',
- PRIMARY KEY (`id`),
- KEY `idx_request_id` (`request_id`),
- KEY `idx_user_id` (`user_id`),
- KEY `idx_create_time` (`create_time`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '系统操作日志表';
- -- ----------------------------
- -- 应用用户表(手机号验证码登录的普通用户/专家)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `app_user` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `phone` VARCHAR(20) COMMENT '手机号(唯一,用于登录)',
- `username` VARCHAR(64) COMMENT '用户名(管理员登录用,唯一)',
- `password` VARCHAR(64) COMMENT '密码(MD5加密,管理员用)',
- `nickname` VARCHAR(100) COMMENT '昵称',
- `avatar` VARCHAR(500) COMMENT '头像URL',
- `user_type` TINYINT NOT NULL DEFAULT 1 COMMENT '用户类型:1=普通用户,2=专家,3=管理员',
- `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=正常,0=禁用',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_phone` (`phone`),
- UNIQUE KEY `uk_username` (`username`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '应用用户表';
- -- 初始化管理员账号(密码:admin123,MD5后为:0192023a7bbd73250516f069df18b500)
- INSERT INTO `app_user` (`username`, `password`, `nickname`, `user_type`, `status`, `deleted`, `create_time`, `update_time`)
- VALUES ('admin', MD5('admin123'), '超级管理员', 3, 1, 0, NOW(), NOW());
- -- ----------------------------
- -- 用户类别表(用户类型字典,code 与 app_user.user_type / user_profile.user_type 关联)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `user_category` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `code` INT NOT NULL COMMENT '类别编码(与 user_type 字段对应,唯一)',
- `name` VARCHAR(50) NOT NULL COMMENT '类别名称',
- `remark` VARCHAR(255) COMMENT '备注说明',
- `tag_type` VARCHAR(20) DEFAULT 'primary' COMMENT '前端标签颜色:primary/success/warning/danger/info',
- `sort` INT NOT NULL DEFAULT 0 COMMENT '排序(升序)',
- `builtin` TINYINT NOT NULL DEFAULT 0 COMMENT '是否内置:1=内置(不可删除/改code),0=自定义',
- `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=启用,0=停用',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_code` (`code`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '用户类别表';
- -- 初始化内置用户类别(与原 userType 取值保持一致)
- INSERT INTO `user_category` (`code`, `name`, `remark`, `tag_type`, `sort`, `builtin`, `status`, `deleted`, `create_time`, `update_time`)
- VALUES
- (1, '普通用户', '默认用户类别', 'primary', 1, 1, 1, 0, NOW(), NOW()),
- (2, '专家', '可创作方案的专家用户', 'warning', 2, 1, 1, 0, NOW(), NOW()),
- (3, '管理员', '后台管理员(仅 code=3 可登录后台)', 'danger', 3, 1, 1, 0, NOW(), NOW());
- -- ----------------------------
- -- 子用户信息表(属于某个应用用户)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `user_profile` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `user_id` BIGINT COMMENT '所属应用用户ID(关联 app_user.id)',
- `name` VARCHAR(50) NOT NULL COMMENT '姓名',
- `gender` TINYINT COMMENT '性别:1=男,2=女',
- `age` INT COMMENT '年龄',
- `phone` VARCHAR(20) COMMENT '手机号(唯一)',
- `user_type` TINYINT NOT NULL DEFAULT 1 COMMENT '用户类型:1=普通用户,2=专家,3=管理员',
- `province_code` VARCHAR(12) COMMENT '省级行政区划编码(GB/T 2260)',
- `city_code` VARCHAR(12) COMMENT '市级行政区划编码',
- `district_code` VARCHAR(12) COMMENT '区/县级行政区划编码',
- `address` VARCHAR(255) COMMENT '详细地址',
- `shoulder_width` DECIMAL(5,1) COMMENT '肩宽(cm)',
- `body_height` DECIMAL(5,1) COMMENT '身长/坐高(cm)',
- `spine_length` DECIMAL(5,1) COMMENT '脊柱长度C7-S4(cm,范围30-60)',
- `height` DECIMAL(5,1) COMMENT '身高(cm)',
- `weight` DECIMAL(5,1) COMMENT '体重(kg)',
- `finger_width_1` DECIMAL(4,2) COMMENT '1寸指宽(cm,选填,提高穴位精度)',
- `finger_width_15` DECIMAL(4,2) COMMENT '1.5寸指宽(cm,选填)',
- `finger_width_3` DECIMAL(4,2) COMMENT '3寸指宽(cm,选填)',
- `acupoint_table_id` BIGINT COMMENT '用户穴位表ID',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_user_id` (`user_id`),
- KEY `idx_phone` (`phone`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '子用户信息表';
- -- ----------------------------
- -- 用户设备绑定表(子用户与设备绑定)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `user_device` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `user_id` BIGINT NOT NULL COMMENT '所属子用户ID(关联 user_profile.id)',
- `device_code` VARCHAR(100) NOT NULL COMMENT '设备唯一编码',
- `device_name` VARCHAR(100) COMMENT '设备名称',
- `device_model` VARCHAR(100) COMMENT '设备型号',
- `is_primary` TINYINT NOT NULL DEFAULT 0 COMMENT '是否主设备:1=是,0=否',
- `online_status` TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态:1=在线,0=离线',
- `status` TINYINT NOT NULL DEFAULT 1 COMMENT '绑定状态:1=已绑定,0=已解绑',
- `bind_time` DATETIME COMMENT '绑定时间',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_user_id` (`user_id`),
- KEY `idx_device_code` (`device_code`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '用户设备绑定表(子用户绑定设备)';
- -- ----------------------------
- -- 艾灸椅设备表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `device` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `device_code` VARCHAR(50) NOT NULL COMMENT '设备编号(唯一,如:AJY001)',
- `device_name` VARCHAR(100) NOT NULL COMMENT '设备名称(如:艾灸椅-001)',
- `device_model` VARCHAR(50) COMMENT '设备型号(AJY-2026 / AJY-2025)',
- `serial_no` VARCHAR(50) COMMENT '设备序列号(唯一)',
- `firmware_version` VARCHAR(20) COMMENT '固件版本(如:v1.2.0)',
- `online_status` TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态:1=在线,0=离线',
- `last_online_time` DATETIME COMMENT '最后在线时间',
- `remark` VARCHAR(200) COMMENT '备注',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_device_code` (`device_code`),
- UNIQUE KEY `uk_serial_no` (`serial_no`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '艾灸椅设备表';
- -- ----------------------------
- -- 穴位信息表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `acupoint` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `acupoint_code` VARCHAR(100) NOT NULL COMMENT '穴位编码(唯一标识,如 dazhui)',
- `excel_serial_no` VARCHAR(20) COMMENT 'Excel原始序号',
- `name` VARCHAR(50) NOT NULL COMMENT '穴位名称(如:大椎穴)',
- `single_double` VARCHAR(20) COMMENT '单/双穴(Excel原始字段)',
- `meridian` VARCHAR(100) COMMENT '所属经络(如:督脉、足太阳膀胱经)',
- `body_part` VARCHAR(50) COMMENT '身体部位(Excel原始字段)',
- `body_region` VARCHAR(50) COMMENT '部位分区(Excel原始字段)',
- `location_description` VARCHAR(500) COMMENT '穴位定位原文(Excel原始字段)',
- `relative_position` VARCHAR(200) COMMENT '相对位置原文(Excel原始字段)',
- `indications` VARCHAR(1000) COMMENT '主管疾病/主治(Excel原始字段)',
- `product_symptoms` VARCHAR(500) COMMENT '产品症状覆盖(Excel原始字段)',
- `acupoint_method` VARCHAR(500) COMMENT '取穴说明(Excel原始字段)',
- `product_acupoint` TINYINT NOT NULL DEFAULT 0 COMMENT '是否艾灸椅产品穴位:1=是,0=否',
- `effects` VARCHAR(500) COMMENT '功效描述(逗号分隔,如:驱寒,祛湿)',
- `location_type` TINYINT COMMENT '定位方式:固定为1=相对定位',
- `longitude_offset` DECIMAL(6,2) COMMENT '相对大椎穴纵向偏移(寸)',
- `lateral_offset` DECIMAL(6,2) COMMENT '相对大椎穴横向偏移(寸,双侧穴位填写)',
- `normalized_y` DECIMAL(6,4) COMMENT 'Panjabi归一化Y位置系数(0~1,基于C7-S4脊柱长度)',
- `y_reference_term` VARCHAR(100) COMMENT 'Y轴医学定位术语',
- `parse_status` VARCHAR(20) COMMENT '定位描述解析状态:已解析/待复核/暂不支持',
- `parse_confidence` VARCHAR(10) COMMENT '定位描述解析置信度:高/中/低',
- `parse_message` VARCHAR(500) COMMENT '定位描述解析说明',
- `side` TINYINT COMMENT '侧别:1=中心,2=左侧,3=右侧,4=双侧',
- `finger_width_type` TINYINT COMMENT '指寸类型:1=一寸,2=1.5寸,3=三寸',
- `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '适用性别:0=不限,1=仅男,2=仅女',
- `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=活跃,0=待完善',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_acupoint_code` (`acupoint_code`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '穴位信息表';
- -- 初始化12个常用穴位(含Panjabi归一化Y位置系数)
- INSERT INTO `acupoint` (`acupoint_code`, `name`, `meridian`, `effects`, `location_type`, `longitude_offset`, `lateral_offset`, `normalized_y`, `side`, `finger_width_type`, `gender`, `status`, `deleted`, `create_time`, `update_time`) VALUES
- ('dazhui', '大椎穴', '督脉', '驱寒,祛湿', 1, 0.00, NULL, 0.0000, 1, 1, 0, 1, 0, NOW(), NOW()),
- ('fengmen', '风门穴', '足太阳膀胱经', '祛风,解表', 1, 2.00, 1.50, 0.0780, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('feishu', '肺俞穴', '足太阳膀胱经', '宣肺,止咳', 1, 3.00, 1.50, 0.1200, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('xinshu', '心俞穴', '足太阳膀胱经', '养心,安神', 1, 5.00, 1.50, 0.2030, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('ganshu', '肝俞穴', '足太阳膀胱经', '疏肝,理气', 1, 9.00, 1.50, 0.3610, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('pishu', '脾俞穴', '足太阳膀胱经', '健脾,祛湿', 1, 11.00, 1.50, 0.4380, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('weishu', '胃俞穴', '足太阳膀胱经', '和胃,消食', 1, 12.00, 1.50, 0.4760, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('shenshu', '肾俞穴', '足太阳膀胱经', '补肾,扶阳', 1, 14.00, 1.50, 0.5520, 4, 1, 0, 1, 0, NOW(), NOW()),
- ('mingmen', '命门穴', '督脉', '扶阳,固本', 1, 14.00, NULL, 0.5520, 1, 1, 0, 1, 0, NOW(), NOW()),
- ('zhongwan', '中脘穴', '任脉', '健脾,和胃', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW()),
- ('qihai', '气海穴', '任脉', '扶阳,益气', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW()),
- ('guanyuan', '关元穴', '任脉', '温肾,扶阳', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW());
- -- ----------------------------
- -- 用户穴位坐标表(根据用户体型数据和穴位信息计算生成)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `user_acupoint` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `user_id` BIGINT NOT NULL COMMENT '子用户ID(关联 user_profile.id)',
- `acupoint_id` BIGINT NOT NULL COMMENT '穴位ID(关联 acupoint.id)',
- `acupoint_name` VARCHAR(50) NOT NULL COMMENT '穴位名称(冗余存储,加速查询)',
- `acupoint_side` VARCHAR(10) NOT NULL DEFAULT 'center' COMMENT '侧别:center=中心,left=左侧,right=右侧',
- `cun_value` DECIMAL(6,2) COMMENT '相对大椎穴纵向偏移(寸)',
- `cun_type` VARCHAR(10) COMMENT '指寸类型:1寸/1.5寸/3寸',
- `coordinate_x` DECIMAL(8,2) COMMENT '计算后的X坐标(mm,相对大椎穴,向右为正)',
- `coordinate_y` DECIMAL(8,2) COMMENT '计算后的Y坐标(mm,相对大椎穴,向下为正)',
- `coordinate_z` DECIMAL(8,2) COMMENT 'Z坐标(mm,体表=0)',
- `confidence` VARCHAR(10) COMMENT '置信度:high=高/medium=中/low=低',
- `create_time` DATETIME COMMENT '首次生成时间',
- `update_time` DATETIME COMMENT '最后更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_user_id` (`user_id`),
- KEY `idx_acupoint_id` (`acupoint_id`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '用户穴位坐标表(按用户体型计算的个性化穴位坐标)';
- -- ----------------------------
- -- 艾灸手法表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `moxibustion_technique` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `technique_code` VARCHAR(50) NOT NULL COMMENT '手法编码(唯一标识,如 oneclick)',
- `name` VARCHAR(50) NOT NULL COMMENT '手法名称(如:一键艾灸)',
- `motion_mode` VARCHAR(20) NOT NULL COMMENT '运动模式:default=默认工艺,fixed=固定不动,updown=上下移动,circular=圆周运动,dotted=点状移动',
- `temp_min` DECIMAL(4,1) NOT NULL COMMENT '温度范围最小值(°C,35-50)',
- `temp_max` DECIMAL(4,1) NOT NULL COMMENT '温度范围最大值(°C,35-50)',
- `time_min` INT NOT NULL COMMENT '时间范围最小值(分钟,1-30)',
- `time_max` INT NOT NULL COMMENT '时间范围最大值(分钟,1-30)',
- `motion_amplitude` DECIMAL(4,1) COMMENT '运动幅度(mm,1-20,运动模式非固定不动时必填)',
- `motion_speed` DECIMAL(4,1) COMMENT '运动速度(mm/s,1-10,运动模式非固定不动时必填)',
- `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=启用,0=禁用',
- `remark` VARCHAR(200) COMMENT '备注(最长200字符)',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_technique_code` (`technique_code`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '艾灸手法表';
- -- 初始化5个内置手法
- INSERT INTO `moxibustion_technique` (`technique_code`, `name`, `motion_mode`, `temp_min`, `temp_max`, `time_min`, `time_max`, `motion_amplitude`, `motion_speed`, `status`, `deleted`, `create_time`, `update_time`) VALUES
- ('oneclick', '一键艾灸', 'default', 42.0, 45.0, 30, 30, NULL, NULL, 1, 0, NOW(), NOW()),
- ('gentle', '温和灸', 'fixed', 35.0, 50.0, 1, 30, NULL, NULL, 1, 0, NOW(), NOW()),
- ('pecking', '雀啄灸', 'updown', 35.0, 50.0, 1, 30, 10.0, 5.0, 1, 0, NOW(), NOW()),
- ('circular', '回旋灸', 'circular', 35.0, 50.0, 1, 30, 15.0, 3.0, 1, 0, NOW(), NOW()),
- ('sparrow', '麻雀灸', 'dotted', 35.0, 50.0, 1, 30, 8.0, 4.0, 1, 0, NOW(), NOW());
- -- ----------------------------
- -- 方案表(艾灸方案)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `plan` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `plan_code` VARCHAR(50) NOT NULL COMMENT '方案编码(唯一标识,如 S001)',
- `name` VARCHAR(100) NOT NULL COMMENT '方案名称(最长100字符)',
- `mode_type` TINYINT NOT NULL COMMENT '模式类型:1=一键艾灸,2=专业模式,3=自定义模式,4=延年圣手',
- `effect_type` VARCHAR(20) COMMENT '功效类型:驱寒/祛湿/祛风/化瘀/活血/化痰/养颜/扶阳',
- `symptoms` VARCHAR(500) COMMENT '适用症状(逗号分隔:怕冷,感冒,咳嗽,...)',
- `applicable_gender` TINYINT NOT NULL DEFAULT 0 COMMENT '适用人群:0=不限,1=仅男,2=仅女',
- `age_min` INT NOT NULL DEFAULT 1 COMMENT '适用年龄最小值(岁)',
- `age_max` INT NOT NULL DEFAULT 120 COMMENT '适用年龄最大值(岁)',
- `description` VARCHAR(200) COMMENT '方案描述(最长200字符)',
- `author_type` TINYINT NOT NULL DEFAULT 1 COMMENT '创作人类型:1=管理员,2=普通用户,3=专家',
- `author_id` BIGINT COMMENT '创作人ID(对应 app_user.id)',
- `author_name` VARCHAR(100) COMMENT '创作人名称(冗余存储,加速查询)',
- `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0=草稿,1=已发布,2=已下架',
- `use_count` INT NOT NULL DEFAULT 0 COMMENT '使用人数',
- `avg_rating` DECIMAL(3,1) COMMENT '平均评分',
- `last_use_time` DATETIME COMMENT '最后使用时间',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_plan_code` (`plan_code`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '方案表(艾灸方案)';
- -- ----------------------------
- -- 方案步骤表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `plan_step` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `plan_id` BIGINT NOT NULL COMMENT '方案ID(关联 plan.id)',
- `step_order` INT NOT NULL COMMENT '步骤序号(从1开始)',
- `acupoint_id` BIGINT COMMENT '穴位ID(关联 acupoint.id)',
- `acupoint_name` VARCHAR(50) NOT NULL COMMENT '穴位名称(冗余存储)',
- `side` TINYINT NOT NULL COMMENT '侧别:1=中心,2=左侧,3=右侧,4=双侧',
- `temperature` DECIMAL(4,1) NOT NULL COMMENT '温度(°C,35-50)',
- `duration` INT NOT NULL COMMENT '时间(分钟,1-30)',
- `technique_id` BIGINT COMMENT '艾灸手法ID(关联 moxibustion_technique.id)',
- `technique_name` VARCHAR(50) COMMENT '艾灸手法名称(冗余存储)',
- `remark` VARCHAR(100) COMMENT '备注(最长100字符)',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_plan_id` (`plan_id`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '方案步骤表';
- -- ----------------------------
- -- 用户方案表(用户个性化工艺参数方案)
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `user_plan` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `user_id` BIGINT NOT NULL COMMENT '子用户ID(关联 user_profile.id)',
- `plan_id` BIGINT NOT NULL COMMENT '方案ID(关联 plan.id)',
- `plan_code` VARCHAR(50) NOT NULL COMMENT '方案编码(冗余存储)',
- `plan_name` VARCHAR(100) NOT NULL COMMENT '方案名称(冗余存储)',
- `mode_type` TINYINT NOT NULL COMMENT '模式类型:1=一键艾灸,2=专业模式,3=自定义模式,4=延年圣手',
- `author_name` VARCHAR(100) COMMENT '创作人名称(冗余存储)',
- `step_count` INT NOT NULL DEFAULT 0 COMMENT '步骤数量',
- `use_count` INT NOT NULL DEFAULT 0 COMMENT '使用次数',
- `steps_json` TEXT COMMENT '个性化工艺步骤(JSON格式,含坐标/温度/时间等)',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '订阅/生成时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_user_id` (`user_id`),
- KEY `idx_plan_id` (`plan_id`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '用户方案表(用户个性化工艺参数方案)';
- -- ----------------------------
- -- 初始化系统默认方案:1个一键艾灸 + 8个专业方案
- -- ----------------------------
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S001', '一键艾灸(默认)', 1, NULL, '日常调理,疲劳,手脚冰凉', 0, 1, 120, '系统默认一键艾灸方案,覆盖基础督脉与膀胱经调理。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S001');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S002', '专业驱寒方案', 2, '驱寒', '怕冷,手脚冰凉,畏寒', 0, 1, 120, '针对阳虚畏寒人群的基础驱寒方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S002');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S003', '专业祛湿方案', 2, '祛湿', '困倦,湿重,食欲差', 0, 1, 120, '围绕脾胃与膀胱经调理的祛湿方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S003');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S004', '专业祛风方案', 2, '祛风', '感冒,怕风,鼻塞', 0, 1, 120, '针对风寒风邪引发不适的基础调理方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S004');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S005', '专业化瘀方案', 2, '化瘀', '僵硬,酸痛,局部不适', 0, 1, 120, '用于改善局部瘀滞与经络不畅。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S005');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S006', '专业活血方案', 2, '活血', '疲劳,循环差,肩背紧张', 0, 1, 120, '帮助提升背部循环与整体活力。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S006');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S007', '专业化痰方案', 2, '化痰', '咳嗽,痰多,胸闷', 0, 1, 120, '偏肺俞与中焦调理的化痰方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S007');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S008', '专业养颜方案', 2, '养颜', '气色差,疲劳,睡眠一般', 0, 1, 120, '通过脾胃与气海关元综合调理气色。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S008');
- INSERT INTO `plan` (`plan_code`, `name`, `mode_type`, `effect_type`, `symptoms`, `applicable_gender`, `age_min`, `age_max`, `description`, `author_type`, `author_name`, `status`, `use_count`, `deleted`, `create_time`, `update_time`)
- SELECT 'S009', '专业扶阳方案', 2, '扶阳', '乏力,腰酸,手脚冰凉', 0, 1, 120, '以命门肾俞关元为主的扶阳固本方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
- WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S009');
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 1, '大椎穴', 1, 42.0, 6, 2, '温和灸', '默认基准穴位', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S001'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 2, '风门穴', 4, 43.0, 6, 2, '温和灸', '双侧温养', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S001'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 3, '肺俞穴', 4, 43.0, 6, 2, '温和灸', '提升背部暖感', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S001'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 8, '肾俞穴', 4, 44.0, 6, 2, '温和灸', '补益下焦', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S001'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 5, 9, '命门穴', 1, 45.0, 6, 2, '温和灸', '固本收尾', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S001'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 5);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 1, '大椎穴', 1, 44.0, 8, 3, '雀啄灸', '驱寒起始', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S002'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 2, '风门穴', 4, 44.0, 8, 3, '雀啄灸', '祛寒散表', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S002'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 8, '肾俞穴', 4, 45.0, 8, 2, '温和灸', '温补肾阳', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S002'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 9, '命门穴', 1, 45.0, 8, 2, '温和灸', '扶阳收尾', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S002'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 6, '脾俞穴', 4, 43.0, 8, 2, '温和灸', '健脾化湿', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S003'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 7, '胃俞穴', 4, 43.0, 8, 2, '温和灸', '和胃助运化', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S003'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 10, '中脘穴', 1, 42.0, 8, 4, '回旋灸', '中焦祛湿', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S003'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 11, '气海穴', 1, 42.0, 6, 2, '温和灸', '益气固本', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S003'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 1, '大椎穴', 1, 42.0, 7, 2, '温和灸', '祛风首穴', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S004'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 2, '风门穴', 4, 43.0, 7, 3, '雀啄灸', '散风解表', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S004'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 3, '肺俞穴', 4, 42.0, 7, 2, '温和灸', '协同宣肺', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S004'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 4, '心俞穴', 4, 42.0, 6, 2, '温和灸', '安神扶正', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S004'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 4, '心俞穴', 4, 43.0, 8, 4, '回旋灸', '活络起始', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S005'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 5, '肝俞穴', 4, 44.0, 8, 4, '回旋灸', '疏肝化瘀', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S005'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 6, '脾俞穴', 4, 43.0, 6, 2, '温和灸', '调和中焦', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S005'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 9, '命门穴', 1, 44.0, 6, 2, '温和灸', '温阳通络', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S005'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 3, '肺俞穴', 4, 42.0, 7, 4, '回旋灸', '活血舒展', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S006'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 4, '心俞穴', 4, 43.0, 7, 4, '回旋灸', '促进循环', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S006'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 5, '肝俞穴', 4, 43.0, 7, 4, '回旋灸', '疏通经络', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S006'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 11, '气海穴', 1, 42.0, 6, 2, '温和灸', '补气收束', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S006'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 3, '肺俞穴', 4, 42.0, 8, 2, '温和灸', '宣肺止咳', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S007'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 2, '风门穴', 4, 42.0, 8, 2, '温和灸', '协同宣散', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S007'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 10, '中脘穴', 1, 41.0, 7, 4, '回旋灸', '中焦运化', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S007'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 11, '气海穴', 1, 41.0, 7, 2, '温和灸', '益气化痰', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S007'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 6, '脾俞穴', 4, 42.0, 8, 2, '温和灸', '健脾养颜', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S008'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 7, '胃俞穴', 4, 42.0, 8, 2, '温和灸', '和胃生化', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S008'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 11, '气海穴', 1, 42.0, 7, 4, '回旋灸', '补气养神', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S008'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 12, '关元穴', 1, 42.0, 7, 2, '温和灸', '养元收尾', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S008'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 1, 8, '肾俞穴', 4, 44.0, 8, 2, '温和灸', '补肾扶阳', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S009'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 2, 9, '命门穴', 1, 45.0, 8, 2, '温和灸', '培补命门火', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S009'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 3, 11, '气海穴', 1, 44.0, 7, 2, '温和灸', '益气助阳', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S009'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
- INSERT INTO `plan_step` (`plan_id`, `step_order`, `acupoint_id`, `acupoint_name`, `side`, `temperature`, `duration`, `technique_id`, `technique_name`, `remark`, `deleted`, `create_time`, `update_time`)
- SELECT p.id, 4, 12, '关元穴', 1, 44.0, 7, 2, '温和灸', '固本培元', 0, NOW(), NOW()
- FROM `plan` p
- WHERE p.plan_code = 'S009'
- AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
- -- ----------------------------
- -- 管理员权限配置表
- -- ----------------------------
- CREATE TABLE IF NOT EXISTS `admin_permission` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `admin_id` BIGINT NOT NULL COMMENT '管理员ID(关联 app_user.id)',
- `route_paths` TEXT COMMENT '可访问的路由路径(JSON数组,如 ["/dashboard","/device"])',
- `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
- `create_time` DATETIME COMMENT '创建时间',
- `update_time` DATETIME COMMENT '更新时间',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_admin_id` (`admin_id`)
- ) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4
- COMMENT = '管理员权限配置表';
- -- app_user 表追加管理员相关字段(首次执行时添加,若字段已存在请注释掉此段)
- ALTER TABLE `app_user`
- ADD COLUMN `admin_role` TINYINT DEFAULT NULL COMMENT '管理员角色:1=超级管理员,2=运营人员,3=只读用户(仅 userType=3 时有效)',
- ADD COLUMN `last_login_time` DATETIME DEFAULT NULL COMMENT '最后登录时间';
- -- 更新初始管理员为超级管理员角色
- UPDATE `app_user` SET `admin_role` = 1 WHERE `username` = 'admin' AND `admin_role` IS NULL;
- SET FOREIGN_KEY_CHECKS = 1;
|