#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Power BI模板生成器
=======================================
功能：生成Power BI模板文件(.pbit)和Power Query M代码
"""

import json
import os
from pathlib import Path
from datetime import datetime


def create_pbit_template(output_dir: Path):
    """创建Power BI模板文件"""

    # M代码字符串定义

    template = {
        "version": "1.0.0",
        "creationDate": datetime.now().isoformat(),
        "description": "离职处理看板模板",
        "queries": {
            "Unified_Termination_Logs": {
                "query": """
let
    Source = Csv.Document(File.Contents("output/Unified_Termination_Logs.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"ExecutionDate", type date},
        {"ExecutionTime", type time},
        {"System", type text},
        {"LogFile", type text},
        {"EmployeeID", type text},
        {"UserName", type text},
        {"Action", type text},
        {"Status", type text},
        {"Message", type text},
        {"Property", type text},
        {"Position", type text},
        {"ExtraInfo", type text}
    }),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, Int64.Type, Int64.Type)
in
    #"Added Index"
""",
                "queryOptions": {
                    "FTA_QueryGroup": "Unified_Termination_Logs",
                    "FTA_QuerySource": "Output/Unified_Termination_Logs.csv",
                    "FTA_DataKind": "FOLDER",
                    "FTA_FileSource": "Output",
                    "FTA_DatabaseSource": "Unified_Termination_Logs.csv"
                }
            },
            "Daily_Summary": {
                "query": """
let
    Source = Csv.Document(File.Contents("output/Daily_Summary.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"ExecutionDate", type date},
        {"System", type text},
        {"TotalProcessed", type number},
        {"SuccessCount", type number},
        {"FailedCount", type number},
        {"SkippedCount", type number},
        {"SuccessRate", type number}
    })
in
    #"Changed Type"
""",
                "queryOptions": {
                    "FTA_QueryGroup": "Daily_Summary",
                    "FTA_QuerySource": "output/Daily_Summary.csv"
                }
            },
            "Error_Details": {
                "query": """
let
    Source = Csv.Document(File.Contents("output/Error_Details.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [TextType.Double]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"ExecutionDate", type date},
        {"System", type text},
        {"ErrorType", type text},
        {"ErrorCount", type number},
        {"EmployeeCount", type number},
        {"TopEmployees", type text},
        {"FirstOccurrence", type datetime}
    })
in
    #"Changed Type"
""",
                "queryOptions": {
                    "FTA_QueryGroup": "Error_Details",
                    "FTA_QuerySource": "output/Error_Details.csv"
                }
            },
            "DateTable": {
                "query": """
