# ============================================================
# Payroll Hub engine - salary breakup, adjusted payslips, loan schedules, cycle validation, bank file, PDF
# The base salary row is ALWAYS the existing Employee.Payroll (Company.payroll_engine); the hub only layers
# approved inputs / variable pay / deductions / loan EMIs on top and never recalculates attendance salary.
# ============================================================
import csv
import io
from calendar import monthrange
from datetime import date
from decimal import Decimal

from django.db.models import Q, Sum

from Employee.models import BankAccount, EmployeeDT, Payroll
from People.common import employees_qs
from Timesheet.models import OvertimeClaim

from .common import MONTHS, f, get_setting, month_label, q2
from .models import (Loan, LoanRepayment, PayrollCycle, PayrollDeduction, PayrollInput, SalaryComponent,
                     VariablePay)

COUNTED_INPUT = ("APPROVED", "VERIFIED", "PROCESSED")


# ---------------- salary structure ----------------
def basis_text(c):
    m, v = c.calc_method, float(q2(c.calc_value))
    if m == "PERCENT_OF_GROSS":
        t = f"{v:g}% of Monthly Gross"
    elif m == "PERCENT_OF_BASIC":
        t = f"{v:g}% of Basic"
    elif m == "PERCENT_OF_COMPONENT":
        t = f"{v:g}% of {c.base_component.name if c.base_component_id else 'component'}"
    elif m == "FIXED_AMOUNT":
        t = f"₹{v:,.0f} / Month"
    else:
        t = "Gross - other earnings"
    if c.cap_amount:
        t += f" (capped at ₹{q2(c.cap_amount):,.0f} or actuals)"
    return t


def component_row(c):
    nature = ("Fixed" if c.calc_method != "BALANCING" else "Balancing") if c.component_type == "EARNING" else None
    return {"id": c.id, "code": c.code, "name": c.name, "component_type": c.component_type,
            "type_label": f"Earning ({nature})" if nature else "Deduction", "calc_method": c.calc_method,
            "calc_value": f(c.calc_value), "base_component": {"id": c.base_component_id, "code": c.base_component.code}
            if c.base_component_id else None, "cap_amount": f(c.cap_amount) if c.cap_amount is not None else None,
            "calculation_basis": basis_text(c), "is_taxable": c.is_taxable, "tax_treatment": c.tax_treatment,
            "description": c.description, "sort_order": c.sort_order}


def breakup(company, gross):
    """Apply the company's salary architecture to a monthly gross -> lines (nothing hard-coded)."""
    gross = q2(gross)
    comps = list(SalaryComponent.objects.filter(company=company, is_active=True).select_related("base_component"))
    amt, lines = {}, []

    def calc(c):
        if c.calc_method == "PERCENT_OF_GROSS":
            v = gross * c.calc_value / 100
        elif c.calc_method == "PERCENT_OF_BASIC":
            base = next((x for x in comps if x.code == "BASIC"), None)
            v = amt.get(base.id, Decimal("0")) * c.calc_value / 100 if base else Decimal("0")
        elif c.calc_method == "PERCENT_OF_COMPONENT":
            v = amt.get(c.base_component_id, Decimal("0")) * c.calc_value / 100
        elif c.calc_method == "FIXED_AMOUNT":
            v = c.calc_value
        else:
            v = Decimal("0")
        if c.cap_amount is not None and v > c.cap_amount:
            v = c.cap_amount
        return q2(v)

    earn = [c for c in comps if c.component_type == "EARNING"]
    for c in [x for x in earn if x.calc_method != "BALANCING"]:
        amt[c.id] = calc(c)
    for c in [x for x in earn if x.calc_method == "BALANCING"]:
        amt[c.id] = max(q2(gross - sum(amt.values(), Decimal("0"))), Decimal("0"))
    for c in [x for x in comps if x.component_type == "DEDUCTION"]:
        amt[c.id] = calc(c)
    for c in comps:
        lines.append({"code": c.code, "name": c.name, "component_type": c.component_type, "amount": f(amt[c.id]),
                      "calculation_basis": basis_text(c)})
    te = sum(a["amount"] for a in lines if a["component_type"] == "EARNING")
    td = sum(a["amount"] for a in lines if a["component_type"] == "DEDUCTION")
    return {"monthly_gross": f(gross), "lines": lines, "total_earnings": round(te, 2), "total_deductions": round(td, 2),
            "net_in_hand": round(te - td, 2), "balanced": abs(te - float(gross)) < 0.01 or not any(
                x.calc_method == "BALANCING" for x in comps if x.component_type == "EARNING")}


