from pathlib import Path

import matplotlib.pyplot as plt
import numpy as np
import openpyxl
from matplotlib import font_manager

BASE = Path(__file__).resolve().parent
XLSX = BASE / 'oda-model.xlsx'
OUT = BASE / 'generated-charts'
OUT.mkdir(parents=True, exist_ok=True)

font_candidates = [
    '/System/Library/Fonts/Hiragino Sans GB.ttc',
    '/System/Library/Fonts/PingFang.ttc',
]
for candidate in font_candidates:
    if Path(candidate).exists():
        font_manager.fontManager.addfont(candidate)
        plt.rcParams['font.family'] = font_manager.FontProperties(fname=candidate).get_name()
        break
plt.rcParams.update({
    'axes.unicode_minus': False,
    'figure.facecolor': '#f4f1eb',
    'axes.facecolor': '#f4f1eb',
    'axes.edgecolor': '#817b72',
    'axes.labelcolor': '#282622',
    'text.color': '#282622',
    'xtick.color': '#615d56',
    'ytick.color': '#615d56',
    'grid.color': '#d8d2c8',
    'grid.linewidth': 0.7,
    'font.size': 11,
})
COLORS = ['#4f6d7a', '#c07a52', '#7f9b76', '#a98467']


def finish(fig, name):
    fig.tight_layout()
    fig.savefig(OUT / name, dpi=180, bbox_inches='tight', facecolor=fig.get_facecolor())
    plt.close(fig)


wb = openpyxl.load_workbook(XLSX, data_only=True)

# 1. Composition
labels = ['日元贷款', '无偿援助', '技术合作']
values = [33165, 1576, 1858]
fig, ax = plt.subplots(figsize=(8, 5))
wedges, _ = ax.pie(values, startangle=90, colors=COLORS[:3], wedgeprops={'width': 0.35, 'edgecolor': '#f4f1eb'})
ax.text(0, 0.08, '90.6%', ha='center', va='center', fontsize=24, fontweight='bold')
ax.text(0, -0.17, '为需偿还贷款', ha='center', va='center', fontsize=11, color='#615d56')
ax.legend(wedges, [f'{label}  {value / sum(values):.1%}' for label, value in zip(labels, values)], loc='lower center', bbox_to_anchor=(0.5, -0.12), ncol=3, frameon=False)
ax.set_title('日本对华 ODA 的面额构成', fontsize=16, fontweight='bold', pad=16)
finish(fig, 'oda-composition.webp')

# 2. Annual commitments and GDP share
ws = wb['中日GDP与ODA占比']
years, commitments, shares = [], [], []
for row in ws.iter_rows(min_row=4, values_only=True):
    year, amount, share = row[0], row[20], row[26]
    if isinstance(year, int) and 1979 <= year <= 2007:
        years.append(year)
        commitments.append(float(amount or 0))
        shares.append(float(share or 0) * 100)
fig, ax1 = plt.subplots(figsize=(10, 5.4))
ax1.bar(years, commitments, color=COLORS[0], alpha=0.82, width=0.8)
ax1.set_ylabel('贷款承诺（亿日元）')
ax1.set_xlabel('年份')
ax1.grid(axis='y', alpha=0.7)
ax2 = ax1.twinx()
ax2.plot(years, shares, color=COLORS[1], linewidth=2.2, marker='o', markersize=3.5)
ax2.set_ylabel('贷款承诺 / 中国名义 GDP（%）', color=COLORS[1])
ax2.tick_params(axis='y', colors=COLORS[1])
peak = int(np.argmax(shares))
ax2.annotate(f'{years[peak]}：{shares[peak]:.3f}%', (years[peak], shares[peak]), xytext=(8, 13), textcoords='offset points', color=COLORS[1], fontsize=10)
ax1.set_title('ODA 可以重要，但宏观数量级始终有限', fontsize=16, fontweight='bold', pad=14)
finish(fig, 'oda-commitments-gdp-share.webp')

# 3. Debt service and FX cost
ws = wb['年度动态结果']
years, service, fx_cost = [], [], []
for row in ws.iter_rows(min_row=4, values_only=True):
    year = row[0]
    if isinstance(year, int) and year <= 2024:
        years.append(year)
        service.append(float(row[5] or 0))
        fx_cost.append(float(row[7] or 0))
fig, ax = plt.subplots(figsize=(10, 5.4))
ax.plot(years, service, color=COLORS[0], linewidth=2.1, label='实际汇率下本息')
ax.bar(years, fx_cost, color=COLORS[1], alpha=0.72, width=0.8, label='相对签约年汇率的增量成本')
ax.axhline(0, color='#817b72', linewidth=0.8)
ax.set_xlabel('财政年度')
ax.set_ylabel('亿元人民币')
ax.grid(axis='y', alpha=0.7)
ax.legend(frameon=False, loc='upper left')
ax.set_title('长期日元债务：低利率之外，还有汇率路径', fontsize=16, fontweight='bold', pad=14)
finish(fig, 'oda-debt-service-fx.webp')

# 4. Counterfactual financing ranges
ws = wb['情景区间']
rows = list(ws.iter_rows(min_row=4, max_row=6, values_only=True))
low_policy, central_policy, high_policy = [float(r[7]) for r in rows]
low_commercial, central_commercial, high_commercial = [float(r[8]) for r in rows]
centers = np.array([central_policy, central_commercial])
errors = np.array([[central_policy - low_policy, central_commercial - low_commercial], [high_policy - central_policy, high_commercial - central_commercial]])
fig, ax = plt.subplots(figsize=(8.6, 5.2))
positions = np.arange(2)
ax.bar(positions, centers, color=[COLORS[1], COLORS[2]], width=0.56)
ax.errorbar(positions, centers, yerr=errors, fmt='none', ecolor='#282622', capsize=7, linewidth=1.5)
ax.axhline(0, color='#282622', linewidth=1)
ax.set_xticks(positions, ['低成本政策/主权资金', '普通商业融资'])
ax.set_ylabel('累计融资优势（亿元人民币，折算至 2025 年）')
ax.grid(axis='y', alpha=0.7)
for i, value in enumerate(centers):
    ax.text(i, value + (35 if value >= 0 else -55), f'{value:.0f}', ha='center', va='bottom' if value >= 0 else 'top', fontweight='bold')
ax.set_title('“是否划算”取决于拿什么作反事实', fontsize=16, fontweight='bold', pad=14)
finish(fig, 'oda-financing-counterfactual.webp')

print('\n'.join(str(p) for p in sorted(OUT.glob('*.webp'))))