"""Bulk candidate import from a CSV file, with optional Job Order pipeline assignment."""
import csv
import io
import re
from decimal import Decimal, InvalidOperation

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


# CSV header -> normalised key (case/space-insensitive)
def _norm(h):
    return re.sub(r"[^a-z0-9]", "", (h or "").lower())


HEADER_MAP = {
    "joborderid": "job_ids",
    "candidatename": "name",
    "contactno": "phone",
    "email": "email",
    "currentemployer": "current_company",
    "designation": "current_role",
    "currentannualctc": "current_ctc",
    "expectedannualctc": "expected_ctc",
    # legacy header aliases (older templates)
    "currentannual": "current_ctc",
    "expectedannual": "expected_ctc",
    "annualsalary": "current_ctc",
    "desiredpay": "expected_ctc",
    "currentlocation": "current_location",
    "qualification": "highest_qualification",
    "workexp": "work_exp",
    "noticeperiod": "notice_period",
}

REQUIRED = ["name", "phone"]


def _num(val):
    """Extract the first number from strings like '2.94 LPA', '4 Years'."""
    if val is None:
        return None
    m = re.search(r"\d+(?:\.\d+)?", str(val))
    return m.group() if m else None


def _decimal(val):
    n = _num(val)
    try:
        return Decimal(n) if n is not None else None
    except (InvalidOperation, TypeError):
        return None


def _notice_days(val):
    """'Immediate' -> 0, '1 month' -> 30, '15 days' -> 15, else first number."""
    if not val:
        return None
    s = str(val).lower()
    if "immediate" in s:
        return 0
    n = _num(s)
    if n is None:
        return None
    n = int(float(n))
    if "month" in s:
        return n * 30
    return n


def _rows_from_xlsx(file_obj):
    """Yield dict rows from the first sheet of an .xlsx file (header row 1)."""
    try:
        from openpyxl import load_workbook
    except ImportError:
        # TEMP: openpyxl is not installed in the offline venv — fall back to a
        # minimal stdlib reader. Remove after `pip install openpyxl`.
        yield from _rows_from_xlsx_stdlib(file_obj)
        return
    wb = load_workbook(file_obj, read_only=True, data_only=True)
    ws = wb.active
    it = ws.iter_rows(values_only=True)
    header = [str(h).strip() if h is not None else "" for h in next(it, [])]
    for values in it:
        if values is None or all(v in (None, "") for v in values):
            continue
        yield {header[i]: ("" if v is None else str(v).strip())
               for i, v in enumerate(values) if i < len(header)}
    wb.close()


def _rows_from_xlsx_stdlib(file_obj):
    """Minimal .xlsx reader (zipfile + XML): first worksheet, inline/shared
    strings and plain values. Enough for tabular import templates."""
    import re as _re
    import zipfile
    from xml.etree import ElementTree as ET

    NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
    with zipfile.ZipFile(file_obj) as zf:
        shared = []
        if "xl/sharedStrings.xml" in zf.namelist():
            root = ET.fromstring(zf.read("xl/sharedStrings.xml"))
            for si in root.findall("m:si", NS):
                shared.append("".join(t.text or "" for t in si.iter(f"{{{NS['m']}}}t")))
        sheet_name = next(
            (n for n in zf.namelist() if _re.match(r"xl/worksheets/sheet1\.xml$", n)),
            next((n for n in zf.namelist() if n.startswith("xl/worksheets/")), None),
        )
        if not sheet_name:
            return
        root = ET.fromstring(zf.read(sheet_name))

        def cell_value(c):
            v = c.find("m:v", NS)
            if c.get("t") == "s" and v is not None:
                return shared[int(v.text)] if v.text and int(v.text) < len(shared) else ""
            if c.get("t") == "inlineStr":
                return "".join(t.text or "" for t in c.iter(f"{{{NS['m']}}}t"))
            raw = v.text if v is not None else ""
            # Excel numerics arrive as "20.0" strings — leave normalisation to the caller
            return raw or ""

        def col_index(ref):
            letters = _re.match(r"[A-Z]+", ref or "")
            if not letters:
                return None
            n = 0
            for ch in letters.group():
                n = n * 26 + (ord(ch) - 64)
            return n - 1

        rows_iter = root.find("m:sheetData", NS)
        header = None
        for row in (rows_iter.findall("m:row", NS) if rows_iter is not None else []):
            values = {}
            next_col = 0  # cells may omit the r= reference — position is sequential
            for c in row.findall("m:c", NS):
                idx = col_index(c.get("r"))
                if idx is None:
                    idx = next_col
                values[idx] = str(cell_value(c)).strip()
                next_col = idx + 1
            if not values:
                continue
            width = max(values) + 1
            cells = [values.get(i, "") for i in range(width)]
            if header is None:
                header = cells
                continue
            if all(v == "" for v in cells):
                continue
            yield {header[i]: cells[i] for i in range(min(len(header), len(cells))) if header[i]}


