THE/web.py

from flask import Flask, render_template, request, jsonify, send_file, send_from_directory
import pandas as pd
from openpyxl import load_workbook
import os
import shutil
import requests
import json
import re
import time
from datetime import datetime
from collections import Counter

app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 100 * 1024 * 1024

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
UPLOAD_FOLDER = os.path.join(BASE_DIR, 'uploads')
TEMPLATES_FOLDER = os.path.join(BASE_DIR, 'templates_excel')
OUTPUT_FOLDER = os.path.join(BASE_DIR, 'output')
EXAMPLE_FOLDER = os.path.join(BASE_DIR, 'example_templates')

for folder in [UPLOAD_FOLDER, TEMPLATES_FOLDER, OUTPUT_FOLDER, EXAMPLE_FOLDER]:
    if not os.path.exists(folder):
        os.makedirs(folder)

AI_CONFIG = {
    'api_url': 'https://ark.cn-beijing.volces.com/api/v3/responses',
    'authorization': 'Bearer ark-f7930e50-4837-4f8f-96b4-bd2f56313300-b9dd7',
    'vision_model': 'doubao-seed-2-1-pro-260628',
    'extract_model': 'deepseek-v4-pro-260425',
}

# AI 排除字段（这些字段永远不交给 AI 填写）
# 全局排除字段（Amazon & Wayfair 都不填）
AI_EXCLUDED_FIELDS = {
    # === 原排除字段 ===
    'Amazon Seller SKU', 'Universal Product Code', 'Collection Name',
    'Manufacturer Product URL', 'Group Reference ID',
    'Variant Grouping 3', 'Variant Attribute Name On Site 3',
    'Minimum Advertised Price', 'Everyday B2B Discount Rate', 'Marketing Copy',
    'Freight Class', 'National Motor Freight Class', 'Ship Palletized',
    'Document Type 1', 'Legal Document Type 1', 'Document Region 1', 'Document Language 1',
    'Document File Name or URL 2', 'Document Type 2', 'Legal Document Type 2',
    'Document Region 2', 'Document Language 2',
    'Document File Name or URL 3', 'Document Type 3', 'Legal Document Type 3',
    'Document Region 3', 'Document Language 3',
    'Chemical 1', 'Country of Origin - Additional Details',
    'Sustainability & Social Responsibility Certifications (North America Only)', 'Toxicity 1',

    # === Amazon 不需要填写的字段（你提供的列表） ===
    'Product Id', 'Package Contains SKU Identifier', 'Construction Type', 'Number of Pieces',
    'Weave Type', 'Surface Recommendation', 'Material Features', 'Pile Height', 'Orientation',
    'Pattern', 'Finish Type', 'Product Site Launch Date', 'League Name', 'Team Name',
    'Back Material Type', 'Embellishment Feature', 'Item Thickness', 'Item Thickness Unit',
    'Animal Theme', 'Rug Form Type', 'Letter Character', 'Government Contract Name',
    'Government Contract Number', 'Collection Item', 'Lifting Mechanism', 'Product Grade',
    'Drawer Size', 'Drawer Weight Capacity', 'Drawer Weight Capacity Unit',
    'Keyboard Tray Length', 'Keyboard Tray Length Unit', 'Keyboard Tray Width',
    'Keyboard Tray Width Unit', 'Has Keyboard Tray', 'Sport Type', 'Hand Orientation',
    'Model Variant', 'Seat Height', 'Seat Height Unit', 'Seat Material', 'Seat Width',
    'Seat Width Unit', 'Specification Met', 'Plastic Type', 'Release Date', 'Metal Finish',
    'Handle Finish Type', 'Athlete', 'Top Style', 'Minimum Required Door Width',
    'Minimum Required Door Width Unit', 'Umbrella Hole Diameter', 'Umbrella Hole Diameter Unit',
    'Skip Offer', 'Offer Condition Note', 'Product Tax Code', 'Merchant Release Date',
    'Main Image Location', 'Other Image Location', 'Battery Life Percentage', 'Cosmetic',
    'Features', 'Functional Condition', 'Source Type', 'Import Designation', 'Restock Date (US)',
    'Your Price USD (Sell on Amazon, US)', 'Pricing Rule (Sell on Amazon, US)',
    'Minimum Seller Allowed Price (Sell on Amazon, US)',
    'Maximum Seller Allowed Price (Sell on Amazon, US)',
    'Sale Price USD (Sell on Amazon, US)', 'Sale Start Date (Sell on Amazon, US)',
    'Sale End Date (Sell on Amazon, US)', 'Offering Release Date (Sell on Amazon, US)',
    'Stop Selling Date (Sell on Amazon, US)', 'Master Pack Layers per Pallet Quantity',
    'Master Packs Per Layer Quantity', 'Minimum Height', 'Minimum Height Unit', 'Safety Warning',
    'Radio Frequency Emission & Authorization Status', 'FCC ID', 'SDoC Contact Name',
    'SDoC Contact US Mailing Address', 'SDoC Contact Email Address',
    'SDOC Contact US Phone Number', 'Compliance Regulation Type', 'Regulatory Identification',
    'Responsible Person\'s Email or Electronic Address',
    'Compliance Media Source Location (en_US, Application Guide)',
    'Compliance Media Source Location (en_US, Certificate of Analysis)',
    'Compliance Media Source Location (en_US, Certificate of Compliance)',
    'Compliance Media Source Location (en_US, Compatibility Guide)',
    'Compliance Media Source Location (en_US, Data Act Transparency Declaration)',
    'Compliance Media Source Location (en_US, Emergency Use Authorization)',
    'Compliance Media Source Location (en_US, Emergency Use Authorization Amendment)',
    'Compliance Media Source Location (en_US, Installation Manual)',
    'Compliance Media Source Location (en_US, Instructions for Use)',
    'Compliance Media Source Location (en_US, Patient Fact Sheet)',
    'Compliance Media Source Location (en_US, Product Certificate of Conformity)',
    'Compliance Media Source Location (en_US, Provider Fact Sheet)',
    'Compliance Media Source Location (en_US, Safety Data Sheet)',
    'Compliance Media Source Location (en_US, Safety Information)',
    'Compliance Media Source Location (en_US, Specification Sheet)',
    'Compliance Media Source Location (en_US, Troubleshooting Guide)',
    'Compliance Media Source Location (en_US, User Guide)',
    'Compliance Media Source Location (en_US, User Manual)',
    'Compliance Media Source Location (en_US, Warranty)', 'Contains PFAS',
    'GHS Chemical H Code', 'BAA/TAA Regulation Compliance', 'BAA TAA Compliance Acknowledgement',
    'TAA Compliant Country', 'Battery Cell Composition', 'Battery Cell Composition Other Than Listed',
    'Battery IEC Code', 'Battery Weight', 'Battery Weight Unit', 'Number of Batteries',
    'Battery Type', 'Number of Lithium Metal Cells', 'Number of Lithium-ion Cells',
    'Lithium Battery Energy Content', 'Lithium Battery Energy Content Unit',
    'Lithium Battery Packaging', 'Lithium Battery Weight', 'Lithium Battery Weight Unit',
    'GHS Class', 'Hazmat Aspect', 'Hazmat', 'Safety Data Sheet (SDS or MSDS) URL',
    'Mfg Warranty Type (i.e. Parts, Labor)', 'California Proposition 65 Warning Type',
    'California Proposition 65 Chemical Name(s)', 'Pesticide Marking',
    'Pesticide Registration Status', 'Pesticide Certification Number',
    'Has Multiple Battery Powered Components', 'Contains Battery or Cell',
    'Battery Contains free Unabsorbed Liquid', 'Is Battery Non Spillable',
    'Non-Lithium Battery Packaging', 'Has Replaceable Battery',
    'Non-Lithium Battery Energy Content', 'Non-Lithium Battery Energy Content Unit',
    'Has Less than 30 Percent State of Charge', 'Battery Installation Device Type', 'Value',
    'Item Weight Unit', 'Country as Labeled', 'Product Compliance Certificate',
    'Regulatory Organization Name', 'Compliance Certification Status',
    'Compliance Certification Value', 'Recycled Content Percentage', 'Swatch Image URL',
}

AI_FIELD_MAPPING = {
    'Table Top Shape': 'Table Top Shape',
    'Coffee Table Lift Top': 'Coffee Table Lift Top',
    'Upholstered': 'Upholstered',
    'Plug-In': 'Plug-In',
    'Storage Included': 'Storage Included',
    'Shelves Included': 'Shelves Included',
    'Drawers Included': 'Drawers Included',
    'Cabinets Included': 'Cabinets Included',
    'Decal/Laminate Design': 'Decal/Laminate Design',
    'Set Type': 'Set Type',
    'Durability': 'Durability',
    'Number of Tables Included': 'Number of Tables Included',
    'CAL TB 117-2013 Compliant': 'CAL TB 117-2013 Compliant',
    'SOFFA Compliant': 'SOFFA Compliant',
}