def component_amount(company, code, gross):
    for ln in breakup(company, gross)["lines"]:
        if ln["code"] == code:
            return Decimal(str(ln["amount"]))
    return Decimal("0")


# ---------------- payslip ----------------
def payroll_row(company, code, month, year):
    return Payroll.objects.filter(company=company, Employee_ID=code, month=month, year=year).first()


def _l(code, label, amount, source):
    return {"code": code, "label": label, "amount": f(amount), "source": source}


def compute_payslip(company, emp, month, year, st=None, prow=None):
    """Adjusted payslip = Employee.Payroll row + approved inputs / overtime claims / variable pay (earnings)
    - statutory (from the row) - PT component - non-statutory deductions - loan EMIs."""
    st = st or get_setting(company)
    prow = prow or payroll_row(company, emp.employee_code, month, year)
    if prow is None:
        return None
    E, D = [], []
    z = Decimal("0")
    days_pay = (prow.full_day_salary or z) + (prow.half_day_salary or z)
    ot = prow.overtime_pay or z
    other = (prow.gross_salary or z) - days_pay - ot
    E.append(_l("SALARY", f"Salary for attendance ({prow.present_days} days, {prow.half_days} half days)", days_pay, "PAYROLL"))
    if ot:
        E.append(_l("OVERTIME_ATT", f"Overtime from attendance ({prow.overtime_hours} hrs)", ot, "PAYROLL"))
    if other > Decimal("0.005"):
        E.append(_l("EXTRA_HOURS", "Extra hours pay", other, "PAYROLL"))
    for i in PayrollInput.objects.filter(company=company, employee=emp, month=month, year=year, is_active=True,
                                         status__in=COUNTED_INPUT).exclude(input_type="LOP"):
        line = _l(i.input_type, i.label or dict(PayrollInput.TYPES)[i.input_type], i.amount, "INPUT")
        (E if i.input_type in PayrollInput.EARNING_TYPES else D).append(line | {"ref_id": i.id})
    if st.include_timesheet_overtime:
        oc = OvertimeClaim.objects.filter(company=company, employee=emp, month=month, year=year, is_active=True,
                                          status="Passed to Payroll")
        tot = oc.aggregate(s=Sum("calculated_pay"), h=Sum("approved_hours"))
        if tot["s"]:
            E.append(_l("OVERTIME_PAYOUT", f"Overtime payout ({tot['h']} hrs, timesheet claims)", tot["s"], "TIMESHEET"))
    for v in VariablePay.objects.filter(company=company, employee=emp, payout_month=month, payout_year=year,
                                        is_active=True, status__in=["APPROVED", "PAID"]):
        E.append(_l("VARIABLE_PAY", f"Variable pay {v.period_label} ({float(q2(v.achievement_percent)):g}% achievement)",
                    v.payable_amount, "VARIABLE") | {"ref_id": v.id})
    for code, label, val in (("PF", "Provident Fund (EPF)", prow.PF_deduction), ("ESI", "ESIC", prow.ESI_deduction),
                             ("TDS", "Tax Deducted at Source (TDS)", prow.TDS_deduction),
                             ("INSURANCE", "Insurance premium", prow.insurance_deduction)):
        if val:
            D.append(_l(code, label, val, "PAYROLL"))
    pt = component_amount(company, "PT", prow.gross_salary or z) if prow.gross_salary else z
    if pt:
        D.append(_l("PT", "Professional Tax (PT)", pt, "COMPONENT"))
    for d in PayrollDeduction.objects.filter(company=company, employee=emp, month=month, year=year, is_active=True,
                                             status__in=["APPROVED", "RECOVERED"]):
        D.append(_l("DEDUCTION", d.head, d.amount, "DEDUCTION") | {"ref_id": d.id})
    for r in LoanRepayment.objects.filter(company=company, loan__employee=emp, month=month, year=year, is_active=True,
                                          status__in=["SCHEDULED", "DEDUCTED"],
                                          loan__status__in=["ACTIVE", "CLOSED"]).select_related("loan"):
        D.append(_l("LOAN_EMI", f"{r.loan.loan_no} EMI {r.installment_no}/{r.loan.tenure_months}", r.amount, "LOAN")
                  | {"ref_id": r.id})
    gross = sum(Decimal(str(x["amount"])) for x in E)
    ded = sum(Decimal(str(x["amount"])) for x in D)
    return {"employee_code": emp.employee_code, "month": month, "year": year, "period": month_label(month, year),
            "gross_salary": f(gross), "total_deductions": f(ded), "net_payable": f(gross - ded),
            "earnings": E, "deductions": D, "payroll_status": prow.status, "payment_method": prow.payment_method,
            "present_days": prow.present_days, "half_days": prow.half_days}


