from __future__ import annotations

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

import pymysql
from PIL import Image, ImageDraw, ImageFont
SCRIPT_DIR = Path(__file__).resolve().parent
if str(SCRIPT_DIR) not in sys.path:
    sys.path.insert(0, str(SCRIPT_DIR))

from runtime_config import add_config_source_args, load_runtime_config


MONTH_CN_MAP = {
    1: "\u4e00\u6708",
    2: "\u4e8c\u6708",
    3: "\u4e09\u6708",
    4: "\u56db\u6708",
    5: "\u4e94\u6708",
    6: "\u516d\u6708",
    7: "\u4e03\u6708",
    8: "\u516b\u6708",
    9: "\u4e5d\u6708",
    10: "\u5341\u6708",
    11: "\u5341\u4e00\u6708",
    12: "\u5341\u4e8c\u6708",
}
MONTH_CN_TO_NUM = {label: number for number, label in MONTH_CN_MAP.items()}


def normalize_month_label(raw_month: Any) -> str:
    if raw_month is None:
        return MONTH_CN_MAP[1]
    if isinstance(raw_month, int):
        return MONTH_CN_MAP.get(raw_month, MONTH_CN_MAP[1])
    text = str(raw_month).strip()
    if text in MONTH_CN_TO_NUM:
        return text
    if text.isdigit():
        value = int(text)
        if 1 <= value <= 12:
            return MONTH_CN_MAP[value]
    return text