def _fill_empty_fields(candidate, row):
    """Upsert helper: copy row values onto the existing candidate, but ONLY
    into fields that are currently empty (None / blank). Existing non-empty
    values are never overwritten. Returns the list of field names filled."""
    new_vals = {
        "email": (row.get("email") or "").strip() or None,
        "phone_number": (row.get("phone") or "").strip() or None,
        "current_company": row.get("current_company") or None,
        "current_role": row.get("current_role") or None,
        "current_ctc": _decimal(row.get("current_ctc")),
        "expected_ctc": _decimal(row.get("expected_ctc")),
        "current_location": row.get("current_location") or None,
        "city": row.get("current_location") or None,
        "highest_qualification": row.get("highest_qualification") or None,
        "total_experience": _decimal(row.get("work_exp")),
        "notice_period": _notice_days(row.get("notice_period")),
    }
    filled = []
    for field, val in new_vals.items():
        if val in (None, ""):
            continue
        cur = getattr(candidate, field)
        if cur is None or (isinstance(cur, str) and cur.strip() == ""):
            setattr(candidate, field, val)
            filled.append(field)
    # The fresher flag follows newly-learned experience.
    if "total_experience" in filled and candidate.total_experience and candidate.total_experience > 0:
        candidate.fresher = False
    if filled:
        candidate.save()
    return filled


