schema.prisma

datasource db {
    provider   = "postgresql"
    url        = env("DATABASE_URL")
    extensions = [pgcrypto, citext, uuidOssp(map: "uuid-ossp")]
}

generator client {
    provider        = "prisma-client-js"
    previewFeatures = ["extendedWhereUnique", "postgresqlExtensions", "clientExtensions"]
}

generator erd {
    provider  = "prisma-erd-generator"
    output    = "./db-schema.svg"
    theme     = "forest"
    tableOnly = true
}

model Currency {
    code          String          @id @db.Citext
    name          String
    accounts      Account[]
    moneyTransfer MoneyTransfer[]
    bankAccount   BankAccount[]
    invoice       Invoice[]

    @@map("currency")
}

model Label {
    name           String          @id @db.Citext
    description    String
    accounts       Account[]       @relation("label_account")
    initiatives    Initiative[]    @relation("label_initiative")
    agreements     Agreement[]     @relation("label_agreement")
    invoices       Invoice[]       @relation("label_invoice")
    moneyTransfers MoneyTransfer[] @relation("label_transfer")
    shareTransfers ShareTransfer[] @relation("label_share")
    shareTypes     ShareType[]     @relation("label_share_type")

    @@map("label")
}

model Tenant {
    id             String          @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name           String
    status         TenantStatus    @default(active)
    permalink      String          @unique @db.Citext
    users          TenantOnUsers[]
    accounts       Account[]
    decisions      Decision[]
    agreements     Agreement[]
    initiatives    Initiative[]
    shareTypes     ShareType[]
    moneyTransfers MoneyTransfer[]
    shareTransfers ShareTransfer[]
    invoices       Invoice[]

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("tenant")
}

model User {
    id        String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    email     String  @unique @db.Citext
    password  String
    role      AppRole @default(member)
    firstName String  @map("first_name")
    lastName  String  @map("last_name")
    avatarUrl String? @map("avatar_url")

    tenants       TenantOnUsers[]
    accounts      Account[]
    posts         Post[]
    initiatives   Initiative[]
    agreementRows AgreementRow[]

    questions            Question[]
    reactions            Reaction[]
    proposals            Proposal[]
    objections           Objection[]
    outcomes             Outcome[]
    startedDecisions     Decision[]  @relation("driver")
    facilitatedDecisions Decision[]  @relation("facilitator")
    comments             Comment[]
    pollVotes            PollVote[]

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("user")
}

model TenantOnUsers {
    id               String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    tenantId         String  @map("tenant_id") @db.Uuid
    userId           String  @map("user_id") @db.Uuid
    assignedByUserId String? @default(dbgenerated("active_user_id()")) @map("assigned_by_user_id") @db.Uuid
    tenant           Tenant  @relation(fields: [tenantId], references: [id])
    user             User    @relation(fields: [userId], references: [id])

    createdAt DateTime @default(now()) @map("created_at")

    @@unique([tenantId, userId])
    @@map("tenant_user")
}

model Account {
    id                String          @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name              String
    userId            String?         @map("user_id") @db.Uuid
    initiativeId      String?         @map("initiative_id") @db.Uuid
    tenantId          String          @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    bankAccountId     String?         @db.Uuid
    currencyCode      String          @map("currency_code") @db.Citext
    user              User?           @relation(fields: [userId], references: [id])
    initiative        Initiative?     @relation(fields: [initiativeId], references: [id])
    tenant            Tenant          @relation(fields: [tenantId], references: [id])
    currency          Currency        @relation(fields: [currencyCode], references: [code])
    labels            Label[]         @relation("label_account")
    bankAccount       BankAccount?    @relation(fields: [bankAccountId], references: [id])
    incomingTransfers MoneyTransfer[] @relation("incoming_transfer")
    outgoingTransfers MoneyTransfer[] @relation("outgoing_transfer")

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@unique([userId, name])
    @@map("account")
}

