data-report/generate-data-report-ppt/scripts/ppt_builder.py

"""
PPT builder: assemble daily/weekly/monthly reports by duplicating master templates
and filling charts, tables, KPI cards, and structured insight text blocks.

Key design principle: Conclusion-first page titles + structured multi-paragraph
insights (title + body per paragraph) aligned with reference PPT style.
"""
import copy
import os
import sys
import re as re_module
from pathlib import Path
from datetime import datetime, timedelta

sys.path.insert(0, str(Path(__file__).parent))

from pptx import Presentation
from pptx.util import Emu, Pt
from pptx.dml.color import RGBColor
from pptx.enum.text import PP_ALIGN
from pptx.enum.shapes import MSO_SHAPE

from data_loader import (
    load_daily, load_weekly, load_monthly, load_date_range,
    load_generic_excel,
)
from metrics_calculator import (
    calc_daily_metrics, calc_weekly_metrics, calc_monthly_metrics, generate_deep_insights,
    calc_generic_metrics, calc_generic_trend, calc_generic_distribution,
    calc_generic_ranking, generate_generic_insights,
)
from chart_factory import (
    add_column_chart, add_bar_chart, add_line_chart, add_doughnut_chart,
    add_pie_chart, add_funnel_chart, add_horizontal_bar_chart,
    add_grouped_bar_chart, add_table
)
from page_layouts import (
    get_kpi_grid, get_chart_left_zone, get_insight_right_zone,
    get_full_width_zone, get_two_column_zones,
)
from quality_inspector import QualityInspector
from theme_manager import theme_to_rgb_colors, get_theme
from report_config import (
    ReportConfig, PageDef, MetricDef, PeriodType, ChartType,
    validate_six_confirmations,
)
from quality_rules import SLIDE_WIDTH, SLIDE_HEIGHT, CONTENT_LEFT, CONTENT_TOP_BASE, FOOTER_TOP

# Colors — aligned with reference design theme YAML
C_PRIMARY = RGBColor(0x1E, 0x3A, 0x5F)
C_ACCENT = RGBColor(0x10, 0xB9, 0x81)
C_ACCENT_NEG = RGBColor(0xEF, 0x44, 0x44)
C_SECONDARY = RGBColor(0x64, 0x74, 0x8B)
C_DARK = RGBColor(0x1F, 0x3A, 0x5C)
C_WHITE = RGBColor(0xFF, 0xFF, 0xFF)
C_GRAY_BG = RGBColor(0xF2, 0xF2, 0xF2)
C_TEXT = RGBColor(0x33, 0x33, 0x33)
C_TEXT_GRAY = RGBColor(0x66, 0x66, 0x66)
C_LINE = RGBColor(0xD9, 0xD9, 0xD9)
C_CARD_BG = RGBColor(0xE7, 0xF0, 0xF7)
C_GREEN = RGBColor(0x10, 0xB9, 0x81)
C_RED = RGBColor(0xEF, 0x44, 0x44)
C_ORANGE = RGBColor(0xED, 0x7D, 0x31)


# ==============================================================================
# MASTER / SLIDE HELPERS
# ==============================================================================

def get_master_template(report_type: str) -> str:
    """Route report type to corresponding master template."""
    base = os.path.join(os.path.dirname(__file__), '..', 'assets')
    template_map = {
        'daily': os.path.join(base, 'report-master.pptx'),
        'weekly': os.path.join(base, 'weekly-master.pptx'),
        'monthly': os.path.join(base, 'monthly-master.pptx'),
    }
    path = template_map.get(report_type, template_map['daily'])
    if os.path.exists(path):
        return os.path.abspath(path)
    # Fallbacks
    for fallback in [template_map['daily']]:
        if os.path.exists(fallback):
            return os.path.abspath(fallback)
    raise FileNotFoundError(f"Master template not found for {report_type}")


def _detect_content_top(slide) -> int:
    """Detect content start Y from a content slide template by reading {page_title} position."""
    page_title_bottom = Emu(1422400)  # daily default
    for shape in slide.shapes:
        if shape.has_text_frame and '{page_title}' in shape.text_frame.text:
            page_title_bottom = shape.top + shape.height
            break
    # Gap: generous spacing between page title and content to avoid crowding
    gap = Emu(381000)
    return int(page_title_bottom) + int(gap)


def _delete_template_slides(prs, count=4):
    for _ in range(count):
        if len(prs.slides) == 0:
            break
        rId = prs.slides._sldIdLst[0].rId
        prs.part.drop_rel(rId)
        del prs.slides._sldIdLst[0]


def _duplicate_slide(prs, source_slide):
    blank_layout = prs.slide_layouts[6]
    new_slide = prs.slides.add_slide(blank_layout)
    for shape in source_slide.shapes:
        el = shape.element
        new_el = copy.deepcopy(el)
        new_slide.shapes._spTree.insert_element_before(new_el, 'p:extLst')
    return new_slide


def _replace_placeholder(slide, placeholder, new_text):
    for shape in slide.shapes:
        if not shape.has_text_frame:
            continue
        for para in shape.text_frame.paragraphs:
            if placeholder in para.text:
                para.text = para.text.replace(placeholder, str(new_text))
                for run in para.runs:
                    run.font.name = '微软雅黑'


def _replace_all_placeholders(slide, mapping: dict):
    for placeholder, new_text in mapping.items():
        _replace_placeholder(slide, placeholder, new_text)


def _remove_shape(shape):
    """Remove a python-pptx shape from its parent tree."""
    el = shape.element
    el.getparent().remove(el)


def _remove_empty_cover_kpi_placeholders(slide):
    """
    Remove template KPI cards when generic cover data does not provide values.
    This prevents empty rounded rectangles from staying on the cover.
    """
    kpi_pattern = re_module.compile(r'\{kpi\d+_(label|value)\}')
    placeholder_shapes = [
        shape for shape in slide.shapes
        if shape.has_text_frame and kpi_pattern.search(shape.text_frame.text or '')
    ]
    if not placeholder_shapes:
        return

    x_min = min(int(shape.left) for shape in placeholder_shapes)
    x_max = max(int(shape.left) + int(shape.width) for shape in placeholder_shapes)
    y_min = min(int(shape.top) for shape in placeholder_shapes)
    y_max = max(int(shape.top) + int(shape.height) for shape in placeholder_shapes)
    pad = Emu(220000)

    to_remove = []
    for shape in slide.shapes:
        sx = int(shape.left)
        sy = int(shape.top)
        sw = int(shape.width)
        sh = int(shape.height)
        in_region = (
            sx >= x_min - pad and sx + sw <= x_max + pad and
            sy >= y_min - pad and sy + sh <= y_max + pad
        )
        is_text_placeholder = shape in placeholder_shapes
        is_empty_kpi_card = (
            in_region and
            getattr(shape, 'auto_shape_type', None) == MSO_SHAPE.ROUNDED_RECTANGLE
        )
        if is_text_placeholder or is_empty_kpi_card:
            to_remove.append(shape)

    for shape in to_remove:
        _remove_shape(shape)


# ==============================================================================
# NAVIGATION TABS
# ==============================================================================

def _add_nav_tabs(slide, tabs, active_index=0, slide_width=None,
                  tab_y=Emu(254000), tab_h=Emu(762000), underline_h=Emu(127000)):
    if slide_width is None:
        slide_width = slide.shapes._spTree.getparent().getparent().attrib.get('cx')
        slide_width = Emu(int(slide_width)) if slide_width else Emu(16256000)
    n = len(tabs)
    tab_w = Emu(int(slide_width) // n)
    for i, label in enumerate(tabs):
        x = Emu(i * int(tab_w))
        box = slide.shapes.add_textbox(x, tab_y, tab_w, tab_h)
        p = box.text_frame.paragraphs[0]
        p.text = label
        p.font.size = Pt(11)
        p.font.name = '微软雅黑'
        p.font.color.rgb = C_PRIMARY if i == active_index else C_TEXT_GRAY
        p.alignment = PP_ALIGN.CENTER
        if i == active_index:
            line = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, Emu(457200), tab_w, underline_h)
            line.fill.solid()
            line.fill.fore_color.rgb = C_PRIMARY
            line.line.fill.background()


# ==============================================================================
# KPI CARDS
# ==============================================================================

def _add_kpi_cards(slide, kpis, start_x=Emu(762000), start_y=Emu(1651000)):
    """Draw 3x2 KPI card grid. Each kpi: {'label', 'value', 'unit', 'change', 'sub'}"""
    positions = [
        (start_x, start_y),
        (Emu(5778500), start_y),
        (Emu(10795000), start_y),
        (start_x, Emu(start_y + 3429000)),
        (Emu(5778500), Emu(start_y + 3429000)),
        (Emu(10795000), Emu(start_y + 3429000)),
    ]
    for i, kpi in enumerate(kpis[:6]):
        if i >= len(positions):
            break
        x, y = positions[i]
        w, h = Emu(4699000), Emu(3048000)
        card = slide.shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE, x, y, w, h)
        card.fill.solid()
        card.fill.fore_color.rgb = C_CARD_BG
        card.line.fill.background()

        # Label
        lbl = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 228600), Emu(2540000), Emu(406400))
        p = lbl.text_frame.paragraphs[0]
        p.text = kpi.get('label', '')
        p.font.size = Pt(14)
        p.font.color.rgb = C_TEXT_GRAY
        p.font.name = '微软雅黑'

        # Value
        val = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 762000), Emu(2540000), Emu(698500))
        p = val.text_frame.paragraphs[0]
        p.text = str(kpi.get('value', ''))
        p.font.size = Pt(36)
        p.font.bold = True
        p.font.color.rgb = C_PRIMARY
        p.font.name = 'Arial'

        # Unit
        unit = kpi.get('unit', '')
        if unit:
            ubox = slide.shapes.add_textbox(Emu(x + 3048000), Emu(y + 1016000), Emu(508000), Emu(381000))
            p = ubox.text_frame.paragraphs[0]
            p.text = unit
            p.font.size = Pt(14)
            p.font.color.rgb = C_TEXT_GRAY
            p.font.name = '微软雅黑'

        # Change badge
        chg = kpi.get('change', '')
        if chg:
            cbox = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 1778000), Emu(4064000), Emu(304800))
            p = cbox.text_frame.paragraphs[0]
            p.text = chg
            p.font.size = Pt(12)
            chg_str = str(chg)
            is_positive = chg_str.startswith('+') or any(k in chg_str for k in ['↑', '提升', '增长', '上调', '增加', '大幅', '好', '突破', '达成', '优化'])
            is_negative = chg_str.startswith('-') or any(k in chg_str for k in ['↓', '下滑', '下降', '减少', '回落', '滞后', '堆积', '阻塞', '缺口', '延迟'])
            if is_negative:
                p.font.color.rgb = C_RED
            elif is_positive:
                p.font.color.rgb = C_GREEN
            else:
                p.font.color.rgb = C_TEXT_GRAY
            p.font.name = '微软雅黑'

        # Sub note with semantic background color tag (e.g. "日均51笔")
        sub = kpi.get('sub', '')
        if sub:
            sub_text = _truncate_text(sub, 20)
            tag_color = _sentiment_color(sub_text)
            tag_x = Emu(x + 508000)
            tag_y = Emu(y + 2159000)
            tag_w = Emu(min(len(sub_text) * 220000 + 400000, 3600000))
            tag_h = Emu(304800)
            if tag_color:
                tag_bg = slide.shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE, tag_x, tag_y, tag_w, tag_h)
                tag_bg.fill.solid()
                tag_bg.fill.fore_color.rgb = tag_color
                tag_bg.line.fill.background()
            sbox = slide.shapes.add_textbox(tag_x, tag_y, tag_w, tag_h)
            p = sbox.text_frame.paragraphs[0]
            p.text = sub_text
            p.font.size = Pt(11)
            p.font.color.rgb = C_TEXT_GRAY
            p.font.name = '微软雅黑'
            p.alignment = PP_ALIGN.CENTER


# ==============================================================================
# TEXT BLOCKS
# ==============================================================================

def _add_text_block(slide, title, body, left, top, width, height,
                    title_size=Pt(14), body_size=Pt(11), line_space=Pt(6)):
    """Single text box with title + body."""
    box = slide.shapes.add_textbox(left, top, width, height)
    tf = box.text_frame
    tf.word_wrap = True
    p = tf.paragraphs[0]
    p.text = title
    p.font.size = title_size
    p.font.bold = True
    p.font.color.rgb = C_PRIMARY if title else C_TEXT
    p.font.name = '微软雅黑'
    if body:
        p2 = tf.add_paragraph()
        p2.text = body
        p2.font.size = body_size
        p2.font.color.rgb = C_TEXT
        p2.font.name = '微软雅黑'
        p2.space_before = line_space
        p2.line_spacing = 1.3


