invoice.py

import os, re, json, datetime, subprocess, sys, random, unicodedata, shutil
from typing import Dict, Any, Optional, Tuple
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.drawing.image import Image as XLImage  # FIX: needed for logo
from openpyxl.worksheet.page import PageMargins
from openpyxl.utils import get_column_letter
# top of file
from openpyxl.styles import Font
from openpyxl.styles import Border, Side, PatternFill, Alignment, Font

_THIN = Side(style="thin", color="000000")
_BORDER_ALL = Border(left=_THIN, right=_THIN, top=_THIN, bottom=_THIN)
_GRAY = "FFBFBFBF"   # slightly darker than hairline
_GREEN = "FF6FB643"  # your brand
_WHITE = "FFFFFFFF"

# Tune these to the template's designed page
PRINT_COLS = 11   # A..K typically
PRINT_ROWS = 70   # enough to include footer band
# ---------- initial paths for logo ----------
ASSETS = os.environ.get("INVOICE_ASSETS", "assets")
LOGO_PATH = os.path.join(ASSETS, "logo.png")

def logo_data_uri() -> str:
    try:
        import base64
        with open(LOGO_PATH, "rb") as f:
            data = base64.b64encode(f.read()).decode("ascii")
        return f"data:image/png;base64,{data}"
    except Exception:
        return ""

# ---------- optional libs ----------
docx_available = False
pdf_available = False
win32_available = False
try:
    import docx
    docx_available = True
except Exception:
    pass
try:
    import PyPDF2
    pdf_available = True
except Exception:
    pass
try:
    import win32com.client  # Windows only
    win32_available = True
except Exception:
    pass

# ---------- paths ----------
OUT_DIR = os.environ.get("INVOICE_OUT", "outputs")
os.makedirs(OUT_DIR, exist_ok=True)

TEMPLATE_JC = os.path.join(ASSETS, "JC Auditing Invoice Template.xlsx")
TEMPLATE_SPRINGY = os.path.join(ASSETS, "Springy Invoice Template.xlsx")
INPUTS_DOCX = os.path.join(ASSETS, "Invoice Inputs.docx")

# ---------- excel helpers ----------
def _border_range(ws, r1, c1, r2, c2):
    for r in range(r1, r2 + 1):
        for c in range(c1, c2 + 1):
            ws.cell(r, c).border = _BORDER_ALL

def _ensure_table_grid(ws, header_row, col, rows_visible=12):
    """Force visible borders for the item table so LO prints them."""
    c_first = col.get("item", 1)
    c_last  = col.get("price", max(col.values()))
    start = header_row + 1
    end = start + rows_visible
    # Give every cell a very light fill so LO doesn't drop empty borders
    for r in range(start, end + 1):
        for c in range(c_first, c_last + 1):
            ws.cell(r, c).fill = PatternFill("solid", fgColor=_WHITE)
    _border_range(ws, start, c_first, end, c_last)
    # Make header line a bit darker so it matches your template
    for c in range(c_first, c_last + 1):
        ws.cell(header_row, c).border = Border(bottom=Side(style="medium", color=_GRAY))

def _footer_band(ws, row_top, col_first=1, col_last=10):
    """Draw the green footer bar as cells (no shapes)."""
    # band 2 rows high
    for r in (row_top, row_top + 1):
        for c in range(col_first, col_last + 1):
            ws.cell(r, c).fill = PatternFill("solid", fgColor=_GREEN)
            ws.cell(r, c).border = Border(top=_THIN, bottom=_THIN)
    # centered white text in first row
    msg = "0417 664 190 | P.O. BOX 14, O’Halloran Hill, SA 5158 | www.springyconsultingservices.com"
    mid_col = (col_first + col_last) // 2
    ws.merge_cells(start_row=row_top, start_column=col_first, end_row=row_top, end_column=col_last)
    cell = ws.cell(row_top, col_first)
    cell.value = msg
    cell.font = Font(bold=True, color=_WHITE)
    cell.alignment = Alignment(horizontal="center", vertical="center")

reportlab_available = False
try:
    from reportlab.lib.pagesizes import A4
    from reportlab.pdfgen import canvas
    from reportlab.lib.units import mm
    from reportlab.lib import colors
    reportlab_available = True
except Exception:
    pass
