# ============================================================
# ADDITIVE: Payroll calculation + PDF/Excel export helpers.
#
# Ported from the older template-based (MVT) HRMS project's
# Company/views.py, adapted to hrnexus's real models/fields
# (EmployeeDT, AttendanceEmp, Payroll, Invoice, Client, etc.)
# and kept as pure helper functions so the DRF views in
# Company/views_api.py stay thin.
# ============================================================

import calendar
import io
import os
from datetime import date, datetime, timedelta
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP

from django.db.models import Q, Sum, Count
from django.http import HttpResponse
from django.utils.timezone import now

import openpyxl
from openpyxl.styles import Font, Alignment

from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A4
from reportlab.lib import colors
from reportlab.lib.units import mm
from reportlab.lib.utils import ImageReader
from reportlab.platypus import (
    SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image,
)
from reportlab.lib.styles import getSampleStyleSheet


# ============================================================
# SMALL DATE HELPER
# ============================================================

def last_month_year(today=None):
    """(month, year) for the calendar month immediately before today."""

    today = today or datetime.now()

    if today.month == 1:
        return 12, today.year - 1

    return today.month - 1, today.year


def is_not_future_month(selected_month: int, selected_year: int) -> bool:
    """True if (year, month) is current or past (never a future month)."""

    today = datetime.now()

    return (int(selected_year), int(selected_month)) <= (today.year, today.month)


# ============================================================
# SALARY CALCULATION
# ============================================================

def time_to_decimal(time_str):
    """
    Convert a working-hours string to decimal hours.
    Supports "HH:MM", "HH:MM:SS" and "HH:MM AM/PM".
    """

    if not time_str:
        return Decimal(0)

    try:

        if "AM" in time_str.upper() or "PM" in time_str.upper():

            t = datetime.strptime(time_str.strip(), "%I:%M %p")

            return Decimal(t.hour) + Decimal(t.minute) / 60

        parts = time_str.split(":")

        hours = int(parts[0])
        minutes = int(parts[1]) if len(parts) > 1 else 0
        seconds = int(parts[2]) if len(parts) > 2 else 0

        return Decimal(hours) + Decimal(minutes) / 60 + Decimal(seconds) / 3600

    except Exception:
        return Decimal(0)


