# ============================================================
# ADDITIVE: HR MIS & Workforce Analytics - analytics engine
# ------------------------------------------------------------
# Pure read logic. Every number is computed from the live tables of the
# existing modules; no report data is stored or duplicated.
#
# Used by People/views_reports.py (Admin / HR - whole company) and
# People/views_reports_self.py (employee = own data, manager = team).
# `codes` = None means "whole company"; a list restricts every query to
# those employee_codes (self / team views).
# ============================================================

import re
from calendar import monthrange
from collections import Counter, defaultdict
from datetime import date, timedelta
from decimal import Decimal, ROUND_HALF_UP

from django.db.models import Avg, Count, Q, Sum
from django.utils import timezone

from Company.leave_engine import balances_for, financial_year, fy_range, valid_fy
from Company import performance_engine as perf
from Employee.leave_models import LeavePolicy
from Employee.models import AttendanceEmp, EMP_check_in_check_out, EmployeeDT, LeaveRequest, Payroll
from Employee.performance_models import PerfAppraisal, PerfRatingHistory
from Recruitment.models import CandidateProfile, InterviewRound, JobRequisition, Offer, StageHistory, STAGES
from Training.models import TrainingAttendance, TrainingCertificate, TrainingEnrollment, TrainingProgram

from .common import employees_qs
from .exit_models import ExitInterview
from .models import ExitClearance
from .reports_models import ExitReasonGroup, ReportBenchmark

# ------------------------------------------------------------
# constants / defaults
# ------------------------------------------------------------

DEFAULT_BENCHMARKS = {
    "industry_attrition_percent": {"label": "Industry attrition benchmark", "value": Decimal("14"), "unit": "%"},
    "office_start_minutes": {"label": "Office start time (minutes after midnight, 570 = 09:30)",
                             "value": Decimal("570"), "unit": "minutes"},
    "punctuality_grace_minutes": {"label": "Late-mark grace period", "value": Decimal("15"), "unit": "minutes"},
}

# applied only until HR saves its own groups (POST exit-reason-groups/seed-defaults/ persists them)
DEFAULT_EXIT_GROUPS = [
    {"name": "Higher Education / Relocation", "reasons": ["Higher Studies", "Relocation"],
     "preventable_percent": 10, "recommendation": "Offer sabbaticals and remote work contracts"},
    {"name": "Compensation / External Offer", "reasons": ["Compensation", "Better Opportunity"],
     "preventable_percent": 65, "recommendation": "Align tech bands to 75th percentile benchmark"},
    {"name": "Career Role Progression", "reasons": ["Career Growth", "Work Environment"],
     "preventable_percent": 80, "recommendation": "Accelerate internal promotion tracks"},
]

LEAVE_STATUSES = ("Paid Leave", "Sick Leave", "Casual Leave")
PRE_INTERVIEW_STAGES = ("INTERVIEW", "FEEDBACK_PENDING", "SELECTED", "OFFER_SENT", "OFFER_ACCEPTED",
                        "DOCS_PENDING", "BGV", "READY_TO_JOIN", "JOINED")
TABS = ["hr-mis", "recruitment", "attendance", "leave", "payroll", "employees", "performance", "training", "exit"]


# ------------------------------------------------------------
# small helpers
# ------------------------------------------------------------

def D(v):
    return Decimal(str(v if v not in (None, "") else 0))


def f1(v, places=1):
    if v is None:
        return None
    return float(D(v).quantize(Decimal(1).scaleb(-places), rounding=ROUND_HALF_UP))


def pct(part, whole, places=1):
    return f1(D(part) * 100 / D(whole), places) if whole else None


def inr(v):
    """Indian short money format used by the UI: Rs 3.42 Cr / Rs 58 Lakhs."""
    v = float(v or 0)
    if abs(v) >= 1e7:
        return f"₹{v / 1e7:.2f} Cr"
    if abs(v) >= 1e5:
        return f"₹{round(v / 1e5, 1):g} Lakhs"
    return f"₹{v:,.0f}"


def clean_text(v, fallback="Unassigned"):
    v = (v or "").strip() if isinstance(v, str) else v
    return v or fallback


def parse_minutes(t):
    """'09:12:33' | '09:12' | '9:12 AM' -> minutes after midnight (None if unreadable)."""
    if not t:
        return None
    m = re.match(r"^\s*(\d{1,2}):(\d{2})(?::\d{2})?\s*([AaPp][Mm])?\s*$", str(t))
    if not m:
        return None
    h, mi, ap = int(m.group(1)), int(m.group(2)), m.group(3)
    if ap:
        h = h % 12 + (12 if ap.lower() == "pm" else 0)
    return h * 60 + mi if h < 24 and mi < 60 else None


def parse_years(text):
    m = re.search(r"\d+(?:\.\d+)?", str(text or ""))
    return float(m.group()) if m else None


def years_between(a, b):
    return (b - a).days / 365.25


def fmt_years(v):
    return None if v is None else f"{v:.1f} Yrs"


def get_benchmark(company, key, fy=None):
    """Saved value (FY specific first, then 'all years') else the built-in default (or None)."""
    qs = ReportBenchmark.objects.filter(company=company, key=key, is_active=True)
    row = (qs.filter(financial_year=fy).first() if fy else None) or qs.filter(financial_year="").first()
    if row is not None:
        return D(row.value)
    d = DEFAULT_BENCHMARKS.get(key)
    return d["value"] if d else None


