// FischerX Database Schema
// Database: PostgreSQL

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

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

// 用户表
model User {
  id            String   @id @default(uuid())
  email         String?  @unique
  phone         String?  @unique
  username      String   @unique
  password      String?
  firstName     String?
  lastName      String?
  avatar        String?
  isActive      Boolean  @default(true)
  emailVerified Boolean  @default(false)
  phoneVerified Boolean  @default(false)
  lastLoginAt   DateTime?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  roles              UserRole[]
  sessions           Session[]
  files              File[]
  oauthAccounts      OAuthAccount[]
  realnameAuth       RealnameAuth?
  orders             Order[]
  paymentOrders      PaymentOrder[]
  paymentRefunds     PaymentRefund[]
  notifications      Notification[]
  notificationPreference NotificationPreference?
  notificationBatches NotificationBatch[]
  articles           Article[]
  articleVersions    ArticleVersion[]
  comments           Comment[]
  mfaSecret          String?
  mfaEnabled    Boolean  @default(false)
  loginAttempts Int      @default(0)
  lockedUntil   DateTime?

  @@index([email])
  @@index([phone])
  @@index([username])
  @@index([isActive])
  @@index([createdAt])
  @@map("users")
}

// 角色表
model Role {
  id          String   @id @default(uuid())
  name        String   @unique
  description String?
  isSystem    Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  users       UserRole[]
  permissions RolePermission[]

  @@map("roles")
}

// 权限表
model Permission {
  id          String   @id @default(uuid())
  name        String   @unique
  description String?
  resource    String
  action      String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  roles       RolePermission[]

  @@map("permissions")
}

// 用户角色关联表
model UserRole {
  id        String   @id @default(uuid())
  userId    String
  roleId    String
  createdAt DateTime @default(now())

  user      User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  role      Role   @relation(fields: [roleId], references: [id], onDelete: Cascade)

  @@unique([userId, roleId])
  @@map("user_roles")
}

// 角色权限关联表
model RolePermission {
  id           String   @id @default(uuid())
  roleId       String
  permissionId String
  createdAt    DateTime @default(now())

  role         Role       @relation(fields: [roleId], references: [id], onDelete: Cascade)
  permission   Permission @relation(fields: [permissionId], references: [id], onDelete: Cascade)

  @@unique([roleId, permissionId])
  @@map("role_permissions")
}

// 文件表
model File {
  id          String   @id @default(uuid())
  name        String
  originalName String
  mimeType    String
  size        Int
  path        String
  url         String?
  cdnUrl      String?
  storageType String   @default("local") // local, aliyun, tencent, minio
  bucket      String?
  fileHash    String?  // 文件哈希,用于去重
  category    String?  // 文件分类: avatar, document, image, video, etc.
  tags        String[] // 文件标签
  isPublic    Boolean  @default(false)
  accessCount Int      @default(0)
  lastAccessedAt DateTime?
  ownerId     String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  owner       User     @relation(fields: [ownerId], references: [id], onDelete: Cascade)

  @@index([ownerId])
  @@index([storageType])
  @@index([category])
  @@index([fileHash])
  @@map("files")
}

// 会话表
model Session {
  id           String   @id @default(uuid())
  userId       String
  token        String   @unique
  refreshToken String?  @unique
  expiresAt    DateTime
  refreshExpiresAt DateTime?
  ipAddress    String?
  userAgent    String?
  deviceName   String?
  isActive     Boolean  @default(true)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([token])
  @@index([isActive])
  @@index([expiresAt])
  @@map("sessions")
}

// OAuth账号关联表
model OAuthAccount {
  id           String   @id @default(uuid())
  userId       String
  provider     String
  providerId   String
  providerData Json?
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerId])
  @@map("oauth_accounts")
}

// 实名认证表
model RealnameAuth {
  id              String   @id @default(uuid())
  userId          String   @unique
  realName        String
  idCardNumber    String
  idCardFrontUrl  String?
  idCardBackUrl   String?
  faceImageUrl    String?
  status          String   @default("pending")
  rejectReason    String?
  verifiedAt      DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  user            User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("realname_auth")
}