ai_logs = []

def add_ai_log(message, level='info'):
    timestamp = datetime.now().strftime('%H:%M:%S')
    log_entry = {'time': timestamp, 'message': message, 'level': level}
    ai_logs.append(log_entry)
    print(f"[AI] {timestamp} - {message}")
    if len(ai_logs) > 500:
        ai_logs.pop(0)

def get_ai_logs():
    return ai_logs

def clear_ai_logs():
    global ai_logs
    ai_logs = []

TEMPLATE_CONFIG = {
    'wayfair': {
        'name': 'Wayfair',
        'source_header': 2,
        'template_header': 4,
        'label': 'Wayfair'
    },
    'amazon': {
        'name': '亚马逊',
        'source_header': 2,
        'template_header': 4,
        'label': '亚马逊'
    }
}

def load_valid_values(wb):
    valid_data = {}
    if "Valid Values" not in wb.sheetnames:
        return valid_data
    ws_valid = wb["Valid Values"]
    for row in range(1, ws_valid.max_row + 1):
        # 智能检测字段名所在列：优先第1列，为空则取第2列
        field_name = ws_valid.cell(row=row, column=1).value
        start_col = 2  # 值从第2列开始读
        
        if not field_name:
            field_name = ws_valid.cell(row=row, column=2).value
            start_col = 3  # 字段名在第2列，值从第3列开始
        
        if not field_name:
            continue
            
        field_name = str(field_name).strip()
        # 去掉 " - [ XXX ]" 后缀，如 "Product Type - [ UTILITY_CART_WAGON ]" → "Product Type"
        field_name = re.sub(r'\s*-\s*\[.*?\]\s*$', '', field_name)
        
        if not field_name:
            continue
            
        if field_name not in valid_data:
            valid_data[field_name] = []
            
        for col in range(start_col, ws_valid.max_column + 1):
            val = ws_valid.cell(row=row, column=col).value
            if val:
                val_str = str(val).strip()
                if val_str and val_str not in valid_data[field_name]:
                    valid_data[field_name].append(val_str)
    return valid_data

def get_valid_value(valid_values, field_name, default=None):
    if field_name in valid_values and valid_values[field_name]:
        return valid_values[field_name][0]
    return default

def call_vision_api(image_url, row_index):
    add_ai_log(f"🖼️ [第{row_index}行] 开始识别图片: {image_url}", 'info')
    headers = {
        'Accept': '*/*',
        'Accept-Encoding': 'gzip, deflate, br',
        'Authorization': AI_CONFIG['authorization'],
        'Connection': 'keep-alive',
        'Content-Type': 'application/json',
        'User-Agent': 'PostmanRuntime-ApipostRuntime/1.1.0'
    }
    data = {
        "model": AI_CONFIG['vision_model'],
        "input": [
            {
                "role": "user",
                "content": [
                    {"type": "input_image", "image_url": image_url},
                    {"type": "input_text", "text": "请详细描述这张图片中的商品，包括但不限于：形状、颜色、材质、结构、功能特点、适用场景。描述要详细具体。"}
                ]
            }
        ]
    }
    add_ai_log(f"📤 [第{row_index}行] 调用视觉模型 ({AI_CONFIG['vision_model']})...", 'info')
    start_time = time.time()
    try:
        response = requests.post(AI_CONFIG['api_url'], headers=headers, json=data, timeout=360)
        elapsed = time.time() - start_time
        add_ai_log(f"⏱️ [第{row_index}行] 视觉识别耗时: {elapsed:.1f}秒", 'info')
        result = response.json()
        if 'output' in result:
            for item in result['output']:
                if item.get('type') == 'message' and 'content' in item:
                    for content in item['content']:
                        if content.get('type') == 'output_text':
                            text = content.get('text', '')
                            add_ai_log(f"✅ [第{row_index}行] 视觉识别成功，描述长度: {len(text)} 字符", 'success')
                            return text
        add_ai_log(f"⚠️ [第{row_index}行] 视觉识别返回格式异常", 'warn')
        return None
    except requests.exceptions.Timeout:
        add_ai_log(f"❌ [第{row_index}行] 视觉识别超时 (360秒)", 'error')
        return None
    except Exception as e:
        add_ai_log(f"❌ [第{row_index}行] 视觉识别失败: {str(e)}", 'error')
        return None

def call_extract_api(description, row_index, fields):
    add_ai_log(f"🧠 [第{row_index}行] 开始属性提取，候选字段 {len(fields)} 个...", 'info')
    headers = {
        'Accept': '*/*',
        'Accept-Encoding': 'gzip, deflate, br',
        'Authorization': AI_CONFIG['authorization'],
        'Connection': 'keep-alive',
        'Content-Type': 'application/json',
        'User-Agent': 'PostmanRuntime-ApipostRuntime/1.1.0'
    }
    fields_str = ', '.join(fields)
    data = {
        "model": AI_CONFIG['extract_model'],
        "stream": False,
        "tools": [{"type": "web_search", "max_keyword": 3}],
        "input": [
            {
                "role": "user",
                "content": [
                    {
                        "type": "input_text",
                        "text": f"""请根据下面的商品图片识别结果，尽可能填写以下字段。对于每个字段，请根据商品描述推断最合适的值。

需要填写的字段列表：
{fields_str}

返回格式要求：每个属性用方括号括起来，格式为 [属性名:值]
例如：[Table Top Shape:Rectangle with rounded corners] [Coffee Table Lift Top:No]

如果描述中没有相关信息，请填写 "Unknown"。回答的[属性名:值]都用英文。

商品描述：
{description}"""
                    }
                ]
            }
        ]
    }
    add_ai_log(f"📤 [第{row_index}行] 调用提取模型 ({AI_CONFIG['extract_model']})...", 'info')
    start_time = time.time()
    try:
        response = requests.post(AI_CONFIG['api_url'], headers=headers, json=data, timeout=360)
        elapsed = time.time() - start_time
        add_ai_log(f"⏱️ [第{row_index}行] 属性提取耗时: {elapsed:.1f}秒", 'info')
        result = response.json()
        if 'output' in result:
            for item in result['output']:
                if item.get('type') == 'message' and 'content' in item:
                    for content in item['content']:
                        if content.get('type') == 'output_text':
                            text = content.get('text', '')
                            add_ai_log(f"✅ [第{row_index}行] 属性提取成功，结果长度: {len(text)} 字符", 'success')
                            return text
        add_ai_log(f"⚠️ [第{row_index}行] 属性提取返回格式异常", 'warn')
        return None
    except requests.exceptions.Timeout:
        add_ai_log(f"❌ [第{row_index}行] 属性提取超时 (360秒)", 'error')
        return None
    except Exception as e:
        add_ai_log(f"❌ [第{row_index}行] 属性提取失败: {str(e)}", 'error')
        return None

def parse_ai_result(text):
    result = {}
    if not text:
        return result
    pattern = r'\[([^:\]]+):([^\]]+)\]'
    matches = re.findall(pattern, text)
    for field, value in matches:
        field = field.strip()
        value = value.strip()
        if value and value not in ['Unknown', 'Not Applicable', 'N/A', '']:
            result[field] = value
    return result

def get_ai_filled_data(image_url, row_index, fields):
    add_ai_log(f"🚀 [第{row_index}行] 开始AI处理，候选字段数: {len(fields)}", 'info')
    add_ai_log(f"📷 [第{row_index}行] 图片URL: {image_url}", 'info')
    description = call_vision_api(image_url, row_index)
    if not description:
        add_ai_log(f"⚠️ [第{row_index}行] 图片识别失败，跳过该行", 'warn')
        return {}
    add_ai_log(f"📝 [第{row_index}行] 图片描述预览: {description}", 'info')
    extract_result = call_extract_api(description, row_index, fields)
    if not extract_result:
        add_ai_log(f"⚠️ [第{row_index}行] 属性提取失败，跳过该行", 'warn')
        return {}
    parsed = parse_ai_result(extract_result)
    if parsed:
        add_ai_log(f"📋 [第{row_index}行] 解析成功，提取到 {len(parsed)} 个字段:", 'success')
        for field, value in parsed.items():
            add_ai_log(f"   └─ {field}: {value}", 'info')
    else:
        add_ai_log(f"⚠️ [第{row_index}行] 解析结果为空", 'warn')
    return parsed

