DiscordTickets/db/sqlite/schema.prisma

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

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

model ArchivedChannel {
    channelId String
    createdAt DateTime @default(now())
    name      String
    ticket    Ticket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId  String

    @@id([ticketId, channelId])
    @@unique([ticketId, channelId])
    @@map("archivedChannels")
}

model ArchivedMessage {
    author    ArchivedUser @relation(fields: [ticketId, authorId], references: [ticketId, userId], onDelete: Cascade)
    authorId  String
    content   String
    createdAt DateTime     @default(now())
    deleted   Boolean      @default(false)
    edited    Boolean      @default(false)
    id        String       @id
    external  Boolean      @default(false)
    ticket    Ticket       @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId  String

    @@map("archivedMessages")
}

model ArchivedRole {
    archivedUsers ArchivedUser[]
    colour        String         @default("5865F2") // 7289DA
    createdAt     DateTime       @default(now())
    name          String
    roleId        String
    ticket        Ticket         @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId      String

    @@id([ticketId, roleId])
    @@unique([ticketId, roleId])
    @@map("archivedRoles")
}

model ArchivedUser {
    archivedMessages ArchivedMessage[]
    avatar           String?
    bot              Boolean           @default(false)
    createdAt        DateTime          @default(now())
    discriminator    String?
    displayName      String?
    role             ArchivedRole?     @relation(fields: [ticketId, roleId], references: [ticketId, roleId], onDelete: Cascade)
    roleId           String?
    ticket           Ticket            @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId         String
    userId           String
    username         String?

    @@id([ticketId, userId])
    @@unique([ticketId, userId])
    @@map("archivedUsers")
}

model Category {
    channelName     String
    claiming        Boolean    @default(false)
    createdAt       DateTime   @default(now())
    cooldown        Int?
    customTopic     String?
    description     String
    discordCategory String
    emoji           String
    enableFeedback  Boolean    @default(false)
    guild           Guild      @relation(fields: [guildId], references: [id], onDelete: Cascade)
    guildId         String
    id              Int        @id @default(autoincrement())
    image           String?
    memberLimit     Int        @default(1)
    name            String
    openingMessage  String
    pingRoles       String     @default("[]")
    questions       Question[]
    ratelimit       Int?
    requiredRoles   String     @default("[]")
    requireTopic    Boolean    @default(false)
    staffRoles      String
    tickets         Ticket[]
    totalLimit      Int        @default(50)

    @@map("categories")
}

model Feedback {
    comment   String?
    createdAt DateTime @default(now())
    guild     Guild    @relation(fields: [guildId], references: [id], onDelete: Cascade)
    guildId   String
    rating    Int
    ticket    Ticket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId  String   @id
    user      User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
    userId    String?

    @@map("feedback")
}

model Guild {
    autoClose     Int        @default(43200000)
    autoTag       String     @default("[]")
    archive       Boolean    @default(true)
    blocklist     String     @default("[]")
    categories    Category[]
    claimButton   Boolean    @default(false)
    closeButton   Boolean    @default(false)
    createdAt     DateTime   @default(now())
    errorColour   String     @default("Red")
    feedback      Feedback[]
    footer        String?    @default("Discord Tickets by eartharoid")
    id            String     @id
    locale        String     @default("en-GB")
    logChannel    String?
    primaryColour String     @default("#009999")
    staleAfter    Int?
    successColour String     @default("Green")
    tags          Tag[]
    tickets       Ticket[]
    workingHours  String     @default("[\"UTC\", [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"], [\"00:00\",\"23:59\"]]")

    @@map("guilds")
}

model Question {
    answers     QuestionAnswer[]
    createdAt   DateTime         @default(now())
    id          String           @id @default(uuid())
    category    Category         @relation(fields: [categoryId], references: [id], onDelete: Cascade)
    categoryId  Int
    label       String
    maxLength   Int?             @default(4000)
    minLength   Int?             @default(0)
    options     String           @default("[]")
    order       Int
    placeholder String?
    required    Boolean          @default(true)
    style       Int              @default(2)
    type        String           @default("TEXT")
    value       String?

    @@map("questions")
}

model QuestionAnswer {
    createdAt  DateTime @default(now())
    id         Int      @id @default(autoincrement())
    ticket     Ticket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
    ticketId   String
    question   Question @relation(fields: [questionId], references: [id], onDelete: Cascade)
    questionId String
    user       User     @relation(fields: [userId], references: [id], onDelete: Cascade)
    userId     String
    value      String?

    @@map("questionAnswers")
}

model Tag {
    content   String
    createdAt DateTime @default(now())
    guild     Guild    @relation(fields: [guildId], references: [id], onDelete: Cascade)
    guildId   String
    id        Int      @id @default(autoincrement())
    name      String
    regex     String?

    @@unique([guildId, name])
    @@map("tags")
}

model Ticket {
    archivedChannels    ArchivedChannel[]
    archivedMessages    ArchivedMessage[]
    archivedRoles       ArchivedRole[]
    archivedUsers       ArchivedUser[]
    category            Category?         @relation(fields: [categoryId], references: [id], onDelete: Cascade)
    categoryId          Int?
    claimedBy           User?             @relation(name: "TicketsClaimedByUser", fields: [claimedById], references: [id])
    claimedById         String?
    closedAt            DateTime?
    closedBy            User?             @relation(name: "TicketsClosedByUser", fields: [closedById], references: [id])
    closedById          String?
    closedReason        String?
    createdAt           DateTime          @default(now())
    createdBy           User              @relation(name: "TicketsCreatedByUser", fields: [createdById], references: [id])
    createdById         String
    deleted             Boolean           @default(false)
    feedback            Feedback?
    firstResponseAt     DateTime?
    guild               Guild             @relation(fields: [guildId], references: [id], onDelete: Cascade)
    guildId             String
    id                  String            @id
    lastMessageAt       DateTime?
    messageCount        Int?
    number              Int
    open                Boolean           @default(true)
    openingMessageId    String
    pinnedMessageIds    String            @default("[]")
    priority            String?
    referencedBy        Ticket[]          @relation("TicketsReferencedByTicket")
    referencesMessageId String?
    referencesTicket    Ticket?           @relation(name: "TicketsReferencedByTicket", fields: [referencesTicketId], references: [id], onDelete: SetNull)
    referencesTicketId  String?
    topic               String?
    questionAnswers     QuestionAnswer[]

    @@unique([guildId, number])
    @@map("tickets")
}

model User {
    createdAt       DateTime         @default(now())
    feedback        Feedback[]
    id              String           @id
    messageCount    Int              @default(0)
    ticketsCreated  Ticket[]         @relation("TicketsCreatedByUser")
    ticketsClosed   Ticket[]         @relation("TicketsClosedByUser")
    ticketsClaimed  Ticket[]         @relation("TicketsClaimedByUser")
    questionAnswers QuestionAnswer[]

    @@map("users")
}