Database Schema Design

// ==========================================
// 1. NHÓM BẢNG QUẢN LÝ NHÂN SỰ (EMPLOYEES)
// ==========================================

model Employee {
  id                    Int              @id @unique @default(autoincrement())
  isDeleted             Boolean          @default(false) @map("is_deleted")
  departmentId          Int              @map("department_id")
  jobTitleId            Int?             @map("job_title_id")
  employeeCode          String?          @unique @map("employee_code")
  firstName             String?          @map("first_name")
  lastName              String?          @map("last_name")
  email                 String?
  phone                 String?
  status                Int              @default(1)
  userId                Int?             @unique @map("user_id")
  fullName              String?          @map("full_name")
  createdAt             DateTime         @default(now()) @map("created_at")
  updatedAt             DateTime         @default(now()) @updatedAt @map("updated_at")

  skills                EmployeeSkill[]  @relation("skillEmployeeRelation")
  idCodes               EmployeeIdCode[] @relation("employeeIdCodeRelation")
  user                  User?            @relation("employeeUserRelation", fields: [userId], references: [id])
  
  @@map("employees")
}

model EmployeeSkill {
  id         Int        @id @unique @default(autoincrement())
  isDeleted  Boolean    @default(false) @map("is_deleted")
  employeeId Int        @map("employee_id")
  skillId    Int        @map("skill_id")
  
  employee   Employee   @relation("skillEmployeeRelation", fields: [employeeId], references: [id])
  skill      MasterData @relation("employeeSkillRelation", fields: [skillId], references: [id])

  @@map("employee_skills")
}

model EmployeeIdCode {
  id         Int       @id @unique @default(autoincrement())
  isDeleted  Boolean   @default(false) @map("is_deleted")
  employeeId Int       @map("employee_id")
  cardType   String?   @map("card_type")
  isActive   Boolean   @default(true) @map("is_active")

  employee   Employee  @relation("employeeIdCodeRelation", fields: [employeeId], references: [id])

  @@map("employee_id_codes")
}

// ==========================================
// 2. NHÓM BẢNG PHÂN QUYỀN (USERS & AUTHORIZATION)
// ==========================================

model User {
  id           Int         @id @unique @default(autoincrement())
  isDeleted    Boolean     @default(false) @map("is_deleted")
  username     String      @unique
  password     String
  isSuperAdmin Boolean     @default(false) @map("is_super_admin")
  status       Int         @default(1)
  createdAt    DateTime    @default(now()) @map("created_at")
  updatedAt    DateTime    @default(now()) @updatedAt @map("updated_at")

  employee     Employee?   @relation("employeeUserRelation")
  userTokens   UserToken[]
  userRoles    UserRole[]  @relation("UserToUserRole")

  @@map("users")
}

model Role {
  id          Int              @id @unique @default(autoincrement())
  isDeleted   Boolean          @default(false) @map("is_deleted")
  name        String           @unique
  description String?
  
  rolePermissions RolePermission[]
  userRoles       UserRole[]       @relation("RoleToUserRole")

  @@map("roles")
}

model UserRole {
  uuid      String   @id @unique @default(uuid()) @map("uuid")
  userId    Int      @map("user_id")
  roleId    Int      @map("role_id")
  isDeleted Boolean  @default(false) @map("is_deleted")

  user User @relation("UserToUserRole", fields: [userId], references: [id])
  role Role @relation("RoleToUserRole", fields: [roleId], references: [id])

  @@map("user_roles")
}

model RolePermission {
  uuid           String   @id @unique @default(uuid()) @map("uuid")
  roleId         Int      @map("role_id")
  permissionCode String   @map("permission_code")
  isDeleted      Boolean  @default(false) @map("is_deleted")

  role Role @relation(fields: [roleId], references: [id])

  @@unique([roleId, permissionCode])
  @@map("role_permissions")
}

model UserToken {
  id           Int      @id @unique @default(autoincrement())
  isDeleted    Boolean  @default(false) @map("is_deleted")
  userId       Int      @map("user_id")
  accessToken  String   @map("access_token") @db.Text
  refreshToken String   @map("refresh_token") @db.Text

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

  @@map("user_tokens")
}