# finance/models.py
"""
Finance models aligned with the actual Rapha-Bethel BNPS fee structure:

ANGLO SECTION:
  Pre-Nursery:  Registration (New 15,000 / Old 10,000), Fees 130,000  → 1st 70,000 / 2nd 60,000
  Nursery 1&2:  Registration (New 15,000 / Old 10,000), Fees  95,000  → 1st 50,000 / 2nd 45,000
  Primary 1-5:  Registration (New 15,000 / Old 10,000), Fees  80,000  → 1st 45,000 / 2nd 35,000
  Primary 6:    Registration (New 15,000 / Old 10,000), Fees 145,000  → 1st 85,000 / 2nd 60,000

BILINGUAL SECTION:
  Maternelle (Moyenne & Grande): Registration (New 15,000 / Old 10,000), Fees 110,000 → 1st 60,000 / 2nd 50,000
  SIL–CM1:  Registration (New 15,000 / Old 10,000), Fees 100,000 → 1st 50,000 / 2nd 50,000
  CM2:       Registration (New 15,000 / Old 10,000), Fees 145,000 → 1st 85,000 / 2nd 60,000

EXTRA / OTHER INCOMES (sold items):
  Uniforms:    5,000 XAF
  Sportswear:  3,500 XAF
  Report Card: 1,000 XAF
  Pullovers:   5,000 XAF
"""

from django.db import models
from django.utils.translation import gettext_lazy as _
from django.core.validators import MinValueValidator
from django.utils import timezone
from datetime import datetime, timedelta
from students.models import Student
from academics.models import SchoolClass
from core.models import CustomUser
from .utils import generate_invoice_number


# ---------------------------------------------------------------------------
# SECTION & CLASS-LEVEL HELPERS
# (Mirrors the handwritten fee table exactly)
# ---------------------------------------------------------------------------

class SchoolSection(models.TextChoices):
    ANGLO    = 'anglo',    _('Anglo Section')
    BILINGUAL = 'bilingual', _('Bilingual Section')


class FeeClassGroup(models.TextChoices):
    """
    Groups that share the same fee tier (matches the handwritten table rows).
    """
    # Anglo groups
    PRE_NURSERY  = 'pre_nursery',  _('Pre-Nursery (Anglo)')
    NURSERY      = 'nursery',      _('Nursery 1 & 2 (Anglo)')
    PRIMARY_1_5  = 'primary_1_5',  _('Primary 1–5 (Anglo)')
    PRIMARY_6    = 'primary_6',    _('Primary 6 (Anglo)')
    # Bilingual groups
    MATERNELLE   = 'maternelle',   _('Maternelle / Moyenne & Grande Section (Bilingual)')
    SIL_CM1      = 'sil_cm1',      _('SIL – CM1 (Bilingual)')
    CM2          = 'cm2',          _('CM2 (Bilingual)')


# ---------------------------------------------------------------------------
# FeeStructure — one row per class-group, per academic year
# ---------------------------------------------------------------------------

