#!/usr/bin/env python3
"""Generate fillable smart-canteen presales intake templates.

Inputs are the normalized CSV files extracted from the WeWork cached workbook.
Outputs are generated artifacts for field collection and human review. The
original WeWork binary is not copied.
"""

from __future__ import annotations

import csv
import html
from collections import defaultdict
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter


ROOT = Path(__file__).resolve().parents[1]
FIELD_CSV = ROOT / "presales-intake-field-list.csv"
REQ_CSV = ROOT / "presales-requirement-seed.csv"
PHOTO_CSV = ROOT / "presales-photo-checklist.csv"
RISK_CSV = ROOT / "presales-risk-seed.csv"
OUT_DIR = ROOT / "templates"
OUT_XLSX = OUT_DIR / "智慧食堂售前信息采集模板_标准版_20260516.xlsx"
OUT_HTML = OUT_DIR / "smart-canteen-presales-intake-form.html"
OUT_README = OUT_DIR / "README.md"


def read_csv(path: Path) -> list[dict[str, str]]:
    with path.open(encoding="utf-8-sig", newline="") as f:
        return list(csv.DictReader(f))


def setup_sheet(ws, widths: dict[str, int]) -> None:
    ws.freeze_panes = "A2"
    for col, width in widths.items():
        ws.column_dimensions[col].width = width
    ws.sheet_view.showGridLines = False


def style_header(ws, row: int = 1) -> None:
    fill = PatternFill("solid", fgColor="DDEFEA")
    border = Border(bottom=Side(style="thin", color="B7C8D6"))
    for cell in ws[row]:
        cell.fill = fill
        cell.font = Font(bold=True, color="173B33")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = border


def append_table(ws, headers: list[str], rows: list[list[str]]) -> None:
    ws.append(headers)
    style_header(ws)
    for row in rows:
        ws.append(row)
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.alignment = Alignment(vertical="top", wrap_text=True)


