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

wb = Workbook()

blue = Font(name="Microsoft YaHei", size=11, color="0000FF")
black = Font(name="Microsoft YaHei", size=11, color="000000")
bold = Font(name="Microsoft YaHei", size=11, color="000000", bold=True)
bold_blue = Font(name="Microsoft YaHei", size=11, color="0000FF", bold=True)
hdr = Font(name="Microsoft YaHei", size=12, color="FFFFFF", bold=True)
lblue = PatternFill("solid", fgColor="DCE6F1")
lgreen = PatternFill("solid", fgColor="E2EFDA")
lgray = PatternFill("solid", fgColor="F2F2F2")
highlight = PatternFill("solid", fgColor="FFF2CC")
center = Alignment(horizontal="center", vertical="center", wrap_text=True)
left = Alignment(horizontal="left", vertical="center", wrap_text=True)
bdr = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)
cur = '¥#,##0;("¥"#,##0);-'
pct = "0.0%"
hr = '¥#,##0"元/h"'
num = "#,##0"


def sc(ws, r, c, v, font=black, fill=None, fmt=None, align=center, merge_to=None):
    cell = ws.cell(row=r, column=c, value=v)
    cell.font = font
    if fill:
        cell.fill = fill
    if fmt:
        cell.number_format = fmt
    cell.alignment = align
    cell.border = bdr
    if merge_to:
        ws.merge_cells(start_row=r, start_column=c, end_row=r, end_column=merge_to)


def hdr_row(ws, r, vals, bg="2F5496"):
    f = PatternFill("solid", fgColor=bg)
    for i, v in enumerate(vals, 1):
        sc(ws, r, i, v, font=hdr, fill=f)


# ════════════ Sheet 1 ════════════
ws1 = wb.active
ws1.title = "第一期"
ws1.sheet_properties.tabColor = "4472C4"
for c in range(1, 4):
    ws1.column_dimensions[chr(64 + c)].width = 24

sc(
    ws1,
    1,
    1,
    "青年钢琴集体课 · 教师薪酬测算 — 第一期（公益入门）",
    font=bold,
    align=left,
    merge_to=3,
)
sc(ws1, 2, 1, "A 类教师（研发教师）", font=bold_blue, align=left, merge_to=3)
hdr_row(ws1, 4, ["项目", "数值", "说明"])

inputs1 = [
    ("▎输入区（蓝色可改）", None, None),
    ("学员学费（元/期）", 600, "10+1 次课"),
    ("满班人数", 13, ""),
    ("平台抽成比例（含渠道）", 0.126, "12%+0.6%"),
    ("教师基础分成比例", 0.50, "A 类锁定 2 年"),
    ("研发奖金（元）", 1000, "仅第 1 期"),
    ("续课奖金（元）", 0, "仅统计基线"),
    ("总课时数", 11, "含 1 节免费体验"),
    ("单课时长（分钟）", 90, ""),
    ("预估覆盖成本（元/期）", 3000, "房租/折旧/管理等"),
]
for i, (label, val, note) in enumerate(inputs1):
    r = 5 + i
    if val is None:
        sc(ws1, r, 1, label, font=bold, fill=lblue, align=left, merge_to=3)
    else:
        sc(ws1, r, 1, label, font=blue, fill=lblue, align=left)
        sc(ws1, r, 2, val, font=blue, fill=lblue)
        sc(ws1, r, 3, note, font=blue, fill=lblue, align=left)
        if any(k in label for k in ["学费", "奖金", "成本"]):
            ws1.cell(row=r, column=2).number_format = cur
        elif "比例" in label:
            ws1.cell(row=r, column=2).number_format = pct

