//
// Created by mwo on 15/06/18.
//

#include "../src/MicroCore.h"
#include "../src/OpenMoneroRequests.h"
#include "../src/db/MysqlPing.h"

//#include "chaingen.h"
//#include "chaingen_tests_list.h"

#include "gmock/gmock.h"
#include "gtest/gtest.h"
#include "../src/ThreadRAII.h"

#include "helpers.h"

namespace
{


using json = nlohmann::json;
using namespace std;
using namespace mysqlpp;
using namespace cryptonote;
using namespace epee::string_tools;
using namespace std::chrono_literals;

using ::testing::AllOf;
using ::testing::Ge;
using ::testing::Le;


json
readin_config()
{
    // read in confing json file and get test db info

    std::string config_json_path{"../config/config.json"};

    // check if config-file provided exist
    if (!boost::filesystem::exists(config_json_path))
    {
        std::cerr << "Config file " << config_json_path
                  << " does not exist\n";

        return {};
    }

    json config_json;

    try
    {
        // try reading and parsing json config file provided
        std::ifstream i(config_json_path);
        i >> config_json;

        return config_json["database_test"];
    }
    catch (const std::exception &e)
    {
        std::cerr << "Error reading confing file "
                  << config_json_path << ": "
                  << e.what() << '\n';
        return {};
    }

    return {};
}

shared_ptr<xmreg::MySqlConnector>
make_connection()
{
    json db_config = readin_config();

    if (db_config.empty())
    {
        cerr << "Cant readin_config()";
        return nullptr;
    }

    xmreg::MySqlConnector::url = db_config["url"];
    xmreg::MySqlConnector::port = db_config["port"];
    xmreg::MySqlConnector::username = db_config["user"];
    xmreg::MySqlConnector::password = db_config["password"];
    xmreg::MySqlConnector::dbname = db_config["dbname"];

    return make_shared<xmreg::MySqlConnector>();
}

TEST(MYSQL_CONNECTION, CantConnect)
{
    // we did specify wrong mysql details so this should throw.

    xmreg::MySqlConnector::url = "127.0.0.1";
    xmreg::MySqlConnector::port = 3306;
    xmreg::MySqlConnector::username = "wrong_user";
    xmreg::MySqlConnector::password = "wrong_pass";
    xmreg::MySqlConnector::dbname = "wrong_name";

    try
    {
        auto xmr_accounts = std::make_shared<xmreg::MySqlAccounts>(nullptr);
    }
    catch(...) {
        EXPECT_TRUE(true);
        return;
    }

    FAIL() << "Should have thrown exception";
}


TEST(MYSQL_CONNECTION, CanConnect)
{
    // we did specify wrong mysql details so this should throw.

    json db_config = readin_config();

    if (db_config.empty())
        FAIL() << "Cant read in_config()";

    xmreg::MySqlConnector::url = db_config["url"];
    xmreg::MySqlConnector::port = db_config["port"];
    xmreg::MySqlConnector::username = db_config["user"];
    xmreg::MySqlConnector::password = db_config["password"];
    xmreg::MySqlConnector::dbname = db_config["dbname"];


    try
    {
        auto xmr_accounts = std::make_shared<xmreg::MySqlAccounts>(nullptr);

        // try to connect again
        // it should not perform the connection again, but just return true;
        EXPECT_TRUE(xmr_accounts->get_connection()->connect());
    }
    catch(std::exception const& e)
    {
        FAIL();
    }

    EXPECT_TRUE(true);
}


/**
* Fixture that connects to openmonero_test database
* and repopulates it with known data for each test.
*/
class MYSQL_TEST : public ::testing::Test
{
public:

    static void
    SetUpTestCase()
    {

        db_config = readin_config();

        if (db_config.empty())
            FAIL() << "Cant read in_config()";

        xmreg::MySqlConnector::url = db_config["url"];
        xmreg::MySqlConnector::port = db_config["port"];
        xmreg::MySqlConnector::username = db_config["user"];
        xmreg::MySqlConnector::password = db_config["password"];
        xmreg::MySqlConnector::dbname = db_config["dbname"];

        db_data = xmreg::read("../sql/openmonero_test.sql");
    }

protected:

    virtual bool
    connect(std::shared_ptr<xmreg::CurrentBlockchainStatus> _bc_status)
    {
        try
        {
            auto conn = std::make_shared<xmreg::MySqlConnector>(
                    new mysqlpp::MultiStatementsOption(true));

            // MySqlAccounts will try connecting to the mysql database
            xmr_accounts = std::make_shared<xmreg::MySqlAccounts>(_bc_status, conn);
        }
        catch (std::exception const &e)
        {
            std::cerr << e.what() << '\n';
            return false;
        }

        return xmr_accounts->get_connection()->get_connection().connected();
    }


    virtual void
    initDatabase()
    {
        mysqlpp::Query query = xmr_accounts
                ->get_connection()->get_connection().query(db_data);

        query.parse();

        try
        {
            ASSERT_TRUE(query.exec());

            while(query.more_results())
                query.store_next();
        }
        catch (std::exception &e)
        {
            std::cerr << e.what() << '\n';
            throw e;
        }
    }


    virtual void
    SetUp()
    {
        current_bc_status = nullptr;
        connect(current_bc_status);
        initDatabase();
    }

    virtual void
    TearDown()
    {
        xmr_accounts->disconnect();
    }

    static std::string db_data;
    static json db_config;
    std::shared_ptr<xmreg::MySqlAccounts> xmr_accounts;
    std::shared_ptr<xmreg::CurrentBlockchainStatus> current_bc_status;
};

std::string MYSQL_TEST::db_data;
json MYSQL_TEST::db_config;


TEST_F(MYSQL_TEST, Connection)
{
    EXPECT_TRUE(xmr_accounts != nullptr);
    EXPECT_TRUE(xmr_accounts->get_connection()->get_connection().connected());
    EXPECT_TRUE(xmr_accounts->get_connection()->ping());
}


TEST_F(MYSQL_TEST, Disconnection)
{
    xmr_accounts->disconnect();

    EXPECT_FALSE(xmr_accounts->get_connection()->get_connection().connected());
    EXPECT_FALSE(xmr_accounts->get_connection()->ping());
}

string addr_57H_hex {"57Hx8QpLUSMjhgoCNkvJ2Ch91mVyxcffESCprnRPrtbphMCv8iGUEfCUJxrpUWUeWrS9vPWnFrnMmTwnFpSKJrSKNuaXc5q"};
// viewkey: 9595c2445cdd4c88d78f0af41ebdf52f68ae2e3597b9e7b99bc3d62e300df806



TEST_F(MYSQL_TEST, GetAccount)
{
    ACC_FROM_HEX(addr_57H_hex);

    EXPECT_EQ(acc.id.data, 129);
    EXPECT_EQ(acc.scanned_block_height, 101610);
    EXPECT_EQ(acc.viewkey_hash, "1acf92d12101afe2ce7392169a38d2d547bd042373148eaaab323a3b5185a9ba");

    // try get data when disconnected
    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->select(addr_57H_hex, acc));
}


