数据设计 ✅

本文档定义系统的数据模型设计，包括领域模型（Prisma Schema）和数据库表结构设计。

---

模型设计

Prisma Schema 定义

命名规范：

• Model 名称：PascalCase (如 User, FamilyMember)
• Enum 名称：PascalCase (如 FamilyMemberRole)
• 字段名称：camelCase (如 userId, createdAt)
• 数据库表名/列名：snake_case (通过 @map 映射)

// ============================================
// Reflekt Health - Prisma Schema
// 数据库: PostgreSQL 17
// ============================================

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// --------------------------------------------
// 用户与认证
// --------------------------------------------

/// 用户账户表
model User {
  id                String    @id @default(uuid()) @map("id")
  phone             String    @unique @map("phone")
  passwordHash      String    @map("password_hash")
  email             String?   @unique @map("email")
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")
  deletedAt         DateTime? @map("deleted_at")

  // 关联关系
  familyMembers     FamilyMember[] @relation("UserFamilyMembers")
  invitations       Invitation[]   @relation("UserInvitations")
  notificationPref  NotificationPreference?
  auditLogs         AuditLog[]

  @@index([phone], name: "idx_user_phone")
  @@index([deletedAt], name: "idx_user_deleted_at")
  @@map("user")
}

// --------------------------------------------
// 家庭与成员
// --------------------------------------------

/// 家庭档案表
model Family {
  id                String    @id @default(uuid()) @map("id")
  name              String?   @map("name")

  // 老人信息
  elderName         String?   @map("elder_name")
  elderAge          Int?      @map("elder_age")
  elderGender       String?   @map("elder_gender")

  // 健康信息（敏感数据，需加密存储）
  elderDiseaseHistory    String?   @db.Text @map("elder_disease_history")
  elderMedicationHistory String?   @db.Text @map("elder_medication_history")

  // 生活习惯
  wakeUpTime        String?   @map("wake_up_time")
  sleepTime         String?   @map("sleep_time")
  silenceStartTime  String?   @map("silence_start_time")
  silenceEndTime    String?   @map("silence_end_time")

  // 地址与时区
  address           String?   @map("address")
  timezone          String    @default("America/New_York") @map("timezone")

  // 隐私同意
  privacyConsent    Boolean   @default(false) @map("privacy_consent")
  privacyConsentAt   DateTime? @map("privacy_consent_at")
  privacyRevokedAt  DateTime? @map("privacy_revoked_at")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  // 关联关系
  members              FamilyMember[]
  devices              DeviceBinding[]
  invitations          Invitation[]
  alerts               Alert[]
  reminders            Reminder[]
  healthData           HealthData[]
  alertNotifications   AlertNotification[]

  @@index([elderName], name: "idx_family_elder_name")
  @@index([createdAt], name: "idx_family_created_at")
  @@map("family")
}

/// 家庭成员表（统一管理家属和协作者）
model FamilyMember {
  id                String    @id @default(uuid()) @map("id")

  // 关联的用户
  userId            String    @map("user_id")
  user              User      @relation("UserFamilyMembers", fields: [userId], references: [id], onDelete: Cascade)

  // 关联的家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 成员信息
  name              String    @map("name")
  phone             String    @map("phone")
  relationship      String?   @map("relationship")

  // 权限与角色
  role              FamilyMemberRole @default(VIEWER) @map("role")

  // 紧急联系顺序（1, 2, 3... 数字越小优先级越高）
  emergencyOrder    Int       @default(999) @map("emergency_order")

  // 状态
  status            FamilyMemberStatus @default(ACTIVE) @map("status")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@unique([userId, familyId], name: "uq_user_family")
  @@index([familyId], name: "idx_family_member_family_id")
  @@index([emergencyOrder], name: "idx_family_member_emergency_order")
  @@map("family_member")
}

enum FamilyMemberRole {
  ADMIN   @map("ADMIN")   // 管理员：拥有家庭所有权限
  VIEWER  @map("VIEWER")  // 查看者：仅能查看数据
}

enum FamilyMemberStatus {
  ACTIVE      @map("ACTIVE")      // 活跃
  INVITED     @map("INVITED")     // 已邀请待确认
  INACTIVE    @map("INACTIVE")    // 已移除/已退出
}

/// 邀请记录表
model Invitation {
  id                String    @id @default(uuid()) @map("id")

  // 邀请人（管理员）
  inviterId         String    @map("inviter_id")
  inviter           User      @relation("UserInvitations", fields: [inviterId], references: [id])

  // 被邀请人手机号
  inviteePhone      String    @map("invitee_phone")

  // 目标家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 邀请信息
  role              FamilyMemberRole @default(VIEWER) @map("role")
  status            InvitationStatus @default(PENDING) @map("status")

  // 过期时间（24小时）
  expiresAt         DateTime  @map("expires_at")

  // 响应时间
  respondedAt       DateTime? @map("responded_at")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@unique([inviteePhone, familyId, status], name: "uq_invite_pending")
  @@index([familyId], name: "idx_invitation_family_id")
  @@index([expiresAt], name: "idx_invitation_expires_at")
  @@map("invitation")
}

enum InvitationStatus {
  PENDING     @map("PENDING")     // 待处理
  ACCEPTED    @map("ACCEPTED")    // 已接受
  REJECTED    @map("REJECTED")    // 已拒绝
  EXPIRED     @map("EXPIRED")     // 已过期
}

// --------------------------------------------
// 设备管理
// --------------------------------------------

