from django.db import models from django.conf import settings from django.contrib.auth import get_user_model User = get_user_model() # ============================================================ # GEO STRUCTURE # ============================================================ class Region(models.Model): name = models.CharField(max_length=150, unique=True) code = models.CharField(max_length=50, blank=True, null=True) slug = models.SlugField(max_length=120, null=True, blank=True) # def save(self, *args, **kwargs): # if not self.slug: # self.slug = slugify(self.name) # super().save(*args, **kwargs) class Meta: db_table = "geo_region" ordering = ["name"] def __str__(self): return self.name class District(models.Model): region = models.ForeignKey( Region, on_delete=models.CASCADE, related_name="districts", db_index=True ) name = models.CharField(max_length=150) code = models.CharField(max_length=50, blank=True, null=True) slug = models.SlugField(max_length=120, null=True, blank=True) # def save(self, *args, **kwargs): # if not self.slug: # self.slug = slugify(self.name) # super().save(*args, **kwargs) class Meta: db_table = "geo_district" unique_together = ("region", "name") ordering = ["name"] indexes = [ models.Index(fields=["region", "name"]), ] def __str__(self): return f"{self.name} ({self.region.name})" class Ward(models.Model): district = models.ForeignKey( District, on_delete=models.CASCADE, related_name="wards", db_index=True ) name = models.CharField(max_length=150) class Meta: db_table = "geo_ward" unique_together = ("district", "name") ordering = ["name"] indexes = [ models.Index(fields=["district", "name"]), ] def __str__(self): return f"{self.name} ({self.district.name})" class School(models.Model): school_code = models.CharField(max_length=20, unique=True, db_index=True) school_regno = models.CharField( max_length=50, unique=True, null=True, blank=True ) name = models.CharField(max_length=255) name_short = models.CharField(max_length=255, blank=True, null=True) region = models.ForeignKey(Region, on_delete=models.PROTECT, db_index=True) district = models.ForeignKey(District, on_delete=models.PROTECT, db_index=True) ward = models.ForeignKey(Ward, on_delete=models.PROTECT, db_index=True) ownership = models.CharField( max_length=20, choices=[("GVT", "Government"), ("NON-GVT", "Private")] ) latitude = models.DecimalField(max_digits=9, decimal_places=6, null=True, blank=True) longitude = models.DecimalField(max_digits=9, decimal_places=6, null=True, blank=True) class Meta: db_table = "geo_school" ordering = ["school_code"] indexes = [ models.Index(fields=["region", "district"]), models.Index(fields=["district", "ward"]), ] def __str__(self): return f"{self.school_code} - {self.name}" # ============================================================ # USER PROFILE # ============================================================ class UserProfile(models.Model): ROLE_CHOICES = [ ("super", "Super Admin"), ("region", "Region Officer"), ("district", "District Officer"), ("ward", "Ward Officer"), ("school", "School Officer"), ] user = models.OneToOneField( settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="profile" ) role = models.CharField(max_length=20, choices=ROLE_CHOICES, db_index=True) photo = models.ImageField(upload_to="profile_images/", null=True, blank=True) phone = models.CharField(max_length=50, null=True, blank=True) region = models.ForeignKey(Region, on_delete=models.SET_NULL, null=True, blank=True) district = models.ForeignKey(District, on_delete=models.SET_NULL, null=True, blank=True) ward = models.ForeignKey(Ward, on_delete=models.SET_NULL, null=True, blank=True) school = models.ForeignKey(School, on_delete=models.SET_NULL, null=True, blank=True) class Meta: db_table = "user_profile" indexes = [ models.Index(fields=["role"]), ] def __str__(self): return f"{self.user.username} ({self.role})" # ============================================================ # EXAM TYPES # ============================================================ class ExamType(models.Model): code = models.CharField(max_length=20, unique=True) name = models.CharField(max_length=100) description = models.TextField(blank=True, null=True) year = models.PositiveIntegerField(db_index=True, null=True) is_active = models.BooleanField(default=True) class Meta: db_table = "exam_type" indexes = [ models.Index(fields=["is_active"]), ] def __str__(self): return f"{self.code} - {self.name}" # models.py class AcademicLevel(models.Model): code = models.CharField(max_length=20, unique=True) name = models.CharField(max_length=100) best_subjects_count = models.IntegerField(default=7) description = models.TextField(blank=True, null=True) is_active = models.BooleanField(default=True) def __str__(self): return self.name # ============================================================ # SUBJECTS # ============================================================ class ExamSubject(models.Model): subject_id = models.AutoField(primary_key=True) subject_code = models.CharField(max_length=50, unique=True, db_index=True) subject_name_sw = models.CharField(max_length=255) subject_name_eng = models.CharField(max_length=255) subject_level = models.CharField(max_length=255, null=True) subject_shortname = models.CharField(max_length=255, null=True) SUBJECT_TYPES = [ ("ARTS", "ARTS"), ("SCIENCE", "SCIENCE"), ("BUSINESS", "BUSINESS"), ("LANGUAGE", "LANGUAGE"), ("SOCIAL SCIENCE", "SOCIAL SCIENCE"), ("ENGINEERING", "ENGINEERING"), ("RELIGIOUS", "RELIGIOUS"), ("SPORTS", "SPORTS"), ("MATHEMATICS", "MATHEMATICS"), ] subject_type = models.CharField( max_length=20, default="ARTS", choices=SUBJECT_TYPES ) is_active = models.BooleanField(default=True) academic_level = models.ForeignKey( AcademicLevel, on_delete=models.CASCADE ) is_practical = models.BooleanField(default=False) # 1 or 0 is_principal = models.BooleanField(default=True) number_of_papers = models.IntegerField(default=1) # NEW FIELD color_code = models.CharField( max_length=7, default="#FFFFFF", help_text="HEX color code, e.g. #FFD700" ) class Meta: db_table = "exams_subject" ordering = ["subject_code"] indexes = [ models.Index(fields=["subject_code"]), models.Index(fields=["is_active"]), ] def __str__(self): return f"{self.subject_code} - {self.subject_name_eng}" # ============================================================ # EXAM CANDIDATE # ============================================================ class ExamCandidate(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True ) exam_type = models.ForeignKey(ExamType, on_delete=models.CASCADE, db_index=True) year = models.PositiveIntegerField(db_index=True) cand_no = models.CharField(max_length=20) prem_no = models.CharField(max_length=20, null=True, blank=True) sex = models.CharField(max_length=1) name = models.CharField(max_length=255) division = models.CharField(max_length=20, null=True, blank=True) points = models.CharField(max_length=20, null=True, blank=True) cand_comb = models.CharField(max_length=20, null=True, blank=True) position = models.CharField(max_length=20, null=True, blank=True) avg_grade = models.CharField(max_length=3, null=True, blank=True) avg_numeric = models.FloatField(default=0) status = models.CharField(max_length=20, null=True, blank=True) full_results = models.TextField(null=True, blank=True) class Meta: db_table = "exam_candidate" unique_together = ("cand_no", "year", "exam_type") indexes = [ models.Index(fields=["exam_type", "year"]), models.Index(fields=["school", "year", "exam_type"]), models.Index(fields=["cand_no", "year", "exam_type"]), ] def __str__(self): return f"{self.cand_no} - {self.exam_type.code} {self.year}" # ============================================================ # STUDENT SUBJECT MARKS (7M+ ROWS) # ============================================================ class StudentSubjectMark(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True ) exam_type = models.ForeignKey(ExamType, on_delete=models.CASCADE, db_index=True) year = models.PositiveIntegerField(db_index=True) cand_no = models.CharField(max_length=20, db_index=True) subject_code = models.CharField(max_length=50, db_index=True) numeric_score = models.FloatField(null=True, blank=True) grade = models.CharField(max_length=3, null=True, blank=True) class Meta: db_table = "student_subject_mark" unique_together = ("school", "year", "exam_type", "cand_no", "subject_code") indexes = [ models.Index(fields=["exam_type", "year"]), models.Index(fields=["school", "year", "exam_type"]), models.Index(fields=["cand_no", "year", "exam_type"]), models.Index(fields=["subject_code", "year", "exam_type"]), models.Index(fields=["school", "subject_code", "year", "exam_type"]), ] def __str__(self): return f"{self.cand_no} - {self.subject_code}" # ============================================================ # SCHOOL YEAR SUMMARY # ============================================================ class SchoolYearSummary(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True ) exam_type = models.ForeignKey(ExamType, on_delete=models.CASCADE, db_index=True) year = models.PositiveIntegerField(db_index=True) candidates = models.IntegerField(default=0) avg_numeric = models.FloatField(default=0) avg_gpa = models.FloatField(default=0) avg_grade = models.CharField(max_length=3, null=True, blank=True) pass_rate = models.FloatField(default=0) fail_rate = models.FloatField(default=0) status = models.CharField(max_length=20, null=True, blank=True) class Meta: db_table = "school_year_summary" unique_together = ("school", "exam_type", "year") indexes = [ models.Index(fields=["exam_type", "year"]), models.Index(fields=["school", "year", "exam_type"]), ] def __str__(self): return f"{self.school.name} - {self.exam_type.code} - {self.year}" # ============================================================ # SCHOOL SUBJECT PERFORMANCE # ============================================================ class SchoolSubjectPerformance(models.Model): school_code = models.CharField(max_length=20, db_index=True) year = models.PositiveIntegerField(db_index=True) subject_code = models.CharField(max_length=10, db_index=True) exam_type = models.ForeignKey( ExamType, on_delete=models.CASCADE, db_index=True ) total_std = models.PositiveIntegerField(default=0) total_pass = models.PositiveIntegerField(default=0) subj_gpa = models.DecimalField(max_digits=7, decimal_places=4, default=0) subj_avg = models.DecimalField(max_digits=6, decimal_places=4, default=0) subj_grade = models.CharField(max_length=2, blank=True, null=True) subj_status = models.CharField(max_length=20, blank=True, null=True) class Meta: db_table = "school_subject_performance" unique_together = ("school_code", "year", "subject_code", "exam_type") ordering = ["school_code", "subject_code"] indexes = [ models.Index(fields=["exam_type", "year"]), models.Index(fields=["school_code", "year", "exam_type"]), models.Index(fields=["subject_code", "year", "exam_type"]), ] def __str__(self): return f"{self.school_code} - {self.subject_code} - {self.exam_type.code} - {self.year}" # TIMETABLE class SchoolClass(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True, null=True, # 🔴 TEMPORARY blank=True # 🔴 TEMPORARY ) name = models.CharField(max_length=10) # FI, FII, FIII, FIV def __str__(self): return self.name class Stream(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True, null=True, # 🔴 TEMPORARY blank=True # 🔴 TEMPORARY ) school_class = models.ForeignKey(SchoolClass, on_delete=models.CASCADE) name = models.CharField(max_length=5) def __str__(self): return f"{self.school_class.name} {self.name}" class TimetableSubject(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True, null=True, # 🔴 TEMPORARY blank=True # 🔴 TEMPORARY ) exam_subject = models.ForeignKey( ExamSubject, on_delete=models.CASCADE, related_name="timetable_config" ) weekly_periods = models.IntegerField() morning_priority = models.BooleanField(default=False) def __str__(self): return self.exam_subject.subject_name_eng class Teacher(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True, null=True, # 🔴 TEMPORARY blank=True # 🔴 TEMPORARY ) name = models.CharField(max_length=100) def __str__(self): return self.name class TeacherSubject(models.Model): school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True, null=True, # 🔴 TEMPORARY blank=True # 🔴 TEMPORARY ) teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE) subject = models.ForeignKey( ExamSubject, # ✅ FIX HERE on_delete=models.CASCADE ) stream = models.ForeignKey(Stream, on_delete=models.CASCADE) class Meta: unique_together = ("teacher", "subject", "stream") class SchoolDivisionSummary(models.Model): year = models.PositiveIntegerField(db_index=True) school = models.ForeignKey( School, on_delete=models.CASCADE, to_field="school_code", db_column="school_code", db_index=True ) exam_type = models.ForeignKey(ExamType, on_delete=models.CASCADE, db_index=True) sex = models.CharField(max_length=1, db_index=True) # M / F division = models.CharField(max_length=5, db_index=True) # I, II, III, IV, 0 total = models.PositiveIntegerField() class Meta: db_table = "school_division_summary" unique_together = ("year", "school", "exam_type", "sex", "division") indexes = [ models.Index(fields=["year", "school"]), models.Index(fields=["year", "division"]), ] class SchoolSubjectGradeSummary(models.Model): year = models.PositiveIntegerField(db_index=True) school = models.ForeignKey( School, to_field="school_code", db_column="school_code", on_delete=models.CASCADE, db_index=True, ) exam_type = models.ForeignKey( ExamType, on_delete=models.CASCADE, db_index=True, ) subject_code = models.CharField(max_length=50, db_index=True) sex = models.CharField(max_length=1, db_index=True) grade = models.CharField(max_length=3, db_index=True) total = models.PositiveIntegerField() class Meta: db_table = "school_subject_grade_summary" unique_together = ( "year", "school", "subject_code", "sex", "grade", "exam_type", ) indexes = [ models.Index(fields=["year", "school"]), models.Index(fields=["subject_code", "year"]), models.Index(fields=["school", "subject_code", "year"]), ] class JointExamProject(models.Model): name = models.CharField(max_length=200) academic_level = models.ForeignKey( AcademicLevel, on_delete=models.CASCADE, db_index=True ) year = models.IntegerField(db_index=True) description = models.TextField(blank=True, null=True) is_active = models.BooleanField(default=True) is_live = models.BooleanField(default=False) created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True) def __str__(self): return f"{self.name} - {self.year}" class ExamSubjectPaper(models.Model): subject = models.ForeignKey( ExamSubject, on_delete=models.CASCADE, related_name="papers" ) paper_code = models.CharField(max_length=20, db_index=True) paper_name = models.CharField(max_length=100) max_marks = models.FloatField() def __str__(self): return f"{self.subject.subject_code} - {self.paper_code}" class ExamCombination(models.Model): academic_level = models.ForeignKey( AcademicLevel, on_delete=models.CASCADE ) code = models.CharField(max_length=20) # PCM, PCB name = models.CharField(max_length=100) combination_category = models.CharField(max_length=100,default='NULL') def __str__(self): return self.code class Meta: unique_together = ("academic_level", "code") indexes = [ models.Index(fields=["academic_level", "code"]) ] class CombinationPaper(models.Model): combination = models.ForeignKey( ExamCombination, on_delete=models.CASCADE, related_name="papers" ) paper = models.ForeignKey( ExamSubjectPaper, on_delete=models.CASCADE ) class Meta: unique_together = ("combination", "paper") class ProjectStudent(models.Model): project = models.ForeignKey( JointExamProject, on_delete=models.CASCADE, db_index=True ) school = models.ForeignKey( School, on_delete=models.PROTECT, db_index=True ) student_number = models.CharField(max_length=20, db_index=True) full_name = models.CharField(max_length=200) sex = models.CharField(max_length=1) combination = models.ForeignKey( ExamCombination, on_delete=models.PROTECT, null=True, blank=True ) class Meta: unique_together = ("project", "student_number") indexes = [ models.Index(fields=["project", "school"]), models.Index(fields=["student_number"]), ] def __str__(self): return f"{self.student_number} - {self.full_name}" class ExamScore(models.Model): project = models.ForeignKey( JointExamProject, on_delete=models.CASCADE, db_index=True ) student = models.ForeignKey( ProjectStudent, on_delete=models.CASCADE, db_index=True ) paper = models.ForeignKey( ExamSubjectPaper, on_delete=models.CASCADE, db_index=True ) score = models.CharField( max_length=10, null=True, blank=True, db_index=True ) updated_by = models.ForeignKey( User, on_delete=models.SET_NULL, null=True, blank=True ) score_status = models.IntegerField(default=0) # 0 = not updated, 1 = updat updated_at = models.DateTimeField(auto_now=True) class Meta: indexes = [ models.Index(fields=["project", "student", "paper"]), models.Index(fields=["student"]), models.Index(fields=["paper"]), ] unique_together = ("project", "student", "paper") class GradingRule(models.Model): academic_level = models.ForeignKey(AcademicLevel, on_delete=models.CASCADE) min_score = models.FloatField() max_score = models.FloatField() grade = models.CharField(max_length=5) points = models.FloatField() status = models.CharField(max_length=15,default='OK') weight = models.FloatField(default=1) # subject weight multiplier class Meta: ordering = ["-min_score"] class DivisionRule(models.Model): academic_level = models.ForeignKey(AcademicLevel, on_delete=models.CASCADE) min_points = models.FloatField() max_points = models.FloatField() division = models.CharField(max_length=20) class Meta: ordering = ["min_points"] class GpaRule(models.Model): academic_level = models.ForeignKey(AcademicLevel, on_delete=models.CASCADE) min_gpa = models.FloatField() max_gpa = models.FloatField() gpa_grade = models.CharField(max_length=5) gpa_points = models.FloatField() gpa_status = models.CharField(max_length=15) gpa_colour = models.CharField(max_length=15,default='#03fcfc') class Meta: ordering = ["-min_gpa"] class StudentResult(models.Model): project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) student = models.ForeignKey(ProjectStudent, on_delete=models.CASCADE) total_points = models.FloatField(default=0) gpa = models.FloatField(default=0) division = models.CharField(max_length=20, null=True) status = models.CharField(max_length=20, null=True) processed_at = models.DateTimeField(auto_now=True) class SchoolResult(models.Model): project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) school = models.ForeignKey(School, on_delete=models.CASCADE) total_students = models.IntegerField(default=0) pass_count = models.IntegerField(default=0) fail_count = models.IntegerField(default=0) average_gpa = models.FloatField(default=0) rank = models.IntegerField(null=True) processed_at = models.DateTimeField(auto_now=True) def __str__(self): return f"{self.school.name} - {self.project.name}" class ProcessedSubjectScore(models.Model): project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) student = models.ForeignKey(ProjectStudent, on_delete=models.CASCADE) subject = models.ForeignKey(ExamSubject, on_delete=models.CASCADE) raw_total = models.FloatField() # e.g. 120 max_total = models.FloatField() # e.g. 150 percentage_score = models.CharField(max_length=7) # e.g. 80.00 grade = models.CharField(max_length=2, null=True, blank=True) points = models.IntegerField(null=True, blank=True) processed_by = models.ForeignKey( User, null=True, blank=True, on_delete=models.SET_NULL ) processed_at = models.DateTimeField(auto_now=True) class Meta: unique_together = ("project", "student", "subject") class StudentFinalResult(models.Model): project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) student = models.ForeignKey(ProjectStudent, on_delete=models.CASCADE) std_average = models.CharField(max_length=10) std_averagegrade = models.CharField(max_length=10) total_points = models.IntegerField() division = models.CharField(max_length=10) full_results = models.CharField(max_length=255) processed_at = models.DateTimeField(auto_now=True) class SubjectProcessingRule(models.Model): subject = models.ForeignKey(ExamSubject, on_delete=models.CASCADE) academic_level = models.ForeignKey(AcademicLevel, on_delete=models.CASCADE) total_max_marks = models.FloatField() # e.g. 150, 250 formula = models.CharField(max_length=100) # Example: "(P1 + P2) / 150 * 100" class UploadProgress(models.Model): PROCESS_CHOICES = ( ("student", "Student"), ("school", "School"), ("subject", "Subject"), ) project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) user = models.ForeignKey(User, on_delete=models.CASCADE) process_type = models.CharField( max_length=20, choices=PROCESS_CHOICES, default='' ) total_rows = models.IntegerField(default=0) processed_rows = models.IntegerField(default=0) status = models.CharField(max_length=20, default="processing") report = models.TextField(blank=True) updated_at = models.DateTimeField(auto_now=True) class Meta: indexes = [ models.Index(fields=["project", "process_type"]), ] # class UploadProgress(models.Model): # project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) # user = models.ForeignKey(User, on_delete=models.CASCADE) # total_rows = models.IntegerField(default=0) # processed_rows = models.IntegerField(default=0) # status = models.CharField(max_length=20, default="processing") # updated_at = models.DateTimeField(auto_now=True) # report = models.TextField(blank=True, default="") class ExamSchoolResult(models.Model): project = models.ForeignKey( JointExamProject, on_delete=models.CASCADE, db_index=True ) school = models.ForeignKey( School, on_delete=models.CASCADE, db_index=True ) with_results = models.IntegerField( help_text="Total students with valid division / average" ) sch_avg = models.FloatField(null=True, blank=True) sch_avg_grd = models.CharField(max_length=10, null=True, blank=True) sch_avg_status = models.CharField(max_length=20, null=True, blank=True) sch_gpa = models.FloatField(null=True, blank=True) sch_gpa_grade = models.CharField(max_length=10, null=True, blank=True) sch_gpa_status = models.CharField(max_length=20, null=True, blank=True) sch_release_status = models.BooleanField(default=True) sch_rank_involvement = models.BooleanField(default=True) processed_at = models.DateTimeField(auto_now=True) class Meta: unique_together = ("project", "school") indexes = [ models.Index(fields=["project"]), models.Index(fields=["school"]), models.Index(fields=["sch_gpa"]), ] def __str__(self): return f"{self.school.school_code} – {self.project.name}" class ExamSubjectResult(models.Model): project = models.ForeignKey(JointExamProject, on_delete=models.CASCADE) subject = models.ForeignKey(ExamSubject, on_delete=models.CASCADE) school = models.ForeignKey(School, on_delete=models.CASCADE) with_results = models.IntegerField() # total students with valid results subj_avg = models.FloatField(null=True, blank=True) subj_avg_grd = models.CharField(max_length=5, null=True, blank=True) subj_avg_status = models.CharField(max_length=15, null=True, blank=True) subj_gpa = models.FloatField(null=True, blank=True) subj_gpa_grade = models.CharField(max_length=5, null=True, blank=True) subj_gpa_status = models.CharField(max_length=15, null=True, blank=True) subj_release_status = models.BooleanField(default=False) subj_rank_involvement = models.BooleanField(default=True) processed_at = models.DateTimeField(auto_now=True) class Meta: unique_together = ("project", "subject", "school") indexes = [ models.Index(fields=["project", "subject"]), models.Index(fields=["school"]), ] class SiteSetting(models.Model): site_name = models.CharField(max_length=255) about = models.TextField() logo = models.ImageField(upload_to="site/", blank=True, null=True) is_active = models.BooleanField(default=True) def __str__(self): return self.site_name class Gallery(models.Model): title = models.CharField(max_length=255) image = models.ImageField(upload_to="gallery/") description = models.CharField(max_length=255, blank=True) is_active = models.BooleanField(default=True) created_at = models.DateTimeField(auto_now_add=True) def __str__(self): return self.title class News(models.Model): title = models.CharField(max_length=255) content = models.TextField() image = models.ImageField(upload_to="news/", blank=True, null=True) is_active = models.BooleanField(default=True) created_at = models.DateTimeField(auto_now_add=True) def __str__(self): return self.title class Download(models.Model): title = models.CharField(max_length=255) file = models.FileField(upload_to="downloads/") description = models.TextField(blank=True) is_active = models.BooleanField(default=True) created_at = models.DateTimeField(auto_now_add=True) def __str__(self): return self.title # exams/models.py class VisitorLog(models.Model): ip = models.GenericIPAddressField() visited_at = models.DateTimeField(auto_now_add=True)