"""
CA Automator — bulk Excel import.

Every module that supports import gets a predefined template: one or more
sheets, each a list of (json_key, header_label, type, required) columns.
build_template() writes that layout as a ready-to-fill workbook (an
instructions row, styled headers, a couple of worked sample rows per sheet);
parse_upload() reads a filled one back into rows, one result per sheet.

Columns are matched by header text, not position — case-insensitive and
order-independent — so a user who reorders columns or deletes the
instructions row still gets a sane result. Sheets are matched by tab name
(also case-insensitive); a module can define several, as Due Diligence does
for its seventeen registers, and any sheet the user didn't fill in is simply
reported as not found rather than failing the whole upload. A row missing a
required value is skipped with a message rather than dropped silently; a
completely blank row is skipped without comment.
"""
import io

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, PatternFill

_HEADER_FILL = PatternFill("solid", fgColor="16211C")
_HEADER_FONT = Font(color="FBFCFA", bold=True, size=10)
_NOTE_FONT = Font(italic=True, size=9, color="5C6B60")

# Fiscal-month order (Shrawan = month 1, the start of the Nepali income year) —
# matches TRANCHE_BY_MONTH in engines.py and NEPALI_MONTHS in app.js.
NEPALI_MONTHS = ["Shrawan", "Bhadra", "Ashwin", "Kartik", "Mangsir", "Poush",
                 "Magh", "Falgun", "Chaitra", "Baishakh", "Jestha", "Ashadh"]
_MONTH_LOOKUP = {}
for _i, _name in enumerate(NEPALI_MONTHS, 1):
    _MONTH_LOOKUP[_name.lower()] = _i
    _MONTH_LOOKUP[str(_i)] = _i


def _sheet(key, title, instructions, columns, samples=None):
    return {"key": key, "sheet": title, "instructions": instructions,
            "columns": columns, "samples": samples or []}


# ------------------------------------------------------------- single-sheet modules