/// 设备主表
model Device {
  id                String    @id @default(uuid()) @map("id")

  // 设备标识
  deviceCode        String    @unique @map("device_code")
  deviceType        DeviceType @map("device_type")

  // 设备信息
  manufacturer      String?   @map("manufacturer")
  model             String?   @map("model")
  firmwareVersion   String?   @map("firmware_version")

  // 设备位置（物理安装位置）
  location          String?   @map("location")

  // 设备能力（JSON存储）
  capabilities      Json?     @map("capabilities")

  // 设备状态
  status            DeviceStatus @default(OFFLINE) @map("status")

  // 最后在线时间
  lastOnlineAt      DateTime? @map("last_online_at")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  // 关联关系
  bindings          DeviceBinding[]
  deviceStatusLogs  DeviceStatusLog[]

  @@index([deviceType], name: "idx_device_type")
  @@index([status], name: "idx_device_status")
  @@map("device")
}

enum DeviceType {
  LUMA        @map("LUMA")        // 触屏智能音箱
  L4P         @map("L4P")         // 跌倒监测雷达
  X1          @map("X1")          // 睡眠监测仪
  WATCH       @map("WATCH")       // 4G智能手表
}

enum DeviceStatus {
  ONLINE      @map("ONLINE")      // 在线
  OFFLINE     @map("OFFLINE")     // 离线
  BOUND       @map("BOUND")       // 已绑定
  UNBOUND     @map("UNBOUND")     // 未绑定
}

/// 设备绑定表
model DeviceBinding {
  id                String    @id @default(uuid()) @map("id")

  // 设备
  deviceId          String    @map("device_id")
  device            Device    @relation(fields: [deviceId], references: [id], onDelete: Cascade)

  // 家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 绑定信息
  nickname          String?   @map("nickname")
  bindingType       BindingType @map("binding_type")

  // 安装位置（雷达用）
  roomName          String?   @map("room_name")

  // Wi-Fi信息
  wifiSsid          String?   @map("wifi_ssid")

  // 绑定状态
  status            BindingStatus @default(PENDING) @map("status")

  // 自检结果
  selfCheckResult   Json?     @map("self_check_result")

  // 绑定时间
  boundAt           DateTime? @map("bound_at")
  unboundAt         DateTime? @map("unbound_at")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@unique([deviceId, familyId], name: "uq_device_family")
  @@index([familyId], name: "idx_device_binding_family_id")
  @@index([status], name: "idx_device_binding_status")
  @@map("device_binding")
}

enum BindingType {
  PRIMARY     @map("PRIMARY")     // 主设备（Luma）
  SECONDARY   @map("SECONDARY")   // 辅助设备（雷达、手表）
}

enum BindingStatus {
  PENDING     @map("PENDING")     // 绑定中
  BOUND       @map("BOUND")       // 已绑定
  UNBOUNDING  @map("UNBOUNDING")  // 解绑中
  UNBOUNDED   @map("UNBOUNDED")   // 已解绑
}

/// 设备状态日志表
model DeviceStatusLog {
  id                String    @id @default(uuid()) @map("id")

  // 设备
  deviceId          String    @map("device_id")
  device            Device    @relation(fields: [deviceId], references: [id], onDelete: Cascade)

  // 日志内容
  eventType         DeviceEventType @map("event_type")
  eventData         Json?     @map("event_data")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([deviceId], name: "idx_device_log_device_id")
  @@index([createdAt], name: "idx_device_log_created_at")
  @@map("device_status_log")
}

enum DeviceEventType {
  ONLINE         @map("ONLINE")         // 设备上线
  OFFLINE        @map("OFFLINE")        // 设备离线
  SELF_CHECK     @map("SELF_CHECK")     // 设备自检
  BINDING        @map("BINDING")        // 绑定事件
  UNBINDING      @map("UNBINDING")      // 解绑事件
  HEARTBEAT      @map("HEARTBEAT")      // 心跳
  ERROR          @map("ERROR")          // 错误
}

// --------------------------------------------
// 预警与告警
// --------------------------------------------

/// 预警事件表
model Alert {
  id                String    @id @default(uuid()) @map("id")

  // 关联家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 预警类型与级别
  alertType         AlertType @map("alert_type")
  alertLevel        AlertLevel @map("alert_level")

  // 事件详情
  title             String    @map("title")
  description       String?   @map("description")
  evidence          Json?     @map("evidence")

  // 时间信息
  occurredAt        DateTime  @map("occurred_at")

  // 处理状态
  status            AlertStatus @default(NEW) @map("status")
  resolvedAt        DateTime? @map("resolved_at")

  // 误报信息
  isFalseAlarm      Boolean   @default(false) @map("is_false_alarm")
  falseAlarmReason  String?   @map("false_alarm_reason")

  // 处理人
  resolvedBy        String?   @map("resolved_by")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  // 关联关系
  timeline          AlertTimeline[]
  notifications     AlertNotification[]

  @@index([familyId], name: "idx_alert_family_id")
  @@index([alertType], name: "idx_alert_type")
  @@index([alertLevel], name: "idx_alert_level")
  @@index([status], name: "idx_alert_status")
  @@index([occurredAt], name: "idx_alert_occurred_at")
  @@map("alert")
}

