from __future__ import annotations

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

import pymysql

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


CHANNELS = ["Target-Max", "\u552f\u667a\u5c55", "\u552f\u76f4\u8fbe", "\u552f\u89e6\u70b9", "\u817e\u8baf\u5e7f\u544a"]
OPTIONAL_TREND_CHANNELS = ["\u552f\u4eab\u5ba2"]
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",
}


@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 MONTH_ABBR[self.month]


def parse_month(raw: Any) -> int:
    text = str(raw or "1").strip()
    digits = "".join(ch for ch in text if ch.isdigit())
    if not digits:
        return 1
    return min(12, max(1, int(digits)))


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


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


def fmt_amount(value: Decimal) -> str:
    return f"{value.quantize(Decimal('0.01'), rounding=ROUND_HALF_UP):,}"


def fmt_roi(return_value: Decimal, investment: Decimal) -> str:
    if investment <= 0:
        return "0.0"
    return f"{(return_value / investment).quantize(Decimal('0.1'), rounding=ROUND_HALF_UP):,}"


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


def connect_from_config(cfg: dict[str, Any]):
    mysql_cfg = cfg.get("mysql") or {}
    return pymysql.connect(
        host=mysql_cfg.get("host", "127.0.0.1"),
        port=int(mysql_cfg.get("port", 3306)),
        user=mysql_cfg.get("username") or mysql_cfg.get("user") or "root",
        password=mysql_cfg.get("password") or "",
        database=mysql_cfg.get("database") or "ckc_cep_db_test",
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )


def table_exists(conn, table: str) -> bool:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT COUNT(*) AS c
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
            """,
            (table,),
        )
        return int((cur.fetchone() or {}).get("c") or 0) > 0


def table_count(conn, table: str) -> int:
    with conn.cursor() as cur:
        cur.execute(f"SELECT COUNT(*) AS c FROM `{table}`")
        return int((cur.fetchone() or {}).get("c") or 0)


def fetch_columns(conn, table: str) -> set[str]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT COLUMN_NAME
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
            """,
            (table,),
        )
        return {str(r["COLUMN_NAME"]) for r in cur.fetchall()}


