generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

datasource db {
  provider = "sqlite"
}

model ShopeeShopToken {
  id              String   @id @default(cuid())
  shopId          String   @unique
  shopName        String?
  accessTokenEnc  String
  refreshTokenEnc String
  expireIn        Int?
  tokenCreatedAt  DateTime
  connectedAt     DateTime @default(now())
  updatedAt       DateTime @updatedAt
  status          String   @default("active")
  environment     String   @default("live")
  partnerId       String?
}

model AppAccount {
  id            String   @id
  username      String   @unique
  passwordHash  String?
  passwordSetAt DateTime?
  role          String   @default("super_admin")
  ownerId       String?
  status        String   @default("active")
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  owner         AppAccount?  @relation("OwnerStaff", fields: [ownerId], references: [id], onDelete: Cascade)
  staff         AppAccount[] @relation("OwnerStaff")
  sessions      AppSession[]
  ownedShops    AppOwnerShop[]

  @@index([role])
  @@index([ownerId])
  @@index([status])
}

model AppOwnerShop {
  id         String   @id @default(cuid())
  ownerId    String
  shopId     String   @unique
  assignedAt DateTime @default(now())

  owner AppAccount @relation(fields: [ownerId], references: [id], onDelete: Cascade)

  @@index([ownerId])
}

model AppSession {
  id         String   @id
  accountId  String
  expiresAt  DateTime
  createdAt  DateTime @default(now())
  lastSeenAt DateTime @default(now())

  account AppAccount @relation(fields: [accountId], references: [id], onDelete: Cascade)

  @@index([accountId])
  @@index([expiresAt])
}

model ShopeeOrder {
  id                String   @id @default(cuid())
  shopId            String
  orderSn           String
  packageNumber     String?
  orderStatus       String?
  paymentMethod     String?
  buyerUserId        String?
  recipientName      String?
  recipientPhone     String?
  recipientRegion    String?
  recipientCity      String?
  recipientDistrict  String?
  recipientZipcode   String?
  recipientFullAddr  String?
  shippingCarrier    String?
  trackingNumber     String?
  logisticsStatus    String?
  totalAmount        Float?
  currency           String?
  createTime         DateTime?
  updateTime         DateTime?
  shippingDocumentPrintedAt DateTime?
  pickupReadyAt       DateTime?
  shipmentMethod      String?
  pickupScheduledAt   DateTime?
  rawJson            Json?
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  @@unique([shopId, orderSn])
  @@index([shopId])
  @@index([orderStatus])
  @@index([createTime])
}

model ShopeeOrderItem {
  id        String   @id @default(cuid())
  shopId    String?
  orderSn   String
  itemId    String?
  modelId   String?
  itemName  String?
  modelName String?
  itemSku   String?
  modelSku  String?
  imageUrl  String?
  quantity  Int?
  itemPrice Float?
  hppAwalSnapshot Float?
  hppAwalSnapshotAt DateTime?
  hppFinalSnapshot Float?
  hppFinalSnapshotAt DateTime?
  rawJson   Json?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([orderSn])
  @@index([shopId, orderSn])
  @@index([itemSku])
  @@index([modelSku])
}

model ShopeeProductSku {
  id              String   @id @default(cuid())
  shopId          String
  itemId          String
  modelId         String   @default("0")
  itemName        String?
  modelName       String?
  imageUrl        String?
  masterSku       String?
  platformSku     String?
  platformItemId  String
  platformModelId String?
  stock           Int?
  reservedStock   Int?
  availableStock  Int?
  price           Float?
  currency        String?
  hpp             Float?
  hppCurrency     String?
  hppUpdatedAt    DateTime?
  hppAwal         Float?
  hppAwalCurrency String?
  hppAwalUpdatedAt DateTime?
  hppFinal        Float?
  hppFinalCurrency String?
  hppFinalUpdatedAt DateTime?
  status          String?
  updateTime      DateTime?
  rawJson         Json?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@unique([shopId, itemId, modelId])
  @@index([shopId])
  @@index([platformSku])
  @@index([masterSku])
  @@index([status])
}

model ShopeeFinanceSettlement {
  id                     String   @id @default(cuid())
  shopId                 String
  orderSn                String
  buyerUsername          String?
  escrowAmount           Float?
  productPrice           Float?
  originalPrice          Float?
  sellerDiscount         Float?
  shopeeDiscount         Float?
  buyerPaidShippingFee   Float?
  actualShippingFee      Float?
  shippingRebate         Float?
  commissionFee          Float?
  serviceFee             Float?
  sellerTransactionFee   Float?
  finalAmount            Float?
  currency               String?
  settlementStatus       String?
  orderCompleteTime      DateTime?
  walletTransactionTime  DateTime?
  walletTransactionAmount Float?
  walletTransactionType  String?
  walletTransactionJson  Json?
  rawJson                Json?
  syncedAt               DateTime @default(now())
  createdAt              DateTime @default(now())
  updatedAt              DateTime @updatedAt

  @@unique([shopId, orderSn])
  @@index([shopId])
  @@index([orderSn])
  @@index([orderCompleteTime])
  @@index([walletTransactionTime])
}