def generate_workbook(fields: list[dict[str, str]], reqs: list[dict[str, str]], photos: list[dict[str, str]], risks: list[dict[str, str]]) -> None:
    wb = Workbook()
    wb.remove(wb.active)

    intro = wb.create_sheet("填写说明")
    intro.append(["智慧食堂售前信息采集模板（标准版）"])
    intro["A1"].font = Font(size=18, bold=True, color="173B33")
    intro.append(["用途", "用于售前首访、现场勘查、报价前确认和实施交接。"])
    intro.append(["证据等级", "A=客户已确认/现场照片/系统截图/接口文档；B=历史项目/模板；C=行业/竞品；D=假设。"])
    intro.append(["填写规则", "必填项为空时不得进入报价或实施承诺；缺少证据时写入风险清单。"])
    intro.append(["来源边界", "本模板由结构化 CSV 生成，未复制企业微信原始 xlsx。"])
    setup_sheet(intro, {"A": 20, "B": 110})
    for row in intro.iter_rows():
        for cell in row:
            cell.alignment = Alignment(vertical="top", wrap_text=True)

    overview = wb.create_sheet("完成度看板")
    overview_headers = ["模块", "字段数", "必填项", "已填必填项", "必填完成率", "门禁"]
    overview.append(overview_headers)
    style_header(overview)
    grouped = defaultdict(list)
    for field in fields:
        grouped[field["source_sheet"]].append(field)
    gate_text = {
        "基础信息": "报价前必须闭环",
        "前厅采集": "方案前必须闭环",
        "后厨采集": "方案前必须闭环",
        "结算财务": "报价前必须闭环",
        "系统集成": "方案/实施前必须闭环",
        "设备网络": "报价和实施前必须闭环",
    }
    for sheet, rows in grouped.items():
        total = len(rows)
        required = sum(1 for row in rows if row["required"] == "是")
        overview.append([sheet, total, required, "", "", gate_text.get(sheet, "按项目确认")])
    for row_idx in range(2, overview.max_row + 1):
        overview[f"D{row_idx}"] = f'=COUNTIFS(INDIRECT("\'"&A{row_idx}&"\'!E:E"),"是",INDIRECT("\'"&A{row_idx}&"\'!D:D"),"<>")'
        overview[f"E{row_idx}"] = f'=IFERROR(D{row_idx}/C{row_idx},0)'
        overview[f"E{row_idx}"].number_format = "0%"
    setup_sheet(overview, {"A": 18, "B": 12, "C": 12, "D": 14, "E": 14, "F": 28})

    evidence_dv = DataValidation(type="list", formula1='"A-客户确认,B-模板/历史,C-参考,D-假设"', allow_blank=True)
    status_dv = DataValidation(type="list", formula1='"待确认,处理中,已确认,不适用"', allow_blank=True)
    required_dv = DataValidation(type="list", formula1='"是,否"', allow_blank=False)

    headers = ["序号", "分类", "信息项", "填写内容", "必填", "选项/示例", "采集方式", "负责人", "证据等级", "证据文件/链接", "备注"]
    for sheet, rows in grouped.items():
        ws = wb.create_sheet(sheet[:31])
        append_table(
            ws,
            headers,
            [
                [
                    row["source_no"],
                    row["field_group"],
                    row["field_name"],
                    "",
                    row["required"],
                    row["example_or_acceptance"],
                    row["collection_method"],
                    row["recommended_owner"],
                    "",
                    "",
                    "",
                ]
                for row in rows
            ],
        )
        ws.add_data_validation(evidence_dv)
        ws.add_data_validation(required_dv)
        evidence_dv.add(f"I2:I{ws.max_row}")
        required_dv.add(f"E2:E{ws.max_row}")
        setup_sheet(ws, {"A": 8, "B": 16, "C": 24, "D": 34, "E": 8, "F": 32, "G": 20, "H": 28, "I": 16, "J": 28, "K": 24})

    req_ws = wb.create_sheet("需求排期")
    append_table(
        req_ws,
        ["序号", "用户类型", "用户入口", "功能模块", "需求说明", "优先级", "状态", "预计排期", "验收标准", "备注"],
        [[r["source_no"], r["user_type"], r["user_entry"], r["function_module"], r["requirement_summary"], r["priority"], r["status"], r["expected_schedule"], r["acceptance_criteria"], ""] for r in reqs],
    )
    req_ws.add_data_validation(status_dv)
    status_dv.add(f"G2:G{req_ws.max_row}")
    setup_sheet(req_ws, {"A": 8, "B": 20, "C": 14, "D": 24, "E": 44, "F": 14, "G": 14, "H": 16, "I": 38, "J": 24})

    photo_ws = wb.create_sheet("现场照片清单")
    append_table(
        photo_ws,
        ["序号", "区域", "照片/截图内容", "是否已采集", "文件名/链接", "用途", "备注"],
        [[p["source_no"], p["area"], p["photo_content"], "", "", p["required_for"], ""] for p in photos],
    )
    setup_sheet(photo_ws, {"A": 8, "B": 16, "C": 30, "D": 14, "E": 34, "F": 36, "G": 24})

    risk_ws = wb.create_sheet("风险问题清单")
    append_table(
        risk_ws,
        ["序号", "风险", "所属模块", "影响", "责任方", "解决建议", "截止时间", "状态", "备注"],
        [[r["source_no"], r["risk"], r["source_module"], r["impact"], r["responsible_party"], r["recommendation"], "", r["status"], ""] for r in risks],
    )
    risk_ws.add_data_validation(status_dv)
    status_dv.add(f"H2:H{risk_ws.max_row}")
    setup_sheet(risk_ws, {"A": 8, "B": 38, "C": 16, "D": 10, "E": 24, "F": 42, "G": 14, "H": 14, "I": 24})

    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                cell.alignment = Alignment(vertical="top", wrap_text=True)

    OUT_DIR.mkdir(exist_ok=True)
    wb.save(OUT_XLSX)


