PlateRecApp/src/database.cpp

/**
 * database.cpp — 数据库实现
 */
#include "database.h"
#include "utils.h"
#include <iostream>
#include <thread>
#include <chrono>

// 前向声明（static函数定义在后面，但init_database()需要调用）
static void _db_load_tw_cache_unlocked();

bool check_db_health() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return false;
    
    sqlite3_stmt* stmt = nullptr;
    const char* sql = "SELECT 1;";
    int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        std::cerr << "[数据库健康检查] 失败: " << sqlite3_errmsg(g_db) << std::endl;
        return false;
    }
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_ROW) {
        std::cerr << "[数据库健康检查] 执行失败" << std::endl;
        return false;
    }
    
    g_metrics.record_db_health_check();
    return true;
}

int init_database() {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	g_db_path = pathComm + "data/upload_records.db";
	int rc = sqlite3_open(g_db_path.c_str(), &g_db);
	if (rc) {
		std::cerr << "无法打开数据库: " << sqlite3_errmsg(g_db) << std::endl;
		g_db = nullptr;
		return -1;
	}

	if (sqlite3_db_readonly(g_db, "main") == 1) {
		std::cerr << "数据库为只读，尝试修复权限..." << std::endl;
		sqlite3_close(g_db);
		chmod(g_db_path.c_str(), 0666);
		rc = sqlite3_open_v2(g_db_path.c_str(), &g_db,
			SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL);
		if (rc != SQLITE_OK) {
			std::cerr << "无法以读写方式打开数据库" << std::endl;
			g_db = nullptr;
			return -1;
		}
	}

	// ✅ v41 优化：SQLite busy_timeout 设为 100ms
	sqlite3_busy_timeout(g_db, DB_BUSY_TIMEOUT_MS);

	const char* create_table_sql =
		"CREATE TABLE IF NOT EXISTS upload_records ("
		"    id INTEGER PRIMARY KEY AUTOINCREMENT,"
		"    create_time DATETIME DEFAULT (datetime('now', 'localtime')),"
		"    capture_time INTEGER,"  // ✅ fix24-v13: 抓拍时刻Unix时间戳，解决DB插入延迟导致create_time不准
		"    plate_number TEXT NOT NULL,"
		"    tb_num TEXT NOT NULL,"
		"    station_type INTEGER,"
		"    lo_photo_path TEXT,"
		"    hi_photo_path TEXT,"
		"    lo_upload_status INTEGER DEFAULT 0,"
		"    hi_upload_status INTEGER DEFAULT 0,"
		"    feishu_status INTEGER DEFAULT 0,"
		"    retry_count INTEGER DEFAULT 0"
		");";

	char* errMsg = nullptr;
	rc = sqlite3_exec(g_db, create_table_sql, nullptr, nullptr, &errMsg);
	if (rc != SQLITE_OK) {
		std::cerr << "创建表失败: " << errMsg << std::endl;
		sqlite3_free(errMsg);
		return -1;
	}

	// ✅ fix24-v13: 为已有数据库添加capture_time列（新库已包含，旧库需ALTER）
	const char* alter_sql = "ALTER TABLE upload_records ADD COLUMN capture_time INTEGER;";
	sqlite3_exec(g_db, alter_sql, nullptr, nullptr, nullptr);  // 忽略"duplicate column"错误

	// ✅ 新增覆盖所有查询条件的联合索引，解决N+1查询性能问题
	const char* create_index_sql =
		"CREATE INDEX IF NOT EXISTS idx_plate_number ON upload_records(plate_number);"
		"CREATE INDEX IF NOT EXISTS idx_tb_num ON upload_records(tb_num);"
		"CREATE INDEX IF NOT EXISTS idx_create_time ON upload_records(create_time);"
		"CREATE INDEX IF NOT EXISTS idx_plate_station_time_status "
		"ON upload_records(plate_number, station_type, create_time, lo_upload_status, hi_upload_status);";

	// ✅ TIME_WINDOW持久化表：存储进站/出站工单配额缓存
	const char* create_tw_cache_sql =
		"CREATE TABLE IF NOT EXISTS time_window_cache ("
		"    id INTEGER PRIMARY KEY AUTOINCREMENT,"
		"    plate_number TEXT NOT NULL,"
		"    station_type INTEGER NOT NULL,"  // 0=进站, 1=出站
		"    create_time INTEGER NOT NULL,"   // Unix时间戳：首次处理时间
		"    last_time INTEGER NOT NULL,"     // Unix时间戳：最后处理时间
		"    bill_count INTEGER DEFAULT 0,"  // 工单数量
		"    photo_count INTEGER DEFAULT 0,"  // 照片计数（进站用）
		"    tb_num TEXT,"                    // 最后一次工单编号
		"    generation INTEGER DEFAULT 1,"   // 周期代数
		"    UNIQUE(plate_number, station_type)"
		");";

	rc = sqlite3_exec(g_db, create_tw_cache_sql, nullptr, nullptr, &errMsg);
	if (rc != SQLITE_OK) {
		std::cerr << "创建time_window_cache表失败: " << errMsg << std::endl;
		sqlite3_free(errMsg);
	} else {
		// 创建索引
		const char* create_tw_index_sql =
			"CREATE INDEX IF NOT EXISTS idx_tw_plate ON time_window_cache(plate_number, station_type);"
			"CREATE INDEX IF NOT EXISTS idx_tw_create_time ON time_window_cache(create_time);";
		sqlite3_exec(g_db, create_tw_index_sql, nullptr, nullptr, nullptr);
		std::cout << "✅ time_window_cache表初始化成功" << std::endl;
	}  // ✅ v43修复：闭合else块，station_lock_cache不嵌套在内

	// ✅ v42新增/v43修复：交替锁定缓存表（独立于time_window_cache的else块）
	const char* create_station_lock_sql =
		"CREATE TABLE IF NOT EXISTS station_lock_cache ("
		"    plate_number TEXT PRIMARY KEY,"
		"    last_in_time INTEGER NOT NULL DEFAULT 0,"
		"    last_out_time INTEGER NOT NULL DEFAULT 0,"
		"    current_mode INTEGER NOT NULL DEFAULT 0"  // ✅ v43修复：DEFAULT 0 = INBOUND_ALLOWED
		");"
		"CREATE INDEX IF NOT EXISTS idx_slc_plate ON station_lock_cache(plate_number);";

	rc = sqlite3_exec(g_db, create_station_lock_sql, nullptr, nullptr, &errMsg);
	if (rc != SQLITE_OK) {
		std::cerr << "创建station_lock_cache表失败: " << errMsg << std::endl;
		sqlite3_free(errMsg);
	} else {
		std::cout << "✅ station_lock_cache表初始化成功" << std::endl;
	}

	// ✅ fix24-v9新增：称重记录表
	const char* create_weight_sql =
		"CREATE TABLE IF NOT EXISTS weight_records ("
		"    id INTEGER PRIMARY KEY AUTOINCREMENT,"
		"    create_time DATETIME DEFAULT (datetime('now', 'localtime')),"
		"    plate_number TEXT NOT NULL,"
		"    tb_num TEXT NOT NULL,"
		"    station_type INTEGER NOT NULL,"
		"    weight_kg REAL NOT NULL,"
		"    upload_status INTEGER DEFAULT 0,"
		"    retry_count INTEGER DEFAULT 0,"
		"    response_msg TEXT"
		");"
		"CREATE INDEX IF NOT EXISTS idx_wr_plate ON weight_records(plate_number);"
		"CREATE INDEX IF NOT EXISTS idx_wr_tb_num ON weight_records(tb_num);"
		"CREATE INDEX IF NOT EXISTS idx_wr_status ON weight_records(upload_status);";

	rc = sqlite3_exec(g_db, create_weight_sql, nullptr, nullptr, &errMsg);
	if (rc != SQLITE_OK) {
		std::cerr << "创建weight_records表失败: " << errMsg << std::endl;
		sqlite3_free(errMsg);
	} else {
		std::cout << "✅ weight_records表初始化成功" << std::endl;
	}

	rc = sqlite3_exec(g_db, create_index_sql, nullptr, nullptr, &errMsg);
	if (rc != SQLITE_OK) {
		sqlite3_free(errMsg);
	}

	std::cout << "✅ 数据库初始化成功: " << g_db_path << std::endl;
	
	return 0;
}