def _estimate_text_height(items, title_size_pt, body_size_pt, width_emu,
                            line_spacing=1.15, title_extra=1.3):
    """Estimate rendered text height in EMU for adaptive font sizing."""
    width_pt = width_emu / 12700.0
    chars_per_line_body = max(10, int(width_pt / (body_size_pt * 1.15)))
    chars_per_line_title = max(10, int(width_pt / (title_size_pt * 1.15)))
    line_height_body = int(body_size_pt * line_spacing * 12700)
    line_height_title = int(title_size_pt * title_extra * 12700)
    total = 0
    for item in items:
        title = item.get('title', '')
        content = item.get('content', '')
        title_lines = max(1, (len(title) + chars_per_line_title - 1) // chars_per_line_title)
        content_lines = max(1, (len(content) + chars_per_line_body - 1) // chars_per_line_body)
        total += title_lines * line_height_title + content_lines * line_height_body + int(6 * 12700)
    return total


def _add_structured_insight(slide, items, left, top, width, height,
                            title_size=Pt(12), body_size=Pt(11),
                            max_items=None, min_body_size=Pt(9)):
    """
    High-density structured multi-paragraph insight block.
    items: list of {'title': str, 'content': str}
    Features:
      - No truncation; full content rendered
      - No max_items limit by default (render all)
      - Auto-shrink body font to fit within height (down to min_body_size)
      - Compact line spacing (1.15) to maximize density
      - Each bullet has emoji + bold title + normal body
    """
    if not items:
        return

    # Adaptive font sizing: shrink body_size until it fits
    target_height = int(height)
    # title_size/body_size may be EMU integers or Pt objects; normalize to pt
    _ts = float(title_size) / 12700.0 if float(title_size) > 1000 else float(title_size)
    _bs = float(body_size) / 12700.0 if float(body_size) > 1000 else float(body_size)
    _min_bs = float(min_body_size) / 12700.0 if float(min_body_size) > 1000 else float(min_body_size)
    ts_pt = _ts
    bs_pt = _bs
    min_bs_pt = _min_bs

    # Binary-search-like shrink to fit
    while bs_pt > min_bs_pt:
        est = _estimate_text_height(items, ts_pt, bs_pt, int(width))
        if est <= target_height:
            break
        bs_pt -= 0.5
        ts_pt = max(bs_pt + 1, ts_pt - 0.25)

    box = slide.shapes.add_textbox(left, top, width, height)
    tf = box.text_frame
    tf.word_wrap = True
    first = True

    for item in items[:max_items] if max_items else items:
        if not first:
            spacer = tf.add_paragraph()
            spacer.text = ''
            spacer.space_before = Pt(3)
        title = item.get('title', '')
        emoji = _emoji_for_item(title)
        # Avoid double emoji
        if emoji and title.startswith(emoji):
            emoji = ''
        title_text = f'{emoji} {title}' if emoji else title
        p = tf.paragraphs[0] if first else tf.add_paragraph()
        p.text = title_text
        p.font.size = Pt(ts_pt)
        p.font.bold = True
        p.font.color.rgb = C_PRIMARY
        p.font.name = '微软雅黑'
        p.line_spacing = 1.15
        first = False

        content = item.get('content', '')
        if content:
            p2 = tf.add_paragraph()
            p2.text = content
            p2.font.size = Pt(bs_pt)
            p2.font.color.rgb = C_TEXT
            p2.font.name = '微软雅黑'
            p2.line_spacing = 1.15
            p2.space_before = Pt(1)


# ==============================================================================
# ALERT / ACTION / ISSUE / GOAL CARDS
# ==============================================================================

def _add_alert_cards(slide, alerts, start_y=Emu(1651000)):
    """Draw 1-3 alert cards horizontally. Supports 严重/中度/一般 levels."""
    colors = {'严重': C_RED, '警告': C_ORANGE, '关注': C_PRIMARY, '中度': C_ORANGE, '一般': C_SECONDARY}
    positions = [Emu(762000), Emu(5778500), Emu(10795000)]
    for i, alert in enumerate(alerts[:3]):
        x = positions[i]
        y = start_y
        lvl = alert.get('level', '关注')
        c = colors.get(lvl, C_PRIMARY)

        bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(2286000))
        bar.fill.solid()
        bar.fill.fore_color.rgb = c
        bar.line.fill.background()

        tbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 228600), Emu(4064000), Emu(406400))
        p = tbox.text_frame.paragraphs[0]
        p.text = alert.get('title', '')
        p.font.size = Pt(15)
        p.font.bold = True
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'

        dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 762000), Emu(4064000), Emu(1270000))
        tf = dbox.text_frame
        tf.word_wrap = True
        p = tf.paragraphs[0]
        p.text = alert.get('detail', '')
        p.font.size = Pt(11)
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'


def _add_action_cards(slide, actions, start_y=Emu(2540000)):
    """Draw 3 action cards horizontally."""
    positions = [Emu(762000), Emu(5778500), Emu(10795000)]
    for i, act in enumerate(actions[:3]):
        x = positions[i]
        y = start_y
        bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(406400))
        bar.fill.solid()
        bar.fill.fore_color.rgb = C_PRIMARY
        bar.line.fill.background()

        tbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 952500), Emu(4064000), Emu(406400))
        p = tbox.text_frame.paragraphs[0]
        p.text = act.get('title', '')
        p.font.size = Pt(17)
        p.font.bold = True
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'

        dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 1524000), Emu(4064000), Emu(3429000))
        tf = dbox.text_frame
        tf.word_wrap = True
        p = tf.paragraphs[0]
        p.text = act.get('detail', '')
        p.font.size = Pt(11)
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'
        p.line_spacing = 1.3


def _add_issue_cards(slide, issues, start_y=Emu(1524000)):
    """Draw stacked issue cards with severity, title, detail, action."""
    colors = {'严重': C_RED, '中度': C_ORANGE, '轻度': C_PRIMARY, '一般': C_SECONDARY}
    for i, issue in enumerate(issues[:3]):
        x = Emu(762000)
        y = Emu(int(start_y) + i * (1778000 + 254000))
        sev = issue.get('severity', '中度')
        c = colors.get(sev, C_ORANGE)

        bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(1778000))
        bar.fill.solid()
        bar.fill.fore_color.rgb = c
        bar.line.fill.background()

        sbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 228600), Emu(660400), Emu(304800))
        p = sbox.text_frame.paragraphs[0]
        p.text = sev
        p.font.size = Pt(11)
        p.font.bold = True
        p.font.color.rgb = c
        p.font.name = '微软雅黑'

        tbox = slide.shapes.add_textbox(Emu(x + 1778000), Emu(y + 228600), Emu(13462000), Emu(355600))
        p = tbox.text_frame.paragraphs[0]
        p.text = issue.get('title', '')
        p.font.size = Pt(13)
        p.font.bold = True
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'

        dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 698500), Emu(14224000), Emu(355600))
        p = dbox.text_frame.paragraphs[0]
        p.text = issue.get('detail', '')
        p.font.size = Pt(11)
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'

        abox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 1193800), Emu(14224000), Emu(609600))
        tf = abox.text_frame
        tf.word_wrap = True
        p = tf.paragraphs[0]
        p.text = f"建议措施：{issue.get('action', '')}"
        p.font.size = Pt(11)
        p.font.color.rgb = C_TEXT_GRAY
        p.font.name = '微软雅黑'


def _add_goal_cards(slide, goals, start_y=Emu(1524000)):
    """Draw G1-G4 goal cards in 2x2 grid with icon+title+detail."""
    sy = int(start_y)
    positions = [
        (Emu(762000), Emu(sy)),
        (Emu(8318500), Emu(sy)),
        (Emu(762000), Emu(sy + 1879600)),
        (Emu(8318500), Emu(sy + 1879600)),
    ]
    icon_chars = ['🎯', '💰', '🚀', '⚡']
    for i, goal in enumerate(goals[:4]):
        x, y = positions[i]
        gid = goal.get('id', f'G{i+1}')

        gbox = slide.shapes.add_textbox(x, Emu(y + 101600), Emu(635000), Emu(355600))
        p = gbox.text_frame.paragraphs[0]
        p.text = f"{icon_chars[i % len(icon_chars)]} {gid}"
        p.font.size = Pt(16)
        p.font.bold = True
        p.font.color.rgb = C_PRIMARY
        p.font.name = 'Arial'

        tbox = slide.shapes.add_textbox(Emu(x + 863600), Emu(y + 101600), Emu(6096000), Emu(355600))
        p = tbox.text_frame.paragraphs[0]
        p.text = goal.get('title', '')
        p.font.size = Pt(14)
        p.font.bold = True
        p.font.color.rgb = C_TEXT
        p.font.name = '微软雅黑'

        dbox = slide.shapes.add_textbox(Emu(x + 228600), Emu(y + 571500), Emu(6731000), Emu(863600))
        tf = dbox.text_frame
        tf.word_wrap = True
        p = tf.paragraphs[0]
        p.text = goal.get('detail', '')
        p.font.size = Pt(11)
        p.font.color.rgb = C_TEXT_GRAY
        p.font.name = '微软雅黑'
        p.line_spacing = 1.3


def _add_summary_text(slide, text, left=Emu(1016000), top=Emu(5435600), width=Emu(14224000), height=Emu(1270000)):
    box = slide.shapes.add_textbox(left, top, width, height)
    tf = box.text_frame
    tf.word_wrap = True
    p = tf.paragraphs[0]
    p.text = text
    p.font.size = Pt(12)
    p.font.color.rgb = C_TEXT
    p.font.name = '微软雅黑'
    p.line_spacing = 1.3


# ==============================================================================
# STRUCTURED INSIGHT GENERATORS
# ==============================================================================

def _insight_trend_structured(trend_dates, trend_vals, metrics, period_name='近10天'):
    """Generate structured multi-paragraph trend insight."""
    items = []
    if not trend_vals or len(trend_vals) < 2:
        items.append({'title': '数据概览', 'content': f'{period_name}订单数据平稳，暂无明显波动。'})
        return items

    peak = max(trend_vals)
    peak_idx = trend_vals.index(peak)
    low = min(trend_vals)
    low_idx = trend_vals.index(low)
    last = trend_vals[-1]
    first = trend_vals[0]
    total = sum(trend_vals)
    avg = total / len(trend_vals)

    # Paragraph 1: Order scale
    curr_orders = metrics.get('tracking_orders', last)
    prev_orders = metrics.get('prev_tracking_orders', 0)
    order_chg = _pct_val(curr_orders, prev_orders)
    curr_qty = metrics.get('total_qty', 0)
    prev_qty = metrics.get('prev_total_qty', 0)
    qty_chg = _pct_val(curr_qty, prev_qty)
    avg_size = metrics.get('avg_order_size', 0)
    prev_avg_size = metrics.get('prev_avg_order_size', 0)

    scale_text = f'今日订单量{curr_orders}单'
    if prev_orders > 0:
        diff = curr_orders - prev_orders
        scale_text += f'，较昨日{"增加" if diff >= 0 else "减少"}{abs(diff)}单'
    if curr_qty > 0:
        scale_text += f'，订单总数量{curr_qty:,}台'
        if prev_qty > 0:
            qdiff = curr_qty - prev_qty
            scale_text += f'，较昨日{"增加" if qdiff >= 0 else "减少"}{abs(qdiff)}台'
    if avg_size > 0:
        scale_text += f'，单笔订单平均规模{avg_size:.0f}台'
        if prev_avg_size > 0:
            adiff = avg_size - prev_avg_size
            if abs(adiff) >= 1:
                scale_text += f'（{"上升" if adiff >= 0 else "下降"}{abs(adiff):.0f}台）'
    items.append({'title': '订单规模分析', 'content': scale_text})

    # Paragraph 2: Peak fluctuation
    peak_text = ''
    if peak == last:
        peak_text = f'今日达到峰值{peak}单，为{period_name}最高水平。'
    elif low == last:
        peak_text = f'今日回落至{last}单，为{period_name}最低水平。'
    else:
        peak_text = f'峰值出现在{trend_dates[peak_idx]}（{peak}单），低谷在{trend_dates[low_idx]}（{low}单）。'
        # Describe recovery pattern
        if len(trend_vals) >= 3:
            recent = trend_vals[-3:]
            if recent[-1] > recent[-2] and recent[-2] < recent[0]:
                peak_text += f'连续回落后今日回升至{last}单，呈现反弹态势。'
            elif recent[-1] < recent[-2]:
                peak_text += f'近期呈回落趋势，需关注后续走势。'
    items.append({'title': '峰值波动', 'content': peak_text})

    # Paragraph 3: Activity / update
    updated = metrics.get('updated_orders', 0)
    prev_updated = metrics.get('prev_updated_orders', 0)
    if updated > 0 or prev_updated > 0:
        act_text = f'今日进度更新{updated}单'
        if prev_updated > 0:
            udiff = updated - prev_updated
            upct = _pct_val(updated, prev_updated)
            act_text += f'，较昨日{"增加" if udiff >= 0 else "减少"}{abs(udiff)}单（{upct:+.1f}%）'
            if abs(upct) > 20:
                act_text += '，团队活跃度波动较大。' if abs(upct) > 30 else '，团队活跃度有所变化。'
            else:
                act_text += '，团队活跃度保持平稳。'
        else:
            act_text += '。'
        items.append({'title': '活跃度分析', 'content': act_text})

    return items


def _insight_status_structured(status_dist, prev_status_dist=None):
    """Generate structured status distribution insight."""
    items = []
    total = sum(status_dist.values())
    if not total:
        items.append({'title': '状态概览', 'content': '暂无订单状态数据。'})
        return items

    max_status = max(status_dist.items(), key=lambda x: x[1])
    max_pct = max_status[1] / total * 100

    # Production share (C+D)
    prod = status_dist.get('已付订金待生产', 0) + status_dist.get('已生产待付尾款', 0)
    prod_pct = prod / total * 100

    status_text = f'{max_status[0]}占比最高（{max_status[1]}单，{max_pct:.1f}%）'
    if prod_pct > 0:
        status_text += f'。生产端（已付订金+已生产）合计{prod}单（{prod_pct:.1f}%）'
        if prod_pct > 30:
            status_text += '，生产推进力度加大。'
        else:
            status_text += '。'
    items.append({'title': '状态分布特征', 'content': status_text})

    # WoW change
    if prev_status_dist:
        changes = []
        for name, curr in status_dist.items():
            prev = prev_status_dist.get(name, 0)
            if prev > 0:
                chg = _pct_val(curr, prev)
                if abs(chg) > 5:
                    changes.append(f'{name}{chg:+.1f}%')
        if changes:
            items.append({'title': '状态变化（vs 昨日）', 'content': ' | '.join(changes[:4])})

    return items


