import argparse
import os
from pathlib import Path

import pandas as pd
import pymysql


def find_excel_file(pattern: str) -> str:
    desktop = Path(r"C:\Users\ethan.chang\Desktop")
    matches = sorted(desktop.glob(pattern), key=lambda p: p.stat().st_mtime, reverse=True)
    if not matches:
        raise FileNotFoundError("No matching Excel file found on Desktop.")
    return str(matches[0])


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--excel", default=os.environ.get("EXCEL_PATH"))
    parser.add_argument("--daily", required=True)
    parser.add_argument("--table", default="cep_traffic_summary")
    parser.add_argument("--pattern", default="*20260101-20260131*.xlsx")
    args = parser.parse_args()

    excel_path = args.excel or find_excel_file(args.pattern)
    daily_val = args.daily
    table = args.table

    df = pd.read_excel(excel_path, sheet_name=0)
    col_map = {
        "流量类型": "traffic_type",
        "一级入口": "primary_entry",
        "二级入口": "secondary_entry",
        "商详UV": "product_detail_uv",
        "商详UV占比": "product_detail_uv_ratio",
        "销售额(含拒退)": "sales_amount",
        "销售额占比": "sales_ratio",
        "销售量": "sales_volume",
        "购买转化率": "purchase_conversion_rate",
        "件单价": "unit_price",
        "客户数": "customer_count",
        "新客数": "new_customer_count",
        "营销新客数": "marketing_new_customer_count",
        "加购人数": "add_to_cart_users",
        "商详UV价值": "product_detail_uv_value",
        "ARPU": "arpu",
    }
    df = df.rename(columns=col_map)

    pct_cols = ["product_detail_uv_ratio", "sales_ratio", "purchase_conversion_rate"]
    for c in pct_cols:
        df[c] = df[c].astype(str).str.replace("%", "", regex=False)

    int_cols = [
        "product_detail_uv",
        "sales_volume",
        "customer_count",
        "new_customer_count",
        "marketing_new_customer_count",
        "add_to_cart_users",
    ]
    for c in int_cols:
        df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0).astype(int)

    num_cols = [
        "product_detail_uv_ratio",
        "sales_amount",
        "sales_ratio",
        "purchase_conversion_rate",
        "unit_price",
        "product_detail_uv_value",
        "arpu",
    ]
    for c in num_cols:
        df[c] = pd.to_numeric(df[c], errors="coerce")

    df["daily"] = daily_val

    records = []
    for _, r in df.iterrows():
        records.append(
            (
                None if pd.isna(r["traffic_type"]) else str(r["traffic_type"]),
                None if pd.isna(r["primary_entry"]) else str(r["primary_entry"]),
                None if pd.isna(r["secondary_entry"]) else str(r["secondary_entry"]),
                int(r["product_detail_uv"]),
                None if pd.isna(r["product_detail_uv_ratio"]) else float(r["product_detail_uv_ratio"]),
                None if pd.isna(r["sales_amount"]) else float(r["sales_amount"]),
                None if pd.isna(r["sales_ratio"]) else float(r["sales_ratio"]),
                int(r["sales_volume"]),
                None if pd.isna(r["purchase_conversion_rate"]) else float(r["purchase_conversion_rate"]),
                None if pd.isna(r["unit_price"]) else float(r["unit_price"]),
                int(r["customer_count"]),
                int(r["new_customer_count"]),
                int(r["marketing_new_customer_count"]),
                int(r["add_to_cart_users"]),
                None if pd.isna(r["product_detail_uv_value"]) else float(r["product_detail_uv_value"]),
                None if pd.isna(r["arpu"]) else float(r["arpu"]),
                daily_val,
            )
        )

    conn = pymysql.connect(
        host="192.168.138.182",
        port=3306,
        user="ceptest",
        password="ck@123456",
        database="ckc_cep_db_test",
        charset="utf8mb4",
        autocommit=False,
    )

    old_table = "cep_traffic_summary_202601"
    create_sql = f"""
CREATE TABLE IF NOT EXISTS `{table}` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `traffic_type` VARCHAR(64) NULL,
  `primary_entry` VARCHAR(64) NULL,
  `secondary_entry` VARCHAR(64) NULL,
  `product_detail_uv` INT NULL,
  `product_detail_uv_ratio` DECIMAL(7,2) NULL,
  `sales_amount` DECIMAL(18,2) NULL,
  `sales_ratio` DECIMAL(7,2) NULL,
  `sales_volume` INT NULL,
  `purchase_conversion_rate` DECIMAL(7,2) NULL,
  `unit_price` DECIMAL(12,2) NULL,
  `customer_count` INT NULL,
  `new_customer_count` INT NULL,
  `marketing_new_customer_count` INT NULL,
  `add_to_cart_users` INT NULL,
  `product_detail_uv_value` DECIMAL(12,2) NULL,
  `arpu` DECIMAL(12,2) NULL,
  `daily` CHAR(7) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_daily` (`daily`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

    insert_sql = f"""
INSERT INTO `{table}` (
  traffic_type, primary_entry, secondary_entry,
  product_detail_uv, product_detail_uv_ratio,
  sales_amount, sales_ratio, sales_volume, purchase_conversion_rate,
  unit_price, customer_count, new_customer_count,
  marketing_new_customer_count, add_to_cart_users,
  product_detail_uv_value, arpu, daily
) VALUES (
  %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s
)
"""

    try:
        with conn.cursor() as cur:
            cur.execute(f"SHOW TABLES LIKE '{old_table}'")
            old_exists = cur.fetchone() is not None
            cur.execute(f"SHOW TABLES LIKE '{table}'")
            new_exists = cur.fetchone() is not None
            if old_exists and not new_exists and old_table != table:
                cur.execute(f"RENAME TABLE `{old_table}` TO `{table}`")
            cur.execute(create_sql)
            cur.execute(f"DELETE FROM `{table}` WHERE daily=%s", (daily_val,))
            cur.executemany(insert_sql, records)
            cur.execute(f"SELECT COUNT(*) FROM `{table}` WHERE daily=%s", (daily_val,))
            count_daily = cur.fetchone()[0]
        conn.commit()
        print(f"TABLE={table}")
        print(f"EXCEL={excel_path}")
        print(f"INSERTED={len(records)}")
        print(f"COUNT_DAILY={count_daily}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()