TEST_F(MYSQL_TEST, UpdateAccount)
{
    ACC_FROM_HEX(addr_57H_hex);

    // make copy of the orginal account so that we can update it easly
    auto updated_account = acc;

    updated_account.scanned_block_height = 555;
    updated_account.scanned_block_timestamp
            = DateTime(static_cast<time_t>(5555555));

    EXPECT_TRUE(xmr_accounts->update(acc, updated_account));

    // fetch the account data and check if it was updated
    xmreg::XmrAccount account2;

    ASSERT_TRUE(xmr_accounts->select(addr_57H_hex, account2));

    EXPECT_EQ(account2.id.data, acc.id.data);
    EXPECT_EQ(account2.scanned_block_height, 555);
    EXPECT_EQ(account2.scanned_block_timestamp, 5555555);

    // try ding same but when disconnected
    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->update(acc, updated_account));
}

TEST_F(MYSQL_TEST, InsertAndGetAccount)
{

    uint64_t mock_current_blockchain_height = 452145;
    uint64_t mock_current_blockchain_timestamp = 1529302789;

    DateTime blk_timestamp_mysql_format
            = mysqlpp::DateTime(static_cast<time_t>(
                                    mock_current_blockchain_timestamp));

    // address to insert
    string xmr_addr {"4AKNvHrLG6KKtKYRaSPWtSZSnAcaBZ2UzeJg7guFNFK46EN93FnBDS5eiFgxH87Sb7ZcSFxMyBhhgKBJiG5kKBBmCY5tbgw"};
    string view_key {"f359631075708155cc3d92a32b75a7d02a5dcf27756707b47a2b31b21c389501"};
    string view_key_hash {"cdd3ae89cbdae1d14b178c7e7c6ba380630556cb9892bd24eb61a9a517e478cd"};


    uint64_t expected_primary_id
            = xmr_accounts->get_next_primary_id(xmreg::XmrAccount());

    xmreg::XmrAccount new_account(mysqlpp::null,
                                  xmr_addr,
                                  view_key_hash,
                                  mock_current_blockchain_height, /* for scanned_block_height */
                                  blk_timestamp_mysql_format,
                                  mock_current_blockchain_height);

    int64_t acc_id = xmr_accounts->insert(new_account);

    EXPECT_EQ(acc_id, expected_primary_id);

    xmreg::XmrAccount acc;

    bool is_success = xmr_accounts->select(xmr_addr, acc);

    ASSERT_TRUE(is_success);

    EXPECT_EQ(acc.id.data, expected_primary_id);
    EXPECT_EQ(acc.scanned_block_height, mock_current_blockchain_height);
    EXPECT_EQ(acc.scanned_block_timestamp, mock_current_blockchain_timestamp);
    EXPECT_EQ(acc.viewkey_hash, view_key_hash);

    // now try inserting same account. it should fail
    acc_id = xmr_accounts->insert(new_account);

    EXPECT_EQ(acc_id, 0);

    // try doing same but when disconnected
    xmr_accounts->disconnect();

    acc_id = xmr_accounts->insert(new_account);
    EXPECT_EQ(acc_id, 0);
}

TEST_F(MYSQL_TEST, GetNextPrimaryIdFailure)
{
    xmr_accounts->disconnect();

    EXPECT_EQ(xmr_accounts->get_next_primary_id(xmreg::XmrAccount()), 0);
}

// stagenet tx: 4b40cfb2fdce2cd57a834a380901d55d70aba29dad13ac6c4dc82a895f439ecf
const string tx_4b40_hex {"[encoded data, 26504 characters]"};


TEST_F(MYSQL_TEST, SelectSingleTx)
{
    TX_AND_ACC_FROM_HEX(tx_4b40_hex, addr_57H_hex);

    xmreg::XmrTransaction mysql_tx;
    xmr_accounts->tx_exists(acc.id.data, tx_hash_str, mysql_tx);

    EXPECT_EQ(mysql_tx.hash       , tx_hash_str);
    EXPECT_EQ(mysql_tx.prefix_hash, tx_prefix_hash_str);
    EXPECT_EQ(mysql_tx.total_received, 0);
    EXPECT_EQ(mysql_tx.total_sent, 100000000000000);
    EXPECT_EQ(mysql_tx.blockchain_tx_id, 93830);
    EXPECT_TRUE(static_cast<int>(mysql_tx.spendable) == 1);
    EXPECT_TRUE(static_cast<int>(mysql_tx.coinbase) == 0);
    EXPECT_TRUE(static_cast<int>(mysql_tx.is_rct) == 1);
    EXPECT_TRUE(static_cast<int>(mysql_tx.rct_type) == 1);
    EXPECT_EQ(mysql_tx.payment_id, string{});
    EXPECT_EQ(mysql_tx.mixin, 8);


    // try doing same but when disconnected
    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, mysql_tx));
}

// existing address
string owner_addr_5Ajfk {"5AjfkEY7RFgNGDYvoRQkncfwHXT6Fh7oJBisqFUX5u96i3ZepxDPocQK29tmAwBDuvKRpskZnfA6N8Ra58qFzA4bSA3QZFp"};
// stagenet wallet: boxes smash cajun fences ouch hamburger heron pulp  initiate hubcaps academy acumen weird pliers powder jive soil tissue skydive bygones nobody sifting novelty ounce sifting
// the list of txs containing our outputs and also when used in key images
// ./xmr2csv --stagenet -m -a 5AjfkEY7RFgNGDYvoRQkncfwHXT6Fh7oJBisqFUX5u96i3ZepxDPocQK29tmAwBDuvKRpskZnfA6N8Ra58qFzA4bSA3QZFp -v 3ee772709dcf834a881c432fc15625d84585e4462d7905c42460dd478a315008 -t 100000  -g 101610

// Txs associates with the stagenet address and viewkey are, based on the xmr2csv csv files

//    Block_no	Tx_hash
//    100938	efa653785fd536ec42283985666612eca961a0bf6a8d56c4c43b1027d173a32c
//    100939	00458128c40886b22d15cbf3c02fcbb1a0860cab654d81230dba216b50fe887d
//    100943	fc619eeccfa0626f4be78cb1002a232e0ae8c8be6826f800341977d23a5a8e1e
//    100943	9ba6fa1c6f0277651e38b9f076a2cf674d92d683beac42ad93db1f3cc429cffe
//    100948	da22b85e51644b7c5df30f65b33f4f00bb58278e8189f9073e0573eb6df1fc1f
//    100949	3fb5d474378431bfa1b01e61965dcd5a62e1753cb7b3064ba7adb98bcfd398cd
//    100964	ac715b386010bd95a506fde0ac0405aa8ad72080b1d7cef257b5b112d9ed84bb
//    100968	9bfe477f2df750dd31b2a3939b32a5017323586a11bad6cd81650cfd2d54113d
//    100985	380ff7683774c989c1fd7348b90cd37b76d68ee6f93bd5239ecc3bb3794030bf
//    100993	8901400e2cac9fa26a47a06d76c8c0ee499ff691df9d22bad2a0183943532040
//    101028	6e1368cd8c48636ee7a327c165fae95496a7098191398c950fe26031246287f1
//    101048	c3004a573547e5cd671c79a6f33f7907e2e6193d4abe47a3c5328f533b3d6cdc
//    101081	fc4b8d5956b30dc4a353b171b4d974697dfc32730778f138a8e7f16c11907691
//    101085	ad3f3d9ff1e2dd2145067950d68d24cbd53db6f9d771f0d275c33fd88e458cf0
//    101553	dcb792bba1da7a9872f9c36772ee8625282b1f144380288fbd8acb968f6de56a
//    101554	c8965d4f54de1e39033b07e88bb20cacaa725a0dc266444e2efde6f624b9245d