def _insight_region_structured(region_dist):
    """Generate structured regional insight with top countries."""
    items = []
    if not region_dist:
        items.append({'title': '区域概览', 'content': '暂无区域分布数据。'})
        return items

    sorted_regions = sorted(region_dist.items(), key=lambda x: -x[1]['qty'])
    top3 = sorted_regions[:3]
    total = sum(v['qty'] for v in region_dist.values())
    top3_pct = sum(v['qty'] for _, v in top3) / total * 100 if total else 0

    top_names = [k for k, _ in top3]
    items.append({'title': '核心市场', 'content': f'{"、".join(top_names)}三大核心市场合计占比{top3_pct:.1f}%，是海外订单的核心增长引擎。'})

    # Each region detail
    for name, data in sorted_regions[:5]:
        top_c = data.get('top_countries', [])
        top_c_str = '/'.join([c['country'] for c in top_c[:3]]) if top_c else ''
        change = data.get('change_pct', 0)
        if change is None:
            chg_str = ''
        elif change > 0:
            chg_str = f'（+{change:.1f}%）'
        elif change < 0:
            chg_str = f'（{change:.1f}%）'
        else:
            chg_str = ''
        content = f'{data["pct"]:.1f}% | {data["qty"]:,}台'
        if top_c_str:
            content += f' | {top_c_str}为主力'
        if change != 0:
            content += f' {chg_str}'
        if change < 0:
            content += ' | 需关注'
        items.append({'title': name, 'content': content})

    return items


def _insight_team_structured(team, total_qty=0, per_capita=0, countries_covered=0):
    """Generate structured team performance insight."""
    items = []
    if not team:
        items.append({'title': '团队概览', 'content': '暂无团队绩效数据。'})
        return items

    n_members = len(team)
    avg = per_capita or _safe_div(sum(v.get('orders', 0) for v in team.values()), n_members)
    top = max(team.items(), key=lambda x: x[1].get('orders', 0))

    overview = f'团队共{n_members}人，'
    if countries_covered:
        overview += f'覆盖{countries_covered}国，'
    overview += f'人均追踪{avg:.0f}单。'
    items.append({'title': '团队概况', 'content': overview})

    # Top performers
    sorted_team = sorted(team.items(), key=lambda x: -x[1].get('orders', 0))
    for name, data in sorted_team[:2]:
        orders = data.get('orders', 0)
        qty = data.get('qty', 0)
        comment = '增长主力' if orders > avg * 1.3 else '稳健跟进'
        content = f'{name} {orders}单'
        if qty:
            content += f'/{qty:,}台'
        content += f' - {comment}'
        items.append({'title': '领跑者点评', 'content': content})

    # Find laggard
    low = min(team.items(), key=lambda x: x[1].get('orders', 0))
    if low[1].get('orders', 0) < avg * 0.7:
        items.append({'title': '关注提醒', 'content': f'{low[0]}订单量低于团队均值，建议关注产能提升空间。'})

    return items


def _insight_stage_structured(stage_analysis, funnel):
    """Generate structured monthly stage funnel insight."""
    items = []
    early = stage_analysis.get('early', {})
    mid = stage_analysis.get('mid', {})
    late = stage_analysis.get('late', {})

    a_b = early.get('orders', 0)
    items.append({
        'title': '前期Pipeline充足',
        'content': f'合同拟定中(A) + 已锁定待付订金(B)共{a_b}单，占总量的{early.get("pct", 0):.1f}%，后续转化空间充足。'
    })

    c_d = mid.get('orders', 0)
    items.append({
        'title': '中期生产推进',
        'content': f'已付订金待生产(C) + 已生产待付尾款(D)共{c_d}单，占总量的{mid.get("pct", 0):.1f}%。'
    })

    e_f = late.get('orders', 0)
    items.append({
        'title': '后期交付待加速',
        'content': f'已付尾款待发运(E) + 已发运(F)仅{e_f}单，占总量的{late.get("pct", 0):.1f}%。'
    })

    if early.get('pct', 0) > 40:
        items.append({
            'title': '⚠ 风险提示',
            'content': f'近半数订单仍停留在合同拟定阶段，需关注A→B的转化效率，加速合同确认和订金回收。'
        })

    return items


def _insight_top_countries_structured(top_countries_change, total_qty, top_n=6):
    """Generate structured TOP countries insight."""
    items = []
    if not top_countries_change:
        items.append({'title': '国家概览', 'content': '暂无国家分布数据。'})
        return items

    sorted_items = sorted(top_countries_change.items(), key=lambda x: -x[1]['qty'])
    top_list = sorted_items[:top_n]
    total_top = sum(v['qty'] for _, v in top_list)
    pct = total_top / total_qty * 100 if total_qty else 0

    items.append({'title': '集中度分析', 'content': f'Top {top_n}目的国合计覆盖{total_top:,}台，占总量的{pct:.1f}%，重点市场集中度高。'})

    for i, (country, data) in enumerate(top_list, 1):
        chg = data.get('change_pct', 0)
        comment = ''
        if chg is None:
            comment = '新增市场，潜力可期'
            chg_str = ''
        elif chg > 30:
            comment = '本周增长最快市场之一'
            chg_str = f'（{chg:+.1f}%）'
        elif chg > 10:
            comment = '持续增长'
            chg_str = f'（{chg:+.1f}%）'
        elif chg < -10:
            comment = '虽有下滑但仍高位' if data['qty'] > total_qty * 0.05 else '需关注'
            chg_str = f'（{chg:.1f}%）'
        elif chg < 0:
            comment = '小幅回落'
            chg_str = f'（{chg:.1f}%）'
        else:
            comment = 'steady增长' if i <= 3 else '新兴市场，潜力可期'
            chg_str = f'（{chg:+.1f}%）' if chg != 0 else ''
        items.append({'title': f'{i}. {country} {data["qty"]:,}台{chg_str}', 'content': comment})

    return items


# ==============================================================================
# TEXT / LAYOUT HELPERS
# ==============================================================================

def _truncate_text(text, max_chars=60):
    """Truncate text to max_chars, appending '...' if truncated."""
    if not text:
        return text
    if len(text) > max_chars:
        return text[:max_chars - 1] + '...'
    return text


def _sentiment_color(text):
    """Return a light background color based on text sentiment."""
    if not text:
        return None
    text = str(text)
    positive_words = ['提升', '增长', '上调', '增加', '高', '好', '大幅', '冲刺', '领跑', '上升', '扩大', '优化', '改善', '突破', '达成']
    negative_words = ['下滑', '下降', '减少', '低', '差', '回落', '下滑', '滞后', '堆积', '阻塞', '缺口', '延迟', '超期', '逾期', '风险', '警告']
    pos_score = sum(1 for w in positive_words if w in text)
    neg_score = sum(1 for w in negative_words if w in text)
    if neg_score > pos_score:
        return RGBColor(0xFE, 0xE2, 0xE2)  # light red ~ #EF444420
    if pos_score > neg_score:
        return RGBColor(0xD1, 0xFA, 0xE5)  # light green ~ #10B98120
    return None


import re

def _emoji_for_item(title):
    """Return an emoji prefix based on title keywords."""
    if not title:
        return '📈'
    title = str(title)
    # Skip if title already starts with an emoji
    if re.match(r'^[\U0001F300-\U0001F9FF\u2600-\u26FF\u2700-\u27BF]', title):
        return ''
    if any(k in title for k in ['风险', '警告', '关注', '下滑', '下降', '延迟', '超期', '缺口', '阻塞']):
        return '⚠️'
    if any(k in title for k in ['建议', '措施', '行动', '协调', '对接']):
        return '💡'
    if any(k in title for k in ['目标', '计划', '冲刺', '展望', '聚焦']):
        return '🎯'
    if any(k in title for k in ['增长', '上升', '提升', '峰值', '领跑', '突破', '活跃', '好转']):
        return '📈'
    return '💡'


def _add_footer_if_missing(slide, footer_text, slide_width=None):
    """Add a bottom footer bar to slides that don't already have one (Cover, TOC, End)."""
    if slide_width is None:
        slide_width = slide.shapes._spTree.getparent().getparent().attrib.get('cx')
        slide_width = Emu(int(slide_width)) if slide_width else Emu(16256000)
    # Check if footer already exists
    has_footer = False
    for shape in slide.shapes:
        if shape.has_text_frame and '数据来源' in shape.text_frame.text:
            has_footer = True
            break
    if has_footer:
        return
    bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, 0, Emu(8824000), slide_width, Emu(320000))
    bar.fill.solid()
    bar.fill.fore_color.rgb = C_PRIMARY
    bar.line.fill.background()
    box = slide.shapes.add_textbox(Emu(762000), Emu(8824000), Emu(14000000), Emu(320000))
    p = box.text_frame.paragraphs[0]
    p.text = footer_text
    p.font.size = Pt(10)
    p.font.color.rgb = C_WHITE
    p.font.name = '微软雅黑'


def _ensure_word_wrap_all(slide):
    """Enable word_wrap on all text frames in a slide."""
    for shape in slide.shapes:
        if shape.has_text_frame:
            shape.text_frame.word_wrap = True
            for para in shape.text_frame.paragraphs:
                for run in para.runs:
                    run.font.name = '微软雅黑'


# ==============================================================================
# MATH HELPERS
# ==============================================================================

def _pct_val(curr, prev):
    if prev and prev != 0:
        return (curr - prev) / prev * 100
    return None

def _format_pct(pct, with_sign=True, suffix='%', zero_suffix=''):
    """Safely format a percentage value. Returns '—' if pct is None."""
    if pct is None:
        return '—'
    sign = '+' if with_sign and pct >= 0 else ''
    return f"{sign}{pct:.1f}{suffix}{zero_suffix}"


def _pct_str(curr, prev):
    if prev and prev != 0:
        pct = round((curr - prev) / prev * 100, 1)
        sign = '+' if pct >= 0 else ''
        return f"{sign}{pct}% vs 上期"
    return "—"


def _safe_div(a, b):
    return round(a / b, 1) if b else 0



# ==============================================================================
# DYNAMIC / UNIVERSAL REPORT BUILDER
# ==============================================================================

def build_report(data_file: str, config: ReportConfig, output_path: str) -> str:
    master_path = config.template_path or get_master_template('daily')
    prs = Presentation(master_path)

    df = load_generic_excel(data_file)
    if config.require_six_confirmations:
        confirmation_issues = validate_six_confirmations(config, list(df.columns))
        if confirmation_issues:
            raise ValueError('生成前六项确认未通过：\n- ' + '\n- '.join(confirmation_issues))
    profile = config.data_profiling or {}

    colors = theme_to_rgb_colors(config.theme)

    metrics = calc_generic_metrics(df, config)

    content_top = _detect_content_top(prs.slides[1]) if len(prs.slides) > 1 else 1524000

    total_pages = len([p for p in config.pages if p.selected])
    if total_pages == 0:
        total_pages = len(config.pages)

    for page_idx, page_def in enumerate(config.pages):
        if not page_def.selected:
            continue

        page_num = page_idx + 1

        if page_def.page_type == 'cover':
            _build_cover_page(prs, config, colors)
        elif page_def.page_type == 'toc':
            _build_toc_page(prs, config, colors)
        elif page_def.page_type == 'kpi_overview':
            _build_kpi_overview_page(prs, config, metrics, colors, content_top, df, profile)
        elif page_def.page_type == 'trend':
            _build_trend_page(prs, config, df, profile, colors, content_top)
        elif page_def.page_type == 'distribution':
            _build_distribution_page(prs, config, df, profile, colors, content_top, page_def)
        elif page_def.page_type == 'ranking':
            _build_ranking_page(prs, config, df, profile, colors, content_top, page_def)
        elif page_def.page_type == 'summary':
            _build_summary_page(prs, config, metrics, profile, colors, content_top, page_def)
        elif page_def.page_type == 'end':
            _build_end_page(prs, config, colors)

    for slide in prs.slides:
        _ensure_word_wrap_all(slide)

    _delete_template_slides(prs)
    prs.save(output_path)
    print(f"Report saved: {output_path}")
    return output_path


def quality_assured_build(data_file: str, config: ReportConfig,
                          output_path: str) -> tuple:
    if config.require_six_confirmations:
        df = load_generic_excel(data_file)
        confirmation_issues = validate_six_confirmations(config, list(df.columns))
        if confirmation_issues:
            raise ValueError('生成前六项确认未通过：\n- ' + '\n- '.join(confirmation_issues))

    inspector = QualityInspector(theme_to_rgb_colors(config.theme))

    return inspector.quality_assured_build(
        build_fn=lambda d, c: _build_without_save(d, c, config),
        data=data_file,
        config=config,
        output_path=output_path,
    )


def _build_without_save(data_file, temp_config, original_config):
    from pptx import Presentation as Prs
    prs = Prs(get_master_template('daily'))
    df = load_generic_excel(data_file)
    profile = original_config.data_profiling or {}
    colors = theme_to_rgb_colors(original_config.theme)
    metrics = calc_generic_metrics(df, original_config)
    content_top = _detect_content_top(prs.slides[1]) if len(prs.slides) > 1 else 1524000

    for page_def in original_config.pages:
        if not page_def.selected:
            continue
        if page_def.page_type == 'cover':
            _build_cover_page(prs, original_config, colors)
        elif page_def.page_type == 'kpi_overview':
            _build_kpi_overview_page(prs, original_config, metrics, colors, content_top, df, profile)
        elif page_def.page_type == 'trend':
            if not _build_trend_page(prs, original_config, df, profile, colors, content_top):
                _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top)
        elif page_def.page_type == 'distribution':
            if not _build_distribution_page(prs, original_config, df, profile, colors, content_top, page_def):
                _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top)
        elif page_def.page_type == 'ranking':
            if not _build_ranking_page(prs, original_config, df, profile, colors, content_top, page_def):
                _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top)
        elif page_def.page_type == 'summary':
            _build_summary_page(prs, original_config, metrics, profile, colors, content_top, page_def)
        elif page_def.page_type == 'end':
            _build_end_page(prs, original_config, colors)
        elif page_def.page_type == 'toc':
            _build_toc_page(prs, original_config, colors)

    for slide in prs.slides:
        _ensure_word_wrap_all(slide)
    _delete_template_slides(prs)
    return prs