def _render_pdf_reportlab(payload: Dict[str, Any], pdf_path: str) -> bool:
    if not reportlab_available:
        return False
    W, H = A4
    margin = 15 * mm
    c = canvas.Canvas(pdf_path, pagesize=A4)

    # Header with logo (if exists)
    y = H - margin
    if os.path.exists(LOGO_PATH):
        try:
            c.drawImage(LOGO_PATH, margin, y - 50, width=W - 2*margin, height=40, preserveAspectRatio=True, mask='auto')
            y -= 60
        except Exception:
            # Fallback to text header
            c.setFont("Helvetica-Bold", 20)
            c.setFillColor(colors.HexColor("#6FB643"))
            c.drawString(margin, y, "SPRINGY CONSULTING SERVICES")
            y -= 15
            c.setFont("Helvetica-Bold", 10)
            c.drawString(margin, y, "HEAVY VEHICLE AUDITING & COMPLIANCE")
            y -= 25
    else:
        # Text header
        c.setFont("Helvetica-Bold", 20)
        c.setFillColor(colors.HexColor("#6FB643"))
        c.drawString(margin, y, "SPRINGY CONSULTING SERVICES")
        y -= 15
        c.setFont("Helvetica-Bold", 10)
        c.drawString(margin, y, "HEAVY VEHICLE AUDITING & COMPLIANCE")
        y -= 25

    # Tax Invoice title
    c.setFillColor(colors.black)
    c.setFont("Helvetica-Bold", 16)
    c.drawString(margin, y, "Tax Invoice")
    y -= 25

    # Invoice details (right aligned)
    c.setFont("Helvetica", 10)
    meta_lines = [
        f"Invoice Date: {payload.get('invoice_date','')}",
        f"Invoice #: {payload.get('invoice_number','')}",
        "ABN: 646 382 464 92",
    ]
    for line in meta_lines:
        c.drawRightString(W - margin, y, line)
        y -= 12
    y -= 10

    # Get invoice type from payload
    inv_type = payload.get('inv_type', 'springy')  # Default to springy if not specified

    # Customer block - behavior changes based on invoice type
    c.setFont("Helvetica-Bold", 10)
    c.drawString(margin, y, f"Customer: {payload.get('customer','Customer')}")
    y -= 12
    
    # For Springy invoices - show customer details under Customer
    if inv_type == "springy":
        c.setFont("Helvetica", 9)
        for line in [payload.get("address",""), payload.get("email",""), payload.get("phone",""), payload.get("audit_date","")]:
            if line:
                c.drawString(margin, y, str(line))
                y -= 11
    y -= 15

    # Table header
    c.setFont("Helvetica-Bold", 10)
    cols = [("Item", margin), ("Description", margin + 50), ("Qty/Hours", margin + 300), ("Unit Price", margin + 380), ("Price", margin + 460)]
    
    # Draw header background
    c.setFillColor(colors.lightgrey)
    c.rect(margin, y-15, W-2*margin, 15, fill=1, stroke=1)
    c.setFillColor(colors.black)
    
    for title, x in cols:
        c.drawString(x + 3, y - 12, title)
    y -= 20

    # First table row - main item
    c.setFont("Helvetica", 10)
    modules = payload.get("modules", 1)
    unit = float(payload.get("unit_price", 0.0))
    
    # Draw table row border
    c.rect(margin, y-15, W-2*margin, 15, fill=0, stroke=1)
    
    c.drawString(margin + 15, y - 12, str(modules))
    c.drawString(margin + 53, y - 12, payload.get("audit_type","NHVR Audit"))
    c.drawString(margin + 315, y - 12, "1")
    c.drawRightString(margin + 430, y - 12, f"{unit:.2f}")
    c.drawRightString(margin + 510, y - 12, f"{unit:.2f}")
    y -= 20

    # For Third Party invoices - show customer details in table rows
    if inv_type == "third_party":
        customer_details = [
            f"{payload.get('customer','')} NHVR audit {payload.get('audit_date','')}".strip(),
            payload.get("address",""),
            payload.get("email",""),
            payload.get("phone","")
        ]
        
        for detail in customer_details:
            if detail:
                c.rect(margin, y-15, W-2*margin, 15, fill=0, stroke=1)
                c.setFont("Helvetica", 9)
                c.drawString(margin + 53, y - 12, detail)
                y -= 15
    
    # Add empty rows to match Excel table structure (show complete table)
    empty_rows = 8 - (4 if inv_type == "third_party" else 0)  # Adjust based on customer detail rows
    for i in range(empty_rows):
        c.rect(margin, y-15, W-2*margin, 15, fill=0, stroke=1)
        y -= 15

    # Third-party admin fee (if applicable)
    admin = float(payload.get("admin_fee", 0.0) or 0.0)
    if admin != 0:
        c.rect(margin, y-15, W-2*margin, 15, fill=0, stroke=1)
        c.setFont("Helvetica", 10)
        c.drawString(margin + 53, y - 12, "JC Auditing administration fee")
        c.drawString(margin + 315, y - 12, "1")
        c.drawRightString(margin + 510, y - 12, f"{admin:.2f}")
        y -= 20
    
    y -= 20

    # Payment information section (left side)
    payment_y = y - 80
    c.setFont("Helvetica-Bold", 10)
    c.drawString(margin, payment_y, "All payments can be made by direct deposit to the following")
    payment_y -= 20
    c.drawString(margin, payment_y, "NAB")
    payment_y -= 12
    c.setFont("Helvetica", 10)
    c.drawString(margin, payment_y, "BSB 085 005")
    payment_y -= 12
    c.drawString(margin, payment_y, "Account 898 164 211")
    payment_y -= 20
    c.drawString(margin, payment_y, "Invoice due in 14 days")
    payment_y -= 15
    c.drawString(margin, payment_y, "contact@springyconsultingservices.com")

    # Totals box (right side)
    box_x = W - margin - 160
    box_w = 160
    box_y = y - 80
    c.rect(box_x, box_y - 60, box_w, 60, stroke=1, fill=0)
    
    rows = [
        ("Invoice Subtotal", float(payload.get("subtotal", 0.0))),
        ("Tax Rate", "10%"),
        ("GST", float(payload.get("gst", 0.0))),
        ("Total", float(payload.get("total", 0.0))),
    ]
    
    ty = box_y - 15
    c.setFont("Helvetica", 9)
    for label, val in rows:
        c.drawString(box_x + 6, ty, label)
        if isinstance(val, (int, float)):
            c.drawRightString(box_x + box_w - 6, ty, f"{val:.2f}")
        else:
            c.drawRightString(box_x + box_w - 6, ty, str(val))
        ty -= 14

    # Thank you message
    c.setFont("Helvetica-Bold", 12)
    c.drawString(margin, payment_y - 25, "Thank you for your Business")

    # Footer band
    footer_y = 25 * mm
    c.setFillColor(colors.HexColor("#6FB643"))
    c.rect(0, footer_y - 8, W, 16, fill=1, stroke=0)
    c.setFillColor(colors.white)
    c.setFont("Helvetica-Bold", 10)
    c.drawCentredString(W/2, footer_y - 3, "0417 664 190 | P.O. BOX 14, O'Halloran Hill, SA 5158 | www.springyconsultingservices.com")

    c.showPage()
    c.save()
    return os.path.exists(pdf_path)

