import uuid
from decimal import Decimal
from django.db import models
from django.db.models import Q, F
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators
from .constants import (
    BudgetStatus, FFPStatus, FinanceCategory, FinanceDirection,
    GLAccountType, GLNormalBalance, JournalStatus, AccrualDocumentType,
    AccrualStatus, DepreciationMethod, StatementType,
)
from .exceptions import BudgetIntegrityError


class ImmutableFinanceQuerySet(models.QuerySet):
    def update(self, **kwargs):
        raise BudgetIntegrityError("Immutable financial evidence cannot be bulk-updated")
    def delete(self):
        raise BudgetIntegrityError("Immutable financial evidence cannot be deleted")


class AccountingPeriod(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="accounting_periods")
    season = models.ForeignKey("seasons.Season", on_delete=models.PROTECT, null=True, blank=True, related_name="club_accounting_periods")
    name = models.CharField(max_length=120)
    starts_at = models.DateTimeField()
    ends_at = models.DateTimeField()
    is_closed = models.BooleanField(default=False)
    closed_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    class Meta:
        ordering = ["-starts_at", "id"]
        constraints = [
            models.CheckConstraint(condition=Q(ends_at__gt=F("starts_at")), name="cf_period_dates_order"),
            models.UniqueConstraint(fields=["club", "name"], name="cf_period_unique_club_name"),
            ExclusionConstraint(name='cf_period_no_overlap',expressions=[
                (F('club'),RangeOperators.EQUAL),
                (models.Func(F('starts_at'),F('ends_at'),models.Value('[)'),function='TSTZRANGE',output_field=DateTimeRangeField()),RangeOperators.OVERLAPS),
            ]),
        ]
        indexes = [models.Index(fields=["club", "starts_at", "ends_at"], name="cf_period_club_dates_idx")]