def fetch_attribution(conn, month: MonthKey) -> dict[str, dict[str, Decimal]]:
    rows_by_channel: dict[str, dict[str, Decimal]] = {}
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT
              primary_entry,
              product_detail_uv,
              sales_amount,
              customer_count,
              marketing_new_customer_count,
              add_to_cart_users
            FROM cep_traffic_summary
            WHERE daily = %s
              AND primary_entry IN %s
              AND (secondary_entry IS NULL OR secondary_entry = 'all')
            ORDER BY CASE WHEN secondary_entry IS NULL THEN 0 ELSE 1 END
            """,
            (month.ym, CHANNELS),
        )
        for row in cur.fetchall():
            name = str(row["primary_entry"])
            if name in rows_by_channel:
                continue
            rows_by_channel[name] = {
                "product_detail_uv": to_decimal(row.get("product_detail_uv")),
                "sales_amount": to_decimal(row.get("sales_amount")),
                "customer_count": to_decimal(row.get("customer_count")),
                "marketing_new_customer_count": to_decimal(row.get("marketing_new_customer_count")),
                "add_to_cart_users": to_decimal(row.get("add_to_cart_users")),
            }
    return rows_by_channel


def validate_otd_source(conn, table: str, month: MonthKey) -> tuple[bool, list[str], dict[str, Any]]:
    diagnostics: dict[str, Any] = {
        "table": table,
        "exists": False,
        "row_count": 0,
        "report_month_rows": None,
        "min_date": None,
        "max_date": None,
        "columns": [],
    }
    required_groups = {
        "channel": {"channel", "media", "platform", "traffic_channel", "name", "\u6e20\u9053"},
        "month": {"month", "daily", "date", "biz_date", "\u6708\u4efd", "\u65e5\u671f"},
        "investment": {"investment", "spend", "cost", "fee", "consume", "platform_paid_traffic", "\u82b1\u8d39", "\u8d39\u7528", "\u6d88\u8017"},
        "impressions": {"impressions", "impression", "exposure", "show", "display", "\u66dd\u5149", "\u66dd\u5149\u91cf", "\u5c55\u73b0"},
        "clicks": {"clicks", "click", "click_qty", "\u70b9\u51fb", "\u70b9\u51fb\u91cf"},
    }
    if not table_exists(conn, table):
        return False, [f"OTD source table `{table}` does not exist."], diagnostics

    diagnostics["exists"] = True
    count = table_count(conn, table)
    diagnostics["row_count"] = count
    cols = fetch_columns(conn, table)
    diagnostics["columns"] = sorted(cols)
    if count <= 0:
        return False, [f"OTD source table `{table}` is empty."], diagnostics

    lower_cols = {c.lower(): c for c in cols}
    date_col = None
    for candidate in ["date", "daily", "biz_date", "month", "\u65e5\u671f", "\u6708\u4efd"]:
        if candidate.lower() in lower_cols:
            date_col = lower_cols[candidate.lower()]
            break
    if date_col:
        start = f"{month.year}-{month.month:02d}-01"
        if month.month == 12:
            end = f"{month.year + 1}-01-01"
        else:
            end = f"{month.year}-{month.month + 1:02d}-01"
        with conn.cursor() as cur:
            cur.execute(
                f"""
                SELECT
                  MIN(`{date_col}`) AS min_date,
                  MAX(`{date_col}`) AS max_date,
                  SUM(CASE WHEN `{date_col}` >= %s AND `{date_col}` < %s THEN 1 ELSE 0 END) AS report_month_rows
                FROM `{table}`
                """,
                (start, end),
            )
            row = cur.fetchone() or {}
        diagnostics["min_date"] = str(row.get("min_date")) if row.get("min_date") is not None else None
        diagnostics["max_date"] = str(row.get("max_date")) if row.get("max_date") is not None else None
        diagnostics["report_month_rows"] = int(row.get("report_month_rows") or 0)

    errors: list[str] = []
    if diagnostics["report_month_rows"] == 0:
        errors.append(f"OTD source table `{table}` has no rows for report month {month.ym}.")

    missing: list[str] = []
    for group, aliases in required_groups.items():
        if not any(alias.lower() in lower_cols for alias in aliases):
            missing.append(group)
    if missing:
        errors.append(f"OTD source table `{table}` is missing required field group(s): {', '.join(missing)}.")
    if errors:
        return False, errors, diagnostics
    return True, [], diagnostics


def build_diagnostic(output_dir: Path, month: MonthKey, otd_diag: dict[str, Any], errors: list[str], attribution: dict[str, dict[str, Decimal]]) -> Path:
    data_dir = output_dir / "data" / "slide16-otd"
    data_dir.mkdir(parents=True, exist_ok=True)
    payload = {
        "report_month": month.ym,
        "status": "blocked",
        "errors": errors,
        "otd_source": otd_diag,
        "available_attribution_rows": {
            channel: {k: float(v) for k, v in values.items()} for channel, values in attribution.items()
        },
        "required_slide16_fields": [
            "channel",
            "investment",
            "impressions",
            "clicks",
            "product_detail_uv",
            "ctr",
            "cpc",
            "14_day_add_to_cart",
            "14_day_customers",
            "14_day_new_customers",
            "14_day_new_customer_rate",
            "14_day_sales",
            "14_day_roi",
        ],
    }
    path = data_dir / "slide16-otd.diagnostic.json"
    path.write_text(json.dumps(payload, ensure_ascii=False, indent=2), encoding="utf-8")
    return path


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Build editable slide 16 OTD operations.")
    add_config_source_args(parser, default_config=Path("config.yaml"))
    parser.set_defaults(config_source="yaml")
    parser.add_argument("--otd-table", default="ec_mkt_cost")
    parser.add_argument("--output", default=r"output\vip-report\render-ops.slide16-otd.json")
    parser.add_argument("--allow-incomplete", action="store_true", help="Only write diagnostics; do not fail when OTD spend data is missing.")
    return parser.parse_args()


def main() -> None:
    args = parse_args()
    cfg = load_runtime_config(args, default_config=Path("config.yaml"))
    report = cfg.get("report") or {}
    month = MonthKey(int(report.get("year") or 2026), parse_month(report.get("month_cn")))
    workdir = Path((cfg.get("paths") or {}).get("workdir") or r"output\vip-report")
    if not workdir.is_absolute():
        workdir = Path.cwd() / workdir

    conn = connect_from_config(cfg)
    try:
        attribution = fetch_attribution(conn, month)
        ok, errors, diag = validate_otd_source(conn, args.otd_table, month)
    finally:
        conn.close()

    if not ok:
        diag_path = build_diagnostic(workdir, month, diag, errors, attribution)
        message = f"Slide 16 OTD source is incomplete. Diagnostic written to {diag_path}"
        if args.allow_incomplete:
            print(message)
            return
        raise SystemExit(message)

    raise SystemExit(
        "OTD source table exists, but this script has not mapped the source columns yet. "
        "Add the column mapping once the source schema is confirmed."
    )


if __name__ == "__main__":
    main()
