"""住民記録システム Web アプリ 設計書（Excel）生成スクリプト

Usage:
    python tools/build_design.py [out.xlsx]
    OUT=path/to/file.xlsx python tools/build_design.py

何も指定がなければリポジトリ直下に `住民記録システム_Web版_設計書.xlsx` を出力する。
"""
import os
import sys
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

REPO_ROOT = Path(__file__).resolve().parent.parent
DEFAULT_OUT = REPO_ROOT / "住民記録システム_Web版_設計書.xlsx"
OUT = sys.argv[1] if len(sys.argv) > 1 else os.environ.get("OUT", str(DEFAULT_OUT))

FONT_NAME = "Yu Gothic UI"
HEAD_FILL = PatternFill("solid", start_color="1F4E78")
SUB_FILL  = PatternFill("solid", start_color="D9E1F2")
NOTE_FILL = PatternFill("solid", start_color="FFF2CC")
BORDER = Border(
    left=Side(style="thin", color="999999"),
    right=Side(style="thin", color="999999"),
    top=Side(style="thin", color="999999"),
    bottom=Side(style="thin", color="999999"),
)

def style_header(cell):
    cell.font = Font(name=FONT_NAME, bold=True, color="FFFFFF", size=11)
    cell.fill = HEAD_FILL
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    cell.border = BORDER

def style_sub(cell):
    cell.font = Font(name=FONT_NAME, bold=True, size=11)
    cell.fill = SUB_FILL
    cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
    cell.border = BORDER

def style_body(cell, wrap=True):
    cell.font = Font(name=FONT_NAME, size=10)
    cell.alignment = Alignment(horizontal="left", vertical="top", wrap_text=wrap)
    cell.border = BORDER

def write_table(ws, start_row, headers, rows, widths=None):
    for j, h in enumerate(headers, 1):
        c = ws.cell(row=start_row, column=j, value=h)
        style_header(c)
    for i, row in enumerate(rows, start=start_row + 1):
        for j, v in enumerate(row, 1):
            c = ws.cell(row=i, column=j, value=v)
            style_body(c)
    if widths:
        for j, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[start_row].height = 28

def title_row(ws, text, row=1, span=10):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=span)
    c = ws.cell(row=row, column=1, value=text)
    c.font = Font(name=FONT_NAME, bold=True, size=16, color="FFFFFF")
    c.fill = HEAD_FILL
    c.alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[row].height = 32

def section(ws, text, row, span=10):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=span)
    c = ws.cell(row=row, column=1, value=text)
    c.font = Font(name=FONT_NAME, bold=True, size=12)
    c.fill = SUB_FILL
    c.alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[row].height = 22

wb = Workbook()
wb.remove(wb.active)

# ===== 1. 表紙 =====
ws = wb.create_sheet("00_表紙")
title_row(ws, "住民記録システム Web 版 設計書", 1, 6)
meta = [
    ["文書名", "住民記録システム（自治体標準準拠）Web アプリ 設計書"],
    ["版数", "Ver. 0.1 (初版ドラフト)"],
    ["作成日", "2026-05-16"],
    ["準拠仕様", "住民記録システム標準仕様書 第6.1版（令和8年3月25日版） 自治体システム等標準化検討会"],
    ["関連仕様", "データ要件・連携要件標準仕様書 / 共通機能標準仕様書 / 申請管理システム標準仕様書"],
    ["対象クライアント", "ChromeOS Flex / Windows / macOS（Chromium系ブラウザ最新2世代をサポート）"],
    ["想定利用者", "市区町村職員（窓口・審査・統計・運用管理）、システム管理者、外部連携システム"],
    ["目的", "標準仕様書に規定される必須機能・標準オプション・様式・帳票・連携要件を、ChromeOS Flex端末でも完全に利用できるWebアプリとして実装するための設計を示す"],
    ["スコープ外", "宛名番号付番（番号連携サーバ）本体、住基ネットCS本体、マイナポータル本体（いずれも連携先として扱う）"],
]
for i, (k, v) in enumerate(meta, start=3):
    a = ws.cell(row=i, column=1, value=k); style_sub(a)
    ws.merge_cells(start_row=i, start_column=2, end_row=i, end_column=6)
    b = ws.cell(row=i, column=2, value=v); style_body(b)
    ws.row_dimensions[i].height = 36
ws.column_dimensions['A'].width = 18
for col in 'BCDEF':
    ws.column_dimensions[col].width = 24

section(ws, "■ 本書の構成（シート一覧）", 13, 6)
sheets_idx = [
    ("01_改訂履歴", "本書の改訂履歴"),
    ("02_システム概要", "システム位置付け・全体像・利用シナリオ"),
    ("03_スコープ", "標準仕様書の章別準拠範囲とトレーサビリティ"),
    ("04_機能一覧", "標準仕様書 第3章 機能要件に対応する機能ID付き一覧"),
    ("05_画面一覧", "Webアプリ画面構成（SPA）と画面ID"),
    ("06_帳票一覧", "標準帳票（住民票の写し等）一覧と帳票ID"),
    ("07_連携一覧", "住基ネットCS／番号連携／庁内他業務／申請管理"),
    ("08_データモデル", "主要テーブル（住民・世帯・異動履歴 等）"),
    ("09_API一覧", "REST API エンドポイント設計"),
    ("10_認証権限", "認証方式・役割・操作権限"),
    ("11_非機能要件", "性能・可用性・セキュリティ・運用・移行"),
    ("12_技術スタック", "ChromeOS Flex対応Web技術構成"),
    ("13_エラー一覧", "エラー・アラート（標準仕様書 11章）"),
    ("14_バッチ一覧", "夜間/オンデマンドバッチ"),
    ("15_開発計画", "工程・WBS・体制案"),
]
write_table(ws, 14, ["シート", "内容"], sheets_idx, widths=[20, 60])