class LedgerClassification(models.Model):
    """Accounting/FFP projection over the canonical Economy LedgerEntry."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    ledger_entry = models.ForeignKey("economy.LedgerEntry", on_delete=models.PROTECT, related_name="club_finance_classifications")
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ledger_classifications")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, null=True, blank=True, related_name="classifications")
    direction = models.CharField(max_length=12, choices=FinanceDirection.choices)
    category = models.CharField(max_length=32, choices=FinanceCategory.choices)
    amount = models.DecimalField(max_digits=24, decimal_places=8)
    ffp_relevant = models.BooleanField(default=True)
    capex = models.BooleanField(default=False)
    manual_override = models.BooleanField(default=False)
    evidence = models.JSONField(default=dict, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()
    class Meta:
        ordering = ["-created_at", "id"]
        constraints = [
            models.CheckConstraint(condition=Q(amount__gt=0), name="cf_class_amount_positive"),
            models.UniqueConstraint(fields=["ledger_entry", "club"], name="cf_class_unique_entry_club"),
        ]
        indexes = [
            models.Index(fields=["club", "category", "created_at"], name="cf_class_club_cat_idx"),
            models.Index(fields=["club", "direction", "created_at"], name="cf_class_direction_idx"),
            models.Index(fields=["ffp_relevant", "created_at"], name="cf_class_ffp_idx"),
        ]


class ClubBudget(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="budgets")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="budgets")
    name = models.CharField(max_length=120)
    status = models.CharField(max_length=12, choices=BudgetStatus.choices, default=BudgetStatus.DRAFT)
    contingency_amount = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    notes = models.TextField(blank=True, default="")
    activated_at = models.DateTimeField(null=True, blank=True)
    closed_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    class Meta:
        ordering = ["-created_at", "id"]
        constraints = [
            models.CheckConstraint(condition=Q(contingency_amount__gte=0), name="cf_budget_contingency_nonneg"),
            models.UniqueConstraint(fields=["club", "period", "name"], name="cf_budget_unique_name"),
            models.UniqueConstraint(fields=["club","period"],condition=Q(status=BudgetStatus.ACTIVE),name="cf_one_active_budget"),
        ]
        indexes = [models.Index(fields=["club", "status"], name="cf_budget_club_status_idx")]


class BudgetLine(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    budget = models.ForeignKey(ClubBudget, on_delete=models.PROTECT, related_name="lines")
    category = models.CharField(max_length=32, choices=FinanceCategory.choices)
    planned_amount = models.DecimalField(max_digits=24, decimal_places=8)
    hard_limit = models.BooleanField(default=False)
    warning_threshold_pct = models.DecimalField(max_digits=5, decimal_places=2, default=Decimal("90"))
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    class Meta:
        constraints = [
            models.CheckConstraint(condition=Q(planned_amount__gte=0), name="cf_budget_line_nonneg"),
            models.CheckConstraint(condition=Q(warning_threshold_pct__gte=0, warning_threshold_pct__lte=100), name="cf_budget_warning_pct"),
            models.UniqueConstraint(fields=["budget", "category"], name="cf_budget_unique_category"),
        ]


class FFPPolicy(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=120)
    version = models.PositiveIntegerField(default=1)
    effective_from = models.DateTimeField()
    effective_to = models.DateTimeField(null=True, blank=True)
    max_loss = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    max_wage_to_revenue_ratio = models.DecimalField(max_digits=8, decimal_places=4, default=Decimal("0.70"))
    max_transfer_spend_to_revenue_ratio = models.DecimalField(max_digits=8, decimal_places=4, default=Decimal("1.50"))
    warning_buffer_pct = models.DecimalField(max_digits=5, decimal_places=2, default=Decimal("10"))
    is_active = models.BooleanField(default=True)
    rules = models.JSONField(default=dict, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    class Meta:
        ordering = ["-effective_from", "-version"]
        constraints = [
            models.CheckConstraint(condition=Q(max_loss__gte=0), name="cf_ffp_max_loss_nonneg"),
            models.CheckConstraint(condition=Q(max_wage_to_revenue_ratio__gte=0), name="cf_ffp_wage_ratio_nonneg"),
            models.CheckConstraint(condition=Q(max_transfer_spend_to_revenue_ratio__gte=0), name="cf_ffp_transfer_ratio_nonneg"),
            models.UniqueConstraint(fields=["name", "version"], name="cf_ffp_policy_version_unique"),
        ]


class FFPAssessment(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ffp_assessments")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="ffp_assessments")
    policy = models.ForeignKey(FFPPolicy, on_delete=models.PROTECT, related_name="assessments")
    status = models.CharField(max_length=12, choices=FFPStatus.choices)
    relevant_income = models.DecimalField(max_digits=24, decimal_places=8)
    relevant_expense = models.DecimalField(max_digits=24, decimal_places=8)
    result = models.DecimalField(max_digits=24, decimal_places=8)
    wage_cost = models.DecimalField(max_digits=24, decimal_places=8)
    transfer_spend = models.DecimalField(max_digits=24, decimal_places=8)
    wage_to_revenue_ratio = models.DecimalField(max_digits=12, decimal_places=6, null=True, blank=True)
    transfer_spend_to_revenue_ratio = models.DecimalField(max_digits=12, decimal_places=6, null=True, blank=True)
    reasons = models.JSONField(default=list, blank=True)
    evidence = models.JSONField(default=dict, blank=True)
    assessed_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()
    class Meta:
        ordering = ["-assessed_at", "id"]
        indexes = [models.Index(fields=["club", "status", "assessed_at"], name="cf_ffp_club_status_idx")]


class FinancialControlEvent(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="financial_control_events")
    event_type = models.CharField(max_length=40)
    severity = models.CharField(max_length=12, default="INFO")
    reference_type = models.CharField(max_length=80, blank=True, default="")
    reference_id = models.CharField(max_length=120, blank=True, default="")
    payload = models.JSONField(default=dict, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()
    class Meta:
        ordering = ["-created_at", "id"]
        indexes = [models.Index(fields=["club", "event_type", "created_at"], name="cf_control_event_idx")]


class GLAccount(models.Model):
    """Per-club chart-of-accounts node used by the accrual general ledger."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="gl_accounts")
    code = models.CharField(max_length=24)
    name = models.CharField(max_length=140)
    account_type = models.CharField(max_length=12, choices=GLAccountType.choices)
    normal_balance = models.CharField(max_length=8, choices=GLNormalBalance.choices)
    parent = models.ForeignKey("self", null=True, blank=True, on_delete=models.PROTECT, related_name="children")
    is_system = models.BooleanField(default=True)
    is_control_account = models.BooleanField(default=False)
    allow_manual_entries = models.BooleanField(default=False)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["code", "id"]
        constraints = [
            models.UniqueConstraint(fields=["club", "code"], name="cf_gl_account_club_code_uq"),
        ]
        indexes = [models.Index(fields=["club", "account_type", "is_active"], name="cf_gl_acct_type_idx")]


class JournalEntryQuerySet(models.QuerySet):
    def delete(self):
        raise BudgetIntegrityError("Posted journal entries cannot be deleted; reverse them instead")


