/*  edb-sqlite.c  --  Main driver for eurephia authentication plugin for OpenVPN
 *                    This is the SQLite database driver
 *
 *  GPLv2 only - Copyright (C) 2008 - 2012
 *               David Sommerseth <dazo@users.sourceforge.net>
 *
 *  This program is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU General Public License
 *  as published by the Free Software Foundation; version 2
 *  of the License.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 *
 */

/**
 * @file   edb-sqlite.c
 * @author David Sommerseth <dazo@users.sourceforge.net>
 * @date   2008-08-06
 *
 * @brief  eurephia database driver for the SQLite3 database.
 *         This file is the main API for the driver.
 *
 */


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <assert.h>

#define DRIVERVERSION "1.3"     /**< Defines the software version of this driver */
#ifndef DRIVERAPIVERSION
# define DRIVERAPIVERSION 3     /**< Sets the API version level of this driver */
#endif

#include <sqlite3.h>

#include <eurephiadb_driver.h>
#include <eurephia_nullsafe.h>
#include <eurephia_log.h>
#include <eurephia_values.h>
#include <eurephiadb_session_common.h>
#include <eurephiadb_session_struct.h>
#include <passwd.h>

#include "sqlite.h"



/**
 * Mapping struct - maps attempt types (attempt_IPADDR, attempt_CERTIFICATE, attempt_USERNAME)
 * to database field names, configuration options (with default values) and description
 */
typedef struct {
        char *colname;          /**< Field when doing look up in blacklist and attempts tables */
        char *colname_where;    /**< Field when using column name in WHERE section of SQL queries */
        char *allow_cfg;        /**< Configure parameter for the attempt limits */
        char *descr;            /**< Description, used to give more readable output for users */
        char *default_value;    /**< Default value, if config option is not found */
        char *value_func;       /**< If not NULL, the value will be passed through the given SQL function */
} eDBattempt_types_t;
               

/**
 *  Static mapping table with the needed values.  Uses the eDBattempt_types_t struct.
 */
static const eDBattempt_types_t eDBattempt_types[] = {
        {NULL, NULL, NULL, NULL},
        {"remoteip\0", "remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL},
        {"digest\0", "lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"},
        {"username\0", "username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL},
        {NULL, NULL, NULL, NULL}
};


/**
 * @copydoc eDB_DriverVersion()
 */
const char *eDB_DriverVersion(void) {
        return "edb-sqlite (v"DRIVERVERSION")  David Sommerseth 2008-2012 (C) GPLv2";
}


/**
 * @copydoc eDB_DriverAPIVersion()
 */
int eDB_DriverAPIVersion() {
        return DRIVERAPIVERSION;
}


/*
 *   local functions
 */

/**
 * Internal driver function for simplifying update of openvpn_blacklist.  It will simply just
 * update the 'last_accessed' field in the blacklist table.
 *
 * @param ctx  eurephiaCTX
 * @param blid Blacklist ID, integer value corresponding to the record in the database
 */
void update_attempts(eurephiaCTX *ctx, const char *blid) {
        dbresult *res = NULL;

        if( blid != NULL ) {
                res = sqlite_query(ctx, 
                                   "UPDATE openvpn_blacklist "
                                   "   SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %q", blid);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_CRITICAL, 0,
                                     "Could not update openvpn_blacklist.last_accessed for blid=%s", blid);
                        sqlite_log_error(ctx, res);
                }
                sqlite_free_results(res);
        }
}

/*
 *   Public driver functions
 */

/**
 * @copydoc eDBconnect()
 * Connect to the database ... connection is stored in the eurephiaCTX context
 */
