zrd-nb666/campus_health_app/backend/database/schema.sql

-- ========================================
-- 数智健调系统 - 体重管理模块数据库架构
-- ========================================
-- 版本: 1.0
-- 创建日期: 2025-10-20
-- 说明: 体重管理核心表结构定义
-- ========================================

-- 设置字符集
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- ========================================
-- 表1: users (用户表)
-- ========================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(36) NOT NULL COMMENT '用户ID（UUID）',
  `phone` VARCHAR(20) NOT NULL COMMENT '手机号',
  `nickname` VARCHAR(50) NOT NULL COMMENT '昵称',
  `school_id` VARCHAR(36) DEFAULT NULL COMMENT '学校ID',
  `grade` VARCHAR(20) DEFAULT NULL COMMENT '年级',
  `is_under_14` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否未满14岁',
  `parent_phone` VARCHAR(20) DEFAULT NULL COMMENT '家长手机号',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '软删除时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone` (`phone`),
  KEY `idx_school_id` (`school_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ========================================
-- 表2: weight_records (体重记录表) ⭐
-- ========================================
CREATE TABLE IF NOT EXISTS `weight_records` (
  `id` VARCHAR(36) NOT NULL COMMENT '记录ID（UUID）',
  `user_id` VARCHAR(36) NOT NULL COMMENT '用户ID',
  `record_date` DATE NOT NULL COMMENT '记录日期',
  `measurement_time` TIME DEFAULT NULL COMMENT '测量时间',
  `weight` DECIMAL(5,2) NOT NULL COMMENT '体重(kg)，范围30.00-200.00',
  `body_fat` DECIMAL(4,2) NOT NULL COMMENT '体脂率(%)，范围5.00-60.00',
  `muscle_mass` DECIMAL(5,2) NOT NULL COMMENT '肌肉含量(kg)，范围10.00-100.00',
  `measurement_condition` ENUM('fasting', 'after_meal') DEFAULT NULL COMMENT '测量条件：空腹/餐后',
  `notes` TEXT DEFAULT NULL COMMENT '备注',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '软删除时间',
  
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `record_date` DESC),
  KEY `idx_user_created` (`user_id`, `created_at` DESC),
  KEY `idx_measurement_condition` (`measurement_condition`),
  KEY `idx_deleted_at` (`deleted_at`),
  
  CONSTRAINT `fk_weight_records_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_weight` CHECK (`weight` >= 30.00 AND `weight` <= 200.00),
  CONSTRAINT `chk_body_fat` CHECK (`body_fat` >= 5.00 AND `body_fat` <= 60.00),
  CONSTRAINT `chk_muscle_mass` CHECK (`muscle_mass` >= 10.00 AND `muscle_mass` <= 100.00)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='体重记录表';

-- ========================================
-- 表3: weight_goals (体重目标表) ⭐
-- ========================================
CREATE TABLE IF NOT EXISTS `weight_goals` (
  `id` VARCHAR(36) NOT NULL COMMENT '目标ID（UUID）',
  `user_id` VARCHAR(36) NOT NULL COMMENT '用户ID',
  `target_weight` DECIMAL(5,2) NOT NULL COMMENT '目标体重(kg)',
  `target_body_fat` DECIMAL(4,2) DEFAULT NULL COMMENT '目标体脂率(%)',
  `target_date` DATE NOT NULL COMMENT '目标日期',
  `start_weight` DECIMAL(5,2) NOT NULL COMMENT '起始体重(kg)',
  `start_body_fat` DECIMAL(4,2) DEFAULT NULL COMMENT '起始体脂率(%)',
  `start_date` DATE NOT NULL COMMENT '开始日期',
  `weekly_target` DECIMAL(4,2) DEFAULT NULL COMMENT '每周目标减重量(kg)',
  `status` ENUM('active', 'completed', 'abandoned') NOT NULL DEFAULT 'active' COMMENT '状态',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT '完成时间',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '软删除时间',
  
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_user_created` (`user_id`, `created_at` DESC),
  KEY `idx_deleted_at` (`deleted_at`),
  
  CONSTRAINT `fk_weight_goals_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_target_date` CHECK (`target_date` > `start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='体重目标表';

-- ========================================
-- 表4: tags (标签表)
-- ========================================
CREATE TABLE IF NOT EXISTS `tags` (
  `id` VARCHAR(36) NOT NULL COMMENT '标签ID（UUID）',
  `user_id` VARCHAR(36) DEFAULT NULL COMMENT '用户ID（NULL表示系统标签）',
  `tag_name` VARCHAR(50) NOT NULL COMMENT '标签名称',
  `tag_type` ENUM('system', 'custom') NOT NULL DEFAULT 'custom' COMMENT '类型：系统/自定义',
  `color` VARCHAR(7) DEFAULT NULL COMMENT '颜色代码（HEX）',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_tag` (`user_id`, `tag_name`),
  KEY `idx_tag_type` (`tag_type`),
  
  CONSTRAINT `fk_tags_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='标签表';

-- ========================================
-- 表5: weight_record_tags (体重记录-标签关联表)
-- ========================================
CREATE TABLE IF NOT EXISTS `weight_record_tags` (
  `id` VARCHAR(36) NOT NULL COMMENT '关联ID（UUID）',
  `weight_record_id` VARCHAR(36) NOT NULL COMMENT '体重记录ID',
  `tag_id` VARCHAR(36) NOT NULL COMMENT '标签ID',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_record_tag` (`weight_record_id`, `tag_id`),
  KEY `idx_tag_id` (`tag_id`),
  
  CONSTRAINT `fk_wrt_record` FOREIGN KEY (`weight_record_id`) REFERENCES `weight_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wrt_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='体重记录-标签关联表';

-- ========================================
-- 表6: anomaly_logs (异常日志表) - 扩展表
-- ========================================
CREATE TABLE IF NOT EXISTS `anomaly_logs` (
  `id` VARCHAR(36) NOT NULL COMMENT '日志ID（UUID）',
  `user_id` VARCHAR(36) NOT NULL COMMENT '用户ID',
  `anomaly_type` ENUM('rapid_change', 'body_fat_anomaly', 'missing_data', 'extreme_value') NOT NULL COMMENT '异常类型',
  `severity` ENUM('info', 'warning', 'danger') NOT NULL COMMENT '严重程度',
  `message` TEXT NOT NULL COMMENT '提示信息',
  `detected_date` DATE NOT NULL COMMENT '检测日期',
  `related_record_ids` JSON DEFAULT NULL COMMENT '相关记录ID数组',
  `is_read` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已读',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `detected_date` DESC),
  KEY `idx_is_read` (`is_read`),
  
  CONSTRAINT `fk_anomaly_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常日志表';

-- ========================================
-- 插入系统预设标签
-- ========================================
INSERT INTO `tags` (`id`, `user_id`, `tag_name`, `tag_type`, `color`) VALUES
(UUID(), NULL, '开始运动', 'system', '#3b82f6'),
(UUID(), NULL, '目标调整', 'system', '#f59e0b'),
(UUID(), NULL, '饮食改变', 'system', '#10b981'),
(UUID(), NULL, '生病', 'system', '#ef4444'),
(UUID(), NULL, '假期', 'system', '#8b5cf6'),
(UUID(), NULL, '压力期', 'system', '#f97316')
ON DUPLICATE KEY UPDATE `tag_name` = VALUES(`tag_name`);

-- ========================================
-- 创建视图：用户体重统计
-- ========================================
CREATE OR REPLACE VIEW `v_user_weight_stats` AS
SELECT 
  u.id AS user_id,
  u.nickname,
  COUNT(wr.id) AS total_records,
  MIN(wr.record_date) AS first_record_date,
  MAX(wr.record_date) AS last_record_date,
  (SELECT weight FROM weight_records WHERE user_id = u.id AND deleted_at IS NULL ORDER BY record_date DESC LIMIT 1) AS current_weight,
  (SELECT weight FROM weight_records WHERE user_id = u.id AND deleted_at IS NULL ORDER BY record_date ASC LIMIT 1) AS initial_weight,
  (SELECT body_fat FROM weight_records WHERE user_id = u.id AND deleted_at IS NULL ORDER BY record_date DESC LIMIT 1) AS current_body_fat
FROM users u
LEFT JOIN weight_records wr ON u.id = wr.user_id AND wr.deleted_at IS NULL
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.nickname;

-- ========================================
-- 表结构创建完成
-- ========================================