// ✅ 优化N+1查询为GROUP BY聚合查询，启动速度提升100倍以上
void load_recent_tbnums_from_db()
{
	if (!g_db) return;
	int window_seconds = g_time_window_min * 60;
	time_t now = time(NULL);

	std::vector<std::tuple<std::string, std::string, int, time_t, int>> records;
	{
		std::lock_guard<std::mutex> lock(g_db_mtx);
		const char* sql = 
			"SELECT ur1.plate_number, ur1.tb_num, ur1.station_type, ur1.create_time, "
			"COUNT(ur2.id) as photo_count "
			"FROM upload_records ur1 "
			"LEFT JOIN upload_records ur2 ON "
			"ur2.plate_number = ur1.plate_number "
			"AND ur2.station_type = ur1.station_type "
			"AND ur2.create_time >= ur1.create_time "
			"AND ur2.lo_upload_status = 1 "
			"AND ur2.hi_upload_status = 1 "
			"GROUP BY ur1.plate_number, ur1.station_type, ur1.create_time, ur1.tb_num "
			"ORDER BY ur1.create_time DESC LIMIT 1000;";

		sqlite3_stmt* stmt = nullptr;
		int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
		if (rc != SQLITE_OK) {
			std::cerr << "准备恢复缓存语句失败: " << sqlite3_errmsg(g_db) << std::endl;
			return;
		}

		while (sqlite3_step(stmt) == SQLITE_ROW) {
			const unsigned char* plate_text = sqlite3_column_text(stmt, 0);
			const unsigned char* tb_text = sqlite3_column_text(stmt, 1);
			int station_type = sqlite3_column_int(stmt, 2);
			const unsigned char* create_time_text = sqlite3_column_text(stmt, 3);
			int photo_count = sqlite3_column_int(stmt, 4);

			if (!plate_text || !tb_text || !create_time_text) continue;

			std::string plate = reinterpret_cast<const char*>(plate_text);
			std::string tb = reinterpret_cast<const char*>(tb_text);
			std::string create_time_str = reinterpret_cast<const char*>(create_time_text);

			struct tm tm_buf;
			memset(&tm_buf, 0, sizeof(tm_buf));
			if (strptime(create_time_str.c_str(), "%Y-%m-%d %H:%M:%S", &tm_buf) == NULL) continue;
			time_t ct = mktime(&tm_buf);
			if (ct == (time_t)-1) continue;

			if (difftime(now, ct) <= window_seconds) {
				records.emplace_back(plate, tb, station_type, ct, photo_count);
			}
			else {
				break;
			}
		}

		sqlite3_finalize(stmt);
	}

	int restored_count = 0;
	for (const auto& rec : records) {
		const std::string& plate = std::get<0>(rec);
		const std::string& tb = std::get<1>(rec);
		int station_type = std::get<2>(rec);
		time_t ct = std::get<3>(rec);
		int photo_count = std::get<4>(rec);

		if (station_type == 1) {
			std::lock_guard<std::mutex> lock2(g_in_bill_cache_mtx);
			if (g_in_bill_cache.find(plate) == g_in_bill_cache.end()) {
				g_in_bill_cache[plate] = { tb, ct, 0, photo_count, true };
				restored_count++;
			}
		}
		else if (station_type == 2) {
			std::lock_guard<std::mutex> lock2(g_out_bill_cache_mtx);
			if (g_out_bill_cache.find(plate) == g_out_bill_cache.end()) {
				g_out_bill_cache[plate] = { tb, ct, 0, photo_count, true };
				restored_count++;
			}
		}
	}

	std::cout << "✅ 从数据库恢复了 " << restored_count << " 条联单缓存" << std::endl;
}

void close_database() {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (g_db) {
		sqlite3_close(g_db);
		g_db = nullptr;
	}
}

int db_self_test() {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return -1;

	const char* insert_sql = "INSERT INTO upload_records (plate_number, tb_num, station_type) "
		"VALUES ('__db_test__', 'TESTTB', 1);";
	char* err = nullptr;
	int rc = sqlite3_exec(g_db, insert_sql, nullptr, nullptr, &err);
	if (rc != SQLITE_OK) {
		std::cerr << "数据库自测失败: 插入失败" << std::endl;
		if (err) sqlite3_free(err);
		return -1;
	}

	const char* select_sql = "SELECT id FROM upload_records WHERE plate_number='__db_test__' LIMIT 1;";
	sqlite3_stmt* stmt = nullptr;
	rc = sqlite3_prepare_v2(g_db, select_sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK || sqlite3_step(stmt) != SQLITE_ROW) {
		std::cerr << "数据库自测失败: 查询失败" << std::endl;
		sqlite3_finalize(stmt);
		return -1;
	}

	int test_id = sqlite3_column_int(stmt, 0);
	sqlite3_finalize(stmt);

	char delete_sql[256];
	snprintf(delete_sql, sizeof(delete_sql), "DELETE FROM upload_records WHERE id=%d;", test_id);
	rc = sqlite3_exec(g_db, delete_sql, nullptr, nullptr, &err);
	if (rc != SQLITE_OK) {
		if (err) sqlite3_free(err);
	}

	std::cout << "✅ 数据库自测通过" << std::endl;
	return 0;
}

void db_cleanup_old_records_unlocked() {
    if (!g_db) return;

    struct stat st;
    if (stat(g_db_path.c_str(), &st) == -1) {
        std::cerr << "[数据库清理] stat调用失败: " << strerror(errno) 
                  << "，跳过本次清理" << std::endl;
        return;
    }

    if (st.st_size < DB_CLEANUP_THRESHOLD_BYTES) {
        return;
    }

    char* err_msg = nullptr;
    
    const char* delete_sql = "DELETE FROM upload_records WHERE create_time < datetime('now', '-30 days');";
    int rc = sqlite3_exec(g_db, delete_sql, nullptr, nullptr, &err_msg);
    if (rc != SQLITE_OK) {
        std::cerr << "[数据库清理] 删除旧记录失败: " << err_msg << std::endl;
        sqlite3_free(err_msg);
    } else {
        int affected = sqlite3_changes(g_db);
        std::cout << "[数据库清理] 删除30天前记录 " << affected << " 条" << std::endl;
    }
}


// ============================================
// ============================================
// TIME_WINDOW持久化函数
// ============================================

// 保存单个车牌的时间窗口缓存到数据库（仅保存到数据库，不更新内存缓存）
void db_save_tw_cache(const std::string& plate, bool is_in, time_t create_time, time_t last_time, int bill_count, int photo_count, const std::string& tb_num, int generation) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return;
    
    char* errMsg = nullptr;
    std::string sql = "INSERT OR REPLACE INTO time_window_cache (plate_number, station_type, create_time, last_time, bill_count, photo_count, tb_num, generation) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, plate.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 2, is_in ? 0 : 1);  // 0=进站, 1=出站
        sqlite3_bind_int64(stmt, 3, create_time);
        sqlite3_bind_int64(stmt, 4, last_time);
        sqlite3_bind_int(stmt, 5, bill_count);
        sqlite3_bind_int(stmt, 6, photo_count);
        sqlite3_bind_text(stmt, 7, tb_num.empty() ? "" : tb_num.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 8, generation);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
    // ✅ 修复：不再在此处更新内存缓存，避免死锁
    // 内存缓存的更新由调用方在持有正确锁的情况下完成
}

// 辅助函数：将单条TIME_WINDOW缓存记录加载到内存
static void _load_single_tw_cache_record(const std::string& plate, int station_type, 
    time_t create_time, time_t last_time, int bill_count, int photo_count, 
    const std::string& tb_num, int generation) {
    bool is_in = (station_type == 0);
    
    if (is_in) {
        std::lock_guard<std::mutex> lock_in(g_in_bill_cache_mtx);
        g_in_bill_cache[plate] = { tb_num, create_time, 0, photo_count, (bill_count > 0) };
    } else {
        std::lock_guard<std::mutex> lock_out(g_out_bill_cache_mtx);
        g_out_bill_cache[plate] = { tb_num, create_time, 0, photo_count, (bill_count > 0) };
    }
    
    std::lock_guard<std::mutex> lock_rec(is_in ? g_in_record_mtx : g_out_record_mtx);
    auto& records = is_in ? g_in_upload_records : g_out_upload_records;
    SceneUploadRecord rec;
    rec.bill_count = bill_count;
    rec.last_time = last_time;
    rec.create_time = create_time;
    rec.tb_num = tb_num;
    rec.generation = generation;
    rec.bill_created = (bill_count > 0);
    records[plate] = rec;
}

