// ============ 数据库初始化和连接管理 ============
// ============ v2 - 版本化迁移系统 ============
use rusqlite::Connection;
use std::sync::Mutex;
use uuid::Uuid;
use chrono::Local;

lazy_static::lazy_static! {
    pub static ref DB_CONN: Mutex<Option<Connection>> = Mutex::new(None);
}

/// 当前期望的数据库 Schema 版本号
/// 每次增加新表/新字段或需要数据迁移时，同步递增此值并添加对应迁移步骤
const EXPECTED_SCHEMA_VERSION: i32 = 9;

/// 获取数据库当前 Schema 版本
fn get_schema_version(conn: &Connection) -> i32 {
    conn.query_row("PRAGMA user_version", [], |row| row.get(0)).unwrap_or(0)
}

/// 设置数据库 Schema 版本（持久化到数据库文件头）
fn set_schema_version(conn: &Connection, version: i32) -> Result<(), String> {
    conn.execute(&format!("PRAGMA user_version = {}", version), [])
        .map(|_| ())
        .map_err(|e| format!("设置 Schema 版本失败: {}", e))
}

/// 安全地添加列：如果列已存在则忽略，其他错误才报错
fn try_add_column(conn: &Connection, table: &str, column: &str, col_type: &str, default: &str) -> Result<(), String> {
    let sql = format!("ALTER TABLE {} ADD COLUMN {} {} DEFAULT {}", table, column, col_type, default);
    match conn.execute(&sql, []) {
        Ok(_) => Ok(()),
        Err(e) => {
            let msg = e.to_string();
            // SQLite 返回 "duplicate column name" 时表示列已存在，忽略
            if msg.contains("duplicate column") {
                Ok(())
            } else {
                Err(format!("迁移失败: {}.{} - {}", table, column, msg))
            }
        }
    }
}

