/*
 * for sqlite3 databases 
 * db.h 
 *
 *  Created on: FEB 17, 2019
 *      Author: skerlan
 */

#ifndef DB_SQLITE3_H_
#define DB_SQLITE3_H_

#include "util.h"

#define TEXT_STR_SIZE 32

#define LOOKUPPASSUSER "select uid from users where reader_sn = ? COLLATE NOCASE and uid = ?;"
#define LOOKUPGATE "select uid from users where gate_sn = ? COLLATE NOCASE;"
#define INSERTPASSUSER "insert or ignore into users (uid,reserve_start,reserve_end,reader_sn,gate_sn,gate_dir,check_time,check_wristband,wristband) VALUES (?, ?, ?, ?, ?, ?, ?,?,?);"
#define UPDATEWRISTBAND "update  users set wristband = ? where uid = ?;"
#define DELETEPASSUSER "delete from users where uid = ? and gate_sn = ?;"
#define QUREYPASSUSER  "select uid,gate_sn,reserve_start,reserve_end,gate_dir,check_time,check_wristband,wristband from users where reader_sn = ? COLLATE NOCASE and uid = ? COLLATE NOCASE;"
#define DELETEPASSUSERALL "delete from users where gate_sn = ?;"

#define CREATEUSERS "\
CREATE TABLE IF NOT EXISTS `users` (\
`uid` TEXT PRIMARY KEY DEFAULT NULL,\
`reserve_start` TEXT DEFAULT 0,\
`reserve_end` TEXT DEFAULT 0,\
`reader_sn` TEXT DEFAULT NULL,\
`gate_sn` TEXT DEFAULT NULL,\
`gate_dir` TEXT DEFAULT NULL,\
`check_time` INTEGER DEFAULT 0,\
`check_wristband` INTEGER DEFAULT 0,\
`wristband` INTEGER DEFAULT 0\
);"

#define INSERTCFG "INSERT OR IGNORE INTO config (option, value) VALUES ('server', 'localhost'), ('upport', '1700'), ('dwport', '1701')"

#define INITSTMT(SQL, STMT) if (sqlite3_prepare_v2(cntx->db, SQL, -1, &STMT, NULL) != SQLITE_OK) {  \
									MSG_DEBUG(DEBUG_DEBUG, "failed to prepare sql; %s -> %s\n", SQL,  sqlite3_errmsg(cntx->db));\
									goto out;\
								}

struct context {
	sqlite3* db;
    sqlite3_stmt* insert_user;
    sqlite3_stmt* delete_user;
    sqlite3_stmt* lookup_user;
    sqlite3_stmt* lookup_gate;
    sqlite3_stmt* qurey_user;
    sqlite3_stmt* clean_user;
    sqlite3_stmt* updatewristband;
};

struct vipinfo {
    char uid[TEXT_STR_SIZE];
    char gate_sn[TEXT_STR_SIZE];
    char reserve_start[TEXT_STR_SIZE];
    char reserve_end[TEXT_STR_SIZE];
    char gate_dir[TEXT_STR_SIZE];
    bool check_time;
    bool check_wristband;
    bool wristband;
};

bool db_init(const char* dbpath, struct context* cntx);
void db_destroy(struct context* cntx);
bool db_write_vipinfo(sqlite3_stmt* , void* );
bool db_lookup_vipinfo(sqlite3_stmt* , char *);
bool db_lookup_gateinfo(sqlite3_stmt* , char *);
bool db_delete_vipinfo(sqlite3_stmt* , char *);
bool db_result_vipinfo(sqlite3_stmt* stmt, char *data,void *info);
bool db_update_wristband(sqlite3_stmt* stmt, void* data);
#endif   /* end defined DB_SQLITE3_H_ */