// 加载时间窗口缓存到内存（内部版本，无锁，由db_load_tw_cache调用）
static void _db_load_tw_cache_unlocked() {
    if (!g_db) return;
    
    time_t now = time(NULL);
    int window_seconds = g_time_window_min * 60;
    
    std::cout << "[DEBUG] 当前时间: " << now << ", TIME_WINDOW: " << g_time_window_min << "分钟(" << window_seconds << "秒)" << std::endl;
    
    // 从time_window_cache加载
    const char* sql = "SELECT plate_number, station_type, create_time, last_time, bill_count, photo_count, tb_num, generation FROM time_window_cache;";
    sqlite3_stmt* stmt;
    
    int count = 0;
    int reset_count = 0;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            std::string plate = (const char*)sqlite3_column_text(stmt, 0);
            int station_type = sqlite3_column_int(stmt, 1);
            bool is_in = (station_type == 0);
            time_t create_time = sqlite3_column_int64(stmt, 2);
            time_t last_time = sqlite3_column_int64(stmt, 3);
            int bill_count = sqlite3_column_int(stmt, 4);
            int photo_count = sqlite3_column_int(stmt, 5);
            const char* tb_num_cstr = (const char*)sqlite3_column_text(stmt, 6);
            std::string tb_num = tb_num_cstr ? tb_num_cstr : "";
            int generation = sqlite3_column_int(stmt, 7);
            
            // ✅ 核心判断：用last_time判断是否超过TIME_WINDOW
            // 如果last_time=0，用create_time
            time_t record_time = last_time > 0 ? last_time : create_time;
            
            // 如果record_time=0，说明是新记录（从未处理过），正常加载
            if (record_time == 0) {
                std::cout << "[DEBUG] " << plate << (is_in ? "进站" : "出站") 
                    << " 新记录，首次处理，正常加载" << std::endl;
                // 正常加载到内存
                _load_single_tw_cache_record(plate, station_type, create_time, last_time, 
                    bill_count, photo_count, tb_num, generation);
                count++;
            } else {
                std::cout << "[DEBUG] " << plate << (is_in ? "进站" : "出站") 
                    << " last_time=" << last_time << ", create_time=" << create_time 
                    << ", 距今: " << (int)difftime(now, record_time) << "秒" << std::endl;
                
                // ✅ 判断是否超过TIME_WINDOW
                if (difftime(now, record_time) >= window_seconds) {
                    std::cout << "[TIME_WINDOW重置] " << plate << (is_in ? "进站" : "出站")
                        << " 距上次处理" << (int)difftime(now, record_time) << "秒超过" << window_seconds << "秒，重启后允许重置" << std::endl;
                    
                    // ✅ v43.2修复：使用参数化查询防止SQL注入
                    const char* del_sql = "DELETE FROM time_window_cache WHERE plate_number=? AND station_type=?;";
                    sqlite3_stmt* del_stmt;
                    if (sqlite3_prepare_v2(g_db, del_sql, -1, &del_stmt, nullptr) == SQLITE_OK) {
                        sqlite3_bind_text(del_stmt, 1, plate.c_str(), -1, SQLITE_TRANSIENT);
                        sqlite3_bind_int(del_stmt, 2, station_type);
                        sqlite3_step(del_stmt);
                        sqlite3_finalize(del_stmt);
                    }
                    
                    // ✅ 关键修复：同时清理 g_upload_records 中的旧记录，避免重启后配额检查错误
                    if (is_in) {
                        std::lock_guard<std::mutex> lock(g_in_record_mtx);
                        g_in_upload_records.erase(plate);
                    } else {
                        std::lock_guard<std::mutex> lock(g_out_record_mtx);
                        g_out_upload_records.erase(plate);
                    }
                    
                    reset_count++;
                    // 不加载到内存，让程序重新识别
                } else {
                    // ✅ 5分钟内，正常加载缓存
                    std::cout << "[DEBUG] " << plate << (is_in ? "进站" : "出站") 
                        << " 5分钟内，正常加载缓存" << std::endl;
                    _load_single_tw_cache_record(plate, station_type, create_time, last_time, 
                        bill_count, photo_count, tb_num, generation);
                    count++;
                }
            }
        }
        sqlite3_finalize(stmt);
        std::cout << "✅ 从数据库加载" << count << "条TIME_WINDOW缓存记录，重置" << reset_count << "条" << std::endl;
    }
}

// 加载时间窗口缓存到内存（带锁版本，供外部调用）
void db_load_tw_cache() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    _db_load_tw_cache_unlocked();
}

// 清理过期的时间窗口缓存（超过24小时）
void db_cleanup_expired_tw_cache() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return;
    
    time_t now = time(NULL);
    int expired_time = now - 24 * 60 * 60;  // 24小时前
    
    // ✅ Bug修复：使用 last_time 判断过期，因为 create_time 可能为0
    // 同时排除 last_time=0 的记录（表示从未处理过）
    char sql[256];
    snprintf(sql, sizeof(sql), 
        "DELETE FROM time_window_cache WHERE last_time > 0 AND last_time < %ld;", 
        expired_time);
    
    char* errMsg = nullptr;
    int rc = sqlite3_exec(g_db, sql, nullptr, nullptr, &errMsg);
    if (rc == SQLITE_OK) {
        int changes = sqlite3_changes(g_db);
        if (changes > 0) {
            std::cout << "[TIME_WINDOW清理] 删除" << changes << "条过期缓存记录" << std::endl;
        }
    } else {
        std::cerr << "[TIME_WINDOW清理] 删除失败: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
}


void db_vacuum() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return;

    char* err_msg = nullptr;
    const char* vacuum_sql = "VACUUM;";
    int rc = sqlite3_exec(g_db, vacuum_sql, nullptr, nullptr, &err_msg);
    if (rc != SQLITE_OK) {
        std::cerr << "[数据库优化] VACUUM失败: " << err_msg << std::endl;
        sqlite3_free(err_msg);
    } else {
        std::cout << "[数据库优化] VACUUM执行完成，释放磁盘空间" << std::endl;
    }
}

void db_cleanup_old_records() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    db_cleanup_old_records_unlocked();
}

// ✅ v41 优化：单次数据库操作最大阻塞时间 < 100ms
int db_insert_record_with_retry(const std::string& plate_number, const std::string& tb_num, int station_type,
	const std::string& lo_photo_path, const std::string& hi_photo_path,
	int lo_upload_status, int hi_upload_status, int feishu_status,
	time_t capture_time) {  // ✅ fix24-v13: 新增capture_time参数
    
    for (int retry = 0; retry < DB_INSERT_RETRY_COUNT; retry++) {
        if (retry > 0) {
            // 仅重试1次，等待50ms
            std::cerr << "[数据库插入] 重试第" << retry << "次，等待50ms..." << std::endl;
            std::this_thread::sleep_for(std::chrono::milliseconds(50));
        }
        
        int result = db_insert_record(plate_number, tb_num, station_type,
            lo_photo_path, hi_photo_path, lo_upload_status, hi_upload_status, feishu_status, capture_time);
        
        if (result > 0) {
            if (retry > 0) {
                g_metrics.record_db_insert_retry_success();
            }
            return result;
        }
    }
    
    g_metrics.record_db_insert_error();
    // ✅ v41 新增：数据库插入失败时记录严重告警日志
    std::cerr << "[严重] 数据库插入失败，车牌=" << plate_number 
              << "，联单=" << tb_num << "，数据将在下次重试时重新插入" << std::endl;
    return -1;
}

int db_insert_record(const std::string& plate_number, const std::string& tb_num, int station_type,
	const std::string& lo_photo_path, const std::string& hi_photo_path,
	int lo_upload_status, int hi_upload_status, int feishu_status,
	time_t capture_time) {  // ✅ fix24-v13: 新增capture_time参数
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return -1;

	const char* sql = "INSERT INTO upload_records "
		"(plate_number, tb_num, station_type, lo_photo_path, hi_photo_path, "
		"lo_upload_status, hi_upload_status, feishu_status, capture_time, create_time) "
		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, datetime(?, 'unixepoch', 'localtime'));";

	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) {
		std::cerr << "准备插入语句失败: " << sqlite3_errmsg(g_db) << std::endl;
		return -1;
	}

	sqlite3_bind_text(stmt, 1, plate_number.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 2, tb_num.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(stmt, 3, station_type);
	sqlite3_bind_text(stmt, 4, lo_photo_path.empty() ? nullptr : lo_photo_path.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 5, hi_photo_path.empty() ? nullptr : hi_photo_path.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(stmt, 6, lo_upload_status);
	sqlite3_bind_int(stmt, 7, hi_upload_status);
	sqlite3_bind_int(stmt, 8, feishu_status);
	sqlite3_bind_int64(stmt, 9, capture_time > 0 ? capture_time : time(NULL));  // ✅ fix24-v13
	sqlite3_bind_int64(stmt, 10, capture_time > 0 ? capture_time : time(NULL));  // ✅ fix24-v17: create_time使用capture_time，避免DB插入延迟

	rc = sqlite3_step(stmt);
	if (rc != SQLITE_DONE) {
		std::cerr << "插入记录失败: " << sqlite3_errmsg(g_db) << std::endl;
		sqlite3_finalize(stmt);
		return -1;
	}

	int new_id = static_cast<int>(sqlite3_last_insert_rowid(g_db));
	sqlite3_finalize(stmt);
	db_cleanup_old_records_unlocked();

	if (DEBUG_LOG) {
		std::cout << "[DEBUG] 数据库插入成功，ID: " << new_id
			<< "，车牌: " << plate_number << "，联单: " << tb_num << std::endl;
	}
	return new_id;
}

int db_update_upload_status(int id, int lo_status, int hi_status) {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db || id <= 0) return -1;

	const char* sql = "UPDATE upload_records SET lo_upload_status=?, hi_upload_status=? WHERE id=?;";
	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return -1;

	sqlite3_bind_int(stmt, 1, lo_status);
	sqlite3_bind_int(stmt, 2, hi_status);
	sqlite3_bind_int(stmt, 3, id);

	rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	return (rc == SQLITE_DONE) ? 0 : -1;
}

int db_update_feishu_status(int id, int status) {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db || id <= 0) return -1;

	const char* sql = "UPDATE upload_records SET feishu_status=? WHERE id=?;";
	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return -1;

	sqlite3_bind_int(stmt, 1, status);
	sqlite3_bind_int(stmt, 2, id);

	rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	return (rc == SQLITE_DONE) ? 0 : -1;
}

