#!/usr/bin/env python3
"""
导入采购订单参考表 → 更新 products 表的 supplier_name_match 和 cost_price
匹配规则:
  1. 物料编码 精确匹配 product_code
  2. 若未命中，去掉已知后缀后再匹配
"""
import csv
import pymysql
import sys

CSV_FILE = "/home/gxd/.openclaw/workspace-dingtalk-assistant/media/inbound/采购订单-20260531-1780213945661.csv"

# 已知后缀（顺序重要：长后缀优先）
SUFFIXES = [
    '-FM/GZ', '-TMGJ-NL', '-ALJK-BT', '-ALJK-FM/GZ',
    '-TM-BT', '-TM-PZ', '-TM-CH', '-TM-BQ', '-TM-SSM',
    '-TM-HMT', '-TM-RG', '-TM-GZ', '-TM-FM/GZ',
    '-PDD-BT', '-PDD-PZ', '-PDD-CH', '-PDD-HMT',
    '-NL', '-BT', '-PZ', '-CH', '-BQ', '-SSM', '-HMT',
    '-RG', '-GZ', '-JD', '-PDD', '-ALJK', '-TM',
    '-WPH', '-DW', '-WPH-BT', '-WPH-PZ', '-WPH-CH', '-WPH-BQ',
    '-WPH-MD', '-WPH-RG',
]

def strip_suffix(code):
    """去掉已知后缀，返回 base_code；没有匹配返回原 code"""
    code = code.strip()
    for s in SUFFIXES:
        if code.endswith(s):
            base = code[:-len(s)]
            if base:
                return base
    return code


def main():
    conn = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="dy_app",
        password="dy_data_2026",
        database="dy_data",
        charset="utf8mb4"
    )
    cur = conn.cursor()

    # 加载所有 product_code → id 映射
    cur.execute("SELECT product_code, id FROM products")
    all_products = {}
    exact_index = {}
    for code, pid in cur.fetchall():
        code = code.strip()
        all_products[code] = pid
        exact_index[code] = pid

    print(f"DB 中产品总数: {len(all_products)}")

    # 读取 CSV
    csv_rows = []
    with open(CSV_FILE, encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        for row in reader:
            code = row["物料编码"].strip()
            supplier = row["供应商"].strip()
            price_str = row["含税单价"].strip()
            try:
                price = float(price_str) if price_str else None
            except ValueError:
                price = None
            csv_rows.append({
                "code": code,
                "supplier": supplier,
                "price": price,
            })

    print(f"CSV 总行数: {len(csv_rows)}")
    print(f"CSV 唯一物料编码数: {len(set(r['code'] for r in csv_rows))}")

    # 统计匹配
    exact_hits = 0
    prefix_hits = 0
    no_hits = 0

    # 批量更新
    update_sql = "UPDATE products SET supplier_name_match=%s, cost_price=%s WHERE id=%s"
    updates = []
    seen_products = set()

    for row in csv_rows:
        code = row["code"]
        # 1. 精确匹配
        if code in exact_index:
            pid = exact_index[code]
            match_type = "exact"
        else:
            # 2. 剥离后缀后匹配
            base = strip_suffix(code)
            if base in exact_index:
                pid = exact_index[base]
                match_type = "prefix"
            else:
                # no match
                no_hits += 1
                continue

        if match_type == "exact":
            exact_hits += 1
        else:
            prefix_hits += 1

        # 同一个 product 可能被 CSV 多行命中；保留最后一条（或可按需取最小价格）
        updates.append((row["supplier"], row["price"], pid))
        seen_products.add(pid)

    print(f"\n======== 匹配结果 ========")
    print(f"精确匹配: {exact_hits}")
    print(f"后缀剥离后匹配: {prefix_hits}")
    print(f"未匹配: {no_hits}")
    print(f"涉及去重后产品数: {len(seen_products)}")

    # 执行更新
    updated = 0
    batch_size = 500
    for i in range(0, len(updates), batch_size):
        batch = updates[i:i+batch_size]
        cur.executemany(update_sql, batch)
        conn.commit()
        updated += len(batch)
        print(f"  已更新 {updated}/{len(updates)}")

    conn.close()
    print(f"\n✅ 完成！共更新 {updated} 条记录")


if __name__ == "__main__":
    main()