TEST_F(MYSQL_TEST, SelectAllTxsForAnAccount)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    EXPECT_TRUE(xmr_accounts->select(acc.id.data, txs));

    EXPECT_EQ(txs.size(), 16);
    EXPECT_EQ(txs[0].hash    , string{"efa653785fd536ec42283985666612eca961a0bf6a8d56c4c43b1027d173a32c"});
    EXPECT_EQ(txs.back().hash, string{"c8965d4f54de1e39033b07e88bb20cacaa725a0dc266444e2efde6f624b9245d"});


    // try ding same but when disconnected
    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->select(acc.id.data, txs));

}

// stagenet fc4b8d5956b30dc4a353b171b4d974697dfc32730778f138a8e7f16c11907691
string tx_fc4_hex {"[encoded data, 26376 characters]"};


// for different account we can have same tx in the Transaction table
string addres_of_different_acc {"57hGLsqr6eLjUDoqWwP3Ko9nCJ4GFN5AyezdxNXwpa1PMt6M4AbsBgcHH21hVe2MJrLGSM9C7UTqcEmyBepdhvFE4eyW3Kd"};


// seed: fall lava tudor nucleus hemlock afar tuition poaching waffle palace roped nifty wipeout fierce mystery thumbs hubcaps toffee maps etiquette jolted symptoms winter abyss fall
string addr_55Zb {"55ZbQdMnZHPFS8pmrhHN5jMpgJwnnTXpTDmmM5wkrBBx4xD6aEnpZq7dPkeDeWs67TV9HunDQtT3qF2UGYWzGGxq3zYWCBE"};


TEST_F(MYSQL_TEST, TryInsertingExistingTxToSameAccount)
{
    // we should not be able to insert same tx twice for the same account
    TX_AND_ACC_FROM_HEX(tx_fc4_hex, owner_addr_5Ajfk);

    xmreg::XmrTransaction tx_data;

    tx_data.hash             = tx_hash_str;
    tx_data.account_id       = acc.id.data;
    // rest of fields is not important

    uint64_t tx_mysql_id = xmr_accounts->insert(tx_data);

    // if zero than the insert failed
    EXPECT_EQ(tx_mysql_id, 0);
}

TEST_F(MYSQL_TEST, TryInsertingExistingTxToDifferentAccount)
{
    // we should  be able to insert same tx to different account
    // even though it exisits already for other account

    TX_AND_ACC_FROM_HEX(tx_fc4_hex, addres_of_different_acc);

    xmreg::XmrTransaction tx_data;

    tx_data.id               = mysqlpp::null;
    tx_data.hash             = tx_hash_str;
    tx_data.account_id       = acc.id.data;
    // rest of fields is not important

    uint64_t expected_primary_id = xmr_accounts->get_next_primary_id(tx_data);

    uint64_t tx_mysql_id = xmr_accounts->insert(tx_data);

    EXPECT_EQ(tx_mysql_id, expected_primary_id);
}


TEST_F(MYSQL_TEST, IfTxExistsForInOnwnerAccount)
{
    TX_AND_ACC_FROM_HEX(tx_fc4_hex, owner_addr_5Ajfk);

    xmreg::XmrTransaction tx_data;

    EXPECT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));
}


TEST_F(MYSQL_TEST, IfTxExistsForInNoneOnwnerAccount)
{
    TX_AND_ACC_FROM_HEX(tx_fc4_hex, addres_of_different_acc);

    xmreg::XmrTransaction tx_data;

    EXPECT_FALSE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));
}


TEST_F(MYSQL_TEST, DeleteExistingTx)
{
    // tx hash fc4b8d5956b30dc4a353b171b4d974697dfc32730778f138a8e7f16c11907691
    TX_AND_ACC_FROM_HEX(tx_fc4_hex, owner_addr_5Ajfk);

    xmreg::XmrTransaction tx_data;

    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    uint64_t no_of_deleted_rows = xmr_accounts->delete_tx(tx_data.id.data);

    EXPECT_EQ(no_of_deleted_rows, 1);

    // try ding same but when disconnected
    xmr_accounts->disconnect();
    EXPECT_EQ(xmr_accounts->delete_tx(tx_data.id.data), 0);
}

string tx_1640_hex {"[encoded data, 27626 characters]"};
// tx hash 1640236fe817f83b0bd2082c655d152be390e4f78e41db5b935bb8a53249fe8c

TEST_F(MYSQL_TEST, DeleteExistingTxAndAssociatedOutputsAndInputs)
{
    // removing a tx should automatically remove its outputs and inputs
    // from mysql. we have constrans doing this, so we check here if
    // they work as we think they do

    TX_AND_ACC_FROM_HEX(tx_1640_hex, addr_57H_hex)

    xmreg::XmrTransaction tx_data;

    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    uint64_t no_of_deleted_rows = xmr_accounts->delete_tx(tx_data.id.data);

    ASSERT_EQ(no_of_deleted_rows, 1);

    // this tx has 1 output and two key images.
    //
    // output: a9d876b01eb972db944b78899b4c90c2b66a3c81fe04bf54ff61565f3db53419
    // key1: 45d3cf7b4f5db9d614602e9e956c63c35cf3da6f7b93740c514e06c898fb0da2
    // key2: 3ef54482411c54a280081a87fd35e542b3b8906b702123c55468e71dd620d3a4

    // so we expect those to be also deleted after tx has been deleted


    vector<xmreg::XmrOutput> outputs;

    bool is_success = xmr_accounts->select_for_tx(tx_data.id.data, outputs);

    // if no outputs found, we return false
    EXPECT_FALSE(is_success);
    EXPECT_EQ(outputs.size(), 0);

    vector<xmreg::XmrInput> inputs;

    is_success = xmr_accounts->select_for_tx(tx_data.id.data, inputs);

    // if no inputs found, we return false
    EXPECT_FALSE(is_success);
    EXPECT_EQ(inputs.size(), 0);
}

TEST_F(MYSQL_TEST, DeleteNoNExistingTx)
{
    uint64_t some_non_existing_tx_id = 7774748483;

    uint64_t no_of_deleted_rows
            = xmr_accounts->delete_tx(some_non_existing_tx_id);

    EXPECT_EQ(no_of_deleted_rows, 0);
}


TEST_F(MYSQL_TEST, MarkTxSpendableAndNonSpendable)
{
    TX_AND_ACC_FROM_HEX(tx_fc4_hex, owner_addr_5Ajfk);

    xmreg::XmrTransaction tx_data;


    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    // this particular tx is marked as spendable in mysql
    EXPECT_TRUE(static_cast<bool>(tx_data.spendable));

    uint64_t no_of_changed_rows
            = xmr_accounts->mark_tx_nonspendable(tx_data.id.data);

    EXPECT_EQ(no_of_changed_rows, 1);

    // fetch tx_data again and check if its not-spendable now
    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    EXPECT_FALSE(static_cast<bool>(tx_data.spendable));

    // mark it as spendable
    no_of_changed_rows = xmr_accounts->mark_tx_spendable(tx_data.id.data);

    EXPECT_EQ(no_of_changed_rows, 1);

    // fetch it again, and check if its spendable
    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    EXPECT_TRUE(static_cast<bool>(tx_data.spendable));

    // try ding same but when disconnected
    xmr_accounts->disconnect();
    EXPECT_EQ(xmr_accounts->mark_tx_spendable(tx_data.id.data), 0);
}

