"""Step F: Excel (.xlsx) export -- the zero-risk deliverable.

Columns: Date, Narration, Ref, Debit, Credit, Balance, Category, VoucherType.
Adds a Totals row, freezes the header, and styles the header. CAs can import
this into Tally via Excel-to-Tally utilities, so it's the safety net.
"""

from constants import voucher_type_for

HEADERS = ["Date", "Narration", "Ref", "Debit", "Credit", "Balance",
           "Category", "VoucherType", "Flags"]


def write_excel(transactions, path):
    """Write transactions to an .xlsx file at `path`. Returns the path."""
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
    from openpyxl.utils import get_column_letter

    wb = Workbook()
    ws = wb.active
    ws.title = "Transactions"

    header_fill = PatternFill("solid", fgColor="1A3C6E")
    header_font = Font(bold=True, color="FFFFFF")
    thin = Side(style="thin", color="CCCCCC")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    # Header row
    for col, name in enumerate(HEADERS, start=1):
        c = ws.cell(row=1, column=col, value=name)
        c.fill = header_fill
        c.font = header_font
        c.alignment = Alignment(horizontal="center")
        c.border = border

    total_debit = 0.0
    total_credit = 0.0
    for r, t in enumerate(transactions, start=2):
        debit = t.get("debit")
        credit = t.get("credit")
        total_debit += debit or 0
        total_credit += credit or 0
        vtype = t.get("voucher_type") or voucher_type_for(debit, credit, t.get("category"))
        flags = "; ".join(t.get("flags") or [])
        values = [
            t.get("date", ""),
            t.get("narration", ""),
            t.get("ref_no") or "",
            debit,
            credit,
            t.get("balance"),
            t.get("category", ""),
            vtype,
            flags,
        ]
        for col, v in enumerate(values, start=1):
            cell = ws.cell(row=r, column=col, value=v)
            cell.border = border
            if col in (4, 5, 6):  # amount columns
                cell.number_format = "#,##0.00"

    # Totals row
    total_row = len(transactions) + 2
    ws.cell(row=total_row, column=1, value="TOTAL").font = Font(bold=True)
    td = ws.cell(row=total_row, column=4, value=round(total_debit, 2))
    tc = ws.cell(row=total_row, column=5, value=round(total_credit, 2))
    for cell in (td, tc):
        cell.font = Font(bold=True)
        cell.number_format = "#,##0.00"

    # Column widths
    widths = [12, 52, 12, 14, 14, 16, 28, 12, 30]
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w

    ws.freeze_panes = "A2"
    wb.save(path)
    return path