model MoneyTransfer {
    id            String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    fromAccountId String    @map("from_account_id") @db.Uuid
    toAccountId   String    @map("to_account_id") @db.Uuid
    amount        Int       @default(0) @map("amount")
    currencyCode  String    @map("currency_code") @db.Citext
    tenantId      String    @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    tenant        Tenant    @relation(fields: [tenantId], references: [id])
    currency      Currency  @relation(fields: [currencyCode], references: [code])
    fromAccount   Account   @relation("incoming_transfer", fields: [fromAccountId], references: [id])
    toAccount     Account   @relation("outgoing_transfer", fields: [toAccountId], references: [id])
    invoice       Invoice[]
    labels        Label[]   @relation("label_transfer")

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("transfer_money")
}

model ShareTransfer {
    id            String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    fromAccountId String    @map("from_account_id") @db.Uuid
    toAccountId   String    @map("to_account_id") @db.Uuid
    amount        Int       @default(0) @map("amount")
    shareTypeId   String    @map("share_type_id") @db.Uuid
    tenantId      String    @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    tenant        Tenant    @relation(fields: [tenantId], references: [id])
    shareType     ShareType @relation(fields: [shareTypeId], references: [id])
    labels        Label[]   @relation("label_share")

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("transfer_share")
}

model Invoice {
    id              String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    invoiceNumber   String        @map("invoice_number")
    moneyTransferId String        @map("money_transfer_id") @db.Uuid
    totalAmount     Int           @default(0) @map("total_amount")
    vatAmount       Int           @default(0) @map("vat_amount")
    currencyCode    String        @map("currency_code") @db.Citext
    tenantId        String        @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    rows            InvoiceRow[]
    currency        Currency      @relation(fields: [currencyCode], references: [code])
    tenant          Tenant        @relation(fields: [tenantId], references: [id])
    moneyTransfer   MoneyTransfer @relation(fields: [moneyTransferId], references: [id])

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")
    labels     Label[]  @relation("label_invoice")

    @@map("invoice")
}

model InvoiceRow {
    id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    description String
    invoiceId   String    @map("invoice_id") @db.Uuid
    agreementId String    @map("agreement_id") @db.Uuid
    quantity    Int       @default(0)
    amount      Int       @default(0) @map("amount")
    invoice     Invoice   @relation(fields: [invoiceId], references: [id])
    agreement   Agreement @relation(fields: [agreementId], references: [id])

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("invoice_row")
}

model BankAccount {
    id                String            @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name              String
    status            BankAccountStatus @default(new)
    ibanNumber        String            @db.VarChar(34)
    accountHolderName String            @map("account_holder_name")
    accountHolderType AccountHolderType @map("account_holder_type")
    bankName          String?           @map("bank_name") @db.VarChar(50)
    bankIdCode        String?           @map("bank_id_code") @db.VarChar(50)
    fingerprint       String?           @db.VarChar(100)
    currencyCode      String            @map("currency_code") @db.Citext
    currency          Currency          @relation(fields: [currencyCode], references: [code])
    accounts          Account[]

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("bank_account")
}

model Agreement {
    id           String       @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name         String
    description  String
    tenantId     String       @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    initiativeId String?      @map("initiative_id") @db.Uuid
    tenant       Tenant       @relation(fields: [tenantId], references: [id])
    initiative   Initiative?  @relation(fields: [initiativeId], references: [id])
    labels       Label[]      @relation("label_agreement")
    invoiceRows  InvoiceRow[]

    createdAt    DateTime       @default(now()) @map("created_at")
    updatedAt    DateTime       @updatedAt @map("updated_at")
    isArchived   Boolean        @default(false) @map("is_archived")
    isDeleted    Boolean        @default(false) @map("is_deleted")
    AgreementRow AgreementRow[]

    @@unique([initiativeId, name])
    @@map("agreement")
}

