/********************************************************************************
 *      Copyright:  (C) 2020 LingYun IoT System Studio
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This library used to operate blob packet in sqlite database.
 *
 *        Version:  1.0.0(2020年05月13日)
 *         Author:  Guo Wenxue <guowenxue@gmail.com>
 *      ChangeLog:  1, Release initial version on "2020年05月13日 12时14分23秒"
 *
 ********************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include "database.h"
#include "logger.h"

/* Blob packet table name */
#define TABLE_NAME     "PackTable"

/* Use static global handler here in order to simplify the API,
 * But it will make this library not thread safe
 */
static sqlite3         *s_clidb = NULL;


/* description: open or create sqlite database if not exist
 * input args:
 * $db_file: sqlite database file name
 * return value: <0: failure   0:ok
 * */
int db_open(const char *db_file)
{
    char               sql[SQL_COMMAND_LEN]={0};
    char              *errmsg = NULL;

    if( !db_file )
    {
        log_error("%s() Invalid input arguments\n", __func__);
        return -1;
    }

    /* open database */
    if( SQLITE_OK != sqlite3_open_v2(db_file, &s_clidb, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL) )
    {
        log_error("open/create database '%s' failed: %s\n", db_file, sqlite3_errmsg(s_clidb));
        sqlite3_close(s_clidb);
        return -2;
    }

    /* ---------------------------------------------
     * SQLite performance & storage tuning
     * --------------------------------------------- */

    /* Disable synchronous for higher performance */
    sqlite3_exec(s_clidb, "pragma synchronous = OFF; ", NULL, NULL, NULL);

    /* enable full auto vacuum, Auto increase/decrease  */
    sqlite3_exec(s_clidb, "pragma auto_vacuum = 2 ; ", NULL, NULL, NULL);


    /* ---------------------------------------------
     * Create table if not exists
     * --------------------------------------------- */

    snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS %s (packet BLOB);", TABLE_NAME);
    if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg) )
    {
        log_error("create table '%s' failed: %s\n", TABLE_NAME, errmsg);
        sqlite3_free(errmsg);   /* free errmsg  */
        sqlite3_close(s_clidb); /* close databse */
        unlink(db_file);        /* remove file */
        return -3;
    }

    log_info("database '%s' initialized ok (table=%s)\n", db_file, TABLE_NAME);
    return 0;
}


/* description: close sqlite database handler
 * return value: none
 */
void db_close(void)
{
    log_warn("close sqlite database now\n");
    sqlite3_close(s_clidb);

    return ;
}


/*
 * description: write a blob packet into database
 * input args:
 *   pack: blob packet data address
 *   size: blob packet data length
 * return value:
 *   <0: failure
 *    0: ok
 */
int db_write(const void *pack, int size)
{
    static sqlite3_stmt     *stmt = NULL;
    int                     rv = 0;

    if (!pack || size <= 0)
    {
        log_error("%s(): invalid input arguments\n", __func__);
        return -1;
    }

    if (!s_clidb)
    {
        log_error("sqlite database not opened\n");
        return -2;
    }

    /* Prepare statement once */
    if (!stmt)
    {
        const char *sql = "INSERT INTO " TABLE_NAME " (packet) VALUES (?);";
        if( SQLITE_OK != sqlite3_prepare_v2(s_clidb, sql, -1, &stmt, NULL) )
        {
            log_error("sqlite3_prepare_v2 failed: %s\n", sqlite3_errmsg(s_clidb));
            return -3;
        }
    }

    /* Bind blob */
    if( SQLITE_OK != sqlite3_bind_blob(stmt, 1, pack, size, SQLITE_STATIC) )
    {
        log_error("sqlite3_bind_blob failed: %s\n", sqlite3_errmsg(s_clidb));
        rv = -4;
        goto out;
    }

    /* Execute */
    if( SQLITE_DONE != sqlite3_step(stmt) )
    {
        log_error("sqlite3_step failed: %s\n", sqlite3_errmsg(s_clidb));
        rv = -5;
        goto out;
    }

    rv = 0;
out:
    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    if (rv == 0)
        log_debug("add new blob packet(size=%d) into database ok\n", size);
    else
        log_error("add new blob packet(size=%d) into database failed, rv=%d\n", rv);

    return rv;
}


