# ============================================================
# ADDITIVE: HR MIS - export builders ("Export Employee Master", "Export Recruitment Data")
# Sensitive columns (Aadhaar, PAN, bank, PF/ESI/UAN numbers, passwords, salary) are NEVER exported.
# ============================================================
import csv
import io

from django.db.models import Q
from django.utils import timezone

from Employee.leave_models import ReportingHierarchy
from Recruitment.models import CandidateProfile, Offer

from .common import employees_qs, to_date
from .reports_engine import clean_text

MAX_ROWS = 50000

EMPLOYEE_COLUMNS = ["Employee Code", "Name", "Email", "Phone", "Gender", "Date of Birth", "Designation", "Department",
                    "Employee Type", "Work Location", "Branch", "Date of Joining", "Probation Period", "Lifecycle Status",
                    "Employee Status", "Active", "Reporting Manager", "Notice Period", "Resignation Status",
                    "Resignation Date", "Last Working Day"]
RECRUITMENT_COLUMNS = ["Candidate Code", "Name", "Email", "Phone", "Applied Position", "Requisition", "Source", "Recruiter",
                       "Stage", "Applied On", "Current Company", "Experience (Years)", "Notice Period (Days)", "Offer ID",
                       "Offer Status", "Offered CTC (LPA)", "Target Joining Date", "Rejection Reason"]


def _d(v):
    return str(v) if v else ""


def employee_master(company, f):
    status = (f.get("status") or "active").lower()
    if status not in ("active", "exited", "all"):
        raise ValueError("status must be active, exited or all.")
    qs = employees_qs(company).select_related("department", "people_profile", "people_profile__branch")
    if status == "active":
        qs = qs.filter(is_active=True)
    elif status == "exited":
        qs = qs.filter(is_active=False)
    if f.get("department"):
        qs = qs.filter(Q(department__name__iexact=f["department"]) | Q(Department__iexact=f["department"]))
    if f.get("employee_type"):
        qs = qs.filter(Employee_Type__iexact=f["employee_type"])
    if f.get("joined_from"):
        qs = qs.filter(Date_of_Joining__gte=to_date(f["joined_from"]))
    if f.get("joined_to"):
        qs = qs.filter(Date_of_Joining__lte=to_date(f["joined_to"]))
    qs = qs.order_by("employee_code")[:MAX_ROWS]
    mgr = dict(ReportingHierarchy.objects.filter(company=company, is_active=True).values_list("Employee_ID", "manager_code"))
    names = dict(employees_qs(company).values_list("employee_code", "name"))
    rows = []
    for e in qs:
        px = getattr(e, "people_profile", None)
        m = mgr.get(e.employee_code)
        rows.append([e.employee_code, e.name, e.email, e.phone_number, e.Gender, _d(e.Date_of_Birth), e.Designation,
                     clean_text(e.department.name if e.department_id else e.Department, ""), e.Employee_Type, e.Work_Location,
                     px.branch.name if (px and px.branch_id) else "", _d(e.Date_of_Joining), e.Probation_Period,
                     px.lifecycle_status if px else "", e.Employee_Status, "Yes" if e.is_active else "No",
                     f"{names.get(m, m)} ({m})" if m else "", e.Notice_Period, e.Resignation_Status,
                     _d(e.Resignation_Date), _d(e.Last_Working_Day)])
    return EMPLOYEE_COLUMNS, rows


def recruitment_data(company, f):
    qs = CandidateProfile.objects.filter(company=company).select_related("candidate", "requisition", "recruiter")
    if f.get("stage"):
        qs = qs.filter(candidate__candidate_status=str(f["stage"]).upper())
    if f.get("source"):
        qs = qs.filter(candidate__candidate_source__iexact=f["source"])
    if f.get("requisition_id"):
        qs = qs.filter(requisition__requisition_id=f["requisition_id"])
    if f.get("from"):
        qs = qs.filter(created_at__date__gte=to_date(f["from"]))
    if f.get("to"):
        qs = qs.filter(created_at__date__lte=to_date(f["to"]))
    profs = list(qs.order_by("-created_at")[:MAX_ROWS])
    offers = {}
    for o in Offer.objects.filter(company=company, candidate_id__in=[p.candidate_id for p in profs]).order_by("id"):
        offers[o.candidate_id] = o                                   # latest offer wins
    rows = []
    for p in profs:
        c, o = p.candidate, offers.get(p.candidate_id)
        rows.append([c.candidate_id, c.name, c.email, c.phone_number, c.applied_position,
                     p.requisition.requisition_id if p.requisition_id else "", c.candidate_source,
                     p.recruiter.name if p.recruiter_id else (c.recruiter_name or ""), c.candidate_status,
                     _d(p.created_at.date()), p.current_company, p.total_experience_years, p.notice_period_days,
                     o.offer_id if o else "", o.status if o else "", o.offered_ctc_lpa if o else "",
                     _d(o.target_joining_date) if o else "", p.rejection_reason])
    return RECRUITMENT_COLUMNS, rows


BUILDERS = {"employee_master": employee_master, "recruitment_data": recruitment_data}


def safe_cell(v):
    """Neutralise spreadsheet formula injection (=, +, -, @ at the start of a text cell)."""
    if isinstance(v, str) and v[:1] in ("=", "+", "-", "@", "\t", "\r"):
        return "'" + v
    return "" if v is None else v


def render(report_type, file_format, company, filters):
    """-> (file_name, bytes, content_type, row_count)"""
    if report_type not in BUILDERS:
        raise ValueError("report_type must be one of: " + ", ".join(BUILDERS))
    if file_format not in ("csv", "xlsx"):
        raise ValueError("file_format must be csv or xlsx.")
    header, rows = BUILDERS[report_type](company, filters or {})
    rows = [[safe_cell(c) for c in r] for r in rows]
    name = f"{report_type}_{timezone.localdate():%Y%m%d}_{timezone.now():%H%M%S}.{file_format}"
    if file_format == "csv":
        buf = io.StringIO()
        w = csv.writer(buf)
        w.writerow(header)
        w.writerows(rows)
        return name, buf.getvalue().encode("utf-8-sig"), "text/csv", len(rows)
    import openpyxl
    from openpyxl.styles import Font
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = report_type.replace("_", " ").title()[:31]
    ws.append(header)
    for c in ws[1]:
        c.font = Font(bold=True)
    for r in rows:
        ws.append(r)
    for i, h in enumerate(header, 1):
        ws.column_dimensions[ws.cell(1, i).column_letter].width = max(14, min(40, len(h) + 4))
    out = io.BytesIO()
    wb.save(out)
    return name, out.getvalue(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", len(rows)
