// 库位管理模块
use serde::Deserialize;
use crate::models::*;
use crate::database::DB_CONN;
use crate::operation_logs::log_operation;
use crate::error_util;
use crate::permissions::check_permission;
use uuid::Uuid;
use chrono::Local;

#[derive(Debug, Deserialize)]
pub struct AddLocationRequest {
    pub warehouse_id: String,
    pub warehouse_name: String,
    pub code: String,
    pub name: String,
    pub zone: String,
    pub aisle: String,
    pub shelf: String,
    pub level: String,
    pub position: String,
    pub description: String,
}

#[tauri::command]
pub fn add_location(request: AddLocationRequest, username: String, real_name: String) -> Result<Location, String> {
    if !check_permission(&username, "warehouses_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 exists: i64 = conn.query_row(
        "SELECT COUNT(*) FROM locations WHERE code = ?1",
        [&request.code],
        |row| row.get(0)
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    if exists > 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();
    
    conn.execute(
        "INSERT INTO locations (id, warehouse_id, warehouse_name, code, name, zone, aisle, shelf, level, position, description, created_at) 
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)",
        [&id, &request.warehouse_id, &request.warehouse_name, &request.code, &request.name, 
         &request.zone, &request.aisle, &request.shelf, &request.level, &request.position, 
         &request.description, &now]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    log_operation(&username, &real_name, "添加", "库位管理", &format!("添加库位: {} ({})", request.name, request.code));
    
    Ok(Location {
        id,
        warehouse_id: request.warehouse_id,
        warehouse_name: request.warehouse_name,
        code: request.code,
        name: request.name,
        zone: request.zone,
        aisle: request.aisle,
        shelf: request.shelf,
        level: request.level,
        position: request.position,
        description: request.description,
        created_at: now,
    })
}

#[tauri::command]
pub fn get_locations(warehouse_id: String) -> Result<Vec<Location>, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_ref().ok_or("系统错误：数据库未初始化")?;

    let sql = if warehouse_id.is_empty() {
        "SELECT id, warehouse_id, warehouse_name, code, name, zone, aisle, shelf, level, position, description, created_at FROM locations ORDER BY created_at DESC"
    } else {
        "SELECT id, warehouse_id, warehouse_name, code, name, zone, aisle, shelf, level, position, description, created_at FROM locations WHERE warehouse_id = ?1 ORDER BY created_at DESC"
    };
    
    let mut stmt = conn.prepare(sql).map_err(|e| error_util::to_chinese_error(e))?;
    
    let row_to_location = |row: &rusqlite::Row| -> rusqlite::Result<Location> {
        Ok(Location {
            id: row.get(0)?,
            warehouse_id: row.get(1)?,
            warehouse_name: row.get::<_, String>(2).unwrap_or_default(),
            code: row.get(3)?,
            name: row.get(4)?,
            zone: row.get::<_, Option<String>>(5)?.unwrap_or_default(),
            aisle: row.get::<_, Option<String>>(6)?.unwrap_or_default(),
            shelf: row.get::<_, Option<String>>(7)?.unwrap_or_default(),
            level: row.get::<_, Option<String>>(8)?.unwrap_or_default(),
            position: row.get::<_, Option<String>>(9)?.unwrap_or_default(),
            description: row.get::<_, Option<String>>(10)?.unwrap_or_default(),
            created_at: row.get::<_, String>(11).unwrap_or_default(),
        })
    };
    
    let locations = if warehouse_id.is_empty() {
        stmt.query_map([], row_to_location)
    } else {
        stmt.query_map([&warehouse_id], row_to_location)
    }.map_err(|e| error_util::to_chinese_error(e))?;
    
    let result = locations.collect::<Result<Vec<_>, _>>().map_err(|e| error_util::to_chinese_error(e))?;
    
    Ok(result)
}