class JournalEntry(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="journal_entries")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="journal_entries")
    accounting_date = models.DateField()
    journal_no = models.CharField(max_length=80, unique=True)
    status = models.CharField(max_length=12, choices=JournalStatus.choices, default=JournalStatus.POSTED)
    source_type = models.CharField(max_length=64)
    source_id = models.CharField(max_length=120, blank=True, default="")
    economy_ledger = models.ForeignKey(
        "economy.LedgerEntry", null=True, blank=True, on_delete=models.PROTECT,
        related_name="club_gl_journals",
    )
    reversal_of = models.OneToOneField(
        "self", null=True, blank=True, on_delete=models.PROTECT, related_name="reversal_entry"
    )
    memo = models.CharField(max_length=255, blank=True, default="")
    idempotency_key = models.CharField(max_length=180, unique=True)
    checksum = models.CharField(max_length=64)
    posted_at = models.DateTimeField(auto_now_add=True)
    objects = JournalEntryQuerySet.as_manager()

    class Meta:
        ordering = ["accounting_date", "posted_at", "id"]
        constraints = [
            models.UniqueConstraint(
                fields=["club", "economy_ledger"],
                condition=Q(economy_ledger__isnull=False),
                name="cf_gl_one_journal_per_club_ledger",
            ),
            models.CheckConstraint(condition=~Q(checksum=""), name="cf_gl_journal_checksum_nonempty"),
        ]
        indexes = [
            models.Index(fields=["club", "accounting_date"], name="cf_gl_journal_date_idx"),
            models.Index(fields=["club", "source_type", "source_id"], name="cf_gl_journal_source_idx"),
        ]


