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


EN_MONTH_ABBR = {
    1: "Jan",
    2: "Feb",
    3: "Mar",
    4: "Apr",
    5: "May",
    6: "Jun",
    7: "Jul",
    8: "Aug",
    9: "Sep",
    10: "Oct",
    11: "Nov",
    12: "Dec",
}

TABLE_NAME = "cep_live_session_data"


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

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

    @property
    def label(self) -> str:
        return EN_MONTH_ABBR[self.month]


@dataclass(frozen=True)
class Agg:
    accumulated_viewers: Decimal
    live_sales_penetration_rate: Decimal
    purchasers_total: Decimal
    conversion_rate: Decimal
    live_period_sales_total: Decimal
    guided_sales_total: Decimal
    brand_customers_total: Decimal
    brand_new_customers_total: Decimal
    new_customer_ratio: Decimal
    new_brand_members: Decimal


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 previous_month(key: MonthKey) -> MonthKey:
    if key.month == 1:
        return MonthKey(key.year - 1, 12)
    return MonthKey(key.year, key.month - 1)


def to_decimal(value: Any) -> Decimal:
    if value is None:
        return Decimal("0")
    text = str(value).strip()
    if not text:
        return Decimal("0")
    text = text.replace(",", "")
    try:
        return Decimal(text)
    except Exception:
        return Decimal("0")


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\arialbd.ttf"])
    candidates.extend([r"C:\Windows\Fonts\msyh.ttc", r"C:\Windows\Fonts\arial.ttf", r"C:\Windows\Fonts\simsun.ttc"])
    for path in candidates:
        try:
            return ImageFont.truetype(path, size=size)
        except OSError:
            continue
    return ImageFont.load_default()


def fmt_int(v: Decimal) -> str:
    return f"{int(v.quantize(Decimal('1'), rounding=ROUND_HALF_UP)):,}"


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


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


def fetch_month_agg(conn: pymysql.connections.Connection, month_key: MonthKey) -> Agg:
    sql = f"""
SELECT
  SUM(IFNULL(accumulated_viewers, 0)) AS accumulated_viewers,
  SUM(IFNULL(purchasers_total, 0)) AS purchasers_total,
  SUM(IFNULL(live_period_sales_total, 0)) AS live_period_sales_total,
  SUM(IFNULL(guided_sales_total, 0)) AS guided_sales_total,
  SUM(IFNULL(brand_customers_total, 0)) AS brand_customers_total,
  SUM(IFNULL(brand_new_customers_total, 0)) AS brand_new_customers_total,
  SUM(IFNULL(new_brand_members, 0)) AS new_brand_members,
  SUM(IFNULL(live_sales_penetration_rate, 0) * IFNULL(accumulated_viewers, 0)) AS weighted_penetration,
  SUM(IFNULL(accumulated_viewers, 0)) AS weighted_base
FROM `{TABLE_NAME}`
WHERE data_month = %s
"""
    with conn.cursor() as cur:
        cur.execute(sql, (month_key.ym,))
        row = cur.fetchone() or {}

    viewers = to_decimal(row.get("accumulated_viewers"))
    purchasers = to_decimal(row.get("purchasers_total"))
    brand_customers = to_decimal(row.get("brand_customers_total"))
    brand_new = to_decimal(row.get("brand_new_customers_total"))
    weighted_pen = to_decimal(row.get("weighted_penetration"))
    weighted_base = to_decimal(row.get("weighted_base"))

    live_pen = (weighted_pen / weighted_base) if weighted_base > 0 else Decimal("0")
    conv = (purchasers / viewers) if viewers > 0 else Decimal("0")
    new_ratio = (brand_new / brand_customers) if brand_customers > 0 else Decimal("0")

    return Agg(
        accumulated_viewers=viewers,
        live_sales_penetration_rate=live_pen,
        purchasers_total=purchasers,
        conversion_rate=conv,
        live_period_sales_total=to_decimal(row.get("live_period_sales_total")),
        guided_sales_total=to_decimal(row.get("guided_sales_total")),
        brand_customers_total=brand_customers,
        brand_new_customers_total=brand_new,
        new_customer_ratio=new_ratio,
        new_brand_members=to_decimal(row.get("new_brand_members")),
    )


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
    b = draw.textbbox((0, 0), text, font=font)
    tw = b[2] - b[0]
    th = b[3] - b[1]
    draw.text((x0 + (x1 - x0 - tw) / 2, y0 + (y1 - y0 - th) / 2 - 1), text, font=font, fill=fill)