int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv)
{
        eDBconn *dbc = NULL;
        dbresult *res = NULL;
        int rc;

        DEBUG(ctx, 20, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[0]);

        if( (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) {
                eurephia_log(ctx, LOG_PANIC, 0,
                             "Wrong parameters to edb-sqlite.  Cannot open database.");
                return 0;
        }
        
        // Connect to the database
        dbc = (eDBconn *) malloc_nullsafe(ctx, sizeof(eDBconn)+2);
        dbc->dbname = strdup(argv[0]);

        eurephia_log(ctx, LOG_INFO, 1, "Opening database '%s'", dbc->dbname);

        rc = sqlite3_open(argv[0], (void *) &dbc->dbhandle);
        if( rc ) {
                eurephia_log(ctx, LOG_PANIC, 0, "Could not open database '%s'", dbc->dbname);
                free_nullsafe(ctx, dbc->dbname);
                free_nullsafe(ctx, dbc);
                return 0;
        }

        dbc->config = NULL;
        ctx->dbc = dbc;

        if( sqlite_init_functions(ctx) != dbSUCCESS ) {
                sqlite3_close((sqlite3 *) dbc->dbhandle);
                free_nullsafe(ctx, dbc->dbname);
                dbc->dbhandle = NULL;
                return 0;
        }

        // Load configuration parameters into memory
        eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)");
        res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_config");
        if( sqlite_query_status(res) == dbSUCCESS ) {
                int i = 0;
                eurephiaVALUES *cfg = NULL;
                
                cfg = eCreate_value_space(ctx, 11);
                if( cfg == NULL ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not allocate memory for config variables");
                        sqlite_free_results(res);
                        return 0;
                }
                for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                        eAdd_value(ctx, cfg, sqlite_get_value(res, i, 0), sqlite_get_value(res, i, 1));
                }
                ctx->dbc->config = cfg;
        } else {
                sqlite_log_error(ctx, res);
        }
        sqlite_free_results(res);
        return 1;
}

/**
 * @copydoc eDBdisconnect()
 * Disconnect from the database
 */
void eDBdisconnect(eurephiaCTX *ctx) 
{
        eDBconn *dbc = NULL;

        DEBUG(ctx, 20, "Function call: eDBdisconnect(ctx)");

        if( ctx->dbc == NULL ) {
                eurephia_log(ctx, LOG_WARNING, 0, "Database not open, cannot close database.");
                return;
        }

        dbc = ctx->dbc;
        eurephia_log(ctx, LOG_INFO, 1, "Closing database '%s'", dbc->dbname);

        // Close database connection
        sqlite3_close((sqlite3 *) dbc->dbhandle);
        free_nullsafe(ctx, dbc->dbname);
        dbc->dbhandle = NULL;

        // Free up config memory
        eFree_values(ctx, dbc->config);
        free_nullsafe(ctx, dbc);
        ctx->dbc = NULL;
}


/**
 * @copydoc eDBauth_TLS()
 */
int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char *email, 
                const char *digest, const unsigned int depth)
{
        dbresult *res = NULL;
        int certid = 0;
        char *blid = NULL;

        DEBUG(ctx, 20, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i)",
              org, cname, email, digest, depth);

        // Check if certificate is valid, and not too many attempts has been tried with the given certificate
        res = sqlite_query(ctx, 
                           "SELECT cert.certid, blid "
                           "  FROM openvpn_certificates cert"
                           "  LEFT JOIN openvpn_blacklist bl USING(digest)"
                           " WHERE organisation='%q' AND common_name='%q' "
                           "      AND email='%q' AND depth='%i' AND lower(cert.digest)=lower('%q')%c",
                           org, cname, email, depth, digest, 0);

        if( sqlite_query_status(res) == dbSUCCESS ) {
                certid = atoi_nullsafe(sqlite_get_value(res, 0, 0));
                blid = strdup_nullsafe(sqlite_get_value(res, 0, 1));

                // Check if the certificate is blacklisted or not.  blid != NULL when blacklisted
                if( blid != NULL ) {
                        // If the certificate or IP is blacklisted, update status and deny access.
                        eurephia_log(ctx, LOG_WARNING, 0,
                                     "Attempt with BLACKLISTED certificate (certid %i)", certid);
                        update_attempts(ctx, blid);
                        certid = -1;
                }
                free_nullsafe(ctx, blid);
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information");
                sqlite_log_error(ctx, res);
        }
        sqlite_free_results(res);

        DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i) - %i",
              org, cname, email, digest, depth, certid);

        return certid;
}


/**
 * @copydoc eDBauth_user()
 */
