audio_codebuddy/server/prisma/schema.prisma

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

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

model User {
  id              Int       @id @default(autoincrement())
  phone           String?   @unique
  openid          String?   @unique
  nickname        String    @default("用户")
  avatar          String    @default("")
  memberLevel     Int       @default(0)
  memberExpireAt  DateTime?
  dailyUsage      Int       @default(0)
  lastUsageDate   String    @default("")
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  audios          Audio[]
  orders          Order[]
  playRecords     PlayRecord[]
  preferences     UserPreference?
  favorites       Favorite[]
  comments        Comment[]
  albumSubscriptions AlbumSubscription[]

  @@index([phone])
  @@index([openid])
}

model Audio {
  id              Int       @id @default(autoincrement())
  userId          Int?
  title           String
  text            String    @db.Text
  summary         String?   @db.Text
  tags            String?   @db.Text // JSON 字符串存储数组
  audioUrl        String    @db.Text
  audioDuration   Int       @default(0)
  audioSize       Int       @default(0)
  wordCount       Int
  voiceId         String
  voiceParams     String?   @db.Text // JSON 存储 {speed, pitch, volume}
  status          String    @default("processing")
  isFavorite      Boolean   @default(false)
  categoryId      Int?
  albumId         Int?      // 专辑ID，没有则归入默认专辑
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  user            User?     @relation(fields: [userId], references: [id])
  category        Category? @relation("CategoryAudios", fields: [categoryId], references: [id])
  album           Album?    @relation(fields: [albumId], references: [id])

  playRecords     PlayRecord[]
  favorites       Favorite[]
  comments        Comment[]
  albumAudios     AlbumAudio[]

  @@index([userId, createdAt])
  @@index([userId, isFavorite])
  @@index([categoryId])
  @@index([albumId])
}

model Order {
  id              Int       @id @default(autoincrement())
  userId          Int
  orderNo         String    @unique
  productType     String    // monthly 或 yearly
  amount          Decimal   @db.Decimal(10, 2)
  status          String    @default("pending")
  paymentMethod   String?
  paidAt          DateTime?
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  
  user            User      @relation(fields: [userId], references: [id])
  
  @@index([userId, createdAt])
  @@index([orderNo])
}

model PlayRecord {
  id        Int       @id @default(autoincrement())
  userId    Int
  audioId   Int
  progress  Float     @default(0)  // 播放进度（秒）
  duration  Float     @default(0)  // 总时长
  updatedAt DateTime  @updatedAt
  createdAt DateTime  @default(now())
  
  user      User      @relation(fields: [userId], references: [id])
  audio     Audio    @relation(fields: [audioId], references: [id])
  
  @@unique([userId, audioId])
  @@index([userId])
  @@index([audioId])
}

model UserPreference {
  id        Int      @id @default(autoincrement())
  userId    Int      @unique
  playSpeed Float    @default(1.0)
  quality   String   @default("standard")  // standard, high
  theme     String   @default("light")
  updatedAt DateTime @updatedAt
  createdAt DateTime @default(now())
  
  user      User     @relation(fields: [userId], references: [id])
}

model Favorite {
  id        Int      @id @default(autoincrement())
  userId    Int
  audioId   Int
  createdAt DateTime @default(now())
  
  user      User     @relation(fields: [userId], references: [id])
  audio     Audio   @relation(fields: [audioId], references: [id])
  
  @@unique([userId, audioId])
  @@index([userId])
}

model Category {
  id        Int      @id @default(autoincrement())
  name      String
  icon      String   @default("")
  sort      Int      @default(0)
  createdAt DateTime @default(now())
  
  audios    Audio[]  @relation("CategoryAudios")
}

model Comment {
  id        Int      @id @default(autoincrement())
  userId    Int
  audioId   Int
  content   String   @db.Text
  rating    Int      // 1-5星
  createdAt DateTime @default(now())

  user      User     @relation(fields: [userId], references: [id])
  audio     Audio   @relation(fields: [audioId], references: [id])

  @@index([audioId])
  @@index([userId])
}

model Notification {
  id        String   @id @default(uuid())
  userId    String
  title     String
  content   String   @db.Text
  isRead    Boolean  @default(false)
  createdAt DateTime @default(now())
}

