schema.prisma

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

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

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

// This is an optional zod helper script:
// It will generate zod schemas based on this prisma-schema in `/db/zod-schema-inspiration`
// We can use those, to improve our own zod schema files.
// However, we cannot have this active all the time, due to an issue with blitz.
// How to use:
// 1. Remove comment below
// 2. Use `npx blitz prisma generate` (mind the `x`)
// 3. Copy what you need to your own schema
// 4. Revert (1)
//
// generator zod {
//   provider                 = "zod-prisma"
//   output                   = "./zod-schema-inspiration"
//   relationModel            = true
//   modelCase                = "PascalCase"
//   modelSuffix              = "Model"
//   useDecimalJs             = true
//   imports                  = null
//   prismaJsonNullability    = true
// }

// --------------------------------------

model User {
  id             Int          @id @default(autoincrement())
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt
  //
  email          String       @unique
  hashedPassword String?
  role           UserRoleEnum @default(USER)
  firstName      String
  lastName       String
  phone          String?
  institution    String? // Organisation / Kommune

  tokens                Token[]
  sessions              Session[]
  projects              Project[]
  subsections           Subsection[]
  memberships           Membership[]
  Subsubsection         Subsubsection[]
  invites               Invite[]
  logEntries            LogEntry[]
  SurveyResponseComment SurveyResponseComment[]
}

enum UserRoleEnum {
  USER
  ADMIN
}

model Invite {
  id        Int                @id @default(autoincrement())
  createdAt DateTime           @default(now())
  updatedAt DateTime           @updatedAt
  //
  token     String             @unique
  status    InviteStatusEnum   @default(PENDING)
  //
  email     String
  role      MembershipRoleEnum @default(VIEWER)
  //
  projectId Int
  project   Project            @relation(fields: [projectId], references: [id])
  inviterId Int
  inviter   User               @relation(fields: [inviterId], references: [id])

  @@unique([email, projectId])
}

enum InviteStatusEnum {
  PENDING
  ACCEPTED
  EXPIRED
}

model Session {
  id                 Int       @id @default(autoincrement())
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
  expiresAt          DateTime?
  handle             String    @unique
  hashedSessionToken String?
  antiCSRFToken      String?
  publicData         String?
  privateData        String?

  user   User? @relation(fields: [userId], references: [id])
  userId Int?
}

model Token {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  hashedToken String
  type        String
  // See note below about TokenType enum
  // type        TokenType
  expiresAt   DateTime
  sentTo      String

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

  @@unique([hashedToken, type])
}

// NOTE: It's highly recommended to use an enum for the token type
//       but enums only work in Postgres.
//       See: https://blitzjs.com/docs/database-overview#switch-to-postgre-sql
// enum TokenType {
//   RESET_PASSWORD
// }

model Project {
  id              Int      @id @default(autoincrement())
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  //
  slug            String   @unique // shortTitle and URL Slug
  subTitle        String?
  description     String?
  logoSrc         String?
  partnerLogoSrcs String[]
  exportEnabled   Boolean  @default(false)
  //
  manager         User?    @relation(fields: [managerId], references: [id])
  managerId       Int?

  surveyResponseTopics  SurveyResponseTopic[]
  subsections           Subsection[]
  calendarEntries       CalendarEntry[]
  contacts              Contact[]
  uploads               Upload[]
  memberships           Membership[]
  operators             Operator[]
  surveys               Survey[]
  qualityLevels         QualityLevel[]
  subsubsectionStatuses SubsubsectionStatus[]
  subsubsectionTasks    SubsubsectionTask[]
  subsubsectionInfras   SubsubsectionInfra[]
  subsubsectionSpecials SubsubsectionSpecial[]
  networkHierarchies    NetworkHierarchy[]
  invites               Invite[]
  logEntries            LogEntry[]
}

