from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


ROOT = Path(__file__).resolve().parent
OUTPUT = ROOT / "output" / "智慧食堂产品经理岗位说明书与绩效考核_按模板精简版_20260903.xlsx"

ORANGE = PatternFill("solid", fgColor="E97132")
LIGHT_ORANGE = PatternFill("solid", fgColor="FBE2D5")
LIGHT_GRAY = PatternFill("solid", fgColor="F7F7F7")
WHITE = PatternFill("solid", fgColor="FFFFFF")
THIN = Side(style="thin", color="D9D9D9")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def setup(ws, widths):
    ws.freeze_panes = "A3"
    for row in ws.iter_rows():
        for cell in row:
            cell.font = Font(name="微软雅黑", size=12, color="222222")
            cell.alignment = Alignment(vertical="center", wrap_text=True)
            cell.border = BORDER
            if cell.row == 1:
                cell.fill = ORANGE
                cell.font = Font(name="微软雅黑", size=14, bold=True, color="FFFFFF")
                cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            elif cell.row == 2:
                cell.fill = LIGHT_ORANGE
                cell.font = Font(name="微软雅黑", size=12, bold=True, color="222222")
                cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            else:
                cell.fill = WHITE if cell.row % 2 else LIGHT_GRAY
    for idx, width in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(idx)].width = width
    for idx in range(1, ws.max_row + 1):
        ws.row_dimensions[idx].height = 36
    ws.row_dimensions[1].height = 42
    ws.auto_filter.ref = ws.dimensions


def title(ws, text, headers):
    ws.append([text] + [""] * (len(headers) - 1))
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(headers))
    ws.append(headers)


def build():
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    wb = Workbook()
    wb.remove(wb.active)

    ws = wb.create_sheet("岗位说明书要求")
    headers = ["模板字段", "填写要求", "智慧食堂产品经理口径"]
    title(ws, "岗位说明书要求（按2024岗位说明书模板）", headers)
    rows = [
        ["岗位名称", "写清岗位，不写泛称。", "智慧食堂产品经理"],
        ["岗位定位", "一句话说明岗位价值。", "负责售前偏差评估、产品边界判断和产品标准化回流。"],
        ["岗位职责描述", "按主次写，不堆空话。", "客户访谈、招投标需求拆解、偏差判断、定制周期评估、跨部门评审、标准化沉淀。"],
        ["任职资格", "写能胜任岗位的硬要求。", "懂智慧食堂业务、会拆需求、懂招投标、能判断标准/配置/定制/无法响应。"],
        ["汇报对象", "写直接上级岗位。", "产品技术部负责人或数字技术中心负责人。"],
        ["管理范围", "写实际管理对象。", "需求池、偏差分析表、产品标准化清单、评审闭环；如有团队再写产品专员/UI等。"],
        ["权限与任务", "权限必须和责任匹配。", "有需求判断权、偏差评估建议权、产品标准化建议权；无单方对外承诺价格、交期、接口和功能的权限。"],
        ["通用技能", "小白能看懂，不写虚词。", "会沟通、会记录、会推动评审、会用数据和证据说话。"],
        ["专业技能", "围绕真实工作写。", "客户访谈、招标文件拆解、需求分类、周期评估、产品文档、项目复盘。"],
        ["关键考核指标", "和月度绩效表一致。", "客户访谈质量、需求拆解、偏差结论、定制周期评估、标准化回流、跨部门评审闭环。"],
        ["工作标准", "写可检查结果。", "每个重点售前项目必须有访谈纪要、逐条偏差表、偏差比例、周期评估和评审结论。"],
    ]
    for row in rows:
        ws.append(row)
    setup(ws, [24, 40, 70])

    ws = wb.create_sheet("绩效考核要求")
    headers = ["指标类型", "考核指标", "权重", "指标说明", "衡量标准", "月度目标"]
    title(ws, "月度绩效考核要求（按绩效考核表模板）", headers)
    rows = [
        ["关键结果指标", "招投标需求偏差分析", 30, "判断项目标准能力覆盖多少、配置覆盖多少、定制多少、不能响应多少。", "重点项目100%形成逐条偏差表；结论包含四类比例。", "每月至少完成重点售前/招投标项目偏差分析。"],
        ["关键结果指标", "定制开发周期评估", 25, "对定制项拆分研发、测试、联调周期。", "周期经研发、测试、交付确认；未经确认不得对外使用。", "所有定制项都有周期、依据和风险说明。"],
        ["关键结果指标", "产品标准化回流", 20, "把高频定制转成标准功能、配置项、接口模板、验收清单或拒绝项。", "每项有来源项目、问题、处理方式和下一步。", "每月至少沉淀3项标准化改进。"],
        ["关键结果指标", "客户访谈与需求证据", 15, "通过访谈弄清场景、角色、流程、系统、接口、设备、验收和时间要求。", "访谈纪要字段完整，关键缺口有待确认项。", "每月至少完成4次有效访谈或售前澄清。"],
        ["过程性指标", "跨部门评审闭环", 10, "组织产品、研发、测试、交付、商务确认偏差和周期。", "评审有结论、负责人、下一步和风险处理。", "重点偏差项目100%完成评审或说明阻塞原因。"],
    ]
    for row in rows:
        ws.append(row)
    setup(ws, [18, 26, 10, 44, 52, 42])

    ws = wb.create_sheet("一句话说明")
    headers = ["对象", "要求"]
    title(ws, "给小白看的核心要求", headers)
    rows = [
        ["岗位说明书", "这名产品经理不是负责讲产品的人，而是负责判断客户要求能不能做、偏差有多少、要改多久的人。"],
        ["绩效考核", "考核重点不是写了多少材料，而是每个售前项目有没有讲清：标准覆盖多少、定制多少、不能承诺什么、开发周期多久。"],
        ["售前边界", "产品经理可以给判断和依据，不能单方承诺价格、工期、接口、资质、功能和效果。"],
    ]
    for row in rows:
        ws.append(row)
    setup(ws, [24, 90])

    wb.save(OUTPUT)
    check = load_workbook(OUTPUT, data_only=True)
    expected = ["岗位说明书要求", "绩效考核要求", "一句话说明"]
    if check.sheetnames != expected:
        raise SystemExit(check.sheetnames)
    print(OUTPUT)
    for sheet in check.worksheets:
        print(sheet.title, sheet.max_row, sheet.max_column)


if __name__ == "__main__":
    build()