// Excel 导入导出模块
use crate::models::*;
use crate::database::DB_CONN;
use crate::permissions::check_permission;
use uuid::Uuid;
use chrono::Local;
use serde::Deserialize;

/// 批量导入销售订单的请求项
#[derive(Debug, Deserialize)]
pub struct ImportSaleItem {
    #[serde(default)]
    pub product_code: String,
    pub product_name: String,
    pub quantity: f64,
    pub price: f64,
}

#[derive(Debug, Deserialize)]
pub struct ImportSaleOrder {
    pub customer_name: String,
    pub items: Vec<ImportSaleItem>,
    pub remark: String,
}

/// 批量导入采购订单的请求项
#[derive(Debug, Deserialize)]
pub struct ImportPurchaseItem {
    #[serde(default)]
    pub product_code: String,
    pub product_name: String,
    pub quantity: f64,
    pub price: f64,
}

#[derive(Debug, Deserialize)]
pub struct ImportPurchaseOrder {
    pub supplier_name: String,
    pub items: Vec<ImportPurchaseItem>,
    pub remark: String,
}

#[tauri::command]
pub fn export_products(username: String) -> Result<Vec<Product>, String> {
    if !check_permission(&username, "data_export") {
        return Err("权限不足：没有导出权限".to_string());
    }
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    let mut stmt = conn.prepare(
        "SELECT id, name, code, category, unit, unit2, price, cost, stock, stock_unit2, \
         batch_no, production_date, expiry_date, brand, model, package_type, description, image_path, \
         warehouse_id, location_id, created_at, barcode, trace_code, status, category_id, unit_rate \
         FROM products ORDER BY name"
    ).map_err(|e| e.to_string())?;
    let products = stmt.query_map([], |row| {
        Ok(Product {
            id: row.get(0)?, name: row.get(1)?, code: row.get(2)?, category: row.get(3)?,
            unit: row.get(4)?, unit2: row.get::<_, Option<String>>(5)?.unwrap_or_default(),
            price: row.get(6)?, cost: row.get(7)?, stock: row.get(8)?,
            stock_unit2: row.get::<_, Option<f64>>(9)?.unwrap_or(0.0),
            batch_no: row.get::<_, Option<String>>(10)?.unwrap_or_default(),
            production_date: row.get::<_, Option<String>>(11)?.unwrap_or_default(),
            expiry_date: row.get::<_, Option<String>>(12)?.unwrap_or_default(),
            brand: row.get::<_, Option<String>>(13)?.unwrap_or_default(),
            model: row.get::<_, Option<String>>(14)?.unwrap_or_default(),
            package_type: row.get::<_, Option<String>>(15)?.unwrap_or_default(),
            description: row.get::<_, Option<String>>(16)?.unwrap_or_default(),
            image_path: row.get::<_, Option<String>>(17)?.unwrap_or_default(),
            warehouse_id: row.get::<_, Option<String>>(18)?.unwrap_or_default(),
            location_id: row.get::<_, Option<String>>(19)?.unwrap_or_default(),
            barcode: row.get::<_, Option<String>>(21).unwrap_or(None).unwrap_or_default(),
            trace_code: row.get::<_, Option<String>>(22).unwrap_or(None).unwrap_or_default(),
            status: row.get::<_, Option<String>>(23).unwrap_or(None).unwrap_or_default(),
            category_id: row.get::<_, Option<String>>(24).unwrap_or(None).unwrap_or_default(),
            unit_rate: row.get::<_, Option<f64>>(25)?.unwrap_or(1.0),
            created_at: row.get::<_, String>(20).unwrap_or_default(),
        })
    }).map_err(|e| e.to_string())?;
    products.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn import_products(products: Vec<AddProductRequest>, username: String) -> Result<Vec<Product>, String> {
    if !check_permission(&username, "data_import") {
        return Err("权限不足：没有导入权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_mut().ok_or("Database not initialized")?;
    let mut result = Vec::new();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let total = products.len();
    let mut skipped = 0usize;

    for request in &products {
        // 检查编码是否已存在
        let existing_id: Option<String> = conn.query_row(
            "SELECT id FROM products WHERE code=?1",
            [&request.code], |row| row.get(0)
        ).ok();

        if let Some(pid) = existing_id {
            // 更新已有商品（更新价格、成本、库存等）
            conn.execute(
                "UPDATE products SET name=?1, category=?2, unit=?3, unit2=?4, price=?5, cost=?6,
                 stock=stock+?7, stock_unit2=stock_unit2+?8,
                 barcode=COALESCE(?9,barcode), brand=?10, model=?11, package_type=?12,
                 description=?13, warehouse_id=?14,
                 location_id=?15, unit_rate=?16 WHERE id=?17",
                [&request.name, &request.category, &request.unit, &request.unit2,
                 &request.price.to_string(), &request.cost.to_string(),
                 &request.stock.to_string(), &request.stock_unit2.to_string(),
                 &request.barcode.clone().unwrap_or_default(), &request.brand, &request.model,
                 &request.package_type, &request.description,
                 &request.warehouse_id, &request.location_id, &request.unit_rate.to_string(), &pid]
            ).map_err(|e| e.to_string())?;
            skipped += 1;
            continue;
        }

        let id = Uuid::new_v4().to_string();
        let barcode = request.barcode.clone().unwrap_or_default();
        let trace_code = request.trace_code.clone().unwrap_or_default();
        let production_date = request.production_date.clone().unwrap_or_default();
        let expiry_date = request.expiry_date.clone().unwrap_or_default();
        conn.execute(
            "INSERT INTO products (id, name, code, category, unit, unit2, price, cost, stock, stock_unit2,
             batch_no, production_date, expiry_date, brand, model, package_type, description, image_path, warehouse_id, location_id, barcode, trace_code, unit_rate, created_at)
             VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17,?18,?19,?20,?21,?22,?23,?24)",
            [&id, &request.name, &request.code, &request.category, &request.unit, &request.unit2,
             &request.price.to_string(), &request.cost.to_string(), &request.stock.to_string(),
             &request.stock_unit2.to_string(), &request.batch_no, &production_date, &expiry_date,
             &request.brand, &request.model, &request.package_type, &request.description,
             &request.image_path, &request.warehouse_id, &request.location_id,
             &barcode, &trace_code, &request.unit_rate.to_string(), &now]
        ).map_err(|e| e.to_string())?;

        result.push(Product {
            id, name: request.name.clone(), code: request.code.clone(),
            category: request.category.clone(), unit: request.unit.clone(),
            unit2: request.unit2.clone(), price: request.price, cost: request.cost,
            stock: request.stock, stock_unit2: request.stock_unit2,
            batch_no: request.batch_no.clone(), production_date, expiry_date,
            brand: request.brand.clone(), model: request.model.clone(),
            package_type: request.package_type.clone(), description: request.description.clone(),
            image_path: request.image_path.clone(), warehouse_id: request.warehouse_id.clone(),
            location_id: request.location_id.clone(),
            barcode, trace_code, status: String::from("active"), category_id: String::new(),
            unit_rate: request.unit_rate,
            created_at: now.clone(),
        });
    }
    let imported = result.len();
    crate::operation_logs::log_operation(&username, &username, "导入商品", "Excel导入", 
        &format!("成功{}条, 跳过{}条重复, 总计{}条", imported, skipped, total));
    Ok(result)
}

#[tauri::command]
pub fn export_sale_orders(username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_export") {
        return Err("权限不足：没有导出权限".to_string());
    }
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    let mut stmt = conn.prepare("SELECT so.*, soi.product_name, soi.quantity, soi.price, soi.amount FROM sale_orders so JOIN sale_order_items soi ON so.id=soi.order_id ORDER BY so.created_at DESC")
        .map_err(|e| e.to_string())?;
    let rows = stmt.query_map([], |row| {
        Ok(serde_json::json!({
            "order_no": row.get::<_, String>(1)?,
            "customer_name": row.get::<_, String>(3)?,
            "total_amount": row.get::<_, f64>(4)?,
            "status": row.get::<_, String>(5)?,
            "created_at": row.get::<_, String>(7)?,
            "product_name": row.get::<_, String>(9)?,
            "quantity": row.get::<_, f64>(10)?,
            "price": row.get::<_, f64>(11)?,
            "amount": row.get::<_, f64>(12)?,
        }))
    }).map_err(|e| e.to_string())?;
    rows.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn export_purchase_orders(username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_export") {
        return Err("权限不足：没有导出权限".to_string());
    }
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    let mut stmt = conn.prepare(
        "SELECT po.*, poi.product_name, poi.quantity, poi.price, poi.amount 
         FROM purchase_orders po JOIN purchase_order_items poi ON po.id=poi.order_id ORDER BY po.created_at DESC")
        .map_err(|e| e.to_string())?;
    let rows = stmt.query_map([], |row| {
        Ok(serde_json::json!({
            "order_no": row.get::<_, String>(1)?,
            "supplier_name": row.get::<_, String>(3)?,
            "total_amount": row.get::<_, f64>(4)?,
            "status": row.get::<_, String>(5)?,
            "created_at": row.get::<_, String>(7)?,
            "product_name": row.get::<_, String>(9)?,
            "quantity": row.get::<_, f64>(10)?,
            "price": row.get::<_, f64>(11)?,
            "amount": row.get::<_, f64>(12)?,
        }))
    }).map_err(|e| e.to_string())?;
    rows.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn export_customers(username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_export") {
        return Err("权限不足：没有导出权限".to_string());
    }
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    let mut stmt = conn.prepare("SELECT * FROM customers ORDER BY name").map_err(|e| e.to_string())?;
    let rows = stmt.query_map([], |row| {
        Ok(serde_json::json!({
            "name": row.get::<_, String>(1)?,
            "contact": row.get::<_, Option<String>>(2)?.unwrap_or_default(),
            "phone": row.get::<_, Option<String>>(3)?.unwrap_or_default(),
            "address": row.get::<_, Option<String>>(4)?.unwrap_or_default(),
        }))
    }).map_err(|e| e.to_string())?;
    rows.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn export_suppliers(username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_export") {
        return Err("权限不足：没有导出权限".to_string());
    }
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    let mut stmt = conn.prepare("SELECT * FROM suppliers ORDER BY name").map_err(|e| e.to_string())?;
    let rows = stmt.query_map([], |row| {
        Ok(serde_json::json!({
            "name": row.get::<_, String>(1)?,
            "contact": row.get::<_, Option<String>>(2)?.unwrap_or_default(),
            "phone": row.get::<_, Option<String>>(3)?.unwrap_or_default(),
            "address": row.get::<_, Option<String>>(4)?.unwrap_or_default(),
        }))
    }).map_err(|e| e.to_string())?;
    rows.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn import_customers(customers: Vec<AddCustomerRequest>, username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_import") {
        return Err("权限不足：没有导入客户的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_mut().ok_or("Database not initialized")?;
    let mut result = Vec::new();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let mut success_count = 0;
    let mut skip_count = 0;
    for c in &customers {
        let id = Uuid::new_v4().to_string();
        if let Err(e) = conn.execute(
            "INSERT INTO customers (id, name, contact, phone, address, created_at) VALUES (?1,?2,?3,?4,?5,?6)",
            [&id, &c.name, &c.contact, &c.phone, &c.address, &now]
        ) {
            result.push(serde_json::json!({"name": &c.name, "status": "跳过", "error": e.to_string()}));
            skip_count += 1;
        } else {
            result.push(serde_json::json!({"name": &c.name, "status": "成功"}));
            success_count += 1;
        }
    }
    crate::operation_logs::log_operation(&username, &username, "导入客户", "Excel导入", 
        &format!("成功{}条, 跳过{}条, 总计{}条", success_count, skip_count, customers.len()));
    Ok(result)
}

#[tauri::command]
pub fn import_suppliers(suppliers: Vec<AddSupplierRequest>, username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_import") {
        return Err("权限不足：没有导入供应商的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_mut().ok_or("Database not initialized")?;
    let mut result = Vec::new();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let mut success_count = 0;
    let mut skip_count = 0;
    for s in &suppliers {
        let id = Uuid::new_v4().to_string();
        if let Err(e) = conn.execute(
            "INSERT INTO suppliers (id, name, contact, phone, address, created_at) VALUES (?1,?2,?3,?4,?5,?6)",
            [&id, &s.name, &s.contact, &s.phone, &s.address, &now]
        ) {
            result.push(serde_json::json!({"name": &s.name, "status": "跳过", "error": e.to_string()}));
            skip_count += 1;
        } else {
            result.push(serde_json::json!({"name": &s.name, "status": "成功"}));
            success_count += 1;
        }
    }
    crate::operation_logs::log_operation(&username, &username, "导入供应商", "Excel导入", 
        &format!("成功{}条, 跳过{}条, 总计{}条", success_count, skip_count, suppliers.len()));
    Ok(result)
}

/// 批量导入销售订单（含明细）- 走完整业务流程
#[tauri::command]
pub fn import_sale_orders(orders: Vec<ImportSaleOrder>, username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_import") {
        return Err("权限不足：没有导入销售订单的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let mut conn = conn_guard.take().ok_or("Database not initialized")?;
    let mut result = Vec::new();
    // 使用毫秒级时间戳，避免同一秒内重复导入时 order_no 冲突
    let ts_millis = Local::now().timestamp_millis();
    
    for (i, order) in orders.iter().enumerate() {
        let order_id = Uuid::new_v4().to_string();
        // order_no 带毫秒时间戳，确保每次导入都唯一
        let order_no = format!("SO-{}-{:04}", ts_millis, i + 1);
        let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
        
        // ===== 使用事务保证数据一致性 =====
        let tx = match conn.transaction() {
            Ok(tx) => tx,
            Err(e) => {
                result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": 0, "items": 0, "status": "失败", "error": format!("启动事务失败: {}", e)}));
                continue;
            }
        };
        
        // 查找或创建客户
        let customer_id = match tx.query_row(
            "SELECT id FROM customers WHERE name = ?1", [&order.customer_name], |row| row.get::<_, String>(0)
        ) {
            Ok(cid) => cid,
            Err(_) => {
                let cid = Uuid::new_v4().to_string();
                if let Err(e) = tx.execute("INSERT INTO customers (id, name, created_at) VALUES (?1,?2,?3)",
                    rusqlite::params![cid, order.customer_name, &now]) {
                    result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": 0, "items": 0, "status": "失败", "error": format!("创建客户失败: {}", e)}));
                    continue;
                }
                cid
            }
        };
        
        let total: f64 = order.items.iter().map(|it| it.quantity * it.price).sum();
        
        // 插入销售订单
        if let Err(e) = tx.execute(
            "INSERT INTO sale_orders (id,order_no,customer_id,customer_name,total_amount,status,remark,created_at) VALUES (?1,?2,?3,?4,?5,'completed',?6,?7)",
            rusqlite::params![order_id, order_no, customer_id, order.customer_name, total, order.remark, &now]) {
            result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": total, "items": 0, "status": "失败", "error": format!("创建订单失败: {}", e)}));
            continue;
        }
        
        let mut item_ok = 0usize;
        let mut item_errors = Vec::new();
        for item in &order.items {
            // 查找商品：优先按编码匹配，编码为空或未找到时按名称匹配
            let pid = match tx.query_row("SELECT id FROM products WHERE code=?1", [&item.product_code], |r| r.get::<_,String>(0)) {
                Ok(id) => id,
                Err(_) => match tx.query_row("SELECT id FROM products WHERE name=?1", [&item.product_name], |r| r.get::<_,String>(0)) {
                    Ok(id) => id,
                    Err(_) => {
                        item_errors.push(format!("商品'{}'(编码:{})不存在", item.product_name, item.product_code));
                        continue;
                    }
                }
            };
            
            // 插入订单明细
            if let Err(e) = tx.execute(
                "INSERT INTO sale_order_items (id,order_id,product_id,product_name,quantity,price,amount) VALUES (?1,?2,?3,?4,?5,?6,?7)",
                rusqlite::params![Uuid::new_v4().to_string(), order_id, pid, item.product_name, item.quantity, item.price, item.quantity * item.price]
            ) {
                item_errors.push(format!("商品'{}'插入明细失败: {}", item.product_name, e));
                continue;
            }
            
            // 扣减库存
            let current_stock: f64 = tx.query_row("SELECT COALESCE(stock,0.0) FROM products WHERE id=?1",
                [&pid], |row| row.get(0)).unwrap_or(0.0);
            let new_stock = current_stock - item.quantity;
            if let Err(e) = tx.execute("UPDATE products SET stock=?1 WHERE id=?2",
                [&new_stock.to_string(), &pid]) {
                item_errors.push(format!("商品'{}'扣减库存失败: {}", item.product_name, e));
                continue;
            }
            
            // 记录库存变动
            if let Err(e) = tx.execute(
                "INSERT INTO inventory_records (id, product_id, product_name, change_type, quantity, before_stock, after_stock, remark, created_at) VALUES (?1,?2,?3,'sale',?4,?5,?6,?7,?8)",
                [&Uuid::new_v4().to_string(), &pid, &item.product_name,
                 &(-item.quantity).to_string(), &current_stock.to_string(), &new_stock.to_string(),
                 &format!("导入销售订单: {}", order_no), &now]
            ) {
                item_errors.push(format!("商品'{}'记录库存失败: {}", item.product_name, e));
                continue;
            }
            
            item_ok += 1;
        }
        
        // 自动生成应收账单
        if item_ok > 0 {
            let billing_exists: i64 = tx.query_row(
                "SELECT COUNT(*) FROM billing_records WHERE order_id=?1", [&order_id], |row| row.get(0)
            ).unwrap_or(0);
            if billing_exists == 0 {
                let _ = tx.execute(
                    "INSERT INTO billing_records (id, party_type, party_id, party_name, order_id, order_no, amount, paid_amount, bill_type, bill_date, due_date, status, remark, created_at) VALUES (?1,'customer',?2,?3,?4,?5,?6,0.0,'应收货款',?7,'','unpaid','导入自动生成',?7)",
                    [&Uuid::new_v4().to_string(), &customer_id, &order.customer_name,
                     &order_id, &order_no, &total.to_string(), &now]
                );
            }
        }
        
        // 提交事务
        if let Err(e) = tx.commit() {
            result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": total, "items": item_ok, "status": "失败", "error": format!("提交事务失败: {}", e)}));
            continue;
        }
        
        // 记录操作日志
        crate::operation_logs::log_operation(&username, &username, "导入销售订单", "Excel导入", 
            &format!("订单号{} 客户{} 金额:{:.2} 明细{}条", order_no, order.customer_name, total, item_ok));
        
        if item_errors.is_empty() {
            result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": total, "items": item_ok, "status": "成功"}));
        } else {
            result.push(serde_json::json!({"order_no": order_no, "customer": order.customer_name, "amount": total, "items": item_ok, "status": "部分成功", "error": item_errors.join("; ")}));
        }
    }
    
    let success_count = result.iter().filter(|r| r["status"].as_str().unwrap_or("") == "成功" || r["status"].as_str().unwrap_or("") == "部分成功").count();
    let total_items: usize = result.iter().map(|r| r["items"].as_u64().unwrap_or(0) as usize).sum();
    crate::operation_logs::log_operation(&username, &username, "批量导入销售订单完成", "Excel导入", 
        &format!("成功{}个订单, 共{}条明细, 总计{}条输入", success_count, total_items, orders.len()));
    
    drop(conn_guard); // 释放锁，避免重入死锁
    *DB_CONN.lock().map_err(|e| e.to_string())? = Some(conn);
    Ok(result)
}

/// 批量导入采购订单（含明细）- 走完整业务流程
#[tauri::command]
pub fn import_purchase_orders(orders: Vec<ImportPurchaseOrder>, username: String) -> Result<Vec<serde_json::Value>, String> {
    if !check_permission(&username, "data_import") {
        return Err("权限不足：没有导入采购订单的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let mut conn = conn_guard.take().ok_or("Database not initialized")?;
    let mut result = Vec::new();
    // 使用毫秒级时间戳，避免同一秒内重复导入时 order_no 冲突
    let ts_millis = Local::now().timestamp_millis();
    
    for (i, order) in orders.iter().enumerate() {
        let order_id = Uuid::new_v4().to_string();
        let order_no = format!("PO-{}-{:04}", ts_millis, i + 1);
        let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
        
        // ===== 使用事务保证数据一致性 =====
        let tx = match conn.transaction() {
            Ok(tx) => tx,
            Err(e) => {
                result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": 0, "items": 0, "status": "失败", "error": format!("启动事务失败: {}", e)}));
                continue;
            }
        };
        
        // 查找或创建供应商
        let supplier_id = match tx.query_row(
            "SELECT id FROM suppliers WHERE name = ?1", [&order.supplier_name], |row| row.get::<_, String>(0)
        ) {
            Ok(sid) => sid,
            Err(_) => {
                let sid = Uuid::new_v4().to_string();
                if let Err(e) = tx.execute("INSERT INTO suppliers (id, name, created_at) VALUES (?1,?2,?3)",
                    rusqlite::params![sid, order.supplier_name, &now]) {
                    result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": 0, "items": 0, "status": "失败", "error": format!("创建供应商失败: {}", e)}));
                    continue;
                }
                sid
            }
        };
        
        let total: f64 = order.items.iter().map(|it| it.quantity * it.price).sum();
        
        // 插入采购订单
        if let Err(e) = tx.execute(
            "INSERT INTO purchase_orders (id,order_no,supplier_id,supplier_name,total_amount,status,remark,created_at) VALUES (?1,?2,?3,?4,?5,'completed',?6,?7)",
            rusqlite::params![order_id, order_no, supplier_id, order.supplier_name, total, order.remark, &now]) {
            result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": total, "items": 0, "status": "失败", "error": format!("创建订单失败: {}", e)}));
            continue;
        }
        
        let mut item_ok = 0usize;
        let mut item_errors = Vec::new();
        for item in &order.items {
            // 查找商品：优先按编码匹配，编码为空或未找到时按名称匹配
            let pid = match tx.query_row("SELECT id FROM products WHERE code=?1", [&item.product_code], |r| r.get::<_,String>(0)) {
                Ok(id) => id,
                Err(_) => match tx.query_row("SELECT id FROM products WHERE name=?1", [&item.product_name], |r| r.get::<_,String>(0)) {
                    Ok(id) => id,
                    Err(_) => {
                        item_errors.push(format!("商品'{}'(编码:{})不存在", item.product_name, item.product_code));
                        continue;
                    }
                }
            };
            
            // 插入订单明细
            if let Err(e) = tx.execute(
                "INSERT INTO purchase_order_items (id,order_id,product_id,product_name,quantity,price,amount) VALUES (?1,?2,?3,?4,?5,?6,?7)",
                rusqlite::params![Uuid::new_v4().to_string(), order_id, pid, item.product_name, item.quantity, item.price, item.quantity * item.price]
            ) {
                item_errors.push(format!("商品'{}'插入明细失败: {}", item.product_name, e));
                continue;
            }
            
            // 增加库存
            let current_stock: f64 = tx.query_row("SELECT COALESCE(stock,0.0) FROM products WHERE id=?1",
                [&pid], |row| row.get(0)).unwrap_or(0.0);
            let new_stock = current_stock + item.quantity;
            if let Err(e) = tx.execute("UPDATE products SET stock=?1, cost=?2 WHERE id=?3",
                [&new_stock.to_string(), &item.price.to_string(), &pid]) {
                item_errors.push(format!("商品'{}'增加库存失败: {}", item.product_name, e));
                continue;
            }
            
            // 记录库存变动
            if let Err(e) = tx.execute(
                "INSERT INTO inventory_records (id, product_id, product_name, change_type, quantity, before_stock, after_stock, remark, created_at) VALUES (?1,?2,?3,'purchase',?4,?5,?6,?7,?8)",
                [&Uuid::new_v4().to_string(), &pid, &item.product_name,
                 &item.quantity.to_string(), &current_stock.to_string(), &new_stock.to_string(),
                 &format!("导入采购订单: {}", order_no), &now]
            ) {
                item_errors.push(format!("商品'{}'记录库存失败: {}", item.product_name, e));
                continue;
            }
            
            item_ok += 1;
        }
        
        // 自动生成应付账单
        if item_ok > 0 {
            let billing_exists: i64 = tx.query_row(
                "SELECT COUNT(*) FROM billing_records WHERE order_id=?1", [&order_id], |row| row.get(0)
            ).unwrap_or(0);
            if billing_exists == 0 {
                let _ = tx.execute(
                    "INSERT INTO billing_records (id, party_type, party_id, party_name, order_id, order_no, amount, paid_amount, bill_type, bill_date, due_date, status, remark, created_at) VALUES (?1,'supplier',?2,?3,?4,?5,?6,0.0,'应付货款',?7,'','unpaid','导入自动生成',?7)",
                    [&Uuid::new_v4().to_string(), &supplier_id, &order.supplier_name,
                     &order_id, &order_no, &total.to_string(), &now]
                );
            }
        }
        
        // 提交事务
        if let Err(e) = tx.commit() {
            result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": total, "items": item_ok, "status": "失败", "error": format!("提交事务失败: {}", e)}));
            continue;
        }
        
        // 记录操作日志
        crate::operation_logs::log_operation(&username, &username, "导入采购订单", "Excel导入", 
            &format!("订单号{} 供应商{} 金额:{:.2} 明细{}条", order_no, order.supplier_name, total, item_ok));
        
        if item_errors.is_empty() {
            result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": total, "items": item_ok, "status": "成功"}));
        } else {
            result.push(serde_json::json!({"order_no": order_no, "supplier": order.supplier_name, "amount": total, "items": item_ok, "status": "部分成功", "error": item_errors.join("; ")}));
        }
    }
    
    let success_count = result.iter().filter(|r| r["status"].as_str().unwrap_or("") == "成功" || r["status"].as_str().unwrap_or("") == "部分成功").count();
    let total_items: usize = result.iter().map(|r| r["items"].as_u64().unwrap_or(0) as usize).sum();
    crate::operation_logs::log_operation(&username, &username, "批量导入采购订单完成", "Excel导入", 
        &format!("成功{}个订单, 共{}条明细, 总计{}条输入", success_count, total_items, orders.len()));
    
    drop(conn_guard); // 释放锁，避免重入死锁
    *DB_CONN.lock().map_err(|e| e.to_string())? = Some(conn);
    Ok(result)
}