/// 分版本运行数据库迁移，确保旧版本数据库能升级到最新 Schema
fn run_migrations(conn: &Connection) -> Result<(), String> {
    let current_ver = get_schema_version(conn);
    
    if current_ver >= EXPECTED_SCHEMA_VERSION {
        return Ok(()); // 已是最新版本，跳过
    }
    
    // === 版本 0 → 1：初始建表（由 CREATE TABLE IF NOT EXISTS 完成，无需额外迁移）===
    if current_ver < 1 {
        set_schema_version(conn, 1)?;
    }
    
    // === 版本 1 → 2：添加 products 扩展字段 + 发票标识位 + 有效期字段 ===
    if current_ver < 2 {
        try_add_column(conn, "products", "barcode", "TEXT", "''")?;
        try_add_column(conn, "products", "trace_code", "TEXT", "''")?;
        try_add_column(conn, "products", "status", "TEXT", "'active'")?;
        try_add_column(conn, "products", "category_id", "TEXT", "''")?;
        try_add_column(conn, "sale_orders", "is_invoiced", "INTEGER", "0")?;
        try_add_column(conn, "purchase_orders", "is_invoiced", "INTEGER", "0")?;
        try_add_column(conn, "purchase_order_items", "expiry_date", "TEXT", "''")?;
        try_add_column(conn, "locations", "warehouse_name", "TEXT", "''")?;
        set_schema_version(conn, 2)?;
    }
    
    // === 版本 2 → 3：预留 —— 后续在此添加新迁移 ===
    if current_ver < 3 {
        set_schema_version(conn, 3)?;
    }
    
    // === 版本 3 → 4：打印模板支持 template_type 字段 ===
    if current_ver < 4 {
        try_add_column(conn, "print_templates", "template_type", "TEXT", "''")?;
        let _ = conn.execute("UPDATE print_templates SET template_type='sale' WHERE template_name LIKE '%销售%' AND (template_type='' OR template_type IS NULL)", []);
        let _ = conn.execute("UPDATE print_templates SET template_type='purchase' WHERE template_name LIKE '%采购%' AND (template_type='' OR template_type IS NULL)", []);
        let _ = conn.execute("UPDATE print_templates SET template_type='stock_in' WHERE template_name LIKE '%入库%' AND (template_type='' OR template_type IS NULL)", []);
        let _ = conn.execute("UPDATE print_templates SET template_type='stock_out' WHERE template_name LIKE '%出库%' AND (template_type='' OR template_type IS NULL)", []);
        set_schema_version(conn, 4)?;
    }
    
    // === 版本 4 → 5：数据迁移 - 旧版 status='completed' 转为新状态机值 ===
    // 老版本数据库默认 status='completed'，新版本已改为 6 种状态
    if current_ver < 5 {
        // 销售订单：旧版 completed → received（已签收）
        let count1 = conn.execute("UPDATE sale_orders SET status = 'received' WHERE status = 'completed'", [])
            .unwrap_or(0);
        // 采购订单：旧版 completed → received（已签收）
        let count2 = conn.execute("UPDATE purchase_orders SET status = 'received' WHERE status = 'completed'", [])
            .unwrap_or(0);
        // 退货单：旧版 completed 保持不变（新版本仍然保留 completed 状态）
        // 其他可能残留 'invoiced' / 'paid' 状态已是新版兼容的
        if count1 > 0 || count2 > 0 {
            #[cfg(debug_assertions)]
            println!("数据迁移：已转换 sale_orders={} 条, purchase_orders={} 条", count1, count2);
        }
        set_schema_version(conn, 5)?;
    }
    
    // === 版本 5 → 6：补齐旧数据库 products 表缺失的扩展列（防止 SELECT * 按位置读取时错位）===
    // 旧数据库的 products 表可能只有基础列，新版本 CREATE TABLE 中的 batch_no/expiry_date/brand 等
    // 通过 ALTER TABLE ADD COLUMN 逐个补齐，确保 `SELECT *` 的列顺序与 Product 结构体一致
    if current_ver < 6 {
        try_add_column(conn, "products", "batch_no", "TEXT", "''")?;
        try_add_column(conn, "products", "expiry_date", "TEXT", "''")?;
        try_add_column(conn, "products", "brand", "TEXT", "''")?;
        try_add_column(conn, "products", "model", "TEXT", "''")?;
        try_add_column(conn, "products", "package_type", "TEXT", "''")?;
        try_add_column(conn, "products", "description", "TEXT", "''")?;
        try_add_column(conn, "products", "image_path", "TEXT", "''")?;
        try_add_column(conn, "products", "warehouse_id", "TEXT", "''")?;
        try_add_column(conn, "products", "location_id", "TEXT", "''")?;
        // 同时补齐其他表可能缺失的列
        try_add_column(conn, "sale_order_items", "batch_no", "TEXT", "''")?;
        try_add_column(conn, "purchase_order_items", "batch_no", "TEXT", "''")?;
        try_add_column(conn, "purchase_order_items", "expiry_date", "TEXT", "''")?;
        try_add_column(conn, "locations", "warehouse_name", "TEXT", "''")?;
        try_add_column(conn, "print_templates", "template_type", "TEXT", "''")?;
        set_schema_version(conn, 6)?;
    }
    
    // === 版本 6 → 7：增加双单位换算率字段 ===
    if current_ver < 7 {
        try_add_column(conn, "products", "unit_rate", "REAL", "1.0")?;
        set_schema_version(conn, 7)?;
    }

    // === 版本 7 → 8：增加生产日期字段 ===
    if current_ver < 8 {
        try_add_column(conn, "products", "production_date", "TEXT", "''")?;
        set_schema_version(conn, 8)?;
    }

    // === 版本 8 → 9：打印模板增加显示设置字段 ===
    if current_ver < 9 {
        try_add_column(conn, "print_templates", "subheader_text", "TEXT", "''")?;
        try_add_column(conn, "print_templates", "show_company_info", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_order_no", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_date", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_customer", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_items", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_total", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_remark", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_signature", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_unit_price", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_amount", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "show_model", "INTEGER", "1")?;
        try_add_column(conn, "print_templates", "signer_name", "TEXT", "''")?;
        try_add_column(conn, "print_templates", "auditor_name", "TEXT", "''")?;
        try_add_column(conn, "print_templates", "confirmer_name", "TEXT", "''")?;
        set_schema_version(conn, 9)?;
    }
    // 设置最终版本
    set_schema_version(conn, EXPECTED_SCHEMA_VERSION)?;
    
    Ok(())
}

/// 验证数据库 Schema 完整性，确保所有核心表和关键字段可查询
pub fn validate_schema(conn: &Connection) -> Result<Vec<String>, String> {
    let mut checks = Vec::new();
    
    // 核心表列表：依次验证每张表是否存在、可查询
    let core_tables = [
        "users", "products", "customers", "suppliers",
        "sale_orders", "sale_order_items",
        "purchase_orders", "purchase_order_items",
        "inventory_records", "billing_records",
        "categories", "warehouses", "locations",
        "warehouse_stock", "system_config",
        "operation_logs", "role_permissions",
        "print_templates", "return_orders",
        "stock_transactions",
    ];
    
    for table in &core_tables {
        let sql = format!("SELECT COUNT(*) FROM {}", table);
        match conn.query_row(&sql, [], |row| row.get::<_, i64>(0)) {
            Ok(count) => checks.push(format!("{} 表正常 ({} 条记录)", table, count)),
            Err(e) => {
                let msg = e.to_string();
                // SQLITE_ERROR(1) 且包含 "no such table" 才是真正的表缺失
                if msg.contains("no such table") {
                    return Err(format!("核心表 {} 缺失，迁移可能未完全执行！错误: {}", table, msg));
                }
                checks.push(format!("{} 表异常: {}", table, msg));
            }
        }
    }
    
    // 关键字段验证：检查 products 表的扩展字段是否存在
    // 注意：必须用 query_row 而不是 execute（SELECT 语句用 execute 会报错）
    let key_columns = ["barcode", "status", "category_id"];
    for col in &key_columns {
        let sql = format!("SELECT {} FROM products LIMIT 1", col);
        match conn.query_row(&sql, [], |_| Ok(())) {
            Ok(_) => checks.push(format!("products.{} 字段正常", col)),
            Err(e) => {
                let msg = e.to_string();
                if msg.contains("no such column") {
                    checks.push(format!("⚠️ products.{} 字段缺失！迁移可能未执行", col));
                } else {
                    // PRAGMA 或空表等情况，视为正常
                }
            }
        }
    }
    
    Ok(checks)
}

#[tauri::command]
pub fn init_local_database() -> Result<String, String> {
    init_db().map(|_| "本地数据库初始化成功".to_string())
}

pub fn init_db() -> Result<(), String> {
    let mut conn_guard = DB_CONN.lock().map_err(|e| e.to_string())?;
    let conn = Connection::open("inventory.db").map_err(|e| e.to_string())?;
    
    // 创建 users 表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            real_name TEXT NOT NULL,
            role TEXT NOT NULL,
            created_at TEXT NOT NULL
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // 创建商品表 - 扩展支持双单位、批号、有效期、品牌型号等
    conn.execute(
        "CREATE TABLE IF NOT EXISTS products (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            code TEXT UNIQUE NOT NULL,
            category TEXT,
            unit TEXT DEFAULT '',
            unit2 TEXT DEFAULT '',
            price REAL DEFAULT 0.0,
            cost REAL DEFAULT 0.0,
            stock REAL DEFAULT 0.0,
            stock_unit2 REAL DEFAULT 0.0,
            batch_no TEXT DEFAULT '',
            production_date TEXT DEFAULT '',
            expiry_date TEXT DEFAULT '',
            brand TEXT DEFAULT '',
            model TEXT DEFAULT '',
            package_type TEXT DEFAULT '',
            description TEXT,
            image_path TEXT DEFAULT '',
            warehouse_id TEXT DEFAULT '',
            location_id TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建客户表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS customers (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            contact TEXT,
            phone TEXT,
            address TEXT,
            balance REAL DEFAULT 0.0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建供应商表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS suppliers (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            contact TEXT,
            phone TEXT,
            address TEXT,
            balance REAL DEFAULT 0.0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建销售订单表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS sale_orders (
            id TEXT PRIMARY KEY,
            order_no TEXT UNIQUE NOT NULL,
            customer_id TEXT,
            customer_name TEXT,
            total_amount REAL DEFAULT 0.0,
            status TEXT DEFAULT 'completed',
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建销售订单明细表 - 数量改为REAL支持小数,添加批号字段
    conn.execute(
        "CREATE TABLE IF NOT EXISTS sale_order_items (
            id TEXT PRIMARY KEY,
            order_id TEXT,
            product_id TEXT,
            product_name TEXT,
            quantity REAL DEFAULT 0.0,
            price REAL,
            amount REAL,
            batch_no TEXT DEFAULT ''
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建采购订单表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS purchase_orders (
            id TEXT PRIMARY KEY,
            order_no TEXT UNIQUE NOT NULL,
            supplier_id TEXT,
            supplier_name TEXT,
            total_amount REAL DEFAULT 0.0,
            status TEXT DEFAULT 'completed',
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建采购订单明细表 - 数量改为REAL支持小数,添加批号字段
    conn.execute(
        "CREATE TABLE IF NOT EXISTS purchase_order_items (
            id TEXT PRIMARY KEY,
            order_id TEXT,
            product_id TEXT,
            product_name TEXT,
            quantity REAL DEFAULT 0.0,
            price REAL,
            amount REAL,
            batch_no TEXT DEFAULT ''
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建库存记录表 - 数量改为REAL支持小数
    conn.execute(
        "CREATE TABLE IF NOT EXISTS inventory_records (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            product_name TEXT NOT NULL,
            change_type TEXT NOT NULL,
            quantity REAL NOT NULL,
            before_stock REAL NOT NULL,
            after_stock REAL NOT NULL,
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 创建打印模板表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS print_templates (
            id TEXT PRIMARY KEY,
            template_name TEXT NOT NULL,
            paper_size TEXT NOT NULL,
            paper_width REAL NOT NULL,
            paper_height REAL NOT NULL,
            margin_top REAL NOT NULL,
            margin_bottom REAL NOT NULL,
            margin_left REAL NOT NULL,
            margin_right REAL NOT NULL,
            header_text TEXT,
            footer_text TEXT,
            show_logo INTEGER DEFAULT 0,
            logo_path TEXT,
            font_size INTEGER DEFAULT 12,
            is_default INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 商品规格表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS product_specs (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            spec_name TEXT NOT NULL,
            spec_value TEXT NOT NULL,
            price REAL DEFAULT 0.0,
            stock INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 类别表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS categories (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            parent_id TEXT DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 应收应付账单表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS billing_records (
            id TEXT PRIMARY KEY,
            party_type TEXT NOT NULL,
            party_id TEXT NOT NULL,
            party_name TEXT NOT NULL,
            order_id TEXT,
            order_no TEXT,
            amount REAL NOT NULL,
            paid_amount REAL DEFAULT 0.0,
            bill_type TEXT NOT NULL,
            bill_date TEXT NOT NULL,
            due_date TEXT,
            status TEXT DEFAULT 'unpaid',
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // 自定义销售单模板
    conn.execute(
        "CREATE TABLE IF NOT EXISTS sale_templates (
            id TEXT PRIMARY KEY,
            template_name TEXT NOT NULL,
            fields_config TEXT NOT NULL,
            is_default INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增表:仓库管理 ============
    
    // 仓库表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS warehouses (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            code TEXT UNIQUE NOT NULL,
            address TEXT,
            contact TEXT,
            phone TEXT,
            description TEXT,
            is_default INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // 库位表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS locations (
            id TEXT PRIMARY KEY,
            warehouse_id TEXT NOT NULL,
            warehouse_name TEXT DEFAULT '',
            code TEXT NOT NULL,
            name TEXT NOT NULL,
            zone TEXT,
            aisle TEXT,
            shelf TEXT,
            level TEXT,
            position TEXT,
            description TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime')),
            UNIQUE(warehouse_id, code)
        )",
        [],
    ).map_err(|e| e.to_string())?;
    // 兼容旧数据库：添加 warehouse_name 列（如果不存在）
    let _ = conn.execute("ALTER TABLE locations ADD COLUMN warehouse_name TEXT DEFAULT ''", []);
    
    // 调拨订单表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS transfer_orders (
            id TEXT PRIMARY KEY,
            order_no TEXT UNIQUE NOT NULL,
            from_warehouse_id TEXT NOT NULL,
            from_warehouse_name TEXT,
            to_warehouse_id TEXT NOT NULL,
            to_warehouse_name TEXT,
            status TEXT DEFAULT 'pending',
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // 调拨订单明细表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS transfer_order_items (
            id TEXT PRIMARY KEY,
            transfer_id TEXT NOT NULL,
            product_id TEXT NOT NULL,
            product_name TEXT,
            quantity REAL NOT NULL,
            unit TEXT,
            batch_no TEXT
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 发票表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS invoices (
            id TEXT PRIMARY KEY,
            invoice_type TEXT NOT NULL,
            invoice_no TEXT NOT NULL,
            party_id TEXT,
            party_name TEXT,
            order_id TEXT,
            order_no TEXT,
            amount REAL DEFAULT 0.0,
            tax_amount REAL DEFAULT 0.0,
            invoice_date TEXT,
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 还款记录表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS payment_records (
            id TEXT PRIMARY KEY,
            billing_id TEXT NOT NULL,
            party_type TEXT,
            party_id TEXT,
            party_name TEXT,
            amount REAL DEFAULT 0.0,
            payment_date TEXT,
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 退货单表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS return_orders (
            id TEXT PRIMARY KEY,
            return_no TEXT UNIQUE NOT NULL,
            return_type TEXT NOT NULL,
            party_id TEXT,
            party_name TEXT,
            total_amount REAL DEFAULT 0.0,
            reason TEXT,
            status TEXT DEFAULT 'completed',
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS return_order_items (
            id TEXT PRIMARY KEY,
            return_id TEXT NOT NULL,
            product_id TEXT NOT NULL,
            product_name TEXT,
            quantity REAL NOT NULL,
            price REAL,
            amount REAL,
            batch_no TEXT
        )",
        [],
    ).map_err(|e| e.to_string())?;

    // 库存统计表视图或单独表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS stock_transactions (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            product_name TEXT,
            transaction_type TEXT NOT NULL,
            quantity REAL NOT NULL,
            before_stock REAL DEFAULT 0.0,
            after_stock REAL DEFAULT 0.0,
            ref_order_no TEXT,
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增表:产品组装拆分 ============
    
    // 组装拆分记录表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS assembly_records (
            id TEXT PRIMARY KEY,
            record_no TEXT UNIQUE NOT NULL,
            assembly_type TEXT NOT NULL,
            product_id TEXT NOT NULL,
            product_name TEXT,
            quantity REAL NOT NULL,
            components TEXT,
            remark TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增表:包件管理 ============
    
    // 产品包件表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS product_packages (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            package_name TEXT NOT NULL,
            package_code TEXT NOT NULL,
            quantity INTEGER DEFAULT 1,
            is_shared INTEGER DEFAULT 0,
            shared_products TEXT,
            sort_order INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // 仓库库存表（支持多仓库存查询）
    conn.execute(
        "CREATE TABLE IF NOT EXISTS warehouse_stock (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            warehouse_id TEXT NOT NULL,
            warehouse_name TEXT DEFAULT '',
            stock REAL DEFAULT 0.0,
            updated_at TEXT DEFAULT (datetime('now', 'localtime')),
            UNIQUE(product_id, warehouse_id)
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增系统配置表 ============
    conn.execute(
        "CREATE TABLE IF NOT EXISTS system_config (
            id TEXT PRIMARY KEY,
            config_key TEXT UNIQUE NOT NULL,
            config_value TEXT NOT NULL,
            description TEXT DEFAULT '',
            updated_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增操作日志表 ============
    conn.execute(
        "CREATE TABLE IF NOT EXISTS operation_logs (
            id TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            real_name TEXT NOT NULL,
            action TEXT NOT NULL,
            module TEXT NOT NULL,
            detail TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 新增角色权限表 ============
    conn.execute(
        "CREATE TABLE IF NOT EXISTS role_permissions (
            id TEXT PRIMARY KEY,
            role_name TEXT NOT NULL,
            permission_key TEXT NOT NULL,
            permission_name TEXT DEFAULT '',
            is_granted INTEGER DEFAULT 1,
            updated_at TEXT DEFAULT (datetime('now', 'localtime')),
            UNIQUE(role_name, permission_key)
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 商品文档附件表 ============
    conn.execute(
        "CREATE TABLE IF NOT EXISTS product_documents (
            id TEXT PRIMARY KEY,
            product_id TEXT NOT NULL,
            file_name TEXT NOT NULL,
            file_type TEXT DEFAULT '',
            file_size INTEGER DEFAULT 0,
            file_data TEXT NOT NULL,
            created_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
        [],
    ).map_err(|e| e.to_string())?;
    
    // ============ 运行版本化迁移（自动补充旧数据库缺失的列和表）============
    run_migrations(&conn)?;
    
    // 初始化默认角色权限（不自己加锁，防止死锁）
    crate::permissions::init_default_permissions(&conn)?;
    
    // 插入默认系统配置
    let config_count: i64 = conn.query_row(
        "SELECT COUNT(*) FROM system_config", [], |row| row.get(0)
    ).unwrap_or(0);
    if config_count == 0 {
        let _ = conn.execute(
            "INSERT INTO system_config (id, config_key, config_value, description, updated_at) VALUES (?1,'dual_unit_enabled','1','双单位功能开关(1启用/0禁用)',datetime('now','localtime'))",
            [&Uuid::new_v4().to_string()]
        );
    }
    
    // 插入默认管理员账户（如果不存在）
    let admin_exists: Result<i64, _> = conn.query_row(
        "SELECT COUNT(*) FROM users WHERE username = 'admin'",
        [],
        |row| row.get(0)
    );
    
    if let Ok(count) = admin_exists {
        if count == 0 {
            let admin_id = Uuid::new_v4().to_string();
            let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
            conn.execute(
                "INSERT INTO users (id, username, password, real_name, role, created_at) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                [&admin_id, "admin", "admin123", "系统管理员", "admin", &now]
            ).map_err(|e| e.to_string())?;
        }
    }
    
    // 插入打印模板预设数据（如果表为空）
    let template_count: Result<i64, _> = conn.query_row(
        "SELECT COUNT(*) FROM print_templates",
        [],
        |row| row.get(0)
    );
    
    if let Ok(count) = template_count {
        if count == 0 {
            let now = Local::now().format("%Y-%m-%d %H:%M:%S").to_string();
            
            // A4 纵向模板 - 销售单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准销售单（A4纵向）", "sale", "A4", "210", "297",
                    "15", "15", "15", "15", "某某商贸有限公司", "感谢您的惠顾！欢迎再次光临。",
                    "0", "", "12", "1", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A4 纵向模板 - 采购单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准采购单（A4纵向）", "purchase", "A4", "210", "297",
                    "15", "15", "15", "15", "某某商贸有限公司", "请按时交货，谢谢合作！",
                    "0", "", "12", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A4 纵向模板 - 入库单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准入库单（A4纵向）", "stock_in", "A4", "210", "297",
                    "10", "10", "10", "10", "某某商贸有限公司", "仓库管理员签字确认",
                    "0", "", "11", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A4 纵向模板 - 出库单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准出库单（A4纵向）", "stock_out", "A4", "210", "297",
                    "10", "10", "10", "10", "某某商贸有限公司", "客户签收：______________",
                    "0", "", "11", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A4 横向模板 - 销售单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准销售单（A4横向）", "sale", "A4", "297", "210",
                    "15", "15", "15", "15", "某某商贸有限公司", "感谢您的惠顾！欢迎再次光临。",
                    "0", "", "12", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A4 横向模板 - 采购单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "标准采购单（A4横向）", "purchase", "A4", "297", "210",
                    "15", "15", "15", "15", "某某商贸有限公司", "请按时交货，谢谢合作！",
                    "0", "", "12", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A5 纵向模板 - 销售单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "简易销售单（A5纵向）", "sale", "A5", "148", "210",
                    "10", "10", "10", "10", "销售单", "谢谢惠顾",
                    "0", "", "10", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A5 纵向模板 - 采购单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "简易采购单（A5纵向）", "purchase", "A5", "148", "210",
                    "10", "10", "10", "10", "采购单", "请按时交货",
                    "0", "", "10", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A5 横向模板 - 销售单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "简易销售单（A5横向）", "sale", "A5", "210", "148",
                    "10", "10", "10", "10", "销售单", "谢谢惠顾",
                    "0", "", "10", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
            
            // A5 横向模板 - 采购单
            conn.execute(
                "INSERT INTO print_templates (id, template_name, template_type, paper_size, paper_width, paper_height,
                 margin_top, margin_bottom, margin_left, margin_right, header_text, footer_text,
                 show_logo, logo_path, font_size, is_default, created_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                [
                    &Uuid::new_v4().to_string(), "简易采购单（A5横向）", "purchase", "A5", "210", "148",
                    "10", "10", "10", "10", "采购单", "请按时交货",
                    "0", "", "10", "0", &now
                ]
            ).map_err(|e| e.to_string())?;
        }
    }

    // 确保所有模板的 template_type 已正确设置（修复旧数据库/迁移遗漏的模板）
    let _ = conn.execute("UPDATE print_templates SET template_type='sale' WHERE template_name LIKE '%销售%' AND (template_type='' OR template_type IS NULL)", []);
    let _ = conn.execute("UPDATE print_templates SET template_type='purchase' WHERE template_name LIKE '%采购%' AND (template_type='' OR template_type IS NULL)", []);
    let _ = conn.execute("UPDATE print_templates SET template_type='stock_in' WHERE template_name LIKE '%入库%' AND (template_type='' OR template_type IS NULL)", []);
    let _ = conn.execute("UPDATE print_templates SET template_type='stock_out' WHERE template_name LIKE '%出库%' AND (template_type='' OR template_type IS NULL)", []);

    // ?? WAL ???????????????????????????
    let _ = conn.execute("PRAGMA journal_mode=WAL", []); // ???????????
    let _ = conn.execute("PRAGMA busy_timeout=5000", []); // ???????5???????

    *conn_guard = Some(conn);
    Ok(())
}