int db_increment_retry_count(int id) {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db || id <= 0) return -1;

	const char* sql = "UPDATE upload_records SET retry_count=retry_count+1 WHERE id=?;";
	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return -1;

	sqlite3_bind_int(stmt, 1, id);

	rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	return (rc == SQLITE_DONE) ? 0 : -1;
}

int db_get_total_count() {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return 0;

	const char* sql = "SELECT COUNT(*) FROM upload_records;";
	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return 0;

	int count = 0;
	if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
	sqlite3_finalize(stmt);
	return count;
}

int db_get_failed_count() {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return 0;

	const char* sql = "SELECT COUNT(*) FROM upload_records WHERE "
		"(lo_upload_status != 1 OR hi_upload_status != 1 OR feishu_status = 2);";

	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return 0;

	int count = 0;
	if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
	sqlite3_finalize(stmt);
	return count;
}

std::vector<DbRecord> db_get_records(int page, int page_size, const std::string& keyword) {
	std::vector<DbRecord> records;
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return records;

	// ✅ fix24-v14: 校正strftime('%s')的时区问题
	// SQLite的strftime('%s', str)将str视为UTC，但create_time存的是本地时间
	// 需要减去时区偏移才能得到正确的Unix时间戳
	// ✅ fix24-v15.1: 显式CAST为INTEGER，解决COALESCE类型不一致导致排序按字符串比较的Bug
	std::string tz_modifier = std::to_string(-g_tz_offset_seconds) + " seconds";
	std::string sort_expr = "CAST(COALESCE(capture_time, strftime('%s', create_time, '" + tz_modifier + "')) AS INTEGER)";
	std::string sql;
	if (keyword.empty()) {
		sql = "SELECT id, create_time, capture_time, plate_number, tb_num, station_type, "
			"lo_photo_path, hi_photo_path, lo_upload_status, hi_upload_status, "
			"feishu_status, retry_count FROM upload_records "
			"ORDER BY " + sort_expr + " DESC LIMIT ? OFFSET ?;";
	}
	else {
		sql = "SELECT id, create_time, capture_time, plate_number, tb_num, station_type, "
			"lo_photo_path, hi_photo_path, lo_upload_status, hi_upload_status, "
			"feishu_status, retry_count FROM upload_records "
			"WHERE plate_number LIKE ? OR tb_num LIKE ? OR create_time LIKE ? "
			"ORDER BY " + sort_expr + " DESC LIMIT ? OFFSET ?;";
	}

	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return records;

	int param_idx = 1;
	if (!keyword.empty()) {
		std::string like_keyword = "%" + keyword + "%";
		sqlite3_bind_text(stmt, param_idx++, like_keyword.c_str(), -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(stmt, param_idx++, like_keyword.c_str(), -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(stmt, param_idx++, like_keyword.c_str(), -1, SQLITE_TRANSIENT);
	}

	sqlite3_bind_int(stmt, param_idx++, page_size);
	sqlite3_bind_int(stmt, param_idx++, (page - 1) * page_size);

	while (sqlite3_step(stmt) == SQLITE_ROW) {
		DbRecord rec;
		rec.id = sqlite3_column_int(stmt, 0);
		rec.create_time = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
		// ✅ fix24-v13: 读取capture_time，旧记录为NULL时fallback到create_time
		if (sqlite3_column_type(stmt, 2) != SQLITE_NULL) {
			rec.capture_time = sqlite3_column_int64(stmt, 2);
		} else {
			rec.capture_time = 0;
		}
		rec.plate_number = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
		rec.tb_num = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
		rec.station_type = sqlite3_column_int(stmt, 5);
		const char* lo_path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
		rec.lo_photo_path = lo_path ? lo_path : "";
		const char* hi_path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
		rec.hi_photo_path = hi_path ? hi_path : "";
		rec.lo_upload_status = sqlite3_column_int(stmt, 8);
		rec.hi_upload_status = sqlite3_column_int(stmt, 9);
		rec.feishu_status = sqlite3_column_int(stmt, 10);
		rec.retry_count = sqlite3_column_int(stmt, 11);
		records.push_back(rec);
	}

	sqlite3_finalize(stmt);
	return records;
}

// ==================== fix24 v41: 分类查询实现 ====================

// 内部：构造 WHERE 子句（不含 WHERE 关键字本身），并返回绑定参数数量
// 通过 vector<string> 收集绑定值
static std::string _build_where_clause(const RecordQuery& q, std::vector<std::string>& bind_vals) {
	std::string where;
	auto addCond = [&](const std::string& cond) {
		if (where.empty()) where = " WHERE " + cond;
		else where += " AND " + cond;
	};

	if (!q.plate_number.empty()) {
		addCond("plate_number LIKE ?");
		bind_vals.push_back("%" + q.plate_number + "%");
	}
	if (!q.tb_num.empty()) {
		addCond("tb_num LIKE ?");
		bind_vals.push_back("%" + q.tb_num + "%");
	}
	if (!q.date_from.empty()) {
		addCond("create_time >= ?");
		bind_vals.push_back(q.date_from + " 00:00:00");
	}
	if (!q.date_to.empty()) {
		addCond("create_time <= ?");
		bind_vals.push_back(q.date_to + " 23:59:59");
	}
	if (q.station_type == 1 || q.station_type == 2) {
		addCond("station_type = ?");
		bind_vals.push_back(std::to_string(q.station_type));
	}
	if (q.upload_status == 1) {
		// 成功：两路均上传成功
		addCond("lo_upload_status = 1 AND hi_upload_status = 1");
	} else if (q.upload_status == 2) {
		// 失败：任一路上传失败
		addCond("(lo_upload_status != 1 OR hi_upload_status != 1)");
	}
	if (q.feishu_status == 1) {
		addCond("feishu_status = 1");
	} else if (q.feishu_status == 2) {
		addCond("feishu_status = 2");
	} else if (q.feishu_status == 3) {
		addCond("feishu_status = 0");
	}
	return where;
}

int db_count_records_advanced(const RecordQuery& q) {
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return 0;

	std::vector<std::string> bind_vals;
	std::string where = _build_where_clause(q, bind_vals);
	std::string sql = "SELECT COUNT(*) FROM upload_records" + where + ";";

	sqlite3_stmt* stmt = nullptr;
	if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) return 0;

	for (size_t i = 0; i < bind_vals.size(); ++i) {
		sqlite3_bind_text(stmt, (int)i + 1, bind_vals[i].c_str(), -1, SQLITE_TRANSIENT);
	}

	int count = 0;
	if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
	sqlite3_finalize(stmt);
	return count;
}

std::vector<DbRecord> db_get_records_advanced(int page, int page_size, const RecordQuery& q) {
	std::vector<DbRecord> records;
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db) return records;

	if (page < 1) page = 1;
	if (page_size < 1) page_size = 20;
	if (page_size > 200) page_size = 200;  // 防止过大查询

	std::vector<std::string> bind_vals;
	std::string where = _build_where_clause(q, bind_vals);

	// 排序表达式与旧版保持一致
	std::string tz_modifier = std::to_string(-g_tz_offset_seconds) + " seconds";
	std::string sort_expr = "CAST(COALESCE(capture_time, strftime('%s', create_time, '" + tz_modifier + "')) AS INTEGER)";

	std::string sql =
		"SELECT id, create_time, capture_time, plate_number, tb_num, station_type, "
		"lo_photo_path, hi_photo_path, lo_upload_status, hi_upload_status, "
		"feishu_status, retry_count FROM upload_records"
		+ where + " ORDER BY " + sort_expr + " DESC LIMIT ? OFFSET ?;";

	sqlite3_stmt* stmt = nullptr;
	if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) return records;

	int param_idx = 1;
	for (auto& v : bind_vals) {
		sqlite3_bind_text(stmt, param_idx++, v.c_str(), -1, SQLITE_TRANSIENT);
	}
	sqlite3_bind_int(stmt, param_idx++, page_size);
	sqlite3_bind_int(stmt, param_idx++, (page - 1) * page_size);

	while (sqlite3_step(stmt) == SQLITE_ROW) {
		DbRecord rec;
		rec.id = sqlite3_column_int(stmt, 0);
		const char* ct = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
		rec.create_time = ct ? ct : "";
		if (sqlite3_column_type(stmt, 2) != SQLITE_NULL)
			rec.capture_time = sqlite3_column_int64(stmt, 2);
		else
			rec.capture_time = 0;
		const char* pn = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
		rec.plate_number = pn ? pn : "";
		const char* tb = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
		rec.tb_num = tb ? tb : "";
		rec.station_type = sqlite3_column_int(stmt, 5);
		const char* lo = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
		rec.lo_photo_path = lo ? lo : "";
		const char* hi = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
		rec.hi_photo_path = hi ? hi : "";
		rec.lo_upload_status = sqlite3_column_int(stmt, 8);
		rec.hi_upload_status = sqlite3_column_int(stmt, 9);
		rec.feishu_status = sqlite3_column_int(stmt, 10);
		rec.retry_count = sqlite3_column_int(stmt, 11);
		records.push_back(rec);
	}
	sqlite3_finalize(stmt);
	return records;
}

