#!/usr/bin/env python3
"""导入包材下单表到数据库"""
import os, sys, re
import openpyxl
import pymysql
from config import DB_CONFIG

EXCEL_PATH = "/home/gxd/.openclaw/workspace-dingtalk-assistant/media/inbound/包材下单表（跟单）6-17-1781683171953.xlsx"

COLUMNS = [
    ("日期", "DATE"),
    ("编码", "VARCHAR(100)"),
    ("名称", "VARCHAR(500)"),
    ("单价", "DECIMAL(12,4)"),
    ("数量", "INT"),
    ("合计", "DECIMAL(14,2)"),
    ("供应商", "VARCHAR(200)"),
    ("仓库到货", "VARCHAR(200)"),
    ("进度", "TEXT"),
    ("是否已做采购单", "VARCHAR(50)"),
    ("是否已做入库", "VARCHAR(50)"),
    ("采购下单人", "VARCHAR(100)"),
    ("旺店通编号", "VARCHAR(100)"),
    ("是否已录入旺店通", "VARCHAR(50)"),
    ("到货日期", "VARCHAR(50)"),
    ("状态", "VARCHAR(50)"),
    ("做货天数", "VARCHAR(50)"),
    ("包材开发", "VARCHAR(200)"),
]

CREATE_SQL = """
CREATE TABLE IF NOT EXISTS packaging_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    {fields},
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

def main():
    conn = pymysql.connect(**DB_CONFIG)
    cur = conn.cursor()
    
    field_defs = ", ".join([f"`{name}` {dtype} DEFAULT NULL" for name, dtype in COLUMNS])
    cur.execute(CREATE_SQL.format(fields=field_defs))
    print("✅ 表 packaging_orders 已就绪")
    
    cur.execute("TRUNCATE TABLE packaging_orders")
    print("✅ 已清空旧数据")
    
    wb = openpyxl.load_workbook(EXCEL_PATH, data_only=True)
    ws = wb.active
    
    headers = []
    for col in range(1, ws.max_column + 1):
        val = ws.cell(row=1, column=col).value
        headers.append(str(val).strip() if val else "")
    
    expected = [c[0] for c in COLUMNS]
    mismatch = []
    for i, (h, e) in enumerate(zip(headers, expected)):
        if h != e:
            mismatch.append(f"  列{i+1}: 期望'{e}' 实际'{h}'")
    if mismatch:
        print("⚠️  表头不匹配：")
        for m in mismatch:
            print(m)
        return
    
    col_types = [c[1] for c in COLUMNS]
    
    def clean_val(val, dtype):
        if val is None: return None
        s = str(val).strip()
        if not s: return None
        if dtype.startswith('INT'):
            nums = re.sub(r'[^0-9\-]', '', s)
            if nums and nums != '-':
                try: return int(nums)
                except: return None
            return None
        if dtype.startswith('DECIMAL'):
            clean = re.sub(r'[^0-9\.\-]', '', s)
            if clean and clean != '.':
                try: return float(clean)
                except: return None
            return None
        if dtype == 'DATE':
            date_match = re.match(r'(\d{4})[\-\/](\d{1,2})[\-\/](\d{1,2})', s)
            if date_match:
                return f"{date_match.group(1)}-{int(date_match.group(2)):02d}-{int(date_match.group(3)):02d}"
            return None if not s else s
        return s if s else None
    
    placeholders = ", ".join(["%s"] * len(COLUMNS))
    col_names = ", ".join([f"`{c[0]}`" for c in COLUMNS])
    insert_sql = f"INSERT INTO packaging_orders ({col_names}) VALUES ({placeholders})"
    
    total = 0
    batch = []
    for row_idx in range(2, ws.max_row + 1):
        row_data = []
        has_data = False
        for col_idx in range(1, len(COLUMNS) + 1):
            val = ws.cell(row=row_idx, column=col_idx).value
            cleaned = clean_val(val, col_types[col_idx - 1])
            row_data.append(cleaned)
            if cleaned is not None:
                has_data = True
        if not has_data:
            continue
        batch.append(tuple(row_data))
        total += 1
        if len(batch) >= 500:
            cur.executemany(insert_sql, batch)
            conn.commit()
            print(f"  已导入 {total} 行...")
            batch = []
    
    if batch:
        cur.executemany(insert_sql, batch)
        conn.commit()
    
    print(f"✅ 导入完成，共 {total} 行")
    
    cur.execute("SELECT COUNT(*) FROM packaging_orders")
    print(f"📊 数据库记录数: {cur.fetchone()[0]}")
    
    cur.execute("SELECT `日期`,`编码`,`名称`,`数量`,`供应商`,`状态` FROM packaging_orders WHERE `日期` IS NOT NULL ORDER BY `日期` DESC LIMIT 5")
    print("\n最新记录：")
    for r in cur.fetchall():
        print(f"  {r[0]} | {r[1]} | {r[2][:25]}... | {r[3]}件 | {r[4]} | {r[5]}")
    
    conn.close()

if __name__ == "__main__":
    main()