"""記事「販売管理をエクセルで続けると何年で限界が来る?」(excel-sales-ledger-row-years)の計算・実験と図。

1. 年数の計算(外部データなし):
     年数 = 上限の行数 ÷(1日の明細数 × 年の営業日 245日)
   - Excel のワークシートの上限 1,048,576 行(見出し1行を引いて 1,048,575 明細)。売上明細だけを1枚のシートに積む場合
   - B社の公開仕様:伝票の合計60万明細。受注と売上の両方の伝票を使うと、売上明細1行で2明細(入金は数えない)。
     top_numbers.py(トップと柱の数字)と同じ数え方
2. 実験(--measure を付けたときだけ。数分かかる):
   架空の売上明細(11列、乱数の種 20261016)を10万・30万・50万・100万行で .xlsx に書き出し、
     - ファイルの容量
     - Python(openpyxl の read_only)で全行を読む時間
     - Excel(COM で新しいプロセスを起動し、読み取り専用で開く)の Workbooks.Open の時間(3回の中央値)
   を測る。.xlsx は一時フォルダに書き、測ったら消す。結果は scripts/figs/out/excel-rows-years.json。
   Excel が入っていない PC では Excel の列を飛ばす。

使い方:
  python scripts/figs/excel_rows_years.py --measure   (fields/sales-management-app/ で実行。実験+図)
  python scripts/figs/excel_rows_years.py             (保存済みの JSON から図だけ書き直す)
書き出すもの:
  public/img/articles/excel-sales-ledger-row-years.svg(図1 年数。frontmatter の image にも使う)
  scripts/figs/out/excel-ledger-open-time.svg(図2 実験。記事にインラインで貼る)
"""
import json, pathlib, html, sys, time, random, statistics, tempfile, platform, datetime, os

ROOT = pathlib.Path(__file__).resolve().parents[2]
OUT_IMG = ROOT / 'public/img/articles'; OUT_IMG.mkdir(parents=True, exist_ok=True)
OUT_INL = ROOT / 'scripts/figs/out'; OUT_INL.mkdir(parents=True, exist_ok=True)
RES = OUT_INL / 'excel-rows-years.json'

EXCEL_ROWS = 1_048_576 - 1      # 見出しの1行を引く
B_LINES = 600_000               # B社 伝票の合計60万明細
DAYS = 245                      # 年の営業日(仮定)
PER_DAY = (100, 200, 300, 400, 500, 1000)   # 1日の売上明細
SIZES = (100_000, 300_000, 500_000, 1_000_000)
SEED = 20261016


def years_table():
    rows = []
    for n in PER_DAY:
        rows.append({'salesLinesPerDay': n,
                     'excelYears': round(EXCEL_ROWS / (n * DAYS), 1),
                     'bLinesPerDay': 2 * n,
                     'bYears': round(B_LINES / (2 * n * DAYS), 1)})
    return rows


# ---- 実験
COLS = ['伝票日付', '伝票番号', '行', '得意先コード', '得意先名', '商品コード', '商品名', '数量', '単価', '金額', '税率']


def fake_rows(n):
    """架空の売上明細。得意先400社・商品3,000点、1伝票1〜8行。"""
    rng = random.Random(SEED)
    cust = [(f'C{i:05d}', f'架空商事{i:03d}') for i in range(1, 401)]
    prod = [(f'P{i:06d}', f'資材{i:04d} {rng.choice(["箱", "本", "袋", "枚", "kg"])}入', rng.choice([120, 198, 480, 980, 1580, 3150, 6400])) for i in range(1, 3001)]
    d0 = datetime.date(2016, 4, 1)
    slip, line, day, left = 1, 0, 0, 0
    for _ in range(n):
        if left == 0:
            slip += 1; line = 0; left = rng.randint(1, 8); c = rng.choice(cust)
            if rng.random() < 0.02: day += 1
        line += 1; left -= 1
        p = rng.choice(prod); q = rng.randint(1, 60)
        yield [d0 + datetime.timedelta(days=day), slip, line, c[0], c[1], p[0], p[1], q, p[2], q * p[2], 0.1]


def write_xlsx(path, n):
    import xlsxwriter
    wb = xlsxwriter.Workbook(str(path), {'constant_memory': True})
    ws = wb.add_worksheet('売上明細'); df = wb.add_format({'num_format': 'yyyy/mm/dd'})
    ws.write_row(0, 0, COLS)
    for i, r in enumerate(fake_rows(n), start=1):
        ws.write_datetime(i, 0, datetime.datetime.combine(r[0], datetime.time()), df)
        ws.write_row(i, 1, r[1:])
    wb.close()