// 登录历史表
model LoginHistory {
  id          String   @id @default(uuid())
  userId      String
  ipAddress   String?
  userAgent   String?
  deviceName  String?
  loginMethod String
  status      String
  createdAt   DateTime @default(now())

  @@index([userId])
  @@index([ipAddress])
  @@index([status])
  @@index([createdAt])
  @@map("login_history")
}

// 支付渠道表
model PaymentChannel {
  id          String   @id @default(uuid())
  code        String   @unique
  name        String
  type        String   // alipay, wechat, unionpay
  isEnabled   Boolean  @default(true)
  isSandbox   Boolean  @default(true)
  config      Json
  sortOrder   Int      @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  orders      PaymentOrder[]
  refunds     PaymentRefund[]

  @@index([type])
  @@index([isEnabled])
  @@map("payment_channels")
}

// 支付订单表
model PaymentOrder {
  id              String   @id @default(uuid())
  orderNo         String   @unique
  channelOrderNo  String?
  userId          String
  channelId       String?
  amount          Decimal  @db.Decimal(12, 2)
  currency        String   @default("CNY")
  subject         String
  body            String?
  status          String   @default("pending") // pending, paid, failed, cancelled, refunded
  paidAt          DateTime?
  cancelledAt     DateTime?
  clientIp        String?
  userAgent       String?
  metadata        Json?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  user            User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  channel         PaymentChannel? @relation(fields: [channelId], references: [id], onDelete: SetNull)
  refunds         PaymentRefund[]
  logs            PaymentLog[]
  order           Order?

  @@index([userId])
  @@index([orderNo])
  @@index([status])
  @@index([createdAt])
  @@map("payment_orders")
}

// 退款记录表
model PaymentRefund {
  id              String   @id @default(uuid())
  refundNo        String   @unique
  channelRefundNo String?
  orderId         String
  channelId       String?
  userId          String
  amount          Decimal  @db.Decimal(12, 2)
  reason          String?
  status          String   @default("pending") // pending, success, failed
  processedAt     DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  order           PaymentOrder @relation(fields: [orderId], references: [id], onDelete: Cascade)
  channel         PaymentChannel? @relation(fields: [channelId], references: [id], onDelete: SetNull)
  user            User @relation(fields: [userId], references: [id], onDelete: Cascade)
  logs            PaymentLog[]

  @@index([orderId])
  @@index([userId])
  @@index([status])
  @@map("payment_refunds")
}

// 支付日志表
model PaymentLog {
  id          String   @id @default(uuid())
  orderId     String?
  refundId    String?
  type        String   // order, refund, callback, query
  action      String
  request     Json?
  response    Json?
  status      String   // success, failed
  error       String?
  createdAt   DateTime @default(now())

  order       PaymentOrder? @relation(fields: [orderId], references: [id], onDelete: Cascade)
  refund      PaymentRefund? @relation(fields: [refundId], references: [id], onDelete: Cascade)

  @@index([orderId])
  @@index([refundId])
  @@index([createdAt])
  @@map("payment_logs")
}

// 订单状态枚举
enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  COMPLETED
  CANCELLED
  REFUNDED
}

// 订单表
model Order {
  id             String       @id @default(uuid())
  orderNo        String       @unique
  userId         String
  status         OrderStatus  @default(PENDING)
  totalAmount    Decimal      @db.Decimal(12, 2)
  paidAmount     Decimal      @db.Decimal(12, 2)
  discountAmount Decimal      @default(0) @db.Decimal(12, 2)
  paymentMethod  String?
  paymentOrderId String?  @unique
  remark         String?
  paidAt         DateTime?
  shippedAt      DateTime?
  completedAt    DateTime?
  cancelledAt    DateTime?
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt

  user           User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  paymentOrder   PaymentOrder? @relation(fields: [paymentOrderId], references: [id], onDelete: SetNull)
  items          OrderItem[]

  @@index([userId])
  @@index([orderNo])
  @@index([status])
  @@index([createdAt])
  @@map("orders")
}

// 订单项表
model OrderItem {
  id           String   @id @default(uuid())
  orderId      String
  productId    String
  productName  String
  productImage String?
  quantity     Int
  unitPrice    Decimal  @db.Decimal(12, 2)
  totalAmount  Decimal  @db.Decimal(12, 2)
  createdAt    DateTime @default(now())

  order        Order    @relation(fields: [orderId], references: [id], onDelete: Cascade)

  @@index([orderId])
  @@index([productId])
  @@map("order_items")
}

