ScraperControl/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
}

model Church {
  id               String    @id @default(uuid())
  name             String
  address          String?
  city             String?
  state            String?
  zip              String?
  country          String    @default("US")
  latitude         Float
  longitude        Float
  phone            String?
  website          String?
  massScheduleUrl  String?   @map("mass_schedule_url")
  email            String?
  pastorName       String?   @map("pastor_name")
  diocese          String?
  directions       String?
  wheelchairAccess Boolean   @default(false) @map("wheelchair_access")
  masstimesId      String?   @unique @map("masstimes_id")
  source           String    @default("masstimes") // "osm", "masstimes", "manual"
  osmId            String?   @unique @map("osm_id") // OpenStreetMap node/way/relation ID (e.g. "node/12345")
  baiduId          String?   @unique @map("baidu_id") // Baidu Maps POI uid
  hasWebsite       Boolean   @default(false) @map("has_website")
  osmLastSyncedAt  DateTime? @map("osm_last_synced_at") // Last time this church was synced from OSM
  baiduLastSyncedAt DateTime? @map("baidu_last_synced_at") // Last time this church was synced from Baidu Maps
  googlePlaceId    String?   @unique @map("google_place_id")
  orarimesseId     String?   @unique @map("orarimesse_id")
  orarimesseLastSyncedAt DateTime? @map("orarimesse_last_synced_at")
  massSchedulesPhId     String?   @unique @map("mass_schedules_ph_id")
  philmassId            String?   @unique @map("philmass_id")
  horariosMisasId       String?   @unique @map("horarios_misas_id")
  mszeInfoId            String?   @unique @map("msze_info_id")
  weekdayMassesId       String?   @unique @map("weekday_masses_id")
  messesInfoId          String?   @unique @map("messes_info_id")
  bohosluzbyId          String?   @unique @map("bohosluzby_id")
  miserendId            String?   @unique @map("miserend_id")
  discovermassId        String?   @unique @map("discovermass_id")
  gottesdienstzeitenId  String?   @unique @map("gottesdienstzeiten_id")
  kerknetId             String?   @unique @map("kerknet_id")
  buscarmisasNetworkId  String?   @unique @map("buscarmisas_network_id")
  gcatholicId           String?   @unique @map("gcatholic_id")
  claimed          Boolean   @default(false)
  claimedAt        DateTime? @map("claimed_at")
  lastScrapedAt    DateTime? @map("last_scraped_at")
  scrapeStrategy   String    @default("generic") @map("scrape_strategy")
  cityNormalized    String?   @map("city_normalized")
  lastTransferredAt DateTime? @map("last_transferred_at") // Last time this church was transferred to Neon production
  freeSearchedAt    DateTime? @map("free_searched_at") // Last time FreeSearch was used to find website
  websiteLanguage   String?   @map("website_language") // ISO 639-1 language code detected from website
  websiteType       String?   @map("website_type") // URL classification: parish, social_media, wiki, directory, umbrella, government, heritage, review, video, maps
  reverseGeocodedAt DateTime? @map("reverse_geocoded_at") // When reverse geocoding was attempted
  googleSearchedAt  DateTime? @map("google_searched_at") // When Google Places enrichment was attempted
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")
  parochiaSlug      String?   @map("parochia_slug")

  dioceseId           String?   @map("diocese_id")

  claimedByUserId   String?   @map("claimed_by_user_id")

  massSchedules       MassSchedule[]
  confessionSchedules ConfessionSchedule[]
  adorationSchedules  AdorationSchedule[]
  specialServices     SpecialService[]
  scraperConfig       ChurchScraperConfig?
  dioceseObj          Diocese?  @relation(fields: [dioceseId], references: [id])
  claimedByUser       User?     @relation(fields: [claimedByUserId], references: [id])
  claimRequests       ClaimRequest[]

  @@index([osmId])
  @@index([baiduId])
  @@index([source])
  @@index([hasWebsite])
  @@index([state])
  @@index([city])
  @@index([state, cityNormalized])
  @@index([country])
  @@index([country, cityNormalized])
  @@index([lastTransferredAt])
  @@index([freeSearchedAt])
  @@index([websiteLanguage])
  @@index([websiteType])
  @@index([orarimesseId])
  @@index([massSchedulesPhId])
  @@index([philmassId])
  @@index([horariosMisasId])
  @@index([mszeInfoId])
  @@index([weekdayMassesId])
  @@index([messesInfoId])
  @@index([bohosluzbyId])
  @@index([miserendId])
  @@index([discovermassId])
  @@index([gottesdienstzeitenId])
  @@index([kerknetId])
  @@index([buscarmisasNetworkId])
  @@index([gcatholicId])
  @@index([dioceseId])
  @@index([claimedByUserId])
  @@map("churches")
}

