#ifndef __DY_DB_H
#define __DY_DB_H

#include "sqlite3.h"
#include "dy_common.h"
#include "dy_pp.h"

#define DATA_COLLECT_DB_NAME "/app/collect.db"
#define DATA_COLLECT_DB_TMPFS_NAME "/tmp/collect.db"
#define TAG_TABLE_NAME       "data_table"

/******************************************************************************
	+─────────────+──────────+───────────────────────────────+
	| 字段名       | 类型     | 描述                          |
	+─────────────+──────────+───────────────────────────────+
	| id          | INTEGER  | 索引                          |
	+─────────────+──────────+───────────────────────────────+
	| sn          | TEXT     | 设备序列号                     |
	+─────────────+──────────+───────────────────────────────+
	| tag         | BLOB     | 数据内容，可以是json字符串或二进制数据|
	+─────────────+──────────+───────────────────────────────+
	| time        | INTEGER  | 数据时间戳                     |
	+─────────────+──────────+───────────────────────────────+
	| identifier  | TEXT     | 服务名称                       |
	+─────────────+──────────+───────────────────────────────+
	| type        | INTEGER  | 数据类型； 0 - 属性  1 - 事件  2 - 服务  |
	+─────────────+──────────+───────────────────────────────+
	| report      | INTEGER  | 上报标记； 0-需要上报但未上报；1-已上报；2-无需上报 |
	+─────────────+──────────+───────────────────────────────+
	| port        | TEXT     | 数据来源端口                   |
	+─────────────+──────────+───────────────────────────────+
	| app_key     | TEXT     | 数据所属APP KEY                |
	+─────────────+──────────+───────────────────────────────+
	| res         | INTEGER  | 数据质量码                     |
	+─────────────+──────────+───────────────────────────────+
	| ext         | TEXT     | 扩展字段json字符串             |
	+─────────────+──────────+───────────────────────────────+
******************************************************************************/
#define TAG_TABLE_CREATE \
    "id INTEGER PRIMARY KEY AUTOINCREMENT,"\
    "sn VARCHAR(64),"\
    "tag BLOB,"\
    "time INTEGER,"\
    "identifier VARCHAR(128),"\
    "type INTEGER,"\
    "report INTEGER,"\
    "port VARCHAR(16),"\
    "app_key VARCHAR(64),"\
    "res INTEGER,"\
    "ext VARCHAR(128)"

#define TAG_INSERT_SQL "INSERT INTO "TAG_TABLE_NAME" VALUES (NULL,?,?,?,?,?,?,?,?,?,?)"

#define TAG_UPDATE_SQL "UPDATE "TAG_TABLE_NAME" SET report = ? WHERE id = ?"

#define TAG_UPDATE_TAG_SQL "UPDATE "TAG_TABLE_NAME" SET tag = ? WHERE id = ?"

#define TAG_UPDATE_SQL_BY_TAG "UPDATE "TAG_TABLE_NAME" SET report = ? WHERE tag = ?"

#define TAG_DELETE_REPORT_SQL "DELETE FROM "TAG_TABLE_NAME" WHERE report = 1"

#define TAG_DELETE_DATA_SQL "DELETE FROM "TAG_TABLE_NAME" WHERE id < (select (min(id)+max(id))/2 as mid from "TAG_TABLE_NAME")"

#define TAG_TABLE_SELECT_PROPERTY_BY_SN \
	"SELECT * FROM "TAG_TABLE_NAME" WHERE id IN ( SELECT MAX(id) AS max_id FROM "TAG_TABLE_NAME" WHERE sn IN (?) and identifier = \"post\" and type=0)"
#define TAG_TABLE_SELECT_PROPERTY_SN \
	"SELECT * FROM "TAG_TABLE_NAME" WHERE id IN ( SELECT MAX(id) AS max_id FROM "TAG_TABLE_NAME" WHERE sn IN (?) and type=0)"
#define TAG_TABLE_SELECT_NOT_REPORTED \
	"SELECT * FROM "TAG_TABLE_NAME" WHERE report = 0 ORDER BY time DESC LIMIT ?"
#define TAG_TABLE_CREATE_INDEX "CREATE INDEX report_index ON "TAG_TABLE_NAME"(report)"