calcs1 = [
    ("▎计算区（自动）", None, None),
    ("总学费收入", "=B6*B7", "学费 × 人数"),
    ("平台抽成金额", "=B17*B8", "总学费 × 抽成"),
    ("机构实收", "=B17-B18", "总学费 - 抽成"),
    ("", "", ""),
    ("教师基础分成", "=B19*B9", "实收 × 分成比例"),
    ("研发奖金", "=B10", ""),
    ("续课奖金", "=B11", ""),
    ("教师一期总收入", "=B21+B22+B23", ""),
    ("", "", ""),
    ("机构剩余", "=B19-B24", "实收 - 教师总收入"),
    ("机构净利润", "=B26-B14", "剩余 - 覆盖成本"),
    ("", "", ""),
    ("教师单次出勤收益", "=B24/B12", "总收入 ÷ 课时数"),
    ("折合时薪", "=B29/(B13/60)", "单次收益 ÷ 小时"),
]
for i, (label, val, note) in enumerate(calcs1):
    r = 16 + i
    if val is None:
        sc(ws1, r, 1, label, font=bold, fill=lgreen, align=left, merge_to=3)
    else:
        sc(ws1, r, 1, label, font=black, fill=lgreen, align=left)
        sc(ws1, r, 2, val, font=black, fill=lgreen)
        sc(ws1, r, 3, note, font=black, fill=lgreen, align=left)
        if any(
            k in label
            for k in [
                "收入",
                "分成",
                "奖金",
                "剩余",
                "利润",
                "出勤",
                "学费",
                "抽成",
                "实收",
            ]
        ):
            ws1.cell(row=r, column=2).number_format = cur
        elif "时薪" in label:
            ws1.cell(row=r, column=2).number_format = hr

for rr in [24, 27, 29, 30]:
    ws1.cell(row=rr, column=2).font = bold
    ws1.cell(row=rr, column=2).fill = highlight

# ════════════ Sheet 2 ════════════
ws2 = wb.create_sheet("第二期")
ws2.sheet_properties.tabColor = "548235"
for c in range(1, 7):
    ws2.column_dimensions[chr(64 + c)].width = 22

sc(
    ws2,
    1,
    1,
    "青年钢琴集体课 · 教师薪酬测算 — 第二期起（双班并行）",
    font=bold,
    align=left,
    merge_to=6,
)

# ── 公共参数 ──
hdr_row(ws2, 3, ["项目", "数值", "说明", "项目", "数值", "说明"])
common = [
    ("▎公共参数", "", "", "▎进阶班参数（自营）", "", ""),
    ("入门班课时数", 10, "", "进阶班课时数", 10, ""),
    ("入门班单课时长 (min)", 60, "", "进阶班单课时长 (min)", 60, ""),
    ("预估覆盖成本 (元/期·双班)", 6000, "", "", "", ""),
]
for i, row_data in enumerate(common):
    r = 4 + i
    for j, v in enumerate(row_data):
        is_hdr = j in (0, 3)
        sc(
            ws2,
            r,
            j + 1,
            v,
            font=bold if is_hdr else blue,
            fill=lblue,
            align=left if is_hdr else center,
        )
    if i == 3:
        ws2.cell(row=r, column=2).number_format = cur

# ── A 类 ──
sc(ws2, 9, 1, "A 类教师（研发教师）", font=bold_blue, align=left, merge_to=6)
hdr_row(ws2, 10, ["项目", "入门班", "进阶班", "双班合计", "", ""])

# 输入区：按次定价 → 按期自动计算
a_in = [
    ("学员学费/单价 (元/次)", 60, 118, cur),
    ("学员学费/单价 (元/期)", "=B11*B5", "=C11*E5", cur),
    ("满班人数", 18, 18, num),
    ("平台抽成比例", 0.126, 0.0, pct),
    ("基础分成比例", 0.40, 0.40, pct),
    ("浮动分成比例 (=续课奖金)", 0.10, 0.10, pct),
    ("研发奖金 (元)", 500, 0, cur),
]
for i, (lb, b1, b2, fmt) in enumerate(a_in):
    r = 11 + i
    sc(ws2, r, 1, lb, font=blue, fill=lblue, align=left)
    sc(ws2, r, 2, b1, font=black if str(b1).startswith("=") else blue, fill=lblue)
    sc(ws2, r, 3, b2, font=black if str(b2).startswith("=") else blue, fill=lblue)
    ws2.cell(row=r, column=2).number_format = fmt
    ws2.cell(row=r, column=3).number_format = fmt

# A 计算区
sc(ws2, 19, 1, "▎计算区", font=bold, fill=lgreen, align=left, merge_to=6)