def period_from(params):
    """?from=&to= | ?month=YYYY-MM | ?financial_year=2026-27 (default = current FY). -> (start, end, fy)."""
    from .common import to_date          # local import keeps module import light
    p = params
    if p.get("month"):
        try:
            y, m = [int(x) for x in str(p["month"]).split("-")]
            return date(y, m, 1), date(y, m, monthrange(y, m)[1]), financial_year(date(y, m, 1))
        except (ValueError, IndexError):
            raise ValueError("month must look like 2026-09.")
    if p.get("from") or p.get("to"):
        start, end = to_date(p.get("from")), to_date(p.get("to"))
        start = start or (end - timedelta(days=364))
        end = end or timezone.localdate()
        if end < start:
            raise ValueError("'to' cannot be before 'from'.")
        return start, end, financial_year(end)
    fy = p.get("financial_year") or financial_year()
    if not valid_fy(fy):
        raise ValueError("financial_year must look like 2026-27.")
    s, e = fy_range(fy)
    return s, e, fy


def direct_reports(company, manager_code):
    from Employee.leave_models import ReportingHierarchy
    return list(ReportingHierarchy.objects.filter(company=company, manager_code=manager_code, is_active=True)
                .values_list("Employee_ID", flat=True))


def emps(company, codes=None, active=True):
    qs = employees_qs(company)
    if active:
        qs = qs.filter(is_active=True)
    return qs.filter(employee_code__in=codes) if codes is not None else qs


EMP_COLS = ("employee_code", "name", "Gender", "Date_of_Birth", "Date_of_Joining", "Basic_Salary", "Employee_Type",
            "Work_Location", "Work_Experience", "Designation", "department__name", "Department",
            "people_profile__branch__name")


def emp_rows(qs):
    out = []
    for r in qs.values(*EMP_COLS):
        r["dept"] = clean_text(r["department__name"] or r["Department"])
        r["hub"] = clean_text(r["people_profile__branch__name"] or r["Work_Location"], "Unassigned")
        out.append(r)
    return out


# ------------------------------------------------------------
# shared KPI: attrition
# ------------------------------------------------------------

def attrition(company, start, end):
    """exits / average headcount. Exit = ExitClearance Completed with last_working_day in range."""
    exits_qs = ExitClearance.objects.filter(company=company, is_active=True, status="Completed",
                                            last_working_day__gte=start, last_working_day__lte=end)
    exits = exits_qs.count()
    active = emps(company).count()
    joins = employees_qs(company).filter(Date_of_Joining__gte=start, Date_of_Joining__lte=end).count()
    start_hc = max(0, active - joins + exits)
    avg = (start_hc + active) / 2
    return {"exits": exits, "joins": joins, "start_headcount": start_hc, "end_headcount": active,
            "average_headcount": f1(avg), "rate_percent": pct(exits, avg)}


def time_to_hire(company, start, end):
    """Average days from candidate profile creation to the JOINED stage (joins inside the range)."""
    first_join = {}
    for h in (StageHistory.objects.filter(company=company, to_stage="JOINED", changed_at__date__gte=start,
                                          changed_at__date__lte=end).order_by("changed_at")):
        first_join.setdefault(h.candidate_id, h.changed_at)
    if not first_join:
        return {"average_days": None, "hires": 0}
    created = dict(CandidateProfile.objects.filter(company=company, candidate_id__in=first_join)
                   .values_list("candidate_id", "created_at"))
    days = [max(0, (first_join[c] - created[c]).days) for c in first_join if c in created]
    return {"average_days": f1(sum(days) / len(days)) if days else None, "hires": len(days)}


def latest_payroll_month(company, start, end, codes=None):
    qs = Payroll.objects.filter(company=company, is_active=True)
    if codes is not None:
        qs = qs.filter(Employee_ID__in=codes)
    best = None
    for y, m in qs.values_list("year", "month").distinct():
        if date(y, m, 1) <= end and date(y, m, monthrange(y, m)[1]) >= start:
            best = max(best, (y, m)) if best else (y, m)
    return best


# ============================================================
# 1. HR MIS
# ============================================================