def _replace_token(ws, token: str, value: str):
    tok = str(token).strip().lower()
    for row in ws.iter_rows():
        for c in row:
            v = str(c.value).strip().lower() if c.value is not None else ""
            if v == tok:
                c.value = value

def _place_logo(ws):
    """Embed the header logo across the invoice header band if logo.png exists."""
    if os.path.exists(LOGO_PATH):
        img = XLImage(LOGO_PATH)
        img.height = 140   # larger
        img.width = 1100   # span full header
        ws.add_image(img, "A1")   # assumes header row starts at row 1

def _nz(v): return "" if v is None else v

def _named_cell(ws: Worksheet, name: str):
    try:
        dn = ws.parent.defined_names[name]
    except KeyError:
        return None
    for title, ref in dn.destinations:
        if title == ws.title:
            return ws[ref].cells[0]
    return None

def _set_named(ws: Worksheet, name: str, value) -> bool:
    c = _named_cell(ws, name)
    if c is None: return False
    c.value = value
    return True

def _find_row_exact(ws: Worksheet, label: str) -> Optional[int]:
    target = str(label).strip().lower()
    for r in ws.iter_rows(min_row=1, max_row=ws.max_row):
        for c in r:
            if str(c.value).strip().lower() == target:
                return c.row
    return None

