esp32DIY_web/backend/prisma/schema.prisma

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

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

enum UserRole {
  admin
  seller
  buyer
}

enum ProjectStatus {
  draft
  pending
  approved
  rejected
  suspended
}

enum OrderStatus {
  pending
  paid
  cancelled
  refunded
}

enum PaymentGateway {
  toss
  stripe
}

model User {
  id              String    @id @default(uuid())
  email           String    @unique
  passwordHash    String
  nickname        String    @unique
  role            UserRole  @default(buyer)
  profileImageUrl String?
  isEmailVerified Boolean   @default(false)
  isActive        Boolean   @default(true)
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  lastLoginAt     DateTime?
  lastLoginIp     String?

  projects  Project[]
  orders    Order[]
  reviews   Review[]
  auditLogs AuditLog[]
}

model Project {
  id              String        @id @default(uuid())
  userId          String
  user            User          @relation(fields: [userId], references: [id])
  title           String
  description     String        @db.Text
  difficultyLevel Int           @default(3)
  chipFamily      String        @default("ESP32-S3")
  requiredParts   Json?
  status          ProjectStatus @default(draft)
  adminNote       String?
  commissionRate  Float         @default(0.1)
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt

  files   ProjectFile[]
  product Product?
}

model ProjectFile {
  id              String   @id @default(uuid())
  projectId       String
  project         Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  fileType        String
  url             String
  thumbnailUrl    String?
  fileSize        Int
  mimeType        String
  originalName    String?
  flashOffset     String   @default("0x0")
  firmwareVersion String?  // e.g. "1.0.0" — firmware 파일에만 사용
  displayOrder    Int      @default(0)
  createdAt       DateTime @default(now())
}

model Product {
  id         String   @id @default(uuid())
  projectId  String   @unique
  project    Project  @relation(fields: [projectId], references: [id])
  price      Int
  isOnSale   Boolean  @default(true)
  totalSales Int      @default(0)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  orders  Order[]
  reviews Review[]
}

model Order {
  id               String         @id @default(uuid())
  buyerId          String
  buyer            User           @relation(fields: [buyerId], references: [id])
  productId        String
  product          Product        @relation(fields: [productId], references: [id])
  amount           Int
  commissionAmount Int
  sellerAmount     Int
  paymentGateway   PaymentGateway @default(toss)
  paymentKey       String?
  tossOrderId      String?        @unique
  status           OrderStatus    @default(pending)
  buyerInfo        Json
  deviceInfo       Json?
  orderedAt        DateTime       @default(now())
  paidAt           DateTime?
  refundedAt       DateTime?
  refundReason     String?

  flashTokens FlashToken[]  // 원본 + 업데이트 토큰 복수 허용
  review      Review?
}

model FlashToken {
  id            String    @id @default(uuid())
  token         String    @unique @default(uuid())
  orderId       String
  order         Order     @relation(fields: [orderId], references: [id])
  isUsed        Boolean   @default(false)   // 성공적으로 플래시된 경우 true
  usedAt        DateTime?
  macAddress    String?
  chipFamily    String?
  expiresAt     DateTime
  createdAt     DateTime  @default(now())
  maxAttempts   Int       @default(3)        // 최대 시도 횟수
  attemptCount  Int       @default(0)        // 현재 시도 횟수 (실패 포함)
  lastAttemptAt DateTime?
  isLocked      Boolean   @default(false)    // maxAttempts 초과 시 잠금
  lockedReason  String?
  isUpdateToken Boolean   @default(false)    // 펌웨어 업데이트용 재발급 토큰

  flashLogs FlashLog[]

  @@index([orderId])
}

model FlashLog {
  id            String     @id @default(uuid())
  flashTokenId  String
  flashToken    FlashToken @relation(fields: [flashTokenId], references: [id])
  attemptNumber Int        @default(1)
  macAddress    String?    // 플래시 전 보드에서 읽은 MAC 주소
  chipFamily    String?    // 칩 종류 (esptool-js 에서 읽음)
  chipId        String?    // 칩 ID
  flashSize     String?    // 플래시 메모리 크기
  firmwareName  String
  firmwareId    String
  success       Boolean
  errorMessage  String?
  clientIp      String
  userAgent     String?
  startedAt     DateTime?  // 플래시 시작 시각
  completedAt   DateTime?  // 플래시 완료/실패 시각
  durationMs    Int?       // 소요 시간 ms
  flashedAt     DateTime   @default(now())

  @@index([flashTokenId])
  @@index([clientIp])
  @@index([macAddress])
  @@index([flashedAt])
}

// 이상 감지 이벤트
model FlashAnomaly {
  id           String    @id @default(uuid())
  type         String    // RATE_LIMIT_IP | HIGH_VOLUME_IP | MAC_REUSE | TOKEN_LOCK | SUSPICIOUS_DURATION
  severity     String    @default("medium")  // low | medium | high
  description  String
  clientIp     String?
  macAddress   String?
  flashTokenId String?
  flashLogId   String?
  metadata     Json?
  resolved     Boolean   @default(false)
  resolvedAt   DateTime?
  resolvedBy   String?
  createdAt    DateTime  @default(now())

  @@index([type])
  @@index([clientIp])
  @@index([resolved])
  @@index([createdAt])
}

model Review {
  id        String   @id @default(uuid())
  orderId   String   @unique
  order     Order    @relation(fields: [orderId], references: [id])
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  productId String
  product   Product  @relation(fields: [productId], references: [id])
  rating    Int
  title     String
  content   String   @db.Text
  isVisible Boolean  @default(true)
  createdAt DateTime @default(now())

  media ReviewMedia[]
}

model ReviewMedia {
  id           String   @id @default(uuid())
  reviewId     String
  review       Review   @relation(fields: [reviewId], references: [id], onDelete: Cascade)
  mediaType    String
  url          String
  thumbnailUrl String?
  createdAt    DateTime @default(now())
}

model AuditLog {
  id             String   @id @default(uuid())
  userId         String?
  user           User?    @relation(fields: [userId], references: [id])
  action         String
  targetType     String?
  targetId       String?
  ipAddress      String
  userAgent      String?
  requestMethod  String?
  requestPath    String?
  requestBody    Json?
  responseStatus Int?
  metadata       Json?
  createdAt      DateTime @default(now())

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