IMPORT_SPECS = {
    "fin_statements": {"sheets": [_sheet(
        "tb", "Trial balance",
        "One row per ledger line. Opening balance is optional — fill it in if you want an "
        "opening-tie check and richer notes; leave it blank for a closing-only trial balance. "
        "Leave the financial-statement head to be suggested automatically — set it in the app "
        "afterwards if the suggestion is wrong.",
        [("name", "Ledger head", "str", True),
         ("group", "Group / schedule", "str", False),
         ("opening_dr", "Opening balance — debit", "float", False),
         ("opening_cr", "Opening balance — credit", "float", False),
         ("dr", "Debit", "float", False),
         ("cr", "Credit", "float", False),
         ("prev", "Previous year (optional)", "float", False)],
        [{"name": "Cash in hand", "group": "Cash and bank", "opening_dr": 98000, "opening_cr": 0,
          "dr": 125000, "cr": 0, "prev": 98000},
         {"name": "Sales", "group": "Revenue", "opening_dr": 0, "opening_cr": 0,
          "dr": 0, "cr": 4500000, "prev": 3800000}],
    )]},

    "depreciation": {"sheets": [_sheet(
        "additions", "Additions",
        "One row per asset added during the year. Pool is A-E; month is the Nepali fiscal "
        "month of purchase (Shrawan = month 1, the start of the income year) — type the "
        "month name or its number 1-12.",
        [("pool", "Pool (A-E)", "str", True),
         ("desc", "Description", "str", True),
         ("amount", "Cost", "float", True),
         ("bs_month", "Month of purchase", "month", True)],
        [{"pool": "B", "desc": "Office laptops (4)", "amount": 320000, "bs_month": "Shrawan"},
         {"pool": "C", "desc": "Delivery van", "amount": 2800000, "bs_month": "Magh"}],
    )]},

    "salary_tds": {"sheets": [_sheet(
        "employees", "Employees",
        "One row per employee. Leave a column blank or zero if it does not apply — "
        "perquisites, foreign posting and retirement fields are all optional.",
        [("name", "Name", "str", True),
         ("designation", "Designation", "str", False),
         ("status", "Filing status (individual/couple)", "str", False),
         ("months", "Months this year", "float", False),
         ("basic_monthly", "Basic / month", "float", False),
         ("allowance_monthly", "Allowance / month", "float", False),
         ("other_annual", "Other allowances (annual)", "float", False),
         ("bonus_annual", "Bonus (annual)", "float", False),
         ("overtime_annual", "Overtime (annual)", "float", False),
         ("commission_annual", "Commission (annual)", "float", False),
         ("leave_encashment_annual", "Leave encashment (annual)", "float", False),
         ("ssf_contributor", "SSF contributor (Y/N)", "bool", False),
         ("vehicle_facility", "Vehicle facility (Y/N)", "bool", False),
         ("vehicle_cost", "Vehicle prescribed cost", "float", False),
         ("accommodation_facility", "Accommodation facility (Y/N)", "bool", False),
         ("accommodation_rent_paid", "Accommodation rent paid by employer", "float", False),
         ("employer_loan_amount", "Employer loan amount", "float", False),
         ("employer_loan_rate_charged", "Employer loan rate charged (e.g. 0.03)", "float", False),
         ("other_perquisites_annual", "Other perquisites (annual)", "float", False),
         ("is_foreign_posting", "Posted abroad (Y/N)", "bool", False),
         ("foreign_allowance_annual", "Foreign allowance (annual)", "float", False),
         ("is_retirement_year", "Separating this year (Y/N)", "bool", False),
         ("retirement_lumpsum_amount", "Retirement lump sum", "float", False),
         ("other_employer_income_annual", "Other employer's taxable income", "float", False),
         ("insurance_premium", "Life insurance premium", "float", False),
         ("health_premium", "Health insurance premium", "float", False),
         ("remote_area_category", "Remote area category (A-E)", "str", False),
         ("remote_area_allowance", "Remote area allowance received", "float", False),
         ("approved_medical_expense_annual", "Approved medical expense (annual)", "float", False),
         ("is_resident_woman", "Resident woman (Y/N)", "bool", False),
         ("is_senior_citizen", "Senior citizen (Y/N)", "bool", False),
         ("tds_already_deducted", "TDS already deducted", "float", False)],
        [{"name": "Sample Employee", "designation": "Accountant", "status": "individual", "months": 12,
          "basic_monthly": 60000, "allowance_monthly": 5000, "ssf_contributor": "Y"}],
    )]},

    "biz_tax": {"sheets": [_sheet(
        "adjustments", "Adjustments",
        "One row per book-to-tax adjustment. Type is either 'Addition' (added back to book "
        "profit — a disallowed expense) or 'Deduction' (an allowance that reduces it).",
        [("kind", "Type (Addition/Deduction)", "str", True),
         ("desc", "Particulars", "str", True),
         ("amount", "Amount", "float", True)],
        [{"kind": "Addition", "desc": "Personal / non-business expenses charged to P&L", "amount": 85000},
         {"kind": "Addition", "desc": "Provision for doubtful debts (not yet written off)", "amount": 120000},
         {"kind": "Deduction", "desc": "Prior year expense allowed on payment basis this year", "amount": 40000}],
    )]},

    "penalty": {"sheets": [_sheet(
        "shortfalls", "Instalment shortfalls",
        "One row per instalment (Poush/Chaitra/Ashadh) where estimated tax paid fell short "
        "of the required cumulative percentage.",
        [("label", "Instalment", "str", True),
         ("shortfall", "Shortfall amount", "float", True),
         ("months", "Months outstanding", "float", True)],
        [{"label": "Poush", "shortfall": 85000, "months": 6},
         {"label": "Chaitra", "shortfall": 40000, "months": 3}],
    )]},

    "fdi": {"sheets": [_sheet(
        "investors", "Investors",
        "One row per investor. Kind is FC (foreign company), FI (foreign individual), "
        "NC (Nepali company) or NI (Nepali individual).",
        [("kind", "Kind (FC/FI/NC/NI)", "str", True),
         ("name", "Investor name", "str", True),
         ("country", "Country", "str", False),
         ("amount", "Investment amount", "float", True),
         ("currency", "Currency", "str", False),
         ("source_of_funds", "Source of funds", "str", False)],
        [{"kind": "FC", "name": "Example Holdings Pte Ltd", "country": "Singapore",
          "amount": 500000, "currency": "USD", "source_of_funds": "Retained earnings"}],
    )]},

    "inventory": {"sheets": [
        _sheet(
            "raw_materials", "Raw materials",
            "One row per raw material item — add as many as needed. Costing method is fifo or "
            "weighted_avg. Opening quantity/rate can be left blank for a material with no opening balance.",
            [("name", "Material name", "str", True),
             ("unit", "Unit", "str", False),
             ("costing_method", "Costing method (fifo/weighted_avg)", "str", False),
             ("opening_qty", "Opening quantity", "float", False),
             ("opening_rate", "Opening rate per unit", "float", False),
             ("replacement_cost_per_unit", "Replacement cost per unit (optional, for NRV check)", "float", False),
             ("fg_expected_below_cost", "Related finished goods expected to sell below cost (Y/N)", "bool", False)],
            [{"name": "Steel sheet", "unit": "kg", "costing_method": "weighted_avg", "opening_qty": 100, "opening_rate": 50}],
        ),
        _sheet(
            "rm_purchases", "RM purchases",
            "One row per purchase. Material name must match a row on the Raw materials sheet exactly.",
            [("material", "Material name", "str", True),
             ("date", "Date (AD, YYYY-MM-DD)", "str", True),
             ("qty", "Quantity", "float", True),
             ("rate", "Rate per unit", "float", True),
             ("freight", "Freight (optional)", "float", False),
             ("duty", "Import duty (optional)", "float", False),
             ("other_cost", "Other landed cost (optional)", "float", False),
             ("discount", "Discount (optional)", "float", False),
             ("desc", "Description (optional)", "str", False)],
            [{"material": "Steel sheet", "date": "2082-04-10", "qty": 200, "rate": 55, "freight": 1000, "duty": 500}],
        ),
        _sheet(
            "rm_issues", "RM issues",
            "One row per issue to production. Material name must match a row on the Raw materials sheet exactly.",
            [("material", "Material name", "str", True),
             ("date", "Date (AD, YYYY-MM-DD)", "str", True),
             ("qty", "Quantity", "float", True),
             ("desc", "Description (optional)", "str", False)],
            [{"material": "Steel sheet", "date": "2082-04-20", "qty": 250, "desc": "Issued to Process 1"}],
        ),
        _sheet(
            "fg_sales", "FG sales",
            "One row per sale of finished goods, at cost (costing method set on the FG side of the module).",
            [("date", "Date (AD, YYYY-MM-DD)", "str", True),
             ("qty", "Quantity", "float", True),
             ("desc", "Description (optional)", "str", False)],
            [{"date": "2082-05-01", "qty": 700, "desc": "Sold to distributor"}],
        ),
    ]},

    "tds": {"sheets": [
        _sheet(
            "entries", "Payments",
            "One row per payment made as a withholding agent. Category must match one of the module's "
            "category slugs exactly (see the Rate Master tds domain, e.g. contract, consultancy_registered, "
            "consultancy_unregistered, commission, interest, dividend, nonresident, meeting_fee, freight, "
            "vehicle_rent, royalty, business_promotion, rent) — leave rate_override blank to use that "
            "category's Rate Master rate.",
            [("payee_name", "Payee name", "str", True),
             ("payee_pan", "Payee PAN (optional)", "str", False),
             ("category", "Category slug", "str", True),
             ("description", "Description (optional)", "str", False),
             ("gross_amount", "Gross amount", "float", True),
             ("rate_override", "Rate override % (optional)", "float", False),
             ("payment_date", "Payment date (AD, YYYY-MM-DD)", "str", True),
             ("deposited", "Deposited (Y/N)", "bool", False),
             ("deposit_date", "Deposit date (optional)", "str", False),
             ("deposit_ref", "Deposit reference (optional)", "str", False)],
            [{"payee_name": "ABC Consultants Pvt Ltd", "payee_pan": "123456789", "category": "consultancy_registered",
              "gross_amount": 100000, "payment_date": "2082-04-10"}],
        ),
    ]},

    "fixed_assets": {"sheets": [
        _sheet(
            "classes", "Asset classes",
            "One row per asset class ('type') — add as many as needed, e.g. Furniture, Vehicles, "
            "Computer & IT equipment. Method is slm or wdv. For WDV give a rate; for SLM give a useful "
            "life and residual %. The opening balance columns are only for a legacy amount not yet "
            "broken down into individual assets — leave blank if every asset is entered on the Assets sheet.",
            [("key", "Class key (short code, e.g. furniture)", "str", True),
             ("label", "Class label", "str", True),
             ("method", "Method (slm/wdv)", "str", True),
             ("rate_pct", "WDV rate % p.a.", "float", False),
             ("useful_life_years", "SLM useful life (years)", "float", False),
             ("residual_pct", "SLM residual value %", "float", False),
             ("opening_block_nbv", "Unallocated opening NBV (optional)", "float", False),
             ("opening_block_remaining_life_years", "Unallocated block — remaining SLM life (years)", "float", False),
             ("opening_block_residual_value", "Unallocated block — SLM residual value (amount)", "float", False)],
            [{"key": "furniture", "label": "Furniture & Fixtures", "method": "wdv", "rate_pct": 15},
             {"key": "vehicle", "label": "Vehicles", "method": "slm", "useful_life_years": 5, "residual_pct": 10}],
        ),
        _sheet(
            "assets", "Assets",
            "One row per asset — any number, added one by one or in bulk. Class key must match a row "
            "on the Asset classes sheet exactly. Status is existing (carried forward from last year — "
            "give the opening NBV) or addition (bought this year — give the acquisition date and cost). "
            "Method/rate/life/residual are only needed if this asset's policy differs from its class's default.",
            [("class_key", "Class key", "str", True),
             ("code", "Asset code", "str", False),
             ("name", "Asset name / description", "str", True),
             ("status", "Status (existing/addition)", "str", True),
             ("acquisition_date", "Acquisition date (AD, YYYY-MM-DD) — for additions", "str", False),
             ("cost", "Cost — for additions", "float", False),
             ("additional_cost", "Additional cost (installation, etc.)", "float", False),
             ("opening_nbv", "Opening NBV — for existing assets", "float", False),
             ("opening_nbv_remaining_life_years", "Remaining SLM life (years) — existing SLM assets", "float", False),
             ("opening_nbv_residual_value", "SLM residual value (amount) — existing SLM assets", "float", False),
             ("method", "Method override (slm/wdv, optional)", "str", False),
             ("rate_pct", "WDV rate % override (optional)", "float", False),
             ("useful_life_years", "SLM useful life override (optional)", "float", False),
             ("residual_pct", "SLM residual % override (optional)", "float", False),
             ("business_use_pct", "Business use % (optional, default 100)", "float", False),
             ("disposal_date", "Disposal date (AD, YYYY-MM-DD, optional)", "str", False),
             ("disposal_proceeds", "Disposal proceeds (optional)", "float", False)],
            [{"class_key": "furniture", "code": "FUR-001", "name": "Old desks", "status": "existing", "opening_nbv": 100000},
             {"class_key": "furniture", "code": "FUR-002", "name": "New chairs", "status": "addition",
              "acquisition_date": "2082-10-01", "cost": 60000}],
        ),
    ]},
}

