ecommerce-platform/app/prisma/schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
}

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

enum UserRole {
  CUSTOMER
  ADMIN
  OWNER
}

enum ProductStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

enum OrderStatus {
  PENDING
  PAID
  CANCELLED
  REFUNDED
  FULFILLED
}

enum ReviewStatus {
  PENDING
  APPROVED
  HIDDEN
}

model User {
  id                  String    @id @default(cuid())
  email               String    @unique
  passwordHash        String
  role                UserRole  @default(CUSTOMER)
  name                String?
  mustChangePassword  Boolean   @default(false)
  emailVerifiedAt     DateTime?
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  sessions            Session[]
  orders              Order[]
  reviews             Review[]
  passwordResetTokens PasswordResetToken[]
}

model Session {
  id          String   @id @default(cuid())
  userId      String
  tokenHash   String   @unique
  expiresAt   DateTime
  createdAt   DateTime @default(now())

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

model PasswordResetToken {
  id          String   @id @default(cuid())
  userId      String
  tokenHash   String   @unique
  expiresAt   DateTime
  usedAt      DateTime?
  createdAt   DateTime @default(now())

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

model ProductType {
  id          String    @id @default(cuid())
  name        String
  slug        String    @unique
  schema      Json
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  products    Product[]
}

model Product {
  id            String        @id @default(cuid())
  typeId        String
  title         String
  slug          String        @unique
  description   String
  basePrice     Int
  currency      String        @default("EUR")
  status        ProductStatus @default(DRAFT)
  attributes    Json
  stock         Int?
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  type          ProductType   @relation(fields: [typeId], references: [id])
  variants      ProductVariant[]
  categories    ProductCategory[]
  images        MediaAsset[]
  orderItems    OrderItem[]
  reviews       Review[]
}

model ProductVariant {
  id          String   @id @default(cuid())
  productId   String
  sku         String   @unique
  price       Int
  stock       Int
  attributes  Json
  active      Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  product     Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
}

model Category {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  parentId    String?
  createdAt   DateTime @default(now())

  products    ProductCategory[]
}

model ProductCategory {
  productId   String
  categoryId  String

  product     Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  category    Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  @@id([productId, categoryId])
}

model MediaAsset {
  id          String   @id @default(cuid())
  productId   String?
  url         String
  altText     String?
  mimeType    String
  size        Int
  createdAt   DateTime @default(now())

  product     Product? @relation(fields: [productId], references: [id], onDelete: SetNull)
}

model Order {
  id              String      @id @default(cuid())
  userId          String
  status          OrderStatus @default(PENDING)
  currency        String      @default("EUR")
  subtotal        Int
  taxTotal        Int         @default(0)
  shippingTotal   Int         @default(0)
  grandTotal      Int
  createdAt       DateTime    @default(now())
  updatedAt       DateTime    @updatedAt

  user            User        @relation(fields: [userId], references: [id])
  items           OrderItem[]
  payment         Payment?
}

model OrderItem {
  id          String   @id @default(cuid())
  orderId     String
  productId   String
  title       String
  quantity    Int
  unitPrice   Int
  totalPrice  Int
  metadata    Json?

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

model Payment {
  id                    String   @id @default(cuid())
  orderId               String   @unique
  provider              String
  providerPaymentId     String?  @unique
  status                String
  amount                Int
  currency              String
  rawPayload            Json?
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt

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

model Review {
  id          String       @id @default(cuid())
  userId      String
  productId   String
  rating      Int
  title       String?
  comment     String?
  status      ReviewStatus @default(PENDING)
  verified    Boolean      @default(false)
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

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

  @@unique([userId, productId])
}

model SiteSettings {
  id          String   @id @default(cuid())
  key         String   @unique
  value       Json
  updatedAt   DateTime @updatedAt
}

model Page {
  id          String   @id @default(cuid())
  slug        String   @unique
  title       String
  seoTitle    String?
  seoDesc     String?
  published   Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  sections    PageSection[]
}

model PageSection {
  id          String   @id @default(cuid())
  pageId      String
  type        String
  sortOrder   Int
  data        Json
  visible     Boolean  @default(true)

  page        Page     @relation(fields: [pageId], references: [id], onDelete: Cascade)
}

model AuditLog {
  id          String   @id @default(cuid())
  userId      String?
  action      String
  entity      String
  entityId    String?
  metadata    Json?
  createdAt   DateTime @default(now())
}