from django.db.models import Avg, Case, Count, F, Q, Sum, Value, When, CharField
from django.db.models.functions import TruncDate

from apps.commercial.models import ServiceQuotation, QuotationLine, ServiceInvoice, ServicePayment, PaymentReversal
from apps.commercial.payment_queries import _settlements
from .permissions import COMMERCIAL
from .scope import cases
from .filters import period
from .query import grouped, daily, durations, table
from .service_dashboard import aging


def invoices(user, filters, *, permission=COMMERCIAL, export=False):
    return ServiceInvoice.objects.filter(service_case_id__in=cases(user, permission, filters, export=export).values("pk"), status="FINALIZED")


def settlements(user, filters, *, permission=COMMERCIAL, export=False):
    return _settlements(invoices(user, filters, permission=permission, export=export)).annotate(clearance=Case(
        When(customer_pay_total=0, then=Value("NO_CUSTOMER_DUE")),
        When(balance_due=0, then=Value("PAID")),
        When(release_limit__gte=F("balance_due"), then=Value("DUE_RELEASE")),
        default=Value("UNCLEARED"), output_field=CharField()))


def payments(user, filters, *, permission=COMMERCIAL, export=False):
    rows = ServicePayment.objects.filter(invoice_id__in=invoices(user, filters, permission=permission, export=export).values("pk"))
    return period(rows, "received_at", filters)


def collection_totals(user, filters, *, permission=COMMERCIAL, export=False):
    rows = payments(user, filters, permission=permission, export=export)
    return grouped(rows, ["currency"], received=Sum("amount"), reversed=Sum("reversal__amount", default=0),
        net_collected=Sum("amount", filter=Q(status="POSTED"), default=0), valid_allocations=Sum("allocation__amount", filter=Q(status="POSTED"), default=0), receipts=Count("receipt"))


