# ============================================================
# ADDITIVE: HR / Admin DESK APIs  (same API for Admin and HR - decided by role checkboxes)
#
#   /api/recruitment/desk/...
#     interviews/      list + pagination + search + filters, one record (full view), export csv/xlsx, import
#     candidates/      list + pagination + search + filters, one record (full view), export, import (+ invite links)
#     documents/       candidate document desk: list, full view, export, bulk verify/reject, import of review sheet,
#                      missing-documents report
#     onboarding/      candidate-link tracker (registered? opened? % complete? submitted?), full view, export
#     filter-options/  every dropdown value in ONE call (department, role, interviewer, outcome, stage, ...)
#
# Nothing existing is touched: every view reads the same tables the existing Recruitment screens use.
# ============================================================
import secrets
from datetime import timedelta

from django.core.exceptions import ValidationError
from django.core.validators import validate_email
from django.db import transaction
from django.db.models import Exists, F, OuterRef, Q, Value
from django.db.models.functions import Coalesce, NullIf
from django.utils import timezone
from rest_framework.decorators import action
from rest_framework.views import APIView
from rest_framework.viewsets import ViewSet

from Employee.models import CandidateEducation, CandidateExperience, EmployeeDocument, EmployeeDT

from . import desk_utils as du
from .common import RecruitmentAuthMixin, candidates_qs, emp_brief, err, get_profile, ok, set_stage, stage_label, \
    to_bool, to_date, to_datetime, to_decimal, next_seq
from .models import STAGES, DocumentReview, InterviewFeedback, InterviewRound, JobRequisition, StageHistory
from .models_portal import CandidatePortalActivity, CandidatePortalLink
from .models_register import CandidatePortalAccount
from .views_candidates import serialize_candidate
from .views_interview import EARLY_STAGES, serialize_feedback
from .views_portal import (CAND_DOC_TYPES, DOC_LABELS, _base_urls, lock_state, missing_items, ser_link, snapshot)
from .views_post import REQUIRED_DOC_TYPES, doc_status, docs_summary, serialize_doc, advance_after_docs

OUTCOMES = [c for c, _ in InterviewFeedback.RECO] + ["Pending"]
DOC_STATUSES = ["Pending", "Verified", "Rejected"]
LINK_STATUSES = ["Not opened", "Opened", "In progress", "Submitted", "Updated after submit", "Expired", "Revoked"]


# ============================================================
# shared building blocks
# ============================================================
def _nn(field):
    return NullIf(F(field), Value(""))


def annotate_candidate(qs, prefix=""):
    """department + role as ONE value each, whatever is filled (requisition first, then the candidate's own)."""
    p = prefix
    return qs.annotate(
        dept=Coalesce(_nn(p + "recruitment_profile__requisition__department__name"),
                      _nn(p + "recruitment_profile__requisition__department_name"),
                      _nn(p + "Department"), Value("")),
        role=Coalesce(_nn(p + "applied_position"),
                      _nn(p + "recruitment_profile__requisition__position_title"), Value("")))


def doc_status_q(status, prefix=""):
    """DB-side version of views_post.doc_status() on EmployeeDocument rows."""
    st = (status or "").strip().lower()
    p = prefix
    if st == "verified":
        return Q(**{p + "review__status": "Verified"}) | Q(**{p + "review__isnull": True, p + "is_verified": True})
    if st == "rejected":
        return Q(**{p + "review__status": "Rejected"})
    if st == "pending":
        return Q(**{p + "review__status": "Pending"}) | Q(**{p + "review__isnull": True, p + "is_verified": False})
    return Q(pk__in=[])


def csv_list(v):
    return [x.strip() for x in str(v).split(",") if x.strip()] if v else []


def text_filter(qs, request, param, field):
    """?param=a,b  -> case-insensitive exact match on any of the values."""
    vals = csv_list(request.query_params.get(param))
    if not vals:
        return qs
    q = Q()
    for v in vals:
        q |= Q(**{field + "__iexact": v})
    return qs.filter(q)


def _cand_brief(c):
    return {"id": c.id, "candidate_id": c.candidate_id, "name": c.name, "email": c.email,
            "phone_number": c.phone_number}


def _person(e):
    return {"employee_code": e.employee_code, "name": e.name, "email": e.email} if e else None


def _bad_date(e):
    return err("VALIDATION", str(e))


class DeskBase(RecruitmentAuthMixin, ViewSet):
    """Shared plumbing. Subclasses define: queryset(), apply_filters(), row(), export_headers, export_row()."""
    permission_action_map = {"export": "view", "filter_options": "view", "import_file": "create",
                             "import_template": "view", "bulk_review": "approve", "missing": "view"}
    filter_keys = ()
    search_fields = ()
    orderings = {}
    default_order = "-id"
    export_name = "export"
    code = "DESK"

    def queryset(self):
        raise NotImplementedError

    def search(self, qs, request):
        s = (request.query_params.get("search") or "").strip()
        if not s:
            return qs
        q = Q()
        for f in self.search_fields:
            q |= Q(**{f + "__icontains": s})
        return qs.filter(q)

    def filtered(self, request):
        qs = self.queryset()
        qs = self.apply_filters(qs, request)
        qs = self.search(qs, request)
        return qs.order_by(du.pick_ordering(request, self.orderings, self.default_order), "-id").distinct()

    def list(self, request):
        try:
            qs = self.filtered(request)
        except ValueError as e:
            return _bad_date(e)
        items, page = du.paginate(request, qs)
        return ok(self.code + "_LIST", "Records.", [self.row(x, request) for x in items],
                  pagination=page, filters_applied=du.applied_filters(request, self.filter_keys + ("search", "ordering")),
                  **self.list_extra(qs, request))

    def list_extra(self, qs, request):
        return {}

    @action(detail=False, methods=["get"])
    def export(self, request):
        """Same filters as the list. ?file=csv (default) | xlsx. No pagination - all matching rows."""
        try:
            qs = self.filtered(request)
        except ValueError as e:
            return _bad_date(e)
        rows = [self.export_row(x, request) for x in qs[:20000]]
        return du.export_response(request, self.export_name, self.export_headers, rows)

    @action(detail=False, methods=["get"], url_path="filter-options")
    def filter_options(self, request):
        return ok("FILTER_OPTIONS", "Dropdown values.", build_options(self.company()))

    @action(detail=False, methods=["get"], url_path="import-template")
    def import_template(self, request):
        if not getattr(self, "import_headers", None):
            return err("NOT_ALLOWED", "Import is not available here.", 405)
        return du.template_response(request, self.export_name + "_import_template", self.import_headers,
                                    self.import_sample)


