#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
把业务线策略生成结果写成 .xlsx（业务线策略生成 skill 专用，纯标准库实现，无第三方依赖）。

职责边界：本脚本只负责「结构化数据 -> Excel 文件」的机械落盘，
不做任何数据拉取、策略文案生成或判断——那些都由调用方（Agent）在此之前完成。

用法：
    python3 build_report.py --input payload.json [--output /path/to/file.xlsx]
    cat payload.json | python3 build_report.py --input -

payload.json 结构：
{
  "output_path": "可选，未传时默认写到桌面 业务线策略生成_YYYYMMDD_HHMMSS.xlsx",
  "rows": [
    {
      "group": "组别",
      "bizLine": "业务线名称",
      "project": "项目名称",
      "pm": "项目PM",
      "budgetStrategy": "预算策略内容", "budgetLearning": "预算策略learning",
      "productStrategy": "广告产品策略内容", "productLearning": "广告产品策略learning",
      "audienceStrategy": "受众策略内容", "audienceLearning": "受众策略learning",
      "creativeStrategy": "素材策略内容", "creativeLearning": "素材策略learning"
    }
  ]
}

列固定为：组别、业务线、项目名称、项目PM、
预算策略内容、预算策略learning、广告产品策略内容、广告产品策略learning、
受众策略内容、受众策略learning、素材策略内容、素材策略learning。
"""
import argparse
import json
import os
import sys
import zipfile
from datetime import datetime
from xml.sax.saxutils import escape as xml_escape

COLUMNS = [
    ("group", "组别"),
    ("bizLine", "业务线"),
    ("project", "项目名称"),
    ("pm", "项目PM"),
    ("budgetStrategy", "预算策略内容"),
    ("budgetLearning", "预算策略learning"),
    ("productStrategy", "广告产品策略内容"),
    ("productLearning", "广告产品策略learning"),
    ("audienceStrategy", "受众策略内容"),
    ("audienceLearning", "受众策略learning"),
    ("creativeStrategy", "素材策略内容"),
    ("creativeLearning", "素材策略learning"),
]

# 内容列（非组别/业务线/项目名称/项目PM）默认加宽并开启自动换行
CONTENT_KEYS = {key for key, _label in COLUMNS[4:]}

CONTENT_TYPES_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
</Types>"""

ROOT_RELS_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>"""

WORKBOOK_RELS_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
</Relationships>"""

WORKBOOK_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets><sheet name="业务线策略生成" sheetId="1" r:id="rId1"/></sheets>
</workbook>"""

# cellXfs: 0=默认, 1=表头(粗体+浅底), 2=内容(自动换行+顶对齐)
STYLES_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font><sz val="10"/><name val="Microsoft YaHei"/></font>
<font><b/><sz val="10"/><color rgb="FFFFFFFF"/><name val="Microsoft YaHei"/></font>
</fonts>
<fills count="3">
<fill><patternFill patternType="none"/></fill>
<fill><patternFill patternType="none"/></fill>
<fill><patternFill patternType="solid"><fgColor rgb="FF4472C4"/><bgColor indexed="64"/></patternFill></fill>
</fills>
<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
<cellXfs count="3">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1">
<alignment horizontal="center" vertical="center"/>
</xf>
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
<alignment vertical="top" wrapText="1"/>
</xf>
</cellXfs>
</styleSheet>"""


def col_letter(index: int) -> str:
    """0-based 列号转 Excel 列字母（A, B, ..., Z, AA, ...）"""
    letters = ""
    n = index + 1
    while n > 0:
        n, rem = divmod(n - 1, 26)
        letters = chr(65 + rem) + letters
    return letters


def build_shared_strings(rows):
    """收集全部字符串去重，返回 (sst_xml, header_indices, row_indices)"""
    seen = {}
    order = []

    def intern(value):
        if value not in seen:
            seen[value] = len(order)
            order.append(value)
        return seen[value]

    header_indices = [intern(label) for _key, label in COLUMNS]
    row_indices = []
    for row in rows:
        row_indices.append(
            [intern(str(row.get(key, "") or "")) for key, _label in COLUMNS]
        )

    items = "".join(
        f'<si><t xml:space="preserve">{xml_escape(text)}</t></si>' for text in order
    )
    sst_xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        f'<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
        f'count="{len(order)}" uniqueCount="{len(order)}">{items}</sst>'
    )
    return sst_xml, header_indices, row_indices