DbRecord db_get_record(int id) {
	DbRecord rec;
	std::lock_guard<std::mutex> lock(g_db_mtx);
	if (!g_db || id <= 0) return rec;

	const char* sql = "SELECT id, create_time, capture_time, plate_number, tb_num, station_type, "
		"lo_photo_path, hi_photo_path, lo_upload_status, hi_upload_status, "
		"feishu_status, retry_count FROM upload_records WHERE id=?;";

	sqlite3_stmt* stmt = nullptr;
	int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr);
	if (rc != SQLITE_OK) return rec;

	sqlite3_bind_int(stmt, 1, id);
	if (sqlite3_step(stmt) == SQLITE_ROW) {
		rec.id = sqlite3_column_int(stmt, 0);
		rec.create_time = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
		// ✅ fix24-v13: 读取capture_time
		if (sqlite3_column_type(stmt, 2) != SQLITE_NULL) {
			rec.capture_time = sqlite3_column_int64(stmt, 2);
		} else {
			rec.capture_time = 0;
		}
		rec.plate_number = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
		rec.tb_num = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
		rec.station_type = sqlite3_column_int(stmt, 5);
		const char* lo_path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
		rec.lo_photo_path = lo_path ? lo_path : "";
		const char* hi_path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
		rec.hi_photo_path = hi_path ? hi_path : "";
		rec.lo_upload_status = sqlite3_column_int(stmt, 8);
		rec.hi_upload_status = sqlite3_column_int(stmt, 9);
		rec.feishu_status = sqlite3_column_int(stmt, 10);
		rec.retry_count = sqlite3_column_int(stmt, 11);
	}

	sqlite3_finalize(stmt);
	return rec;
}

int manual_retry_record(int id) {
	DbRecord rec = db_get_record(id);
	if (rec.id == 0) return -1;
	std::cout << "[手动重试] ID=" << id << " 车牌=" << rec.plate_number << std::endl;
	db_increment_retry_count(id);
	return 0;
}

// ==================== v43新增：增强重试功能 ====================

// 重试结果结构体


// v43新增：更新联单编号
void db_update_tb_num(int id, const std::string& tb_num) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db || id <= 0) return;
    const char* sql = "UPDATE upload_records SET tb_num = ? WHERE id = ?;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, tb_num.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 2, id);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
}

// v43新增：更新单侧上传状态
void db_update_upload_status_side(int id, const std::string& side, int status) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db || id <= 0) return;
    std::string col = (side == "lo") ? "lo_upload_status" : "hi_upload_status";
    std::string sql = "UPDATE upload_records SET " + col + " = ? WHERE id = ?;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
        sqlite3_bind_int(stmt, 1, status);
        sqlite3_bind_int(stmt, 2, id);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
}

// v43新增：增强重试记录 — 重新获取联单编号 + 重传失败照片 + 更新DB

// ==================== fix24-v9: 称重记录管理 ====================

int db_insert_weight_record(const std::string& plate_number, const std::string& tb_num,
    int station_type, double weight_kg, int upload_status, const std::string& response_msg) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return -1;
    const char* sql = "INSERT INTO weight_records (plate_number, tb_num, station_type, weight_kg, upload_status, response_msg) VALUES (?, ?, ?, ?, ?, ?);";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return -1;
    sqlite3_bind_text(stmt, 1, plate_number.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, tb_num.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 3, station_type);
    sqlite3_bind_double(stmt, 4, weight_kg);
    sqlite3_bind_int(stmt, 5, upload_status);
    sqlite3_bind_text(stmt, 6, response_msg.c_str(), -1, SQLITE_TRANSIENT);
    int rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (rc == SQLITE_DONE) ? (int)sqlite3_last_insert_rowid(g_db) : -1;
}

int db_update_weight_upload_status(int id, int upload_status, const std::string& response_msg) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db || id <= 0) return -1;
    const char* sql = "UPDATE weight_records SET upload_status = ?, response_msg = ? WHERE id = ?;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return -1;
    sqlite3_bind_int(stmt, 1, upload_status);
    sqlite3_bind_text(stmt, 2, response_msg.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 3, id);
    int rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (rc == SQLITE_DONE) ? 0 : -1;
}

int db_increment_weight_retry_count(int id) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db || id <= 0) return -1;
    const char* sql = "UPDATE weight_records SET retry_count = retry_count + 1 WHERE id = ?;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return -1;
    sqlite3_bind_int(stmt, 1, id);
    int rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (rc == SQLITE_DONE) ? 0 : -1;
}

std::vector<DbRecord> db_get_weight_records(int page, int page_size, const std::string& keyword) {
    std::vector<DbRecord> records;
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return records;
    if (page < 1) page = 1;
    if (page_size < 1 || page_size > 100) page_size = 20;
    int offset = (page - 1) * page_size;

    std::string sql;
    if (keyword.empty()) {
        sql = "SELECT id, create_time, plate_number, tb_num, station_type, weight_kg, upload_status, retry_count, response_msg FROM weight_records ORDER BY id DESC LIMIT ? OFFSET ?;";
    } else {
        sql = "SELECT id, create_time, plate_number, tb_num, station_type, weight_kg, upload_status, retry_count, response_msg FROM weight_records WHERE plate_number LIKE ? OR tb_num LIKE ? ORDER BY id DESC LIMIT ? OFFSET ?;";
    }

    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) return records;

    if (keyword.empty()) {
        sqlite3_bind_int(stmt, 1, page_size);
        sqlite3_bind_int(stmt, 2, offset);
    } else {
        std::string kw = "%" + keyword + "%";
        sqlite3_bind_text(stmt, 1, kw.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, kw.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 3, page_size);
        sqlite3_bind_int(stmt, 4, offset);
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        DbRecord rec;
        rec.id = sqlite3_column_int(stmt, 0);
        rec.create_time = (const char*)sqlite3_column_text(stmt, 1);
        rec.plate_number = (const char*)sqlite3_column_text(stmt, 2);
        rec.tb_num = (const char*)sqlite3_column_text(stmt, 3);
        rec.station_type = sqlite3_column_int(stmt, 4);
        rec.lo_upload_status = sqlite3_column_int(stmt, 6); // upload_status
        rec.hi_upload_status = sqlite3_column_int(stmt, 7); // retry_count
        rec.feishu_status = 0;
        double weight = sqlite3_column_double(stmt, 5);
        rec.hi_photo_path = std::to_string(weight);
        const char* msg = (const char*)sqlite3_column_text(stmt, 8);
        rec.lo_photo_path = msg ? msg : "";
        records.push_back(rec);
    }
    sqlite3_finalize(stmt);
    return records;
}

int db_get_weight_total_count() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return 0;
    const char* sql = "SELECT COUNT(*) FROM weight_records;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return 0;
    int count = 0;
    if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

int db_get_weight_failed_count() {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return 0;
    const char* sql = "SELECT COUNT(*) FROM weight_records WHERE upload_status = 2;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return 0;
    int count = 0;
    if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

// fix27: 根据车牌和联单编号获取重量（用于飞书消息）
double db_get_weight_by_plate(const std::string& plate, const std::string& tb_num) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return -1.0;
    const char* sql = "SELECT weight_kg FROM weight_records WHERE plate_number = ? AND tb_num = ? ORDER BY id DESC LIMIT 1;";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) return -1.0;
    sqlite3_bind_text(stmt, 1, plate.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, tb_num.c_str(), -1, SQLITE_TRANSIENT);
    double weight = -1.0;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        weight = sqlite3_column_double(stmt, 0);
    }
    sqlite3_finalize(stmt);
    return weight;
}

// fix27: 等待重量数据写入数据库（最多等待 max_wait_ms 毫秒）
double db_wait_weight_by_plate(const std::string& plate, const std::string& tb_num, int max_wait_ms) {
    // 每 200ms 检查一次，最多等待 max_wait_ms
    const int check_interval_ms = 200;
    const int max_checks = max_wait_ms / check_interval_ms;
    
    for (int i = 0; i < max_checks; i++) {
        double weight = db_get_weight_by_plate(plate, tb_num);
        if (weight >= 0) {
            return weight;  // 找到重量数据
        }
        std::this_thread::sleep_for(std::chrono::milliseconds(check_interval_ms));
    }
    return -1.0;  // 超时未找到
}

// ==================== fix24 v41: 称重记录分类查询 ====================

