from django.db import models
from django.conf import settings
from django.core.validators import MinValueValidator
from decimal import Decimal
import uuid
from django.utils import timezone as tz


# ==============================#
# LOCATION HIERARCHY MODELS     #
# ==============================#


class Country(models.Model):
    """Dynamic country table"""

    code = models.CharField(max_length=3, unique=True, primary_key=True)
    name = models.CharField(max_length=100)
    phone_code = models.CharField(max_length=10, blank=True, null=True)
    currency_code = models.CharField(max_length=3, blank=True, null=True)
    currency_symbol = models.CharField(max_length=10, blank=True, null=True)
    timezone = models.CharField(max_length=50, blank=True, null=True)
    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        verbose_name_plural = "Countries"
        ordering = ["sort_order", "name"]
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["name"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.name} ({self.code})"


class State(models.Model):
    """Dynamic state/region/province table"""

    country = models.ForeignKey(
        Country, on_delete=models.CASCADE, related_name="states"
    )
    code = models.CharField(max_length=10, blank=True, null=True)
    name = models.CharField(max_length=100)
    local_name = models.CharField(max_length=100, blank=True, null=True)
    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    capital = models.CharField(max_length=100, blank=True, null=True)
    area_sq_km = models.FloatField(blank=True, null=True)
    population = models.BigIntegerField(blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        unique_together = ["country", "name"]
        ordering = ["country", "sort_order", "name"]
        indexes = [
            models.Index(fields=["country", "name"]),
            models.Index(fields=["name"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.name}, {self.country.name}"


class City(models.Model):
    """Dynamic city table"""

    state = models.ForeignKey(State, on_delete=models.CASCADE, related_name="cities")
    name = models.CharField(max_length=100)
    local_name = models.CharField(max_length=100, blank=True, null=True)
    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    is_metro = models.BooleanField(default=False)
    latitude = models.DecimalField(
        max_digits=9, decimal_places=6, blank=True, null=True
    )
    longitude = models.DecimalField(
        max_digits=9, decimal_places=6, blank=True, null=True
    )
    population = models.BigIntegerField(blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        unique_together = ["state", "name"]
        verbose_name_plural = "Cities"
        ordering = ["state", "sort_order", "name"]
        indexes = [
            models.Index(fields=["state", "name"]),
            models.Index(fields=["name"]),
            models.Index(fields=["is_active"]),
            models.Index(fields=["latitude", "longitude"]),
        ]

    def __str__(self):
        return f"{self.name}, {self.state.name}"


class Area(models.Model):
    """Dynamic area/locality table"""

    city = models.ForeignKey(City, on_delete=models.CASCADE, related_name="areas")
    name = models.CharField(max_length=100)
    local_name = models.CharField(max_length=100, blank=True, null=True)
    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    pincode = models.CharField(max_length=20, blank=True, null=True)
    zone = models.CharField(max_length=50, blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        unique_together = ["city", "name"]
        ordering = ["city", "sort_order", "name"]
        indexes = [
            models.Index(fields=["city", "name"]),
            models.Index(fields=["name"]),
            models.Index(fields=["pincode"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.name}, {self.city.name}"


class Pincode(models.Model):
    """Dynamic pincode table"""

    area = models.ForeignKey(Area, on_delete=models.CASCADE, related_name="pincodes")
    pincode = models.CharField(max_length=20, unique=True)
    is_active = models.BooleanField(default=True)

    delivery_office = models.CharField(max_length=200, blank=True, null=True)
    delivery_status = models.CharField(max_length=50, blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        ordering = ["pincode"]
        indexes = [
            models.Index(fields=["pincode"]),
            models.Index(fields=["area", "pincode"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.pincode} - {self.area.name}"


# ==============================#
# SCHOOL TYPES AND BOARDS       #
# ==============================#


class SchoolBoard(models.Model):
    """Dynamic school board/curriculum types"""

    BOARD_TYPE_CHOICES = [
        ("national", "National"),
        ("state", "State"),
        ("international", "International"),
        ("other", "Other"),
    ]

    code = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)
    board_type = models.CharField(
        max_length=20, choices=BOARD_TYPE_CHOICES, default="state"
    )
    country = models.ForeignKey(
        Country,
        on_delete=models.SET_NULL,
        related_name="school_boards",
        null=True,
        blank=True,
    )

    description = models.TextField(blank=True, null=True)
    website = models.URLField(blank=True, null=True)
    logo = models.ImageField(upload_to="board_logos/", blank=True, null=True)

    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    has_streams = models.BooleanField(default=False)
    has_elective_subjects = models.BooleanField(default=False)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        ordering = ["sort_order", "name"]
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["name"]),
            models.Index(fields=["board_type"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.name} ({self.board_type})"


class SchoolType(models.Model):
    """Dynamic school type classification"""

    INSTITUTION_TYPE_CHOICES = [
        ("preschool", "Preschool/Nursery"),
        ("primary", "Primary School"),
        ("middle", "Middle School"),
        ("secondary", "Secondary School"),
        ("higher_secondary", "Higher Secondary"),
        ("k12", "K-12 School"),
        ("junior_college", "Junior College"),
        ("college", "College"),
        ("university", "University"),
        ("vocational", "Vocational Institute"),
        ("coaching", "Coaching Center"),
        ("other", "Other"),
    ]

    OWNERSHIP_CHOICES = [
        ("government", "Government"),
        ("private", "Private"),
        ("aided", "Government Aided"),
        ("trust", "Trust"),
        ("society", "Society"),
        ("other", "Other"),
    ]

    GENDER_CHOICES = [
        ("coed", "Co-educational"),
        ("boys", "Boys Only"),
        ("girls", "Girls Only"),
    ]

    code = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)
    institution_type = models.CharField(max_length=50, choices=INSTITUTION_TYPE_CHOICES)
    ownership = models.CharField(
        max_length=50, choices=OWNERSHIP_CHOICES, default="private"
    )
    gender_type = models.CharField(
        max_length=50, choices=GENDER_CHOICES, default="coed"
    )

    description = models.TextField(blank=True, null=True)

    min_grade = models.IntegerField(blank=True, null=True)
    max_grade = models.IntegerField(blank=True, null=True)

    is_active = models.BooleanField(default=True)
    sort_order = models.IntegerField(default=0)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    class Meta:
        ordering = ["sort_order", "name"]
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["name"]),
            models.Index(fields=["institution_type"]),
            models.Index(fields=["ownership"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return f"{self.name} ({self.get_institution_type_display()})"


# ==============================#
# SYSTEM SETTINGS & CONFIG      #
# ==============================#


class SystemSettingCategory(models.Model):
    """Categories for system settings"""

    code = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)
    description = models.TextField(blank=True, null=True)
    icon = models.CharField(max_length=100, blank=True, null=True)
    sort_order = models.IntegerField(default=0)
    is_active = models.BooleanField(default=True)

    class Meta:
        verbose_name_plural = "System Setting Categories"
        ordering = ["sort_order", "name"]
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["is_active"]),
        ]

    def __str__(self):
        return self.name


class SystemSetting(models.Model):
    """Dynamic system-wide settings"""

    SETTING_TYPE_CHOICES = [
        ("string", "String"),
        ("text", "Text"),
        ("integer", "Integer"),
        ("float", "Float"),
        ("boolean", "Boolean"),
        ("json", "JSON"),
        ("datetime", "DateTime"),
        ("file", "File"),
        ("password", "Password"),
        ("url", "URL"),
        ("email", "Email"),
    ]

    category = models.ForeignKey(
        SystemSettingCategory,
        on_delete=models.SET_NULL,
        related_name="settings",
        null=True,
        blank=True,
    )
    key = models.CharField(max_length=100, unique=True)
    name = models.CharField(max_length=200)
    description = models.TextField(blank=True, null=True)

    value_type = models.CharField(
        max_length=20, choices=SETTING_TYPE_CHOICES, default="string"
    )
    string_value = models.CharField(max_length=500, blank=True, null=True)
    text_value = models.TextField(blank=True, null=True)
    integer_value = models.IntegerField(blank=True, null=True)
    float_value = models.FloatField(blank=True, null=True)
    boolean_value = models.BooleanField(default=False)
    json_value = models.JSONField(blank=True, null=True, default=dict)
    datetime_value = models.DateTimeField(blank=True, null=True)
    file_value = models.FileField(upload_to="system_settings/", blank=True, null=True)

    is_public = models.BooleanField(default=False)
    is_encrypted = models.BooleanField(default=False)
    is_required = models.BooleanField(default=False)
    is_active = models.BooleanField(default=True)

    validation_regex = models.CharField(max_length=500, blank=True, null=True)
    min_value = models.FloatField(blank=True, null=True)
    max_value = models.FloatField(blank=True, null=True)
    options = models.JSONField(blank=True, null=True, default=dict)

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    last_modified_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True
    )

    class Meta:
        ordering = ["category", "key"]
        indexes = [
            models.Index(fields=["key"]),
            models.Index(fields=["category"]),
            models.Index(fields=["is_active"]),
            models.Index(fields=["is_public"]),
        ]

    def __str__(self):
        return f"{self.key}: {self.name}"

    def get_value(self):
        value_map = {
            "string": self.string_value,
            "text": self.text_value,
            "integer": self.integer_value,
            "float": self.float_value,
            "boolean": self.boolean_value,
            "json": self.json_value,
            "datetime": self.datetime_value,
            "file": self.file_value,
            "password": self.string_value,
            "url": self.string_value,
            "email": self.string_value,
        }
        return value_map.get(self.value_type)


# ==============================#
# MAIN MODELS                   #
# ==============================#


class School(models.Model):
    """Main school/tenant model"""

    # Basic Information
    uuid = models.UUIDField(default=uuid.uuid4, editable=False)

    name = models.CharField(max_length=255)
    slug = models.SlugField(unique=True)
    domain_name = models.CharField(max_length=255, unique=True, null=True, blank=True)

    # Classification
    school_type = models.ForeignKey(
        SchoolType,
        on_delete=models.SET_NULL,
        related_name="schools",
        null=True,
        blank=True,
    )
    school_board = models.ForeignKey(
        SchoolBoard,
        on_delete=models.SET_NULL,
        related_name="schools",
        null=True,
        blank=True,
    )
    # REMOVED: school_level foreign key

    # Location Information
    country = models.ForeignKey(
        Country,
        on_delete=models.SET_NULL,
        related_name="schools",
        null=True,
        blank=True,
    )
    state = models.ForeignKey(
        State, on_delete=models.SET_NULL, related_name="schools", null=True, blank=True
    )
    city = models.ForeignKey(
        City, on_delete=models.SET_NULL, related_name="schools", null=True, blank=True
    )
    area = models.ForeignKey(
        Area, on_delete=models.SET_NULL, related_name="schools", null=True, blank=True
    )
    pincode = models.ForeignKey(
        Pincode,
        on_delete=models.SET_NULL,
        related_name="schools",
        null=True,
        blank=True,
    )

    # Address Details
    address_line1 = models.TextField(blank=True, null=True)
    address_line2 = models.TextField(blank=True, null=True)
    landmark = models.CharField(max_length=200, blank=True, null=True)
    latitude = models.DecimalField(
        max_digits=9, decimal_places=6, blank=True, null=True
    )
    longitude = models.DecimalField(
        max_digits=9, decimal_places=6, blank=True, null=True
    )

    # Contact Information
    email = models.EmailField(max_length=255, unique=True, null=True, blank=True)
    phone = models.CharField(max_length=20, blank=True, null=True)
    alternate_phone = models.CharField(max_length=20, blank=True, null=True)
    fax = models.CharField(max_length=20, blank=True, null=True)
    website = models.URLField(max_length=500, blank=True, null=True)

    # School Details
    established_year = models.IntegerField(blank=True, null=True)
    registration_number = models.CharField(max_length=100, blank=True, null=True)
    affiliation_number = models.CharField(max_length=100, blank=True, null=True)
    motto = models.CharField(max_length=500, blank=True, null=True)
    vision = models.TextField(blank=True, null=True)
    mission = models.TextField(blank=True, null=True)

    # Media
    logo = models.ImageField(upload_to="school_logos/", blank=True, null=True)
    banner_image = models.ImageField(upload_to="school_banners/", blank=True, null=True)
    prospectus = models.FileField(upload_to="school_prospectus/", blank=True, null=True)

    # Database Routing
    db_name = models.CharField(max_length=100, unique=True, default="default_db")
    db_user = models.CharField(max_length=100, default="postgres")
    db_password = models.CharField(max_length=255, default="password")
    db_host = models.CharField(max_length=255, default="localhost")
    db_port = models.CharField(max_length=10, default="5432")

    # Subscription Status
    is_active = models.BooleanField(default=True)
    is_trial = models.BooleanField(default=True)
    trial_ends_at = models.DateTimeField(null=True, blank=True)

    # Status Tracking
    verified_at = models.DateTimeField(null=True, blank=True)
    verified_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="verified_schools",
    )

    # Metadata
    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)
    deleted_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        indexes = [
            models.Index(fields=["slug"]),
            models.Index(fields=["domain_name"]),
            models.Index(fields=["email"]),
            models.Index(fields=["is_active"]),
            models.Index(fields=["is_trial"]),
            models.Index(fields=["created_at"]),
            models.Index(fields=["school_type", "school_board"]),
        ]
        constraints = [
            models.UniqueConstraint(fields=["uuid"], name="unique_school_uuid"),
        ]
        ordering = ["-created_at"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        school_type_name = self.school_type.name if self.school_type else "No Type"
        return f"{self.name} ({school_type_name})"


class Plan(models.Model):
    """Plans/Packages available for subscription"""

    BILLING_CYCLE_CHOICES = [
        ("monthly", "Monthly"),
        ("yearly", "Yearly"),
        ("quarterly", "Quarterly"),
        ("half_yearly", "Half Yearly"),
        ("lifetime", "Lifetime"),
    ]

    PLAN_TIER_CHOICES = [
        ("free", "Free"),
        ("basic", "Basic"),
        ("standard", "Standard"),
        ("premium", "Premium"),
        ("enterprise", "Enterprise"),
        ("custom", "Custom"),
    ]

    name = models.CharField(max_length=100)
    code = models.CharField(max_length=50, unique=True)
    tier = models.CharField(max_length=20, choices=PLAN_TIER_CHOICES, default="basic")
    description = models.TextField(blank=True, null=True)

    price = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        validators=[MinValueValidator(Decimal("0.00"))],
        default=Decimal("0.00"),
    )
    billing_cycle = models.CharField(
        max_length=20, choices=BILLING_CYCLE_CHOICES, default="monthly"
    )
    currency = models.CharField(max_length=3, default="USD")

    max_students = models.IntegerField(default=0)
    max_staff = models.IntegerField(default=0)
    max_storage_mb = models.IntegerField(default=1024)
    max_branches = models.IntegerField(default=1)

    max_classes = models.IntegerField(default=10)
    max_subjects = models.IntegerField(default=50)
    max_users = models.IntegerField(default=0)

    is_active = models.BooleanField(default=True)
    is_visible = models.BooleanField(default=True)
    is_popular = models.BooleanField(default=False)
    sort_order = models.IntegerField(default=0)

    trial_days = models.IntegerField(default=14)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["tier"]),
            models.Index(fields=["is_active"]),
            models.Index(fields=["price"]),
        ]
        ordering = ["sort_order", "price"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.name} ({self.billing_cycle})"


class Feature(models.Model):
    """Features that can be enabled/disabled for plans"""

    FEATURE_CATEGORY_CHOICES = [
        ("academic", "Academic"),
        ("administrative", "Administrative"),
        ("financial", "Financial"),
        ("communication", "Communication"),
        ("analytics", "Analytics"),
        ("integration", "Integration"),
        ("security", "Security"),
    ]

    code = models.CharField(max_length=100, unique=True)
    name = models.CharField(max_length=200)
    category = models.CharField(
        max_length=50, choices=FEATURE_CATEGORY_CHOICES, default="academic"
    )
    description = models.TextField(blank=True, null=True)
    icon = models.CharField(max_length=100, blank=True, null=True)

    is_active = models.BooleanField(default=True)
    is_core = models.BooleanField(default=False)
    requires_configuration = models.BooleanField(default=False)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["category"]),
            models.Index(fields=["is_active"]),
        ]
        ordering = ["category", "name"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.name} ({self.code})"