enum AlertType {
  FALL_DETECTED         @map("FALL_DETECTED")         // 跌倒检测
  SOS                   @map("SOS")                   // SOS紧急呼叫
  HEART_RATE_HIGH      @map("HEART_RATE_HIGH")      // 心率过高
  HEART_RATE_LOW       @map("HEART_RATE_LOW")       // 心率过低
  BLOOD_OXYGEN_LOW     @map("BLOOD_OXYGEN_LOW")     // 血氧过低
  ACTIVITY_LOW          @map("ACTIVITY_LOW")          // 活动量过低
  SLEEP_QUALITY_POOR   @map("SLEEP_QUALITY_POOR")   // 睡眠质量差
  RHYTHM_MISSING       @map("RHYTHM_MISSING")       // 节律缺失（长时间无活动）
  DEVICE_OFFLINE        @map("DEVICE_OFFLINE")        // 设备离线
  STAY_ALERT            @map("STAY_ALERT")            // 驻留预警
  NO_MOTION_ALERT       @map("NO_MOTION_ALERT")       // 无人预警
  BED_EXIT_ALERT        @map("BED_EXIT_ALERT")        // 离床预警
}

enum AlertLevel {
  RED       @map("RED")       // 红色：紧急预警（跌倒、SOS）
  YELLOW    @map("YELLOW")    // 黄色：风险预警
  NORMAL    @map("NORMAL")    // 普通：提醒
}

enum AlertStatus {
  NEW              @map("NEW")              // 新建
  PROCESSING       @map("PROCESSING")       // 处理中
  RESOLVED         @map("RESOLVED")         // 已解决
  FALSE_ALARM      @map("FALSE_ALARM")      // 误报
}

/// 预警时间线表
model AlertTimeline {
  id                String    @id @default(uuid()) @map("id")

  // 关联预警
  alertId           String    @map("alert_id")
  alert             Alert     @relation(fields: [alertId], references: [id], onDelete: Cascade)

  // 时间线内容
  timestamp         DateTime  @map("timestamp")
  event             String    @map("event")
  source            String?   @map("source")
  data              Json?     @map("data")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([alertId], name: "idx_alert_timeline_alert_id")
  @@map("alert_timeline")
}

/// 预警通知表（用于升级链）
model AlertNotification {
  id                String    @id @default(uuid()) @map("id")

  // 关联预警
  alertId           String    @map("alert_id")
  alert             Alert     @relation(fields: [alertId], references: [id], onDelete: Cascade)

  // 关联家庭（用于查询）
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 通知目标
  memberId          String?   @map("member_id")
  memberName        String?   @map("member_name")

  // 通知方式
  notifyType        NotifyType @map("notify_type")

  // 通知状态
  status            NotificationStatus @default(PENDING) @map("status")

  // 通知时间
  scheduledAt       DateTime  @map("scheduled_at")
  sentAt            DateTime? @map("sent_at")
  deliveredAt       DateTime? @map("delivered_at")
  confirmedAt       DateTime? @map("confirmed_at")

  // 升级信息
  level             Int       @default(1) @map("level")

  // 重试机制
  retryCount        Int       @default(0) @map("retry_count")
  maxRetries        Int       @default(3) @map("max_retries")
  nextRetryAt       DateTime? @map("next_retry_at")

  // 失败信息
  failureReason     String?   @map("failure_reason")
  failureCode       String?   @map("failure_code")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([alertId], name: "idx_alert_notification_alert_id")
  @@index([familyId], name: "idx_alert_notification_family_id")
  @@index([status], name: "idx_alert_notification_status")
  @@index([nextRetryAt], name: "idx_alert_notification_next_retry_at")
  @@map("alert_notification")
}

enum NotifyType {
  PHONE_CALL    @map("PHONE_CALL")    // 电话（Twilio）
  PUSH_IOS      @map("PUSH_IOS")      // iOS推送（APNs）
  PUSH_ANDROID  @map("PUSH_ANDROID")  // Android推送（FCM）
  SMS           @map("SMS")           // 短信
}

enum NotificationStatus {
  PENDING       @map("PENDING")       // 待发送
  SENT          @map("SENT")          // 已发送
  DELIVERED     @map("DELIVERED")     // 已送达
  CONFIRMED     @map("CONFIRMED")     // 已确认（家属处理）
  FAILED        @map("FAILED")        // 发送失败
}

// --------------------------------------------
// 健康数据
// --------------------------------------------

/// 健康数据表
model HealthData {
  id                String    @id @default(uuid()) @map("id")

  // 关联家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 数据类型
  dataType          HealthDataType @map("data_type")

  // 数据值（JSON存储）
  value             Json      @map("value")

  // 时间范围
  recordedAt        DateTime  @map("recorded_at")

  // 来源设备
  sourceDeviceId    String?   @map("source_device_id")
  sourceDeviceType  DeviceType? @map("source_device_type")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([familyId], name: "idx_health_data_family_id")
  @@index([dataType], name: "idx_health_data_type")
  @@index([recordedAt], name: "idx_health_data_recorded_at")
  @@map("health_data")
}

enum HealthDataType {
  HEART_RATE         @map("HEART_RATE")         // 心率
  BLOOD_OXYGEN       @map("BLOOD_OXYGEN")       // 血氧
  STEP_COUNT         @map("STEP_COUNT")         // 步数
  SLEEP_DURATION     @map("SLEEP_DURATION")     // 睡眠时长
  SLEEP_QUALITY      @map("SLEEP_QUALITY")      // 睡眠质量
  ACTIVITY_LEVEL     @map("ACTIVITY_LEVEL")     // 活动量
  RESPIRATION_RATE   @map("RESPIRATION_RATE")   // 呼吸率
  BODY_TEMPERATURE   @map("BODY_TEMPERATURE")   // 体温
  BLOOD_PRESSURE     @map("BLOOD_PRESSURE")     // 血压
}