int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const char *passwd) 
{
        dbresult *res = NULL;
        char *crpwd = NULL, *activated = NULL, *deactivated = NULL, *blid_uname = NULL, *blid_cert = NULL;
        char *dbpwd = NULL;
        int uicid = 0, uid = 0, pwdok = 0;

        DEBUG(ctx, 20, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username);


        // Generate SHA512 hash of password, used for password auth
        res = sqlite_query(ctx,
                           "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, password "
                           "  FROM openvpn_users ou"
                           "  JOIN openvpn_usercerts uc USING(uid) "
                           "  LEFT JOIN openvpn_blacklist bl1 ON( ou.username = bl1.username) "
                           "  LEFT JOIN (SELECT blid, certid "
                           "               FROM openvpn_certificates "
                           "               JOIN openvpn_blacklist USING(digest)) bl2 ON(uc.certid = bl2.certid)"
                           " WHERE uc.certid = '%i' AND ou.username = '%q'",
                           certid, username);
        memset(crpwd, 0, strlen_nullsafe(crpwd));
        free_nullsafe(ctx, crpwd);
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Could not lookup user in database (certid %i, username '%s'", certid, username);
                sqlite_log_error(ctx, res);
                sqlite_free_results(res);
                return 0;
        }

        if( sqlite_get_numtuples(res) == 1 ) {
                uid         = atoi_nullsafe(sqlite_get_value(res, 0, 1));
                activated   = sqlite_get_value(res, 0, 2);
                deactivated = sqlite_get_value(res, 0, 3);
                blid_uname  = sqlite_get_value(res, 0, 4);
                blid_cert   = sqlite_get_value(res, 0, 5);
                dbpwd       = sqlite_get_value(res, 0, 6);

                if( dbpwd == NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. DB error.",
                                     username);
                        pwdok = 0;
                } else {
                        crpwd = eurephia_pwd_crypt(ctx, passwd, dbpwd);
                        pwdok = ((crpwd != NULL) && (strcmp(crpwd, dbpwd) == 0) ? 1 : 0);
                        memset(crpwd, 0, strlen_nullsafe(crpwd));
                        memset(dbpwd, 0, strlen_nullsafe(dbpwd));
                        free_nullsafe(ctx, crpwd);
                }

                if( blid_uname != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "User account is BLACKLISTED (uid: %i, %s)",
                                     uid, username);
                        uicid = -1;
                } else if( blid_cert != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0,
                                     "User account linked with a BLACKLISTED certificate "
                                     "(uid: %i, %s) - certid: %s",
                                     uid, username, certid);
                        uicid = -1;
                } else if( activated == NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "User account is not activated (uid: %i, %s)",
                                     uid, username);
                        uicid = -1;
                } else if( deactivated != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "User account is deactivated (uid: %i, %s)",
                                     uid, username);
                        uicid = -1;
                } else if( pwdok != 1 ) {
                        eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. Wrong password.",
                                     username);
                        sleep(2);
                        uicid = -1;
                } else {
                        dbresult *upd = NULL;

                        uicid = atoi_nullsafe(sqlite_get_value(res, 0, 0));

                        // Update last accessed status
                        upd = sqlite_query(ctx,
                                           "UPDATE openvpn_users SET last_accessed = CURRENT_TIMESTAMP"
                                           " WHERE uid = %i", uid);
                        if( sqlite_query_status(upd) != dbSUCCESS) {
                                eurephia_log(ctx, LOG_ERROR, 0,
                                             "Could not update last access status for uid %i", uid);
                                sqlite_log_error(ctx, upd);
                        }
                        sqlite_free_results(upd);
                }
        } else {
                eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'.  "
                             "Could not find user or user-certificate link.", username);
                sleep(2);
                uicid = 0;
        }
        sqlite_free_results(res);

        DEBUG(ctx, 20, "Result function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx') - %i",
              certid, username, uicid);

        return uicid;
}

/**
 * @copydoc eDBget_uid()
 */
int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username)  
{
        dbresult *res = NULL;
        int ret = 0;

        DEBUG(ctx, 20, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username);

        res = sqlite_query(ctx,
                           "SELECT uid "
                           "  FROM openvpn_usercerts "
                           "  JOIN openvpn_users USING (uid) "
                           " WHERE certid = '%i' AND username = '%q'",
                           certid, username);
        if( (sqlite_query_status(res) != dbSUCCESS) || (sqlite_get_numtuples(res) != 1) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup userid for user '%s'", username);
                if( sqlite_query_status(res) == dbERROR ) {
                        sqlite_log_error(ctx, res);
                }
                ret = -1;
        } else {
                ret = atoi_nullsafe(sqlite_get_value(res, 0, 0));
        }
        sqlite_free_results(res);

        return ret;
}


/**
 * @copydoc eDBblacklist_check()
 */
