# ============================================================
# ADDITIVE: shared helpers for the HR / Admin "desk" APIs
# pagination - CSV / Excel export - CSV / Excel import
# ============================================================
import csv
import io
import math
from datetime import date, datetime

from django.http import HttpResponse
from django.utils import timezone

MAX_IMPORT_BYTES = 5 * 1024 * 1024
MAX_IMPORT_ROWS = 2000
DEFAULT_PAGE_SIZE = 20
MAX_PAGE_SIZE = 100


def paginate(request, qs):
    """-> (page_items, pagination dict). Works for QuerySet and list. Bad numbers fall back to defaults."""
    p = request.query_params

    def _int(key, default, lo, hi):
        try:
            return min(max(int(p.get(key)), lo), hi)
        except (TypeError, ValueError):
            return default

    size = _int("page_size", DEFAULT_PAGE_SIZE, 1, MAX_PAGE_SIZE)
    total = qs.count() if hasattr(qs, "count") and not isinstance(qs, list) else len(qs)
    pages = max(1, math.ceil(total / size))
    page = _int("page", 1, 1, pages)
    start = (page - 1) * size
    items = qs[start:start + size]
    return items, {"page": page, "page_size": size, "total": total, "total_pages": pages,
                   "has_next": page < pages, "has_prev": page > 1}


def pick_ordering(request, allowed, default):
    """?ordering=-created_at  (only whitelisted fields)."""
    raw = (request.query_params.get("ordering") or "").strip()
    name = raw.lstrip("-")
    if name in allowed:
        return ("-" if raw.startswith("-") else "") + allowed[name]
    return default


def applied_filters(request, keys):
    return {k: request.query_params.get(k) for k in keys if request.query_params.get(k) not in (None, "")}


# ---------------------------------------------------------------
# export
# ---------------------------------------------------------------
def _cell(v):
    if v is None:
        return ""
    if isinstance(v, datetime):
        if timezone.is_aware(v):
            v = timezone.localtime(v).replace(tzinfo=None)
        return v.strftime("%Y-%m-%d %H:%M")
    if isinstance(v, date):
        return v.isoformat()
    if isinstance(v, bool):
        return "Yes" if v else "No"
    if isinstance(v, (list, tuple, set)):
        v = ", ".join(str(i) for i in v)
    s = v if isinstance(v, (int, float)) else str(v)
    # spreadsheet formula injection guard
    if isinstance(s, str) and s[:1] in ("=", "+", "-", "@", "\t", "\r"):
        s = "'" + s
    return s


def export_response(request, filename, headers, rows):
    """rows: list of lists in the same order as headers. ?file=csv (default) or ?file=xlsx.
    (The query key is `file` because DRF reserves `format` for content negotiation.)"""
    kind = (request.query_params.get("file") or request.query_params.get("export_format") or "csv").lower()
    stamp = timezone.localtime().strftime("%Y%m%d_%H%M")
    if kind in ("xlsx", "excel", "xls"):
        from openpyxl import Workbook
        from openpyxl.styles import Font
        wb = Workbook()
        ws = wb.active
        ws.title = "Export"
        ws.append(headers)
        for c in ws[1]:
            c.font = Font(bold=True)
        for r in rows:
            ws.append([_cell(v) for v in r])
        for i, h in enumerate(headers, 1):
            width = max([len(str(h))] + [len(str(_cell(r[i - 1]))) for r in rows[:200]]) + 2
            ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = min(width, 50)
        buf = io.BytesIO()
        wb.save(buf)
        resp = HttpResponse(buf.getvalue(),
                            content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        resp["Content-Disposition"] = f'attachment; filename="{filename}_{stamp}.xlsx"'
        return resp
    sio = io.StringIO()
    w = csv.writer(sio)
    w.writerow(headers)
    for r in rows:
        w.writerow([_cell(v) for v in r])
    resp = HttpResponse("\ufeff" + sio.getvalue(), content_type="text/csv; charset=utf-8")   # BOM: Excel opens UTF-8
    resp["Content-Disposition"] = f'attachment; filename="{filename}_{stamp}.csv"'
    return resp


# ---------------------------------------------------------------
# import
# ---------------------------------------------------------------
def _norm_key(k):
    return str(k or "").strip().lower().replace(" ", "_").replace("-", "_")


def read_upload_rows(f):
    """CSV / XLSX upload -> list of dicts (keys normalised: 'Candidate Name' -> 'candidate_name').
    Raises ValueError with a friendly message."""
    if f is None:
        raise ValueError("Send the file in the 'file' form field (multipart).")
    name = (getattr(f, "name", "") or "").lower()
    if f.size <= 0:
        raise ValueError("The file is empty.")
    if f.size > MAX_IMPORT_BYTES:
        raise ValueError("File too large (max 5 MB).")
    raw = f.read()
    if name.endswith(".xlsx"):
        if not raw.startswith(b"PK"):
            raise ValueError("This does not look like a real .xlsx file.")
        from openpyxl import load_workbook
        try:
            wb = load_workbook(io.BytesIO(raw), read_only=True, data_only=True)
        except Exception:
            raise ValueError("Could not read the Excel file.")
        ws = wb.active
        it = ws.iter_rows(values_only=True)
        head = next(it, None)
        if not head:
            raise ValueError("The sheet is empty.")
        keys = [_norm_key(h) for h in head]
        out = []
        for row in it:
            if row is None or all(v in (None, "") for v in row):
                continue
            out.append({keys[i]: ("" if v is None else v) for i, v in enumerate(row) if i < len(keys) and keys[i]})
    elif name.endswith(".csv") or name.endswith(".txt"):
        try:
            text = raw.decode("utf-8-sig")
        except UnicodeDecodeError:
            text = raw.decode("latin-1")
        rd = csv.DictReader(io.StringIO(text))
        if not rd.fieldnames:
            raise ValueError("The file is empty.")
        out = []
        for row in rd:
            row = {_norm_key(k): (v or "").strip() for k, v in row.items() if k}
            if any(row.values()):
                out.append(row)
    else:
        raise ValueError("Only .csv or .xlsx files are accepted.")
    if not out:
        raise ValueError("No data rows found.")
    if len(out) > MAX_IMPORT_ROWS:
        raise ValueError(f"Too many rows (max {MAX_IMPORT_ROWS} per import).")
    return out


def s(v):
    """cell -> clean string"""
    if v is None:
        return ""
    if isinstance(v, float) and v.is_integer():
        v = int(v)
    return str(v).strip()


def template_response(request, filename, headers, sample):
    return export_response(request, filename, headers, [sample])