/// 获取所有库位（无参数版本，避免 Tauri IPC 序列化问题）
/// 使用最简查询方式，兼容旧数据库
#[tauri::command]
pub fn get_all_locations() -> Result<Vec<Location>, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| error_util::to_chinese_error(e))?;
    let conn = conn_guard.as_ref().ok_or("系统错误：数据库未初始化")?;
    
    // 先检查表是否存在及记录数
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM locations", [], |r| r.get(0)).unwrap_or(-1);
    if count <= 0 {
        return Ok(Vec::new());
    }
    
    // 用最简方式查询，每个字段都做类型容错
    let mut stmt = match conn.prepare("SELECT id, warehouse_id, warehouse_name, code, name, zone, aisle, shelf, level, position, description, created_at FROM locations ORDER BY created_at DESC") {
        Ok(s) => s,
        Err(_) => {
            // 如果显式列名失败，尝试用 SELECT *
            conn.prepare("SELECT * FROM locations ORDER BY created_at DESC").map_err(|e| error_util::to_chinese_error(e))?
        }
    };
    
    let locations = stmt.query_map([], |row| {
        Ok(Location {
            id: row.get::<_, String>(0).unwrap_or_default(),
            warehouse_id: row.get::<_, String>(1).unwrap_or_default(),
            warehouse_name: row.get::<_, String>(2).unwrap_or_default(),
            code: row.get::<_, String>(3).unwrap_or_default(),
            name: row.get::<_, String>(4).unwrap_or_default(),
            zone: row.get::<_, Option<String>>(5).ok().flatten().unwrap_or_default(),
            aisle: row.get::<_, Option<String>>(6).ok().flatten().unwrap_or_default(),
            shelf: row.get::<_, Option<String>>(7).ok().flatten().unwrap_or_default(),
            level: row.get::<_, Option<String>>(8).ok().flatten().unwrap_or_default(),
            position: row.get::<_, Option<String>>(9).ok().flatten().unwrap_or_default(),
            description: row.get::<_, Option<String>>(10).ok().flatten().unwrap_or_default(),
            created_at: row.get::<_, String>(11).unwrap_or_default(),
        })
    }).map_err(|e| error_util::to_chinese_error(e))?;
    
    let result = locations.collect::<Result<Vec<_>, _>>().map_err(|e| error_util::to_chinese_error(e))?;
    Ok(result)
}

/// 调试命令：返回 locations 表的信息
#[tauri::command]
pub fn debug_locations() -> 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")?;
    
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM locations", [], |r| r.get(0)).unwrap_or(-1);
    
    // 使用简单方式查询列名
    let mut stmt = conn.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='locations'")
        .map_err(|e| e.to_string())?;
    let create_sql: String = stmt.query_row([], |row| row.get(0)).unwrap_or_default();
    
    Ok(format!("locations表: {} 条记录\n建表语句: {}", count, create_sql))
}

#[tauri::command]
pub fn update_location(request: Location, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "warehouses_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("系统错误：数据库未初始化")?;
    
    conn.execute(
        "UPDATE locations SET warehouse_id = ?1, warehouse_name = ?2, code = ?3, name = ?4, zone = ?5, aisle = ?6, shelf = ?7, level = ?8, position = ?9, description = ?10 WHERE id = ?11",
        [&request.warehouse_id, &request.warehouse_name, &request.code, &request.name, 
         &request.zone, &request.aisle, &request.shelf, &request.level, &request.position, 
         &request.description, &request.id]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    log_operation(&username, &real_name, "修改", "库位管理", &format!("修改库位: {} ({})", request.name, request.code));
    
    Ok("更新成功".to_string())
}

#[tauri::command]
pub fn delete_location(location_id: String, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "warehouses_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 product_count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM products WHERE location_id = ?1",
        [&location_id],
        |row| row.get(0)
    ).map_err(|e| error_util::to_chinese_error(e))?;
    
    if product_count > 0 {
        return Err("该库位下有商品，无法删除".to_string());
    }
    
    let loc_info: String = conn.query_row("SELECT code || ' ' || name FROM locations WHERE id=?1", [&location_id], |row| row.get(0)).unwrap_or_default();
    conn.execute("DELETE FROM locations WHERE id = ?1", [&location_id])
        .map_err(|e| error_util::to_chinese_error(e))?;

    log_operation(&username, &real_name, "删除", "库位管理", &format!("删除库位: {}", loc_info));
    
    Ok("删除成功".to_string())
}