# ============================================================
# dropdown values (one call for every desk)
# ============================================================
def build_options(company):
    cands = annotate_candidate(candidates_qs(company))
    depts = sorted({d for d in cands.values_list("dept", flat=True) if d}, key=str.lower)
    roles = sorted({r for r in cands.values_list("role", flat=True) if r}, key=str.lower)
    interviewers = (EmployeeDT.objects.filter(company=company, interviews_taken__is_active=True)
                    .distinct().order_by("name"))
    recruiters = EmployeeDT.objects.filter(company=company, recruited_candidates__isnull=False).distinct().order_by("name")
    sources = sorted({x for x in cands.values_list("candidate_source", flat=True) if x}, key=str.lower)
    reqs = JobRequisition.objects.filter(company=company, is_active=True).order_by("-id")[:300]
    return {
        "departments": depts,
        "roles": roles,
        "interviewers": [{"value": e.employee_code, "label": e.name} for e in interviewers],
        "outcomes": OUTCOMES,
        "interview_status": [c for c, _ in InterviewRound.STATUS],
        "feedback_status": [c for c, _ in InterviewRound.FEEDBACK],
        "round_names": sorted({x for x in InterviewRound.objects.filter(company=company, is_active=True)
                               .values_list("round_name", flat=True) if x}, key=str.lower),
        "stages": [{"value": c, "label": l} for c, l in STAGES],
        "recruiters": [{"value": e.employee_code, "label": e.name} for e in recruiters],
        "sources": sources,
        "priorities": [c for c, _ in JobRequisition.PRIORITY],
        "requisitions": [{"value": r.requisition_id, "label": f"{r.requisition_id} - {r.position_title}"} for r in reqs],
        "document_types": [{"value": c, "label": DOC_LABELS.get(c, c)} for c in CAND_DOC_TYPES],
        "document_status": DOC_STATUSES,
        "link_status": LINK_STATUSES,
        "yes_no": ["true", "false"],
    }


class FilterOptionsView(RecruitmentAuthMixin, APIView):
    """GET /api/recruitment/desk/filter-options/"""

    def get(self, request):
        return ok("FILTER_OPTIONS", "Dropdown values.", build_options(self.company()))