# ===== 改訂履歴 =====
ws = wb.create_sheet("01_改訂履歴")
title_row(ws, "改訂履歴", 1, 5)
write_table(ws, 3,
    ["版数", "日付", "担当", "区分", "内容"],
    [
        ["0.1", "2026-05-16", "設計担当", "新規", "初版ドラフト作成（標準仕様書6.1版に準拠）"],
    ],
    widths=[8, 14, 14, 10, 60])

# ===== システム概要 =====
ws = wb.create_sheet("02_システム概要")
title_row(ws, "システム概要", 1, 6)
section(ws, "1. 位置付け", 3, 6)
ws.merge_cells("A4:F4")
ws["A4"] = ("市区町村における住民基本台帳事務（住民票・世帯・異動・証明・統計・連携）を司る基幹システム。"
            "ガバメントクラウド上での共通利用を想定し、ブラウザのみで動作するWebアプリとして提供する。"
            "クライアントは ChromeOS Flex を含むChromium系ブラウザで、追加クライアントソフトのインストールを不要とする。")
ws["A4"].font = Font(name=FONT_NAME, size=10)
ws["A4"].alignment = Alignment(wrap_text=True, vertical="top")
ws.row_dimensions[4].height = 70

section(ws, "2. システム全体像", 6, 6)
arch = [
    ["レイヤ", "構成要素", "内容"],
    ["プレゼンテーション", "SPA (React + TypeScript)", "ChromeOS Flex / Chrome / Edge で動作。PWA化により若干のオフライン耐性付与。"],
    ["BFF / API", "REST API (HTTPS/JSON)", "認証済みセッションでアクセス。OpenAPIで仕様管理。"],
    ["業務ロジック", "アプリサーバ (Java 21 + Spring Boot もしくは .NET 8)", "標準仕様書 第3章の機能要件を実装。"],
    ["データ", "RDB (PostgreSQL 16, クラウドマネージド)", "住民・世帯・異動履歴・抑止・権限・監査ログ。"],
    ["連携", "住基ネットCS / 番号連携 / 庁内他業務 / 申請管理 / マイナポータル", "標準仕様書 第7章。非同期メッセージ＋REST。"],
    ["共通機能", "共通機能標準仕様書 準拠（認証・権限・ログ・EUC・帳票出力）", "共通基盤として外出し。"],
    ["インフラ", "ガバメントクラウド (IaaS/PaaS) ／ コンテナ (Kubernetes)", "可用性ゾーン分散構成。"],
]
write_table(ws, 7, arch[0], arch[1:], widths=[18, 32, 70])

section(ws, "3. 利用シナリオ（代表）", 17, 6)
scen = [
    ["#", "アクター", "シナリオ", "関連機能"],
    [1, "窓口職員", "転入届の受付・審査・住民票登録・住民票の写し交付", "4.1 届出 / 5 証明"],
    [2, "窓口職員", "住民票コード／個人番号の確認・通知", "4.3 / 4.4"],
    [3, "審査担当", "職権記載・職権修正の起票・決裁・反映", "4.2"],
    [4, "統計担当", "月次・年次の住基統計集計、年報様式の出力", "6 統計 / 20.6"],
    [5, "システム管理者", "ユーザ・権限・抑止設定の管理、監査ログ閲覧", "3 抑止設定 / 10 共通"],
    [6, "外部連携", "CS連携／番号連携／庁内他業務（税・国保・選挙 等）", "7 連携"],
    [7, "申請管理", "オンライン申請受領・本人確認・職員審査", "8 標準オプション（本人通知）+ 申請管理連携"],
    [8, "バッチ", "夜間バッチ（年齢到達、住民票コード一括、データ抽出）", "9 バッチ"],
]
write_table(ws, 18, scen[0], scen[1:], widths=[5, 16, 60, 24])

# ===== スコープ =====
ws = wb.create_sheet("03_スコープ")
title_row(ws, "標準仕様書 章別 スコープ", 1, 5)
scope = [
    ["章", "節", "本システム対応", "備考"],
    ["第1章", "本仕様書について", "参照", "—"],
    ["第2章", "標準化の対象範囲", "対応", "本仕様書の対象範囲を完全実装"],
    ["第3章 1", "管理項目（住民データ / 異動履歴 / その他）", "実装", "1.1〜1.3 すべて"],
    ["第3章 2", "検索・照会・操作", "実装", "2.1〜2.3"],
    ["第3章 3", "抑止設定", "実装", "DV等支援措置を含む"],
    ["第3章 4", "異動（届出／職権／住民票コード／個人番号／外国人／取消）", "実装", "4.1〜4.6"],
    ["第3章 5", "証明（住民票の写し等）", "実装", "改ざん防止・コンビニ交付連携含む"],
    ["第3章 6", "統計", "実装", "年報・人口動態・任意集計"],
    ["第3章 7", "連携（CS・番号・庁内他業務）", "実装", "7.1 / 7.2"],
    ["第3章 8", "標準オプション機能", "実装", "8.1 本人通知 / 8.2 特別永住者"],
    ["第3章 9", "バッチ", "実装", "夜間・オンデマンド"],
    ["第3章 10", "共通（権限・ログ・EUC 等）", "実装", "共通機能標準仕様書と整合"],
    ["第3章 11", "エラー・アラート", "実装", "標準アラート全種"],
    ["第4章", "様式・帳票要件", "実装", "20.1〜20.6 すべて"],
    ["第5章", "データ要件", "対応", "データ要件・連携要件標準仕様書に準拠"],
    ["第6章", "非機能要件", "対応", "本書11_非機能要件で具体化"],
    ["第7章", "用語", "参照", "用語集として整備"],
]
write_table(ws, 3, scope[0], scope[1:], widths=[10, 60, 12, 30])

