/*
 * Copyright (c) 2015 Samsung Electronics Co., Ltd All Rights Reserved
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *        http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
/*
 * @file        sql_query.cpp
 * @author      Janusz Kozerski (j.kozerski@samsung.com)
 * @version     1.0
 * @brief       This file is the implementation of SQL queries
 */

#include <cchecker/dpl/db/sql_connection.h>
#include <cchecker/sql_query.h>
#include <cchecker/log.h>

namespace {

    #define DB_ISSUER      101
    #define DB_URL         102
    #define DB_DATE        103
    #define DB_APP_ID      104
    #define DB_PKG_ID      105
    #define DB_UID         106
    #define DB_CHECK_ID    107
    #define DB_CERTIFICATE 108
    #define DB_VERIFIED    109
    #define DB_CHAIN_ID    110

    // This changes define into question mark and a number in quotes
    // e.g. _(DB_ISSUER) -> "?" "101"
    #define _(db) __(db)
    #define __(db) "?" #db

    // setup
    const char *DB_CMD_SETUP = "VACUUM; PRAGMA foregin_keys=ON;";

    const char *DB_CMD_GET_LAST_INSERTED_ROW = "SELECT last_insert_rowid();";

    // urls
    const char *DB_CMD_GET_URL =
            "SELECT url, date FROM ocsp_urls WHERE issuer = " _(DB_ISSUER) ";";

    const char *DB_CMD_SET_URL =
            "INSERT INTO  ocsp_urls(issuer, url, date) VALUES(" _(DB_ISSUER) ", " _(DB_URL) ", " _(DB_DATE) ");";

    const char *DB_CMD_UPDATE_URL =
            "UPDATE ocsp_urls SET url=" _(DB_URL) ", date=" _(DB_DATE) " WHERE issuer=" _(DB_ISSUER) ";"; // Issuer should be unique

    // apps
    const char *DB_CMD_ADD_APP =
            "INSERT INTO to_check(app_id, pkg_id, uid, verified) VALUES(" _(DB_APP_ID) ", " _(DB_PKG_ID) ", " _(DB_UID) ", " _(DB_VERIFIED) ");";

    const char *DB_CMD_GET_CHECK_ID =
            "SELECT check_id FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _(DB_PKG_ID) " AND uid=" _(DB_UID) ";";

    const char *DB_CMD_ADD_CHAIN =
            "INSERT INTO chains_to_check(check_id) VALUES(" _(DB_CHECK_ID) ");";

    const char *DB_CMD_ADD_CERT =
            "INSERT INTO certs_to_check(chain_id, certificate) VALUES(" _(DB_CHAIN_ID) ", " _(DB_CERTIFICATE) ");";

    const char *DB_CMD_GET_CHAINS =
            "SELECT chain_id FROM chains_to_check INNER JOIN to_check ON chains_to_check.check_id=to_check.check_id WHERE to_check.app_id="
            _(DB_APP_ID) " AND to_check.pkg_id=" _(DB_PKG_ID) " AND to_check.uid=" _(DB_UID) ";";

    const char *DB_CMD_REMOVE_APP =
            "DELETE FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _(DB_PKG_ID) " AND uid=" _(DB_UID) ";";

    const char *DB_CMD_GET_APPS =
            "SELECT app_id, pkg_id, uid, verified FROM to_check";

    const char *DB_CMD_GET_CERTS =
            "SELECT certificate FROM certs_to_check WHERE chain_id=" _(DB_CHAIN_ID) ";";

    const char *DB_CMD_SET_APP_AS_VERIFIED =
            "UPDATE to_check SET verified=" _(DB_VERIFIED) " WHERE check_id=" _(DB_CHECK_ID) ";";
}