class PlanFeature(models.Model):
    """Mapping between plans and features with limits"""

    plan = models.ForeignKey(
        Plan, on_delete=models.CASCADE, related_name="plan_features"
    )
    feature = models.ForeignKey(
        Feature, on_delete=models.CASCADE, related_name="feature_plans"
    )

    is_enabled = models.BooleanField(default=True)
    limit_value = models.IntegerField(null=True, blank=True)

    is_addon = models.BooleanField(default=False)
    addon_price = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        null=True,
        blank=True,
        validators=[MinValueValidator(Decimal("0.00"))],
        default=Decimal("0.00"),
    )

    current_usage = models.IntegerField(default=0)

    metadata = models.JSONField(blank=True, null=True, default=dict)
    notes = models.TextField(blank=True, null=True)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    class Meta:
        unique_together = ["plan", "feature"]
        indexes = [
            models.Index(fields=["plan", "feature"]),
            models.Index(fields=["is_enabled"]),
        ]

    def __str__(self):
        return f"{self.plan.name} - {self.feature.name}"


class SchoolSubscription(models.Model):
    """School's subscription to a plan"""

    SUBSCRIPTION_STATUS_CHOICES = [
        ("trial", "Trial"),
        ("active", "Active"),
        ("pending", "Pending"),
        ("expired", "Expired"),
        ("cancelled", "Cancelled"),
        ("suspended", "Suspended"),
    ]

    school = models.ForeignKey(
        School, on_delete=models.CASCADE, related_name="subscriptions"
    )
    plan = models.ForeignKey(
        Plan, on_delete=models.PROTECT, related_name="school_subscriptions"
    )

    start_date = models.DateField(default=tz.now)
    end_date = models.DateField(null=True, blank=True)

    status = models.CharField(
        max_length=20, choices=SUBSCRIPTION_STATUS_CHOICES, default="trial"
    )
    is_auto_renew = models.BooleanField(default=True)

    subscribed_price = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        validators=[MinValueValidator(Decimal("0.00"))],
        default=Decimal("0.00"),
    )
    currency = models.CharField(max_length=3, default="USD")
    discount_applied = models.DecimalField(
        max_digits=10, decimal_places=2, default=Decimal("0.00")
    )

    billing_cycle = models.CharField(
        max_length=20, choices=Plan.BILLING_CYCLE_CHOICES, default="monthly"
    )
    next_billing_date = models.DateField(null=True, blank=True)

    cancelled_at = models.DateTimeField(null=True, blank=True)
    cancellation_reason = models.TextField(blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)
    notes = models.TextField(blank=True, null=True)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=["school", "status"]),
            models.Index(fields=["status"]),
            models.Index(fields=["end_date"]),
            models.Index(fields=["next_billing_date"]),
        ]
        ordering = ["-created_at"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.school.name} - {self.plan.name} ({self.status})"

    @property
    def is_active(self):
        today = tz.now().date()
        return (
            self.status == "active"
            and self.start_date <= today
            and (self.end_date is None or today <= self.end_date)
        )