def _build_cover_page(prs, config, colors):
    slide = _duplicate_slide(prs, prs.slides[0])
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{report_type}': '数据报告',
        '{date}': config.period_str or config.date_range[0].strftime('%Y年%m月%d日'),
        '{department}': config.source_label,
        '{period}': config.period_str,
        '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'),
    })
    _remove_empty_cover_kpi_placeholders(slide)
    _add_footer_if_missing(slide, f'数据来源：{config.source_label} | 1/{len(config.pages)}')


def _build_fallback_analysis_page(prs, config, page_def, df, profile, metrics, colors, content_top):
    """
    Fallback page builder: generates analysis text from available data
    when the primary page type cannot produce content (e.g. no time columns
    for trend, no category columns for distribution).
    Produces at least 4 deep analysis blocks with data citations.
    """
    slide = _duplicate_slide(prs, prs.slides[1])
    page_title = page_def.title if page_def and page_def.title else f'{config.title}数据分析'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })

    num_cols = profile.get('numeric_columns', [])
    cat_cols = profile.get('category_columns', [])

    insight_items = []

    if num_cols:
        top_metric = num_cols[0]
        top_name = top_metric.get('inferred_label', top_metric['column_name'])
        top_vals = df[top_metric['column_name']].dropna()
        if len(top_vals) > 0:
            mean_val = top_vals.mean()
            max_val = top_vals.max()
            min_val = top_vals.min()
            median_val = top_vals.median()
            total_val = top_vals.sum()
            insight_items.append({
                'title': f'{top_name}整体概览',
                'content': f'报告周期内，{top_name}统计数据共包含 {len(top_vals)} 条有效记录。'
                           f'总和为 {total_val:,.0f}，平均值为 {mean_val:,.2f}，中位数为 {median_val:,.2f}。'
                           f'最大值为 {max_val:,.2f}，最小值为 {min_val:,.2f}。'
                           f'{"数据波动范围较大，最大值与最小值差距显著，说明不同条目间差异明显，建议深入分析极端值成因" if min_val > 0 and max_val / max(min_val, 1) > 100 else "数据整体分布较为均衡，波动性在合理范围内"}。'
                           f'中位数与平均值的偏差反映了数据的{"右偏分布（少数大值拉高了均值），说明存在显著头部效应" if median_val < mean_val * 0.8 else "左偏分布" if median_val > mean_val * 1.2 else "较为对称，数据呈正态分布趋势"}。',
            })

            insight_items.append({
                'title': f'{top_name}分段分析',
                'content': f'对 {top_name} 进行四分段统计：上四分位数（25%数据高于此值）为 {top_vals.quantile(0.75):,.2f}，'
                           f'下四分位数（25%数据低于此值）为 {top_vals.quantile(0.25):,.2f}，'
                           f'四分位距（IQR）为 {top_vals.quantile(0.75) - top_vals.quantile(0.25):,.2f}。'
                           f'{"IQR较大，数据分布较为离散，不同类别的表现差异明显，需关注尾部类别的提升空间" if (top_vals.quantile(0.75) - top_vals.quantile(0.25)) > abs(mean_val) * 0.5 else "IQR在合理范围内，数据集中度较好"}。'
                           f'建议按四分位将数据分为四组，重点跟踪上四分位组的表现，识别可复制的成功因素。',
            })

    if cat_cols and num_cols:
        cat = cat_cols[0]
        cat_name = cat.get('inferred_label', cat['column_name'])
        num = num_cols[0]
        num_name = num.get('inferred_label', num['column_name'])
        cat_unique = df[cat['column_name']].dropna().nunique()
        insight_items.append({
            'title': f'{cat_name}分类覆盖分析',
            'content': f'数据共覆盖 {cat_unique} 个不同的{cat_name}，在 {num_name} 维度上呈现差异化分布。'
                       f'不同{cat_name}对整体{num_name}的贡献度各异，建议按贡献度大小将{cat_name}进行分类管理。'
                       f'高贡献类别应重点维护和深度挖掘，中等贡献类别需持续培育和资源投入，'
                       f'低贡献类别可评估其战略价值，适当调整投入节奏。建议建立分类分级管理体系，'
                       f'每月跟踪各类别的变化趋势和占比波动。',
        })

    if len(num_cols) >= 2:
        num1 = num_cols[0]
        num2 = num_cols[1]
        ratio = df[num1['column_name']].sum() / max(df[num2['column_name']].sum(), 1)
        insight_items.append({
            'title': '关键比率与效率指标',
            'content': f'{num1.get("inferred_label", num1["column_name"])}与{num2.get("inferred_label", num2["column_name"])}的比率为 {ratio:.2f}，'
                       f'该比率是衡量业务效率的重要参考指标。'
                       f'{"比率处于较高水平，表明单位投入产出效率良好" if ratio > 1 else "比率偏低，单位投入的产出效益有限，存在效率提升空间"}。'
                       f'建议将此比率纳入定期监控指标，按月环比追踪变化趋势，'
                       f'并针对低比率项目制定专项提升计划，分析制约因素和可优化环节。',
        })

    insight_items.append({
        'title': '数据质量与代表性评估',
        'content': f'本报告基于共 {len(df)} 条记录进行分析，数据覆盖范围包括上述多个维度。'
                   f'建议在后续周期中持续关注数据完整性和及时性，确保分析结果准确反映业务真实情况。'
                   f'对于数据量较小或集中度较高的维度，应结合业务判断进行解读，避免以偏概全。'
                   f'同时建议补充更多维度的数据（如时间序列数据、竞品对标数据等），'
                   f'以支撑更全面的分析视角和更精准的决策建议。',
    })

    if not insight_items:
        insight_items = [{
            'title': '数据总览',
            'content': f'当前数据集包含 {len(df)} 条记录，{len(df.columns)} 个字段。'
                       f'数值字段 {len(num_cols)} 个，分类字段 {len(cat_cols)} 个。'
                       f'建议结合业务场景规划具体的数据分析维度，'
                       f'以生成更具洞察力和指导意义的数据报告。',
        }]

    if num_cols and len(df) > 0:
        top_col = num_cols[0]
        chart_zone = get_chart_left_zone(content_top, 0.4)
        text_zone = get_insight_right_zone(content_top, 0.4)
        sample_vals = df[top_col['column_name']].dropna().head(10).tolist()
        sample_labels = [f'记录{i+1}' for i in range(len(sample_vals))]
        if sample_vals:
            add_bar_chart(slide, sample_labels, sample_vals,
                         Emu(chart_zone.x), Emu(chart_zone.y),
                         Emu(chart_zone.width), Emu(chart_zone.height),
                         series_name=top_col.get('inferred_label', top_col['column_name']),
                         color=colors.get('primary'))
        _add_structured_insight(slide, insight_items,
                                Emu(text_zone.x), Emu(text_zone.y),
                                Emu(text_zone.width), Emu(text_zone.height))
    else:
        zone = get_full_width_zone(content_top)
        _add_structured_insight(slide, insight_items,
                                Emu(zone.x), Emu(zone.y),
                                Emu(zone.width), Emu(zone.height))


def _build_toc_page(prs, config, colors):
    slide = _duplicate_slide(prs, prs.slides[1])
    active_pages = [p for p in config.pages if p.selected and p.page_type not in ('cover', 'toc', 'end')]
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': '目录',
        '{source}': config.source_label,
        '{period}': f'2/{len(config.pages)}',
        '{page_num}': '',
    })
    for i, page in enumerate(active_pages[:6], 1):
        _replace_placeholder(slide, f'{{chapter{i}_title}}', page.title)
        _replace_placeholder(slide, f'{{chapter{i}_desc}}', page.conclusion_title or page.title)


def _build_kpi_overview_page(prs, config, metrics, colors, content_top, df=None, profile=None):
    slide = _duplicate_slide(prs, prs.slides[1])
    page_title = '核心指标概览'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })
    kpi_items = []
    primary_vals = {}
    all_vals = {}
    for md in config.metrics:
        if md.metric_type.value == 'kpi' and md.selected:
            val = metrics.get(md.name, 0)
            display_val = format(val, md.format_spec) if isinstance(val, (int, float)) else str(val)
            kpi_items.append({
                'label': md.label,
                'value': display_val,
                'unit': md.unit,
                'change': '',
                'sub': '',
            })
            if md.is_primary:
                primary_vals[md.label] = val
            all_vals[md.label] = val

    if kpi_items:
        _add_kpi_cards(slide, kpi_items[:6], start_y=Emu(content_top))

        insight_items = []

        kpi_names = [m.label for m in config.metrics if m.selected]
        kpi_str = "、".join(kpi_names[:6]) if kpi_names else "各指标"
        primary_kpis = [m for m in config.metrics if m.is_primary and m.selected]
        if not primary_kpis:
            primary_kpis = [m for m in config.metrics if m.selected][:3]

        kpi_detail_parts = []
        for i, pk in enumerate(primary_kpis):
            val = all_vals.get(pk.label, 0)
            unit_str = pk.unit if pk.unit else ''
            display_val = format(val, pk.format_spec) if isinstance(val, (int, float)) else str(val)
            kpi_detail_parts.append(f'{pk.label}: {display_val}{unit_str}')

        insight_items.append({
            'title': '核心数据概览',
            'content': f'本期报告涵盖 {kpi_str} 共 {len(kpi_names)} 项核心指标。'
                       f'{"；".join(kpi_detail_parts[:4])}。'
                       f'其中{"、".join(p.label for p in primary_kpis[:3])}为本次分析的重点关注指标。'
                       f'建议将这些指标与历史同期数据进行纵向对比，以及与行业基准进行横向对标，以全面评估当前业务健康度。'
                       f'对于波动较大的指标，需深入追溯其背后的业务动因，判断是否为趋势性变化还是季节性波动。',
        })

        cat_cols = profile.get('category_columns', []) if profile else []
        num_cols = profile.get('numeric_columns', []) if profile else []
        total_rows = profile.get('total_rows', 0) if profile else 0

        if cat_cols:
            top_cats = [c.get('inferred_label', c.get('column_name', '')) for c in cat_cols[:3]]
            cat_details = []
            for c in cat_cols[:3]:
                uc = c.get('unique_count', 'N/A')
                cat_details.append(f'{c.get("inferred_label", c.get("column_name", ""))}({uc}类)')
            insight_items.append({
                'title': '数据覆盖与维度分析',
                'content': f'数据覆盖 {total_rows:,} 条记录，包含 {", ".join(cat_details)} 等多个分析维度。'
                           f'丰富的维度数据支持从 {", ".join(top_cats)} 等角度进行多维度联动分析。'
                           f'建议关注各维度下的数据分布特征，识别高贡献或异常的分类群体，'
                           f'针对性地分析不同维度的表现差异，为精细化运营和数据驱动决策提供支撑。',
            })

        if len(config.metrics) >= 3:
            compare_items = []
            for a, b in zip(primary_kpis[:2], primary_kpis[1:3]):
                va = all_vals.get(a.label, 0)
                vb = all_vals.get(b.label, 0)
                if va and vb:
                    ratio = round(va / vb, 2) if vb else 0
                    compare_items.append(f'{a.label}与{b.label}的比值为 {ratio}')
            if compare_items:
                insight_items.append({
                    'title': '指标间关联分析',
                    'content': f'{"；".join(compare_items)}。通过指标间的比值关系可以发现数据的内在规律，'
                               f'比值异常偏离正常区间时需重点关注。建议进一步计算各指标与核心业务目标之间的相关系数，'
                               f'量化不同指标对业务目标的影响力排序，将有限资源聚焦在驱动型指标上。',
                })
            else:
                insight_items.append({
                    'title': '指标间关联分析',
                    'content': f'本期核心指标包括 {", ".join(p.label for p in primary_kpis[:3])}。'
                               f'建议通过散点图或相关系数分析探索指标间的线性/非线性关系，识别是否存在协同或对冲效应。'
                               f'同时建议按时间序列分析各指标的周期性规律，为资源配置和预测提供依据。',
                })

        insight_items.append({
            'title': '关键发现与行动建议',
            'content': f'综合分析 {len(kpi_names)} 项指标，建议重点关注以下方向：'
                       f'(1) 定期监控核心指标的趋势变化，建立异常预警机制，当指标偏离正常区间时及时触发排查流程；'
                       f'(2) 深化多维度交叉分析，挖掘不同群体间的结构差异，识别增长机会和风险点；'
                       f'(3) 结合业务经验和外部数据，验证数据指标的准确性和合理性；'
                       f'(4) 将分析结论转化为可执行的具体行动项，明确责任人和时间节点，建立跟踪闭环机制。',
        })

        kpi_rows = 2 if len(kpi_items) > 3 else 1
        kpi_grid_bottom = int(content_top) + Emu(3048000)
        if kpi_rows == 2:
            kpi_grid_bottom += Emu(3429000)
        insight_zone_y = kpi_grid_bottom + Emu(254000)
        remaining_height = int(FOOTER_TOP - insight_zone_y - Emu(180000))
        if remaining_height >= Emu(1400000):
            compact_items = insight_items[:2] if kpi_rows == 2 else insight_items[:3]
            _add_structured_insight(slide, compact_items,
                                    Emu(CONTENT_LEFT), Emu(insight_zone_y),
                                    Emu(SLIDE_WIDTH - 2 * CONTENT_LEFT), Emu(remaining_height),
                                    title_size=Pt(10), body_size=Pt(9), min_body_size=Pt(8))


