// InkReach Product Center Prisma Schema
// Generated based on docs/dev/database-table-design.md
// All TIMESTAMPTZ columns use DateTime @db.Timestamptz(6)
// All BIGINT columns use BigInt
// snake_case table & column names via @@map / @map

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

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

// ---------- Origin Goods ----------
enum OriginGoodSource {
  SDS
  CUSTOM
}

model OriginGood {
  id            BigInt   @id @default(autoincrement()) @map("origin_good_id")
  sdsGoodId     String   @unique @map("sds_good_id")
  // Cached SDS product metadata (filled during sync)
  sdsCategoryId String?  @map("sds_category_id")
  goodName      String?  @map("good_name")
  goodImage     String?  @map("good_image")
  goodPrice     Decimal? @map("good_price") @db.Decimal(12, 2)
  delisted      Boolean  @default(false)
  source        OriginGoodSource @default(SDS)
  createdAt     DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt     DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  goods    Good[]
  detail   OriginGoodDetail?
  variants OriginGoodVariant[]

  @@index([sdsCategoryId])
  @@index([source])
  @@map("origin_goods")
}

// ---------- Origin Good Details (cached from SDS /products/{id}) ----------
model OriginGoodDetail {
  originGoodId         BigInt    @id @map("origin_good_id")
  productCode          String?   @map("product_code")
  englishName          String?   @map("english_name")
  blankDesignUrl       String?   @map("blank_design_url")
  detailsPageVideoUrl  String?   @map("details_page_video_url")
  textureName          String?   @map("texture_name")
  productionCycleHours Int?      @map("production_cycle_hours")
  minWeightG           Decimal?  @map("min_weight_g") @db.Decimal(12, 3)
  reminder             String?
  productionProcess    String?   @map("production_process")
  materialDescription  String?   @map("material_description")
  productPerformance   String?   @map("product_performance")
  applicableScenarios  String?   @map("applicable_scenarios")
  washingInstructions  String?   @map("washing_instructions")
  specialDescription   String?   @map("special_description")
  designExplanation    String?   @map("design_explanation")
  designArea           String?   @map("design_area")
  pictureRequest       String?   @map("picture_request")
  sizeChart            Json?     @map("size_chart")
  packageSpecs         Json?     @map("package_specs")
  options              Json?
  media                Json?
  upstreamUpdatedAt    DateTime? @map("upstream_updated_at") @db.Timestamptz(6)
  syncedAt             DateTime  @default(now()) @map("synced_at") @db.Timestamptz(6)
  updatedAt            DateTime  @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  originGood OriginGood @relation(fields: [originGoodId], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@map("origin_good_details")
}

// ---------- Origin Good Variants (cached SDS child products / SKUs) ----------
model OriginGoodVariant {
  id            BigInt   @id @default(autoincrement()) @map("origin_good_variant_id")
  originGoodId  BigInt   @map("origin_good_id")
  sdsVariantId  String   @map("sds_variant_id")
  sku           String
  sizeId        String?  @map("size_id")
  sizeName      String?  @map("size_name")
  colorId       String?  @map("color_id")
  colorName     String?  @map("color_name")
  colorHex      String?  @map("color_hex")
  imageUrl      String?  @map("image_url")
  price         Decimal? @db.Decimal(12, 2)
  originalPrice Decimal? @map("original_price") @db.Decimal(12, 2)
  weightG       Decimal? @map("weight_g") @db.Decimal(12, 3)
  boxLengthCm   Decimal? @map("box_length_cm") @db.Decimal(12, 3)
  boxWidthCm    Decimal? @map("box_width_cm") @db.Decimal(12, 3)
  boxHeightCm   Decimal? @map("box_height_cm") @db.Decimal(12, 3)
  enabled       Boolean  @default(true)
  sortOrder     Int      @default(0) @map("sort_order")
  designData    Json?    @map("design_data")
  createdAt     DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt     DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  originGood OriginGood @relation(fields: [originGoodId], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@unique([originGoodId, sdsVariantId])
  @@index([originGoodId, sortOrder])
  @@index([sku])
  @@map("origin_good_variants")
}

// ---------- Countries ----------
model Country {
  id          BigInt   @id @default(autoincrement()) @map("country_id")
  countryName String   @unique @map("country_name")
  countryIcon String?  @map("country_icon")
  createdAt   DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt   DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  goods     Good[]
  positions Position[]

  @@map("countries")
}

// ---------- Categories (self-referential tree) ----------
model Category {
  id               BigInt   @id @default(autoincrement()) @map("category_id")
  parentCategoryId BigInt?  @map("parent_category_id")
  categoryName     String   @map("category_name")
  categoryIcon     String?  @map("category_icon")
  sdsCategoryId    String?  @unique @map("sds_category_id")
  createdAt        DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt        DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  parent    Category?  @relation("CategoryToCategory", fields: [parentCategoryId], references: [id], onDelete: Restrict, onUpdate: NoAction)
  children  Category[] @relation("CategoryToCategory")
  goods     Good[]
  positions Position[]

  @@index([parentCategoryId])
  @@map("categories")
}

// ---------- Tag Groups ----------
model TagGroup {
  id         BigInt   @id @default(autoincrement()) @map("tag_group_id")
  groupName  String   @unique @map("group_name")
  groupIcon  String?  @map("group_icon")
  groupColor String?  @map("group_color")
  sortOrder  Int      @default(0) @map("sort_order")
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt  DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  tags Tag[]

  @@map("tag_groups")
}

// ---------- Tags ----------
model Tag {
  id           BigInt   @id @default(autoincrement()) @map("tag_id")
  tagName      String   @unique @map("tag_name")
  tagColor     String?  @map("tag_color")
  tagFontColor String?  @map("tag_font_color")
  timing       String?
  tagGroupId   BigInt?  @map("tag_group_id")
  sortOrder    Int      @default(0) @map("sort_order")
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  goods    Good[]
  goodTags GoodTag[]
  tagGroup TagGroup? @relation(fields: [tagGroupId], references: [id], onDelete: SetNull, onUpdate: NoAction)

  @@index([tagGroupId])
  @@index([tagGroupId, sortOrder])
  @@map("tags")
}

// ---------- Positions ----------
model Position {
  id         BigInt   @id @default(autoincrement()) @map("position_id")
  indexVal   Int      @map("index_val")
  countryId  BigInt?  @map("country_id")
  categoryId BigInt?  @map("category_id")
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt  DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  country  Country?  @relation(fields: [countryId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  category Category? @relation(fields: [categoryId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  goods    Good[]

  @@index([countryId])
  @@index([categoryId])
  @@index([countryId, categoryId])
  @@map("positions")
}

// ---------- Goods ----------
model Good {
  id           BigInt   @id @default(autoincrement()) @map("good_id")
  originGoodId BigInt   @map("origin_good_id")
  countryId    BigInt   @map("country_id")
  categoryId   BigInt   @map("category_id")
  tagId        BigInt?  @map("tag_id")
  positionId   BigInt?  @map("position_id")
  goodName     String   @map("good_name")
  goodImage    String?  @map("good_image")
  goodPriority Int      @default(0) @map("good_priority")
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  originGood OriginGood @relation(fields: [originGoodId], references: [id], onDelete: Restrict, onUpdate: NoAction)
  country    Country    @relation(fields: [countryId], references: [id], onDelete: Restrict, onUpdate: NoAction)
  category   Category   @relation(fields: [categoryId], references: [id], onDelete: Restrict, onUpdate: NoAction)
  tag        Tag?       @relation(fields: [tagId], references: [id], onDelete: SetNull, onUpdate: NoAction)
  position   Position?  @relation(fields: [positionId], references: [id], onDelete: SetNull, onUpdate: NoAction)
  goodTags   GoodTag[]

  @@index([originGoodId])
  @@index([countryId])
  @@index([categoryId])
  @@index([tagId])
  @@index([positionId])
  @@index([goodPriority(sort: Desc)])
  @@index([countryId, goodPriority(sort: Desc)])
  @@index([categoryId, countryId])
  @@map("goods")
}

// ---------- Good-Tag Junction (M:N) ----------
model GoodTag {
  goodId    BigInt   @map("good_id")
  tagId     BigInt   @map("tag_id")
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(6)

  good Good @relation(fields: [goodId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  tag  Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@id([goodId, tagId])
  @@index([tagId])
  @@map("good_tags")
}

// ---------- Users (admin authentication) ----------
model User {
  id           BigInt   @id @default(autoincrement())
  username     String   @unique
  passwordHash String   @map("password_hash")
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

  @@map("users")
}

// ---------- Sync Logs ----------
enum SyncType {
  CATEGORIES
  PRODUCTS
  PRODUCT_DETAILS
}

enum SyncStatus {
  RUNNING
  SUCCESS
  FAILED
}

model SyncLog {
  id         BigInt     @id @default(autoincrement())
  type       SyncType
  status     SyncStatus
  message    String?
  startedAt  DateTime   @default(now()) @map("started_at") @db.Timestamptz(6)
  finishedAt DateTime?  @map("finished_at") @db.Timestamptz(6)

  @@index([type, startedAt])
  @@map("sync_logs")
}