def read_openpyxl(path):
    import openpyxl
    t = time.perf_counter()
    wb = openpyxl.load_workbook(str(path), read_only=True, data_only=True)
    cnt = sum(1 for _ in wb.active.iter_rows(values_only=True))
    wb.close()
    return time.perf_counter() - t, cnt


def excel_open(path, reps=3):
    try:
        import win32com.client, pythoncom
    except ImportError:
        return None, None
    pythoncom.CoInitialize()
    app = win32com.client.DispatchEx('Excel.Application')   # 開いている Excel とは別の新しいプロセス
    app.Visible = False; app.DisplayAlerts = False; app.ScreenUpdating = False
    ver = f'{app.Version}(ビルド {app.Build})'
    ts = []
    try:
        for _ in range(reps):
            t = time.perf_counter()
            wb = app.Workbooks.Open(str(path), 0, True)
            ts.append(time.perf_counter() - t)
            wb.Close(False)
    finally:
        app.Quit()
    return statistics.median(ts), ver


def measure():
    import openpyxl, xlsxwriter
    env = {'date': datetime.date.today().isoformat(), 'python': platform.python_version(),
           'openpyxl': openpyxl.__version__, 'xlsxwriter': xlsxwriter.__version__,
           'os': platform.platform(), 'cpu': os.environ.get('PROCESSOR_IDENTIFIER', platform.processor())}
    out = []
    with tempfile.TemporaryDirectory() as d:
        for n in SIZES:
            p = pathlib.Path(d) / f'sales_{n}.xlsx'
            t = time.perf_counter(); write_xlsx(p, n); tw = time.perf_counter() - t
            mb = p.stat().st_size / 1_000_000
            tr, cnt = read_openpyxl(p)
            assert cnt == n + 1, cnt
            te, ver = excel_open(p)
            if ver: env['excel'] = ver
            out.append({'rows': n, 'mb': round(mb, 1), 'writeSec': round(tw, 1), 'openpyxlSec': round(tr, 1),
                        'excelOpenSec': None if te is None else round(te, 1)})
            print(out[-1], flush=True)
    data = {'env': env, 'seed': SEED, 'cols': COLS, 'results': out}
    RES.write_text(json.dumps(data, ensure_ascii=False, indent=1), encoding='utf-8')
    return data


# ---- 図(差し色 #678b2c は面だけ、文字は #557323 / #46601d)
FONT = "'Hiragino Sans','Yu Gothic','Meiryo',sans-serif"
INK, SUB, MUTE, LINE = '#23221f', '#4c4c4c', '#706d65', '#dddbd8'
ACC, ACC_TXT, ACC_DK, GRAY = '#678b2c', '#557323', '#46601d', '#c9c6bf'
f = lambda n: f'{n:,}'


def t(x, y, s, size=12, fill=INK, anchor='start', weight=400):
    return (f"<text x='{x}' y='{y}' font-size='{size}' fill='{fill}' text-anchor='{anchor}' "
            f"font-weight='{weight}'>{html.escape(s)}</text>")


def svg_open(w, h, tid, title, standalone):
    ns = " xmlns='http://www.w3.org/2000/svg'"
    style = '' if standalone else " style='max-width:100%;height:auto'"
    return (f"<svg{ns} viewBox='0 0 {w} {h}' width='{w}' height='{h}' role='img' aria-labelledby='{tid}' "
            f"font-family=\"{FONT}\"{style}><title id='{tid}'>{html.escape(title)}</title><rect width='{w}' height='{h}' fill='#fff'/>")