def fill_wayfair_row(src_row, source_map, col_indices, valid_values, row_num, ws, suffix="", mfr_suffix="", ai_data=None, src_row_index=0):
    fill_count = 0
    item_code = ""
    product_name = ""
    product_name_en = ""
    filled_summary = []

    try:
        item_code = src_row.get("Item Code", "")
        product_name = src_row.get("产品名称", "")
        product_name_en = src_row.get("产品英文名称", "")
        if pd.isna(item_code): item_code = ""
        if pd.isna(product_name): product_name = ""
        if pd.isna(product_name_en): product_name_en = ""

        # ==================== 1. 从 Valid Values 下拉取值的字段 ====================

        # Brand: 下拉一个即可（取第一个有效值）
        if "Brand" in col_indices:
            brand_val = get_valid_value(valid_values, "Brand", "DGpoco")
            ws.cell(row=row_num, column=col_indices["Brand"], value=brand_val)
            fill_count += 1
            filled_summary.append(f"Brand={brand_val}")

        # Variant Type: 下拉选择第3个（索引2），目标为 "Not Variant"
        variant_type_val = "Not Variant"
        if "Variant Type" in col_indices:
            vt_list = valid_values.get("Variant Type", [])
            if len(vt_list) >= 3:
                variant_type_val = vt_list[2]
            elif vt_list:
                # 若不足3个，尝试匹配 "Not Variant"，否则取第一个
                variant_type_val = next((v for v in vt_list if "not variant" in v.lower()), vt_list[0])
            ws.cell(row=row_num, column=col_indices["Variant Type"], value=variant_type_val)
            fill_count += 1
            filled_summary.append(f"VariantType={variant_type_val}")

        is_not_variant = ("not variant" in variant_type_val.lower())

        # Variant Grouping 1: 默认选 color（仅当 Variant Type 不是 Not Variant 时填写）
        if "Variant Grouping 1" in col_indices and not is_not_variant:
            vg_list = valid_values.get("Variant Details - Variant Grouping", 
                       valid_values.get("Variant Grouping", []))
            # 优先查找 color（不区分大小写），找不到则取第一个
            vg1_val = next((v for v in vg_list if v.lower() == "color"), None)
            if vg1_val is None:
                vg1_val = next((v for v in vg_list if "color" in v.lower()), 
                              vg_list[0] if vg_list else "Color")
            ws.cell(row=row_num, column=col_indices["Variant Grouping 1"], value=vg1_val)
            fill_count += 1
            filled_summary.append(f"VarGroup1={vg1_val}")

        # Variant Attribute Name On Site 1: 默认 Finish（仅当不是 Not Variant）
        if "Variant Attribute Name On Site 1" in col_indices and not is_not_variant:
            ws.cell(row=row_num, column=col_indices["Variant Attribute Name On Site 1"], value="Finish")
            fill_count += 1

        # Variant Grouping 2: 默认选 size（仅当不是 Not Variant）
        if "Variant Grouping 2" in col_indices and not is_not_variant:
            vg_list = valid_values.get("Variant Details - Variant Grouping", 
                       valid_values.get("Variant Grouping", []))
            vg2_val = next((v for v in vg_list if v.lower() == "size"), None)
            if vg2_val is None:
                vg2_val = next((v for v in vg_list if "size" in v.lower()), 
                              vg_list[0] if vg_list else "Size")
            ws.cell(row=row_num, column=col_indices["Variant Grouping 2"], value=vg2_val)
            fill_count += 1
            filled_summary.append(f"VarGroup2={vg2_val}")

        # Variant Attribute Name On Site 2: 默认 Dimensions（仅当不是 Not Variant）
        if "Variant Attribute Name On Site 2" in col_indices and not is_not_variant:
            ws.cell(row=row_num, column=col_indices["Variant Attribute Name On Site 2"], value="Dimensions")
            fill_count += 1

        # ==================== 2. 固定值字段 ====================
        fixed_values = {
            "Minimum Order Quantity": 1,
            "Force Quantity Multiplier": 1,
            "Display Set Quantity": 1,
            "Lead Time": 48,
            "Replacement Lead Time": 120,
            "Flat Pack": "yes",
            "Assembly Required": "yes",
            "Canada Product Restriction": "yes",
            "CARB Phase II Compliant (formaldehyde emissions)": "yes",
            "Commercial Warranty": "yes",
            "CANFER Compliant": "yes",
            "Commercial Warranty Length": "1years",
            "Composite Wood Product (CWP)": "yes",
            "Country Of Manufacturer": "China",
            "General Certificate of Conformity (GCC)": "yes",
            "Hazard Class(es)": "Does Not Apply",
            "Hazardous Material / Dangerous Goods": "No",
            "Hazardous Material Weight": "Does Not Apply",
            "Level of Assembly": "Full Assembly Needed",
            "Packing Group": "Does Not Apply",
            "Reason for Restriction": "Does Not Apply",
            "Supplier Intended and Approved Use": "Residential Use",
            "TSCA Title VI Compliant (formaldehyde emissions)": "yes",
            "UN or ID number": "Does Not Apply",
            "Uniform Packaging and Labeling Regulations (UPLR) Compliant": "yes",
            "Warning Required": "NO",
            "Warranty Length": "1Years",
            "Battery or Batteries Included": "No",
            "Wayfair Compliance Verified Program (including Baby Safety Alliance fka JPMA) for this product category": "Yes",
            "CPSC - 16 CFR 1303 Compliant": "Yes",
            "ADA Compliant": "Yes",
            "Safety Listing(s)": "Does Not Apply",
            "SOR/2016-193 - Surface Coating Materials Regulations": "Yes"
        }

        fixed_count = 0
        for field_name, fixed_val in fixed_values.items():
            if field_name in col_indices:
                ws.cell(row=row_num, column=col_indices[field_name], value=fixed_val)
                fill_count += 1
                fixed_count += 1
        if fixed_count > 0:
            filled_summary.append(f"固定值x{fixed_count}")

        # ==================== 3. 从 Valid Values 匹配特定文本的字段 ====================

        # Ship Type: 默认 Small Parcel
        if "Ship Type" in col_indices:
            ship_list = valid_values.get("Ship Type", [])
            ship_val = next((v for v in ship_list if "small parcel" in v.lower()), 
                           ship_list[0] if ship_list else "Small Parcel")
            ws.cell(row=row_num, column=col_indices["Ship Type"], value=ship_val)
            fill_count += 1
            filled_summary.append(f"ShipType={ship_val}")

        # ISTA Certified: 包装认证（后端无法弹窗，优先取 Valid Values 第一个值，否则留空待人工确认）
        if "ISTA Certified" in col_indices:
            ista_list = valid_values.get("ISTA Certified", [])
            ista_val = ista_list[0] if ista_list else ""
            ws.cell(row=row_num, column=col_indices["ISTA Certified"], value=ista_val)
            fill_count += 1
            if ista_val:
                filled_summary.append(f"ISTA={ista_val}")
            else:
                filled_summary.append("ISTA=待人工确认")

        # ==================== 4. 从源数据映射的字段 ====================

        # Product Type: 取产品英文名称，没有则取产品名称
        if "Product Type" in col_indices:
            pt_val = product_name_en if product_name_en else product_name
            ws.cell(row=row_num, column=col_indices["Product Type"], value=pt_val)
            fill_count += 1
            filled_summary.append(f"ProductType={str(pt_val)[:30]}")

        # ==================== 5. Wayfair 基础字段映射（颜色/材质/尺寸/重量等）====================
        wayfair_field_map = {
            # === 颜色/材质（已有）===
            "Color": "颜色",
            "Base Color": "颜色",
            "Top Color": "颜色",
            "Frame Material": "材质",
            "Base Material": "材质",
            "Top Material": "材质",
            "Furniture Leg Material": "材质",
            "Upholstery Fabric Type": "材质",
            "Material": "材质",  # ← 新增
            "Product Name": "产品名称",  # ← 添加这一行

            # === 价格（新增）===
            "Base Cost": "优惠单价",
            "Manufacturer Suggested Retail Price": "单价",
            "List Price": "优惠单价",
            "Your Price": "优惠单价",

            # === 产品尺寸/重量（已有+新增）===
            "Item Length": "组装长度",
            "Item Width": "组装宽度",
            "Item Height": "组装高度",
            "Item Weight": "产品重量",
            "Product Weight": "产品重量",  # ← 新增
            "Overall Product Weight": "产品重量",  # ← 新增

            "Overall Width - Side to Side": "组装长度",  # ← 新增（长边/横向）
            "Overall Depth - Front to Back": "组装宽度",  # ← 新增（深度/前后）
            "Overall Height - Top to Bottom": "组装高度",  # ← 新增（高度）

            "Item Length Longer Edge": "组装长度",
            "Item Width Shorter Edge": "组装宽度",
            "Length longer edge when assembled": "组装长度",
            "Width shorter edge when assembled": "组装宽度",
            "Height base to top when assembled": "组装高度",
            "Item Depth Front To Back": "组装宽度",
            "Item Height Floor To Top": "组装高度",
            "Item Width Side To Side": "组装长度",
            "Working Surface Length": "组装长度",
            "Working Surface Width": "组装宽度",

            # === 包装尺寸/重量（已有+新增）===
            "Item Package Length": "包装尺寸-长度",
            "Item Package Width": "包装尺寸-宽度",
            "Item Package Height": "包装尺寸-高度",
            "Package Weight": "包装尺寸-重量",

            "Carton Weight 1": "包装尺寸-重量",  # ← 新增
            "Carton Height 1": "包装尺寸-高度",  # ← 新增
            "Carton Width 1": "包装尺寸-宽度",  # ← 新增
            "Carton Depth 1": "包装尺寸-长度",  # ← 新增（Depth对应长度）

            "Unit Count": "包装尺寸-重量",

            # === 原产地（已有）===
            "Country of Origin": "原产地",
            "Image File Name or URL 1": "产品主图",
            "Image File Name or URL 2": "上架图片1",
            "Image File Name or URL 3": "上架图片2",
            "Image File Name or URL 4": "上架图片3",
            "Image File Name or URL 5": "上架图片4",
        }

        mapped_count = 0
        for wayfair_field, source_key in wayfair_field_map.items():
            if wayfair_field not in col_indices:
                continue
            col_idx = col_indices[wayfair_field]
            source_col_name = source_map.get(source_key) if source_map else None
            if source_col_name and source_col_name in src_row:
                val = src_row[source_col_name]
                if pd.notna(val):
                    if wayfair_field == "Country of Origin":
                        val = str(val).strip()
                        if val in ["CHN", "CN"]:
                            val = "China"
                    ws.cell(row=row_num, column=col_idx, value=val)
                    fill_count += 1
                    mapped_count += 1
        if mapped_count > 0:
            filled_summary.append(f"基础映射x{mapped_count}")
        

        combined_value = f"{item_code}{product_name_en if product_name_en else product_name}"
        if suffix:
            combined_value = f"{combined_value}{suffix}"

        if "Supplier Part Number" in col_indices:
            ws.cell(row=row_num, column=col_indices["Supplier Part Number"], value=combined_value)
            fill_count += 1
            mapped_count += 1

        if "Manufacturer Part Number" in col_indices:
            ws.cell(row=row_num, column=col_indices["Manufacturer Part Number"], value=combined_value)
            fill_count += 1
            mapped_count += 1
        # ==================== 6. 图片字段 ====================
        image_fields = [
            ("Main Image URL", "产品主图"),
            ("Other Image URL", "上架图片1"), ("Other Image URL", "上架图片2"),
            ("Other Image URL", "上架图片3"), ("Other Image URL", "上架图片4"),
            ("Other Image URL", "上架图片5"), ("Other Image URL", "上架图片6"),
            ("Other Image URL", "上架图片7"), ("Other Image URL", "上架图片8"),
        ]
        img_count = 0
        for field_name, source_key in image_fields:
            if field_name not in col_indices:
                continue
            col_idx = col_indices[field_name]
            source_col = source_map.get(source_key, source_key) if source_map else source_key
            if source_col in src_row:
                val = src_row[source_col]
                if pd.notna(val) and str(val).strip():
                    ws.cell(row=row_num, column=col_idx, value=val)
                    fill_count += 1
                    img_count += 1
        if img_count > 0:
            filled_summary.append(f"图片URLx{img_count}")
        # ==================== AI 字段填充（只填系统未填过的空单元格）====================
        ai_filled = []
        if ai_data:
            for ai_field, ai_value in ai_data.items():
                if ai_field in col_indices:
                    current = ws.cell(row=row_num, column=col_indices[ai_field]).value
                    if current is None or str(current).strip() == '':
                        ws.cell(row=row_num, column=col_indices[ai_field], value=ai_value)
                        fill_count += 1
                        ai_filled.append(f"{ai_field}={str(ai_value)[:25]}")
        if ai_filled:
            filled_summary.append(f"[AI]{', '.join(ai_filled[:3])}")
            if len(ai_filled) > 3:
                filled_summary.append(f"...等{len(ai_filled)}个AI字段")
        # ==================== 结束 ====================
        # 日志汇总
        if filled_summary:
            summary_str = " | ".join(filled_summary)
            add_ai_log(f"📋 [Wayfair模板第{row_num}行] 填写详情({fill_count}个字段): {summary_str}", 'info')
        else:
            add_ai_log(f"⚠️ [Wayfair模板第{row_num}行] 未填写任何字段", 'warn')

    except Exception as e:
        add_ai_log(f"❌ [Wayfair模板第{row_num}行] 填写异常: {str(e)}", 'error')
        print(f"fill_wayfair_row error: {e}")

    return fill_count