# ============================================================
# 1. INTERVIEWS
# ============================================================
class InterviewDeskViewSet(DeskBase):
    code = "INTERVIEW_DESK"
    export_name = "interviews"
    filter_keys = ("department", "role", "interviewer", "outcome", "status", "feedback_status", "round_name",
                   "candidate", "date_from", "date_to")
    search_fields = ("interview_id", "round_name", "notes", "candidate__name", "candidate__email",
                     "candidate__candidate_id", "candidate__phone_number", "interviewer__name",
                     "interviewer__employee_code")
    orderings = {"scheduled_at": "scheduled_at", "interview_id": "interview_id", "round_name": "round_name",
                 "candidate": "candidate__name", "interviewer": "interviewer__name", "status": "status",
                 "outcome": "outcome", "department": "dept", "role": "role"}
    default_order = "-scheduled_at"
    export_headers = ["Interview ID", "Candidate ID", "Candidate", "Email", "Department", "Role", "Round", "Round No",
                      "Interviewer", "Interviewer Code", "Scheduled At", "Duration (min)", "Mode", "Status",
                      "Feedback", "Outcome", "Average Score", "Notes"]
    import_headers = ["candidate_id", "round_name", "scheduled_at", "interviewer_code", "duration_minutes", "mode",
                      "meeting_link", "notes"]
    import_sample = ["CAN-301", "Technical Round 1", "2026-12-01 11:00", "EMP001", 60, "Google Meet",
                     "https://meet.google.com/abc", "Bring laptop"]

    def queryset(self):
        qs = InterviewRound.objects.filter(company=self.company(), is_active=True).select_related(
            "candidate", "interviewer", "feedback", "candidate__recruitment_profile__requisition")
        qs = annotate_candidate(qs, "candidate__")
        return qs.annotate(outcome=Coalesce(_nn("feedback__recommendation"), Value("Pending")))

    def apply_filters(self, qs, request):
        p = request.query_params
        qs = text_filter(qs, request, "department", "dept")
        qs = text_filter(qs, request, "role", "role")
        qs = text_filter(qs, request, "outcome", "outcome")
        qs = text_filter(qs, request, "status", "status")
        qs = text_filter(qs, request, "feedback_status", "feedback_status")
        qs = text_filter(qs, request, "round_name", "round_name")
        if p.get("interviewer"):
            qs = qs.filter(interviewer__employee_code__in=csv_list(p["interviewer"]))
        if p.get("candidate"):
            ref = p["candidate"].strip()
            qs = qs.filter(Q(candidate__candidate_id=ref) | Q(candidate_id=int(ref) if ref.isdigit() else -1))
        if p.get("date_from"):
            qs = qs.filter(scheduled_at__date__gte=to_date(p["date_from"]))
        if p.get("date_to"):
            qs = qs.filter(scheduled_at__date__lte=to_date(p["date_to"]))
        return qs

    def row(self, r, request):
        fb = getattr(r, "feedback", None) if r.feedback_status == "Submitted" else None
        return {"id": r.id, "interview_id": r.interview_id, "candidate": _cand_brief(r.candidate),
                "department": r.dept or None, "role": r.role or None, "round_name": r.round_name,
                "round_no": r.round_no, "interviewer": _person(r.interviewer), "scheduled_at": r.scheduled_at,
                "duration_minutes": r.duration_minutes, "mode": r.mode, "status": r.status,
                "feedback_status": r.feedback_status, "outcome": r.outcome,
                "average_score": fb.average_score if fb else None}

    def export_row(self, r, request):
        fb = getattr(r, "feedback", None) if r.feedback_status == "Submitted" else None
        c, i = r.candidate, r.interviewer
        return [r.interview_id, c.candidate_id, c.name, c.email, r.dept, r.role, r.round_name, r.round_no,
                i.name if i else "", i.employee_code if i else "", r.scheduled_at, r.duration_minutes, r.mode,
                r.status, r.feedback_status, r.outcome, fb.average_score if fb else "", r.notes]

    def list_extra(self, qs, request):
        # outcome split for the filtered set (handy for the screen's summary chips)
        from django.db.models import Count
        counts = {x["outcome"]: x["n"] for x in qs.order_by().values("outcome").annotate(n=Count("id", distinct=True))}
        return {"outcome_summary": {o: counts.get(o, 0) for o in OUTCOMES}}

    def retrieve(self, request, pk=None):
        r = self.queryset().filter(pk=pk if str(pk).isdigit() else -1).first()
        if r is None:
            return err("NOT_FOUND", "Interview not found.", 404)
        c = r.candidate
        fb = InterviewFeedback.objects.filter(interview=r, is_active=True).select_related(
            "interview", "interview__candidate", "interview__interviewer").first()
        prof = get_profile(c, create=False)
        req = prof.requisition if prof and prof.requisition_id else None
        others = InterviewRound.objects.filter(candidate=c, is_active=True).exclude(pk=r.pk).select_related(
            "interviewer", "feedback").order_by("round_no")
        data = self.row(r, request) | {
            "meeting_link": r.meeting_link, "notes": r.notes, "created_at": r.created_at, "created_by": r.created_by,
            "candidate_detail": {**_cand_brief(c), "stage": c.candidate_status,
                                 "stage_label": stage_label(c.candidate_status),
                                 "current_company": getattr(prof, "current_company", None),
                                 "total_experience_years": (float(prof.total_experience_years) if prof and
                                                            prof.total_experience_years is not None else None)},
            "requisition": ({"requisition_id": req.requisition_id, "position_title": req.position_title,
                             "department": req.department.name if req.department_id else req.department_name,
                             "hiring_manager": _person(req.hiring_manager)} if req else None),
            "interviewer_detail": emp_brief(r.interviewer, request),
            "scorecard": serialize_feedback(fb, request) if fb else None,
            "other_rounds": [{"id": o.id, "interview_id": o.interview_id, "round_name": o.round_name,
                              "round_no": o.round_no, "status": o.status, "scheduled_at": o.scheduled_at,
                              "interviewer": _person(o.interviewer),
                              "outcome": (o.feedback.recommendation if o.feedback_status == "Submitted"
                                          and hasattr(o, "feedback") else "Pending")} for o in others]}
        return ok(self.code + "_DETAIL", "Interview - full view.", data)

    @action(detail=False, methods=["post"], url_path="import")
    def import_file(self, request):
        """multipart: file=<csv|xlsx>  [dry_run=true]  -> schedules the rounds. Bad rows are reported, good rows saved."""
        try:
            rows = du.read_upload_rows(request.FILES.get("file"))
        except ValueError as e:
            return err("VALIDATION", str(e))
        company, dry = self.company(), to_bool(request.data.get("dry_run"))
        created, errors = [], []
        with transaction.atomic():
            for n, d in enumerate(rows, start=2):               # row 1 is the header
                try:
                    with transaction.atomic():
                        created.append(self._make_round(company, d))
                except (ValueError, LookupError) as e:
                    errors.append({"row": n, "error": str(e), "data": {k: du.s(v) for k, v in d.items()}})
            if dry:
                transaction.set_rollback(True)
        return ok("INTERVIEW_IMPORT", "Dry run - nothing saved." if dry else "Import finished.",
                  {"dry_run": dry, "total_rows": len(rows), "created": len(created), "failed": len(errors),
                   "interview_ids": created, "errors": errors}, status=200 if (dry or created or not errors) else 400)

    def _make_round(self, company, d):
        ref = du.s(d.get("candidate_id") or d.get("candidate"))
        cand = candidates_qs(company).filter(Q(candidate_id=ref) | Q(pk=int(ref) if ref.isdigit() else -1)).first()
        if cand is None:
            raise LookupError(f"Candidate '{ref}' not found.")
        if (cand.candidate_status or "").upper() in ("REJECTED", "JOINED"):
            raise ValueError("Candidate is Rejected / Joined.")
        name = du.s(d.get("round_name"))
        if not name or not du.s(d.get("scheduled_at")):
            raise ValueError("round_name and scheduled_at are required.")
        when = to_datetime(du.s(d.get("scheduled_at")).replace("/", "-"))
        if when < timezone.now() - timedelta(minutes=5):
            raise ValueError("scheduled_at cannot be in the past.")
        iv = None
        code = du.s(d.get("interviewer_code") or d.get("interviewer"))
        if code:
            iv = EmployeeDT.objects.filter(company=company, employee_code=code).first()
            if iv is None:
                raise LookupError(f"Interviewer '{code}' not found.")
        try:
            minutes = int(float(du.s(d.get("duration_minutes")) or 60))
        except ValueError:
            raise ValueError("duration_minutes must be a number.")
        r = InterviewRound.objects.create(
            company=company, candidate=cand, interviewer=iv, scheduled_at=when, round_name=name[:100],
            interview_id=next_seq(InterviewRound, company, "interview_id", "INT-", width=3, start=801),
            duration_minutes=minutes, round_no=cand.interview_rounds.count() + 1, mode=du.s(d.get("mode")) or None,
            meeting_link=du.s(d.get("meeting_link")) or None, notes=du.s(d.get("notes")) or None,
            created_by=self.actor(), updated_by=self.actor())
        stage = (cand.candidate_status or "").upper()
        if stage in EARLY_STAGES or stage == "FEEDBACK_PENDING":
            set_stage(cand, "SCREENING" if "screen" in name.lower() else "INTERVIEW", self.actor(),
                      f"{name} scheduled ({r.interview_id}) - bulk import")
        return r.interview_id


