# ============================================================
# ADDITIVE: SuperAdmin platform panel
#   - public company registration + approval workflow
#   - company credential reset
#   - platform billing (invoices / payments)
#   - platform overview dashboard
# Every view is server-side permission checked.
# ============================================================

import csv
import mimetypes
import os
import re
from datetime import timedelta
from decimal import Decimal, InvalidOperation

from django.core.cache import cache
from django.core.exceptions import ValidationError
from django.core.mail import send_mail
from django.core.validators import validate_email
from django.conf import settings
from django.contrib import messages
from django.db import IntegrityError, transaction
from django.db.models import Count, DecimalField, Q, Sum, Value
from django.db.models.functions import Coalesce
from django.http import FileResponse, Http404, HttpResponse
from django.shortcuts import get_object_or_404, redirect, render
from django.utils import timezone
from django.views.decorators.http import require_POST

from Company.models import AuthLog, company_details
from Employee.models import EmployeeDT

from .company_mail import generate_temp_password, send_company_welcome_email
from .models import (
    PlatformInvoice,
    PlatformPayment,
    RegistrationEvent,
    RegistrationRequest,
    SubscriptionPackage,
    SuperAdminAuditLog,
    SuperAdminSettings,
)
from .platform_helpers import (
    actor_label,
    client_ip,
    paginate,
    platform_permission_required,
    write_audit,
)

PHONE_RE = re.compile(r"^\+?\d{10,15}$")
MAX_DOC_BYTES = 5 * 1024 * 1024
ALLOWED_DOC_EXT = {".pdf", ".png", ".jpg", ".jpeg"}
REG_LIMIT_PER_HOUR = 5


def _platform_settings():
    return SuperAdminSettings.objects.order_by("id").first()


# ============================================================
# PUBLIC REGISTRATION (no login)
# ============================================================

def company_registration_public(request):
    cfg = _platform_settings()
    if cfg is not None and not cfg.allow_new_company_registration:
        return render(request, "superadmin/register_public.html",
                      {"closed": True}, status=403)

    packages = SubscriptionPackage.objects.filter(
        is_active=True, is_public=True
    ).order_by("sort_order", "name")
    ctx = {"packages": packages, "errors": [], "form": {}}

    if request.method != "POST":
        return render(request, "superadmin/register_public.html", ctx)

    form = request.POST
    ctx["form"] = form
    errors = ctx["errors"]

    # honeypot: real users never fill this hidden field
    if form.get("contact_url"):
        return redirect("company_registration_submitted")

    ip = client_ip(request) or "unknown"
    key = f"platform-reg:{ip}"
    count = cache.get(key, 0)
    if count >= REG_LIMIT_PER_HOUR:
        errors.append("Too many registration attempts. Please try again later.")
        return render(request, "superadmin/register_public.html", ctx, status=429)

    company_name = form.get("company_name", "").strip()
    contact_person = form.get("contact_person", "").strip()
    email = form.get("email", "").strip().lower()
    phone = re.sub(r"[\s\-]", "", form.get("phone", ""))
    website = form.get("website", "").strip()
    industry = form.get("industry_type", "").strip()
    address = form.get("address", "").strip()
    message = form.get("message", "").strip()

    if not company_name or len(company_name) > 100:
        errors.append("Company name is required (max 100 characters).")
    if not contact_person or len(contact_person) > 150:
        errors.append("Contact person is required.")
    try:
        validate_email(email)
    except ValidationError:
        errors.append("Enter a valid email address.")
    if not PHONE_RE.match(phone):
        errors.append("Enter a valid phone number (10-15 digits).")
    if len(website) > 50:
        errors.append("Website must be at most 50 characters.")
    if len(industry) > 100 or len(address) > 500 or len(message) > 1000:
        errors.append("One of the text fields is too long.")
    try:
        employee_count = int(form.get("employee_count") or 0)
        if not 0 <= employee_count <= 1000000:
            raise ValueError
    except ValueError:
        employee_count = 0
        errors.append("Employee count must be a valid number.")

    package = None
    if form.get("package"):
        package = packages.filter(pk=form.get("package")).first()

    upload = request.FILES.get("document")
    if upload:
        ext = os.path.splitext(upload.name)[1].lower()
        if ext not in ALLOWED_DOC_EXT:
            errors.append("Document must be a PDF, PNG or JPG file.")
        elif upload.size > MAX_DOC_BYTES:
            errors.append("Document must be 5 MB or smaller.")

    if not errors:
        if company_details.objects.filter(
            Q(email__iexact=email) | Q(Company_Official_Email__iexact=email)
        ).exists() or company_details.objects.filter(
            Q(contact_no=phone) | Q(Company_Official_Contact=phone)
        ).exists():
            errors.append("A company with this email or phone is already registered.")
        elif RegistrationRequest.objects.filter(
            status=RegistrationRequest.STATUS_PENDING
        ).filter(Q(email__iexact=email) | Q(phone=phone)).exists():
            errors.append("A registration with this email or phone is already pending review.")

    if errors:
        return render(request, "superadmin/register_public.html", ctx, status=400)

    with transaction.atomic():
        reg = RegistrationRequest.objects.create(
            company_name=company_name,
            brand_name=form.get("brand_name", "").strip()[:100],
            contact_person=contact_person,
            email=email,
            phone=phone,
            website=website,
            industry_type=industry,
            address=address,
            employee_count=employee_count,
            requested_package=package,
            message=message,
            document=upload or None,
            ip_address=client_ip(request),
            created_by="public",
        )
        RegistrationEvent.objects.create(
            request=reg, action="SUBMITTED", actor="public",
            note="Registration submitted via public form.",
        )
    cache.set(key, count + 1, 3600)
    return redirect("company_registration_submitted")