class PaymentType(models.Model):
    """Types of payment methods available"""

    PAYMENT_TYPE_CHOICES = [
        ("credit_card", "Credit Card"),
        ("debit_card", "Debit Card"),
        ("bank_transfer", "Bank Transfer"),
        ("upi", "UPI"),
        ("net_banking", "Net Banking"),
        ("wallet", "Digital Wallet"),
        ("cash", "Cash"),
        ("cheque", "Cheque"),
        ("paypal", "PayPal"),
        ("stripe", "Stripe"),
        ("razorpay", "Razorpay"),
        ("other", "Other"),
    ]

    name = models.CharField(max_length=100)
    code = models.CharField(max_length=50, unique=True, choices=PAYMENT_TYPE_CHOICES)
    description = models.TextField(blank=True, null=True)

    is_active = models.BooleanField(default=True)
    requires_online = models.BooleanField(default=False)
    processing_fee_percentage = models.DecimalField(
        max_digits=5, decimal_places=2, default=Decimal("0.00")
    )
    min_amount = models.DecimalField(
        max_digits=10, decimal_places=2, null=True, blank=True
    )
    max_amount = models.DecimalField(
        max_digits=10, decimal_places=2, null=True, blank=True
    )

    api_key = models.TextField(blank=True, null=True)
    api_secret = models.TextField(blank=True, null=True)
    webhook_secret = models.TextField(blank=True, null=True)
    is_test_mode = models.BooleanField(default=False)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=["code"]),
            models.Index(fields=["is_active"]),
        ]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return self.name


