schema.prisma

// https://pris.ly/d/prisma-schema

generator client {
  provider        = "prisma-client-js"
  // https://www.prisma.io/docs/orm/prisma-client/queries/full-text-search
  previewFeatures = ["fullTextSearch"]
}

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

model Book {
  id            Int       @id @default(autoincrement())
  createdAt     DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt     DateTime? @updatedAt @db.Timestamptz(3)
  isbn13        BigInt    @unique
  title         String
  publishedDate DateTime? @db.Timestamptz(1)
  imageUrl      String?

  authors Author[]

  publisher   BookSource @relation(fields: [publisherId], references: [id])
  publisherId Int

  format   Format @relation(fields: [formatId], references: [id])
  formatId Int

  genre   Genre @relation(fields: [genreId], references: [id])
  genreId Int

  priceInCents Int
  quantity     Int           @default(0)
  invoiceItems InvoiceItem[]
  orderItems   OrderItem[]

  inventoryAdjustments InventoryAdjustment[]
}

model Author {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)
  name      String
  imageUrl  String?
  books     Book[]
}

model BookSource {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)
  name      String

  isPublisher Boolean
  books       Book[]

  isVendor           Boolean
  accountNumber      String?
  discountPercentage Decimal?  @db.Decimal(5, 4)
  invoices           Invoice[]
}

model Format {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  displayName String @unique

  books Book[]
}

model Genre {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  displayName String @unique

  books Book[]
}

model Invoice {
  id              Int       @id @default(autoincrement())
  createdAt       DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt       DateTime? @updatedAt @db.Timestamptz(3)
  invoiceNumber   String
  invoiceDate     DateTime  @db.Timestamptz(1)
  dateReceived    DateTime? @db.Timestamptz(1)
  isCompleted     Boolean   @default(false)
  subTotalInCents Int
  taxInCents      Int
  totalInCents    Int

  vendor   BookSource @relation(fields: [vendorId], references: [id])
  vendorId Int

  invoiceItems InvoiceItem[]
}

model InvoiceItem {
  id               Int       @id @default(autoincrement())
  createdAt        DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt        DateTime? @updatedAt @db.Timestamptz(3)
  itemCostInCents  Int
  quantity         Int
  totalCostInCents Int

  invoice   Invoice @relation(fields: [invoiceId], references: [id])
  invoiceId Int

  productType ProductType

  book   Book? @relation(fields: [bookId], references: [id])
  bookId Int?
}

enum ProductType {
  BOOK

  // eventually other product types like general merchandise, etc
}

model Order {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  orderUID        String     @unique @default(uuid())
  orderState      OrderState
  orderOpenedDate DateTime   @db.Timestamptz(1)
  orderClosedDate DateTime?  @db.Timestamptz(1)
  subTotalInCents Int
  taxInCents      Int
  totalInCents    Int

  orderItems OrderItem[]

  transactions Transaction[]
}

enum OrderState {
  // "open" states
  OPEN
  PENDING_TRANSACTION

  // "closed" states
  PAID
}

model OrderItem {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  productPriceInCents Int
  quantity            Int
  totalPriceInCents   Int

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

  productType ProductType

  book   Book? @relation(fields: [bookId], references: [id])
  bookId Int?
}

model Transaction {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  order    Order  @relation(fields: [orderUID], references: [orderUID], onDelete: Cascade)
  orderUID String

  amountInCents  Int
  transactionUID String @unique @default(uuid())

  status TransactionStatus

  transactionType TransactionType
  squareCheckout  SquareCheckout?
}

enum TransactionStatus {
  // initial state
  PENDING

  // terminal states
  CANCELLED
  COMPLETE
}

enum TransactionType {
  SQUARE_CHECKOUT
}

model SquareCheckout {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  checkoutId    String
  amountInCents Int
  status        String
  paymentType   String?
  cancelReason  String?

  transaction    Transaction @relation(fields: [transactionUID], references: [transactionUID], onDelete: Cascade)
  transactionUID String      @unique
}

model InventoryAdjustment {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  updatedQuantity Int
  reason          InventoryAdjustmentReason @relation(fields: [reasonId], references: [id])
  reasonId        Int

  productType ProductType

  book   Book? @relation(fields: [bookId], references: [id])
  bookId Int?
}

model InventoryAdjustmentReason {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime? @updatedAt @db.Timestamptz(3)

  displayName String @unique

  inventoryAdjustments InventoryAdjustment[]
}