# ------------------------------------------------------------- due diligence registers
# Seventeen registers, one workbook sheet each, matching DD_GRID / regTitles in app.js
# exactly — the key, column set and order must stay in lockstep with that file since
# the parsed rows are dropped straight into payload.g[key].

_DD_YESNO = " (Yes/No)"

_DD_SHEETS = [
    _sheet("dr", "Data room register",
           "One row per requested document.",
           [("doc", "Document", "str", True), ("cat", "Set (Financial/Tax/Legal/Other)", "str", False),
            ("per", "Period", "str", False), ("crit", "Critical" + _DD_YESNO, "yesno", False),
            ("status", "Status (Pending/Partial/Received/Not applicable)", "str", False),
            ("recd", "Received", "str", False), ("src", "Source", "str", False), ("rem", "Remarks", "str", False)],
           [{"doc": "Audited financial statements — 3 years", "cat": "Financial", "per": "FY 2080-83",
             "crit": "Yes", "status": "Received"}]),
    _sheet("conc", "Customer concentration",
           "One row per top customer.",
           [("cust", "Customer", "str", True), ("rel", "Relationship", "str", False),
            ("amt", "Amount", "float", False), ("recv", "Receivable", "float", False),
            ("yrs", "Years as customer", "float", False), ("rem", "Remarks", "str", False)], []),
    _sheet("stream", "Revenue streams",
           "One row per revenue stream / product line.",
           [("name", "Revenue stream", "str", True), ("amt", "Amount", "float", False),
            ("gm", "Gross margin %", "float", False), ("rem", "Remarks", "str", False)], []),
    _sheet("norm", "EBITDA normalisation",
           "One row per adjustment to reported EBITDA.",
           [("fy", "FY", "str", False), ("desc", "Description", "str", True), ("amt", "Amount", "float", False),
            ("conf", "Confidence (High/Medium/Low)", "str", False), ("ev", "Evidence", "str", False),
            ("rem", "Remarks", "str", False)], []),
    _sheet("wcm", "Monthly working capital",
           "Twelve rows, one per month, for the seasonality check.",
           [("m", "Month", "str", True), ("inv", "Inventory", "float", False), ("tr", "Receivables", "float", False),
            ("oca", "Other current assets", "float", False), ("tp", "Payables", "float", False),
            ("ocl", "Other current liabilities", "float", False)], []),
    _sheet("debt", "Loan register",
           "One row per loan or facility.",
           [("lender", "Lender", "str", True), ("ty", "Type", "str", False), ("sanc", "Sanctioned", "float", False),
            ("out", "Outstanding", "float", False), ("rate", "Rate %", "float", False), ("mat", "Maturity", "str", False),
            ("cov", "Covenant status (Not tested/Compliant/Breached/No covenant)", "str", False),
            ("coc", "Change of control (Unknown/No/Yes-consent needed)", "str", False),
            ("pg", "Promoter guarantee (No/Yes/Unknown)", "str", False)], []),
    _sheet("dlike", "Debt-like items",
           "One row per debt-like or cash-like item affecting the deal bridge.",
           [("item", "Item", "str", True), ("cat", "Category (Debt-like/Cash-like/Off balance sheet/Neither)", "str", False),
            ("amt", "Amount", "float", False), ("basis", "Basis", "str", False), ("rem", "Remarks", "str", False)], []),
    _sheet("rp", "Related-party register",
           "One row per related-party balance or transaction.",
           [("party", "Party", "str", True), ("rel", "Relationship", "str", False), ("nat", "Nature", "str", False),
            ("amt", "Amount", "float", False), ("bal", "Balance", "float", False),
            ("arm", "Arm's length (Not assessed/Yes/No/Partly)", "str", False),
            ("disc", "NAS 24 disclosure (Not disclosed/Disclosed/Partial)", "str", False),
            ("act", "Action", "str", False)], []),
    _sheet("oneoff", "One-off items",
           "One row per non-recurring item.",
           [("fy", "FY", "str", False), ("desc", "Description", "str", True),
            ("nat", "Nature", "str", False), ("amt", "Amount", "float", False),
            ("inc", "In normalisation" + _DD_YESNO, "yesno", False), ("ev", "Evidence", "str", False),
            ("rem", "Remarks", "str", False)], []),
    _sheet("cont", "Contingent liabilities",
           "One row per contingent liability or off-balance-sheet exposure.",
           [("item", "Item", "str", True), ("ty", "Type", "str", False), ("amt", "Gross exposure", "float", False),
            ("prov", "Provided", "float", False), ("prob", "Probability (Probable/Possible/Remote)", "str", False),
            ("stat", "Status / forum", "str", False), ("rem", "Remarks", "str", False)], []),
    _sheet("it", "Income tax by year",
           "One row per fiscal year.",
           [("fy", "FY", "str", True), ("filed", "Filed (Filed on time/Filed late/Not filed/Unknown)", "str", False),
            ("inc", "Assessable income", "float", False), ("tself", "Tax self-assessed", "float", False),
            ("tpaid", "Tax paid", "float", False),
            ("stat", "Status (Self-assessed/Under audit/Amended u/s 101/...)", "str", False),
            ("dis", "Estimated disallowance", "float", False), ("mo", "Months", "float", False),
            ("rem", "Remarks", "str", False)], []),
    _sheet("tds", "TDS register",
           "One row per payment head / section.",
           [("head", "Payment head", "str", True), ("sec", "Section", "str", False), ("fy", "FY", "str", False),
            ("base", "Base", "float", False), ("applied", "Applied %", "float", False),
            ("req", "Required %", "float", False), ("dep", "Deposited (Yes/No/Partial)", "str", False),
            ("mo", "Months", "float", False), ("rem", "Remarks", "str", False)], []),
    _sheet("vat", "VAT register",
           "One row per period.",
           [("per", "Period", "str", True), ("filed", "Filed (On time/Late/Not filed/Partly)", "str", False),
            ("late", "Late returns (count)", "float", False), ("sales", "Sales", "float", False),
            ("out", "Output VAT", "float", False), ("inp", "Input claimed", "float", False),
            ("dis", "Input at risk", "float", False), ("short", "Output under-declared", "float", False),
            ("mo", "Months", "float", False)], []),
    _sheet("notice", "Tax notices",
           "One row per tax notice or assessment.",
           [("ref", "Reference", "str", True), ("auth", "Authority", "str", False), ("fy", "FY", "str", False),
            ("subj", "Subject", "str", False), ("dem", "Demand", "float", False),
            ("dep", "Deposit made", "float", False), ("prob", "Loss probability %", "float", False),
            ("rem", "Remarks", "str", False)], []),
    _sheet("acct", "Accounting assessment",
           "One row per NFRS/NAS area tested.",
           [("std", "Standard", "str", True), ("area", "Area", "str", False), ("q", "Test", "str", False),
            ("status", "Status (Not assessed/Compliant/Deviation/Not applicable)", "str", False),
            ("find", "Finding", "str", False), ("pl", "P&L effect", "float", False),
            ("bs", "BS effect", "float", False), ("rag", "RAG (G/A/R/N)", "str", False)], []),
    _sheet("find", "Manual findings",
           "One row per manually-raised finding. Auto-raised findings appear in the risk "
           "register inside the app and do not need a row here.",
           [("ws", "Workstream (Data room/Financial DD/Tax DD/Accounting DD)", "str", True),
            ("title", "Title", "str", True), ("detail", "Detail", "str", False),
            ("qty", "Quantum", "float", False), ("lik", "Likelihood (1-5)", "float", False),
            ("imp", "Impact (1-5)", "float", False),
            ("prot", "Protection (Price adjustment/Indemnity/Escrow.../...)", "str", False),
            ("status", "Status (Open/Info awaited/Closed)", "str", False)], []),
    _sheet("rec", "Recommendations",
           "One row per recommendation.",
           [("area", "Area", "str", True), ("rec", "Recommendation", "str", True),
            ("prio", "Priority (High/Medium/Low)", "str", False),
            ("stage", "Stage (Pre-signing/Condition precedent/Completion/Post-completion)", "str", False),
            ("own", "Owner", "str", False)], []),
]
IMPORT_SPECS["dd"] = {"sheets": _DD_SHEETS}