// 通知表
model Notification {
  id              String         @id @default(uuid())
  userId          String?
  type            String         // info, warning, error, success
  title           String
  content         String
  contentHtml     String?
  channel         String         // email, sms, push, in-app
  channelData     Json?          // 渠道特定数据
  status          String         @default("pending") // pending, sending, sent, failed, cancelled
  sentAt          DateTime?
  readAt          DateTime?
  priority        String         @default("normal") // low, normal, high, urgent
  metadata        Json?
  expiresAt       DateTime?
  templateId      String?
  templateVersion Int?
  variables       Json?
  retryCount      Int            @default(0)
  maxRetries      Int            @default(3)
  errorMessage    String?
  deduplicationId String?
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt

  user            User?          @relation(fields: [userId], references: [id], onDelete: Cascade)
  template        NotificationTemplate? @relation(fields: [templateId], references: [id], onDelete: SetNull)
  batchItems      NotificationBatchItem[]

  @@index([userId])
  @@index([status])
  @@index([channel])
  @@index([type])
  @@index([priority])
  @@index([deduplicationId])
  @@map("notifications")
}

// 通知模板表
model NotificationTemplate {
  id          String                   @id @default(uuid())
  code        String                   @unique // 模板编码，如 user_welcome, password_reset
  name        String                   // 模板名称
  description String?
  channels    String[]                 // 支持的渠道
  version     Int                      @default(1)
  isActive    Boolean                  @default(true)
  locales     NotificationTemplateLocale[]
  notifications Notification[]
  createdAt   DateTime                 @default(now())
  updatedAt   DateTime                 @updatedAt

  @@index([code])
  @@index([isActive])
  @@map("notification_templates")
}

// 通知模板多语言表
model NotificationTemplateLocale {
  id          String   @id @default(uuid())
  templateId  String
  locale      String   // zh-CN, en-US, etc.
  subject     String?  // 邮件标题等
  title       String?  // 通知标题
  content     String   // 通知内容
  contentHtml String?  // HTML内容
  variables   String[] // 可用变量列表
  isDefault   Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  template    NotificationTemplate @relation(fields: [templateId], references: [id], onDelete: Cascade)

  @@unique([templateId, locale])
  @@index([locale])
  @@map("notification_template_locales")
}

// 通知渠道配置表
model NotificationChannel {
  id          String   @id @default(uuid())
  name        String   // 渠道名称
  type        String   // email, sms, push, in-app
  provider    String   // 服务商：aliyun, tencent, jpush, etc.
  config      Json     // 渠道配置，如API密钥等
  isActive    Boolean  @default(true)
  priority    Int      @default(0) // 优先级
  metadata    Json?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([type])
  @@index([isActive])
  @@map("notification_channels")
}

// 用户通知偏好表
model NotificationPreference {
  id             String   @id @default(uuid())
  userId         String   @unique
  // 各渠道开关
  emailEnabled   Boolean  @default(true)
  smsEnabled     Boolean  @default(true)
  pushEnabled    Boolean  @default(true)
  inAppEnabled   Boolean  @default(true)
  // 各类通知开关
  securityEnabled Boolean @default(true)
  marketingEnabled Boolean @default(false)
  systemEnabled  Boolean  @default(true)
  // 静音时段
  quietStartHour Int?     // 静音开始时间，如22
  quietEndHour   Int?     // 静音结束时间，如8
  timezone       String?  @default("Asia/Shanghai")
  metadata       Json?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("notification_preferences")
}

// 通知批量任务表
model NotificationBatch {
  id          String   @id @default(uuid())
  name        String
  description String?
  type        String   // immediate, scheduled
  scheduledAt DateTime?
  status      String   @default("pending") // pending, processing, completed, failed, cancelled
  totalCount  Int      @default(0)
  sentCount   Int      @default(0)
  failedCount Int      @default(0)
  creatorId   String?
  metadata    Json?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  creator     User?    @relation(fields: [creatorId], references: [id], onDelete: SetNull)
  items       NotificationBatchItem[]

  @@index([status])
  @@index([creatorId])
  @@map("notification_batches")
}