int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val)
{
        dbresult *blr = NULL, *atpr = NULL;
        int atpexceed = -1, blacklisted = 0;
        char *atpid = NULL, *blid = NULL;

        DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')",
              eDBattempt_types[type].descr, val);

        blr  = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = %s%s'%q'%s",
                            eDBattempt_types[type].colname_where,
                            defaultValue(eDBattempt_types[type].value_func, ""),
                            (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
                            val,
                            (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : ""));
        if( sqlite_query_status(blr) == dbSUCCESS ) {
                blid = strdup_nullsafe(sqlite_get_value(blr, 0, 0));

                if( blid != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s",
                                     eDBattempt_types[type].descr, val);
                        blacklisted = 1; // [type] is blacklisted
                }
                // Update attempt information
                update_attempts(ctx, blid);
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Querying openvpn_blacklist for blacklisted %s failed",
                             eDBattempt_types[type].descr);
                sqlite_log_error(ctx, blr);
        }
        sqlite_free_results(blr);

        if( blacklisted == 0 ) {
                // Check if this [type] has been attempted earlier - if it has reaced the maximum 
                // attempt limit, blacklist it
                atpr = sqlite_query(ctx,
                                    "SELECT atpid, attempts >= %q FROM openvpn_attempts WHERE %s = '%q'",
                                    defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
                                                  eDBattempt_types[type].default_value),
                                    eDBattempt_types[type].colname_where, val);

                if( sqlite_query_status(atpr) == dbSUCCESS ) {
                        atpid = strdup_nullsafe(sqlite_get_value(atpr, 0, 0));
                        atpexceed = atoi_nullsafe(sqlite_get_value(atpr, 0, 1));
                        
                        // If [type] has reached attempt limit and it is not black listed, black list it
                        if( (atpexceed > 0) && (blid == NULL) ) {
                                eurephia_log(ctx, LOG_WARNING, 0,
                                             "%s got BLACKLISTED due to too many failed attempts: %s",
                                             eDBattempt_types[type].descr, val);
                                blr = sqlite_query(ctx, 
                                                   "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
                                                   eDBattempt_types[type].colname, val);
                                if( sqlite_query_status(blr) != dbSUCCESS ) {
                                        eurephia_log(ctx, LOG_CRITICAL, 0,
                                                     "Could not blacklist %s (%s)",
                                                     eDBattempt_types[type].descr, val);
                                        sqlite_log_error(ctx, blr);
                                }
                                blacklisted = 1; // [type] is blacklisted
                                sqlite_free_results(blr);
                        }
                        free_nullsafe(ctx, atpid);
                } else {
                        eurephia_log(ctx, LOG_CRITICAL, 0, "Querying openvpn_attempts for blacklisted %s failed",
                                     eDBattempt_types[type].descr);
                        sqlite_log_error(ctx, blr);
                }
                sqlite_free_results(atpr);
        }
        free_nullsafe(ctx, blid);

        DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i",
              eDBattempt_types[type].descr, val, blacklisted);

        return blacklisted;
}

/**
 * @copydoc eDBregister_attempt()
 */