class FeeStructure(models.Model):
    """
    Defines the official fee schedule for a class group and academic year.

    Each row maps directly to one row in the handwritten fee table and holds:
      • registration_new  — registration fee for NEW pupils
      • registration_old  — registration fee for OLD (returning) pupils
      • total_fees        — total annual school fees (tuition)
      • installment_1     — 1st installment amount
      • installment_2     — 2nd installment amount
      • section           — Anglo or Bilingual
      • class_group       — which tier/row of the fee table

    The constraint  installment_1 + installment_2 == total_fees  is enforced
    in clean() and highlighted in the admin.

    Additional fee types (uniform, sportswear, report card, pullover) are
    handled by the SchoolItemPrice model below.

    Attributes:
        name (str): Human-readable label, e.g. "Primary 1-5 (Anglo) 2025-2026"
        section (str): Anglo or Bilingual
        class_group (str): Fee tier from FeeClassGroup
        academic_year (str): e.g. "2025-2026"
        registration_new (Decimal): Registration fee for new pupils (XAF)
        registration_old (Decimal): Registration fee for old/returning pupils (XAF)
        total_fees (Decimal): Total annual school fees (XAF)
        installment_1 (Decimal): First installment amount (XAF)
        installment_2 (Decimal): Second installment amount (XAF)
        specific_classes (M2M): Optionally pin to exact SchoolClass records
        is_active (bool): Whether this row is currently in use
        description (str): Free-text notes
        created_by (FK): Staff who created/last edited the record
        created_at / updated_at: Timestamps
    """

    name = models.CharField(
        max_length=150,
        blank=True,
        verbose_name=_("Fee Structure Name"),
        help_text=_("Auto-generated if left blank")
    )

    section = models.CharField(
        max_length=20,
        choices=SchoolSection.choices,
        default=SchoolSection.ANGLO,
        verbose_name=_("School Section"),
    )

    class_group = models.CharField(
        max_length=20,
        choices=FeeClassGroup.choices,
        verbose_name=_("Class Group / Fee Tier"),
        help_text=_(
            "Select the row from the fee table this structure represents. "
            "e.g. 'Primary 1–5 (Anglo)'"
        ),
    )

    academic_year = models.CharField(
        max_length=9,
        default="2025-2026",
        verbose_name=_("Academic Year"),
        help_text=_("e.g. 2025-2026"),
    )

    # ── Registration fees ────────────────────────────────────────────────────
    registration_new = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=15000,
        validators=[MinValueValidator(0)],
        verbose_name=_("Registration Fee — New Pupil (XAF)"),
        help_text=_("From fee table: 15,000 XAF for new pupils"),
    )

    registration_old = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=10000,
        validators=[MinValueValidator(0)],
        verbose_name=_("Registration Fee — Old/Returning Pupil (XAF)"),
        help_text=_("From fee table: 10,000 XAF for returning pupils"),
    )

    # ── Annual school fees ───────────────────────────────────────────────────
    total_fees = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        validators=[MinValueValidator(0)],
        verbose_name=_("Total Annual Fees (XAF)"),
        help_text=_(
            "The 'Fees' column in the table — full-year tuition. "
            "Must equal installment_1 + installment_2."
        ),
    )

    installment_1 = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        validators=[MinValueValidator(0)],
        verbose_name=_("1st Installment (XAF)"),
    )

    installment_2 = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        validators=[MinValueValidator(0)],
        verbose_name=_("2nd Installment (XAF)"),
    )

    # ── Optional: pin to specific class records ──────────────────────────────
    specific_classes = models.ManyToManyField(
        SchoolClass,
        blank=True,
        verbose_name=_("Specific Classes"),
        related_name='fee_structures',
        help_text=_(
            "Optionally link to exact class records (e.g. CP-A, CP-B). "
            "Leave blank to apply to the whole class group."
        ),
    )

    # ── Admin / metadata ─────────────────────────────────────────────────────
    is_active = models.BooleanField(
        default=True,
        verbose_name=_("Active"),
    )

    description = models.TextField(
        blank=True,
        verbose_name=_("Notes"),
    )

    created_by = models.ForeignKey(
        CustomUser,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='fee_structures_created',
        verbose_name=_("Created By"),
    )

    created_at = models.DateTimeField(
        auto_now_add=True,
        null=True,
        blank=True,
        verbose_name=_("Created At"),
    )

    updated_at = models.DateTimeField(
        auto_now=True,
        verbose_name=_("Updated At"),
    )

    class Meta:
        verbose_name = _("Fee Structure")
        verbose_name_plural = _("Fee Structures")
        ordering = ['academic_year', 'section', 'class_group']
        unique_together = [['section', 'class_group', 'academic_year']]
        indexes = [
            models.Index(fields=['academic_year', 'section', 'class_group']),
            models.Index(fields=['is_active']),
        ]

    # ── Lifecycle ─────────────────────────────────────────────────────────────
    def save(self, *args, **kwargs):
        if not self.name:
            self.name = (
                f"{self.get_class_group_display()} — {self.academic_year}"
            )
        super().save(*args, **kwargs)

    def clean(self):
        """Check for duplicate payments within 5 minutes"""
        if self.pk:  # Only check on updates
            return
            
        five_min_ago = timezone.now() - timedelta(minutes=5)
        duplicate = Payment.objects.filter(
            invoice=self.invoice,
            amount=self.amount,
            payment_date=self.payment_date,
            created_at__gte=five_min_ago
        ).exists()
        
        if duplicate:
            raise ValidationError('A payment with these details was already recorded in the last 5 minutes.')
  
        from django.core.exceptions import ValidationError
        if self.installment_1 and self.installment_2 and self.total_fees:
            if self.installment_1 + self.installment_2 != self.total_fees:
                raise ValidationError(
                    _("Installment 1 + Installment 2 must equal Total Fees. "
                      f"({self.installment_1} + {self.installment_2} ≠ {self.total_fees})")
                )

    def __str__(self):
        return self.name or f"{self.get_class_group_display()} ({self.academic_year})"

    # ── Helper methods ────────────────────────────────────────────────────────
    def get_registration_fee(self, is_new_pupil: bool) -> int:
        """
        Return the correct registration fee for new or returning pupils.

        Args:
            is_new_pupil: True if the student is enrolling for the first time.

        Returns:
            int: Registration amount in XAF.
        """
        return int(self.registration_new if is_new_pupil else self.registration_old)

    def display_total(self) -> str:
        """Return formatted total fees string."""
        return f"{int(self.total_fees):,} XAF"

    def display_installments(self) -> str:
        """Return a human-readable installment summary."""
        return (
            f"1st: {int(self.installment_1):,} XAF  |  "
            f"2nd: {int(self.installment_2):,} XAF"
        )

    @classmethod
    def get_defaults(cls):
        """
        Return the canonical fee data from the handwritten tables so it can
        be loaded via a data migration or management command.

        Returns:
            list[dict]: One dict per row ready for get_or_create().
        """
        return [
            # ── ANGLO SECTION ──────────────────────────────────────────────
            {
                'section': 'anglo', 'class_group': 'pre_nursery',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 130000, 'installment_1': 70000, 'installment_2': 60000,
            },
            {
                'section': 'anglo', 'class_group': 'nursery',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 95000, 'installment_1': 50000, 'installment_2': 45000,
            },
            {
                'section': 'anglo', 'class_group': 'primary_1_5',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 80000, 'installment_1': 45000, 'installment_2': 35000,
            },
            {
                'section': 'anglo', 'class_group': 'primary_6',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 145000, 'installment_1': 85000, 'installment_2': 60000,
            },
            # ── BILINGUAL SECTION ──────────────────────────────────────────
            {
                'section': 'bilingual', 'class_group': 'maternelle',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 110000, 'installment_1': 60000, 'installment_2': 50000,
            },
            {
                'section': 'bilingual', 'class_group': 'sil_cm1',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 100000, 'installment_1': 60000, 'installment_2': 40000,
            },
            {
                'section': 'bilingual', 'class_group': 'cm2',
                'registration_new': 15000, 'registration_old': 10000,
                'total_fees': 145000, 'installment_1': 85000, 'installment_2': 60000,
            },
        ]


