tremor-tracker/prisma/schema.prisma

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

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

// User model
model User {
    id              Int           @id @default(autoincrement())
    createdAt       DateTime      @default(now())
    name            String
    email           String        @unique
    passwordHash    String
    role            String        @default("GUEST") @db.VarChar(10) // Allowed: ADMIN, SCIENTIST, GUEST
    scientist       Scientist?    @relation
    purchasedOrders Order[]       @relation("UserOrders")
    requests        Request[]     @relation("UserRequests")
    earthquakes     Earthquake[]  @relation("UserEarthquakeCreator")
    observatories   Observatory[] @relation("UserObservatoryCreator")
    artefacts       Artefact[]    @relation("UserArtefactCreator")
}

model Request {
    id               Int      @id @default(autoincrement())
    createdAt        DateTime @default(now())
    requestType      String   @db.VarChar(20) // Allowed: NEW_USER, CHANGE_LEVEL, DELETE
    requestingUser   User     @relation("UserRequests", fields: [requestingUserId], references: [id])
    requestingUserId Int
    outcome          String   @default("IN_PROGRESS") @db.VarChar(20) // Allowed: FULFILLED, REJECTED, IN_PROGRESS, CANCELLED, OTHER
}

// Scientist model
model Scientist {
    id           Int         @id @default(autoincrement())
    createdAt    DateTime    @default(now())
    name         String
    level        String      @db.VarChar(10) // JUNIOR, SENIOR
    user         User        @relation(fields: [userId], references: [id])
    userId       Int         @unique
    superior     Scientist?  @relation("SuperiorRelation", fields: [superiorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
    superiorId   Int?
    subordinates Scientist[] @relation("SuperiorRelation")
}

model Earthquake {
    id            Int           @id @default(autoincrement())
    createdAt     DateTime      @default(now())
    updatedAt     DateTime      @updatedAt
    date          DateTime
    code          String        @unique
    magnitude     Float
    type          String // e.g. 'volcanic'
    latitude      Float
    longitude     Float
    location      String
    depth         String
    creatorId     Int?
    creator       User?         @relation("UserEarthquakeCreator", fields: [creatorId], references: [id])
    artefacts     Artefact[]
    observatories Observatory[] @relation("EarthquakeObservatory")
}

model Observatory {
    id                  Int          @id @default(autoincrement())
    createdAt           DateTime     @default(now())
    updatedAt           DateTime     @updatedAt
    name                String
    location            String
    longitude           Float
    latitude            Float
    dateEstablished     DateTime
    isFunctional        Boolean
    seismicSensorOnline Boolean      @default(true)
    creatorId           Int?
    creator             User?        @relation("UserObservatoryCreator", fields: [creatorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
    earthquakes         Earthquake[] @relation("EarthquakeObservatory")
}

model Artefact {
    id              Int        @id @default(autoincrement())
    createdAt       DateTime   @default(now())
    updatedAt       DateTime   @updatedAt
    name            String
    type            String     @db.VarChar(50) // Lava, Tephra, Ash, Soil
    warehouseArea   String
    description     String
    imageName       String
    earthquakeId    Int
    earthquake      Earthquake @relation(fields: [earthquakeId], references: [id])
    creatorId       Int?
    creator         User?      @relation("UserArtefactCreator", fields: [creatorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
    isRequired      Boolean    @default(true) @map("isRequired")
    dateAddedToShop DateTime?
    shopPrice       Float?
    isSold          Boolean    @default(false)
    orderId         Int?
    order           Order?     @relation("OrderArtefacts", fields: [orderId], references: [id], onDelete: NoAction, onUpdate: NoAction)
    isCollected     Boolean    @default(false)
}

model Pallet {
    id            Int      @id @default(autoincrement())
    createdAt     DateTime @default(now())
    updatedAt     DateTime @updatedAt
    warehouseArea String
    palletNote    String
}

model Order {
    id          Int        @id @default(autoincrement())
    createdAt   DateTime   @default(now())
    updatedAt   DateTime   @updatedAt
    orderNumber String
    userId      Int?
    user        User?      @relation("UserOrders", fields: [userId], references: [id], onDelete: SetNull)
    email       String?    // Non-account email for orders without a user account
    artefacts   Artefact[] @relation("OrderArtefacts")
}