# ===== 機能一覧 =====
ws = wb.create_sheet("04_機能一覧")
title_row(ws, "機能一覧（標準仕様書 第3章 機能要件）", 1, 7)
func = [
    ["機能ID", "大分類", "中分類", "機能名", "概要", "必須/任意", "画面ID"],
    ["F-1-1-01", "1 管理項目", "1.1 住民データ", "住民データ管理", "氏名・生年月日・住所・続柄・在留資格 等の管理", "必須", "SCR-101"],
    ["F-1-2-01", "1 管理項目", "1.2 異動履歴データ", "異動履歴管理", "全異動の履歴データを完全保持（変更有無に依らず全項目）", "必須", "SCR-102"],
    ["F-1-3-01", "1 管理項目", "1.3 その他管理項目", "通称・旧氏・別字管理", "通称・旧氏・別字・読み仮名", "必須", "SCR-103"],
    ["F-2-1-01", "2 検索照会操作", "2.1 検索", "住民検索", "氏名・生年月日・住所・宛名番号・個人番号・住民票コード等の組合せ検索", "必須", "SCR-201"],
    ["F-2-2-01", "2 検索照会操作", "2.2 照会", "住民票照会", "1.2.1 異動履歴を用いた異動時点照会", "必須", "SCR-202"],
    ["F-2-3-01", "2 検索照会操作", "2.3 操作", "操作ログ", "操作履歴の保持と参照", "必須", "SCR-203"],
    ["F-3-00-01", "3 抑止", "3 抑止設定", "DV等支援措置抑止", "対象者の閲覧抑止・職員別解除権限管理", "必須", "SCR-301"],
    ["F-4-1-01", "4 異動", "4.1 届出", "転入届", "他市町村からの転入処理", "必須", "SCR-411"],
    ["F-4-1-02", "4 異動", "4.1 届出", "転出届", "他市町村への転出・転出証明書発行", "必須", "SCR-412"],
    ["F-4-1-03", "4 異動", "4.1 届出", "転居届", "市町村内住所変更", "必須", "SCR-413"],
    ["F-4-1-04", "4 異動", "4.1 届出", "世帯変更届", "世帯主変更・世帯分離・世帯合併", "必須", "SCR-414"],
    ["F-4-1-05", "4 異動", "4.1 届出", "出生届連動", "戸籍からの出生通知受領による住民登録", "必須", "SCR-415"],
    ["F-4-1-06", "4 異動", "4.1 届出", "死亡届連動", "戸籍からの死亡通知受領による消除", "必須", "SCR-416"],
    ["F-4-1-07", "4 異動", "4.1 届出", "婚姻・離婚・養子縁組等", "戸籍異動による住民登録反映", "必須", "SCR-417"],
    ["F-4-2-01", "4 異動", "4.2 職権", "職権記載・修正・消除", "起票・決裁・反映フロー", "必須", "SCR-421"],
    ["F-4-3-01", "4 異動", "4.3 住民票コード異動", "コード付番・変更・修正", "通知票発行（0010009/0010010/0010011）", "必須", "SCR-431"],
    ["F-4-4-01", "4 異動", "4.4 個人番号異動", "個人番号付番・変更・修正", "番号連携サーバ連携", "必須", "SCR-441"],
    ["F-4-5-01", "4 異動", "4.5 外国人住民", "在留資格・在留期間管理", "在留期間終了予告（0010012）", "必須", "SCR-451"],
    ["F-4-5-02", "4 異動", "4.5 外国人住民", "通称履歴管理", "外国人通称の登録・削除・履歴", "必須", "SCR-452"],
    ["F-4-6-01", "4 異動", "4.6 異動取消", "異動取消", "誤異動の取消処理（履歴は保持）", "必須", "SCR-461"],
    ["F-5-00-01", "5 証明", "5 証明", "住民票の写し（本人/世帯/連件連記）", "0010001/0010003 等の発行", "必須", "SCR-501"],
    ["F-5-00-02", "5 証明", "5 証明", "住民票記載事項証明書", "0010002 系", "必須", "SCR-502"],
    ["F-5-00-03", "5 証明", "5 証明", "住民票の除票の写し", "0010004", "必須", "SCR-503"],
    ["F-5-00-04", "5 証明", "5 証明", "住民基本台帳の一部の写し（閲覧用）", "0010005", "必須", "SCR-504"],
    ["F-5-00-05", "5 証明", "5 証明", "転出証明書", "0010007 / 0010008", "必須", "SCR-505"],
    ["F-5-00-06", "5 証明", "5 証明", "改ざん防止コード／QR付与", "0010001 ほか", "必須", "SCR-506"],
    ["F-5-00-07", "5 証明", "5 証明", "コンビニ交付連携", "J-LIS / 多機能端末", "必須", "SCR-507"],
    ["F-6-00-01", "6 統計", "6 統計", "住基年報", "20.6 様式群を自動出力", "必須", "SCR-601"],
    ["F-6-00-02", "6 統計", "6 統計", "人口動態集計", "月次・年次", "必須", "SCR-602"],
    ["F-6-00-03", "6 統計", "6 統計", "任意条件集計（EUC）", "10.1 EUC機能", "必須", "SCR-603"],
    ["F-7-1-01", "7 連携", "7.1 CS連携", "住基ネット連携", "本人確認情報の送受信", "必須", "—"],
    ["F-7-1-02", "7 連携", "7.1 番号連携", "番号連携", "符号取得、情報照会、情報提供", "必須", "—"],
    ["F-7-2-01", "7 連携", "7.2 庁内他業務連携", "税・国保・年金・選挙 等", "標準データ連携項目に従う", "必須", "—"],
    ["F-7-2-02", "7 連携", "7.2 申請管理連携", "申請管理システム連携", "オンライン申請の受領", "必須", "—"],
    ["F-8-1-01", "8 標準OP", "8.1 本人通知", "本人通知制度", "対象者への通知設定・発送", "オプション", "SCR-801"],
    ["F-8-2-01", "8 標準OP", "8.2 特別永住者", "特別永住者証明関連", "0010013/0010014 起票", "オプション", "SCR-802"],
    ["F-9-00-01", "9 バッチ", "9 バッチ", "夜間バッチ", "年齢到達、在留期間終了、コード一括", "必須", "—"],
    ["F-10-1-01", "10 共通", "10.1 EUC", "EUC機能", "データ抽出・加工・出力", "必須", "SCR-A01"],
    ["F-10-3-01", "10 共通", "10.3 権限管理", "権限ロール管理", "個人/世帯/抑止対象等の項目別非表示制御", "必須", "SCR-A02"],
    ["F-10-4-01", "10 共通", "10.4 操作権限設定", "ボタン/項目単位の表示・編集制御", "10.3を参照", "必須", "SCR-A03"],
    ["F-11-00-01", "11 エラー", "11 エラー・アラート", "標準アラート出力", "個人番号重複等の標準チェック", "必須", "SCR-A04"],
]
write_table(ws, 3, func[0], func[1:], widths=[12, 12, 18, 24, 60, 10, 10])