# ============================================================
# 2. CANDIDATES
# ============================================================
def doc_counts(ids):
    out = {i: {"total": 0, "verified": 0, "pending": 0, "rejected": 0} for i in ids}
    for d in EmployeeDocument.objects.filter(employee_id__in=ids, is_active=True,
                                              document_type__in=CAND_DOC_TYPES).select_related("review"):
        o = out[d.employee_id]
        o["total"] += 1
        o[doc_status(d).lower()] += 1
    return out


def make_link(company, c, actor, days=7, types=None):
    CandidatePortalLink.objects.filter(candidate=c, revoked=False).update(revoked=True)
    return CandidatePortalLink.objects.create(
        company=company, candidate=c, token=secrets.token_urlsafe(32), document_types=types or [],
        expires_at=timezone.now() + timedelta(days=min(max(int(days), 1), 30)), created_by=actor, updated_by=actor)


class CandidateDeskViewSet(DeskBase):
    code = "CANDIDATE_DESK"
    export_name = "candidates"
    filter_keys = ("stage", "department", "role", "requisition", "recruiter", "source", "priority", "registered",
                   "submitted", "docs", "applied_from", "applied_to")
    search_fields = ("name", "email", "phone_number", "candidate_id", "applied_position", "candidate_source",
                     "recruitment_profile__current_company")
    orderings = {"name": "name", "stage": "candidate_status", "created": "id", "department": "dept", "role": "role",
                 "experience": "recruitment_profile__total_experience_years"}
    export_headers = ["Candidate ID", "Name", "Email", "Phone", "Department", "Role", "Requisition", "Stage", "Source",
                      "Recruiter", "Current Company", "Experience (yrs)", "Notice (days)", "Priority",
                      "Registered On Portal", "Details Submitted", "Docs Total", "Docs Verified", "Docs Pending",
                      "Docs Rejected", "Created"]
    import_headers = ["name", "email", "phone_number", "applied_position", "requisition", "source", "current_company",
                      "total_experience_years", "notice_period_days", "priority", "recruiter_code"]
    import_sample = ["Anita Rao", "anita@example.com", "9876543210", "Django Developer", "REQ-2026-001", "LinkedIn",
                     "Acme Pvt Ltd", 3.5, 30, "Medium", "EMP001"]

    def queryset(self):
        qs = candidates_qs(self.company()).select_related(
            "recruitment_profile", "recruitment_profile__requisition", "recruitment_profile__recruiter")
        return annotate_candidate(qs).annotate(
            has_account=Exists(CandidatePortalAccount.objects.filter(candidate=OuterRef("pk"))))

    def apply_filters(self, qs, request):
        p = request.query_params
        if p.get("stage"):
            qs = qs.filter(candidate_status__in=[x.upper() for x in csv_list(p["stage"])])
        qs = text_filter(qs, request, "department", "dept")
        qs = text_filter(qs, request, "role", "role")
        qs = text_filter(qs, request, "source", "candidate_source")
        qs = text_filter(qs, request, "priority", "recruitment_profile__priority")
        if p.get("requisition"):
            qs = qs.filter(recruitment_profile__requisition__requisition_id__in=csv_list(p["requisition"]))
        if p.get("recruiter"):
            qs = qs.filter(recruitment_profile__recruiter__employee_code__in=csv_list(p["recruiter"]))
        if p.get("registered") not in (None, ""):
            qs = qs.filter(has_account=to_bool(p["registered"]))
        if p.get("submitted") not in (None, ""):
            qs = qs.filter(application_submitted=to_bool(p["submitted"]))
        if p.get("docs"):                                     # pending | verified | rejected | none
            if p["docs"].lower() == "none":
                qs = qs.exclude(Exists(EmployeeDocument.objects.filter(employee=OuterRef("pk"), is_active=True)))
            else:
                qs = qs.filter(Exists(EmployeeDocument.objects.filter(
                    doc_status_q(p["docs"]), employee=OuterRef("pk"), is_active=True)))
        if p.get("applied_from"):
            qs = qs.filter(Q(application_submitted_at__date__gte=to_date(p["applied_from"])))
        if p.get("applied_to"):
            qs = qs.filter(Q(application_submitted_at__date__lte=to_date(p["applied_to"])))
        return qs

    def list_extra(self, qs, request):
        from django.db.models import Count
        counts = {x["candidate_status"]: x["n"] for x in qs.order_by().values("candidate_status")
                  .annotate(n=Count("id", distinct=True))}
        return {"stage_summary": {c: counts.get(c, 0) for c, _ in STAGES}}

    def row(self, c, request, dc=None):
        prof = getattr(c, "recruitment_profile", None) if hasattr(c, "recruitment_profile") else None
        dc = dc or doc_counts([c.id])[c.id]
        stage = (c.candidate_status or "APPLIED").upper()
        return {"id": c.id, "candidate_id": c.candidate_id, "name": c.name, "email": c.email,
                "phone_number": c.phone_number, "department": c.dept or None, "role": c.role or None,
                "requisition": prof.requisition.requisition_id if prof and prof.requisition_id else None,
                "stage": stage, "stage_label": stage_label(stage), "source": c.candidate_source,
                "recruiter": _person(prof.recruiter) if prof and prof.recruiter_id else None,
                "current_company": prof.current_company if prof else None,
                "total_experience_years": (float(prof.total_experience_years) if prof and
                                           prof.total_experience_years is not None else None),
                "notice_period_days": prof.notice_period_days if prof else None,
                "priority": prof.priority if prof else None, "registered": c.has_account,
                "details_submitted": bool(c.application_submitted), "documents": dc}

    def list(self, request):
        try:
            qs = self.filtered(request)
        except ValueError as e:
            return _bad_date(e)
        items, page = du.paginate(request, qs)
        items = list(items)
        dcs = doc_counts([c.id for c in items])
        return ok(self.code + "_LIST", "Candidates.", [self.row(c, request, dcs[c.id]) for c in items],
                  pagination=page, filters_applied=du.applied_filters(request, self.filter_keys + ("search", "ordering")),
                  **self.list_extra(qs, request))

    def export_row(self, c, request, dc=None):
        r = self.row(c, request, dc)
        rec = r["recruiter"]["name"] if r["recruiter"] else ""
        d = r["documents"]
        return [c.candidate_id, c.name, c.email, c.phone_number, r["department"], r["role"], r["requisition"],
                r["stage_label"], c.candidate_source, rec, r["current_company"], r["total_experience_years"],
                r["notice_period_days"], r["priority"], r["registered"], r["details_submitted"], d["total"],
                d["verified"], d["pending"], d["rejected"], c.created_at if hasattr(c, "created_at") else ""]

    @action(detail=False, methods=["get"])
    def export(self, request):
        try:
            qs = self.filtered(request)
        except ValueError as e:
            return _bad_date(e)
        items = list(qs[:20000])
        dcs = doc_counts([c.id for c in items])
        rows = [self.export_row(c, request, dcs[c.id]) for c in items]
        return du.export_response(request, self.export_name, self.export_headers, rows)

    def retrieve(self, request, pk=None):
        c = self.queryset().filter(pk=pk if str(pk).isdigit() else -1).first()
        if c is None:
            return err("NOT_FOUND", "Candidate not found.", 404)
        link = CandidatePortalLink.objects.filter(candidate=c, is_active=True).order_by("-id").first()
        acc = CandidatePortalAccount.objects.filter(candidate=c).first()
        data = serialize_candidate(c, request, detail=True) | {
            "department": c.dept or None, "role": c.role or None,
            "education": [{"id": e.id, **{k: getattr(e, k, None) for k in (
                "highest_qualification", "degree_course", "specialization_branch", "university_board",
                "college_institute", "passing_year", "percentage_cgpa", "key_certifications")}}
                for e in CandidateEducation.objects.filter(employee=c, is_active=True)],
            "experience": [{"id": x.id, **{k: getattr(x, k, None) for k in (
                "company_name", "designation", "department", "start_date", "end_date", "reason_for_leaving",
                "reporting_manager", "core_responsibilities")}}
                for x in CandidateExperience.objects.filter(employee=c, is_active=True)],
            "documents": [serialize_doc(d, request) for d in EmployeeDocument.objects.filter(
                employee=c, is_active=True).select_related("review", "employee").order_by("document_type")],
            "documents_summary": docs_summary(c),
            "interviews": [{"id": r.id, "interview_id": r.interview_id, "round_name": r.round_name,
                            "status": r.status, "scheduled_at": r.scheduled_at, "interviewer": _person(r.interviewer),
                            "outcome": r.feedback.recommendation if r.feedback_status == "Submitted"
                            and hasattr(r, "feedback") else "Pending"}
                           for r in InterviewRound.objects.filter(candidate=c, is_active=True)
                           .select_related("interviewer", "feedback").order_by("round_no")],
            "portal": {"registered": acc is not None, "registered_at": acc.registered_at if acc else None,
                       "last_login_at": acc.last_login_at if acc else None,
                       "link": ser_link(link, request) if link else None,
                       "missing": missing_items(c, link) if link else None}}
        return ok(self.code + "_DETAIL", "Candidate - full view.", data)

    # ---------- import ----------
    @action(detail=False, methods=["post"], url_path="import")
    def import_file(self, request):
        """multipart: file=<csv|xlsx>  [dry_run=true] [create_links=true] [link_days=7]
        Existing e-mail -> that row is reported as duplicate (nothing overwritten)."""
        try:
            rows = du.read_upload_rows(request.FILES.get("file"))
        except ValueError as e:
            return err("VALIDATION", str(e))
        company, actor = self.company(), self.actor()
        dry, links = to_bool(request.data.get("dry_run")), to_bool(request.data.get("create_links"))
        created, errors = [], []
        with transaction.atomic():
            for n, d in enumerate(rows, start=2):
                try:
                    with transaction.atomic():
                        c = self._make_candidate(company, actor, d)
                        item = {"id": c.id, "candidate_id": c.candidate_id, "name": c.name, "email": c.email}
                        if links:
                            lk = make_link(company, c, actor, request.data.get("link_days") or 7)
                            item["frontend_url"] = f"{_base_urls()}/candidate-portal/{lk.token}"
                            item["register_api"] = f"/api/recruitment/hiring-plus/public/candidate/{lk.token}/register/"
                        created.append(item)
                except (ValueError, LookupError) as e:
                    errors.append({"row": n, "error": str(e), "data": {k: du.s(v) for k, v in d.items()}})
            if dry:
                transaction.set_rollback(True)
        return ok("CANDIDATE_IMPORT", "Dry run - nothing saved." if dry else "Import finished.",
                  {"dry_run": dry, "total_rows": len(rows), "created": len(created), "failed": len(errors),
                   "candidates": created, "errors": errors}, status=200 if (dry or created or not errors) else 400)

    def _make_candidate(self, company, actor, d):
        from .views_portal import CandidatePortalLinkViewSet
        name, email = du.s(d.get("name")), du.s(d.get("email")).lower()
        if not name or not email:
            raise ValueError("name and email are required.")
        try:
            validate_email(email)
        except ValidationError:
            raise ValueError("Email is not valid.")
        if EmployeeDT.objects.filter(company=company, email__iexact=email).exists():
            raise LookupError("Duplicate - a candidate/employee with this email already exists.")
        helper = CandidatePortalLinkViewSet()
        helper.request = self.request
        c = helper._new_candidate({"name": name, "email": email, "phone_number": du.s(d.get("phone_number") or d.get("phone")),
                                   "applied_position": du.s(d.get("applied_position")) or None,
                                   "requisition": du.s(d.get("requisition")) or None,
                                   "source": du.s(d.get("source")) or "Bulk import"})
        prof = get_profile(c, company)
        if du.s(d.get("current_company")):
            prof.current_company = du.s(d.get("current_company"))[:200]
        if du.s(d.get("total_experience_years")):
            prof.total_experience_years = to_decimal(du.s(d.get("total_experience_years")))
        if du.s(d.get("notice_period_days")):
            try:
                prof.notice_period_days = int(float(du.s(d.get("notice_period_days"))))
            except ValueError:
                raise ValueError("notice_period_days must be a number.")
        pr = du.s(d.get("priority")).title()
        if pr:
            if pr not in dict(JobRequisition.PRIORITY):
                raise ValueError("priority must be Low / Medium / High / Urgent.")
            prof.priority = pr
        rc = du.s(d.get("recruiter_code") or d.get("recruiter"))
        if rc:
            rec = EmployeeDT.objects.filter(company=company, employee_code=rc).first()
            if rec is None:
                raise LookupError(f"Recruiter '{rc}' not found.")
            prof.recruiter = rec
        prof.updated_by = actor
        prof.save()
        return c