// 通知批量任务关联表
model NotificationBatchItem {
  id             String      @id @default(uuid())
  batchId        String
  notificationId String
  status         String      @default("pending") // pending, sent, failed
  sentAt         DateTime?
  errorMessage   String?
  createdAt      DateTime    @default(now())

  batch          NotificationBatch @relation(fields: [batchId], references: [id], onDelete: Cascade)
  notification   Notification @relation(fields: [notificationId], references: [id], onDelete: Cascade)

  @@index([batchId])
  @@index([notificationId])
  @@index([status])
  @@map("notification_batch_items")
}

// ==================== 内容管理模块 ====================

// 文章表
model Article {
  id              String   @id @default(uuid())
  title           String
  slug            String   @unique // URL友好的标识
  summary         String?  // 摘要
  content         String   // Markdown内容
  contentHtml     String?  // HTML内容
  coverImage      String?  // 封面图URL
  authorId        String
  categoryId      String?
  status          String   @default("draft") // draft, pending_review, published, rejected, archived
  viewCount       Int      @default(0)
  likeCount       Int      @default(0)
  commentCount    Int      @default(0)
  isTop           Boolean  @default(false) // 是否置顶
  isRecommend     Boolean  @default(false) // 是否推荐
  publishedAt     DateTime?
  reviewedAt      DateTime?
  reviewedBy      String?
  reviewComment   String?
  seoTitle        String?
  seoKeywords     String?
  seoDescription  String?
  sortOrder       Int      @default(0)
  version         Int      @default(1)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  author          User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  category        Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  tags            ArticleTag[]
  comments        Comment[]
  versions        ArticleVersion[]

  @@index([authorId])
  @@index([categoryId])
  @@index([status])
  @@index([publishedAt])
  @@index([slug])
  @@map("articles")
}

// 文章版本表
model ArticleVersion {
  id          String   @id @default(uuid())
  articleId   String
  version     Int
  title       String
  content     String
  contentHtml String?
  summary     String?
  changeLog   String?  // 变更说明
  authorId    String
  createdAt   DateTime @default(now())

  article     Article  @relation(fields: [articleId], references: [id], onDelete: Cascade)
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@unique([articleId, version])
  @@index([articleId])
  @@map("article_versions")
}

// 分类表
model Category {
  id          String   @id @default(uuid())
  name        String
  slug        String   @unique
  description String?
  icon        String?
  parentId    String?
  sortOrder   Int      @default(0)
  articleCount Int     @default(0)
  isActive    Boolean  @default(true)
  seoTitle    String?
  seoKeywords String?
  seoDescription String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  parent      Category? @relation("CategoryHierarchy", fields: [parentId], references: [id], onDelete: SetNull)
  children    Category[] @relation("CategoryHierarchy")
  articles    Article[]

  @@index([parentId])
  @@index([slug])
  @@map("categories")
}

// 标签表
model Tag {
  id          String   @id @default(uuid())
  name        String   @unique
  slug        String   @unique
  description String?
  articleCount Int     @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  articles    ArticleTag[]

  @@index([slug])
  @@map("tags")
}

// 文章标签关联表
model ArticleTag {
  id        String   @id @default(uuid())
  articleId String
  tagId     String
  createdAt DateTime @default(now())

  article   Article  @relation(fields: [articleId], references: [id], onDelete: Cascade)
  tag       Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@unique([articleId, tagId])
  @@index([articleId])
  @@index([tagId])
  @@map("article_tags")
}

// 评论表
model Comment {
  id          String   @id @default(uuid())
  content     String
  articleId   String
  userId      String
  parentId    String?  // 父评论ID,用于回复
  status      String   @default("pending") // pending, approved, rejected, deleted
  likeCount   Int      @default(0)
  ipAddress   String?
  userAgent   String?
  reviewedAt  DateTime?
  reviewedBy  String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  article     Article  @relation(fields: [articleId], references: [id], onDelete: Cascade)
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  parent      Comment? @relation("CommentHierarchy", fields: [parentId], references: [id], onDelete: Cascade)
  replies     Comment[] @relation("CommentHierarchy")

  @@index([articleId])
  @@index([userId])
  @@index([parentId])
  @@index([status])
  @@map("comments")
}