erd.schema

//// ------------------------------------------------------
//// THIS FILE WAS AUTOMATICALLY GENERATED (DO NOT MODIFY)
//// ------------------------------------------------------

Project "WigglePaw" {
  database_type: 'postgresql'
  Note: ''
}

Table Account {
  id String [pk]
  userId String [not null]
  type String [not null]
  provider String [not null]
  providerAccountId String [not null]
  refresh_token String
  access_token String
  expires_at Int
  token_type String
  scope String
  id_token String
  session_state String
  user User [not null]

  indexes {
    (provider, providerAccountId) [unique]
  }
}

Table Session {
  id String [pk]
  sessionToken String [unique, not null]
  userId String [not null]
  expires DateTime [not null]
  user User [not null]
}

Table User {
  userId String [pk]
  petOwner PetOwner
  petSitter PetSitter
  username String [unique, not null]
  password String [not null]
  email String [unique, not null]
  emailVerified DateTime
  phoneNumber String
  address String
  imageUri String
  accounts Account [not null]
  sessions Session [not null]
  createdAt DateTime [default: `now()`, not null]
  salt String [not null]
  Admin Admin
  ReportTicket ReportTicket [not null]
  messages Message [not null]
  blockedUsers BlockedUser [not null]
  blockedBy BlockedUser [not null]
  mutedUser MutedUser [not null]
  mutedBy MutedUser [not null]
}

Table BlockedUser {
  blockedBy User [not null]
  blockedById String [not null]
  blockedUser User [not null]
  blockedUserId String [not null]
  createdAt DateTime [default: `now()`, not null]

  indexes {
    (blockedById, blockedUserId) [pk]
  }
}

Table MutedUser {
  mutedBy User [not null]
  mutedById String [not null]
  mutedUser User [not null]
  mutedUserId String [not null]
  createdAt DateTime [default: `now()`, not null]

  indexes {
    (mutedById, mutedUserId) [pk]
  }
}

Table PetOwner {
  userId String [pk]
  user User [not null]
  petTypes String[] [not null]
  firstName String [not null]
  lastName String [not null]
  pet Pet [not null]
  booking Booking [not null]
  review Review [not null]
  Chatroom Chatroom [not null]
  customerId String [unique, not null]
}

Table PetSitter {
  userId String [pk]
  user User [not null]
  freelancePetSitter FreelancePetSitter
  petHotel PetHotel
  booking Booking [not null]
  petTypes String[] [not null]
  verifyStatus Boolean [not null]
  certificationUri String
  startPrice Float
  endPrice Float
  post Post [not null]
  review Review [not null]
  avgRating Float
  reviewCount Int [not null, default: 0]
  Chatroom Chatroom [not null]
  ApprovalRequest ApprovalRequest [not null]
  recipientId String [unique, not null, note: 'Associated Omise\'s recipient ID (for getting paid)']
}

Table FreelancePetSitter {
  userId String [pk]
  petSitter PetSitter [not null]
  firstName String [not null]
  lastName String [not null]
}

Table PetHotel {
  userId String [pk]
  petSitter PetSitter [not null]
  businessLicenseUri String
  hotelName String [not null]
}

Table Booking {
  bookingId String [pk]
  petOwner PetOwner [not null]
  petSitter PetSitter [not null]
  petOwnerId String [not null]
  petSitterId String [not null]
  totalPrice Float [not null]
  startDate DateTime [not null]
  endDate DateTime [not null]
  numberOfPets Int [not null]
  pet Pet [not null]
  status BookingStatus [not null]
  note String
  createdAt DateTime [default: `now()`, not null]
}

Table Pet {
  petId String [pk]
  petOwnerId String [not null]
  petOwner PetOwner [not null]
  petType String [not null]
  name String
  sex String
  breed String
  weight Float
  booking Booking [not null]
  createdAt DateTime [default: `now()`, not null]
}

Table VerificationToken {
  identifier String [not null]
  token String [unique, not null]
  expires DateTime [not null]

  indexes {
    (identifier, token) [unique]
  }
}