model MassSchedule {
  id              String   @id @default(uuid())
  churchId        String   @map("church_id")
  dayOfWeek       Int      @map("day_of_week")
  time            String   // Store as HH:MM string
  massType        String?  @map("mass_type")
  language        String   @default("English")
  notes           String?
  isActive        Boolean  @default(true) @map("is_active")
  createdByUserId String?  @map("created_by_user_id")
  createdAt       DateTime @default(now()) @map("created_at")
  updatedAt       DateTime @updatedAt @map("updated_at")

  church    Church @relation(fields: [churchId], references: [id], onDelete: Cascade)
  createdBy User?  @relation("MassScheduleCreator", fields: [createdByUserId], references: [id])

  @@index([churchId, dayOfWeek, isActive])
  @@index([churchId, isActive])
  @@map("mass_schedules")
}

model ConfessionSchedule {
  id              String   @id @default(uuid())
  churchId        String   @map("church_id")
  dayOfWeek       Int      @map("day_of_week")
  startTime       String   @map("start_time")
  endTime         String   @map("end_time")
  notes           String?
  isActive        Boolean  @default(true) @map("is_active")
  createdByUserId String?  @map("created_by_user_id")
  createdAt       DateTime @default(now()) @map("created_at")
  updatedAt       DateTime @updatedAt @map("updated_at")

  church    Church @relation(fields: [churchId], references: [id], onDelete: Cascade)
  createdBy User?  @relation("ConfessionScheduleCreator", fields: [createdByUserId], references: [id])

  @@index([churchId, dayOfWeek, isActive])
  @@index([churchId, isActive])
  @@map("confession_schedules")
}

model AdorationSchedule {
  id              String   @id @default(uuid())
  churchId        String   @map("church_id")
  dayOfWeek       Int      @map("day_of_week")
  startTime       String   @map("start_time")
  endTime         String   @map("end_time")
  isPerpetual     Boolean  @default(false) @map("is_perpetual")
  notes           String?
  isActive        Boolean  @default(true) @map("is_active")
  createdByUserId String?  @map("created_by_user_id")
  createdAt       DateTime @default(now()) @map("created_at")
  updatedAt       DateTime @updatedAt @map("updated_at")

  church    Church @relation(fields: [churchId], references: [id], onDelete: Cascade)
  createdBy User?  @relation("AdorationScheduleCreator", fields: [createdByUserId], references: [id])

  @@index([churchId, dayOfWeek, isActive])
  @@index([churchId, isActive])
  @@map("adoration_schedules")
}

