#!/usr/bin/env python3
"""Archive an exported WeCom/Wedoc online sheet into searchable project files."""

from __future__ import annotations

import argparse
import csv
import html
import re
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path
from typing import Iterable

from openpyxl import load_workbook


@dataclass
class SheetArchive:
    title: str
    row_count: int
    column_count: int
    non_empty_rows: int
    csv_path: Path
    markdown_path: Path
    headers: list[str]


def slugify(value: str) -> str:
    value = re.sub(r"\s+", "-", value.strip())
    value = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff._-]+", "-", value)
    return value.strip("-") or "sheet"


def cell_text(value: object) -> str:
    if value is None:
        return ""
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d")
    return str(value).replace("\r\n", "\n").replace("\r", "\n").strip()


def normalize_headers(row: list[str], width: int) -> list[str]:
    headers: list[str] = []
    seen: dict[str, int] = {}
    for idx in range(width):
        raw = row[idx].strip() if idx < len(row) and row[idx].strip() else f"字段{idx + 1}"
        count = seen.get(raw, 0) + 1
        seen[raw] = count
        headers.append(raw if count == 1 else f"{raw}_{count}")
    return headers


def iter_rows(ws) -> Iterable[list[str]]:
    for row in ws.iter_rows(values_only=True):
        yield [cell_text(value) for value in row]


def trim_width(rows: list[list[str]]) -> int:
    width = 0
    for row in rows:
        for idx, value in enumerate(row, start=1):
            if value:
                width = max(width, idx)
    return width


