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}" # ============================================================ # 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) is_active = models.BooleanField(default=True) # 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"]), ]