void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) {
        dbresult *res;
        char *id = NULL, *atmpt_block = NULL, *blid = NULL;
        int attempts = 0;

        DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')",
              eDBattempt_types[type].colname,
              (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"),
              value);

        //
        //  openvpn_attempts
        //
        res = sqlite_query(ctx, 
                           "SELECT atpid, attempts > %s, blid, attempts "
                           "  FROM openvpn_attempts "
                           "  LEFT JOIN openvpn_blacklist USING(%s)"
                           " WHERE %s = %s%s'%q'%s",
                           defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
                                        eDBattempt_types[type].default_value),
                           eDBattempt_types[type].colname,
                           eDBattempt_types[type].colname_where,
                           defaultValue(eDBattempt_types[type].value_func, ""),
                           (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
                           value,
                           (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "")
                           );
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not look up atpid in openvpn_attempts");
                sqlite_log_error(ctx, res);
                sqlite_free_results(res);
                return;
        }

        attempts = atoi_nullsafe(sqlite_get_value(res, 0, 3));
        // If we are asked to reset the attempt counter and we do not find any attempts, exit here
        if( (mode == ATTEMPT_RESET) && ((sqlite_get_numtuples(res) == 0) || (attempts == 0))) {
                sqlite_free_results(res);
                return;
        }

        id = strdup_nullsafe(sqlite_get_value(res, 0, 0));
        atmpt_block = strdup_nullsafe(sqlite_get_value(res, 0, 1));
        blid = strdup_nullsafe(sqlite_get_value(res, 0, 2));

        sqlite_free_results(res);

        if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) {
                // Only insert record when we are in registering mode
                res = sqlite_query(ctx, "INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%q', 1)",
                                   eDBattempt_types[type].colname, value);
        } else if( id != NULL ){
                // if a attempt record exists, update it according to mode
                switch( mode ) {
                case ATTEMPT_RESET:
                        res = sqlite_query(ctx,
                                           "UPDATE openvpn_attempts "
                                           "   SET attempts = 0 "
                                           " WHERE atpid = '%q'", id);
                        break;
                default:
                        res = sqlite_query(ctx,
                                           "UPDATE openvpn_attempts "
                                           "   SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1"
                                           " WHERE atpid = '%q'", id);
                        break;
                }
        }
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_CRITICAL, 0, 
                             "Could not update openvpn_attempts for %s = %s", 
                             eDBattempt_types[type].colname, value);
                sqlite_log_error(ctx, res);
        }
        sqlite_free_results(res);

        //  If attempts have exceeded attempt limit, blacklist it immediately if not already registered
        if( (mode == ATTEMPT_REGISTER) 
            && (blid == NULL) && (atmpt_block != NULL) && (atoi_nullsafe(atmpt_block) > 0) ) {
                eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s",
                             eDBattempt_types[type].descr, value);
                res = sqlite_query(ctx, "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')",
                                   eDBattempt_types[type].colname, value);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_CRITICAL, 0, 
                                     "Could not blacklist %s: %s", eDBattempt_types[type].descr, value);
                        sqlite_log_error(ctx, res);
                }
                sqlite_free_results(res);
        }
        free_nullsafe(ctx, id);
        free_nullsafe(ctx, atmpt_block);
        free_nullsafe(ctx, blid);
}


/**
 * @copydoc eDBregister_login()
 */
int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, const int uid, 
                      const char *proto, const char *remipaddr, const char *remport, 
                      const char *vpnipaddr, const char *vpnipmask) 
{
        dbresult *res = NULL;
        int ret = 0;

        DEBUG(ctx, 20, "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')",
              skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask);
        
        if( skey->sessionstatus != SESSION_NEW ) {
                eurephia_log(ctx, LOG_ERROR, 5, "Not a new session, will not register it again");
                return 1;
        }

        res = sqlite_query(ctx, 
                           "INSERT INTO openvpn_lastlog (uid, certid, "
                           "                             protocol, remotehost, remoteport,"
                           "                             vpnipaddr, vpnipmask,"
                           "                             sessionstatus, sessionkey, login) "
                           "VALUES (%i, %i, '%q','%q','%q','%q','%q', 1,'%q', CURRENT_TIMESTAMP)",
                           uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionkey);
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not insert new session into openvpn_lastlog");
                sqlite_log_error(ctx, res);
                ret = 0;
        } else {
                skey->sessionstatus = SESSION_REGISTERED;
                ret = 1;
        }
        sqlite_free_results(res);

        return ret;
}

/**
 * @copydoc eDBregister_vpnmacaddr()
 */
int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr)
{
        dbresult *res = NULL;
        int ret = 0;

        DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')",
              session->sessionkey, macaddr);

        if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address");
                return 0;
        }

        // Register MAC address into history table
        res = sqlite_query(ctx, "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')",
                           session->sessionkey, macaddr);
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new MAC address for session");
                sqlite_log_error(ctx, res);
                ret = 0;
                goto exit;
        }
        sqlite_free_results(res);

        // Update lastlog to reflect last used MAC address for the session
        res = sqlite_query(ctx, 
                           "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q' "
                           " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                // Save the MAC address in the session values register - needed for the destroy session
                if( eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
                        ret = 0;
                } else {
                        ret = 1;
                }
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new MAC address for session");
                sqlite_log_error(ctx, res);
                ret = 0;
        }

 exit:
        sqlite_free_results(res);
        return ret;
}


/**
 * @copydoc eDBregister_vpnclientaddr()
 */