class JournalLine(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    entry = models.ForeignKey(JournalEntry, on_delete=models.PROTECT, related_name="lines")
    line_no = models.PositiveSmallIntegerField()
    account = models.ForeignKey(GLAccount, on_delete=models.PROTECT, related_name="journal_lines")
    debit = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    credit = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    description = models.CharField(max_length=255, blank=True, default="")
    metadata = models.JSONField(default=dict, blank=True)

    class Meta:
        ordering = ["entry_id", "line_no"]
        constraints = [
            models.UniqueConstraint(fields=["entry", "line_no"], name="cf_gl_line_no_uq"),
            models.CheckConstraint(condition=Q(debit__gte=0, credit__gte=0), name="cf_gl_line_nonnegative"),
            models.CheckConstraint(
                condition=(Q(debit__gt=0, credit=0) | Q(credit__gt=0, debit=0)),
                name="cf_gl_line_one_side",
            ),
        ]
        indexes = [models.Index(fields=["account", "entry"], name="cf_gl_line_account_idx")]


class AccrualDocument(models.Model):
    """Generic AP/AR subledger document with immutable recognition source."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="accrual_documents")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="accrual_documents")
    document_type = models.CharField(max_length=12, choices=AccrualDocumentType.choices)
    counterparty_wallet = models.ForeignKey(
        "economy.Wallet", null=True, blank=True, on_delete=models.PROTECT,
        related_name="club_finance_accrual_documents",
    )
    counterparty_label = models.CharField(max_length=160, blank=True, default="")
    recognition_account = models.ForeignKey(GLAccount, on_delete=models.PROTECT, related_name="recognized_accrual_documents")
    control_account = models.ForeignKey(GLAccount, on_delete=models.PROTECT, related_name="controlled_accrual_documents")
    original_amount = models.DecimalField(max_digits=24, decimal_places=8)
    settled_amount = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    recognized_on = models.DateField()
    due_date = models.DateField()
    status = models.CharField(max_length=12, choices=AccrualStatus.choices, default=AccrualStatus.OPEN)
    source_type = models.CharField(max_length=64)
    source_id = models.CharField(max_length=120)
    recognition_journal = models.OneToOneField(
        JournalEntry, on_delete=models.PROTECT, related_name="recognized_accrual_document"
    )
    idempotency_key = models.CharField(max_length=180, unique=True)
    metadata = models.JSONField(default=dict, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["due_date", "created_at", "id"]
        constraints = [
            models.CheckConstraint(condition=Q(original_amount__gt=0), name="cf_accrual_amount_positive"),
            models.CheckConstraint(condition=Q(settled_amount__gte=0), name="cf_accrual_settled_nonneg"),
            models.CheckConstraint(condition=Q(settled_amount__lte=F("original_amount")), name="cf_accrual_settled_lte"),
            models.UniqueConstraint(
                fields=["club", "document_type", "source_type", "source_id"],
                name="cf_accrual_source_uq",
            ),
        ]
        indexes = [
            models.Index(fields=["club", "document_type", "status", "due_date"], name="cf_accrual_due_idx"),
        ]

    @property
    def outstanding_amount(self):
        return self.original_amount - self.settled_amount


class AccrualSettlement(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    document = models.ForeignKey(AccrualDocument, on_delete=models.PROTECT, related_name="settlements")
    ledger_entry = models.ForeignKey("economy.LedgerEntry", on_delete=models.PROTECT, related_name="accrual_settlements")
    journal_entry = models.ForeignKey(JournalEntry, on_delete=models.PROTECT, related_name="accrual_settlements")
    amount = models.DecimalField(max_digits=24, decimal_places=8)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        constraints = [
            models.CheckConstraint(condition=Q(amount__gt=0), name="cf_accrual_settlement_positive"),
            models.UniqueConstraint(fields=["document", "ledger_entry"], name="cf_accrual_settlement_doc_ledger_uq"),
        ]


class FixedAssetBook(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="fixed_asset_books")
    asset = models.ForeignKey("club_assets.ClubAsset", on_delete=models.PROTECT, related_name="accounting_books")
    acquisition_cost = models.DecimalField(max_digits=24, decimal_places=8)
    residual_value = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    accumulated_depreciation = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    useful_life_months = models.PositiveIntegerField()
    depreciation_method = models.CharField(max_length=20, choices=DepreciationMethod.choices, default=DepreciationMethod.STRAIGHT_LINE)
    placed_in_service = models.DateField()
    last_depreciated_through = models.DateField(null=True, blank=True)
    disposed_on = models.DateField(null=True, blank=True)
    source_type = models.CharField(max_length=64, blank=True, default="")
    source_id = models.CharField(max_length=120, blank=True, default="")
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        constraints = [
            models.CheckConstraint(condition=Q(acquisition_cost__gt=0), name="cf_fixed_cost_positive"),
            models.CheckConstraint(condition=Q(residual_value__gte=0), name="cf_fixed_residual_nonneg"),
            models.CheckConstraint(condition=Q(accumulated_depreciation__gte=0), name="cf_fixed_accum_depr_nonneg"),
            models.CheckConstraint(condition=Q(useful_life_months__gte=1), name="cf_fixed_life_positive"),
            models.UniqueConstraint(
                fields=["asset", "club"], condition=Q(disposed_on__isnull=True),
                name="cf_fixed_active_asset_club_uq",
            ),
        ]
        indexes = [models.Index(fields=["club", "disposed_on"], name="cf_fixed_active_idx")]

    @property
    def carrying_amount(self):
        return max(Decimal("0"), self.acquisition_cost - self.accumulated_depreciation)


class PlayerRegistrationAsset(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="player_registration_assets")
    player = models.ForeignKey("players.Player", on_delete=models.PROTECT, related_name="registration_assets")
    contract = models.OneToOneField("contracts.Contract", on_delete=models.PROTECT, related_name="registration_asset")
    acquisition_cost = models.DecimalField(max_digits=24, decimal_places=8)
    accumulated_amortization = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    starts_on = models.DateField()
    ends_on = models.DateField()
    last_amortized_through = models.DateField(null=True, blank=True)
    disposed_on = models.DateField(null=True, blank=True)
    source_transfer_id = models.UUIDField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        constraints = [
            models.CheckConstraint(condition=Q(acquisition_cost__gt=0), name="cf_reg_asset_cost_positive"),
            models.CheckConstraint(condition=Q(accumulated_amortization__gte=0), name="cf_reg_asset_amort_nonneg"),
            models.CheckConstraint(condition=Q(ends_on__gt=F("starts_on")), name="cf_reg_asset_dates_order"),
        ]
        indexes = [models.Index(fields=["club", "disposed_on"], name="cf_reg_asset_active_idx")]

    @property
    def carrying_amount(self):
        return max(Decimal("0"), self.acquisition_cost - self.accumulated_amortization)


class FinancialStatementSnapshot(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="financial_statement_snapshots")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="statement_snapshots")
    statement_type = models.CharField(max_length=24, choices=StatementType.choices)
    as_of = models.DateField()
    payload = models.JSONField(default=dict)
    checksum = models.CharField(max_length=64)
    generated_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ["period_id", "statement_type", "generated_at"]
        constraints = [
            models.UniqueConstraint(fields=["period", "statement_type"], name="cf_statement_period_type_uq"),
            models.CheckConstraint(condition=~Q(checksum=""), name="cf_statement_checksum_nonempty"),
        ]

# ---------------------------------------------------------------------------
# Regulatory FFP bridge / multi-season compliance
# ---------------------------------------------------------------------------
from .regulatory_constants import (
    RegulatoryAdjustmentType,
    RegulatoryEvidenceStatus,
    RelatedPartyReviewStatus,
    RegulatoryAssessmentStatus,
    RegulatorySubmissionStatus,
    FederationReviewStatus,
)


class FFPRegulatoryPolicy(models.Model):
    """Versioned federation policy over the accrual General Ledger.

    Accounting remains authoritative for recognition; this model defines only
    regulatory treatments, rolling-window limits and permitted adjustments.
    """
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    federation = models.ForeignKey(
        "federations.Federation", on_delete=models.PROTECT, related_name="ffp_regulatory_policies"
    )
    name = models.CharField(max_length=140)
    version = models.PositiveIntegerField(default=1)
    effective_from = models.DateTimeField()
    effective_to = models.DateTimeField(null=True, blank=True)
    rolling_seasons = models.PositiveSmallIntegerField(default=3)
    max_aggregate_loss = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    owner_contribution_cap = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    max_squad_cost_ratio = models.DecimalField(max_digits=8, decimal_places=4, default=Decimal("0.70"))
    max_wage_to_revenue_ratio = models.DecimalField(max_digits=8, decimal_places=4, default=Decimal("0.70"))
    warning_buffer_pct = models.DecimalField(max_digits=5, decimal_places=2, default=Decimal("10"))
    require_full_window = models.BooleanField(default=False)
    require_related_party_fair_value = models.BooleanField(default=True)
    permitted_exclusion_types = models.JSONField(default=list, blank=True)
    rules = models.JSONField(default=dict, blank=True)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ["federation_id", "-effective_from", "-version"]
        constraints = [
            models.UniqueConstraint(fields=["federation", "name", "version"], name="cf_reg_policy_version_uq"),
            models.CheckConstraint(condition=Q(rolling_seasons__gte=1), name="cf_reg_policy_window_pos"),
            models.CheckConstraint(condition=Q(max_aggregate_loss__gte=0), name="cf_reg_policy_loss_nonneg"),
            models.CheckConstraint(condition=Q(owner_contribution_cap__gte=0), name="cf_reg_policy_owner_cap_nonneg"),
            models.CheckConstraint(condition=Q(max_squad_cost_ratio__gte=0), name="cf_reg_policy_squad_ratio_nonneg"),
            models.CheckConstraint(condition=Q(max_wage_to_revenue_ratio__gte=0), name="cf_reg_policy_wage_ratio_nonneg"),
            models.CheckConstraint(condition=Q(warning_buffer_pct__gte=0, warning_buffer_pct__lte=100), name="cf_reg_policy_warning_pct"),
            models.CheckConstraint(condition=Q(effective_to__isnull=True) | Q(effective_to__gt=F("effective_from")), name="cf_reg_policy_dates_order"),
        ]
        indexes = [models.Index(fields=["federation", "is_active", "effective_from"], name="cf_reg_policy_active_idx")]


class FFPRegulatoryAdjustment(models.Model):
    """Evidence-backed permitted exclusion requested by a club and decided by its federation."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ffp_regulatory_adjustments")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="ffp_regulatory_adjustments")
    adjustment_type = models.CharField(max_length=32, choices=RegulatoryAdjustmentType.choices)
    amount = models.DecimalField(max_digits=24, decimal_places=8)
    status = models.CharField(max_length=16, choices=RegulatoryEvidenceStatus.choices, default=RegulatoryEvidenceStatus.DRAFT)
    source_type = models.CharField(max_length=80, blank=True, default="")
    source_id = models.CharField(max_length=120, blank=True, default="")
    explanation = models.TextField(blank=True, default="")
    evidence = models.JSONField(default=dict, blank=True)
    requested_by = models.ForeignKey("users.User", on_delete=models.PROTECT, related_name="requested_ffp_adjustments")
    reviewed_by = models.ForeignKey("users.User", null=True, blank=True, on_delete=models.PROTECT, related_name="reviewed_ffp_adjustments")
    submitted_at = models.DateTimeField(null=True, blank=True)
    reviewed_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ["-created_at", "id"]
        constraints = [
            models.CheckConstraint(condition=Q(amount__gt=0), name="cf_reg_adjust_amount_pos"),
            models.UniqueConstraint(
                fields=["club", "period", "adjustment_type", "source_type", "source_id"],
                condition=~Q(source_id=""), name="cf_reg_adjust_source_uq",
            ),
        ]
        indexes = [models.Index(fields=["club", "period", "status"], name="cf_reg_adjust_status_idx")]


