// 统计分析模块
use crate::database::DB_CONN;
use chrono::{Local, Duration};
use serde_json::json;

#[tauri::command]
pub fn get_statistics() -> Result<serde_json::Value, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = conn_guard.as_ref().ok_or("系统错误：数据库未初始化")?;
    
    let product_count: i64 = conn.query_row("SELECT COUNT(*) FROM products", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    let customer_count: i64 = conn.query_row("SELECT COUNT(*) FROM customers", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    let supplier_count: i64 = conn.query_row("SELECT COUNT(*) FROM suppliers", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    let sale_count: i64 = conn.query_row("SELECT COUNT(*) FROM sale_orders", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    let purchase_count: i64 = conn.query_row("SELECT COUNT(*) FROM purchase_orders", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    let total_sales: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM sale_orders",
        [],
        |row| row.get(0)
    ).map_err(|e| e.to_string())?;
    
    let total_purchases: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM purchase_orders",
        [],
        |row| row.get(0)
    ).map_err(|e| e.to_string())?;

    // 退货统计
    let sale_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='销售退货'",
        [],
        |row| row.get(0)
    ).unwrap_or(0.0);
    
    let purchase_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='采购退货'",
        [],
        |row| row.get(0)
    ).unwrap_or(0.0);

    let net_sales = total_sales - sale_returns;
    let net_purchases = total_purchases - purchase_returns;
    
    Ok(serde_json::json!({
        "productCount": product_count,
        "customerCount": customer_count,
        "supplierCount": supplier_count,
        "saleCount": sale_count,
        "purchaseCount": purchase_count,
        "totalSales": net_sales,
        "totalPurchases": net_purchases,
        "profit": net_sales - net_purchases
    }))
}