def fill_amazon_row(src_row, source_map, col_indices, valid_values, row_num, ws, suffix, mfr_suffix, ai_data=None, src_row_index=0):
    fill_count = 0
    item_code = ""
    product_name = ""
    product_name_en = ""
    filled_summary = []

    try:
        item_code = src_row.get("Item Code", "")
        product_name = src_row.get("产品名称", "")
        product_name_en = src_row.get("产品英文名称", "")
        if pd.isna(item_code): item_code = ""
        if pd.isna(product_name): product_name = ""
        if pd.isna(product_name_en): product_name_en = ""

        sku_name = product_name_en if product_name_en else product_name
        sku_value = f"{item_code}{sku_name}{suffix}"
        if "SKU" in col_indices:
            ws.cell(row=row_num, column=col_indices["SKU"], value=sku_value)
            fill_count += 1
            filled_summary.append(f"SKU={sku_value[:40]}")

        if "Model Number" in col_indices:
            ws.cell(row=row_num, column=col_indices["Model Number"], value=sku_value)
            fill_count += 1
            filled_summary.append(f"ModelNumber={sku_value[:40]}")

        if "Model Name" in col_indices:
            ws.cell(row=row_num, column=col_indices["Model Name"], value=sku_value)
            fill_count += 1

        if "Part Number" in col_indices:
            ws.cell(row=row_num, column=col_indices["Part Number"], value=sku_value)
            fill_count += 1

        if "Set Name" in col_indices:
            set_name = product_name_en if product_name_en else product_name
            ws.cell(row=row_num, column=col_indices["Set Name"], value=set_name)
            fill_count += 1
            filled_summary.append(f"SetName={str(set_name)[:40]}")

        field_map = {
            "Color": "颜色",
            "Base Color": "颜色",
            "Top Color": "颜色",
            "Frame Material": "材质",
            "Base Material": "材质",
            "Top Material": "材质",
            "Furniture Leg Material":"材质",
            "Upholstery Fabric Type": "材质",
            "Item Length": "组装长度",
            "Item Width": "组装宽度",
            "Item Height": "组装高度",
            "Item Weight": "产品重量",
            "Item Package Length": "包装尺寸-长度",
            "Item Package Width": "包装尺寸-宽度",
            "Item Package Height": "包装尺寸-高度",
            "Package Weight": "包装尺寸-重量",
            "List Price": "优惠单价",
            "Your Price USD": "优惠单价",
            "Unit Count": "包装尺寸-重量",
            "Item Length Longer Edge": "组装长度",
            "Item Width Shorter Edge": "组装宽度",
            "Length longer edge when assembled": "组装长度",
            "Width shorter edge when assembled": "组装宽度",
            "Height base to top when assembled": "组装高度",
            "Item Height Floor To Top": "组装高度",
            "Item Width Side To Side": "组装长度",
            "Working Surface Length": "组装长度",
            "Working Surface Width": "组装宽度",
            "Country of Origin": "原产地"
        }

        source_col_map = {
            "颜色": "颜色",
            "材质": "材质",
            "组装长度": "组装长度(英寸)",
            "组装宽度": "组装宽度(英寸)",
            "组装高度": "组装高度(英寸)",
            "产品重量": "产品重量(磅)",
            "包装尺寸-长度": "包装尺寸-长度(英寸)",
            "包装尺寸-宽度": "包装尺寸-宽度(英寸)",
            "包装尺寸-高度": "包装尺寸-高度(英寸)",
            "包装尺寸-重量": "包装尺寸-重量(磅)",
            "优惠单价": "优惠单价",
            "产品名称": "产品名称",
            "产品英文名称": "产品英文名称",
            "Item Code": "Item Code",
            "店铺名称": "店铺名称",
            "原产地": "原产地",
            "产品主图": "产品主图",
            "上架图片1": "上架图片1",
            "上架图片2": "上架图片2",
            "上架图片3": "上架图片3",
            "上架图片4": "上架图片4",
            "上架图片5": "上架图片5",
            "上架图片6": "上架图片6",
            "上架图片7": "上架图片7",
            "上架图片8": "上架图片8",
            "产品描述": "产品描述",
            "产品特点1": "产品特点1",
            "产品特点2": "产品特点2",
            "产品特点3": "产品特点3",
            "产品特点4": "产品特点4",
            "产品特点5": "产品特点5",
        }

        mapped_fields = []
        for amazon_field, source_key in field_map.items():
            if amazon_field not in col_indices:
                continue
            col_idx = col_indices[amazon_field]
            source_col_name = source_col_map.get(source_key, source_key)
            source_col = source_map.get(source_key, source_col_name)
            if source_col and source_col in src_row:
                val = src_row[source_col]
                if pd.notna(val):
                    if amazon_field == "Country of Origin":
                        val = str(val).strip()
                        if val in ["CHN", "CN"]:
                            val = "China"
                    ws.cell(row=row_num, column=col_idx, value=val)
                    fill_count += 1
                    mapped_fields.append(f"{amazon_field}={str(val)[:25]}")

        if mapped_fields:
            filled_summary.extend(mapped_fields[:5])
            if len(mapped_fields) > 5:
                filled_summary.append(f"...等{len(mapped_fields)}个映射字段")

        # AI 字段填充（只填系统未填过的空单元格，避免覆盖）
        ai_filled = []
        if ai_data:
            for ai_field, ai_value in ai_data.items():
                if ai_field in col_indices and ai_field not in AI_EXCLUDED_FIELDS:
                    current = ws.cell(row=row_num, column=col_indices[ai_field]).value
                    if current is None or str(current).strip() == '':
                        ws.cell(row=row_num, column=col_indices[ai_field], value=ai_value)
                        fill_count += 1
                        ai_filled.append(f"{ai_field}={str(ai_value)[:25]}")

        if ai_filled:
            filled_summary.append(f"[AI]{', '.join(ai_filled[:3])}")
            if len(ai_filled) > 3:
                filled_summary.append(f"...等{len(ai_filled)}个AI字段")

        image_fields = [
            ("Main Image URL", "产品主图"),
            ("Other Image URL", "上架图片1"), ("Other Image URL", "上架图片2"),
            ("Other Image URL", "上架图片3"), ("Other Image URL", "上架图片4"),
            ("Other Image URL", "上架图片5"), ("Other Image URL", "上架图片6"),
            ("Other Image URL", "上架图片7"), ("Other Image URL", "上架图片8"),
        ]
        img_count = 0
        for field_name, source_key in image_fields:
            if field_name not in col_indices:
                continue
            col_idx = col_indices[field_name]
            source_col = source_col_map.get(source_key, source_key)
            if source_col in src_row:
                val = src_row[source_col]
                if pd.notna(val) and str(val).strip():
                    ws.cell(row=row_num, column=col_idx, value=val)
                    fill_count += 1
                    img_count += 1

        if img_count > 0:
            filled_summary.append(f"图片URLx{img_count}")

        fixed_count = 0
        fixed_values = {
            "Listing Action": "Create or Replace (Full Update)",
            "Package Level": "Unit",
            "Package Contains SKU Quantity": 1,
            "Number of Items": 1,
            "Item Package Quantity": 1,
            "Unit Count Type": "pound",
            "Item Thickness Decimal Value": 0.6,
            "Item Length Unit": "Inches",
            "Item Width Unit": "Inches",
            "Item Height Unit": "Inches",
            "Item Height Unit of Measure": "inch",
            "Item Depth Front To Back": "inch",
            "Item Package Length Unit": "Inches",
            "Item Package Width Unit": "Inches",
            "Item Package Height Unit": "Inches",
            "Package Weight Unit": "Pounds",
            "Item Weight Unit": "Pounds",
            "Recommended Number of People for Assembly": 2,
            "Has Finished Back": "TRUE",
            "Includes All Assembly Tools": "Yes",
            "Wood Type": "Character",
            "Number of Players": 2,
            "Is Fragile?": "No",
            "Load Capacity Unit": "Pound",
            "Surface Texture": "wood grain",
            "Maximum Order Quantity": 1,
            "Handling Time (US)": 3,
            "Number of Boxes": 1,
            "Is This Product Subject To Buyer Age Restrictions": "No",
            "Safety Attestation": "Yes",
            "Ships Globally": "Yes",
            "Are batteries required?": "No",
            "Are batteries included?": "No",
            "Is OEM Sourced Product": "Yes",
            "Is Customizable?": "No",
            "Is Foldable": "No",
            "Is Stain Resistant": "Yes",
            "Tilting": "No",
            "Offering Can Be Gift Messaged": "Yes",
            "Is Gift Wrap Available": "Yes",
            "Accessories": "Generic",
            "Packaging": "OEM Original",
            "Fulfillment Channel Code (US)": "DEFAULT",
            "Inventory Always Available (US)": "Disabled",
            "Dangerous Goods Regulations": "Not Applicable",
            "Product Id Type": "GTIN Exempt"
        }

        for field_name, fixed_val in fixed_values.items():
            if field_name in col_indices:
                ws.cell(row=row_num, column=col_indices[field_name], value=fixed_val)
                fill_count += 1
                fixed_count += 1

        if fixed_count > 0:
            filled_summary.append(f"固定值x{fixed_count}")

        valid_count = 0
        valid_fields = {
            "Product Type": "Product Type",
            "Brand Name": "Brand Name",
            "Item Type Keyword": "Item Type Keyword",
            "Warranty Type": "Warranty Type",
            "Theme": "Theme",
            "Frame Joint Type": "Frame Joint Type",
            "Assembly Instructions": "Assembly Instructions",
            "Specific Uses for Product": "Specific Uses for Product",
            "Room Type": "Room Type",
            "Maximum Weight Recommendation Unit": "Maximum Weight Recommendation Unit",
            "Leg Style": "Leg Style",
            "Indoor Outdoor Usage": "Indoor Outdoor Usage",
            "Table Design": "Table Design",
            "Furniture Base Movement": "Furniture Base Movement",
            "Item Condition": "Item Condition", "Natural Variation Type": "Natural Variation Type",
            "Warranty Description": "Warranty Description",
            "Special Features": "Special Features",
            "Style": "Style"
        }

        for field_name, valid_key in valid_fields.items():
            if field_name in col_indices:
                val = get_valid_value(valid_values, valid_key)
                if val:
                    ws.cell(row=row_num, column=col_indices[field_name], value=val)
                    fill_count += 1
                    valid_count += 1

        if valid_count > 0:
            filled_summary.append(f"ValidValuesx{valid_count}")
        
        # ==================== 图片字段处理 ====================
        # 定义图片字段和对应的数据源列
        image_fields = [
            ("Main Image URL", "产品主图"),
            ("Other Image URL 1", "上架图片1"),  # 注意这里加序号区分
            ("Other Image URL 2", "上架图片2"),
            ("Other Image URL 3", "上架图片3"),
            ("Other Image URL 4", "上架图片4"),
            ("Other Image URL 5", "上架图片5"),
            ("Other Image URL 6", "上架图片6"),
            ("Other Image URL 7", "上架图片7"),
            ("Other Image URL 8", "上架图片8"),
        ]

        img_count = 0
        for field_name, source_key in image_fields:
            # 检查模板中是否有这个字段
            if field_name not in col_indices:
                continue
            
            col_idx = col_indices[field_name]
            source_col = source_col_map.get(source_key, source_key)
            
            if source_col in src_row:
                val = src_row[source_col]
                if pd.notna(val) and str(val).strip():
                    ws.cell(row=row_num, column=col_idx, value=val)
                    fill_count += 1
                    img_count += 1

        if img_count > 0:
            filled_summary.append(f"图片URLx{img_count}")

        care_vals = valid_values.get("Care Instructions", ["Wipe with Dry Cloth", "Wipe with Damp Cloth"])
        care_count = 0
        for i in range(5):
            field = f"Care Instructions {i+1}" if i > 0 else "Care Instructions"
            if field in col_indices:
                if i % 2 == 0:
                    val = care_vals[0] if care_vals else "Wipe with Dry Cloth"
                else:
                    val = care_vals[1] if len(care_vals) > 1 else "Wipe with Damp Cloth"
                ws.cell(row=row_num, column=col_indices[field], value=val)
                fill_count += 1
                care_count += 1

        theme_val = get_valid_value(valid_values, "Theme", "Space")
        theme_count = 0
        for i in range(5):
            field = f"Theme {i+1}" if i > 0 else "Theme"
            if field in col_indices:
                ws.cell(row=row_num, column=col_indices[field], value=theme_val)
                fill_count += 1
                theme_count += 1

        warranty_val = get_valid_value(valid_values, "Warranty Description", "1 Year Manufacturer")
        warranty_count = 0
        for i in range(5):
            field = f"Warranty Description {i+1}" if i > 0 else "Warranty Description"
            if field in col_indices:
                ws.cell(row=row_num, column=col_indices[field], value=warranty_val)
                fill_count += 1
                warranty_count += 1

        dgr_val = "Not Applicable"
        dgr_count = 0
        for i in range(5):
            field = f"Dangerous Goods Regulations {i+1}" if i > 0 else "Dangerous Goods Regulations"
            if field in col_indices:
                ws.cell(row=row_num, column=col_indices[field], value=dgr_val)
                fill_count += 1
                dgr_count += 1

        tools = ["Hammer", "Screw Driver"]
        tool_count = 0
        if "Tools Recommended For Assembly" in col_indices:
            for i, tool in enumerate(tools):
                if i == 0:
                    field = "Tools Recommended For Assembly"
                else:
                    field = f"Tools Recommended For Assembly {i+1}"
                if field in col_indices:
                    ws.cell(row=row_num, column=col_indices[field], value=tool)
                    fill_count += 1
                    tool_count += 1

        components = ["Installation Tool", "Assembly Guide"]
        comp_count = 0
        if "Included Components" in col_indices:
            for i, comp in enumerate(components):
                if i == 0:
                    field = "Included Components"
                else:
                    field = f"Included Components {i+1}"
                if field in col_indices:
                    ws.cell(row=row_num, column=col_indices[field], value=comp)
                    fill_count += 1
                    comp_count += 1
        if "Manufacturer" in col_indices:
            brand_val = None
            if "Brand Name" in col_indices:
                brand_val = ws.cell(row=row_num, column=col_indices["Brand Name"]).value
            if brand_val:
                ws.cell(row=row_num, column=col_indices["Manufacturer"], value=brand_val)
                fill_count += 1
                filled_summary.append(f"Manufacturer={brand_val}")
        if filled_summary:
            summary_str = " | ".join(filled_summary)
            add_ai_log(f"📋 [模板第{row_num}行] 填写详情({fill_count}个字段): {summary_str}", 'info')
        else:
            add_ai_log(f"⚠️ [模板第{row_num}行] 未填写任何字段", 'warn')

    except Exception as e:
        add_ai_log(f"❌ [模板第{row_num}行] 填写异常: {str(e)}", 'error')
        print(f"fill_amazon_row error: {e}")

    return fill_count

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/get_config')
def get_config():
    return jsonify({
        'templates': [
            {'key': 'wayfair', 'name': 'Wayfair'},
            {'key': 'amazon', 'name': '亚马逊'}
        ],
        'config': TEMPLATE_CONFIG
    })