def _coerce(value, kind):
    if value is None or value == "":
        return None
    if kind == "float":
        try:
            return float(value)
        except (TypeError, ValueError):
            return None
    if kind == "bool":
        if isinstance(value, bool):
            return value
        return str(value).strip().lower() in ("1", "true", "yes", "y")
    if kind == "yesno":
        # Unlike "bool" (a real True/False the engine tests), this feeds a
        # Yes/No dropdown whose stored value the engine compares as a string.
        if isinstance(value, bool):
            truthy = value
        else:
            truthy = str(value).strip().lower() in ("1", "true", "yes", "y")
        return "Yes" if truthy else "No"
    if kind == "month":
        return _MONTH_LOOKUP.get(str(value).strip().lower())
    return str(value).strip()


def _write_sheet(wb, sheet_spec):
    columns = sheet_spec["columns"]
    ws = wb.create_sheet(sheet_spec["sheet"][:31])

    note = ws.cell(row=1, column=1, value=sheet_spec["instructions"])
    note.font = _NOTE_FONT
    note.alignment = Alignment(wrap_text=True, vertical="top")
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max(1, len(columns)))
    ws.row_dimensions[1].height = 32

    for ci, (key, label, kind, required) in enumerate(columns, 1):
        c = ws.cell(row=2, column=ci, value=label + (" *" if required else ""))
        c.font = _HEADER_FONT
        c.fill = _HEADER_FILL
        ws.column_dimensions[c.column_letter].width = max(14, min(34, len(label) + 4))

    r = 3
    for sample in sheet_spec.get("samples", []):
        for ci, (key, label, kind, required) in enumerate(columns, 1):
            ws.cell(row=r, column=ci, value=sample.get(key, ""))
        r += 1