# ---------------------------------------------------------------------------
# SchoolItemPrice — uniforms, sportswear, report cards, pullovers
# (Image 2: "Extra / Other Incomes")
# ---------------------------------------------------------------------------

class SchoolItemPrice(models.Model):
    """
    Selling prices for school items such as uniforms and sportswear.

    These are the items listed under "Extra / Other Incomes" in the
    handwritten document (Image 2):
      - Uniforms:    5,000 XAF
      - Sportswear:  3,500 XAF
      - Report Card: 1,000 XAF
      - Pullovers:   5,000 XAF

    Attributes:
        item_type (str): Item category from ITEM_TYPE_CHOICES
        name (str): Display label (e.g. "School Uniform – Blue")
        price (Decimal): Current selling price in XAF
        academic_year (str): Year this price applies to
        is_active (bool): Whether the item is currently on sale
        description (str): Optional notes
        updated_at (datetime): Last price-change timestamp
    """

    ITEM_TYPE_CHOICES = [
        ('uniform',     _('Uniform')),
        ('sportswear',  _('Sportswear')),
        ('report_card', _('Report Card')),
        ('pullover',    _('Pullover')),
        ('other',       _('Other')),
    ]

    item_type = models.CharField(
        max_length=20,
        choices=ITEM_TYPE_CHOICES,
        verbose_name=_("Item Type"),
    )

    name = models.CharField(
        max_length=100,
        verbose_name=_("Item Name"),
        help_text=_("e.g. 'School Uniform', 'Sportswear Kit'"),
    )

    price = models.DecimalField(
        max_digits=8,
        decimal_places=0,
        validators=[MinValueValidator(0)],
        verbose_name=_("Selling Price (XAF)"),
    )

    academic_year = models.CharField(
        max_length=9,
        default="2025-2026",
        verbose_name=_("Academic Year"),
    )

    is_active = models.BooleanField(
        default=True,
        verbose_name=_("Available for Sale"),
    )

    description = models.TextField(
        blank=True,
        verbose_name=_("Description / Notes"),
    )

    updated_at = models.DateTimeField(
        auto_now=True,
        verbose_name=_("Last Updated"),
    )

    class Meta:
        verbose_name = _("School Item Price")
        verbose_name_plural = _("School Item Prices")
        ordering = ['item_type', 'name']
        unique_together = [['item_type', 'academic_year']]

    def __str__(self):
        return f"{self.name} — {int(self.price):,} XAF ({self.academic_year})"

    def display_price(self) -> str:
        return f"{int(self.price):,} XAF"

    @classmethod
    def get_defaults(cls):
        """
        Return the canonical item prices from Image 2.

        Returns:
            list[dict]: One dict per item ready for get_or_create().
        """
        return [
            {'item_type': 'uniform',     'name': 'Uniform',     'price': 5000},
            {'item_type': 'sportswear',  'name': 'Sportswear',  'price': 3500},
            {'item_type': 'report_card', 'name': 'Report Card', 'price': 1000},
            {'item_type': 'pullover',    'name': 'Pullover',    'price': 5000},
        ]


# ---------------------------------------------------------------------------
# StudentEnrollmentFee — per-student record of registration + tuition
# ---------------------------------------------------------------------------

