#!/usr/bin/env python3

from __future__ import annotations

import argparse
import json
from datetime import datetime
from pathlib import Path

from openpyxl import Workbook
from openpyxl.chart import BarChart, DoughnutChart, Reference
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo


FONT = "微软雅黑"
NAVY = "203864"
BLUE = "4472C4"
LIGHT_BLUE = "D9EAF7"
LIGHT_GRAY = "F3F5F7"
GRID = "D9E1E8"
WHITE = "FFFFFF"
RED = "F4CCCC"
ORANGE = "FCE5CD"
GREEN = "D9EAD3"
GRAY = "E7E6E6"
YELLOW = "FFF2CC"
THIN = Side(style="thin", color=GRID)

HEADERS = [
    "快照时间", "资源类型", "资源名称", "资源实例ID", "地域", "可用区", "运行状态", "业务用途/环境",
    "规格", "软件/引擎版本", "存储/容量", "公网地址/访问端点", "私网地址", "付费方式", "到期时间", "剩余天数",
    "近7日CPU均值%", "近7日CPU峰值%", "近7日内存均值%", "近7日内存峰值%", "近7日磁盘峰值%",
    "备份/快照状态", "网络与权限状态", "风险等级", "风险说明", "运维建议", "数据来源",
]

WIDTHS = {
    "快照时间": 19, "资源类型": 15, "资源名称": 36, "资源实例ID": 32, "地域": 16, "可用区": 17,
    "运行状态": 12, "业务用途/环境": 38, "规格": 32, "软件/引擎版本": 34, "存储/容量": 32,
    "公网地址/访问端点": 40, "私网地址": 40, "付费方式": 12, "到期时间": 19, "剩余天数": 12,
    "近7日CPU均值%": 16, "近7日CPU峰值%": 16, "近7日内存均值%": 17, "近7日内存峰值%": 17,
    "近7日磁盘峰值%": 17, "备份/快照状态": 46, "网络与权限状态": 55, "风险等级": 12,
    "风险说明": 55, "运维建议": 55, "数据来源": 38,
}

FIELD_NOTES = {
    "快照时间": "阿里云只读盘点数据生成时间（北京时间）。",
    "资源类型": "ECS、RDS、Redis、MQTT、CLB、OSS等资源分类。",
    "资源名称": "阿里云控制台可读名称；名称为空时使用资源标识。",
    "资源实例ID": "阿里云资源实例标识，用于控制台精确定位。",
    "地域": "资源所属阿里云地域。",
    "可用区": "资源所属可用区；全局或托管服务可能为空。",
    "运行状态": "API返回状态的中文归一：正常、服务中、停止、异常、待确认。",
    "业务用途/环境": "来自实例名称、标签或资源描述；标记待确认的内容需负责人补充。",
    "规格": "实例型号、CPU/内存或托管服务规格。",
    "软件/引擎版本": "操作系统、数据库、Redis、MQTT或函数运行时版本。",
    "存储/容量": "云盘、数据库、缓存或文件系统容量。",
    "公网地址/访问端点": "公网IP、托管服务域名或公开访问端点；不含密码。",
    "私网地址": "VPC私网IP或托管数据库内网端点；不含认证信息。",
    "付费方式": "包年包月、按量付费或待确认。",
    "到期时间": "API返回时间转换为北京时间。",
    "剩余天数": "快照日期到到期日期的自然日差。",
    "近7日CPU均值%": "CloudMonitor近7天每小时采样的平均值。",
    "近7日CPU峰值%": "CloudMonitor近7天采样峰值。",
    "近7日内存均值%": "CloudMonitor Agent近7天内存使用平均值；无Agent时为空。",
    "近7日内存峰值%": "CloudMonitor Agent近7天内存使用峰值。",
    "近7日磁盘峰值%": "CloudMonitor Agent返回的各挂载点近7日最高使用率。",
    "备份/快照状态": "ECS自动快照覆盖、RDS/Redis备份策略或待专项复核状态。",
    "网络与权限状态": "公网端口、VPC、ACL、版本控制等只读检查结果。",
    "风险等级": "高=需尽快处理；中=纳入近期计划；低=当前未发现主要问题；待确认=证据不足。",
    "风险说明": "根据到期、老旧系统、磁盘、公开端口、备份和ACL等证据生成。",
    "运维建议": "针对当前证据的下一步检查或整改建议，不代表已执行。",
    "数据来源": "本行使用的阿里云只读API或CloudMonitor来源。",
}


