init.sql 45 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614
  1. -- ==================== 艾灸椅管理后台初始化 SQL ====================
  2. -- 执行前请确保已创建数据库:CREATE DATABASE aijiuyi_admin DEFAULT CHARACTER SET utf8mb4;
  3. -- 数据库连接:jdbc:mysql://localhost:3306/aijiuyi_admin
  4. SET NAMES utf8mb4;
  5. SET FOREIGN_KEY_CHECKS = 0;
  6. -- ----------------------------
  7. -- 系统操作日志表
  8. -- ----------------------------
  9. CREATE TABLE IF NOT EXISTS `sys_log` (
  10. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  11. `user_id` BIGINT COMMENT '请求用户ID',
  12. `user_name` VARCHAR(100) COMMENT '请求用户名',
  13. `user_type` TINYINT COMMENT '操作者类型:1=普通用户,2=专家,3=管理员(统一使用app_user表)',
  14. `request_url` VARCHAR(500) COMMENT '请求接口路径',
  15. `request_params` TEXT COMMENT '请求参数(JSON格式)',
  16. `request_result` TEXT COMMENT '请求结果(JSON格式,超2000字符截断)',
  17. `api_desc` VARCHAR(200) COMMENT '接口说明(来自@Log注解)',
  18. `operation_type` VARCHAR(20) COMMENT '操作方式(查询/新增/更新/删除/其他)',
  19. `request_ip` VARCHAR(50) COMMENT '请求客户端IP',
  20. `operation_module` VARCHAR(100) COMMENT '操作模块(来自@Log注解)',
  21. `request_id` VARCHAR(50) COMMENT '请求唯一ID(同一请求的所有日志共享)',
  22. `create_time` DATETIME COMMENT '创建时间',
  23. PRIMARY KEY (`id`),
  24. KEY `idx_request_id` (`request_id`),
  25. KEY `idx_user_id` (`user_id`),
  26. KEY `idx_create_time` (`create_time`)
  27. ) ENGINE = InnoDB
  28. DEFAULT CHARSET = utf8mb4
  29. COMMENT = '系统操作日志表';
  30. -- ----------------------------
  31. -- 应用用户表(手机号验证码登录的普通用户/专家)
  32. -- ----------------------------
  33. CREATE TABLE IF NOT EXISTS `app_user` (
  34. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  35. `phone` VARCHAR(20) COMMENT '手机号(唯一,用于登录)',
  36. `username` VARCHAR(64) COMMENT '用户名(管理员登录用,唯一)',
  37. `password` VARCHAR(64) COMMENT '密码(MD5加密,管理员用)',
  38. `nickname` VARCHAR(100) COMMENT '昵称',
  39. `avatar` VARCHAR(500) COMMENT '头像URL',
  40. `user_type` TINYINT NOT NULL DEFAULT 1 COMMENT '用户类型:1=普通用户,2=专家,3=管理员',
  41. `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=正常,0=禁用',
  42. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  43. `create_time` DATETIME COMMENT '创建时间',
  44. `update_time` DATETIME COMMENT '更新时间',
  45. PRIMARY KEY (`id`),
  46. UNIQUE KEY `uk_phone` (`phone`),
  47. UNIQUE KEY `uk_username` (`username`)
  48. ) ENGINE = InnoDB
  49. DEFAULT CHARSET = utf8mb4
  50. COMMENT = '应用用户表';
  51. -- 初始化管理员账号(密码:admin123,MD5后为:0192023a7bbd73250516f069df18b500)
  52. INSERT INTO `app_user` (`username`, `password`, `nickname`, `user_type`, `status`, `deleted`, `create_time`, `update_time`)
  53. VALUES ('admin', MD5('admin123'), '超级管理员', 3, 1, 0, NOW(), NOW());
  54. -- ----------------------------
  55. -- 用户类别表(用户类型字典,code 与 app_user.user_type / user_profile.user_type 关联)
  56. -- ----------------------------
  57. CREATE TABLE IF NOT EXISTS `user_category` (
  58. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  59. `code` INT NOT NULL COMMENT '类别编码(与 user_type 字段对应,唯一)',
  60. `name` VARCHAR(50) NOT NULL COMMENT '类别名称',
  61. `remark` VARCHAR(255) COMMENT '备注说明',
  62. `tag_type` VARCHAR(20) DEFAULT 'primary' COMMENT '前端标签颜色:primary/success/warning/danger/info',
  63. `sort` INT NOT NULL DEFAULT 0 COMMENT '排序(升序)',
  64. `builtin` TINYINT NOT NULL DEFAULT 0 COMMENT '是否内置:1=内置(不可删除/改code),0=自定义',
  65. `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=启用,0=停用',
  66. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  67. `create_time` DATETIME COMMENT '创建时间',
  68. `update_time` DATETIME COMMENT '更新时间',
  69. PRIMARY KEY (`id`),
  70. UNIQUE KEY `uk_code` (`code`)
  71. ) ENGINE = InnoDB
  72. DEFAULT CHARSET = utf8mb4
  73. COMMENT = '用户类别表';
  74. -- 初始化内置用户类别(与原 userType 取值保持一致)
  75. INSERT INTO `user_category` (`code`, `name`, `remark`, `tag_type`, `sort`, `builtin`, `status`, `deleted`, `create_time`, `update_time`)
  76. VALUES
  77. (1, '普通用户', '默认用户类别', 'primary', 1, 1, 1, 0, NOW(), NOW()),
  78. (2, '专家', '可创作方案的专家用户', 'warning', 2, 1, 1, 0, NOW(), NOW()),
  79. (3, '管理员', '后台管理员(仅 code=3 可登录后台)', 'danger', 3, 1, 1, 0, NOW(), NOW());
  80. -- ----------------------------
  81. -- 子用户信息表(属于某个应用用户)
  82. -- ----------------------------
  83. CREATE TABLE IF NOT EXISTS `user_profile` (
  84. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  85. `user_id` BIGINT COMMENT '所属应用用户ID(关联 app_user.id)',
  86. `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  87. `gender` TINYINT COMMENT '性别:1=男,2=女',
  88. `age` INT COMMENT '年龄',
  89. `phone` VARCHAR(20) COMMENT '手机号(唯一)',
  90. `user_type` TINYINT NOT NULL DEFAULT 1 COMMENT '用户类型:1=普通用户,2=专家,3=管理员',
  91. `province_code` VARCHAR(12) COMMENT '省级行政区划编码(GB/T 2260)',
  92. `city_code` VARCHAR(12) COMMENT '市级行政区划编码',
  93. `district_code` VARCHAR(12) COMMENT '区/县级行政区划编码',
  94. `address` VARCHAR(255) COMMENT '详细地址',
  95. `shoulder_width` DECIMAL(5,1) COMMENT '肩宽(cm)',
  96. `body_height` DECIMAL(5,1) COMMENT '身长/坐高(cm)',
  97. `spine_length` DECIMAL(5,1) COMMENT '脊柱长度C7-S4(cm,范围30-60)',
  98. `height` DECIMAL(5,1) COMMENT '身高(cm)',
  99. `weight` DECIMAL(5,1) COMMENT '体重(kg)',
  100. `finger_width_1` DECIMAL(4,2) COMMENT '1寸指宽(cm,选填,提高穴位精度)',
  101. `finger_width_15` DECIMAL(4,2) COMMENT '1.5寸指宽(cm,选填)',
  102. `finger_width_3` DECIMAL(4,2) COMMENT '3寸指宽(cm,选填)',
  103. `acupoint_table_id` BIGINT COMMENT '用户穴位表ID',
  104. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  105. `create_time` DATETIME COMMENT '创建时间',
  106. `update_time` DATETIME COMMENT '更新时间',
  107. PRIMARY KEY (`id`),
  108. KEY `idx_user_id` (`user_id`),
  109. KEY `idx_phone` (`phone`)
  110. ) ENGINE = InnoDB
  111. DEFAULT CHARSET = utf8mb4
  112. COMMENT = '子用户信息表';
  113. -- ----------------------------
  114. -- 用户设备绑定表(子用户与设备绑定)
  115. -- ----------------------------
  116. CREATE TABLE IF NOT EXISTS `user_device` (
  117. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  118. `user_id` BIGINT NOT NULL COMMENT '所属子用户ID(关联 user_profile.id)',
  119. `device_code` VARCHAR(100) NOT NULL COMMENT '设备唯一编码',
  120. `device_name` VARCHAR(100) COMMENT '设备名称',
  121. `device_model` VARCHAR(100) COMMENT '设备型号',
  122. `is_primary` TINYINT NOT NULL DEFAULT 0 COMMENT '是否主设备:1=是,0=否',
  123. `online_status` TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态:1=在线,0=离线',
  124. `status` TINYINT NOT NULL DEFAULT 1 COMMENT '绑定状态:1=已绑定,0=已解绑',
  125. `bind_time` DATETIME COMMENT '绑定时间',
  126. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  127. `create_time` DATETIME COMMENT '创建时间',
  128. `update_time` DATETIME COMMENT '更新时间',
  129. PRIMARY KEY (`id`),
  130. KEY `idx_user_id` (`user_id`),
  131. KEY `idx_device_code` (`device_code`)
  132. ) ENGINE = InnoDB
  133. DEFAULT CHARSET = utf8mb4
  134. COMMENT = '用户设备绑定表(子用户绑定设备)';
  135. -- ----------------------------
  136. -- 艾灸椅设备表
  137. -- ----------------------------
  138. CREATE TABLE IF NOT EXISTS `device` (
  139. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  140. `device_code` VARCHAR(50) NOT NULL COMMENT '设备编号(唯一,如:AJY001)',
  141. `device_name` VARCHAR(100) NOT NULL COMMENT '设备名称(如:艾灸椅-001)',
  142. `device_model` VARCHAR(50) COMMENT '设备型号(AJY-2026 / AJY-2025)',
  143. `serial_no` VARCHAR(50) COMMENT '设备序列号(唯一)',
  144. `firmware_version` VARCHAR(20) COMMENT '固件版本(如:v1.2.0)',
  145. `online_status` TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态:1=在线,0=离线',
  146. `last_online_time` DATETIME COMMENT '最后在线时间',
  147. `remark` VARCHAR(200) COMMENT '备注',
  148. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  149. `create_time` DATETIME COMMENT '创建时间',
  150. `update_time` DATETIME COMMENT '更新时间',
  151. PRIMARY KEY (`id`),
  152. UNIQUE KEY `uk_device_code` (`device_code`),
  153. UNIQUE KEY `uk_serial_no` (`serial_no`)
  154. ) ENGINE = InnoDB
  155. DEFAULT CHARSET = utf8mb4
  156. COMMENT = '艾灸椅设备表';
  157. -- ----------------------------
  158. -- 穴位信息表
  159. -- ----------------------------
  160. CREATE TABLE IF NOT EXISTS `acupoint` (
  161. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  162. `acupoint_code` VARCHAR(100) NOT NULL COMMENT '穴位编码(唯一标识,如 dazhui)',
  163. `excel_serial_no` VARCHAR(20) COMMENT 'Excel原始序号',
  164. `name` VARCHAR(50) NOT NULL COMMENT '穴位名称(如:大椎穴)',
  165. `single_double` VARCHAR(20) COMMENT '单/双穴(Excel原始字段)',
  166. `meridian` VARCHAR(100) COMMENT '所属经络(如:督脉、足太阳膀胱经)',
  167. `body_part` VARCHAR(50) COMMENT '身体部位(Excel原始字段)',
  168. `body_region` VARCHAR(50) COMMENT '部位分区(Excel原始字段)',
  169. `location_description` VARCHAR(500) COMMENT '穴位定位原文(Excel原始字段)',
  170. `relative_position` VARCHAR(200) COMMENT '相对位置原文(Excel原始字段)',
  171. `indications` VARCHAR(1000) COMMENT '主管疾病/主治(Excel原始字段)',
  172. `product_symptoms` VARCHAR(500) COMMENT '产品症状覆盖(Excel原始字段)',
  173. `acupoint_method` VARCHAR(500) COMMENT '取穴说明(Excel原始字段)',
  174. `product_acupoint` TINYINT NOT NULL DEFAULT 0 COMMENT '是否艾灸椅产品穴位:1=是,0=否',
  175. `effects` VARCHAR(500) COMMENT '功效描述(逗号分隔,如:驱寒,祛湿)',
  176. `location_type` TINYINT COMMENT '定位方式:固定为1=相对定位',
  177. `longitude_offset` DECIMAL(6,2) COMMENT '相对大椎穴纵向偏移(寸)',
  178. `lateral_offset` DECIMAL(6,2) COMMENT '相对大椎穴横向偏移(寸,双侧穴位填写)',
  179. `normalized_y` DECIMAL(6,4) COMMENT 'Panjabi归一化Y位置系数(0~1,基于C7-S4脊柱长度)',
  180. `y_reference_term` VARCHAR(100) COMMENT 'Y轴医学定位术语',
  181. `parse_status` VARCHAR(20) COMMENT '定位描述解析状态:已解析/待复核/暂不支持',
  182. `parse_confidence` VARCHAR(10) COMMENT '定位描述解析置信度:高/中/低',
  183. `parse_message` VARCHAR(500) COMMENT '定位描述解析说明',
  184. `side` TINYINT COMMENT '侧别:1=中心,2=左侧,3=右侧,4=双侧',
  185. `finger_width_type` TINYINT COMMENT '指寸类型:1=一寸,2=1.5寸,3=三寸',
  186. `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '适用性别:0=不限,1=仅男,2=仅女',
  187. `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=活跃,0=待完善',
  188. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  189. `create_time` DATETIME COMMENT '创建时间',
  190. `update_time` DATETIME COMMENT '更新时间',
  191. PRIMARY KEY (`id`),
  192. UNIQUE KEY `uk_acupoint_code` (`acupoint_code`)
  193. ) ENGINE = InnoDB
  194. DEFAULT CHARSET = utf8mb4
  195. COMMENT = '穴位信息表';
  196. -- 初始化12个常用穴位(含Panjabi归一化Y位置系数)
  197. 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
  198. ('dazhui', '大椎穴', '督脉', '驱寒,祛湿', 1, 0.00, NULL, 0.0000, 1, 1, 0, 1, 0, NOW(), NOW()),
  199. ('fengmen', '风门穴', '足太阳膀胱经', '祛风,解表', 1, 2.00, 1.50, 0.0780, 4, 1, 0, 1, 0, NOW(), NOW()),
  200. ('feishu', '肺俞穴', '足太阳膀胱经', '宣肺,止咳', 1, 3.00, 1.50, 0.1200, 4, 1, 0, 1, 0, NOW(), NOW()),
  201. ('xinshu', '心俞穴', '足太阳膀胱经', '养心,安神', 1, 5.00, 1.50, 0.2030, 4, 1, 0, 1, 0, NOW(), NOW()),
  202. ('ganshu', '肝俞穴', '足太阳膀胱经', '疏肝,理气', 1, 9.00, 1.50, 0.3610, 4, 1, 0, 1, 0, NOW(), NOW()),
  203. ('pishu', '脾俞穴', '足太阳膀胱经', '健脾,祛湿', 1, 11.00, 1.50, 0.4380, 4, 1, 0, 1, 0, NOW(), NOW()),
  204. ('weishu', '胃俞穴', '足太阳膀胱经', '和胃,消食', 1, 12.00, 1.50, 0.4760, 4, 1, 0, 1, 0, NOW(), NOW()),
  205. ('shenshu', '肾俞穴', '足太阳膀胱经', '补肾,扶阳', 1, 14.00, 1.50, 0.5520, 4, 1, 0, 1, 0, NOW(), NOW()),
  206. ('mingmen', '命门穴', '督脉', '扶阳,固本', 1, 14.00, NULL, 0.5520, 1, 1, 0, 1, 0, NOW(), NOW()),
  207. ('zhongwan', '中脘穴', '任脉', '健脾,和胃', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW()),
  208. ('qihai', '气海穴', '任脉', '扶阳,益气', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW()),
  209. ('guanyuan', '关元穴', '任脉', '温肾,扶阳', 1, NULL, NULL, NULL, 1, NULL, 0, 1, 0, NOW(), NOW());
  210. -- ----------------------------
  211. -- 用户穴位坐标表(根据用户体型数据和穴位信息计算生成)
  212. -- ----------------------------
  213. CREATE TABLE IF NOT EXISTS `user_acupoint` (
  214. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  215. `user_id` BIGINT NOT NULL COMMENT '子用户ID(关联 user_profile.id)',
  216. `acupoint_id` BIGINT NOT NULL COMMENT '穴位ID(关联 acupoint.id)',
  217. `acupoint_name` VARCHAR(50) NOT NULL COMMENT '穴位名称(冗余存储,加速查询)',
  218. `acupoint_side` VARCHAR(10) NOT NULL DEFAULT 'center' COMMENT '侧别:center=中心,left=左侧,right=右侧',
  219. `cun_value` DECIMAL(6,2) COMMENT '相对大椎穴纵向偏移(寸)',
  220. `cun_type` VARCHAR(10) COMMENT '指寸类型:1寸/1.5寸/3寸',
  221. `coordinate_x` DECIMAL(8,2) COMMENT '计算后的X坐标(mm,相对大椎穴,向右为正)',
  222. `coordinate_y` DECIMAL(8,2) COMMENT '计算后的Y坐标(mm,相对大椎穴,向下为正)',
  223. `coordinate_z` DECIMAL(8,2) COMMENT 'Z坐标(mm,体表=0)',
  224. `confidence` VARCHAR(10) COMMENT '置信度:high=高/medium=中/low=低',
  225. `create_time` DATETIME COMMENT '首次生成时间',
  226. `update_time` DATETIME COMMENT '最后更新时间',
  227. PRIMARY KEY (`id`),
  228. KEY `idx_user_id` (`user_id`),
  229. KEY `idx_acupoint_id` (`acupoint_id`)
  230. ) ENGINE = InnoDB
  231. DEFAULT CHARSET = utf8mb4
  232. COMMENT = '用户穴位坐标表(按用户体型计算的个性化穴位坐标)';
  233. -- ----------------------------
  234. -- 艾灸手法表
  235. -- ----------------------------
  236. CREATE TABLE IF NOT EXISTS `moxibustion_technique` (
  237. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  238. `technique_code` VARCHAR(50) NOT NULL COMMENT '手法编码(唯一标识,如 oneclick)',
  239. `name` VARCHAR(50) NOT NULL COMMENT '手法名称(如:一键艾灸)',
  240. `motion_mode` VARCHAR(20) NOT NULL COMMENT '运动模式:default=默认工艺,fixed=固定不动,updown=上下移动,circular=圆周运动,dotted=点状移动',
  241. `temp_min` DECIMAL(4,1) NOT NULL COMMENT '温度范围最小值(°C,35-50)',
  242. `temp_max` DECIMAL(4,1) NOT NULL COMMENT '温度范围最大值(°C,35-50)',
  243. `time_min` INT NOT NULL COMMENT '时间范围最小值(分钟,1-30)',
  244. `time_max` INT NOT NULL COMMENT '时间范围最大值(分钟,1-30)',
  245. `motion_amplitude` DECIMAL(4,1) COMMENT '运动幅度(mm,1-20,运动模式非固定不动时必填)',
  246. `motion_speed` DECIMAL(4,1) COMMENT '运动速度(mm/s,1-10,运动模式非固定不动时必填)',
  247. `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1=启用,0=禁用',
  248. `remark` VARCHAR(200) COMMENT '备注(最长200字符)',
  249. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  250. `create_time` DATETIME COMMENT '创建时间',
  251. `update_time` DATETIME COMMENT '更新时间',
  252. PRIMARY KEY (`id`),
  253. UNIQUE KEY `uk_technique_code` (`technique_code`)
  254. ) ENGINE = InnoDB
  255. DEFAULT CHARSET = utf8mb4
  256. COMMENT = '艾灸手法表';
  257. -- 初始化5个内置手法
  258. 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
  259. ('oneclick', '一键艾灸', 'default', 42.0, 45.0, 30, 30, NULL, NULL, 1, 0, NOW(), NOW()),
  260. ('gentle', '温和灸', 'fixed', 35.0, 50.0, 1, 30, NULL, NULL, 1, 0, NOW(), NOW()),
  261. ('pecking', '雀啄灸', 'updown', 35.0, 50.0, 1, 30, 10.0, 5.0, 1, 0, NOW(), NOW()),
  262. ('circular', '回旋灸', 'circular', 35.0, 50.0, 1, 30, 15.0, 3.0, 1, 0, NOW(), NOW()),
  263. ('sparrow', '麻雀灸', 'dotted', 35.0, 50.0, 1, 30, 8.0, 4.0, 1, 0, NOW(), NOW());
  264. -- ----------------------------
  265. -- 方案表(艾灸方案)
  266. -- ----------------------------
  267. CREATE TABLE IF NOT EXISTS `plan` (
  268. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  269. `plan_code` VARCHAR(50) NOT NULL COMMENT '方案编码(唯一标识,如 S001)',
  270. `name` VARCHAR(100) NOT NULL COMMENT '方案名称(最长100字符)',
  271. `mode_type` TINYINT NOT NULL COMMENT '模式类型:1=一键艾灸,2=专业模式,3=自定义模式,4=延年圣手',
  272. `effect_type` VARCHAR(20) COMMENT '功效类型:驱寒/祛湿/祛风/化瘀/活血/化痰/养颜/扶阳',
  273. `symptoms` VARCHAR(500) COMMENT '适用症状(逗号分隔:怕冷,感冒,咳嗽,...)',
  274. `applicable_gender` TINYINT NOT NULL DEFAULT 0 COMMENT '适用人群:0=不限,1=仅男,2=仅女',
  275. `age_min` INT NOT NULL DEFAULT 1 COMMENT '适用年龄最小值(岁)',
  276. `age_max` INT NOT NULL DEFAULT 120 COMMENT '适用年龄最大值(岁)',
  277. `description` VARCHAR(200) COMMENT '方案描述(最长200字符)',
  278. `author_type` TINYINT NOT NULL DEFAULT 1 COMMENT '创作人类型:1=管理员,2=普通用户,3=专家',
  279. `author_id` BIGINT COMMENT '创作人ID(对应 app_user.id)',
  280. `author_name` VARCHAR(100) COMMENT '创作人名称(冗余存储,加速查询)',
  281. `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0=草稿,1=已发布,2=已下架',
  282. `use_count` INT NOT NULL DEFAULT 0 COMMENT '使用人数',
  283. `avg_rating` DECIMAL(3,1) COMMENT '平均评分',
  284. `last_use_time` DATETIME COMMENT '最后使用时间',
  285. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  286. `create_time` DATETIME COMMENT '创建时间',
  287. `update_time` DATETIME COMMENT '更新时间',
  288. PRIMARY KEY (`id`),
  289. UNIQUE KEY `uk_plan_code` (`plan_code`)
  290. ) ENGINE = InnoDB
  291. DEFAULT CHARSET = utf8mb4
  292. COMMENT = '方案表(艾灸方案)';
  293. -- ----------------------------
  294. -- 方案步骤表
  295. -- ----------------------------
  296. CREATE TABLE IF NOT EXISTS `plan_step` (
  297. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  298. `plan_id` BIGINT NOT NULL COMMENT '方案ID(关联 plan.id)',
  299. `step_order` INT NOT NULL COMMENT '步骤序号(从1开始)',
  300. `acupoint_id` BIGINT COMMENT '穴位ID(关联 acupoint.id)',
  301. `acupoint_name` VARCHAR(50) NOT NULL COMMENT '穴位名称(冗余存储)',
  302. `side` TINYINT NOT NULL COMMENT '侧别:1=中心,2=左侧,3=右侧,4=双侧',
  303. `temperature` DECIMAL(4,1) NOT NULL COMMENT '温度(°C,35-50)',
  304. `duration` INT NOT NULL COMMENT '时间(分钟,1-30)',
  305. `technique_id` BIGINT COMMENT '艾灸手法ID(关联 moxibustion_technique.id)',
  306. `technique_name` VARCHAR(50) COMMENT '艾灸手法名称(冗余存储)',
  307. `remark` VARCHAR(100) COMMENT '备注(最长100字符)',
  308. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  309. `create_time` DATETIME COMMENT '创建时间',
  310. `update_time` DATETIME COMMENT '更新时间',
  311. PRIMARY KEY (`id`),
  312. KEY `idx_plan_id` (`plan_id`)
  313. ) ENGINE = InnoDB
  314. DEFAULT CHARSET = utf8mb4
  315. COMMENT = '方案步骤表';
  316. -- ----------------------------
  317. -- 用户方案表(用户个性化工艺参数方案)
  318. -- ----------------------------
  319. CREATE TABLE IF NOT EXISTS `user_plan` (
  320. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  321. `user_id` BIGINT NOT NULL COMMENT '子用户ID(关联 user_profile.id)',
  322. `plan_id` BIGINT NOT NULL COMMENT '方案ID(关联 plan.id)',
  323. `plan_code` VARCHAR(50) NOT NULL COMMENT '方案编码(冗余存储)',
  324. `plan_name` VARCHAR(100) NOT NULL COMMENT '方案名称(冗余存储)',
  325. `mode_type` TINYINT NOT NULL COMMENT '模式类型:1=一键艾灸,2=专业模式,3=自定义模式,4=延年圣手',
  326. `author_name` VARCHAR(100) COMMENT '创作人名称(冗余存储)',
  327. `step_count` INT NOT NULL DEFAULT 0 COMMENT '步骤数量',
  328. `use_count` INT NOT NULL DEFAULT 0 COMMENT '使用次数',
  329. `steps_json` TEXT COMMENT '个性化工艺步骤(JSON格式,含坐标/温度/时间等)',
  330. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  331. `create_time` DATETIME COMMENT '订阅/生成时间',
  332. `update_time` DATETIME COMMENT '更新时间',
  333. PRIMARY KEY (`id`),
  334. KEY `idx_user_id` (`user_id`),
  335. KEY `idx_plan_id` (`plan_id`)
  336. ) ENGINE = InnoDB
  337. DEFAULT CHARSET = utf8mb4
  338. COMMENT = '用户方案表(用户个性化工艺参数方案)';
  339. -- ----------------------------
  340. -- 初始化系统默认方案:1个一键艾灸 + 8个专业方案
  341. -- ----------------------------
  342. 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`)
  343. SELECT 'S001', '一键艾灸(默认)', 1, NULL, '日常调理,疲劳,手脚冰凉', 0, 1, 120, '系统默认一键艾灸方案,覆盖基础督脉与膀胱经调理。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  344. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S001');
  345. 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`)
  346. SELECT 'S002', '专业驱寒方案', 2, '驱寒', '怕冷,手脚冰凉,畏寒', 0, 1, 120, '针对阳虚畏寒人群的基础驱寒方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  347. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S002');
  348. 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`)
  349. SELECT 'S003', '专业祛湿方案', 2, '祛湿', '困倦,湿重,食欲差', 0, 1, 120, '围绕脾胃与膀胱经调理的祛湿方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  350. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S003');
  351. 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`)
  352. SELECT 'S004', '专业祛风方案', 2, '祛风', '感冒,怕风,鼻塞', 0, 1, 120, '针对风寒风邪引发不适的基础调理方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  353. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S004');
  354. 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`)
  355. SELECT 'S005', '专业化瘀方案', 2, '化瘀', '僵硬,酸痛,局部不适', 0, 1, 120, '用于改善局部瘀滞与经络不畅。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  356. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S005');
  357. 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`)
  358. SELECT 'S006', '专业活血方案', 2, '活血', '疲劳,循环差,肩背紧张', 0, 1, 120, '帮助提升背部循环与整体活力。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  359. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S006');
  360. 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`)
  361. SELECT 'S007', '专业化痰方案', 2, '化痰', '咳嗽,痰多,胸闷', 0, 1, 120, '偏肺俞与中焦调理的化痰方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  362. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S007');
  363. 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`)
  364. SELECT 'S008', '专业养颜方案', 2, '养颜', '气色差,疲劳,睡眠一般', 0, 1, 120, '通过脾胃与气海关元综合调理气色。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  365. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S008');
  366. 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`)
  367. SELECT 'S009', '专业扶阳方案', 2, '扶阳', '乏力,腰酸,手脚冰凉', 0, 1, 120, '以命门肾俞关元为主的扶阳固本方案。', 1, 'ADMIN', 1, 0, 0, NOW(), NOW()
  368. WHERE NOT EXISTS (SELECT 1 FROM `plan` WHERE `plan_code` = 'S009');
  369. 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`)
  370. SELECT p.id, 1, 1, '大椎穴', 1, 42.0, 6, 2, '温和灸', '默认基准穴位', 0, NOW(), NOW()
  371. FROM `plan` p
  372. WHERE p.plan_code = 'S001'
  373. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  374. 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`)
  375. SELECT p.id, 2, 2, '风门穴', 4, 43.0, 6, 2, '温和灸', '双侧温养', 0, NOW(), NOW()
  376. FROM `plan` p
  377. WHERE p.plan_code = 'S001'
  378. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  379. 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`)
  380. SELECT p.id, 3, 3, '肺俞穴', 4, 43.0, 6, 2, '温和灸', '提升背部暖感', 0, NOW(), NOW()
  381. FROM `plan` p
  382. WHERE p.plan_code = 'S001'
  383. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  384. 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`)
  385. SELECT p.id, 4, 8, '肾俞穴', 4, 44.0, 6, 2, '温和灸', '补益下焦', 0, NOW(), NOW()
  386. FROM `plan` p
  387. WHERE p.plan_code = 'S001'
  388. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  389. 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`)
  390. SELECT p.id, 5, 9, '命门穴', 1, 45.0, 6, 2, '温和灸', '固本收尾', 0, NOW(), NOW()
  391. FROM `plan` p
  392. WHERE p.plan_code = 'S001'
  393. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 5);
  394. 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`)
  395. SELECT p.id, 1, 1, '大椎穴', 1, 44.0, 8, 3, '雀啄灸', '驱寒起始', 0, NOW(), NOW()
  396. FROM `plan` p
  397. WHERE p.plan_code = 'S002'
  398. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  399. 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`)
  400. SELECT p.id, 2, 2, '风门穴', 4, 44.0, 8, 3, '雀啄灸', '祛寒散表', 0, NOW(), NOW()
  401. FROM `plan` p
  402. WHERE p.plan_code = 'S002'
  403. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  404. 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`)
  405. SELECT p.id, 3, 8, '肾俞穴', 4, 45.0, 8, 2, '温和灸', '温补肾阳', 0, NOW(), NOW()
  406. FROM `plan` p
  407. WHERE p.plan_code = 'S002'
  408. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  409. 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`)
  410. SELECT p.id, 4, 9, '命门穴', 1, 45.0, 8, 2, '温和灸', '扶阳收尾', 0, NOW(), NOW()
  411. FROM `plan` p
  412. WHERE p.plan_code = 'S002'
  413. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  414. 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`)
  415. SELECT p.id, 1, 6, '脾俞穴', 4, 43.0, 8, 2, '温和灸', '健脾化湿', 0, NOW(), NOW()
  416. FROM `plan` p
  417. WHERE p.plan_code = 'S003'
  418. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  419. 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`)
  420. SELECT p.id, 2, 7, '胃俞穴', 4, 43.0, 8, 2, '温和灸', '和胃助运化', 0, NOW(), NOW()
  421. FROM `plan` p
  422. WHERE p.plan_code = 'S003'
  423. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  424. 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`)
  425. SELECT p.id, 3, 10, '中脘穴', 1, 42.0, 8, 4, '回旋灸', '中焦祛湿', 0, NOW(), NOW()
  426. FROM `plan` p
  427. WHERE p.plan_code = 'S003'
  428. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  429. 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`)
  430. SELECT p.id, 4, 11, '气海穴', 1, 42.0, 6, 2, '温和灸', '益气固本', 0, NOW(), NOW()
  431. FROM `plan` p
  432. WHERE p.plan_code = 'S003'
  433. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  434. 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`)
  435. SELECT p.id, 1, 1, '大椎穴', 1, 42.0, 7, 2, '温和灸', '祛风首穴', 0, NOW(), NOW()
  436. FROM `plan` p
  437. WHERE p.plan_code = 'S004'
  438. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  439. 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`)
  440. SELECT p.id, 2, 2, '风门穴', 4, 43.0, 7, 3, '雀啄灸', '散风解表', 0, NOW(), NOW()
  441. FROM `plan` p
  442. WHERE p.plan_code = 'S004'
  443. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  444. 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`)
  445. SELECT p.id, 3, 3, '肺俞穴', 4, 42.0, 7, 2, '温和灸', '协同宣肺', 0, NOW(), NOW()
  446. FROM `plan` p
  447. WHERE p.plan_code = 'S004'
  448. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  449. 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`)
  450. SELECT p.id, 4, 4, '心俞穴', 4, 42.0, 6, 2, '温和灸', '安神扶正', 0, NOW(), NOW()
  451. FROM `plan` p
  452. WHERE p.plan_code = 'S004'
  453. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  454. 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`)
  455. SELECT p.id, 1, 4, '心俞穴', 4, 43.0, 8, 4, '回旋灸', '活络起始', 0, NOW(), NOW()
  456. FROM `plan` p
  457. WHERE p.plan_code = 'S005'
  458. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  459. 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`)
  460. SELECT p.id, 2, 5, '肝俞穴', 4, 44.0, 8, 4, '回旋灸', '疏肝化瘀', 0, NOW(), NOW()
  461. FROM `plan` p
  462. WHERE p.plan_code = 'S005'
  463. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  464. 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`)
  465. SELECT p.id, 3, 6, '脾俞穴', 4, 43.0, 6, 2, '温和灸', '调和中焦', 0, NOW(), NOW()
  466. FROM `plan` p
  467. WHERE p.plan_code = 'S005'
  468. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  469. 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`)
  470. SELECT p.id, 4, 9, '命门穴', 1, 44.0, 6, 2, '温和灸', '温阳通络', 0, NOW(), NOW()
  471. FROM `plan` p
  472. WHERE p.plan_code = 'S005'
  473. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  474. 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`)
  475. SELECT p.id, 1, 3, '肺俞穴', 4, 42.0, 7, 4, '回旋灸', '活血舒展', 0, NOW(), NOW()
  476. FROM `plan` p
  477. WHERE p.plan_code = 'S006'
  478. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  479. 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`)
  480. SELECT p.id, 2, 4, '心俞穴', 4, 43.0, 7, 4, '回旋灸', '促进循环', 0, NOW(), NOW()
  481. FROM `plan` p
  482. WHERE p.plan_code = 'S006'
  483. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  484. 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`)
  485. SELECT p.id, 3, 5, '肝俞穴', 4, 43.0, 7, 4, '回旋灸', '疏通经络', 0, NOW(), NOW()
  486. FROM `plan` p
  487. WHERE p.plan_code = 'S006'
  488. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  489. 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`)
  490. SELECT p.id, 4, 11, '气海穴', 1, 42.0, 6, 2, '温和灸', '补气收束', 0, NOW(), NOW()
  491. FROM `plan` p
  492. WHERE p.plan_code = 'S006'
  493. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  494. 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`)
  495. SELECT p.id, 1, 3, '肺俞穴', 4, 42.0, 8, 2, '温和灸', '宣肺止咳', 0, NOW(), NOW()
  496. FROM `plan` p
  497. WHERE p.plan_code = 'S007'
  498. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  499. 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`)
  500. SELECT p.id, 2, 2, '风门穴', 4, 42.0, 8, 2, '温和灸', '协同宣散', 0, NOW(), NOW()
  501. FROM `plan` p
  502. WHERE p.plan_code = 'S007'
  503. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  504. 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`)
  505. SELECT p.id, 3, 10, '中脘穴', 1, 41.0, 7, 4, '回旋灸', '中焦运化', 0, NOW(), NOW()
  506. FROM `plan` p
  507. WHERE p.plan_code = 'S007'
  508. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  509. 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`)
  510. SELECT p.id, 4, 11, '气海穴', 1, 41.0, 7, 2, '温和灸', '益气化痰', 0, NOW(), NOW()
  511. FROM `plan` p
  512. WHERE p.plan_code = 'S007'
  513. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  514. 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`)
  515. SELECT p.id, 1, 6, '脾俞穴', 4, 42.0, 8, 2, '温和灸', '健脾养颜', 0, NOW(), NOW()
  516. FROM `plan` p
  517. WHERE p.plan_code = 'S008'
  518. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  519. 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`)
  520. SELECT p.id, 2, 7, '胃俞穴', 4, 42.0, 8, 2, '温和灸', '和胃生化', 0, NOW(), NOW()
  521. FROM `plan` p
  522. WHERE p.plan_code = 'S008'
  523. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  524. 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`)
  525. SELECT p.id, 3, 11, '气海穴', 1, 42.0, 7, 4, '回旋灸', '补气养神', 0, NOW(), NOW()
  526. FROM `plan` p
  527. WHERE p.plan_code = 'S008'
  528. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  529. 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`)
  530. SELECT p.id, 4, 12, '关元穴', 1, 42.0, 7, 2, '温和灸', '养元收尾', 0, NOW(), NOW()
  531. FROM `plan` p
  532. WHERE p.plan_code = 'S008'
  533. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  534. 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`)
  535. SELECT p.id, 1, 8, '肾俞穴', 4, 44.0, 8, 2, '温和灸', '补肾扶阳', 0, NOW(), NOW()
  536. FROM `plan` p
  537. WHERE p.plan_code = 'S009'
  538. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 1);
  539. 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`)
  540. SELECT p.id, 2, 9, '命门穴', 1, 45.0, 8, 2, '温和灸', '培补命门火', 0, NOW(), NOW()
  541. FROM `plan` p
  542. WHERE p.plan_code = 'S009'
  543. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 2);
  544. 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`)
  545. SELECT p.id, 3, 11, '气海穴', 1, 44.0, 7, 2, '温和灸', '益气助阳', 0, NOW(), NOW()
  546. FROM `plan` p
  547. WHERE p.plan_code = 'S009'
  548. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 3);
  549. 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`)
  550. SELECT p.id, 4, 12, '关元穴', 1, 44.0, 7, 2, '温和灸', '固本培元', 0, NOW(), NOW()
  551. FROM `plan` p
  552. WHERE p.plan_code = 'S009'
  553. AND NOT EXISTS (SELECT 1 FROM `plan_step` ps WHERE ps.plan_id = p.id AND ps.step_order = 4);
  554. -- ----------------------------
  555. -- 管理员权限配置表
  556. -- ----------------------------
  557. CREATE TABLE IF NOT EXISTS `admin_permission` (
  558. `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  559. `admin_id` BIGINT NOT NULL COMMENT '管理员ID(关联 app_user.id)',
  560. `route_paths` TEXT COMMENT '可访问的路由路径(JSON数组,如 ["/dashboard","/device"])',
  561. `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0=未删除,1=已删除',
  562. `create_time` DATETIME COMMENT '创建时间',
  563. `update_time` DATETIME COMMENT '更新时间',
  564. PRIMARY KEY (`id`),
  565. UNIQUE KEY `uk_admin_id` (`admin_id`)
  566. ) ENGINE = InnoDB
  567. DEFAULT CHARSET = utf8mb4
  568. COMMENT = '管理员权限配置表';
  569. -- app_user 表追加管理员相关字段(首次执行时添加,若字段已存在请注释掉此段)
  570. ALTER TABLE `app_user`
  571. ADD COLUMN `admin_role` TINYINT DEFAULT NULL COMMENT '管理员角色:1=超级管理员,2=运营人员,3=只读用户(仅 userType=3 时有效)',
  572. ADD COLUMN `last_login_time` DATETIME DEFAULT NULL COMMENT '最后登录时间';
  573. -- 更新初始管理员为超级管理员角色
  574. UPDATE `app_user` SET `admin_role` = 1 WHERE `username` = 'admin' AND `admin_role` IS NULL;
  575. SET FOREIGN_KEY_CHECKS = 1;