class StudentEnrollmentFee(models.Model):
    """
    Records the fee obligation for one student for one academic year.

    Created when a student is admitted or re-enrolled. It references the
    correct FeeStructure row and stores whether the student is new or
    returning so the right registration fee is applied automatically.

    Attributes:
        student (FK): The enrolled student
        fee_structure (FK): The FeeStructure row that applies
        academic_year (str): Academic year
        is_new_pupil (bool): True = new admission (15,000 reg fee), False = returning (10,000)
        registration_fee (Decimal): Actual registration amount (auto-set on save)
        total_fees (Decimal): Total annual tuition (copied from fee_structure)
        installment_1_due (date): Due date for first installment
        installment_2_due (date): Due date for second installment
        notes (str): Additional notes
        created_at (datetime): Record creation timestamp
    """

    student = models.ForeignKey(
        Student,
        on_delete=models.CASCADE,
        related_name='enrollment_fees',
        verbose_name=_("Student"),
    )

    fee_structure = models.ForeignKey(
        FeeStructure,
        on_delete=models.PROTECT,
        related_name='enrollment_fees',
        verbose_name=_("Fee Structure"),
        help_text=_("Select the correct class-group fee tier for this student"),
    )

    academic_year = models.CharField(
        max_length=9,
        default="2025-2026",
        verbose_name=_("Academic Year"),
    )

    is_new_pupil = models.BooleanField(
        default=False,
        verbose_name=_("New Pupil?"),
        help_text=_(
            "Tick for new admissions (registration = 15,000 XAF). "
            "Untick for returning pupils (registration = 10,000 XAF)."
        ),
    )

    # Auto-filled from fee_structure on save
    registration_fee = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=0,
        verbose_name=_("Registration Fee Applied (XAF)"),
    )

    total_fees = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=0,
        verbose_name=_("Total Annual Fees (XAF)"),
    )

    installment_1_due = models.DateField(
        null=True,
        blank=True,
        verbose_name=_("1st Installment Due Date"),
    )

    installment_2_due = models.DateField(
        null=True,
        blank=True,
        verbose_name=_("2nd Installment Due Date"),
    )

    notes = models.TextField(
        blank=True,
        verbose_name=_("Notes"),
    )

    created_at = models.DateTimeField(
        auto_now_add=True,
        null=True,
        blank=True,
    )

    class Meta:
        verbose_name = _("Student Enrollment Fee")
        verbose_name_plural = _("Student Enrollment Fees")
        unique_together = [['student', 'academic_year']]
        ordering = ['-academic_year', 'student__last_name']

    def save(self, *args, **kwargs):
        """Auto-populate registration_fee and total_fees from fee_structure."""
        if self.fee_structure_id:
            self.registration_fee = self.fee_structure.get_registration_fee(self.is_new_pupil)
            self.total_fees = self.fee_structure.total_fees
        super().save(*args, **kwargs)

    def __str__(self):
        tag = "New" if self.is_new_pupil else "Returning"
        return (
            f"{self.student.get_full_name()} — "
            f"{self.fee_structure.get_class_group_display()} — "
            f"{self.academic_year} ({tag})"
        )

    @property
    def total_due(self) -> int:
        """Total amount the student owes: registration + tuition."""
        return int(self.registration_fee) + int(self.total_fees)


# ---------------------------------------------------------------------------
# Invoice — issued to a student for a term or for registration
# ---------------------------------------------------------------------------

class Invoice(models.Model):
    """
    Invoice issued to a student, covering one or more fee obligations.

    Possible invoice types:
      • 'registration'  — one-off registration fee (new or old pupil)
      • 'installment_1' — first tuition installment
      • 'installment_2' — second tuition installment
      • 'item_sale'     — uniform, sportswear, report card, pullover
      • 'other'         — miscellaneous

    The balance and status are recalculated automatically on save whenever
    a Payment is posted.

    Attributes:
        invoice_number (str): Unique auto-generated identifier (INV-YYYY-NNNN)
        student (FK): Student this invoice belongs to
        enrollment_fee (FK): The enrollment record this invoice is for (optional)
        invoice_type (str): Category of invoice
        academic_year (str): Academic year
        issue_date (date): Date issued
        due_date (date): Payment due date
        total_amount (Decimal): Amount due
        amount_paid (Decimal): Amount received so far
        balance (Decimal): Remaining amount (auto-calculated)
        status (str): Draft / Sent / Partial / Paid / Overdue / Cancelled
        notes (str): Free-text notes
        created_by (FK): Staff who created the invoice
        created_at / updated_at: Timestamps
        last_reminder_sent (date): Date of last payment reminder
    """

    INVOICE_TYPE_CHOICES = [
        ('registration',  _('Registration Fee')),
        ('installment_1', _('1st Installment (Tuition)')),
        ('installment_2', _('2nd Installment (Tuition)')),
        ('item_sale',     _('School Item Sale')),
        ('other',         _('Other')),
    ]

    STATUS_CHOICES = [
        ('draft',     _('Draft')),
        ('sent',      _('Sent')),
        ('partial',   _('Partially Paid')),
        ('paid',      _('Paid')),
        ('overdue',   _('Overdue')),
        ('cancelled', _('Cancelled')),
    ]

    invoice_number = models.CharField(
        max_length=20,
        unique=True,
        default=generate_invoice_number,
        verbose_name=_("Invoice Number"),
    )

    student = models.ForeignKey(
        Student,
        on_delete=models.CASCADE,
        related_name='invoices',
        verbose_name=_("Student"),
    )

    enrollment_fee = models.ForeignKey(
        StudentEnrollmentFee,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='invoices',
        verbose_name=_("Enrollment Fee Record"),
        help_text=_("Link to the student's enrollment fee for this year"),
    )

    invoice_type = models.CharField(
        max_length=20,
        choices=INVOICE_TYPE_CHOICES,
        default='installment_1',
        verbose_name=_("Invoice Type"),
    )

    academic_year = models.CharField(
        max_length=9,
        default="2025-2026",
        verbose_name=_("Academic Year"),
    )

    issue_date = models.DateField(
        default=timezone.now,
        verbose_name=_("Issue Date"),
    )

    due_date = models.DateField(
        verbose_name=_("Due Date"),
    )

    total_amount = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        verbose_name=_("Total Amount (XAF)"),
    )

    amount_paid = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=0,
        verbose_name=_("Amount Paid (XAF)"),
    )

    balance = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=0,
        verbose_name=_("Balance (XAF)"),
    )

    status = models.CharField(
        max_length=20,
        choices=STATUS_CHOICES,
        default='draft',
        verbose_name=_("Status"),
    )

    notes = models.TextField(
        blank=True,
        verbose_name=_("Notes"),
    )

    created_by = models.ForeignKey(
        CustomUser,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='invoices_created',
        verbose_name=_("Created By"),
    )

    created_at = models.DateTimeField(
        auto_now_add=True,
        null=True,
        blank=True,
        verbose_name=_("Created At"),
    )

    updated_at = models.DateTimeField(
        auto_now=True,
        verbose_name=_("Updated At"),
    )

    last_reminder_sent = models.DateField(
        null=True,
        blank=True,
        verbose_name=_("Last Reminder Sent"),
    )

    class Meta:
        verbose_name = _("Invoice")
        verbose_name_plural = _("Invoices")
        ordering = ['-issue_date', 'student__last_name']
        indexes = [
            models.Index(fields=['invoice_number']),
            models.Index(fields=['student', 'academic_year']),
            models.Index(fields=['status']),
            models.Index(fields=['due_date']),
        ]

    def save(self, *args, **kwargs):
        """Recalculate balance and auto-update status."""
        self.balance = self.total_amount - self.amount_paid

        if self.amount_paid >= self.total_amount:
            self.status = 'paid'
        elif self.amount_paid > 0:
            self.status = 'partial'
        elif self.due_date and self.due_date < timezone.now().date() and self.amount_paid == 0:
            self.status = 'overdue'

        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.invoice_number} — {self.student} — {self.get_invoice_type_display()}"

    @property
    def is_overdue(self) -> bool:
        return self.due_date < timezone.now().date() and self.balance > 0

    @property
    def payment_percentage(self) -> float:
        if self.total_amount == 0:
            return 0.0
        return round(float(self.amount_paid) / float(self.total_amount) * 100, 1)