def read_records(path: Path) -> list[dict]:
    payload = json.loads(path.read_text(encoding="utf-8"))
    return [record["values"] for record in payload["records"]]


def normalize(value):
    if value is None:
        return None
    if isinstance(value, str) and value.endswith(":00"):
        try:
            return datetime.strptime(value, "%Y-%m-%d %H:%M:%S")
        except ValueError:
            return value
    return value


def style_sheet(ws):
    ws.sheet_view.showGridLines = False
    for row in ws.iter_rows():
        for cell in row:
            cell.font = Font(name=FONT, size=11, color="1F1F1F")
            cell.alignment = Alignment(vertical="center", wrap_text=True)


def add_data_sheet(wb, title, records, columns=None):
    columns = columns or HEADERS
    ws = wb.create_sheet(title)
    ws.append(columns)
    for record in records:
        ws.append([normalize(record.get(header)) for header in columns])
    style_sheet(ws)
    ws.sheet_view.zoomScale = 75
    for cell in ws[1]:
        cell.font = Font(name=FONT, size=11, bold=True, color=WHITE)
        cell.fill = PatternFill("solid", fgColor=NAVY)
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = Border(bottom=Side(style="medium", color=BLUE))
    ws.row_dimensions[1].height = 32
    ws.freeze_panes = "C2"
    ws.auto_filter.ref = ws.dimensions
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_setup.orientation = "landscape"
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.print_title_rows = "1:1"
    for idx, header in enumerate(columns, 1):
        ws.column_dimensions[get_column_letter(idx)].width = WIDTHS.get(header, 20)
    for row in range(2, ws.max_row + 1):
        ws.row_dimensions[row].height = 38
        risk = ws.cell(row, columns.index("风险等级") + 1).value if "风险等级" in columns else None
        color = {"高": RED, "中": ORANGE, "低": GREEN, "待确认": GRAY}.get(risk)
        if color:
            ws.cell(row, columns.index("风险等级") + 1).fill = PatternFill("solid", fgColor=color)
            ws.cell(row, columns.index("风险等级") + 1).font = Font(name=FONT, size=11, bold=True)
        if "运行状态" in columns:
            status_cell = ws.cell(row, columns.index("运行状态") + 1)
            status_cell.fill = PatternFill("solid", fgColor={"正常": GREEN, "服务中": GREEN, "停止": GRAY, "异常": RED, "待确认": YELLOW}.get(status_cell.value, WHITE))
        if "资源实例ID" in columns:
            ws.cell(row, columns.index("资源实例ID") + 1).number_format = "@"
        if "公网地址/访问端点" in columns:
            ws.cell(row, columns.index("公网地址/访问端点") + 1).number_format = "@"
        if "私网地址" in columns:
            ws.cell(row, columns.index("私网地址") + 1).number_format = "@"
    for header in ["近7日CPU均值%", "近7日CPU峰值%", "近7日内存均值%", "近7日内存峰值%", "近7日磁盘峰值%"]:
        if header in columns:
            col = get_column_letter(columns.index(header) + 1)
            ws.column_dimensions[col].width = WIDTHS[header]
            ws.conditional_formatting.add(f"{col}2:{col}{ws.max_row}", CellIsRule(operator="greaterThanOrEqual", formula=["90"], fill=PatternFill("solid", fgColor=RED)))
            ws.conditional_formatting.add(f"{col}2:{col}{ws.max_row}", CellIsRule(operator="between", formula=["75", "89.999"], fill=PatternFill("solid", fgColor=ORANGE)))
            for row in range(2, ws.max_row + 1):
                ws.cell(row, columns.index(header) + 1).number_format = "0.00"
    if "剩余天数" in columns:
        col = get_column_letter(columns.index("剩余天数") + 1)
        ws.conditional_formatting.add(f"{col}2:{col}{ws.max_row}", CellIsRule(operator="lessThanOrEqual", formula=["30"], fill=PatternFill("solid", fgColor=RED)))
        ws.conditional_formatting.add(f"{col}2:{col}{ws.max_row}", CellIsRule(operator="between", formula=["31", "60"], fill=PatternFill("solid", fgColor=ORANGE)))
    if ws.max_row > 1:
        ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
        table = Table(displayName=f"Table_{len(wb.worksheets)}", ref=ref)
        table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
        ws.add_table(table)
    return ws


