"""
make_sample_pdf.py — build a realistic bank-statement PDF from the sample xlsx.

This gives us a *test input* (a text-based PDF that looks like a real statement)
so we can prove the Python-first converter works end-to-end.

It reads the transactions out of Sample-CSV-Bank-Statement.xlsx, sorts them
chronologically, recomputes a clean running balance from a $0.00 opening, and
renders a header + a ruled transaction table.
"""
from __future__ import annotations

import datetime as dt
from pathlib import Path

import openpyxl
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib.units import mm
from reportlab.platypus import (
    SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle,
)

HERE = Path(__file__).resolve().parent
XLSX = HERE.parent / "Sample-CSV-Bank-Statement.xlsx"
OUT_PDF = HERE / "sample_statement.pdf"


def _to_float(v):
    if v is None or v == "":
        return None
    try:
        return float(str(v).replace(",", "").replace("$", "").strip())
    except ValueError:
        return None


def load_transactions(xlsx_path: Path):
    wb = openpyxl.load_workbook(xlsx_path, data_only=True)
    ws = wb.active
    rows = list(ws.iter_rows(values_only=True))

    # find the header row (the one containing "Date")
    header_idx = next(
        i for i, r in enumerate(rows)
        if r and any(str(c).strip().lower() == "date" for c in r if c is not None)
    )
    header = [str(c).strip().lower() if c is not None else "" for c in rows[header_idx]]
    col = {name: header.index(name) for name in ("date", "description", "debit", "credit") if name in header}

    txns = []
    for r in rows[header_idx + 1:]:
        if not r or r[col["date"]] is None:
            continue
        d = r[col["date"]]
        if isinstance(d, dt.datetime):
            date = d.date()
        else:
            continue
        txns.append({
            "date": date,
            "description": str(r[col["description"]] or "").strip(),
            "debit": _to_float(r[col["debit"]]),
            "credit": _to_float(r[col["credit"]]),
        })
    txns.sort(key=lambda t: t["date"])
    return txns


def build_pdf(txns, out_path: Path, opening: float = 0.00):
    styles = getSampleStyleSheet()
    doc = SimpleDocTemplate(str(out_path), pagesize=A4,
                            topMargin=18 * mm, bottomMargin=18 * mm,
                            leftMargin=16 * mm, rightMargin=16 * mm)

    # recompute a clean running balance so the statement reconciles
    running = opening
    table_rows = [["Date", "Description", "Debit", "Credit", "Balance"]]
    for t in txns:
        running += (t["credit"] or 0.0) - (t["debit"] or 0.0)
        table_rows.append([
            t["date"].strftime("%d/%m/%Y"),
            t["description"],
            f"${t['debit']:,.2f}" if t["debit"] else "",
            f"${t['credit']:,.2f}" if t["credit"] else "",
            f"${running:,.2f} CR",
        ])
    closing = running
    period = f"{txns[0]['date'].strftime('%d/%m/%Y')} to {txns[-1]['date'].strftime('%d/%m/%Y')}"

    story = [
        Paragraph("<b>Macquarie Cash Management Account</b>", styles["Title"]),
        Spacer(1, 6),
        Paragraph("Account: 123456789 - CASH MANAGEMENT ACCOUNT", styles["Normal"]),
        Paragraph("Account Holder: ABC Super Fund", styles["Normal"]),
        Paragraph(f"Statement Period: {period}", styles["Normal"]),
        Paragraph(f"Opening Balance: ${opening:,.2f}", styles["Normal"]),
        Paragraph(f"Closing Balance: ${closing:,.2f}", styles["Normal"]),
        Spacer(1, 12),
    ]

    tbl = Table(table_rows, colWidths=[24 * mm, 70 * mm, 24 * mm, 24 * mm, 28 * mm])
    tbl.setStyle(TableStyle([
        ("GRID", (0, 0), (-1, -1), 0.5, colors.grey),
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#1f3a5f")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("FONTSIZE", (0, 0), (-1, -1), 8),
        ("ALIGN", (2, 0), (-1, -1), "RIGHT"),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
    ]))
    story.append(tbl)
    doc.build(story)
    return closing


if __name__ == "__main__":
    txns = load_transactions(XLSX)
    closing = build_pdf(txns, OUT_PDF)
    print(f"Wrote {OUT_PDF}")
    print(f"{len(txns)} transactions, closing balance ${closing:,.2f}")