a_rows = [
    ("总学费收入", "=B12*B13", "=C12*C13", cur),
    ("平台抽成金额", "=B20*B14", "=C20*C14", cur),
    ("机构实收", "=B20-B21", "=C20-C21", cur),
    ("", "", "", None),
    ("教师基础分成", "=B22*B15", "=C22*C15", cur),
    ("教师浮动分成/续课奖金 (满额)", "=B22*B16", "=C22*C16", cur),
    ("教师单班总收入", "=B24+B25+B17", "=C24+C25+C17", cur),
    ("", "", "", None),
    ("▎双班合计（A 类）", "", "", None),
    ("教师双班总收入", "=B26+C26", "", cur),
    ("单次出勤收益", "=B29/B5", "", cur),
    ("折合时薪", "=B30/((B6+E6)/60)", "", hr),
    ("", "", "", None),
    ("机构双班总实收", "=B22+C22", "", cur),
    ("机构净利润 (A 类带双班)", "=B33-B29-B7", "", cur),
]
for i, (lb, b1, b2, fmt) in enumerate(a_rows):
    r = 20 + i
    is_sec = "▎" in lb
    sc(ws2, r, 1, lb, font=bold if is_sec else black, fill=lgreen, align=left)
    sc(ws2, r, 2, b1, font=black if str(b1).startswith("=") else blue, fill=lgreen)
    sc(ws2, r, 3, b2, font=black if str(b2).startswith("=") else blue, fill=lgreen)
    if fmt:
        ws2.cell(row=r, column=2).number_format = fmt
        if b2:
            ws2.cell(row=r, column=3).number_format = fmt

for rr in [26, 29, 30, 31, 34]:
    ws2.cell(row=rr, column=2).font = bold
    ws2.cell(row=rr, column=2).fill = highlight

# ── B 类 ──
sc(ws2, 36, 1, "B 类教师（普通接手）", font=bold_blue, align=left, merge_to=6)
hdr_row(ws2, 37, ["项目", "入门班", "进阶班", "双班合计", "", ""])

b_in = [
    ("学员学费/单价 (元/次)", 60, 118, cur),
    ("学员学费/单价 (元/期)", "=B38*B5", "=C38*E5", cur),
    ("满班人数", 18, 18, num),
    ("平台抽成比例", 0.126, 0.0, pct),
    ("基础分成比例", 0.30, 0.30, pct),
    ("浮动分成比例 (=续课奖金)", 0.10, 0.10, pct),
    ("研发奖金 (元)", 0, 0, cur),
]
for i, (lb, b1, b2, fmt) in enumerate(b_in):
    r = 38 + i
    sc(ws2, r, 1, lb, font=blue, fill=lblue, align=left)
    sc(ws2, r, 2, b1, font=black if str(b1).startswith("=") else blue, fill=lblue)
    sc(ws2, r, 3, b2, font=black if str(b2).startswith("=") else blue, fill=lblue)
    ws2.cell(row=r, column=2).number_format = fmt
    ws2.cell(row=r, column=3).number_format = fmt

sc(ws2, 46, 1, "▎计算区", font=bold, fill=lgreen, align=left, merge_to=6)

b_rows = [
    ("总学费收入", "=B39*B40", "=C39*C40", cur),
    ("平台抽成金额", "=B47*B41", "=C47*C41", cur),
    ("机构实收", "=B47-B48", "=C47-C48", cur),
    ("", "", "", None),
    ("教师基础分成", "=B49*B42", "=C49*C42", cur),
    ("教师浮动分成/续课奖金 (满额)", "=B49*B43", "=C49*C43", cur),
    ("教师单班总收入", "=B51+B52+B44", "=C51+C52+C44", cur),
    ("", "", "", None),
    ("▎双班合计（B 类）", "", "", None),
    ("教师双班总收入", "=B53+C53", "", cur),
    ("单次出勤收益", "=B56/B5", "", cur),
    ("折合时薪", "=B57/((B6+E6)/60)", "", hr),
    ("", "", "", None),
    ("机构双班总实收", "=B49+C49", "", cur),
    ("机构净利润 (B 类带双班)", "=B60-B56-B7", "", cur),
]
for i, (lb, b1, b2, fmt) in enumerate(b_rows):
    r = 47 + i
    is_sec = "▎" in lb
    sc(ws2, r, 1, lb, font=bold if is_sec else black, fill=lgreen, align=left)
    sc(ws2, r, 2, b1, font=black if str(b1).startswith("=") else blue, fill=lgreen)
    sc(ws2, r, 3, b2, font=black if str(b2).startswith("=") else blue, fill=lgreen)
    if fmt:
        ws2.cell(row=r, column=2).number_format = fmt
        if b2:
            ws2.cell(row=r, column=3).number_format = fmt

