"""
15_excel_export.py
Excel导出模块：多工作表汇总导出
Excel Export Module: multi-sheet consolidated workbook export

用途 / Purpose:
    将全流程各阶段的关键产出汇总为一个多工作表 Excel 工作簿，便于
    非技术读者（政策分析师、审校人员）直接查阅，而不需要单独打开
    每个 CSV/JSON 文件。工作表包括：
        - Data: 最终 RE 分数面板（38国 x 24年 = 912行）
        - Weights: 维度权重方案
        - Reliability: 信度效度检验结果
        - DomainSubIndices: 领域子指数
        - README: 字段说明与方法学简介

依赖 / Dependencies: pandas, openpyxl
"""

import json
import logging
from pathlib import Path

import pandas as pd

logging.basicConfig(level=logging.INFO, format="%(asctime)s  %(message)s")
log = logging.getLogger("15_excel_export")

DATA_DIR = Path("./data")
OUTPUT_DIR = Path("./output")

README_ROWS = [
    ("iso3", "ISO 3166-1 alpha-3 国家代码 / country code"),
    ("year", "年份 (2000-2023) / year"),
    ("v_score", "财政响应力维度分数 [0,1] / Fiscal Responsiveness dimension score"),
    ("c_score", "转化能力维度分数 [0,1] / Conversion Capacity dimension score"),
    ("f_score", "制度摩擦维度分数 [0,1] / Institutional Friction dimension score"),
    ("re_score", "资源重配置效率复合指数 [0,100] / Resource Reallocation Efficiency composite score"),
]


def main():
    OUTPUT_DIR.mkdir(exist_ok=True)

    scores = pd.read_csv(DATA_DIR / "panel_re_scores.csv")

    try:
        with open(DATA_DIR / "dimension_weights.json", "r", encoding="utf-8") as f:
            weights = json.load(f)
        weight_rows = []
        for dim, cols in weights["weights"].items():
            for col, w in cols.items():
                weight_rows.append({"dimension": dim, "indicator": col, "weight": round(w, 4)})
        weights_df = pd.DataFrame(weight_rows)
    except FileNotFoundError:
        weights_df = pd.DataFrame(columns=["dimension", "indicator", "weight"])
        log.warning("dimension_weights.json not found; Weights sheet will be empty")

    try:
        with open(DATA_DIR / "reliability_validity_report.json", "r", encoding="utf-8") as f:
            reliability = json.load(f)
        reliability_df = pd.DataFrame(
            [{"dimension": d, "cronbachs_alpha": a, "ave": reliability["ave"].get(d)}
             for d, a in reliability["cronbachs_alpha"].items()]
        )
    except FileNotFoundError:
        reliability_df = pd.DataFrame(columns=["dimension", "cronbachs_alpha", "ave"])
        log.warning("reliability_validity_report.json not found; Reliability sheet will be empty")

    try:
        domain_df = pd.read_csv(DATA_DIR / "domain_sub_indices.csv")
    except FileNotFoundError:
        domain_df = pd.DataFrame()
        log.warning("domain_sub_indices.csv not found; DomainSubIndices sheet will be empty")

    readme_df = pd.DataFrame(README_ROWS, columns=["field", "description"])

    out_path = OUTPUT_DIR / "OECD38_RE_Index_2000_2023.xlsx"
    with pd.ExcelWriter(out_path, engine="openpyxl") as writer:
        scores.to_excel(writer, sheet_name="Data", index=False)
        weights_df.to_excel(writer, sheet_name="Weights", index=False)
        reliability_df.to_excel(writer, sheet_name="Reliability", index=False)
        domain_df.to_excel(writer, sheet_name="DomainSubIndices", index=False)
        readme_df.to_excel(writer, sheet_name="README", index=False)

    log.info("Excel workbook exported -> %s", out_path)


if __name__ == "__main__":
    main()