let
    StartDate = #date(2026, 1, 1),
    EndDate = DateTime.Date(DateTime.LocalNow()),
    Days = Number.From(EndDate - StartDate),
    Source = List.Dates(StartDate, Days, #duration(1, 0, 0, 0, 0, 0)),
    #"Table to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"Changed Type" = Table.TransformColumnTypes(#"Table to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{{"Column1", "Date"}}}),
    #"Added Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Month" = Table.AddColumn(#"Added Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Added MonthName" = Table.AddColumn(#"Added Month", "MonthName", each Date.ToText([Date], "MMMM"), type text),
    #"Added Day" = Table.AddColumn(#"Added MonthName", "Day", each Date.Day([Date]), Int64.Type),
    #"Added Quarter" = Table.AddColumn(#"Added Day", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Week" = Table.AddColumn(#"Added Quarter", "Week", each Date.WeekOfYear([Date]), Int64.Type),
    #"Added Weekday" = Table.AddColumn(#"Added Week", "Weekday", each Date.DayOfWeek([Date]), Int64.Type)
in
    #"Added Weekday"
""",
                "queryOptions": {
                    "FTA_QueryGroup": "DateTable",
                    "FTA_QuerySource": "参数"
                }
            }
        },
        "model": {
            "relationships": [
                {
                    "name": "5a0b0e3f-7ff5-4e0a-babe-1e75db6beaab",
                    "from": "DateTable",
                    "to": "Unified_Termination_Logs",
                    "fromCardinality": "one",
                    "toCardinality": "many",
                    "fromColumn": "Date",
                    "toColumn": "ExecutionDate",
                    "isActive": True
                },
                {
                    "name": "8c7d8f5e-5c7b-4e3d-9a2f-1c5e6d8b5e8a",
                    "from": "DateTable",
                    "to": "Daily_Summary",
                    "fromCardinality": "one",
                    "toCardinality": "many",
                    "fromColumn": "Date",
                    "toColumn": "ExecutionDate",
                    "isActive": True
                }
            ],
            "tables": [
                {
                    "name": "Unified_Termination_Logs",
                    "columns": [
                        {"name": "ExecutionDate", "dataType": "string"},
                        {"name": "ExecutionTime", "dataType": "string"},
                        {"name": "System", "dataType": "string"},
                        {"name": "LogFile", "dataType": "string"},
                        {"name": "EmployeeID", "dataType": "string"},
                        {"name": "UserName", "dataType": "string"},
                        {"name": "Action", "dataType": "string"},
                        {"name": "Status", "dataType": "string"},
                        {"name": "Message", "dataType": "string"},
                        {"name": "Property", "dataType": "string"},
                        {"name": "Position", "dataType": "string"},
                        {"name": "ExtraInfo", "dataType": "string"}
                    ],
                    "measures": {
                        "Total Processed": {
                            "expression": "COUNTROWS('Unified_Termination_Logs')"
                        },
                        "Success Count": {
                            "expression": "CALCULATE(COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = \"SUCCESS\")"
                        },
                        "Failed Count": {
                            "expression": "CALCULATE(COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = \"FAILED\")"
                        },
                        "Skipped Count": {
                            "expression": "CALCULATE(COUNTROWS('Unified_Termination_Logs'), 'Unified_Termination_Logs'[Status] = \"SKIPPED\")"
                        },
                        "Success Rate": {
                            "expression": "DIVIDE([Success Count], [Total Processed])"
                        }
                    }
                },
                {
                    "name": "Daily_Summary",
                    "columns": [
                        {"name": "ExecutionDate", "dataType": "string"},
                        {"name": "System", "dataType": "string"},
                        {"name": "TotalProcessed", "dataType": "string"},
                        {"name": "SuccessCount", "dataType": "string"},
                        {"name": "FailedCount", "dataType": "string"},
                        {"name": "SkippedCount", "dataType": "string"},
                        {"name": "SuccessRate", "dataType": "string"}
                    ]
                },
                {
                    "name": "Error_Details",
                    "columns": [
                        {"name": "ExecutionDate", "dataType": "string"},
                        {"name": "System", "dataType": "string"},
                        {"name": "ErrorType", "dataType": "string"},
                        {"name": "ErrorCount", "dataType": "string"},
                        {"name": "EmployeeCount", "dataType": "string"},
                        {"name": "TopEmployees", "dataType": "string"},
                        {"name": "FirstOccurrence", "dataType": "string"}
                    ]
                },
                {
                    "name": "DateTable",
                    "columns": [
                        {"name": "Date", "dataType": "dateTime"},
                        {"name": "Year", "dataType": "int64"},
                        {"name": "Month", "dataType": "int64"},
                        {"name": "MonthName", "dataType": "string"},
                        {"name": "Day", "dataType": "int64"},
                        {"name": "Quarter", "dataType": "int64"},
                        {"name": "Week", "dataType": "int64"},
                        {"name": "Weekday", "dataType": "int64"}
                    ]
                }
            ]
        }
    }

    # 保存.pbit文件
    pbit_file = output_dir / 'Termination_Dashboard.pbit'
    with open(pbit_file, 'w', encoding='utf-8') as f:
        json.dump(template, f, indent=2, ensure_ascii=False)

    print(f"[OK] Created: {pbit_file.name}")

    return output_dir


def create_powerbi_m_files(output_dir: Path):
    """创建独立的Power Query M代码文件"""

    # Unified_Termination_Logs.m
    m_code_logs = """
let
    // 获取CSV文件
    Source = Folder.Files("output"),
    FilteredFiles = Table.SelectRows(Source, each ([Name] = "Unified_Termination_Logs.csv")),
    BinaryContent = FilteredFiles{[Name="Unified_Termination_Logs.csv"]}[Content],
    ImportCSV = Csv.Document(BinaryContent, [
        Delimiter=",",
        Columns=12,
        Encoding=65001,
        QuoteStyle=QuoteStyle.None
    ]),
    PromoteHeaders = Table.PromoteHeaders(ImportCSV, [TextType.Double]),
    ChangeTypes = Table.TransformColumnTypes(PromoteHeaders, {
        {"ExecutionDate", type date},
        {"ExecutionTime", type time},
        {"System", type text},
        {"LogFile", type text},
        {"EmployeeID", type text},
        {"UserName", type text},
        {"Action", type text},
        {"Status", type text},
        {"Message", type text},
        {"Property", type text},
        {"Position", type text},
        {"ExtraInfo", type text}
    })
in
    ChangeTypes
"""

    # Daily_Summary.m
    m_code_summary = """
let
    Source = Folder.Files("output"),
    FilteredFiles = Table.SelectRows(Source, each ([Name] = "Daily_Summary.csv")),
    BinaryContent = FilteredFiles{[Name="Daily_Summary.csv"]}[Content],
    ImportCSV = Csv.Document(BinaryContent, [
        Delimiter=",",
        Columns=7,
        Encoding=65001,
        QuoteStyle=QuoteStyle.None
    ]),
    PromoteHeaders = Table.PromoteHeaders(ImportCSV, [TextType.Double]),
    ChangeTypes = Table.TransformColumnTypes(PromoteHeaders, {
        {"ExecutionDate", type date},
        {"System", type text},
        {"TotalProcessed", type number},
        {"SuccessCount", type number},
        {"FailedCount", type number},
        {"SkippedCount", type number},
        {"SuccessRate", type number}
    })
in
    ChangeTypes
"""

    # Error_Details.m
    m_code_errors = """
let
    Source = Folder.Files("output"),
    FilteredFiles = Table.SelectRows(Source, each ([Name] = "Error_Details.csv")),
    BinaryContent = FilteredFiles{[Name="Error_Details.csv"]}[Content],
    ImportCSV = Csv.Document(BinaryContent, [
        Delimiter=",",
        Columns=7,
        Encoding=65001,
        QuoteStyle=QuoteStyle.None
    ]),
    PromoteHeaders = Table.PromoteHeaders(ImportCSV, [TextType.Double]),
    ChangeTypes = Table.TransformColumnTypes(PromoteHeaders, {
        {"ExecutionDate", type date},
        {"System", type text},
        {"ErrorType", type text},
        {"ErrorCount", type number},
        {"EmployeeCount", type number},
        {"TopEmployees", type text},
        {"FirstOccurrence", type datetime}
    })
in
    ChangeTypes
"""

    # 保存M代码文件
    m_files = {
        'Unified_Termination_Logs.m': m_code_logs,
        'Daily_Summary.m': m_code_summary,
        'Error_Details.m': m_code_errors
    }

    for filename, content in m_files.items():
        file_path = output_dir / filename
        with open(file_path, 'w', encoding='utf-8') as f:
            f.write(content)


def create_dax_measures(output_dir: Path):
    """创建DAX度量值代码文件"""

    dax_code = """
// ========================================
// 总体统计度量值
// ========================================

// 总处理数
Total Processed =
    COUNTROWS('Unified_Termination_Logs')

// 成功数
Success Count =
    CALCULATE(
        COUNTROWS('Unified_Termination_Logs'),
        'Unified_Termination_Logs'[Status] = "SUCCESS"
    )

// 失败数
Failed Count =
    CALCULATE(
        COUNTROWS('Unified_Termination_Logs'),
        'Unified_Termination_Logs'[Status] = "FAILED"
    )

// 跳过数
Skipped Count =
    CALCULATE(
        COUNTROWS('Unified_Termination_Logs'),
        'Unified_Termination_Logs'[Status] = "SKIPPED"
    )

// 成功率
Success Rate =
    DIVIDE([Success Count], [Total Processed])

// ========================================
// 系统对比度量值
// ========================================

// MDP处理数
MDP Processed =
    CALCULATE(
        [Total Processed],
        'Unified_Termination_Logs'[System] = "MDP"
    )

// HUB处理数
HUB Processed =
    CALCULATE(
        [Total Processed],
        'Unified_Termination_Logs'[System] = "HUB"
    )

// POTS处理数
POTS Processed =
    CALCULATE(
        [Total Processed],
        'Unified_Termination_Logs'[System] = "POTS"
    )

// WDTS处理数
WDTS Processed =
    CALCULATE(
        [Total Processed],
        'Unified_Termination_Logs'[System] = "WDTS"
    )

// MDP成功率
MDP Success Rate =
    CALCULATE(
        [Success Rate],
        'Unified_Termination_Logs'[System] = "MDP"
    )

// HUB成功率
HUB Success Rate =
    CALCULATE(
        [Success Rate],
        'Unified_Termination_Logs'[System] = "HUB"
    )

// POTS成功率
POTS Success Rate =
    CALCULATE(
        [Success Rate],
        'Unified_Termination_Logs'[System] = "POTS"
    )

// WDTS成功率
WDTS Success Rate =
    CALCULATE(
        [Success Rate],
        'Unified_Termination_Logs'[System] = "WDTS"
    )

// ========================================
// 时间趋势度量值
// ========================================

// 今日处理数
Today Processed =
    CALCULATE(
        [Total Processed],
        'DateTable'[Date] = TODAY()
    )

// 昨日处理数
Yesterday Processed =
    CALCULATE(
        [Total Processed],
        'DateTable'[Date] = TODAY() - 1
    )

// 本周总计
This Week Processed =
    CALCULATE(
        [Total Processed],
        WEEKNUM('DateTable'[Date]) = WEEKNUM(TODAY())
    )

// 本月总计
This Month Processed =
    CALCULATE(
        [Total Processed],
        MONTH('DateTable'[Date]) = MONTH(TODAY())
    )

// 近7日平均
Last 7 Days Avg =
    AVERAGEX(
        DATESINPERIOD('DateTable'[Date], TODAY(), -7, DAY),
        [Total Processed]
    )

// 环比增长
MoM Growth =
    VAR ThisMonth = [Total Processed]
    VAR LastMonth =
        CALCULATE(
            [Total Processed],
            DATEADD('DateTable'[Date], -1, MONTH)
        )
    RETURN
        DIVIDE(ThisMonth - LastMonth, LastMonth)

// 同比增长
YoY Growth =
    VAR ThisYear = [Total Processed]
    VAR LastYear =
        CALCULATE(
            [Total Processed],
            SAMEPERIODLASTYEAR('DateTable'[Date])
        )
    RETURN
        DIVIDE(ThisYear - LastYear, LastYear)

// ========================================
// 排名度量值
// ========================================

// 系统处理量排名
System Rank =
    RANKX(
        ALL('Unified_Termination_Logs'[System]),
        [Total Processed],
        ,
        DESC
    )

// 处理量前三的系统
Top 3 Systems =
    TOPN(
        3,
        VALUES('Unified_Termination_Logs'[System]),
        [Total Processed],
        DESC
    )

// ========================================
// 错误分析度量值
// ========================================

// 错误总数
Total Errors = [Failed Count] + [Skipped Count]

// 错误率
Error Rate =
    DIVIDE([Total Errors], [Total Processed])

// 最常见错误类型
Top Error Type =
    TOPN(
        1,
        VALUES('Error_Details'[ErrorType]),
        SUM('Error_Details'[ErrorCount]),
        DESC
    )

// ========================================
// 格式化度量值
// ========================================

// 成功率百分比格式
Success Rate % =
    FORMAT([Success Rate], "0.00%")

// 环比增长率格式
MoM Growth % =
    FORMAT([MoM Growth], "0.00%")

// 同比增长率格式
YoY Growth % =
    FORMAT([YoY Growth], "0.00%")
"""

    file_path = output_dir / 'DAX_Measures.dax'
    with open(file_path, 'w', encoding='utf-8') as f:
        f.write(dax_code)


def main():
    """主函数"""
    print("="*70)
    print("Power BI模板生成器")
    print("="*70)

    # 创建输出目录
    output_dir = Path(__file__).parent / 'powerbi_templates'
    output_dir.mkdir(exist_ok=True)

    # 生成文件
    print("\nGenerating Power BI files...")

    # 1. .pbit模板文件
    create_pbit_template(output_dir)

    # 2. Power Query M代码文件
    create_powerbi_m_files(output_dir)
    print(f"[OK] Created: 3 Power Query M code files")

    # 3. DAX度量值文件
    create_dax_measures(output_dir)
    print(f"[OK] Created: DAX_Measures.dax")

    print(f"\nAll files saved to: {output_dir}")

    print("\n" + "="*70)
    print("下一步操作：")
    print("="*70)
    print("""
1. 使用 .pbit 模板文件：
   - 双击 Termination_Dashboard.pbit
   - Power BI Desktop会自动打开
   - 点击"加载"按钮导入数据

2. 或手动创建：
   - 打开Power BI Desktop
   - 获取数据 → 文本/CSV
   - 加载 output/ 目录下的3个CSV文件

3. 添加DAX度量值：
   - 在"建模"选项卡中，点击"新建度量值"
   - 复制DAX_Measures.dax中的代码

4. 创建可视化：
   - 参考POWERBI_GUIDE.md中的看板设计
    """)

    print("\n" + "="*70)
    print("完成！")
    print("="*70)


if __name__ == '__main__':
    main()