model AgreementRow {
    id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name        String
    description String
    userId      String    @map("user_id") @db.Uuid
    agreementId String    @map("agreement_id") @db.Uuid
    user        User      @relation(fields: [userId], references: [id])
    agreement   Agreement @relation(fields: [agreementId], references: [id])

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@unique([agreementId, name])
    @@map("agreement_row")
}

model Initiative {
    id              String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name            String
    tenantId        String      @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    createdByUserId String      @default(dbgenerated("active_user_id()")) @map("created_by_user_id") @db.Uuid
    createdByUser   User        @relation(fields: [createdByUserId], references: [id])
    tenant          Tenant      @relation(fields: [tenantId], references: [id])
    agreements      Agreement[]
    accounts        Account[]
    shareTypes      ShareType[]
    labels          Label[]     @relation("label_initiative")

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("initiative")
}

model ShareType {
    id              String          @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name            String          @unique
    tenantId        String          @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    initiativeId    String?         @map("initiative_id") @db.Uuid
    ratio           Int             @default(0) @map("ratio")
    createdByUserId String          @default(dbgenerated("active_user_id()")) @map("created_by_user_id") @db.Uuid
    initiative      Initiative?     @relation(fields: [initiativeId], references: [id])
    tenant          Tenant          @relation(fields: [tenantId], references: [id])
    shareTransfers  ShareTransfer[]
    labels          Label[]         @relation("label_share_type")

    createdAt  DateTime @default(now()) @map("created_at")
    updatedAt  DateTime @updatedAt @map("updated_at")
    isArchived Boolean  @default(false) @map("is_archived")
    isDeleted  Boolean  @default(false) @map("is_deleted")

    @@map("share_type")
}

model Post {
    id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    published Boolean
    title     String
    content   String?
    authorId  String?  @map("author_id") @db.Uuid
    createdAt DateTime @default(now()) @map("created_at")
    updatedAt DateTime @updatedAt @map("updated_at")
    author    User?    @relation(fields: [authorId], references: [id])

    @@map("post")
}


model Decision {
    id            String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    title         String
    description   String
    state         DecisionState @default(draft)
    tenant        Tenant        @relation(fields: [tenantId], references: [id])
    tenantId      String        @default(dbgenerated("active_tenant_id()")) @map("tenant_id") @db.Uuid
    createdAt     DateTime      @default(now()) @map("created_at")
    driver        User          @relation("driver", fields: [driverId], references: [id])
    driverId      String        @map("driver_id") @db.Uuid
    facilitator   User?         @relation("facilitator", fields: [facilitatorId], references: [id])
    facilitatorId String?       @map("facilitator_id") @db.Uuid
    rounds        Round[]
    isArchived    Boolean       @default(false) @map("is_archived")
    isDeleted     Boolean       @default(false) @map("is_deleted")

    @@map("decision")
}

model Round {
    id         String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    round      DecisionRound @default(initial)
    createdAt  DateTime      @default(now()) @map("created_at")
    decision   Decision      @relation(fields: [decisionId], references: [id])
    decisionId String        @map("decision_id") @db.Uuid

    proposals  Proposal[]
    reactions  Reaction[]
    questions  Question[]
    objections Objection[]
    outcomes   Outcome[]

    @@map("round")
}

