from __future__ import annotations

import argparse
import json
import re
import sys
from dataclasses import dataclass
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
from typing import Any

SCRIPT_DIR = Path(__file__).resolve().parent
if str(SCRIPT_DIR) not in sys.path:
    sys.path.insert(0, str(SCRIPT_DIR))
for dep_dir in [SCRIPT_DIR / ".pydeps", SCRIPT_DIR.parent / ".pydeps", Path.cwd() / ".pydeps"]:
    if dep_dir.exists() and str(dep_dir) not in sys.path:
        sys.path.insert(0, str(dep_dir))

import pymysql
from PIL import Image, ImageDraw, ImageFont
import yaml

from runtime_config import add_config_source_args, load_runtime_config


TABLE_NAME = "cep_live_goods_effect"


@dataclass(frozen=True)
class ReportMonth:
    year: int
    month: int

    @property
    def ym(self) -> str:
        return f"{self.year}-{self.month:02d}"


def parse_month_number(raw: Any) -> int:
    if raw is None:
        return 1
    if isinstance(raw, int):
        return min(12, max(1, raw))
    text = str(raw).strip()
    m = re.search(r"(1[0-2]|0?[1-9])", text)
    if m:
        return int(m.group(1))
    return 1


def resolve_report_month(args: argparse.Namespace, config: dict[str, Any]) -> ReportMonth:
    report_cfg = config.get("report", {})
    year = int(args.report_year or report_cfg.get("year", 2026))
    month = parse_month_number(args.report_month or report_cfg.get("month_cn", "1"))
    return ReportMonth(year=year, month=month)


def previous_month(m: ReportMonth) -> ReportMonth:
    if m.month == 1:
        return ReportMonth(m.year - 1, 12)
    return ReportMonth(m.year, m.month - 1)


def same_month_last_year(m: ReportMonth) -> ReportMonth:
    return ReportMonth(m.year - 1, m.month)


def to_decimal(v: Any) -> Decimal:
    if v is None:
        return Decimal("0")
    try:
        return Decimal(str(v))
    except Exception:
        return Decimal("0")


def round_int(v: Decimal) -> int:
    return int(v.quantize(Decimal("1"), rounding=ROUND_HALF_UP))


def pct(part: Decimal, total: Decimal) -> Decimal:
    if total == 0:
        return Decimal("0")
    return part / total


def lfl(curr: Decimal, prev: Decimal) -> Decimal:
    if prev == 0:
        return Decimal("0")
    return (curr - prev) / prev


def fmt_pct(v: Decimal) -> str:
    q = (v * Decimal("100")).quantize(Decimal("1"), rounding=ROUND_HALF_UP)
    sign = "+" if q > 0 else ""
    return f"{sign}{q}%"


def fmt_share(v: Decimal) -> str:
    q = (v * Decimal("100")).quantize(Decimal("1"), rounding=ROUND_HALF_UP)
    return f"{q}%"


def load_font(size: int, *, bold: bool = False) -> ImageFont.ImageFont:
    candidates: list[str] = []
    if bold:
        candidates.extend([r"C:\Windows\Fonts\msyhbd.ttc", r"C:\Windows\Fonts\simhei.ttf", r"C:\Windows\Fonts\arialbd.ttf"])
    candidates.extend([r"C:\Windows\Fonts\msyh.ttc", r"C:\Windows\Fonts\simsun.ttc", r"C:\Windows\Fonts\arial.ttf"])
    for p in candidates:
        try:
            return ImageFont.truetype(p, size=size)
        except OSError:
            continue
    return ImageFont.load_default()


def draw_center(draw: ImageDraw.ImageDraw, box: tuple[int, int, int, int], text: str, font: ImageFont.ImageFont, fill: str) -> None:
    x0, y0, x1, y1 = box
    bb = draw.textbbox((0, 0), text, font=font)
    tw = bb[2] - bb[0]
    th = bb[3] - bb[1]
    draw.text((x0 + (x1 - x0 - tw) / 2, y0 + (y1 - y0 - th) / 2), text, fill=fill, font=font)


def fetch_month_rows(conn: pymysql.connections.Connection, ym: str) -> list[dict[str, Any]]:
    sql = f"""
SELECT
  CASE
    WHEN category_level_1 IN ('配饰', '珠宝首饰') THEN '配饰'
    ELSE category_level_1
  END AS cat,
  COUNT(DISTINCT product_spu_id) AS article_cnt,
  SUM(IFNULL(item_count_total, 0)) AS qty,
  SUM(IFNULL(sales_total, 0)) AS sales,
  SUM(IFNULL(brand_new_customer_count, 0)) AS brand_new_customers
FROM `{TABLE_NAME}`
WHERE data_month = %s
GROUP BY cat
"""
    with conn.cursor() as cur:
        cur.execute(sql, (ym,))
        return cur.fetchall() or []