# ============================================================
# 3. DOCUMENTS (verification desk with search + filter)
# ============================================================
class DocumentDeskViewSet(DeskBase):
    code = "DOCUMENT_DESK"
    export_name = "candidate_documents"
    filter_keys = ("candidate", "document_type", "status", "stage", "department", "role", "uploaded_by",
                   "uploaded_from", "uploaded_to")
    search_fields = ("document_name", "remarks", "employee__name", "employee__email", "employee__candidate_id",
                     "employee__phone_number")
    orderings = {"uploaded_at": "uploaded_at", "candidate": "employee__name", "type": "document_type",
                 "department": "dept", "role": "role"}
    default_order = "-uploaded_at"
    export_headers = ["Doc ID", "Candidate ID", "Candidate", "Email", "Department", "Role", "Stage", "Document Type",
                      "Document Name", "Status", "Remarks", "Verified By", "Verified At", "Uploaded At", "Uploaded By",
                      "File URL"]
    import_headers = ["id", "status", "remarks"]
    import_sample = [101, "Verified", ""]

    def queryset(self):
        company = self.company()
        qs = EmployeeDocument.objects.filter(company=company, is_active=True,
                                             employee__in=candidates_qs(company)).select_related(
            "employee", "review", "employee__recruitment_profile__requisition")
        return annotate_candidate(qs, "employee__")

    def apply_filters(self, qs, request):
        p = request.query_params
        if p.get("candidate"):
            ref = p["candidate"].strip()
            qs = qs.filter(Q(employee__candidate_id=ref) | Q(employee_id=int(ref) if ref.isdigit() else -1))
        if p.get("document_type"):
            qs = qs.filter(document_type__in=[x.upper() for x in csv_list(p["document_type"])])
        if p.get("status"):
            q = Q()
            for st in csv_list(p["status"]):
                q |= doc_status_q(st)
            qs = qs.filter(q)
        if p.get("stage"):
            qs = qs.filter(employee__candidate_status__in=[x.upper() for x in csv_list(p["stage"])])
        qs = text_filter(qs, request, "department", "dept")
        qs = text_filter(qs, request, "role", "role")
        ub = (p.get("uploaded_by") or "").lower()
        if ub == "candidate":
            qs = qs.filter(created_by__startswith="candidate-link")
        elif ub == "hr":
            qs = qs.exclude(created_by__startswith="candidate-link")
        if p.get("uploaded_from"):
            qs = qs.filter(uploaded_at__date__gte=to_date(p["uploaded_from"]))
        if p.get("uploaded_to"):
            qs = qs.filter(uploaded_at__date__lte=to_date(p["uploaded_to"]))
        return qs

    def row(self, d, request):
        return serialize_doc(d, request) | {
            "department": d.dept or None, "role": d.role or None, "stage": d.employee.candidate_status,
            "uploaded_by": "candidate" if (d.created_by or "").startswith("candidate-link") else "hr"}

    def export_row(self, d, request):
        r = self.row(d, request)
        c = d.employee
        return [d.id, c.candidate_id, c.name, c.email, r["department"], r["role"], r["stage"], r["type_label"],
                d.document_name, r["status"], r["remarks"], d.verified_by, d.verified_at, d.uploaded_at,
                r["uploaded_by"], r["file_url"]]

    def list_extra(self, qs, request):
        base = self.apply_filters(self.queryset(), request)
        base = self.search(base, request)
        return {"status_summary": {st: base.filter(doc_status_q(st)).distinct().count() for st in DOC_STATUSES}}

    def retrieve(self, request, pk=None):
        d = self.queryset().filter(pk=pk if str(pk).isdigit() else -1).first()
        if d is None:
            return err("NOT_FOUND", "Document not found.", 404)
        sibling = EmployeeDocument.objects.filter(employee=d.employee, is_active=True).exclude(pk=d.pk).select_related(
            "review", "employee").order_by("document_type")
        older = EmployeeDocument.objects.filter(employee=d.employee, document_type=d.document_type,
                                                is_active=False).order_by("-id")[:10]
        return ok(self.code + "_DETAIL", "Document - full view.", self.row(d, request) | {
            "review": ({"status": d.review.status, "remarks": d.review.remarks, "reviewed_by": d.review.reviewed_by,
                        "reviewed_at": d.review.reviewed_at} if hasattr(d, "review") else None),
            "candidate_detail": {**_cand_brief(d.employee), "stage": d.employee.candidate_status},
            "candidate_documents_summary": docs_summary(d.employee),
            "other_documents": [serialize_doc(x, request) for x in sibling],
            "replaced_versions": [{"id": x.id, "uploaded_at": x.uploaded_at, "remarks": x.remarks} for x in older]})

    # ---------- bulk verify / reject ----------
    def _apply_review(self, doc, status, reason):
        if status == "Rejected" and not reason:
            raise ValueError("reason is required to reject a document.")
        rev, _ = DocumentReview.objects.get_or_create(document=doc)
        rev.status, rev.remarks, rev.reviewed_by, rev.reviewed_at = status, reason or None, self.actor(), timezone.now()
        rev.save()
        doc.is_verified = status == "Verified"
        doc.verified_by = self.actor() if status == "Verified" else None
        doc.verified_at = timezone.now() if status == "Verified" else None
        doc.remarks = reason if reason else doc.remarks
        doc.updated_by = self.actor()
        doc.save()
        advance_after_docs(doc.employee, self.actor())

    @action(detail=False, methods=["post"], url_path="bulk-review")
    def bulk_review(self, request):
        """{"ids":[1,2,3], "action":"verify"|"reject"|"pending", "reason":"..."}"""
        ids = request.data.get("ids")
        act = (request.data.get("action") or "").lower()
        if not isinstance(ids, list) or not ids or len(ids) > 200:
            return err("VALIDATION", "ids must be a list of 1 to 200 document ids.")
        if act not in ("verify", "reject", "pending"):
            return err("VALIDATION", "action must be verify, reject or pending.")
        status = {"verify": "Verified", "reject": "Rejected", "pending": "Pending"}[act]
        docs = {d.id: d for d in self.queryset().filter(pk__in=[i for i in ids if str(i).isdigit()])}
        done, failed = [], []
        for i in ids:
            d = docs.get(int(i)) if str(i).isdigit() else None
            if d is None:
                failed.append({"id": i, "error": "Document not found."})
                continue
            try:
                with transaction.atomic():
                    self._apply_review(d, status, du.s(request.data.get("reason")))
                done.append(d.id)
            except ValueError as e:
                failed.append({"id": i, "error": str(e)})
        return ok("DOCUMENT_BULK_REVIEW", f"{len(done)} document(s) marked {status}.",
                  {"status": status, "updated": done, "failed": failed}, status=200 if done or not failed else 400)

    @action(detail=False, methods=["post"], url_path="import")
    def import_file(self, request):
        """Import a review sheet: columns id, status (Verified/Rejected/Pending), remarks. Use export -> edit -> import."""
        try:
            rows = du.read_upload_rows(request.FILES.get("file"))
        except ValueError as e:
            return err("VALIDATION", str(e))
        dry = to_bool(request.data.get("dry_run"))
        qs = self.queryset()
        done, errors = [], []
        with transaction.atomic():
            for n, r in enumerate(rows, start=2):
                try:
                    did, st = du.s(r.get("id") or r.get("doc_id")), du.s(r.get("status")).title()
                    if not did.isdigit():
                        raise ValueError("id is required.")
                    if st not in DOC_STATUSES:
                        raise ValueError("status must be Pending / Verified / Rejected.")
                    d = qs.filter(pk=int(did)).first()
                    if d is None:
                        raise LookupError(f"Document {did} not found.")
                    with transaction.atomic():
                        self._apply_review(d, st, du.s(r.get("remarks")))
                    done.append(d.id)
                except (ValueError, LookupError) as e:
                    errors.append({"row": n, "error": str(e), "data": {k: du.s(v) for k, v in r.items()}})
            if dry:
                transaction.set_rollback(True)
        return ok("DOCUMENT_IMPORT", "Dry run - nothing saved." if dry else "Import finished.",
                  {"dry_run": dry, "total_rows": len(rows), "updated": len(done), "failed": len(errors),
                   "updated_ids": done, "errors": errors}, status=200 if (dry or done or not errors) else 400)

    # ---------- who is still missing documents ----------
    @action(detail=False, methods=["get"])
    def missing(self, request):
        """Candidates who have not uploaded every required document (RESUME + AADHAAR/PAN/EDUCATION/BANK).
        Same search / department / role / stage filters. Paginated. ?file=csv|xlsx via /missing/?export=1"""
        company = self.company()
        qs = annotate_candidate(candidates_qs(company)).exclude(candidate_status__in=["REJECTED", "JOINED"])
        p = request.query_params
        qs = text_filter(qs, request, "department", "dept")
        qs = text_filter(qs, request, "role", "role")
        if p.get("stage"):
            qs = qs.filter(candidate_status__in=[x.upper() for x in csv_list(p["stage"])])
        s = (p.get("search") or "").strip()
        if s:
            qs = qs.filter(Q(name__icontains=s) | Q(email__icontains=s) | Q(candidate_id__icontains=s))
        need = ["RESUME"] + [t for t in REQUIRED_DOC_TYPES]
        have = {}
        for cid, t in EmployeeDocument.objects.filter(company=company, is_active=True,
                                                       document_type__in=need).values_list("employee_id", "document_type"):
            have.setdefault(cid, set()).add(t)
        items = []
        for c in qs.order_by("-id"):
            miss = [t for t in need if t not in have.get(c.id, set())]
            if miss:
                items.append({**_cand_brief(c), "department": c.dept or None, "role": c.role or None,
                              "stage": c.candidate_status, "missing_types": miss,
                              "missing_labels": [DOC_LABELS.get(t, t) for t in miss]})
        if to_bool(p.get("export")):
            return du.export_response(request, "missing_documents",
                                      ["Candidate ID", "Name", "Email", "Department", "Role", "Stage", "Missing"],
                                      [[x["candidate_id"], x["name"], x["email"], x["department"], x["role"],
                                        x["stage"], x["missing_labels"]] for x in items])
        page_items, page = du.paginate(request, items)
        return ok("DOCUMENT_MISSING", "Candidates with missing documents.", list(page_items), pagination=page)