int eDBregister_vpnclientaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr,
                              const char *vpnip4addr, const char *vpnip6addr)
{
        dbresult *res = NULL;
        int ret = 0;

        DEBUG(ctx, 20, "Function call: eDBregister_vpnclientaddr(ctx, '%s', '%s', '%s', '%s')",
              session->sessionkey, macaddr, vpnip4addr, vpnip6addr);

        if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address");
                return 0;
        }

        // Register client addresses into history table
        res = sqlite_query(ctx,
                           "INSERT INTO openvpn_vpnaddr_history (sessionkey, macaddr, ip4addr, ip6addr) "
                           "VALUES ('%q','%q','%q','%q')",
                           session->sessionkey,
                           (ctx->tuntype == tuntype_TAP ? macaddr : ""),
                           (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""));
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new VPN client addresses for session");
                sqlite_log_error(ctx, res);
                ret = 0;
                goto exit;
        }
        sqlite_free_results(res);

        // Update lastlog to reflect last used MAC address for the session
        res = sqlite_query(ctx,
                           "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q', vpnipaddr = '%q', vpnipv6addr = '%q' "
                           " WHERE sessionkey = '%q' AND sessionstatus = 1",
                           (macaddr ? macaddr : ""), (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""),
                           session->sessionkey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                // TAP mode: Save the MAC address in the session values register - needed for the destroy session
                if( (ctx->tuntype == tuntype_TAP) && eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
                        ret = 0;
                } else {
                        ret = 1;
                }
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new VPN client addresses for session");
                sqlite_log_error(ctx, res);
                ret = 0;
        }

 exit:
        sqlite_free_results(res);
        return ret;
}



/**
 * @copydoc eDBregister_logout()
 */
int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, 
                       const char *bytes_sent, const char *bytes_received, const char *duration)
{
        dbresult *res = NULL;
        int ret = 0;

        DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)",
              skey->sessionkey, bytes_sent, bytes_received);

        res = sqlite_query(ctx, 
                           "UPDATE openvpn_lastlog "
                           "   SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, "
                           "       bytes_sent = '%i', bytes_received = '%i', session_duration = '%i' "
                           " WHERE sessionkey = '%q' AND sessionstatus = 2",
                           atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received),
                           atoi_nullsafe(duration), skey->sessionkey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                skey->sessionstatus = SESSION_LOGGEDOUT;
                ret = 1;
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with logout information (%s)",
                             skey->sessionkey);
                ret = 0;
        }
        sqlite_free_results(res);
        return ret;
}


/**
 * @copydoc eDBget_sessionkey_seed()
 */
char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) {
        dbresult *res = NULL;
        char *skey = NULL;

        DEBUG(ctx, 20, "eDBget_sessionkey_seed(ctx, %i, '%s')", type, sessionseed);

        if( sessionseed == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 1,
                             "eDBget_sessionkey: No session seed given - cannot locate sessionkey");
                return NULL;
        }

        switch( type ) {
        case stSESSION:
                res = sqlite_query(ctx,
                                   "SELECT sessionkey "
                                   "  FROM openvpn_sessionkeys "
                                   "  JOIN openvpn_lastlog USING (sessionkey)"
                                   " WHERE sessionstatus IN (1,2)"
                                   "       AND sessionseed = '%q'",
                                   sessionseed);
                break;

        case stAUTHENTICATION:
                res = sqlite_query(ctx,
                                   "SELECT sessionkey"
                                   "  FROM openvpn_sessionkeys"
                                   "  LEFT JOIN openvpn_lastlog USING(sessionkey)"
                                   " WHERE sessionstatus IS NULL"
                                   "   AND sessionseed = '%q'",
                                   sessionseed);
                break;

        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid session type: %i", type);
                return NULL;
        }

        if( sqlite_query_status(res) == dbSUCCESS ) {
                if( sqlite_get_numtuples(res) == 1 ) {
                        skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
                } else {
                        skey = NULL;
                }
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,"Could not retrieve sessionkey from openvpn_sessionkeys (%s)",
                             sessionseed);
                sqlite_log_error(ctx, res);
                skey = NULL;
        }
        sqlite_free_results(res);
        return skey;
}


/**
 * @copydoc eDBget_sessionkey_macaddr()
 */
char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) {
        dbresult *res = NULL;
        char *skey = NULL;

        DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr);

        // Find sessionkey from MAC address
        res = sqlite_query(ctx,
                           "SELECT sessionkey "
                           "  FROM openvpn_sessions "
                           "  JOIN openvpn_lastlog USING (sessionkey)"
                           " WHERE sessionstatus = 3 "
                           "       AND datakey = 'macaddr'"
                           "       AND dataval = '%q'", macaddr);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                skey = strdup_nullsafe(sqlite_get_value(res, 0, 0));
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Could not remove session from database (MAC addr: %s)", macaddr);
                sqlite_log_error(ctx, res);
                skey = NULL;
        }
        sqlite_free_results(res);

        return skey;
}