# ---------------------------------------------------------------------------
# InvoiceItem — line items on an invoice
# ---------------------------------------------------------------------------

class InvoiceItem(models.Model):
    """
    Individual line item on an invoice.

    Can reference either a FeeStructure row or a SchoolItemPrice for
    uniform / sportswear / report-card / pullover sales.

    Attributes:
        invoice (FK): Parent invoice
        fee_structure (FK): Optional link to a FeeStructure (tuition fees)
        school_item (FK): Optional link to a SchoolItemPrice (item sales)
        description (str): Label shown on the invoice
        quantity (int): Units sold/charged
        unit_price (Decimal): Price per unit in XAF
        discount (Decimal): Discount off this line
        total (Decimal): Auto-calculated (qty × unit_price − discount)
    """

    invoice = models.ForeignKey(
        Invoice,
        on_delete=models.CASCADE,
        related_name='items',
        verbose_name=_("Invoice"),
    )

    fee_structure = models.ForeignKey(
        FeeStructure,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_("Fee Structure"),
    )

    school_item = models.ForeignKey(
        SchoolItemPrice,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_("School Item"),
        help_text=_("For uniform / sportswear / report card / pullover sales"),
    )

    description = models.CharField(
        max_length=200,
        verbose_name=_("Description"),
    )

    quantity = models.PositiveIntegerField(
        default=1,
        verbose_name=_("Quantity"),
    )

    unit_price = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        verbose_name=_("Unit Price (XAF)"),
    )

    discount = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        default=0,
        verbose_name=_("Discount (XAF)"),
    )

    total = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        verbose_name=_("Total (XAF)"),
    )

    class Meta:
        verbose_name = _("Invoice Item")
        verbose_name_plural = _("Invoice Items")

    def save(self, *args, **kwargs):
        self.total = (self.quantity * self.unit_price) - self.discount
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.description} × {self.quantity} = {int(self.total):,} XAF"


# ---------------------------------------------------------------------------
# Payment
# ---------------------------------------------------------------------------