// 构建 WHERE 子句，返回参数化占位符；bind_values 收集需要绑定的值
// 返回值: WHERE 子句字符串（不含 "WHERE" 关键字），空串表示无条件
static std::string _build_weight_where_clause(const WeightQuery& q,
    std::vector<std::string>& bind_texts, std::vector<int>& bind_ints) {
    std::string where;
    bind_texts.clear();
    bind_ints.clear();

    if (!q.plate_number.empty()) {
        if (!where.empty()) where += " AND ";
        where += "plate_number LIKE ?";
        bind_texts.push_back("%" + q.plate_number + "%");
    }
    if (!q.tb_num.empty()) {
        if (!where.empty()) where += " AND ";
        where += "tb_num LIKE ?";
        bind_texts.push_back("%" + q.tb_num + "%");
    }
    if (!q.date_from.empty()) {
        if (!where.empty()) where += " AND ";
        where += "create_time >= ?";
        bind_texts.push_back(q.date_from + " 00:00:00");
    }
    if (!q.date_to.empty()) {
        if (!where.empty()) where += " AND ";
        where += "create_time <= ?";
        bind_texts.push_back(q.date_to + " 23:59:59");
    }
    if (q.station_type == 1 || q.station_type == 2) {
        if (!where.empty()) where += " AND ";
        where += "station_type = ?";
        bind_ints.push_back(q.station_type);
    }
    if (q.upload_status == 1) {
        // 成功
        if (!where.empty()) where += " AND ";
        where += "upload_status = 1";
    } else if (q.upload_status == 2) {
        // 失败
        if (!where.empty()) where += " AND ";
        where += "upload_status = 2";
    } else if (q.upload_status == 3) {
        // 待上传
        if (!where.empty()) where += " AND ";
        where += "upload_status = 0";
    }
    return where;
}

int db_count_weight_records_advanced(const WeightQuery& q) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return 0;

    std::vector<std::string> bind_texts;
    std::vector<int> bind_ints;
    std::string where = _build_weight_where_clause(q, bind_texts, bind_ints);

    std::string sql = "SELECT COUNT(*) FROM weight_records";
    if (!where.empty()) sql += " WHERE " + where;
    sql += ";";

    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) return 0;

    int idx = 1;
    for (auto& v : bind_texts) sqlite3_bind_text(stmt, idx++, v.c_str(), -1, SQLITE_TRANSIENT);
    for (auto& v : bind_ints) sqlite3_bind_int(stmt, idx++, v);

    int count = 0;
    if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

std::vector<DbRecord> db_get_weight_records_advanced(int page, int page_size, const WeightQuery& q) {
    std::vector<DbRecord> records;
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return records;
    if (page < 1) page = 1;
    if (page_size < 1) page_size = 20;
    if (page_size > 200) page_size = 200;
    int offset = (page - 1) * page_size;

    std::vector<std::string> bind_texts;
    std::vector<int> bind_ints;
    std::string where = _build_weight_where_clause(q, bind_texts, bind_ints);

    std::string sql = "SELECT id, create_time, plate_number, tb_num, station_type, weight_kg, upload_status, retry_count, response_msg FROM weight_records";
    if (!where.empty()) sql += " WHERE " + where;
    sql += " ORDER BY id DESC LIMIT ? OFFSET ?;";

    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) return records;

    int idx = 1;
    for (auto& v : bind_texts) sqlite3_bind_text(stmt, idx++, v.c_str(), -1, SQLITE_TRANSIENT);
    for (auto& v : bind_ints) sqlite3_bind_int(stmt, idx++, v);
    sqlite3_bind_int(stmt, idx++, page_size);
    sqlite3_bind_int(stmt, idx++, offset);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        DbRecord rec;
        rec.id = sqlite3_column_int(stmt, 0);
        const char* ct = (const char*)sqlite3_column_text(stmt, 1);
        rec.create_time = ct ? ct : "";
        const char* pn = (const char*)sqlite3_column_text(stmt, 2);
        rec.plate_number = pn ? pn : "";
        const char* tn = (const char*)sqlite3_column_text(stmt, 3);
        rec.tb_num = tn ? tn : "";
        rec.station_type = sqlite3_column_int(stmt, 4);
        rec.lo_upload_status = sqlite3_column_int(stmt, 6);  // upload_status
        rec.hi_upload_status = sqlite3_column_int(stmt, 7);  // retry_count
        rec.feishu_status = 0;
        double weight = sqlite3_column_double(stmt, 5);
        rec.hi_photo_path = std::to_string(weight);
        const char* msg = (const char*)sqlite3_column_text(stmt, 8);
        rec.lo_photo_path = msg ? msg : "";
        records.push_back(rec);
    }
    sqlite3_finalize(stmt);
    return records;
}

// ==================== fix24 v41: 照片清理DB同步 ====================

/**
 * 从完整路径中提取文件名（basename）
 * 例: /opt/openAI/.../PlateJPG/沪FQ7108_1787365677_side_out.jpg -> 沪FQ7108_1787365677_side_out.jpg
 */
static std::string extract_basename(const std::string& path) {
    size_t pos = path.find_last_of("/\\");
    if (pos == std::string::npos) return path;
    return path.substr(pos + 1);
}

/**
 * 清空指定路径对应的照片字段（fix24 v41 修复版）
 * 当 daily_file_cleanup 物理删除照片后调用，同步清空 upload_records 表中的路径字段。
 * 保留业务记录（车牌号、联单编号等），只清空 lo_photo_path/hi_photo_path。
 *
 * 关键修复：DB 中存储的是相对文件名（如 沪FQ7108_xxx.jpg），
 * 而 daily_file_cleanup 传入的是绝对路径（如 /opt/.../PlateJPG/沪FQ7108_xxx.jpg），
 * 因此必须提取 basename 再匹配，否则永远匹配不到。
 *
 * @param paths 被删除的照片完整路径列表（绝对路径或相对文件名均可）
 * @return 成功更新的字段数（lo + hi 合计）
 */
int db_clear_photo_paths_by_paths(const std::vector<std::string>& paths) {
    if (paths.empty()) return 0;

    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return -1;

    int updated_count = 0;

    // 用一个事务批量执行，提升性能
    sqlite3_exec(g_db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);

    const char* sql_lo = "UPDATE upload_records SET lo_photo_path = NULL WHERE lo_photo_path = ?;";
    const char* sql_hi = "UPDATE upload_records SET hi_photo_path = NULL WHERE hi_photo_path = ?;";

    for (const auto& path : paths) {
        // 提取文件名进行匹配（DB 存储的是相对文件名）
        std::string basename = extract_basename(path);
        if (basename.empty()) continue;

        // 清空 lo_photo_path
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(g_db, sql_lo, -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, basename.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                updated_count += sqlite3_changes(g_db);
            }
            sqlite3_finalize(stmt);
        }

        // 清空 hi_photo_path
        stmt = nullptr;
        if (sqlite3_prepare_v2(g_db, sql_hi, -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, basename.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                updated_count += sqlite3_changes(g_db);
            }
            sqlite3_finalize(stmt);
        }
    }

    sqlite3_exec(g_db, "COMMIT;", nullptr, nullptr, nullptr);
    return updated_count;
}

/**
 * 启动时清理孤儿照片路径（fix24 v41 新增）
 *
 * 扫描 upload_records 表中所有 lo_photo_path / hi_photo_path 非 NULL 的记录，
 * 检查对应文件是否还存在于 PlateJPG/ 目录下；不存在的清空路径字段。
 * 用于修复历史遗留数据（照片已被旧版本清理但 DB 路径未同步）。
 *
 * @param photo_dir PlateJPG 目录的绝对路径（以 / 结尾）
 * @return 成功清空的字段数
 */
int db_purge_missing_photo_paths(const std::string& photo_dir) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return -1;

    int purged = 0;
    std::vector<std::pair<int64_t, std::string>> lo_paths, hi_paths;

    // 收集所有非空路径
    const char* select_sql =
        "SELECT id, lo_photo_path, hi_photo_path FROM upload_records "
        "WHERE lo_photo_path IS NOT NULL OR hi_photo_path IS NOT NULL;";

    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(g_db, select_sql, -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[DB清理] 查询失败: " << sqlite3_errmsg(g_db) << std::endl;
        return -1;
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int64_t id = sqlite3_column_int64(stmt, 0);
        const char* lo = (const char*)sqlite3_column_text(stmt, 1);
        const char* hi = (const char*)sqlite3_column_text(stmt, 2);
        if (lo && lo[0]) lo_paths.emplace_back(id, lo);
        if (hi && hi[0]) hi_paths.emplace_back(id, hi);
    }
    sqlite3_finalize(stmt);

    if (lo_paths.empty() && hi_paths.empty()) {
        std::cout << "[DB清理] 无需清理的孤儿路径" << std::endl;
        return 0;
    }

    std::cout << "[DB清理] 检查 " << lo_paths.size() << " 条lo路径 + "
              << hi_paths.size() << " 条hi路径..." << std::endl;

    // 检查 lo_photo_path
    sqlite3_exec(g_db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);

    const char* update_lo = "UPDATE upload_records SET lo_photo_path = NULL WHERE id = ?;";
    for (const auto& [id, filename] : lo_paths) {
        std::string full = photo_dir + filename;
        struct stat st;
        if (stat(full.c_str(), &st) != 0) {
            // 文件不存在，清空
            sqlite3_stmt* u = nullptr;
            if (sqlite3_prepare_v2(g_db, update_lo, -1, &u, nullptr) == SQLITE_OK) {
                sqlite3_bind_int64(u, 1, id);
                if (sqlite3_step(u) == SQLITE_DONE) purged++;
                sqlite3_finalize(u);
            }
        }
    }

    const char* update_hi = "UPDATE upload_records SET hi_photo_path = NULL WHERE id = ?;";
    for (const auto& [id, filename] : hi_paths) {
        std::string full = photo_dir + filename;
        struct stat st;
        if (stat(full.c_str(), &st) != 0) {
            sqlite3_stmt* u = nullptr;
            if (sqlite3_prepare_v2(g_db, update_hi, -1, &u, nullptr) == SQLITE_OK) {
                sqlite3_bind_int64(u, 1, id);
                if (sqlite3_step(u) == SQLITE_DONE) purged++;
                sqlite3_finalize(u);
            }
        }
    }

    sqlite3_exec(g_db, "COMMIT;", nullptr, nullptr, nullptr);

    std::cout << "[DB清理] 清空 " << purged << " 个孤儿路径字段" << std::endl;
    return purged;
}
// ==================== fix24 v43: 原子写入两表 ====================

