/*
 * @Author: qianziyang qianzy@bmser.com
 * @Date: 2023-12-20 10:07:40
 * @LastEditors: qianziyang qianzy@bmser.com
 * @LastEditTime: 2024-01-25 17:32:10
 * @FilePath: /rk3568_sdk_1018/bmser_app/bmser_upgrade/src/tools/db3_common/db3_common.c
 * @Description: db3处理
 */

#include "dtc_db3_common.h"
#include "bmser_dev.h"
#include "bmser_log.h"
#include "sqlite_api.h"

sqlite3 *g_dtcDb = NULL;
sqlite3_stmt *g_dtcInsertStmt = NULL;
sqlite3_stmt *g_dtcIndexIdStmt = NULL;
sqlite3_stmt *g_dtcMaxIdStmt = NULL;
sqlite3_stmt *g_dtcSameStmt = NULL;

#if 0
typedef struct 
{
    char values[DTC_ONCE_BATCH_MAX_SIZE][DTC_EXPORT_VALUES_MAX_LEN];
    int count;
    int batchSize;
} BatchInsertInfo;
BatchInsertInfo g_batchInfo;

/**
 * @brief 批量插入接口初始化
 * @param[in] batchSize 批量插入数量
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 */
void DTC_Export_DB3_batchInsertInit(int batchSize)
{
    memset(g_batchInfo.values, 0, sizeof(g_batchInfo.values));
    g_batchInfo.count = 0;
    g_batchInfo.batchSize = batchSize;
}

// 将缓存的values插入DB表
static int insertBatch(sqlite3 *db, const char *tableName, const char *columns)
{
    if (db == NULL || tableName == NULL || columns == NULL) {
        return -1;
    }

    if (g_batchInfo.count == 0) {
        return 0;
    }

    sqlite3_stmt *stmt;
    const char *insertSQL = "REPLACE INTO %s %s VALUES %s;";
    char sql[DTC_SQL_MAX_LEN];

    if (sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, 0) != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to begin transaction: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    for (int i = 0; i < g_batchInfo.count; i++)
    {
        snprintf(sql, sizeof(sql), insertSQL, tableName, columns, g_batchInfo.values[i]);

        if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) != SQLITE_OK) {
            dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
            return -1;
        }

        if (sqlite3_step(stmt) != SQLITE_DONE) {
            dy_syslog(LOG_ERR, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
            sqlite3_finalize(stmt);
            return -1;
        }

        sqlite3_finalize(stmt);
    }

    if (sqlite3_exec(db, "COMMIT", 0, 0, 0) != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to commit transaction: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    return 0;
}

/**
 * @brief 将需要插入的values存至批量插入缓冲区
 * @param[in] tableName 目标插入表名
 * @param[in] values    插入值
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 */
int DTC_Export_DB3_addValuesToBatch(char *tableName, char *values)
{
    if (g_dtcDb != NULL && values != NULL) 
    {
        snprintf(g_batchInfo.values[g_batchInfo.count], sizeof(g_batchInfo.values[0]), "%s", values);
        g_batchInfo.count++;

        if (g_batchInfo.count >= g_batchInfo.batchSize)
        {
            if (insertBatch(g_dtcDb, tableName, DTC_RECORD_TABLE_COLUMNS) == 0) {
                g_batchInfo.count = 0;  // 重置计数
                memset(g_batchInfo.values, 0, sizeof(g_batchInfo.values));  // 清空二维数组
                return 0;
            } else {
                // 插入错误
                return -1;
            }
        }
        return 0;
    }
    return -1;
}

/**
 * @brief 将缓冲区中不满单次批量插入数量的剩余values，强制插入DB
 * @param[in] tableName 目标插入表名
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 */
int DTC_Export_DB3_flushValuesToBatch(char *tableName)
{
    if(g_batchInfo.count > 0)
    {
        if (insertBatch(g_dtcDb, tableName, DTC_RECORD_TABLE_COLUMNS) == 0) {
            g_batchInfo.count = 0;  // 重置计数
            memset(g_batchInfo.values, 0, sizeof(g_batchInfo.values));  // 清空二维数组
            return 0;
        } else {
            return -1;
        }
    }
}