/// 健康周报表
model HealthWeeklyReport {
  id                String    @id @default(uuid()) @map("id")

  // 关联家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 周报信息
  weekStartDate     DateTime  @map("week_start_date")
  weekEndDate       DateTime  @map("week_end_date")

  // 周报内容
  summary           String    @db.Text @map("summary")
  details           Json?     @map("details")

  // 生成状态
  status            ReportStatus @default(GENERATING) @map("status")

  generatedAt       DateTime? @map("generated_at")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@unique([familyId, weekStartDate], name: "uq_health_report_family_week")
  @@index([familyId], name: "idx_health_report_family_id")
  @@map("health_weekly_report")
}

enum ReportStatus {
  GENERATING    @map("GENERATING")    // 生成中
  READY         @map("READY")         // 已就绪
  FAILED        @map("FAILED")        // 生成失败
}

// --------------------------------------------
// 提醒功能
// --------------------------------------------

/// 提醒模板表
model ReminderTemplate {
  id                String    @id @default(uuid()) @map("id")

  // 模板信息
  name              String    @map("name")
  nameEn            String    @map("name_en")
  category          ReminderCategory @map("category")
  content           String    @map("content")
  isMedication      Boolean   @default(false) @map("is_medication")

  // 状态
  isActive          Boolean   @default(true) @map("is_active")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@index([category], name: "idx_template_category")
  @@index([isActive], name: "idx_template_is_active")
  @@map("reminder_template")
}

enum ReminderCategory {
  MEDICATION     @map("MEDICATION")     // 用药提醒
  HEALTH        @map("HEALTH")        // 健康提醒
  SAFETY        @map("SAFETY")        // 安全提醒
  SOCIAL        @map("SOCIAL")        // 社交提醒
  LIFESTYLE     @map("LIFESTYLE")     // 生活提醒
}

/// 提醒计划表
model Reminder {
  id                String    @id @default(uuid()) @map("id")

  // 关联家庭
  familyId          String    @map("family_id")
  family            Family    @relation(fields: [familyId], references: [id], onDelete: Cascade)

  // 提醒内容
  content           String    @map("content")
  contentType       ReminderContentType @default(TEXT) @map("content_type")

  // 提醒时间配置
  timeType          ReminderTimeType @map("time_type")
  timeValue         String    @map("time_value")

  // 重复规则
  repeatType        RepeatType @default(ONCE) @map("repeat_type")
  repeatDays        Int[]?    @map("repeat_days")
  repeatStartDate   DateTime? @map("repeat_start_date")
  repeatEndDate     DateTime? @map("repeat_end_date")

  // 提醒方式
  notifyViaLuma     Boolean   @default(true) @map("notify_via_luma")
  notifyViaApp     Boolean   @default(false) @map("notify_via_app")

  // 用药提醒标记
  isMedication      Boolean   @default(false) @map("is_medication")
  secondReminderDelay Int?  @map("second_reminder_delay")

  // 状态
  isActive          Boolean   @default(true) @map("is_active")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  // 关联关系
  executions        ReminderExecution[]

  @@index([familyId], name: "idx_reminder_family_id")
  @@index([isActive], name: "idx_reminder_is_active")
  @@map("reminder")
}

enum ReminderContentType {
  TEXT     @map("TEXT")     // 文字提醒
  VOICE    @map("VOICE")    // 语音提醒（URL）
  TEMPLATE @map("TEMPLATE") // 模板提醒
}

enum ReminderTimeType {
  SINGLE       @map("SINGLE")       // 单次
  MULTIPLE     @map("MULTIPLE")     // 多次
}

enum RepeatType {
  ONCE         @map("ONCE")         // 单次
  DAILY        @map("DAILY")        // 每天
  WEEKLY       @map("WEEKLY")       // 每周
  WEEKDAYS     @map("WEEKDAYS")     // 工作日
  CUSTOM       @map("CUSTOM")       // 自定义
}

/// 提醒执行记录表
model ReminderExecution {
  id                String    @id @default(uuid()) @map("id")

  // 关联提醒
  reminderId        String    @map("reminder_id")
  reminder          Reminder  @relation(fields: [reminderId], references: [id], onDelete: Cascade)

  // 执行时间
  scheduledAt       DateTime  @map("scheduled_at")
  executedAt        DateTime? @map("executed_at")

  // 执行状态
  status            ExecutionStatus @default(PENDING) @map("status")

  // 播报状态
  broadcastStatus   BroadcastStatus? @map("broadcast_status")
  broadcastAt       DateTime? @map("broadcast_at")

  // 确认状态
  confirmed         Boolean   @default(false) @map("confirmed")
  confirmedAt       DateTime? @map("confirmed_at")
  confirmMethod     ConfirmMethod? @map("confirm_method")

  // 二次提醒
  isSecondReminder  Boolean   @default(false) @map("is_second_reminder")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([reminderId], name: "idx_execution_reminder_id")
  @@index([scheduledAt], name: "idx_execution_scheduled_at")
  @@index([status], name: "idx_execution_status")
  @@map("reminder_execution")
}

enum ExecutionStatus {
  PENDING       @map("PENDING")       // 待执行
  EXECUTED      @map("EXECUTED")      // 已执行
  SKIPPED       @map("SKIPPED")       // 已跳过
  FAILED        @map("FAILED")        // 执行失败
}

enum BroadcastStatus {
  PENDING       @map("PENDING")       // 待播报
  SENT          @map("SENT")          // 已发送
  PLAYING       @map("PLAYING")       // 播报中
  COMPLETED     @map("COMPLETED")     // 播报完成
  FAILED        @map("FAILED")        // 播报失败
}

enum ConfirmMethod {
  VOICE    @map("VOICE")    // 语音确认
  TOUCH    @map("TOUCH")    // 触屏确认
}