def _build_trend_page(prs, config, df, profile, colors, content_top):
    slide = _duplicate_slide(prs, prs.slides[1])
    time_cols = profile.get('time_columns', [])
    num_cols = profile.get('numeric_columns', [])
    if not time_cols or not num_cols:
        return False

    time_col = time_cols[0]['column_name']
    metric_col = num_cols[0]['column_name']
    label = num_cols[0].get('inferred_label', metric_col)

    page_title = f'{label}趋势'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })

    trend_data = calc_generic_trend(df, time_col, metric_col)

    if trend_data.get('dates'):
        chart_zone = get_chart_left_zone(content_top, 0.6)
        text_zone = get_insight_right_zone(content_top, 0.6)
        add_line_chart(slide, trend_data['dates'], trend_data['values'],
                       Emu(chart_zone.x), Emu(chart_zone.y),
                       Emu(chart_zone.width), Emu(chart_zone.height),
                       series_name=label, color=colors.get('primary'))

        dates = trend_data['dates']
        vals = trend_data['values']
        n = len(vals)
        first_v, last_v = vals[0], vals[-1]
        change = last_v - first_v
        change_pct = round(change / first_v * 100, 1) if first_v else 0
        max_v = max(vals) if vals else 0
        min_v = min(vals) if vals else 0
        max_idx = vals.index(max_v) if vals else 0
        min_idx = vals.index(min_v) if vals else 0
        peak_date = dates[max_idx] if max_idx < len(dates) else 'N/A'
        trough_date = dates[min_idx] if min_idx < len(dates) else 'N/A'

        direction_text = '上升' if change > 0 else '下降' if change < 0 else '平稳'
        volatility = round((max_v - min_v) / (sum(vals) / n) * 100, 1) if sum(vals) else 0 if vals else 0
        insight_items = [
            {
                'title': f'{label}整体趋势概况',
                'content': f'在报告周期内共采集 {n} 个时间点的数据，{label}'
                           f'从 {dates[0]} 的 {first_v:,.0f} 变动至 {dates[-1]} 的 {last_v:,.0f}，'
                           f'整体{direction_text}{abs(change_pct):.1f}%，{direction_text}趋势{"显著" if abs(change_pct) > 20 else "温和" if abs(change_pct) > 5 else "较为平缓"}。'
                           f'数据变化轨迹反映出{"持续向好的增长态势" if direction_text == "上升" and abs(change_pct) > 10 else "温和改善的积极信号" if direction_text == "上升" else "回调盘整的阶段性特征" if direction_text == "下降" else "平稳运行的基本状态"}，'
                           f'建议将当前趋势与业务目标和历史同期数据进行交叉对比，评估达成全年目标的可行性。如需更详尽的趋势分析，建议增加数据采集频度和时间跨度。',
            },
            {
                'title': '峰值与谷值分析',
                'content': f'周期内最高值出现在 {peak_date}，为 {max_v:,.0f}；'
                           f'最低值出现在 {trough_date}，为 {min_v:,.0f}。'
                           f'极值差距 {max_v - min_v:,.0f}，波动幅度 {volatility}%，'
                           f'{"波动显著，需关注异常节点的驱动因素，建议排查是否受节假日、促销活动、外部政策变化等因素影响" if volatility > 30 else "波动在可控范围内，但仍需对异常波动保持警觉"}{"." if volatility > 30 else "，建立异常值的快速预警和响应机制。"}',
            },
            {
                'title': '趋势阶段性特征',
                'content': f'前半程（{dates[0]}至{dates[min(n//2, n-1)]}）'
                           f'{"呈上升态势" if sum(vals[:n//2]) < sum(vals[n//2:]) else "呈下降态势" if sum(vals[:n//2]) > sum(vals[n//2:]) else "基本持平"}，'
                           f'后半程均值为 {sum(vals[n//2:])/(n-n//2):,.0f}。建议结合业务事件节点深入分析拐点成因，'
                           f'重点关注是否存在季节性波动、周期性波动或外部冲击等结构性因素。'
                           f'若数据量较少，趋势解读应以业务经验为主，辅以数据验证。',
            },
            {
                'title': '业务启示',
                'content': f'综合趋势分析，当前数据反映出{"积极向好的发展态势" if direction_text == "上升" and abs(change_pct) > 10 else "温和稳定的运行动态" if abs(change_pct) <= 10 else "需重点关注的下行风险"}。'
                           f'建议{"加大资源投入以把握增长机遇，同时关注增速的可持续性，避免盲目扩张" if direction_text == "上升" else "排查下降原因并制定针对性应对措施，分析是短期波动还是长期趋势转折" if direction_text == "下降" else "保持当前运营节奏，同时关注潜在变化信号，适时调整策略" if direction_text == "平稳" else "继续观察数据走势"}。'
                           f'建议将数据与业务KPI目标进行对标分析，定期回顾趋势变化。',
            },
        ]
        _add_structured_insight(slide, insight_items,
                                Emu(text_zone.x), Emu(text_zone.y),
                                Emu(text_zone.width), Emu(text_zone.height))
        return True
    return False


def _build_distribution_page(prs, config, df, profile, colors, content_top, page_def=None):
    slide = _duplicate_slide(prs, prs.slides[1])
    cat_cols = profile.get('category_columns', [])
    num_cols = profile.get('numeric_columns', [])
    if not cat_cols:
        return False

    elem = (page_def.elements or [{}])[0] if page_def else {}
    cat_col = elem.get('category') or cat_cols[0]['column_name']
    cat_label = elem.get('category_label') or next(
        (c.get('inferred_label', cat_col) for c in cat_cols if c['column_name'] == cat_col), cat_col)
    metric_col = elem.get('metric') or (num_cols[0]['column_name'] if num_cols else None)
    metric_label = elem.get('metric_label') or (next(
        (c.get('inferred_label', metric_col) for c in num_cols if c['column_name'] == metric_col), metric_col) if metric_col else '')

    page_title = page_def.title if page_def and page_def.title else f'{cat_label}分布'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })

    dist = calc_generic_distribution(df, cat_col, metric_col, top_n=8)

    if dist.get('categories'):
        chart_zone = get_chart_left_zone(content_top, 0.55)
        text_zone = get_insight_right_zone(content_top, 0.55)
        if len(dist['categories']) <= 8:
            add_doughnut_chart(slide, dist['categories'], dist['values'],
                              Emu(chart_zone.x), Emu(chart_zone.y),
                              Emu(chart_zone.width), Emu(chart_zone.height),
                              colors=colors.get('series'))
        else:
            add_bar_chart(slide, dist['categories'], dist['values'],
                         Emu(chart_zone.x), Emu(chart_zone.y),
                         Emu(chart_zone.width), Emu(chart_zone.height),
                         series_name=metric_label, color=colors.get('primary'))

        cats, vals, pcts = dist['categories'], dist['values'], dist['percentages']
        grand_total = sum(vals)
        top3_pct = sum(pcts[:3])
        top1_name, top1_val, top1_pct = cats[0], vals[0], pcts[0]

        metric_suffix = metric_label if metric_label else '数量'
        insight_items = [
            {
                'title': f'{cat_label}分布概况',
                'content': f'共有 {len(cats)} 个不同的{cat_label}，覆盖范围'
                           f'{"广泛" if len(cats) >= 8 else "较为丰富" if len(cats) >= 5 else "相对集中"}。'
                           f'前3名合计占比 {top3_pct:.1f}%，集中度'
                           f'{"较高，呈现显著的头部集中特征" if top3_pct > 70 else "中等，呈现梯度递减分布" if top3_pct > 50 else "较低，分布较为均衡"}。',
            },
            {
                'title': f'排名第一: {top1_name}',
                'content': f'{top1_name}以 {top1_val:,}{metric_suffix}（占比 {top1_pct:.1f}%）位居榜首，'
                           f'{"是第二名" + cats[1] + "的" + f"{round(top1_val/vals[1],1)}" + "倍，优势极为显著" if len(cats) > 1 else "是该维度中最重要的类别"}。'
                           f'该类别贡献了超过三分之一的{metric_label}，是整体业务的基本盘和核心增长极。',
            },
        ]
        if len(vals) >= 3:
            top3_sum = sum(vals[:3])
            tail_sum = sum(vals[3:])
            tail_pct = sum(pcts[3:])
            insight_items.append({
                'title': '长尾分布特征',
                'content': f'前三名累计 {top3_sum:,}{metric_suffix}（{top3_pct:.1f}%），'
                           f'剩余 {len(cats)-3} 个合计 {tail_sum:,}{metric_suffix}（{tail_pct:.1f}%），'
                           f'属于{"头部集中型分布" if top3_pct > 70 else "相对均衡分布" if top3_pct < 50 else "梯度递减型分布"}。'
                           f'头部贡献了绝大部分{metric_label}，尾部虽数量众多但单个贡献有限。',
            })
        if len(vals) > 1:
            avg_val = sum(vals) / len(vals)
            cv = round(vals[0] / avg_val, 1) if avg_val else 0
            median_idx = len(vals) // 2
            median_val = vals[median_idx]
            insight_items.append({
                'title': '差异化与离散度分析',
                'content': f'排名第一的{cat_label}{top1_name}的{metric_suffix}是全部分类均值的 {cv} 倍，'
                           f'中位数分类（第{median_idx+1}名）为 {median_val:,}{metric_suffix}，'
                           f'表明该维度{"差异化显著，资源集中度较高" if cv > 3 else "差异化适中，各分类间差距可控" if cv > 1.5 else "分布较为均匀"}。'
                           f'头部与中位数的差距反映了{cat_label}维度上的分层特征，是运营资源重点倾斜方向。',
            })
        insight_items.append({
            'title': '业务启示',
            'content': f'建议重点关注 {cats[0]} 的增量拓展与存量维护，同时深入分析排名中位类别的提升空间。'
                       f'对于 {metric_label}贡献较小的尾部类别（如占比低于3%的分类），可评估是否优化资源配置、'
                       f'调整运营策略或将资源向高回报类别倾斜。结合{cat_label}维度持续跟踪分布变化，及时把握结构性机会。',
        })

        _add_structured_insight(slide, insight_items,
                                Emu(text_zone.x), Emu(text_zone.y),
                                Emu(text_zone.width), Emu(text_zone.height))
        return True
    return False


def _build_ranking_page(prs, config, df, profile, colors, content_top, page_def=None):
    slide = _duplicate_slide(prs, prs.slides[1])
    cat_cols = profile.get('category_columns', [])
    num_cols = profile.get('numeric_columns', [])
    if not cat_cols or not num_cols:
        return False

    elem = (page_def.elements or [{}])[0] if page_def else {}
    rank_col = elem.get('category') or cat_cols[-1]['column_name']
    rank_label = elem.get('category_label') or next(
        (c.get('inferred_label', rank_col) for c in cat_cols if c['column_name'] == rank_col), rank_col)
    metric_col = elem.get('metric') or num_cols[0]['column_name']
    metric_label = elem.get('metric_label') or next(
        (c.get('inferred_label', metric_col) for c in num_cols if c['column_name'] == metric_col), metric_col)

    page_title = page_def.title if page_def and page_def.title else f'{rank_label}TOP排行'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })

    ranking = calc_generic_ranking(df, rank_col, metric_col, top_n=15)
    if ranking:
        chart_zone = get_chart_left_zone(content_top, 0.6)
        text_zone = get_insight_right_zone(content_top, 0.6)
        names = [r['name'] for r in ranking]
        vals = [r['value'] for r in ranking]
        add_bar_chart(slide, names, vals,
                     Emu(chart_zone.x), Emu(chart_zone.y),
                     Emu(chart_zone.width), Emu(chart_zone.height),
                     series_name=metric_label, color=colors.get('primary'))

        total_val = sum(vals)
        top3_names = [r['name'] for r in ranking[:3]]
        top3_vals = [r['value'] for r in ranking[:3]]
        top3_pct = [round(v / total_val * 100, 1) for v in top3_vals] if total_val else [0, 0, 0]
        top1_vs_last = round(vals[0] / vals[-1], 1) if len(vals) > 1 and vals[-1] > 0 else 'N/A'

        insight_items = [
            {
                'title': f'{rank_label}TOP排行概况',
                'content': f'共展示 {len(ranking)} 个排名项，前3名分别为 {top3_names[0]}、{top3_names[1]}、'
                           f'{top3_names[2]}，累计 {sum(top3_vals):,}{metric_label}（{sum(top3_pct):.1f}%）。'
                           f'前三名合计贡献超过总量的三分之一，表明{rank_label}维度呈现{"显著的头部集中特征" if sum(top3_pct) > 60 else "梯度递减的分布格局" if sum(top3_pct) > 40 else "相对均衡的分布态势"}。',
            },
            {
                'title': f'榜首分析: {top3_names[0]}',
                'content': f'{top3_names[0]}以 {top3_vals[0]:,}{metric_label}（占比 {top3_pct[0]:.1f}%）位居榜首，'
                           f'{"是第2名" + top3_names[1] + "的" + f"{round(top3_vals[0]/top3_vals[1],1)}倍，领先优势显著" if len(ranking) > 1 and top3_vals[1] > 0 else "优势突出"}。'
                           f'作为排名第一的{rank_label}，其业绩表现直接影响整体业务大盘，建议重点关注其可持续增长策略。',
            },
            {
                'title': '头部与尾部差距分析',
                'content': f'第1名与第{len(ranking)}名差距达 {top1_vs_last} 倍，'
                           f'前5名平均 {round(sum(vals[:5])/5):,}{metric_label}，'
                           f'后5名平均 {round(sum(vals[-5:])/5):,}{metric_label}，'
                           f'前后差距约 {round((sum(vals[:5])/5)/(sum(vals[-5:])/5),1) if sum(vals[-5:]) > 0 else "N/A"} 倍。'
                           f'{"头部效应极为明显，需关注是否因资源分配不均导致" if isinstance(top1_vs_last, float) and top1_vs_last > 10 else "差距较为显著，存在分层优化的空间" if isinstance(top1_vs_last, float) and top1_vs_last > 5 else "梯度分布相对均衡，可针对性提升各层级表现"}。',
            },
            {
                'title': '累计贡献率与分层分析',
                'content': f'前5名累计贡献 {sum(vals[:5]):,}{metric_label}（{round(sum(vals[:5])/total_val*100,1) if total_val else 0}%），'
                           f'前10名累计贡献 {sum(vals[:10]):,}{metric_label}（{round(sum(vals[:10])/total_val*100,1) if total_val else 0}%），'
                           f'剩余 {len(ranking)-10} 名合计贡献 {sum(vals[10:]):,}{metric_label}（{round(sum(vals[10:])/total_val*100,1) if total_val else 0}%）。'
                           f'从分层结构来看，可划分为三个梯队：第一梯队（前3名）为业绩核心贡献者，第二梯队（第4-8名）为稳定输出层，'
                           f'第三梯队（第9名及以后）为潜力提升层。',
            },
            {
                'title': '业务建议',
                'content': f'重点关注 {", ".join(top3_names)} 的发展动态，提炼其成功经验并推广至团队。'
                           f'对于排名靠后的{rank_label}，可评估其增长潜力与资源匹配度，'
                           f'识别可突破的增量空间。建议建立{rank_label}的绩效考核与激励体系，'
                           f'通过标杆带动和梯队培养实现整体业绩提升。',
            },
        ]
        _add_structured_insight(slide, insight_items,
                                Emu(text_zone.x), Emu(text_zone.y),
                                Emu(text_zone.width), Emu(text_zone.height))
        return True
    return False


