from django.db import models
from django.conf import settings
from django.contrib.auth import get_user_model

User = get_user_model()

# ============================================================
# GEO STRUCTURE
# ============================================================

# In models.py - Update the existing SiteSetting model or add this enhanced version
# 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

# models.py
from django.db import models
from django.core.validators import FileExtensionValidator

class SiteSetting(models.Model):
    # Basic Information
    site_name = models.CharField(max_length=200, default="PRIME MINISTER'S OFFICE-RALG")
    site_title = models.CharField(max_length=200, default="Regional Examinations Results")
    site_tagline = models.CharField(max_length=500, blank=True, default="Quality Education • Digital Systems • Transparency")
    about = models.TextField(blank=True, help_text="About the institution")
    
    # Logo & Branding
    logo = models.ImageField(upload_to='settings/logos/', blank=True, null=True)
    logo_light = models.ImageField(upload_to='settings/logos/', blank=True, null=True, help_text="Logo for dark backgrounds")
    favicon = models.ImageField(upload_to='settings/favicon/', blank=True, null=True, validators=[FileExtensionValidator(['ico', 'png'])])
    
    # Header Settings
    header_text = models.TextField(blank=True, help_text="HTML supported")
    header_image = models.ImageField(upload_to='settings/headers/', blank=True, null=True)
    header_bg_color = models.CharField(max_length=20, default="#0066B3", blank=True)
    
    # Footer Settings
    footer_text = models.TextField(blank=True, help_text="HTML supported - address, contact info")
    footer_copyright = models.CharField(max_length=200, default="© {year} All Rights Reserved")
    footer_links = models.JSONField(default=list, blank=True, help_text='Format: [{"title": "About", "url": "/about"}]')
    
    # Contact Information
    contact_email = models.EmailField(blank=True)
    contact_phone = models.CharField(max_length=50, blank=True)
    contact_address = models.TextField(blank=True)
    contact_po_box = models.CharField(max_length=50, blank=True, help_text="P.O. Box")
    
    # Social Media
    facebook_url = models.URLField(blank=True)
    twitter_url = models.URLField(blank=True)
    instagram_url = models.URLField(blank=True)
    youtube_url = models.URLField(blank=True)
    whatsapp_url = models.URLField(blank=True)
    
    # Custom Code
    custom_css = models.TextField(blank=True)
    custom_js = models.TextField(blank=True)
    
    # SEO
    meta_description = models.TextField(blank=True, max_length=500)
    meta_keywords = models.TextField(blank=True)
    
    # Status
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True, null=True, blank=True)
    
    class Meta:
        verbose_name = "Site Setting"
        verbose_name_plural = "Site Settings"
    
    def __str__(self):
        return self.site_name

    @classmethod
    def get_active(cls):
        """Get the active site settings or create default"""
        try:
            # Get the first active setting
            return cls.objects.filter(is_active=True).first()
        except:
            # If none exists, create default
            return cls.objects.create(
                site_name="PRIME MINISTER'S OFFICE-RALG",
                site_title="Regional Examinations Results",
                site_tagline="Quality Education • Digital Systems • Transparency",
                is_active=True
            )

    def save(self, *args, **kwargs):
        # Ensure only one record exists
        if not self.pk and SiteSetting.objects.exists():
            raise ValueError("Only one SiteSetting instance can exist")
        super().save(*args, **kwargs)


class Gallery(models.Model):
    title = models.CharField(max_length=200)
    description = models.TextField(blank=True)
    image = models.ImageField(upload_to='gallery/')
    is_active = models.BooleanField(default=True)
    order_by = models.IntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    
    class Meta:
        ordering = ['order_by', '-created_at']
        verbose_name_plural = "Gallery"
    
    def __str__(self):
        return self.title


class News(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    summary = models.CharField(max_length=300, blank=True)
    image = models.ImageField(upload_to='news/', blank=True, null=True)
    is_active = models.BooleanField(default=True)
    is_featured = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True, null=True, blank=True)
    
    class Meta:
        ordering = ['-created_at']
        verbose_name_plural = "News"
    
    def __str__(self):
        return self.title
    
    def save(self, *args, **kwargs):
        if not self.summary and self.content:
            self.summary = self.content[:300]
        super().save(*args, **kwargs)

















        
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):

    # NEW FIELDS - Add these
    title = models.CharField(max_length=500, blank=True, null=True, 
                             help_text="Full project title for reports and certificates")
    project_place = models.CharField(max_length=200, blank=True, null=True,
                                     help_text="Location where the exam was conducted (e.g., Kigoma)")
    project_logo = models.ImageField(upload_to="project_logos/", blank=True, null=True,
                                     help_text="Logo to display on project reports")
    
    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 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)