class Payment(models.Model):
    """
    Records a payment made against an invoice.

    Supports cash, mobile money (MTN/Orange), bank transfer, and cheque.
    On save, the parent invoice's amount_paid and status are updated.

    Attributes:
        payment_number (str): Unique auto-generated identifier (PAY-YYYY-NNNN)
        invoice (FK): Invoice being paid
        student (FK): Student making the payment
        amount (Decimal): Amount paid in XAF
        payment_method (str): Cash / Mobile Money / Bank Transfer / Cheque
        payment_date (date): Date of payment
        mobile_money_provider (str): MTN or Orange (if mobile money)
        mobile_money_number (str): MoMo account used
        transaction_id (str): Bank / MoMo transaction reference
        bank_name (str): Bank name (if bank transfer)
        cheque_number (str): Cheque number (if cheque)
        status (str): Pending / Completed / Failed / Cancelled / Refunded
        receipt_number (str): Receipt number issued
        receipt_issued (bool): Whether receipt was printed/issued
        receipt_issued_date (date): Date receipt was issued
        notes (str): Free-text notes
        received_by (FK): Staff who accepted payment
        created_at / updated_at: Timestamps
    """

    PAYMENT_METHOD_CHOICES = [
        ('cash',          _('Cash')),
        ('mobile_money',  _('Mobile Money (MTN / Orange)')),
        ('bank_transfer', _('Bank Transfer')),
        ('cheque',        _('Cheque')),
        ('other',         _('Other')),
    ]

    PAYMENT_STATUS_CHOICES = [
        ('pending',   _('Pending')),
        ('completed', _('Completed')),
        ('failed',    _('Failed')),
        ('cancelled', _('Cancelled')),
        ('refunded',  _('Refunded')),
    ]

    MOBILE_MONEY_PROVIDERS = [
        ('mtn',           _('MTN Mobile Money')),
        ('orange',        _('Orange Money')),
        ('express_union', _('Express Union')),
        ('other',         _('Other')),
    ]

    payment_number = models.CharField(
        max_length=20,
        unique=True,
        blank=True,
        verbose_name=_("Payment Number"),
    )

    invoice = models.ForeignKey(
        Invoice,
        on_delete=models.CASCADE,
        related_name='payments',
        verbose_name=_("Invoice"),
    )

    student = models.ForeignKey(
        Student,
        on_delete=models.CASCADE,
        related_name='payments',
        verbose_name=_("Student"),
    )

    amount = models.DecimalField(
        max_digits=10,
        decimal_places=0,
        validators=[MinValueValidator(1)],
        verbose_name=_("Amount (XAF)"),
    )

    payment_method = models.CharField(
        max_length=20,
        choices=PAYMENT_METHOD_CHOICES,
        verbose_name=_("Payment Method"),
    )

    payment_date = models.DateField(
        default=timezone.now,
        verbose_name=_("Payment Date"),
    )

    mobile_money_provider = models.CharField(
        max_length=20,
        choices=MOBILE_MONEY_PROVIDERS,
        blank=True,
        verbose_name=_("Mobile Money Provider"),
    )

    mobile_money_number = models.CharField(
        max_length=20,
        blank=True,
        verbose_name=_("Mobile Money Number"),
    )

    transaction_id = models.CharField(
        max_length=100,
        blank=True,
        verbose_name=_("Transaction ID / Reference"),
    )

    bank_name = models.CharField(
        max_length=100,
        blank=True,
        verbose_name=_("Bank Name"),
    )

    cheque_number = models.CharField(
        max_length=50,
        blank=True,
        verbose_name=_("Cheque Number"),
    )

    status = models.CharField(
        max_length=20,
        choices=PAYMENT_STATUS_CHOICES,
        default='pending',
        verbose_name=_("Status"),
    )

    receipt_number = models.CharField(
        max_length=50,
        blank=True,
        verbose_name=_("Receipt Number"),
    )

    receipt_issued = models.BooleanField(
        default=False,
        verbose_name=_("Receipt Issued"),
    )

    receipt_issued_date = models.DateField(
        null=True,
        blank=True,
        verbose_name=_("Receipt Issued Date"),
    )

    notes = models.TextField(
        blank=True,
        verbose_name=_("Notes"),
    )

    received_by = models.ForeignKey(
        CustomUser,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='payments_received',
        verbose_name=_("Received By"),
    )

    created_at = models.DateTimeField(
        auto_now_add=True,
        null=True,
        blank=True,
        verbose_name=_("Created At"),
    )

    updated_at = models.DateTimeField(
        auto_now=True,
        verbose_name=_("Updated At"),
    )

    class Meta:
        verbose_name = _("Payment")
        verbose_name_plural = _("Payments")
        ordering = ['-payment_date', '-created_at']
        indexes = [
            models.Index(fields=['payment_number']),
            models.Index(fields=['invoice', 'status']),
            models.Index(fields=['payment_date']),
            models.Index(fields=['student']),
        ]
        # Add this constraint to prevent duplicates within 5 minutes
        constraints = [
            models.UniqueConstraint(
                fields=['invoice', 'amount', 'payment_date'],
                name='unique_payment_per_invoice_amount_date',
                condition=models.Q(created_at__gte=timezone.now() - timedelta(minutes=5))
            ),
        ]
          

    def save(self, *args, **kwargs):
        """Auto-generate payment number; update invoice totals on completion."""
        if not self.payment_number:
            year = timezone.now().year
            last = Payment.objects.filter(
                payment_number__startswith=f"PAY-{year}"
            ).order_by('-id').first()
            new_num = (int(last.payment_number.split('-')[-1]) + 1) if last else 1
            self.payment_number = f"PAY-{year}-{new_num:04d}"

        super().save(*args, **kwargs)

        if self.status == 'completed':
            total_paid = (
                Payment.objects.filter(invoice=self.invoice, status='completed')
                .aggregate(total=models.Sum('amount'))['total'] or 0
            )
            self.invoice.amount_paid = total_paid
            self.invoice.save()

    def __str__(self):
        return f"{self.payment_number} — {int(self.amount):,} XAF — {self.payment_date}"