def build_template(module_key):
    spec = IMPORT_SPECS.get(module_key)
    if not spec:
        raise ValueError(f"No import template for {module_key}")
    wb = Workbook()
    wb.remove(wb.active)
    for sheet_spec in spec["sheets"]:
        _write_sheet(wb, sheet_spec)
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()


def _parse_sheet(ws, columns):
    labels_lower = {label.strip().lower(): key for key, label, kind, required in columns}
    kind_by_key = {key: kind for key, label, kind, required in columns}
    required_keys = [key for key, label, kind, required in columns if required]

    header_row_idx, col_map = None, {}
    for ridx in range(1, min(ws.max_row, 6) + 1):
        m = {}
        for cidx in range(1, ws.max_column + 1):
            val = ws.cell(row=ridx, column=cidx).value
            if val is None:
                continue
            key = labels_lower.get(str(val).strip().lower().rstrip(" *"))
            if key:
                m[cidx] = key
        if len(m) > len(col_map):
            header_row_idx, col_map = ridx, m
    if not col_map:
        return {"rows": [], "errors": ["Could not find a recognisable header row on this sheet — "
                                        "use the column names from the downloaded template."], "row_count": 0}

    rows, errors = [], []
    for ridx in range(header_row_idx + 1, ws.max_row + 1):
        raw = {col_map[c]: ws.cell(row=ridx, column=c).value for c in col_map}
        if all(v is None or str(v).strip() == "" for v in raw.values()):
            continue
        row = {key: _coerce(val, kind_by_key[key]) for key, val in raw.items()}
        missing = [key for key in required_keys if row.get(key) in (None, "")]
        if missing:
            errors.append(f"Row {ridx}: missing {', '.join(missing)} — skipped.")
            continue
        rows.append(row)

    return {"rows": rows, "errors": errors, "row_count": len(rows)}