class Payment(models.Model):
    """Payment records for subscriptions"""

    PAYMENT_STATUS_CHOICES = [
        ("pending", "Pending"),
        ("processing", "Processing"),
        ("completed", "Completed"),
        ("failed", "Failed"),
        ("refunded", "Refunded"),
        ("partially_refunded", "Partially Refunded"),
        ("cancelled", "Cancelled"),
    ]

    PAYMENT_GATEWAY_CHOICES = [
        ("stripe", "Stripe"),
        ("razorpay", "Razorpay"),
        ("paypal", "PayPal"),
        ("manual", "Manual"),
        ("other", "Other"),
    ]

    payment_reference = models.CharField(max_length=100, unique=True)
    subscription = models.ForeignKey(
        SchoolSubscription,
        on_delete=models.SET_NULL,
        related_name="payments",
        null=True,
        blank=True,
    )
    school = models.ForeignKey(
        School, on_delete=models.CASCADE, related_name="payments"
    )

    amount = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        validators=[MinValueValidator(Decimal("0.00"))],
        default=Decimal("0.00"),
    )
    currency = models.CharField(max_length=3, default="USD")
    payment_type = models.ForeignKey(
        PaymentType, on_delete=models.PROTECT, related_name="payments"
    )

    processing_fee = models.DecimalField(
        max_digits=10, decimal_places=2, default=Decimal("0.00")
    )
    tax_amount = models.DecimalField(
        max_digits=10, decimal_places=2, default=Decimal("0.00")
    )
    total_amount = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        validators=[MinValueValidator(Decimal("0.00"))],
        default=Decimal("0.00"),
    )

    status = models.CharField(
        max_length=20, choices=PAYMENT_STATUS_CHOICES, default="pending"
    )
    payment_gateway = models.CharField(
        max_length=20, choices=PAYMENT_GATEWAY_CHOICES, default="manual"
    )
    gateway_reference = models.CharField(max_length=200, blank=True, null=True)
    gateway_response = models.JSONField(blank=True, null=True, default=dict)

    payment_date = models.DateTimeField(null=True, blank=True)
    due_date = models.DateField(null=True, blank=True)

    billing_period_start = models.DateField(null=True, blank=True)
    billing_period_end = models.DateField(null=True, blank=True)

    invoice_number = models.CharField(max_length=100, blank=True, null=True)
    invoice_url = models.URLField(max_length=500, blank=True, null=True)
    receipt_url = models.URLField(max_length=500, blank=True, null=True)

    refund_amount = models.DecimalField(
        max_digits=10, decimal_places=2, default=Decimal("0.00")
    )
    refund_reason = models.TextField(blank=True, null=True)
    refunded_at = models.DateTimeField(null=True, blank=True)

    payer_name = models.CharField(max_length=255, blank=True, null=True)
    payer_email = models.EmailField(blank=True, null=True)
    payer_phone = models.CharField(max_length=20, blank=True, null=True)

    notes = models.TextField(blank=True, null=True)

    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=["payment_reference"]),
            models.Index(fields=["school", "status"]),
            models.Index(fields=["status"]),
            models.Index(fields=["payment_date"]),
            models.Index(fields=["invoice_number"]),
        ]
        ordering = ["-created_at"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"Payment #{self.payment_reference} - {self.school.name} - {self.amount} {self.currency}"


class UserProfile(models.Model):
    """Bridge between User and School"""

    user = models.OneToOneField(
        settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="profile"
    )
    school = models.ForeignKey(
        School, on_delete=models.CASCADE, related_name="member_profiles"
    )

    profile_picture = models.ImageField(
        upload_to="profile_pictures/", blank=True, null=True
    )
    phone = models.CharField(max_length=20, blank=True, null=True)
    address = models.TextField(blank=True, null=True)
    date_of_birth = models.DateField(null=True, blank=True)

    external_id = models.CharField(max_length=100, blank=True, null=True)
    department = models.CharField(max_length=100, blank=True, null=True)
    designation = models.CharField(max_length=100, blank=True, null=True)
    joining_date = models.DateField(null=True, blank=True)

    country = models.ForeignKey(
        Country, on_delete=models.SET_NULL, null=True, blank=True
    )
    state = models.ForeignKey(State, on_delete=models.SET_NULL, null=True, blank=True)
    city = models.ForeignKey(City, on_delete=models.SET_NULL, null=True, blank=True)
    area = models.ForeignKey(Area, on_delete=models.SET_NULL, null=True, blank=True)
    pincode = models.ForeignKey(
        Pincode, on_delete=models.SET_NULL, null=True, blank=True
    )

    is_active = models.BooleanField(default=True)
    is_primary_contact = models.BooleanField(default=False)

    email_notifications = models.BooleanField(default=True)
    sms_notifications = models.BooleanField(default=True)
    language = models.CharField(max_length=10, default="en")
    timezone = models.CharField(max_length=50, default="UTC")

    metadata = models.JSONField(blank=True, null=True, default=dict)

    # Audit - FIXED: removed auto_now_add to avoid migration issues
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        unique_together = ["user", "school"]
        indexes = [
            models.Index(fields=["user", "school"]),
            models.Index(fields=["is_active"]),
            models.Index(fields=["external_id"]),
            models.Index(fields=["designation"]),
        ]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.user.username} -> {self.school.name}"
 