# ---------------- loans ----------------
def emi_for(principal, rate, n):
    principal, r = Decimal(principal), Decimal(rate) / Decimal(1200)
    if n <= 0:
        raise ValueError("tenure_months must be at least 1.")
    if r == 0:
        return q2(principal / n)
    p = (1 + r) ** n
    return q2(principal * r * p / (p - 1))


def loan_schedule(principal, rate, n, start_month, start_year):
    emi, bal, r = emi_for(principal, rate, n), Decimal(principal), Decimal(rate) / Decimal(1200)
    out, m, y = [], start_month, start_year
    for i in range(1, n + 1):
        interest = q2(bal * r)
        pp = bal if i == n else q2(emi - interest)
        out.append({"installment_no": i, "month": m, "year": y, "amount": q2(pp + interest), "principal_part": q2(pp),
                    "interest_part": interest})
        bal = q2(bal - pp)
        m, y = (1, y + 1) if m == 12 else (m + 1, y)
    return out


def loan_balance(loan):
    rows = list(loan.repayments.filter(is_active=True))
    left = [r for r in rows if r.status == "SCHEDULED"]
    return {"balance_remaining": f(sum((r.amount for r in left), Decimal("0"))), "emis_left": len(left),
            "emis_paid": sum(1 for r in rows if r.status == "DEDUCTED"),
            "principal_outstanding": f(sum((r.principal_part for r in left), Decimal("0")))}


def next_loan_no(company, year):
    n = Loan.objects.filter(company=company, loan_no__startswith=f"LN-{year}-").count() + 1
    while Loan.objects.filter(company=company, loan_no=f"LN-{year}-{n:02d}").exists():
        n += 1
    return f"LN-{year}-{n:02d}"


# ---------------- cycle ----------------
STATUS_CARD = {"OPEN": ("Open", "Inputs open for changes"), "INPUTS_FROZEN": ("Frozen", "Run payroll validation"),
               "VERIFIED": ("Frozen", "Ready for Bank Transfer"), "BANK_FILE_READY": ("Frozen", "Bank file generated"),
               "RELEASED": ("Released", "Payslips released")}


def get_cycle(company, month, year, actor=None):
    c, _ = PayrollCycle.objects.get_or_create(company=company, month=month, year=year, defaults={
        "created_by": actor, "updated_by": actor})
    return c


def default_release_date(st, month, year):
    last = monthrange(year, month)[1]
    return date(year, month, last if not st.payslip_release_day else min(st.payslip_release_day, last))