TEST_F(MYSQL_TEST, GetTotalRecievedByAnAddressWhenDisconnected)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    uint64_t total_recieved;

    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->get_total_recieved(acc.id.data, total_recieved));
}

// now we can test total number of incoming xmr (only outputs)
// for serveral addresses. For this we can use
// parametrized fixture

                                          // addr, expected recieved xmr
using address_incoming_balance = std::pair<string, uint64_t>;

class MYSQL_TEST_PARAM :
        public MYSQL_TEST,
        public ::testing::WithParamInterface<address_incoming_balance>
{};

TEST_P(MYSQL_TEST_PARAM, GetTotalRecievedByAnAddress)
{
    auto const& own_addr          = GetParam().first;
    auto const& expected_recieved = GetParam().second;

    ACC_FROM_HEX(own_addr);

    uint64_t total_recieved;

    EXPECT_TRUE(xmr_accounts->get_total_recieved(acc.id.data, total_recieved));

    EXPECT_EQ(total_recieved, expected_recieved);
}

INSTANTIATE_TEST_CASE_P(
        RecivedXmrTest, MYSQL_TEST_PARAM,
                ::testing::Values(
                        make_pair(owner_addr_5Ajfk, 697348926585540ull),
                        make_pair(addr_55Zb       , 1046498996045077ull)
                ));


auto
make_mock_output_data(string last_char_pub_key = "4")
{
    xmreg::XmrOutput mock_output_data ;

    mock_output_data.id           = mysqlpp::null;
    // mock_output_data.account_id   = acc.id; need to be set when used
    mock_output_data.tx_id        = 106086; // some tx id for this output
    mock_output_data.out_pub_key  = "18c6a80311d6f455ac1e5abdce7e86828d1ecf911f78da12a56ce8fdd5c716f"
                                    + last_char_pub_key; // out_pub_key is unique field, so to make
                                                         // few public keys, we just change its last char.
    mock_output_data.tx_pub_key   = "38ae1d790bce890c3750b20ba8d35b8edee439fc8fb4218d50cec39a0cb7844a";
    mock_output_data.amount       = 999916984840000ull;
    mock_output_data.out_index    = 1;
    mock_output_data.rct_outpk    = "e17cdc23fac1d92f2de196b567c8dd55ecd4cac52d6fef4eb446b6de4edb1d01";
    mock_output_data.rct_mask     = "03cea1ffc18193639f7432287432c058a70551ceebed0db2c9d18088b423a255";
    mock_output_data.rct_amount   = "f02e6d9dd504e6b428170d37b79344cad5538a4ad32f3f7dcebd5b96ac522e07";
    mock_output_data.global_index = 64916;
    mock_output_data.mixin        = 7;
    mock_output_data.timestamp    = mysqlpp::DateTime(static_cast<time_t>(44434554));;

    return mock_output_data;
}

TEST_F(MYSQL_TEST, SelectOutputsForAccount)
{
    // select all outputs associated with the given account

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrOutput> outputs;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, outputs));

    // can use this command to get list of outputs
    // ./xmr2csv --stagenet -m -a 5AjfkEY7RFgNGDYvoRQkncfwHXT6Fh7oJBisqFUX5u96i3ZepxDPocQK29tmAwBDuvKRpskZnfA6N8Ra58qFzA4bSA3QZFp -v 3ee772709dcf834a881c432fc15625d84585e4462d7905c42460dd478a315008 -t 90000 -g 101610
    EXPECT_EQ(outputs.size(), 9);
}

TEST_F(MYSQL_TEST, SelectOutputsForTransaction)
{
    // select all outputs associated with given transaction

    // tx hash 1640236fe817f83b0bd2082c655d152be390e4f78e41db5b935bb8a53249fe8c
    TX_AND_ACC_FROM_HEX(tx_1640_hex, addr_57H_hex)

    xmreg::XmrTransaction tx_data;

    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    vector<xmreg::XmrOutput> outputs;

    bool is_success = xmr_accounts->select_for_tx(tx_data.id.data, outputs);

    ASSERT_TRUE(is_success);

    EXPECT_EQ(outputs.size(), 1);
    EXPECT_EQ(outputs[0].out_pub_key, "a9d876b01eb972db944b78899b4c90c2b66a3c81fe04bf54ff61565f3db53419");


    // now use output_exists

    xmreg::XmrOutput out;

    EXPECT_TRUE(xmr_accounts->output_exists(outputs[0].out_pub_key, out));

    EXPECT_EQ(outputs[0], out);

    // use output_exists on non-exisiting output

    string non_exist_key {"a9d876b01eb972db944b78899b4c90c2b66a3c81fe04bf54ff61565f3db53000"};

    EXPECT_FALSE(xmr_accounts->output_exists(non_exist_key, out));

    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->output_exists(non_exist_key, out));

}

TEST_F(MYSQL_TEST, InsertOneOutput)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    xmreg::XmrOutput mock_output_data = make_mock_output_data();

    mock_output_data.account_id = acc.id.data;

    uint64_t expected_primary_id
            = xmr_accounts->get_next_primary_id(mock_output_data);
    uint64_t inserted_output_id = xmr_accounts->insert(mock_output_data);

    EXPECT_EQ(inserted_output_id, expected_primary_id);

    // now we fetch the inserted output and compare its values

    xmreg::XmrOutput out_data2;

    EXPECT_TRUE(xmr_accounts
                ->select_by_primary_id(inserted_output_id, out_data2));

    EXPECT_EQ(out_data2.tx_id, mock_output_data.tx_id);
    EXPECT_EQ(out_data2.out_pub_key, mock_output_data.out_pub_key);
    EXPECT_EQ(out_data2.tx_pub_key, mock_output_data.tx_pub_key);
    EXPECT_EQ(out_data2.amount, mock_output_data.amount);
    EXPECT_EQ(out_data2.out_index, mock_output_data.out_index);
    EXPECT_EQ(out_data2.rct_outpk, mock_output_data.rct_outpk);
    EXPECT_EQ(out_data2.rct_mask, mock_output_data.rct_mask);
    EXPECT_EQ(out_data2.rct_amount, mock_output_data.rct_amount);
    EXPECT_EQ(out_data2.global_index, mock_output_data.global_index);
    EXPECT_EQ(out_data2.mixin, mock_output_data.mixin);
    EXPECT_EQ(out_data2.timestamp, mock_output_data.timestamp);

    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->select_by_primary_id(inserted_output_id, out_data2));
}


TEST_F(MYSQL_TEST, TryToInsertSameOutputTwice)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    xmreg::XmrOutput mock_output_data = make_mock_output_data();

    mock_output_data.account_id = acc.id.data;

    // first time insert should be fine
    uint64_t inserted_output_id = xmr_accounts->insert(mock_output_data);

    EXPECT_GT(inserted_output_id, 0);

    // second insert should fail and result in 0
    inserted_output_id = xmr_accounts->insert(mock_output_data);

    EXPECT_EQ(inserted_output_id, 0);
}