# ---------------------------------------------------------------------------
# PaymentReminder
# ---------------------------------------------------------------------------

class PaymentReminder(models.Model):
    """
    Tracks payment reminders sent to parents for overdue invoices.

    Attributes:
        invoice (FK): Invoice being reminded about
        reminder_type (str): SMS / Email / WhatsApp / Call / Letter
        sent_to (str): Phone number or email address
        sent_date (datetime): When the reminder was sent
        sent_by (FK): Staff who sent the reminder
        response (str): Any response received
    """

    REMINDER_TYPE_CHOICES = [
        ('sms',       _('SMS')),
        ('email',     _('Email')),
        ('whatsapp',  _('WhatsApp')),
        ('call',      _('Phone Call')),
        ('letter',    _('Physical Letter')),
    ]

    invoice = models.ForeignKey(
        Invoice,
        on_delete=models.CASCADE,
        related_name='reminders',
        verbose_name=_("Invoice"),
    )

    reminder_type = models.CharField(
        max_length=20,
        choices=REMINDER_TYPE_CHOICES,
        verbose_name=_("Reminder Type"),
    )

    sent_to = models.CharField(
        max_length=100,
        verbose_name=_("Sent To"),
    )

    sent_date = models.DateTimeField(
        auto_now_add=True,
        null=True,
        blank=True,
        verbose_name=_("Sent Date"),
    )

    sent_by = models.ForeignKey(
        CustomUser,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_("Sent By"),
    )

    response = models.TextField(
        blank=True,
        verbose_name=_("Response"),
    )

    class Meta:
        verbose_name = _("Payment Reminder")
        verbose_name_plural = _("Payment Reminders")
        ordering = ['-sent_date']

    def __str__(self):
        date_str = self.sent_date.date() if self.sent_date else "pending"
        return f"{self.get_reminder_type_display()} to {self.sent_to} on {date_str}"


# ---------------------------------------------------------------------------
# Discount / StudentDiscount
# ---------------------------------------------------------------------------

class Discount(models.Model):
    """
    Discount definitions (sibling discount, staff-child discount, etc.).

    Attributes:
        name (str): Discount name in English
        name_fr (str): Discount name in French
        discount_type (str): Percentage or fixed XAF amount
        value (Decimal): Numeric discount value
        applies_to (str): All fees / Tuition only / Specific fees
        specific_fees (M2M): Specific FeeStructure rows if applicable
        description (str): Notes
        is_active (bool): Whether discount is currently available
    """

    DISCOUNT_TYPE_CHOICES = [
        ('percentage', _('Percentage (%)')),
        ('fixed',      _('Fixed Amount (XAF)')),
    ]

    APPLIES_TO_CHOICES = [
        ('all_fees',      _('All Fees')),
        ('tuition_only',  _('Tuition Only')),
        ('specific_fees', _('Specific Fee Rows')),
    ]

    name = models.CharField(max_length=100, verbose_name=_("Discount Name"))
    name_fr = models.CharField(max_length=100, blank=True, verbose_name=_("Discount Name (French)"))
    discount_type = models.CharField(max_length=20, choices=DISCOUNT_TYPE_CHOICES, default='percentage')
    value = models.DecimalField(max_digits=5, decimal_places=0, verbose_name=_("Value"))
    applies_to = models.CharField(max_length=20, choices=APPLIES_TO_CHOICES, default='all_fees')
    specific_fees = models.ManyToManyField(FeeStructure, blank=True, verbose_name=_("Specific Fee Rows"))
    description = models.TextField(blank=True)
    is_active = models.BooleanField(default=True)

    class Meta:
        verbose_name = _("Discount")
        verbose_name_plural = _("Discounts")

    def __str__(self):
        symbol = "%" if self.discount_type == 'percentage' else " XAF"
        return f"{self.name} — {int(self.value)}{symbol}"


class StudentDiscount(models.Model):
    """
    Applies a Discount to a specific student for a given academic year.

    Attributes:
        student (FK): Student receiving the discount
        discount (FK): Discount being applied
        academic_year (str): Academic year
        reason (str): Justification
        approved_by (FK): Authorising staff member
        created_at (datetime): When the discount was applied
    """

    student = models.ForeignKey(
        Student, on_delete=models.CASCADE, related_name='discounts', verbose_name=_("Student")
    )
    discount = models.ForeignKey(
        Discount, on_delete=models.CASCADE, verbose_name=_("Discount")
    )
    academic_year = models.CharField(max_length=9, default="2025-2026", verbose_name=_("Academic Year"))
    reason = models.CharField(max_length=200, blank=True, verbose_name=_("Reason"))
    approved_by = models.ForeignKey(
        CustomUser, on_delete=models.SET_NULL, null=True, blank=True,
        related_name='approved_discounts', verbose_name=_("Approved By")
    )
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)

    class Meta:
        verbose_name = _("Student Discount")
        verbose_name_plural = _("Student Discounts")
        unique_together = [['student', 'discount', 'academic_year']]

    def __str__(self):
        return f"{self.student} — {self.discount} ({self.academic_year})"