def calculate_salary(employee_id, month, year):
    """
    Calculate one employee's salary for (month, year) from their
    AttendanceEmp records.

    Rules:
      - Present day -> full per-day salary.
      - Half day -> 50% of per-day salary; hours beyond half the
        expected shift on that day are paid separately (extra_hr).
      - Overtime -> hours beyond the expected shift, paid at the
        employee's Overtime_Rate.
      - Sunday -> always a fully paid day; if the employee works,
        the ENTIRE worked hours count as overtime.
      - Saturday (employee.saturday_policy):
          OFF  -> full salary regardless; if worked, all hours
                  worked count as overtime.
          HALF -> full salary if the employee works at least half
                  the expected shift; hours beyond that half-day
                  count as overtime. Less than half a day -> unpaid.
          FULL -> treated exactly like a normal weekday (full /
                  half / unpaid based on hours worked, overtime
                  only beyond the expected shift).
      - Holiday / Paid Leave / Sick Leave / Casual Leave -> fully
        paid day; if the employee works, the entire worked hours
        count as overtime.
      - Deductions: PF / TDS / ESI, each a percentage of Gross
        Salary (not of Basic Salary).
      - Gross Salary  = Full Day Salary + Half Day Salary
                         + Overtime Pay + Extra Hour Pay
      - Net Salary    = Gross Salary - (PF + TDS + ESI)

    Returns a dict of computed figures, or {"error": ...}.
    """

    from Employee.models import EmployeeDT, AttendanceEmp

    try:
        employee = EmployeeDT.objects.get(employee_code=employee_id)
    except EmployeeDT.DoesNotExist:
        return {"error": "Employee Not Found"}

    if not employee.Date_of_Joining:
        return {"error": "Date of Joining not set"}

    basic_salary = employee.Basic_Salary or Decimal(0)
    pf_percentage = employee.PF_Percentage or Decimal(0)
    tds_percentage = employee.TDS_Percentage or Decimal(0)
    esi_percentage = employee.ESI_Percentage or Decimal(0)
    overtime_rate = employee.Overtime_Rate or Decimal(0)

    expected_work_hours = Decimal(employee.Work_hours or 8)

    total_days = calendar.monthrange(int(year), int(month))[1]

    first_day = date(int(year), int(month), 1)
    last_day = date(int(year), int(month), total_days)

    joining_date = employee.Date_of_Joining

    if joining_date > last_day:
        return {
            "Employee Name": employee.name,
            "Basic Salary": basic_salary,
            "Month Days": total_days,
            "Net Salary": Decimal(0),
            "message": "Employee joined after this payroll month",
        }

    start_date = max(first_day, joining_date)
    end_date = min(last_day, date.today() - timedelta(days=1))

    if end_date < start_date:
        # Nothing has happened yet this month (e.g. generating on
        # the 1st for the current month) - zero salary, not an error.
        end_date = start_date - timedelta(days=1)

    per_day_salary = basic_salary / Decimal(total_days)
    extra_hr_rate = (
        per_day_salary / expected_work_hours
        if expected_work_hours
        else Decimal(0)
    )

    attendance_records = AttendanceEmp.objects.filter(
        Q(Employee_ID=employee_id) &
        Q(date__gte=start_date) &
        Q(date__lte=end_date)
    )

    attendance_map = {a.date: a for a in attendance_records}

    present_days = 0
    half_days = 0
    overtime_hours = Decimal(0)
    extra_hr = Decimal(0)
    total_working_hr = Decimal(0)

    LEAVE_TYPES = ["Holiday", "Paid Leave", "Sick Leave", "Casual Leave"]

    day = start_date.day

    while day <= end_date.day:

        current_date = date(int(year), int(month), day)
        weekday = current_date.weekday()

        record = attendance_map.get(current_date)

        if not record:
            day += 1
            continue

        work_hours = time_to_decimal(record.total_working_hours)

        if record.status == "Present" and not work_hours:
            work_hours = expected_work_hours

        # -------- Sunday --------
        if weekday == 6:

            present_days += 1

            if work_hours:
                overtime_hours += work_hours

            total_working_hr += work_hours if work_hours else expected_work_hours

            day += 1
            continue

        # -------- Saturday --------
        if weekday == 5:

            saturday_policy = employee.saturday_policy or "FULL"

            if saturday_policy == "OFF":

                present_days += 1

                if work_hours:
                    overtime_hours += work_hours

                total_working_hr += work_hours if work_hours else expected_work_hours

            elif saturday_policy == "HALF":

                half_expected = expected_work_hours / 2

                if record.status == "Absent":
                    day += 1
                    continue

                if record.status in LEAVE_TYPES:

                    present_days += 1

                    if work_hours:
                        overtime_hours += work_hours

                    total_working_hr += work_hours if work_hours else expected_work_hours

                elif work_hours >= half_expected:

                    present_days += 1
                    overtime_hours += max(work_hours - half_expected, Decimal(0))
                    total_working_hr += work_hours

                else:
                    total_working_hr += work_hours

            else:  # FULL - treated exactly like a normal weekday

                if record.status in LEAVE_TYPES:

                    present_days += 1

                    if work_hours:
                        overtime_hours += work_hours

                    total_working_hr += work_hours if work_hours else expected_work_hours

                elif work_hours >= expected_work_hours:

                    present_days += 1
                    overtime_hours += max(work_hours - expected_work_hours, Decimal(0))
                    total_working_hr += work_hours

                elif work_hours >= expected_work_hours / 2:

                    half_days += 1
                    extra_hr += max(work_hours - (expected_work_hours / 2), Decimal(0))
                    total_working_hr += work_hours

                else:
                    # Absent, or worked less than half day -> no pay
                    total_working_hr += work_hours

            day += 1
            continue

        # -------- Holiday / Leave (Mon-Fri) --------
        if record.status in LEAVE_TYPES:

            present_days += 1

            if work_hours:
                overtime_hours += work_hours

            total_working_hr += work_hours if work_hours else expected_work_hours

            day += 1
            continue

        # -------- Normal weekday --------
        if work_hours >= expected_work_hours:

            present_days += 1
            overtime_hours += work_hours - expected_work_hours

        elif work_hours >= expected_work_hours / 2:

            half_days += 1
            extra_hr += work_hours - (expected_work_hours / 2)

        total_working_hr += work_hours

        day += 1

    # -------- Salary calculation --------

    full_day_salary = per_day_salary * present_days
    half_day_salary = (per_day_salary / 2) * half_days

    overtime_pay = overtime_hours * overtime_rate
    extra_hr_pay = extra_hr * extra_hr_rate

    gross_salary = full_day_salary + half_day_salary + overtime_pay + extra_hr_pay

    pf_deduction = (pf_percentage / 100) * gross_salary
    tds_deduction = (tds_percentage / 100) * gross_salary
    esi_deduction = (esi_percentage / 100) * gross_salary

    total_deductions = pf_deduction + tds_deduction + esi_deduction

    net_salary = gross_salary - total_deductions

    return {
        "Employee Name": employee.name,
        "Basic Salary": basic_salary,
        "Month Days": total_days,
        "Total Present Days": present_days,
        "Total Half Days": half_days,
        "Overtime Hours": overtime_hours,
        "Full Day Salary": full_day_salary,
        "Half Day Salary": half_day_salary,
        "Overtime Pay": overtime_pay,
        "Gross Salary": gross_salary,
        "Total Deductions": total_deductions,
        "Net Salary": net_salary,
        "total_working_hr": total_working_hr,
        "Start Date": start_date,
        "End Date": end_date,
        "pf_deduction": pf_deduction,
        "tds_deduction": tds_deduction,
        "esi_deduction": esi_deduction,
    }