def import_candidates_csv(file_obj, created_by, filename=""):
    """Parse a CSV or XLSX and create candidates. Existing candidates
    (matched by email, then phone) are upserted: only their empty fields are
    filled from the row. Returns a summary dict."""
    if (filename or getattr(file_obj, "name", "")).lower().endswith(".xlsx"):
        reader = _rows_from_xlsx(file_obj)
    else:
        raw = file_obj.read()
        text = raw.decode("utf-8-sig", errors="ignore") if isinstance(raw, (bytes, bytearray)) else raw
        # restkey captures overflow cells: an UNQUOTED "1,2" in the last column would
        # otherwise split into an extra field and be silently dropped.
        reader = csv.DictReader(io.StringIO(text), restkey="_overflow")

    created, updated, skipped, errors = 0, 0, 0, []
    pipeline_added = 0
    candidate_ids = []   # every candidate covered by THIS file (created or matched duplicate)
    first_stage = PipelineStage.objects.filter(is_active=True).order_by("sort_order", "id").first()

    def _digits(p):
        return re.sub(r"\D", "", p or "")

    # Lookup maps for duplicate detection: normalised email / phone digits ->
    # candidate id. Kept up to date as rows create or fill records, so in-file
    # duplicates are matched too.
    email_map = {
        e.lower(): cid for cid, e in Candidate.objects.exclude(email__isnull=True)
        .exclude(email="").values_list("id", "email")
    }
    phone_map = {}
    for cid, p in Candidate.objects.values_list("id", "phone_number"):
        d = _digits(p)
        if d:
            phone_map[d] = cid

    def _remember(candidate):
        e = (candidate.email or "").lower().strip()
        if e:
            email_map[e] = candidate.id
        d = _digits(candidate.phone_number)
        if d:
            phone_map[d] = candidate.id

    for i, raw_row in enumerate(reader, start=2):   # row 1 = header
        overflow = raw_row.get("_overflow") or []
        row = { HEADER_MAP.get(_norm(k)): (v.strip() if isinstance(v, str) else v)
                for k, v in raw_row.items() if HEADER_MAP.get(_norm(k)) }
        # Fold any overflow cells (from an unquoted "1,2") back into job_ids.
        if overflow:
            extra = ",".join(str(x).strip() for x in overflow if str(x).strip())
            if extra:
                row["job_ids"] = (row.get("job_ids") or "") + "," + extra
        if not row.get("name") or not row.get("phone"):
            skipped += 1
            errors.append(f"Row {i}: missing name or contact number — skipped")
            continue

        email_norm = (row.get("email") or "").lower().strip()
        phone_norm = _digits(row.get("phone"))

        def _assign_jobs(candidate, row_no, row_name):
            """Assign the candidate to each Job Order ID in the row. Returns entries added."""
            nonlocal pipeline_added
            job_ids_raw = str(row.get("job_ids") or "")
            # Excel numeric cells arrive as "20.0" — normalise to "20".
            tokens = [re.sub(r"\.0+$", "", t.strip()) for t in re.split(r"[,\s]+", job_ids_raw)]
            wanted = [t for t in tokens if t.isdigit()]
            added, not_found = 0, []
            for jid in wanted:
                job = JobDescription.objects.filter(id=int(jid)).first()
                if not job:
                    not_found.append(jid)
                    continue
                if not JobApplication.objects.filter(candidate=candidate, job=job).exists():
                    JobApplication.objects.create(
                        candidate=candidate, job=job, stage=first_stage, created_by=created_by
                    )
                    pipeline_added += 1
                    added += 1
            if not_found:
                errors.append(f"Row {row_no} ({row_name}): Job Order ID(s) {', '.join(not_found)} not found — not assigned")
            if wanted and first_stage is None:
                errors.append(f"Row {row_no}: no active pipeline stage exists — cannot assign to jobs")
            return added

        # Duplicate detection — email is the primary unique key, phone the
        # secondary. The maps also catch in-file duplicates, since created
        # rows are added to them as we go.
        existing = None
        reason = None
        if email_norm and email_norm in email_map:
            existing = Candidate.objects.filter(id=email_map[email_norm]).first()
            reason = f"email '{row.get('email')}'"
        if existing is None and phone_norm and phone_norm in phone_map:
            existing = Candidate.objects.filter(id=phone_map[phone_norm]).first()
            reason = f"contact number '{row.get('phone')}'"
        if existing is not None:
            # Upsert: never create a duplicate — fill only the fields that are
            # currently empty on the existing record, then assign job orders.
            try:
                filled = _fill_empty_fields(existing, row)
            except Exception as e:
                skipped += 1
                errors.append(f"Row {i} ({row.get('name')}): could not update existing candidate — {e}")
                continue
            updated += 1
            _remember(existing)   # it may have just gained an email/phone
            added = _assign_jobs(existing, i, row.get("name"))
            notes = []
            if filled:
                notes.append(f"filled empty field(s): {', '.join(filled)}")
            if added:
                notes.append(f"assigned to {added} job order(s)")
            detail = f" — {'; '.join(notes)}" if notes else " — no empty fields to fill"
            errors.append(f"Row {i} ({row.get('name')}): {reason} already exists — updated{detail}")
            if filled:
                from apps.audit_logs.services import log_activity
                log_activity(created_by, "CANDIDATE_UPLOADED",
                             f"Updated candidate via bulk import (filled empty fields): {existing.first_name} {existing.last_name}")
            candidate_ids.append(existing.id)   # part of this file — include for mail/sms
            continue

        parts = row["name"].split(" ", 1)
        first = parts[0]
        last = parts[1] if len(parts) > 1 else "-"

        work_exp = _num(row.get("work_exp"))
        total_exp = Decimal(work_exp) if work_exp else None

        try:
            candidate = Candidate.objects.create(
                first_name=first,
                last_name=last,
                email=row.get("email") or None,
                phone_number=row.get("phone"),
                current_company=row.get("current_company") or None,
                current_role=row.get("current_role") or None,
                current_ctc=_decimal(row.get("current_ctc")),
                expected_ctc=_decimal(row.get("expected_ctc")),
                current_location=row.get("current_location") or None,
                city=row.get("current_location") or None,
                highest_qualification=row.get("highest_qualification") or None,
                total_experience=total_exp,
                fresher=not bool(total_exp and total_exp > 0),
                notice_period=_notice_days(row.get("notice_period")),
                status=Candidate.StatusChoices.PROFILE_COMPLETED,
                source=Candidate.SourceChoices.UPLOAD,   # SRC-007: bulk import → CV/list Upload
                created_by=created_by,
            )
        except Exception as e:
            skipped += 1
            errors.append(f"Row {i} ({row.get('name')}): {e}")
            continue

        created += 1
        from apps.audit_logs.services import log_activity
        log_activity(created_by, "CANDIDATE_UPLOADED", f"Uploaded candidate via bulk import: {candidate.first_name} {candidate.last_name}")
        candidate_ids.append(candidate.id)
        _remember(candidate)

        # Optional pipeline assignment — "1074, 1072" -> each existing JobDescription
        _assign_jobs(candidate, i, row.get("name"))

    return {
        "created": created,
        "updated": updated,
        "skipped": skipped,
        "pipeline_entries": pipeline_added,
        "errors": errors[:20],   # cap the returned error list
        "candidate_ids": candidate_ids,   # candidates covered by this file (for mail/sms)
    }