31 行
2.4 KiB
SQL
31 行
2.4 KiB
SQL
-- 迁移：扫描报告存储 + 诊断配置种子（对应小程序 commit 6b91ebe 的诊断链路上线）
|
||
-- 幂等：可重复执行。只做两件事，不碰其他表/账号。
|
||
-- 执行方式见 WorkLog: products/jw-beauty/2026-07-28-生产DB迁移指引.md
|
||
|
||
-- 1) 扫描报告表（Phase 1 已定义，生产一直未建）
|
||
CREATE TABLE IF NOT EXISTS scan_reports (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
device_id VARCHAR(32) NULL,
|
||
scanned_at DATETIME NULL,
|
||
regions JSON NULL COMMENT 'array of per-region analysis',
|
||
overall JSON NULL COMMENT 'array of overall tendencies',
|
||
recommend_mask INT NOT NULL DEFAULT 0 COMMENT 'bitmask of regions recommended for care',
|
||
recommend_plan JSON NULL COMMENT 'array of {region, mask, wave, wavelength, label_key, level, brightness}',
|
||
raw_pd JSON NULL COMMENT 'raw PD samples for the calibration dataset (may be partial)',
|
||
calibrated TINYINT NOT NULL DEFAULT 0 COMMENT 'whether thresholds were calibrated',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_scan_reports_user_created (user_id, created_at),
|
||
KEY idx_scan_reports_device_created (device_id, created_at),
|
||
CONSTRAINT fk_scan_reports_user FOREIGN KEY (user_id) REFERENCES users (user_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 2) 诊断配置种子（2026-07-28 新结构：极性/阈值/亮度/PD映射全部热调）
|
||
-- pd_region_map 已由固件确认(2026-07-28)：左=PD1 中上=PD2 中=PD3/6/7 中下=PD4 右=PD5（0基下标）
|
||
-- ON DUPLICATE KEY UPDATE 会覆盖已有的 diagnosis_config——当前生产从未配置过该键，
|
||
-- 若执行前发现已有人工调过的值（见指引文档的核查步骤），先备份再执行。
|
||
INSERT INTO system_settings (setting_key, setting_value) VALUES
|
||
('diagnosis_config', '{"calibrated": false, "note": "阈值为待标定初始值(文档建议值)；polarity=inverted 与 pd_region_map(左PD1/中上PD2/中PD3,6,7/中下PD4/右PD5) 均已由固件确认(2026-07-28)", "polarity": "inverted", "th": 0.10, "severity": {"low": 0.05, "mid": 0.15, "high": 0.30}, "brightness": 204, "pd_region_map": {"left": [0], "top": [1], "middle": [2, 5, 6], "bottom": [3], "right": [4]}, "problem_wavelength": {"aging": 2, "acne": 3, "pigment": 4, "deep": 1}}')
|
||
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
|