def generate_monthly_payroll(month, year, company_dt):
    """
    Generate/update payroll for every employee of company_dt for
    (month, year). A record already marked "Paid" is left untouched.
    Returns the list of Payroll rows (created, updated or left as-is).
    """

    from Employee.models import EmployeeDT, Payroll

    employees = EmployeeDT.objects.filter(company=company_dt)
    payroll_entries = []

    for employee in employees:

        salary_data = calculate_salary(employee.employee_code, month, year)

        if "error" in salary_data:
            continue

        existing = Payroll.objects.filter(
            Employee_ID=employee.employee_code,
            month=month,
            year=year,
        ).first()

        if existing and existing.status == "Paid":
            payroll_entries.append(existing)
            continue

        defaults = {
            "name": employee.name,
            "company": employee.company,
            "basic_salary": salary_data.get("Basic Salary") or 0,
            "present_days": salary_data.get("Total Present Days") or 0,
            "half_days": salary_data.get("Total Half Days") or 0,
            "overtime_hours": salary_data.get("Overtime Hours") or 0,
            "full_day_salary": salary_data.get("Full Day Salary") or 0,
            "half_day_salary": salary_data.get("Half Day Salary") or 0,
            "overtime_pay": salary_data.get("Overtime Pay") or 0,
            "gross_salary": salary_data.get("Gross Salary") or 0,
            "total_deductions": salary_data.get("Total Deductions") or 0,
            "net_salary": salary_data.get("Net Salary") or 0,
            "total_working_hr": salary_data.get("total_working_hr") or 0,
            "PF_deduction": salary_data.get("pf_deduction") or 0,
            "TDS_deduction": salary_data.get("tds_deduction") or 0,
            "ESI_deduction": salary_data.get("esi_deduction") or 0,
        }

        if existing:

            for k, v in defaults.items():
                setattr(existing, k, v)

            existing.save()
            payroll_entries.append(existing)

        else:

            obj = Payroll.objects.create(
                Employee_ID=employee.employee_code,
                month=month,
                year=year,
                **defaults,
            )

            payroll_entries.append(obj)

    return payroll_entries


# ============================================================
# FINANCE SUMMARY HELPERS
# ============================================================

def monthly_profit_loss(company_dt):

    from Company.models import BankTransaction, Expense

    current_year = now().year
    current_month = now().month

    total_income = BankTransaction.objects.filter(
        company=company_dt,
        transaction_type="Credit",
        date__year=current_year,
        date__month=current_month,
    ).aggregate(Sum("amount"))["amount__sum"] or 0

    total_expenses = Expense.objects.filter(
        company=company_dt,
        date__year=current_year,
        date__month=current_month,
    ).aggregate(Sum("amount"))["amount__sum"] or 0

    return {
        "month": current_month,
        "year": current_year,
        "total_income": total_income,
        "total_expenses": total_expenses,
        "net_profit_loss": total_income - total_expenses,
    }


def yearly_profit_loss(company_dt):

    from Company.models import BankTransaction, Expense

    current_year = now().year

    total_income = BankTransaction.objects.filter(
        company=company_dt,
        transaction_type="Credit",
        date__year=current_year,
    ).aggregate(Sum("amount"))["amount__sum"] or 0

    total_expenses = Expense.objects.filter(
        company=company_dt,
        date__year=current_year,
    ).aggregate(Sum("amount"))["amount__sum"] or 0

    return {
        "year": current_year,
        "total_income": total_income,
        "total_expenses": total_expenses,
        "net_profit_loss": total_income - total_expenses,
    }