model Membership {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  project   Project @relation(fields: [projectId], references: [id])
  projectId Int

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

  role MembershipRoleEnum @default(VIEWER)

  @@unique([projectId, userId])
}

enum MembershipRoleEnum {
  VIEWER
  EDITOR
}

model CalendarEntry {
  id           Int      @id @default(autoincrement())
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  //
  title        String
  startAt      DateTime
  locationName String?
  locationUrl  String?
  description  String?

  project   Project @relation(fields: [projectId], references: [id])
  projectId Int
}

model Contact {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  //
  firstName String?
  lastName  String
  email     String
  note      String?
  phone     String?
  role      String?

  project   Project @relation(fields: [projectId], references: [id])
  projectId Int

  @@unique([projectId, email])
}

enum LabelPositionEnum {
  top
  topRight
  right
  bottomRight
  bottom
  bottomLeft
  left
  topLeft
}

model Operator {
  id              Int              @id @default(autoincrement())
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
  //
  slug            String // shortTitle and URL Slug
  title           String
  //
  project         Project          @relation(fields: [projectId], references: [id])
  projectId       Int
  subsections     Subsection[]
  surveyResponses SurveyResponse[]

  @@unique([projectId, slug])
}

model Subsection {
  id                            Int               @id @default(autoincrement())
  createdAt                     DateTime          @default(now())
  updatedAt                     DateTime          @updatedAt
  //
  slug                          String // shortTitle and URL Slug
  start                         String
  end                           String
  order                         Int               @default(autoincrement())
  geometry                      Json
  labelPos                      LabelPositionEnum @default(top)
  description                   String?
  lengthKm                      Float // Kilometer
  priority                      PriorityEnum      @default(OPEN) // Priorität
  estimatedCompletionDateString String? // Jahresquartal der geplanten Fertigstellung als string Form "1-2023"

  //
  project               Project              @relation(fields: [projectId], references: [id])
  projectId             Int
  manager               User?                @relation(fields: [managerId], references: [id])
  managerId             Int?
  operator              Operator?            @relation(fields: [operatorId], references: [id])
  operatorId            Int?
  networkHierarchy      NetworkHierarchy?    @relation(fields: [networkHierarchyId], references: [id])
  networkHierarchyId    Int?
  SubsubsectionStatus   SubsubsectionStatus? @relation(fields: [subsubsectionStatusId], references: [id])
  subsubsectionStatusId Int?
  //

  subsubsections   Subsubsection[]
  stakeholdernotes Stakeholdernote[]
  uploads          Upload[]

  @@unique([projectId, slug])
  @@unique([projectId, order])
}

enum PriorityEnum {
  OPEN
  TOP //sehr hoch, hoch, mittel, niedrig
  HIGH
  MEDIUM
  LOW
}

model NetworkHierarchy {
  id          Int          @id @default(autoincrement())
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt
  //
  slug        String // shortTitle and URL Slug
  title       String
  //
  project     Project      @relation(fields: [projectId], references: [id])
  projectId   Int
  subsections Subsection[]

  @@unique([projectId, slug])
}