def parse_upload(module_key, blob):
    spec = IMPORT_SPECS.get(module_key)
    if not spec:
        raise ValueError(f"No import format defined for {module_key}")

    try:
        wb = load_workbook(io.BytesIO(blob), data_only=True)
    except Exception:
        raise ValueError("That file could not be read as an Excel workbook (.xlsx).")
    sheet_by_title = {name.strip().lower(): name for name in wb.sheetnames}

    sheets, all_errors, total_rows, any_found = {}, [], 0, False
    for sheet_spec in spec["sheets"]:
        actual = sheet_by_title.get(sheet_spec["sheet"].strip().lower())
        if not actual:
            sheets[sheet_spec["key"]] = {"rows": [], "errors": [], "row_count": 0, "found": False}
            continue
        any_found = True
        parsed = _parse_sheet(wb[actual], sheet_spec["columns"])
        parsed["found"] = True
        sheets[sheet_spec["key"]] = parsed
        total_rows += parsed["row_count"]
        all_errors += [f"{sheet_spec['sheet']}: {e}" for e in parsed["errors"]]

    if not any_found:
        expected = ", ".join(s["sheet"] for s in spec["sheets"])
        raise ValueError(f"None of the expected sheet tabs were found ({expected}) — "
                          "use the downloaded template's sheet names.")

    return {"sheets": sheets, "row_count": total_rows, "errors": all_errors}