def company_registration_submitted(request):
    return render(request, "superadmin/register_public.html", {"submitted": True})


# ============================================================
# REGISTRATION MANAGEMENT (SuperAdmin)
# ============================================================

@platform_permission_required("registrations.manage")
def superadmin_registrations(request):
    qs = RegistrationRequest.objects.select_related("requested_package", "company")
    status = request.GET.get("status", "").strip()
    q = request.GET.get("q", "").strip()
    if status in dict(RegistrationRequest.STATUS_CHOICES):
        qs = qs.filter(status=status)
    if q:
        qs = qs.filter(
            Q(company_name__icontains=q) | Q(email__icontains=q)
            | Q(phone__icontains=q) | Q(reference__icontains=q)
            | Q(contact_person__icontains=q)
        )
    counts = dict(
        RegistrationRequest.objects.values_list("status").annotate(c=Count("id"))
    )
    page, qs_params = paginate(request, qs)
    return render(request, "superadmin/registrations.html", {
        "page": page, "qs_params": qs_params, "status": status, "q": q,
        "counts": counts,
        "status_choices": RegistrationRequest.STATUS_CHOICES,
    })


@platform_permission_required("registrations.manage")
def superadmin_registration_detail(request, pk):
    reg = get_object_or_404(
        RegistrationRequest.objects.select_related("requested_package", "company"),
        pk=pk,
    )
    return render(request, "superadmin/registration_detail.html", {
        "reg": reg,
        "events": reg.events.all(),
        "packages": SubscriptionPackage.objects.filter(is_active=True).order_by("sort_order", "name"),
    })


@platform_permission_required("registrations.manage")
def superadmin_registration_document(request, pk):
    reg = get_object_or_404(RegistrationRequest, pk=pk)
    if not reg.document:
        raise Http404("No document attached.")
    write_audit(request, "OTHER", reg.company,
                f"Viewed registration document of {reg.reference}")
    ctype = mimetypes.guess_type(reg.document.name)[0] or "application/octet-stream"
    resp = FileResponse(
        reg.document.open("rb"),
        as_attachment=True,
        filename=os.path.basename(reg.document.name),
        content_type=ctype,
    )
    resp["X-Content-Type-Options"] = "nosniff"
    return resp


def _send_rejection_mail(reg, reason):
    try:
        send_mail(
            subject="Your company registration",
            message=(
                f"Hello {reg.contact_person},\n\n"
                f"We could not approve the registration of {reg.company_name} "
                f"(ref {reg.reference}).\n\nReason: {reason}\n\n"
                "You are welcome to register again after addressing this."
            ),
            from_email=getattr(settings, "DEFAULT_FROM_EMAIL", None),
            recipient_list=[reg.email],
            fail_silently=False,
        )
        return True
    except Exception:
        return False


