"""Shared filter parsing + filtered querysets for every report endpoint.

All endpoints accept the same optional query params:
    date_from / date_to  — ISO dates (YYYY-MM-DD), applied to created_at
    client               — client id (via the application's JD)
    job                  — job description id
    recruiter            — user id (who added the candidate / application)
    status               — candidate status (Draft / Profile Completed / ...)
"""

from django.db.models import Count
from django.utils.dateparse import parse_date

from apps.candidates.models import Candidate
from apps.jobs.models import JobDescription
from apps.pipeline.models import JobApplication, PipelineStage

# JD statuses considered "active" for the overall dashboard
ACTIVE_JOB_STATUSES = ["Published", "Active"]


def parse_filters(request):
    p = request.query_params
    return {
        "date_from": parse_date(p.get("date_from") or ""),
        "date_to": parse_date(p.get("date_to") or ""),
        "client": p.get("client") or None,
        "job": p.get("job") or None,
        "recruiter": p.get("recruiter") or None,
        "status": p.get("status") or None,
    }


def filtered_applications(filters):
    """Pipeline entries (candidate ↔ JD) matching the filters."""
    qs = JobApplication.objects.filter(candidate__is_deleted=False).select_related(
        "candidate", "job", "job__client", "stage", "created_by"
    )
    if filters["date_from"]:
        qs = qs.filter(created_at__date__gte=filters["date_from"])
    if filters["date_to"]:
        qs = qs.filter(created_at__date__lte=filters["date_to"])
    if filters["client"]:
        qs = qs.filter(job__client_id=filters["client"])
    if filters["job"]:
        qs = qs.filter(job_id=filters["job"])
    if filters["recruiter"]:
        qs = qs.filter(created_by_id=filters["recruiter"])
    if filters["status"]:
        qs = qs.filter(candidate__status=filters["status"])
    return qs


def filtered_candidates(filters):
    """Candidates matching the filters (client/job filters go via applications)."""
    qs = Candidate.objects.all()  # default manager excludes soft-deleted rows
    if filters["date_from"]:
        qs = qs.filter(created_at__date__gte=filters["date_from"])
    if filters["date_to"]:
        qs = qs.filter(created_at__date__lte=filters["date_to"])
    if filters["recruiter"]:
        qs = qs.filter(created_by_id=filters["recruiter"])
    if filters["status"]:
        qs = qs.filter(status=filters["status"])
    if filters["client"]:
        qs = qs.filter(applications__job__client_id=filters["client"]).distinct()
    if filters["job"]:
        qs = qs.filter(applications__job_id=filters["job"]).distinct()
    return qs


def filtered_jobs(filters):
    """Active (published) JDs matching the filters."""
    qs = JobDescription.objects.filter(status="Published")
    if filters["client"]:
        qs = qs.filter(client_id=filters["client"])
    if filters["job"]:
        qs = qs.filter(id=filters["job"])
    if filters["recruiter"]:
        qs = qs.filter(assigned_recruiters__id=filters["recruiter"]).distinct()
    return qs


# ─── Shared aggregation builders (used by /reports/ and /reports/overall-dashboard/) ───


def pct(part, whole, digits=1):
    return round(part / whole * 100, digits) if whole else 0


def stage_counts(applications):
    """{stage_id: application count} for the given pipeline entries."""
    return {
        row["stage_id"]: row["c"]
        for row in applications.values("stage_id").annotate(c=Count("id"))
    }


def stage_breakdown(applications):
    """Per-stage counts + percentages, ordered by the pipeline stage master."""
    counts = stage_counts(applications)
    total = applications.count()
    stages = []
    for stage in PipelineStage.objects.filter(is_active=True).order_by("sort_order", "id"):
        count = counts.get(stage.id, 0)
        stages.append({
            "stage_id": stage.id,
            "stage": stage.name,
            "code": stage.code,
            "outcome": stage.outcome,
            "count": count,
            "percentage": pct(count, total),
        })
    return {"total": total, "stages": stages}


def build_funnel(applications):
    """Conversion funnel over the non-LOST stages.

    Only the current stage of each application is stored (no stage history),
    so a candidate sitting at stage N counts as having reached every earlier
    stage too. LOST/unstaged candidates count toward the top of the funnel."""
    counts = stage_counts(applications)
    all_stages = list(PipelineStage.objects.filter(is_active=True).order_by("sort_order", "id"))
    progress = [s for s in all_stages if s.outcome != PipelineStage.Outcome.LOST]
    lost_or_unstaged = applications.count() - sum(counts.get(s.id, 0) for s in progress)

    reached = [0] * len(progress)
    running = 0
    for i in range(len(progress) - 1, -1, -1):
        running += counts.get(progress[i].id, 0)
        reached[i] = running
    if reached:
        reached[0] += lost_or_unstaged

    funnel = []
    for i, stage in enumerate(progress):
        conversion = 100.0 if i == 0 else pct(reached[i], reached[i - 1])
        funnel.append({
            "stage_id": stage.id,
            "stage": stage.name,
            "code": stage.code,
            "outcome": stage.outcome,
            "count": reached[i],
            "conversion_rate": conversion,
            "drop_off_rate": 0 if i == 0 else round(100 - conversion, 1),
        })
    return funnel


# ─── Overall ATS dashboard (active JDs only) ─────────────────────────────────


def parse_overall_filters(request):
    p = request.query_params
    return {
        "date_from": parse_date(p.get("date_from") or ""),
        "date_to": parse_date(p.get("date_to") or ""),
        "client": p.get("client") or None,
        "job": p.get("job") or None,
        "recruiter": p.get("recruiter") or None,
        "department": p.get("department") or None,
    }


def active_jobs(filters):
    """Active JDs (Published/Active) matching the overall-dashboard filters."""
    qs = JobDescription.objects.filter(status__in=ACTIVE_JOB_STATUSES).select_related("client")
    if filters["client"]:
        qs = qs.filter(client_id=filters["client"])
    if filters["job"]:
        qs = qs.filter(id=filters["job"])
    if filters["department"]:
        qs = qs.filter(department__iexact=filters["department"])
    if filters["recruiter"]:
        qs = qs.filter(assigned_recruiters__id=filters["recruiter"]).distinct()
    return qs


def applications_on(jobs_qs, filters):
    """Pipeline entries belonging to the given JDs, narrowed by date/recruiter."""
    qs = JobApplication.objects.filter(
        job__in=jobs_qs, candidate__is_deleted=False
    ).select_related("candidate", "job", "stage", "created_by")
    if filters["date_from"]:
        qs = qs.filter(created_at__date__gte=filters["date_from"])
    if filters["date_to"]:
        qs = qs.filter(created_at__date__lte=filters["date_to"])
    if filters["recruiter"]:
        qs = qs.filter(created_by_id=filters["recruiter"])
    return qs