def hr_mis(company, start, end, fy, can_payroll=True):
    rows = emp_rows(emps(company))
    total = len(rows)
    att = attrition(company, start, end)
    tth = time_to_hire(company, start, end)
    bench = get_benchmark(company, "industry_attrition_percent", fy)

    pm = latest_payroll_month(company, start, end) if can_payroll else None
    gross_by_code, gross_total = {}, Decimal("0")
    if pm:
        for code, g in Payroll.objects.filter(company=company, is_active=True, year=pm[0], month=pm[1]) \
                .values_list("Employee_ID", "gross_salary"):
            gross_by_code[code] = gross_by_code.get(code, Decimal("0")) + D(g)
            gross_total += D(g)

    today = timezone.localdate()
    by = defaultdict(lambda: {"headcount": 0, "wage": Decimal("0"), "exp": [], "ten": []})
    for r in rows:
        b = by[r["dept"]]
        b["headcount"] += 1
        if can_payroll:
            b["wage"] += gross_by_code.get(r["employee_code"], D(r["Basic_Salary"]) if not pm else Decimal("0"))
        yrs = parse_years(r["Work_Experience"])
        if r["Date_of_Joining"]:
            b["ten"].append(years_between(r["Date_of_Joining"], today))
        if yrs is not None:
            b["exp"].append(yrs)

    depts = []
    for name, b in sorted(by.items(), key=lambda kv: -kv[1]["headcount"]):
        exp = b["exp"] or b["ten"]
        depts.append({
            "department": name, "headcount": b["headcount"], "percent_of_total": pct(b["headcount"], total),
            "percent_label": f"{pct(b['headcount'], total)}%",
            "monthly_wage_bill": f1(b["wage"], 0) if can_payroll else None,
            "monthly_wage_bill_label": inr(b["wage"]) if can_payroll else None,
            "avg_experience_years": f1(sum(exp) / len(exp)) if exp else None,
            "avg_experience_label": fmt_years(sum(exp) / len(exp)) if exp else None,
            "experience_source": "work_experience" if b["exp"] else "tenure"})

    return {
        "title": "HR MIS & Workforce Analytics",
        "subtitle": "Real-time headcount trends, recruitment throughput, and statutory reporting exports",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "cards": {
            "active_headcount": {"title": "Active Headcount", "value": total, "label": str(total),
                                 "caption": "Full-time on roll"},
            "annual_attrition_rate": {"title": "Annual Attrition Rate", "value": att["rate_percent"],
                                      "label": f"{att['rate_percent']}%" if att["rate_percent"] is not None else "-",
                                      "caption": f"Industry benchmark: {float(bench):g}%" if bench is not None else "",
                                      "benchmark_percent": float(bench) if bench is not None else None,
                                      "detail": att},
            "recruitment_turnaround": {"title": "Recruitment Turnaround", "value": tth["average_days"],
                                       "label": f"{tth['average_days']:g} Days" if tth["average_days"] is not None else "-",
                                       "caption": "Time to hire average", "hires": tth["hires"]},
            "monthly_payroll_outflow": {
                "title": "Monthly Payroll Outflow", "restricted": not can_payroll,
                "value": f1(gross_total, 0) if can_payroll else None,
                "label": inr(gross_total) if (can_payroll and pm) else ("-" if can_payroll else None),
                "caption": "Gross monthly compensation", "month": f"{pm[0]}-{pm[1]:02d}" if pm else None}},
        "department_table": {
            "title": "Department Headcount & Cost Distribution",
            "wage_source": "payroll" if pm else ("basic_salary" if can_payroll else "restricted"),
            "rows": depts,
            "total": {"headcount": total, "monthly_wage_bill": f1(sum((b["wage"] for b in by.values()), Decimal("0")), 0)
                      if can_payroll else None}},
    }


# ============================================================
# 2. RECRUITMENT
# ============================================================

def recruitment(company, start, end, fy):
    profs = list(CandidateProfile.objects.filter(company=company, created_at__date__gte=start,
                                                 created_at__date__lte=end).select_related("candidate"))
    ids = [p.candidate_id for p in profs]
    interviewed = set(InterviewRound.objects.filter(company=company, candidate_id__in=ids)
                      .values_list("candidate_id", flat=True))
    interviewed |= set(StageHistory.objects.filter(company=company, candidate_id__in=ids, to_stage="INTERVIEW")
                       .values_list("candidate_id", flat=True))
    interviewed |= {p.candidate_id for p in profs if p.candidate.candidate_status in PRE_INTERVIEW_STAGES}
    offers = list(Offer.objects.filter(company=company, candidate_id__in=ids, sent_at__isnull=False))

    chan = defaultdict(lambda: {"applied": 0, "interviewed": 0, "released": 0, "accepted": 0, "joined": 0})
    src = {p.candidate_id: clean_text(p.candidate.candidate_source, "Direct / Unspecified") for p in profs}
    for p in profs:
        c = chan[src[p.candidate_id]]
        c["applied"] += 1
        c["interviewed"] += p.candidate_id in interviewed
        c["joined"] += p.candidate.candidate_status == "JOINED"
    for o in offers:
        c = chan[src[o.candidate_id]]
        c["released"] += 1
        c["accepted"] += o.status == "Accepted"

    rows = []
    for name, c in sorted(chan.items(), key=lambda kv: -kv[1]["applied"]):
        acc = pct(c["accepted"], c["released"])
        rows.append({"source_channel": name, "total_applied": c["applied"], "interviewed": c["interviewed"],
                     "offers_released": c["released"], "offers_accepted": c["accepted"], "joined": c["joined"],
                     "offer_acceptance_percent": acc,
                     "offer_acceptance_label": (f"{acc:g}%" if acc is not None else "-")})
    stage_counts = Counter(p.candidate.candidate_status for p in profs)
    reqs = JobRequisition.objects.filter(company=company, is_active=True)
    open_reqs = reqs.filter(approval_status="Approved", closed_at__isnull=True)
    tth = time_to_hire(company, start, end)
    return {
        "title": "Sourcing Channel Performance & Conversion Throughput",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "channels": rows,
        "totals": {"applied": len(profs), "interviewed": len(interviewed), "offers_released": len(offers),
                   "offers_accepted": sum(1 for o in offers if o.status == "Accepted"),
                   "joined": sum(1 for p in profs if p.candidate.candidate_status == "JOINED"),
                   "offer_acceptance_percent": pct(sum(1 for o in offers if o.status == "Accepted"), len(offers))},
        "funnel": [{"stage": code, "label": label, "count": stage_counts.get(code, 0)} for code, label in STAGES],
        "requisitions": {"open": open_reqs.count(),
                         "open_vacancies": open_reqs.aggregate(v=Sum("vacancies"))["v"] or 0,
                         "total": reqs.count()},
        "time_to_hire": tth,
    }