def _find_cell(ws: Worksheet, needle: str):
    patt = needle.lower()
    for row in ws.iter_rows():
        for cell in row:
            v = str(cell.value).strip().lower() if cell.value is not None else ""
            if patt == v or patt in v: return cell
    return None

def _header_map(ws: Worksheet) -> Tuple[int, dict]:
    hdr = _find_cell(ws, "Description") or _find_cell(ws, "Item")
    if not hdr:
        raise RuntimeError("Table header not found (need 'Item'/'Description').")
    row = hdr.row
    cols = {}
    for j in range(1, ws.max_column + 1):
        txt = str(ws.cell(row=row, column=j).value or "").strip().lower()
        if txt == "item": cols["item"] = j
        if "description" in txt: cols["desc"] = j
        if "qty" in txt or "hours" in txt: cols["qty"] = j
        if "unit price" in txt: cols["unit"] = j
        if txt == "price" or "price" in txt: cols["price"] = j
    return row, cols

def _clear_data(ws: Worksheet, start_row: int, col_first: int, col_last: int):
    stop = _find_row_exact(ws, "Invoice Subtotal") or ws.max_row
    for r in range(start_row, max(start_row, stop)):
        for c in range(col_first, col_last + 1):
            ws.cell(row=r, column=c).value = None
    # clear named TP notes if present
    for nm in ["TP_NOTE1","TP_NOTE2","TP_NOTE3","TP_NOTE4"]:
        c = _named_cell(ws, nm)
        if c: c.value = None

def _apply_print_setup(ws: Worksheet):
    # Respect template Print_Area if present
    try:
        dn = ws.parent.defined_names.get('_xlnm.Print_Area') or ws.parent.defined_names.get('Print_Area')
        if dn:
            for title, ref in dn.destinations:
                if title == ws.title:
                    ws.print_area = ref
                    break
    except Exception:
        pass

    # If not set by template, cover the whole layout explicitly
    if not ws.print_area:
        ws.print_area = "A1:L110"  # <<< match the “L110” used above

    # Fit the whole area on a single A4 page
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ps = ws.page_setup
    ps.orientation = "portrait"
    ps.paperSize = ws.PAPERSIZE_A4
    ps.fitToWidth = 1
    ps.fitToHeight = 1
    ps.scale = None

    # Clean output
    ws.print_options.gridLines = False
    ws.sheet_view.showGridLines = False
    ws.page_margins = PageMargins(left=0.3, right=0.3, top=0.5, bottom=0.5, header=0.25, footer=0.25)

# ---------- date / numbering ----------
def today_str() -> str:
    if sys.platform == "win32":
        return datetime.date.today().strftime("%#d/%#m/%Y")
    return datetime.date.today().strftime("%-d/%-m/%Y")

def new_invoice_code(inv_type: str) -> str:
    prefix = "SPR" if inv_type == "springy" else "TP"
    return f"{prefix}#{random.randint(10000, 99999)}"

# ---------- filenames ----------
def _safe_filename(s: str) -> str:
    s = unicodedata.normalize("NFKD", s).encode("ascii", "ignore").decode()
    s = re.sub(r"[^\w\s.-]", "", s)
    s = re.sub(r"\s+", " ", s).strip()
    return s

def _friendly_pdf_name(inv_type: str, customer: str, dt: datetime.date) -> str:
    date_token = dt.strftime("%d%m%Y")  # DDMMYYYY
    head = "Springy Invoice "
    mid = "JCAuditing " if inv_type == "third_party" else ""
    tail = " NHVR Audit report "
    return _safe_filename(f"{head}{mid}{customer}{tail}{date_token}") + ".pdf"

# ---------- readers ----------
def read_pdf(path: str) -> str:
    if not pdf_available: return ""
    try:
        out = []
        with open(path, "rb") as f:
            r = PyPDF2.PdfReader(f)
            for p in r.pages:
                out.append(p.extract_text() or "")
        return "\n".join(out)
    except Exception:
        return ""