def cycle_steps(c, st):
    order = ["OPEN", "INPUTS_FROZEN", "VERIFIED", "BANK_FILE_READY", "RELEASED"]
    idx = order.index(c.status)
    done = [idx >= 1, idx >= 2, idx >= 3, idx >= 4]
    d = lambda dt: dt.strftime("%d %b") if dt else ""
    portal = st.bank_portal_name or "bank portal"
    rel = c.release_on or default_release_date(st, c.month, c.year)
    detail = [f"Completed {d(c.inputs_frozen_at)}" if done[0] else "Pending",
              f"Completed {d(c.verified_at)}" if done[1] else "Pending",
              (f"Generated {d(c.bank_file_at)}" if done[2] else f"Ready for {portal}" if idx == 2 else "Pending"),
              f"Released {d(c.released_at)}" if done[3] else f"Scheduled {rel.strftime('%d %b')}"]
    titles = ["Inputs Freeze", "Verification Run", "Bank Transfer File", "Release Payslips"]
    keys = ["inputs_freeze", "verification_run", "bank_transfer_file", "release_payslips"]
    cur = next((i for i, x in enumerate(done) if not x), None)
    return [{"no": i + 1, "key": keys[i], "title": titles[i], "detail": detail[i],
             "state": "done" if done[i] else ("current" if i == cur else "pending")} for i in range(4)]


def employees_in_cycle(company, month, year):
    rows = Payroll.objects.filter(company=company, month=month, year=year)
    codes = list(rows.values_list("Employee_ID", flat=True))
    return {e.employee_code: e for e in EmployeeDT.objects.filter(company=company, employee_code__in=codes)}, rows


def validate_cycle(company, month, year):
    """Payroll validation run -> issues. ERROR blocks verification, WARNING does not."""
    issues = []
    emps, rows = employees_in_cycle(company, month, year)
    last = date(year, month, monthrange(year, month)[1])
    if not rows.exists():
        issues.append({"severity": "ERROR", "code": "NO_PAYROLL", "employee_code": None,
                       "message": f"Payroll has not been generated for {month_label(month, year)}."})
    st = get_setting(company)
    active = employees_qs(company).filter(is_active=True).filter(
        Q(Date_of_Joining__isnull=True) | Q(Date_of_Joining__lte=last))
    for e in active:
        if e.employee_code not in emps:
            issues.append({"severity": "WARNING", "code": "NO_PAYROLL_ROW", "employee_code": e.employee_code,
                           "message": f"{e.name} has no payroll row for this month."})
    pend = (PayrollInput.objects.filter(company=company, month=month, year=year, is_active=True,
                                        status="PENDING_REVIEW").select_related("employee"))
    for i in pend:
        issues.append({"severity": "ERROR", "code": "INPUT_PENDING", "employee_code": i.employee.employee_code,
                       "message": f"Payroll input '{i.label or i.input_type}' for {i.employee.name} is pending review."})
    for d in PayrollDeduction.objects.filter(company=company, month=month, year=year, is_active=True,
                                             status="PENDING_APPROVAL").select_related("employee"):
        issues.append({"severity": "ERROR", "code": "DEDUCTION_PENDING", "employee_code": d.employee.employee_code,
                       "message": f"Deduction '{d.head}' for {d.employee.name} is pending approval."})
    for v in VariablePay.objects.filter(company=company, payout_month=month, payout_year=year, is_active=True,
                                        status__in=["DRAFT", "PENDING_APPROVAL"]).select_related("employee"):
        issues.append({"severity": "ERROR", "code": "VARIABLE_PENDING", "employee_code": v.employee.employee_code,
                       "message": f"Variable pay {v.period_label} for {v.employee.name} is not approved."})
    banks = set(BankAccount.objects.filter(employee__company=company).values_list("employee__employee_code", flat=True))
    for code, e in emps.items():
        slip = compute_payslip(company, e, month, year, st)
        if slip["net_payable"] < 0:
            issues.append({"severity": "ERROR", "code": "NEGATIVE_NET", "employee_code": code,
                           "message": f"{e.name} has a negative net salary ({slip['net_payable']})."})
        elif slip["net_payable"] == 0:
            issues.append({"severity": "WARNING", "code": "ZERO_NET", "employee_code": code,
                           "message": f"{e.name} has a zero net salary."})
        if slip["payment_method"] == "BANK" and slip["net_payable"] > 0 and code not in banks:
            issues.append({"severity": "WARNING", "code": "BANK_MISSING", "employee_code": code,
                           "message": f"{e.name} has no bank account on file - excluded from the bank file."})
    return {"passed": not any(i["severity"] == "ERROR" for i in issues),
            "errors": sum(1 for i in issues if i["severity"] == "ERROR"),
            "warnings": sum(1 for i in issues if i["severity"] == "WARNING"), "issues": issues}