Table Review {
  reviewId String [pk]
  petOwnerId String [not null]
  petOwner PetOwner [not null]
  petSitterId String [not null]
  petSitter PetSitter [not null]
  status ReviewStatus [not null]
  adminComment String
  text String
  rating Int [not null]
  createdAt DateTime [default: `now()`, not null]
}

Table Post {
  postId String [pk]
  petSitterId String [not null]
  petSitter PetSitter [not null]
  title String [not null]
  text String
  pictureUri String[] [not null]
  videoUri String
  createdAt DateTime [default: `now()`, not null]
}

Table Admin {
  userId String [pk]
  user User [not null]
  ActiveTickets ReportTicket [not null]
  ApprovalRequest ApprovalRequest [not null]
  Chatroom Chatroom [not null]
}

Table ReportTicket {
  ticketId String [pk]
  reporterId String [not null]
  reporter User [not null]
  title String [not null]
  description String
  adminId String
  admin Admin
  status ReportTicketStatus [not null]
  notes String
  createdAt DateTime [default: `now()`, not null]
  pictureUri String[] [not null]
}

Table ApprovalRequest {
  requestId String [pk]
  petSitterId String [unique, not null]
  petSitter PetSitter [not null]
  status ApprovalRequestStatus [not null]
  adminId String
  latestStatusUpdateby Admin
  latestStatusUpdateAt DateTime [not null]
  notes String
  createdAt DateTime [default: `now()`, not null]
}

Table Chatroom {
  chatroomId String [pk]
  user1Id String [not null]
  user2Id String [not null]
  messages Message [not null]
  PetOwner PetOwner
  petOwnerUserId String
  PetSitter PetSitter
  petSitterUserId String
  Admin Admin
  adminUserId String
}

Table Message {
  messageId String [pk]
  type MessageType [not null, default: 'text']
  data String [not null]
  senderId String [not null]
  sender User [not null]
  createdAt DateTime [default: `now()`, not null]
  chatroomId String [not null]
  Chatroom Chatroom
  read Boolean [not null, default: false]
}

Enum BookingStatus {
  requested
  accepted
  canceled
  rejected
  paid
}

Enum ReviewStatus {
  submitted
  pending
  resolved
}

Enum ReportTicketStatus {
  pending
  acked
  canceled
  resolved
}

Enum ApprovalRequestStatus {
  pending
  declined
  approved
}

Enum MessageType {
  text
  image
}

Ref: Account.userId > User.userId [delete: Cascade]

Ref: Session.userId > User.userId [delete: Cascade]

Ref: BlockedUser.blockedById > User.userId [delete: Cascade]

Ref: BlockedUser.blockedUserId > User.userId [delete: Cascade]

Ref: MutedUser.mutedById > User.userId [delete: Cascade]

Ref: MutedUser.mutedUserId > User.userId [delete: Cascade]

Ref: PetOwner.userId - User.userId [delete: Cascade]

Ref: PetSitter.userId - User.userId [delete: Cascade]

Ref: FreelancePetSitter.userId - PetSitter.userId [delete: Cascade]

Ref: PetHotel.userId - PetSitter.userId [delete: Cascade]

Ref: Booking.petOwnerId > PetOwner.userId [delete: Cascade]

Ref: Booking.petSitterId > PetSitter.userId [delete: Cascade]

Ref: Pet.petOwnerId > PetOwner.userId [delete: Cascade]

Ref: Review.petOwnerId > PetOwner.userId [delete: Cascade]

Ref: Review.petSitterId > PetSitter.userId [delete: Cascade]

Ref: Post.petSitterId > PetSitter.userId [delete: Cascade]

Ref: Admin.userId - User.userId [delete: Cascade]

Ref: ReportTicket.reporterId > User.userId [delete: Cascade]

Ref: ReportTicket.adminId > Admin.userId [delete: Cascade]

Ref: ApprovalRequest.petSitterId > PetSitter.userId [delete: Cascade]

Ref: ApprovalRequest.adminId > Admin.userId

Ref: Chatroom.petOwnerUserId > PetOwner.userId

Ref: Chatroom.petSitterUserId > PetSitter.userId

Ref: Chatroom.adminUserId > Admin.userId

Ref: Message.senderId > User.userId [delete: Cascade]

Ref: Message.chatroomId > Chatroom.chatroomId [delete: Cascade]