def build_sheet_xml(header_indices, row_indices):
    """生成 sheet1.xml：首行表头样式 s="1"，其余行内容样式 s="2"（自动换行）"""
    col_count = len(COLUMNS)
    dimension_ref = f"A1:{col_letter(col_count - 1)}{len(row_indices) + 1}"

    cols_xml_parts = []
    for i, (key, _label) in enumerate(COLUMNS):
        width = 32 if key in CONTENT_KEYS else 14
        cols_xml_parts.append(
            f'<col min="{i + 1}" max="{i + 1}" width="{width}" customWidth="1"/>'
        )
    cols_xml = f"<cols>{''.join(cols_xml_parts)}</cols>"

    def row_xml(row_num, indices, style_id):
        cells = []
        for i, sst_index in enumerate(indices):
            ref = f"{col_letter(i)}{row_num}"
            cells.append(f'<c r="{ref}" s="{style_id}" t="s"><v>{sst_index}</v></c>')
        return f'<row r="{row_num}">{"".join(cells)}</row>'

    rows_xml_parts = [row_xml(1, header_indices, 1)]
    for offset, indices in enumerate(row_indices):
        rows_xml_parts.append(row_xml(offset + 2, indices, 2))

    sheet_data = f"<sheetData>{''.join(rows_xml_parts)}</sheetData>"

    return (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        f'<dimension ref="{dimension_ref}"/>'
        '<sheetViews><sheetView workbookViewId="0">'
        '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>'
        '</sheetView></sheetViews>'
        f'{cols_xml}{sheet_data}'
        '</worksheet>'
    )


def default_output_path() -> str:
    """未指定输出路径时，默认写到用户桌面"""
    home = os.path.expanduser("~")
    desktop = os.path.join(home, "Desktop")
    if not os.path.isdir(desktop):
        # 部分中文 Windows / OneDrive 环境桌面路径不同，找不到就退回主目录
        desktop = home
    stamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    return os.path.join(desktop, f"业务线策略生成_{stamp}.xlsx")


def write_xlsx(rows, output_path: str) -> str:
    sst_xml, header_indices, row_indices = build_shared_strings(rows)
    sheet_xml = build_sheet_xml(header_indices, row_indices)

    out_dir = os.path.dirname(output_path)
    if out_dir and not os.path.isdir(out_dir):
        os.makedirs(out_dir, exist_ok=True)

    with zipfile.ZipFile(output_path, "w", zipfile.ZIP_DEFLATED) as zf:
        zf.writestr("[Content_Types].xml", CONTENT_TYPES_XML)
        zf.writestr("_rels/.rels", ROOT_RELS_XML)
        zf.writestr("xl/workbook.xml", WORKBOOK_XML)
        zf.writestr("xl/_rels/workbook.xml.rels", WORKBOOK_RELS_XML)
        zf.writestr("xl/styles.xml", STYLES_XML)
        zf.writestr("xl/sharedStrings.xml", sst_xml)
        zf.writestr("xl/worksheets/sheet1.xml", sheet_xml)

    return output_path


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument(
        "--input", required=True, help="payload JSON 文件路径，传 - 表示从 stdin 读取"
    )
    parser.add_argument(
        "--output",
        default=None,
        help="输出 .xlsx 路径；未传时用 payload.output_path，仍未传则默认桌面",
    )
    args = parser.parse_args()

    if args.input == "-":
        raw = sys.stdin.read()
    else:
        with open(args.input, "r", encoding="utf-8") as f:
            raw = f.read()

    payload = json.loads(raw)
    rows = payload.get("rows", [])
    if not isinstance(rows, list) or len(rows) == 0:
        print(json.dumps({"ok": False, "error": "payload.rows 为空或不是数组"}, ensure_ascii=False))
        return 1

    output_path = args.output or payload.get("output_path") or default_output_path()
    output_path = os.path.abspath(output_path)

    write_xlsx(rows, output_path)

    print(
        json.dumps(
            {"ok": True, "output_path": output_path, "row_count": len(rows)},
            ensure_ascii=False,
        )
    )
    return 0


if __name__ == "__main__":
    sys.exit(main())