def invoice_summary(company_dt):

    from Company.models import Invoice

    today = now().date()
    first_day_of_month = today.replace(day=1)
    first_day_of_year = today.replace(month=1, day=1)

    monthly_summary = list(
        Invoice.objects.filter(
            company=company_dt, created_at__date__gte=first_day_of_month
        ).values("status").annotate(
            total_amount=Sum("grand_total"), total_count=Count("id")
        )
    )

    yearly_summary = list(
        Invoice.objects.filter(
            company=company_dt, created_at__date__gte=first_day_of_year
        ).values("status").annotate(
            total_amount=Sum("grand_total"), total_count=Count("id")
        )
    )

    total_pending = Invoice.objects.filter(
        status="Pending", company=company_dt
    ).aggregate(total_amount=Sum("grand_total"), total_count=Count("id"))

    total_paid = Invoice.objects.filter(
        status="Paid", company=company_dt
    ).aggregate(total_amount=Sum("grand_total"), total_count=Count("id"))

    return {
        "monthly_summary": monthly_summary,
        "yearly_summary": yearly_summary,
        "total_pending_amount": total_pending["total_amount"] or 0,
        "total_pending_count": total_pending["total_count"] or 0,
        "total_paid_amount": total_paid["total_amount"] or 0,
        "total_paid_count": total_paid["total_count"] or 0,
    }


# ============================================================
# PDF: SALARY SLIP
# ============================================================

def _safe(v, default="-"):

    try:
        s = "" if v is None else str(v).strip()
        return s if s else default
    except Exception:
        return default


def _image_reader_from_filefield(filefield):

    if not filefield:
        return None

    try:

        if hasattr(filefield, "path") and os.path.exists(filefield.path):
            return ImageReader(filefield.path)

        f = getattr(filefield, "file", None)

        if f:
            data = f.read()
            f.seek(0)
            return ImageReader(io.BytesIO(data))

    except Exception:
        pass

    return None


def _normalize_amount(val) -> Decimal:

    try:
        return Decimal("0") if val is None else Decimal(str(val))
    except (InvalidOperation, ValueError, TypeError):
        return Decimal("0")


def _two_digit_word(n: int) -> str:

    n = int(n) % 100

    ones = [
        "zero", "one", "two", "three", "four", "five", "six", "seven",
        "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen",
        "fifteen", "sixteen", "seventeen", "eighteen", "nineteen",
    ]

    tens = [
        "", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy",
        "eighty", "ninety",
    ]

    if n < 20:
        return ones[n]

    t, o = divmod(n, 10)

    return tens[t] if o == 0 else f"{tens[t]} {ones[o]}"


def _three_digit_word(n: int) -> str:

    n = int(n) % 1000
    h, r = divmod(n, 100)

    if h and r:
        return f"{_two_digit_word(h)} hundred {_two_digit_word(r)}"

    if h:
        return f"{_two_digit_word(h)} hundred"

    return _two_digit_word(r)


def _int_to_indian_words(n: int) -> str:

    n = int(n)

    if n == 0:
        return "zero"

    parts = []

    crore, n = divmod(n, 10_000_000)
    lakh, n = divmod(n, 100_000)
    thousand, n = divmod(n, 1_000)

    if crore:
        parts.append(f"{_three_digit_word(crore)} crore")
    if lakh:
        parts.append(f"{_three_digit_word(lakh)} lakh")
    if thousand:
        parts.append(f"{_three_digit_word(thousand)} thousand")
    if n:
        parts.append(_three_digit_word(n))

    return " ".join(parts)


def inr_words(amount) -> str:

    dec = _normalize_amount(amount)
    sign = "minus " if dec < 0 else ""
    dec = abs(dec)
    dec_q = dec.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

    rupees_part = int(dec_q)
    paise_part = int((dec_q - Decimal(rupees_part)) * 100)

    words_rupees = _int_to_indian_words(rupees_part)

    if paise_part:
        res = f"{sign}{words_rupees} rupees and {_two_digit_word(paise_part)} paise only"
    else:
        res = f"{sign}{words_rupees} rupees only"

    return res.title()


