`prisma/schema.prisma`

132 lines
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model Listing {
  id               Int      @id @default(autoincrement())
  externalId       String?  @unique @map("external_id")
  source           String
  title            String
  brand            String?
  model            String?
  priceJpy         Float    @map("price_jpy")
  priceUsd         Float?   @map("price_usd")
  currency         String?
  condition        String?
  imageUrl         String?  @map("image_url")
  thumbnailUrl     String?  @map("thumbnail_url")
  productUrl       String?  @map("product_url")
  listingType      String?  @map("listing_type")
  auctionEndDate   String?  @map("auction_end_date")
  location         String?
  sellerName       String?  @map("seller_name")
  description      String?
  crawledAt        String?  @map("crawled_at")
  isActive         Int      @default(1) @map("is_active")
  isNew            Int?     @default(0) @map("is_new")

  usComparisons    UsComparison[]
  dealAnalysis     DealAnalysis?
  priceHistory     PriceHistory[]

  @@unique([externalId, source])
  @@index([brand])
  @@index([priceUsd])
  @@index([listingType])
  @@index([crawledAt])
  @@map("listings")
}

model UsComparison {
  id             Int      @id @default(autoincrement())
  listingId      Int      @map("listing_id")
  usSource       String   @map("us_source")
  usPriceUsd     Float?   @map("us_price_usd")
  usUrl          String?  @map("us_url")
  usCondition    String?  @map("us_condition")
  comparisonDate String?  @map("comparison_date")

  listing        Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)

  @@map("us_comparisons")
}

model DealAnalysis {
  id                Int      @id @default(autoincrement())
  listingId         Int      @unique @map("listing_id")
  avgUsPrice        Float?   @map("avg_us_price")
  minUsPrice        Float?   @map("min_us_price")
  maxUsPrice        Float?   @map("max_us_price")
  dealPercentage    Float?   @map("deal_percentage")
  confidenceScore   Float?   @map("confidence_score")
  isDeal            Int      @default(0) @map("is_deal")
  analysisDate      String?  @map("analysis_date")
  aiNotes           String?  @map("ai_notes")

  listing           Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)

  @@index([dealPercentage])
  @@index([isDeal])
  @@map("deal_analysis")
}

model CrawlHistory {
  id           Int      @id @default(autoincrement())
  source       String
  startTime    String?  @map("start_time")
  endTime      String?  @map("end_time")
  itemsFound   Int      @default(0) @map("items_found")
  itemsAdded   Int      @default(0) @map("items_added")
  itemsUpdated Int      @default(0) @map("items_updated")
  status       String   // running, completed, failed
  errorMessage String?  @map("error_message")

  @@map("crawl_history")
}

model PriceHistory {
  id             Int      @id @default(autoincrement())
  listingId      Int      @map("listing_id")
  externalId     String   @map("external_id")
  source         String
  priceJpy       Float    @map("price_jpy")
  priceUsd       Float?   @map("price_usd")
  condition      String?
  listingType    String?  @map("listing_type")
  recordedAt     String   @map("recorded_at")
  isAvailable    Int      @default(1) @map("is_available")

  listing        Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)

  @@index([externalId])
  @@index([listingId])
  @@index([recordedAt])
  @@map("price_history")
}

model PriceAlert {
  id             Int      @id @default(autoincrement())
  listingId      Int      @map("listing_id")
  externalId     String   @map("external_id")
  userEmail      String?  @map("user_email")
  targetPrice    Float    @map("target_price")
  alertType      String   @default("below") // below, above, change
  isActive       Int      @default(1) @map("is_active")
  createdAt      String   @map("created_at")
  triggeredAt    String?  @map("triggered_at")
  notified       Int      @default(0)

  @@index([externalId])
  @@index([listingId])
  @@index([isActive])
  @@map("price_alerts")
}