for rr in [53, 56, 57, 58, 61]:
    ws2.cell(row=rr, column=2).font = bold
    ws2.cell(row=rr, column=2).fill = highlight

# ── 续课阶梯 ──
# 续课奖金 = 浮动分成满额 × 阶梯系数
sc(
    ws2,
    63,
    1,
    "▎续课奖金阶梯表（= 浮动分成满额 × 系数）",
    font=bold,
    fill=lgray,
    align=left,
    merge_to=6,
)
hdr_row(ws2, 64, ["续课率", "系数", "A 类奖金 (公式)", "B 类奖金 (公式)", "", ""])

# A 类浮动满额 = B25+C25, B 类 = B52+C52
bonus_rows = [
    ("≥ 80%", 1.0, "=ROUND((B25+C25)*B65,0)", "=ROUND((B52+C52)*B65,0)"),
    ("60% ~ 80%", 0.6, "=ROUND((B25+C25)*B66,0)", "=ROUND((B52+C52)*B66,0)"),
    ("40% ~ 60%", 0.3, "=ROUND((B25+C25)*B67,0)", "=ROUND((B52+C52)*B67,0)"),
    ("< 40%", 0.0, "=ROUND((B25+C25)*B68,0)", "=ROUND((B52+C52)*B68,0)"),
]
for i, (rate, coef, a_f, b_f) in enumerate(bonus_rows):
    r = 65 + i
    sc(ws2, r, 1, rate, font=black, fill=lgreen)
    sc(ws2, r, 2, coef, font=black, fill=lgreen, fmt=pct)
    sc(ws2, r, 3, a_f, font=black, fill=lgreen, fmt=cur)
    sc(ws2, r, 4, b_f, font=black, fill=lgreen, fmt=cur)

# ── 敏感性分析 ──
sc(
    ws2,
    70,
    1,
    "▎敏感性分析：续课率对 A 类教师收入的影响",
    font=bold,
    fill=lgray,
    align=left,
    merge_to=6,
)
hdr_row(
    ws2,
    71,
    ["续课率", "续课奖金", "教师一期总收入", "单次出勤", "折合时薪", "机构净利润"],
)

sens = [
    (
        "80%",
        "=C65",
        "=B29-(B25+C25)+C65",
        "=C72/B5",
        "=D72/((B6+E6)/60)",
        "=B33-C72-B7",
    ),
    (
        "60%",
        "=C66",
        "=B29-(B25+C25)+C66",
        "=C73/B5",
        "=D73/((B6+E6)/60)",
        "=B33-C73-B7",
    ),
    (
        "40%",
        "=C67",
        "=B29-(B25+C25)+C67",
        "=C74/B5",
        "=D74/((B6+E6)/60)",
        "=B33-C74-B7",
    ),
    ("0%", "=C68", "=B29-(B25+C25)+C68", "=C75/B5", "=D75/((B6+E6)/60)", "=B33-C75-B7"),
]
for i, row_data in enumerate(sens):
    r = 72 + i
    for j, v in enumerate(row_data):
        is_f = str(v).startswith("=")
        sc(ws2, r, j + 1, v, font=black if is_f else blue, fill=lgreen)
        fmt_map = {1: cur, 2: cur, 3: cur, 4: hr, 5: cur}
        if j in fmt_map:
            ws2.cell(row=r, column=j + 1).number_format = fmt_map[j]

out = "D:/F/NewI/opencode/daily-workspace/projects/青年钢琴集体课/04-薪酬与协议/教师薪酬测算模型.xlsx"
wb.save(out)
print("Saved:", out)