def render_salary_slip_pdf(employee, payroll):
    """
    Render one employee's payroll as a polished A4 PDF salary slip
    and return it as an HttpResponse (attachment download).
    """

    company = employee.company

    meta = getattr(payroll, "payment_account_json", {}) or {}
    method = _safe(getattr(payroll, "payment_method", None), "BANK").upper()
    status = _safe(getattr(payroll, "status", None), "Unpaid")

    response = HttpResponse(content_type="application/pdf")
    response["Content-Disposition"] = (
        f'attachment; filename="salary_slip_{employee.employee_code}_'
        f'{payroll.month}_{payroll.year}.pdf"'
    )

    pdf = canvas.Canvas(response, pagesize=A4)
    pw, ph = A4
    pdf.setTitle(f"Salary Slip - {payroll.month}/{payroll.year}")

    margin = 18 * mm
    left = margin
    right = pw - margin

    logo_reader = _image_reader_from_filefield(
        getattr(company, "Company_logo", None)
    )

    # Header band
    pdf.setFillColor(colors.HexColor("#0F172A"))
    pdf.rect(0, ph - 28 * mm, pw, 28 * mm, stroke=0, fill=1)

    if logo_reader:
        pdf.drawImage(
            logo_reader, right - 20 * mm, ph - 26 * mm,
            width=18 * mm, height=18 * mm,
            preserveAspectRatio=True, mask="auto",
        )

    pdf.setFillColor(colors.white)
    pdf.setFont("Helvetica-Bold", 14)
    pdf.drawString(
        left, ph - 12 * mm,
        _safe(getattr(company, "Registered_Company_Name", None), "Company Name"),
    )
    pdf.setFont("Helvetica", 9)
    pdf.drawString(
        left, ph - 17 * mm,
        _safe(getattr(company, "Company_address", None), "Company Address"),
    )
    contact_line = f"Phone: {_safe(getattr(company, 'Company_Official_Contact', None), 'N/A')}"
    email = getattr(company, "Company_Official_Email", None)
    if email:
        contact_line += f"  |  Email: {email}"
    pdf.drawString(left, ph - 22 * mm, contact_line)

    pdf.setFillColor(colors.HexColor("#111827"))
    pdf.setFont("Helvetica-Bold", 16)
    pdf.drawCentredString(pw / 2, ph - 34 * mm, "SALARY SLIP")
    pdf.setFont("Helvetica-Bold", 11)
    pdf.setFillColor(colors.HexColor("#6B7280"))
    pdf.drawCentredString(pw / 2, ph - 40 * mm, f"{payroll.month}/{payroll.year}")

    y = ph - 46 * mm

    def section_title(x, y_pos, text):
        pdf.setFont("Helvetica-Bold", 12)
        pdf.setFillColor(colors.HexColor("#111111"))
        pdf.drawString(x, y_pos, text)
        pdf.setStrokeColor(colors.HexColor("#E5E7EB"))
        pdf.line(x, y_pos - 2, x + 170 * mm, y_pos - 2)

    def table(data, x, y_top, col_widths, header_bg="#F3F4F6"):
        t = Table(data, colWidths=col_widths, repeatRows=1)
        t.setStyle(TableStyle([
            ("FONT", (0, 0), (-1, -1), "Helvetica", 9),
            ("FONT", (0, 0), (-1, 0), "Helvetica-Bold", 9),
            ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor(header_bg)),
            ("TEXTCOLOR", (0, 0), (-1, 0), colors.HexColor("#111827")),
            ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#FAFAFA")]),
            ("GRID", (0, 0), (-1, -1), 0.4, colors.HexColor("#D1D5DB")),
            ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
            ("ALIGN", (-1, 1), (-1, -1), "RIGHT"),
        ]))
        w, h = t.wrapOn(pdf, 0, 0)
        t.drawOn(pdf, x, y_top - h)
        return y_top - h

    emp_info = [
        ["Employee Name", _safe(employee.name), "Employee ID", _safe(payroll.Employee_ID)],
        ["Department", _safe(getattr(employee, "Department", None), "N/A"),
         "Designation", _safe(getattr(employee, "Designation", None), "N/A")],
        ["Pay Period", f"{_safe(payroll.month)}/{_safe(payroll.year)}", "Status", status],
    ]
    y = table(emp_info, left, y, [35 * mm, 60 * mm, 35 * mm, 40 * mm], header_bg="#E5E7EB") - 6 * mm

    col_w = (right - left - 8 * mm) / 2.0
    left_x = left
    right_x = left + col_w + 8 * mm

    section_title(left_x, y, "Earnings")
    section_title(right_x, y, "Deductions & Taxes")
    y -= 8 * mm

    earnings = [
        ["Head", "Amount"],
        ["Basic Salary", _safe(payroll.basic_salary)],
        ["Full Day Salary", _safe(payroll.full_day_salary)],
        ["Half Day Salary", _safe(payroll.half_day_salary)],
        ["Overtime Hours", _safe(payroll.overtime_hours)],
        ["Overtime Pay", _safe(payroll.overtime_pay)],
        ["Gross Salary", _safe(payroll.gross_salary)],
    ]

    deductions = [
        ["Head", "Amount"],
        ["PF", _safe(payroll.PF_deduction)],
        ["TDS", _safe(payroll.TDS_deduction)],
        ["ESI", _safe(payroll.ESI_deduction)],
        ["Total Deductions", _safe(payroll.total_deductions)],
        ["Net Salary (Take Home)", _safe(payroll.net_salary)],
    ]

    y_left = table(earnings, left_x, y, [col_w * 0.65, col_w * 0.35])
    y_right = table(deductions, right_x, y, [col_w * 0.65, col_w * 0.35])
    y = min(y_left, y_right) - 6 * mm

    section_title(left, y, "Payment Details")
    y -= 8 * mm

    pay_rows = [["Field", "Details"], ["Status", status], ["Method", method]]

    if method in ("BANK", "CHEQUE"):
        pay_rows += [
            ["Bank", _safe(meta.get("bank_name"))],
            ["Account No.", _safe(meta.get("account_number"))],
            ["IFSC", _safe(meta.get("ifsc_code"))],
            ["UTR/Cheque No.", _safe(meta.get("utr"))],
        ]
    elif method == "UPI":
        pay_rows += [
            ["UPI ID", _safe(payroll.upi_id or meta.get("upi_id"))],
            ["UPI Txn/UTR", _safe(payroll.upi_txn_id)],
        ]
    elif method in ("WALLET", "CASH"):
        pay_rows += [["Wallet/Cash Ref", _safe(meta.get("wallet_ref"))]]

    pay_rows += [
        ["Paid Amount", _safe(payroll.amount_paid)],
        ["Payment Date", _safe(payroll.payment_date)],
    ]

    y = table(pay_rows, left, y, [40 * mm, (right - left) - 40 * mm]) - 8 * mm

    pdf.setFillColor(colors.HexColor("#374151"))
    pdf.setFont("Helvetica-Bold", 10)
    pdf.drawString(left, y, "Net Salary in Words")
    pdf.setStrokeColor(colors.HexColor("#D1D5DB"))
    pdf.line(left, y - 2, right, y - 2)
    y -= 6 * mm

    pdf.setFillColor(colors.HexColor("#111827"))
    pdf.setFont("Helvetica", 10)
    pdf.drawString(left, y, inr_words(payroll.net_salary))
    y -= 12 * mm

    pdf.setFont("Helvetica", 10)
    pdf.drawString(left, y, "Authorized Signatory:")
    pdf.line(left, y - 4 * mm, left + 60 * mm, y - 4 * mm)
    pdf.drawString(right - 60 * mm, y, "Employee Signature:")
    pdf.line(right - 60 * mm, y - 4 * mm, right, y - 4 * mm)

    pdf.setFont("Helvetica", 8)
    pdf.setFillColor(colors.HexColor("#6B7280"))
    pdf.drawString(left, 12 * mm, f"Generated on: {datetime.now().strftime('%d/%m/%Y %H:%M')}")

    pdf.showPage()
    pdf.save()

    return response