# ---------------------------------------------------------------------------
# Expense tracking
# ---------------------------------------------------------------------------

class ExpenseCategory(models.Model):
    name = models.CharField(max_length=100, unique=True, verbose_name=_("Category Name"))
    name_fr = models.CharField(max_length=100, blank=True, verbose_name=_("Category Name (French)"))
    description = models.TextField(blank=True)
    is_active = models.BooleanField(default=True)

    class Meta:
        verbose_name = _("Expense Category")
        verbose_name_plural = _("Expense Categories")
        ordering = ['name']

    def __str__(self):
        return self.name


class Expense(models.Model):
    """
    Records a school expenditure.

    Attributes:
        expense_number (str): Auto-generated (EXP-YYYY-NNNN)
        category (FK): Expense category
        description (str): What was purchased / paid
        amount (Decimal): Amount in XAF
        expense_date (date): Date of expense
        payment_method (str): Cash / Mobile Money / Bank / Cheque
        receipt_number (str): Reference on physical receipt
        vendor (str): Supplier / payee name
        notes (str): Additional notes
        approved_by (FK): Authorising staff
        receipt_file (FileField): Scanned/photographed receipt
        created_by (FK): Staff who entered the record
        created_at / updated_at: Timestamps
    """

    PAYMENT_METHOD_CHOICES = [
        ('cash',          _('Cash')),
        ('mobile_money',  _('Mobile Money')),
        ('bank_transfer', _('Bank Transfer')),
        ('cheque',        _('Cheque')),
    ]

    expense_number = models.CharField(max_length=20, unique=True, blank=True, verbose_name=_("Expense Number"))
    category = models.ForeignKey(ExpenseCategory, on_delete=models.PROTECT, related_name='expenses')
    description = models.CharField(max_length=200, verbose_name=_("Description"))
    amount = models.DecimalField(max_digits=10, decimal_places=0, verbose_name=_("Amount (XAF)"))
    expense_date = models.DateField(default=timezone.now, verbose_name=_("Date"))
    payment_method = models.CharField(max_length=20, choices=PAYMENT_METHOD_CHOICES, default='cash')
    receipt_number = models.CharField(max_length=50, blank=True)
    vendor = models.CharField(max_length=100, blank=True, verbose_name=_("Vendor/Payee"))
    notes = models.TextField(blank=True)
    approved_by = models.ForeignKey(
        CustomUser, on_delete=models.SET_NULL, null=True, blank=True,
        related_name='approved_expenses'
    )
    receipt_file = models.FileField(upload_to='expenses/receipts/', null=True, blank=True)
    created_by = models.ForeignKey(
        CustomUser, on_delete=models.SET_NULL, null=True, blank=True,
        related_name='expenses_created'
    )
    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name = _("Expense")
        verbose_name_plural = _("Expenses")
        ordering = ['-expense_date']

    def save(self, *args, **kwargs):
        if not self.expense_number:
            year = timezone.now().year
            last = Expense.objects.filter(expense_number__startswith=f"EXP-{year}").order_by('-id').first()
            new_num = (int(last.expense_number.split('-')[-1]) + 1) if last else 1
            self.expense_number = f"EXP-{year}-{new_num:04d}"
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.expense_number} — {int(self.amount):,} XAF — {self.expense_date}"


class FinancialReport(models.Model):
    """
    Stores generated financial summary reports.

    Attributes:
        title (str): Report title
        report_type (str): Daily / Weekly / Monthly / Termly / Annual / Custom
        start_date (date): Period start
        end_date (date): Period end
        total_revenue (Decimal): Total income in XAF
        total_expenses (Decimal): Total outgoings in XAF
        net_profit (Decimal): Revenue minus expenses
        report_file (FileField): Generated PDF/Excel file
        generated_by (FK): User who triggered report generation
        generated_at (datetime): Timestamp
        notes (str): Additional comments
    """

    REPORT_TYPE_CHOICES = [
        ('daily',   _('Daily Report')),
        ('weekly',  _('Weekly Report')),
        ('monthly', _('Monthly Report')),
        ('termly',  _('Termly Report')),
        ('annual',  _('Annual Report')),
        ('custom',  _('Custom Date Range')),
    ]

    title = models.CharField(max_length=200, verbose_name=_("Report Title"))
    report_type = models.CharField(max_length=20, choices=REPORT_TYPE_CHOICES)
    start_date = models.DateField()
    end_date = models.DateField()
    total_revenue = models.DecimalField(max_digits=12, decimal_places=0, default=0)
    total_expenses = models.DecimalField(max_digits=12, decimal_places=0, default=0)
    net_profit = models.DecimalField(max_digits=12, decimal_places=0, default=0)
    report_file = models.FileField(upload_to='reports/financial/', null=True, blank=True)
    generated_by = models.ForeignKey(
        CustomUser, on_delete=models.SET_NULL, null=True, blank=True
    )
    generated_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    notes = models.TextField(blank=True)

    class Meta:
        verbose_name = _("Financial Report")
        verbose_name_plural = _("Financial Reports")
        ordering = ['-generated_at']

    def __str__(self):
        return f"{self.title} — {self.start_date} to {self.end_date}"