TEST_F(MYSQL_TEST, TryToInsertSameOutputTwiceToDifferent)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    xmreg::XmrOutput mock_output_data = make_mock_output_data();

    mock_output_data.account_id = acc.id.data;

    // first time insert should be fine
    uint64_t inserted_output_id = xmr_accounts->insert(mock_output_data);

    EXPECT_GT(inserted_output_id, 0);

    xmreg::XmrAccount acc2;                                                 \
    ASSERT_TRUE(xmr_accounts->select(addr_55Zb, acc2));

    mock_output_data.account_id = acc2.id.data;

    // second insert should fail and result in 0
    inserted_output_id = xmr_accounts->insert(mock_output_data);

    EXPECT_EQ(inserted_output_id, 0);
}

TEST_F(MYSQL_TEST, InsertSeverlOutputsAtOnce)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    // create vector of several outputs to be written into database
    vector<xmreg::XmrOutput> mock_outputs_data;

    for (size_t i = 0; i < 10; ++i)
    {
        mock_outputs_data.push_back(make_mock_output_data(std::to_string(i)));
        mock_outputs_data.back().account_id = acc.id.data;
    }

    uint64_t expected_primary_id
            = xmr_accounts->get_next_primary_id(xmreg::XmrOutput());

    // first time insert should be fine
    uint64_t no_inserted_rows = xmr_accounts->insert(mock_outputs_data);

    EXPECT_EQ(no_inserted_rows, mock_outputs_data.size());

    // after inserting 10 rows, the expected ID should be before + 11
    uint64_t expected_primary_id2
            = xmr_accounts->get_next_primary_id(xmreg::XmrOutput());

    EXPECT_EQ(expected_primary_id2, expected_primary_id + mock_outputs_data.size());

    for (size_t i = 0; i < 10; ++i)
    {
        uint64_t output_id_to_get = expected_primary_id + i;

        xmreg::XmrOutput out_data;
        xmr_accounts->select_by_primary_id(output_id_to_get, out_data);

        EXPECT_EQ(mock_outputs_data[i].out_pub_key, out_data.out_pub_key);
    }

    xmr_accounts->disconnect();
    EXPECT_EQ(xmr_accounts->insert(mock_outputs_data), 0);
}


TEST_F(MYSQL_TEST, SelectInputsForAccount)
{
    // select all inputs associated with the given account

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrInput> inputs;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, inputs));

    // can use this command to get the list of ring members
    // ./xmr2csv --stagenet -m -a 5AjfkEY7RFgNGDYvoRQkncfwHXT6Fh7oJBisqFUX5u96i3ZepxDPocQK29tmAwBDuvKRpskZnfA6N8Ra58qFzA4bSA3QZFp -v 3ee772709dcf834a881c432fc15625d84585e4462d7905c42460dd478a315008 -t 90000 -g 101610
    EXPECT_EQ(inputs.size(), 12);
}

TEST_F(MYSQL_TEST, SelectInputsForTransaction)
{
    // select all outputs associated with given transaction

    // tx hash 1640236fe817f83b0bd2082c655d152be390e4f78e41db5b935bb8a53249fe8c
    TX_AND_ACC_FROM_HEX(tx_1640_hex, addr_57H_hex)

    xmreg::XmrTransaction tx_data;

    ASSERT_TRUE(xmr_accounts->tx_exists(acc.id.data, tx_hash_str, tx_data));

    vector<xmreg::XmrInput> inputs;

    bool is_success = xmr_accounts->select_for_tx(tx_data.id.data, inputs);

    ASSERT_TRUE(is_success);

    EXPECT_EQ(inputs.size(), 2);
    EXPECT_EQ(inputs[0].key_image, "45d3cf7b4f5db9d614602e9e956c63c35cf3da6f7b93740c514e06c898fb0da2");
    EXPECT_EQ(inputs[1].key_image, "3ef54482411c54a280081a87fd35e542b3b8906b702123c55468e71dd620d3a4");
}


TEST_F(MYSQL_TEST, SelectInputsForOutput)
{
    // select all inputs associated with given output
    // i.e., where the output is used as a ring member

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrOutput> outputs;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, outputs));


    // we check only for the first output
    // for second output we should have three key images
    // out pub key: 7f03cbcf4f9ddc763543959f0152bb5e66147359b6097bccf8fa0bbd748e1445
    uint64_t output_id = outputs.at(1).id.data;

    vector<xmreg::XmrInput> inputs;

    ASSERT_TRUE(xmr_accounts->select_inputs_for_out(output_id, inputs));

    EXPECT_EQ(inputs.size(), 3);

    EXPECT_EQ(inputs.front().key_image, "00dd88b3a16b3616d342faec2bc47b24add433407ef79b9a00b55b75d96239a4");
    EXPECT_EQ(inputs.back().key_image, "abc529357f90641d501d5108f822617049c19461569eafa45cb5400ee45bef33");

    inputs.clear();

    // for non exisitng output
    ASSERT_FALSE(xmr_accounts->select_inputs_for_out(444444, inputs));

    // try doing same when disconnected
    xmr_accounts->disconnect();

    EXPECT_FALSE(xmr_accounts->select_inputs_for_out(output_id, inputs));
}



auto
make_mock_input_data(string last_char_pub_key = "0")
{
    xmreg::XmrInput mock_data;

    mock_data.id           = mysqlpp::null;
    // mock_output_data.account_id   = acc.id; need to be set when used
    mock_data.tx_id        = 106086; // some tx id for this output
    mock_data.output_id    = 428900; // some output id
    mock_data.key_image    = "18c6a80311d6f455ac1e5abdce7e86828d1ecf911f78da12a56ce8fdd5c716f"
                                    + last_char_pub_key; // out_pub_key is unique field, so to make
    // few public keys, we just change its last char.
    mock_data.amount       = 999916984840000ull;
    mock_data.timestamp    = mysqlpp::DateTime(static_cast<time_t>(44434554));;

    return mock_data;
}


TEST_F(MYSQL_TEST, InsertOneInput)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    xmreg::XmrInput mock_input_data = make_mock_input_data();

    mock_input_data.account_id = acc.id.data;

    uint64_t expected_primary_id
            = xmr_accounts->get_next_primary_id(mock_input_data);
    uint64_t inserted_id  = xmr_accounts->insert(mock_input_data);

    EXPECT_EQ(expected_primary_id, inserted_id);

    // now we fetch the inserted input and compare its values

    xmreg::XmrInput in_data2;

    EXPECT_TRUE(xmr_accounts->select_by_primary_id(inserted_id, in_data2));

    EXPECT_EQ(in_data2.account_id, mock_input_data.account_id);
    EXPECT_EQ(in_data2.tx_id, mock_input_data.tx_id);
    EXPECT_EQ(in_data2.amount, mock_input_data.amount);
    EXPECT_EQ(in_data2.key_image, mock_input_data.key_image);
    EXPECT_EQ(in_data2.output_id, mock_input_data.output_id);
    EXPECT_EQ(in_data2.timestamp, mock_input_data.timestamp);
}