# ============================================================
# PDF: INVOICE
# ============================================================

def render_invoice_pdf(invoice):
    """Render a Client invoice as a PDF and return an HttpResponse."""

    response = HttpResponse(content_type="application/pdf")
    response["Content-Disposition"] = (
        f'attachment; filename="Invoice_{invoice.invoice_number}.pdf"'
    )

    doc = SimpleDocTemplate(response, pagesize=A4)
    elements = []
    styles = getSampleStyleSheet()

    logo_field = getattr(invoice.company, "Company_logo", None)
    if logo_field and hasattr(logo_field, "path") and os.path.exists(logo_field.path):
        elements.append(Image(logo_field.path, width=80, height=80))

    elements.append(Paragraph("TAX INVOICE", styles["Title"]))
    elements.append(Spacer(1, 12))

    company_block = (
        f"<b>Company:</b> {invoice.company}<br/>"
        f"{invoice.company.Company_address or 'Company Address'}<br/>"
        f"<b>Phone:</b> {invoice.company.Company_Official_Contact or 'N/A'} | "
        f"<b>Email:</b> {invoice.company.Company_Official_Email or 'N/A'}"
    )
    elements.append(Paragraph(company_block, styles["Normal"]))
    elements.append(Spacer(1, 12))

    client_block = (
        f"<b>Client Name:</b> {invoice.client.name if invoice.client else 'N/A'}<br/>"
        f"{invoice.client.address if invoice.client else 'N/A'}"
    )
    elements.append(Paragraph(client_block, styles["Normal"]))
    elements.append(Spacer(1, 12))

    invoice_block = (
        f"<b>Invoice No:</b> {invoice.invoice_number}<br/>"
        f"<b>Invoice Date:</b> {invoice.created_at.strftime('%d-%m-%Y')}<br/>"
        f"<b>Due Date:</b> {invoice.due_date.strftime('%d-%m-%Y')}<br/>"
        f"<b>Status:</b> {invoice.status}"
    )
    elements.append(Paragraph(invoice_block, styles["Normal"]))
    elements.append(Spacer(1, 12))

    data = [
        ["Description", "Amount"],
        ["Base Amount", invoice.amount],
        [f"GST ({invoice.gst_percentage}%)", invoice.gst_amount],
        [f"TDS ({invoice.tds_percentage}%)", invoice.tds_amount],
        ["Total Tax", invoice.total_tax],
        ["Grand Total", invoice.grand_total],
    ]

    tbl = Table(data, colWidths=[300, 100])
    tbl.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, 0), colors.grey),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.whitesmoke),
        ("ALIGN", (0, 0), (-1, -1), "CENTER"),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("BOTTOMPADDING", (0, 0), (-1, 0), 12),
        ("BACKGROUND", (0, 1), (-1, -1), colors.beige),
        ("GRID", (0, 0), (-1, -1), 1, colors.black),
    ]))
    elements.append(tbl)
    elements.append(Spacer(1, 20))

    elements.append(Paragraph("Authorized Signatory", styles["Normal"]))
    elements.append(Spacer(1, 12))
    elements.append(Paragraph("_____________________________", styles["Normal"]))

    doc.build(elements)

    return response