model Subsubsection {
  id              Int                   @id @default(autoincrement())
  createdAt       DateTime              @default(now())
  updatedAt       DateTime              @updatedAt
  //
  slug            String // shortTitle and URL Slug
  subTitle        String?
  type            SubsubsectionTypeEnum @default(ROUTE) // Führungform
  geometry        Json
  labelPos        LabelPositionEnum     @default(top)
  task            String? // Maßnahmentyp - UNUSED
  lengthKm        Float // Kilometer
  width           Float? // Meter
  widthExisting   Float? // Meter
  description     String? // Anmerkungen
  mapillaryKey    String?
  isExistingInfra Boolean?              @default(false) // Bestandsführung

  maxSpeed        Float? // Maximalgeschwindigkeit
  trafficLoad     Int? // Verkehrsbelastung
  trafficLoadDate DateTime? // Datum der Verkehrsbelastung

  planningPeriod                Float? //  Planungsdauer in Monaten
  constructionPeriod            Float? // Baudauer in Monaten
  estimatedCompletionDate       DateTime? // Jahresquartal der geplanten Fertigstellung als string Form "1-2023"
  //
  costEstimate                  Float? // Euro
  //
  planningCosts                 Float? //  Planungskosten
  deliveryCosts                 Float? //  Lieferkosten
  constructionCosts             Float? //  Baukosten
  landAcquisitionCosts          Float? //  Grunderwerbskosten
  expensesOfficialOrders        Float? //  Ausgaben aufgrund behördlicher Anordnungen
  expensesTechnicalVerification Float? //  Ausgaben für den fachtechnischen Nachweis usw.
  //
  nonEligibleExpenses           Float? //  Nicht zuwendungsfähige Ausgaben
  //
  revenuesEconomicIncome        Float? //  Erlöse und wirtschafltiche Einnahmen
  contributionsThirdParties     Float? //  Beiträge Dritter
  grantsOtherFunding            Float? //  Zuwendungen aus anderen Förderprogrammen
  ownFunds                      Float? //  Einsatz Eigenmittel

  qualityLevel          QualityLevel?        @relation(fields: [qualityLevelId], references: [id])
  qualityLevelId        Int?
  //
  manager               User?                @relation(fields: [managerId], references: [id])
  managerId             Int?
  //
  subsection            Subsection           @relation(fields: [subsectionId], references: [id])
  subsectionId          Int
  //
  uploads               Upload[]
  //
  SubsubsectionStatus   SubsubsectionStatus? @relation(fields: [subsubsectionStatusId], references: [id])
  subsubsectionStatusId Int?
  //
  SubsubsectionTask     SubsubsectionTask?   @relation(fields: [subsubsectionTaskId], references: [id])
  subsubsectionTaskId   Int?
  //
  SubsubsectionInfra    SubsubsectionInfra?  @relation(fields: [subsubsectionInfraId], references: [id])
  subsubsectionInfraId  Int?

  specialFeatures SubsubsectionSpecial[]

  @@unique([subsectionId, slug])
}

enum SubsubsectionTypeEnum {
  ROUTE // Regelführung Strecke RF
  AREA // Sonderführung Punkt (Bereich) SF
}

model QualityLevel {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  //
  slug           String // shortTitle and URL Slug
  title          String
  //
  project        Project         @relation(fields: [projectId], references: [id])
  projectId      Int
  subsubsections Subsubsection[]

  @@unique([projectId, slug])
}

model SubsubsectionStatus {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  //
  slug           String // shortTitle and URL Slug
  title          String
  //
  project        Project         @relation(fields: [projectId], references: [id])
  projectId      Int
  subsubsections Subsubsection[]
  Subsection     Subsection[]

  @@unique([projectId, slug])
}

// Maßnahmentyp
model SubsubsectionTask {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  //
  slug           String // shortTitle and URL Slug
  title          String
  //
  project        Project         @relation(fields: [projectId], references: [id])
  projectId      Int
  subsubsections Subsubsection[]

  @@unique([projectId, slug])
}

// Führungsform
model SubsubsectionInfra {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  //
  slug           String // shortTitle and URL Slug
  title          String
  //
  project        Project         @relation(fields: [projectId], references: [id])
  projectId      Int
  subsubsections Subsubsection[]

  @@unique([projectId, slug])
}

model SubsubsectionSpecial {
  id             Int             @id @default(autoincrement())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @updatedAt
  //
  slug           String // shortTitle and URL Slug
  title          String
  //
  project        Project         @relation(fields: [projectId], references: [id])
  projectId      Int
  //
  subsubsections Subsubsection[]

  @@unique([projectId, slug])
}