def _build_summary_page(prs, config, metrics, profile, colors, content_top, page_def=None):
    slide = _duplicate_slide(prs, prs.slides[1])
    page_title = page_def.title if page_def and page_def.title else '总结与建议'
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
        '{date}': config.period_str,
        '{page_title}': page_title,
        '{source}': config.source_label,
        '{period}': '',
        '{page_num}': '',
    })

    elem = (page_def.elements or [{}])[0] if page_def else {}

    if elem.get('support_status') is not None:
        status = elem['support_status']
        dept = elem.get('support_by_dept', {})
        sc = elem.get('support_count', 0)
        cc = elem.get('closed_count', 0)
        close_rate = round(cc / sc * 100, 1) if sc else 0
        fully_closed = status.get('已闭环', 0)
        partial_closed = status.get('部分闭环', 0)
        not_closed = status.get('未闭环', 0)
        insight_items = [{
            'title': '支持需求总览',
            'content': f'本期共产生 {sc} 项跨部门支持需求，其中已闭环 {cc} 项（含完全闭环 {fully_closed} 项、部分闭环 {partial_closed} 项），'
                       f'闭环率 {close_rate}%。未闭环需求 {sc - cc} 项（占比 {round((sc-cc)/sc*100,1) if sc else 0}%），'
                       f'闭环率{"较高，跨部门协作效率良好" if close_rate >= 60 else "处于中等水平，仍有提升空间" if close_rate >= 30 else "偏低，需重点关注闭环推动"}。'
                       f'跨部门支持是保障项目推进的重要环节，高效的闭环机制有助于提升客户满意度和订单转化效率。',
        }]
        if status:
            total_status = sum(status.values())
            fully_pct = round(fully_closed / total_status * 100, 1) if total_status else 0
            partial_pct = round(partial_closed / total_status * 100, 1) if total_status else 0
            not_pct = round(not_closed / total_status * 100, 1) if total_status else 0
            insight_items.append({
                'title': '闭环状态明细',
                'content': f'已闭环 {fully_closed} 项（{fully_pct}%）、部分闭环 {partial_closed} 项（{partial_pct}%）、'
                           f'未闭环 {not_closed} 项（{not_pct}%）。'
                           f'其中完全闭环占比{"超过七成，闭环质量较高" if fully_pct >= 70 else "处于中等水平" if fully_pct >= 40 else "偏低，需提升闭环完整性"}。'
                           f'部分闭环表明需求已部分满足但未完全解决，需持续跟踪至彻底闭环。',
            })
        if dept:
            dept_top = list(dept.items())[:5]
            dept_top_sum = sum(v for _, v in dept_top)
            dept_total = sum(dept.values())
            dept_str = '、'.join([f'{k}（{v}项）' for k, v in dept_top])
            avg_dept_load = round(dept_total / len(dept), 1) if dept else 0
            max_dept = dept_top[0]
            insight_items.append({
                'title': '支持部门工作量分布',
                'content': f'需求覆盖 {len(dept)} 个部门/科室，前5个部门承接 {dept_top_sum} 项（{round(dept_top_sum/dept_total*100,1) if dept_total else 0}%）。'
                           f'Top部门：{dept_str}。其中{max_dept[0]}承接最多（{max_dept[1]}项），'
                           f'平均每个部门承接 {avg_dept_load} 项。请关注工作量较大的部门资源分配是否充足，'
                           f'同时识别是否有部门长期未被分配需求（可能表明资源未充分利用）。',
            })
        if sc - cc > 0:
            insight_items.append({
                'title': '未闭环需求跟进建议',
                'content': f'当前仍有 {sc - cc} 项需求未完成闭环。建议按以下策略推进：第一，按紧急程度和影响范围对未闭环需求进行优先级排序，'
                           f'高优需求指定专人负责限期解决；第二，建立周度闭环跟踪机制，定期更新需求处理进展；'
                           f'第三，对于跨部门协同的复杂需求，建议指定牵头部门统筹协调推进，'
                           f'并建立问题升级机制（当需求超期未解决时自动升级至更高层级协调）。',
            })
        insight_items.append({
            'title': '闭环效率提升建议',
            'content': f'为持续提升支持需求闭环效率，建议：一是建立标准化的需求流转流程，明确各环节责任人和响应时限；'
                       f'二是定期开展闭环案例复盘，提炼最佳实践并在团队内推广；'
                       f'三是建立闭环率考核指标，将闭环时效纳入部门协作评价体系，'
                       f'通过制度保障跨部门协作的效率和质量。',
        })
    else:
        insight_items = generate_generic_insights(profile, metrics)

    zone = get_full_width_zone(content_top)
    _add_structured_insight(slide, insight_items,
                            Emu(zone.x), Emu(zone.y),
                            Emu(zone.width), Emu(zone.height))


def _build_end_page(prs, config, colors):
    slide = _duplicate_slide(prs, prs.slides[3] if len(prs.slides) > 3 else prs.slides[0])
    total = len([p for p in config.pages if p.selected])
    _add_footer_if_missing(slide, f'数据来源：{config.source_label} | {total}/{total}')
    _replace_all_placeholders(slide, {
        '{report_title}': config.title,
    })


# ==============================================================================
# DAILY REPORT
# ==============================================================================