# ==============================#
# MODULE ACCESS                 #
# ==============================#


class SchoolModule(models.Model):
    """Catalog of available modules/features that can be enabled per school"""

    code = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)
    description = models.TextField(blank=True, null=True)
    icon = models.CharField(max_length=50, blank=True, null=True)
    is_default = models.BooleanField(default=False)  # always-on (e.g. academics)
    is_active = models.BooleanField(default=True)
    order = models.PositiveIntegerField(default=0)

    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['order', 'name']
        indexes = [
            models.Index(fields=['code']),
            models.Index(fields=['is_active']),
        ]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.name} ({self.code})"


class SchoolModuleAccess(models.Model):
    """Per-school module enable/disable assignments (stored in master DB)"""

    school = models.ForeignKey(
        School, on_delete=models.CASCADE, related_name='module_access'
    )
    module = models.ForeignKey(
        SchoolModule, on_delete=models.CASCADE, related_name='school_access'
    )
    is_enabled = models.BooleanField(default=True)
    enabled_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
    )
    enabled_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        unique_together = ['school', 'module']
        indexes = [
            models.Index(fields=['school', 'module']),
            models.Index(fields=['is_enabled']),
        ]

    def save(self, *args, **kwargs):
        if not self.enabled_at:
            self.enabled_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        status = 'enabled' if self.is_enabled else 'disabled'
        return f"{self.school.name} - {self.module.name} ({status})"