@require_POST
@platform_permission_required("registrations.manage")
def superadmin_registration_action(request, pk):
    action = request.POST.get("action")
    back = redirect("superadmin_registration_detail", pk=pk)
    if action not in ("approve", "reject", "note"):
        messages.error(request, "Invalid action.")
        return back

    actor_name = request.superadmin_profile.full_name

    if action == "note":
        note = request.POST.get("note", "").strip()[:1000]
        if note:
            reg = get_object_or_404(RegistrationRequest, pk=pk)
            RegistrationEvent.objects.create(
                request=reg, action="NOTE", note=note, actor=actor_name,
                created_by=actor_label(request),
            )
            messages.success(request, "Note added.")
        return back

    welcome = None
    try:
        with transaction.atomic():
            reg = RegistrationRequest.objects.select_for_update().get(pk=pk)
            if reg.status != RegistrationRequest.STATUS_PENDING:
                messages.error(request, f"Already {reg.get_status_display().lower()}.")
                return back

            if action == "reject":
                reason = request.POST.get("reason", "").strip()
                if len(reason) < 5:
                    messages.error(request, "Enter a rejection reason (min 5 characters).")
                    return back
                reg.status = RegistrationRequest.STATUS_REJECTED
                reg.rejection_reason = reason
                reg.reviewed_by_name = actor_name
                reg.reviewed_at = timezone.now()
                reg.updated_by = actor_label(request)
                reg.save()
                RegistrationEvent.objects.create(
                    request=reg, action="REJECTED", note=reason, actor=actor_name,
                    created_by=actor_label(request),
                )
                write_audit(request, "OTHER", None,
                            f"Rejected registration {reg.reference}: {reason}")
                sent = _send_rejection_mail(reg, reason)
                messages.success(
                    request,
                    "Registration rejected." + ("" if sent else " (Email to applicant could not be sent.)"),
                )
                return back

            # ---- approve ----
            if (
                company_details.objects.filter(email__iexact=reg.email).exists()
                or company_details.objects.filter(contact_no=reg.phone).exists()
            ):
                messages.error(
                    request,
                    "A company with this email or phone already exists. Reject this request or fix the duplicate first.",
                )
                return back

            package = reg.requested_package
            override = request.POST.get("package")
            if override:
                package = SubscriptionPackage.objects.filter(pk=override, is_active=True).first() or package
            cfg = _platform_settings()
            trial_days = (package.trial_days if package else None) or (
                cfg.default_trial_days if cfg else 14
            )
            today = timezone.localdate()

            company = company_details(
                email=reg.email,
                contact_no=reg.phone,
                Registered_Company_Name=reg.company_name,
                Brand_Name=reg.brand_name or None,
                Company_Official_Email=reg.email,
                Company_Official_Contact=reg.phone,
                Website=reg.website or None,
                Industry_Type=reg.industry_type or None,
                Company_address=reg.address or None,
                REGISTERED_OFFICE_add=reg.address or None,
                plan=package.plan if package else "FREE",
                company_status="TRIAL",
                status=True,
                is_verified=True,
                created_by_superadmin=True,
                trial_start_date=today,
                trial_end_date=today + timedelta(days=trial_days),
                employee_limit=(package.employee_limit if package else (cfg.default_employee_limit if cfg else 10)),
                admin_limit=(package.admin_limit if package else (cfg.default_admin_limit if cfg else 1)),
                hr_limit=(package.hr_limit if package else (cfg.default_hr_limit if cfg else 1)),
                device_limit=(package.device_limit if package else (cfg.default_device_limit if cfg else 1)),
                notes=f"Approved from registration {reg.reference}",
                created_by=actor_label(request),
            )
            temp_password = generate_temp_password()
            company.password = temp_password
            company.save()

            reg.status = RegistrationRequest.STATUS_APPROVED
            reg.company = company
            reg.reviewed_by_name = actor_name
            reg.reviewed_at = timezone.now()
            reg.updated_by = actor_label(request)
            reg.save()
            RegistrationEvent.objects.create(
                request=reg, action="APPROVED", actor=actor_name,
                note=f"Company {company.company_id} created; credentials emailed.",
                created_by=actor_label(request),
            )
            write_audit(request, "CREATE_COMPANY", company,
                        f"Approved registration {reg.reference} -> company {company.company_id}")
            welcome = (company, temp_password)
    except IntegrityError:
        messages.error(request, "Could not create the company (duplicate email or phone).")
        return back

    if welcome:
        company, pw = welcome
        try:
            sent_to = send_company_welcome_email(company, pw)
            messages.success(request, f"Approved. Credentials emailed to {sent_to}.")
        except Exception as exc:
            messages.warning(request, f"Approved, but the credentials email failed: {exc}")
    return back


