#!/usr/bin/env python3
"""Extract source workbook previews for the smart-canteen product map task."""

from __future__ import annotations

import csv
from pathlib import Path

from openpyxl import load_workbook


ROOT = Path(__file__).resolve().parents[2]
OUT = ROOT / "2026-05-17-smart-canteen-product-map-excel" / "outputs"
OUT.mkdir(parents=True, exist_ok=True)

FILES = [
    (
        "nutrition_settlement_plan",
        Path("/Users/jack/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/A74DF84611CC2ED79C4C206E40B5D80E/Caches/Files/2026-05/4a9fefa72eb8a310768283eaeae05fce/营养结算标准产品规划_三页整合版_20260506.xlsx"),
    ),
    (
        "scenario_product_plan",
        Path("/Users/jack/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/A74DF84611CC2ED79C4C206E40B5D80E/Caches/Files/2026-05/cd81e0e35f6fa2472bd75b153d8d3ac5/【】康比特_智慧营养健康餐厅_场景化产品规划_对外.xlsx"),
    ),
    ("product_graph_original", Path("/Users/jack/Downloads/产品图.xlsx")),
]


def cell_text(value: object) -> str:
    if value is None:
        return ""
    return str(value).replace("\n", " / ").strip()


def main() -> None:
    summary_rows: list[dict[str, object]] = []
    for source_id, file_path in FILES:
        wb = load_workbook(file_path, read_only=False, data_only=False)
        for ws in wb.worksheets:
            rows = []
            for row in ws.iter_rows(
                min_row=1,
                max_row=min(ws.max_row or 1, 80),
                min_col=1,
                max_col=min(ws.max_column or 1, 24),
                values_only=True,
            ):
                values = [cell_text(v) for v in row]
                if any(values):
                    rows.append(values)

            extract_path = OUT / f"{source_id}__{ws.title}.csv"
            with extract_path.open("w", encoding="utf-8-sig", newline="") as f:
                writer = csv.writer(f)
                writer.writerows(rows)

            summary_rows.append(
                {
                    "source_id": source_id,
                    "file_path": str(file_path),
                    "sheet_name": ws.title,
                    "max_row": ws.max_row,
                    "max_column": ws.max_column,
                    "extract_csv": str(extract_path.relative_to(ROOT)),
                }
            )
        wb.close()

    with (OUT / "source-workbook-summary.csv").open("w", encoding="utf-8-sig", newline="") as f:
        writer = csv.DictWriter(
            f,
            fieldnames=["source_id", "file_path", "sheet_name", "max_row", "max_column", "extract_csv"],
        )
        writer.writeheader()
        writer.writerows(summary_rows)


if __name__ == "__main__":
    main()