model Proposal {
    id           String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    challenge    String
    opportunity  String
    state        ProposalState @default(draft)
    reviewPeriod Int           @default(0) @map("review_period")
    reviewAt     DateTime?     @map("review_at")
    createdAt    DateTime      @default(now()) @map("created_at")
    author       User          @relation(fields: [authorId], references: [id])
    authorId     String        @map("author_id") @db.Uuid

    round        Round       @relation(fields: [roundId], references: [id])
    roundId      String      @map("round_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("proposal")
}

model Reaction {
    id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    content   String
    createdAt DateTime @default(now()) @map("created_at")
    author    User     @relation(fields: [authorId], references: [id])
    authorId  String   @map("author_id") @db.Uuid

    round        Round       @relation(fields: [roundId], references: [id])
    roundId      String      @map("round_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("reaction")
}

model Question {
    id        String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    content   String
    state     QuestionState @default(draft)
    createdAt DateTime      @default(now()) @map("created_at")
    author    User          @relation(fields: [authorId], references: [id])
    authorId  String        @map("author_id") @db.Uuid
    polls     Poll[]

    round        Round       @relation(fields: [roundId], references: [id])
    roundId      String      @map("round_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("question")
}

model Objection {
    id        String         @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    content   String
    state     ObjectionState @default(draft)
    createdAt DateTime       @default(now()) @map("created_at")
    author    User           @relation(fields: [authorId], references: [id])
    authorId  String         @map("author_id") @db.Uuid

    round        Round       @relation(fields: [roundId], references: [id])
    roundId      String      @map("round_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("objection")
}

model Outcome {
    id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    content   String
    createdAt DateTime @default(now()) @map("created_at")
    author    User     @relation(fields: [authorId], references: [id])
    authorId  String   @map("author_id") @db.Uuid

    round        Round       @relation(fields: [roundId], references: [id])
    roundId      String      @map("round_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("outcome")
}

model Poll {
    id        String       @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    type      PollType
    createdAt DateTime     @default(now()) @map("created_at")
    options   PollOption[]
    votes     PollVote[]

    question     Question    @relation(fields: [questionId], references: [id])
    questionId   String      @map("question_id") @db.Uuid
    discussion   Discussion? @relation(fields: [discussionId], references: [id])
    discussionId String?     @map("discussion_id") @db.Uuid

    @@map("poll")
}

model PollOption {
    id        String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    name      String
    createdAt DateTime   @default(now()) @map("created_at")
    poll      Poll       @relation(fields: [pollId], references: [id])
    pollId    String     @map("poll_id") @db.Uuid
    votes     PollVote[]

    @@map("poll_option")
}

model PollVote {
    id        String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    value     Json?      @db.JsonB
    createdAt DateTime   @default(now()) @map("created_at")
    author    User       @relation(fields: [authorId], references: [id])
    authorId  String     @map("author_id") @db.Uuid
    pollId    String     @map("poll_id") @db.Uuid
    poll      Poll       @relation(fields: [pollId], references: [id])
    option    PollOption @relation(fields: [optionId], references: [id])
    optionId  String     @map("option_id") @db.Uuid

    @@map("poll_vote")
}

model Discussion {
    id         String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    createdAt  DateTime    @default(now()) @map("created_at")
    comments   Comment[]
    proposals  Proposal[]
    questions  Question[]
    reactions  Reaction[]
    objections Objection[]
    outcomes   Outcome[]
    polls      Poll[]

    @@map("discussion")
}

model Comment {
    id           String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    content      String
    author       User       @relation(fields: [authorId], references: [id])
    authorId     String     @map("author_id") @db.Uuid
    discussion   Discussion @relation(fields: [discussionId], references: [id])
    discussionId String     @map("discussion_id") @db.Uuid
    createdAt    DateTime   @default(now()) @map("created_at")

    @@map("comment")
}

enum TenantStatus {
    active
    suspended
    disabled

    @@map("tenant_status")
}

enum AppRole {
    anonymous
    member
    viewer
    client
    accountant
    admin
    superadmin

    @@map("app_role")
}

enum DecisionRound {
    initial
    proposal
    question
    reaction
    objection

    @@map("decision_round")
}

enum DecisionState {
    draft
    accepted
    archived
    closed

    @@map("decision_state")
}

enum QuestionState {
    draft
    asked
    answered

    @@map("question_state")
}

enum ObjectionState {
    draft
    sent
    resolved

    @@map("objection_state")
}

enum ProposalState {
    draft
    accepted
    rejected

    @@map("proposal_state")
}

enum PollType {
    binary
    single
    multiple
    score

    @@map("poll_type")
}

enum AccountHolderType {
    individual
    company

    @@map("account_holder_type")
}

enum BankAccountStatus {
    new
    validated
    verified
    verification_failed
    errored

    @@map("bank_account_status")
}