def add_summary(wb, records, snapshot_time):
    ws = wb.active
    ws.title = "运维总览"
    ws.sheet_view.showGridLines = False
    ws.sheet_view.zoomScale = 75
    ws.merge_cells("A1:L2")
    ws["A1"] = "阿里云资源运维快照"
    ws["A1"].font = Font(name=FONT, size=24, bold=True, color=WHITE)
    ws["A1"].fill = PatternFill("solid", fgColor=NAVY)
    ws["A1"].alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[1].height = 34
    ws.row_dimensions[2].height = 16
    ws["A3"] = "快照时间"
    ws["B3"] = snapshot_time
    ws["A4"] = "数据边界"
    ws.merge_cells("B4:L4")
    ws["B4"] = "阿里云只读API与CloudMonitor快照；未读取业务数据库内容、OSS对象、账号密码、AccessKey、Token或证书私钥。"
    for cell in (ws["A3"], ws["A4"]):
        cell.font = Font(name=FONT, size=11, bold=True, color=NAVY)
    ws["B3"].number_format = "yyyy-mm-dd hh:mm"
    ws["B4"].alignment = Alignment(wrap_text=True)
    cards = [
        ("A6", "资源总数", "=COUNTA('全部资源'!A:A)-1", BLUE),
        ("C6", "ECS服务器", '=COUNTIF(\'全部资源\'!B:B,"ECS")', "5B9BD5"),
        ("E6", "高风险", '=COUNTIF(\'全部资源\'!X:X,"高")', "C00000"),
        ("G6", "中风险", '=COUNTIF(\'全部资源\'!X:X,"中")', "ED7D31"),
        ("I6", "30天内到期", '=COUNTIFS(\'全部资源\'!P:P,">=0",\'全部资源\'!P:P,"<=30")', "BF9000"),
        ("K6", "待确认", '=COUNTIF(\'全部资源\'!X:X,"待确认")', "7F7F7F"),
    ]
    for cell_ref, label, formula, color in cards:
        cell = ws[cell_ref]
        cell.value = label
        cell.font = Font(name=FONT, size=11, bold=True, color=WHITE)
        cell.fill = PatternFill("solid", fgColor=color)
        cell.alignment = Alignment(horizontal="center")
        value_cell = ws.cell(cell.row + 1, cell.column)
        value_cell.value = formula
        value_cell.font = Font(name=FONT, size=22, bold=True, color=color)
        value_cell.alignment = Alignment(horizontal="center")
        ws.merge_cells(start_row=cell.row, start_column=cell.column, end_row=cell.row, end_column=cell.column + 1)
        ws.merge_cells(start_row=value_cell.row, start_column=value_cell.column, end_row=value_cell.row, end_column=value_cell.column + 1)
    types = sorted({record.get("资源类型", "待确认") for record in records})
    ws["A10"] = "资源类型"
    ws["B10"] = "数量"
    for idx, resource_type in enumerate(types, 11):
        ws.cell(idx, 1, resource_type)
        ws.cell(idx, 2, f'=COUNTIF(\'全部资源\'!B:B,A{idx})')
    ws["D10"] = "风险等级"
    ws["E10"] = "数量"
    for idx, risk in enumerate(["高", "中", "低", "待确认"], 11):
        ws.cell(idx, 4, risk)
        ws.cell(idx, 5, f'=COUNTIF(\'全部资源\'!X:X,D{idx})')
        ws.cell(idx, 4).fill = PatternFill("solid", fgColor={"高": RED, "中": ORANGE, "低": GREEN, "待确认": GRAY}[risk])
    ws["G10"] = "重点运维发现"
    ws["H10"] = "数量"
    findings = [
        ("ECS老旧操作系统/临期/公开高危端口", "=COUNTIF('高风险与临期'!B:B,\"ECS\")"),
        ("RDS数据库版本升级评估", "=COUNTIF('高风险与临期'!B:B,\"RDS\")"),
        ("OSS公共读写", '=COUNTIFS(\'全部资源\'!B:B,"OSS",\'全部资源\'!Y:Y,"*公共读写*")'),
        ("函数环境变量敏感凭据字段", '=COUNTIFS(\'全部资源\'!B:B,"函数计算",\'全部资源\'!Y:Y,"*明文敏感凭据*")'),
        ("MQTT 60天内到期", '=COUNTIFS(\'全部资源\'!B:B,"MQTT",\'全部资源\'!P:P,"<=60")'),
    ]
    for idx, (finding, formula) in enumerate(findings, 11):
        ws.cell(idx, 7, finding)
        ws.cell(idx, 8, formula)
    for area in ("A10:B26", "D10:E14", "G10:H15"):
        for row in ws[area]:
            for cell in row:
                cell.border = Border(bottom=THIN)
                cell.alignment = Alignment(vertical="center", wrap_text=True)
    for header in ("A10", "B10", "D10", "E10", "G10", "H10"):
        ws[header].font = Font(name=FONT, size=11, bold=True, color=WHITE)
        ws[header].fill = PatternFill("solid", fgColor=NAVY)
        ws[header].alignment = Alignment(horizontal="center")
    bar = BarChart()
    bar.type = "bar"
    bar.style = 10
    bar.title = "资源类型分布"
    bar.y_axis.title = "资源类型"
    bar.x_axis.title = "数量"
    bar.height = 8
    bar.width = 13
    bar.add_data(Reference(ws, min_col=2, min_row=10, max_row=10 + len(types)), titles_from_data=True)
    bar.set_categories(Reference(ws, min_col=1, min_row=11, max_row=10 + len(types)))
    ws.add_chart(bar, "A29")
    doughnut = DoughnutChart()
    doughnut.title = "风险等级分布"
    doughnut.height = 8
    doughnut.width = 10
    doughnut.holeSize = 55
    doughnut.add_data(Reference(ws, min_col=5, min_row=10, max_row=14), titles_from_data=True)
    doughnut.set_categories(Reference(ws, min_col=4, min_row=11, max_row=14))
    ws.add_chart(doughnut, "H29")
    for col, width in {"A": 28, "B": 14, "C": 18, "D": 16, "E": 14, "F": 4, "G": 46, "H": 14, "I": 18, "J": 4, "K": 18, "L": 18}.items():
        ws.column_dimensions[col].width = width
    ws.sheet_view.zoomScale = 90
    ws.freeze_panes = "A5"
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_setup.orientation = "landscape"
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 1


