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])
}