from django.contrib.contenttypes.fields import GenericForeignKey
from django.db import models
from django.conf import settings
from django.utils import timezone
import secrets
import time
from apps.core.uploads import tenant_upload_path


def uuid7() -> str:
    """Generate a UUID v7 (time-ordered UUID)."""
    nanoseconds = time.time_ns()
    uuid_int = (nanoseconds << 16) | secrets.randbits(48)
    return f"{uuid_int:032x}"


def _generate_project_number(organization_id, dept_code: str) -> str:
    """Next project number for an org, formatted <CODE>.<YY>.<NNN> (e.g. ECO.26.001).

    Sequence is scoped to the org + prefix + year, so each department code
    restarts at 001 each year. Falls back to PRJ when no department code is
    available (departments are optional, and a department may have no code).
    """
    prefix = (dept_code or "").strip().upper() or Project.NUMBER_FALLBACK_PREFIX
    yy = f"{timezone.now().year % 100:02d}"
    stem = f"{prefix}.{yy}."
    last = (
        Project.objects.filter(organization_id=organization_id, project_number__startswith=stem)
        .order_by("-project_number")
        .values_list("project_number", flat=True)
        .first()
    )
    seq = 1
    if last:
        tail = last[len(stem):]
        if tail.isdigit():
            seq = int(tail) + 1
    return f"{stem}{seq:03d}"


class Project(models.Model):
    """Project management."""
    NUMBER_FALLBACK_PREFIX = "PRJ"

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    organization = models.ForeignKey("companies.Organization", on_delete=models.CASCADE, related_name="projects")
    name = models.CharField(max_length=255)
    project_number = models.CharField(
        max_length=32,
        blank=True,
        help_text="Auto-assigned on create, e.g. ECO.26.001. Frozen once set.",
    )
    description = models.TextField(blank=True)
    # Research departments that own this project. Only departments marked
    # is_research are offered in the UI.
    departments = models.ManyToManyField(
        "companies.Department",
        blank=True,
        related_name="projects",
    )
    status = models.CharField(max_length=20, choices=[
        ("planning", "Planning"),
        ("active", "Active"),
        ("on_hold", "On Hold"),
        ("completed", "Completed"),
        ("archived", "Archived"),
    ], default="planning")
    lead = models.ForeignKey(
        "hr.Employee",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="led_projects",
    )
    start_date = models.DateField(null=True, blank=True)
    end_date = models.DateField(null=True, blank=True)
    tags = models.JSONField(default=list)
    # How financials are displayed: convert everything to IDR, or keep each
    # entry in its original currency (per-currency breakdown).
    DISPLAY_CURRENCY_CHOICES = [("idr", "Convert to Rupiah (IDR)"), ("original", "Use original currency")]
    display_currency = models.CharField(max_length=10, choices=DISPLAY_CURRENCY_CHOICES, default="idr")
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "projects"
        ordering = ["-created_at"]
        indexes = [
            models.Index(fields=["status"]),
            models.Index(fields=["start_date"]),
            models.Index(fields=["end_date"]),
            models.Index(fields=["organization", "project_number"]),
        ]
        constraints = [
            # blank numbers are allowed (legacy rows); only assigned ones must be unique.
            models.UniqueConstraint(
                fields=["organization", "project_number"],
                condition=~models.Q(project_number=""),
                name="unique_project_number_per_org",
            )
        ]

    def __str__(self):
        return self.name

    def assign_project_number(self) -> str:
        """Assign project_number from the first research department's code.

        Called after M2M departments are set (they can't be read during save()
        on create). No-op once a number exists — the number is frozen, so
        editing departments later never renumbers the project.
        """
        if self.project_number:
            return self.project_number
        dept = self.departments.order_by("name").first()
        self.project_number = _generate_project_number(
            self.organization_id, dept.code if dept else ""
        )
        self.save(update_fields=["project_number"])
        return self.project_number


class ProjectDocument(models.Model):
    """Documents attached to projects."""
    ACCESS_PUBLIC = "public"
    ACCESS_RESTRICTED = "restricted"
    ACCESS_CHOICES = [(ACCESS_PUBLIC, "Public"), (ACCESS_RESTRICTED, "Restricted")]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="documents")
    name = models.CharField(max_length=255)
    file = models.FileField(upload_to=tenant_upload_path("project-documents"))
    uploaded_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True)
    access_level = models.CharField(max_length=20, choices=ACCESS_CHOICES, default=ACCESS_PUBLIC)
    # When access_level=restricted, only uploader + these users can read.
    allowed_users = models.ManyToManyField(
        settings.AUTH_USER_MODEL,
        blank=True,
        related_name="accessible_project_documents",
    )
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "project_documents"
        ordering = ["-created_at"]

    def can_be_read_by(self, user) -> bool:
        if self.access_level == self.ACCESS_PUBLIC:
            return True
        if not getattr(user, "is_authenticated", False):
            return False
        if user.is_superuser:
            return True
        if self.uploaded_by_id == user.id:
            return True
        return self.allowed_users.filter(id=user.id).exists()

    @classmethod
    def visible_to(cls, queryset, user):
        """DB-level equivalent of can_be_read_by. Apply on a ProjectDocument queryset."""
        from django.db.models import Q
        public_q = Q(access_level=cls.ACCESS_PUBLIC)
        if not getattr(user, "is_authenticated", False):
            return queryset.filter(public_q)
        if user.is_superuser:
            return queryset
        return queryset.filter(
            public_q | Q(uploaded_by_id=user.id) | Q(allowed_users=user)
        ).distinct()


