/************************************************************
*版权所有 （C）2020，公司（或个人）名称
*
*文件名称： kk_history_db.c
*内容摘要： 记录传感器类设备和功率计量设备的历史数据模块
*其他说明： 
*当前版本：  
*************************************************************/
 
/*************************************************************
头文件引用 
*************************************************************/
	
#include <stdio.h>
#include "kk_tsl_api.h"
#include "sqlite3.h"
#include "kk_log.h"
#include "kk_hal.h"
#include "kk_history_db.h"
#include "kk_dm_mng.h"

 /*************************************************************
全局变量定义 
*************************************************************/
extern sqlite3 *g_kk_pDb;
#define KK_HISTORY_MAX_COUNT  100
/*************************************************************
函数实现 
*************************************************************/
typedef struct {
	void *mutex;
	sqlite3 *pDb;
} kk_history_ctx_t;

static kk_history_ctx_t s_kk_history_ctx = {NULL};
kk_history_ctx_t *_kk_history_get_ctx(void)
{
	return &s_kk_history_ctx;
}
void _kk_history_lock(void)
{
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	if (ctx->mutex) {
		HAL_MutexLock(ctx->mutex);
	}
}

void _kk_history_unlock(void)
{
	kk_history_ctx_t *ctx = _kk_history_get_ctx();

	if (ctx->mutex) {
		HAL_MutexUnlock(ctx->mutex);
	}
}

/************************************************************
*功能描述： 历史记录数据库初始化
*输入参数： 无
*输出参数： 无
*返 回 值：  0：成功；其他：失败
*其他说明：
*************************************************************/
int kk_history_db_init(void)
{
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	char *pcErr;

	//eUtils_LockLock(&sLock);

	ctx->pDb = g_kk_pDb;

	INFO_PRINT("kk_history db Database opened\n");
	
	/* Create Mutex */
	ctx->mutex = HAL_MutexCreate();
	if (ctx->mutex == NULL) {
		return FAIL_RETURN;
	}
	_kk_history_lock();	
	const char *pSensorHistoryTable = "CREATE TABLE IF NOT EXISTS SensorHistoryInfo( \
		deviceCode varchar(255), \
		identifier varchar(255), \
		value varchar(255), \
		recordtime INTEGER)";

	if (sqlite3_exec(ctx->pDb, pSensorHistoryTable, NULL, NULL, &pcErr) != SQLITE_OK)
	{
		ERROR_PRINT("Error creating table (%s)\n", pcErr);
		sqlite3_free(pcErr);
		//eUtils_LockUnlock(&sLock);
		_kk_history_unlock();
		return FAIL_RETURN;
	}

	const char *pOutletHistoryTable = "CREATE TABLE IF NOT EXISTS OutletHistoryInfo( \
		deviceCode varchar(255), \
		power varchar(32), \
		metering varchar(32), \
		recordtime INTEGER)";

	if (sqlite3_exec(ctx->pDb, pOutletHistoryTable, NULL, NULL, &pcErr) != SQLITE_OK)
	{
		ERROR_PRINT("Error creating table (%s)\n", pcErr);
		sqlite3_free(pcErr);
		//eUtils_LockUnlock(&sLock);
		_kk_history_unlock();
		return FAIL_RETURN;
	}
	_kk_history_unlock();
	return SUCCESS_RETURN;
}