def build_daily_report(data_file: str, date: datetime, output_path: str,
                       department='海外事业部', source='海外订单日报系统'):
    master_path = get_master_template('daily')
    prs = Presentation(master_path)
    content_top = _detect_content_top(prs.slides[1])

    df = load_daily(data_file, date)
    prev_date = date - timedelta(days=1)
    try:
        prev_df = load_daily(data_file, prev_date)
    except Exception:
        prev_df = None

    metrics = calc_daily_metrics(df, prev_df)
    prev_metrics = calc_daily_metrics(prev_df, None) if prev_df is not None else {}
    date_str = date.strftime('%Y年%m月%d日')
    period_str = date.strftime('%Y年%m月%d日')

    # ---- Page 1: Cover ----
    slide = _duplicate_slide(prs, prs.slides[0])
    _replace_all_placeholders(slide, {
        '{report_title}': '海外订单数据日报',
        '{report_type}': '数据日报',
        '{date}': date_str,
        '{department}': department,
        '{period}': period_str,
        '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'),
    })
    _add_footer_if_missing(slide, f'数据来源：{source} | 1/8')
    cover_kpis = [
        ('在跟订单', metrics['tracking_orders'], '单',
         _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0))),
        ('订单总数量', f"{metrics['total_qty']:,}", '台',
         _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0))),
        ('今日已更新', metrics['updated_orders'], '单',
         _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0))),
        ('支持需求', metrics['support_requests'], '项', '需跨部门协调'),
    ]
    for i, (lbl, val, unit, chg) in enumerate(cover_kpis, 1):
        _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl)
        _replace_placeholder(slide, f'{{kpi{i}_value}}', str(val))

    # ---- Page 2: KPI Overview ----
    s2 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s2, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': '今日核心指标概览',
        '{source}': source,
        '{period}': '2/8',
        '{page_num}': '',
    })
    kpis = [
        {'label': '在跟订单总数', 'value': metrics['tracking_orders'], 'unit': '单',
         'change': _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0)),
         'sub': '日均跟踪'},
        {'label': '订单总数量', 'value': f"{metrics['total_qty']:,}", 'unit': '台',
         'change': _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0)),
         'sub': '规模稳定'},
        {'label': '今日已更新', 'value': metrics['updated_orders'], 'unit': '单',
         'change': _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0)),
         'sub': '团队活跃'},
        {'label': '下月预测交付', 'value': metrics['forecast_next'], 'unit': '台',
         'change': _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0)),
         'sub': '交付预期'},
        {'label': '支持需求总数', 'value': metrics['support_requests'], 'unit': '项',
         'change': '需跨部门协调', 'sub': '建议集中处理'},
        {'label': '已发运订单', 'value': metrics['shipped_orders'], 'unit': '单',
         'change': f'共{metrics.get("shipped_orders", 0) * 8}台 | {metrics["shipped_orders"] - metrics.get("prev_shipped_orders", 0)}单 vs 昨日',
         'sub': '交付稳步推进'},
    ]
    _add_kpi_cards(s2, kpis, start_y=Emu(content_top))

    # ---- Page 3: 10-Day Trend ----
    s3 = _duplicate_slide(prs, prs.slides[1])
    trend_dates = []
    trend_vals = []
    for d_offset in range(-9, 1):
        d = date + timedelta(days=d_offset)
        try:
            tdf = load_daily(data_file, d)
            trend_dates.append(d.strftime('%m/%d'))
            trend_vals.append(len(tdf))
        except Exception:
            pass

    # Generate conclusion title
    trend_conclusion = '近10天订单趋势'
    if len(trend_vals) >= 2:
        if trend_vals[-1] > trend_vals[-2]:
            trend_conclusion = '近10天订单趋势：订单规模回升'
        elif trend_vals[-1] < trend_vals[-2]:
            trend_conclusion = '近10天订单趋势：订单量继续回落'
        peak = max(trend_vals)
        if trend_vals[-1] == peak:
            trend_conclusion = '近10天订单趋势：今日达到峰值'

    _replace_all_placeholders(s3, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': trend_conclusion,
        '{source}': source,
        '{period}': '3/8',
        '{page_num}': '',
    })
    if len(trend_dates) >= 2:
        add_column_chart(s3, trend_dates, trend_vals,
                         Emu(762000), Emu(content_top), Emu(8890000), Emu(5334000),
                         series_name='订单量', color=C_ACCENT,
                         category_axis_title='日期', value_axis_title='订单数')
        insight_items = generate_deep_insights('daily', 'trend', metrics,
                                                prev_metrics=prev_metrics,
                                                trend_dates=trend_dates,
                                                trend_vals=trend_vals)
        _add_structured_insight(s3, insight_items,
                                Emu(9906000), Emu(content_top), Emu(4826000), Emu(5334000))

    # ---- Page 4: Status Distribution ----
    s4 = _duplicate_slide(prs, prs.slides[1])
    status_names = list(metrics['status_dist'].keys())
    status_vals = list(metrics['status_dist'].values())
    total_status = sum(status_vals)
    prod_share = metrics.get('production_share', 0)
    status_title = '订单状态分布'
    if prod_share > 0:
        status_title = f'订单状态分布：生产端占比提升至{prod_share:.1f}%'

    _replace_all_placeholders(s4, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': status_title,
        '{source}': source,
        '{period}': '4/8',
        '{page_num}': '',
    })
    if status_names and total_status > 0:
        # Left donut chart: 55% width
        chart_w = Emu(int(prs.slide_width) * 0.55)
        add_doughnut_chart(s4, status_names, status_vals,
                           Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                           show_legend=True, show_data_labels=True, show_percent=True,
                           ring_ratio=0.6)
        # Right side: status change + deep insights (no table to save space for dense text)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)

        prev_status = prev_metrics.get('status_dist', {})
        # Status change text: "合同拟定中 -30.8% ↓ | 已付订金待生产 +55.6% ↑"
        changes = []
        for name, curr in metrics['status_dist'].items():
            prev = prev_status.get(name, 0)
            if prev > 0:
                chg = _pct_val(curr, prev)
                if chg is not None:
                    arrow = '↑' if chg >= 0 else '↓'
                    changes.append(f'{name} {_format_pct(chg)}{arrow}')
        if changes:
            change_text = ' | '.join(changes[:4])
            _add_text_block(s4, '状态变化（vs 昨日）', change_text,
                            text_left, Emu(content_top), text_w, Emu(609600),
                            title_size=Pt(12), body_size=Pt(10))

        # Deep insight fills remaining right-side space
        insight_items = generate_deep_insights('daily', 'status', metrics,
                                               prev_status_dist=prev_status)
        insight_top = Emu(int(content_top) + 685800)
        insight_height = Emu(5334000 - 685800)
        _add_structured_insight(s4, insight_items,
                                text_left, insight_top, text_w, insight_height)

    # ---- Page 5: Owner Distribution ----
    s5 = _duplicate_slide(prs, prs.slides[1])
    owner_names = list(metrics['owner_dist'].keys())[:8]
    owner_vals = list(metrics['owner_dist'].values())[:8]
    top_owner = owner_names[0] if owner_names else ''
    second_owner = owner_names[1] if len(owner_names) > 1 else ''
    owner_title = '负责人订单分布'
    if top_owner and second_owner:
        owner_title = f'负责人订单分布：{top_owner}、{second_owner}领跑'

    _replace_all_placeholders(s5, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': owner_title,
        '{source}': source,
        '{period}': '5/8',
        '{page_num}': '',
    })
    if owner_names:
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_horizontal_bar_chart(s5, owner_names, owner_vals,
                                 Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                                 series_name='订单笔数', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='订单笔数')
        # Deep insight: owner distribution analysis
        prev_owner_dist = prev_metrics.get('owner_dist', {}) if prev_metrics else {}
        insight_items = generate_deep_insights('daily', 'owner', metrics,
                                               prev_owner_dist=prev_owner_dist)
        _add_structured_insight(s5, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # ---- Page 6: Country TOP8 ----
    s6 = _duplicate_slide(prs, prs.slides[1])
    countries = list(metrics['country_top8'].keys())[:8]
    country_vals = list(metrics['country_top8'].values())[:8]
    top_country = countries[0] if countries else ''
    country_title = '目的国家TOP8'
    if top_country:
        country_title = f'目的国家TOP8：{top_country}订单量领先'

    _replace_all_placeholders(s6, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': country_title,
        '{source}': source,
        '{period}': '6/8',
        '{page_num}': '',
    })
    if countries:
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_horizontal_bar_chart(s6, countries, country_vals,
                                 Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                                 series_name='订单量(台)', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='订单量（台）')
        # Deep insight: country top8 analysis
        insight_items = generate_deep_insights('daily', 'country', metrics)
        _add_structured_insight(s6, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # ---- Page 7: Support Analysis ----
    s7 = _duplicate_slide(prs, prs.slides[1])
    alerts = metrics['alerts']
    alert_title = '异常告警'
    if alerts:
        severe = [a for a in alerts if a.get('level') == '严重']
        if severe:
            alert_title = f'异常告警：{severe[0]["title"]}'
        else:
            alert_title = f'异常告警：{alerts[0]["title"]}'

    _replace_all_placeholders(s7, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': alert_title,
        '{source}': source,
        '{period}': '7/8',
        '{page_num}': '',
    })

    # Unified left-chart + right-insight layout
    sc = metrics.get('support_categories', {})
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)

    if sc:
        sc_names = list(sc.keys())
        sc_vals = list(sc.values())
        add_doughnut_chart(s7, sc_names, sc_vals,
                           Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                           show_legend=True, show_data_labels=True, show_percent=True,
                           ring_ratio=0.6)

    # Deep insight: alerts & support analysis
    insight_items = generate_deep_insights('daily', 'alert', metrics)
    _add_structured_insight(s7, insight_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    # ---- Page 8: Key Points ----
    s8 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s8, {
        '{report_title}': '海外订单数据日报',
        '{date}': date_str,
        '{page_title}': '明日工作重点',
        '{source}': source,
        '{period}': '8/8',
        '{page_num}': '',
    })

    # Left chart: overdue orders horizontal bar (or support categories fallback)
    overdue = metrics.get('overdue_orders', [])
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)

    if overdue:
        o_countries = [o['country'] for o in overdue[:8]]
        o_days = [o['days'] for o in overdue[:8]]
        add_horizontal_bar_chart(s8, o_countries, o_days,
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='超期天数', color=C_ORANGE, reverse_order=True,
                                 value_axis_title='天数')
    elif metrics.get('support_categories'):
        sc = metrics['support_categories']
        add_horizontal_bar_chart(s8, list(sc.keys()), list(sc.values()),
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='需求数', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='数量')

    # Deep insight: tomorrow's action items
    action_items = generate_deep_insights('daily', 'action', metrics)
    _add_structured_insight(s8, action_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    for slide in prs.slides:
        _ensure_word_wrap_all(slide)
    _delete_template_slides(prs)
    prs.save(output_path)
    print(f"Daily report saved: {output_path}")





# ==============================================================================
# WEEKLY REPORT
# ==============================================================================

def build_weekly_report(data_file: str, year: int, week: int, output_path: str,
                        department='海外事业部', source='海外订单日报系统'):
    master_path = get_master_template('weekly')
    prs = Presentation(master_path)
    content_top = _detect_content_top(prs.slides[1])

    df, prev_df = load_weekly(data_file, year, week)
    metrics = calc_weekly_metrics(df, prev_df)

    period_str = f"{year}年第{week}周"
    date_range_str = f"{df['_data_date'].min().strftime('%m/%d')} - {df['_data_date'].max().strftime('%m/%d')}"

    # Page 1: Cover
    slide = _duplicate_slide(prs, prs.slides[0])
    _replace_all_placeholders(slide, {
        '{report_title}': '海外订单数据周报',
        '{report_type}': 'Weekly Overseas Order Data Report',
        '{date}': period_str,
        '{department}': department,
        '{period}': date_range_str,
        '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'),
    })
    _add_footer_if_missing(slide, f'数据来源：{source} | 1/9')
    # Fix {kpi4_label} {kpi4_value} with actual metric (下月预测交付)
    cover_kpis = [
        ('跟踪订单笔数', f"{metrics['tracking_orders']:,}", '笔',
         _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0))),
        ('订单总数量', f"{metrics['total_qty']:,}", '台',
         _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0))),
        ('覆盖目的国', f"{metrics['countries']}", '个', '全球布局持续深化'),
        ('下月预测交付', f"{metrics['forecast_next']:,}", '台',
         _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0))),
    ]
    for i, (lbl, val, unit, chg) in enumerate(cover_kpis, 1):
        _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl)
        _replace_placeholder(slide, f'{{kpi{i}_value}}', str(val))

    nav_labels = ['周汇总', '趋势图', '环比分析', '区域排行', '问题建议', '下周计划']

    # Page 2: Weekly Summary (KPI cards)
    s2 = _duplicate_slide(prs, prs.slides[1])
    t_chg = _pct_val(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0))
    q_chg = _pct_val(metrics['total_qty'], metrics.get('prev_total_qty', 0))
    t_chg_str = _format_pct(t_chg)
    q_chg_str = _format_pct(q_chg)
    _replace_all_placeholders(s2, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': f"周汇总：跟踪订单环比{t_chg_str}，订单总量增长{q_chg_str}",
        '{source}': source,
        '{period}': '2/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s2, nav_labels, active_index=0, slide_width=prs.slide_width)
    kpis = [
        {'label': '跟踪订单笔数', 'value': f"{metrics['tracking_orders']:,}", 'unit': '笔',
         'change': f'{t_chg_str} vs W1 ({metrics.get("prev_tracking_orders", 0)}笔)' if t_chg is not None else ('新增' if metrics['tracking_orders'] > 0 else '—'),
         'sub': f'日均{metrics["avg_daily_orders"]:.0f}笔'},
        {'label': '订单总数量', 'value': f"{metrics['total_qty']:,}", 'unit': '台',
         'change': f'{q_chg_str} vs W1 ({metrics.get("prev_total_qty", 0):,})' if q_chg is not None else ('新增' if metrics['total_qty'] > 0 else '—'),
         'sub': f'日均{metrics["avg_daily_qty"]:.0f}台'},
        {'label': '已发运订单', 'value': metrics['shipped_orders'], 'unit': '笔',
         'change': _pct_str(metrics['shipped_orders'], metrics.get('prev_shipped_orders', 0)),
         'sub': '环比大幅提升'},
        {'label': '覆盖目的国', 'value': metrics['countries'], 'unit': '个',
         'change': '持平', 'sub': '全球布局持续深化'},
        {'label': '进度更新订单', 'value': metrics['updated_orders'], 'unit': '笔',
         'change': _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0)),
         'sub': '推进效率提升'},
        {'label': '下月预测交付', 'value': f"{metrics['forecast_next']:,}", 'unit': '台',
         'change': _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0)),
         'sub': '交付预期大幅上调'},
    ]
    _add_kpi_cards(s2, kpis, start_y=Emu(content_top))

    # Page 3: 7-Day Trend
    s3 = _duplicate_slide(prs, prs.slides[1])
    trend = metrics.get('daily_trend', {})
    trend_title = '7日趋势：订单总量稳步上升'
    if trend:
        dates = list(trend.keys())
        vals = list(trend.values())
        if vals:
            peak = max(vals)
            peak_date = dates[vals.index(peak)]
            if vals[-1] >= vals[0]:
                trend_title = f'7日趋势：订单总量稳步上升，峰值出现在{peak_date}'
            else:
                trend_title = f'7日趋势：订单量有所波动，峰值出现在{peak_date}'
    _replace_all_placeholders(s3, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': trend_title,
        '{source}': source,
        '{period}': '3/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s3, nav_labels, active_index=1, slide_width=prs.slide_width)
    trend = metrics.get('daily_trend', {})
    if trend:
        dates = list(trend.keys())
        vals = list(trend.values())
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_line_chart(s3, dates, vals,
                       Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                       series_name='订单量', color=C_ACCENT,
                       category_axis_title='日期（MM/DD）', value_axis_title='订单数')
        peak = max(vals) if vals else 0
        peak_date = dates[vals.index(peak)] if vals else ''
        avg = sum(vals) // len(vals) if vals else 0
        prev_avg = metrics.get('prev_avg_daily_orders', 0)
        above_days = metrics.get('days_above_prev_avg', 0)
        total_days = len(vals)

        # Deep insight: weekly trend analysis
        insight_items = generate_deep_insights('weekly', 'weekly_trend', metrics,
                                               trend_dates=dates, trend_vals=vals)
        _add_structured_insight(s3, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 4: WoW Analysis
    s4 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s4, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': '环比分析：各阶段全面增长，已发运环节增幅最大',
        '{source}': source,
        '{period}': '4/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s4, nav_labels, active_index=2, slide_width=prs.slide_width)
    sw_data = metrics.get('status_wow', {})
    if sw_data:
        names = list(sw_data.keys())
        current_vals = [v['current'] for v in sw_data.values()]
        previous_vals = [v['previous'] for v in sw_data.values()]
        # Replace None with 0 for chart data to avoid crashes
        changes = [v['change_pct'] if v['change_pct'] is not None else 0 for v in sw_data.values()]
        
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        
        # Grouped bar chart: 本期 vs 上期
        add_grouped_bar_chart(s4, names,
                              [('本期', current_vals), ('上期', previous_vals)],
                              Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                              colors=[C_ACCENT, C_SECONDARY],
                              show_legend=True, show_data_labels=True,
                              category_axis_title='订单状态', value_axis_title='订单数')
        
        # WoW增幅 labels below chart
        wow_label_items = []
        for k, v in sw_data.items():
            pct_str = _format_pct(v['change_pct'])
            arrow = '↑' if v['change_pct'] is not None and v['change_pct'] >= 0 else '↓' if v['change_pct'] is not None else ''
            wow_label_items.append(f'{k} {pct_str}{arrow}')
        _add_text_block(s4, '环比增幅', ' | '.join(wow_label_items),
                        Emu(762000), Emu(6604000), chart_w, Emu(609600),
                        title_size=Pt(11), body_size=Pt(10))

        # Deep insight: WoW analysis
        insight_items = generate_deep_insights('weekly', 'weekly_wow', metrics)
        _add_structured_insight(s4, insight_items,
                                text_left, Emu(content_top), text_w, Emu(4826000))

    # Page 5: Region Distribution
    s5 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s5, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': '区域分布：中东增速领跑，欧洲为唯一下滑区域',
        '{source}': source,
        '{period}': '5/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s5, nav_labels, active_index=3, slide_width=prs.slide_width)
    rdist = metrics.get('region_dist', {})
    if rdist:
        regions = list(rdist.keys())
        qtys = [v['qty'] for v in rdist.values()]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_doughnut_chart(s5, regions, qtys,
                           Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                           show_legend=True, show_data_labels=True, show_percent=True,
                           ring_ratio=0.6)
        # Deep insight: regional analysis
        insight_items = generate_deep_insights('weekly', 'weekly_region', metrics)
        _add_structured_insight(s5, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 6: TOP Countries
    s6 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s6, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': 'TOP国家排行：科威特344台居首，TOP15贡献70%+总量',
        '{source}': source,
        '{period}': '6/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s6, nav_labels, active_index=3, slide_width=prs.slide_width)
    topc_change = metrics.get('top_countries_change', {})
    if topc_change:
        countries = list(topc_change.keys())[:10]
        vals = [v['qty'] for v in list(topc_change.values())[:10]]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_horizontal_bar_chart(s6, countries, vals,
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='订单量(台)', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='订单量（台）')
        # Deep insight: top countries analysis
        insight_items = generate_deep_insights('weekly', 'weekly_country', metrics)
        _add_structured_insight(s6, insight_items,
                                text_left, Emu(content_top), text_w, Emu(4826000))

    # Page 7: Team Tracking
    s7 = _duplicate_slide(prs, prs.slides[1])
    team = metrics.get('team', {})
    n_members = len(team.get('owners', {})) if team else 0
    n_growers = sum(1 for v in metrics.get('team_wow', {}).values() if v.get('change', 0) > 0)
    team_title = f'团队追踪：{n_members}人团队全面覆盖，{n_growers}人实现增长'
    _replace_all_placeholders(s7, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': team_title,
        '{source}': source,
        '{period}': '7/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s7, nav_labels, active_index=4, slide_width=prs.slide_width)
    team = metrics.get('team', {})
    owners = team.get('owners', {})
    if owners:
        names = list(owners.keys())
        vals = list(owners.values())
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_horizontal_bar_chart(s7, names, vals,
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='订单笔数', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='订单笔数')
        # Deep insight: team tracking
        insight_items = generate_deep_insights('weekly', 'weekly_team', metrics)
        _add_structured_insight(s7, insight_items,
                                text_left, Emu(content_top), text_w, Emu(4826000))

    # Page 8: Issues
    s8 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s8, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': '问题识别：系统数据匹配问题为本周首要障碍',
        '{source}': source,
        '{period}': '8/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s8, nav_labels, active_index=4, slide_width=prs.slide_width)
    # Left chart: support request categories
    sc = metrics.get('support_categories', {})
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)
    if sc:
        add_horizontal_bar_chart(s8, list(sc.keys()), list(sc.values()),
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='需求数', color=C_ORANGE, reverse_order=True,
                                 value_axis_title='数量')
    # Right: deep insight
    insight_items = generate_deep_insights('weekly', 'weekly_issue', metrics)
    _add_structured_insight(s8, insight_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 9: Next Week Plan
    s9 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s9, {
        '{report_title}': '海外订单数据周报',
        '{date}': period_str,
        '{page_title}': '下周计划：聚焦发运交付，冲刺交付目标',
        '{source}': source,
        '{period}': '9/9',
        '{page_num}': '',
    })
    _add_nav_tabs(s9, nav_labels, active_index=5, slide_width=prs.slide_width)

    # Left chart: goals as column chart
    goals = metrics.get('next_week_goals', [])
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)
    if goals:
        goal_names = [g['title'].split('：')[0] for g in goals[:4]]
        goal_nums = [g.get('number', 0) for g in goals[:4]]
        add_column_chart(s9, goal_names, goal_nums,
                         Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                         series_name='目标数量', color=C_ACCENT,
                         category_axis_title='目标', value_axis_title='数量')

    # Deep insight: next week plan
    insight_items = generate_deep_insights('weekly', 'weekly_plan', metrics)
    _add_structured_insight(s9, insight_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    for slide in prs.slides:
        _ensure_word_wrap_all(slide)
    _delete_template_slides(prs)
    prs.save(output_path)
    print(f"Weekly report saved: {output_path}")






# ==============================================================================
# MONTHLY REPORT
# ==============================================================================

def build_monthly_report(data_file: str, year: int, month: int, output_path: str,
                         department='海外事业部', source='海外订单日报系统'):
    master_path = get_master_template('monthly')
    prs = Presentation(master_path)
    content_top = _detect_content_top(prs.slides[1])

    df, prev_df, yoy_df = load_monthly(data_file, year, month)
    metrics = calc_monthly_metrics(df, prev_df, yoy_df)

    period_str = f"{year}年{month}月"

    # Page 1: Cover
    slide = _duplicate_slide(prs, prs.slides[0])
    _replace_all_placeholders(slide, {
        '{report_title}': '海外订单月度数据报告',
        '{report_type}': 'Monthly Overseas Order Data Report',
        '{date}': period_str,
        '{department}': department,
        '{period}': period_str,
        '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'),
    })
    _add_footer_if_missing(slide, f'数据来源：{source} | 1/11')
    cover_kpis = [
        ('合同总数', f"{metrics['total_contracts']:,}"),
        ('车辆总数', f"{metrics['total_qty']:,}"),
        ('目的国家', f"{metrics['countries']}+"),
        ('负责团队', '9人'),
    ]
    for i, (lbl, val) in enumerate(cover_kpis, 1):
        _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl)
        _replace_placeholder(slide, f'{{kpi{i}_value}}', val)

    # Page 2: TOC
    s_toc = _duplicate_slide(prs, prs.slides[2])
    _add_footer_if_missing(s_toc, f'数据来源：{source} | 2/11')
    _replace_all_placeholders(s_toc, {
        '{chapter1_title}': '月度总览',
        '{chapter1_desc}': '核心KPI一览：合同总数、车辆规模、新签与发运表现',
        '{chapter2_title}': '订单状态分析',
        '{chapter2_desc}': '订单阶段漏斗：从合同拟定到发运的全流程追踪',
        '{chapter3_title}': '区域与趋势',
        '{chapter3_desc}': '区域分布、国家排名与30日追踪趋势',
        '{chapter4_title}': '团队与展望',
        '{chapter4_desc}': '团队绩效、支持需求与下月工作规划',
    })

    nav_labels = ['月度总览', '订单状态', '区域趋势', '团队展望']

    # Page 3: Monthly Overview
    s3 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s3, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': f"{month}月核心指标：累计追踪{metrics['total_contracts']:,}单，覆盖{metrics['total_qty']:,}台车辆",
        '{source}': source,
        '{period}': '3/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s3, nav_labels, active_index=0, slide_width=prs.slide_width)
    kpis = [
        {'label': '合同总数', 'value': f"{metrics['total_contracts']:,}", 'unit': '单',
         'change': f'日均{metrics["avg_daily_orders"]:.0f}单', 'sub': '覆盖全月'},
        {'label': '车辆总数', 'value': f"{metrics['total_qty']:,}", 'unit': '台',
         'change': f"{metrics['shipped_qty']:,}台已发运", 'sub': '交付持续推进'},
        {'label': f'{month}月新签', 'value': metrics['new_contracts'], 'unit': '单',
         'change': f"{metrics['new_qty']:,}台", 'sub': '新签势头良好'},
        {'label': '已发运', 'value': metrics['shipped_orders'], 'unit': '单',
         'change': f"{metrics['shipped_qty']:,}台", 'sub': '交付稳步推进'},
        {'label': '目的国', 'value': f"{metrics['countries']}+", 'unit': '个',
         'change': '全球市场布局', 'sub': '持续深化'},
        {'label': '待处理需求', 'value': metrics['support_count'], 'unit': '单',
         'change': f"{metrics['support_pct']}%订单涉及", 'sub': '需跨部门协调'},
    ]
    _add_kpi_cards(s3, kpis, start_y=Emu(content_top))

    # Monthly overview: KPI cards only, no bottom insight text to avoid overlap with cards

    # Page 4: Status Funnel
    s4 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s4, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': '订单阶段漏斗：合同拟定与生产中订单占主导地位',
        '{source}': source,
        '{period}': '4/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s4, nav_labels, active_index=1, slide_width=prs.slide_width)
    funnel = metrics.get('status_funnel', {})
    if funnel:
        names = list(funnel.keys())
        orders = [v['orders'] for v in funnel.values()]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_funnel_chart(s4, names, orders,
                         Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                         show_data_labels=True, show_percent=True)
        # Deep insight: monthly funnel
        insight_items = generate_deep_insights('monthly', 'monthly_funnel', metrics)
        _add_structured_insight(s4, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 5: Region Distribution
    s5 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s5, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': '区域分布：拉美、东南亚、非洲三大市场并驾齐驱',
        '{source}': source,
        '{period}': '5/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s5, nav_labels, active_index=2, slide_width=prs.slide_width)
    rdist = metrics.get('region_dist', {})
    if rdist:
        regions = list(rdist.keys())
        qtys = [v['qty'] for v in rdist.values()]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_doughnut_chart(s5, regions, qtys,
                           Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                           show_legend=True, show_data_labels=True, show_percent=True,
                           ring_ratio=0.6)
        # Deep insight: monthly region
        insight_items = generate_deep_insights('monthly', 'monthly_region', metrics)
        _add_structured_insight(s5, insight_items,
                                text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 6: TOP10 Countries
    s6 = _duplicate_slide(prs, prs.slides[1])
    top10 = metrics.get('top_countries', {})
    top_country = list(top10.keys())[0] if top10 else ''
    top_qty = list(top10.values())[0]['qty'] if top10 else 0
    top10_title = f'Top 10目的国：{top_country}{top_qty:,}台领跑' if top_country else 'Top 10目的国：重点市场领跑'
    _replace_all_placeholders(s6, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': top10_title,
        '{source}': source,
        '{period}': '6/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s6, nav_labels, active_index=2, slide_width=prs.slide_width)
    top10 = metrics.get('top_countries', {})
    if top10:
        countries = list(top10.keys())
        qtys = [v['qty'] for v in top10.values()]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        add_horizontal_bar_chart(s6, countries, qtys,
                                 Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                                 series_name='订单量(台)', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='订单量（台）')
        # Deep insight: monthly top countries
        insight_items = generate_deep_insights('monthly', 'monthly_country', metrics)
        _add_structured_insight(s6, insight_items,
                                text_left, Emu(content_top), text_w, Emu(4826000))

    # Page 7: 30-Day Trend
    s7 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s7, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': '30日追踪趋势：下旬订单活跃度显著提升',
        '{source}': source,
        '{period}': '7/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s7, nav_labels, active_index=2, slide_width=prs.slide_width)
    trend = metrics.get('daily_trend', {})
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)
    if trend:
        dates = list(trend.keys())
        vals = list(trend.values())
        add_line_chart(s7, dates, vals,
                       Emu(762000), Emu(content_top), chart_w, Emu(5334000),
                       series_name='订单量', color=C_ACCENT,
                       category_axis_title='日期（MM/DD）', value_axis_title='订单数')
    tbp = metrics.get('trend_by_period', {})
    late_change = tbp.get('late_change_pct', 0)
    late_change_str = _format_pct(late_change, with_sign=True) if late_change is not None else '—'

    # Deep insight: monthly trend
    insight_items = generate_deep_insights('monthly', 'monthly_trend', metrics)
    _add_structured_insight(s7, insight_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 8: Team Performance
    s8 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s8, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': '团队绩效：9位负责人均匀分布，多人领跑',
        '{source}': source,
        '{period}': '8/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s8, nav_labels, active_index=3, slide_width=prs.slide_width)
    team = metrics.get('team', {})
    if team:
        names = list(team.keys())
        orders = [v['orders'] for v in team.values()]
        qtys = [v['qty'] for v in team.values()]
        chart_w = Emu(int(prs.slide_width) * 0.55)
        text_left = Emu(int(prs.slide_width) * 0.62)
        text_w = Emu(int(prs.slide_width) * 0.36)
        # Horizontal bar chart for orders + secondary series for qty
        add_grouped_bar_chart(s8, names,
                              [('订单数', orders), ('车辆数', qtys)],
                              Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                              colors=[C_ACCENT, C_ORANGE],
                              show_legend=True, show_data_labels=True,
                              category_axis_title='负责人', value_axis_title='数量')
        # Deep insight: monthly team
        insight_items = generate_deep_insights('monthly', 'monthly_team', metrics)
        _add_structured_insight(s8, insight_items,
                                text_left, Emu(content_top), text_w, Emu(4826000))

    # Page 9: Support Analysis
    s9 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s9, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': '支持需求分析：财务、售后、法务为三大核心诉求',
        '{source}': source,
        '{period}': '9/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s9, nav_labels, active_index=3, slide_width=prs.slide_width)
    sc = metrics.get('support_categories', {})
    if sc:
        cats = list(sc.keys())
        vals = list(sc.values())
        add_horizontal_bar_chart(s9, cats, vals,
                                 Emu(762000), Emu(content_top), Emu(8636000), Emu(5334000),
                                 series_name='需求数', color=C_ACCENT, reverse_order=True,
                                 value_axis_title='需求数')
        top_cat = max(sc.items(), key=lambda x: x[1])
        # Deep insight: monthly support
        insight_items = generate_deep_insights('monthly', 'monthly_support', metrics)
        _add_structured_insight(s9, insight_items,
                                Emu(9779000), Emu(content_top), Emu(5715000), Emu(5334000))

    # Page 10: Next Month Plan
    s10 = _duplicate_slide(prs, prs.slides[1])
    _replace_all_placeholders(s10, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{page_title}': f'{month+1 if month < 12 else 1}月展望：预测交付{metrics["forecast_next"]}台，重点关注交付转化',
        '{source}': source,
        '{period}': '10/11',
        '{page_num}': '',
    })
    _add_nav_tabs(s10, nav_labels, active_index=3, slide_width=prs.slide_width)

    # Left chart: next month goals as column chart
    goals = metrics.get('next_month_goals', [])
    chart_w = Emu(int(prs.slide_width) * 0.55)
    text_left = Emu(int(prs.slide_width) * 0.62)
    text_w = Emu(int(prs.slide_width) * 0.36)
    if goals:
        goal_names = [g['title'].split('：')[0] for g in goals[:5]]
        goal_nums = [g.get('number', 0) for g in goals[:5]]
        add_column_chart(s10, goal_names, goal_nums,
                         Emu(762000), Emu(content_top), chart_w, Emu(4826000),
                         series_name='目标数量', color=C_ACCENT,
                         category_axis_title='目标', value_axis_title='数量')

    # Deep insight: monthly plan
    insight_items = generate_deep_insights('monthly', 'monthly_plan', metrics)
    _add_structured_insight(s10, insight_items,
                            text_left, Emu(content_top), text_w, Emu(5334000))

    # Page 11: End
    s_end = _duplicate_slide(prs, prs.slides[3])
    _add_footer_if_missing(s_end, f'数据来源：{source} | 11/11')
    _replace_all_placeholders(s_end, {
        '{report_title}': '海外订单月度数据报告',
        '{date}': period_str,
        '{department}': department,
    })
    end_kpis = [
        ('合同总数', f"{metrics['total_contracts']:,}"),
        ('车辆总数', f"{metrics['total_qty']:,}"),
        ('目的国家', f"{metrics['countries']}+"),
        ('团队', '9人'),
    ]
    for i, (lbl, val) in enumerate(end_kpis, 1):
        _replace_placeholder(s_end, f'{{kpi{i}_label}}', lbl)
        _replace_placeholder(s_end, f'{{kpi{i}_value}}', val)

    for slide in prs.slides:
        _ensure_word_wrap_all(slide)
    _delete_template_slides(prs)
    prs.save(output_path)
    print(f"Monthly report saved: {output_path}")


# ==============================================================================
# CLI
# ==============================================================================

if __name__ == '__main__':
    import sys
    if len(sys.argv) >= 4:
        cmd = sys.argv[1]
        data_file = sys.argv[2]
        output = sys.argv[3]
        if cmd == 'daily':
            d = datetime.strptime(sys.argv[4], '%Y-%m-%d')
            build_daily_report(data_file, d, output)
        elif cmd == 'weekly':
            build_weekly_report(data_file, int(sys.argv[4]), int(sys.argv[5]), output)
        elif cmd == 'monthly':
            build_monthly_report(data_file, int(sys.argv[4]), int(sys.argv[5]), output)