def commercial_tables(user, filters, *, permission=COMMERCIAL, export=False):
    scoped = cases(user, permission, filters, export=export)
    quotes = ServiceQuotation.objects.filter(family__service_case_id__in=scoped.values("pk"))
    current_approved = period(quotes.filter(is_current=True, status="APPROVED"), "decision__created_at", filters)
    quote_events = (
        ("created", period(quotes, "created_at", filters)),
        ("submitted", period(quotes.filter(submitted_at__isnull=False), "submitted_at", filters)),
        ("approved", period(quotes.filter(decision__outcome="APPROVED"), "decision__created_at", filters)),
        ("rejected", period(quotes.filter(decision__outcome="REJECTED"), "decision__created_at", filters)),
        ("replacements", period(quotes.filter(revision__gt=1), "created_at", filters)),
    )
    result = []
    for key, rows in quote_events:
        result.append(table("quotations_" + key, "Quotation revisions " + key, grouped(rows, ["currency"], count=Count("pk")), [("currency", "Currency"), ("count", "Revisions")], "Event counts include retained superseded history; these counts are not business-value totals."))
    result.append(table("quotation_values", "Current approved quotation value", grouped(current_approved, ["currency"], value=Sum("grand_total"), customer=Sum("customer_pay_total"), covered=Sum("covered_total")), [("currency", "Currency"), ("value", "Approved quotation value"), ("customer", "Customer liability"), ("covered", "Covered value")], "Current APPROVED revisions only, selected by approval date; superseded revisions excluded from value."))
    lines = QuotationLine.objects.filter(quotation_id__in=current_approved.values("pk"), is_active=True)
    result.append(table("quotation_responsibility", "Current approved quotation responsibility", grouped(lines, ["quotation__currency", "responsibility"], value=Sum("total")), [("quotation__currency", "Currency"), ("responsibility", "Explicit responsibility"), ("value", "Approved line value")]))
    result.append(table("quotations", "Quotation revision drill-down", period(quotes, "created_at", filters).order_by("created_at", "pk").values("pk", "family__number", "revision", "status", "is_current", "currency", "grand_total", "created_at"), [("pk", "Revision ID"), ("family__number", "Quotation"), ("revision", "Revision"), ("status", "Status"), ("is_current", "Current"), ("currency", "Currency"), ("grand_total", "Revision value"), ("created_at", "Created")], "Creation date. Revision values must not be added across superseded versions."))
    invoice_rows = period(invoices(user, filters, permission=permission, export=export), "finalized_at", filters)
    metric = dict(count=Count("pk"), value=Sum("grand_total"), customer=Sum("customer_pay_total"), warranty=Sum("warranty_covered_total"), company=Sum("company_covered_total"), average=Avg("grand_total"))
    money_cols = [("count", "Finalized invoices"), ("value", "Invoiced Service Value"), ("customer", "Customer liability"), ("warranty", "Warranty value"), ("company", "Company value"), ("average", "Average invoice value")]
    result.append(table("invoice_values", "Invoiced Service Value", grouped(invoice_rows, ["currency"], **metric), [("currency", "Currency"), *money_cols], "Finalized timestamp; frozen invoice totals. No revenue recognition or currency conversion."))
    for key, field, label in (("center", "context_snapshot__service_center", "ServiceCenter snapshot"), ("model", "context_snapshot__quotation__model", "Model snapshot")):
        fields = ["currency", field] + (["service_center_id"] if key == "center" else [])
        labels = [("currency", "Currency"), (field, label)] + ([("service_center_id", "ServiceCenter ID")] if key == "center" else [])
        result.append(table("invoice_" + key, "Invoiced Service Value by " + label, grouped(invoice_rows, fields, **metric), labels + money_cols, "Historical display snapshot; centers retain their authoritative IDs. Identical recorded model labels share a snapshot-label group. Product/organization UUID filters use current protected relationships."))
    result.append(table("invoice_trend", "Invoiced Service Value trend", grouped(invoice_rows.annotate(day=TruncDate("finalized_at")), ["currency", "day"], **metric), [("currency", "Currency"), ("day", "Finalization local date"), *money_cols]))
    invoice_cols = [("pk", "Invoice ID"), ("number", "Invoice"), ("context_snapshot__job_number", "Job snapshot"), ("currency", "Currency"), ("grand_total", "Invoiced Service Value"), ("customer_pay_total", "Customer liability"), ("finalized_at", "Finalized")]
    result.append(table("invoices", "Finalized invoice drill-down", invoice_rows.order_by("finalized_at", "pk").values(*(f for f, _ in invoice_cols)), invoice_cols))
    pay = payments(user, filters, permission=permission, export=export)
    payment_metrics = dict(received=Sum("amount"), reversed=Sum("reversal__amount", default=0), net_collected=Sum("amount", filter=Q(status="POSTED"), default=0), valid_allocations=Sum("allocation__amount", filter=Q(status="POSTED"), default=0), receipts=Count("receipt"))
    payment_cols = [("received", "Gross received"), ("reversed", "Reversed from this cohort"), ("net_collected", "Net valid collections"), ("valid_allocations", "Valid allocations"), ("receipts", "Receipts issued")]
    payment_note = "Payment-received-date cohort, net of all recorded full reversals of those payments, even later reversals. VOIDED allocations do not settle debt; retained receipts still count. Reversal-date activity is shown separately."
    result.append(table("collections", "Payment collections", collection_totals(user, filters, permission=permission, export=export), [("currency", "Currency"), *payment_cols], payment_note))
    for key, field, label in (("center", "receipt__center_snapshot__code", "Receipt center code snapshot"), ("method", "method", "Payment method")):
        fields = ["currency", field] + (["service_center_id"] if key == "center" else [])
        labels = [("currency", "Currency"), (field, label)] + ([("service_center_id", "ServiceCenter ID")] if key == "center" else [])
        result.append(table("collections_" + key, "Collections by " + label, grouped(pay, fields, **payment_metrics), labels + payment_cols, payment_note))
    result.append(table("collection_trend", "Collection trend", grouped(pay.annotate(day=TruncDate("received_at")), ["currency", "day"], **payment_metrics), [("currency", "Currency"), ("day", "Received local date"), *payment_cols], payment_note))
    payment_fields = [("pk", "Payment ID"), ("receipt__invoice_number", "Invoice snapshot"), ("receipt__number", "Receipt"), ("currency", "Currency"), ("amount", "Amount"), ("method", "Method"), ("status", "Status"), ("received_at", "Received")]
    result.append(table("payments", "Payment drill-down", pay.order_by("received_at", "pk").values(*(f for f, _ in payment_fields)), payment_fields, payment_note))
    reversals = PaymentReversal.objects.filter(payment__invoice_id__in=invoices(user, filters, permission=permission, export=export).values("pk"))
    result.append(table("reversal_events", "Reversal-date activity", grouped(period(reversals, "reversed_at", filters), ["payment__currency"], amount=Sum("amount"), count=Count("pk")), [("payment__currency", "Currency"), ("amount", "Reversed amount"), ("count", "Reversals")], "Reversed timestamp; may relate to payments received before this period. Not subtracted again from the received-cohort net."))
    settled = settlements(user, filters, permission=permission, export=export)
    outstanding = settled.filter(balance_due__gt=0)
    result.append(table("outstanding_summary", "Current outstanding customer liability", grouped(outstanding, ["currency", "settlement_state"], balance=Sum("balance_due"), count=Count("pk"), delivered_due=Count("pk", filter=Q(service_case__handover__isnull=False, clearance="DUE_RELEASE"))), [("currency", "Currency"), ("settlement_state", "Settlement"), ("balance", "Outstanding"), ("count", "Invoices"), ("delivered_due", "Delivered with outstanding due-release")], "Current balance, independent of date range. Due-release does not reduce debt."))
    result.append(table("outstanding_aging", "Current outstanding aging", grouped(aging(outstanding, "finalized_at"), ["currency", "age_bucket"], balance=Sum("balance_due"), count=Count("pk")), [("currency", "Currency"), ("age_bucket", "Elapsed since finalization"), ("balance", "Outstanding"), ("count", "Invoices")]))
    result.append(table("clearance", "Current financial clearance", grouped(settled, ["currency", "clearance"], count=Count("pk"), balance=Sum("balance_due")), [("currency", "Currency"), ("clearance", "Financial clearance"), ("count", "Invoices"), ("balance", "Outstanding")], "Clearance and settlement differ: due-release can permit delivery with unpaid debt. Invoice-free cases have no inferred commercial classification."))
    cols = [("pk", "Invoice ID"), ("number", "Invoice"), ("currency", "Currency"), ("customer_pay_total", "Customer liability"), ("paid_amount", "Valid settlement"), ("balance_due", "Outstanding"), ("settlement_state", "Settlement"), ("clearance", "Clearance"), ("finalized_at", "Finalized")]
    result.append(table("outstanding", "Current outstanding invoice drill-down", outstanding.order_by("finalized_at", "pk").values(*(f for f, _ in cols)), cols, "Current balances; date filters do not exclude old debt."))
    return result


def quotation_approval_turnaround(user, filters, *, permission=COMMERCIAL, export=False):
    rows = ServiceQuotation.objects.filter(family__service_case_id__in=cases(user, permission, filters, export=export).values("pk"), decision__outcome="APPROVED")
    return durations(period(rows, "decision__created_at", filters), "submitted_at", "decision__created_at")