class Language(models.Model):
    """Catalog of languages available in the platform (stored in master DB)"""

    code = models.CharField(max_length=10, unique=True)  # ISO 639-1 e.g. 'en', 'ta'
    name = models.CharField(max_length=100)              # English name e.g. 'Tamil'
    native_name = models.CharField(max_length=100, blank=True, null=True)  # e.g. 'தமிழ்'
    is_default = models.BooleanField(default=False)  # always-on (English)
    is_active = models.BooleanField(default=True)
    order = models.PositiveIntegerField(default=0)

    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['order', 'name']
        indexes = [
            models.Index(fields=['code']),
            models.Index(fields=['is_active']),
        ]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.name} ({self.code})"


class LegalDocument(models.Model):
    """
    Platform-wide legal documents (Terms & Conditions, Privacy Policy).
    Stored in the master DB and visible to every school and mobile user.
    """

    DOC_TYPE_CHOICES = [
        ("terms", "Terms & Conditions"),
        ("privacy", "Privacy Policy"),
    ]

    doc_type = models.CharField(max_length=20, choices=DOC_TYPE_CHOICES, unique=True)
    title = models.CharField(max_length=200)
    content = models.TextField(blank=True)
    is_published = models.BooleanField(default=False)

    updated_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="updated_legal_documents",
    )
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["doc_type"]

    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)

    def __str__(self):
        status = "published" if self.is_published else "draft"
        return f"{self.get_doc_type_display()} ({status})"