model Stakeholdernote {
  id           Int                       @id @default(autoincrement())
  createdAt    DateTime                  @default(now())
  updatedAt    DateTime                  @updatedAt
  title        String
  status       StakeholdernoteStatusEnum @default(PENDING)
  statusText   String?
  //
  subsection   Subsection                @relation(fields: [subsectionId], references: [id])
  subsectionId Int
}

enum StakeholdernoteStatusEnum {
  PENDING
  IN_PROGRESS
  DONE
  IRRELEVANT
}

model Upload {
  id              Int            @id @default(autoincrement())
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt
  //
  title           String
  externalUrl     String
  //
  project         Project        @relation(fields: [projectId], references: [id])
  projectId       Int
  subsection      Subsection?    @relation(fields: [subsectionId], references: [id])
  subsectionId    Int?
  Subsubsection   Subsubsection? @relation(fields: [subsubsectionId], references: [id])
  subsubsectionId Int?
}

model Survey {
  id                     Int             @id @default(autoincrement())
  createdAt              DateTime        @default(now())
  updatedAt              DateTime        @updatedAt
  startDate              DateTime?
  endDate                DateTime?
  slug                   String          @unique
  title                  String
  active                 Boolean         @default(false)
  interestedParticipants Int?
  surveyResultsUrl       String?
  //
  projectId              Int
  project                Project         @relation(fields: [projectId], references: [id])
  //
  surveySessions         SurveySession[]
}

model SurveySession {
  id        Int              @id @default(autoincrement())
  createdAt DateTime         @default(now())
  updatedAt DateTime         @updatedAt
  //
  survey    Survey           @relation(fields: [surveyId], references: [id])
  surveyId  Int
  //
  responses SurveyResponse[]
}

model SurveyResponse {
  id                     Int                                     @id @default(autoincrement())
  surveyPart             Int
  data                   String
  status                 String?
  note                   String?
  source                 SurveyResponseSourceEnum                @default(FORM)
  //
  surveySession          SurveySession                           @relation(fields: [surveySessionId], references: [id])
  surveySessionId        Int
  operatorId             Int?
  operator               Operator?                               @relation(fields: [operatorId], references: [id])
  //
  surveyResponseTopics   SurveyResponseTopicsOnSurveyResponses[]
  surveyResponseComments SurveyResponseComment[]
}

enum SurveyResponseSourceEnum {
  EMAIL
  LETTER
  FORM
}

model SurveyResponseTopic {
  id              Int                                     @id @default(autoincrement())
  title           String
  //
  project         Project                                 @relation(fields: [projectId], references: [id])
  projectId       Int
  surveyResponses SurveyResponseTopicsOnSurveyResponses[]

  @@unique([title, projectId])
}

model SurveyResponseTopicsOnSurveyResponses {
  surveyResponse        SurveyResponse      @relation(fields: [surveyResponseId], references: [id])
  surveyResponseId      Int
  surveyResponseTopic   SurveyResponseTopic @relation(fields: [surveyResponseTopicId], references: [id])
  surveyResponseTopicId Int

  @@id([surveyResponseId, surveyResponseTopicId])
}

model SurveyResponseComment {
  id               Int            @id @default(autoincrement())
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
  //
  author           User           @relation(fields: [userId], references: [id])
  userId           Int
  //
  surveyResponse   SurveyResponse @relation(fields: [surveyResponseId], references: [id], onDelete: Cascade)
  surveyResponseId Int
  //
  body             String
}

model LogEntry {
  id        Int          @id @default(autoincrement())
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
  //
  userId    Int?
  user      User?        @relation(fields: [userId], references: [id])
  projectId Int?
  project   Project?     @relation(fields: [projectId], references: [id])
  //
  logLevel  LogLevelEnum @default(INFO)
  message   String
  context   Json?

  // Maybe later…
  // @@index([createdAt])
  // @@index([userId])
  // @@index([projectId])
}

enum LogLevelEnum {
  INFO
  WARN
  ERROR
}