def month_label_to_number(label: str) -> int:
    return MONTH_CN_TO_NUM.get(label, 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 pct_text(value: Decimal) -> str:
    pct = (value * Decimal("100")).quantize(Decimal("1"), rounding=ROUND_HALF_UP)
    return f"{int(pct)}%"


def lfl_text(current: Decimal, previous: Decimal) -> str:
    if previous == 0:
        return "0%"
    lfl = ((current - previous) / previous * Decimal("100")).quantize(
        Decimal("1"), rounding=ROUND_HALF_UP
    )
    return f"{int(lfl)}%"


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


def draw_center_text(
    draw: ImageDraw.ImageDraw,
    box: tuple[int, int, int, int],
    text: str,
    font: ImageFont.ImageFont,
    fill: str,
) -> None:
    left, top, right, bottom = box
    bbox = draw.textbbox((0, 0), text, font=font)
    tw = bbox[2] - bbox[0]
    th = bbox[3] - bbox[1]
    x = left + (right - left - tw) / 2
    y = top + (bottom - top - th) / 2 - 1
    draw.text((x, y), text, font=font, fill=fill)


def query_month_row(
    conn: pymysql.connections.Connection,
    *,
    year: int,
    month_num: int,
) -> dict[str, Decimal]:
    month_prefix = f"{year}{month_num:02d}"
    sql = """
SELECT
  SUM(CAST(REPLACE(IFNULL(stationTraffic, '0'), ',', '') AS DECIMAL(20,4))) AS stationTraffic,
  SUM(CAST(REPLACE(IFNULL(paidTraffic, '0'), ',', '') AS DECIMAL(20,4))) AS paidTraffic,
  SUM(CAST(REPLACE(IFNULL(organicTraffic, '0'), ',', '') AS DECIMAL(20,4))) AS organicTraffic,
  SUM(CAST(REPLACE(IFNULL(externalTraffic, '0'), ',', '') AS DECIMAL(20,4))) AS externalTraffic,
  SUM(CAST(REPLACE(IFNULL(off_site_paid_traffic, '0'), ',', '') AS DECIMAL(20,4))) AS off_site_paid_traffic,
  SUM(CAST(REPLACE(IFNULL(off_site_free_traffic, '0'), ',', '') AS DECIMAL(20,4))) AS off_site_free_traffic,
  COUNT(*) AS row_count
FROM ceptrafficanalysis
WHERE daily LIKE %s
"""
    with conn.cursor() as cursor:
        cursor.execute(sql, (f"{month_prefix}%",))
        row = cursor.fetchone() or {}

    if int(row.get("row_count") or 0) <= 0:
        raise SystemExit(f"No data in ceptrafficanalysis for month prefix: {month_prefix}")

    return {
        "stationTraffic": to_decimal(row.get("stationTraffic")),
        "paidTraffic": to_decimal(row.get("paidTraffic")),
        "organicTraffic": to_decimal(row.get("organicTraffic")),
        "externalTraffic": to_decimal(row.get("externalTraffic")),
        "off_site_paid_traffic": to_decimal(row.get("off_site_paid_traffic")),
        "off_site_free_traffic": to_decimal(row.get("off_site_free_traffic")),
    }


def build_table_values(
    *,
    report_year: int,
    compare_year: int,
    current: dict[str, Decimal],
    previous: dict[str, Decimal],
) -> tuple[list[str], list[str], list[str]]:
    cur_in_paid_pct = (
        current["paidTraffic"] / current["stationTraffic"] if current["stationTraffic"] else Decimal("0")
    )
    prev_in_paid_pct = (
        previous["paidTraffic"] / previous["stationTraffic"] if previous["stationTraffic"] else Decimal("0")
    )
    cur_in_free_pct = (
        current["organicTraffic"] / current["stationTraffic"] if current["stationTraffic"] else Decimal("0")
    )
    prev_in_free_pct = (
        previous["organicTraffic"] / previous["stationTraffic"] if previous["stationTraffic"] else Decimal("0")
    )

    cur_out_paid_pct = (
        current["off_site_paid_traffic"] / current["externalTraffic"]
        if current["externalTraffic"]
        else Decimal("0")
    )
    prev_out_paid_pct = (
        previous["off_site_paid_traffic"] / previous["externalTraffic"]
        if previous["externalTraffic"]
        else Decimal("0")
    )
    cur_out_free_pct = (
        current["off_site_free_traffic"] / current["externalTraffic"]
        if current["externalTraffic"]
        else Decimal("0")
    )
    prev_out_free_pct = (
        previous["off_site_free_traffic"] / previous["externalTraffic"]
        if previous["externalTraffic"]
        else Decimal("0")
    )

    row_current = [
        str(report_year),
        f"{int(current['stationTraffic']):,}",
        f"{int(current['paidTraffic']):,}",
        pct_text(cur_in_paid_pct),
        f"{int(current['organicTraffic']):,}",
        pct_text(cur_in_free_pct),
        f"{int(current['externalTraffic']):,}",
        f"{int(current['off_site_paid_traffic']):,}",
        pct_text(cur_out_paid_pct),
        f"{int(current['off_site_free_traffic']):,}",
        pct_text(cur_out_free_pct),
    ]
    row_previous = [
        str(compare_year),
        f"{int(previous['stationTraffic']):,}",
        f"{int(previous['paidTraffic']):,}",
        pct_text(prev_in_paid_pct),
        f"{int(previous['organicTraffic']):,}",
        pct_text(prev_in_free_pct),
        f"{int(previous['externalTraffic']):,}",
        f"{int(previous['off_site_paid_traffic']):,}",
        pct_text(prev_out_paid_pct),
        f"{int(previous['off_site_free_traffic']):,}",
        pct_text(prev_out_free_pct),
    ]
    row_lfl = [
        "LFL",
        lfl_text(current["stationTraffic"], previous["stationTraffic"]),
        lfl_text(current["paidTraffic"], previous["paidTraffic"]),
        lfl_text(cur_in_paid_pct, prev_in_paid_pct),
        lfl_text(current["organicTraffic"], previous["organicTraffic"]),
        lfl_text(cur_in_free_pct, prev_in_free_pct),
        lfl_text(current["externalTraffic"], previous["externalTraffic"]),
        lfl_text(current["off_site_paid_traffic"], previous["off_site_paid_traffic"]),
        lfl_text(cur_out_paid_pct, prev_out_paid_pct),
        lfl_text(current["off_site_free_traffic"], previous["off_site_free_traffic"]),
        lfl_text(cur_out_free_pct, prev_out_free_pct),
    ]
    return row_current, row_previous, row_lfl


def render_table_image(
    *,
    output_path: Path,
    row_current: list[str],
    row_previous: list[str],
    row_lfl: list[str],
) -> None:
    headers = [
        "Year",
        "In-site Traffic",
        "In-site Paid Traffic",
        "In-site Paid %",
        "In-site Organic Traffic",
        "In-site Organic %",
        "Off-site Traffic",
        "Off-site Paid Traffic",
        "Off-site Paid %",
        "Off-site Organic Traffic",
        "Off-site Organic %",
    ]
    col_widths = [92, 128, 144, 110, 144, 110, 122, 144, 110, 144, 110]
    row_height = 46
    width = sum(col_widths) + 2
    height = row_height * 4 + 2

    img = Image.new("RGB", (width, height), "#FFFFFF")
    draw = ImageDraw.Draw(img)

    header_bg = "#5f6fce"
    grid = "#d5d5d5"
    text_black = "#111111"
    text_white = "#ffffff"
    lfl_pos = "#d11f1f"
    lfl_neg = "#0f9d58"

    font_header = load_font(22, bold=True)
    font_cell = load_font(22, bold=False)
    font_lfl = load_font(22, bold=True)

    # Header row
    x = 1
    for i, header in enumerate(headers):
        w = col_widths[i]
        box = (x, 1, x + w, 1 + row_height)
        draw.rectangle(box, fill=header_bg, outline=grid, width=1)
        draw_center_text(draw, box, header, font_header, text_white)
        x += w

    rows = [row_current, row_previous, row_lfl]
    for row_idx, row in enumerate(rows):
        y_top = 1 + row_height * (row_idx + 1)
        x = 1
        for col_idx, value in enumerate(row):
            w = col_widths[col_idx]
            box = (x, y_top, x + w, y_top + row_height)
            draw.rectangle(box, fill="#ffffff", outline=grid, width=1)
            color = text_black
            if row_idx == 2 and col_idx > 0:
                if value.startswith("-"):
                    color = lfl_neg
                elif value.startswith("0"):
                    color = text_black
                else:
                    color = lfl_pos
            draw_center_text(draw, box, value, font_lfl if row_idx == 2 else font_cell, color)
            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 CEP traffic month-vs-last-year summary image.")
    add_config_source_args(parser, default_config=Path(r"D:\projects\skills\vip-report\config.yaml"))
    parser.add_argument("--report-month", default="", help="Month label override, e.g. Jan / 1")
    parser.add_argument("--report-year", type=int, default=0, help="Report year override")
    parser.add_argument("--compare-year", type=int, default=0, help="Compare year override")
    return parser.parse_args()


def execute(
    config: dict[str, Any],
    *,
    config_ref: str = "",
    report_month: str = "",
    report_year: int = 0,
    compare_year: int = 0,
) -> dict[str, str]:
    config_path = config_ref or "<runtime-config>"
    report_cfg = config.get("report", {})

    report_month_label = normalize_month_label(report_month or report_cfg.get("month_cn", MONTH_CN_MAP[1]))
    report_month_num = month_label_to_number(report_month_label)
    report_year = int(report_year or report_cfg.get("year", 2026))
    compare_year = int(compare_year or report_cfg.get("compare_year", report_year - 1))

    mysql_cfg = config["mysql"]
    conn = pymysql.connect(
        host=mysql_cfg["host"],
        port=int(mysql_cfg["port"]),
        user=mysql_cfg["username"],
        password=mysql_cfg["password"],
        database=mysql_cfg["database"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )
    try:
        current = query_month_row(conn, year=report_year, month_num=report_month_num)
        previous = query_month_row(conn, year=compare_year, month_num=report_month_num)
    finally:
        conn.close()

    row_current, row_previous, row_lfl = build_table_values(
        report_year=report_year,
        compare_year=compare_year,
        current=current,
        previous=previous,
    )

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

    render_table_image(
        output_path=image_path,
        row_current=row_current,
        row_previous=row_previous,
        row_lfl=row_lfl,
    )

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

    manifest_path.parent.mkdir(parents=True, exist_ok=True)
    manifest_path.write_text(
        json.dumps(
            {
                "source": {
                    "table": "ceptrafficanalysis",
                    "config_path": str(config_path),
                    "report_period": {
                        "month_label": report_month_label,
                        "month_number": report_month_num,
                        "year": report_year,
                        "compare_year": compare_year,
                    },
                },
                "values": {
                    "current": row_current,
                    "previous": row_previous,
                    "lfl": row_lfl,
                },
                "asset_path": str(image_path),
                "operations_path": str(operations_path),
            },
            ensure_ascii=False,
            indent=2,
        ),
        encoding="utf-8",
    )

    return {
        "operations_path": str(operations_path),
        "manifest_path": str(manifest_path),
        "image_path": str(image_path),
    }


def main() -> None:
    args = parse_args()
    config = load_runtime_config(args, default_config=Path(r"D:\projects\skills\vip-report\config.yaml"))
    result = execute(
        config,
        config_ref=(args.config if args.config_source == "yaml" else f"db:{args.config_table}/{args.config_key}"),
        report_month=args.report_month,
        report_year=args.report_year,
        compare_year=args.compare_year,
    )
    print(json.dumps(result, ensure_ascii=False))


if __name__ == "__main__":
    main()