def bank_of(e):
    qs = BankAccount.objects.filter(employee=e)
    return qs.filter(active=True).first() or qs.first()


def bank_file_rows(company, month, year):
    st = get_setting(company)
    emps, _ = employees_in_cycle(company, month, year)
    rows, skipped = [], []
    for code, e in sorted(emps.items()):
        slip = compute_payslip(company, e, month, year, st)
        if slip["net_payable"] <= 0 or slip["payment_method"] != "BANK":
            continue
        b = bank_of(e)
        if b is None:
            skipped.append({"employee_code": code, "reason": "No bank account"})
            continue
        rows.append({"employee_code": code, "name": e.name, "bank_name": b.bank_name, "account_number": b.account_number,
                     "ifsc": b.ifsc_code, "amount": slip["net_payable"], "narration": f"Salary {MONTHS[month]} {year}"})
    return rows, skipped


def bank_csv(rows):
    buf = io.StringIO()
    w = csv.writer(buf)
    w.writerow(["Employee Code", "Beneficiary Name", "Bank", "Account Number", "IFSC", "Amount", "Narration"])
    for r in rows:
        w.writerow([r["employee_code"], r["name"], r["bank_name"], r["account_number"], r["ifsc"],
                    f"{r['amount']:.2f}", r["narration"]])
    return buf.getvalue()


# ---------------- PDF ----------------
def payslip_pdf(company, emp, slip):
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.styles import getSampleStyleSheet
    from reportlab.lib.units import mm
    from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle
    buf = io.BytesIO()
    doc = SimpleDocTemplate(buf, pagesize=A4, leftMargin=15 * mm, rightMargin=15 * mm, topMargin=15 * mm)
    ss = getSampleStyleSheet()
    name = getattr(company, "Registered_Company_Name", None) or getattr(company, "Brand_Name", None) or "Company"
    el = [Paragraph(f"<b>{name}</b>", ss["Title"]), Paragraph(f"Payslip - {slip['period']}", ss["Heading3"]),
          Paragraph(f"{emp.name} ({emp.employee_code}) &nbsp;|&nbsp; {emp.Designation or ''}", ss["Normal"]),
          Spacer(1, 6 * mm)]
    n = max(len(slip["earnings"]), len(slip["deductions"]))
    data = [["Earnings", "Amount (Rs.)", "Deductions", "Amount (Rs.)"]]
    for i in range(n):
        e = slip["earnings"][i] if i < len(slip["earnings"]) else None
        d = slip["deductions"][i] if i < len(slip["deductions"]) else None
        data.append([e["label"] if e else "", f"{e['amount']:,.2f}" if e else "", d["label"] if d else "",
                     f"{d['amount']:,.2f}" if d else ""])
    data.append(["Gross", f"{slip['gross_salary']:,.2f}", "Total deductions", f"{slip['total_deductions']:,.2f}"])
    data.append(["", "", "NET PAYABLE", f"{slip['net_payable']:,.2f}"])
    t = Table(data, colWidths=[62 * mm, 28 * mm, 62 * mm, 28 * mm])
    t.setStyle(TableStyle([("GRID", (0, 0), (-1, -1), 0.4, colors.grey), ("BACKGROUND", (0, 0), (-1, 0), colors.lightgrey),
                           ("FONTNAME", (0, -2), (-1, -1), "Helvetica-Bold"), ("ALIGN", (1, 0), (1, -1), "RIGHT"),
                           ("ALIGN", (3, 0), (3, -1), "RIGHT"), ("FONTSIZE", (0, 0), (-1, -1), 8)]))
    el += [t, Spacer(1, 8 * mm), Paragraph("This is a system generated payslip.", ss["Italic"])]
    doc.build(el)
    return buf.getvalue()