def write_csv(path: Path, headers: list[str], rows: list[list[str]]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    with path.open("w", newline="", encoding="utf-8") as handle:
        writer = csv.writer(handle)
        writer.writerow(headers)
        writer.writerows(rows)


def markdown_escape(value: str) -> str:
    return value.replace("|", "\\|").replace("\n", "<br>")


def write_markdown(path: Path, title: str, headers: list[str], rows: list[list[str]]) -> None:
    lines = [f"# {title}", "", f"- 字段数：{len(headers)}", f"- 内容行数：{len(rows)}", ""]
    lines.append("| " + " | ".join(markdown_escape(header) for header in headers) + " |")
    lines.append("| " + " | ".join("---" for _ in headers) + " |")
    for row in rows:
        padded = row + [""] * (len(headers) - len(row))
        lines.append("| " + " | ".join(markdown_escape(value) for value in padded[: len(headers)]) + " |")
    path.write_text("\n".join(lines) + "\n", encoding="utf-8")


def archive_sheet(ws, output_dir: Path) -> SheetArchive:
    raw_rows = list(iter_rows(ws))
    width = trim_width(raw_rows)
    if width == 0:
        width = max(ws.max_column, 1)
    trimmed = [row[:width] + [""] * max(0, width - len(row)) for row in raw_rows]
    header_row_index = 0
    for idx, row in enumerate(trimmed):
        if sum(1 for value in row if value) >= 2:
            header_row_index = idx
            break
    headers = normalize_headers(trimmed[header_row_index], width)
    data_rows = [
        row
        for row in trimmed[header_row_index + 1 :]
        if any(value for value in row)
    ]

    sheet_slug = slugify(ws.title)
    csv_path = output_dir / "sheets" / f"{sheet_slug}.csv"
    markdown_path = output_dir / "sheets" / f"{sheet_slug}.md"
    write_csv(csv_path, headers, data_rows)
    write_markdown(markdown_path, ws.title, headers, data_rows)

    return SheetArchive(
        title=ws.title,
        row_count=ws.max_row,
        column_count=ws.max_column,
        non_empty_rows=len(data_rows),
        csv_path=csv_path,
        markdown_path=markdown_path,
        headers=headers,
    )


def write_summary(
    output_dir: Path,
    title: str,
    online_url: str,
    export_path: Path,
    sha256: str,
    exported_at: str,
    archives: list[SheetArchive],
) -> None:
    total_rows = sum(item.non_empty_rows for item in archives)
    summary_lines = [
        f"# {title} 在线表格归档",
        "",
        "## 归档目标",
        "",
        "将企业微信在线表格内容转成 `zhctprompt` 内可检索、可恢复、可复用的 Markdown/CSV 资产。原始在线表格仍是协作真源，本目录保存导出快照和派生索引。",
        "",
        "## 来源",
        "",
        f"- 在线表格 URL：{online_url}",
        f"- 本机导出文件：`{export_path}`",
        f"- 导出时间：{exported_at}",
        f"- 导出文件 SHA256：`{sha256}`",
        "- 原始 xlsx 是否进入 Git：否",
        "",
        "## 内容结构",
        "",
        "| sheet | 内容行数 | 字段数 | CSV | Markdown |",
        "| --- | ---: | ---: | --- | --- |",
    ]
    for item in archives:
        summary_lines.append(
            "| {title} | {rows} | {cols} | `{csv}` | `{md}` |".format(
                title=item.title,
                rows=item.non_empty_rows,
                cols=len(item.headers),
                csv=item.csv_path.relative_to(output_dir),
                md=item.markdown_path.relative_to(output_dir),
            )
        )
    summary_lines.extend(
        [
            "",
            "## 归档结果",
            "",
            f"- sheet 数：{len(archives)}",
            f"- 内容行数合计：{total_rows}",
            "- 已生成完整 CSV 和 Markdown；CSV 适合结构化检索，Markdown 适合 AI 直接阅读。",
            "",
            "## 边界",
            "",
            "- 本次归档基于 2026-05-21 13:34 左右的在线表格导出快照，不代表后续在线表格实时状态。",
            "- 若在线表格继续编辑，应重新导出并用同一脚本覆盖本目录派生产物，同时更新 SHA256 和导出时间。",
            "- 表格包含内部项目问题、人员姓名、外部链接和问题原因，默认仅作团队内部知识库使用。",
        ]
    )
    (output_dir / "summary.md").write_text("\n".join(summary_lines) + "\n", encoding="utf-8")


def write_source_index(
    output_dir: Path,
    title: str,
    online_url: str,
    export_path: Path,
    sha256: str,
    exported_at: str,
    archives: list[SheetArchive],
) -> None:
    with (output_dir / "source-index.csv").open("w", newline="", encoding="utf-8") as handle:
        writer = csv.DictWriter(
            handle,
            fieldnames=[
                "source_id",
                "title",
                "source_type",
                "online_url",
                "local_export_path",
                "sha256",
                "exported_at",
                "sheet_count",
                "content_rows",
                "archive_status",
                "notes",
            ],
        )
        writer.writeheader()
        writer.writerow(
            {
                "source_id": "WEDOC-SHEET-20260521-PROBLEM-QA",
                "title": title,
                "source_type": "enterprise_wechat_wedoc_sheet_export",
                "online_url": online_url,
                "local_export_path": str(export_path),
                "sha256": sha256,
                "exported_at": exported_at,
                "sheet_count": len(archives),
                "content_rows": sum(item.non_empty_rows for item in archives),
                "archive_status": "derived-content-archived",
                "notes": "原始在线表格仍在企业微信；本目录保存导出快照的 CSV/Markdown 派生产物。",
            }
        )


def write_review_html(output_dir: Path, title: str, online_url: str, archives: list[SheetArchive]) -> None:
    rows = "\n".join(
        "<tr><td>{}</td><td>{}</td><td>{}</td><td><code>{}</code></td></tr>".format(
            html.escape(item.title),
            item.non_empty_rows,
            len(item.headers),
            html.escape(str(item.csv_path.relative_to(output_dir))),
        )
        for item in archives
    )
    body = f"""<!doctype html>
<html lang="zh-CN">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>{html.escape(title)} 在线表格归档 Review</title>
  <style>
    body {{ font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif; margin: 0; color: #18202a; background: #f7f8fa; }}
    main {{ max-width: 1040px; margin: 0 auto; padding: 40px 24px 56px; }}
    h1 {{ font-size: 32px; margin: 0 0 12px; }}
    h2 {{ margin-top: 32px; font-size: 20px; }}
    p {{ line-height: 1.7; }}
    .panel {{ background: #fff; border: 1px solid #e3e6ea; border-radius: 8px; padding: 22px; margin-top: 18px; }}
    table {{ width: 100%; border-collapse: collapse; margin-top: 12px; background: #fff; }}
    th, td {{ border-bottom: 1px solid #e8ebef; padding: 10px 12px; text-align: left; vertical-align: top; }}
    th {{ background: #f0f3f6; font-weight: 650; }}
    code {{ white-space: normal; color: #233a5e; }}
    .meta {{ color: #5f6b7a; }}
  </style>
</head>
<body>
<main>
  <h1>{html.escape(title)} 在线表格归档</h1>
  <p class="meta">企业微信在线表格导出快照已转成项目内 Markdown/CSV。在线表格仍是协作真源，本页面用于人审归档范围和后续更新方式。</p>
  <section class="panel">
    <h2>来源</h2>
    <p><code>{html.escape(online_url)}</code></p>
  </section>
  <section class="panel">
    <h2>内容结构</h2>
    <table>
      <thead><tr><th>Sheet</th><th>内容行数</th><th>字段数</th><th>CSV</th></tr></thead>
      <tbody>{rows}</tbody>
    </table>
  </section>
  <section class="panel">
    <h2>Review Checklist</h2>
    <p>确认这份快照是否可作为当前团队问题复盘知识库基线；若在线表格继续更新，需重新导出并覆盖派生产物，同时更新 source-index 和 SHA256。</p>
  </section>
</main>
</body>
</html>
"""
    (output_dir / "review.html").write_text(body, encoding="utf-8")


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser()
    parser.add_argument("--xlsx", required=True, type=Path)
    parser.add_argument("--title", required=True)
    parser.add_argument("--online-url", required=True)
    parser.add_argument("--output-dir", required=True, type=Path)
    parser.add_argument("--sha256", required=True)
    parser.add_argument("--exported-at", required=True)
    return parser.parse_args()


def main() -> int:
    args = parse_args()
    args.output_dir.mkdir(parents=True, exist_ok=True)
    workbook = load_workbook(args.xlsx, read_only=True, data_only=True)
    archives = [archive_sheet(ws, args.output_dir) for ws in workbook.worksheets]
    write_summary(
        args.output_dir,
        args.title,
        args.online_url,
        args.xlsx,
        args.sha256,
        args.exported_at,
        archives,
    )
    write_source_index(
        args.output_dir,
        args.title,
        args.online_url,
        args.xlsx,
        args.sha256,
        args.exported_at,
        archives,
    )
    write_review_html(args.output_dir, args.title, args.online_url, archives)
    print(f"archived {len(archives)} sheets into {args.output_dir}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())