def render_image(output_path: Path, prev_key: MonthKey, prev: Agg, curr_key: MonthKey, curr: Agg) -> None:
    headers = [
        "Month",
        "Accumulated Viewers",
        "Live Penetration",
        "Purchasers",
        "Conversion",
        "Live Period Sales",
        "Guided Sales",
        "Brand Customers",
        "Brand New Customers",
        "New Customer Ratio",
        "New Brand Members",
    ]

    row_prev = [
        prev_key.label,
        fmt_int(prev.accumulated_viewers),
        fmt_pct(prev.live_sales_penetration_rate),
        fmt_int(prev.purchasers_total),
        fmt_pct(prev.conversion_rate),
        fmt_int(prev.live_period_sales_total),
        fmt_int(prev.guided_sales_total),
        fmt_int(prev.brand_customers_total),
        fmt_int(prev.brand_new_customers_total),
        fmt_pct(prev.new_customer_ratio, 0),
        fmt_int(prev.new_brand_members),
    ]
    row_curr = [
        curr_key.label,
        fmt_int(curr.accumulated_viewers),
        fmt_pct(curr.live_sales_penetration_rate),
        fmt_int(curr.purchasers_total),
        fmt_pct(curr.conversion_rate),
        fmt_int(curr.live_period_sales_total),
        fmt_int(curr.guided_sales_total),
        fmt_int(curr.brand_customers_total),
        fmt_int(curr.brand_new_customers_total),
        fmt_pct(curr.new_customer_ratio, 0),
        fmt_int(curr.new_brand_members),
    ]
    row_lfl = [
        "LFL",
        fmt_pct(lfl(curr.accumulated_viewers, prev.accumulated_viewers), 0),
        fmt_pct(lfl(curr.live_sales_penetration_rate, prev.live_sales_penetration_rate), 0),
        fmt_pct(lfl(curr.purchasers_total, prev.purchasers_total), 0),
        fmt_pct(lfl(curr.conversion_rate, prev.conversion_rate), 0),
        fmt_pct(lfl(curr.live_period_sales_total, prev.live_period_sales_total), 0),
        fmt_pct(lfl(curr.guided_sales_total, prev.guided_sales_total), 0),
        fmt_pct(lfl(curr.brand_customers_total, prev.brand_customers_total), 0),
        fmt_pct(lfl(curr.brand_new_customers_total, prev.brand_new_customers_total), 0),
        fmt_pct(lfl(curr.new_customer_ratio, prev.new_customer_ratio), 0),
        fmt_pct(lfl(curr.new_brand_members, prev.new_brand_members), 0),
    ]

    col_widths = [90, 125, 122, 102, 90, 145, 130, 120, 145, 125, 128]
    row_h = 25
    table_x = 27
    table_y = 50
    table_w = sum(col_widths)
    table_h = row_h * 4

    image_w = max(1126, table_x + table_w + 28)
    img = Image.new("RGB", (image_w, 160), "#e7e7e7")
    draw = ImageDraw.Draw(img)

    font_title = load_font(38, bold=True)
    font_header = load_font(13, bold=True)
    font_cell = load_font(16, bold=False)
    font_lfl = load_font(16, bold=True)
    font_brand = load_font(30, bold=True)

    # Title + underline
    draw.text((16, 6), "LIVESTREAM", fill="#111111", font=font_title)
    draw.line((16, 43, 150, 43), fill="#cf2323", width=2)

    # Brand text
    brand_text = "CHARLES & KEITH"
    brand_bbox = draw.textbbox((0, 0), brand_text, font=font_brand)
    draw.text((image_w - (brand_bbox[2] - brand_bbox[0]) - 18, 8), brand_text, fill="#cfcfcf", font=font_brand)

    # Table header bg
    header_bg = "#6c72df"
    grid = "#cfcfcf"
    draw.rectangle((table_x, table_y, table_x + table_w, table_y + row_h), fill=header_bg, outline=grid, width=1)
    draw.rectangle((table_x, table_y, table_x + table_w, table_y + table_h), outline="#a9a9a9", width=1)

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

    rows = [row_prev, row_curr, row_lfl]
    for ridx, row in enumerate(rows):
        y0 = table_y + row_h * (ridx + 1)
        y1 = y0 + row_h
        draw.line((table_x, y1, table_x + table_w, y1), fill=grid, width=1)
        x = table_x
        for cidx, val in enumerate(row):
            w = col_widths[cidx]
            fill = "#111111"
            font = font_lfl if ridx == 2 else font_cell
            if ridx == 2 and cidx > 0:
                if str(val).startswith("-"):
                    fill = "#00a35a"
                elif str(val).startswith("0"):
                    fill = "#111111"
                else:
                    fill = "#d91f1f"
            draw_center(draw, (x, y0, x + w, y1), str(val), font, fill)
            x += w

    output_path.parent.mkdir(parents=True, exist_ok=True)
    img.save(output_path)


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Generate livestream summary image from cep_live_session_data.")
    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")
    config = load_runtime_config(args, default_config=config_path)
    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"))
    curr_key = MonthKey(year, month)
    prev_key = previous_month(curr_key)

    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 = fetch_month_agg(conn, curr_key)
        prev = fetch_month_agg(conn, prev_key)
    finally:
        conn.close()

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

    render_image(image_path, prev_key, prev, curr_key, curr)

    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_key.ym,
            "previous_month": prev_key.ym,
            "config_path": str(config_path),
        },
        "asset_path": str(image_path),
        "operations_path": str(operations_path),
        "metrics": {
            "prev": {
                "accumulated_viewers": str(prev.accumulated_viewers),
                "live_penetration": str(prev.live_sales_penetration_rate),
                "purchasers_total": str(prev.purchasers_total),
                "conversion_rate": str(prev.conversion_rate),
            },
            "curr": {
                "accumulated_viewers": str(curr.accumulated_viewers),
                "live_penetration": str(curr.live_sales_penetration_rate),
                "purchasers_total": str(curr.purchasers_total),
                "conversion_rate": str(curr.conversion_rate),
            },
        },
    }
    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(
            {
                "operations_path": str(operations_path),
                "manifest_path": str(manifest_path),
                "image_path": str(image_path),
            },
            ensure_ascii=False,
        )
    )


if __name__ == "__main__":
    main()
