#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel模板生成器
=======================================
创建Excel模板文件，包含示例数据和公式
"""

import pandas as pd
from pathlib import Path
from datetime import datetime, timedelta


def create_excel_template():
    """创建Excel模板"""

    output_dir = Path(__file__).parent / 'report' / 'excel_templates'
    output_dir.mkdir(parents=True, exist_ok=True)

    # 读取实际数据
    logs_file = Path(__file__).parent / 'report' / 'output_test' / 'Unified_Termination_Logs.csv'

    if not logs_file.exists():
        print("[ERROR] Please run logs_etl.py first to generate data!")
        return

    df = pd.read_csv(logs_file, encoding='utf-8-sig')

    # 创建Excel文件
    excel_file = output_dir / 'Termination_Dashboard_Template.xlsx'

    with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
        # Sheet 1: 详细日志
        df.to_excel(writer, sheet_name='详细日志', index=False)

        # Sheet 2: 每日汇总
        summary = df.groupby(['ExecutionDate', 'System']).agg({
            'EmployeeID': 'count',
            'Status': lambda x: (x == 'SUCCESS').sum()
        }).rename(columns={'EmployeeID': '总数', 'Status': '成功数'})
        summary['成功率'] = (summary['成功数'] / summary['总数'] * 100).round(2)
        summary.to_excel(writer, sheet_name='每日汇总', index=True)

        # Sheet 3: 系统对比
        system_stats = df.groupby('System').agg({
            'EmployeeID': 'count',
            'Status': lambda x: (x == 'SUCCESS').sum()
        }).rename(columns={'EmployeeID': '总数', 'Status': '成功数'})
        system_stats['成功率'] = (system_stats['成功数'] / system_stats['总数'] * 100).round(2)
        system_stats.to_excel(writer, sheet_name='系统对比', index=True)

        # Sheet 4: 错误分析
        errors = df[df['Status'] != 'SUCCESS'].copy()
        if len(errors) > 0:
            error_stats = errors.groupby('Message').agg({
                'EmployeeID': 'count'
            }).rename(columns={'EmployeeID': '次数'}).sort_values('次数', ascending=False)
            error_stats.head(10).to_excel(writer, sheet_name='错误分析', index=True)
        else:
            pd.DataFrame().to_excel(writer, sheet_name='错误分析', index=False)

    print(f"[OK] Excel template created: {excel_file.name}")
    print(f"     Location: {excel_file}")

    # 创建简单的统计报告
    stats_file = output_dir / '统计报告.txt'
    with open(stats_file, 'w', encoding='utf-8') as f:
        f.write("="*70 + "\n")
        f.write("离职处理统计报告\n")
        f.write("="*70 + "\n\n")

        f.write(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        f.write(f"数据来源: {logs_file.name}\n\n")

        f.write("总体统计:\n")
        f.write(f"  总记录数: {len(df)}\n")
        f.write(f"  成功: {len(df[df['Status']=='SUCCESS'])}\n")
        f.write(f"  失败: {len(df[df['Status']=='FAILED'])}\n")
        f.write(f"  跳过: {len(df[df['Status']=='SKIPPED'])}\n\n")

        f.write("系统统计:\n")
        system_stats = df.groupby('System').agg({
            'EmployeeID': 'count',
            'Status': lambda x: (x == 'SUCCESS').sum()
        }).rename(columns={'EmployeeID': '总数', 'Status': '成功数'})

        for system, row in system_stats.iterrows():
            success_rate = (row['成功数'] / row['总数'] * 100) if row['总数'] > 0 else 0
            f.write(f"  {system:10} - 总数:{row['总数']:3}  成功:{row['成功数']:3}  成功率:{success_rate:.1f}%\n")

        f.write("\n" + "="*70 + "\n")

    print(f"[OK] Statistics report: {stats_file.name}")

    return excel_file


def main():
    print("="*70)
    print("Excel Template Generator")
    print("="*70)
    print("\n[INFO] Creating Excel template with sample data...")

    excel_file = create_excel_template()

    print("\n" + "="*70)
    print("Excel Template Created Successfully!")
    print("="*70)
    print(f"\nFile: {excel_file}")
    print("\nNext Steps:")
    print("1. Open the Excel file")
    print("2. Use the data to create PivotTables and Charts")
    print("3. Or connect to Power BI using this Excel as source")
    print("\n" + "="*70)


if __name__ == '__main__':
    main()