/*
 * description: read the first blob packet and its rowid from database
 * input args:
 *   pack     : blob output buffer
 *   size     : buffer size
 *   bytes    : actual blob size (output)
 *   rowid    : rowid of the record (output)
 *
 * return value:
 *   <0 : failure
 *    0 : ok
 */
int db_read(void *pack, int size, int *bytes, int *rowid)
{
    static sqlite3_stmt     *stmt = NULL;
    const void              *blob_ptr;
    int                     rv = 0;

    if (!pack || size <= 0 || !bytes || !rowid)
    {
        log_error("%s(): invalid input arguments\n", __func__);
        return -1;
    }

    if (!s_clidb)
    {
        log_error("sqlite database not opened\n");
        return -2;
    }

    /* Prepare statement once */
    if (!stmt)
    {
        const char *sql = "SELECT rowid, packet FROM " TABLE_NAME " ORDER BY rowid ASC LIMIT 1;";
        if( SQLITE_OK != sqlite3_prepare_v2(s_clidb, sql, -1, &stmt, NULL) )
        {
            log_error("sqlite3_prepare_v2 failed: %s\n", sqlite3_errmsg(s_clidb));
            return -3;
        }
    }

    /* Step to first row */
    if ( SQLITE_ROW != sqlite3_step(stmt) )
    {
        rv = -4;
        goto out;
    }

    /* get rowid */
    *rowid = (int)sqlite3_column_int64(stmt, 0);

    /* get blob */
    blob_ptr = sqlite3_column_blob(stmt, 1);
    *bytes = sqlite3_column_bytes(stmt, 1);

    if (!blob_ptr || *bytes <= 0)
    {
        log_error("invalid blob data (bytes=%d)\n", *bytes);
        rv = -5;
        goto out;
    }

    if (*bytes > size)
    {
        log_error("blob too large (%d > %d)\n", *bytes, size);
        rv = -6;
        goto out;
    }

    memcpy(pack, blob_ptr, *bytes);
    rv = 0;

out:
    sqlite3_reset(stmt);
    return rv;
}


/*
 * description: remove a blob packet by rowid
 * input args:
 *   rowid : rowid of the record to delete
 * return value: <0: failure   0:ok
 */
int db_remove(int rowid)
{
    char        sql[SQL_COMMAND_LEN] = {0};
    char        *errmsg = NULL;
    int         changes = 0;
    static int  count = 0;

    if (!s_clidb)
    {
        log_error("sqlite database not opened\n");
        return -1;
    }

    if (rowid <= 0)
    {
        log_error("invalid rowid (%d)\n", rowid);
        return -2;
    }

    /* Execute SQL */
    snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE rowid = %d;", TABLE_NAME, rowid);
    if (SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg))
    {
        log_error("remove packet failed (rowid=%d): %s\n",
                  rowid, errmsg ? errmsg : "unknown error");
        sqlite3_free(errmsg);
        return -3;
    }

    /* Check how many rows were actually deleted */
    changes = sqlite3_changes(s_clidb);
    if (changes == 0)
    {
        log_warn("no record deleted (rowid=%d)\n", rowid);
        return -4;
    }

    log_debug("remove packet ok (rowid=%d)\n", rowid);

    /*
     * Optional: vacuum after N successful deletions
     * WARNING: VACUUM is expensive and blocks DB access
     */
    if (++count >= 500)
    {
        log_info("trigger database VACUUM\n");
        sqlite3_exec(s_clidb, "VACUUM;", NULL, 0, NULL);
        count = 0;
    }

    return 0;
}