ajy/code/backend/src/main/resources/sql/migration_algorithm_v3.sql

-- ========== 算法3.0 数据库迁移脚本 ==========
-- 请在已有数据库上执行此脚本，新建库请直接使用更新后的 init.sql

SET NAMES utf8mb4;

-- 1. user_profile 表新增字段
ALTER TABLE `user_profile`
    ADD COLUMN `spine_length` DECIMAL(5,1) DEFAULT NULL COMMENT '脊柱长度C7-S4（cm，范围30-60）' AFTER `body_height`,
    ADD COLUMN `height`       DECIMAL(5,1) DEFAULT NULL COMMENT '身高（cm）' AFTER `spine_length`,
    ADD COLUMN `weight`       DECIMAL(5,1) DEFAULT NULL COMMENT '体重（kg）' AFTER `height`;

-- 2. acupoint 表新增字段
ALTER TABLE `acupoint`
    ADD COLUMN `normalized_y` DECIMAL(6,4) DEFAULT NULL COMMENT 'Panjabi归一化Y位置系数（0~1，基于C7-S4脊柱长度）' AFTER `lateral_offset`;

-- 3. user_acupoint 表新增字段
ALTER TABLE `user_acupoint`
    ADD COLUMN `coordinate_z` DECIMAL(8,2) DEFAULT NULL COMMENT 'Z坐标（mm，体表=0）' AFTER `coordinate_y`,
    ADD COLUMN `confidence`   VARCHAR(10)  DEFAULT NULL COMMENT '置信度：high=高/medium=中/low=低' AFTER `coordinate_z`;

-- 4. 更新现有穴位的 Panjabi 归一化 Y 位置系数
UPDATE `acupoint` SET `normalized_y` = 0.0000 WHERE `acupoint_code` = 'dazhui';
UPDATE `acupoint` SET `normalized_y` = 0.0780 WHERE `acupoint_code` = 'fengmen';
UPDATE `acupoint` SET `normalized_y` = 0.1200 WHERE `acupoint_code` = 'feishu';
UPDATE `acupoint` SET `normalized_y` = 0.2030 WHERE `acupoint_code` = 'xinshu';
UPDATE `acupoint` SET `normalized_y` = 0.3610 WHERE `acupoint_code` = 'ganshu';
UPDATE `acupoint` SET `normalized_y` = 0.4380 WHERE `acupoint_code` = 'pishu';
UPDATE `acupoint` SET `normalized_y` = 0.4760 WHERE `acupoint_code` = 'weishu';
UPDATE `acupoint` SET `normalized_y` = 0.5520 WHERE `acupoint_code` = 'shenshu';
UPDATE `acupoint` SET `normalized_y` = 0.5520 WHERE `acupoint_code` = 'mingmen';
-- zhongwan / qihai / guanyuan 为绝对定位，normalized_y 保持 NULL