namespace CCHECKER {
namespace DB {

SqlQuery::SqlQuery(const std::string& path)
{
    m_connection = NULL;
    m_inUserTransaction = false;

    if (!connect(path))
        throw std::runtime_error("Database error");
}

bool SqlQuery::connect(const std::string& path)
{
    if (m_connection != NULL) {
        LogError("Already connected!");
        return true;
    }

    Try {
        m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW);
        m_connection->ExecCommand(DB_CMD_SETUP);
        return true;
    } Catch(std::bad_alloc) {
        LogError("Couldn't allocate SqlConnection");
    } Catch(SqlConnection::Exception::ConnectionBroken) {
        LogError("Couldn't connect to database: " << path);
    } Catch(SqlConnection::Exception::InvalidColumn) {
        LogError("Couldn't set the key for database");
    } Catch(SqlConnection::Exception::SyntaxError) {
        LogError("Couldn't initiate the database");
    } Catch(SqlConnection::Exception::InternalError) {
        LogError("Couldn't create the database");
    }
    return false;
}

SqlQuery::~SqlQuery()
{
    delete m_connection;
}

bool SqlQuery::get_url(const std::string &issuer, std::string &url)
{
    SqlConnection::DataCommandAutoPtr getUrlCommand =
                    m_connection->PrepareDataCommand(DB_CMD_GET_URL);
    getUrlCommand->BindString(DB_ISSUER, issuer.c_str());

    if (getUrlCommand->Step()) {
        url = getUrlCommand->GetColumnString(0);
        LogDebug("Url for " << issuer << " found in databse: " << url);
        return true;
    }

    LogDebug("No url for " << issuer << " in databse.");
    return false;
}

void SqlQuery::set_url(const std::string &issuer, const std::string &url, const int64_t &date)
{
    m_connection->BeginTransaction();
    SqlConnection::DataCommandAutoPtr getUrlCommand =
                    m_connection->PrepareDataCommand(DB_CMD_GET_URL);
    getUrlCommand->BindString(DB_ISSUER, issuer.c_str());

    if (getUrlCommand->Step()) { // This means that url already exists in database for this issuer
                                 // There's need to check the date
        LogDebug("Url for " << issuer << " already exists. Checking the date");
        int64_t db_date = getUrlCommand->GetColumnInt64(1);
        if (db_date < date) {
            LogDebug("Url for " << issuer << " in database is older. Update is needed");
            // Url in DB is older - update is needed
            SqlConnection::DataCommandAutoPtr updateUrlCommand =
                            m_connection->PrepareDataCommand(DB_CMD_UPDATE_URL);
            updateUrlCommand->BindString(DB_ISSUER, issuer.c_str());
            updateUrlCommand->BindString(DB_URL, url.c_str());
            updateUrlCommand->BindInt64(DB_DATE, date);
            updateUrlCommand->Step();
        } else // Url in DB is up-to-date, no need for update
            LogDebug("Url for " << issuer << " in databse is up-to-date. No update needed");

    } else { // No url in database for this issuer, add the new one
        LogDebug("No url for "<< issuer << " in databse. Adding the new one.");
        SqlConnection::DataCommandAutoPtr setUrlCommand =
                        m_connection->PrepareDataCommand(DB_CMD_SET_URL);
        setUrlCommand->BindString(DB_ISSUER, issuer.c_str());
        setUrlCommand->BindString(DB_URL, url.c_str());
        setUrlCommand->BindInt64(DB_DATE, date);
        setUrlCommand->Step();
    }
    m_connection->CommitTransaction();
}

bool SqlQuery::check_if_app_exists(const app_t &app)
{
    int32_t check_id;
    return get_check_id(app, check_id);
}

bool SqlQuery::get_check_id(const app_t &app, int32_t &check_id)
{
    SqlConnection::DataCommandAutoPtr getCheckIDCommand =
                    m_connection->PrepareDataCommand(DB_CMD_GET_CHECK_ID);
    getCheckIDCommand->BindString(DB_APP_ID, app.app_id.c_str());
    getCheckIDCommand->BindString(DB_PKG_ID, app.pkg_id.c_str());
    getCheckIDCommand->BindInt64(DB_UID, app.uid);
    if (getCheckIDCommand->Step()) {
        check_id = getCheckIDCommand->GetColumnInt32(0);
        LogDebug("Found check id: " << check_id << ", for app: " << app.app_id);
        return true;
    }
    LogDebug("No check_id for app: " << app.app_id << " in database");
    return false;
}

bool SqlQuery::add_chain_id(const int32_t check_id, int32_t &chain_id)
{
    // Add new chain for an app
    SqlConnection::DataCommandAutoPtr addChainCommand =
            m_connection->PrepareDataCommand(DB_CMD_ADD_CHAIN);
    addChainCommand->BindInt32(DB_CHECK_ID, check_id);
    addChainCommand->Step();

    // get chain_id
    SqlConnection::DataCommandAutoPtr getLastInserted =
            m_connection->PrepareDataCommand(DB_CMD_GET_LAST_INSERTED_ROW);
    if(getLastInserted->Step()) {
        chain_id = getLastInserted->GetColumnInt32(0);
        LogDebug("Found chain_id: " << chain_id << ", for check_id " << check_id);
        return true;
    }
    return false;
}

bool SqlQuery::add_app_to_check_list(const app_t &app)
{
    //Check if app exists in DB
    if (check_if_app_exists(app)) {
        LogDebug(app.str() << " already exists in database");
        return true;
    }

    m_connection->BeginTransaction();
    //Add app to to_check table
    SqlConnection::DataCommandAutoPtr addAppCommand =
            m_connection->PrepareDataCommand(DB_CMD_ADD_APP);
    addAppCommand->BindString(DB_APP_ID, app.app_id.c_str());
    addAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str());
    addAppCommand->BindInt64(DB_UID, app.uid);
    addAppCommand->BindInt32(DB_VERIFIED, static_cast<int32_t>(app_t::verified_t::UNKNOWN));  // Set app as not-verified
    addAppCommand->Step();
    LogDebug("App " << app.app_id << " added to to_check table, adding certificates.");