model Album {
  id          Int       @id @default(autoincrement())
  name        String
  description String?   @db.Text
  coverUrl    String?
  userId      Int?
  isDefault   Boolean   @default(false) // 是否是默认专辑
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  albumAudios AlbumAudio[]
  subscriptions AlbumSubscription[]
  audios       Audio[]

  @@index([userId])
  @@index([createdAt])
}

model AlbumAudio {
  id        Int      @id @default(autoincrement())
  albumId   Int
  audioId   Int
  order     Int      @default(0)
  addedAt   DateTime @default(now())

  album     Album    @relation(fields: [albumId], references: [id], onDelete: Cascade)
  audio     Audio    @relation(fields: [audioId], references: [id], onDelete: Cascade)

  @@unique([albumId, audioId])
  @@index([albumId])
  @@index([audioId])
}

model AlbumSubscription {
  id        Int      @id @default(autoincrement())
  userId    Int
  albumId   Int
  createdAt DateTime @default(now())

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

  @@unique([userId, albumId])
  @@index([userId])
  @@index([albumId])
}

model Template {
  id          Int      @id @default(autoincrement())
  name        String
  category    String   // 广告营销/知识付费/短视频/企业宣传/日常生活/新闻资讯
  content     String   @db.Text // 模板内容
  description String?  @db.Text // 模板描述
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([category])
}

// 学习路径任务
model LearningPath {
  id          Int       @id @default(autoincrement())
  userId      Int?
  topic       String    @db.Text // 用户输入的主题
  status      String    @default("pending") // pending, generating, completed, failed
  progress    Int       @default(0) // 0-100 进度百分比
  totalCount  Int       @default(0) // 总内容块数
  completedCount Int     @default(0) // 已完成内容块数
  errorMsg    String?   @db.Text // 错误信息

  // 生成模式: progressive(渐进确认) | multi-agent(多Agent并行)
  generationMode String  @default("progressive")
  // 当前步骤(渐进模式): pending, subjects, chapters, sections, content, completed
  currentStep   String  @default("pending")
  // 多Agent模式状态: 存储各Agent的进度JSON
  agentStatus   String? @db.Text

  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  subjects    Subject[]

  @@index([userId, status])
}

// 学科（大类）
model Subject {
  id            Int       @id @default(autoincrement())
  learningPathId Int
  name          String    // 学科名称，如"计算机原理"
  orderIndex    Int       @default(0) // 排序
  status        String    @default("pending") // pending, generating, completed
  aiResponse    String?   @db.Text // AI返回的原始内容
  // 确认状态(渐进模式): pending, confirmed, regenerating
  confirmationStatus String @default("pending")
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  learningPath  LearningPath @relation(fields: [learningPathId], references: [id], onDelete: Cascade)
  chapters      Chapter[]

  @@index([learningPathId, orderIndex])
}

// 章节
model Chapter {
  id          Int       @id @default(autoincrement())
  subjectId   Int
  name        String    // 章节名称
  orderIndex  Int       @default(0)
  status      String    @default("pending")
  aiResponse  String?   @db.Text
  // 确认状态(渐进模式): pending, confirmed, regenerating
  confirmationStatus String @default("pending")
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  subject     Subject   @relation(fields: [subjectId], references: [id], onDelete: Cascade)
  sections    Section[]

  @@index([subjectId, orderIndex])
}

// 小节
model Section {
  id          Int       @id @default(autoincrement())
  chapterId   Int
  name        String    // 小节名称
  orderIndex  Int       @default(0)
  status      String    @default("pending")
  aiResponse  String?   @db.Text
  // 确认状态(渐进模式): pending, confirmed, regenerating
  confirmationStatus String @default("pending")
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  chapter     Chapter   @relation(fields: [chapterId], references: [id], onDelete: Cascade)
  contentBlocks ContentBlock[]

  @@index([chapterId, orderIndex])
}

// 内容块（详细内容）
model ContentBlock {
  id          Int       @id @default(autoincrement())
  sectionId   Int
  content     String    @db.Text // 详细内容
  orderIndex  Int       @default(0)
  wordCount   Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  section     Section   @relation(fields: [sectionId], references: [id], onDelete: Cascade)

  @@index([sectionId, orderIndex])
}