def fig_years(rows):
    W, H = 760, 410
    pick = [r for r in rows if r['salesLinesPerDay'] in (100, 300, 500, 1000)]
    title = ('1日の売上明細の数ごとに、上限に届く年数を比べた横棒グラフ。B社の伝票60万明細には100行で'
             f"{pick[0]['bYears']}年、300行で{pick[1]['bYears']}年、500行で{pick[2]['bYears']}年。"
             f"Excelの1,048,576行には100行で{pick[0]['excelYears']}年、500行で{pick[2]['excelYears']}年")
    s = [svg_open(W, H, 'years-title', title, True)]
    s.append(t(20, 30, '上限に届くまでの年数(1日の売上明細 × 年245日)', 15, INK, weight=700))
    s.append(f"<rect x='430' y='16' width='12' height='12' fill='{ACC}'/>"); s.append(t(448, 27, 'B社 伝票60万明細', 11.5, SUB))
    s.append(f"<rect x='574' y='16' width='12' height='12' fill='{GRAY}'/>"); s.append(t(592, 27, 'Excel 1,048,576行', 11.5, SUB))
    x0, xmax, y0, gh = 150, 700, 56, 70
    mx = 45
    for v in (0, 10, 20, 30, 40):
        x = x0 + (xmax - x0) * v / mx
        s.append(f"<line x1='{x:.1f}' y1='{y0 - 6}' x2='{x:.1f}' y2='{y0 + gh * len(pick) - 8}' stroke='{LINE}' stroke-dasharray='2 3'/>")
        s.append(t(x, y0 + gh * len(pick) + 6, f'{v}年', 10.5, MUTE, 'middle'))
    for i, r in enumerate(pick):
        y = y0 + i * gh
        s.append(t(20, y + 22, f"1日 {f(r['salesLinesPerDay'])}行", 13, INK, weight=700))
        s.append(t(20, y + 40, f"(伝票では{f(r['bLinesPerDay'])}明細)", 10.5, MUTE))
        for j, (key, col) in enumerate((('bYears', ACC), ('excelYears', GRAY))):
            w = (xmax - x0) * r[key] / mx; yy = y + 6 + j * 26
            s.append(f"<rect x='{x0}' y='{yy}' width='{w:.1f}' height='20' fill='{col}'/>")
            s.append(t(x0 + w + 8, yy + 15, f'{r[key]}年', 12.5, ACC_DK if key == 'bYears' else INK, weight=700))
    s.append(t(20, H - 32, 'B社は受注と売上の両方の伝票を使う場合(売上明細1行で2明細、入金は数えない)。Excel は売上明細だけを1枚のシートに積む場合', 10.5, MUTE))
    s.append(t(20, H - 14, '商いの帳場の計算値。上限はB社の主要スペックとMicrosoftの「Excelの仕様と制限」(2026-10-16確認)。年245日は仮定', 10.5, MUTE))
    s.append('</svg>')
    return ''.join(s)


def fig_open(data):
    res = data['results']; env = data['env']
    W, H = 700, 330
    has_x = all(r['excelOpenSec'] is not None for r in res)
    title = ('架空の売上明細を10万〜100万行の.xlsxにしたときの、ファイル容量と開く時間の棒グラフ。'
             + '、'.join(f"{r['rows'] // 10000}万行は{r['mb']}MB・Excelで{r['excelOpenSec']}秒" for r in res))
    s = [svg_open(W, H, 'open-title', title, False)]
    s.append(t(20, 28, '行数ごとのファイル容量と、Excelで開く時間(架空の売上明細11列)', 13.5, INK, weight=700))
    base, top = 236, 60
    mt = max((r['excelOpenSec'] or 0) for r in res) if has_x else max(r['openpyxlSec'] for r in res)
    mt = max(mt * 1.15, 1)
    for i, r in enumerate(res):
        cx = 120 + i * 150
        v = r['excelOpenSec'] if has_x else r['openpyxlSec']
        h = (base - top) * v / mt
        s.append(f"<rect x='{cx - 28}' y='{base - h:.1f}' width='56' height='{h:.1f}' fill='{ACC}'/>")
        s.append(t(cx, base - h - 7, f'{v}秒', 13, ACC_DK, 'middle', 700))
        s.append(t(cx, base + 20, f"{r['rows'] // 10000}万行", 12.5, INK, 'middle', 700))
        s.append(t(cx, base + 38, f"{r['mb']}MB", 12, SUB, 'middle'))
    s.append(f"<line x1='60' y1='{base}' x2='680' y2='{base}' stroke='{GRAY}'/>")
    s.append(t(20, H - 32, f"Excel {env.get('excel', '—')} で読み取り専用で開いた時間(3回の中央値)。{env['date']} 実行、Python {env['python']}・xlsxwriter {env['xlsxwriter']} で作成", 10.5, MUTE))
    s.append(t(20, H - 14, f"PC:{env.get('pcLabel', env['cpu'])}。数式・書式・ピボットなしの値だけのシート。商いの帳場の実測", 10.5, MUTE))
    s.append('</svg>')
    return ''.join(s)


if __name__ == '__main__':
    rows = years_table()
    if '--measure' in sys.argv:
        data = measure()
    else:
        data = json.loads(RES.read_text(encoding='utf-8'))
    data['years'] = rows
    if 'pcLabel' not in data['env']:
        data['env']['pcLabel'] = 'AMD Ryzen 7 7735U・メモリ32GB・SSD・Windows 11 Pro'
    RES.write_text(json.dumps(data, ensure_ascii=False, indent=1), encoding='utf-8')
    (OUT_IMG / 'excel-sales-ledger-row-years.svg').write_text(fig_years(rows), encoding='utf-8')
    (OUT_INL / 'excel-ledger-open-time.svg').write_text(fig_open(data), encoding='utf-8')
    print(json.dumps({'years': rows, 'env': data['env'], 'results': data['results']}, ensure_ascii=False, indent=1))