# ===== 画面一覧 =====
ws = wb.create_sheet("05_画面一覧")
title_row(ws, "画面一覧（SPA）", 1, 6)
scr = [
    ["画面ID", "画面名", "区分", "主要機能", "主要API", "備考"],
    ["SCR-001", "ログイン", "認証", "ID/PW + 2FA、ICカード（クライアント証明書）", "POST /auth/login", "ChromeOS Flex 対応"],
    ["SCR-002", "メインメニュー", "共通", "ロール別メニュー表示", "GET /me", "—"],
    ["SCR-101", "住民データ", "管理項目", "住民票一覧/個別表示/編集", "GET/PUT /residents/{id}", "—"],
    ["SCR-102", "異動履歴", "管理項目", "履歴一覧/時点照会", "GET /residents/{id}/history", "—"],
    ["SCR-103", "通称・旧氏管理", "管理項目", "通称・旧氏履歴", "GET/POST /residents/{id}/alias", "—"],
    ["SCR-201", "住民検索", "検索", "複合条件検索（抑止考慮）", "POST /residents/search", "—"],
    ["SCR-202", "住民票照会", "照会", "現在/時点指定", "GET /residents/{id}?asOf=", "—"],
    ["SCR-203", "操作ログ", "管理", "監査ログ閲覧", "GET /audit", "—"],
    ["SCR-301", "抑止設定", "抑止", "抑止対象登録・解除権限管理", "POST /restrictions", "—"],
    ["SCR-411", "転入届", "異動", "受付/審査/反映", "POST /transactions/in", "—"],
    ["SCR-412", "転出届", "異動", "受付/反映/転出証明", "POST /transactions/out", "—"],
    ["SCR-413", "転居届", "異動", "—", "POST /transactions/move", "—"],
    ["SCR-414", "世帯変更", "異動", "—", "POST /transactions/household", "—"],
    ["SCR-415", "出生連動", "異動", "戸籍通知受領", "POST /transactions/birth", "—"],
    ["SCR-416", "死亡連動", "異動", "戸籍通知受領", "POST /transactions/death", "—"],
    ["SCR-417", "戸籍異動連動", "異動", "婚姻・離婚等", "POST /transactions/koseki", "—"],
    ["SCR-421", "職権異動起票", "異動", "起票/決裁/反映", "POST /transactions/official", "—"],
    ["SCR-431", "住民票コード", "異動", "付番/変更/修正/通知票", "POST /codes/jumin", "—"],
    ["SCR-441", "個人番号", "異動", "付番/変更/修正", "POST /codes/mynumber", "—"],
    ["SCR-451", "在留情報", "異動", "在留資格・期間", "PUT /residents/{id}/foreigner", "—"],
    ["SCR-452", "外国人通称", "異動", "通称履歴", "—", "—"],
    ["SCR-461", "異動取消", "異動", "—", "POST /transactions/cancel", "—"],
    ["SCR-501", "住民票発行", "証明", "本人/世帯/連件", "POST /certificates/jumin", "PDF生成"],
    ["SCR-502", "記載事項証明", "証明", "—", "POST /certificates/items", "—"],
    ["SCR-503", "除票発行", "証明", "—", "POST /certificates/removed", "—"],
    ["SCR-504", "閲覧申請対応", "証明", "閲覧用一部写し", "POST /certificates/inspection", "—"],
    ["SCR-505", "転出証明発行", "証明", "—", "POST /certificates/out", "—"],
    ["SCR-506", "改ざん防止確認", "証明", "QR検証", "GET /verify/{token}", "外部公開"],
    ["SCR-507", "コンビニ交付確認", "証明", "J-LIS連携状態", "—", "—"],
    ["SCR-601", "住基年報", "統計", "様式群出力", "POST /reports/annual", "—"],
    ["SCR-602", "人口動態", "統計", "—", "POST /reports/population", "—"],
    ["SCR-603", "EUC", "統計", "任意SQL/テンプレ抽出", "POST /euc/query", "—"],
    ["SCR-801", "本人通知", "標準OP", "—", "POST /notify", "—"],
    ["SCR-802", "特別永住者", "標準OP", "—", "—", "—"],
    ["SCR-A01", "EUC設計", "共通", "—", "—", "—"],
    ["SCR-A02", "権限ロール管理", "共通", "—", "POST /roles", "—"],
    ["SCR-A03", "項目別操作権限", "共通", "—", "POST /permissions", "—"],
    ["SCR-A04", "エラー・アラート設定", "共通", "—", "—", "—"],
]
write_table(ws, 3, scr[0], scr[1:], widths=[10, 22, 12, 35, 32, 14])

