#!/usr/bin/env python3
"""
批量下载并导入钉盘库存CSV（20260531版本）
流程：
  1. dws doc list 获取所有文件列表（已完成）
  2. 按文件名匹配仓库编码，下载每个文件
  3. 解析CSV（GBK→UTF-8），合并写入 inventory_detail
  4. 清空旧数据，插入新数据
"""
import json, subprocess, csv, io, os, re
from config import DB_CONFIG
import pymysql
from datetime import datetime

FOLDER_ID = "NkDwLng8ZLGrXmrnFxYq3vRqVKMEvZBY"
DATA_DATE = "2026-05-31"

def dws(args):
    result = subprocess.run(["dws"] + args.split(), capture_output=True, text=True, timeout=60)
    return json.loads(result.stdout)

def main():
    # 1. 列出文件夹中所有文件
    print("📋 获取文件列表...")
    result = dws(f"doc list --folder {FOLDER_ID}")
    files = []
    for n in result['nodes']:
        name = n['name']
        ext = n.get('extension', '')
        if ext != 'csv':
            continue
        # 提取仓库编码（文件名不包含扩展名）
        m = re.search(r'-(\d+)$', name)
        if not m:
            continue
        wh_code = m.group(1)
        if len(wh_code) == 1:
            wh_code = '0' + wh_code
        files.append({
            'name': name,
            'node_id': n['nodeId'],
            'wh_code': wh_code,
            'date_part': name[:8] if len(name) >= 8 else ''
        })

    # 只处理最新日期的文件
    new_files = [f for f in files if f['date_part'] == '20260531']
    print(f"找到 {len(new_files)} 个新文件（20260531）")

    conn = pymysql.connect(**DB_CONFIG)
    cur = conn.cursor()

    total_rows = 0
    all_rows = []

    for f in new_files:
        print(f"  ⬇️ 下载 {f['name']} (仓库{f['wh_code']})...")
        dl = dws(f"doc download --node {f['node_id']}")
        url = dl.get('resourceUrl', dl.get('cdnUrl', ''))
        if not url:
            print(f"    ❌ 无下载链接")
            continue

        resp = subprocess.run(["curl", "-s", url], capture_output=True, timeout=120)
        raw = resp.stdout

        # 尝试GBK解码
        try:
            text = raw.decode('gbk')
        except:
            try:
                text = raw.decode('utf-8-sig')
            except:
                text = raw.decode('utf-8', errors='ignore')

        # 跳过BOM
        if text.startswith('\ufeff'):
            text = text[1:]

        # 解析CSV
        reader = csv.DictReader(io.StringIO(text))
        count = 0
        for row in reader:
            # 字段可能有BOM前缀，清洗
            cleaned = {}
            for k, v in row.items():
                k2 = k.strip().replace('\ufeff', '').replace('"', '')
                cleaned[k2] = (v or '').strip()

            product_code = cleaned.get('货品编号', cleaned.get('商品编码', '')).strip()
            product_name = cleaned.get('货品名称', '')
            brand = cleaned.get('品牌', '')
            cost_price = cleaned.get('成本价', cleaned.get('单价', '0'))
            available_qty = cleaned.get('可用库存', '0')
            prod_transit = cleaned.get('生产在途', '0')
            purch_transit = cleaned.get('采购在途量', cleaned.get('采购在途', '0'))
            category1 = cleaned.get('一级类目', '')
            category2 = cleaned.get('二级类目', '')
            category3 = cleaned.get('三级类目', '')
            created_time = cleaned.get('单品创建时间', cleaned.get('创建时间', ''))

            if not product_code:
                continue

            all_rows.append((
                DATA_DATE, '旺店通', f['wh_code'],
                product_code, product_code, product_name, brand,
                float(cost_price) if cost_price else 0,
                int(float(available_qty)) if available_qty else 0,
                int(float(prod_transit)) if prod_transit else 0,
                int(float(purch_transit)) if purch_transit else 0,
                category1, category2, category3,
                created_time
            ))
            count += 1
        total_rows += count
        print(f"    ✅ {count} 条")

    print(f"\n📊 共读取 {total_rows} 条数据")

    # 2. 清空旧数据（该日期的）
    cur.execute("DELETE FROM inventory_detail WHERE data_date=%s", (DATA_DATE,))
    deleted = cur.rowcount
    print(f"🗑️ 已清空 {DATA_DATE} 旧数据: {deleted} 条")

    # 3. 批量插入
    if total_rows > 0:
        insert_sql = """INSERT INTO inventory_detail 
            (data_date, source, warehouse_code, goods_code, product_code, 
             product_name, brand, cost_price, available_qty, 
             production_in_transit, purchase_in_transit,
             category1, category2, category3, created_time)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
        
        batch_size = 500
        inserted = 0
        for i in range(0, len(all_rows), batch_size):
            batch = all_rows[i:i+batch_size]
            cur.executemany(insert_sql, batch)
            conn.commit()
            inserted += len(batch)
            if inserted % 2000 == 0:
                print(f"  💾 已写入 {inserted}/{total_rows}")

        print(f"✅ 写入完成: {inserted} 条")

    # 4. 统计
    cur.execute("SELECT COUNT(*) FROM inventory_detail WHERE data_date=%s", (DATA_DATE,))
    final_count = cur.fetchone()[0]
    print(f"\n📈 {DATA_DATE} 库存数据共 {final_count} 条")

    conn.close()

if __name__ == "__main__":
    main()