#define TAG_TABLE_SELECT_PROPERTY_BY_SN_WITH_START_COUNT \
	"SELECT * FROM "TAG_TABLE_NAME" WHERE sn IN (?) and id >= ? and identifier = \"post\" and type=0 limit ?"

#define TAG_TABLE_SELECT_PROPERTY_WITH_START_COUNT \
	"SELECT * FROM "TAG_TABLE_NAME" WHERE id >= ? and identifier = \"post\" and type=0 limit ?"

#define INSERT_REQ_BIT   0
#define SELECT_REQ_BIT   1
#define UPDATE_REQ_BIT   2
#define DELETE_REPORTED_REQ_BIT 3
#define UPDATE_BYTAG_CONTENT_BIT 4

#define INSERT_REQ_MASK   (1 << INSERT_REQ_BIT)
#define SELECT_REQ_MASK   (1 << SELECT_REQ_BIT)
#define UPDATE_REQ_MASK   (1 << UPDATE_REQ_BIT)
#define DEL_REPTED_REQ_MASK   (1 << DELETE_REPORTED_REQ_BIT)
#define UPDATE_BYTAG_CONTENT (1 << UPDATE_BYTAG_CONTENT_BIT)

typedef struct dy_db_session_s
{
    sqlite3 *db_write; //写db连接
    sqlite3 *db_read; //写db连接
    struct list_head insert_list; //待插入列表
    struct list_head update_list; //待更新列表
    struct list_head query_list; //查询结果列表
    struct list_head update_content_list; //
    pthread_mutex_t db_ops_lock;
    pthread_cond_t db_ops_cond;
    int db_ops_req;
    int (*query_finish_cb)(struct dy_db_session_s *session, void *user_data);
    void *user_data;
	sqlite3_stmt *update_tag_content;
	sqlite3_stmt *update_tag;
	sqlite3_stmt *update_by_tag_content;
	sqlite3_stmt *select_data_not_reported;
	sqlite3_stmt *select_collect_by_sn;
	sqlite3_stmt *stmt_without_sn;
	sqlite3_stmt *stmt_with_sn;
	sqlite3_stmt *delete_reported;
	sqlite3_stmt *intert_tag_one;
	sqlite3_stmt *delete_data;
} dy_db_session_t;

int dy_db_session_init(dy_db_session_t **session, char *db_file, void *user_data);
int dy_db_session_insert_tag(dy_db_session_t *session, tag_table_t *tag);
int dy_db_select_property_sn(dy_db_session_t *session, char *SNs, struct list_head *query_list);
int dy_db_select_property_by_sn(dy_db_session_t *session, char *SNs, struct list_head *query_list);
int dy_db_session_update_reported(dy_db_session_t *session);
int dy_db_session_select_not_repoted(dy_db_session_t *session, int count);
int db_update_tag(dy_db_session_t *session, tag_table_t *tag);
int db_update_by_tag_content(dy_db_session_t *session, tag_table_t *tag);
sqlite3 *db_open_db(char *db_file);
int dy_db_session_add_update_list(dy_db_session_t *session, struct list_head *new_list);
int dy_db_session_delete_reported(dy_db_session_t *session);
int dy_db_session_update_tag_content(dy_db_session_t *session, tag_table_t *tag);

/**
*******************************************************************************
*
* @brief 根据条件检索服务为post 的tag数据, 检索后的结果存放于query_list
*
* @param[in] session DB会话结构体的指针
* @param[in] SN 要检索的SN，只支持一个SN，如果没有则为NULL
* @param[in] start_id 检索数据库中的起始ID，应该>=0
* @param[in] count 最大查询个数，防止数据库过大导致内存耗尽,count要>0
* @param[out] query_list 查询的结果存放于此链表中
* @return 返回值
*   - 0 成功
*   - -1 执行失败
* @note 
*   1. 此函数为同步函数，调用者会等待直到检索完成
*   2. query_list中的内存需要调用者主动释放
*   3. 当没有匹配条件的数据时，会返回0，但是query_list中节点个数为0
*
*******************************************************************************
*/
int dy_db_select_property_with_start_count(dy_db_session_t *session, char *SN, int start_id, int count, struct list_head *query_list);
#endif