@require_POST
@platform_permission_required("companies.manage")
def superadmin_company_credentials(request, company_id):
    """Assign / reset Company Admin login credentials and email them."""
    company = get_object_or_404(company_details, pk=company_id)
    pw = generate_temp_password()
    company.password = pw
    company.updated_by = actor_label(request)
    company.save(update_fields=["password", "updated_by"])
    write_audit(request, "UPDATE_ADMIN", company, "Company admin credentials reset")
    try:
        sent_to = send_company_welcome_email(company, pw)
        messages.success(request, f"New credentials emailed to {sent_to}.")
    except Exception as exc:
        messages.warning(request, f"Password reset, but the email failed: {exc}")
    return redirect("superadmin_company_detail", company_id=company.pk)


# ============================================================
# PLATFORM BILLING
# ============================================================

def _dec(value, default=None):
    try:
        return Decimal(str(value).strip())
    except (InvalidOperation, ValueError):
        return default


def _billing_summary():
    live = PlatformInvoice.objects.exclude(
        status__in=[PlatformInvoice.STATUS_DRAFT, PlatformInvoice.STATUS_CANCELLED]
    )
    billed = live.aggregate(s=Sum("total_amount"))["s"] or Decimal("0")
    collected = PlatformPayment.objects.filter(
        is_active=True, invoice__in=live
    ).aggregate(s=Sum("amount"))["s"] or Decimal("0")
    overdue_qs = live.filter(
        status__in=[PlatformInvoice.STATUS_ISSUED, PlatformInvoice.STATUS_PARTIAL],
        due_date__lt=timezone.localdate(),
    ).annotate(
        paid=Coalesce(
            Sum("payments__amount", filter=Q(payments__is_active=True)),
            Value(Decimal("0")),
            output_field=DecimalField(),
        )
    )
    overdue_amount = sum((i.total_amount - i.paid) for i in overdue_qs)
    return {
        "billed": billed,
        "collected": collected,
        "outstanding": billed - collected,
        "overdue_count": overdue_qs.count(),
        "overdue_amount": overdue_amount,
    }


@platform_permission_required("billing.view")
def superadmin_billing(request):
    qs = PlatformInvoice.objects.select_related("company", "package")
    status = request.GET.get("status", "").strip()
    q = request.GET.get("q", "").strip()
    company = request.GET.get("company", "").strip()
    if status == "OVERDUE":
        qs = qs.filter(
            status__in=[PlatformInvoice.STATUS_ISSUED, PlatformInvoice.STATUS_PARTIAL],
            due_date__lt=timezone.localdate(),
        )
    elif status in dict(PlatformInvoice.STATUS_CHOICES):
        qs = qs.filter(status=status)
    if company.isdigit():
        qs = qs.filter(company_id=company)
    if q:
        qs = qs.filter(
            Q(invoice_number__icontains=q)
            | Q(company__Registered_Company_Name__icontains=q)
            | Q(company__Brand_Name__icontains=q)
        )

    if request.GET.get("export") == "csv":
        write_audit(request, "OTHER", None, "Exported platform invoices CSV")
        resp = HttpResponse(content_type="text/csv")
        resp["Content-Disposition"] = 'attachment; filename="platform_invoices.csv"'
        w = csv.writer(resp)
        w.writerow(["Invoice", "Company", "Status", "Issue date", "Due date",
                    "Amount", "Tax", "Total", "Paid", "Balance"])
        for i in qs:
            w.writerow([i.invoice_number, i.company.Registered_Company_Name,
                        i.status, i.issue_date, i.due_date, i.amount,
                        i.tax_amount, i.total_amount, i.amount_paid(), i.balance()])
        return resp

    page, qs_params = paginate(request, qs, 25)
    return render(request, "superadmin/billing.html", {
        "page": page, "qs_params": qs_params, "status": status, "q": q,
        "company": company,
        "summary": _billing_summary(),
        "status_choices": PlatformInvoice.STATUS_CHOICES + [("OVERDUE", "Overdue")],
        "companies": company_details.objects.order_by("Registered_Company_Name"),
    })