int db_insert_event_atomic(
    // upload_records 字段
    const std::string& plate_number, const std::string& tb_num, int station_type,
    const std::string& lo_photo_path, const std::string& hi_photo_path,
    int lo_upload_status, int hi_upload_status, int feishu_status,
    time_t capture_time,
    // weight_records 字段
    double weight_kg, int weight_upload_status, const std::string& weight_response_msg)
{
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return -1;

    // 开启事务
    char* errmsg = nullptr;
    int rc = sqlite3_exec(g_db, "BEGIN TRANSACTION;", nullptr, nullptr, &errmsg);
    if (rc != SQLITE_OK) {
        std::cerr << "[v43-DB] BEGIN 失败: " << (errmsg ? errmsg : "unknown") << std::endl;
        if (errmsg) sqlite3_free(errmsg);
        return -1;
    }

    // 写入 upload_records
    const char* sql1 = R"(
        INSERT INTO upload_records
        (plate_number, tb_num, station_type, lo_photo_path, hi_photo_path,
         lo_upload_status, hi_upload_status, feishu_status, capture_time)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
    )";
    sqlite3_stmt* stmt1 = nullptr;
    if (sqlite3_prepare_v2(g_db, sql1, -1, &stmt1, nullptr) != SQLITE_OK) {
        std::cerr << "[v43-DB] prepare upload_records 失败: " << sqlite3_errmsg(g_db) << std::endl;
        sqlite3_exec(g_db, "ROLLBACK;", nullptr, nullptr, nullptr);
        return -1;
    }
    sqlite3_bind_text(stmt1, 1, plate_number.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt1, 2, tb_num.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt1, 3, station_type);
    sqlite3_bind_text(stmt1, 4, lo_photo_path.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt1, 5, hi_photo_path.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt1, 6, lo_upload_status);
    sqlite3_bind_int(stmt1, 7, hi_upload_status);
    sqlite3_bind_int(stmt1, 8, feishu_status);
    sqlite3_bind_int64(stmt1, 9, (long long)capture_time);

    rc = sqlite3_step(stmt1);
    sqlite3_finalize(stmt1);
    if (rc != SQLITE_DONE) {
        std::cerr << "[v43-DB] INSERT upload_records 失败: " << sqlite3_errmsg(g_db) << std::endl;
        sqlite3_exec(g_db, "ROLLBACK;", nullptr, nullptr, nullptr);
        return -1;
    }

    // 写入 weight_records
    const char* sql2 = R"(
        INSERT INTO weight_records
        (plate_number, tb_num, station_type, weight_kg, upload_status, response_msg)
        VALUES (?, ?, ?, ?, ?, ?);
    )";
    sqlite3_stmt* stmt2 = nullptr;
    if (sqlite3_prepare_v2(g_db, sql2, -1, &stmt2, nullptr) != SQLITE_OK) {
        std::cerr << "[v43-DB] prepare weight_records 失败: " << sqlite3_errmsg(g_db) << std::endl;
        sqlite3_exec(g_db, "ROLLBACK;", nullptr, nullptr, nullptr);
        return -1;
    }
    sqlite3_bind_text(stmt2, 1, plate_number.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt2, 2, tb_num.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt2, 3, station_type);
    sqlite3_bind_double(stmt2, 4, weight_kg);
    sqlite3_bind_int(stmt2, 5, weight_upload_status);
    sqlite3_bind_text(stmt2, 6, weight_response_msg.c_str(), -1, SQLITE_TRANSIENT);

    rc = sqlite3_step(stmt2);
    sqlite3_finalize(stmt2);
    if (rc != SQLITE_DONE) {
        std::cerr << "[v43-DB] INSERT weight_records 失败: " << sqlite3_errmsg(g_db) << std::endl;
        sqlite3_exec(g_db, "ROLLBACK;", nullptr, nullptr, nullptr);
        return -1;
    }

    // 提交事务
    rc = sqlite3_exec(g_db, "COMMIT;", nullptr, nullptr, &errmsg);
    if (rc != SQLITE_OK) {
        std::cerr << "[v43-DB] COMMIT 失败: " << (errmsg ? errmsg : "unknown") << std::endl;
        if (errmsg) sqlite3_free(errmsg);
        sqlite3_exec(g_db, "ROLLBACK;", nullptr, nullptr, nullptr);
        return -1;
    }

    std::cout << "[v43-DB] 原子写入成功: " << plate_number << " 联单:" << tb_num
              << " 重量:" << weight_kg << "kg" << std::endl;
    return 0;
}

// ==================== fix24 v43: 按 tb_num 联合查询 ====================

std::vector<EventRecord> db_get_event_records_by_tbnum(const std::string& tb_num) {
    std::vector<EventRecord> results;
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return results;

    const char* sql = R"(
        SELECT
            ur.id AS ur_id,
            ur.plate_number,
            ur.tb_num,
            ur.station_type,
            ur.create_time,
            ur.capture_time,
            ur.lo_photo_path,
            ur.hi_photo_path,
            ur.lo_upload_status,
            ur.hi_upload_status,
            ur.feishu_status,
            wr.id AS wr_id,
            wr.weight_kg,
            wr.upload_status AS weight_upload_status,
            wr.response_msg AS weight_response_msg
        FROM upload_records ur
        LEFT JOIN weight_records wr ON wr.id = (
            SELECT MAX(w2.id) FROM weight_records w2
            WHERE w2.tb_num = ur.tb_num AND w2.plate_number = ur.plate_number
              AND w2.station_type = ur.station_type
        )
        WHERE ur.tb_num LIKE ?
        ORDER BY ur.create_time DESC;
    )";

    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(g_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[v43-DB] prepare 联合查询失败: " << sqlite3_errmsg(g_db) << std::endl;
        return results;
    }

    std::string pattern = "%" + tb_num + "%";
    sqlite3_bind_text(stmt, 1, pattern.c_str(), -1, SQLITE_TRANSIENT);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        EventRecord rec;
        rec.ur_id = sqlite3_column_int(stmt, 0);
        const char* s;
        s = (const char*)sqlite3_column_text(stmt, 1); if (s) rec.plate_number = s;
        s = (const char*)sqlite3_column_text(stmt, 2); if (s) rec.tb_num = s;
        rec.station_type = sqlite3_column_int(stmt, 3);
        s = (const char*)sqlite3_column_text(stmt, 4); if (s) rec.create_time = s;
        // capture_time 存储为 Unix 时间戳 (INTEGER)，需转换为格式化字符串
        if (sqlite3_column_type(stmt, 5) != SQLITE_NULL) {
            int64_t cap_ts = sqlite3_column_int64(stmt, 5);
            struct tm tm_buf;
            time_t t = (time_t)cap_ts;
            localtime_r(&t, &tm_buf);
            char buf[32];
            strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S", &tm_buf);
            rec.capture_time = buf;
        }
        s = (const char*)sqlite3_column_text(stmt, 6); if (s) rec.lo_photo_path = s;
        s = (const char*)sqlite3_column_text(stmt, 7); if (s) rec.hi_photo_path = s;
        rec.lo_upload_status = sqlite3_column_int(stmt, 8);
        rec.hi_upload_status = sqlite3_column_int(stmt, 9);
        rec.feishu_status = sqlite3_column_int(stmt, 10);
        rec.wr_id = sqlite3_column_int(stmt, 11);
        rec.weight_kg = sqlite3_column_double(stmt, 12);
        rec.weight_upload_status = sqlite3_column_int(stmt, 13);
        s = (const char*)sqlite3_column_text(stmt, 14); if (s) rec.weight_response_msg = s;
        results.push_back(rec);
    }
    sqlite3_finalize(stmt);
    return results;
}

// ==================== fix24 v43 P2: 分页联合查询 + 同步统计 ====================

