"""SQL-derived settlement; event counts form a monotonic stale-write revision."""
from decimal import Decimal
from django.db.models import Count, Sum, Max, OuterRef, Subquery, Value, DecimalField, IntegerField, F, Case, When, CharField
from django.db.models.functions import Coalesce
from .models import ServiceInvoice, ServicePayment, PaymentAllocation, ServicePaymentReceipt, PaymentReversal, ServiceFinancialRelease
from .invoice_queries import invoice_cases

VIEW = "commercial.view_servicepayment"
MONEY = DecimalField(max_digits=14, decimal_places=2)


def _aggregate(rows, group, expression, *, money=False):
    field = MONEY if money else IntegerField()
    return Coalesce(Subquery(rows.order_by().values(group).annotate(value=expression).values("value"), output_field=field), Value(Decimal(0) if money else 0, output_field=field))


def _settlements(rows):
    return rows.filter(status="FINALIZED").annotate(
        paid_amount=_aggregate(PaymentAllocation.objects.filter(invoice_id=OuterRef("pk"), payment__status="POSTED"), "invoice_id", Sum("amount"), money=True),
        payment_count=_aggregate(ServicePayment.objects.filter(invoice_id=OuterRef("pk")), "invoice_id", Count("pk")),
        reversal_count=_aggregate(PaymentReversal.objects.filter(payment__invoice_id=OuterRef("pk")), "payment__invoice_id", Count("pk")),
        release_count=_aggregate(ServiceFinancialRelease.objects.filter(invoice_id=OuterRef("pk")), "invoice_id", Count("pk")),
        release_limit=_aggregate(ServiceFinancialRelease.objects.filter(invoice_id=OuterRef("pk")), "invoice_id", Max("outstanding_amount"), money=True),
    ).annotate(balance_due=F("customer_pay_total")-F("paid_amount"), settlement_state=Case(
        When(customer_pay_total=0, then=Value("NO_CUSTOMER_DUE")), When(paid_amount=0, then=Value("UNPAID")),
        When(paid_amount=F("customer_pay_total"), then=Value("PAID")), default=Value("PARTIALLY_PAID"), output_field=CharField()))


def _summary(row):
    return dict(invoice_id=str(row.pk), currency=row.currency, customer_liability=row.customer_pay_total,
        paid=row.paid_amount, balance=row.balance_due, state=row.settlement_state,
        financially_clear=row.balance_due == 0 or row.release_limit >= row.balance_due,
        revision=f"{row.pk}:{row.payment_count + row.reversal_count + row.release_count}")


def _locked_summary(invoice):
    """Internal trusted read: caller holds the case lock for mutation decisions."""
    return _summary(_settlements(ServiceInvoice.objects.filter(pk=invoice.pk)).get())


def settlement_invoices(*, actor, permission=VIEW):
    return _settlements(ServiceInvoice.objects.filter(service_case_id__in=invoice_cases(actor=actor, permission=permission).values("pk"))).select_related(
        "service_case__customer", "service_case__device__product_model__brand", "service_center").order_by("service_center_id", "number", "pk")


def invoice_settlement_summary(*, actor, invoice):
    return _summary(settlement_invoices(actor=actor).get(pk=invoice.pk))


def service_payments(*, actor):
    return ServicePayment.objects.filter(invoice_id__in=settlement_invoices(actor=actor).values("pk")).select_related(
        "invoice__service_case__customer", "service_center", "received_by", "allocation", "receipt", "reversal__reversed_by").order_by("received_at", "pk")


def payments_for_invoice(*, actor, invoice):
    return service_payments(actor=actor).filter(invoice=invoice)


def payment_detail(*, actor, payment):
    return service_payments(actor=actor).get(pk=payment.pk)


def receipt_history(*, actor):
    return ServicePaymentReceipt.objects.filter(payment_id__in=service_payments(actor=actor).values("pk")).select_related(
        "payment__invoice", "service_center", "issued_by").order_by("service_center_id", "number", "pk")


def receipt_lookup(*, actor, service_center, number):
    return receipt_history(actor=actor).get(service_center=service_center, number=number)


def financial_release_history(*, actor, invoice):
    return ServiceFinancialRelease.objects.filter(invoice_id__in=settlement_invoices(actor=actor).filter(pk=invoice.pk).values("pk")).select_related("authorized_by").order_by("authorized_at", "pk")


def unsettled_invoices(*, actor):
    return settlement_invoices(actor=actor).filter(balance_due__gt=0)


def financially_cleared_delivery_queue(*, actor):
    from django.db.models import Q
    return settlement_invoices(actor=actor).filter(service_case__status="READY_FOR_DELIVERY").filter(Q(balance_due=0) | Q(release_limit__gte=F("balance_due")))


def outstanding_customer_balance(*, actor, customer, currency):
    return settlement_invoices(actor=actor).filter(service_case__customer=customer, currency=currency).aggregate(
        total=Coalesce(Sum("balance_due"), Value(Decimal(0)), output_field=MONEY))["total"]