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

-- 健康日记：用药 + 症状 + 主观感受
CREATE TABLE IF NOT EXISTS `health_journal` (
  `id` VARCHAR(36) NOT NULL,
  `user_id` VARCHAR(36) NOT NULL DEFAULT 'synthetic-user-001',
  `record_date` DATE NOT NULL,
  `medicine_type` ENUM('western','chinese_patent','chinese_herb','none') NOT NULL DEFAULT 'none',
  `medicine_name` VARCHAR(200) DEFAULT NULL,
  `dosage` VARCHAR(100) DEFAULT NULL,
  `effect` ENUM('good','normal','none','bad') DEFAULT NULL,
  `gut_score` TINYINT DEFAULT NULL COMMENT '肠胃 1-5',
  `energy_score` TINYINT DEFAULT NULL COMMENT '精力 1-5',
  `symptoms` VARCHAR(255) DEFAULT NULL COMMENT '逗号分隔的症状标签',
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;