# ============================================================
# 4. ONBOARDING (candidate link tracker)
# ============================================================
def link_status_q(status):
    now = timezone.now()
    live = Q(revoked=False, expires_at__gte=now)
    upd = Q(submitted_at__isnull=False, last_edit_at__gt=F("submitted_at"))
    st = (status or "").strip().lower()
    if st == "revoked":
        return Q(revoked=True)
    if st == "expired":
        return Q(revoked=False, expires_at__lt=now)
    if st == "updated after submit":
        return live & upd
    if st == "submitted":
        return live & Q(submitted_at__isnull=False) & ~upd
    if st == "in progress":
        return live & Q(submitted_at__isnull=True, last_edit_at__isnull=False)
    if st == "opened":
        return live & Q(submitted_at__isnull=True, last_edit_at__isnull=True, first_opened_at__isnull=False)
    if st == "not opened":
        return live & Q(submitted_at__isnull=True, last_edit_at__isnull=True, first_opened_at__isnull=True)
    return Q(pk__in=[])


class OnboardingDeskViewSet(DeskBase):
    code = "ONBOARDING_DESK"
    export_name = "candidate_onboarding"
    filter_keys = ("status", "registered", "department", "role", "stage", "live_only", "candidate")
    search_fields = ("candidate__name", "candidate__email", "candidate__candidate_id", "candidate__phone_number",
                     "sent_to")
    orderings = {"created": "created_at", "last_edit": "last_edit_at", "candidate": "candidate__name",
                 "expires": "expires_at", "department": "dept", "role": "role"}
    default_order = "-created_at"
    export_headers = ["Candidate ID", "Candidate", "Email", "Department", "Role", "Stage", "Link Status", "Registered",
                      "Registered At", "Progress %", "Missing Items", "First Opened", "Last Edit", "Submitted At",
                      "Submit Count", "Link Expires"]

    def queryset(self):
        qs = CandidatePortalLink.objects.filter(company=self.company(), is_active=True).select_related(
            "candidate", "candidate__recruitment_profile__requisition")
        return annotate_candidate(qs, "candidate__").annotate(
            has_account=Exists(CandidatePortalAccount.objects.filter(candidate=OuterRef("candidate_id"))))

    def apply_filters(self, qs, request):
        p = request.query_params
        if p.get("status"):
            q = Q()
            for st in csv_list(p["status"]):
                q |= link_status_q(st)
            qs = qs.filter(q)
        if p.get("registered") not in (None, ""):
            qs = qs.filter(has_account=to_bool(p["registered"]))
        if p.get("live_only") and to_bool(p["live_only"]):
            qs = qs.filter(revoked=False)
        if p.get("stage"):
            qs = qs.filter(candidate__candidate_status__in=[x.upper() for x in csv_list(p["stage"])])
        if p.get("candidate"):
            ref = p["candidate"].strip()
            qs = qs.filter(Q(candidate__candidate_id=ref) | Q(candidate_id=int(ref) if ref.isdigit() else -1))
        qs = text_filter(qs, request, "department", "dept")
        return text_filter(qs, request, "role", "role")

    permission_action_map = {**DeskBase.permission_action_map, "reset_account": "edit"}

    @action(detail=True, methods=["post"], url_path="reset-account")
    def reset_account(self, request, pk=None):
        """Candidate forgot the password: remove the registration so the same link can be used to register again.
        Everything the candidate already filled stays."""
        l = self.queryset().filter(pk=pk if str(pk).isdigit() else -1).first()
        if l is None:
            return err("NOT_FOUND", "Link not found.", 404)
        n, _ = CandidatePortalAccount.objects.filter(candidate=l.candidate).delete()
        return ok("ACCOUNT_RESET", "Registration removed. The candidate can register again." if n
                  else "The candidate had not registered yet.", {"removed": bool(n)})

    def _progress(self, l):
        c = l.candidate
        miss = missing_items(c, l)
        total = 5 + len(dict.fromkeys(["RESUME"] + list(l.document_types or [])))
        return max(0, round(100 * (total - len(miss)) / total)), miss

    def row(self, l, request):
        pct, miss = self._progress(l)
        c = l.candidate
        return ser_link(l, request) | {"candidate_detail": _cand_brief(c), "department": l.dept or None,
                                       "role": l.role or None, "stage": c.candidate_status,
                                       "registered": l.has_account, "progress_percent": pct,
                                       "missing_count": len(miss)}

    def export_row(self, l, request):
        r = self.row(l, request)
        _, miss = self._progress(l)
        c = l.candidate
        acc = CandidatePortalAccount.objects.filter(candidate=c).first()
        return [c.candidate_id, c.name, c.email, r["department"], r["role"], r["stage"], r["status"],
                r["registered"], acc.registered_at if acc else "", r["progress_percent"],
                [m["label"] for m in miss], l.first_opened_at, l.last_edit_at, l.submitted_at, l.submit_count,
                l.expires_at]

    def list_extra(self, qs, request):
        base = self.search(self.apply_filters(self.queryset(), request), request)
        return {"status_summary": {st: base.filter(link_status_q(st)).distinct().count() for st in LINK_STATUSES}}

    def retrieve(self, request, pk=None):
        l = self.queryset().filter(pk=pk if str(pk).isdigit() else -1).first()
        if l is None:
            return err("NOT_FOUND", "Link not found.", 404)
        c = l.candidate
        acc = CandidatePortalAccount.objects.filter(candidate=c).first()
        acts = CandidatePortalActivity.objects.filter(candidate=c, company=self.company())[:50]
        return ok(self.code + "_DETAIL", "Candidate onboarding - full view.", self.row(l, request) | {
            "account": ({"email": acc.email, "registered_at": acc.registered_at, "last_login_at": acc.last_login_at}
                        if acc else None),
            "what_candidate_sees": snapshot(l, c),
            "documents": [serialize_doc(d, request) for d in EmployeeDocument.objects.filter(
                employee=c, is_active=True).select_related("review", "employee")],
            "activity": [{"action": a.action, "detail": a.detail, "ip_address": a.ip_address, "at": a.created_at}
                         for a in acts]})