def add_field_notes(wb, snapshot_time):
    ws = wb.create_sheet("字段说明")
    ws.append(["字段", "定义与口径"])
    for header in HEADERS:
        ws.append([header, FIELD_NOTES[header]])
    ws.append([])
    ws.append(["快照时间", snapshot_time])
    ws.append(["数据源", "阿里云ECS/RDS/Redis/MQTT/CLB/NAS/OSS/资源中心/CloudMonitor只读API"])
    ws.append(["未包含", "业务数据库表及记录、OSS对象清单、密码、AccessKey、Token、Cookie、证书私钥、函数环境变量值"])
    ws.append(["风险边界", "风险等级用于运维排查优先级，不代表已确认故障或已完成整改。"])
    style_sheet(ws)
    for cell in ws[1]:
        cell.font = Font(name=FONT, size=11, bold=True, color=WHITE)
        cell.fill = PatternFill("solid", fgColor=NAVY)
        cell.alignment = Alignment(horizontal="center")
    ws.column_dimensions["A"].width = 24
    ws.column_dimensions["B"].width = 110
    ws.freeze_panes = "A2"
    for row in range(2, ws.max_row + 1):
        ws.row_dimensions[row].height = 32
        for cell in ws[row]:
            cell.border = Border(bottom=THIN)


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--input", type=Path, required=True)
    parser.add_argument("--output", type=Path, required=True)
    args = parser.parse_args()
    records = read_records(args.input)
    snapshot_text = records[0]["快照时间"]
    snapshot_time = datetime.strptime(snapshot_text, "%Y-%m-%d %H:%M:%S")
    wb = Workbook()
    wb._named_styles["Normal"].font = Font(name=FONT, size=11, color="1F1F1F")
    add_summary(wb, records, snapshot_time)
    add_data_sheet(wb, "全部资源", records)
    add_data_sheet(wb, "ECS服务器", [r for r in records if r.get("资源类型") == "ECS"])
    add_data_sheet(wb, "数据库与消息", [r for r in records if r.get("资源类型") in {"RDS", "Redis", "MQTT", "表格存储"}])
    add_data_sheet(wb, "高风险与临期", [r for r in records if r.get("风险等级") == "高" or (isinstance(r.get("剩余天数"), (int, float)) and r["剩余天数"] <= 60)])
    add_field_notes(wb, snapshot_time)
    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    wb.calculation.calcMode = "auto"
    args.output.parent.mkdir(parents=True, exist_ok=True)
    wb.save(args.output)


if __name__ == "__main__":
    main()