/**
 * @copydoc eDBcheck_sessionkey_uniqueness()
 */
int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) {
        dbresult *res;
        int uniq = 0;

        DEBUG(ctx, 20, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey);
        if( seskey == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 1,
                             "eDBcheck_sessionkey_uniqness: Invalid session key given");
                return 0;
        }

        switch( ctx->context_type ) {
        case ECTX_NO_PRIVILEGES:
                return 0;
                break;

        case ECTX_ADMIN_CONSOLE:
        case ECTX_ADMIN_WEB:
                res = sqlite_query(ctx,
                                   "SELECT count(sessionkey) = 0 "
                                   "FROM eurephia_adminlog WHERE sessionkey = '%q'", seskey);
                break;

        case ECTX_PLUGIN_AUTH:
        default:
                res = sqlite_query(ctx,
                                   "SELECT count(sessionkey) = 0 "
                                   "FROM openvpn_lastlog WHERE sessionkey = '%q'", seskey);
                break;
        }

        if( sqlite_query_status(res) == dbSUCCESS ) {
                uniq = atoi_nullsafe(sqlite_get_value(res, 0, 0));
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "eDBcheck_sessionkey_uniqness: Could not check uniqueness of sessionkey");
                sqlite_log_error(ctx, res);
                uniq = 0;
        }
        sqlite_free_results(res);

        return uniq;
}


/**
 * @copydoc eDBregister_sessionkey()
 */
int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) {
        dbresult *res;
        int ret = 0;

        DEBUG(ctx, 20, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey);
        if( (seed == NULL) || (seskey == NULL) ) {
                eurephia_log(ctx, LOG_FATAL, 1,
                             "eDBregister_sessionkey: Invalid session seed or session key given");
                return 0;
        }

        res = sqlite_query(ctx,
                           "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')",
                           seed, seskey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                ret = 1;
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "eDBregister_sessionkey: Error registering sessionkey into openvpn_sessionkeys");
                sqlite_log_error(ctx, res);
                ret = 0;
        }
        sqlite_free_results(res);
        return ret;
}

/**
 * Removes a session key reference to a short time session seed conversion table
 *
 * @param ctx     eurephiaCTX
 * @param seskey  String containing the session key reference to remove
 *
 * @return Returns 1 on success, otherwise 0.
 *
 */
int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) {
        dbresult *res;
        int ret = 0;

        DEBUG(ctx, 20, "eDBremove_sessionkey(ctx, '%s')", seskey);
        if( seskey == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 1,
                             "eDBremove_sessionkey: Invalid session key given");
                return 0;
        }

        res = sqlite_query(ctx, "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", seskey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                ret = 1;
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys");
                ret = 0;
        }
        sqlite_free_results(res);
        return ret;
}

/**
 * @copydoc eDBload_sessiondata()
 */
eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) {
        dbresult *res = NULL;
        eurephiaVALUES *sessvals = NULL;
        int i;

        if( (ctx == NULL) || (sesskey == NULL) ) {
                return NULL;
        }

        DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey);

        sessvals = eCreate_value_space(ctx, 10);

        res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_sessions WHERE sessionkey = '%q'",
                           sesskey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                        eAdd_value(ctx, sessvals, 
                                   sqlite_get_value(res, i, 0), 
                                   sqlite_get_value(res, i, 1));
                }
        } else {
                eurephia_log(ctx, LOG_CRITICAL, 0,
                             "Could not load session values for session '%s'", sesskey);
                sqlite_log_error(ctx,res);
        }
        sqlite_free_results(res);
        return sessvals;
}

/**
 * @copydoc eDBstore_session_value()
 */
