/*
 * Created by yuuta on 11/28/21.
 */

#include "db.h"
#include "log.h"
#include "botd.h"
#include "utils.h"

#include <stdio.h>
#include <sqlite3.h>
#include <stdint.h>

struct upgrade {
    const char *sql;

    int (*pre_func)(void);

    int (*post_func)(int);
};

static int upgrade_migrate_msg_index(int res);

static const struct upgrade sql_upgrades[] = {
        {"CREATE TABLE 'says' ("
         "'id' INTEGER PRIMARY KEY,"
         "'t' TEXT NOT NULL UNIQUE,"
         "'url' TEXT,"
         "'msg' INTEGER NOT NULL"
         ");", NULL, NULL},
        {"CREATE TABLE 'stats' ("
         "'user' INTEGER UNIQUE,"
         "'i' INTEGER NOT NULL,"
         "PRIMARY KEY('user')"
         ");", NULL, NULL},
        {"CREATE VIEW 'stat_global'"
         "AS "
         "SELECT * FROM (SELECT COUNT(user), SUM(i), MAX(i), AVG(i) FROM stats), (SELECT COUNT(msg) FROM says);",
         NULL, NULL},
        {"ALTER TABLE says ADD msg_index INTEGER;", NULL, upgrade_migrate_msg_index},
        /* TDSQLite is 3.31, while ALTER TABLE DROP support begins from 3.35. */
        /* {"ALTER TABLE says DROP COLUMN url;", NULL, NULL} */
        {"ALTER TABLE says ADD public INTEGER DEFAULT 0;", NULL, NULL},
};

sqlite3 *db = NULL;

sqlite3_stmt *stmt_global_stat = NULL;

sqlite3_stmt *stmt_personal_stat = NULL;

sqlite3_stmt *stmt_search = NULL;

sqlite3_stmt *stmt_random = NULL;

static int upgrade_migrate_msg_index(int res) {
    int r;
    sqlite3_stmt *stmt = NULL;
    sqlite3_stmt *stmt_set = NULL;

    if ((r = sqlite3_prepare_v2(db, "SELECT id,url FROM says;", -1, &stmt, NULL))) {
        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
    }
    if ((r = sqlite3_prepare_v2(db, "UPDATE says SET url = null, msg_index = ? WHERE id = ?;", -1, &stmt_set, NULL))) {
        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
    }

    while ((r = sqlite3_step(stmt)) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        char *url = (char *) sqlite3_column_text(stmt, 1);
        if (!url || !url[0]) {
            continue;
        }
        uint32_t index = tg_url_get_index(url);
        LOGIV("Migrating record %d (%s).", id, url);

        if (!index) {
            LOGFEV("Unknown URL: %s", 1, url);
        }
        if ((r = sqlite3_bind_int64(stmt_set, 1, index))) {
            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
        }
        if ((r = sqlite3_bind_int(stmt_set, 2, id))) {
            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
        }

        if ((r = sqlite3_step(stmt_set)) != SQLITE_DONE) {
            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
        }
        if (sqlite3_changes(db) != 1) {
            LOGFEV("Cannot upgrade DB: Missing %d.", 1, id);
        }
        if ((r = sqlite3_reset(stmt_set))) {
            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
        }
    }

    if (r != SQLITE_DONE) {
        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
    }

    sqlite3_finalize(stmt);
    sqlite3_finalize(stmt_set);
    return 0;
}

void db_init(void) {
    int r;
    r = sqlite3_open(cmd.db_path, &db);
    if (r) {
        LOGFEV("Cannot open SQLite3 database: %s", r, sqlite3_errstr(r));
    }
    char *errmsg = NULL;
    /* Enable foreign key support */
    if ((r = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &errmsg))) {
        goto sql_err;
        sql_err:
        db_close();
        LOGFEV("%s", r, errmsg);
    }
    /* Get user_version */
    sqlite3_stmt *stmt;
    if ((r = sqlite3_prepare_v2(db, "PRAGMA user_version", -1, &stmt, NULL))) {
        goto sql_err;
    }
    if ((r = sqlite3_step(stmt)) != SQLITE_ROW) {
        errmsg = (char *) sqlite3_errstr(r);
        sqlite3_finalize(stmt);
        goto sql_err;
    }
    int current_var = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    for (; current_var < sizeof(sql_upgrades) / sizeof(struct upgrade); current_var++) {
        const int to = current_var + 1;
        const struct upgrade *upg = &sql_upgrades[current_var];
        LOGIV("Upgrading SQL schema from %d to %d.",
              current_var,
              to);
        /* Pre / post functions return values are currently unused. */
        if (upg->pre_func) {
            upg->pre_func();
        }
        /* TRY to do that anyway, if we have a post-fun. */
        r = sqlite3_exec(db, upg->sql, NULL, NULL, &errmsg);
        if (upg->post_func) {
            if (r) {
                LOGEV("Error while executing upgrade: %s, continuing anyway", errmsg);
                sqlite3_free(errmsg);
            }
            upg->post_func(r);
        } else if (r) {
            goto sql_err;
        }
        char sql[60];
        sprintf(sql, "PRAGMA user_version = %d;", to);
        if ((r = sqlite3_exec(db, sql, NULL, NULL, &errmsg))) {
            goto sql_err;
        }
    }

    /* Prepare statements */
    if ((r = sqlite3_prepare_v2(db, "SELECT * FROM stat_global;", -1, &stmt_global_stat, NULL))) {
        errmsg = (char *) sqlite3_errstr(r);
        goto sql_err;
    }

    if ((r = sqlite3_prepare_v2(db, "SELECT * FROM (SELECT i FROM stats WHERE user = ?), (SELECT COUNT(i) FROM stats WHERE i >= (SELECT i FROM stats WHERE user = ?)), (SELECT COUNT(i) FROM stats)", -1, &stmt_personal_stat, NULL))) {
        errmsg = (char *) sqlite3_errstr(r);
        goto sql_err;
    }

    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, msg_index FROM says WHERE t LIKE ? LIMIT 10;",
                                -1,
                                &stmt_search,
                                NULL))) {
        errmsg = (char *) sqlite3_errstr(r);
        goto sql_err;
    }

    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, msg_index FROM says ORDER BY RANDOM() LIMIT 10;",
                                -1,
                                &stmt_random,
                                NULL))) {
        errmsg = (char *) sqlite3_errstr(r);
        goto sql_err;
    }
}

void db_close(void) {
    if (db == NULL)
        return;
    if (stmt_global_stat) {
        sqlite3_finalize(stmt_global_stat);
        stmt_global_stat = NULL;
    }
    if (stmt_personal_stat) {
        sqlite3_finalize(stmt_personal_stat);
        stmt_personal_stat = NULL;
    }
    if (stmt_random) {
        sqlite3_finalize(stmt_random);
        stmt_random = NULL;
    }
    if (stmt_search) {
        sqlite3_finalize(stmt_search);
        stmt_search = NULL;
    }
    sqlite3_close(db);
    db = NULL;
}