#[tauri::command]
pub fn get_statistics_detail() -> Result<serde_json::Value, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| format!("系统错误：数据库访问失败 - {}", e))?;
    let conn = conn_guard.as_ref().ok_or("系统错误：数据库未初始化")?;
    
    let today = Local::now().format("%Y-%m-%d").to_string();
    let week_ago = (Local::now() - Duration::days(7)).format("%Y-%m-%d").to_string();
    let month_ago = (Local::now() - Duration::days(30)).format("%Y-%m-%d").to_string();
    
    // 当天销售统计
    let today_sales: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM sale_orders WHERE date(created_at)=?1",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    
    let today_sales_count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM sale_orders WHERE date(created_at)=?1",
        [&today], |row| row.get(0)
    ).unwrap_or(0);
    
    // 本周销售统计
    let week_sales: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM sale_orders WHERE date(created_at)>=?1",
        [&week_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    
    let week_sales_count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM sale_orders WHERE date(created_at)>=?1",
        [&week_ago], |row| row.get(0)
    ).unwrap_or(0);
    
    // 本月销售统计
    let month_sales: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM sale_orders WHERE date(created_at)>=?1",
        [&month_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    
    let month_sales_count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM sale_orders WHERE date(created_at)>=?1",
        [&month_ago], |row| row.get(0)
    ).unwrap_or(0);
    
    // 当天采购统计
    let today_purchases: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM purchase_orders WHERE date(created_at)=?1",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    
    // 本周采购统计
    let week_purchases: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM purchase_orders WHERE date(created_at)>=?1",
        [&week_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    
    // 本月采购统计
    let month_purchases: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM purchase_orders WHERE date(created_at)>=?1",
        [&month_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    
    
    // 退货统计
    let today_sale_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='销售退货' AND date(created_at)=?1",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    let today_purchase_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='采购退货' AND date(created_at)=?1",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    let week_sale_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='销售退货' AND date(created_at)>=?1",
        [&week_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    let week_purchase_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='采购退货' AND date(created_at)>=?1",
        [&week_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    let month_sale_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='销售退货' AND date(created_at)>=?1",
        [&month_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    let month_purchase_returns: f64 = conn.query_row(
        "SELECT COALESCE(SUM(total_amount), 0.0) FROM return_orders WHERE return_type='采购退货' AND date(created_at)>=?1",
        [&month_ago], |row| row.get(0)
    ).unwrap_or(0.0);
    // 当天入库/出库统计（包含所有入库和出库类型）
    let today_stock_in: f64 = conn.query_row(
        "SELECT COALESCE(SUM(quantity), 0.0) FROM inventory_records WHERE date(created_at)=?1 AND change_type IN ('in','batch_in','purchase','assembly_in','transfer_in')",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    
    let today_stock_out: f64 = conn.query_row(
        "SELECT COALESCE(SUM(ABS(quantity)), 0.0) FROM inventory_records WHERE date(created_at)=?1 AND change_type IN ('out','batch_out','sale','return_out','assembly_out','disassembly_out','transfer_out')",
        [&today], |row| row.get(0)
    ).unwrap_or(0.0);
    
    // 应收应付概况
    let total_receivable: f64 = conn.query_row(
        "SELECT COALESCE(SUM(amount - paid_amount), 0.0) FROM billing_records WHERE party_type='customer' AND status!='paid'",
        [], |row| row.get(0)
    ).unwrap_or(0.0);
    
    let total_payable: f64 = conn.query_row(
        "SELECT COALESCE(SUM(amount - paid_amount), 0.0) FROM billing_records WHERE party_type='supplier' AND status!='paid'",
        [], |row| row.get(0)
    ).unwrap_or(0.0);
    
    Ok(serde_json::json!({
        "todaySales": today_sales - today_sale_returns,
        "todaySalesCount": today_sales_count,
        "weekSales": week_sales - week_sale_returns,
        "weekSalesCount": week_sales_count,
        "monthSales": month_sales - month_sale_returns,
        "monthSalesCount": month_sales_count,
        "todayPurchases": today_purchases - today_purchase_returns,
        "weekPurchases": week_purchases - week_purchase_returns,
        "monthPurchases": month_purchases - month_purchase_returns,
        "todaySaleReturns": today_sale_returns,
        "todayPurchaseReturns": today_purchase_returns,
        "weekSaleReturns": week_sale_returns,
        "weekPurchaseReturns": week_purchase_returns,
        "monthSaleReturns": month_sale_returns,
        "monthPurchaseReturns": month_purchase_returns,
        "todayStockIn": today_stock_in,
        "todayStockOut": today_stock_out,
        "totalReceivable": total_receivable,
        "totalPayable": total_payable
    }))
}

/// 全局搜索 - 跨表模糊搜索
#[tauri::command]
pub fn global_search(keyword: String) -> Result<Vec<serde_json::Value>, String> {
    let conn_guard = DB_CONN.lock().map_err(|e| format!("系统错误：数据库访问失败 - {}", e))?;
    let conn = conn_guard.as_ref().ok_or("系统错误：数据库未初始化")?;
    let mut results: Vec<serde_json::Value> = Vec::new();
    let like = format!("%{}%", keyword);

    // 搜索商品
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, name, code, price, stock FROM products WHERE name LIKE ?1 OR code LIKE ?1 OR brand LIKE ?1 OR model LIKE ?1 OR barcode LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            Ok(json!({
                "type": "product",
                "typeLabel": "商品",
                "id": row.get::<_, String>(0)?,
                "label": format!("{}（{}）", row.get::<_, String>(1)?, row.get::<_, String>(2)?),
                "subtitle": format!("售价: ¥{:.2}  库存: {}", row.get::<_, f64>(3)?, row.get::<_, f64>(4)?),
                "link": "/products"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    // 搜索客户
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, name, phone, contact FROM customers WHERE name LIKE ?1 OR phone LIKE ?1 OR contact LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            let phone: String = row.get::<_, Option<String>>(2)?.unwrap_or_default();
            let contact: String = row.get::<_, Option<String>>(3)?.unwrap_or_default();
            Ok(json!({
                "type": "customer",
                "typeLabel": "客户",
                "id": row.get::<_, String>(0)?,
                "label": row.get::<_, String>(1)?,
                "subtitle": format!("电话: {}  联系人: {}", phone, contact),
                "link": "/customers"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    // 搜索供应商
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, name, phone, contact FROM suppliers WHERE name LIKE ?1 OR phone LIKE ?1 OR contact LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            let phone: String = row.get::<_, Option<String>>(2)?.unwrap_or_default();
            let contact: String = row.get::<_, Option<String>>(3)?.unwrap_or_default();
            Ok(json!({
                "type": "supplier",
                "typeLabel": "供应商",
                "id": row.get::<_, String>(0)?,
                "label": row.get::<_, String>(1)?,
                "subtitle": format!("电话: {}  联系人: {}", phone, contact),
                "link": "/suppliers"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    // 搜索销售订单
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, order_no, customer_name, total_amount FROM sale_orders WHERE order_no LIKE ?1 OR customer_name LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            Ok(json!({
                "type": "sale_order",
                "typeLabel": "销售订单",
                "id": row.get::<_, String>(0)?,
                "label": row.get::<_, String>(1)?,
                "subtitle": format!("客户: {}  金额: ¥{:.2}", row.get::<_, String>(2)?, row.get::<_, f64>(3)?),
                "link": "/sale-orders"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    // 搜索采购订单
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, order_no, supplier_name, total_amount FROM purchase_orders WHERE order_no LIKE ?1 OR supplier_name LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            Ok(json!({
                "type": "purchase_order",
                "typeLabel": "采购订单",
                "id": row.get::<_, String>(0)?,
                "label": row.get::<_, String>(1)?,
                "subtitle": format!("供应商: {}  金额: ¥{:.2}", row.get::<_, String>(2)?, row.get::<_, f64>(3)?),
                "link": "/purchase-orders"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    // 搜索仓库
    if let Ok(mut stmt) = conn.prepare(
        "SELECT id, name, code FROM warehouses WHERE name LIKE ?1 OR code LIKE ?1 LIMIT 10"
    ) {
        if let Ok(rows) = stmt.query_map([&like], |row| {
            Ok(json!({
                "type": "warehouse",
                "typeLabel": "仓库",
                "id": row.get::<_, String>(0)?,
                "label": format!("{}（{}）", row.get::<_, String>(1)?, row.get::<_, String>(2)?),
                "subtitle": String::new(),
                "link": "/warehouses"
            }))
        }) {
            for row in rows.flatten() { results.push(row); }
        }
    }

    Ok(results)
}