/************************************************************
*功能描述：删除历史数据
*输入参数：itime:记录时间
*输出参数：无
*返 回 值：0:成功；其他：失败
*其他说明：
*************************************************************/
int kk_history_delete_by_recordtime(const char *table,time_t itime)
{
	const char *deleteCmd = "delete from '%s' where recordtime = '%d';";
	char *sqlCmd = NULL;
	int rc = 0;
	char *zErrMsg = 0;
	kk_history_ctx_t *ctx = _kk_history_get_ctx();

	sqlCmd = sqlite3_mprintf(deleteCmd,table,itime);	
	INFO_PRINT("Table delete data sqlCmd:%s\n",sqlCmd);
	rc = sqlite3_exec(ctx->pDb, sqlCmd, NULL, NULL, &zErrMsg);
	if( rc != SQLITE_OK ){
		ERROR_PRINT("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}else{
		//INFO_PRINT("Table delete data successfully\n");
	}
	sqlite3_free(sqlCmd);
	return SUCCESS_RETURN;
}

/*查找数据个数*/
int sqlite_find_count(void)
{
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	char *sql = "select count(recordtime) from  SensorHistoryInfo;";
	sqlite3_stmt *stmt = NULL;
	/*将sql语句转换为sqlite3可识别的语句，返回指针到stmt*/
	int res = sqlite3_prepare_v2(ctx->pDb, sql, strlen(sql), &stmt, NULL);
	if (SQLITE_OK != res || NULL == stmt) {
		goto err1;
	}
	/*执行准备好的sqlite3语句*/
	res = sqlite3_step(stmt);
	if (res != SQLITE_ROW) {
		goto err2;
	}
	int count = sqlite3_column_int(stmt, 0);
	if (count < 0) {
		goto err2;
	}
	INFO_PRINT("count = %d\n", count);
	sqlite3_finalize(stmt);
	return count;
err2:
	sqlite3_finalize(stmt);
err1:
	return -1;
}

cJSON * kk_history_get_sensor_info(void)
{
	char *pDeviceCode = NULL;
	char *pident = NULL;
	char *pValue = NULL;
	int res = 0;
	time_t rtime = 0;
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	char *selectCmd = "select * from SensorHistoryInfo";
	sqlite3_stmt *stmt = NULL;
	sqlite3_prepare_v2(ctx->pDb, selectCmd, strlen(selectCmd), &stmt, NULL);
	cJSON * arrayObj = cJSON_CreateArray();
	while(sqlite3_step(stmt) == SQLITE_ROW){
		cJSON *infoItem = cJSON_CreateObject();
		pDeviceCode = (char*)sqlite3_column_text(stmt, DB_SENSOR_DEVICECODE);
		dm_mgr_dev_node_t *search_node = NULL;
		res = dm_mgr_get_device_by_devicecode(pDeviceCode,&search_node);
		if (res < 0) {
			continue;
		}		
		cJSON_AddStringToObject(infoItem, "productCode", search_node->productCode);
		pident = (char*)sqlite3_column_text(stmt, DB_SENSOR_IDENTIFIER);
		cJSON_AddStringToObject(infoItem, "identifier", pident);
		pValue = (char*)sqlite3_column_text(stmt, DB_SENSOR_VALUE);
		cJSON_AddStringToObject(infoItem, "value", pValue);		
		rtime = sqlite3_column_int(stmt, DB_SENSOR_RECORDTIME);
		cJSON_AddNumberToObject(infoItem, "alarm_time", rtime);	
		cJSON_AddItemToArray(arrayObj, infoItem);
	}
	sqlite3_finalize(stmt);	
	return arrayObj;
}

/************************************************************
*功能描述： 插入传感器警告类信息到数据库
*输入参数： deviceCode：设备deviceCode
          identifier: 事件identifier
          valueStr：事件数据
          time: 当前时间
*输出参数： 无
*返 回 值：  0：成功；其他：失败
*其他说明：
*************************************************************/
int kk_history_insert_sensor_info(const char* deviceCode,const char* identifier,const char* valueStr,time_t itime)
{
	int res = 0;
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	char *sqlCmd = NULL;
	char *zErrMsg = 0;
	sqlite3_stmt *stmt;
	time_t rtime = 0;
	int count = 0;

	const char *insertCmd = "insert into SensorHistoryInfo (deviceCode, identifier,value,recordtime) \
								values ('%s','%s','%s','%d');";	

	INFO_PRINT("kk_history_insert_sensor_info,deviceCode:%s,identifier:%s,value:%s\n",deviceCode,identifier,valueStr);
	_kk_history_lock();
	sqlCmd = sqlite3_mprintf(insertCmd,deviceCode,identifier,valueStr,itime);	
	res = sqlite3_exec(ctx->pDb, sqlCmd, NULL, NULL, &zErrMsg);
	if( res != SQLITE_OK ){
		ERROR_PRINT("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		sqlite3_free(sqlCmd);
		_kk_history_unlock();	
		return FAIL_RETURN;
	}
	sqlite3_free(sqlCmd);
	count =  sqlite_find_count();

	if(count > KK_HISTORY_MAX_COUNT)
	{
		/*按时间删除超过最大数量的记录*/
		const char *selectCmd = "select * from SensorHistoryInfo order by recordtime desc";
		sqlite3_prepare_v2(ctx->pDb, selectCmd, strlen(selectCmd), &stmt, NULL);
		while(sqlite3_step(stmt) == SQLITE_ROW){
			rtime = sqlite3_column_int(stmt, DB_SENSOR_RECORDTIME);
			kk_history_delete_by_recordtime("SensorHistoryInfo",rtime); 
			INFO_PRINT("delete rtime:%ld\n",rtime);
			break;
		}
		sqlite3_finalize(stmt);
	}
	_kk_history_unlock();
	return SUCCESS_RETURN;
}



/************************************************************
*功能描述： 插入插座类功率等信息到数据库
*输入参数： deviceCode：设备deviceCode
          power: 功率
          metering：计电量
          time: 当前时间
*输出参数： 无
*返 回 值：  0：成功；其他：失败
*其他说明：
*************************************************************/
int kk_history_insert_Outlet_info(const char* deviceCode,const char* power,const char* metering,time_t itime)
{
	int res = 0;
	kk_history_ctx_t *ctx = _kk_history_get_ctx();
	char *sqlCmd = NULL;
	char *zErrMsg = 0;
	sqlite3_stmt *stmt;
	time_t rtime = 0;
	int count = 0;

	const char *insertCmd = "insert into OutletHistoryInfo (deviceCode, power,metering,recordtime) \
								values ('%s','%s','%s','%d');";	

	INFO_PRINT("kk_history_insert_Outlet_info,deviceCode:%s,power:%s,metering:%s\n",deviceCode,power,metering);
	_kk_history_lock();
	sqlCmd = sqlite3_mprintf(insertCmd,deviceCode,power,metering,itime);	
	res = sqlite3_exec(ctx->pDb, sqlCmd, NULL, NULL, &zErrMsg);
	if( res != SQLITE_OK ){
		ERROR_PRINT("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		sqlite3_free(sqlCmd);
		_kk_history_unlock();	
		return FAIL_RETURN;
	}
	sqlite3_free(sqlCmd);
	count = sqlite_find_count();
	if(count > KK_HISTORY_MAX_COUNT){	
		/*按时间删除超过最大数量的记录*/
		//const char *selectCmd = "select * from OutletHistoryInfo order by recordtime desc limit (select count(recordtime) from OutletHistoryInfo) offset %d";
		const char *selectCmd = "select * from OutletHistoryInfo order by recordtime desc";			
		sqlite3_prepare_v2(ctx->pDb, selectCmd, strlen(selectCmd), &stmt, NULL);
		while(sqlite3_step(stmt) == SQLITE_ROW){
			rtime = sqlite3_column_int(stmt, DB_OUTLET_RECORDTIME);
			kk_history_delete_by_recordtime("OutletHistoryInfo",rtime); 
			break;
		}
		sqlite3_finalize(stmt);
	}
	_kk_history_unlock();
	return SUCCESS_RETURN;
}