    // Get check_id
    int32_t check_id;
    if (!get_check_id(app, check_id)) { // If get check_id failed return false;
        LogDebug("Failed while addind app "<< app.app_id << " to to_check table.");
        m_connection->RollbackTransaction();
        return false;
    }

    // If get check_id succeed we can add chain to database
    int32_t chain_id;
    for (const auto &iter : app.signatures) {
        // Add chain
        if (add_chain_id(check_id, chain_id)) {
            // add certificates from chain
            for (const auto &iter_cert : iter) {
                SqlConnection::DataCommandAutoPtr addCertCommand =
                        m_connection->PrepareDataCommand(DB_CMD_ADD_CERT);
                addCertCommand->BindInt32(DB_CHAIN_ID, chain_id);
                addCertCommand->BindString(DB_CERTIFICATE, iter_cert.c_str());
                addCertCommand->Step();
                LogDebug("Certificate for app " << app.app_id << "added");
            }
        } else {
            LogDebug("Failed to add certificates chain");
            m_connection->RollbackTransaction();
            return false;
        }

     }
     m_connection->CommitTransaction();
     return true;
}

void SqlQuery::remove_app_from_check_list(const app_t &app)
{
    LogDebug("Removing app: " << app.str());

    //Remove app from to_check table
    SqlConnection::DataCommandAutoPtr removeAppCommand =
            m_connection->PrepareDataCommand(DB_CMD_REMOVE_APP);
    removeAppCommand->BindString(DB_APP_ID, app.app_id.c_str());
    removeAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str());
    removeAppCommand->BindInt32(DB_UID, app.uid);
    removeAppCommand->Step();
    LogDebug("Removed app: " << app.str());

    // Removing certificates should be done automatically by DB because of
    // ON DELETE CASCADE for check_id
}

void SqlQuery::mark_as_verified(const app_t &app, const app_t::verified_t &verified)
{
    int32_t check_id;

    if (get_check_id(app, check_id)) {
        SqlConnection::DataCommandAutoPtr setVerifiedCommand =
                        m_connection->PrepareDataCommand(DB_CMD_SET_APP_AS_VERIFIED);
        setVerifiedCommand->BindInt32(DB_CHECK_ID, check_id);
        setVerifiedCommand->BindInt32(DB_VERIFIED, static_cast<int32_t>(verified));
        setVerifiedCommand->Step();
        LogDebug("App: " << app.str() << " marked as verified: " << static_cast<int32_t>(verified));
    }
}

void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
{
    // This function will fill buffer with check_id, app_id, pkg_id, uid and verified fields -
    // it leaves certificates' list empty.
    SqlConnection::DataCommandAutoPtr getAppsCommand =
                   m_connection->PrepareDataCommand(DB_CMD_GET_APPS);

    while (getAppsCommand->Step()) {
        app_t app;
        app.app_id   = getAppsCommand->GetColumnString(0);
        app.pkg_id   = getAppsCommand->GetColumnString(1);
        app.uid      = getAppsCommand->GetColumnInt64(2);
        app.verified = static_cast<app_t::verified_t>(getAppsCommand->GetColumnInt32(3));
        app.signatures = {};
        LogDebug("App read from DB: app_id: " << app.str() << ", verified: " << static_cast<int32_t>(app.verified));
        apps_buffer.push_back(app);
    }
}

void SqlQuery::get_app_list(std::list<app_t> &apps_buffer)
{
    m_connection->BeginTransaction();
    get_apps(apps_buffer);

    // Get chain for every application
    for (auto &iter_app : apps_buffer) {
        SqlConnection::DataCommandAutoPtr getChainsCommand =
                m_connection->PrepareDataCommand(DB_CMD_GET_CHAINS);
        getChainsCommand->BindString(DB_APP_ID, iter_app.app_id.c_str());
        getChainsCommand->BindString(DB_PKG_ID, iter_app.pkg_id.c_str());
        getChainsCommand->BindInt32(DB_UID, iter_app.uid);

        // Get all certs from chain
        while (getChainsCommand->Step()) {
            chain_t chain;
            int32_t chain_id;
            chain_id = getChainsCommand->GetColumnInt32(0);
            LogDebug("Found chain (" << chain_id << ") for " << iter_app.str());

            SqlConnection::DataCommandAutoPtr getCertsCommand =
                    m_connection->PrepareDataCommand(DB_CMD_GET_CERTS);
            getCertsCommand->BindInt32(DB_CHAIN_ID, chain_id);

            // Add found certs to chain
            while (getCertsCommand->Step()) {
                chain.push_back(getCertsCommand->GetColumnString(0));
                LogDebug("Found certificate (" << chain.size() << ") in chain no. " << chain_id);
            }
            iter_app.signatures.push_back(chain);
        }
    }
    m_connection->CommitTransaction();
}

} // DB
} // CCHECKER