"""Shared helpers for ATS pipeline views."""
from django.db.models import Avg, Count, Max, Q

from jobs.models import Job, JobApplication


PIPELINE_ORDER = ('-match_score', '-created_at')


def pipeline_stats(queryset):
    status_counts = {
        row['status']: row['count']
        for row in queryset.values('status').annotate(count=Count('id'))
    }
    total_count = sum(status_counts.values())
    status_tabs = [
        {
            'value': value,
            'label': label,
            'count': status_counts.get(value, 0),
        }
        for value, label in JobApplication.Status.choices
    ]
    return status_counts, total_count, status_tabs


def build_job_boards(jobs=None):
    """Per-job summary cards (applicants, average/top match score, match bands) for jobs that have applications."""
    jobs = Job.objects.all() if jobs is None else jobs
    boards = list(
        jobs.filter(applications__isnull=False)
        .annotate(
            applicant_count=Count('applications', distinct=True),
            avg_score=Avg('applications__match_score'),
            top_score=Max('applications__match_score'),
            high_count=Count('applications', filter=Q(applications__match_score__gte=70), distinct=True),
            mid_count=Count('applications', filter=Q(applications__match_score__gte=40, applications__match_score__lt=70), distinct=True),
            new_count=Count('applications', filter=Q(applications__status=JobApplication.Status.PENDING), distinct=True),
        )
        .order_by('-created_at')
    )
    for board in boards:
        board.avg_score = round(board.avg_score or 0)
        board.low_count = board.applicant_count - board.high_count - board.mid_count
        board.high_pct = round(100 * board.high_count / board.applicant_count) if board.applicant_count else 0
        board.mid_pct = round(100 * board.mid_count / board.applicant_count) if board.applicant_count else 0
    return boards