# ============================================================
# EXCEL: EMPLOYEE LIST
# ============================================================

def employees_excel_response(employees):

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Employees"

    headers = [
        "Employee Code", "Name", "Email", "Phone Number", "Department", "Designation",
        "Aadhaar Number", "PAN Number", "PF Number", "ESI Number",
        "Basic Salary", "Per Day Salary", "Overtime Rate", "Bank Name",
        "Account Number", "IFSC Code", "Account Type", "UPI ID", "Bank Verified",
    ]

    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center")

    for emp in employees:

        per_day_salary = float(emp.Basic_Salary) / 30 if emp.Basic_Salary else 0
        bank_accounts = emp.bank_accounts.all()

        if bank_accounts.exists():

            for idx, bank in enumerate(bank_accounts):

                if idx == 0:
                    ws.append([
                        emp.employee_code, emp.name, emp.email, emp.phone_number,
                        emp.Department, emp.Designation,
                        emp.aadhaar_number, emp.pan_number, emp.pf_number, emp.esi_number,
                        float(emp.Basic_Salary or 0), per_day_salary,
                        float(emp.Overtime_Rate or 0),
                        bank.bank_name, bank.account_number, bank.ifsc_code,
                        bank.account_type, bank.upi_id,
                        "Yes" if bank.is_verified else "No",
                    ])
                else:
                    ws.append([
                        "", "", "", "", "", "", "", "", "", "", "", "", "",
                        bank.bank_name, bank.account_number, bank.ifsc_code,
                        bank.account_type, bank.upi_id,
                        "Yes" if bank.is_verified else "No",
                    ])

        else:
            ws.append([
                emp.employee_code, emp.name, emp.email, emp.phone_number,
                emp.Department, emp.Designation,
                emp.aadhaar_number, emp.pan_number, emp.pf_number, emp.esi_number,
                float(emp.Basic_Salary or 0), per_day_salary,
                float(emp.Overtime_Rate or 0),
                "", "", "", "", "", "",
            ])

    response = HttpResponse(
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response["Content-Disposition"] = "attachment; filename=employees.xlsx"
    wb.save(response)

    return response


# ============================================================
# EXCEL: ONE EMPLOYEE'S MONTHLY ATTENDANCE
# ============================================================

def monthly_attendance_excel_response(employee, year, month):

    from Employee.models import AttendanceEmp

    attendance_qs = AttendanceEmp.objects.filter(
        Employee_ID=employee.employee_code,
        date__year=year,
        date__month=month,
    ).order_by("date")

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = f"{employee.name}_{month}_{year}"[:31]

    headers = ["Date", "Status", "Total Working Hours", "Comment"]

    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center")

    for row_num, att in enumerate(attendance_qs, 2):
        ws.cell(row=row_num, column=1, value=att.date.strftime("%Y-%m-%d") if att.date else "")
        ws.cell(row=row_num, column=2, value=att.status or "")
        ws.cell(row=row_num, column=3, value=att.total_working_hours or "")
        ws.cell(row=row_num, column=4, value=att.comment or "")

    for col in ws.columns:
        max_length = max((len(str(c.value)) for c in col if c.value), default=0)
        ws.column_dimensions[col[0].column_letter].width = max_length + 5

    filename = f"{employee.name}_Attendance_{month}_{year}.xlsx"
    response = HttpResponse(
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response["Content-Disposition"] = f"attachment; filename={filename}"
    wb.save(response)

    return response


# ============================================================
# EXCEL: COMPANY-WIDE ATTENDANCE + PAYROLL FOR A MONTH
# ============================================================

def attendance_payroll_excel_response(company_dt, month, year):

    from Employee.models import EmployeeDT, AttendanceEmp

    month = int(month)
    year = int(year)

    employees = EmployeeDT.objects.filter(company=company_dt, is_active=True)
    days_in_month = calendar.monthrange(year, month)[1]

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Attendance Payroll"

    headers = ["SN", "Name"] + [str(d) for d in range(1, days_in_month + 1)] + [
        "Total Days", "Present", "Half Days",
        "Basic Salary", "Gross Salary", "PF", "TDS", "ESI", "Net Salary",
    ]
    ws.append(headers)

    for i, emp in enumerate(employees, start=1):

        row = [i, emp.name]
        present_count = 0
        half_days = 0

        for day in range(1, days_in_month + 1):

            att = AttendanceEmp.objects.filter(
                Employee_ID=emp.employee_code,
                date=date(year, month, day),
            ).first()

            if not att:
                row.append("A")
                continue

            if att.status == "Present":
                row.append("P")
                present_count += 1
            elif att.status == "Half Day":
                row.append("H")
                half_days += 1
            elif att.status == "Holiday":
                row.append("H")
            else:
                row.append("A")

        basic_salary = emp.Basic_Salary or Decimal(0)
        per_day_salary = basic_salary / Decimal(days_in_month)

        full_salary = per_day_salary * present_count
        half_salary = (per_day_salary / 2) * half_days
        gross_salary = full_salary + half_salary

        pf = gross_salary * ((emp.PF_Percentage or 0) / Decimal(100))
        tds = gross_salary * ((emp.TDS_Percentage or 0) / Decimal(100))
        esi = gross_salary * ((emp.ESI_Percentage or 0) / Decimal(100))

        net_salary = gross_salary - (pf + tds + esi)

        row += [
            days_in_month, present_count, half_days,
            float(basic_salary), float(gross_salary),
            float(pf), float(tds), float(esi), float(net_salary),
        ]

        ws.append(row)

    filename = f"Attendance_Payroll_{month}_{year}.xlsx"
    response = HttpResponse(
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response["Content-Disposition"] = f"attachment; filename={filename}"
    wb.save(response)

    return response


# ============================================================
# ADDITIVE: salary slip PDF now uses the same bordered template as Payroll Hub
# (PayrollHub.engine.payslip_pdf). Defined AFTER the original render_salary_slip_pdf above, so the later
# definition is the one imported by Company/views_api.py (/api/payroll/<employee_id>/<month>/<year>/slip/).
# Released snapshot is preferred; otherwise the live computed payslip. The original layout stays above, untouched.
# ============================================================
def render_salary_slip_pdf(employee, payroll):
    from PayrollHub import engine as hub_en
    from PayrollHub.common import f as hub_f
    from PayrollHub.models import Payslip as HubPayslip

    company = employee.company
    month, year = payroll.month, payroll.year
    slip = hub_en.compute_payslip(company, employee, month, year, prow=payroll)
    if slip is None:
        slip = {"period": f"{month}/{year}", "earnings": [], "deductions": [], "gross_salary": 0,
                "total_deductions": 0, "net_payable": 0}
    snap = HubPayslip.objects.filter(company=company, employee=employee, month=month, year=year,
                                     status="RELEASED").first()
    if snap:
        slip = slip | {"gross_salary": hub_f(snap.gross_salary), "total_deductions": hub_f(snap.total_deductions),
                       "net_payable": hub_f(snap.net_payable), "earnings": snap.breakup.get("earnings", []),
                       "deductions": snap.breakup.get("deductions", [])}
    response = HttpResponse(hub_en.payslip_pdf(company, employee, slip), content_type="application/pdf")
    response["Content-Disposition"] = (
        f'attachment; filename="salary_slip_{employee.employee_code}_{month}_{year}.pdf"')
    return response
    
    