# ============================================================
# 3. ATTENDANCE
# ============================================================

def punctuality_cutoff(company):
    """Late-mark cutoff in minutes after midnight.

    Follows the company's saved Attendance Policy (shift start + grace) when
    one exists; otherwise falls back to the report benchmarks."""
    try:
        from .att_engine import get_policy, late_cutoff_minutes
        policy = get_policy(company)
        if policy is not None and policy.pk is not None:
            return late_cutoff_minutes(policy)
    except Exception:
        pass
    return int(get_benchmark(company, "office_start_minutes")) + int(get_benchmark(company, "punctuality_grace_minutes"))


def attendance(company, start, end, fy, codes=None, muster=False, limit=100, offset=0):
    cutoff = punctuality_cutoff(company)
    emap = {r["employee_code"]: r for r in emp_rows(emps(company, codes, active=False))}

    base = AttendanceEmp.objects.filter(company=company, is_active=True, date__gte=start, date__lte=end)
    ins = EMP_check_in_check_out.objects.filter(company=company, is_active=True, login_date__gte=start,
                                                login_date__lte=end)
    if codes is not None:
        base, ins = base.filter(Employee_ID__in=codes), ins.filter(Employee_ID__in=codes)

    per = defaultdict(Counter)
    for code, status in base.values_list("Employee_ID", "status"):
        per[code][status] += 1
    punct = defaultdict(lambda: [0, 0])               # code -> [on_time, total]
    seen = set()
    for code, d, t in ins.values_list("Employee_ID", "login_date", "login_time"):
        if (code, d) in seen:
            continue
        seen.add((code, d))
        m = parse_minutes(t)
        if m is None:
            continue
        punct[code][1] += 1
        punct[code][0] += m <= cutoff

    def rate(c):
        worked = c["Present"] + c["Half Day"] + c["Absent"]
        return pct(c["Present"] + 0.5 * c["Half Day"], worked)

    tot = Counter()
    for c in per.values():
        tot.update(c)
    on_time = sum(v[0] for v in punct.values())
    checked = sum(v[1] for v in punct.values())
    overall_p = pct(on_time, checked)

    def group(key):
        agg = defaultdict(lambda: {"on": 0, "n": 0, "people": set()})
        for code, (on, n) in punct.items():
            g = agg[emap.get(code, {}).get(key, "Unassigned")]
            g["on"] += on
            g["n"] += n
            g["people"].add(code)
        return [{key if key != "dept" else "department": k, "employees": len(v["people"]),
                 "check_ins": v["n"], "punctuality_percent": pct(v["on"], v["n"])}
                for k, v in sorted(agg.items(), key=lambda kv: -kv[1]["n"])]

    hubs = group("hub")
    hub_count = len([h for h in hubs if h["check_ins"]])
    insight = (f"Average workforce punctuality rate is {overall_p:g}% across all {hub_count} regional "
               f"hub{'s' if hub_count != 1 else ''}." if overall_p is not None
               else "No check-in data for this period yet.")

    out = {
        "title": "Monthly Workforce Attendance Muster & Punctuality",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "insight": insight,
        "punctuality": {"on_time_check_ins": on_time, "check_ins": checked, "rate_percent": overall_p,
                        "late_cutoff_minutes": cutoff,
                        "late_cutoff_time": f"{cutoff // 60:02d}:{cutoff % 60:02d}"},
        "summary": {"present": tot["Present"], "absent": tot["Absent"], "half_day": tot["Half Day"],
                    "holiday": tot["Holiday"], "leave": sum(tot[s] for s in LEAVE_STATUSES),
                    "attendance_rate_percent": rate(tot), "employees_with_records": len(per)},
        "by_hub": hubs, "by_department": group("dept"),
    }
    if muster:
        codes_sorted = sorted(set(per) | set(punct))
        rows = []
        for code in codes_sorted[offset:offset + limit]:
            c, (on, n) = per[code], punct[code]
            e = emap.get(code, {})
            rows.append({"employee_code": code, "name": e.get("name"), "department": e.get("dept"),
                         "present": c["Present"], "absent": c["Absent"], "half_day": c["Half Day"],
                         "leave": sum(c[s] for s in LEAVE_STATUSES), "holiday": c["Holiday"],
                         "attendance_rate_percent": rate(c), "punctuality_percent": pct(on, n)})
        out["muster"] = {"count": len(codes_sorted), "limit": limit, "offset": offset, "results": rows}
    return out


# ============================================================
# 4. LEAVE
# ============================================================

def fy_quarter_label(d, fy_start_year=None):
    q = ((d.month - 4) % 12) // 3 + 1
    return f"Q{q} close"