# ===== 帳票一覧 =====
ws = wb.create_sheet("06_帳票一覧")
title_row(ws, "帳票一覧（標準仕様書 第4章）", 1, 6)
rep = [
    ["帳票ID", "帳票名", "対応節", "出力契機", "出力先", "備考"],
    ["0010001", "住民票の写し（日本人/外国人）", "20.1", "窓口/オンライン交付", "PDF/紙/コンビニ", "個別/世帯/連件連記"],
    ["0010002", "住民票記載事項証明書（日本人/外国人/世帯連記）", "20.1", "窓口", "PDF/紙", "—"],
    ["0010003", "住民票の写し（世帯連記）", "20.1", "窓口", "PDF/紙", "—"],
    ["0010004", "住民票の除票の写し", "20.1", "窓口", "PDF/紙", "—"],
    ["0010005", "住民基本台帳の一部の写し（閲覧用）", "20.2", "閲覧申請", "PDF/紙", "—"],
    ["0010006", "通知の場合の転入届・転出予定の転入届", "20.3", "窓口", "PDF/紙", "—"],
    ["0010007", "転出証明書", "20.3", "窓口", "PDF/紙", "—"],
    ["0010008", "転出証明書に準ずる証明書", "20.3", "窓口", "PDF/紙", "—"],
    ["0010009", "住民票コード通知票", "20.4", "コード付番時", "PDF/紙/郵送", "—"],
    ["0010010", "住民票コード変更通知票", "20.4", "コード変更時", "PDF/紙/郵送", "—"],
    ["0010011", "住民票コード修正通知票", "20.4", "コード修正時", "PDF/紙/郵送", "—"],
    ["0010012", "在留期間終了通知", "20.5", "バッチ", "PDF/紙/郵送", "—"],
    ["0010013", "通称名変更通知書", "20.5", "通称変更時", "PDF/紙", "—"],
    ["0010014", "通称名変更依頼通知書", "20.5", "外国人通称", "PDF/紙", "—"],
    ["0010015", "住所異動届受理通知", "20.5", "受理時", "PDF/紙", "—"],
    ["0010016", "職権記載・修正・消除通知書", "20.5", "職権処理時", "PDF/紙", "—"],
    ["0010017", "成年後見人異動通知", "20.5", "—", "PDF/紙", "—"],
    ["0010018", "住居表示実施通知書", "20.5", "—", "PDF/紙", "—"],
    ["0010019", "町名整理に伴う住所変更通知", "20.5", "—", "PDF/紙", "—"],
    ["年報-1〜n", "住民基本台帳関係年報", "20.6", "年次バッチ", "Excel/PDF", "—"],
]
write_table(ws, 3, rep[0], rep[1:], widths=[10, 36, 8, 18, 18, 16])

# ===== 連携一覧 =====
ws = wb.create_sheet("07_連携一覧")
title_row(ws, "連携一覧", 1, 6)
itf = [
    ["連携ID", "連携先", "区分", "方式", "通信", "概要"],
    ["IF-CS-01", "住基ネットCS", "国機関", "本人確認情報送受信", "VPN/HTTPS/XML", "標準仕様書 7.1"],
    ["IF-NUM-01", "番号連携サーバ（J-LIS等）", "国機関", "符号取得/情報照会/情報提供", "VPN/HTTPS", "番号法"],
    ["IF-KSK-01", "戸籍システム", "庁内", "出生・死亡・婚姻・離婚通知", "REST/MQ", "標準仕様書 7.2"],
    ["IF-TAX-01", "税システム", "庁内", "住民データ提供", "REST/MQ", "—"],
    ["IF-INS-01", "国保・後期高齢", "庁内", "資格管理用住民データ", "REST/MQ", "—"],
    ["IF-SEN-01", "選挙人名簿", "庁内", "選挙人異動連携", "REST", "—"],
    ["IF-APP-01", "申請管理システム", "庁内/外", "オンライン申請受領", "REST", "申請管理標準仕様書"],
    ["IF-CVS-01", "コンビニ交付センター", "外部", "証明書交付", "J-LIS規定", "—"],
    ["IF-MNP-01", "マイナポータル", "外部", "本人通知・転出予定届", "REST", "デジタル手続法"],
]
write_table(ws, 3, itf[0], itf[1:], widths=[12, 26, 10, 28, 18, 36])

# ===== データモデル =====
ws = wb.create_sheet("08_データモデル")
title_row(ws, "主要データモデル", 1, 6)
section(ws, "■ 主要テーブル", 3, 6)
tbl = [
    ["テーブル名", "論理名", "主キー", "概要", "標準仕様書 出典"],
    ["resident", "住民", "resident_id", "宛名番号で識別される住民1人。日本人/外国人共通項目。", "1.1"],
    ["resident_foreigner", "住民（外国人付加）", "resident_id", "在留資格・在留期間・通称等", "1.1 / 4.5"],
    ["household", "世帯", "household_id", "世帯主・世帯構成", "1.1"],
    ["household_member", "世帯員", "(household_id, resident_id)", "続柄・加入日", "1.1"],
    ["transaction", "異動", "transaction_id", "届出/職権/取消/連動の単位", "4"],
    ["transaction_item", "異動項目", "(transaction_id, field)", "変更前後の項目値（全項目保持）", "1.2"],
    ["resident_history", "住民履歴", "(resident_id, valid_from)", "時点照会のためのSCD-2履歴", "1.2"],
    ["jumin_code", "住民票コード", "resident_id", "現行・履歴", "4.3"],
    ["my_number", "個人番号", "resident_id", "現行・履歴・付番情報", "4.4"],
    ["alias_name", "通称・旧氏", "alias_id", "通称履歴／旧氏履歴", "1.3 / 4.5"],
    ["restriction", "抑止", "restriction_id", "DV等支援措置、解除条件、対象範囲", "3"],
    ["certificate_issue", "証明発行", "issue_id", "発行履歴、改ざん防止コード", "5"],
    ["audit_log", "監査ログ", "log_id", "全操作の不可逆ログ", "2.3 / 10"],
    ["user_account", "ユーザ", "user_id", "ICカード・OIDC subject", "10"],
    ["role", "ロール", "role_id", "—", "10.3"],
    ["permission", "権限", "(role_id, resource, action)", "項目別表示/編集", "10.3 / 10.4"],
    ["link_partner", "連携先", "partner_id", "CS/番号/庁内他業務 等", "7"],
    ["report_request", "帳票出力", "report_id", "—", "4章"],
]
write_table(ws, 4, tbl[0], tbl[1:], widths=[20, 18, 22, 50, 14])