# ============================================================
# ADDITIVE: Detailed salary-slip PDF (bordered template: header band, employee + statutory block, earnings /
# deductions grid, miscellaneous-deduction and OT/allowance detail tables).
# Defined AFTER the original payslip_pdf above, so every caller (employee + HR download) now gets this layout.
# The older basic version is kept untouched above as the reference implementation.
# ============================================================
def _slip_month_year(slip):
    m, y = slip.get("month"), slip.get("year")
    if m and y:
        return int(m), int(y)
    try:
        name, yr = str(slip.get("period") or "").split()
        return MONTHS.index(name), int(yr)
    except (ValueError, TypeError):
        return None, None


def _mask(v, keep=4):
    v = str(v or "").strip()
    return \
        "-" if not v else (v if len(v) <= keep else "X" * (len(v) - keep) + v[-keep:])


def payslip_pdf(company, emp, slip):
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.styles import ParagraphStyle
    from reportlab.lib.units import mm
    from reportlab.lib.utils import ImageReader
    from reportlab.platypus import Image, Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle

    NAVY, LBLUE, YELLOW = colors.HexColor("#1F3C88"), colors.HexColor("#DCE6F4"), colors.HexColor("#FFFF00")
    month, year = _slip_month_year(slip)
    prow = payroll_row(company, emp.employee_code, month, year) if month else None
    z = Decimal("0")

    def amt(v):
        return f"{float(v or 0):,.2f}"

    def para(t, bold=False, color=colors.black, align=0, size=7):
        return Paragraph(str(t), ParagraphStyle("c", fontName="Helvetica-Bold" if bold else "Helvetica",
                                                fontSize=size, leading=size + 2, textColor=color, alignment=align))

    # ---------- bucket the frozen earning / deduction lines into the template rows ----------
    def has(line, *words):
        s = f"{line.get('code', '')} {line.get('label', '')}".lower()
        return any(w in s for w in words)

    E = {k: z for k in ("basic", "hra", "cust", "shift", "ot", "arrears", "misc", "bonus")}
    OT_LINES, MISC_DED_LINES = [], []
    for ln in slip.get("earnings", []):
        a, code = Decimal(str(ln.get("amount") or 0)), ln.get("code", "")
        if code == "SALARY":
            E["basic"] += a
        elif has(ln, "hra", "house rent"):
            E["hra"] += a
        elif has(ln, "customer handling"):
            E["cust"] += a
        elif code in ("OVERTIME_ATT", "EXTRA_HOURS", "OVERTIME_PAYOUT") or has(ln, "overtime", "additional allowance"):
            E["ot"] += a
            OT_LINES.append((a, ln.get("label") or "Overtime"))
        elif has(ln, "shift"):
            E["shift"] += a
        elif code == "ARREAR":
            E["arrears"] += a
        elif code == "BONUS" or has(ln, "statutory bonus"):
            E["bonus"] += a
        else:
            E["misc"] += a
    D = {k: z for k in ("pf", "esi", "lwf", "pt", "tax", "mobile", "loan", "misc")}
    for ln in slip.get("deductions", []):
        a, code = Decimal(str(ln.get("amount") or 0)), ln.get("code", "")
        if code == "PF":
            D["pf"] += a
        elif code == "ESI":
            D["esi"] += a
        elif code == "PT":
            D["pt"] += a
        elif code == "TDS":
            D["tax"] += a
        elif code == "LOAN_EMI":
            D["loan"] += a
        elif has(ln, "welfare", "lwf"):
            D["lwf"] += a
        elif has(ln, "mobile", "phone"):
            D["mobile"] += a
        else:
            D["misc"] += a
            MISC_DED_LINES.append((a, ln.get("label") or "Deduction"))
    gross1 = sum((E[k] for k in ("basic", "hra", "cust", "shift", "ot", "arrears", "misc")), z)
    gross2 = E["bonus"]
    total_earn = gross1 + gross2
    total_ded = sum(D.values(), z)
    net = total_earn - total_ded

    # ---------- employee details ----------
    dept = getattr(getattr(emp, "department", None), "name", None) or getattr(emp, "Department", None) or "-"
    doj = getattr(emp, "Date_of_Joining", None)
    try:
        bank = bank_of(emp)
    except Exception:
        bank = None
    present = Decimal(str((prow.present_days if prow else 0) or 0)) + Decimal(str((prow.half_days if prow else 0) or 0)) / 2
    days_in_month = monthrange(year, month)[1] if month else 30
    lop = max(Decimal(days_in_month) - present, z)
    ctc = getattr(emp, "Basic_Salary", None)
    ps = lambda v: para(v if v not in (None, "") else "-")
    pl = lambda v: para(v, bold=True)

    name = getattr(company, "Registered_Company_Name", None) or getattr(company, "Brand_Name", None) or "Company"
    logo = None
    try:
        lf = getattr(company, "Company_logo", None)
        if lf and getattr(lf, "path", None):
            iw, ih = ImageReader(lf.path).getSize()
            logo = Image(lf.path, width=min(24 * mm, 14 * mm * iw / ih), height=14 * mm)
    except Exception:
        logo = None

    W = 190 * mm
    el = []
    head = Table([[logo or para(name, bold=True, size=9),
                   para(f"Salary Slip for the Month - {slip.get('period', '')}", bold=True, color=colors.white,
                        align=1, size=9)]], colWidths=[45 * mm, W - 45 * mm], rowHeights=[14 * mm])
    head.setStyle(TableStyle([("BACKGROUND", (1, 0), (1, 0), NAVY), ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
                              ("ALIGN", (0, 0), (0, 0), "LEFT")]))
    el += [head, Spacer(1, 1.5 * mm)]

    GRID = [("GRID", (0, 0), (-1, -1), 0.5, NAVY), ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
            ("TOPPADDING", (0, 0), (-1, -1), 3), ("BOTTOMPADDING", (0, 0), (-1, -1), 3)]
    t1 = Table([
        [pl("Employee Name"), ps(emp.name), pl("Department"), ps(dept)],
        [pl("Employee Code"), ps(emp.employee_code), pl("Designation"), ps(getattr(emp, "Designation", None))],
        [pl("O.O.J"), ps(doj.strftime("%d-%m-%Y") if doj else None), pl("Grade"), ps(None)],
        [pl("Location"), ps(getattr(emp, "Work_Location", None)), pl("Monthly CTC"), ps(amt(ctc) if ctc else "-")],
    ], colWidths=[32 * mm, 63 * mm, 32 * mm, 63 * mm])
    t1.setStyle(TableStyle(GRID))
    el += [t1, Spacer(1, 1.5 * mm)]

    t2 = Table([
        [pl("Bank A/C number"), ps(_mask(getattr(bank, "account_number", None))), pl("Aadhaar No."),
         ps(_mask(getattr(emp, "aadhaar_number", None)))],
        [pl("PAN No."), ps(getattr(emp, "pan_number", None)), pl("Net Days Payable"), ps(f"{float(present):g}")],
        [pl("PF No."), ps(getattr(emp, "pf_number", None)), pl("LOP Days"), ps(f"{float(lop):g}")],
        [pl("ESIC NO."), ps(getattr(emp, "esi_number", None)), pl("PF Employer Contribution(Rs.)"), ps("0")],
        [pl("UAN NO."), ps(None), pl("ESIC Employer Contribution(Rs.)"), ps("0")],
    ], colWidths=[32 * mm, 63 * mm, 45 * mm, 50 * mm])
    t2.setStyle(TableStyle(GRID))
    el += [t2, Spacer(1, 1.5 * mm)]

    # ---------- earnings / deductions grid ----------
    wh = lambda t: para(t, bold=True, color=colors.white, align=1)
    R = lambda v: para(amt(v), align=2)
    rows = [[wh("Earnings"), wh("Amt. (INR)"), wh("Deductions"), wh("Amt. (INR)")]]
    pairs = [("Basic Salary", E["basic"], "PF Employee Contribution", D["pf"]),
             ("House Rent Allowance", E["hra"], "ESIC Employee Contribution", D["esi"]),
             ("Customer Handling Allowance", E["cust"], "Labour Welfare Fund", D["lwf"]),
             ("Shift Allowance", E["shift"], "Professional Tax", D["pt"]),
             ("OT/Shift/Additional Allowance", E["ot"], "Income Tax", D["tax"]),
             ("Arrears", E["arrears"], "Mobile Phone Deductions", D["mobile"]),
             ("Miscellaneous Earnings", E["misc"], "Loans and Advances", D["loan"]),
             ("", None, "Miscellaneous Deductions", D["misc"])]
    for a, av, b, bv in pairs:
        rows.append([para(a), R(av) if av is not None else "", para(b), R(bv)])
    rows.append([para("Gross Earnings(1)", True), R(gross1), "", ""])                       # idx 9
    rows.append([para("Other Earnings", True), "", "", ""])                                  # idx 10
    rows.append([para("Statutory Bonus"), R(E["bonus"]), "", ""])                            # idx 11
    rows.append(["", "", "", ""])                                                            # idx 12
    rows.append([para("Other Earnings(2)", True), R(gross2), "", ""])                        # idx 13
    rows.append(["", "", "", ""])                                                            # idx 14
    rows.append([para("Total Earnings(A) = (1+2)", True), R(total_earn), para("Total Deductions(B)", True),
                 R(total_ded)])                                                              # idx 15
    rows.append(["", "", para("Total Net Payable (A-B)", True), R(net)])                     # idx 16
    t3 = Table(rows, colWidths=[62 * mm, 33 * mm, 62 * mm, 33 * mm])
    st = list(GRID) + [("BACKGROUND", (0, 0), (-1, 0), NAVY), ("TOPPADDING", (0, 1), (-1, -1), 2.5),
                       ("BOTTOMPADDING", (0, 1), (-1, -1), 2.5)]
    for r in (9, 13, 15):
        st.append(("BACKGROUND", (0, r), (-1, r), YELLOW))
    st += [("BACKGROUND", (2, 16), (3, 16), YELLOW), ("BACKGROUND", (0, 10), (1, 10), LBLUE)]
    t3.setStyle(TableStyle(st))
    el += [t3, Spacer(1, 1.5 * mm),
           para("<i>This is a system generated Payslip and hence company seal or signature not required</i>", size=6),
           Spacer(1, 3 * mm)]

    # ---------- detail tables ----------
    hdr = lambda cells: [para(c, bold=True, align=1) for c in cells]
    DET = list(GRID) + [("BACKGROUND", (0, 0), (-1, 0), LBLUE)]
    el += [para("Detailing of Miscellaneous Deductions :", bold=True, size=8), Spacer(1, 1 * mm)]
    md = [hdr(["Amount", "Nature Of Deduction", "Client/Vendor Affected", "Client ID/Reference",
               "Date Of event/Offence"])]
    for a, label in (MISC_DED_LINES or [(z, "-")]):
        md.append([para(amt(a) if label != "-" else "0"), para(label), para("-"), para("-"), para("-")])
    tm = Table(md, colWidths=[28 * mm, 52 * mm, 38 * mm, 36 * mm, 36 * mm])
    tm.setStyle(TableStyle(DET))
    el += [tm, Spacer(1, 3 * mm), para("Detail OT/Shift/Additional Allowance :", bold=True, size=8), Spacer(1, 1 * mm)]
    ptxt = f"{year}-{month:02d}" if month else "-"
    od = [hdr(["Amount", "Reason Of Extra Payment", "Payable Month"])]
    for a, label in (OT_LINES or [(z, "-")]):
        od.append([para(amt(a)), para(label), para(ptxt)])
    to = Table(od, colWidths=[28 * mm, 114 * mm, 48 * mm])
    to.setStyle(TableStyle(DET))
    el.append(to)

    buf = io.BytesIO()
    SimpleDocTemplate(buf, pagesize=A4, leftMargin=10 * mm, rightMargin=10 * mm, topMargin=10 * mm,
                      bottomMargin=10 * mm, title=f"Payslip {slip.get('period', '')}").build(el)
    return buf.getvalue()
    