def quarter_end(d):
    """Financial-year quarter end (Jun 30 / Sep 30 / Dec 31 / Mar 31) of the quarter containing d."""
    month, day = [(6, 30), (9, 30), (12, 31), (3, 31)][((d.month - 4) % 12) // 3]
    return date(d.year, month, day)


def leave(company, start, end, fy, codes=None, as_of=None):
    """Liability is projected to the close of the current financial-year quarter ('as of Q3 close')."""
    as_of = as_of or min(quarter_end(min(timezone.localdate(), end)), end)
    reqs = LeaveRequest.objects.filter(company=company, is_active=True, from_date__gte=start, from_date__lte=end)
    if codes is not None:
        reqs = reqs.filter(Employee_ID__in=codes)
    by_type = defaultdict(lambda: {"requests": 0, "approved_days": Decimal("0"), "pending_days": Decimal("0"),
                                   "rejected": 0})
    monthly = defaultdict(Decimal)
    for t, st, days, fd in reqs.values_list("leave_type", "status", "total_days", "from_date"):
        b = by_type[t]
        b["requests"] += 1
        if st == "Approved":
            b["approved_days"] += D(days)
            monthly[fd.strftime("%Y-%m")] += D(days)
        elif st == "Pending":
            b["pending_days"] += D(days)
        elif st == "Rejected":
            b["rejected"] += 1

    # balances + encashment liability (policy.encashable, capped by max_encashable_days)
    policies = {p.leave_type: p for p in LeavePolicy.objects.filter(company=company, is_active=True)}
    alloc, used = defaultdict(Decimal), defaultdict(Decimal)
    liab_days, liab_amt = defaultdict(Decimal), defaultdict(Decimal)
    people = emps(company, codes).values_list("employee_code", "Basic_Salary")
    fy_asof = financial_year(as_of)
    n_people = 0
    for code, basic in people:
        n_people += 1
        per_day = D(basic) / 30
        for b in balances_for(company, code, fy_asof):
            if b["unlimited"]:
                continue
            alloc[b["leave_type"]] += D(b["total"])
            used[b["leave_type"]] += D(b["used"])
            p = policies.get(b["leave_type"])
            if p is not None and p.encashable:
                avail = max(Decimal("0"), D(b["available"]))
                cap = p.max_encashable_days
                days = min(avail, D(cap)) if cap is not None else avail
                liab_days[b["leave_type"]] += days
                liab_amt[b["leave_type"]] += days * per_day

    types = []
    for t in sorted(set(by_type) | set(alloc)):
        b = by_type.get(t) or {"requests": 0, "approved_days": Decimal("0"), "pending_days": Decimal("0"), "rejected": 0}
        types.append({"leave_type": t, "requests": b["requests"], "approved_days": f1(b["approved_days"], 2),
                      "pending_days": f1(b["pending_days"], 2), "rejected_requests": b["rejected"],
                      "allocated_days": f1(alloc.get(t, 0), 2), "used_days": f1(used.get(t, 0), 2),
                      "utilization_percent": pct(used.get(t, 0), alloc.get(t, 0)),
                      "encashable": bool(policies.get(t) and policies[t].encashable),
                      "encashable_days": f1(liab_days.get(t, 0), 2), "liability_amount": f1(liab_amt.get(t, 0), 2)})
    total_liab = sum(liab_amt.values(), Decimal("0"))
    label = fy_quarter_label(as_of)
    return {
        "title": "Leave Utilization & Liability Report",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "insight": (f"Total accumulated earned leave encashment liability is projected at {inr(total_liab)} "
                    f"as of {label}."),
        "liability": {"total_amount": f1(total_liab, 2), "total_amount_label": inr(total_liab),
                      "encashable_days": f1(sum(liab_days.values(), Decimal("0")), 2),
                      "as_of": str(as_of), "as_of_label": label, "employees_considered": n_people,
                      "basis": "encashable leave balance x (basic salary / 30)"},
        "utilization": {"allocated_days": f1(sum(alloc.values(), Decimal("0")), 2),
                        "used_days": f1(sum(used.values(), Decimal("0")), 2),
                        "percent": pct(sum(used.values(), Decimal("0")), sum(alloc.values(), Decimal("0")))},
        "by_leave_type": types,
        "monthly_trend": [{"month": m, "approved_days": f1(d, 2)} for m, d in sorted(monthly.items())],
    }


# ============================================================
# 5. PAYROLL
# ============================================================

def payroll(company, start, end, fy, codes=None):
    qs = Payroll.objects.filter(company=company, is_active=True)
    if codes is not None:
        qs = qs.filter(Employee_ID__in=codes)
    months = defaultdict(lambda: {"gross": Decimal("0"), "net": Decimal("0"), "ded": Decimal("0"),
                                  "ot": Decimal("0"), "pf": Decimal("0"), "tds": Decimal("0"), "esi": Decimal("0"),
                                  "n": 0})
    rows = []
    for r in qs.values("Employee_ID", "year", "month", "gross_salary", "net_salary", "total_deductions",
                       "overtime_pay", "PF_deduction", "TDS_deduction", "ESI_deduction"):
        d1, d2 = date(r["year"], r["month"], 1), date(r["year"], r["month"], monthrange(r["year"], r["month"])[1])
        if d2 < start or d1 > end:
            continue
        rows.append(r)
        m = months[(r["year"], r["month"])]
        m["gross"] += D(r["gross_salary"])
        m["net"] += D(r["net_salary"])
        m["ded"] += D(r["total_deductions"])
        m["ot"] += D(r["overtime_pay"])
        m["pf"] += D(r["PF_deduction"])
        m["tds"] += D(r["TDS_deduction"])
        m["esi"] += D(r["ESI_deduction"])
        m["n"] += 1

    keys = sorted(months)
    trend, prev = [], None
    for k in keys:
        m = months[k]
        trend.append({"month": f"{k[0]}-{k[1]:02d}", "employees": m["n"], "gross": f1(m["gross"], 2),
                      "net": f1(m["net"], 2), "deductions": f1(m["ded"], 2), "overtime": f1(m["ot"], 2),
                      "mom_variance_percent": pct(m["gross"] - prev, prev) if prev else None})
        prev = m["gross"]
    ytd = sum((months[k]["gross"] for k in keys), Decimal("0"))

    budget = get_benchmark(company, "annual_payroll_budget", fy) if codes is None else None   # budget is company-wide
    projected = (ytd / len(keys) * 12) if keys else None
    tracking = pct(projected, budget) if (projected is not None and budget) else None
    fy_label = f"FY{str(fy)[:4]}"
    if tracking is not None:
        insight = f"Gross annual payroll budget tracking at {tracking:g}% of initial {fy_label} forecast."
    elif codes is not None:
        insight = "Payroll summary for the selected employee(s)." if keys else "No payroll has been processed in this period yet."
    elif not budget:
        insight = (f"No annual payroll budget is configured for {fy_label}. Add benchmark "
                   f"'annual_payroll_budget' to track the forecast.")
    else:
        insight = "No payroll has been processed in this period yet."

    emap = {r["employee_code"]: r["dept"] for r in emp_rows(emps(company, codes, active=False))}
    dept = defaultdict(lambda: {"gross": Decimal("0"), "prev": Decimal("0"), "n": 0})
    if keys:
        cur, pre = keys[-1], keys[-2] if len(keys) > 1 else None
        for r in rows:
            k = (r["year"], r["month"])
            d = dept[emap.get(r["Employee_ID"], "Unassigned")]
            if k == cur:
                d["gross"] += D(r["gross_salary"])
                d["n"] += 1
            elif k == pre:
                d["prev"] += D(r["gross_salary"])
    return {
        "title": "Compensation Variance & Outflow Analysis",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "insight": insight,
        "budget": {"configured": bool(budget), "annual_budget": f1(budget, 2) if budget else None,
                   "annual_budget_label": inr(budget) if budget else None,
                   "ytd_gross": f1(ytd, 2), "ytd_gross_label": inr(ytd), "months_processed": len(keys),
                   "projected_annual_gross": f1(projected, 2) if projected is not None else None,
                   "tracking_percent": tracking, "ytd_percent_of_budget": pct(ytd, budget) if budget else None},
        "totals": {"gross": f1(ytd, 2), "net": f1(sum((months[k]["net"] for k in keys), Decimal("0")), 2),
                   "deductions": f1(sum((months[k]["ded"] for k in keys), Decimal("0")), 2),
                   "overtime": f1(sum((months[k]["ot"] for k in keys), Decimal("0")), 2),
                   "pf": f1(sum((months[k]["pf"] for k in keys), Decimal("0")), 2),
                   "tds": f1(sum((months[k]["tds"] for k in keys), Decimal("0")), 2),
                   "esi": f1(sum((months[k]["esi"] for k in keys), Decimal("0")), 2)},
        "monthly_trend": trend,
        "by_department_latest_month": [
            {"department": k, "employees": v["n"], "gross": f1(v["gross"], 2), "gross_label": inr(v["gross"]),
             "mom_variance_percent": pct(v["gross"] - v["prev"], v["prev"]) if v["prev"] else None}
            for k, v in sorted(dept.items(), key=lambda kv: -kv[1]["gross"])],
    }


# ============================================================
# 6. EMPLOYEES (demographics & diversity)
# ============================================================

def band(value, edges, labels):
    for e, l in zip(edges, labels):
        if value < e:
            return l
    return labels[-1]


def employees_report(company, start, end, fy, codes=None):
    rows = emp_rows(emps(company, codes))
    today = timezone.localdate()
    n = len(rows)
    gender = Counter(clean_text(r["Gender"], "Not specified").strip().title() for r in rows)
    known = {k: v for k, v in gender.items() if k != "Not Specified"}
    kn = sum(known.values())
    ages = [years_between(r["Date_of_Birth"], today) for r in rows if r["Date_of_Birth"]]
    ten = [years_between(r["Date_of_Joining"], today) for r in rows if r["Date_of_Joining"]]
    age_bands = Counter(band(a, (25, 30, 35, 40, 50), ("Under 25", "25-29", "30-34", "35-39", "40-49", "50+")) for a in ages)
    ten_bands = Counter(band(t, (1, 3, 5, 10), ("< 1 yr", "1-3 yrs", "3-5 yrs", "5-10 yrs", "10+ yrs")) for t in ten)
    parts = " / ".join(f"{round(v * 100 / kn)}% {k}" for k, v in sorted(known.items(), key=lambda kv: -kv[1])) if kn else None
    avg_ten = sum(ten) / len(ten) if ten else None
    avg_age = sum(ages) / len(ages) if ages else None
    return {
        "title": "Workforce Demographics & Diversity Report",
        "period": {"as_of": str(today)},
        "headcount": n,
        "gender_diversity": {"label": parts, "counts": dict(gender),
                             "percent": {k: pct(v, kn) for k, v in known.items()}, "unspecified": gender.get("Not Specified", 0)},
        "average_employee_tenure": {"years": f1(avg_ten), "label": f"{avg_ten:.1f} Years" if avg_ten is not None else None},
        "average_workforce_age": {"years": f1(avg_age), "label": f"{avg_age:.1f} Years" if avg_age is not None else None},
        "age_bands": [{"band": b, "count": age_bands.get(b, 0)} for b in ("Under 25", "25-29", "30-34", "35-39", "40-49", "50+")],
        "tenure_bands": [{"band": b, "count": ten_bands.get(b, 0)} for b in ("< 1 yr", "1-3 yrs", "3-5 yrs", "5-10 yrs", "10+ yrs")],
        "by_employee_type": [{"employee_type": k, "count": v} for k, v in
                             Counter(clean_text(r["Employee_Type"], "Not specified") for r in rows).most_common()],
        "by_hub": [{"hub": k, "count": v} for k, v in Counter(r["hub"] for r in rows).most_common()],
        "by_department": [{"department": k, "count": v} for k, v in Counter(r["dept"] for r in rows).most_common()],
        "joiners_in_period": employees_qs(company).filter(Date_of_Joining__gte=start, Date_of_Joining__lte=end,
                                                          **({"employee_code__in": codes} if codes is not None else {})).count(),
    }


# ============================================================
# 7. PERFORMANCE  (re-uses the Performance module engine)
# ============================================================

def performance(company, params, codes=None):
    cycle, fy, error = perf.resolve_scope(company, params)
    if error:
        raise ValueError(error)
    if codes is None:
        dist = perf.rating_distribution(company, cycle, fy)
        cards = perf.dashboard_cards(company, cycle, fy)
    else:                                              # same bucket logic, restricted to these employees
        qs = PerfRatingHistory.objects.filter(company=company, is_active=True, employee__employee_code__in=codes)
        if cycle is not None:
            qs = qs.filter(cycle=cycle)
        elif fy:
            qs = qs.filter(Q(financial_year=fy) | Q(cycle__financial_year=fy))
        latest = {}
        for r in qs.order_by("employee_id", "-created_at", "-id"):
            latest.setdefault(r.employee_id, r)
        counts = Counter(r.rating_bucket for r in latest.values())
        total = sum(counts.values())
        dist = {"total_rated": total,
                "average_rating": f1(sum(r.rating for r in latest.values()) / total) if total else None,
                "distribution": [{
                    "rating": b, "label": lbl, "short_label": short, "title": f"Rating {b} ({short})",
                    "count": counts.get(b, 0), "percent": pct(counts.get(b, 0), total) or 0.0,
                    "percent_label": f"{pct(counts.get(b, 0), total) or 0:g}%",
                    "reference_percent": perf.REFERENCE_CURVE[b]}
                    for _f, b, lbl, short in perf.RATING_BANDS],
                "reference_curve": perf.REFERENCE_CURVE}
        ap = PerfAppraisal.objects.filter(company=company, is_active=True, employee__employee_code__in=codes)
        ap = ap.filter(cycle=cycle) if cycle is not None else ap.filter(Q(financial_year=fy) | Q(cycle__financial_year=fy))
        a = ap.aggregate(n=Count("id"), done=Count("id", filter=Q(status="Completed")), avg=Avg("normalized_rating"),
                         hp=Count("id", filter=Q(potential="High")))
        cards = {"company_average_rating": {"value": f1(a["avg"])},
                 "appraisal_completion": {"completed": a["done"], "total": a["n"], "value": pct(a["done"], a["n"])},
                 "high_potential_talent": {"value": a["hp"]}}
    return {"title": "Bell Curve Distribution & Historical Ratings",
            "scope": {"cycle_id": cycle.pk if cycle else None, "cycle_name": cycle.name if cycle else None,
                      "financial_year": fy},
            "cards": cards, **dist}


# ============================================================
# 8. TRAINING
# ============================================================

def training(company, start, end, fy, codes=None, catalog_limit=100):
    att = TrainingAttendance.objects.filter(company=company, is_active=True, status__in=("Present", "Late"),
                                            session__session_date__gte=start, session__session_date__lte=end)
    certs = TrainingCertificate.objects.filter(company=company, is_active=True, issued_on__gte=start, issued_on__lte=end)
    enr = TrainingEnrollment.objects.filter(company=company, is_active=True)
    if codes is not None:
        att = att.filter(enrollment__employee__employee_code__in=codes)
        certs = certs.filter(employee__employee_code__in=codes)
        enr = enr.filter(employee__employee_code__in=codes)
    hours = att.aggregate(h=Sum("hours_attended"))["h"] or 0
    issued = certs.count()
    valid = certs.filter(status="Issued").count()
    sat = enr.filter(feedback_rating__isnull=False, completed_on__gte=start, completed_on__lte=end) \
        .aggregate(a=Avg("feedback_rating"))["a"]

    programs = TrainingProgram.objects.filter(company=company, is_active=True).exclude(status="Draft")
    if codes is not None:
        programs = programs.filter(enrollments__employee__employee_code__in=codes).distinct()
    active = programs.filter(status__in=("Upcoming", "Ongoing"))
    cats = sorted({c for c in active.values_list("category", flat=True) if c})

    ecount = dict(TrainingEnrollment.objects.filter(company=company, is_active=True, status__in=("Enrolled", "Completed"))
                  .values_list("program_id").annotate(n=Count("id")))
    catalog = []
    for p in programs.order_by("-start_date", "-id")[:catalog_limit]:
        n = ecount.get(p.id, 0)
        catalog.append({
            "id": p.id, "code": p.code, "program_title": p.title, "trainer": p.trainer_name or (p.trainer.name if p.trainer_id else None),
            "duration_hours": f1(p.duration_hours), "duration_label": f"{float(p.duration_hours):g} Hours",
            "enrolled": n, "enrolled_label": f"{n} {p.audience_label or 'Employees'}", "status": p.status,
            "start_date": str(p.start_date) if p.start_date else None, "enroll_endpoint": f"/employee/training/programs/{p.id}/nominate/"})
    return {
        "title": "Corporate Course & Workshop Catalog",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "cards": {
            "training_hours_logged": {"title": "Training Hours Logged", "value": f1(hours), "label": f"{float(hours):,.0f} Hrs",
                                      "caption": "Year-to-date total"},
            "certificates_issued": {"title": "Certificates Issued", "value": issued, "label": str(issued),
                                    "caption": f"{pct(valid, issued):g}% verified credentials" if issued else "No certificates yet"},
            "active_courses": {"title": "Active Courses", "value": active.count(), "label": str(active.count()),
                               "caption": " + ".join(cats)},
            "average_satisfaction": {"title": "Average Satisfaction", "value": f1(sat),
                                     "label": f"{float(sat):.1f} / 5.0" if sat is not None else "-",
                                     "caption": "Post-session surveys"}},
        "catalog": catalog,
    }


# ============================================================
# 9. EXIT
# ============================================================

def exit_groups(company):
    saved = list(ExitReasonGroup.objects.filter(company=company, is_active=True))
    if saved:
        return [{"id": g.id, "name": g.name, "reasons": list(g.reasons or []), "preventable_percent": float(g.preventable_percent),
                 "recommendation": g.recommendation} for g in saved], True
    return [{"id": None, **g} for g in DEFAULT_EXIT_GROUPS], False


def exit_report(company, start, end, fy):
    cases = ExitClearance.objects.filter(company=company, is_active=True).exclude(status="Withdrawn") \
        .filter(Q(resignation_date__gte=start, resignation_date__lte=end) | Q(last_working_day__gte=start, last_working_day__lte=end))
    case_ids = list(cases.values_list("id", flat=True))
    reasons = Counter(r for r in ExitInterview.objects.filter(company=company, is_active=True, exit_id__in=case_ids,
                                                              status="Completed").exclude(reason_for_leaving__isnull=True)
                      .values_list("reason_for_leaving", flat=True))
    total = sum(reasons.values())
    groups, configured = exit_groups(company)
    mapped, rows = set(), []
    for g in groups:
        n = sum(reasons.get(r, 0) for r in g["reasons"])
        mapped.update(g["reasons"])
        rows.append({"id": g["id"], "primary_exit_reason": g["name"], "reasons": g["reasons"], "exits": n,
                     "percent_of_total_exits": pct(n, total), "percent_label": f"{pct(n, total):g}%" if total else "-",
                     "preventable_percent": g["preventable_percent"], "preventable_label": f"{g['preventable_percent']:g}%",
                     "key_retention_recommendation": g["recommendation"]})
    other = sum(v for r, v in reasons.items() if r not in mapped)
    if other:
        rows.append({"id": None, "primary_exit_reason": "Other / Unclassified",
                     "reasons": sorted(r for r in reasons if r not in mapped), "exits": other,
                     "percent_of_total_exits": pct(other, total), "percent_label": f"{pct(other, total):g}%",
                     "preventable_percent": None, "preventable_label": "-", "key_retention_recommendation": None})
    sep = Counter()
    from .exit_models import ExitResignationDetail
    for t in ExitResignationDetail.objects.filter(exit_id__in=case_ids).values_list("separation_type", flat=True):
        sep[t] += 1
    dept = Counter(clean_text(d or e) for d, e in cases.values_list("employee__department__name", "employee__Department"))
    att = attrition(company, start, end)
    interviewed = ExitInterview.objects.filter(company=company, is_active=True, exit_id__in=case_ids, status="Completed").count()
    return {
        "title": "Annual Attrition Drivers & Reason Analysis",
        "period": {"from": str(start), "to": str(end), "financial_year": fy},
        "attrition": att, "exit_cases": len(case_ids), "reasons_recorded": total,
        "interview_coverage_percent": pct(interviewed, len(case_ids)),
        "groups_configured": configured, "rows": rows,
        "by_separation_type": [{"separation_type": k, "count": v} for k, v in sep.most_common()],
        "by_department": [{"department": k, "exits": v} for k, v in dept.most_common()],
    }