@app.route('/download_example/<template_key>')
def download_example(template_key):
    if template_key == 'wayfair':
        filename = 'wayfair模板.xlsx'
    elif template_key == 'amazon':
        filename = '亚马逊模板.xlsx'
    else:
        return '无效的模板类型', 400
    file_path = os.path.join(EXAMPLE_FOLDER, filename)
    if not os.path.exists(file_path):
        return f'示例模板文件不存在: {filename}，请将模板文件放入 example_templates/ 目录', 404
    return send_file(file_path, as_attachment=True, download_name=filename)

@app.route('/get_ai_logs')
def get_ai_logs_api():
    return jsonify({'logs': ai_logs[-100:]})

@app.route('/clear_ai_logs')
def clear_ai_logs_api():
    clear_ai_logs()
    return jsonify({'success': True})

@app.route('/preview', methods=['POST'])
def preview():
    try:
        data_file = request.files.get('data_file')
        template_key = request.form.get('template_key')
        if not data_file:
            return jsonify({'error': '请选择数据文件'}), 400
        if not template_key or template_key not in TEMPLATE_CONFIG:
            return jsonify({'error': '请选择有效的模板类型'}), 400
        config = TEMPLATE_CONFIG[template_key]
        source_header = config['source_header']
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        data_path = os.path.join(UPLOAD_FOLDER, f'data_{timestamp}_{data_file.filename}')
        data_file.save(data_path)
        from openpyxl import load_workbook as load_wb_preview
        wb_preview = load_wb_preview(data_path, read_only=True, data_only=True)
        sheet_names = wb_preview.sheetnames
        df = pd.read_excel(data_path, header=source_header - 1)
        preview_data = df.head(10).fillna('').to_dict('records')
        columns = df.columns.tolist()
        add_ai_log(f"📖 预览数据文件: {data_file.filename} | Sheet: '{sheet_names[0]}' | 共 {len(df)} 行 x {len(columns)} 列", 'info')
        add_ai_log(f"   └─ 列名: {', '.join(columns)}", 'info')
        return jsonify({
            'success': True,
            'columns': columns,
            'data': preview_data,
            'total_rows': len(df),
            'data_path': data_path,
            'config': config
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/generate', methods=['POST'])
def generate():
    try:
        data_path = request.form.get('data_path')
        template_key = request.form.get('template_key')
        use_ai = request.form.get('use_ai', 'false').lower() == 'true'

        if not data_path or not os.path.exists(data_path):
            return jsonify({'error': '数据文件不存在'}), 400
        if not template_key or template_key not in TEMPLATE_CONFIG:
            return jsonify({'error': '请选择有效的模板类型'}), 400

        template_file = request.files.get('template_file')
        if not template_file:
            return jsonify({'error': '请上传模板文件'}), 400

        clear_ai_logs()
        add_ai_log(f"🚀 开始生成任务，模板: {template_key}，AI: {'启用' if use_ai else '关闭'}", 'info')

        config = TEMPLATE_CONFIG[template_key]
        source_header = config['source_header']
        template_header_row = config['template_header']

        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        template_path = os.path.join(UPLOAD_FOLDER, f'template_{timestamp}_{template_file.filename}')
        template_file.save(template_path)

        # 读取数据文件并记录sheet信息
        add_ai_log(f"📖 读取数据文件: {os.path.basename(data_path)}", 'info')
        from openpyxl import load_workbook as load_wb_data
        wb_data = load_wb_data(data_path, read_only=True, data_only=True)
        data_sheet_names = wb_data.sheetnames
        add_ai_log(f"📋 数据文件包含 Sheets: {', '.join(data_sheet_names)}", 'info')
        add_ai_log(f"   └─ 使用 Sheet: '{data_sheet_names[0]}' (表头第{source_header}行)", 'info')

        df = pd.read_excel(data_path, header=source_header - 1)
        print("【实际列名】", df.columns.tolist())
        
        total_rows = len(df)
        data_columns = df.columns.tolist()
        add_ai_log(f"📊 数据文件读取完成: 共 {total_rows} 行 x {len(data_columns)} 列", 'info')
        add_ai_log(f"   └─ 列名: {', '.join(data_columns)}", 'info')

        

        # 加载模板
        add_ai_log(f"📖 加载模板文件: {os.path.basename(template_path)}", 'info')
        wb = load_workbook(template_path)
        ws = wb.active
        add_ai_log(f"📋 模板活动Sheet名称: '{ws.title}'", 'info')
        add_ai_log(f"   └─ 模板尺寸: {ws.max_row} 行 x {ws.max_column} 列", 'info')
        valid_values = load_valid_values(wb)
        add_ai_log(f"📋 Valid Values Sheet 分析完成，共 {len(valid_values)} 个字段", 'info')
        if valid_values:
            sample_keys = list(valid_values.keys())
            add_ai_log(f"   └─ 字段: " + ', '.join(sample_keys), 'info')

        # 读取模板表头
        col_indices = {}
        for col in range(1, ws.max_column + 1):
            cell_value = ws.cell(row=template_header_row, column=col).value
            if cell_value:
                col_name = str(cell_value).strip()
                if col_name and not col_name.startswith("Unnamed"):
                    col_indices[col_name] = col
        
        # ==================== 计算系统已处理字段 & AI候选字段 ====================
        system_fields = set()
        if template_key == 'amazon':
            # Amazon 固定值
            system_fields.update([
                "Listing Action", "Package Level", "Package Contains SKU Quantity",
                "Number of Items", "Item Package Quantity", "Unit Count Type", "Item Thickness Decimal Value",
                "Item Length Unit", "Item Width Unit", "Item Height Unit",
                "Item Package Length Unit", "Item Package Width Unit", "Item Package Height Unit",
                "Package Weight Unit", "Item Weight Unit", "Recommended Number of People for Assembly",
                "Has Finished Back", "Includes All Assembly Tools", "Wood Type", "Number of Players",
                "Is Fragile?", "Load Capacity Unit", "Surface Texture", "Maximum Order Quantity",
                "Handling Time (US)", "Number of Boxes", "Is This Product Subject To Buyer Age Restrictions",
                "Safety Attestation", "Ships Globally", "Are batteries required?", "Are batteries included?",
                "Is OEM Sourced Product", "Is Customizable?", "Is Foldable", "Is Stain Resistant",
                "Tilting", "Offering Can Be Gift Messaged", "Is Gift Wrap Available", "Accessories",
                "Packaging", "Fulfillment Channel Code (US)", "Inventory Always Available (US)",
            ])
            # Amazon 映射字段
            system_fields.update([
                "Color", "Base Color", "Top Color", "Frame Material", "Base Material", "Top Material",
                "Furniture Leg Material", "Upholstery Fabric Type", "Item Length", "Item Width", "Item Height",
                "Item Weight", "Item Package Length", "Item Package Width", "Item Package Height",
                "Package Weight", "List Price", "Your Price USD", "Unit Count", "Item Length Longer Edge",
                "Item Width Shorter Edge", "Length longer edge when assembled", "Width shorter edge when assembled",
                "Height base to top when assembled", "Item Depth Front To Back", "Item Height Floor To Top",
                "Item Width Side To Side", "Working Surface Length", "Working Surface Width", "Country of Origin", "Manufacturer",
            ])
            # Amazon Valid Values / 特殊字段
            system_fields.update([
                "Product Type", "Listing Action", "Brand Name", "Product Id Type", "Item Type Keyword",
                "Package Level", "Warranty Type", "Theme", "Frame Joint Type", "Assembly Instructions",
                "Specific Uses for Product", "Room Type", "Maximum Weight Recommendation Unit", "Leg Style",
                "Indoor Outdoor Usage", "Table Design", "Furniture Base Movement", "Item Condition",
                "Natural Variation Type", "Includes All Assembly Tools", "Dangerous Goods Regulations",
                "Warranty Description", "Care Instructions", "Care Instructions 2", "Care Instructions 3",
                "Care Instructions 4", "Care Instructions 5", "Theme 2", "Theme 3", "Theme 4", "Theme 5",
                "Warranty Description 2", "Warranty Description 3", "Warranty Description 4", "Warranty Description 5",
                "Dangerous Goods Regulations 2", "Dangerous Goods Regulations 3", "Dangerous Goods Regulations 4",
                "Dangerous Goods Regulations 5", "Tools Recommended For Assembly", "Tools Recommended For Assembly 2",
                "Included Components", "Included Components 2",
            ])
            # Amazon 图片 + Parentage + SKU相关
            system_fields.update([
                "Main Image URL", "Other Image URL", "Other Image URL 2", "Other Image URL 3",
                "Other Image URL 4", "Other Image URL 5", "Other Image URL 6", "Other Image URL 7", "Other Image URL 8",
                "Parentage Level", "Parent SKU", "Variation Theme Name",
                "SKU", "Model Number", "Model Name", "Part Number", "Set Name"
            ])
        elif template_key == 'wayfair':
            # Wayfair 固定值
            system_fields.update([
                "Minimum Order Quantity", "Force Quantity Multiplier", "Display Set Quantity",
                "Lead Time", "Replacement Lead Time", "Flat Pack", "Assembly Required",
                "Canada Product Restriction", "CARB Phase II Compliant (formaldehyde emissions)",
                "Commercial Warranty", "CANFER Compliant", "Commercial Warranty Length",
                "Composite Wood Product (CWP)", "Country Of Manufacturer", "General Certificate of Conformity (GCC)",
                "Hazard Class(es)", "Hazardous Material / Dangerous Goods", "Hazardous Material Weight",
                "Level of Assembly", "Packing Group", "Reason for Restriction",
                "Supplier Intended and Approved Use", "TSCA Title VI Compliant (formaldehyde emissions)",
                "UN or ID number", "Uniform Packaging and Labeling Regulations (UPLR) Compliant",
                "Warning Required", "Warranty Length", "Battery or Batteries Included",
            ])
            # Wayfair 映射字段
            system_fields.update([
                "Color", "Base Color", "Top Color", "Frame Material", "Base Material", "Top Material",
                "Furniture Leg Material", "Upholstery Fabric Type", "Material", "Supplier Part Number",
                "Manufacturer Part Number", "Base Cost", "Manufacturer Suggested Retail Price",
                "Item Length", "Item Width", "Item Height", "Item Weight", "Product Weight",
                "Overall Product Weight", "Overall Width - Side to Side", "Overall Depth - Front to Back",
                "Overall Height - Top to Bottom", "Item Length Longer Edge", "Item Width Shorter Edge",
                "Length longer edge when assembled", "Width shorter edge when assembled",
                "Height base to top when assembled", "Item Depth Front To Back", "Item Height Floor To Top",
                "Item Width Side To Side", "Working Surface Length", "Working Surface Width",
                "Item Package Length", "Item Package Width", "Item Package Height", "Package Weight",
                "Carton Weight 1", "Carton Height 1", "Carton Width 1", "Carton Depth 1",
                "Unit Count", "List Price", "Your Price", "Country of Origin",
            ])
            # Wayfair Valid Values / 特殊字段
            system_fields.update([
                "Brand", "Variant Type", "Variant Grouping 1", "Variant Grouping 2",
                "Variant Attribute Name On Site 1", "Variant Attribute Name On Site 2",
                "Ship Type", "ISTA Certified", "Product Type",
            ])
            # Wayfair 图片
            system_fields.update([
                "Main Image URL", "Other Image URL", "Other Image URL 2", "Other Image URL 3",
                "Other Image URL 4", "Other Image URL 5", "Other Image URL 6", "Other Image URL 7", "Other Image URL 8",
            ])

        all_template_fields = set(col_indices.keys())
        ai_candidate_fields = sorted(list(all_template_fields - system_fields - AI_EXCLUDED_FIELDS))
        add_ai_log(f"🤖 AI候选字段分析: 模板共 {len(all_template_fields)} 个字段", 'info')
        add_ai_log(f"   ├─ 系统已处理: {len(system_fields)} 个", 'info')
        add_ai_log(f"   ├─ 排除字段: {len(AI_EXCLUDED_FIELDS)} 个", 'info')
        add_ai_log(f"   └─ 交给AI填写: {len(ai_candidate_fields)} 个", 'info')
        if ai_candidate_fields:
            sample = ai_candidate_fields[:15]
            add_ai_log(f"   └─ AI字段示例: {', '.join(sample)}{'...' if len(ai_candidate_fields)>15 else ''}", 'info')
        # ==================== 结束 ====================


        # AI处理
        ai_results = {}
        if use_ai and '产品主图' in df.columns and ai_candidate_fields:
            add_ai_log(f"🤖 开始AI识别，共 {total_rows} 行需要处理", 'info')
            has_image_count = 0
            for idx, row in df.iterrows():
                img_url = row.get('产品主图', '')
                if pd.notna(img_url) and str(img_url).strip():
                    has_image_count += 1
            add_ai_log(f"📷 其中 {has_image_count} 行包含产品主图", 'info')
            processed = 0
            success_count = 0
            for idx, row in df.iterrows():
                img_url = row.get('产品主图', '')
                if pd.notna(img_url) and str(img_url).strip():
                    processed += 1
                    add_ai_log(f"🔄 [第{idx+1}/{total_rows}行] 处理中 ({processed}/{has_image_count})...", 'info')
                    ai_data = get_ai_filled_data(str(img_url).strip(), idx + 1, ai_candidate_fields)
                    if ai_data:
                        ai_results[idx] = ai_data
                        success_count += 1
                        add_ai_log(f"✅ [第{idx+1}行] AI识别完成，提取 {len(ai_data)} 个字段", 'success')
                    if processed % 5 == 0:
                        add_ai_log(f"📊 进度: {processed}/{has_image_count} 行已处理，成功 {success_count} 行", 'info')
            add_ai_log(f"🎉 AI处理完成！共处理 {processed} 行，成功 {success_count} 行", 'success')
        elif use_ai and '产品主图' not in df.columns:
            add_ai_log(f"⚠️ 数据中没有 '产品主图' 列，AI功能将跳过", 'warn')
        elif use_ai and not ai_candidate_fields:
            add_ai_log(f"⏭️ 没有需要AI填写的候选字段，AI功能将跳过", 'info')
        else:
            add_ai_log(f"⏭️ AI功能未启用", 'info')

        add_ai_log(f"📋 模板表头(第{template_header_row}行)共解析出 {len(col_indices)} 个有效列", 'info')
        sample_cols = list(col_indices.keys())
        add_ai_log(f"   └─ 列名: " + ', '.join(sample_cols), 'info')

        # 找到已有数据的最后一行
        start_row = 2
        last_row = start_row
        for row in range(start_row, ws.max_row + 2):
            is_empty = True
            for col in range(1, min(10, ws.max_column + 1)):
                if ws.cell(row=row, column=col).value is not None:
                    is_empty = False
                    break
            if is_empty:
                last_row = row
                break
        else:
            last_row = ws.max_row + 1

        if last_row > start_row:
            add_ai_log(f"📌 扫描模板已有数据: 第 {start_row} 行到第 {last_row-1} 行存在数据", 'info')
        else:
            add_ai_log(f"📌 模板暂无数据，从第 {start_row} 行开始填充", 'info')
        add_ai_log(f"📌 将从第 {last_row} 行开始追加新数据", 'info')

        # 匹配来源列
        source_col_map = {
            "颜色": "颜色",
            "材质": "材质",
            "组装长度": "组装长度(英寸)",
            "组装宽度": "组装宽度(英寸)",
            "组装高度": "组装高度(英寸)",
            "产品重量": "产品重量(磅)",
            "包装尺寸-长度": "包装尺寸-长度(英寸)",
            "包装尺寸-宽度": "包装尺寸-宽度(英寸)",
            "包装尺寸-高度": "包装尺寸-高度(英寸)",
            "包装尺寸-重量": "包装尺寸-重量(磅)",
            "优惠单价": "优惠单价",
            "产品名称": "产品名称",
            "产品英文名称": "产品英文名称",
            "Item Code": "Item Code",
            "店铺名称": "店铺名称",
            "原产地": "原产地",
            "产品主图": "产品主图",
            "上架图片1": "上架图片1",
            "上架图片2": "上架图片2",
            "上架图片3": "上架图片3",
            "上架图片4": "上架图片4",
            "上架图片5": "上架图片5",
            "上架图片6": "上架图片6",
            "上架图片7": "上架图片7",
            "上架图片8": "上架图片8",
            "产品描述": "产品描述",
            "产品特点1": "产品特点1",
            "产品特点2": "产品特点2",
            "产品特点3": "产品特点3",
            "产品特点4": "产品特点4",
            "产品特点5": "产品特点5",
        }
        print("【期望列名】", list(source_col_map.values()))
        source_map = {}
        matched_count = 0
        for key, expected in source_col_map.items():
            found = False
            for col in df.columns:
                if col == expected:
                    source_map[key] = col
                    found = True
                    matched_count += 1
                    break
            if not found:
                source_map[key] = None

        add_ai_log(f"🔗 来源列匹配结果: {matched_count}/{len(source_col_map)} 列匹配成功", 'info')
        matched_cols = [k for k, v in source_map.items() if v is not None]
        if matched_cols:
            add_ai_log(f"   ✅ 匹配成功:" + ', '.join(matched_cols), 'info')
        unmatched_cols = [k for k, v in source_map.items() if v is None]
        if unmatched_cols:
            add_ai_log(f"   ⚠️ 未匹配: " + ', '.join(unmatched_cols), 'warn')

        # 填充数据
        def get_key(row):
            item = row.get("Item Code", "")
            name = row.get("产品英文名称", "")
            if pd.isna(name) or not name:
                name = row.get("产品名称", "")
            return f"{item}_{name}"

        keys = [get_key(row) for _, row in df.iterrows()]
        key_count = Counter(keys)
        key_index = Counter()

        row_num = last_row
        filled_count = 0

        add_ai_log(f"✍️ 开始填充数据，共 {total_rows} 行，目标行号从 {last_row} 开始", 'info')

        for idx, (_, src_row) in enumerate(df.iterrows()):
            key = get_key(src_row)

            if key_count[key] > 1:
                key_index[key] += 1
                suffix = f".{key_index[key] - 1}"
                mfr_suffix = f".{key_index[key]}"
            else:
                suffix = ""
                mfr_suffix = ""

            is_parent = (idx == 0) or (key_index[key] == 1)

            item_code = src_row.get("Item Code", "")
            product_name = src_row.get("产品英文名称", "")
            if pd.isna(product_name) or not product_name:
                product_name = src_row.get("产品名称", "")
            if pd.isna(item_code): item_code = ""
            if pd.isna(product_name): product_name = ""

            add_ai_log(f"✍️ [源数据第{idx+1}行] ItemCode={item_code} | 产品={product_name} → 开始填入模板第{row_num}行", 'info')

            if "Parentage Level" in col_indices:
                if not is_parent:
                    ws.cell(row=row_num, column=col_indices["Parentage Level"], value="Child")
                    add_ai_log(f"   └─ Parentage Level = Child", 'info')

            if "Parent SKU" in col_indices:
                if not is_parent:
                    first_item = df.iloc[0].get("Item Code", "")
                    first_name = df.iloc[0].get("产品英文名称", "")
                    if pd.isna(first_name) or not first_name:
                        first_name = df.iloc[0].get("产品名称", "")
                    first_sku = f"{first_item}{first_name}"
                    ws.cell(row=row_num, column=col_indices["Parent SKU"], value=first_sku)
                    add_ai_log(f"   └─ Parent SKU = {first_sku}", 'info')

            if "Variation Theme Name" in col_indices:
                if key_count[key] > 1:
                    ws.cell(row=row_num, column=col_indices["Variation Theme Name"], value="COLOR")
                    add_ai_log(f"   └─ Variation Theme Name = COLOR", 'info')

            ai_data = ai_results.get(idx, {}) if use_ai else {}

            if template_key == 'amazon':
                fill_count = fill_amazon_row(
                    src_row, source_map, col_indices, valid_values, 
                    row_num, ws, suffix, mfr_suffix, ai_data, idx
                )
            elif template_key == 'wayfair':
                fill_count = fill_wayfair_row(
                    src_row, source_map, col_indices, valid_values, 
                    row_num, ws, suffix, mfr_suffix, ai_data, idx
                )
            else:
                fill_count = 0

            add_ai_log(f"✅ [源数据第{idx+1}行] 完成 → 模板第{row_num}行，共填写 {fill_count} 个单元格", 'success')

            row_num += 1
            filled_count += 1

            if (idx + 1) % 10 == 0:
                add_ai_log(f"📊 填充进度: {idx+1}/{total_rows} 行 (当前模板行: {row_num-1})", 'info')

        add_ai_log(f"✅ 数据填充完成，共填充 {filled_count} 行，模板行号范围: {last_row} - {row_num-1}", 'success')

        # 保存
        output_filename = f"output_{template_key}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        output_path = os.path.join(OUTPUT_FOLDER, output_filename)
        wb.save(output_path)
        add_ai_log(f"💾 文件保存成功: {output_filename}", 'success')
        add_ai_log(f"   └─ 保存路径: {output_path}", 'info')

        # 清理临时文件
        # try:
        #     os.remove(data_path)
        #     os.remove(template_path)
        #     add_ai_log(f"🗑️ 临时文件已清理", 'info')
        # except Exception as e:
        #     add_ai_log(f"⚠️ 清理临时文件失败: {str(e)}", 'warn')

        ai_info = f"，AI识别 {len(ai_results)} 行" if use_ai and ai_results else ""
        return jsonify({
            'success': True,
            'filled_count': filled_count,
            'download_url': f'/download/{output_filename}',
            'message': f'成功追加 {filled_count} 行数据到 {config["name"]} 模板{ai_info}',
            'ai_used': use_ai,
            'ai_rows': len(ai_results) if ai_results else 0,
            'logs': ai_logs[-100:]
        })

    except Exception as e:
        import traceback
        error_detail = traceback.format_exc()
        add_ai_log(f"❌ 生成失败: {str(e)}", 'error')
        print(error_detail)
        return jsonify({'error': str(e), 'detail': error_detail}), 500

@app.route('/download/<filename>')
def download(filename):
    file_path = os.path.join(OUTPUT_FOLDER, filename)
    if not os.path.exists(file_path):
        return '文件不存在', 404
    return send_file(file_path, as_attachment=True, download_name=filename)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5002, debug=True)