#!/usr/bin/env python3
"""Generate a messy Excel workbook matching the published test-pack spec.

This is the reproducible companion to the 0-2 scorecard in
https://infinisynapse.com/en/blog/ai-excel-data-analysis-tools

The point is that you should not trust our scores. Run this script, get a
workbook with the same *class* of defects we tested against, then score your
own shortlist with the blank grid (excel-ai-scorecard-blank.csv).

The data is synthetic and randomly generated. It contains no real customer
records, so it is safe to upload to a consumer AI tier while you are still
deciding which tools may receive real data.

Spec reproduced (see "Shared Test Design" in the guide):
  - 3 sheets, mixed types
  - nulls scattered through numeric and text columns
  - dates stored as inconsistent strings, not date serials
  - duplicate business keys
  - the same logical column renamed between sheets
  - a trailing total row that is not part of the data
  - numbers stored as text with thousands separators and currency symbols

Usage:
    pip install openpyxl
    python make_messy_workbook.py                  # ~18 MB, default
    python make_messy_workbook.py --rows 400000    # bigger
    python make_messy_workbook.py --seed 7 --out my-pack.xlsx

Released under CC BY 4.0.
"""

from __future__ import annotations

import argparse
import random
import string
from datetime import date, timedelta

try:
    from openpyxl import Workbook
except ImportError:  # pragma: no cover
    raise SystemExit("openpyxl is required:  pip install openpyxl")

REGIONS = ["EMEA", "emea", "North America", "NA", "APAC", "apac", "LATAM", None]
CHANNELS = ["Direct", "Partner", "Self-serve", "direct ", " Partner", None]
SEGMENTS = ["Enterprise", "Mid-market", "SMB", "ENT", "smb"]


def _messy_date(d: date, rng: random.Random) -> str | None:
    """Return the same date in one of several inconsistent string formats."""
    fmt = rng.choice(
        [
            "%Y-%m-%d",      # 2026-03-04
            "%d/%m/%Y",      # 04/03/2026  <- ambiguous against US order
            "%m/%d/%Y",      # 03/04/2026  <- the ambiguity, on purpose
            "%b %d, %Y",     # Mar 04, 2026
            "%d-%b-%y",      # 04-Mar-26
            "%Y%m%d",        # 20260304
        ]
    )
    if rng.random() < 0.02:
        return None
    if rng.random() < 0.02:
        return "n/a"
    return d.strftime(fmt)


def _messy_amount(rng: random.Random) -> object:
    """Numbers, sometimes as text with separators or currency symbols."""
    value = round(rng.uniform(-500, 25_000), 2)
    roll = rng.random()
    if roll < 0.03:
        return None
    if roll < 0.08:
        return f"${value:,.2f}"       # text, not numeric
    if roll < 0.11:
        return f"{value:,.2f}"        # text with thousands separator
    if roll < 0.13:
        return f"({abs(value):,.2f})"  # accounting negative
    return value


def _account_key(i: int, rng: random.Random) -> str:
    """Business key with deliberate duplicates and inconsistent padding."""
    base = rng.randint(1, max(2, i // 3))  # collisions by construction
    style = rng.random()
    if style < 0.15:
        return f"ACCT-{base}"            # unpadded
    if style < 0.25:
        return f"acct-{base:05d}"        # lowercase
    if style < 0.30:
        return f" ACCT-{base:05d} "      # whitespace
    return f"ACCT-{base:05d}"


def build(rows: int, seed: int, out: str) -> None:
    rng = random.Random(seed)
    wb = Workbook(write_only=True)
    start = date(2026, 1, 1)

    # --- Sheet 1: transactions (the bulk of the file) ---
    ws = wb.create_sheet("Transactions")
    ws.append(["Account Key", "Region", "Channel", "Booking Date", "Amount", "Qty", "Notes"])
    for i in range(1, rows + 1):
        ws.append(
            [
                _account_key(i, rng),
                rng.choice(REGIONS),
                rng.choice(CHANNELS),
                _messy_date(start + timedelta(days=rng.randint(0, 180)), rng),
                _messy_amount(rng),
                rng.choice([1, 2, 3, None, "two", 0]),
                "".join(rng.choices(string.ascii_letters + " ", k=rng.randint(0, 40))) or None,
            ]
        )
    # trailing total row that is NOT data - a classic pivot trap
    ws.append(["TOTAL", None, None, None, "=SUM(E2:E%d)" % (rows + 1), None, "grand total"])

    # --- Sheet 2: accounts dimension, same key renamed ---
    ws2 = wb.create_sheet("Accounts")
    # note: "Account ID" here vs "Account Key" in Transactions - the rename
    ws2.append(["Account ID", "Account Name", "Segment", "Owner", "Signup Date"])
    for i in range(1, max(2, rows // 3) + 1):
        ws2.append(
            [
                f"ACCT-{i:05d}",
                f"Account {i}" if rng.random() > 0.03 else None,
                rng.choice(SEGMENTS),
                f"rep{rng.randint(1, 40)}@example.invalid",
                _messy_date(start - timedelta(days=rng.randint(0, 900)), rng),
            ]
        )

    # --- Sheet 3: a hand-maintained mapping sheet with gaps ---
    ws3 = wb.create_sheet("Region Mapping")
    ws3.append(["Raw Region", "Canonical Region", "Reviewed?"])
    for raw, canon in [
        ("EMEA", "EMEA"), ("emea", "EMEA"), ("North America", "North America"),
        ("NA", "North America"), ("APAC", "APAC"), ("apac", "APAC"),
        ("LATAM", None),  # deliberately unmapped
    ]:
        ws3.append([raw, canon, rng.choice(["y", "Y", "yes", None])])

    wb.save(out)


def main() -> None:
    ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("--rows", type=int, default=260_000,
                    help="transaction rows; ~260k lands near 17 MB (default: %(default)s)")
    ap.add_argument("--seed", type=int, default=2026, help="RNG seed for reproducibility (default: %(default)s)")
    ap.add_argument("--out", default="messy-close-pack.xlsx", help="output path (default: %(default)s)")
    args = ap.parse_args()

    build(args.rows, args.seed, args.out)

    import os
    size_mb = os.path.getsize(args.out) / 1_048_576
    print(f"wrote {args.out}  ({size_mb:.1f} MB, {args.rows:,} transaction rows, seed={args.seed})")
    print("\nNow run the same five tasks against every tool on your shortlist:")
    print("  1. profile + clean            2. draft 3 lookup/text formulas")
    print("  3. pivot by region x month    4. two meeting-ready charts")
    print("  5. re-run 'next month' with the same definitions")
    print("\nScore 0-2 per dimension in excel-ai-scorecard-blank.csv.")
    print("Watch specifically for: the TOTAL row leaking into pivots, the")
    print("Account Key / Account ID rename, dd/mm vs mm/dd ambiguity, and")
    print("amounts stored as text being silently dropped from sums.")


if __name__ == "__main__":
    main()