#!/usr/bin/env python3
"""扫描三个库中所有"日期样"字符列，判定是否混存带横线/无横线两种格式。

用途：为 P0-6（trade_date 同列混存两格式）确定迁移影响面。
只做探测（每条 LIMIT 1），不修改任何数据。

用法：
    set -a; . ~/.config/findatahub/env; set +a
    python3 Scripts/bin/audit_date_format.py
"""
import os
import sys
from collections import Counter

import pymysql

SCHEMAS = ["tushare_data", "akshare_data", "fin_data_clean"]


def main() -> int:
    pwd = os.environ.get("MYSQL_PASSWORD")
    if not pwd:
        print("✗ 需要 MYSQL_PASSWORD 环境变量")
        return 2

    conn = pymysql.connect(
        host="127.0.0.1", port=3306, user=os.environ.get("MYSQL_USER", "root"),
        password=pwd, charset="utf8mb4", connect_timeout=5, read_timeout=300,
    )
    cur = conn.cursor()
    # 用占位符绑定，避免 SQL 里的 % 与格式化冲突
    cur.execute(
        "SELECT table_schema, table_name, column_name, data_type, collation_name "
        "FROM information_schema.columns "
        "WHERE table_schema IN (%s, %s, %s) "
        "AND data_type IN ('varchar', 'char') "
        "AND (column_name LIKE %s OR column_name LIKE %s) "
        "ORDER BY table_schema, table_name, column_name",
        SCHEMAS + ["%date%", "%_dt%"],
    )
    cols = cur.fetchall()
    print(f"候选日期样字符列：{len(cols)} 个")

    mixed, dashed_only, plain_only, skipped = [], [], [], []
    for sch, tbl, col, dtype, coll in cols:
        try:
            cur.execute(f"SELECT 1 FROM `{sch}`.`{tbl}` WHERE `{col}` LIKE '%-%' LIMIT 1")
            has_dash = cur.fetchone() is not None
            cur.execute(
                f"SELECT 1 FROM `{sch}`.`{tbl}` "
                f"WHERE `{col}` NOT LIKE '%-%' AND `{col}` <> '' LIMIT 1"
            )
            has_plain = cur.fetchone() is not None
        except Exception as e:                       # noqa: BLE001
            skipped.append((f"{sch}.{tbl}.{col}", str(e)[:70]))
            continue
        label = f"{sch}.{tbl}.{col}  [{coll or '-'} {dtype}]"
        if has_dash and has_plain:
            mixed.append(label)
        elif has_dash:
            dashed_only.append(label)
        elif has_plain:
            plain_only.append(label)
        else:
            skipped.append((f"{sch}.{tbl}.{col}", "空列"))

    def show(title, rows, limit=45):
        print(f"\n── {title}（{len(rows)}）──")
        for r in rows[:limit]:
            print(f"    {r}")
        if len(rows) > limit:
            print(f"    …另 {len(rows) - limit} 项")

    show("★ 同列混存两种格式（必须迁移）", mixed)
    show("仅带横线（统一口径时也要改）", dashed_only)
    show("仅无横线（已统一，无需改）", plain_only, limit=8)
    if skipped:
        show("跳过/异常", [f"{p}  → {e}" for p, e in skipped], limit=8)

    print("\n── 涉及列的 collation 分布 ──")
    for k, v in Counter(
        r.split("[")[1].split()[0] for r in (mixed + dashed_only + plain_only)
    ).items():
        print(f"    {k}: {v}")

    conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())