schema.prisma

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

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

datasource db {
  provider  = "postgresql"
}

model Member {
  id            String   @id @default(uuid())
  name          String
  email         String   @unique
  birth_date    DateTime? @db.Date
  phone         String? // Single phone number
  bio           String?
  profilePhoto  String? // URL to profile photo
  github        String?
  linkedin      String?
  twitter       String?
  geeksforgeeks String?
  leetcode      String?
  codechef      String?
  codeforces    String?
  passoutYear   DateTime?
  isApproved    Boolean  @default(false)
  isManager     Boolean  @default(false)
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Approval audit (self-reference)
  approvedBy      Member?  @relation("MemberApprovals", fields: [approvedById], references: [id])
  approvedById    String?
  approvedMembers Member[] @relation("MemberApprovals")

  // Authentication & Relations
  accounts     Account[]
  achievements MemberAchievement[]
  projects     MemberProject[]
  interviews   InterviewExperience[]
  completedQs  CompletedQuestion[]

  // Back-relations for audit fields
  createdTopics       Topic[]       @relation("TopicCreatedBy")
  updatedTopics       Topic[]       @relation("TopicUpdatedBy")
  createdQuestions    Question[]    @relation("QuestionCreatedBy")
  updatedQuestions    Question[]    @relation("QuestionUpdatedBy")
  createdAchievements Achievement[] @relation("AchievementCreatedBy")
  updatedAchievements Achievement[] @relation("AchievementUpdatedBy")
  createdProjects     Project[]     @relation("ProjectCreatedBy")
  updatedProjects     Project[]     @relation("ProjectUpdatedBy")
}

model Account {
  id                String    @id @default(uuid())
  provider          String
  providerAccountId String
  password          String? // for email/password auth
  accessToken       String?
  refreshToken      String?
  expiresAt         DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt

  member   Member @relation(fields: [memberId], references: [id], onDelete: Cascade)
  memberId String

  @@unique([provider, providerAccountId])
}

model Achievement {
  id          Int      @id @default(autoincrement())
  title       String
  description String
  achievedAt  DateTime
  imageUrl    String

  // Audit fields
  createdBy   Member?  @relation("AchievementCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)
  createdById String?
  createdAt   DateTime @default(now())
  updatedBy   Member?  @relation("AchievementUpdatedBy", fields: [updatedById], references: [id], onDelete: SetNull)
  updatedById String?
  updatedAt   DateTime @updatedAt

  members MemberAchievement[]
}

model MemberAchievement {
  member        Member      @relation(fields: [memberId], references: [id], onDelete: Cascade)
  memberId      String
  achievement   Achievement @relation(fields: [achievementId], references: [id], onDelete: Cascade)
  achievementId Int

  @@id([memberId, achievementId])
}

model Project {
  id        Int     @id @default(autoincrement())
  name      String
  imageUrl  String
  githubUrl String
  deployUrl String?

  // Audit fields
  createdBy   Member?  @relation("ProjectCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)
  createdById String?
  createdAt   DateTime @default(now())
  updatedBy   Member?  @relation("ProjectUpdatedBy", fields: [updatedById], references: [id], onDelete: SetNull)
  updatedById String?
  updatedAt   DateTime @updatedAt

  members MemberProject[]
}

model MemberProject {
  member    Member  @relation(fields: [memberId], references: [id], onDelete: Cascade)
  memberId  String
  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  projectId Int

  @@id([memberId, projectId])
}

model Topic {
  id          Int    @id @default(autoincrement())
  title       String
  description String

  // Audit fields
  createdBy   Member?  @relation("TopicCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)
  createdById String?
  createdAt   DateTime @default(now())
  updatedBy   Member?  @relation("TopicUpdatedBy", fields: [updatedById], references: [id], onDelete: SetNull)
  updatedById String?
  updatedAt   DateTime @updatedAt

  questions Question[]
}

model Question {
  id           Int        @id @default(autoincrement())
  questionName String
  difficulty   Difficulty
  link         String

  topic   Topic @relation(fields: [topicId], references: [id], onDelete: Cascade)
  topicId Int

  // Audit fields
  createdBy   Member?  @relation("QuestionCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)
  createdById String?
  createdAt   DateTime @default(now())
  updatedBy   Member?  @relation("QuestionUpdatedBy", fields: [updatedById], references: [id], onDelete: SetNull)
  updatedById String?
  updatedAt   DateTime @updatedAt

  completedBy CompletedQuestion[]
}

enum Difficulty {
  Easy
  Medium
  Hard
}

model InterviewExperience {
  id          Int     @id @default(autoincrement())
  company     String
  role        String
  verdict     Verdict
  content     String
  isAnonymous Boolean
  member      Member  @relation(fields: [memberId], references: [id], onDelete: Cascade)
  memberId    String
}

enum Verdict {
  Selected
  Rejected
  Pending
}

model CompletedQuestion {
  member     Member   @relation(fields: [memberId], references: [id], onDelete: Cascade)
  memberId   String
  question   Question @relation(fields: [questionId], references: [id], onDelete: Cascade)
  questionId Int

  @@id([memberId, questionId])
}