model LiturgicalDay {
  id                  String   @id @default(uuid())
  date                DateTime @unique @db.Date
  season              String                          // "Ordinary Time", "Advent", "Lent", etc.
  seasonWeek          String?  @map("season_week")    // "23rd Week" etc.
  liturgicalColor     String?  @map("liturgical_color") // "green", "violet", "white", "red"
  feastName           String?  @map("feast_name")     // Solemnity/feast name if applicable
  feastRank           String?  @map("feast_rank")     // "Solemnity", "Feast", "Memorial", "Optional Memorial"
  firstReading        String?  @map("first_reading")  // Citation: "Wisdom 9:13-18b"
  psalm               String?                          // "Psalm 90:3-4, 5-6, 12-13, 14 and 17"
  secondReading       String?  @map("second_reading") // Citation (Sundays/Solemnities only)
  gospel              String?                          // Citation: "Luke 14:25-33"
  firstReadingText    String?  @map("first_reading_text")   // Full verse text from Bible API
  psalmText           String?  @map("psalm_text")            // Full verse text from Bible API
  secondReadingText   String?  @map("second_reading_text")   // Full verse text from Bible API
  gospelText          String?  @map("gospel_text")           // Full verse text from Bible API
  usccbUrl            String?  @map("usccb_url")      // Link to full text on USCCB
  versesSource        String?  @map("verses_source")  // 'bible_api' | 'youversion' | 'none'
  versesCachedAt      DateTime? @map("verses_cached_at") // When verses were last fetched
  createdAt           DateTime @default(now()) @map("created_at")
  updatedAt           DateTime @updatedAt @map("updated_at")

  @@index([date])
  @@index([season])
  @@index([versesCachedAt])
  @@map("liturgical_days")
}

model ChurchScraperConfig {
  id            String    @id @default(uuid())
  churchId      String    @unique @map("church_id")
  strategyName  String    @map("strategy_name")
  lastSuccessAt DateTime? @map("last_success_at")
  lastFailureAt DateTime? @map("last_failure_at")
  failureCount  Int       @default(0) @map("failure_count")
  rawHtml       String?   @map("raw_html")
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")

  church Church @relation(fields: [churchId], references: [id], onDelete: Cascade)

  @@map("church_scraper_config")
}

model User {
  id                 String    @id @default(uuid())
  name               String
  email              String    @unique
  emailVerified      DateTime? @map("email_verified")
  hashedPassword     String?   @map("hashed_password")
  image              String?
  sortPreference     String    @default("time") @map("sort_preference")
  languagePreference String?   @map("language_preference")
  lastLatitude       Float?    @map("last_latitude")
  lastLongitude      Float?    @map("last_longitude")
  lastLocationName   String?   @map("last_location_name")
  createdAt          DateTime  @default(now()) @map("created_at")
  updatedAt          DateTime  @updatedAt @map("updated_at")
  suspended          Boolean   @default(false)
  suspendedAt        DateTime? @map("suspended_at")

  accounts            Account[]
  sessions            Session[]
  claimedChurches     Church[]
  massSchedules       MassSchedule[]         @relation("MassScheduleCreator")
  confessionSchedules ConfessionSchedule[]   @relation("ConfessionScheduleCreator")
  adorationSchedules  AdorationSchedule[]    @relation("AdorationScheduleCreator")
  specialServices     SpecialService[]       @relation("SpecialServiceCreator")
  claimRequestsSent     ClaimRequest[] @relation("ClaimRequester")
  claimRequestsReceived ClaimRequest[] @relation("ClaimRespondent")

  @@map("users")
}