def read_docx_plain(path: str) -> str:
    if not docx_available:
        return ""  # consider logging: "python-docx not installed"
    try:
        d = docx.Document(path)
        parts = []

        # Body paragraphs
        parts += [p.text.strip() for p in d.paragraphs if p.text and p.text.strip()]

        # Tables: join first two cells as "Label: Value" to satisfy your regex
        for tbl in d.tables:
            for row in tbl.rows:
                cells = [c.text.strip() for c in row.cells]
                cells = [c for c in cells if c]  # drop empties
                if not cells:
                    continue
                if len(cells) >= 2:
                    parts.append(f"{cells[0]}: {cells[1]}")
                else:
                    parts.append(cells[0])

        # Headers and footers (often hold contact blocks)
        for s in d.sections:
            parts += [p.text.strip() for p in s.header.paragraphs if p.text and p.text.strip()]
            parts += [p.text.strip() for p in s.footer.paragraphs if p.text and p.text.strip()]

        # Normalize spacing so ^|\n anchors work
        text = "\n".join(parts)
        text = re.sub(r"[ \t]+", " ", text)
        text = re.sub(r"\r?\n[ \t]*", "\n", text)
        return text
    except Exception:
        return ""

def read_report(file_obj) -> str:
    if file_obj is None: return ""
    path = getattr(file_obj, "name", None) or (file_obj if isinstance(file_obj, str) else None)
    if not path: return ""
    ext = os.path.splitext(path)[1].lower()
    if ext == ".pdf": return read_pdf(path)
    if ext == ".docx": return read_docx_plain(path)
    return ""

# ---------- parsing (Page 1 tables) ----------
LAB = {
    "date": r"(?:^|\n)\s*date\s*of\s*audit\s*[:\-]?\s*(.+)",
    "name": r"(?:^|\n)\s*operator\s*name(?:\s*\(.*?legal\s*entity.*?\))?\s*[:\-]?\s*(.+)",
    "addr": r"(?:^|\n)\s*operator\s*business\s*address\s*[:\-]?\s*(.+)",
    "email": r"(?:^|\n)\s*email\s*address\s*[:\-]?\s*([^\s]+@[^\s]+)",
    "phone": r"(?:^|\n)\s*operator\s*telephone\s*number\s*[:\-]?\s*([\d\+\s\-()]+)",
}
_EMAIL_RE = r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"
_PHONE_RE = r"\+?\d[\d\s\-()]{7,}"

def _first(pat: str, text: str) -> Optional[str]:
    m = re.search(pat, text, re.I)
    if m:
        return (m.group(1) or "").strip()
    return None

def parse_audit_date(t: str) -> Optional[str]:
    v = _first(LAB["date"], t)
    if v: return v
    m = re.search(r"\b(\d{1,2}\s*(?:st|nd|rd|th)?\s+[A-Za-z]+\.?\s+\d{4})\b", t)
    if m: return m.group(1)
    m = re.search(r"\b(\d{1,2}/\d{1,2}/\d{2,4})\b", t)
    return m.group(1) if m else None

def parse_operator_name(t: str) -> Optional[str]:
    v = _first(LAB["name"], t)
    if v: return v
    v = _first(r"(?:^|\n)\s*(?:operator|customer|client|company\s*name)\s*[:\-]?\s*(.+)", t)
    return v

def parse_operator_address(t: str) -> Optional[str]:
    v = _first(LAB["addr"], t)
    if v: return v
    v = _first(r"(?:^|\n)\s*address\s*[:\-]?\s*(.+)", t)
    return v

def parse_operator_email(t: str) -> Optional[str]:
    v = _first(LAB["email"], t)
    if v: return v
    m = re.search(_EMAIL_RE, t)
    return m.group(0) if m else None

def parse_operator_phone(t: str) -> Optional[str]:
    v = _first(LAB["phone"], t)
    if v: return v
    m = re.search(_PHONE_RE, t)
    return m.group(0) if m else None

