# -*- coding: utf-8 -*-
"""南部大区城市经营看板：数据准备。输出 dashboard_data.json"""
import json

BASE = ".kiro/cache/read-polaris-skill/tmp"
city_m = json.load(open(f"{BASE}/south_city_monthly_v2.json", encoding="utf-8"))
agg_m = json.load(open(f"{BASE}/south_monthly_trend.json", encoding="utf-8"))

MONTHS = ["202605", "202606", "202607", "202608"]
MLABEL = {"202605": "5月", "202606": "6月", "202607": "7月", "202608": "8月MTD"}

def g(r, k):
    v = r.get(k)
    return v if isinstance(v, (int, float)) else None

# ---------- 城市 × 月 索引 ----------
idx = {}
for r in city_m:
    idx[(r["final_city_name"], r["data_date_mounth"])] = r

cities = sorted(set(r["final_city_name"] for r in city_m))
city_type = {}
for r in city_m:
    city_type[r["final_city_name"]] = r["city_type"]

# ---------- 城市表（7月，含6月环比）----------
city_rows = []
for c in cities:
    r7 = idx.get((c, "202607")); r6 = idx.get((c, "202606"))
    if not r7: continue
    def mom(k, ratio=False):
        a, b = g(r7, k), (g(r6, k) if r6 else None)
        if a is None or b in (None, 0): return None
        return (a - b) / b if not ratio else a - b
    city_rows.append({
        "city": c, "type": city_type[c],
        "users": g(r7, "SUM(huandian_hdc_users_ym)"),
        "users_mom": mom("SUM(huandian_hdc_users_ym)"),
        "rev": g(r7, "zongshouru_shuihou_buhanshoumai"),
        "rev_mom": mom("zongshouru_shuihou_buhanshoumai"),
        "gp": g(r7, "huandianyewuyunyingmaoli"),
        "gpm": g(r7, "huandianyewuyunyingmaolilv"),
        "gpm_mom": mom("huandianyewuyunyingmaolilv", ratio=True),
        "gp_user": g(r7, "danyonghuyunyingmaoli"),
        "kedanjia": g(r7, "kedanjia"),
        "kwh_user": g(r7, "danyonghurijunyongdianliang_"),
        "r_dianfei": g(r7, "feilv_dianfei"),
        "r_zujin": g(r7, "feilv_zujin"),
        "r_btrydep": g(r7, "feilv_dianchizhejiu"),
        "r_cabtdep": g(r7, "feilv_dianguizhejiu"),
        "r_renli": g(r7, "feilv_quanzhirenli"),
        "r_jianzhi": g(r7, "feilv_jianzhilaowu"),
        "r_daiyunying": g(r7, "feilv_daiyunyingchengben"),
        "r_yingxiao": g(r7, "feilv_zongyingxiaofei"),
        "r_qita": g(r7, "feilv_qitachengben"),
        "peibi": g(r7, "xiaohachengshiyunyingdianchipeibi"),
        "gekou_user": g(r7, "dangekoufuwuyonghushu_ym"),
        "cabt_num": g(r7, "SUM(operate_cabt_num_ym)"),
    })
city_rows.sort(key=lambda x: -(x["rev"] or 0))

# ---------- 大区汇总卡片（官方聚合）----------
am = {r["data_date_mounth"]: r for r in agg_m}
def agg_card(m):
    r = am.get(m) or {}
    return {
        "rev": g(r, "zongshouru_shuihou_buhanshoumai"),
        "gp": g(r, "huandianyewuyunyingmaoli"),
        "gpm": g(r, "huandianyewuyunyingmaolilv"),
        "users_ym": g(r, "SUM(huandian_hdc_users_ym)"),
        "users_yj": g(r, "huandian_users_hdc_yj"),
        "taonei_ym": g(r, "SUM(taonei_users_ym)"),
        "kedanjia": g(r, "kedanjia"),
        "kedanjia_noka": g(r, "kedanjia_buhanxianxiaka"),
        "kwh_user": g(r, "danyonghurijunyongdianliang_"),
        "hdpinci": g(r, "danyonghurijunhuandianpinci"),
        "peibi": g(r, "xiaohachengshiyunyingdianchipeibi"),
        "gekou_user": g(r, "dangekoufuwuyonghushu_ym"),
        "gp_user": g(r, "danyonghuyunyingmaoli"),
        "cabt_num": g(r, "SUM(operate_cabt_num_ym)"),
        "grid_num": g(r, "SUM(operate_grid_num_ym)"),
        "r_dianfei": g(r, "feilv_dianfei"),
        "r_zujin": g(r, "feilv_zujin"),
        "r_yingxiao": g(r, "feilv_zongyingxiaofei"),
    }
cards = {m: agg_card(m) for m in MONTHS}

# ---------- 趋势序列 ----------
trend = {}
for c in cities:
    trend[c] = {m: {
        "rev": g(idx.get((c, m), {}), "zongshouru_shuihou_buhanshoumai"),
        "gpm": g(idx.get((c, m), {}), "huandianyewuyunyingmaolilv"),
        "users": g(idx.get((c, m), {}), "SUM(huandian_hdc_users_ym)"),
        "kedanjia": g(idx.get((c, m), {}), "kedanjia"),
    } for m in MONTHS}
agg_trend = {m: {
    "rev": cards[m]["rev"], "gpm": cards[m]["gpm"],
    "users": cards[m]["users_ym"], "kedanjia": cards[m]["kedanjia"],
} for m in MONTHS}

out = {"months": MONTHS, "mlabel": MLABEL, "city_rows": city_rows,
       "cards": cards, "trend": trend, "agg_trend": agg_trend}
with open("南部大区城市看板/dashboard_data.json", "w", encoding="utf-8") as f:
    json.dump(out, f, ensure_ascii=False)
print("城市数:", len(city_rows), "| 直营:", sum(1 for c in city_rows if c['type']=='直营'),
      "| 联营:", sum(1 for c in city_rows if c['type']=='联营'))
print("7月合计收入:", round(sum(c['rev'] or 0 for c in city_rows)/1e4), "万")