model Account {
  id                String  @id @default(uuid())
  userId            String  @map("user_id")
  type              String
  provider          String
  providerAccountId String  @map("provider_account_id")
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?

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

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Session {
  id           String   @id @default(uuid())
  sessionToken String   @unique @map("session_token")
  userId       String   @map("user_id")
  expires      DateTime

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

  @@map("sessions")
}

model ContactMessage {
  id             String    @id @default(uuid())
  name           String?
  email          String?
  message        String?
  wantsToDonate  Boolean   @default(false) @map("wants_to_donate")
  showInDonorLog Boolean   @default(false) @map("show_in_donor_log")
  read           Boolean   @default(false)
  createdAt      DateTime  @default(now()) @map("created_at")
  updatedAt      DateTime  @updatedAt @map("updated_at")

  @@index([read])
  @@index([createdAt])
  @@map("contact_messages")
}

model Donation {
  id           String    @id @default(uuid())
  displayName  String?   @map("display_name")
  message      String?
  approved     Boolean   @default(false)
  approvedAt   DateTime? @map("approved_at")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")

  @@index([approved])
  @@index([createdAt])
  @@map("donations")
}

model Diocese {
  id              String    @id @default(uuid())
  name            String
  country         String
  language        String
  website         String?
  directoryUrl    String?   @map("directory_url")
  scrapeConfig    Json?     @map("scrape_config")
  churchCount     Int       @default(0) @map("church_count")
  lastScrapedAt   DateTime? @map("last_scraped_at")
  lastSuccessAt   DateTime? @map("last_success_at")
  lastFailureAt   DateTime? @map("last_failure_at")
  failureCount    Int       @default(0) @map("failure_count")
  active          Boolean   @default(true)
  notes           String?
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  churches        Church[]

  @@unique([name, country])
  @@index([country])
  @@index([active])
  @@index([lastScrapedAt])
  @@map("dioceses")
}

model BackgroundJob {
  id          String    @id @default(uuid())
  type        String                            // "scraper", "google-enrichment", "freesearch-enrichment"
  language    String?                           // "english", "french", "generic" (null for enrichment jobs)
  status      String    @default("pending")     // "pending", "running", "completed", "failed", "stopping"
  totalItems  Int       @default(0) @map("total_items")
  processed   Int       @default(0)
  succeeded   Int       @default(0)
  failed      Int       @default(0)
  itemsFound  Int       @default(0) @map("items_found")
  error       String?
  config      Json?
  startedAt   DateTime? @map("started_at")
  completedAt DateTime? @map("completed_at")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  @@index([type])
  @@index([status])
  @@map("background_jobs")
}

model EmailVerification {
  id        String   @id @default(uuid())
  email     String
  code      String   // 6-digit code
  expiresAt DateTime @map("expires_at")
  attempts  Int      @default(0) // rate limit verification attempts
  createdAt DateTime @default(now()) @map("created_at")

  @@index([email])
  @@index([expiresAt])
  @@map("email_verifications")
}

model SpecialService {
  id              String    @id @default(uuid())
  churchId        String    @map("church_id")
  serviceType     String    @map("service_type") // "stations_of_the_cross", "midnight_mass", "easter_vigil", etc.
  customName      String?   @map("custom_name") // only when serviceType="other"
  dayOfWeek       Int?      @map("day_of_week") // 0-6, null for date-specific
  date            DateTime? @db.Date // specific date for non-recurring
  time            String    // HH:MM
  endTime         String?   @map("end_time") // HH:MM, optional
  recurrence      String    @default("weekly") // "weekly", "biweekly", "monthly", "annually", "once"
  notes           String?
  isActive        Boolean   @default(true) @map("is_active")
  createdByUserId String?   @map("created_by_user_id")
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  church    Church @relation(fields: [churchId], references: [id], onDelete: Cascade)
  createdBy User?  @relation("SpecialServiceCreator", fields: [createdByUserId], references: [id])

  @@index([churchId, isActive])
  @@index([churchId, serviceType])
  @@map("special_services")
}

model ClaimRequest {
  id                    String    @id @default(uuid())
  churchId              String    @map("church_id")
  requesterUserId       String    @map("requester_user_id")
  claimantUserId        String    @map("claimant_user_id")
  message               String
  status                String    @default("pending") // pending, accepted, denied, ignored, auto_transferred
  denyReason            String?   @map("deny_reason")
  expiresAt             DateTime  @map("expires_at")
  respondedAt           DateTime? @map("responded_at")
  createdAt             DateTime  @default(now()) @map("created_at")
  updatedAt             DateTime  @updatedAt @map("updated_at")

  church    Church @relation(fields: [churchId], references: [id], onDelete: Cascade)
  requester User   @relation("ClaimRequester", fields: [requesterUserId], references: [id], onDelete: Cascade)
  claimant  User   @relation("ClaimRespondent", fields: [claimantUserId], references: [id], onDelete: Cascade)

  @@index([claimantUserId, status])
  @@index([requesterUserId])
  @@index([churchId])
  @@index([expiresAt, status])
  @@map("claim_requests")
}