section(ws, "■ resident（住民）主要カラム抜粋", 25, 6)
cols = [
    ["#", "カラム", "型", "桁", "必須", "備考"],
    [1, "resident_id", "varchar", "20", "○", "宛名番号"],
    [2, "jumin_code", "varchar", "11", "○", "住民票コード"],
    [3, "my_number", "varchar", "12", "△", "個人番号（外国人/日本人共通）"],
    [4, "family_name_kanji", "varchar", "100", "○", "氏（漢字）"],
    [5, "given_name_kanji", "varchar", "100", "○", "名（漢字）"],
    [6, "family_name_kana", "varchar", "100", "○", "氏（カナ）"],
    [7, "given_name_kana", "varchar", "100", "○", "名（カナ）"],
    [8, "birth_date", "date", "—", "○", "生年月日"],
    [9, "sex", "char", "1", "○", "性別"],
    [10, "household_id", "varchar", "20", "○", "世帯ID"],
    [11, "relation_to_head", "varchar", "20", "○", "続柄"],
    [12, "address_code", "varchar", "13", "○", "住所コード（街区まで）"],
    [13, "address_text", "varchar", "200", "○", "住所表記"],
    [14, "moved_in_date", "date", "—", "○", "住民となった年月日"],
    [15, "moved_out_date", "date", "—", "—", "消除日"],
    [16, "nationality", "varchar", "3", "—", "外国人のみ"],
    [17, "residence_status", "varchar", "40", "—", "在留資格"],
    [18, "residence_period_end", "date", "—", "—", "在留期間満了日"],
    [19, "alias_kanji", "varchar", "100", "—", "通称"],
    [20, "former_family_name", "varchar", "100", "—", "旧氏"],
    [21, "restricted_flag", "boolean", "—", "○", "抑止対象（参照は抑止テーブル）"],
    [22, "valid_from", "timestamp", "—", "○", "履歴管理"],
    [23, "valid_to", "timestamp", "—", "—", "履歴管理"],
    [24, "created_at / updated_at", "timestamp", "—", "○", "—"],
]
write_table(ws, 26, cols[0], cols[1:], widths=[5, 28, 10, 6, 6, 40])

# ===== API一覧 =====
ws = wb.create_sheet("09_API一覧")
title_row(ws, "REST API 一覧", 1, 6)
api = [
    ["API-ID", "Method", "Path", "概要", "認可", "備考"],
    ["API-AUTH-01", "POST", "/auth/login", "ログイン", "公開", "OIDC/ICカード"],
    ["API-AUTH-02", "POST", "/auth/logout", "ログアウト", "認証", "—"],
    ["API-RES-01", "POST", "/residents/search", "住民検索", "閲覧", "抑止考慮"],
    ["API-RES-02", "GET", "/residents/{id}", "住民詳細", "閲覧", "asOf指定可"],
    ["API-RES-03", "PUT", "/residents/{id}", "住民更新", "編集", "—"],
    ["API-RES-04", "GET", "/residents/{id}/history", "異動履歴", "閲覧", "—"],
    ["API-TRN-01", "POST", "/transactions/in", "転入処理", "編集", "—"],
    ["API-TRN-02", "POST", "/transactions/out", "転出処理", "編集", "—"],
    ["API-TRN-03", "POST", "/transactions/move", "転居処理", "編集", "—"],
    ["API-TRN-04", "POST", "/transactions/household", "世帯変更", "編集", "—"],
    ["API-TRN-05", "POST", "/transactions/official", "職権異動", "編集/決裁", "—"],
    ["API-TRN-06", "POST", "/transactions/cancel", "異動取消", "編集", "—"],
    ["API-CRT-01", "POST", "/certificates/jumin", "住民票発行", "証明", "—"],
    ["API-CRT-02", "POST", "/certificates/items", "記載事項証明", "証明", "—"],
    ["API-CRT-03", "POST", "/certificates/out", "転出証明", "証明", "—"],
    ["API-CRT-04", "GET", "/verify/{token}", "改ざん防止検証", "公開", "QRコード参照"],
    ["API-RPT-01", "POST", "/reports/annual", "住基年報", "統計", "—"],
    ["API-RPT-02", "POST", "/reports/population", "人口動態", "統計", "—"],
    ["API-EUC-01", "POST", "/euc/query", "EUCクエリ", "EUC", "テンプレ/任意"],
    ["API-RST-01", "POST", "/restrictions", "抑止登録", "管理", "—"],
    ["API-LNK-01", "POST", "/link/cs/inbound", "CS連携 入力", "システム", "—"],
    ["API-LNK-02", "POST", "/link/number/inbound", "番号連携 入力", "システム", "—"],
    ["API-LNK-03", "POST", "/link/internal/{partner}", "庁内他業務", "システム", "—"],
    ["API-LNK-04", "POST", "/link/application/inbound", "申請管理連携", "システム", "—"],
    ["API-ADM-01", "GET", "/audit", "監査ログ参照", "管理", "—"],
]
write_table(ws, 3, api[0], api[1:], widths=[14, 8, 36, 26, 12, 14])

# ===== 認証権限 =====
ws = wb.create_sheet("10_認証権限")
title_row(ws, "認証・権限", 1, 6)
section(ws, "■ 認証方式", 3, 6)
auth = [
    ["#", "方式", "用途", "備考"],
    [1, "OIDC + 2FA（TOTP/プッシュ）", "通常職員ログイン", "ChromeOS Flex で動作するブラウザ実装"],
    [2, "クライアント証明書（ICカード）", "高権限操作", "Chrome の証明書ストア利用"],
    [3, "システム間 mTLS + OAuth2 Client Credentials", "連携API", "—"],
]
write_table(ws, 4, auth[0], auth[1:], widths=[5, 38, 26, 36])

