"""Author the synthetic Q3 close workbook the refused-edit demo edits.

Sheet Regions holds an Excel table named Regions (Region, Q2, Q3), a totals row below it, and the
ledger control total for Q3. Sheet Summary reads the totals and holds an empty variance line that
the agent is asked to fill. The numbers are synthetic demonstration data.

openpyxl writes formulas without cached values and stamps the save time into docProps/core.xml
and the zip entries, so the authored file is normalized (fixed modified time and zip entry times)
and then recalculated by `wolf recalc` with a fixed clock, which stores every formula's value. The
result is the same bytes on every run with the same wolf binary.
"""

import datetime as dt
import re
import zipfile
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.worksheet.table import Table, TableStyleInfo

FIXED_TIME = dt.datetime(2026, 10, 1, 0, 0, 0)

REGIONS = [
    # region, Q2 actual, Q3 preliminary ($K)
    ("North America", 8900, 9600),
    ("Europe", 5400, 6000),
    ("APAC", 3800, 4100),
    ("LATAM", 1300, 1500),
]
LEDGER_Q3 = 21750


def author(path: Path) -> None:
    """Write the un-recalculated workbook to `path`."""
    wb = Workbook()
    bold = Font(bold=True)
    title = Font(bold=True, size=14)
    thousands = "#,##0"
    rule = Border(top=Side(style="thin"))

    ws = wb.active
    ws.title = "Regions"
    ws["A1"] = "Q3 revenue by region ($K)"
    ws["A1"].font = title
    for col, header in enumerate(("Region", "Q2", "Q3"), start=1):
        header_cell = ws.cell(3, col, header)
        if col > 1:
            header_cell.alignment = Alignment(horizontal="right")
    for row, (region, q2, q3) in enumerate(REGIONS, start=4):
        ws.cell(row, 1, region)
        ws.cell(row, 2, q2).number_format = thousands
        ws.cell(row, 3, q3).number_format = thousands
    table = Table(displayName="Regions", ref="A3:C7")
    table.tableStyleInfo = TableStyleInfo(name="TableStyleLight9", showRowStripes=True)
    ws.add_table(table)
    ws["A8"] = "Total"
    ws["B8"] = "=SUM(B4:B7)"
    ws["C8"] = "=SUM(C4:C7)"
    for ref in ("A8", "B8", "C8"):
        ws[ref].font = bold
        ws[ref].border = rule
    ws["B8"].number_format = thousands
    ws["C8"].number_format = thousands
    ws["A10"] = "Ledger control, Q3 final"
    ws["C10"] = LEDGER_Q3
    ws["C10"].number_format = thousands
    ws["C10"].fill = PatternFill("solid", fgColor="FFF4D6")
    ws.column_dimensions["A"].width = 26
    ws.column_dimensions["B"].width = 12
    ws.column_dimensions["C"].width = 12

    summary = wb.create_sheet("Summary")
    summary["A1"] = "Q3 board summary"
    summary["A1"].font = title
    lines = [
        (3, "Q3 revenue ($K)", "=Regions!C8", thousands),
        (4, "Q2 revenue ($K)", "=Regions!B8", thousands),
        (5, "Growth, Q2 to Q3", "=B3/B4-1", "0.0%"),
        (7, "Variance to ledger ($K)", None, thousands),
    ]
    for row, label, formula, fmt in lines:
        summary.cell(row, 1, label)
        cell = summary.cell(row, 2, formula)
        cell.number_format = fmt
        cell.alignment = Alignment(horizontal="right")
    summary["A7"].font = bold
    summary.column_dimensions["A"].width = 28
    summary.column_dimensions["B"].width = 14

    wb.properties.creator = "WolfSDK demo"
    wb.properties.lastModifiedBy = "WolfSDK demo"
    wb.properties.created = FIXED_TIME
    wb.save(path)
    normalize_zip(path)


def normalize_zip(path: Path) -> None:
    """Pin the save time openpyxl stamps and rewrite the package with fixed entry times."""
    stamp = FIXED_TIME.strftime("%Y-%m-%dT%H:%M:%SZ")
    with zipfile.ZipFile(path) as src:
        entries = [(info.filename, src.read(info)) for info in src.infolist()]
    entries = [
        (
            name,
            re.sub(
                rb"(<dcterms:modified[^>]*>)[^<]*(</dcterms:modified>)",
                rb"\g<1>" + stamp.encode() + rb"\g<2>",
                data,
            )
            if name == "docProps/core.xml"
            else data,
        )
        for name, data in entries
    ]
    with zipfile.ZipFile(path, "w") as dst:
        for name, data in entries:
            info = zipfile.ZipInfo(name, date_time=FIXED_TIME.timetuple()[:6])
            info.compress_type = zipfile.ZIP_DEFLATED
            dst.writestr(info, data)