ita-ito-horaires/prisma/schema.prisma

// ITA ITO — opening-hours display.
//
// Two rules shape this schema and are worth stating once:
//
//  1. Opening times are WALL-CLOCK strings ("HH:mm") in the shop's timezone,
//     never instants. Nothing here is stored in UTC, so the March and October
//     daylight-saving switches are non-events rather than edge cases.
//  2. VacationPeriod is a source of truth, not a generator. It is never
//     materialised into ScheduleException rows, so it can never overwrite a
//     manual exception and editing a period never leaves orphans behind.

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

datasource db {
  provider = "postgresql"
}

/// Why a day is not on its usual schedule.
enum ExceptionReason {
  TEMPORARY
  HOLIDAY
  VACATION
  SPECIAL_EVENT
}

/// Who created an exception. HOLIDAY_API rows may be rewritten by the daily
/// sync; MANUAL rows never are.
enum ExceptionSource {
  MANUAL
  HOLIDAY_API
}

enum TranslationStatus {
  PENDING
  DONE
  MANUAL
  ERROR
}

enum DeviceLogLevel {
  DEBUG
  INFO
  WARN
  ERROR
}

/// Single-row table. `id` is pinned so upserts never race into a second row.
model Settings {
  id                      String   @id @default("singleton")
  shopName                String   @default("ITA ITO")
  timezone                String   @default("Europe/Zurich")
  countryIsoCode          String   @default("CH")
  subdivisionCode         String   @default("CH-GE")
  defaultClosedMessageFr  String   @default("Fermé")
  defaultClosedMessageEn  String   @default("Closed")
  /// Device wake interval while the shop is open, or about to change state.
  refreshRateOpenSec      Int      @default(600)
  /// Device wake interval overnight and on closed days.
  refreshRateClosedSec    Int      @default(7200)
  imageFormat             String   @default("bmp")
  updatedAt               DateTime @updatedAt

  @@map("settings")
}

/// The reference week. 0 = Sunday .. 6 = Saturday, matching JS getDay().
model WeeklySchedule {
  id        String  @id @default(cuid())
  dayOfWeek Int     @unique
  isClosed  Boolean @default(false)
  /// [{"open":"10:00","close":"13:00"}, ...] — at most 3 slots, validated in
  /// lib/schedule/validate.ts before it ever reaches the database.
  slots     Json    @default("[]")

  @@map("weekly_schedules")
}

/// A single dated departure from the reference week. Covers both "today is
/// different" and "the 24th of December will be different".
model ScheduleException {
  id        String          @id @default(cuid())
  date      DateTime        @unique @db.Date
  isClosed  Boolean         @default(true)
  slots     Json?
  reason    ExceptionReason @default(TEMPORARY)
  noteFr    String?
  noteEn    String?
  source    ExceptionSource @default(MANUAL)
  createdBy String?
  createdAt DateTime        @default(now())
  updatedAt DateTime        @updatedAt

  @@index([date])
  @@map("schedule_exceptions")
}

/// A closure spanning several days. Read directly by the resolver at priority
/// rank 2; deliberately never expanded into ScheduleException rows.
model VacationPeriod {
  id        String   @id @default(cuid())
  startDate DateTime @db.Date
  endDate   DateTime @db.Date
  labelFr   String
  labelEn   String?
  createdBy String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([startDate, endDate])
  @@map("vacation_periods")
}

/// Local cache of the rolling 12-month holiday calendar. `isAutoClosed` is the
/// shop's decision, not the API's: some public holidays are trading days.
model PublicHoliday {
  id              String   @id @default(cuid())
  date            DateTime @db.Date
  nameFr          String
  nameEn          String
  nationwide      Boolean  @default(true)
  subdivisionCode String
  source          String   @default("openholidaysapi.org")
  syncedAt        DateTime @default(now())
  isAutoClosed    Boolean  @default(true)

  @@unique([date, subdivisionCode])
  @@index([date])
  @@map("public_holidays")
}