// --------------------------------------------
// 配置与设置
// --------------------------------------------

/// 通知偏好表
model NotificationPreference {
  id                String    @id @default(uuid()) @map("id")

  // 关联用户
  userId            String    @unique @map("user_id")
  user              User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  // 偏好设置
  receiveAlert      Boolean   @default(true) @map("receive_alert")
  receiveMessage    Boolean   @default(true) @map("receive_message")
  silentMode        Boolean   @default(false) @map("silent_mode")

  // 静默时段
  silentStartTime   String?   @map("silent_start_time")
  silentEndTime     String?   @map("silent_end_time")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@map("notification_preference")
}

/// 系统配置表
model SystemConfig {
  id                String    @id @default(uuid()) @map("id")

  // 配置键
  key               String    @unique @map("key")
  value             Json      @map("value")

  // 版本控制
  version           Int       @default(1) @map("version")
  isActive          Boolean   @default(true) @map("is_active")

  // 灰度发布
  targetFamilies    String[]? @map("target_families")

  description       String?   @map("description")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@index([key], name: "idx_config_key")
  @@index([isActive], name: "idx_config_is_active")
  @@map("system_config")
}

// --------------------------------------------
// 审计日志
// --------------------------------------------

/// 操作审计日志表
model AuditLog {
  id                String    @id @default(uuid()) @map("id")

  // 操作人
  userId            String?   @map("user_id")
  user              User?     @relation(fields: [userId], references: [id])

  // 操作信息
  action            String    @map("action")
  resourceType      String    @map("resource_type")
  resourceId        String?   @map("resource_id")

  // 请求信息
  ipAddress         String?   @map("ip_address")
  userAgent         String?   @map("user_agent")

  // 变更内容
  changes           Json?     @map("changes")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([userId], name: "idx_audit_user_id")
  @@index([action], name: "idx_audit_action")
  @@index([resourceType], name: "idx_audit_resource_type")
  @@index([createdAt], name: "idx_audit_created_at")
  @@map("audit_log")
}

// --------------------------------------------
// 运营后台相关
// --------------------------------------------

/// 运营用户表
model AdminUser {
  id                String    @id @default(uuid()) @map("id")

  // 账户信息
  username          String    @unique @map("username")
  passwordHash      String    @map("password_hash")
  email             String?   @unique @map("email")
  phone             String?   @map("phone")

  // 角色
  role              AdminRole @default(OPERATOR) @map("role")

  // 状态
  isActive          Boolean   @default(true) @map("is_active")
  lastLoginAt       DateTime? @map("last_login_at")

  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@index([username], name: "idx_admin_username")
  @@map("admin_user")
}

enum AdminRole {
  ADMIN       @map("ADMIN")       // 超级管理员
  OPERATOR    @map("OPERATOR")    // 运营人员
  CUSTOMER_SERVICE @map("CUSTOMER_SERVICE") // 客服
  READONLY    @map("READONLY")    // 只读
}

/// 消息推送记录表
model PushMessage {
  id                String    @id @default(uuid()) @map("id")

  // 推送内容
  title             String    @map("title")
  content           String    @db.Text @map("content")
  messageType       MessageType @map("message_type")

  // 推送目标
  targetType        TargetType @map("target_type")
  targetFamilies    String[]? @map("target_families")

  // 发送计划
  sendType          SendType @map("send_type")
  scheduledAt       DateTime? @map("scheduled_at")
  sentAt            DateTime? @map("sent_at")

  // 发送统计
  totalCount        Int       @default(0) @map("total_count")
  successCount      Int       @default(0) @map("success_count")
  failedCount       Int       @default(0) @map("failed_count")

  createdAt         DateTime  @default(now()) @map("created_at")

  @@index([sendType], name: "idx_push_send_type")
  @@index([scheduledAt], name: "idx_push_scheduled_at")
  @@map("push_message")
}

enum MessageType {
  NORMAL      @map("NORMAL")      // 普通消息
  SYSTEM      @map("SYSTEM")      // 系统消息
  HEALTH_REPORT @map("HEALTH_REPORT") // 健康报告
}

enum TargetType {
  ALL         @map("ALL")         // 全部家庭
  FAMILY      @map("FAMILY")      // 指定家庭
}

enum SendType {
  IMMEDIATE   @map("IMMEDIATE")   // 立即发送
  SCHEDULED   @map("SCHEDULED")   // 定时发送
}

---

数据库设计

PostgreSQL 建表语句

-- ============================================
-- Reflekt Health - PostgreSQL 17 Database
-- 表命名: 全小写 + 下划线 (snake_case)
-- ============================================

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- --------------------------------------------
-- 用户与认证
-- --------------------------------------------

