from __future__ import annotations

import json
from decimal import Decimal
from pathlib import Path

import pymysql
import yaml


def dec(value: object) -> Decimal:
    return Decimal(str(value or 0))


def pct(value: object) -> str:
    return f"{(dec(value) * 100).quantize(Decimal('0.01'))}%"


def wan(value: object) -> str:
    return f"{(dec(value) / Decimal('10000')).quantize(Decimal('0.1'))}w"


def sign_pct(curr: object, prev: object) -> str:
    curr_d = dec(curr)
    prev_d = dec(prev)
    if prev_d == 0:
        return "0%"
    q = ((curr_d - prev_d) / prev_d * 100).quantize(Decimal("1"))
    return ("+" if q > 0 else "") + f"{q}%"


def main() -> None:
    root = Path(__file__).resolve().parent.parent
    cfg = yaml.safe_load((root / "config.yaml").read_text(encoding="utf-8"))
    mysql = cfg["mysql"]
    conn = pymysql.connect(
        host=mysql["host"],
        port=int(mysql.get("port", 3306)),
        user=mysql.get("username") or mysql.get("user"),
        password=mysql["password"],
        database=mysql["database"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                  SUM(accumulated_viewers) viewers,
                  SUM(guided_sales_total) guided_sales,
                  SUM(brand_new_customers_total) new_customers,
                  SUM(live_sales_penetration_rate * accumulated_viewers) / NULLIF(SUM(accumulated_viewers), 0) live_pen,
                  SUM(purchasers_total) / NULLIF(SUM(accumulated_viewers), 0) conversion_rate
                FROM cep_live_session_data
                WHERE data_month='2026-05'
                """
            )
            live = cur.fetchone()
            cur.execute(
                """
                SELECT
                  SUM(accumulated_viewers) viewers,
                  SUM(guided_sales_total) guided_sales,
                  SUM(brand_new_customers_total) new_customers,
                  SUM(live_sales_penetration_rate * accumulated_viewers) / NULLIF(SUM(accumulated_viewers), 0) live_pen,
                  SUM(purchasers_total) / NULLIF(SUM(accumulated_viewers), 0) conversion_rate
                FROM cep_live_session_data
                WHERE data_month='2026-04'
                """
            )
            live_prev = cur.fetchone()
            cur.execute(
                """
                SELECT
                  COUNT(DISTINCT product_spu_id) articles,
                  SUM(item_count_total) qty,
                  SUM(sales_total) sales,
                  SUM(brand_new_customer_count) new_customers
                FROM cep_live_goods_effect
                WHERE data_month='2026-05'
                """
            )
            goods = cur.fetchone()
            cur.execute(
                """
                SELECT
                  CASE
                    WHEN category_level_1 IN ('配饰', '珠宝首饰') THEN '配饰'
                    ELSE category_level_1
                  END AS cat,
                  SUM(item_count_total) qty
                FROM cep_live_goods_effect
                WHERE data_month='2026-05'
                GROUP BY cat
                """
            )
            goods_cats = cur.fetchall()
    finally:
        conn.close()

    cat_total = sum(dec(row["qty"]) for row in goods_cats) or Decimal(1)
    cat_parts: list[str] = []
    for row in goods_cats:
        name = str(row["cat"] or "未知")
        share = (dec(row["qty"]) / cat_total * 100).quantize(Decimal("0"))
        cat_parts.append(f"{name}{share}%")

    texts = {
        "cover": "E-Commerce Monthly Sales (VIP Store)May 2026",
        "top_bags": "5月流水销量TOP10包款已按五月网页抓取结果更新；榜单、商品图和销售走势来自Tableau五月筛选数据。",
        "top_shoes": "5月流水销量TOP10鞋款已按五月网页抓取结果更新；榜单、商品图和销售走势来自Tableau五月筛选数据。",
        "uv": "同比去年5月，流量看板已按Compass五月流量来源数据更新；站内/站外、付费/自然流量及入口结构均来自本地库五月抓取结果。",
        "warehouse": "本页60仓表现已按Tableau五月筛选结果更新，包含折扣分布、箱包TOP和鞋类TOP三个模块。",
        "live14": (
            f"本月直播累计观看人数{wan(live['viewers'])}，直播渗透率{pct(live['live_pen'])}，"
            f"环比{sign_pct(live['live_pen'], live_prev['live_pen'])}；转化率{pct(live['conversion_rate'])}，"
            f"总引导销售{wan(live['guided_sales'])}，品牌新客数{int(dec(live['new_customers'])):,}。"
            "直播图表来自本地库cep_live_session_data五月数据。"
        ),
        "live15": (
            f"鞋包直播间有销售商品款数总计{int(dec(goods['articles']))}款，类目结构为{'、'.join(cat_parts)}。"
            f"销售额{wan(goods['sales'])}，直播商品榜单来自cep_live_goods_effect五月商品效果数据。"
        ),
        "member": "本页会员看板已按五月网页截图更新，四个模块图片来自会员看板五月筛选后的自动截图。",
    }

    ops = {
        "replace_text": [
            {"slide": 1, "shape_id": 3, "new_text": texts["cover"]},
            {"slide": 9, "shape_id": 32, "new_text": texts["top_bags"]},
            {"slide": 10, "shape_id": 15, "new_text": texts["top_shoes"]},
            {"slide": 12, "shape_id": 11, "new_text": texts["uv"]},
            {"slide": 13, "shape_id": 16, "new_text": texts["warehouse"]},
            {"slide": 14, "shape_id": 14, "new_text": texts["live14"]},
            {"slide": 15, "shape_id": 8, "new_text": texts["live15"]},
            {"slide": 17, "shape_id": 15, "new_text": texts["member"]},
        ],
        "replace_images": [],
        "replace_tables": [],
        "replace_charts": [],
        "replace_native_charts": [],
    }
    out = root / "output" / "vip-report" / "render-ops.may-202605.auto-text.json"
    out.write_text(json.dumps(ops, ensure_ascii=False, indent=2), encoding="utf-8")
    print(json.dumps({"operations_path": str(out), "texts": texts}, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()
