// 商品管理模块
use serde::{Deserialize, Serialize};
use crate::models::*;
use crate::database::DB_CONN;
use crate::operation_logs::log_operation;
use crate::error_util;
use uuid::Uuid;
use chrono::Local;
use std::fs;
use crate::permissions::check_permission;

#[derive(Debug, Deserialize)]
pub struct AddProductRequest {
    pub name: String,
    pub code: String,
    pub category: String,
    pub unit: String,
    pub unit2: String,
    pub price: f64,
    pub cost: f64,
    pub stock: f64,
    pub stock_unit2: f64,
    pub batch_no: String,
    pub production_date: String,
    pub expiry_date: String,
    pub brand: String,
    pub model: String,
    pub package_type: String,
    pub description: String,
    pub image_path: String,
    pub warehouse_id: String,
    pub location_id: String,
    pub barcode: Option<String>,
    pub trace_code: Option<String>,
    pub status: Option<String>,
    pub category_id: Option<String>,
    pub unit_rate: f64,
}

#[tauri::command]
pub fn add_product(request: AddProductRequest, username: String, real_name: String) -> Result<Product, String> {
    if !check_permission(&username, "products_edit") {
        return Err("权限不足：没有新增/编辑商品的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_mut().ok_or("系统错误：数据库未初始化")?;
    
    // 检查商品编码是否已存在
    let mut check_stmt = conn.prepare(
        "SELECT COUNT(*) FROM products WHERE code = ?1"
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    let count: i64 = check_stmt.query_row([&request.code], |row| row.get(0))
        .map_err(|e| error_util::to_chinese_error(e))?;
    
    if count > 0 {
        return Err(format!("商品编码 '{}' 已存在，请使用其他编码", request.code));
    }
    
    let id = Uuid::new_v4().to_string();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    
    let barcode = request.barcode.clone().unwrap_or_default();
    let trace_code = request.trace_code.clone().unwrap_or_default();
    let status = request.status.clone().unwrap_or_else(|| "active".to_string());
    let category_id = request.category_id.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, status, category_id, 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, ?25, ?26)",
        [&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, &request.production_date, &request.expiry_date,
         &request.brand, &request.model, &request.package_type, &request.description,
         &request.image_path, &request.warehouse_id, &request.location_id, &barcode, &trace_code,
         &status, &category_id, &request.unit_rate.to_string(), &now]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    log_operation(&username, &real_name, "添加商品", "商品管理", &format!("商品'{}' 编码{}", request.name, request.code));
    
    Ok(Product {
        id,
        name: request.name,
        code: request.code,
        category: request.category,
        unit: request.unit,
        unit2: request.unit2,
        price: request.price,
        cost: request.cost,
        stock: request.stock,
        stock_unit2: request.stock_unit2,
        batch_no: request.batch_no,
        production_date: request.production_date,
        expiry_date: request.expiry_date,
        brand: request.brand,
        model: request.model,
        package_type: request.package_type,
        description: request.description,
        image_path: request.image_path,
        warehouse_id: request.warehouse_id,
        location_id: request.location_id,
        barcode: barcode.clone(),
        trace_code: trace_code.clone(),
        status: status.clone(),
        category_id: category_id.clone(),
        unit_rate: request.unit_rate,
        created_at: now,
    })
}

/// 根据编码查找商品，不存在则自动创建（用于销售/采购订单首次填写）
#[tauri::command]
pub fn get_or_create_product(code: String, name: String, price: f64, cost: f64, unit: String) -> Result<Product, String> {
    let mut conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_mut().ok_or("系统错误：数据库未初始化")?;
    
    // 先按编码查找
    if let Ok(product) = conn.query_row(
        "SELECT * FROM products WHERE code=?1",
        [&code], |row| {
            Ok(Product {
                id: row.get(0)?, name: row.get(1)?, code: row.get(2)?, category: row.get::<_, Option<String>>(3)?.unwrap_or_default(),
                unit: row.get::<_, Option<String>>(4)?.unwrap_or_default(), 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(),
            })
        }
    ) {
        return Ok(product);
    }
    
    // 不存在则自动创建
    let id = Uuid::new_v4().to_string();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let unit = if unit.is_empty() { String::new() } else { unit };
    
    conn.execute(
        "INSERT INTO products (id, name, code, unit, price, cost, stock, stock_unit2, batch_no, production_date, expiry_date, brand, model, description, created_at) 
         VALUES (?1,?2,?3,?4,?5,?6,0,0,'','','','','',?7)",
        [&id, &name, &code, &unit, &price.to_string(), &cost.to_string(), &now]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    Ok(Product {
        id, name, code, category: String::new(), unit, unit2: String::new(),
        price, cost, stock: 0.0, stock_unit2: 0.0,
        batch_no: String::new(), production_date: String::new(), expiry_date: String::new(),
        brand: String::new(), model: String::new(), package_type: String::new(),
        description: String::new(), image_path: String::new(),
        warehouse_id: String::new(), location_id: String::new(),
        barcode: String::new(), trace_code: String::new(),
        status: "active".to_string(), category_id: String::new(),
        unit_rate: 1.0,
        created_at: now,
    })
}

#[tauri::command]
pub fn get_products(username: String) -> Result<Vec<Product>, String> {
    if !check_permission(&username, "products_view") {
        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 created_at DESC"
    ).map_err(|e| e.to_string())?;
    let products = stmt.query_map([], |row| {
        Ok(Product {
            id: row.get::<_, String>(0).unwrap_or_default(),
            name: row.get::<_, String>(1).unwrap_or_default(),
            code: row.get::<_, String>(2).unwrap_or_default(),
            category: row.get::<_, Option<String>>(3)?.unwrap_or_default(),
            unit: row.get::<_, String>(4).unwrap_or_default(),
            unit2: row.get::<_, Option<String>>(5)?.unwrap_or_default(),
            price: row.get::<_, f64>(6).unwrap_or(0.0),
            cost: row.get::<_, f64>(7).unwrap_or(0.0),
            stock: row.get::<_, f64>(8).unwrap_or(0.0),
            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_default(),
            trace_code: row.get::<_, Option<String>>(22)?.unwrap_or_default(),
            status: row.get::<_, Option<String>>(23)?.unwrap_or_default(),
            category_id: row.get::<_, Option<String>>(24)?.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 update_product(product: Product, username: String, real_name: String) -> Result<Product, String> {
    if !check_permission(&username, "products_edit") {
        return Err("权限不足：没有新增/编辑商品的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_mut().ok_or("系统错误：数据库未初始化")?;
    
    // 检查商品编码是否已被其他商品使用
    let mut check_stmt = conn.prepare(
        "SELECT COUNT(*) FROM products WHERE code = ?1 AND id != ?2"
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    let count: i64 = check_stmt.query_row([&product.code, &product.id], |row| row.get(0))
        .map_err(|e| error_util::to_chinese_error(e))?;
    
    if count > 0 {
        return Err(format!("商品编码 '{}' 已被其他商品使用，请使用其他编码", product.code));
    }
    
    conn.execute(
        "UPDATE products SET name=?1, code=?2, category=?3, unit=?4, unit2=?5, price=?6, cost=?7, stock=?8, stock_unit2=?9,
         batch_no=?10, production_date=?11, expiry_date=?12, brand=?13, model=?14, package_type=?15, description=?16, warehouse_id=?17, location_id=?18,
         barcode=?19, trace_code=?20, status=?21, category_id=?22, unit_rate=?23 WHERE id=?24",
        [&product.name, &product.code, &product.category, &product.unit, &product.unit2,
         &product.price.to_string(), &product.cost.to_string(), &product.stock.to_string(),
         &product.stock_unit2.to_string(), &product.batch_no, &product.production_date, &product.expiry_date,
         &product.brand, &product.model, &product.package_type, &product.description,
         &product.warehouse_id, &product.location_id, &product.barcode, &product.trace_code,
         &product.status, &product.category_id, &product.unit_rate.to_string(), &product.id]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    log_operation(&username, &real_name, "编辑商品", "商品管理", &format!("商品'{}' 编码{}", product.name, product.code));
    
    Ok(product)
}

#[tauri::command]
pub fn delete_product(product_id: String, username: String, real_name: String) -> Result<(), String> {
    if !check_permission(&username, "products_delete") {
        return Err("权限不足：没有删除商品的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_mut().ok_or("系统错误：数据库未初始化")?;

    // 先删除关联的库存记录
    conn.execute("DELETE FROM inventory_records WHERE product_id = ?1", [&product_id]).map_err(|e| error_util::to_chinese_error(e))?;

    // 先删除关联的销售订单明细
    conn.execute("DELETE FROM sale_order_items WHERE product_id = ?1", [&product_id]).map_err(|e| error_util::to_chinese_error(e))?;

    // 先删除关联的采购订单明细
    conn.execute("DELETE FROM purchase_order_items WHERE product_id = ?1", [&product_id]).map_err(|e| error_util::to_chinese_error(e))?;

    let product_name: String = conn.query_row("SELECT name FROM products WHERE id=?1", [&product_id], |row| row.get(0)).unwrap_or_default();
    // 最后删除商品本身
    conn.execute("DELETE FROM products WHERE id = ?1", [&product_id]).map_err(|e| error_util::to_chinese_error(e))?;

    log_operation(&username, &real_name, "删除商品", "商品管理", &format!("删除商品'{}'", product_name));

    Ok(())
}

#[tauri::command]
pub fn batch_delete_products(product_ids: Vec<String>, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "products_delete") {
        return Err("权限不足：没有删除商品的权限".to_string());
    }
    let mut conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_mut().ok_or("系统错误：数据库未初始化")?;

    let tx = conn.transaction().map_err(|e| error_util::to_chinese_error(e))?;
    let mut deleted = 0;

    for pid in &product_ids {
        let _ = tx.execute("DELETE FROM inventory_records WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM sale_order_items WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM purchase_order_items WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM return_order_items WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM transfer_order_items WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM product_specs WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM product_packages WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM product_documents WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM warehouse_stock WHERE product_id = ?1", [pid]);
        let _ = tx.execute("DELETE FROM stock_transactions WHERE product_id = ?1", [pid]);
        let affected = tx.execute("DELETE FROM products WHERE id = ?1", [pid]).unwrap_or(0);
        if affected > 0 { deleted += 1; }
    }

    tx.commit().map_err(|e| error_util::to_chinese_error(e))?;

    log_operation(&username, &real_name, "批量删除商品", "商品管理", &format!("批量删除{}个商品（共{}个ID）", deleted, product_ids.len()));

    Ok(format!("成功删除 {} 个商品", deleted))
}

// ============ 商品图片管理 ============

#[derive(Debug, Deserialize)]
pub struct UpdateProductImageRequest {
    pub product_id: String,
    pub image_path: String,
}

#[tauri::command]
pub fn update_product_image(request: UpdateProductImageRequest) -> Result<String, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    conn.execute("UPDATE products SET image_path=?1 WHERE id=?2",
        [&request.image_path, &request.product_id]
    ).map_err(|e| e.to_string())?;
    Ok("图片更新成功".to_string())
}

#[tauri::command]
pub fn save_product_image(source_path: String, _app_handle: tauri::AppHandle) -> Result<String, String> {
    // 读取图片文件并转为 base64，直接存入数据库
    let img_bytes = fs::read(&source_path).map_err(|e| format!("读取图片失败: {}", e))?;
    let b64 = base64::Engine::encode(&base64::engine::general_purpose::STANDARD, &img_bytes);
    // 返回 data URL 格式，前端可以直接用
    Ok(format!("data:image/png;base64,{}", b64))
}

// ============ 商品规格管理 ============

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ProductSpec {
    pub id: String,
    pub product_id: String,
    pub spec_name: String,
    pub spec_value: String,
    pub price: f64,
    pub stock: f64,
    pub created_at: String,
}

#[derive(Debug, Deserialize)]
pub struct AddProductSpecRequest {
    pub product_id: String,
    pub spec_name: String,
    pub spec_value: String,
    pub price: f64,
    pub stock: f64,
}

#[tauri::command]
pub fn add_product_spec(request: AddProductSpecRequest) -> Result<ProductSpec, 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 id = Uuid::new_v4().to_string();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    conn.execute(
        "INSERT INTO product_specs (id, product_id, spec_name, spec_value, price, stock, created_at) VALUES (?1,?2,?3,?4,?5,?6,?7)",
        [&id, &request.product_id, &request.spec_name, &request.spec_value, 
         &request.price.to_string(), &request.stock.to_string(), &now]
    ).map_err(|e| e.to_string())?;
    Ok(ProductSpec { 
        id, 
        product_id: request.product_id, 
        spec_name: request.spec_name, 
        spec_value: request.spec_value, 
        price: request.price, 
        stock: request.stock, 
        created_at: now 
    })
}

#[tauri::command]
pub fn get_product_specs(product_id: String) -> Result<Vec<ProductSpec>, 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 product_specs WHERE product_id=?1 ORDER BY created_at")
        .map_err(|e| e.to_string())?;
    let specs = stmt.query_map([&product_id], |row| {
        Ok(ProductSpec {
            id: row.get(0)?, 
            product_id: row.get(1)?, 
            spec_name: row.get(2)?,
            spec_value: row.get(3)?, 
            price: row.get(4)?, 
            stock: row.get::<_, f64>(5)?, 
            created_at: row.get(6)?
        })
    }).map_err(|e| e.to_string())?;
    let mut result = Vec::new();
    for s in specs { 
        result.push(s.map_err(|e| e.to_string())?); 
    }
    Ok(result)
}

#[tauri::command]
pub fn delete_product_spec(spec_id: String) -> Result<String, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    conn.execute("DELETE FROM product_specs WHERE id=?1", [&spec_id])
        .map_err(|e| e.to_string())?;
    Ok("删除成功".to_string())
}

// ============ 批量添加商品 ============

#[tauri::command]
pub fn batch_add_products(products: Vec<AddProductRequest>) -> Result<Vec<Product>, 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 now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let mut result = Vec::new();

    for request in &products {
        let exists: i64 = conn.query_row("SELECT COUNT(*) FROM products WHERE code=?1",
            [&request.code], |row| row.get(0)).unwrap_or(0);
        if exists > 0 { continue; }

let id = Uuid::new_v4().to_string();
        let bcode = request.barcode.clone().unwrap_or_default();
        let tcode = request.trace_code.clone().unwrap_or_default();
        let st = request.status.clone().unwrap_or_else(|| "active".to_string());
        let cid = request.category_id.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, warehouse_id, location_id, barcode, trace_code, status, category_id, 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,?25)",
            [&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, &request.production_date, &request.expiry_date,
             &request.brand, &request.model, &request.package_type, &request.description,
             &request.warehouse_id, &request.location_id, &bcode, &tcode, &st, &cid,
             &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: request.production_date.clone(), expiry_date: request.expiry_date.clone(),
            brand: request.brand.clone(), model: request.model.clone(),
            package_type: request.package_type.clone(), description: request.description.clone(),
            image_path: String::new(), warehouse_id: request.warehouse_id.clone(),
            location_id: request.location_id.clone(),
            barcode: String::new(), trace_code: String::new(), status: String::from("active"), category_id: String::new(),
            unit_rate: request.unit_rate,
            created_at: now.clone(),
        });
    }
    Ok(result)
}

// ============ 更新商品规格 ============

#[derive(Debug, Deserialize)]
pub struct UpdateProductSpecRequest {
    pub id: String,
    pub spec_name: String,
    pub spec_value: String,
    pub price: f64,
    pub stock: f64,
}

#[tauri::command]
pub fn update_product_spec(request: UpdateProductSpecRequest) -> Result<String, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("Database not initialized")?;
    conn.execute(
        "UPDATE product_specs SET spec_name=?1, spec_value=?2, price=?3, stock=?4 WHERE id=?5",
        [&request.spec_name, &request.spec_value, &request.price.to_string(),
         &request.stock.to_string(), &request.id]
    ).map_err(|e| e.to_string())?;
    Ok("更新成功".to_string())
}
