#!/usr/bin/env python3
"""
生成 Demo 数据 Excel 导入模板
===============================
从数据库读取 KpiMaster 指标定义，生成一个带格式的 Excel 模板。
用户可在 target_value / actual_value 列填入数值，未填的由 gen_demo_data.py 自动生成。

用法:
  python3 scripts/gen_demo_template.py
  # 输出: scripts/demo_data_template.xlsx
"""

import sys

DB_CONFIG = dict(
    host="123.60.180.165",
    port=3306,
    user="aidopremote",
    password="1234567890aiDOP#",
    database="aidopdev",
)
DEMO_TENANT_ID = 1300000000888
OUTPUT_PATH = "scripts/demo_data_template.xlsx"

MODULE_NAMES = {
    "S1": "S1 产销协同",
    "S2": "S2 运作协同",
    "S3": "S3 供应协同",
    "S4": "S4 采购执行",
    "S5": "S5 原材料仓储",
    "S6": "S6 制造运作",
    "S7": "S7 成品仓储",
    "S8": "S8 工厂监控",
    "S9": "S9 运营指标",
}

LEVEL_LABELS = {1: "L1", 2: "L2", 3: "L3"}


def connect_db():
    try:
        import pymysql
        return pymysql.connect(**DB_CONFIG)
    except ImportError:
        pass
    try:
        import mysql.connector
        return mysql.connector.connect(**DB_CONFIG)
    except ImportError:
        pass
    print("ERROR: 需要 pymysql 或 mysql-connector-python", file=sys.stderr)
    sys.exit(1)


def load_kpi_master(conn):
    cur = conn.cursor()
    cur.execute(
        "SELECT MetricCode, ModuleCode, MetricLevel, MetricName, Unit, Direction, ParentId, Id "
        "FROM ado_smart_ops_kpi_master WHERE TenantId = %s "
        "ORDER BY ModuleCode, MetricLevel, SortNo",
        (DEMO_TENANT_ID,),
    )
    rows = cur.fetchall()
    cur.close()
    return [
        dict(
            metric_code=r[0],
            module_code=r[1],
            metric_level=r[2],
            metric_name=r[3],
            unit=r[4] or "",
            direction=r[5] or "higher_is_better",
            parent_id=r[6],
            id=r[7],
        )
        for r in rows
    ]


def build_template(masters):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

    wb = Workbook()
    ws = wb.active
    ws.title = "Demo数据模板"

    header_font = Font(name="微软雅黑", bold=True, size=11, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="4472C4")
    l1_fill = PatternFill("solid", fgColor="D6E4F0")
    l2_fill = PatternFill("solid", fgColor="E8F0FE")
    l3_fill = PatternFill("solid", fgColor="F5F5F5")
    level_fills = {1: l1_fill, 2: l2_fill, 3: l3_fill}

    input_fill = PatternFill("solid", fgColor="FFFFCC")
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin"),
    )

    headers = [
        ("metric_code", "指标编码", 18),
        ("module_code", "模块", 8),
        ("module_name", "模块名称", 16),
        ("metric_level", "层级", 6),
        ("metric_name", "指标名称", 30),
        ("unit", "单位", 10),
        ("direction", "方向", 18),
        ("target_value", "目标值 ★", 14),
        ("actual_value", "实际值 ★", 14),
        ("yellow_threshold", "黄色阈值% ★", 14),
        ("red_threshold", "红色阈值% ★", 14),
    ]

    for col_idx, (_, title, width) in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=title)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = thin_border
        ws.column_dimensions[cell.column_letter].width = width

    id_map = {m["id"]: m for m in masters}

    row = 2
    prev_module = None
    for m in masters:
        if m["module_code"] != prev_module:
            if prev_module is not None:
                row += 1
            prev_module = m["module_code"]

        level = m["metric_level"]
        fill = level_fills.get(level, l3_fill)
        indent = "  " * (level - 1)

        default_yellow = 110 if m["direction"] == "lower_is_better" else 95
        default_red = 120 if m["direction"] == "lower_is_better" else 80

        values = [
            m["metric_code"],
            m["module_code"],
            MODULE_NAMES.get(m["module_code"], m["module_code"]),
            LEVEL_LABELS.get(level, f"L{level}"),
            indent + m["metric_name"],
            m["unit"],
            "越高越好" if m["direction"] == "higher_is_better" else "越低越好",
            None,
            None,
            default_yellow,
            default_red,
        ]

        for col_idx, val in enumerate(values, 1):
            cell = ws.cell(row=row, column=col_idx, value=val)
            cell.border = thin_border
            if col_idx <= 7:
                cell.fill = fill
            elif col_idx <= 9:
                cell.fill = input_fill
            else:
                cell.fill = input_fill
            if level == 1:
                cell.font = Font(name="微软雅黑", bold=True, size=10)
            else:
                cell.font = Font(name="微软雅黑", size=10)

        row += 1

    note_row = row + 1
    ws.cell(row=note_row, column=1, value="★ 填写说明：").font = Font(
        bold=True, color="C00000", size=10
    )
    ws.cell(
        row=note_row + 1,
        column=1,
        value="1. 在「目标值」和「实际值」列填入数值，留空则由系统自动生成。",
    )
    ws.cell(
        row=note_row + 2,
        column=1,
        value="2. 支持任意层级填入：可只填 L1，也可填 L2/L3，系统会自动保证父子层级数值自洽。",
    )
    ws.cell(
        row=note_row + 3,
        column=1,
        value="3. 若同时填了父/子指标，以实际填入值为准；系统仅对未填的指标自动生成补全。",
    )
    ws.cell(
        row=note_row + 4,
        column=1,
        value="4.「实际值」为 30 天日均基准，脚本会在此基础上加每日波动。",
    )
    ws.cell(
        row=note_row + 5,
        column=1,
        value="5.「黄色阈值%」「红色阈值%」控制期量差的颜色判定，已预填默认值（越高越好: 95/80，越低越好: 110/120）。",
    )

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:K{row - 1}"

    wb.save(OUTPUT_PATH)
    print(f"模板已生成: {OUTPUT_PATH}  ({row - 2} 条指标)")


def main():
    conn = connect_db()
    masters = load_kpi_master(conn)
    conn.close()
    if not masters:
        print(f"ERROR: 租户 {DEMO_TENANT_ID} 无 KpiMaster 数据", file=sys.stderr)
        sys.exit(1)
    build_template(masters)


if __name__ == "__main__":
    main()