class ProjectTeamMember(models.Model):
    """Team members assigned to projects."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="team_members")
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="project_memberships")
    role = models.CharField(max_length=20, choices=[
        ("owner", "Owner"),
        ("manager", "Manager"),
        ("member", "Member"),
        ("viewer", "Viewer"),
    ], default="member")
    joined_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "project_team_members"
        unique_together = ["project", "user"]
        ordering = ["role", "joined_at"]


class ProjectFinance(models.Model):
    """Income/expense ledger entries per project."""
    LEDGER_CHOICES = [
        ("general", "General Ledger"),
        ("project", "Project Ledger"),
        ("event", "Event Budget Ledger"),
        ("expense", "Expense Ledger"),
        ("apar", "AP/AR Ledger"),
        ("procurement", "Procurement Ledger"),
        ("grant", "Grant Ledger"),
        ("asset", "Asset Ledger"),
        ("cashbank", "Cash/Bank Ledger"),
        ("audit", "Audit Ledger"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="finance_entries")
    # Which fund this entry draws down. CashAdvance and PaymentRequestForm both
    # already record the fund when spending is authorized; without it here the
    # fund is lost at booking time, and donor actuals have to be inferred from
    # project + budget_item. BudgetItem is a shared catalog code, so on a
    # co-funded project that inference sums one donor's spend into another's.
    # Null means unattributed (a bookkeeping gap) or internal budget
    # (a fund whose donor is NULL) — neither is visible to any donor.
    fund = models.ForeignKey(
        "ProjectFund", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="finance_entries", db_index=True,
        help_text="Fund this entry draws down. Required for donor-facing spend attribution.",
    )
    description = models.CharField(max_length=255)
    amount = models.DecimalField(max_digits=14, decimal_places=2)
    currency = models.CharField(max_length=10, default="IDR")
    # amount converted to IDR at the rate on `date` (reporting/pivot base).
    amount_idr = models.DecimalField(max_digits=20, decimal_places=2, null=True, blank=True)
    type = models.CharField(max_length=10, choices=[("income", "Income"), ("expense", "Expense")], default="expense")
    ledger = models.CharField(max_length=20, choices=LEDGER_CHOICES, default="general")
    category = models.CharField(max_length=100, blank=True)
    budget_category = models.ForeignKey(
        "finance.BudgetCategory", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="project_finance_entries", help_text="Budget category this entry maps to.",
    )
    budget_item = models.ForeignKey(
        "finance.BudgetItem", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="finance_entries", help_text="Budget item (catalog code) this entry maps to.",
    )
    date = models.DateField(null=True, blank=True)
    receipt = models.FileField(
        upload_to=tenant_upload_path("project-finance-receipts"), null=True, blank=True,
        help_text="Receipt image or PDF for this expense.",
    )
    # Approval workflow. Expenses are pledged on submit (pending) and only count
    # as realized spend once approved by finance / project manager.
    APPROVAL_CHOICES = [
        ("pending", "Pending"),
        ("approved", "Approved"),
        ("rejected", "Rejected"),
    ]
    approval_status = models.CharField(
        max_length=10, choices=APPROVAL_CHOICES, default="pending", db_index=True,
    )
    approved_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="approved_project_finance",
    )
    approved_at = models.DateTimeField(null=True, blank=True)
    created_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="created_project_finance",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_finance"
        ordering = ["-date", "-created_at"]
        indexes = [
            models.Index(fields=["type"]),
            models.Index(fields=["ledger"]),
            models.Index(fields=["date"]),
            models.Index(fields=["project", "type"]),
            models.Index(fields=["project", "date"]),
            models.Index(fields=["approval_status"]),
        ]


class CashAdvance(models.Model):
    """Cash advance issued against a project fund.

    An advance is NOT an expense when issued — cash moves from Cash/Bank into
    an "Advance" asset account (see apps.accounting), so it does not reduce
    the fund's recognized "spent" total. Only on settlement does the spent
    portion reclass into a real Expense account (and get mirrored into
    ProjectFinance so the existing expense views/totals pick it up).
    """
    STATUS_CHOICES = [
        ("issued", "Issued"),
        ("settled", "Settled"),
        ("cancelled", "Cancelled"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="cash_advances")
    fund = models.ForeignKey(
        "ProjectFund", on_delete=models.PROTECT, related_name="cash_advances",
    )
    recipient = models.ForeignKey(
        "hr.Employee", on_delete=models.PROTECT, related_name="cash_advances",
        help_text="Employee/staff the advance was issued to.",
    )
    purpose = models.CharField(max_length=255)
    budget_item = models.ForeignKey(
        "finance.BudgetItem", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="cash_advances",
    )
    amount = models.DecimalField(max_digits=14, decimal_places=2)
    currency = models.CharField(max_length=10, default="IDR")
    amount_idr = models.DecimalField(max_digits=18, decimal_places=2, null=True, blank=True)
    date_issued = models.DateField()
    status = models.CharField(max_length=10, choices=STATUS_CHOICES, default="issued", db_index=True)

    # Settlement (liquidation) fields — filled in when the advance is settled.
    spent_amount = models.DecimalField(max_digits=14, decimal_places=2, null=True, blank=True)
    returned_amount = models.DecimalField(max_digits=14, decimal_places=2, null=True, blank=True)
    settlement_receipt = models.FileField(
        upload_to=tenant_upload_path("cash-advance-receipts"), null=True, blank=True,
    )
    return_receipt = models.FileField(
        upload_to=tenant_upload_path("cash-advance-return-receipts"), null=True, blank=True,
        help_text="Proof of unspent cash returned (deposit slip, receipt), required when returned_amount > 0.",
    )
    settlement_notes = models.TextField(blank=True)
    settled_at = models.DateTimeField(null=True, blank=True)
    settled_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="settled_cash_advances",
    )

    # Links into the double-entry engine and the expense ledger.
    issue_journal_entry = models.ForeignKey(
        "accounting.JournalEntry", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="+", help_text="Dr Advance Asset / Cr Cash journal posted on issue.",
    )
    settlement_journal_entry = models.ForeignKey(
        "accounting.JournalEntry", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="+", help_text="Dr Expense (+ Dr Cash if returned) / Cr Advance Asset journal posted on settle.",
    )
    project_finance_entry = models.OneToOneField(
        ProjectFinance, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="cash_advance",
        help_text="Mirrored expense row created on settlement so existing expense totals include it.",
    )

    created_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="created_cash_advances",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_cash_advances"
        ordering = ["-date_issued", "-created_at"]
        indexes = [
            models.Index(fields=["status"]),
            models.Index(fields=["fund", "status"]),
        ]

    def __str__(self):
        return f"Advance {self.amount} {self.currency} to {self.recipient_id} ({self.status})"


class ProjectDissemination(models.Model):
    """Outreach / dissemination tied to a project. Optionally references an Event or Publication."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="disseminations")
    title = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    date = models.DateField(null=True, blank=True)
    location = models.CharField(max_length=255, blank=True)
    event = models.ForeignKey(
        "events.Event",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="project_disseminations",
    )
    publication = models.ForeignKey(
        "publications.Publication",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="project_disseminations",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_disseminations"
        ordering = ["-date", "-created_at"]


_FUND_CODE_ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"


def _generate_fund_code() -> str:
    """Random 7-char fund code, retried on the (vanishingly rare) collision."""
    while True:
        code = "".join(secrets.choice(_FUND_CODE_ALPHABET) for _ in range(7))
        if not ProjectFund.objects.filter(fund_code=code).exists():
            return code


def _generate_grant_number() -> str:
    """Random grant number (GR- + 7 chars) auto-filled when staff leave the
    donor-issued number empty; staff can overwrite with the real one."""
    while True:
        code = "GR-" + "".join(secrets.choice(_FUND_CODE_ALPHABET) for _ in range(7))
        if not ProjectFund.objects.filter(grant_number=code).exists():
            return code


class ProjectFund(models.Model):
    """Funding source for a project. A project may have multiple funds."""
    STATUS_CHOICES = [
        ("proposed", "Proposed"),
        ("pledged", "Pledged"),
        ("partially_received", "Partially Received"),
        ("received", "Received"),
        ("allocated", "Allocated"),
        ("closed", "Closed"),
        ("rejected", "Rejected"),
    ]
    FUND_TYPE_CHOICES = [
        ("grant", "Grant"),
        ("sponsorship", "Sponsorship"),
        ("donation", "Donation"),
        ("cofunding", "Co-funding"),
        ("other", "Other"),
    ]
    CONVERSION_TIMING_CHOICES = [
        ("on_arrival", "Convert when fund arrives"),
        ("on_payment", "Convert when you pay"),
        ("on_project_end", "Convert by the end of the project"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    organization = models.ForeignKey("companies.Organization", on_delete=models.CASCADE, related_name="project_funds")
    project = models.ForeignKey(
        Project, on_delete=models.SET_NULL, related_name="funds",
        null=True, blank=True,
        help_text="Owning project. Null while the fund is still a prospect/proposal (fund-first flow).",
    )
    source = models.CharField(max_length=255, help_text="Donor / grantor / funding body name")
    fund_code = models.CharField(
        max_length=7, unique=True, null=True, blank=True, editable=False,
        help_text="Random 7-character internal fund ID (A–Z, 0–9), e.g. K7Q2M9X.",
    )
    grant_number = models.CharField(
        max_length=100, blank=True,
        help_text="Donor-issued grant/agreement number, shown in the donor portal.",
    )
    fund_type = models.CharField(
        max_length=20, choices=FUND_TYPE_CHOICES, default="grant", db_index=True,
        help_text="Funding mechanism. 'grant' is the most common subset.",
    )
    donor = models.ForeignKey(
        "companies.Company",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="funds_provided",
        help_text="Canonical donor record. Auto-linked by name when missing.",
    )
    amount = models.DecimalField(max_digits=14, decimal_places=2)
    currency = models.CharField(max_length=10, default="IDR")
    # IDR is the reporting currency. For foreign funds we store the FX rate on the
    # received date and the converted amount (auto-fetched in the ViewSet).
    exchange_rate = models.DecimalField(
        max_digits=18, decimal_places=6, null=True, blank=True,
        help_text="IDR per 1 unit of currency, on received_date. 1 for IDR.",
    )
    amount_idr = models.DecimalField(
        max_digits=20, decimal_places=2, null=True, blank=True,
        help_text="amount converted to IDR at exchange_rate.",
    )
    conversion_timing = models.CharField(
        max_length=20, choices=CONVERSION_TIMING_CHOICES, default="on_arrival",
        help_text="When the FX rate for this fund's conversions is taken.",
    )
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="pledged")
    frozen = models.BooleanField(
        default=False, db_index=True,
        help_text="Frozen funds are locked: no edits or new allocations until unfrozen.",
    )
    agreement_date = models.DateField(null=True, blank=True)
    received_date = models.DateField(null=True, blank=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_funds"
        ordering = ["-agreement_date", "-created_at"]
        indexes = [
            # Cash statements/reports filter funds by lifecycle status.
            models.Index(fields=["status"]),
        ]

    def save(self, *args, **kwargs):
        if not self.fund_code:
            self.fund_code = _generate_fund_code()
        if not self.grant_number:
            self.grant_number = _generate_grant_number()
        super().save(*args, **kwargs)

    def __str__(self):
        return f"{self.source} — {self.project.name if self.project else 'Unassigned'}"


class ProjectFundAllocation(models.Model):
    """Allocates part of a fund's amount to a budget item (catalog code, e.g. B.1)."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="allocations")
    budget_item = models.ForeignKey(
        "finance.BudgetItem", on_delete=models.PROTECT, related_name="allocations",
        help_text="Budget item this slice maps to.",
    )
    # Grant budget-line breakdown. amount = quantity * unit_cost(fund ccy)
    # * frequency * (time_allocated_pct / 100), computed on save.
    unit = models.CharField(max_length=50, blank=True, help_text="Unit label, e.g. person, month, trip.")
    quantity = models.DecimalField(max_digits=12, decimal_places=2, default=1)
    frequency = models.DecimalField(max_digits=12, decimal_places=2, default=1)
    unit_cost = models.DecimalField(max_digits=14, decimal_places=2, default=0, help_text="Unit cost as entered, in unit_cost_currency.")
    unit_cost_currency = models.CharField(max_length=10, blank=True, help_text="Currency of unit_cost; defaults to the fund currency.")
    unit_cost_fund_ccy = models.DecimalField(
        max_digits=14, decimal_places=2, default=0,
        help_text="unit_cost converted to the fund currency (used in amount).",
    )
    time_allocated_pct = models.DecimalField(max_digits=6, decimal_places=2, default=100)
    amount = models.DecimalField(max_digits=14, decimal_places=2, default=0, help_text="Computed, in fund currency.")
    note = models.CharField(max_length=255, blank=True)
    version = models.PositiveIntegerField(
        default=1, help_text="Bumped each time an approved revision is applied.",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def save(self, *args, **kwargs):
        from decimal import Decimal
        from .fx import convert_between

        fund_ccy = (self.fund.currency if self.fund_id else "IDR") or "IDR"
        cost_ccy = (self.unit_cost_currency or fund_ccy).upper()
        self.unit_cost_currency = cost_ccy

        converted = convert_between(self.unit_cost or Decimal("0"), cost_ccy, fund_ccy, self.fund if self.fund_id else None)
        self.unit_cost_fund_ccy = converted if converted is not None else (self.unit_cost or Decimal("0"))

        self.amount = (
            (self.quantity or 0) * (self.unit_cost_fund_ccy or 0) * (self.frequency or 0)
            * (self.time_allocated_pct or 0) / 100
        )
        super().save(*args, **kwargs)

    class Meta:
        db_table = "project_fund_allocations"
        ordering = ["-created_at"]
        constraints = [
            models.UniqueConstraint(fields=["fund", "budget_item"], name="uniq_fund_budget_item"),
        ]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return f"{self.fund_id} → {self.budget_item_id}: {self.amount}"


class ProjectFundAllocationRevision(models.Model):
    """A proposed create/update/delete of a fund allocation, pending approval.

    Nothing writes to ProjectFundAllocation directly — every change (including
    new allocations) goes through this table so donor reporting can show a
    full before/after trail with who proposed and who approved each change.
    """
    ACTION_CHOICES = [
        ("create", "Create"),
        ("update", "Update"),
        ("delete", "Delete"),
    ]
    STATUS_CHOICES = [
        ("pending", "Pending"),
        ("approved", "Approved"),
        ("rejected", "Rejected"),
    ]
    FIELDS = [
        "budget_item", "unit", "quantity", "frequency",
        "unit_cost", "unit_cost_currency", "time_allocated_pct", "note",
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="allocation_revisions")
    allocation = models.ForeignKey(
        ProjectFundAllocation, on_delete=models.CASCADE, null=True, blank=True,
        related_name="revisions",
        help_text="Target allocation for update/delete. Null for create.",
    )
    action = models.CharField(max_length=10, choices=ACTION_CHOICES)
    status = models.CharField(max_length=10, choices=STATUS_CHOICES, default="pending", db_index=True)
    # Proposed field values (subset of ProjectFundAllocation.FIELDS). Empty for delete.
    proposed_data = models.JSONField(default=dict, blank=True)
    # Snapshot of the allocation's fields at propose time (empty for create).
    previous_data = models.JSONField(default=dict, blank=True)
    reason = models.CharField(max_length=500, blank=True, help_text="Why this change is being proposed.")

    proposed_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="proposed_allocation_revisions",
    )
    proposed_at = models.DateTimeField(auto_now_add=True)
    decided_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="decided_allocation_revisions",
    )
    decided_at = models.DateTimeField(null=True, blank=True)
    decision_note = models.CharField(max_length=500, blank=True)

    class Meta:
        db_table = "project_fund_allocation_revisions"
        ordering = ["-proposed_at"]
        indexes = [models.Index(fields=["fund", "status"])]

    def __str__(self):
        return f"{self.get_action_display()} revision on {self.allocation_id or self.fund_id} ({self.status})"


class ProjectSchedule(models.Model):
    """Project milestones / scheduled phases."""
    STATUS_CHOICES = [
        ("planned", "Planned"),
        ("in_progress", "In Progress"),
        ("completed", "Completed"),
        ("delayed", "Delayed"),
        ("cancelled", "Cancelled"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="schedule_items")
    title = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    start_date = models.DateField(null=True, blank=True)
    end_date = models.DateField(null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="planned", db_index=True)
    completed_date = models.DateField(null=True, blank=True)
    revised_end_date = models.DateField(
        null=True, blank=True,
        help_text="New target date when the milestone slipped; end_date keeps the original commitment.",
    )
    donor_visible = models.BooleanField(
        default=True, help_text="Shown on the donor portal timeline when true.",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_schedule"
        ordering = ["start_date", "-created_at"]


class ProjectSettings(models.Model):
    """One row per organization — access rules and the AI-seeded
    proposal/report templates for the Projects module."""
    ACCESS_LEVELS = [
        ("everyone", "Everyone in the organization"),
        ("members", "Project team members only"),
        ("admins", "Administrators only"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    organization = models.OneToOneField("companies.Organization", on_delete=models.CASCADE, related_name="project_settings")

    access_level = models.CharField(max_length=20, choices=ACCESS_LEVELS, default="members")
    donor_pic_can_access = models.BooleanField(default=False)

    proposal_template = models.TextField(blank=True)
    report_template = models.TextField(blank=True)

    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_settings"

    def __str__(self):
        return "Project Settings"

    @classmethod
    def get_solo(cls, organization) -> "ProjectSettings":
        obj, _ = cls.objects.get_or_create(organization=organization)
        return obj

class FundingOpportunity(models.Model):
    """An open call / prospect this fund originated from — tracked before a
    proposal is submitted."""
    STAGE_CHOICES = [
        ("screening", "Screening"),
        ("applying", "Applying"),
        ("converted", "Converted"),
        ("dropped", "Dropped"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="opportunities")
    name = models.CharField(max_length=255)
    donor = models.ForeignKey(
        "companies.Company", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="funding_opportunities",
    )
    deadline = models.DateField(null=True, blank=True)
    ceiling = models.DecimalField(max_digits=14, decimal_places=2, null=True, blank=True)
    stage = models.CharField(max_length=20, choices=STAGE_CHOICES, default="screening", db_index=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "funding_opportunities"
        ordering = ["-created_at"]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return self.name


class FundingProposal(models.Model):
    """A drafted/submitted proposal tied to a funding source."""
    STATUS_CHOICES = [
        ("draft", "Draft"),
        ("under_review", "Under review"),
        ("approved", "Approved"),
        ("declined", "Declined"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="proposals")
    title = models.CharField(max_length=255)
    submitted_date = models.DateField(null=True, blank=True)
    requested_amount = models.DecimalField(max_digits=14, decimal_places=2, null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="draft", db_index=True)
    document = models.FileField(upload_to=tenant_upload_path("funding-proposals"), null=True, blank=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "funding_proposals"
        ordering = ["-created_at"]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return self.title


# Shared donor review state machine, used by FundReport and Deliverable.
# Donor actions only apply to items sitting in AWAITING_REVIEW states;
# staff (re)submission moves an item into "submitted"/"resubmitted".
REVIEW_AWAITING = {"submitted", "under_review", "resubmitted"}
REVIEW_ACTIONS = {
    "approve": "approved",
    "request_revision": "revision_requested",
    "reject": "rejected",
}


class ReviewableMixin(models.Model):
    """Fields shared by donor-reviewable submissions (reports, deliverables)."""
    reviewed_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True, related_name="+",
        help_text="Donor user who made the latest review decision.",
    )
    reviewed_at = models.DateTimeField(null=True, blank=True)
    review_note = models.CharField(max_length=1000, blank=True)

    class Meta:
        abstract = True

    def apply_review(self, action: str, user, note: str = ""):
        """Apply a donor review decision. Raises ValueError on an illegal
        transition — callers translate that into a 400."""
        if action not in REVIEW_ACTIONS:
            raise ValueError(f"Unknown review action: {action}")
        if self.status not in REVIEW_AWAITING:
            raise ValueError(f"Cannot {action} an item in status '{self.status}'.")
        self.status = REVIEW_ACTIONS[action]
        self.reviewed_by = user
        self.reviewed_at = timezone.now()
        self.review_note = note or ""
        self.save(update_fields=["status", "reviewed_by", "reviewed_at", "review_note", "updated_at"])

    def record_submission(self, *, file, note: str, user):
        """Staff (re)submission: append an immutable version and advance status."""
        from django.contrib.contenttypes.models import ContentType

        content_type = ContentType.objects.get_for_model(type(self))
        last = (
            SubmissionVersion.objects
            .filter(content_type=content_type, object_id=str(self.id))
            .order_by("-version_number")
            .first()
        )
        version = SubmissionVersion.objects.create(
            content_type=content_type, object_id=str(self.id),
            version_number=(last.version_number + 1) if last else 1,
            file=file, note=note or "", submitted_by=user,
        )
        self.status = "resubmitted" if self.status == "revision_requested" else "submitted"
        self.submitted_date = timezone.now().date()
        self.save(update_fields=["status", "submitted_date", "updated_at"])
        return version


class FundReport(ReviewableMixin, models.Model):
    """A narrative/financial report owed to the donor under an agreement."""
    KIND_CHOICES = [
        ("narrative", "Narrative"),
        ("financial", "Financial"),
        ("monitoring", "Monitoring & Evaluation"),
        ("audit", "Audit"),
        ("final", "Final"),
    ]
    # "upcoming"/"overdue" are the pre-submission lifecycle; the rest is the
    # shared donor review workflow (see REVIEW_AWAITING / REVIEW_ACTIONS).
    STATUS_CHOICES = [
        ("upcoming", "Upcoming"),
        ("overdue", "Overdue"),
        ("submitted", "Submitted"),
        ("under_review", "Under Donor Review"),
        ("revision_requested", "Revision Requested"),
        ("resubmitted", "Resubmitted"),
        ("approved", "Approved"),
        ("rejected", "Rejected"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="reports")
    title = models.CharField(max_length=255)
    kind = models.CharField(max_length=20, choices=KIND_CHOICES, default="narrative")
    period_start = models.DateField(null=True, blank=True, help_text="Reporting period start.")
    period_end = models.DateField(null=True, blank=True, help_text="Reporting period end.")
    due_date = models.DateField(null=True, blank=True)
    submitted_date = models.DateField(null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="upcoming", db_index=True)
    document = models.FileField(upload_to=tenant_upload_path("fund-reports"), null=True, blank=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "fund_reports"
        ordering = ["due_date", "-created_at"]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return self.title


class ProjectFundTranche(models.Model):
    """A scheduled/received disbursement (milestone payment) of a fund."""
    STATUS_CHOICES = [
        ("scheduled", "Scheduled"),
        ("received", "Received"),
        ("overdue", "Overdue"),
        ("cancelled", "Cancelled"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="tranches")
    label = models.CharField(max_length=255)
    amount = models.DecimalField(max_digits=14, decimal_places=2, default=0)
    expected_date = models.DateField(null=True, blank=True)
    received_date = models.DateField(null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="scheduled", db_index=True)
    notes = models.TextField(blank=True)
    journal_entry = models.ForeignKey(
        "accounting.JournalEntry", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="+", help_text="Dr Cash / Cr Deferred Grant Income journal posted when this tranche is marked received.",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_fund_tranches"
        ordering = ["expected_date", "-created_at"]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return f"{self.label}: {self.amount}"


class ProjectFundCompliance(models.Model):
    """A compliance/audit obligation owed under a funding agreement."""
    KIND_CHOICES = [
        ("report", "Report"),
        ("audit", "Audit"),
        ("submission", "Submission"),
    ]
    STATUS_CHOICES = [
        ("upcoming", "Upcoming"),
        ("submitted", "Submitted"),
        ("overdue", "Overdue"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="compliance_items")
    title = models.CharField(max_length=255)
    kind = models.CharField(max_length=20, choices=KIND_CHOICES, default="report")
    due_date = models.DateField(null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="upcoming", db_index=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_fund_compliance"
        ordering = ["due_date", "-created_at"]
        indexes = [models.Index(fields=["fund"])]

    def __str__(self):
        return self.title


class FundDocument(models.Model):
    """A document attached to a fund, classified by category.

    Unifies proposals, agreements, donor reports, and compliance/audit items
    behind one model so the fund Documents tab is a single store with a
    category filter.
    """
    CATEGORY_CHOICES = [
        ("proposal", "Proposal"),
        ("agreement", "Agreement"),
        ("reporting", "Reporting"),
        ("compliance", "Compliance & Audit"),
    ]
    STATUS_CHOICES = [
        ("draft", "Draft"),
        ("submitted", "Submitted"),
        ("approved", "Approved"),
        ("signed", "Signed"),
        ("overdue", "Overdue"),
    ]
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="fund_documents")
    category = models.CharField(max_length=20, choices=CATEGORY_CHOICES, default="proposal", db_index=True)
    title = models.CharField(max_length=255)
    document = models.FileField(upload_to=tenant_upload_path("fund-documents"), null=True, blank=True)
    date = models.DateField(null=True, blank=True, help_text="Signed / submitted / due date depending on category.")
    amount = models.DecimalField(max_digits=14, decimal_places=2, null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, blank=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "fund_documents"
        ordering = ["-date", "-created_at"]
        indexes = [models.Index(fields=["fund", "category"])]

    def __str__(self):
        return f"{self.get_category_display()}: {self.title}"


class SubmissionVersion(models.Model):
    """An immutable submitted file version of a reviewable item (FundReport or
    Deliverable) — attaches via a generic relation so both share one history
    table. Every staff (re)submission appends a row; rows are never edited."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    content_type = models.ForeignKey("contenttypes.ContentType", on_delete=models.CASCADE)
    object_id = models.CharField(max_length=64)
    # The pair above is only half a generic relation without this. It is what
    # lets organization_of walk to the owning FundReport/Deliverable and find a
    # tenant — otherwise these rows have no forward FK to an Organization at
    # all, and both the upload prefix and the download check fall back to
    # "unowned". Adds no column.
    content_object = GenericForeignKey("content_type", "object_id")
    version_number = models.PositiveIntegerField(default=1)
    file = models.FileField(upload_to=tenant_upload_path("submission-versions"))
    note = models.CharField(max_length=500, blank=True, help_text="Change summary for this version.")
    submitted_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True, related_name="+",
    )
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "submission_versions"
        ordering = ["-version_number"]
        constraints = [
            models.UniqueConstraint(
                fields=["content_type", "object_id", "version_number"],
                name="uniq_submission_version",
            ),
        ]
        indexes = [models.Index(fields=["content_type", "object_id"])]

    def __str__(self):
        return f"v{self.version_number} of {self.content_type_id}:{self.object_id}"


class Deliverable(ReviewableMixin, models.Model):
    """A contractual output owed to the donor under a fund (grant) agreement.
    Unlike FundReport (periodic reporting), a deliverable is a concrete
    product: publication, policy brief, dataset, event, etc."""
    TYPE_CHOICES = [
        ("research_report", "Research Report"),
        ("publication", "Publication"),
        ("policy_brief", "Policy Brief"),
        ("dataset", "Dataset"),
        ("training_material", "Training Material"),
        ("event", "Event"),
        ("workshop", "Workshop"),
        ("communication_product", "Communication Product"),
        ("technical_document", "Technical Document"),
        ("other", "Other"),
    ]
    # not_started..ready are internal stages (collapsed to "in preparation" on
    # the donor portal); submitted onward is the shared donor review workflow.
    STATUS_CHOICES = [
        ("not_started", "Not Started"),
        ("in_progress", "In Progress"),
        ("internal_review", "Internal Review"),
        ("ready", "Ready for Submission"),
        ("submitted", "Submitted"),
        ("under_review", "Under Donor Review"),
        ("revision_requested", "Revision Requested"),
        ("resubmitted", "Resubmitted"),
        ("approved", "Approved"),
        ("rejected", "Rejected"),
        ("cancelled", "Cancelled"),
    ]
    INTERNAL_STATUSES = {"not_started", "in_progress", "internal_review", "ready"}

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    fund = models.ForeignKey(ProjectFund, on_delete=models.CASCADE, related_name="deliverables")
    milestone = models.ForeignKey(
        ProjectSchedule, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="deliverables", help_text="Timeline item this deliverable belongs to.",
    )
    title = models.CharField(max_length=255)
    deliverable_type = models.CharField(max_length=30, choices=TYPE_CHOICES, default="research_report")
    description = models.TextField(blank=True)
    acceptance_criteria = models.TextField(blank=True)
    due_date = models.DateField(null=True, blank=True)
    submitted_date = models.DateField(null=True, blank=True)
    approved_date = models.DateField(null=True, blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="not_started", db_index=True)
    donor_visible = models.BooleanField(
        default=True, help_text="Listed on the donor portal when true (statuses still collapsed).",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "deliverables"
        ordering = ["due_date", "-created_at"]
        indexes = [models.Index(fields=["fund", "status"])]

    def __str__(self):
        return self.title

    def apply_review(self, action, user, note=""):
        super().apply_review(action, user, note)
        if self.status == "approved":
            self.approved_date = timezone.now().date()
            self.save(update_fields=["approved_date"])


class ProjectIndicator(models.Model):
    """A results-framework indicator for a project (think-tank vocabulary:
    publications, policy outputs, events, participants — unit is free text)."""
    LEVEL_CHOICES = [
        ("activity", "Activity"),
        ("output", "Output"),
        ("outcome", "Outcome"),
        ("impact", "Impact"),
    ]
    AGGREGATION_CHOICES = [
        ("sum", "Sum of records (cumulative)"),
        ("latest", "Latest record value"),
    ]
    FREQUENCY_CHOICES = [
        ("monthly", "Monthly"),
        ("quarterly", "Quarterly"),
        ("semiannual", "Semi-annual"),
        ("annual", "Annual"),
        ("adhoc", "Ad hoc"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="indicators")
    code = models.CharField(max_length=50, blank=True, help_text="Framework code, e.g. OUT-1.2.")
    name = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    result_level = models.CharField(max_length=20, choices=LEVEL_CHOICES, default="output")
    unit = models.CharField(max_length=50, blank=True, help_text="e.g. publications, participants, events.")
    baseline = models.DecimalField(max_digits=14, decimal_places=2, default=0)
    target = models.DecimalField(max_digits=14, decimal_places=2, default=0)
    aggregation = models.CharField(max_length=10, choices=AGGREGATION_CHOICES, default="sum")
    data_source = models.CharField(max_length=255, blank=True)
    frequency = models.CharField(max_length=20, choices=FREQUENCY_CHOICES, default="quarterly")
    donor_visible = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_indicators"
        ordering = ["code", "name"]
        indexes = [models.Index(fields=["project", "result_level"])]

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

    def current_value(self):
        """Achievement to date, per the aggregation rule. None when unreported."""
        records = list(self.records.order_by("period_date").values_list("value", flat=True))
        if not records:
            return None
        return sum(records) if self.aggregation == "sum" else records[-1]


class IndicatorRecord(models.Model):
    """One reported data point for an indicator."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    indicator = models.ForeignKey(ProjectIndicator, on_delete=models.CASCADE, related_name="records")
    period_date = models.DateField(help_text="End date of the period this value covers.")
    value = models.DecimalField(max_digits=14, decimal_places=2)
    note = models.CharField(max_length=500, blank=True)
    created_by = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True, related_name="+",
    )
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "indicator_records"
        ordering = ["-period_date"]
        indexes = [models.Index(fields=["indicator", "period_date"])]

    def __str__(self):
        return f"{self.indicator_id} @ {self.period_date}: {self.value}"


class ProjectRisk(models.Model):
    """A risk (may happen) or issue (has happened) on a project. Hidden from
    the donor portal unless donor_visible is explicitly set."""
    KIND_CHOICES = [("risk", "Risk"), ("issue", "Issue")]
    CATEGORY_CHOICES = [
        ("financial", "Financial"),
        ("operational", "Operational"),
        ("programmatic", "Programmatic"),
        ("compliance", "Compliance"),
        ("legal", "Legal"),
        ("security", "Security"),
        ("political", "Political"),
        ("reputational", "Reputational"),
        ("partnership", "Partnership"),
        ("other", "Other"),
    ]
    STATUS_CHOICES = [
        ("identified", "Identified"),
        ("assessing", "Under Assessment"),
        ("mitigating", "Mitigation in Progress"),
        ("monitoring", "Monitoring"),
        ("escalated", "Escalated"),
        ("resolved", "Resolved"),
        ("accepted", "Accepted"),
        ("closed", "Closed"),
    ]
    OPEN_STATUSES = {"identified", "assessing", "mitigating", "monitoring", "escalated"}

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="risks")
    kind = models.CharField(max_length=10, choices=KIND_CHOICES, default="risk", db_index=True)
    title = models.CharField(max_length=255)
    category = models.CharField(max_length=20, choices=CATEGORY_CHOICES, default="operational")
    description = models.TextField(blank=True)
    probability = models.PositiveSmallIntegerField(default=3, help_text="1 (rare) – 5 (almost certain).")
    impact = models.PositiveSmallIntegerField(default=3, help_text="1 (negligible) – 5 (severe).")
    mitigation = models.TextField(blank=True, help_text="Mitigation / corrective action plan.")
    owner = models.ForeignKey(
        "core.User", on_delete=models.SET_NULL, null=True, blank=True, related_name="owned_risks",
    )
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="identified", db_index=True)
    target_date = models.DateField(null=True, blank=True, help_text="Target resolution date.")
    resolved_date = models.DateField(null=True, blank=True)
    donor_visible = models.BooleanField(
        default=False, help_text="Fail-closed: risks are internal unless explicitly shared.",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_risks"
        ordering = ["-created_at"]
        indexes = [models.Index(fields=["project", "status"])]

    def __str__(self):
        return self.title

    @property
    def score(self):
        return self.probability * self.impact


class ProjectResource(models.Model):
    """Research data sources and literature used in a project."""

    KIND_CHOICES = [
        ("data", "Data"),
        ("literature", "Literature"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="resources")
    kind = models.CharField(max_length=20, choices=KIND_CHOICES)
    title = models.CharField(max_length=255)
    link = models.URLField(max_length=1000, blank=True)
    # data-only
    detail = models.TextField(blank=True)
    source = models.CharField(max_length=255, blank=True)
    # shared
    year = models.PositiveIntegerField(null=True, blank=True)
    # literature-only
    authors = models.CharField(max_length=500, blank=True)
    publisher = models.CharField(max_length=255, blank=True)
    lit_type = models.CharField(
        max_length=20,
        blank=True,
        choices=[
            ("book", "Book"),
            ("journal", "Journal"),
            ("report", "Report"),
            ("website", "Website"),
            ("other", "Other"),
        ],
    )
    quoted_page = models.CharField(max_length=100, blank=True)
    notes = models.TextField(blank=True)

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

    class Meta:
        db_table = "project_resources"
        ordering = ["-created_at"]
        indexes = [models.Index(fields=["project", "kind"])]

    def __str__(self):
        return f"{self.get_kind_display()}: {self.title}"


class ProjectLog(models.Model):
    """Audit log for all project-related actions."""

    LOG_TYPE_CHOICES = [
        ("project", "Project"),
        ("task", "Task"),
        ("document", "Document"),
        ("finance", "Finance"),
        ("fund", "Fund"),
        ("team", "Team"),
        ("dissemination", "Dissemination"),
        ("schedule", "Schedule"),
        ("resource", "Resource"),
        ("status", "Status"),
        ("payment_request", "Payment Request"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="logs")
    actor = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="project_logs",
    )
    log_type = models.CharField(max_length=20, choices=LOG_TYPE_CHOICES, default="project")
    action = models.CharField(max_length=255)
    detail = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "project_logs"
        ordering = ["-created_at"]
        indexes = [models.Index(fields=["project", "-created_at"])]

    def __str__(self):
        return f"{self.log_type}: {self.action}"


class PaymentRequestForm(models.Model):
    """Digitized CSIS Payment Request Form (PRF) — requests release of budget
    to a vendor/recipient against a specific fund + budget line.

    Approval is a fixed 4-step named chain (Prepared → Reviewed → Budget
    Verified → Approved), matching the paper form's signature blocks — not a
    generic reusable engine, so each step is its own nullable actor/timestamp
    pair rather than a child approval-chain table.
    """
    STATUS_CHOICES = [
        ("draft", "Draft"),
        ("in_review", "In Review"),
        ("budget_verification", "Budget Verification"),
        ("pending_approval", "Pending Approval"),
        ("approved", "Approved"),
        ("rejected", "Rejected"),
    ]

    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="payment_request_forms")
    fund = models.ForeignKey(
        ProjectFund, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="payment_request_forms",
        help_text="Fund this payment draws against (sets currency + donor).",
    )
    budget_item = models.ForeignKey(
        "finance.BudgetItem", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="payment_request_forms",
        help_text="Primary budget line this payment is verified against.",
    )
    number = models.CharField(max_length=50, blank=True, help_text="Human-readable PRF number, e.g. PRF-2026-001.")
    payment_date = models.DateField(null=True, blank=True)
    currency = models.CharField(max_length=10, default="IDR")

    # --- B. Informasi Penerima (recipient) ---
    vendor = models.ForeignKey(
        "asset_management.Vendor", on_delete=models.SET_NULL, null=True, blank=True,
        related_name="payment_request_forms",
        help_text="Optional vendor record used only to autofill name/address/tax id — bank fields are always entered fresh below.",
    )
    recipient_name = models.CharField(max_length=255, blank=True)
    company_name = models.CharField(max_length=255, blank=True)
    address = models.TextField(blank=True)
    npwp_nik = models.CharField(max_length=50, blank=True)
    bank_name = models.CharField(max_length=255, blank=True)
    account_number = models.CharField(max_length=100, blank=True)
    account_holder = models.CharField(max_length=255, blank=True)
    swift_code = models.CharField(max_length=20, blank=True)

    # --- C. Rincian Pembayaran ---
    # List of {"description": str, "budget_line": str, "amount": str}. Snapshot
    # rows, not queried independently — same pattern as SOP/Announcement.attachments.
    line_items = models.JSONField(default=list, blank=True)
    amount = models.DecimalField(max_digits=14, decimal_places=2, default=0, help_text="Total of line_items, in `currency`.")

    # --- D. Keterangan ---
    notes = models.TextField(blank=True)

    # --- E. Lampiran — one PRFAttachment row per uploaded file (see below);
    # "Ya/Tidak" is derived from whether a row exists for that slug.
    ATTACHMENT_SLUGS = [
        "approved_budget", "tor_activity_plan", "purchase_request", "purchase_order",
        "kontrak_spk", "invoice", "quotation", "berita_acara", "kwitansi",
        "attendance_list", "bukti_pajak", "dokumen_lain",
    ]

    # --- F. Verifikasi Budget — snapshotted at the budget-verification step so
    # figures stay audit-safe even if later expenses change the live totals.
    budget_available = models.DecimalField(max_digits=16, decimal_places=2, null=True, blank=True)
    realized_before = models.DecimalField(max_digits=16, decimal_places=2, null=True, blank=True)
    realized_after = models.DecimalField(max_digits=16, decimal_places=2, null=True, blank=True)
    budget_remaining = models.DecimalField(max_digits=16, decimal_places=2, null=True, blank=True)

    # --- G. Persetujuan — fixed 4-step named chain ---
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default="draft")

    prepared_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="prf_prepared",
    )
    prepared_at = models.DateTimeField(null=True, blank=True)

    reviewed_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="prf_reviewed",
    )
    reviewed_at = models.DateTimeField(null=True, blank=True)
    review_comment = models.CharField(max_length=500, blank=True)

    budget_verified_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="prf_budget_verified",
    )
    budget_verified_at = models.DateTimeField(null=True, blank=True)
    budget_verify_comment = models.CharField(max_length=500, blank=True)

    finance_approved_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="prf_finance_approved",
    )
    finance_approved_at = models.DateTimeField(null=True, blank=True)
    approve_comment = models.CharField(max_length=500, blank=True)

    # Signed PDF, attached once the chain completes (approved or rejected).
    document = models.ForeignKey(
        ProjectDocument, on_delete=models.SET_NULL, null=True, blank=True,
        related_name="+",
    )

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

    class Meta:
        db_table = "project_payment_request_forms"
        ordering = ["-created_at"]
        indexes = [
            models.Index(fields=["project", "-created_at"]),
            models.Index(fields=["status"]),
        ]

    def __str__(self):
        return f"PRF {self.number or self.id} — {self.recipient_name} ({self.status})"


def validate_prf_attachment(uploaded_file):
    """PRF Lampiran uploads are restricted to zip/pdf/image — stricter than
    the general ATTACHMENT_ALLOWED_MIME_PREFIXES allowlist."""
    from django.core.exceptions import ValidationError
    mime = (getattr(uploaded_file, "content_type", "") or "").lower()
    allowed_prefixes = ("image/", "application/pdf", "application/zip", "application/x-zip-compressed")
    if not any(mime.startswith(p) for p in allowed_prefixes):
        raise ValidationError(f"'{mime}' not allowed — only zip, pdf, or image files.")


class PRFAttachment(models.Model):
    """One uploaded file for a PRF's Lampiran checklist. A slug may have at
    most one attachment; re-uploading to the same slug replaces the file."""
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    prf = models.ForeignKey(PaymentRequestForm, on_delete=models.CASCADE, related_name="attachments")
    slug = models.CharField(max_length=30, choices=[(s, s) for s in PaymentRequestForm.ATTACHMENT_SLUGS])
    file = models.FileField(upload_to=tenant_upload_path("prf-attachments"), validators=[validate_prf_attachment])
    uploaded_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "project_prf_attachments"
        ordering = ["slug"]
        constraints = [
            models.UniqueConstraint(fields=["prf", "slug"], name="unique_prf_attachment_slug"),
        ]

    def __str__(self):
        return f"{self.prf_id}: {self.slug}"


class ExchangeRate(models.Model):
    """Cached IDR-per-unit rate for a currency on a date.

    Historical ECB rates are immutable, so a stored row never needs refreshing —
    this makes the FX cache survive restarts and stay shared across worker
    processes, instead of each one re-fetching from the upstream API.

    A row with rate=None records a lookup that failed; `checked_at` lets the
    caller retry those later without hammering the API on every request.
    """
    id = models.UUIDField(primary_key=True, default=uuid7, editable=False)
    currency = models.CharField(max_length=10)
    date = models.DateField()
    # IDR per 1 unit of `currency`. Null means the upstream lookup failed.
    rate = models.DecimalField(max_digits=20, decimal_places=6, null=True, blank=True)
    checked_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "project_exchange_rates"
        ordering = ["currency", "-date"]
        constraints = [
            models.UniqueConstraint(fields=["currency", "date"], name="unique_fx_rate_per_currency_date"),
        ]
        indexes = [models.Index(fields=["currency", "date"])]

    def __str__(self):
        return f"{self.currency} {self.date}: {self.rate if self.rate is not None else 'unresolved'}"