TEST_F(MYSQL_TEST, InsertSeverlInputsAtOnce)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    // create vector of several inputs to be written into database
    vector<xmreg::XmrInput> mock_data;

    for (size_t i = 0; i < 10; ++i)
    {
        mock_data.push_back(make_mock_input_data(std::to_string(i)));
        mock_data.back().account_id = acc.id.data;
    }

    uint64_t expected_primary_id
            = xmr_accounts->get_next_primary_id(xmreg::XmrInput());

    // first time insert should be fine
    uint64_t no_inserted_rows = xmr_accounts->insert(mock_data);

    EXPECT_EQ(no_inserted_rows, mock_data.size());

    // after inserting 10 rows, the expected ID should be before + 11
    uint64_t expected_primary_id2
            = xmr_accounts->get_next_primary_id(xmreg::XmrInput());

    EXPECT_EQ(expected_primary_id2, expected_primary_id + mock_data.size());

    for (size_t i = 0; i < 10; ++i)
    {
        uint64_t id_to_get = expected_primary_id + i;

        xmreg::XmrInput out_data;
        xmr_accounts->select_by_primary_id(id_to_get, out_data);

        EXPECT_EQ(mock_data[i].key_image, out_data.key_image);
    }

}


TEST_F(MYSQL_TEST, TryToInsertSameInputTwice)
{
    // the input table requires a row to be unique for pair
    // of key_image + output_id

    ACC_FROM_HEX(owner_addr_5Ajfk);

    xmreg::XmrInput mock_data = make_mock_input_data();

    mock_data.account_id = acc.id.data;

    // first time insert should be fine
    uint64_t inserted_id = xmr_accounts->insert(mock_data);

    EXPECT_GT(inserted_id, 0);

    // second insert should fail and result in 0
    inserted_id = xmr_accounts->insert(mock_data);

    EXPECT_EQ(inserted_id, 0);
}


TEST_F(MYSQL_TEST, SelectPaymentForAccount)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrPayment> payments;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, payments));

    EXPECT_EQ(payments.size(), 1);
    EXPECT_EQ(payments[0].payment_id, "74854c1cd490e148");
    EXPECT_EQ(payments[0].payment_address, "5DUWE29P72Eb8inMa41HuNJG4tj9CcaNKGr6EVSbvhWGJdpDQCiNNYBUNF1oDb8BczU5aD68d3HNKXaEsPq8cvbQLK4Tiiy");
    EXPECT_FALSE(static_cast<bool>(payments[0].request_fulfilled));

    //check if there is no payment for the given account id
    EXPECT_FALSE(xmr_accounts->select(5555, payments));
}

TEST_F(MYSQL_TEST, SelectPaymentUsingPaymentID)
{
    string exising_id {"e410eb43e14a28fb"};

    vector<xmreg::XmrPayment> payments;

    EXPECT_TRUE(xmr_accounts->select_payment_by_id(exising_id, payments));

    EXPECT_EQ(payments.size(), 1);


    string non_exising_id {"e410eb43e140000"};

    EXPECT_FALSE(xmr_accounts->select_payment_by_id(non_exising_id, payments));

    EXPECT_EQ(payments.size(), 0);

    xmr_accounts->disconnect();
    EXPECT_FALSE(xmr_accounts->select_payment_by_id(exising_id, payments));
}

auto
make_mock_payment_data(string last_char_pub_key = "0")
{
    xmreg::XmrPayment mock_data;

    mock_data.id                = mysqlpp::null;
    // mock_output_data.account_id   = acc.id; need to be set when used
    mock_data.payment_id        = pod_to_hex(crypto::rand<crypto::hash8>());
    mock_data.import_fee        = 10000000010ull; // xmr
    mock_data.request_fulfilled = false;
    mock_data.tx_hash           = ""; // no tx_hash yet with the payment
    mock_data.payment_address   = "5DUWE29P72Eb8inMa41HuNJG4tj9CcaNKGr6EVSbvhWGJdpDQCiNNYBUNF1oDb8BczU5aD68d3HNKXaEsPq8cvbQLGi6vcb2zkW7mhsWor";

    return mock_data;
}


TEST_F(MYSQL_TEST, InsertOnePayment)
{
    ACC_FROM_HEX(addr_55Zb);

    xmreg::XmrPayment mock_data = make_mock_payment_data();

    mock_data.account_id = acc.id.data;

    uint64_t expected_primary_id = xmr_accounts->get_next_primary_id(mock_data);
    uint64_t inserted_id  = xmr_accounts->insert(mock_data);

    EXPECT_EQ(expected_primary_id, inserted_id);

    // now we fetch the inserted input and compare its values

    xmreg::XmrPayment in_data2;

    EXPECT_TRUE(xmr_accounts->select_by_primary_id(inserted_id, in_data2));

    EXPECT_EQ(in_data2.account_id, mock_data.account_id);
    EXPECT_EQ(in_data2.payment_id, mock_data.payment_id);
    EXPECT_EQ(in_data2.import_fee, mock_data.import_fee);
    EXPECT_EQ(in_data2.payment_address, mock_data.payment_address);
    EXPECT_EQ(in_data2.request_fulfilled, mock_data.request_fulfilled);
}


TEST_F(MYSQL_TEST, TryToInsertSamePaymentTwice)
{
    // the input table requires a row to be unique for pair
    // of key_image + output_id

    ACC_FROM_HEX(addr_55Zb);

    xmreg::XmrPayment mock_data = make_mock_payment_data();

    mock_data.account_id = acc.id.data;

    uint64_t inserted_id  = xmr_accounts->insert(mock_data);

    EXPECT_GT(inserted_id, 0);

    // second insert should fail and result in 0
    inserted_id = xmr_accounts->insert(mock_data);

    EXPECT_EQ(inserted_id, 0);
}


TEST_F(MYSQL_TEST, UpdatePayment)
{
    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrPayment> payments;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, payments));


    // make copy of the orginal payment so that we can update it easly
    auto updated_payment = payments.at(0);


    ASSERT_FALSE(static_cast<bool>(updated_payment.request_fulfilled));
    ASSERT_EQ(updated_payment.tx_hash, "");


    updated_payment.request_fulfilled = 1;
    updated_payment.tx_hash = "7456e8f14cc327ac653fa680e20d53e43b61be238302ea4589bb5a73932e921c";

    EXPECT_TRUE(xmr_accounts->update( payments.at(0), updated_payment));

    // fetch the payment data and check if it was updated
    vector<xmreg::XmrPayment> payments2;

    ASSERT_TRUE(xmr_accounts->select(acc.id.data, payments2));

    EXPECT_EQ(payments2.at(0).id.data, payments.at(0).id.data);
    EXPECT_TRUE(static_cast<bool>(payments2.at(0).request_fulfilled));
    EXPECT_EQ(payments2.at(0).tx_hash, updated_payment.tx_hash);
}

/*
 * Dont want to use real blockchain data, so we are going to
 * mock xmreg::CurrentBlockchainStatus
 */
class MockCurrentBlockchainStatus1 : public xmreg::CurrentBlockchainStatus
{
public:
    MockCurrentBlockchainStatus1()
            : xmreg::CurrentBlockchainStatus(
                  xmreg::BlockchainSetup(),
                  nullptr, nullptr, nullptr)
    {}

    bool tx_unlock_state {true};
    bool tx_exist_state {true};

    std::map<string, uint64_t> tx_exist_mock_data;