/// Free-text notice shown at the bottom of the screen, French then English.
model Message {
  id                String            @id @default(cuid())
  textFr            String
  textEn            String?
  translationStatus TranslationStatus @default(PENDING)
  startsAt          DateTime?
  endsAt            DateTime?
  priority          Int               @default(0)
  isActive          Boolean           @default(true)
  createdBy         String?
  createdAt         DateTime          @default(now())
  updatedAt         DateTime          @updatedAt

  @@index([isActive, priority])
  @@map("messages")
}

/// A reusable phrase, written once and offered wherever a message is composed.
///
/// The shop writes the same handful of notices over and over — "fermeture
/// exceptionnelle", "congés d'été", "ouverture retardée". Keeping them here
/// means they are translated once, worded consistently, and correctable in one
/// place rather than in whichever exception happens to carry a typo.
model MessageTemplate {
  id                String            @id @default(cuid())
  textFr            String
  textEn            String?
  translationStatus TranslationStatus @default(PENDING)
  /// Ordering in the picker; the most-used phrases belong at the top.
  sortOrder         Int               @default(0)
  createdBy         String?
  createdAt         DateTime          @default(now())
  updatedAt         DateTime          @updatedAt

  @@unique([textFr])
  @@index([sortOrder])
  @@map("message_templates")
}

/// Keyed by sha256 of the French source, so the same text is never paid for
/// twice. The model is part of the key: a model change invalidates nothing,
/// it simply misses and re-translates.
model TranslationCache {
  id             String   @id @default(cuid())
  sourceHash     String
  targetLang     String   @default("en")
  sourceText     String
  translatedText String
  model          String
  createdAt      DateTime @default(now())

  @@unique([sourceHash, targetLang, model])
  @@map("translation_cache")
}

model AuditLog {
  id        String   @id @default(cuid())
  userEmail String
  action    String
  entity    String
  entityId  String?
  diff      Json?
  createdAt DateTime @default(now())

  @@index([createdAt])
  @@map("audit_logs")
}

/// One e-ink panel. Registered on its first /api/setup call; the access token
/// is only ever stored as a SHA-256 digest.
model Device {
  id              String    @id @default(cuid())
  macAddress      String    @unique
  friendlyId      String    @unique
  apiKeyHash      String
  label           String    @default("Vitrine")
  isActive        Boolean   @default(true)
  lastSeenAt      DateTime?
  fwVersion       String?
  batteryVoltage  Float?
  percentCharged  Int?
  rssi            Int?
  /// Content hash of the image last handed to the device. The firmware skips
  /// the redraw when the filename is unchanged, which is where the battery
  /// life actually comes from.
  lastFilename    String?
  lastRefreshRate Int?
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  logs DeviceLog[]

  @@map("devices")
}

/// Firmware-side logs, posted to /api/log. Purged after 30 days by the daily job.
model DeviceLog {
  id        String         @id @default(cuid())
  deviceId  String
  level     DeviceLogLevel @default(INFO)
  message   String
  payload   Json?
  createdAt DateTime       @default(now())

  device Device @relation(fields: [deviceId], references: [id], onDelete: Cascade)

  @@index([deviceId, createdAt])
  @@map("device_logs")
}

/// A rendered panel image, addressed by the hash of its own bytes.
///
/// Stored rather than regenerated on demand because the device fetches the
/// image in a second request, moments after being told its name: a restart or
/// a data change in between would otherwise hand it a 404. Old rows are pruned
/// by the daily job.
model ScreenImage {
  /// SHA-256 of `bytes`, truncated; also the filename given to the firmware.
  hash      String   @id
  format    String
  bytes     Bytes
  createdAt DateTime @default(now())

  @@index([createdAt])
  @@map("screen_images")
}

/// Last outcome of each background sync, so the UI can show "last successful
/// sync" instead of failing silently.
model SyncState {
  key           String    @id
  lastSuccessAt DateTime?
  lastErrorAt   DateTime?
  lastError     String?
  updatedAt     DateTime  @updatedAt

  @@map("sync_states")
}