class EmailTemplate(models.Model):
    """Stores reusable email templates with variable placeholders"""
 
    STATUS_CHOICES = [
        ("active", "Active"),
        ("inactive", "Inactive"),
        ("draft", "Draft"),
    ]
 
    uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    name = models.CharField(max_length=255, unique=True)
    subject = models.CharField(max_length=500)
    body = models.TextField(
        help_text="Use {{school_name}}, {{email}}, or any {{variable}} placeholders"
    )
    description = models.TextField(blank=True, null=True)
 
    # Template metadata
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="active")
    tags = models.JSONField(default=list, blank=True)
 
    # Track which variables are used
    variables_used = models.JSONField(
        default=list,
        blank=True,
        help_text="Auto-detected list of {{variable}} placeholders in this template",
    )
 
    # Audit
    created_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="created_email_templates",
    )
    updated_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="updated_email_templates",
    )
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)
 
    class Meta:
        ordering = ["-created_at"]
        indexes = [
            models.Index(fields=["status"]),
            models.Index(fields=["name"]),
            models.Index(fields=["created_at"]),
        ]
 
    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        # Auto-detect variables from subject and body
        import re
        variables = re.findall(r"\{\{(\w+)\}\}", self.subject + " " + self.body)
        self.variables_used = list(set(variables))
        super().save(*args, **kwargs)
 
    def __str__(self):
        return f"{self.name} ({self.status})"
 
    def render(self, context: dict) -> dict:
        """Render subject and body with given context variables"""
        subject = self.subject
        body = self.body
        for key, value in context.items():
            subject = subject.replace(f"{{{{{key}}}}}", str(value))
            body = body.replace(f"{{{{{key}}}}}", str(value))
        return {"subject": subject, "body": body}
 
 