std::vector<EventRecord> db_get_event_records_joined(int page, int page_size, const SyncQuery& q) {
    std::vector<EventRecord> results;
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return results;

    std::string sql = R"(
        SELECT
            ur.id AS ur_id,
            ur.plate_number,
            ur.tb_num,
            ur.station_type,
            ur.create_time,
            ur.capture_time,
            ur.lo_photo_path,
            ur.hi_photo_path,
            ur.lo_upload_status,
            ur.hi_upload_status,
            ur.feishu_status,
            wr.id AS wr_id,
            wr.weight_kg,
            wr.upload_status AS weight_upload_status,
            wr.response_msg AS weight_response_msg
        FROM upload_records ur
        LEFT JOIN weight_records wr ON wr.id = (
            SELECT MAX(w2.id) FROM weight_records w2
            WHERE w2.tb_num = ur.tb_num AND w2.plate_number = ur.plate_number
              AND w2.station_type = ur.station_type
        )
        WHERE 1=1
    )";

    std::vector<std::string> binds;

    if (!q.plate_number.empty()) {
        sql += " AND ur.plate_number LIKE ?";
        binds.push_back("%" + q.plate_number + "%");
    }
    if (!q.tb_num.empty()) {
        sql += " AND ur.tb_num LIKE ?";
        binds.push_back("%" + q.tb_num + "%");
    }
    if (!q.date_from.empty()) {
        sql += " AND ur.create_time >= ?";
        binds.push_back(q.date_from + " 00:00:00");
    }
    if (!q.date_to.empty()) {
        sql += " AND ur.create_time <= ?";
        binds.push_back(q.date_to + " 23:59:59");
    }
    if (q.station_type > 0) {
        sql += " AND ur.station_type = ?";
        binds.push_back(std::to_string(q.station_type));
    }
    if (q.sync_status == 1) {
        sql += " AND wr.id IS NOT NULL";
    } else if (q.sync_status == 2) {
        sql += " AND wr.id IS NULL";
    } else if (q.sync_status == 3) {
        sql += " AND wr.id IS NOT NULL AND wr.upload_status = 2";
    }

    sql += " ORDER BY ur.create_time DESC";

    // 分页
    int offset = (page - 1) * page_size;
    sql += " LIMIT ? OFFSET ?";

    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[v43-P2] prepare 联合查询失败: " << sqlite3_errmsg(g_db) << std::endl;
        return results;
    }

    int idx = 1;
    for (const auto& b : binds) {
        sqlite3_bind_text(stmt, idx++, b.c_str(), -1, SQLITE_TRANSIENT);
    }
    sqlite3_bind_int(stmt, idx++, page_size);
    sqlite3_bind_int(stmt, idx++, offset);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        EventRecord rec;
        rec.ur_id = sqlite3_column_int(stmt, 0);
        const char* s;
        s = (const char*)sqlite3_column_text(stmt, 1); if (s) rec.plate_number = s;
        s = (const char*)sqlite3_column_text(stmt, 2); if (s) rec.tb_num = s;
        rec.station_type = sqlite3_column_int(stmt, 3);
        s = (const char*)sqlite3_column_text(stmt, 4); if (s) rec.create_time = s;
        // capture_time 存储为 Unix 时间戳 (INTEGER)，需转换为格式化字符串
        if (sqlite3_column_type(stmt, 5) != SQLITE_NULL) {
            int64_t cap_ts = sqlite3_column_int64(stmt, 5);
            struct tm tm_buf;
            time_t t = (time_t)cap_ts;
            localtime_r(&t, &tm_buf);
            char buf[32];
            strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S", &tm_buf);
            rec.capture_time = buf;
        }
        s = (const char*)sqlite3_column_text(stmt, 6); if (s) rec.lo_photo_path = s;
        s = (const char*)sqlite3_column_text(stmt, 7); if (s) rec.hi_photo_path = s;
        rec.lo_upload_status = sqlite3_column_int(stmt, 8);
        rec.hi_upload_status = sqlite3_column_int(stmt, 9);
        rec.feishu_status = sqlite3_column_int(stmt, 10);
        rec.wr_id = sqlite3_column_int(stmt, 11);
        rec.weight_kg = sqlite3_column_double(stmt, 12);
        rec.weight_upload_status = sqlite3_column_int(stmt, 13);
        s = (const char*)sqlite3_column_text(stmt, 14); if (s) rec.weight_response_msg = s;
        results.push_back(rec);
    }
    sqlite3_finalize(stmt);
    return results;
}

int db_count_event_records_joined(const SyncQuery& q) {
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return 0;

    std::string sql = R"(
        SELECT COUNT(*)
        FROM upload_records ur
        LEFT JOIN weight_records wr ON wr.id = (
            SELECT MAX(w2.id) FROM weight_records w2
            WHERE w2.tb_num = ur.tb_num AND w2.plate_number = ur.plate_number
              AND w2.station_type = ur.station_type
        )
        WHERE 1=1
    )";

    std::vector<std::string> binds;

    if (!q.plate_number.empty()) {
        sql += " AND ur.plate_number LIKE ?";
        binds.push_back("%" + q.plate_number + "%");
    }
    if (!q.tb_num.empty()) {
        sql += " AND ur.tb_num LIKE ?";
        binds.push_back("%" + q.tb_num + "%");
    }
    if (!q.date_from.empty()) {
        sql += " AND ur.create_time >= ?";
        binds.push_back(q.date_from + " 00:00:00");
    }
    if (!q.date_to.empty()) {
        sql += " AND ur.create_time <= ?";
        binds.push_back(q.date_to + " 23:59:59");
    }
    if (q.station_type > 0) {
        sql += " AND ur.station_type = ?";
        binds.push_back(std::to_string(q.station_type));
    }
    if (q.sync_status == 1) {
        sql += " AND wr.id IS NOT NULL";
    } else if (q.sync_status == 2) {
        sql += " AND wr.id IS NULL";
    } else if (q.sync_status == 3) {
        sql += " AND wr.id IS NOT NULL AND wr.upload_status = 2";
    }

    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(g_db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "[v43-P2] prepare count 联合查询失败: " << sqlite3_errmsg(g_db) << std::endl;
        return 0;
    }

    int idx = 1;
    for (const auto& b : binds) {
        sqlite3_bind_text(stmt, idx++, b.c_str(), -1, SQLITE_TRANSIENT);
    }

    int count = 0;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        count = sqlite3_column_int(stmt, 0);
    }
    sqlite3_finalize(stmt);
    return count;
}

SyncStats db_get_sync_stats() {
    SyncStats stats;
    std::lock_guard<std::mutex> lock(g_db_mtx);
    if (!g_db) return stats;

    // 总上传记录数
    const char* sql1 = "SELECT COUNT(*) FROM upload_records;";
    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(g_db, sql1, -1, &stmt, nullptr) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) stats.total_uploads = sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);
    }

    // 今日上传记录数
    const char* sql2 = "SELECT COUNT(*) FROM upload_records WHERE date(create_time) = date('now', 'localtime');";
    if (sqlite3_prepare_v2(g_db, sql2, -1, &stmt, nullptr) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) stats.today_uploads = sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);
    }

    // 有重量匹配的记录数（两表 tb_num + station_type 关联成功）
    // fix24 v43 Bug#16: 必须带 station_type，同一联单进出站共用 tb_num，
    // 缺条件会导致进站行错误匹配到出站重量（同联单最新记录）
    const char* sql3 = R"(
        SELECT COUNT(DISTINCT ur.id)
        FROM upload_records ur
        INNER JOIN weight_records wr ON ur.tb_num = wr.tb_num
            AND ur.plate_number = wr.plate_number
            AND ur.station_type = wr.station_type;
    )";
    if (sqlite3_prepare_v2(g_db, sql3, -1, &stmt, nullptr) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) stats.matched_weight = sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);
    }

    // 今日匹配数（Bug#16: 同样补 station_type）
    const char* sql4 = R"(
        SELECT COUNT(DISTINCT ur.id)
        FROM upload_records ur
        INNER JOIN weight_records wr ON ur.tb_num = wr.tb_num
            AND ur.plate_number = wr.plate_number
            AND ur.station_type = wr.station_type
        WHERE date(ur.create_time) = date('now', 'localtime');
    )";
    if (sqlite3_prepare_v2(g_db, sql4, -1, &stmt, nullptr) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) stats.today_matched = sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);
    }

    // 重量上传状态统计
    const char* sql5 = R"(
        SELECT
            SUM(CASE WHEN upload_status = 1 THEN 1 ELSE 0 END) AS ok_count,
            SUM(CASE WHEN upload_status = 2 THEN 1 ELSE 0 END) AS fail_count,
            SUM(CASE WHEN upload_status = 0 THEN 1 ELSE 0 END) AS pending_count
        FROM weight_records;
    )";
    if (sqlite3_prepare_v2(g_db, sql5, -1, &stmt, nullptr) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            stats.weight_upload_ok = sqlite3_column_int(stmt, 0);
            stats.weight_upload_fail = sqlite3_column_int(stmt, 1);
            stats.weight_pending = sqlite3_column_int(stmt, 2);
        }
        sqlite3_finalize(stmt);
    }

    // 计算匹配率
    if (stats.total_uploads > 0) {
        stats.match_rate = (double)stats.matched_weight / stats.total_uploads * 100.0;
    }
    if (stats.today_uploads > 0) {
        stats.today_match_rate = (double)stats.today_matched / stats.today_uploads * 100.0;
    }

    return stats;
}