def generate_html(fields: list[dict[str, str]], reqs: list[dict[str, str]], photos: list[dict[str, str]], risks: list[dict[str, str]]) -> None:
    grouped = defaultdict(list)
    for field in fields:
        grouped[field["source_sheet"]].append(field)
    sections = []
    for sheet, rows in grouped.items():
        controls = []
        for row in rows:
            required = "required" if row["required"] == "是" else ""
            label = html.escape(f'{row["field_group"]} / {row["field_name"]}')
            hint = html.escape(row["example_or_acceptance"] or row["collection_method"])
            controls.append(
                f'<label><span>{label} {"*" if required else ""}</span>'
                f'<textarea name="{html.escape(sheet)}-{html.escape(row["source_no"])}" {required} placeholder="{hint}"></textarea>'
                f'<small>{hint}</small></label>'
            )
        sections.append(f'<section><h2>{html.escape(sheet)}</h2><div class="fields">{"".join(controls)}</div></section>')

    req_rows = "".join(
        f"<tr><td>{html.escape(r['user_type'])}</td><td>{html.escape(r['user_entry'])}</td><td>{html.escape(r['function_module'])}</td><td>{html.escape(r['priority'])}</td><td>{html.escape(r['acceptance_criteria'])}</td></tr>"
        for r in reqs
    )
    photo_rows = "".join(
        f"<tr><td>{html.escape(p['area'])}</td><td>{html.escape(p['photo_content'])}</td><td>{html.escape(p['required_for'])}</td></tr>"
        for p in photos
    )
    risk_rows = "".join(
        f"<tr><td>{html.escape(r['risk'])}</td><td>{html.escape(r['impact'])}</td><td>{html.escape(r['responsible_party'])}</td><td>{html.escape(r['recommendation'])}</td></tr>"
        for r in risks
    )
    doc = f"""<!doctype html>
<html lang="zh-CN">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>智慧食堂售前信息采集表</title>
  <style>
    body {{ margin:0; font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif; color:#17212b; background:#f5f7fa; }}
    header {{ background:#0f766e; color:white; padding:32px 24px; }}
    main {{ max-width:1180px; margin:0 auto; padding:24px; }}
    h1 {{ margin:0 0 8px; font-size:30px; }}
    h2 {{ margin:28px 0 14px; font-size:21px; color:#0f4f49; }}
    .meta {{ opacity:.88; line-height:1.6; }}
    section {{ background:white; border:1px solid #d9e3ec; border-radius:8px; padding:20px; margin:18px 0; }}
    .fields {{ display:grid; grid-template-columns:repeat(2,minmax(0,1fr)); gap:14px; }}
    label {{ display:block; }}
    label span {{ display:block; font-weight:650; margin-bottom:6px; }}
    textarea {{ box-sizing:border-box; width:100%; min-height:72px; border:1px solid #c9d6e2; border-radius:6px; padding:10px; font:inherit; resize:vertical; background:#fbfdff; }}
    small {{ display:block; color:#64748b; line-height:1.45; margin-top:5px; }}
    table {{ width:100%; border-collapse:collapse; background:white; }}
    th,td {{ border-bottom:1px solid #e5edf5; padding:10px 12px; text-align:left; vertical-align:top; }}
    th {{ background:#e8f2f1; color:#114b45; }}
    .tools {{ display:flex; gap:10px; flex-wrap:wrap; margin-top:16px; }}
    button {{ border:0; background:#0f766e; color:white; border-radius:6px; padding:10px 14px; font-weight:650; cursor:pointer; }}
    @media (max-width:760px) {{ .fields {{ grid-template-columns:1fr; }} main {{ padding:14px; }} }}
    @media print {{ button {{ display:none; }} body {{ background:white; }} section {{ break-inside:avoid; }} }}
  </style>
</head>
<body>
  <header>
    <h1>智慧食堂售前信息采集表</h1>
    <div class="meta">标准版 · 2026-05-16 · 必填项为空时不得进入报价或实施承诺</div>
    <div class="tools"><button onclick="window.print()">打印 / 另存 PDF</button><button onclick="localStorage.clear(); location.reload()">清空本地填写</button></div>
  </header>
  <main>
    {"".join(sections)}
    <section><h2>需求排期种子</h2><table><thead><tr><th>用户类型</th><th>入口</th><th>功能模块</th><th>优先级</th><th>验收标准</th></tr></thead><tbody>{req_rows}</tbody></table></section>
    <section><h2>现场照片清单</h2><table><thead><tr><th>区域</th><th>照片/截图内容</th><th>用途</th></tr></thead><tbody>{photo_rows}</tbody></table></section>
    <section><h2>风险问题清单</h2><table><thead><tr><th>风险</th><th>影响</th><th>责任方</th><th>建议</th></tr></thead><tbody>{risk_rows}</tbody></table></section>
  </main>
  <script>
    for (const el of document.querySelectorAll('textarea')) {{
      el.value = localStorage.getItem('presales:' + el.name) || '';
      el.addEventListener('input', () => localStorage.setItem('presales:' + el.name, el.value));
    }}
  </script>
</body>
</html>
"""
    OUT_DIR.mkdir(exist_ok=True)
    OUT_HTML.write_text(doc, encoding="utf-8")


def generate_readme() -> None:
    OUT_README.write_text(
        """# 智慧食堂售前 Intake 模板

本目录由 `scripts/generate_presales_intake_templates.py` 生成。

| 文件 | 用途 |
| --- | --- |
| `智慧食堂售前信息采集模板_标准版_20260516.xlsx` | 线下现场采集、客户回填、实施交接用 Excel 模板。 |
| `smart-canteen-presales-intake-form.html` | 浏览器内快速填写、打印和另存 PDF 的 HTML 表单。 |

生成输入来自上一层目录的标准化 CSV，不复制企业微信原始 xlsx。
""",
        encoding="utf-8",
    )


def main() -> None:
    fields = read_csv(FIELD_CSV)
    reqs = read_csv(REQ_CSV)
    photos = read_csv(PHOTO_CSV)
    risks = read_csv(RISK_CSV)
    generate_workbook(fields, reqs, photos, risks)
    generate_html(fields, reqs, photos, risks)
    generate_readme()
    print(f"generated {OUT_XLSX}")
    print(f"generated {OUT_HTML}")


if __name__ == "__main__":
    main()