-- 用户账户表
CREATE TABLE user (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    phone VARCHAR(20) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_user_phone ON user(phone);
CREATE INDEX idx_user_deleted_at ON user(deleted_at);

-- --------------------------------------------
-- 家庭与成员
-- --------------------------------------------

-- 家庭档案表
CREATE TABLE family (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255),

    -- 老人信息
    elder_name VARCHAR(100),
    elder_age INTEGER,
    elder_gender VARCHAR(20),

    -- 健康信息（敏感数据）
    elder_disease_history TEXT,
    elder_medication_history TEXT,

    -- 生活习惯
    wake_up_time VARCHAR(10),
    sleep_time VARCHAR(10),
    silence_start_time VARCHAR(10),
    silence_end_time VARCHAR(10),

    -- 地址与时区
    address TEXT,
    timezone VARCHAR(50) DEFAULT 'America/New_York',

    -- 隐私同意
    privacy_consent BOOLEAN DEFAULT FALSE,
    privacy_consent_at TIMESTAMP WITH TIME ZONE,
    privacy_revoked_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_family_elder_name ON family(elder_name);
CREATE INDEX idx_family_created_at ON family(created_at);

-- 家庭成员表
CREATE TABLE family_member (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES user(id) ON DELETE CASCADE,
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    relationship VARCHAR(50),

    role VARCHAR(20) NOT NULL DEFAULT 'VIEWER',
    emergency_order INTEGER NOT NULL DEFAULT 999,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(user_id, family_id)
);

CREATE INDEX idx_family_member_family_id ON family_member(family_id);
CREATE INDEX idx_family_member_emergency_order ON family_member(emergency_order);

-- 邀请记录表
CREATE TABLE invitation (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    inviter_id UUID NOT NULL REFERENCES user(id),
    invitee_phone VARCHAR(20) NOT NULL,
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    role VARCHAR(20) NOT NULL DEFAULT 'VIEWER',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    responded_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_invitation_family_id ON invitation(family_id);
CREATE INDEX idx_invitation_expires_at ON invitation(expires_at);

-- --------------------------------------------
-- 设备管理
-- --------------------------------------------

-- 设备主表
CREATE TABLE device (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    device_code VARCHAR(100) NOT NULL UNIQUE,
    device_type VARCHAR(20) NOT NULL,

    manufacturer VARCHAR(100),
    model VARCHAR(100),
    firmware_version VARCHAR(50),
    location VARCHAR(100),
    capabilities JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'OFFLINE',
    last_online_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_device_type ON device(device_type);
CREATE INDEX idx_device_status ON device(status);

-- 设备绑定表
CREATE TABLE device_binding (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    device_id UUID NOT NULL REFERENCES device(id) ON DELETE CASCADE,
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    nickname VARCHAR(100),
    room_name VARCHAR(50),
    wifi_ssid VARCHAR(100),
    binding_type VARCHAR(20) DEFAULT 'SECONDARY',

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    self_check_result JSONB,

    bound_at TIMESTAMP WITH TIME ZONE,
    unbound_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(device_id, family_id)
);

CREATE INDEX idx_device_binding_family_id ON device_binding(family_id);
CREATE INDEX idx_device_binding_status ON device_binding(status);

-- 设备状态日志表
CREATE TABLE device_status_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    device_id UUID NOT NULL REFERENCES device(id) ON DELETE CASCADE,
    event_type VARCHAR(30) NOT NULL,
    event_data JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_device_log_device_id ON device_status_log(device_id);
CREATE INDEX idx_device_log_created_at ON device_status_log(created_at);

-- --------------------------------------------
-- 预警与告警
-- --------------------------------------------

-- 预警事件表
CREATE TABLE alert (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    alert_type VARCHAR(30) NOT NULL,
    alert_level VARCHAR(20) NOT NULL,

    title VARCHAR(255) NOT NULL,
    description TEXT,
    evidence JSONB,

    occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,

    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    resolved_at TIMESTAMP WITH TIME ZONE,

    is_false_alarm BOOLEAN DEFAULT FALSE,
    false_alarm_reason VARCHAR(255),
    resolved_by VARCHAR(100),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_alert_family_id ON alert(family_id);
CREATE INDEX idx_alert_type ON alert(alert_type);
CREATE INDEX idx_alert_level ON alert(alert_level);
CREATE INDEX idx_alert_status ON alert(status);
CREATE INDEX idx_alert_occurred_at ON alert(occurred_at);

-- 预警时间线表
CREATE TABLE alert_timeline (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    alert_id UUID NOT NULL REFERENCES alert(id) ON DELETE CASCADE,

    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    event VARCHAR(255) NOT NULL,
    source VARCHAR(50),
    data JSONB,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_alert_timeline_alert_id ON alert_timeline(alert_id);

-- 预警通知表（升级链）
CREATE TABLE alert_notification (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    alert_id UUID NOT NULL REFERENCES alert(id) ON DELETE CASCADE,
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    member_id UUID,
    member_name VARCHAR(100),

    notify_type VARCHAR(30) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',

    scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
    sent_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    confirmed_at TIMESTAMP WITH TIME ZONE,

    level INTEGER NOT NULL DEFAULT 1,

    retry_count INTEGER NOT NULL DEFAULT 0,
    max_retries INTEGER NOT NULL DEFAULT 3,
    next_retry_at TIMESTAMP WITH TIME ZONE,

    failure_reason VARCHAR(255),
    failure_code VARCHAR(50),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_alert_notification_alert_id ON alert_notification(alert_id);
CREATE INDEX idx_alert_notification_family_id ON alert_notification(family_id);
CREATE INDEX idx_alert_notification_status ON alert_notification(status);
CREATE INDEX idx_alert_notification_next_retry_at ON alert_notification(next_retry_at);

-- --------------------------------------------
-- 健康数据
-- --------------------------------------------

-- 健康数据表
CREATE TABLE health_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    data_type VARCHAR(30) NOT NULL,
    value JSONB NOT NULL,
    recorded_at TIMESTAMP WITH TIME ZONE NOT NULL,

    source_device_id UUID,
    source_device_type VARCHAR(20),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_health_data_family_id ON health_data(family_id);
CREATE INDEX idx_health_data_type ON health_data(data_type);
CREATE INDEX idx_health_data_recorded_at ON health_data(recorded_at);

-- 健康周报表
CREATE TABLE health_weekly_report (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    week_start_date DATE NOT NULL,
    week_end_date DATE NOT NULL,

    summary TEXT NOT NULL,
    details JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'GENERATING',
    generated_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(family_id, week_start_date)
);

CREATE INDEX idx_health_report_family_id ON health_weekly_report(family_id);

-- --------------------------------------------
-- 家庭留言
-- --------------------------------------------

-- 提醒模板表
CREATE TABLE reminder_template (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    category VARCHAR(30) NOT NULL,
    content TEXT NOT NULL,
    is_medication BOOLEAN DEFAULT FALSE,

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_template_category ON reminder_template(category);
CREATE INDEX idx_template_is_active ON reminder_template(is_active);

-- 提醒计划表
CREATE TABLE reminder (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    family_id UUID NOT NULL REFERENCES family(id) ON DELETE CASCADE,

    content TEXT NOT NULL,
    content_type VARCHAR(20) NOT NULL DEFAULT 'TEXT',

    time_type VARCHAR(20) NOT NULL,
    time_value VARCHAR(50) NOT NULL,

    repeat_type VARCHAR(20) NOT NULL DEFAULT 'ONCE',
    repeat_days INTEGER[],
    repeat_start_date DATE,
    repeat_end_date DATE,

    notify_via_luma BOOLEAN DEFAULT TRUE,
    notify_via_app BOOLEAN DEFAULT FALSE,

    is_medication BOOLEAN DEFAULT FALSE,
    second_reminder_delay INTEGER,

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_reminder_family_id ON reminder(family_id);
CREATE INDEX idx_reminder_is_active ON reminder(is_active);

-- 提醒执行记录表
CREATE TABLE reminder_execution (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    reminder_id UUID NOT NULL REFERENCES reminder(id) ON DELETE CASCADE,

    scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
    executed_at TIMESTAMP WITH TIME ZONE,

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',

    broadcast_status VARCHAR(20),
    broadcast_at TIMESTAMP WITH TIME ZONE,

    confirmed BOOLEAN DEFAULT FALSE,
    confirmed_at TIMESTAMP WITH TIME ZONE,
    confirm_method VARCHAR(20),

    is_second_reminder BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_execution_reminder_id ON reminder_execution(reminder_id);
CREATE INDEX idx_execution_scheduled_at ON reminder_execution(scheduled_at);
CREATE INDEX idx_execution_status ON reminder_execution(status);

-- --------------------------------------------
-- 配置与设置
-- --------------------------------------------

-- 通知偏好表
CREATE TABLE notification_preference (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES user(id) ON DELETE CASCADE,

    receive_alert BOOLEAN DEFAULT TRUE,
    receive_message BOOLEAN DEFAULT TRUE,
    silent_mode BOOLEAN DEFAULT FALSE,

    silent_start_time VARCHAR(10),
    silent_end_time VARCHAR(10),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 系统配置表
CREATE TABLE system_config (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR(100) NOT NULL UNIQUE,
    value JSONB NOT NULL,

    version INTEGER NOT NULL DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,

    target_families UUID[],
    description TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_config_key ON system_config(key);
CREATE INDEX idx_config_is_active ON system_config(is_active);

-- --------------------------------------------
-- 审计日志
-- --------------------------------------------

-- 操作审计日志表
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES user(id),

    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(100),

    ip_address VARCHAR(50),
    user_agent TEXT,

    changes JSONB,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_audit_user_id ON audit_log(user_id);
CREATE INDEX idx_audit_action ON audit_log(action);
CREATE INDEX idx_audit_resource_type ON audit_log(resource_type);
CREATE INDEX idx_audit_created_at ON audit_log(created_at);

-- --------------------------------------------
-- 运营后台相关
-- --------------------------------------------

-- 运营用户表
CREATE TABLE admin_user (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20),

    role VARCHAR(20) NOT NULL DEFAULT 'OPERATOR',
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_admin_username ON admin_user(username);

-- 消息推送记录表
CREATE TABLE push_message (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    message_type VARCHAR(20) NOT NULL,

    target_type VARCHAR(20) NOT NULL,
    target_families UUID[],

    send_type VARCHAR(20) NOT NULL,
    scheduled_at TIMESTAMP WITH TIME ZONE,
    sent_at TIMESTAMP WITH TIME ZONE,

    total_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_push_send_type ON push_message(send_type);
CREATE INDEX idx_push_scheduled_at ON push_message(scheduled_at);

-- --------------------------------------------
-- 枚举类型定义（PostgreSQL ENUM）
-- --------------------------------------------

-- 家庭成员角色
DO $$ BEGIN
    CREATE TYPE family_member_role AS ENUM ('ADMIN', 'VIEWER');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 家庭成员状态
DO $$ BEGIN
    CREATE TYPE family_member_status AS ENUM ('ACTIVE', 'INVITED', 'INACTIVE');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 邀请状态
DO $$ BEGIN
    CREATE TYPE invitation_status AS ENUM ('PENDING', 'ACCEPTED', 'REJECTED', 'EXPIRED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 设备类型
DO $$ BEGIN
    CREATE TYPE device_type AS ENUM ('LUMA', 'L4P', 'X1', 'WATCH');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 设备状态
DO $$ BEGIN
    CREATE TYPE device_status AS ENUM ('ONLINE', 'OFFLINE', 'BOUND', 'UNBOUND');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 绑定类型
DO $$ BEGIN
    CREATE TYPE binding_type AS ENUM ('PRIMARY', 'SECONDARY');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 绑定状态
DO $$ BEGIN
    CREATE TYPE binding_status AS ENUM ('PENDING', 'BOUND', 'UNBOUNDING', 'UNBOUNDED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 设备事件类型
DO $$ BEGIN
    CREATE TYPE device_event_type AS ENUM ('ONLINE', 'OFFLINE', 'SELF_CHECK', 'BINDING', 'UNBINDING', 'HEARTBEAT', 'ERROR');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 预警类型
DO $$ BEGIN
    CREATE TYPE alert_type AS ENUM (
        'FALL_DETECTED', 'SOS', 'HEART_RATE_HIGH', 'HEART_RATE_LOW',
        'BLOOD_OXYGEN_LOW', 'ACTIVITY_LOW', 'SLEEP_QUALITY_POOR',
        'RHYTHM_MISSING', 'DEVICE_OFFLINE', 'STAY_ALERT', 'NO_MOTION_ALERT', 'BED_EXIT_ALERT'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 预警级别
DO $$ BEGIN
    CREATE TYPE alert_level AS ENUM ('RED', 'YELLOW', 'NORMAL');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 预警状态
DO $$ BEGIN
    CREATE TYPE alert_status AS ENUM ('NEW', 'PROCESSING', 'RESOLVED', 'FALSE_ALARM');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 通知类型
DO $$ BEGIN
    CREATE TYPE notify_type AS ENUM ('PHONE_CALL', 'PUSH_IOS', 'PUSH_ANDROID', 'SMS');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 通知状态
DO $$ BEGIN
    CREATE TYPE notification_status AS ENUM ('PENDING', 'SENT', 'DELIVERED', 'CONFIRMED', 'FAILED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 健康数据类型
DO $$ BEGIN
    CREATE TYPE health_data_type AS ENUM (
        'HEART_RATE', 'BLOOD_OXYGEN', 'STEP_COUNT', 'SLEEP_DURATION',
        'SLEEP_QUALITY', 'ACTIVITY_LEVEL', 'RESPIRATION_RATE', 'BODY_TEMPERATURE', 'BLOOD_PRESSURE'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 报告状态
DO $$ BEGIN
    CREATE TYPE report_status AS ENUM ('GENERATING', 'READY', 'FAILED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 提醒分类
DO $$ BEGIN
    CREATE TYPE reminder_category AS ENUM ('MEDICATION', 'HEALTH', 'SAFETY', 'SOCIAL', 'LIFESTYLE');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 提醒内容类型
DO $$ BEGIN
    CREATE TYPE reminder_content_type AS ENUM ('TEXT', 'VOICE', 'TEMPLATE');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 提醒时间类型
DO $$ BEGIN
    CREATE TYPE reminder_time_type AS ENUM ('SINGLE', 'MULTIPLE');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 重复类型
DO $$ BEGIN
    CREATE TYPE repeat_type AS ENUM ('ONCE', 'DAILY', 'WEEKLY', 'WEEKDAYS', 'CUSTOM');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 执行状态
DO $$ BEGIN
    CREATE TYPE execution_status AS ENUM ('PENDING', 'EXECUTED', 'SKIPPED', 'FAILED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 播报状态
DO $$ BEGIN
    CREATE TYPE broadcast_status AS ENUM ('PENDING', 'SENT', 'PLAYING', 'COMPLETED', 'FAILED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 确认方式
DO $$ BEGIN
    CREATE TYPE confirm_method AS ENUM ('VOICE', 'TOUCH');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 运营用户角色
DO $$ BEGIN
    CREATE TYPE admin_role AS ENUM ('ADMIN', 'OPERATOR', 'CUSTOMER_SERVICE', 'READONLY');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 消息类型
DO $$ BEGIN
    CREATE TYPE message_type AS ENUM ('NORMAL', 'SYSTEM', 'HEALTH_REPORT');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 目标类型
DO $$ BEGIN
    CREATE TYPE target_type AS ENUM ('ALL', 'FAMILY');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 发送类型
DO $$ BEGIN
    CREATE TYPE send_type AS ENUM ('IMMEDIATE', 'SCHEDULED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

---

核心实体关系图

---

数据模型设计说明

1. 核心设计原则

• 家庭为中心：以 family 为核心实体，所有业务数据都关联到家庭
• 统一成员管理：family_member 统一管理家属和协作者，通过 role 区分权限
• 紧急联系顺序：family_member.emergency_order 字段决定预警升级链的顺序
• 敏感数据加密：疾病史、用药史等敏感字段需在应用层加密后存储

2. 预警升级链设计

• alert_notification.level 字段实现四级通知升级：
  • Level 1: 第一联系人
  • Level 2: 第二联系人
  • Level 3: 第三联系人
  • Level 4: 全部广播
• 20秒升级间隔由后端定时任务控制

3. 提醒执行闭环

• reminder 存储提醒计划
• reminder_execution 记录每次执行状态
• 用药提醒通过 is_medication 标记触发二次确认

4. GDPR 合规

• 所有用户数据使用软删除（deleted_at）
• audit_log 记录所有敏感操作
• 支持数据导出和删除能力

5. 预警通知重试机制

• alert_notification.retry_count 记录当前重试次数
• alert_notification.max_retries 定义最大重试次数（系统默认3次）
• alert_notification.next_retry_at 存储下次重试时间，供定时任务查询
• 状态流：PENDING → FAILED（触发重试）→ SENT → DELIVERED → CONFIRMED

6. 关键系统配置键

通过 system_config 表管理的配置项：

配置键	类型	说明	默认值
alert_escalation_interval_seconds	Int	预警升级间隔秒数	20
alert_max_escalation_level	Int	最大升级级别	4
notification_retry_interval_seconds	Int	通知失败重试间隔	60
notification_max_retries	Int	通知最大重试次数	3