@platform_permission_required("billing.manage")
def superadmin_invoice_create(request):
    companies = company_details.objects.order_by("Registered_Company_Name")
    packages = SubscriptionPackage.objects.filter(is_active=True).order_by("sort_order", "name")
    ctx = {"companies": companies, "packages": packages, "errors": [],
           "form": request.GET if request.method != "POST" else request.POST,
           "cycles": PlatformInvoice.CYCLE_CHOICES}
    if request.method != "POST":
        return render(request, "superadmin/invoice_form.html", ctx)

    f = request.POST
    errors = ctx["errors"]
    company = companies.filter(pk=f.get("company") or 0).first()
    package = packages.filter(pk=f.get("package") or 0).first()
    cycle = f.get("billing_cycle", "MONTHLY")
    if cycle not in dict(PlatformInvoice.CYCLE_CHOICES):
        cycle = "MONTHLY"
    if not company:
        errors.append("Select a company.")

    amount = _dec(f.get("amount"), None) if f.get("amount", "").strip() else None
    if amount is None and package:
        amount = package.yearly_price if cycle == "YEARLY" else package.monthly_price
    if amount is None or amount <= 0:
        errors.append("Enter an amount greater than zero (or pick a package with a price).")
    tax = _dec(f.get("tax_percent") or "18", None)
    if tax is None or not (0 <= tax <= 100):
        errors.append("Tax % must be between 0 and 100.")

    def _date(name, required=False):
        raw = f.get(name, "").strip()
        if not raw:
            if required:
                errors.append(f"{name.replace('_', ' ').title()} is required.")
            return None
        try:
            return timezone.datetime.strptime(raw, "%Y-%m-%d").date()
        except ValueError:
            errors.append(f"{name.replace('_', ' ').title()} is not a valid date.")
            return None

    issue = _date("issue_date") or timezone.localdate()
    due = _date("due_date", required=True)
    pstart, pend = _date("period_start"), _date("period_end")
    if due and due < issue:
        errors.append("Due date cannot be before the issue date.")
    if pstart and pend and pend < pstart:
        errors.append("Period end cannot be before period start.")
    if errors:
        return render(request, "superadmin/invoice_form.html", ctx, status=400)

    as_draft = f.get("save_as") == "draft"
    inv = PlatformInvoice.objects.create(
        company=company, package=package, billing_cycle=cycle,
        period_start=pstart, period_end=pend, amount=amount, tax_percent=tax,
        issue_date=issue, due_date=due, notes=f.get("notes", "").strip()[:500],
        status=PlatformInvoice.STATUS_DRAFT if as_draft else PlatformInvoice.STATUS_ISSUED,
        created_by=actor_label(request),
    )
    write_audit(request, "OTHER", company, f"Created invoice {inv.invoice_number} ({inv.total_amount})")
    messages.success(request, f"Invoice {inv.invoice_number} created.")
    return redirect("superadmin_invoice_detail", pk=inv.pk)


@platform_permission_required("billing.view")
def superadmin_invoice_detail(request, pk):
    inv = get_object_or_404(
        PlatformInvoice.objects.select_related("company", "package"), pk=pk
    )
    return render(request, "superadmin/invoice_detail.html", {
        "inv": inv,
        "payments": inv.payments.filter(is_active=True),
        "paid": inv.amount_paid(),
        "balance": inv.balance(),
        "methods": PlatformPayment.METHOD_CHOICES,
        "today": timezone.localdate(),
    })


@require_POST
@platform_permission_required("billing.manage")
def superadmin_invoice_payment(request, pk):
    back = redirect("superadmin_invoice_detail", pk=pk)
    with transaction.atomic():
        inv = get_object_or_404(PlatformInvoice.objects.select_for_update(), pk=pk)
        if inv.status in (PlatformInvoice.STATUS_DRAFT, PlatformInvoice.STATUS_CANCELLED):
            messages.error(request, "Payments can only be recorded on issued invoices.")
            return back
        amount = _dec(request.POST.get("amount"))
        if amount is None or amount <= 0:
            messages.error(request, "Enter a valid payment amount.")
            return back
        if amount > inv.balance():
            messages.error(request, f"Amount exceeds the outstanding balance ({inv.balance()}).")
            return back
        method = request.POST.get("method", "OTHER")
        if method not in dict(PlatformPayment.METHOD_CHOICES):
            method = "OTHER"
        try:
            paid_on = timezone.datetime.strptime(
                request.POST.get("paid_on") or str(timezone.localdate()), "%Y-%m-%d"
            ).date()
        except ValueError:
            messages.error(request, "Invalid payment date.")
            return back
        PlatformPayment.objects.create(
            invoice=inv, amount=amount, method=method, paid_on=paid_on,
            reference=request.POST.get("reference", "").strip()[:100],
            notes=request.POST.get("notes", "").strip()[:300],
            recorded_by=request.superadmin_profile.full_name,
            created_by=actor_label(request),
        )
        write_audit(request, "OTHER", inv.company,
                    f"Recorded payment {amount} on {inv.invoice_number}")
    messages.success(request, "Payment recorded.")
    return back