class FFPRelatedPartyReview(models.Model):
    """Federation fair-value review for a disclosed related-party sponsorship agreement."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ffp_related_party_reviews")
    period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="ffp_related_party_reviews")
    agreement = models.ForeignKey(
        "club_commercial.SponsorshipAgreement", on_delete=models.PROTECT, related_name="ffp_related_party_reviews"
    )
    status = models.CharField(max_length=16, choices=RelatedPartyReviewStatus.choices, default=RelatedPartyReviewStatus.DISCLOSED)
    relationship_summary = models.TextField(blank=True, default="")
    reported_revenue = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    fair_value = models.DecimalField(max_digits=24, decimal_places=8, null=True, blank=True)
    allowed_revenue = models.DecimalField(max_digits=24, decimal_places=8, null=True, blank=True)
    evidence = models.JSONField(default=dict, blank=True)
    disclosed_by = models.ForeignKey("users.User", on_delete=models.PROTECT, related_name="disclosed_related_party_reviews")
    reviewed_by = models.ForeignKey("users.User", null=True, blank=True, on_delete=models.PROTECT, related_name="reviewed_related_party_reviews")
    reviewed_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ["-created_at", "id"]
        constraints = [
            models.UniqueConstraint(fields=["agreement", "period"], name="cf_reg_related_review_uq"),
            models.CheckConstraint(condition=Q(reported_revenue__gte=0), name="cf_reg_related_report_nonneg"),
            models.CheckConstraint(condition=Q(fair_value__isnull=True) | Q(fair_value__gte=0), name="cf_reg_related_fair_nonneg"),
            models.CheckConstraint(condition=Q(allowed_revenue__isnull=True) | Q(allowed_revenue__gte=0), name="cf_reg_related_allowed_nonneg"),
        ]
        indexes = [models.Index(fields=["club", "period", "status"], name="cf_reg_related_status_idx")]


class FFPRegulatoryAssessment(models.Model):
    """Immutable rolling-window regulatory computation derived from GL plus approved policy evidence."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ffp_regulatory_assessments")
    target_period = models.ForeignKey(AccountingPeriod, on_delete=models.PROTECT, related_name="ffp_regulatory_assessments")
    federation = models.ForeignKey("federations.Federation", on_delete=models.PROTECT, related_name="ffp_regulatory_assessments")
    policy = models.ForeignKey(FFPRegulatoryPolicy, on_delete=models.PROTECT, related_name="assessments")
    status = models.CharField(max_length=20, choices=RegulatoryAssessmentStatus.choices)
    relevant_revenue = models.DecimalField(max_digits=24, decimal_places=8)
    base_expense = models.DecimalField(max_digits=24, decimal_places=8)
    approved_exclusions = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    related_party_revenue_reduction = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    owner_contribution_allowance = models.DecimalField(max_digits=24, decimal_places=8, default=Decimal("0"))
    regulatory_expense = models.DecimalField(max_digits=24, decimal_places=8)
    current_result = models.DecimalField(max_digits=24, decimal_places=8)
    rolling_result = models.DecimalField(max_digits=24, decimal_places=8)
    acceptable_loss_limit = models.DecimalField(max_digits=24, decimal_places=8)
    squad_cost = models.DecimalField(max_digits=24, decimal_places=8)
    wage_cost = models.DecimalField(max_digits=24, decimal_places=8)
    squad_cost_ratio = models.DecimalField(max_digits=12, decimal_places=6, null=True, blank=True)
    wage_to_revenue_ratio = models.DecimalField(max_digits=12, decimal_places=6, null=True, blank=True)
    reasons = models.JSONField(default=list, blank=True)
    window_evidence = models.JSONField(default=dict, blank=True)
    checksum = models.CharField(max_length=64)
    assessed_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()

    def save(self, *args, **kwargs):
        if self.pk and type(self).objects.filter(pk=self.pk).exists():
            raise BudgetIntegrityError("Regulatory FFP assessments are immutable; create a new assessment")
        return super().save(*args, **kwargs)

    def delete(self, *args, **kwargs):
        raise BudgetIntegrityError("Regulatory FFP assessments cannot be deleted")

    class Meta:
        ordering = ["-assessed_at", "id"]
        constraints = [models.CheckConstraint(condition=~Q(checksum=""), name="cf_reg_assess_checksum_nonempty")]
        indexes = [
            models.Index(fields=["club", "target_period", "status"], name="cf_reg_assess_club_idx"),
            models.Index(fields=["federation", "status", "assessed_at"], name="cf_reg_assess_fed_idx"),
        ]