int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val) 
{
        dbresult *res = NULL;
        int ret = 0;

        if( session == NULL ) {
                DEBUG(ctx, 20,
                      "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key");
                return 0;
        }

        DEBUG(ctx, 20, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')",
              session->sessionkey, mode, key, val);

        switch( mode ) {
        case SESSVAL_NEW:
                res = sqlite_query(ctx,
                                   "INSERT INTO openvpn_sessions (sessionkey, datakey, dataval) "
                                   "VALUES ('%q','%q','%q')", session->sessionkey, key, val);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_FATAL, 0,
                                     "Could not register new session variable into database: [%s] %s = %s",
                                     session->sessionkey, key, val);
                        sqlite_log_error(ctx, res);
                        goto exit;
                }
                break;

        case SESSVAL_UPDATE:
                res = sqlite_query(ctx,
                                   "UPDATE openvpn_sessions SET dataval = '%q' "
                                   " WHERE sessionkey = '%q' AND datakey = '%q'",
                                   val, session->sessionkey, key);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not update session variable: [%s] %s = %s ",
                                     session->sessionkey, key, val);
                        sqlite_log_error(ctx, res);
                        goto exit;
                }
                break;

        case SESSVAL_DELETE:
                res = sqlite_query(ctx,
                                   "DELETE FROM openvpn_sessions "
                                   " WHERE sessionkey = '%q' AND datakey = '%q'",
                                   session->sessionkey, key);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not delete session variable: [%s] %s",
                                     session->sessionkey, key);
                        sqlite_log_error(ctx, res);
                        goto exit;
                }
                break;

        default:
                eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode);
                return 0;
        }
 exit:
        ret = (sqlite_query_status(res) == dbSUCCESS ? 1 : 0);
        sqlite_free_results(res);
        return ret;
}


/**
 * @copydoc eDBdestroy_session()
 */
int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) {
        dbresult *res = NULL;
        
        DEBUG(ctx, 20, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey);

        if( (session == NULL) || (session->sessionkey == NULL) ) {
                eurephia_log(ctx, LOG_WARNING, 1, "No active session given to be destroyed");
                return 1;
        }

        // Update session status - if we have a "real" session (not auth-session)
        if( session->type == stSESSION ) {
                res = sqlite_query(ctx,
                                   "UPDATE openvpn_lastlog "
                                   "   SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP "
                                   " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey);
                if( sqlite_query_status(res) != dbSUCCESS ) {
                        eurephia_log(ctx, LOG_FATAL, 0,
                                     "Could not update session status in lastlog (%s))", session->sessionkey);
                        sqlite_log_error(ctx, res);
                        sqlite_free_results(res);
                        return 0;
                }
                sqlite_free_results(res);
        }

        // Delete session variables
        res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", session->sessionkey);
        if( sqlite_query_status(res) != dbSUCCESS ) {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Could not delete session variables (%s))", session->sessionkey);
                sqlite_log_error(ctx, res);
                sqlite_free_results(res);
                return 0;
        }
        sqlite_free_results(res);

        // Remove the sessionkey from openvpn_sessions
        if( eDBremove_sessionkey(ctx, session->sessionkey) == 0 ) {
                return 0;
        }
        return 1;
}


/**
 * @copydoc eDBget_firewall_profile()
 */
char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) 
{
        char *ret = NULL;
        dbresult *res = NULL;

        DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')",
              session->sessionkey);

        res = sqlite_query(ctx,
                           "SELECT fw_profile "
                           "  FROM openvpn_lastlog "
                           "  JOIN openvpn_usercerts USING(certid, uid)"
                           "  JOIN openvpn_accesses USING(accessprofile)"
                           " WHERE sessionkey = '%q'", session->sessionkey);
        if( sqlite_query_status(res) == dbSUCCESS ) {
                ret = strdup_nullsafe(sqlite_get_value(res, 0, 0));
        } else {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not retrieve firewall profile for session '%s'",
                             session->sessionkey);
                sqlite_log_error(ctx, res);
                ret = NULL;
        }
        sqlite_free_results(res);
        return ret;
}


/**
 * @copydoc eDBget_blacklisted_ip()
 */
eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) {
        eurephiaVALUES *ret = NULL;
        dbresult *res = NULL;
        int i = 0;
        char *ip = NULL;

        DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)");

        res = sqlite_query(ctx, "SELECT remoteip FROM openvpn_blacklist WHERE remoteip IS NOT NULL");
        if( sqlite_query_status(res) == dbSUCCESS ) {
                ret = eCreate_value_space(ctx, 21);
                for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                        if( (ip = sqlite_get_value(res, i, 0)) != NULL ) {
                                eAdd_value(ctx, ret, NULL, ip);
                        }
                }
        } else {
                eurephia_log(ctx, LOG_FATAL, 0,
                             "Could not retrieve blacklisted IP addresses from the database");
                sqlite_log_error(ctx, res);
                ret = NULL;
        }
        sqlite_free_results(res);

        return ret;
}