    // all txs in the blockchain are unlocked
    virtual bool
    is_tx_unlocked(uint64_t unlock_time,
                   uint64_t block_height,
                   xmreg::TxUnlockChecker const& tx_unlock_checker
                        = xmreg::TxUnlockChecker()) override
    {
        return tx_unlock_state;
    }

    // all ts in the blockchain exists
    virtual bool
    tx_exist(const string& tx_hash_str, uint64_t& tx_index) override
    {
        if (tx_exist_mock_data.empty())
            return tx_exist_state;

        tx_index = tx_exist_mock_data[tx_hash_str];

        return true;
    }
};

TEST_F(MYSQL_TEST, SelectTxsIfAllAreSpendableAndExist)
{
    // if all txs selected for the given account are spendable
    // the select_txs_for_account_spendability_check method
    // should not change anything

    // use mock CurrentBlockchainStatus instead of real object
    // which would access the real blockchain.
    auto mock_bc_status = make_shared<MockCurrentBlockchainStatus1>();

    xmr_accounts->set_bc_status_provider(mock_bc_status);

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    // we mark all txs for this account as spendable
    // so that we have something to work with in this test
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    auto no_of_original_txs = txs.size();

    for (size_t i = 0; i < txs.size(); ++i)
        this->xmr_accounts->mark_tx_spendable(txs[i].id.data);

    // reselect tx after they were marked as spendable
    txs.clear();
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    EXPECT_TRUE(this->xmr_accounts
                ->select_txs_for_account_spendability_check(acc.id.data, txs));

    // we check if non of the input txs got filtere out
    EXPECT_EQ(txs.size(), no_of_original_txs);

    // and we check if all of remained spendable
    for (auto const& tx: txs)
        EXPECT_TRUE(bool {tx.spendable});
}



TEST_F(MYSQL_TEST, SelectTxsIfAllAreNonspendableButUnlockedAndExist)
{
    // if all txs selected for the given account are non-spendable
    // the select_txs_for_account_spendability_check method
    // will check their unlock time, and marked them spendable in
    // they are unlocked.
    // We are going to mock that all are unlocked

    // use mock CurrentBlockchainStatus instead of real object
    // which would access the real blockchain.
    auto mock_bc_status = make_shared<MockCurrentBlockchainStatus1>();

    xmr_accounts->set_bc_status_provider(mock_bc_status);

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    // we mark all txs for this account as spendable
    // so that we have something to work with in this test
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    auto no_of_original_txs = txs.size();

    for (size_t i = 0; i < txs.size(); ++i)
        this->xmr_accounts->mark_tx_nonspendable(txs[i].id.data);

    // reselect tx after they were marked as spendable
    txs.clear();
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    // and we check if all non-spendable before we fetch them into
    // select_txs_for_account_spendability_check
    for (auto const& tx: txs)
        ASSERT_FALSE(bool {tx.spendable});

    EXPECT_TRUE(this->xmr_accounts
                ->select_txs_for_account_spendability_check(acc.id.data, txs));

    // we check if non of the input txs got filtere out
    EXPECT_EQ(txs.size(), no_of_original_txs);


    // reselect tx after they were marked as spendable
    txs.clear();
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    // and we check if all of remained spendable
    for (auto const& tx: txs)
        ASSERT_TRUE(bool {tx.spendable});

    // and we check if all of remained spendable
    for (auto const& tx: txs)
        EXPECT_TRUE(bool {tx.spendable});
}



TEST_F(MYSQL_TEST, SelectTxsIfAllAreNonspendableLockedButExist)
{
    // if all txs selected for the given account are non-spendable
    // the select_txs_for_account_spendability_check method
    // will check if they are unlocked. In this thest all will be locked
    // so new unlock_time for them is going to be set for them.
    // all txs are set to exisit in the blockchain

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    // we mark all txs for this account as spendable
    // so that we have something to work with in this test
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    // use mock CurrentBlockchainStatus instead of real object
    // which would access the real blockchain.
    auto mock_bc_status = make_shared<MockCurrentBlockchainStatus1>();

    // we mock that all txs are still locked and cant be spent
    mock_bc_status->tx_unlock_state = false;

    // now we need to populate mock_bc_status->tx_exist_mock_data map
    // so that tx_exist mock works as if all txs existed
    // and it returns correct/expected blockchain_tx_id
    for(auto const& tx: txs)
        mock_bc_status->tx_exist_mock_data[tx.hash] = tx.blockchain_tx_id;

    // now set the mock_bc_status to be used by xmr_accounts later on
    xmr_accounts->set_bc_status_provider(mock_bc_status);

    auto no_of_original_txs = txs.size();

    for (size_t i = 0; i < txs.size(); ++i)
        this->xmr_accounts->mark_tx_nonspendable(txs[i].id.data);

    // reselect tx after they were marked as spendable
    txs.clear();
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    // and we check if all non-spendable before we fetch them into
    // select_txs_for_account_spendability_check
    for (auto const& tx: txs)
    {
        ASSERT_FALSE(bool {tx.spendable});
        if (!bool {tx.coinbase})
            ASSERT_EQ(tx.unlock_time, 0);
    }

    EXPECT_TRUE(this->xmr_accounts
                ->select_txs_for_account_spendability_check(acc.id.data, txs));

    // we check if non of the input txs got filtere out
    EXPECT_EQ(txs.size(), no_of_original_txs);

    // and we check if all of remained non-spendable and
    // if their unlock time was modified as needed
    for (auto const& tx: txs)
    {
        ASSERT_FALSE(bool {tx.spendable});
        if (!bool {tx.coinbase})
            ASSERT_EQ(tx.unlock_time,
                      tx.height + CRYPTONOTE_DEFAULT_TX_SPENDABLE_AGE);
    }
}


TEST_F(MYSQL_TEST, SelectTxsIfAllAreNonspendableUnlockedAndDontExist)
{
    // if all txs selected for the given account are non-spendable,
    // locked and dont exist in blockchain
    // they should be filtered and removed from the mysql

    // use mock CurrentBlockchainStatus instead of real object
    // which would access the real blockchain.
    auto mock_bc_status = make_shared<MockCurrentBlockchainStatus1>();

    // all txs are locked and dont exisit in blockchain
    mock_bc_status->tx_unlock_state = false;
    mock_bc_status->tx_exist_state = false;

    xmr_accounts->set_bc_status_provider(mock_bc_status);

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    // we mark all txs for this account as nonspendable
    // so that we have something to work with in this test
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    for (size_t i = 0; i < txs.size(); ++i)
        this->xmr_accounts->mark_tx_nonspendable(txs[i].id.data);

    // reselect tx after they were marked as spendable
    txs.clear();
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    // and we check if all non-spendable before we fetch them into
    // select_txs_for_account_spendability_check
    for (auto const& tx: txs)
        ASSERT_FALSE(bool {tx.spendable});

    EXPECT_TRUE(this->xmr_accounts->select_txs_for_account_spendability_check(acc.id.data, txs));

    // after the call to select_txs_for_account_spendability_check
    // all txs should be filted out
    EXPECT_EQ(txs.size(), 0);

    // all these txs should also be deleted from the mysql
    txs.clear();
    ASSERT_FALSE(this->xmr_accounts->select(acc.id.data, txs));
    EXPECT_EQ(txs.size(), 0);
}