def index_by_cat(rows: list[dict[str, Any]]) -> dict[str, dict[str, Decimal]]:
    out: dict[str, dict[str, Decimal]] = {}
    for r in rows:
        cat = str(r.get("cat") or "").strip() or "未知"
        out[cat] = {
            "article_cnt": to_decimal(r.get("article_cnt")),
            "qty": to_decimal(r.get("qty")),
            "sales": to_decimal(r.get("sales")),
            "brand_new_customers": to_decimal(r.get("brand_new_customers")),
        }
    return out


def render_table(output_path: Path, curr_rows: dict[str, dict[str, Decimal]], prev_rows: dict[str, dict[str, Decimal]]) -> dict[str, Any]:
    categories = ["箱包", "鞋", "配饰"]

    # Fill missing categories with zeros
    for c in categories:
        curr_rows.setdefault(c, {"article_cnt": Decimal("0"), "qty": Decimal("0"), "sales": Decimal("0"), "brand_new_customers": Decimal("0")})
        prev_rows.setdefault(c, {"article_cnt": Decimal("0"), "qty": Decimal("0"), "sales": Decimal("0"), "brand_new_customers": Decimal("0")})

    ttl_curr = {
        "article_cnt": sum(curr_rows[c]["article_cnt"] for c in categories),
        "qty": sum(curr_rows[c]["qty"] for c in categories),
        "sales": sum(curr_rows[c]["sales"] for c in categories),
        "brand_new_customers": sum(curr_rows[c]["brand_new_customers"] for c in categories),
    }
    ttl_prev = {
        "article_cnt": sum(prev_rows[c]["article_cnt"] for c in categories),
        "qty": sum(prev_rows[c]["qty"] for c in categories),
        "sales": sum(prev_rows[c]["sales"] for c in categories),
        "brand_new_customers": sum(prev_rows[c]["brand_new_customers"] for c in categories),
    }

    headers = [
        "Cat", "Article", "Article%", "直播QTY", "QTY%", "QTY LFL",
        "SALES", "SALES%", "SALES LFL", "品牌新客数", "品牌新客数%", "品牌新客数 LFL", "ASP", "ASP LFL"
    ]
    col_widths = [56, 70, 74, 78, 68, 72, 110, 70, 74, 92, 92, 96, 66, 72]
    row_h = 26
    table_w = sum(col_widths)
    table_h = row_h * 5

    img = Image.new("RGB", (table_w + 20, table_h + 20), "#e7e7e7")
    draw = ImageDraw.Draw(img)
    x0, y0 = 10, 10

    header_bg = "#7178de"
    grid = "#aab0d8"
    ttl_bg = "#c4c6eb"
    text_color = "#111111"

    font_header = load_font(14, bold=True)
    font_cell = load_font(16, bold=False)
    font_ttl = load_font(16, bold=True)

    draw.rectangle((x0, y0, x0 + table_w, y0 + row_h), fill=header_bg, outline=grid, width=1)
    draw.rectangle((x0, y0, x0 + table_w, y0 + table_h), outline=grid, width=1)

    x = x0
    for i, h in enumerate(headers):
        w = col_widths[i]
        draw_center(draw, (x, y0, x + w, y0 + row_h), h, font_header, "#ffffff")
        x += w

    def row_values(cat: str, curr: dict[str, Decimal], prev: dict[str, Decimal]) -> list[str]:
        asp_curr = curr["sales"] / curr["qty"] if curr["qty"] > 0 else Decimal("0")
        asp_prev = prev["sales"] / prev["qty"] if prev["qty"] > 0 else Decimal("0")
        return [
            cat,
            str(round_int(curr["article_cnt"])),
            fmt_share(pct(curr["article_cnt"], ttl_curr["article_cnt"])),
            str(round_int(curr["qty"])),
            fmt_share(pct(curr["qty"], ttl_curr["qty"])),
            fmt_pct(lfl(curr["qty"], prev["qty"])),
            f"{curr['sales'].quantize(Decimal('0.01'))}",
            fmt_share(pct(curr["sales"], ttl_curr["sales"])),
            fmt_pct(lfl(curr["sales"], prev["sales"])),
            str(round_int(curr["brand_new_customers"])),
            fmt_share(pct(curr["brand_new_customers"], ttl_curr["brand_new_customers"])),
            fmt_pct(lfl(curr["brand_new_customers"], prev["brand_new_customers"])),
            str(round_int(asp_curr)),
            fmt_pct(lfl(asp_curr, asp_prev)),
        ]

    rows_text: list[list[str]] = []
    for c in categories:
        rows_text.append(row_values(c, curr_rows[c], prev_rows[c]))
    rows_text.append(row_values("TTL", ttl_curr, ttl_prev))

    lfl_cols = {5, 8, 11, 13}

    for ridx, row in enumerate(rows_text):
        y_top = y0 + row_h * (ridx + 1)
        y_bottom = y_top + row_h
        if row[0] == "TTL":
            draw.rectangle((x0, y_top, x0 + table_w, y_bottom), fill=ttl_bg, outline=grid, width=1)
        draw.line((x0, y_bottom, x0 + table_w, y_bottom), fill=grid, width=1)

        x = x0
        for cidx, val in enumerate(row):
            w = col_widths[cidx]
            fill = text_color
            fnt = font_ttl if row[0] == "TTL" else font_cell
            if cidx in lfl_cols:
                if val.startswith("-"):
                    fill = "#00a35a"
                elif val.startswith("+"):
                    fill = "#d91f1f"
            draw_center(draw, (x, y_top, x + w, y_bottom), val, fnt, fill)
            x += w

    output_path.parent.mkdir(parents=True, exist_ok=True)
    img.save(output_path)
    return {
        "categories": categories,
        "totals": {
            "article_cnt": str(ttl_curr["article_cnt"]),
            "qty": str(ttl_curr["qty"]),
            "sales": str(ttl_curr["sales"]),
            "brand_new_customers": str(ttl_curr["brand_new_customers"]),
        },
    }


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Generate live goods-effect summary table image.")
    add_config_source_args(parser, default_config=Path(r"D:\projects\skills\vip-report\config.yaml"))
    parser.add_argument("--report-year", type=int, default=0)
    parser.add_argument("--report-month", default="")
    return parser.parse_args()


