// 应收应付/对账单模块
use crate::database::DB_CONN;
use crate::models::*;
use crate::operation_logs::log_operation;
use crate::error_util;
use crate::permissions::check_permission;
use uuid::Uuid;
use chrono::Local;
use serde::{Deserialize, Serialize};

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct StatementItem {
    pub date: String,
    pub order_no: String,
    pub description: String,
    pub bill_type: String,
    pub amount: f64,
    pub paid_amount: f64,
    pub balance: f64,
    pub product_name: String,
    pub product_code: String,
    pub brand: String,
    pub model: String,
    pub quantity: f64,
    pub price: f64,
    pub unit: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct CustomerStatement {
    pub customer_id: String,
    pub customer_name: String,
    pub begin_balance: f64,
    pub end_balance: f64,
    pub total_receivable: f64,
    pub total_paid: f64,
    pub items: Vec<StatementItem>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SupplierStatement {
    pub supplier_id: String,
    pub supplier_name: String,
    pub begin_balance: f64,
    pub end_balance: f64,
    pub total_payable: f64,
    pub total_paid: f64,
    pub items: Vec<StatementItem>,
}

#[derive(Debug, Deserialize)]
pub struct CreateBillingRequest {
    pub party_type: String,
    pub party_id: String,
    pub party_name: String,
    pub order_id: String,
    pub order_no: String,
    pub amount: f64,
    pub bill_type: String,
    pub bill_date: String,
    pub due_date: String,
    pub remark: String,
}

#[derive(Debug, Deserialize)]
pub struct UpdateBillingPaymentRequest {
    pub billing_id: String,
    pub paid_amount: f64,
    pub party_type: String,
    pub party_id: String,
    pub party_name: String,
}

// === 应收应付 CRUD ===

#[tauri::command]
pub fn create_billing(request: CreateBillingRequest, username: String, real_name: String) -> Result<BillingRecord, String> {
    if !check_permission(&username, "billing_edit") {
        return Err("权限不足：没有新增账单的权限".to_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 id = Uuid::new_v4().to_string();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let bill_date = if request.bill_date.is_empty() { now.chars().take(10).collect() } else { request.bill_date.clone() };
    conn.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,?2,?3,?4,?5,?6,?7,0.0,?8,?9,?10,'unpaid',?11,?12)",
        [&id, &request.party_type, &request.party_id, &request.party_name, &request.order_id, &request.order_no,
         &request.amount.to_string(), &request.bill_type, &bill_date, &request.due_date, &request.remark, &now]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    log_operation(&username, &real_name, "创建账单", "财务管理", &format!("{}{}", request.bill_type, request.amount));
    Ok(BillingRecord {
        id, party_type: request.party_type, party_id: request.party_id, party_name: request.party_name,
        order_id: request.order_id, order_no: request.order_no, amount: request.amount, paid_amount: 0.0,
        bill_type: request.bill_type, bill_date, due_date: request.due_date, status: "unpaid".to_string(),
        remark: request.remark, created_at: now,
    })
}

#[tauri::command]
pub fn update_billing_payment(request: UpdateBillingPaymentRequest, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "billing_edit") {
        return Err("权限不足：没有编辑账单的权限".to_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 (total_amount, order_id, _order_no, party_type): (f64, String, String, String) = conn.query_row(
        "SELECT COALESCE(amount, 0.0), COALESCE(order_id,''), COALESCE(order_no,''), COALESCE(party_type,'') FROM billing_records WHERE id=?1",
        [&request.billing_id],
        |row| Ok((row.get(0)?, row.get::<_, String>(1).unwrap_or_default(), row.get::<_, String>(2).unwrap_or_default(), row.get::<_, String>(3).unwrap_or_default()))
    ).map_err(|e| error_util::to_chinese_error(e))?;
    // 前端传的是累加后的新总额，后端不再重复累加
    let new_paid = request.paid_amount;
    // 判断状态：未付 / 部分付 / 已结清（兼容正负金额）
    let status = if (new_paid - total_amount).abs() < 0.001 {
        "paid"
    } else if new_paid.abs() < 0.001 {
        "unpaid"
    } else {
        "partial"
    };
    conn.execute("UPDATE billing_records SET paid_amount=?1, status=?2 WHERE id=?3",
        [&new_paid.to_string(), status, &request.billing_id]).map_err(|e| error_util::to_chinese_error(e))?;

    // 同步更新对应订单的发货/入库状态
    if status == "paid" && !order_id.is_empty() {
        if party_type == "customer" {
            // 销售订单：标记为已回款
            let _ = conn.execute("UPDATE sale_orders SET is_invoiced=1, status='paid' WHERE id=?1 AND status NOT IN ('cancelled','paid')", [&order_id]);
        } else if party_type == "supplier" {
            // 采购订单：标记为已付款
            let _ = conn.execute("UPDATE purchase_orders SET is_invoiced=1, status='paid' WHERE id=?1 AND status NOT IN ('cancelled','paid')", [&order_id]);
        }
    }

    log_operation(&username, &real_name, "账单收款/付款", "财务管理", &format!("{} 金额{} 状态:{}", request.party_name, request.paid_amount, status));
    Ok("更新成功".to_string())
}

#[tauri::command]
pub fn update_billing(request: CreateBillingRequest, billing_id: String, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "billing_edit") {
        return Err("权限不足：没有编辑账单的权限".to_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 bill_date = if request.bill_date.is_empty() { Local::now().format("%Y-%m-%d").to_string() } else { request.bill_date.clone() };
    let paid_amount: f64 = conn.query_row(
        "SELECT COALESCE(paid_amount, 0.0) FROM billing_records WHERE id=?1",
        [&billing_id], |row| row.get(0)
    ).unwrap_or(0.0);
    let status = if (paid_amount - request.amount).abs() < 0.001 {
        "paid"
    } else if paid_amount.abs() < 0.001 {
        "unpaid"
    } else {
        "partial"
    };
    conn.execute(
        "UPDATE billing_records SET party_type=?1, party_id=?2, party_name=?3, order_id=?4, order_no=?5, amount=?6, bill_type=?7, bill_date=?8, due_date=?9, remark=?10, status=?11 WHERE id=?12",
        [&request.party_type, &request.party_id, &request.party_name, &request.order_id, &request.order_no,
         &request.amount.to_string(), &request.bill_type, &bill_date, &request.due_date, &request.remark, status, &billing_id]
    ).map_err(|e| error_util::to_chinese_error(e))?;

    // 同步更新对应订单的 total_amount，确保仪表盘统计正确
    if !request.order_id.is_empty() {
        if request.party_type == "customer" {
            let _ = conn.execute("UPDATE sale_orders SET total_amount=?1 WHERE id=?2",
                [&request.amount.to_string(), &request.order_id]);
        } else if request.party_type == "supplier" {
            let _ = conn.execute("UPDATE purchase_orders SET total_amount=?1 WHERE id=?2",
                [&request.amount.to_string(), &request.order_id]);
        }
    }

    log_operation(&username, &real_name, "编辑账单", "财务管理", &format!("账单{}", billing_id));
    Ok("更新成功".to_string())
}

#[tauri::command]
pub fn auto_create_billing(party_type: String, party_id: String, party_name: String, order_id: String, order_no: String, amount: f64, bill_type: String, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "billing_edit") {
        return Err("权限不足：没有创建账单的权限".to_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 existing: i64 = conn.query_row("SELECT COUNT(*) FROM billing_records WHERE order_id=?1", [&order_id], |row| row.get(0)).unwrap_or(0);
    if existing > 0 { return Ok("账单已存在，跳过自动生成".to_string()); }
    let id = Uuid::new_v4().to_string();
    let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
    let date = now.chars().take(10).collect::<String>();
    conn.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,?2,?3,?4,?5,?6,?7,0.0,?8,?9,'','unpaid','自动生成',?10)",
        [&id, &party_type, &party_id, &party_name, &order_id, &order_no, &amount.to_string(), &bill_type, &date, &now]
    ).map_err(|e| error_util::to_chinese_error(e))?;
    log_operation(&username, &real_name, "自动生成账单", "财务管理", &format!("{}{}", bill_type, amount));
    Ok("账单已自动生成".to_string())
}

#[tauri::command]
pub fn get_billings(party_type: Option<String>, party_id: Option<String>, status: Option<String>, username: String) -> Result<Vec<BillingRecord>, String> {
    if !check_permission(&username, "billing_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 where_clause: String = {
        let mut conditions = Vec::new();
        if let Some(ref pt) = party_type { if !pt.is_empty() { conditions.push(format!("party_type='{}'", pt)); } }
        if let Some(ref pi) = party_id { if !pi.is_empty() { conditions.push(format!("party_id='{}'", pi)); } }
        if let Some(ref s) = status { if !s.is_empty() { conditions.push(format!("status='{}'", s)); } }
        if conditions.is_empty() { String::new() } else { format!("WHERE {}", conditions.join(" AND ")) }
    };
    let sql = format!("SELECT * FROM billing_records {} ORDER BY created_at DESC", where_clause);
    let mut stmt = conn.prepare(&sql).map_err(|e| e.to_string())?;
    let records = stmt.query_map([], |row| {
        Ok(BillingRecord {
            id: row.get(0)?, party_type: row.get(1)?, party_id: row.get(2)?, party_name: row.get(3)?,
            order_id: row.get(4)?, order_no: row.get(5)?, amount: row.get(6)?, paid_amount: row.get(7)?,
            bill_type: row.get(8)?, bill_date: row.get(9)?, due_date: row.get::<_, Option<String>>(10)?.unwrap_or_default(),
            status: row.get(11)?, remark: row.get::<_, Option<String>>(12)?.unwrap_or_default(), created_at: row.get(13)?,
        })
    }).map_err(|e| e.to_string())?;
    records.collect::<Result<Vec<_>, _>>().map_err(|e| e.to_string())
}

#[tauri::command]
pub fn delete_billing(billing_id: String, username: String, real_name: String) -> Result<String, String> {
    if !check_permission(&username, "billing_edit") {
        return Err("权限不足：没有删除账单的权限".to_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 info: (String, String, f64) = conn.query_row(
        "SELECT party_name, bill_type, amount FROM billing_records WHERE id=?1", [&billing_id],
        |row| Ok((row.get(0)?, row.get::<_, Option<String>>(1)?.unwrap_or_default(), row.get::<_, Option<f64>>(2)?.unwrap_or(0.0)))
    ).unwrap_or_default();
    conn.execute("DELETE FROM billing_records WHERE id=?1", [&billing_id]).map_err(|e| error_util::to_chinese_error(e))?;
    log_operation(&username, &real_name, "删除账单", "财务管理", &format!("删除账单: {} {} {:.2}", info.0, info.1, info.2));
    Ok("删除成功".to_string())
}

#[tauri::command]
pub fn generate_billing_pdf(billing_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")?;
    let record: BillingRecord = conn.query_row("SELECT * FROM billing_records WHERE id=?1", [&billing_id], |row| {
        Ok(BillingRecord {
            id: row.get(0)?, party_type: row.get(1)?, party_id: row.get(2)?, party_name: row.get(3)?,
            order_id: row.get(4)?, order_no: row.get(5)?, amount: row.get(6)?, paid_amount: row.get(7)?,
            bill_type: row.get(8)?, bill_date: row.get(9)?, due_date: row.get::<_, Option<String>>(10)?.unwrap_or_default(),
            status: row.get(11)?, remark: row.get::<_, Option<String>>(12)?.unwrap_or_default(), created_at: row.get(13)?,
        })
    }).map_err(|e| e.to_string())?;
    let html = format!(
        "<html><head><meta charset='utf-8'><title>结算单</title></head><body style='font-family:Microsoft YaHei;padding:40px;'>\
         <h1 style='text-align:center;'>结算单</h1>\
         <p>类型：{}</p><p>对方：{}</p><p>金额：¥{:.2}</p><p>已付：¥{:.2}</p>\
         <p>状态：{}</p><p>创建时间：{}</p></body></html>",
        record.bill_type, record.party_name, record.amount, record.paid_amount, record.status, record.created_at
    );
    let b64 = base64::Engine::encode(&base64::engine::general_purpose::STANDARD, html.as_bytes());
    Ok(format!("data:text/html;base64,{}", b64))
}

#[tauri::command]
pub fn get_party_balance(party_type: String, party_id: String, bill_type: String) -> Result<f64, 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 balance: f64 = conn.query_row(
        "SELECT COALESCE(SUM(amount), 0.0) - COALESCE(SUM(paid_amount), 0.0) FROM billing_records WHERE party_type=?1 AND party_id=?2 AND bill_type=?3",
        [&party_type, &party_id, &bill_type], |row| row.get(0)
    ).unwrap_or(0.0);
    Ok(balance)
}

// 获取客户对账单（账单记录 + 无账单的已发货订单 fallback + 还款记录）
#[tauri::command]
pub fn get_customer_statement(party_id: String, start_date: String, end_date: String) -> Result<CustomerStatement, 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 customer_name: String = conn.query_row("SELECT name FROM customers WHERE id=?1", [&party_id], |row| row.get(0)).map_err(|e| e.to_string())?;
    // 将结束日期扩展到当天 23:59:59，解决 bill_date 含时分秒时字符串比较排除当天数据的问题
    let end_full = format!("{} 23:59:59", end_date);
    let begin_balance: f64 = conn.query_row(
        "SELECT COALESCE(SUM(amount), 0.0) - COALESCE(SUM(paid_amount), 0.0) FROM billing_records WHERE party_type='customer' AND party_id=?1 AND bill_date < ?2",
        [&party_id, &start_date], |row| row.get(0)).unwrap_or(0.0);

    let mut items: Vec<StatementItem> = Vec::new();
    let mut have_order_nos: std::collections::HashSet<String> = std::collections::HashSet::new();

    // 1. 应收账单记录（每笔订单一条）
    if let Ok(mut ps) = conn.prepare(
        "SELECT bill_date, order_no, bill_type, amount, paid_amount FROM billing_records WHERE party_type='customer' AND party_id=?1 AND bill_date>=?2 AND bill_date<=?3 AND amount>0 ORDER BY bill_date ASC"
    ) {
        if let Ok(br_rows) = ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            let order_no: String = row.get::<_,String>(1).unwrap_or_default();
            let bt: String = row.get(2)?;
            let amt: f64 = row.get(3)?;
            let pd: f64 = row.get(4)?;
            Ok((d, order_no, bt, amt, pd))
        }) {
            for r in br_rows {
                if let Ok((d, order_no, bt, amt, pd)) = r {
                    have_order_nos.insert(order_no.clone());
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no,
                        description: format!("【{}】", bt), bill_type: bt,
                        amount: amt, paid_amount: pd, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 1b. Fallback：已发货/已完成但尚未生成账单的订单，直接从 sale_orders 补齐（不重复）
    if let Ok(mut ps) = conn.prepare(
        "SELECT so.created_at, so.order_no, so.total_amount, so.status 
         FROM sale_orders so LEFT JOIN billing_records br ON so.id=br.order_id
         WHERE so.customer_id=?1 AND br.id IS NULL 
         AND so.status IN ('shipped','received','completed','invoiced','paid')
         AND so.created_at>=?2 AND so.created_at<=?3
         ORDER BY so.created_at ASC"
    ) {
        if let Ok(fb_rows) = ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            let ono: String = row.get(1)?;
            let amt: f64 = row.get(2)?;
            let status: String = row.get(3)?;
            let label = match status.as_str() {
                "shipped" => "已发货", "received" => "已签收", "completed" => "已完成",
                "invoiced" => "已开票", "paid" => "已回款", _ => &status
            };
            Ok((d, ono, amt, label.to_string()))
        }) {
            for r in fb_rows {
                if let Ok((d, ono, amt, label)) = r {
                    if !have_order_nos.insert(ono.clone()) { continue; }
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no: ono,
                        description: format!("【销售出库-{}】", label), bill_type: "应收货款".to_string(),
                        amount: amt, paid_amount: 0.0, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 2. 回款记录
    if let Ok(mut pay_ps) = conn.prepare(
        "SELECT payment_date, order_no, remark, amount FROM payment_records WHERE party_type='customer' AND party_id=?1 AND payment_date>=?2 AND payment_date<=?3 ORDER BY payment_date ASC"
    ) {
        if let Ok(pay_rows) = pay_ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            Ok((d, row.get::<_,String>(1).unwrap_or_default(), row.get::<_,String>(2).unwrap_or_default(), row.get::<_,f64>(3)?))
        }) {
            for r in pay_rows {
                if let Ok((d, ono, rmk, amt)) = r {
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no: ono,
                        description: rmk, bill_type: "回款".to_string(),
                        amount: 0.0, paid_amount: amt, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 补充商品明细信息
    for item in &mut items {
        if !item.order_no.is_empty() {
            if let Ok(mut ps) = conn.prepare(
                "SELECT soi.product_name, p.code, p.brand, p.model, soi.quantity, soi.price, p.unit FROM sale_order_items soi LEFT JOIN products p ON soi.product_id=p.id JOIN sale_orders so ON soi.order_id=so.id WHERE so.order_no=?1 LIMIT 1"
            ) {
                if let Ok(mut rows) = ps.query_map([&item.order_no], |row| {
                    Ok((row.get::<_,String>(0)?, row.get::<_,String>(1).unwrap_or_default(), row.get::<_,String>(2).unwrap_or_default(), row.get::<_,String>(3).unwrap_or_default(), row.get::<_,f64>(4)?, row.get::<_,f64>(5)?, row.get::<_,String>(6).unwrap_or_default()))
                }) {
                    if let Some(Ok((pn, pc, br, mo, qty, pr, un))) = rows.next() {
                        item.product_name = pn;
                        item.product_code = pc;
                        item.brand = br;
                        item.model = mo;
                        item.quantity = qty;
                        item.price = pr;
                        item.unit = un;
                    }
                }
            }
        }
    }
    items.sort_by(|a, b| a.date.cmp(&b.date));
    let mut rb = begin_balance; let mut ta = 0.0; let mut tp = 0.0;
    for item in &mut items { ta += item.amount; tp += item.paid_amount; rb = rb + item.amount - item.paid_amount; item.balance = rb; }
    Ok(CustomerStatement { customer_id: party_id, customer_name, begin_balance, end_balance: rb, total_receivable: ta, total_paid: tp, items })
}

// 获取供应商对账单（账单记录 + 无账单的已入库订单 fallback + 付款记录）
#[tauri::command]
pub fn get_supplier_statement(party_id: String, start_date: String, end_date: String) -> Result<SupplierStatement, 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 supplier_name: String = conn.query_row("SELECT name FROM suppliers WHERE id=?1", [&party_id], |row| row.get(0)).map_err(|e| e.to_string())?;
    let end_full = format!("{} 23:59:59", end_date);
    let begin_balance: f64 = conn.query_row(
        "SELECT COALESCE(SUM(amount), 0.0) - COALESCE(SUM(paid_amount), 0.0) FROM billing_records WHERE party_type='supplier' AND party_id=?1 AND bill_date < ?2",
        [&party_id, &start_date], |row| row.get(0)).unwrap_or(0.0);

    let mut items: Vec<StatementItem> = Vec::new();
    let mut have_order_nos: std::collections::HashSet<String> = std::collections::HashSet::new();

    // 1. 应付账单记录
    if let Ok(mut ps) = conn.prepare(
        "SELECT bill_date, order_no, bill_type, amount, paid_amount FROM billing_records WHERE party_type='supplier' AND party_id=?1 AND bill_date>=?2 AND bill_date<=?3 AND amount>0 ORDER BY bill_date ASC"
    ) {
        if let Ok(br_rows) = ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            let order_no: String = row.get::<_,String>(1).unwrap_or_default();
            let bt: String = row.get(2)?;
            let amt: f64 = row.get(3)?;
            let pd: f64 = row.get(4)?;
            Ok((d, order_no, bt, amt, pd))
        }) {
            for r in br_rows {
                if let Ok((d, order_no, bt, amt, pd)) = r {
                    have_order_nos.insert(order_no.clone());
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no,
                        description: format!("【{}】", bt), bill_type: bt,
                        amount: amt, paid_amount: pd, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 1b. Fallback：已签收/已完成但尚未生成账单的订单，直接从 purchase_orders 补齐
    if let Ok(mut ps) = conn.prepare(
        "SELECT po.created_at, po.order_no, po.total_amount, po.status 
         FROM purchase_orders po LEFT JOIN billing_records br ON po.id=br.order_id
         WHERE po.supplier_id=?1 AND br.id IS NULL 
         AND po.status IN ('received','completed','invoiced','paid')
         AND po.created_at>=?2 AND po.created_at<=?3
         ORDER BY po.created_at ASC"
    ) {
        if let Ok(fb_rows) = ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            let ono: String = row.get(1)?;
            let amt: f64 = row.get(2)?;
            let status: String = row.get(3)?;
            let label = match status.as_str() {
                "received" => "已签收", "completed" => "已完成",
                "invoiced" => "已开票", "paid" => "已付款", _ => &status
            };
            Ok((d, ono, amt, label.to_string()))
        }) {
            for r in fb_rows {
                if let Ok((d, ono, amt, label)) = r {
                    if !have_order_nos.insert(ono.clone()) { continue; }
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no: ono,
                        description: format!("【采购入库-{}】", label), bill_type: "应付货款".to_string(),
                        amount: amt, paid_amount: 0.0, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 2. 付款记录
    if let Ok(mut pay_ps) = conn.prepare(
        "SELECT payment_date, order_no, remark, amount FROM payment_records WHERE party_type='supplier' AND party_id=?1 AND payment_date>=?2 AND payment_date<=?3 ORDER BY payment_date ASC"
    ) {
        if let Ok(pay_rows) = pay_ps.query_map([&party_id, &start_date, &end_full], |row| {
            let d: String = row.get(0)?;
            Ok((d, row.get::<_,String>(1).unwrap_or_default(), row.get::<_,String>(2).unwrap_or_default(), row.get::<_,f64>(3)?))
        }) {
            for r in pay_rows {
                if let Ok((d, ono, rmk, amt)) = r {
                    items.push(StatementItem { date: d.chars().take(10).collect(), order_no: ono,
                        description: rmk, bill_type: "付款".to_string(),
                        amount: 0.0, paid_amount: amt, balance: 0.0,
                        product_name: String::new(), product_code: String::new(), brand: String::new(), model: String::new(),
                        quantity: 0.0, price: 0.0, unit: String::new() });
                }
            }
        }
    }

    // 补充商品明细信息
    for item in &mut items {
        if !item.order_no.is_empty() {
            if let Ok(mut ps) = conn.prepare(
                "SELECT poi.product_name, p.code, p.brand, p.model, poi.quantity, poi.price, p.unit FROM purchase_order_items poi LEFT JOIN products p ON poi.product_id=p.id JOIN purchase_orders po ON poi.order_id=po.id WHERE po.order_no=?1 LIMIT 1"
            ) {
                if let Ok(mut rows) = ps.query_map([&item.order_no], |row| {
                    Ok((row.get::<_,String>(0)?, row.get::<_,String>(1).unwrap_or_default(), row.get::<_,String>(2).unwrap_or_default(), row.get::<_,String>(3).unwrap_or_default(), row.get::<_,f64>(4)?, row.get::<_,f64>(5)?, row.get::<_,String>(6).unwrap_or_default()))
                }) {
                    if let Some(Ok((pn, pc, br, mo, qty, pr, un))) = rows.next() {
                        item.product_name = pn;
                        item.product_code = pc;
                        item.brand = br;
                        item.model = mo;
                        item.quantity = qty;
                        item.price = pr;
                        item.unit = un;
                    }
                }
            }
        }
    }
    items.sort_by(|a, b| a.date.cmp(&b.date));
    let mut rb = begin_balance; let mut ta = 0.0; let mut tp = 0.0;
    for item in &mut items { ta += item.amount; tp += item.paid_amount; rb = rb + item.amount - item.paid_amount; item.balance = rb; }
    Ok(SupplierStatement { supplier_id: party_id, supplier_name, begin_balance, end_balance: rb, total_payable: ta, total_paid: tp, items })
}
