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

-- ========================================
-- 测试数据种子脚本
-- ========================================
-- 用途：插入测试数据用于开发和演示
-- 使用方法：mysql -u root -p campus_health < seed.sql
-- ========================================

USE `campus_health`;

-- ========================================
-- 1. 插入测试用户
-- ========================================
INSERT INTO `users` (`id`, `phone`, `nickname`, `school_id`, `grade`, `is_under_14`, `parent_phone`) VALUES
('test-user-001', '13800138001', '张三', 'school-001', '大一', FALSE, NULL),
('test-user-002', '13800138002', '李四', 'school-001', '大二', FALSE, NULL),
('test-user-003', '13800138003', '王五', 'school-001', '高一', TRUE, '13900139001')
ON DUPLICATE KEY UPDATE nickname = VALUES(nickname);

-- ========================================
-- 2. 插入体重记录（90天数据）
-- ========================================
-- 为用户 test-user-001 生成90天的体重数据
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS generate_weight_records()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE test_date DATE;
  DECLARE base_weight DECIMAL(5,2) DEFAULT 70.00;
  DECLARE weight_var DECIMAL(5,2);
  DECLARE body_fat_var DECIMAL(4,2);
  
  -- 删除已存在的测试数据
  DELETE FROM weight_records WHERE user_id = 'test-user-001';
  
  -- 生成90天数据
  WHILE i < 90 DO
    SET test_date = DATE_SUB(CURDATE(), INTERVAL (90 - i) DAY);
    SET weight_var = base_weight - (i * 0.05) + (RAND() * 0.4 - 0.2);
    SET body_fat_var = 23.0 - (i * 0.03) + (RAND() * 0.3);
    
    INSERT INTO weight_records (
      id, 
      user_id, 
      record_date, 
      measurement_time,
      weight, 
      body_fat, 
      muscle_mass,
      measurement_condition
    ) VALUES (
      UUID(),
      'test-user-001',
      test_date,
      '08:00:00',
      ROUND(weight_var, 2),
      ROUND(body_fat_var, 2),
      ROUND(28.0 + (RAND() * 2), 2),
      IF(i % 2 = 0, 'fasting', 'after_meal')
    );
    
    SET i = i + 1;
  END WHILE;
  
  SELECT CONCAT('成功生成 ', i, ' 条体重记录') AS result;
END //

DELIMITER ;

-- 执行存储过程
CALL generate_weight_records();

-- 删除存储过程
DROP PROCEDURE IF EXISTS generate_weight_records;

-- ========================================
-- 3. 插入体重目标
-- ========================================
INSERT INTO `weight_goals` (
  `id`,
  `user_id`,
  `target_weight`,
  `target_body_fat`,
  `target_date`,
  `start_weight`,
  `start_body_fat`,
  `start_date`,
  `weekly_target`,
  `status`
) VALUES (
  UUID(),
  'test-user-001',
  65.00,
  18.00,
  DATE_ADD(CURDATE(), INTERVAL 60 DAY),
  70.00,
  23.00,
  DATE_SUB(CURDATE(), INTERVAL 90 DAY),
  0.50,
  'active'
)
ON DUPLICATE KEY UPDATE target_weight = VALUES(target_weight);

-- ========================================
-- 4. 为部分记录添加标签
-- ========================================
-- 获取系统标签ID并为记录添加标签
INSERT INTO weight_record_tags (id, weight_record_id, tag_id)
SELECT 
  UUID(),
  wr.id,
  (SELECT id FROM tags WHERE tag_name = '开始运动' AND user_id IS NULL LIMIT 1)
FROM weight_records wr
WHERE wr.user_id = 'test-user-001'
  AND wr.record_date = DATE_SUB(CURDATE(), INTERVAL 76 DAY)
LIMIT 1
ON DUPLICATE KEY UPDATE weight_record_id = VALUES(weight_record_id);

INSERT INTO weight_record_tags (id, weight_record_id, tag_id)
SELECT 
  UUID(),
  wr.id,
  (SELECT id FROM tags WHERE tag_name = '目标调整' AND user_id IS NULL LIMIT 1)
FROM weight_records wr
WHERE wr.user_id = 'test-user-001'
  AND wr.record_date = DATE_SUB(CURDATE(), INTERVAL 48 DAY)
LIMIT 1
ON DUPLICATE KEY UPDATE weight_record_id = VALUES(weight_record_id);

-- ========================================
-- 5. 插入异常日志示例
-- ========================================
INSERT INTO `anomaly_logs` (
  `id`,
  `user_id`,
  `anomaly_type`,
  `severity`,
  `message`,
  `detected_date`,
  `is_read`
) VALUES (
  UUID(),
  'test-user-001',
  'rapid_change',
  'warning',
  '近7天体重变化过快（-2.5kg/周），建议放慢减重速度',
  DATE_SUB(CURDATE(), INTERVAL 3 DAY),
  FALSE
)
ON DUPLICATE KEY UPDATE message = VALUES(message);

-- ========================================
-- 6. 显示测试数据统计
-- ========================================
SELECT '=== 测试数据插入完成 ===' AS status;

SELECT 
  '用户数量' AS metric,
  COUNT(*) AS count
FROM users
WHERE id LIKE 'test-user-%'
UNION ALL
SELECT 
  '体重记录数量' AS metric,
  COUNT(*) AS count
FROM weight_records
WHERE user_id = 'test-user-001'
UNION ALL
SELECT 
  '目标数量' AS metric,
  COUNT(*) AS count
FROM weight_goals
WHERE user_id = 'test-user-001'
UNION ALL
SELECT 
  '标签数量' AS metric,
  COUNT(*) AS count
FROM tags
UNION ALL
SELECT 
  '异常日志数量' AS metric,
  COUNT(*) AS count
FROM anomaly_logs
WHERE user_id = 'test-user-001';

-- 显示最近10条体重记录
SELECT 
  '=== 最近10条体重记录 ===' AS info;

SELECT 
  record_date,
  weight,
  body_fat,
  muscle_mass,
  measurement_condition
FROM weight_records
WHERE user_id = 'test-user-001'
ORDER BY record_date DESC
LIMIT 10;