Refugee-Integration.sql

Table User {
  Id uuid [pk]
  FirstName varchar
  LastName varchar
  Email varchar
  Phone varchar
  Password varchar
  Role Role
  Status UserStatus
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table Refugee {
 Id uuid [pk]
 UserId varchar 
 OriginCountry varchar
 Gender Gender
 Age int
 IdentificationNumber int
 RefugeeCamp uuid
 RegisteredDate datetime [default: `now()`]
 IntegratedDate datetime
 IsIntegrated boolean
 RegisteredBy uuid
}

Table RefugeeFocalPoint {
 Id uuid [pk]
 UserId varchar 
 IdentificationNumber int
 RefugeeCamp uuid
 CreatedAt datetime [default: `now()`]
 UpdatedAt datetime [default: `now()`]
}

Table RefugeeCamps {
 Id uuid [pk]
 Name varchar [not null]
 Location varchar [not null]
 CampLead varchar
 CreatedAt datetime [default: `now()`]
 UpdatedAt datetime [default: `now()`]
}

Table IntegrationRequests {
  Id uuid [pk]
  Title varchar [not null]
  Description varchar
  RequestLevel uuid
  ConsernedMinistry varchar
  ConsernedDistrict varchar
  ConsernedSector varchar
  RequestStatus RequestStatus
  IntegrationType uuid
  RequestedBy uuid
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`] 
}

Table IntegrationLevels {
  Id uuid [pk]
  Title varchar [not null]
  Description varchar
  InChargeRole Role
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table IntegrationTypes {
  Id uuid [pk]
  Title varchar [not null]
  Description varchar
  IntegrationRequirements text
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table ConsernedMinistries {
  Id uuid [pk]
  Name varchar
  Description varchar
  Location varchar
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table Districts {
  Id uuid [pk]
  Name varchar [not null]
  Region varchar
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table Sectors {
  Id uuid [pk]
  Name varchar [not null]
  DistrictId uuid
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table IntegrationStatus {
  Id uuid [pk]
  StatusName varchar [not null]
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table IntegrationComments {
  Id uuid [pk]
  RequestId uuid
  Comment text [not null]
  CommentedBy uuid
  CommentDate datetime [default: `now()`]
}

Table IntegrationDocuments {
  Id uuid [pk]
  RequestId uuid
  DocumentName varchar [not null]
  DocumentPath varchar [not null]
  UploadedBy uuid
  UploadDate datetime [default: `now()`]
}

Table RefugeeSkills {
  Id uuid [pk]
  RefugeeId uuid
  SkillName varchar [not null]
  SkillLevel int
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table IntegrationHistory {
  Id uuid [pk]
  RequestId uuid
  Action varchar [not null]
  ActionBy uuid
  ActionDate datetime [default: `now()`]
}

Table FollowUpActivities {
  Id uuid [pk]
  Name varchar [not null]
  Description text
  StartDate datetime
  EndDate datetime
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}


Table RefugeeFollowUp {
  Id uuid [pk]
  RefugeeId uuid
  ActivityId uuid
  FollowUpDate datetime [default: `now()`]
  AttendanceStatus varchar [not null]  
  Progress varchar  
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table FollowUpFeedback {
  Id uuid [pk]
  RefugeeId uuid
  ActivityId uuid
  Feedback text
  FeedbackDate datetime [default: `now()`]
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table FollowUpOutcomes {
  Id uuid [pk]
  RefugeeId uuid
  ActivityId uuid
  Outcome text
  OutcomeDate datetime [default: `now()`]
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table MinisterAdmins {
  Id uuid [pk]
  UserId uuid
  MinistryId uuid
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table DistrictAdmins {
  Id uuid [pk]
  UserId uuid
  DistrictId uuid
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}

Table SectorAdmins {
  Id uuid [pk]
  UserId uuid
  SectorId uuid
  CreatedAt datetime [default: `now()`]
  UpdatedAt datetime [default: `now()`]
}


Enum Role{
  ADMIN
  UNHCR_ADMIN
  CONCERNED_MINISTRY_ADMIN
  REFUGEE_FOCAL_POINT
  SECTOR_ADMIN
  DISTRICT_ADMIN
}

Enum UserStatus {
  ACTIVE
  INACTIVE
  APPROVED
  PENDING
  REJECTED
}

Enum Gender {
  MALE
  FEMALE
  OTHER
}

Enum RequestStatus {
  PENDING
  APPROVED
  REJECTED
}

// relations 
Ref: Refugee.UserId > User.Id
Ref: Refugee.RegisteredBy > User.Id
Ref: RefugeeCamps.CampLead > User.Id
Ref: RefugeeFocalPoint.UserId > User.Id
Ref: Refugee.RefugeeCamp > RefugeeCamps.Id
Ref: IntegrationRequests.RequestedBy > User.Id
Ref: IntegrationRequests.RequestLevel > IntegrationLevels.Id
Ref: IntegrationRequests.IntegrationType > IntegrationTypes.Id

Ref: IntegrationRequests.ConsernedMinistry > ConsernedMinistries.Id
Ref: IntegrationRequests.ConsernedDistrict > Districts.Id
Ref: IntegrationRequests.ConsernedSector > Sectors.Id
Ref: IntegrationRequests.RequestStatus > IntegrationStatus.Id
Ref: IntegrationComments.CommentedBy > User.Id
Ref: MinisterAdmins.MinistryId > ConsernedMinistries.Id
Ref: DistrictAdmins.DistrictId > Districts.Id
Ref: SectorAdmins.SectorId > Sectors.Id
Ref: IntegrationRequests.RequestedBy > Refugee.UserId
Ref: IntegrationComments.RequestId > IntegrationRequests.Id
Ref: IntegrationDocuments.RequestId > IntegrationRequests.Id
Ref: RefugeeSkills.RefugeeId > Refugee.Id
Ref: IntegrationHistory.RequestId > IntegrationRequests.Id

Ref: IntegrationHistory.ActionBy > User.Id
Ref: RefugeeFollowUp.RefugeeId > Refugee.Id
Ref: RefugeeFollowUp.ActivityId > FollowUpActivities.Id
Ref: FollowUpFeedback.RefugeeId > Refugee.Id
Ref: FollowUpFeedback.ActivityId > FollowUpActivities.Id
Ref: FollowUpOutcomes.RefugeeId > Refugee.Id
Ref: FollowUpOutcomes.ActivityId > FollowUpActivities.Id