@require_POST
@platform_permission_required("billing.manage")
def superadmin_invoice_status(request, pk):
    inv = get_object_or_404(PlatformInvoice, pk=pk)
    action = request.POST.get("action")
    if action == "issue" and inv.status == PlatformInvoice.STATUS_DRAFT:
        inv.status = PlatformInvoice.STATUS_ISSUED
        inv.save(update_fields=["status", "updated_at"])
        write_audit(request, "OTHER", inv.company, f"Issued invoice {inv.invoice_number}")
        messages.success(request, "Invoice issued.")
    elif action == "cancel" and inv.status in (
        PlatformInvoice.STATUS_DRAFT, PlatformInvoice.STATUS_ISSUED
    ):
        if inv.payments.filter(is_active=True).exists():
            messages.error(request, "Invoices with payments cannot be cancelled.")
        else:
            inv.status = PlatformInvoice.STATUS_CANCELLED
            inv.save(update_fields=["status", "updated_at"])
            write_audit(request, "OTHER", inv.company, f"Cancelled invoice {inv.invoice_number}")
            messages.success(request, "Invoice cancelled.")
    else:
        messages.error(request, "That action is not allowed for this invoice.")
    return redirect("superadmin_invoice_detail", pk=pk)


@platform_permission_required("billing.view")
def superadmin_payments(request):
    qs = PlatformPayment.objects.filter(is_active=True).select_related(
        "invoice", "invoice__company"
    )
    q = request.GET.get("q", "").strip()
    method = request.GET.get("method", "").strip()
    if q:
        qs = qs.filter(
            Q(invoice__invoice_number__icontains=q) | Q(reference__icontains=q)
            | Q(invoice__company__Registered_Company_Name__icontains=q)
        )
    if method in dict(PlatformPayment.METHOD_CHOICES):
        qs = qs.filter(method=method)
    page, qs_params = paginate(request, qs, 25)
    return render(request, "superadmin/payments.html", {
        "page": page, "qs_params": qs_params, "q": q, "method": method,
        "methods": PlatformPayment.METHOD_CHOICES,
        "total": qs.aggregate(s=Sum("amount"))["s"] or Decimal("0"),
    })


# ============================================================
# PLATFORM OVERVIEW DASHBOARD (real DB counts)
# ============================================================

@platform_permission_required("dashboard.view")
def superadmin_platform_overview(request):
    today = timezone.localdate()
    since = timezone.now() - timedelta(days=30)
    status_counts = dict(
        company_details.objects.values_list("company_status").annotate(c=Count("id"))
    )
    reg_counts = dict(
        RegistrationRequest.objects.values_list("status").annotate(c=Count("id"))
    )
    emp_counts = list(
        EmployeeDT.objects.values("company_id")
        .annotate(c=Count("id")).order_by("-c")[:10]
    )
    names = {
        c.pk: c for c in company_details.objects.filter(
            pk__in=[r["company_id"] for r in emp_counts if r["company_id"]]
        )
    }
    top_companies = [
        {"company": names.get(r["company_id"]), "count": r["c"]}
        for r in emp_counts if names.get(r["company_id"])
    ]
    ctx = {
        "total_companies": company_details.objects.count(),
        "status_counts": status_counts,
        "pending_registrations": reg_counts.get("PENDING", 0),
        "new_registrations_30d": RegistrationRequest.objects.filter(created_at__gte=since).count(),
        "new_companies_30d": company_details.objects.filter(created_at__gte=since).count(),
        "total_employees": EmployeeDT.objects.count(),
        "billing": _billing_summary(),
        "expiring": company_details.objects.filter(
            company_status__in=["ACTIVE", "TRIAL"],
        ).filter(
            Q(subscription_end_date__range=(today, today + timedelta(days=30)))
            | Q(trial_end_date__range=(today, today + timedelta(days=30)))
        ).order_by("subscription_end_date", "trial_end_date")[:10],
        "recent_activity": SuperAdminAuditLog.objects.select_related(
            "company", "superadmin"
        ).order_by("-created_at")[:10],
        "failed_logins_24h": AuthLog.objects.filter(
            status=AuthLog.STATUS_FAILED,
            created_at__gte=timezone.now() - timedelta(hours=24),
        ).count(),
        "top_companies": top_companies,
    }
    return render(request, "superadmin/platform_overview.html", ctx)