class FFPRegulatorySubmission(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    club = models.ForeignKey("clubs.Club", on_delete=models.PROTECT, related_name="ffp_regulatory_submissions")
    federation = models.ForeignKey("federations.Federation", on_delete=models.PROTECT, related_name="ffp_regulatory_submissions")
    assessment = models.OneToOneField(FFPRegulatoryAssessment, on_delete=models.PROTECT, related_name="submission")
    status = models.CharField(max_length=20, choices=RegulatorySubmissionStatus.choices, default=RegulatorySubmissionStatus.DRAFT)
    attestation_text = models.TextField(blank=True, default="")
    attested_by = models.ForeignKey("users.User", null=True, blank=True, on_delete=models.PROTECT, related_name="ffp_attestations")
    attested_at = models.DateTimeField(null=True, blank=True)
    submitted_at = models.DateTimeField(null=True, blank=True)
    payload_checksum = models.CharField(max_length=64)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["-created_at", "id"]
        constraints = [models.CheckConstraint(condition=~Q(payload_checksum=""), name="cf_reg_submit_checksum_nonempty")]
        indexes = [models.Index(fields=["federation", "status", "created_at"], name="cf_reg_submit_queue_idx")]


class FFPFederationReviewCase(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    submission = models.OneToOneField(FFPRegulatorySubmission, on_delete=models.PROTECT, related_name="review_case")
    federation = models.ForeignKey("federations.Federation", on_delete=models.PROTECT, related_name="ffp_review_cases")
    status = models.CharField(max_length=24, choices=FederationReviewStatus.choices, default=FederationReviewStatus.OPEN)
    assigned_to = models.ForeignKey("users.User", null=True, blank=True, on_delete=models.PROTECT, related_name="assigned_ffp_cases")
    findings = models.JSONField(default=dict, blank=True)
    sanctions = models.JSONField(default=list, blank=True)
    opened_at = models.DateTimeField(auto_now_add=True)
    decided_at = models.DateTimeField(null=True, blank=True)
    closed_at = models.DateTimeField(null=True, blank=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["-opened_at", "id"]
        indexes = [models.Index(fields=["federation", "status", "opened_at"], name="cf_reg_case_queue_idx")]


class FFPRegulatoryCaseEvent(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    case = models.ForeignKey(FFPFederationReviewCase, on_delete=models.PROTECT, related_name="events")
    event_type = models.CharField(max_length=48)
    actor = models.ForeignKey("users.User", null=True, blank=True, on_delete=models.PROTECT, related_name="ffp_case_events")
    payload = models.JSONField(default=dict, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()

    def save(self, *args, **kwargs):
        if self.pk and type(self).objects.filter(pk=self.pk).exists():
            raise BudgetIntegrityError("Regulatory case events are immutable")
        return super().save(*args, **kwargs)

    def delete(self, *args, **kwargs):
        raise BudgetIntegrityError("Regulatory case events cannot be deleted")

    class Meta:
        ordering = ["created_at", "id"]
        indexes = [models.Index(fields=["case", "created_at"], name="cf_reg_case_event_idx")]


# ---------------------------------------------------------------------------
# Native GL regulatory dimensions / rulebook accounting treatment
# ---------------------------------------------------------------------------
from .regulatory_constants import (
    GLRegulatoryPolicyStatus,
    GLRegulatoryClassificationType,
)


class RegulatoryPolicyLifecycleQuerySet(models.QuerySet):
    def update(self, **kwargs):
        raise BudgetIntegrityError("Regulatory treatment policies cannot be bulk-updated")

    def delete(self):
        raise BudgetIntegrityError("Regulatory treatment policies cannot be bulk-deleted")


class RegulatoryRuleLifecycleQuerySet(models.QuerySet):
    def update(self, **kwargs):
        raise BudgetIntegrityError("Regulatory treatment rules cannot be bulk-updated")

    def delete(self):
        raise BudgetIntegrityError("Regulatory treatment rules cannot be bulk-deleted")


class GLRegulatoryTreatmentPolicy(models.Model):
    """Versioned federation policy that classifies GL lines for regulatory FFP.

    The policy is editable only while DRAFT. Publishing seals its ordered rule
    matrix into ``policy_hash``; Unified Rulebook may then bind that exact policy
    version to effective dates / seasons / competitions.
    """
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    federation = models.ForeignKey(
        "federations.Federation", on_delete=models.PROTECT,
        related_name="gl_regulatory_treatment_policies",
    )
    name = models.CharField(max_length=140)
    version = models.PositiveIntegerField(default=1)
    status = models.CharField(
        max_length=16, choices=GLRegulatoryPolicyStatus.choices,
        default=GLRegulatoryPolicyStatus.DRAFT,
    )
    effective_from = models.DateTimeField()
    effective_to = models.DateTimeField(null=True, blank=True)
    default_revenue_classification = models.CharField(
        max_length=32, choices=GLRegulatoryClassificationType.choices,
        default=GLRegulatoryClassificationType.RELEVANT_REVENUE,
    )
    default_expense_classification = models.CharField(
        max_length=32, choices=GLRegulatoryClassificationType.choices,
        default=GLRegulatoryClassificationType.RELEVANT_EXPENSE,
    )
    default_balance_sheet_classification = models.CharField(
        max_length=32, choices=GLRegulatoryClassificationType.choices,
        default=GLRegulatoryClassificationType.NON_RELEVANT,
    )
    require_full_classification = models.BooleanField(default=True)
    rules = models.JSONField(default=dict, blank=True)
    notes = models.TextField(blank=True, default="")
    policy_hash = models.CharField(max_length=64, blank=True, default="")
    published_at = models.DateTimeField(null=True, blank=True)
    published_by = models.ForeignKey(
        "users.User", null=True, blank=True, on_delete=models.PROTECT,
        related_name="published_gl_regulatory_treatment_policies",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    objects = RegulatoryPolicyLifecycleQuerySet.as_manager()

    def save(self, *args, **kwargs):
        if self.pk:
            previous = type(self).objects.filter(pk=self.pk).values("status", "policy_hash").first()
            if previous and previous["status"] == GLRegulatoryPolicyStatus.PUBLISHED:
                raise BudgetIntegrityError("Published GL regulatory treatment policies are immutable")
        return super().save(*args, **kwargs)

    def delete(self, *args, **kwargs):
        if self.status == GLRegulatoryPolicyStatus.PUBLISHED:
            raise BudgetIntegrityError("Published GL regulatory treatment policies cannot be deleted")
        return super().delete(*args, **kwargs)

    class Meta:
        ordering = ["federation_id", "-effective_from", "-version", "id"]
        constraints = [
            models.UniqueConstraint(
                fields=["federation", "name", "version"],
                name="cf_gl_reg_policy_version_uq",
            ),
            models.CheckConstraint(
                condition=Q(effective_to__isnull=True) | Q(effective_to__gt=F("effective_from")),
                name="cf_gl_reg_policy_dates_order",
            ),
        ]
        indexes = [
            models.Index(fields=["federation", "status", "effective_from"], name="cf_gl_reg_policy_active_idx"),
        ]


class GLRegulatoryTreatmentRule(models.Model):
    """Ordered rule in a GL regulatory treatment policy matrix."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    policy = models.ForeignKey(
        GLRegulatoryTreatmentPolicy, on_delete=models.PROTECT,
        related_name="treatment_rules",
    )
    ordinal = models.PositiveIntegerField()
    priority = models.IntegerField(default=0)
    account_codes = models.JSONField(default=list, blank=True)
    account_types = models.JSONField(default=list, blank=True)
    source_types = models.JSONField(default=list, blank=True)
    metadata_match = models.JSONField(default=dict, blank=True)
    classification = models.CharField(max_length=32, choices=GLRegulatoryClassificationType.choices)
    dimensions = models.JSONField(default=list, blank=True)
    exclusion_type = models.CharField(max_length=32, blank=True, default="")
    require_review = models.BooleanField(default=False)
    notes = models.CharField(max_length=255, blank=True, default="")
    created_at = models.DateTimeField(auto_now_add=True)
    objects = RegulatoryRuleLifecycleQuerySet.as_manager()

    def save(self, *args, **kwargs):
        if self.policy_id:
            status = GLRegulatoryTreatmentPolicy.objects.filter(pk=self.policy_id).values_list("status", flat=True).first()
            if status == GLRegulatoryPolicyStatus.PUBLISHED:
                raise BudgetIntegrityError("Rules of a published GL regulatory treatment policy are immutable")
        return super().save(*args, **kwargs)

    def delete(self, *args, **kwargs):
        if self.policy.status == GLRegulatoryPolicyStatus.PUBLISHED:
            raise BudgetIntegrityError("Rules of a published GL regulatory treatment policy cannot be deleted")
        return super().delete(*args, **kwargs)

    class Meta:
        ordering = ["policy_id", "ordinal", "id"]
        constraints = [
            models.UniqueConstraint(fields=["policy", "ordinal"], name="cf_gl_reg_rule_ordinal_uq"),
        ]
        indexes = [
            models.Index(fields=["policy", "priority", "ordinal"], name="cf_gl_reg_rule_priority_idx"),
        ]


class JournalLineRegulatoryClassification(models.Model):
    """Immutable regulatory projection attached natively to one posted GL line."""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    journal_line = models.OneToOneField(
        JournalLine, on_delete=models.PROTECT, related_name="regulatory_classification",
    )
    entry = models.ForeignKey(
        JournalEntry, on_delete=models.PROTECT, related_name="regulatory_classifications",
    )
    club = models.ForeignKey(
        "clubs.Club", on_delete=models.PROTECT, related_name="gl_regulatory_classifications",
    )
    period = models.ForeignKey(
        AccountingPeriod, on_delete=models.PROTECT, related_name="gl_regulatory_classifications",
    )
    federation = models.ForeignKey(
        "federations.Federation", null=True, blank=True, on_delete=models.PROTECT,
        related_name="gl_regulatory_classifications",
    )
    policy = models.ForeignKey(
        GLRegulatoryTreatmentPolicy, null=True, blank=True, on_delete=models.PROTECT,
        related_name="line_classifications",
    )
    policy_rule = models.ForeignKey(
        GLRegulatoryTreatmentRule, null=True, blank=True, on_delete=models.PROTECT,
        related_name="line_classifications",
    )
    classification = models.CharField(max_length=32, choices=GLRegulatoryClassificationType.choices)
    dimensions = models.JSONField(default=list, blank=True)
    exclusion_type = models.CharField(max_length=32, blank=True, default="")
    automated = models.BooleanField(default=True)
    requires_review = models.BooleanField(default=False)
    source_context = models.JSONField(default=dict, blank=True)
    rulebook_governance = models.JSONField(default=dict, blank=True)
    classification_hash = models.CharField(max_length=64)
    created_at = models.DateTimeField(auto_now_add=True)
    objects = ImmutableFinanceQuerySet.as_manager()

    def save(self, *args, **kwargs):
        if self.pk and type(self).objects.filter(pk=self.pk).exists():
            raise BudgetIntegrityError("GL regulatory classifications are immutable; reclassify via a corrective journal")
        return super().save(*args, **kwargs)

    def delete(self, *args, **kwargs):
        raise BudgetIntegrityError("GL regulatory classifications cannot be deleted")

    class Meta:
        ordering = ["entry__accounting_date", "entry_id", "journal_line__line_no"]
        constraints = [
            models.CheckConstraint(condition=~Q(classification_hash=""), name="cf_gl_reg_class_hash_nonempty"),
        ]
        indexes = [
            models.Index(fields=["club", "period", "classification"], name="cf_gl_reg_class_period_idx"),
            models.Index(fields=["policy", "classification"], name="cf_gl_reg_class_policy_idx"),
        ]