class BulkEmailLog(models.Model):
    """Records of every email sent via the bulk email system"""
 
    STATUS_CHOICES = [
        ("pending", "Pending"),
        ("sent", "Sent"),
        ("failed", "Failed"),
        ("bounced", "Bounced"),
    ]
 
    uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
 
    # Template used (nullable in case template is deleted later)
    template = models.ForeignKey(
        EmailTemplate,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="email_logs",
    )
    template_name_snapshot = models.CharField(
        max_length=255,
        blank=True,
        null=True,
        help_text="Snapshot of template name at send time",
    )
 
    # Recipients (stored as comma-separated for "To" and "CC")
    to_emails = models.TextField(help_text="Comma-separated list of To addresses")
    cc_emails = models.TextField(
        blank=True, null=True, help_text="Comma-separated list of CC addresses"
    )
 
    # Rendered content at time of send
    rendered_subject = models.CharField(max_length=500)
    rendered_body = models.TextField()
 
    # Variables used to render
    context_data = models.JSONField(
        default=dict, blank=True, help_text="Variables passed to render the template"
    )
 
    # Status
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="pending")
    error_message = models.TextField(blank=True, null=True)
 
    # Tracking
    sent_at = models.DateTimeField(null=True, blank=True)
    sent_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="sent_bulk_emails",
    )
 
    # Audit
    created_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)
 
    class Meta:
        ordering = ["-created_at"]
        indexes = [
            models.Index(fields=["status"]),
            models.Index(fields=["template"]),
            models.Index(fields=["sent_at"]),
            models.Index(fields=["sent_by"]),
            models.Index(fields=["created_at"]),
        ]
 
    def save(self, *args, **kwargs):
        if not self.created_at:
            self.created_at = tz.now()
        super().save(*args, **kwargs)
 
    def __str__(self):
        return f"Email to {self.to_emails[:50]} | {self.status} | {self.created_at}"
 
    @property
    def to_email_list(self):
        return [e.strip() for e in self.to_emails.split(",") if e.strip()]
 
    @property
    def cc_email_list(self):
        if not self.cc_emails:
            return []
        return [e.strip() for e in self.cc_emails.split(",") if e.strip()]


# ==============================#
# USER SESSION TRACKING         #
# ==============================#


class UserSession(models.Model):
    """
    Tracks active login sessions for teachers and parents.
    Stored in master DB so school admins can view/revoke from one place.
    """

    USER_TYPE_CHOICES = [
        ("TEACHER", "Teacher"),
        ("PARENT", "Parent"),
    ]
    DEVICE_TYPE_CHOICES = [
        ("MOBILE", "Mobile"),
        ("TABLET", "Tablet"),
        ("DESKTOP", "Desktop"),
    ]

    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="user_sessions",
        db_constraint=False,
    )
    school = models.ForeignKey(
        "School",
        on_delete=models.CASCADE,
        related_name="user_sessions",
        db_constraint=False,
    )
    user_type = models.CharField(max_length=10, choices=USER_TYPE_CHOICES)
    external_id = models.IntegerField(help_text="Teacher/Parent ID in school DB")

    session_token = models.UUIDField(default=uuid.uuid4, unique=True, db_index=True)
    refresh_jti = models.CharField(max_length=200, blank=True, db_index=True)

    device_type = models.CharField(
        max_length=10, choices=DEVICE_TYPE_CHOICES, default="DESKTOP"
    )
    device_name = models.CharField(max_length=200, blank=True)
    browser = models.CharField(max_length=100, blank=True)
    ip_address = models.GenericIPAddressField(null=True, blank=True)
    user_agent = models.TextField(blank=True)

    logged_in_at = models.DateTimeField(auto_now_add=True)
    last_active_at = models.DateTimeField(auto_now_add=True)
    is_active = models.BooleanField(default=True, db_index=True)
    logged_out_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        ordering = ["-logged_in_at"]
        indexes = [
            models.Index(fields=["school", "is_active"]),
            models.Index(fields=["user", "is_active"]),
            models.Index(fields=["user_type", "is_active"]),
        ]

    def __str__(self):
        return f"{self.user_type} session – user {self.user_id} – {self.logged_in_at:%Y-%m-%d %H:%M}"


class ApiCredential(models.Model):
    """
    Platform-wide API credentials (Gemini, Google Translate, SMTP, ...).
    Stored in the MASTER database and loaded dynamically at request time via
    master_admin.credentials.get_credential() - no server restart needed
    after updating a key from the master admin panel.
    """

    key = models.CharField(
        max_length=100,
        unique=True,
        help_text="Machine name, e.g. GOOGLE_TRANSLATE_API_KEY",
    )
    label = models.CharField(max_length=200, blank=True)
    value = models.TextField(blank=True)
    description = models.TextField(blank=True)

    is_active = models.BooleanField(default=True)
    updated_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="updated_credentials",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["key"]

    def __str__(self):
        return self.key