model ShopeeWalletTransaction {
  id              String   @id @default(cuid())
  shopId          String
  transactionKey  String
  transactionType String?
  moneyFlow       String?
  walletType      String?
  status          String?
  amount          Float?
  currentBalance  Float?
  orderSn         String?
  reason          String?
  transactionTime DateTime
  rawJson         Json?
  syncedAt        DateTime @default(now())
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@unique([shopId, transactionKey])
  @@index([shopId, transactionTime])
  @@index([transactionType])
  @@index([orderSn])
}

model ExternalBusinessExpense {
  id          String   @id @default(cuid())
  shopId      String?
  createdById String
  expenseDate DateTime
  category    String
  description String
  amount      Float
  affectsProfit Boolean @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([shopId, expenseDate])
  @@index([createdById, expenseDate])
}

model ShopeeApiLog {
  id         String   @id @default(cuid())
  shopId     String?
  path       String
  method     String
  statusCode Int?
  success    Boolean
  message    String?
  createdAt  DateTime @default(now())
}

model ShopeePushLog {
  id              String   @id @default(cuid())
  shopId          String?
  code            Int?
  pushId          String?
  messageId       String?
  signatureStatus String
  payload         Json?
  createdAt       DateTime @default(now())

  @@index([shopId])
  @@index([code])
  @@index([createdAt])
}

model AiProfitAnalysis {
  id             String   @id @default(cuid())
  ownerId        String
  scopeKey       String
  shopId         String?
  period         String
  dateFrom       DateTime
  dateTo         DateTime
  status         String   @default("awaiting_ai")
  aiConfigured   Boolean  @default(false)
  provider       String?
  model          String?
  overview       Json
  productContext Json
  aiResult       Json?
  error          String?
  createdAt      DateTime @default(now())
  completedAt    DateTime?

  recommendations AiProfitRecommendation[]

  @@index([ownerId, scopeKey, createdAt])
  @@index([status])
}

model AiProfitRecommendation {
  id                     String   @id @default(cuid())
  analysisId             String
  ownerId                String
  shopId                 String?
  productKey             String?
  category               String
  action                 String
  priority               String   @default("medium")
  title                  String
  reason                 String
  payload                Json?
  status                 String   @default("new")
  beforeSnapshot         Json?
  afterSnapshot          Json?
  estimatedMonthlyImpact Float?
  appliedAt              DateTime?
  createdAt              DateTime @default(now())
  updatedAt              DateTime @updatedAt

  analysis AiProfitAnalysis @relation(fields: [analysisId], references: [id], onDelete: Cascade)

  @@index([analysisId])
  @@index([ownerId, status])
  @@index([shopId, productKey])
}

model AiProfitWeeklySnapshot {
  id             String   @id @default(cuid())
  ownerId        String
  scopeKey       String
  shopId         String?
  weekStart      DateTime
  weekEnd        DateTime
  metrics        Json
  productMetrics Json
  aiStatus       String   @default("awaiting_ai")
  aiNote         Json?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  @@unique([ownerId, scopeKey, weekStart])
  @@index([ownerId, scopeKey, weekStart])
}

model AiProviderSetting {
  id            String   @id @default(cuid())
  ownerId       String
  provider      String
  model         String
  apiKeyEnc     String?
  active        Boolean  @default(false)
  lastTestedAt  DateTime?
  lastTestStatus String?
  lastTestMessage String?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  @@unique([ownerId, provider])
  @@index([ownerId, active])
}

model ShopeeAdsPerformance {
  id                String   @id @default(cuid())
  shopId            String
  campaignId        String
  itemId            String   @default("")
  dateKey           String
  adType            String?
  campaignPlacement String?
  adName            String?
  campaignStatus    String?
  impressions       Int      @default(0)
  clicks            Int      @default(0)
  expense           Float    @default(0)
  broadGmv          Float    @default(0)
  broadOrders       Int      @default(0)
  broadOrderAmount  Int      @default(0)
  directGmv         Float    @default(0)
  directOrders      Int      @default(0)
  directOrderAmount Int      @default(0)
  ctr               Float?
  conversionRate    Float?
  roas              Float?
  directRoas        Float?
  rawJson           Json?
  syncedAt          DateTime @default(now())
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  @@unique([shopId, campaignId, itemId, dateKey])
  @@index([shopId, dateKey])
  @@index([shopId, itemId, dateKey])
}