def parse_audit_type(t: str) -> str:
    choices = [
        "NHVR Maintenance Audit","NHVR Mass Audit","NHVR Fatigue Audit",
        "NHVR Maintenance & Mass Audit","NHVR Maintenance & Fatigue Audit",
        "NHVR Maintenance, Mass & Fatigue Audit","NHVR Mass & Fatigue Audit",
        "Accreditation Manual NHVR & WA Main Roads","Policy & Procedure Manual NHVR",
        "Policy & Procedure Manual WA Main roads","Compliance","Consulting",
        "WA Maintenance, Fatigue, Dimensions & Loading Audit",
        "WA Maintenance, Fatigue, Dimensions & Loading, Mass Audit",
        "Submission of NHVR audit summary report","Pre Trip Inspection Books",
    ]
    low = t.lower()
    for ch in choices:
        if ch.lower() in low: return ch
    m = re.search(r"NHVR\s+(Maintenance|Mass|Fatigue)", t, re.I)
    return f"NHVR {m.group(1).title()} Audit" if m else "NHVR Audit"

def parse_modules(t: str) -> int:
    m = re.search(r"\b([1-4])\s+modules?\b", t, re.I)
    if m: return int(m.group(1))
    at = parse_audit_type(t).lower()
    if "maintenance, mass & fatigue" in at: return 3
    if "&" in at:
        parts = [p for p in re.split(r"&|,", at) if any(k in p for k in ["maintenance","mass","fatigue"])]
        return max(1, min(4, len(parts)))
    return 1

# ---------- pricing ----------
def load_pricing() -> Dict[int, float]:
    tiers = {1: 650.0, 2: 750.0, 3: 850.0, 4: 950.0}
    if os.path.exists(INPUTS_DOCX) and docx_available:
        try:
            txt = read_docx_plain(INPUTS_DOCX)
            for m in re.finditer(r"\b([1-4])\s*Modules?\D+\$?\s*([0-9]{3,4})", txt, re.I):
                tiers[int(m.group(1))] = float(m.group(2))
        except Exception:
            pass
    return tiers

def compute_totals(modules: int, inv_type: str, unit: float) -> Tuple[float,float,float,float]:
    admin = -100.0 if inv_type == "third_party" else 0.0
    subtotal = unit + admin
    gst = round(subtotal * 0.10, 2)
    total = round(subtotal + gst, 2)
    return admin, subtotal, gst, total