section(ws, "■ ロール（例）", 10, 6)
roles = [
    ["ロール", "業務範囲", "標準仕様書 参照"],
    ["窓口担当", "住民検索・住民票交付・届出受付", "5 / 2"],
    ["異動審査", "届出審査・職権起票", "4"],
    ["世帯主・抑止管理", "DV等抑止登録・解除", "3"],
    ["証明発行", "住民票・除票・転出証明", "5"],
    ["統計担当", "住基年報・人口動態・EUC", "6 / 10.1"],
    ["システム管理者", "ユーザ・権限・ログ", "10"],
    ["決裁者", "職権異動の決裁", "4.2"],
    ["連携システム", "API限定", "7"],
]
write_table(ws, 11, roles[0], roles[1:], widths=[20, 50, 16])

section(ws, "■ 項目別操作権限（標準仕様書 10.4）", 22, 6)
perm = [
    ["#", "項目", "表示", "編集", "備考"],
    [1, "個人番号", "ロール限定", "ロール限定", "通常窓口は非表示可"],
    [2, "住民票コード", "ロール限定", "ロール限定", "—"],
    [3, "支援措置対象者の住所", "抑止解除権限のみ", "抑止解除権限のみ", "—"],
    [4, "他社の表示／立寄り情報", "設定で非表示可", "—", "10.4"],
]
write_table(ws, 23, perm[0], perm[1:], widths=[5, 30, 20, 20, 36])

# ===== 非機能要件 =====
ws = wb.create_sheet("11_非機能要件")
title_row(ws, "非機能要件", 1, 5)
nfr = [
    ["カテゴリ", "項目", "要求水準", "実現方式"],
    ["性能", "オンライン応答", "95% < 2秒（住民検索）", "RDBインデックス + キャッシュ"],
    ["性能", "証明書発行", "1件 < 5秒", "PDF生成サーバ並列化"],
    ["可用性", "稼働率", "99.9%以上", "AZ分散、ローリングアップデート"],
    ["可用性", "RPO/RTO", "RPO 15分 / RTO 1時間", "WALアーカイブ＋クロスAZレプリカ"],
    ["拡張性", "同時接続", "1,000セッション", "水平スケール"],
    ["セキュリティ", "通信", "TLS 1.3", "—"],
    ["セキュリティ", "保存", "AES-256 / 列暗号化（個人番号）", "クラウドKMS"],
    ["セキュリティ", "監査", "全操作ログ7年保持", "WORMストレージ"],
    ["セキュリティ", "脆弱性", "OWASP ASVS Lv2 相当", "—"],
    ["可搬性", "クライアント", "ChromeOS Flex / Chrome / Edge 最新2世代", "PWA"],
    ["運用", "バッチ", "夜間バッチ枠 22:00-06:00", "—"],
    ["運用", "監視", "メトリクス・ログ・トレース統合", "OpenTelemetry"],
    ["運用", "移行", "現行住基からの一括移行ツール", "—"],
    ["アクセシビリティ", "—", "JIS X 8341-3:2016 AA", "—"],
    ["国際化", "—", "外国人住民氏名（拡張漢字・ASCII）対応", "Unicode IVS"],
]
write_table(ws, 3, nfr[0], nfr[1:], widths=[16, 22, 38, 38])

# ===== 技術スタック =====
ws = wb.create_sheet("12_技術スタック")
title_row(ws, "技術スタック（ChromeOS Flex 対応）", 1, 5)
section(ws, "■ クライアント要件", 3, 5)
cli = [
    ["#", "項目", "内容", "備考"],
    [1, "OS", "ChromeOS Flex / Windows 10/11 / macOS", "—"],
    [2, "ブラウザ", "Google Chrome / Microsoft Edge（Chromium）最新2世代", "ChromeOS Flexで標準提供"],
    [3, "アプリ形態", "PWA（Service Worker / Web App Manifest）", "オフライン時の最低限の読取・下書き対応"],
    [4, "認証", "OIDC + 2FA、WebAuthn、ICカード（クライアント証明書 / PKCS#11 拡張）", "ChromeOS Flexで利用可能"],
    [5, "印刷", "Web Print API（ブラウザ印刷ダイアログ）", "サーバ生成PDF前提"],
    [6, "スキャナ", "USB スキャナ → Webアプリへ画像アップロード", "ChromeOSはUSBデバイス利用可"],
    [7, "ICカードリーダ", "ブラウザ拡張 or 中継エージェント（クラウドエージェント）", "ChromeOS Flex の対応可否は機種依存：本書はWebアプリ要件のみ規定"],
]
write_table(ws, 4, cli[0], cli[1:], widths=[5, 18, 50, 30])

section(ws, "■ サーバ／インフラ", 13, 5)
srv = [
    ["#", "項目", "内容", "備考"],
    [1, "フロントエンド", "React 18 + TypeScript + Vite + React Router + TanStack Query", "—"],
    [2, "UI", "MUI（Material UI） + 自治体共通テンプレ準拠", "—"],
    [3, "バックエンド", "Java 21 + Spring Boot 3 もしくは .NET 8 + ASP.NET Core", "選定は調達条件に従う"],
    [4, "DB", "PostgreSQL 16（マネージド）", "高可用性構成"],
    [5, "検索", "PostgreSQL pg_trgm + 必要に応じ OpenSearch", "氏名・住所のあいまい検索"],
    [6, "メッセージング", "クラウドネイティブMQ（例: Amazon SQS, Pub/Sub）", "連携用"],
    [7, "帳票", "Apache POI / OpenHTMLtoPDF / Playwright Print to PDF", "PDF/A-2b"],
    [8, "コンテナ", "Docker + Kubernetes", "GKE/EKS/AKS いずれか"],
    [9, "CI/CD", "GitHub Actions / GitLab CI", "—"],
    [10, "IaC", "Terraform + Helm", "—"],
    [11, "認証基盤", "OpenID Connect（Keycloak 等）+ WebAuthn", "—"],
    [12, "監視", "OpenTelemetry + Prometheus + Grafana + Loki", "—"],
]
write_table(ws, 14, srv[0], srv[1:], widths=[5, 18, 50, 30])