TEST_F(MYSQL_TEST, SelectTxsFailureDueToSpendabilityAndTxDeletion)
{
    // if all txs selected for the given account are non-spendable,
    // locked and dont exist in blockchain
    // they should be filtered and removed from the mysql
    // however, the mark_tx_spendable and delete_tx can
    // fail. We simulate the failure by providing tx.id which does not
    // exisit in the mysql

    // use mock CurrentBlockchainStatus instead of real object
    // which would access the real blockchain.
    auto mock_bc_status = make_shared<MockCurrentBlockchainStatus1>();

    // all txs are unlocked and dont exisit in blockchain
    mock_bc_status->tx_unlock_state = true;
    mock_bc_status->tx_exist_state = false;

    xmr_accounts->set_bc_status_provider(mock_bc_status);

    ACC_FROM_HEX(owner_addr_5Ajfk);

    vector<xmreg::XmrTransaction> txs;

    // we mark all txs for this account as nonspendable
    // so that we have something to work with in this test
    ASSERT_TRUE(this->xmr_accounts->select(acc.id.data, txs));

    auto no_of_original_txs = txs.size();

    for (size_t i = 0; i < txs.size(); ++i)
    {
        txs[i].id = 5555555555 /*some non-existing id*/;
        txs[i].spendable = false;
    }

    // the non-exisiting ids should result in failure
    EXPECT_FALSE(this->xmr_accounts->select_txs_for_account_spendability_check(acc.id.data, txs));

    // now repeat if all txs are locked and dont exisit in blockchain
    mock_bc_status->tx_unlock_state = false;

    // also should lead to false
    EXPECT_FALSE(this->xmr_accounts->select_txs_for_account_spendability_check(acc.id.data, txs));

}


TEST_F(MYSQL_TEST, MysqlPingThreadStopsOnPingFailure)
{
    // we test creation of the mysql ping thread
    // and its stop due to ping failure, for example,
    // when connection to mysql is lost

    auto conn = xmr_accounts->get_connection();

    // create ping functor that will be pinging mysql every 1 second
    xmreg::MysqlPing mysql_ping {conn, 1s};

    {
        // create ping thread and start pinging

        // we put it in this local scope so that
        // we exit the scope only when the thread is fully finished
        // i.e., it was joined.
        xmreg::ThreadRAII mysql_ping_thread(
                std::thread(std::ref(mysql_ping)),
                xmreg::ThreadRAII::DtorAction::join);

        // wait few seconds so that several pings are done
        std::this_thread::sleep_for(4s);

        // disconnecting from mysql should result in the thread to stop
        conn->get_connection().disconnect();
    }

    // since we waiting 4s, we expect that there were at about 3-4 pings
    EXPECT_EQ(mysql_ping.get_stop_reason(),
              xmreg::MysqlPing::StopReason::PingFailed);
    EXPECT_THAT(mysql_ping.get_counter(), AllOf(Ge(3), Le(5)));
}

TEST_F(MYSQL_TEST, MysqlPingThreadStopsOnPointerExpiry)
{
    // we test creation of the mysql ping thread
    // and weak pointer in the ping object expires.

    // to simulate the pointer expiry, we are going to create new connection here
    // i.e., so we dont use the one from the fixture, as its shared pointer
    // will be keeping the weak pointer in the functor alive

    // we did specify wrong mysql details so this should throw.

    auto new_conn = make_connection();

    ASSERT_TRUE(new_conn->get_connection().connected());

    // create ping functor that will be pinging mysql every 1 second
    xmreg::MysqlPing mysql_ping {new_conn, 1s};

    {
        // create ping thread and start pinging

        // we put it in this local scope so that
        // we exit the scope only when the thread is fully finished
        // i.e., it was joined.
        xmreg::ThreadRAII mysql_ping_thread(
                std::thread(std::ref(mysql_ping)),
                xmreg::ThreadRAII::DtorAction::join);

        // wait few seconds so that several pings are done
        std::this_thread::sleep_for(4s);

        // reset new_conn pointer. this should lead to expiry of
        // weak pointer in mysql_ping function
        new_conn.reset();
    }

    // since we waiting 4s, we expect that there were at about 3-4 pings
    EXPECT_EQ(mysql_ping.get_stop_reason(),
              xmreg::MysqlPing::StopReason::PointerExpired);
    EXPECT_THAT(mysql_ping.get_counter(), AllOf(Ge(3), Le(5)));
}


//class MYSQL_TEST2 : public MYSQL_TEST
//{
//
//};


//TEST(TEST_CHAIN, GenerateTestChain)
//{
//    uint64_t ts_start = 1338224400;
//
//    std::vector<test_event_entry> events;
//
//    GENERATE_ACCOUNT(miner);
//    GENERATE_ACCOUNT(alice);
//
//    MAKE_GENESIS_BLOCK(events, blk_0, miner, ts_start);
//    MAKE_NEXT_BLOCK(events, blk_1, blk_0, miner);
//    MAKE_NEXT_BLOCK(events, blk_1_side, blk_0, miner);
//    MAKE_NEXT_BLOCK(events, blk_2, blk_1, miner);
//
//    std::vector<cryptonote::block> chain;
//    map_hash2tx_t mtx;
//
//
//    REWIND_BLOCKS(events, blk_2r, blk_2, miner);
//    MAKE_TX_LIST_START(events, txlist_0, miner, alice, MK_COINS(1), blk_2);
//    MAKE_TX_LIST(events, txlist_0, miner, alice, MK_COINS(2), blk_2);
//    MAKE_TX_LIST(events, txlist_0, miner, alice, MK_COINS(4), blk_2);
//    MAKE_NEXT_BLOCK_TX_LIST(events, blk_3, blk_2r, miner, txlist_0);
//    REWIND_BLOCKS(events, blk_3r, blk_3, miner);
//    MAKE_TX(events, tx_1, miner, alice, MK_COINS(50), blk_3);
//    MAKE_NEXT_BLOCK_TX1(events, blk_4, blk_3r, miner, tx_1);
//    REWIND_BLOCKS(events, blk_4r, blk_4, miner);
//    MAKE_TX(events, tx_2, miner, alice, MK_COINS(50), blk_4);
//    MAKE_NEXT_BLOCK_TX1(events, blk_5, blk_4r, miner, tx_2);
//    REWIND_BLOCKS(events, blk_5r, blk_5, miner);
//    MAKE_TX(events, tx_3, miner, alice, MK_COINS(50), blk_5);
//    MAKE_NEXT_BLOCK_TX1(events, blk_6, blk_5r, miner, tx_3);
//    REWIND_BLOCKS(events, blk_6r, blk_6, miner);
//    MAKE_TX(events, tx_4, alice, miner, MK_COINS(20), blk_6);
//    MAKE_NEXT_BLOCK_TX1(events, blk_7, blk_6r, miner, tx_4);
//
//    find_block_chain(events, chain, mtx, get_block_hash(blk_7));
//
//
//    std::cout << "Alice BALANCE = " << get_balance(alice, chain, mtx) << std::endl;
//
//    cout << get_account_address_as_str(network_type::MAINNET, false, alice.get_keys().m_account_address) << endl;
//    cout << pod_to_hex(alice.get_keys().m_view_secret_key) << endl;
//    cout << pod_to_hex(alice.get_keys().m_spend_secret_key) << endl;
//
//    cout << '\n\n' << xmreg::tx_to_hex(tx_4) << '\n';
//
//}

}