# ---------- excel writer ----------
def write_invoice_to_xlsx(template_path: str, out_path: str, payload: Dict[str, Any], inv_type: str):
    wb = load_workbook(template_path)
    ws = wb.active

    # ---- 1) Header fields: try named ranges (old behavior), else tokens (new templates) ----
    if not _set_named(ws, "INVOICE_DATE", _nz(payload["invoice_date"])):
        c = _find_cell(ws, "Invoice Date");  c and ws.cell(c.row, c.column+1, _nz(payload["invoice_date"]))
    if not _set_named(ws, "INVOICE_NO", _nz(payload["invoice_number"])):
        c = _find_cell(ws, "Invoice #");     c and ws.cell(c.row, c.column+1, _nz(payload["invoice_number"]))
    if not _set_named(ws, "CUSTOMER", _nz(payload["customer"])):
        c = _find_cell(ws, "Customer");      c and ws.cell(c.row, c.column+1, _nz(payload["customer"]))
    _set_named(ws, "ABN_CELL", _nz(payload.get("abn", "646 382 464 92")))
    if inv_type == "springy":
        _set_named(ws, "EMAIL_CELL",   _nz(payload.get("email", "")))
        _set_named(ws, "PHONE_CELL",   _nz(payload.get("phone", "")))
        _set_named(ws, "ADDRESS_CELL", _nz(payload.get("address", "")))

    # token replacements (clean templates with placeholders)
    #_replace_token(ws, "CUSTOMER",     _nz(payload["customer"]))
    _replace_token(ws, "ADDRESS_CELL", _nz(payload.get("address", "")))
    _replace_token(ws, "EMAIL_CELL",   _nz(payload.get("email", "")))
    _replace_token(ws, "PHONE_CELL",   _nz(payload.get("phone", "")))
    _replace_token(ws, "INVOICE_DATE", _nz(payload["invoice_date"]))
    _replace_token(ws, "INVOICE_NO",   _nz(payload["invoice_number"]))
    _replace_token(ws, "ABN_CELL",     _nz(payload.get("abn", "646 382 464 92")))

    # ---- 2) Table region (clear, then write primary line) ----
    header_row, col = _header_map(ws)
    data_row = header_row + 1
    c_first = col.get("item", 1)
    c_last  = col.get("price", max(col.values()))
    _clear_data(ws, data_row, c_first, c_last)

    r = data_row
    ws.cell(row=r, column=c_first).value     = payload["modules"]
    ws.cell(row=r, column=col["desc"]).value = payload["audit_type"]
    ws.cell(row=r, column=col["qty"]).value  = 1
    ws.cell(row=r, column=col["unit"]).value = payload["unit_price"]
    ws.cell(row=r, column=col["price"]).value= payload["unit_price"]
    _ensure_table_grid(ws, header_row, col, rows_visible=12)

    # Also expose as tokens for very simple templates (optional)
    _replace_token(ws, "AUDIT_TYPE", _nz(payload.get("audit_type", "NHVR Audit")))
    _replace_token(ws, "MODULES",    str(payload.get("modules", "")))
    _replace_token(ws, "UNIT_PRICE", f"{payload.get('unit_price', 0):.2f}")

    # ---- 3) Third-party notes + admin fee ----
    if inv_type == "third_party":
        notes = [
            f"{payload['customer']} NHVR audit {payload.get('audit_date','')}".strip(),
            _nz(payload.get("address","")),
            _nz(payload.get("email","")),
            _nz(payload.get("phone","")),
        ]
        wrote_named = all(_set_named(ws, f"TP_NOTE{i+1}", notes[i]) for i in range(4))
        if not wrote_named:
            for i, txt in enumerate(notes, start=1):
                ws.cell(row=data_row + i, column=col["desc"]).value = txt
        if payload.get("admin_fee", 0):
            row_fee = data_row + 6
            ws.cell(row=row_fee, column=col["desc"]).value  = "JC Auditing administration fee"
            ws.cell(row=row_fee, column=col["qty"]).value   = 1
            ws.cell(row=row_fee, column=col["price"]).value = payload["admin_fee"]
            _replace_token(ws, "ADMIN_FEE", f"{payload.get('admin_fee', 0):.2f}")

    # ---- 4) Totals ----
    _set_named(ws, "SUBTOTAL_CELL", payload["subtotal"])
    _set_named(ws, "GST_CELL",      payload["gst"])
    _set_named(ws, "TOTAL_CELL",    payload["total"])
    _replace_token(ws, "SUBTOTAL",  f"{payload.get('subtotal', 0):.2f}")
    #_replace_token(ws, "GST",       f"{payload.get('gst', 0):.2f}")
    #_replace_token(ws, "TOTAL",     f"{payload.get('total', 0):.2f}")

    # ---- 5) Logo + print setup ----
    _place_logo(ws)
    _apply_print_setup(ws)

    # --- FORCE USED RANGE TO INCLUDE FULL TEMPLATE (so LO doesn't crop) ---
    ws["L110"].value = "•"                   # any tiny char
    ws["L110"].font = Font(color="FFFFFF", size=1)  # invisible in print
    # ----------------------------------------------------------------------

        # draw a footer bar ~ at the bottom of page
    footer_row = (ws.max_row if ws.max_row > 60 else 60)  # push low on page
    _footer_band(ws, footer_row, col_first=1, col_last=max(col.values()))

    # make sure it's inside the printed area
    ws.print_area = f"A1:{get_column_letter(max(col.values()))}{footer_row+1}"

    wb.save(out_path)
    return out_path

# ---------- xlsx -> pdf ----------
def _soffice_path() -> Optional[str]:
    candidates = [
        shutil.which("soffice"),
        "/Applications/LibreOffice.app/Contents/MacOS/soffice",
        "C:\\Program Files\\LibreOffice\\program\\soffice.exe",
        "C:\\Program Files (x86)\\LibreOffice\\program\\soffice.exe",
    ]
    return next((p for p in candidates if p and os.path.exists(p)), None)

def _convert_with_libreoffice(xlsx_path: str, pdf_path: str) -> bool:
    soffice = _soffice_path()
    if not soffice:
        return False
    try:
        # calc_pdf_Export with defaults reliably keeps drawing layer
        cmd = [
            soffice, "--headless", "--nologo", "--nofirststartwizard",
            "--convert-to", "pdf:calc_pdf_Export",
            "--outdir", os.path.dirname(pdf_path),
            os.path.abspath(xlsx_path),
        ]
        r = subprocess.run(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, timeout=90)
        out_pdf = os.path.splitext(os.path.abspath(xlsx_path))[0] + ".pdf"
        if r.returncode == 0 and os.path.exists(out_pdf):
            os.replace(out_pdf, pdf_path)
            return True
    except Exception:
        pass
    return False