# ===== エラー一覧 =====
ws = wb.create_sheet("13_エラー一覧")
title_row(ws, "エラー・アラート一覧（標準仕様書 第11章）", 1, 5)
err = [
    ["No", "区分", "判定タイミング", "メッセージ例", "備考"],
    [1, "エラー", "個人番号入力", "個人番号のチェックデジットが不正です", "—"],
    [2, "エラー", "個人番号付番", "個人番号が他者と重複しています", "標準仕様書 No.7（4エラー）"],
    [3, "エラー", "住民票コード入力", "住民票コードのチェックデジットが不正です", "—"],
    [4, "エラー", "住民票コード付番", "住民票コードが他者と重複しています", "—"],
    [5, "アラート", "転入処理", "前住所に転出記録がありません", "—"],
    [6, "アラート", "世帯変更", "世帯主の続柄が整合しません", "—"],
    [7, "アラート", "在留期間", "在留期間満了が30日以内です", "0010012 発行対象"],
    [8, "アラート", "氏名漢字", "外字または非IVS文字が含まれます", "—"],
    [9, "アラート", "DV等支援措置", "閲覧抑止対象者です。閲覧解除権限が必要です", "3"],
    [10, "アラート", "EUC実行", "個人番号項目が出力対象に含まれています", "10.1"],
]
write_table(ws, 3, err[0], err[1:], widths=[5, 12, 22, 40, 16])

# ===== バッチ =====
ws = wb.create_sheet("14_バッチ一覧")
title_row(ws, "バッチ一覧（標準仕様書 第9章）", 1, 5)
bat = [
    ["バッチID", "バッチ名", "起動契機", "概要", "出力"],
    ["BAT-01", "年齢到達バッチ", "夜間日次", "選挙人名簿・国民年金・国保 等への年齢到達連携", "他業務連携"],
    ["BAT-02", "在留期間チェック", "夜間日次", "在留期間満了30日前抽出", "0010012 アラート"],
    ["BAT-03", "住民票コード一括付番", "オンデマンド", "未付番者の一括付番", "通知票"],
    ["BAT-04", "住基年報集計", "年次", "様式群作成", "Excel/PDF"],
    ["BAT-05", "人口動態集計", "月次", "—", "Excel/PDF"],
    ["BAT-06", "監査ログアーカイブ", "日次", "ホットストレージ→コールド", "—"],
    ["BAT-07", "CS連携バッチ", "日次/随時", "本人確認情報の一括交換", "—"],
    ["BAT-08", "EUC定期抽出", "週次/月次", "事前登録テンプレ実行", "CSV/Excel"],
]
write_table(ws, 3, bat[0], bat[1:], widths=[10, 22, 18, 40, 14])

# ===== 開発計画 =====
ws = wb.create_sheet("15_開発計画")
title_row(ws, "開発計画", 1, 5)
section(ws, "■ 工程概略", 3, 5)
plan = [
    ["#", "工程", "主要成果物", "目安期間"],
    [1, "要件分析・要件定義", "要件定義書、業務フロー、画面遷移、データ要件", "3ヶ月"],
    [2, "基本設計", "本書（精緻化）、ER図、API仕様、画面設計", "3ヶ月"],
    [3, "詳細設計・実装", "ソース、単体テスト", "9ヶ月"],
    [4, "結合・連携テスト", "結合シナリオ、連携先擬似環境", "3ヶ月"],
    [5, "総合・受入・運用テスト", "総合試験報告、運用手順", "3ヶ月"],
    [6, "移行・本稼働", "移行ツール、移行リハーサル", "2ヶ月"],
]
write_table(ws, 4, plan[0], plan[1:], widths=[5, 22, 50, 14])

section(ws, "■ 体制（例）", 13, 5)
team = [
    ["役割", "人数", "備考"],
    ["プロジェクトマネージャ", "1", "—"],
    ["業務リーダ（住基有識者）", "2", "標準仕様書解釈"],
    ["アーキテクト", "1", "—"],
    ["フロントエンドエンジニア", "3〜5", "React/TS"],
    ["バックエンドエンジニア", "5〜8", "Java/Spring 等"],
    ["DBエンジニア", "2", "PostgreSQL"],
    ["インフラ／SRE", "2", "クラウド／K8s"],
    ["セキュリティ", "1", "—"],
    ["テスト／QA", "3", "—"],
    ["移行担当", "2", "—"],
]
write_table(ws, 14, team[0], team[1:], widths=[28, 8, 30])

section(ws, "■ ChromeOS Flex特有のリスクと対策", 26, 5)
risks = [
    ["#", "リスク", "対策"],
    [1, "ICカードリーダの公式ドライバ不在の機種がある", "クライアント中継エージェント or 認証はWebAuthn(FIDO2)で代替可能な設計とする"],
    [2, "プリンタドライバ未提供機種", "サーバ側でPDF/Aを生成し、クラウド印刷（Google クラウド プリント代替/IPP Everywhere）対応プリンタを必須とする"],
    [3, "業務ピーク時のリソース不足", "PWAでクライアント側に検索キャッシュ・帳票プレビューを保持"],
    [4, "オフライン", "PWAキャッシュ＋オフライン下書き機能（送信は復帰後）"],
    [5, "ブラウザ更新によるUI崩れ", "Chromium最新2世代を継続的にE2Eテスト"],
]
write_table(ws, 27, risks[0], risks[1:], widths=[5, 36, 50])

wb.save(OUT)
print("saved:", OUT)