// sql插入数据接口
static int DTC_Export_sqlInsert(sqlite3 *db, const char *tableName, const char *columns, const char *values)
{
    sqlite3_stmt *stmt;

    //允许历史数据更新
    const char *insertSQL = "REPLACE INTO %s %s VALUES %s;";
    char sql[DTC_SQL_MAX_LEN];
    snprintf(sql, sizeof(sql), insertSQL, tableName, columns, values);

    if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    if (sqlite3_step(stmt) != SQLITE_DONE) {
        dy_syslog(LOG_ERR, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return -1;
    }
    sqlite3_finalize(stmt);
    return 0;
}


/**
 * @brief 打开数据库
 * @param[in] dbPath 数据库路径
 * @return 0: 成功  !0: 错误
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 */
int DTC_Export_DB3_open_db(const char* dbPath)
{
    if (g_dtcDb == NULL && sqlite3_open(dbPath, &g_dtcDb) != SQLITE_OK) {
        dy_syslog(LOG_ERR, "open database error:%s\r\n", sqlite3_errmsg(g_dtcDb));
        return -1;
    }
    return 0;
}

/**
 * @brief 关闭数据库
 * @return 0: 成功; !0: 错误
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 */
int DTC_Export_DB3_close_db()
{
    if (g_dtcDb != NULL) {
        sqlite3_close(g_dtcDb);
        return 0;
    }
    return -1;
}
#endif

/**
 * @brief  将值插入指定数据表中对应的列，支持批量插入
 * @param[in] DTC_Export_Table_Stru* info  数据表值
 * @return 0: 成功; !0: 错误
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 * 2024-05-29   QZY      重构, 使用bind方式       \n
 */
int DTC_Export_DB3_dataInsert(DTC_Export_Table_Stru* info)
{
    int ret = 0;
    if (info == NULL) {
        dy_syslog(LOG_ERR, "info pointer is NULL.\n");
        return -1;
    }

    sqlite3_stmt* stmt = g_dtcInsertStmt;
    if (stmt == NULL) {
        dy_syslog(LOG_ERR, "SQL statement is not initialized.\n");
        return -1;
    }

    sqlite3_bind_int(stmt, 1, (sqlite3_int64)info->id);
    sqlite3_bind_int(stmt, 2, info->bankID);
    sqlite3_bind_int(stmt, 3, info->rackID);
    sqlite3_bind_int(stmt, 4, info->recordType);
    sqlite3_bind_int(stmt, 5, info->DBIndexID);
    sqlite3_bind_int64(stmt, 6, (sqlite3_int64)info->dtcIndexID);
    sqlite3_bind_int64(stmt, 7, (sqlite3_int64)info->unixTime);
    sqlite3_bind_text(stmt, 8, info->sysTime, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 9, info->softwareVersion, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 10, info->DID);
    sqlite3_bind_text(stmt, 11, info->dtcName, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 12, info->eventId);
    sqlite3_bind_text(stmt, 13, info->eventName_zh_CN, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 14, info->eventName_en_US, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 15, info->alert1);
    sqlite3_bind_int(stmt, 16, info->alarm1);
    sqlite3_bind_int(stmt, 17, info->stop1);
    sqlite3_bind_int(stmt, 18, info->alert2);
    sqlite3_bind_int(stmt, 19, info->alarm2);
    sqlite3_bind_int(stmt, 20, info->stop2);
    sqlite3_bind_int(stmt, 21, info->resetCount);
    sqlite3_bind_text(stmt, 22, info->uptime, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 23, info->extSnapType);
    sqlite3_bind_int(stmt, 24, info->clusterId);
    sqlite3_bind_int(stmt, 25, info->extRecordsNum);
    sqlite3_bind_text(stmt, 26, info->issueDetails_zh_CN, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 27, info->issueDetails_en_US, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 28, info->alarm_level);
    sqlite3_bind_text(stmt, 29, info->dtcName_en, -1, SQLITE_TRANSIENT);

    if (sqlite3_step(stmt) != SQLITE_DONE) {
        ret = -1;
    }
    sqlite3_reset(stmt);

    return ret;
}

/**
 * @brief 获取指定设备+记录类型 条目id最大时的indexID
 * @param[in]  bankid         堆ID
 * @param[in]  rackid         簇ID
 * @param[in]  record_type  记录类型(告警，事件)
 * @param[out] indexID       最大值
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 * 2024-05-29   QZY      使用bind 方式           \n
 */
int DTC_Export_DB3_getIndexID(uint16_t bankid, uint16_t rackid, int record_type, int *indexID)
{
    int ret = 0;
    if (indexID == NULL) {
        dy_syslog(LOG_ERR, "indexID is NULL.\n");
        return -1;
    }

    sqlite3_stmt* stmt = g_dtcIndexIdStmt;
    if (stmt == NULL) {
        dy_syslog(LOG_ERR, "SQL statement is not initialized.\n");
        return -1;
    }

    sqlite3_bind_int(stmt, 1, bankid);
    sqlite3_bind_int(stmt, 2, rackid);
    sqlite3_bind_int(stmt, 3, record_type);

    ret = sqlite3_step(stmt);
    if (ret == SQLITE_ROW) {
        // 读取结果
        *indexID = sqlite3_column_int(stmt, 0);
    } else {
        // 没有找到，indexID默认取0
        // dy_syslog(LOG_ERR, "get indexID err: %s\n", sqlite3_errmsg(g_dtcDb));
        *indexID = 0;
    }
    sqlite3_reset(stmt);

    return ret;
}

/**
 * @brief 获取最大条目id
 * @param[out] maxID       条目id最大值
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2023-12-27   QZY      创建初始版本            \n
 * 2024-05-29   QZY      使用bind 方式           \n
 */
int DTC_Export_DB3_getMaxID(int *maxID)
{
    int ret = 0;
    if (maxID == NULL) {
        dy_syslog(LOG_ERR, "maxID is NULL.\n");
        return -1;
    }

    sqlite3_stmt* stmt = g_dtcMaxIdStmt;
    if (stmt == NULL) {
        dy_syslog(LOG_ERR, "SQL statement is not initialized.\n");
        return -1;
    }

    ret = sqlite3_step(stmt);
    if (ret == SQLITE_ROW) {
        // 读取结果
        *maxID = sqlite3_column_int(stmt, 0);
    } else {
        // 没有找到，maxID默认取0
        dy_syslog(LOG_ERR, "get maxID err: %s\n", sqlite3_errmsg(g_dtcDb));
        *maxID = 0;
    }
    sqlite3_reset(stmt);

    return ret;
}

/**
 * @brief    查找设备信息，类型，索引，时间戳都相同的记录条目
 * @param[out] maxID       条目id最大值
 * @par 修改日志:
 * Date       Author    Description           \n
 * 2024-10-12   QZY      创建初始版本            \n
 */
int DTC_Export_DB3_getSameRecord(uint16_t bankid, uint16_t rackid, int record_type, \
                                int DBIndexID, uint32_t unixTime, int *count)
{
    int ret = 0;
    if (count == NULL) {
        dy_syslog(LOG_ERR, "count is NULL.\n");
        return -1;
    }

    sqlite3_stmt* stmt = g_dtcSameStmt;
    if (stmt == NULL) {
        dy_syslog(LOG_ERR, "SQL statement is not initialized.\n");
        return -1;
    }

    sqlite3_bind_int(stmt, 1, bankid);
    sqlite3_bind_int(stmt, 2, rackid);
    sqlite3_bind_int(stmt, 3, record_type);
    sqlite3_bind_int(stmt, 4, DBIndexID);
    sqlite3_bind_int64(stmt, 5, (sqlite3_int64)unixTime);

    ret = sqlite3_step(stmt);
    if (ret == SQLITE_ROW) {
        // 读取结果
        *count = sqlite3_column_int(stmt, 0);
    } else {
        // 没有找到，count默认取0
        dy_syslog(LOG_ERR, "get maxID err: %s\n", sqlite3_errmsg(g_dtcDb));
        *count = 0;
    }
    sqlite3_reset(stmt);

    return ret;
}


int DTC_Export_DB3_init()
{
    int ret = 0;
    sqlResult_t result = {0};
    sqlite3_stmt *stmt = NULL;
    int column_count = 0;

    ret = sqlite3_open_v2(DTC_RECORD_DB_FILE_PATH, &g_dtcDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Cannot open or create database:%s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    // 检查表是否存在
    ret = sqlite3_prepare_v2(g_dtcDb, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", -1, &stmt, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    sqlite3_bind_text(stmt, 1, DTC_RECORD_TABLE_NAME, -1, SQLITE_STATIC);
    ret = sqlite3_step(stmt);
    if (ret != SQLITE_ROW) {
        sqlite3_finalize(stmt);
        stmt = NULL;
        // 表不存在，直接创建新表
        goto create_table;
    }

    int table_exists = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    stmt = NULL;

    if (table_exists) {
        // 获取表的列数
        ret = sqlite3_prepare_v2(g_dtcDb, "PRAGMA table_info(" DTC_RECORD_TABLE_NAME ")", -1, &stmt, NULL);
        if (ret != SQLITE_OK) {
            dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
            goto err;
        }

        while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
            column_count++;
        }

        sqlite3_finalize(stmt);
        stmt = NULL;

        // 列数与最新列数不符，备份数据库
        if (column_count != CURRENT_DTC_RECORD_TABLE_COLUMNS) {
            sqlite3_close(g_dtcDb);
            if (rename(DTC_RECORD_DB_FILE_PATH, BACKUP_DB_FILE_PATH) != 0) {
                dy_syslog(LOG_ERR, "Failed to rename database file for backup\n");
                goto err;
            }
            dy_syslog(LOG_DEBUG, "Backup database successfully\n");
            exit(EXIT_SUCCESS);
        } else {
            // 非重新创建DTC记录表的情况，也创建索引提升查询效率
            ret = sqlite3_exec(g_dtcDb, CREATE_IDX_DTC_RECORD_OPTIMIZATION, 0, 0, NULL);
            if (ret != SQLITE_OK) {
                dy_syslog(LOG_ERR, "Failed to create index: %s.", sqlite3_errmsg(g_dtcDb));
                goto err;
            }
            goto prepare_stmt;
        }
    }

create_table:
    // 创建DTC记录表
    ret = sqlite3_exec(g_dtcDb, CREATE_DTC_RECORD_TABLE_SQL, 0, 0, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to create table: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    // 创建索引提升查询效率
    ret = sqlite3_exec(g_dtcDb, CREATE_IDX_DTC_RECORD_OPTIMIZATION, 0, 0, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to create index: %s.", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

prepare_stmt:
    // 准备插入语句
    ret = sqlite3_prepare_v2(g_dtcDb, REPLACE_DTC_RECORD_SQL, -1, &g_dtcInsertStmt, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    ret = sqlite3_prepare_v2(g_dtcDb, SELECT_DTC_INDEX_ID_SQL, -1, &g_dtcIndexIdStmt, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    ret = sqlite3_prepare_v2(g_dtcDb, SELECT_DTC_MAX_ID_SQL, -1, &g_dtcMaxIdStmt, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    ret = sqlite3_prepare_v2(g_dtcDb, GET_DTC_SAME_REC_SQL, -1, &g_dtcSameStmt, NULL);
    if (ret != SQLITE_OK) {
        dy_syslog(LOG_ERR, "Failed to prepare statement: %s\n", sqlite3_errmsg(g_dtcDb));
        goto err;
    }

    return 0;

err:
    if (g_dtcInsertStmt) {
        sqlite3_finalize(g_dtcInsertStmt);
        g_dtcInsertStmt = NULL;
    }
    if (g_dtcIndexIdStmt) {
        sqlite3_finalize(g_dtcIndexIdStmt);
        g_dtcIndexIdStmt = NULL;
    }
    if (g_dtcMaxIdStmt) {
        sqlite3_finalize(g_dtcMaxIdStmt);
        g_dtcMaxIdStmt = NULL;
    }
    if (g_dtcSameStmt) {
        sqlite3_finalize(g_dtcSameStmt);
        g_dtcSameStmt = NULL;
    }
    if (g_dtcDb) {
        sqlite3_close(g_dtcDb);
        g_dtcDb = NULL;
    }
    return -1;
}

/**
 * @brief       DTC db3反初始化
 * @return      void
 * @retval      none
 * @par         修改日志:
 * Date                Author     Description           \n
 * 2024-05-29           QZY        创建初始版本          \n
*/
void DTC_Export_DB3_deinit()
{
    if(g_dtcInsertStmt) {
        sqlite3_finalize(g_dtcInsertStmt);
        g_dtcInsertStmt = NULL;
    }
    if(g_dtcIndexIdStmt) {
        sqlite3_finalize(g_dtcIndexIdStmt);
        g_dtcIndexIdStmt = NULL;
    }
    if(g_dtcMaxIdStmt) {
        sqlite3_finalize(g_dtcMaxIdStmt);
        g_dtcMaxIdStmt = NULL;
    }
    if(g_dtcSameStmt) {
        sqlite3_finalize(g_dtcSameStmt);
        g_dtcSameStmt = NULL;
    }
    if(g_dtcDb) {
        sqlite3_close(g_dtcDb);
        g_dtcDb = NULL;
    }
}

static int dtc_result_call_back(void *data, int argc, char **argv, char **azColName)
{
    if (data == NULL || argv[0] == NULL) {
        return -1;  // 返回一个非零值表示错误
    }

    dtc_call_back_data *cb_data = (dtc_call_back_data*) data;
	cb_data->events = realloc(cb_data->events, (cb_data->rows + 1) * sizeof(DTC_Export_Table_Stru));
	if (cb_data->events == NULL) {
		dy_syslog(LOG_ERR, "realloc fail.");
		return -1;
	}

    //解析查询结果，将结果保存到结构体中去
    if (argv[0]) cb_data->events[cb_data->rows].bankID = atoi(argv[0]);
    if (argv[1]) cb_data->events[cb_data->rows].rackID = atoi(argv[1]);
    if (argv[2]) cb_data->events[cb_data->rows].alarm_level = atoi(argv[2]);
    if (argv[3]) cb_data->events[cb_data->rows].unixTime = strtol(argv[3], NULL, 10);
    if (argv[4]) snprintf(cb_data->events[cb_data->rows].dtcName, STR_VAL_MAX_LEN, "%s", argv[4]);
    if (argv[5]) snprintf(cb_data->events[cb_data->rows].dtcName_en, STR_VAL_MAX_LEN, "%s", argv[5]);
    if (argv[6]) snprintf(cb_data->events[cb_data->rows].issueDetails_zh_CN, EXTMSG_MAX_LEN, "%s", argv[6]);
    if (argv[7]) snprintf(cb_data->events[cb_data->rows].issueDetails_en_US, EXTMSG_MAX_LEN, "%s", argv[7]);
    if (argv[8]) snprintf(cb_data->events[cb_data->rows].eventName_zh_CN, STR_VAL_MAX_LEN, "%s", argv[8]);
    if (argv[9]) snprintf(cb_data->events[cb_data->rows].eventName_en_US, STR_VAL_MAX_LEN, "%s", argv[9]);
    cb_data->rows++;


    return 0;
}

/**
 * @brief       DTC db3数据库查询
 * @param[in]   char *sql 数据库查询语句
 * @param[in]   uint16_t sql_siz 数据查询语句size
 * @param[out]  dtc_call_back_data *result 查询结果
 * @return      int32_t
 * @retval      0:正常  !0:错误
 * @par         修改日志:
 * Date                Author     Description           \n
 * 2024-07-30           ZHY        创建初始版本          \n
*/
int32_t DTC_Export_DB3_query(char *sql, uint16_t sql_size, dtc_call_back_data *result)
{
    if (!sql || !result || !g_dtcDb) {
        return -1;
    }
    
    int32_t rc = 0;

    rc = sqlite3_exec(g_dtcDb, sql, dtc_result_call_back, result, NULL);
    if (rc != SQLITE_OK) {
        dy_syslog(LOG_ERR, "sqlite3_exec fail, err = %s.", sqlite3_errmsg(g_dtcDb));
    }
    return rc;
}

/**
 * @brief       根据查询条件返回dtc总条数
 * @param[out]  uint16_t *counts 总条数
 * @param[in]   char *sql sql语句
 * @return      int32_t
 * @retval      0:正常  !0:错误
 * @par         修改日志:
 * Date                Author     Description           \n
 * 2024-08-07           ZHY        创建初始版本          \n
*/
int32_t DTC_Export_DB3_get_sql_counts(uint16_t *counts, char *sql)
{
    if (!counts || !g_dtcDb || !sql) {
        return -1;
    }
    
    int32_t ret = 0;
    sqlite3_stmt *stmt = NULL;

    ret = sqlite3_prepare_v2(g_dtcDb, sql, -1, &stmt, NULL);
    if (SQLITE_OK != ret) {
        dy_syslog(LOG_ERR, "sqlite3_prepare_v2: %s.", sqlite3_errmsg(g_dtcDb));
        return ret; 
    }

    if (sqlite3_step(stmt) != SQLITE_ROW) {
        ret = -1;
        dy_syslog(LOG_ERR, "sqlite3_step: %s.", sqlite3_errmsg(g_dtcDb));
        goto end;
    }

    *counts = sqlite3_column_int(stmt, 0);

end:
    sqlite3_reset(stmt);
    sqlite3_finalize(stmt);
    return ret;
}

static int rows_callback(void *data, int argc, char **argv, char **azColName)
{
    int *count = (int*)data;
    *count = atoi(argv[0]);
    return 0;
}

static int32_t clean_records(int type, int threshold, int retain_count)
{
    char count_sql[256] = {0};
    char delete_sql[512] = {0};
    int rc, record_count = 0;

    // 统计指定类型的记录数量
    snprintf(count_sql, sizeof(count_sql), "SELECT COUNT(*) FROM dtc_record WHERE recordType = %d", type);
    rc = sqlite3_exec(g_dtcDb, count_sql, rows_callback, &record_count, NULL);
    if (rc != SQLITE_OK) {
        dy_syslog(LOG_ERR, "select count err, type %d: %s\n", type, sqlite3_errmsg(g_dtcDb));
        return -1;
    }

    // 如果记录数量超过阈值，则清理旧记录
    if (record_count > threshold) {
        snprintf(delete_sql, sizeof(delete_sql), \
                 "DELETE FROM dtc_record WHERE recordType = %d AND id NOT IN (SELECT id FROM dtc_record WHERE recordType = %d ORDER BY id DESC LIMIT %d)", \
                 type, type, retain_count);
        rc = sqlite3_exec(g_dtcDb, delete_sql, NULL, NULL, NULL);
        if (rc != SQLITE_OK) {
            dy_syslog(LOG_ERR, "delete records err type %d: %s\n", type, sqlite3_errmsg(g_dtcDb));
            return -1;
        }

        rc = sqlite3_exec(g_dtcDb, "VACUUM;", NULL, NULL, NULL);
        if (rc != SQLITE_OK) {
            dy_syslog(LOG_ERR, "vacuum error type %d: %s\n", type, sqlite3_errmsg(g_dtcDb));
            return -1;
        }
    }
    return 0;
}

/**
 * @brief       DB3清理
 * @param[in]   int record_type   记录类型(告警/事件)
 * @return      int32_t
 * @retval      0:正常  !0:错误
 * @par         修改日志:
 * Date                Author     Description           \n
 * 2024-10-12           QZY        创建初始版本          \n
 * 2024-10-29           QZY        事件告警分开处理，保证剩余事件告警记录比例大致为1:9
 * 2025-02-07           QZY        入参调整
*/
int32_t DTC_Export_DB3_space_cleaning(int record_type)
{
    int threshold = 0; 
    int retain_count = 0;

    if (DTC_RECORD_TYPE_ALARM == record_type) {
        threshold = DTC_TABLE_CLEAN_ALARM_ROWS;
        retain_count = DTC_TABLE_CLEAN_ALARM_ROWS / 2;
    } else if (DTC_RECORD_TYPE_EVENT == record_type) {
        threshold = DTC_TABLE_CLEAN_EVENT_ROWS;
        retain_count = DTC_TABLE_CLEAN_EVENT_ROWS / 2;
    } else {
        return -1;
    }
    // 清理告警或事件记录
    if (clean_records(record_type, threshold, retain_count) != 0) {
        dy_syslog(LOG_ERR, "clean dtc err, record_type: %d.\n", record_type);
        return -1;
    }

    return 0;
}