def _convert_with_excel_win32(xlsx_path: str, pdf_path: str) -> bool:
    if not win32_available: return False
    try:
        excel = win32com.client.DispatchEx("Excel.Application")
        excel.Visible = False
        wb = excel.Workbooks.Open(os.path.abspath(xlsx_path))
        for sh in wb.Worksheets:
            sh.PageSetup.Zoom = False
            sh.PageSetup.FitToPagesWide = 1
            sh.PageSetup.FitToPagesTall = False
        wb.ExportAsFixedFormat(0, os.path.abspath(pdf_path))
        wb.Close(False); excel.Quit()
        return os.path.exists(pdf_path)
    except Exception:
        try: excel.Quit()
        except Exception: pass
        return False

def xlsx_to_pdf(xlsx_path: str, pdf_path: str) -> str:
    # Try native converters first
    if _convert_with_excel_win32(xlsx_path, pdf_path): return pdf_path
    if _convert_with_libreoffice(xlsx_path, pdf_path): return pdf_path
    # Fallback: draw a clean PDF with ReportLab
    if _render_pdf_reportlab(_last_payload_cache or {}, pdf_path):
        return pdf_path
    return ""  # no PDF available

# ---------- API ----------
_last_payload_cache: Dict[str, Any] = {}

def do_parse(file_obj):
    t = read_report(file_obj)
    inv_type = "third_party" if re.search(r"\b(third[- ]?party|kick ?back|referral|jc auditing)\b", t, re.I) else "springy"
    modules = parse_modules(t) or 1
    audit_type = parse_audit_type(t)
    audit_date = parse_audit_date(t) or ""
    name = parse_operator_name(t) or ""
    address = parse_operator_address(t) or ""
    email = parse_operator_email(t) or ""
    phone = parse_operator_phone(t) or ""
    meta = json.dumps({
        "detected_type":inv_type,
        "modules":modules,
        "audit_type":audit_type,
        "audit_date":audit_date
    }, ensure_ascii=False)
    return meta, inv_type, modules, audit_type, audit_date, name, address, email, phone

def do_generate(file_obj, inv_type, modules, audit_type, audit_date, name, address, email, phone):
    tiers = load_pricing()
    unit = tiers.get(int(modules or 1), 650.0)
    inv_num = new_invoice_code(inv_type)
    inv_dt = today_str()
    admin, subtotal, gst, total = compute_totals(int(modules or 1), inv_type, unit)

    # In do_generate function, add this line to the payload:
    payload = {
        "invoice_number": inv_num,
        "invoice_date": inv_dt,
        "customer": name or "Customer",
        "address": address or "",
        "email": email or "",
        "phone": phone or "",
        "audit_type": audit_type or "NHVR Audit",
        "modules": int(modules or 1),
        "unit_price": unit,
        "admin_fee": admin,
        "subtotal": subtotal,
        "gst": gst,
        "total": total,
        "tax_rate": "10%",
        "abn": "646 382 464 92",
        "audit_date": audit_date or "",
        "inv_type": inv_type,  # <-- ADD THIS LINE
    }
    global _last_payload_cache
    _last_payload_cache = payload.copy()

    template = TEMPLATE_JC if inv_type == "third_party" else TEMPLATE_SPRINGY
    excel_out = os.path.join(OUT_DIR, f"{inv_num}.xlsx")
    write_invoice_to_xlsx(template, excel_out, payload, inv_type)

    pdf_filename = _friendly_pdf_name(inv_type, payload["customer"], datetime.date.today())
    pdf_out = os.path.join(OUT_DIR, pdf_filename)
    produced = xlsx_to_pdf(excel_out, pdf_out)
    pdf_final = produced if produced and os.path.exists(produced) else None

    meta = json.dumps({
        "invoice_number": inv_num, "invoice_date": inv_dt, "type": inv_type,
        "modules": modules, "subtotal": subtotal, "gst": gst, "total": total,
        "pdf": pdf_filename if pdf_final else ""
    }, ensure_ascii=False)
    return meta, excel_out, pdf_final