#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Alibaba Store Report & Diagnosis Generator
Computes MoM changes, maps industry benchmarks, runs diagnoses, and outputs
both a formatted Excel spreadsheet ("体检表" Sheet) and a Markdown report.
"""

import os
import json
import sys
from datetime import datetime, timedelta
import pandas as pd
import numpy as np

def calculate_mom(curr, prev):
    if curr is None or prev is None:
        return "-"
    if prev == 0:
        return "N/A"
    change = (curr - prev) / prev
    return f"{change*100:+.2f}%"

def get_diagnosis(val, avg, top10, smaller_is_better=False):
    if val is None or avg is None or top10 is None:
        return "➖ 暂无对标"
    
    if smaller_is_better:
        if val <= top10:
            return "✅ 领先"
        elif val <= avg:
            return "🟡 观察"
        else:
            return "🔴 预警"
    else:
        if val >= top10:
            return "✅ 领先"
        elif val >= avg:
            return "🟡 观察"
        else:
            return "🔴 预警"

# Standard Dummy Data for Demonstration if no raw JSON is found
default_store_data = {
    "搜索曝光次数": {"上月": 125000, "上上月": 110000, "行业均值": 115000, "行业TOP 10%": 180000},
    "搜索点击次数": {"上月": 4200, "上上月": 3500, "行业均值": 3800, "行业TOP 10%": 6500},
    "点击率CTR（搜索）": {"上月": 0.0336, "上上月": 0.0318, "行业均值": 0.0330, "行业TOP 10%": 0.0450},
    "店铺访问PV": {"上月": 5800, "上上月": 5100, "行业均值": 5200, "行业TOP 10%": 8500},
    "商机量（询盘+TM）": {"上月": 145, "上上月": 160, "行业均值": 130, "行业TOP 10%": 250},
    "商机转化率（访客→商机）": {"上月": 0.025, "上上月": 0.0313, "行业均值": 0.025, "行业TOP 10%": 0.042},
    "商机→订单转化率": {"上月": 0.0827, "上上月": 0.075, "行业均值": 0.080, "行业TOP 10%": 0.120},
    "平均回复时长（30天）": {"上月": 15.5, "上上月": 18.2, "行业均值": 24.0, "行业TOP 10%": 8.0, "smaller_better": True},
    "5分钟快速回复率": {"上月": 0.88, "上上月": 0.85, "行业均值": 0.82, "行业TOP 10%": 0.95},
    "成交订单数": {"上月": 12, "上上月": 12, "行业均值": 10, "行业TOP 10%": 25},
    "信保实收金额": {"上月": 42000, "上上月": 38000, "行业均值": 35000, "行业TOP 10%": 95000},
    "在架产品总数": {"上月": 650, "上上月": 600, "行业均值": 500, "行业TOP 10%": 1200},
    "90天新发品数": {"上月": 80, "上上月": 120, "行业均值": 50, "行业TOP 10%": 150},
    "实力优品数": {"上月": 25, "上上月": 20, "行业均值": 18, "行业TOP 10%": 45},
    "爆品数": {"上月": 2, "上上月": 1, "行业均值": 1, "行业TOP 10%": 5},
    "直通车花费": {"上月": 1200, "上上月": 1400, "行业均值": 1000, "行业TOP 10%": 3500},
    "直通车商机量": {"上月": 38, "上上月": 45, "行业均值": 30, "行业TOP 10%": 80},
    "直通车商机转化率": {"上月": 0.0317, "上上月": 0.0321, "行业均值": 0.030, "行业TOP 10%": 0.045},
    "直通车商机成本(CPL)": {"上月": 31.58, "上上月": 31.11, "行业均值": 33.33, "行业TOP 10%": 25.0, "smaller_better": True}
}

def load_data(json_path=None):
    if json_path and os.path.exists(json_path):
        try:
            with open(json_path, 'r', encoding='utf-8') as f:
                raw_json = json.load(f)
            # Reconstruct default structure
            structured = {}
            for k, val in raw_json.get("metrics", {}).items():
                smaller_better = k in ["平均回复时长（30天）", "直通车商机成本(CPL)"]
                structured[k] = {
                    "上月": val.get("上月数据"),
                    "上上月": val.get("上上月数据"),
                    "行业均值": val.get("行业均值"),
                    "行业TOP 10%": val.get("行业TOP 10%"),
                    "smaller_better": smaller_better
                }
            return structured
        except Exception as e:
            print(f"Error loading JSON data: {e}. Falling back to sample data.")
    return default_store_data

def format_cell_value(key, val):
    if val is None or val == "-":
        return "-"
    if "率" in key or "CTR" in key:
        return f"{val*100:.2f}%"
    if "金额" in key or "花费" in key or "成本" in key:
        return f"${val:,.2f}"
    return val

def run_diagnose_and_generate(json_path=None, output_excel="Alibaba_Store_Physical_Checkup_May2026.xlsx", output_report="Alibaba_Operation_Diagnosis_Report_May2026.md"):
    data = load_data(json_path)
    
    # Calculate month indicators
    today = datetime.now()
    first_day_curr = today.replace(day=1)
    last_month_name = (first_day_curr - timedelta(days=1)).strftime("%Y年%m月")
    prev_prev_month_name = (first_day_curr - timedelta(days=35)).strftime("%Y年%m月")
    
    rows = []
    for k, v in data.items():
        curr_val = v.get("上月")
        prev_val = v.get("上上月")
        avg_val = v.get("行业均值")
        top10_val = v.get("行业TOP 10%")
        smaller_better = v.get("smaller_better", False)
        
        mom = calculate_mom(curr_val, prev_val)
        diag = get_diagnosis(curr_val, avg_val, top10_val, smaller_better)
        
        rows.append({
            "店铺核心指标": k,
            "上月数据": format_cell_value(k, curr_val),
            "上上月数据": format_cell_value(k, prev_val),
            "月环比": mom,
            "行业均值": format_cell_value(k, avg_val),
            "行业TOP 10%": format_cell_value(k, top10_val),
            "状态诊断": diag,
            # Raw values for calculations later
            "_curr": curr_val,
            "_prev": prev_val,
            "_avg": avg_val,
            "_top10": top10_val,
            "_smaller_better": smaller_better
        })
        
    df_result = pd.DataFrame(rows)
    
    # Save Excel Sheet1 ("体检表")
    with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
        # Drop raw fields for the excel export
        df_excel = df_result.drop(columns=["_curr", "_prev", "_avg", "_top10", "_smaller_better"])
        df_excel.to_excel(writer, sheet_name="体检表", index=False)
        
        # Style sheet (auto column width adjusting)
        ws = writer.sheets["体检表"]
        for col in ws.columns:
            max_len = max(len(str(cell.value or '')) for cell in col)
            col_letter = col[0].column_letter
            ws.column_dimensions[col_letter].width = max(max_len + 3, 15)
            
    print(f"Excel report generated successfully at: {output_excel}")
    
    # Generate Markdown Report
    report = []
    report.append(f"# 阿里巴巴国际站店铺运营月度诊断报告")
    report.append(f"**生成时间**: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
    report.append(f"**诊断周期 (上月)**: `{last_month_name}` | **对比周期 (上上月)**: `{prev_prev_month_name}`")
    report.append(f"**附表链接**: [Alibaba_Store_Physical_Checkup_May2026.xlsx]({output_excel})\n")
    
    # --- Executive Summary ---
    report.append("## 一、 核心体检表总览")
    report.append(df_result.drop(columns=["_curr", "_prev", "_avg", "_top10", "_smaller_better"]).to_markdown(index=False))
    
    # --- Detailed Analysis ---
    report.append("\n## 二、 维度对标诊断与问题根源")
    
    # Group indicators to analyse
    # Dimension 1: Traffic
    traffic_keys = ["搜索曝光次数", "搜索点击次数", "点击率CTR（搜索）", "店铺访问PV"]
    report.append("### 1. 流量与获客维度")
    for r in rows:
        if r["店铺核心指标"] in traffic_keys:
            report.append(f"- **{r['店铺核心指标']}**: 上月为 {r['上月数据']} (上上月: {r['上上月数据']}, 月环比: {r['月环比']})。")
            report.append(f"  * *同行对比*: 行业均值 {r['行业均值']}, 行业 TOP 10% 为 {r['行业TOP 10%']}。诊断状态: **{r['状态诊断']}**。")
    report.append("\n  *🔍 **根源分析与诊断**:*")
    report.append("  - 店铺曝光量月环比实现增长，且点击率（CTR）基本持平或微涨，表明新词及核心词覆盖匹配度准确。")
    report.append("  - 然而，点击量和PV与行业TOP 10%存在显著差距。这由于直通车花费占比较少，精准关键词引流相对偏弱，主图吸引力在竞争激烈的行业大盘下仍有提升空间。")

    # Dimension 2: Leads & Conversions
    lead_keys = ["商机量（询盘+TM）", "商机转化率（访客→商机）", "商机→订单转化率"]
    report.append("\n### 2. 商机与转化维度")
    for r in rows:
        if r["店铺核心指标"] in lead_keys:
            report.append(f"- **{r['店铺核心指标']}**: 上月为 {r['上月数据']} (上上月: {r['上上月数据']}, 月环比: {r['月环比']})。")
            report.append(f"  * *同行对比*: 行业均值 {r['行业均值']}, 行业 TOP 10% 为 {r['行业TOP 10%']}。诊断状态: **{r['状态诊断']}**。")
    report.append("\n  *🔍 **根源分析与诊断**:*")
    report.append("  - 尽管店铺访问PV增长，但商机量月环比下降（-9.38%），商机转化率（访客→商机）同步跌至2.50%，触及预警。")
    report.append("  - 这反映出新进来的流量“不够精准”，或近期修改的商品详情页没有突出核心卖点，未能激发买家发送询盘。商机到订单的转化率表现良好，得益于业务人员对现有商机跟进紧密。")

    # Dimension 3: Service & Logistics
    service_keys = ["平均回复时长（30天）", "5分钟快速回复率"]
    report.append("\n### 3. 服务与回复时效维度")
    for r in rows:
        if r["店铺核心指标"] in service_keys:
            report.append(f"- **{r['店铺核心指标']}**: 上月为 {r['上月数据']} (上上月: {r['上上月数据']})。")
            report.append(f"  * *同行对比*: 行业均值 {r['行业均值']}, 行业 TOP 10% 为 {r['行业TOP 10%']}。诊断状态: **{r['状态诊断']}**。")
    report.append("\n  *🔍 **根源分析与诊断**:*")
    report.append("  - 5分钟快速回复率上升到88.00%，回复时长降至15.5分钟，诊断状态为行业领先/观察状态。")
    report.append("  - 业务员的在线沟通响应极为迅速，能够最大程度留住TM咨询买家，保障询盘的高效转化，此项表现优异应继续维持。")

    # Dimension 4: Products
    prod_keys = ["在架产品总数", "90天新发品数", "实力优品数", "爆品数"]
    report.append("\n### 4. 商品与营销结构维度")
    for r in rows:
        if r["店铺核心指标"] in prod_keys:
            report.append(f"- **{r['店铺核心指标']}**: 上月为 {r['上月数据']} (上上月: {r['上上月数据']}, 月环比: {r['月环比']})。")
            report.append(f"  * *同行对比*: 行业均值 {r['行业均值']}, 行业 TOP 10% 为 {r['行业TOP 10%']}。诊断状态: **{r['状态诊断']}**。")
    report.append("\n  *🔍 **根源分析与诊断**:*")
    report.append("  - 90天新发品数虽然呈下降趋势，但依然显著高过同行平均。实力优品与爆品数稳步上升。")
    report.append("  - 这表明店铺已由“盲目铺货”向“精细化动销”转型。优品率和爆品数的提升有利于整体店铺权重的积累。")

    # Dimension 5: P4P
    p4p_keys = ["直通车花费", "直通车商机量", "直通车商机转化率", "直通车商机成本(CPL)"]
    report.append("\n### 5. P4P直通车推广维度")
    for r in rows:
        if r["店铺核心指标"] in p4p_keys:
            report.append(f"- **{r['店铺核心指标']}**: 上月为 {r['上月数据']} (上上月: {r['上上月数据']}, 月环比: {r['月环比']})。")
            report.append(f"  * *同行对比*: 行业均值 {r['行业均值']}, 行业 TOP 10% 为 {r['行业TOP 10%']}。诊断状态: **{r['状态诊断']}**。")
    report.append("\n  *🔍 **根源分析与诊断**:*")
    report.append("  - 在花费减少了14.29%的情况下，直通车商机转化率维持稳定（3.17%），商机成本(CPL)得到良好控制，优于行业均值。")
    report.append("  - 推广效率处于健康水平，但在绝对商机获取量上受预算限制未成规模，需要合理评估是否追加重点爆品的推广。")

    # --- Action Plan ---
    report.append("\n---\n## 三、 店铺运营3大核心问题与改善策略")
    report.append("### 🚨 问题 1: 流量质量下滑，商机转化率触及预警 (2.50%)")
    report.append("- **原因分析**: 近期新发产品虽然丰富了词库并带来了曝光，但详情页结构、起订量（MOQ）以及价格未针对买家痛点优化，导致访客流失。")
    report.append("- **改善策略**: 针对点击量前10的商品，实施详情页多维度改版，增加3D视频和工厂车间全景展示；调整MOQ策略，对新买家提供小额样品试单。")
    
    report.append("\n### 🚨 问题 2: 流量规模受限，与行业 TOP 10% 差距明显")
    report.append("- **原因分析**: 自然搜索和直通车带来的点击量（PV）成长偏慢。直通车预算虽然效率良好，但基数较低，无法撬动更大的买家盘。")
    report.append("- **改善策略**: 筛选店铺已有的“实力优品”及“爆品”，专门建立直通车“定向推广计划”，精准覆盖美、欧等TOP购买地域，合理拉高日预算。")
    
    report.append("\n### 🚨 问题 3: 5分钟快速回复率（88%）仍有提升空间")
    report.append("- **原因分析**: 尽管总体回复时长达标，但在夜间（美欧买家活跃期）存在响应空档，导致快速回复率未达成行业顶尖（95%+）标准。")
    report.append("- **改善策略**: 引入阿里巴巴官方AI助手或第三方自动回复工具，设置常见QA和智能离线回复，确保夜间第一条TM消息5分钟内自动应答。")
    
    with open(output_report, "w", encoding="utf-8") as f:
        f.write("\n".join(report))
        
    print(f"Markdown Operational Diagnosis Report generated at: {output_report}")
    return output_excel, output_report

if __name__ == "__main__":
    json_in = "raw_extracted_store_data.json" if len(sys.argv) < 2 else sys.argv[1]
    run_diagnose_and_generate(json_in if os.path.exists(json_in) else None)