def main() -> None:
    args = parse_args()
    config_path = Path(args.config or r"D:\projects\skills\vip-report\config.yaml").resolve()
    config = load_runtime_config(args, default_config=config_path)
    curr_month = resolve_report_month(args, config)
    prev_month = previous_month(curr_month)
    prev2_month = same_month_last_year(curr_month)

    mysql_cfg = config["mysql"]
    mysql_user = mysql_cfg.get("username") or mysql_cfg.get("user")
    conn = pymysql.connect(
        host=mysql_cfg["host"],
        port=int(mysql_cfg["port"]),
        user=mysql_user,
        password=mysql_cfg["password"],
        database=mysql_cfg["database"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )
    try:
        curr_raw = fetch_month_rows(conn, curr_month.ym)
        prev_raw = fetch_month_rows(conn, prev_month.ym)
        compare_month = prev_month.ym
        if not prev_raw:
            prev_raw = fetch_month_rows(conn, prev2_month.ym)
            compare_month = prev2_month.ym
    finally:
        conn.close()

    curr_rows = index_by_cat(curr_raw)
    prev_rows = index_by_cat(prev_raw)

    workdir = Path(config["paths"]["workdir"]).resolve()
    asset_dir = workdir / "assets" / "cep-live-goods-effect"
    data_dir = workdir / "data" / "cep-live-goods-effect"
    image_path = asset_dir / "cep-live-goods-effect-summary.png"
    operations_path = workdir / "render-ops.cep-live-goods-effect-summary.live.json"
    manifest_path = data_dir / "cep-live-goods-effect-summary.live.json"

    metrics = render_table(image_path, curr_rows, prev_rows)

    operations = {"replace_text": [], "replace_images": []}
    operations_path.write_text(json.dumps(operations, ensure_ascii=False, indent=2), encoding="utf-8")

    manifest = {
        "source": {"table": TABLE_NAME, "current_month": curr_month.ym, "compare_month": compare_month, "config_path": str(config_path)},
        "asset_path": str(image_path),
        "operations_path": str(operations_path),
        "metrics": metrics,
    }
    manifest_path.parent.mkdir(parents=True, exist_ok=True)
    manifest_path.write_text(json.dumps(manifest, ensure_ascii=False, indent=2), encoding="utf-8")

    print(json.dumps({"image_path": str(image_path), "manifest_path": str(manifest_path), "operations_path": str(operations_path)}, ensure_ascii=False))


if __name__ == "__main__":
    main()
