/*
 * Copyright (c) 2012 - 2015 Ngewi Fet <ngewif@gmail.com>
 * Copyright (c) 2014 Yongxin Wang <fefe.wyx@gmail.com>
 *
 * 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.
 */

package org.gnucash.android.db.adapter;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.database.sqlite.SQLiteStatement;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.text.TextUtils;
import android.util.Log;

import com.crashlytics.android.Crashlytics;

import org.gnucash.android.app.GnuCashApplication;
import org.gnucash.android.model.AccountType;
import org.gnucash.android.model.Money;
import org.gnucash.android.model.Split;
import org.gnucash.android.model.Transaction;
import org.gnucash.android.util.TimestampHelper;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import static org.gnucash.android.db.DatabaseSchema.AccountEntry;
import static org.gnucash.android.db.DatabaseSchema.ScheduledActionEntry;
import static org.gnucash.android.db.DatabaseSchema.SplitEntry;
import static org.gnucash.android.db.DatabaseSchema.TransactionEntry;

/**
 * Manages persistence of {@link Transaction}s in the database
 * Handles adding, modifying and deleting of transaction records.
 * @author Ngewi Fet <ngewif@gmail.com> 
 * @author Yongxin Wang <fefe.wyx@gmail.com>
 * @author Oleksandr Tyshkovets <olexandr.tyshkovets@gmail.com>
 */
public class TransactionsDbAdapter extends DatabaseAdapter<Transaction> {

    private final SplitsDbAdapter mSplitsDbAdapter;

    private final CommoditiesDbAdapter mCommoditiesDbAdapter;

    /**
     * Overloaded constructor. Creates adapter for already open db
     * @param db SQlite db instance
     */
    public TransactionsDbAdapter(SQLiteDatabase db, SplitsDbAdapter splitsDbAdapter) {
        super(db, TransactionEntry.TABLE_NAME, new String[]{
                TransactionEntry.COLUMN_DESCRIPTION,
                TransactionEntry.COLUMN_NOTES,
                TransactionEntry.COLUMN_TIMESTAMP,
                TransactionEntry.COLUMN_EXPORTED,
                TransactionEntry.COLUMN_CURRENCY,
                TransactionEntry.COLUMN_COMMODITY_UID,
                TransactionEntry.COLUMN_CREATED_AT,
                TransactionEntry.COLUMN_SCHEDX_ACTION_UID,
                TransactionEntry.COLUMN_TEMPLATE
        });
        mSplitsDbAdapter = splitsDbAdapter;
        mCommoditiesDbAdapter = new CommoditiesDbAdapter(db);
    }

    /**
     * Returns an application-wide instance of the database adapter
     * @return Transaction database adapter
     */
    public static TransactionsDbAdapter getInstance(){
        return GnuCashApplication.getTransactionDbAdapter();
    }

    public SplitsDbAdapter getSplitDbAdapter() {
        return mSplitsDbAdapter;
    }

    /**
	 * Adds an transaction to the database. 
	 * If a transaction already exists in the database with the same unique ID, 
	 * then the record will just be updated instead
	 * @param transaction {@link Transaction} to be inserted to database
	 */
    @Override
	public void addRecord(@NonNull Transaction transaction, UpdateMethod updateMethod){
        Log.d(LOG_TAG, "Adding transaction to the db via " + updateMethod.name());
        mDb.beginTransaction();
        try {
            Split imbalanceSplit = transaction.createAutoBalanceSplit();
            if (imbalanceSplit != null){
                String imbalanceAccountUID = new AccountsDbAdapter(mDb, this)
                        .getOrCreateImbalanceAccountUID(transaction.getCommodity());
                imbalanceSplit.setAccountUID(imbalanceAccountUID);
            }
            super.addRecord(transaction, updateMethod);

            Log.d(LOG_TAG, "Adding splits for transaction");
            ArrayList<String> splitUIDs = new ArrayList<>(transaction.getSplits().size());
            for (Split split : transaction.getSplits()) {
                Log.d(LOG_TAG, "Replace transaction split in db");
                if (imbalanceSplit == split) {
                    mSplitsDbAdapter.addRecord(split, UpdateMethod.insert);
                } else {
                    mSplitsDbAdapter.addRecord(split, updateMethod);
                }
                splitUIDs.add(split.getUID());
            }
            Log.d(LOG_TAG, transaction.getSplits().size() + " splits added");

            long deleted = mDb.delete(SplitEntry.TABLE_NAME,
                    SplitEntry.COLUMN_TRANSACTION_UID + " = ? AND "
                            + SplitEntry.COLUMN_UID + " NOT IN ('" + TextUtils.join("' , '", splitUIDs) + "')",
                    new String[]{transaction.getUID()});
            Log.d(LOG_TAG, deleted + " splits deleted");

            mDb.setTransactionSuccessful();
        } catch (SQLException sqlEx) {
            Log.e(LOG_TAG, sqlEx.getMessage());
            Crashlytics.logException(sqlEx);
        } finally {
            mDb.endTransaction();
        }
	}

    /**
     * Adds an several transactions to the database.
     * If a transaction already exists in the database with the same unique ID,
     * then the record will just be updated instead. Recurrence Transactions will not
     * be inserted, instead schedule Transaction would be called. If an exception
     * occurs, no transaction would be inserted.
     * @param transactionList {@link Transaction} transactions to be inserted to database
     * @return Number of transactions inserted
     */
    @Override
    public long bulkAddRecords(@NonNull List<Transaction> transactionList, UpdateMethod updateMethod){
        long start = System.nanoTime();
        long rowInserted = super.bulkAddRecords(transactionList, updateMethod);
        long end = System.nanoTime();
        Log.d(getClass().getSimpleName(), String.format("bulk add transaction time %d ", end - start));
        List<Split> splitList = new ArrayList<>(transactionList.size()*3);
        for (Transaction transaction : transactionList) {
            splitList.addAll(transaction.getSplits());
        }
        if (rowInserted != 0 && !splitList.isEmpty()) {
            try {
                start = System.nanoTime();
                long nSplits = mSplitsDbAdapter.bulkAddRecords(splitList, updateMethod);
                Log.d(LOG_TAG, String.format("%d splits inserted in %d ns", nSplits, System.nanoTime()-start));
            }
            finally {
                SQLiteStatement deleteEmptyTransaction = mDb.compileStatement("DELETE FROM " +
                        TransactionEntry.TABLE_NAME + " WHERE NOT EXISTS ( SELECT * FROM " +
                        SplitEntry.TABLE_NAME +
                        " WHERE " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID +
                        " = " + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID + " ) ");
                deleteEmptyTransaction.execute();
            }
        }
        return rowInserted;
    }

    @Override
    protected @NonNull SQLiteStatement setBindings(@NonNull SQLiteStatement stmt, @NonNull Transaction transaction) {
        stmt.clearBindings();
        stmt.bindString(1, transaction.getDescription());
        stmt.bindString(2, transaction.getNote());
        stmt.bindLong(3, transaction.getTimeMillis());
        stmt.bindLong(4, transaction.isExported() ? 1 : 0);
        stmt.bindString(5, transaction.getCurrencyCode());
        stmt.bindString(6, transaction.getCommodity().getUID());
        stmt.bindString(7, TimestampHelper.getUtcStringFromTimestamp(transaction.getCreatedTimestamp()));

        if (transaction.getScheduledActionUID() == null)
            stmt.bindNull(8);
        else
            stmt.bindString(8, transaction.getScheduledActionUID());
        stmt.bindLong(9, transaction.isTemplate() ? 1 : 0);
        stmt.bindString(10, transaction.getUID());

        return stmt;
    }

    /**
	 * Returns a cursor to a set of all transactions which have a split belonging to the accound with unique ID
	 * <code>accountUID</code>.
	 * @param accountUID UID of the account whose transactions are to be retrieved
	 * @return Cursor holding set of transactions for particular account
     * @throws java.lang.IllegalArgumentException if the accountUID is null
	 */
	public Cursor fetchAllTransactionsForAccount(String accountUID){
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TransactionEntry.TABLE_NAME
                + " INNER JOIN " + SplitEntry.TABLE_NAME + " ON "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + " = "
                + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID);
        queryBuilder.setDistinct(true);
        String[] projectionIn = new String[]{TransactionEntry.TABLE_NAME + ".*"};
        String selection = SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_ACCOUNT_UID + " = ?"
                + " AND " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TEMPLATE + " = 0";
        String[] selectionArgs = new String[]{accountUID};
        String sortOrder = TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TIMESTAMP + " DESC";

        return queryBuilder.query(mDb, projectionIn, selection, selectionArgs, null, null, sortOrder);
    }

    /**
     * Returns a cursor to all scheduled transactions which have at least one split in the account
     * <p>This is basically a set of all template transactions for this account</p>
     * @param accountUID GUID of account
     * @return Cursor with set of transactions
     */
    public Cursor fetchScheduledTransactionsForAccount(String accountUID){
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TransactionEntry.TABLE_NAME
                + " INNER JOIN " + SplitEntry.TABLE_NAME + " ON "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + " = "
                + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID);
        queryBuilder.setDistinct(true);
        String[] projectionIn = new String[]{TransactionEntry.TABLE_NAME + ".*"};
        String selection = SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_ACCOUNT_UID + " = ?"
                + " AND " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TEMPLATE + " = 1";
        String[] selectionArgs = new String[]{accountUID};
        String sortOrder = TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TIMESTAMP + " DESC";

        return queryBuilder.query(mDb, projectionIn, selection, selectionArgs, null, null, sortOrder);
    }

    /**
     * Deletes all transactions which contain a split in the account.
     * <p><b>Note:</b>As long as the transaction has one split which belongs to the account {@code accountUID},
     * it will be deleted. The other splits belonging to the transaction will also go away</p>
     * @param accountUID GUID of the account
     */
    public void deleteTransactionsForAccount(String accountUID){
        String rawDeleteQuery = "DELETE FROM " + TransactionEntry.TABLE_NAME + " WHERE " + TransactionEntry.COLUMN_UID + " IN "
                + " (SELECT " + SplitEntry.COLUMN_TRANSACTION_UID + " FROM " + SplitEntry.TABLE_NAME + " WHERE "
                + SplitEntry.COLUMN_ACCOUNT_UID + " = ?)";
        mDb.execSQL(rawDeleteQuery, new String[]{accountUID});
    }

    /**
     * Deletes all transactions which have no splits associated with them
     * @return Number of records deleted
     */
    public int deleteTransactionsWithNoSplits(){
        return mDb.delete(
                TransactionEntry.TABLE_NAME,
                "NOT EXISTS ( SELECT * FROM " + SplitEntry.TABLE_NAME +
                        " WHERE " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID +
                        " = " + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID + " ) ",
                null
        );
    }

    /**
     * Fetches all recurring transactions from the database.
     * <p>Recurring transactions are the transaction templates which have an entry in the scheduled events table</p>
     * @return Cursor holding set of all recurring transactions
     */
    public Cursor fetchAllScheduledTransactions(){
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TransactionEntry.TABLE_NAME + " INNER JOIN " + ScheduledActionEntry.TABLE_NAME + " ON "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + " = "
                + ScheduledActionEntry.TABLE_NAME + "." + ScheduledActionEntry.COLUMN_ACTION_UID);

        String[] projectionIn = new String[]{TransactionEntry.TABLE_NAME + ".*",
                ScheduledActionEntry.TABLE_NAME+"."+ScheduledActionEntry.COLUMN_UID + " AS " + "origin_scheduled_action_uid"};
        String sortOrder = TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_DESCRIPTION + " ASC";
//        queryBuilder.setDistinct(true);

        return queryBuilder.query(mDb, projectionIn, null, null, null, null, sortOrder);
    }

	/**
	 * Returns list of all transactions for account with UID <code>accountUID</code>
	 * @param accountUID UID of account whose transactions are to be retrieved
	 * @return List of {@link Transaction}s for account with UID <code>accountUID</code>
	 */
    public List<Transaction> getAllTransactionsForAccount(String accountUID){
		Cursor c = fetchAllTransactionsForAccount(accountUID);
		ArrayList<Transaction> transactionsList = new ArrayList<>();
        try {
            while (c.moveToNext()) {
                transactionsList.add(buildModelInstance(c));
            }
        } finally {
            c.close();
        }
		return transactionsList;
	}

    /**
     * Returns all transaction instances in the database.
     * @return List of all transactions
     */
    public List<Transaction> getAllTransactions(){
        Cursor cursor = fetchAllRecords();
        List<Transaction> transactions = new ArrayList<Transaction>();
        try {
            while (cursor.moveToNext()) {
                transactions.add(buildModelInstance(cursor));
            }
        } finally {
            cursor.close();
        }
        return transactions;
    }

    public Cursor fetchTransactionsWithSplits(String [] columns, @Nullable String where, @Nullable String[] whereArgs, @Nullable String orderBy) {
        return mDb.query(TransactionEntry.TABLE_NAME + " , " + SplitEntry.TABLE_NAME +
                        " ON " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID +
                        " = " + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID +
                        " , trans_extra_info ON trans_extra_info.trans_acct_t_uid = " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID ,
                columns, where, whereArgs, null, null,
                orderBy);
    }

    /**
     * Fetch all transactions modified since a given timestamp
     * @param timestamp Timestamp in milliseconds (since Epoch)
     * @return Cursor to the results
     */
    public Cursor fetchTransactionsModifiedSince(Timestamp timestamp){
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TransactionEntry.TABLE_NAME);
        String startTimeString = TimestampHelper.getUtcStringFromTimestamp(timestamp);
        return queryBuilder.query(mDb, null, TransactionEntry.COLUMN_MODIFIED_AT + " >= \"" + startTimeString + "\"",
                null, null, null, TransactionEntry.COLUMN_TIMESTAMP + " ASC", null);
    }

    public Cursor fetchTransactionsWithSplitsWithTransactionAccount(String [] columns, String where, String[] whereArgs, String orderBy) {
        // table is :
        // trans_split_acct , trans_extra_info ON trans_extra_info.trans_acct_t_uid = transactions_uid ,
        // accounts AS account1 ON account1.uid = trans_extra_info.trans_acct_a_uid
        //
        // views effectively simplified this query
        //
        // account1 provides information for the grouped account. Splits from the grouped account
        // can be eliminated with a WHERE clause. Transactions in QIF can be auto balanced.
        //
        // Account, transaction and split Information can be retrieve in a single query.
        return mDb.query(
                "trans_split_acct , trans_extra_info ON trans_extra_info.trans_acct_t_uid = trans_split_acct." +
                TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_UID + " , " +
                AccountEntry.TABLE_NAME + " AS account1 ON account1." + AccountEntry.COLUMN_UID +
                " = trans_extra_info.trans_acct_a_uid",
                columns, where, whereArgs, null, null , orderBy);
    }

    /**
     * Return number of transactions in the database (excluding templates)
     * @return Number of transactions
     */
    public long getRecordsCount() {
        String queryCount = "SELECT COUNT(*) FROM " + TransactionEntry.TABLE_NAME +
                " WHERE " + TransactionEntry.COLUMN_TEMPLATE + " =0";
        Cursor cursor = mDb.rawQuery(queryCount, null);
        try {
            cursor.moveToFirst();
            return cursor.getLong(0);
        } finally {
            cursor.close();
        }
    }

    /**
     * Returns the number of transactions in the database which fulfill the conditions
     * @param where SQL WHERE clause without the "WHERE" itself
     * @param whereArgs Arguments to substitute question marks for
     * @return Number of records in the databases
     */
    public long getRecordsCount(@Nullable String where, @Nullable String[] whereArgs) {
        Cursor cursor = mDb.query(true, TransactionEntry.TABLE_NAME + " , trans_extra_info ON "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID
                        + " = trans_extra_info.trans_acct_t_uid",
                new String[]{"COUNT(*)"},
                where,
                whereArgs,
                null,
                null,
                null,
                null);
        try{
            cursor.moveToFirst();
            return cursor.getLong(0);
        } finally {
            cursor.close();
        }
    }

	/**
	 * Builds a transaction instance with the provided cursor.
	 * The cursor should already be pointing to the transaction record in the database
	 * @param c Cursor pointing to transaction record in database
	 * @return {@link Transaction} object constructed from database record
	 */
    @Override
    public Transaction buildModelInstance(@NonNull final Cursor c){
		String name   = c.getString(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_DESCRIPTION));
		Transaction transaction = new Transaction(name);
        populateBaseModelAttributes(c, transaction);

		transaction.setTime(c.getLong(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_TIMESTAMP)));
		transaction.setNote(c.getString(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_NOTES)));
		transaction.setExported(c.getInt(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_EXPORTED)) == 1);
		transaction.setTemplate(c.getInt(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_TEMPLATE)) == 1);
        String currencyCode = c.getString(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_CURRENCY));
        transaction.setCommodity(mCommoditiesDbAdapter.getCommodity(currencyCode));
        transaction.setScheduledActionUID(c.getString(c.getColumnIndexOrThrow(TransactionEntry.COLUMN_SCHEDX_ACTION_UID)));
        long transactionID = c.getLong(c.getColumnIndexOrThrow(TransactionEntry._ID));
        transaction.setSplits(mSplitsDbAdapter.getSplitsForTransaction(transactionID));

		return transaction;
	}

    /**
     * Returns the transaction balance for the transaction for the specified account.
     * <p>We consider only those splits which belong to this account</p>
     * @param transactionUID GUID of the transaction
     * @param accountUID GUID of the account
     * @return {@link org.gnucash.android.model.Money} balance of the transaction for that account
     */
    public Money getBalance(String transactionUID, String accountUID){
        List<Split> splitList = mSplitsDbAdapter.getSplitsForTransactionInAccount(
                transactionUID, accountUID);

        return Transaction.computeBalance(accountUID, splitList);
    }

    /**
	 * Assigns transaction with id <code>rowId</code> to account with id <code>accountId</code>
	 * @param transactionUID GUID of the transaction
     * @param srcAccountUID GUID of the account from which the transaction is to be moved
	 * @param dstAccountUID GUID of the account to which the transaction will be assigned
	 * @return Number of transactions splits affected
	 */
	public int moveTransaction(String transactionUID, String srcAccountUID, String dstAccountUID){
		Log.i(LOG_TAG, "Moving transaction ID " + transactionUID
                + " splits from " + srcAccountUID + " to account " + dstAccountUID);

		List<Split> splits = mSplitsDbAdapter.getSplitsForTransactionInAccount(transactionUID, srcAccountUID);
        for (Split split : splits) {
            split.setAccountUID(dstAccountUID);
        }
        mSplitsDbAdapter.bulkAddRecords(splits, UpdateMethod.update);
        return splits.size();
	}

    /**
     * Returns the number of transactions belonging to an account
     * @param accountUID GUID of the account
     * @return Number of transactions with splits in the account
     */
    public int getTransactionsCount(String accountUID){
        Cursor cursor = fetchAllTransactionsForAccount(accountUID);
        int count = 0;
        if (cursor == null)
            return count;
        else {
            count = cursor.getCount();
            cursor.close();
        }
        return count;
    }

    /**
     * Returns the number of template transactions in the database
     * @return Number of template transactions
     */
    public long getTemplateTransactionsCount(){
        String sql = "SELECT COUNT(*) FROM " + TransactionEntry.TABLE_NAME
                + " WHERE " + TransactionEntry.COLUMN_TEMPLATE + "=1";
        SQLiteStatement statement = mDb.compileStatement(sql);
        return statement.simpleQueryForLong();
    }

    /**
     * Returns a list of all scheduled transactions in the database
     * @return List of all scheduled transactions
     */
    public List<Transaction> getScheduledTransactionsForAccount(String accountUID){
        Cursor cursor = fetchScheduledTransactionsForAccount(accountUID);
        List<Transaction> scheduledTransactions = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                scheduledTransactions.add(buildModelInstance(cursor));
            }
            return scheduledTransactions;
        } finally {
            cursor.close();
        }
    }

    /**
     * Returns the number of splits for the transaction in the database
     * @param transactionUID GUID of the transaction
     * @return Number of splits belonging to the transaction
     */
    public long getSplitCount(@NonNull String transactionUID){
        if (transactionUID == null)
            return 0;
        String sql = "SELECT COUNT(*) FROM " + SplitEntry.TABLE_NAME
                + " WHERE " + SplitEntry.COLUMN_TRANSACTION_UID + "= '" + transactionUID + "'";
        SQLiteStatement statement = mDb.compileStatement(sql);
        return statement.simpleQueryForLong();
    }

    /**
     * Returns a cursor to transactions whose name (UI: description) start with the <code>prefix</code>
     * <p>This method is used for autocomplete suggestions when creating new transactions. <br/>
     * The suggestions are either transactions which have at least one split with {@code accountUID} or templates.</p>
     * @param prefix Starting characters of the transaction name
     * @param accountUID GUID of account within which to search for transactions
     * @return Cursor to the data set containing all matching transactions
     */
    public Cursor fetchTransactionSuggestions(String prefix, String accountUID){
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables(TransactionEntry.TABLE_NAME
                + " INNER JOIN " + SplitEntry.TABLE_NAME + " ON "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + " = "
                + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID);
        queryBuilder.setDistinct(true);
        String[] projectionIn = new String[]{TransactionEntry.TABLE_NAME + ".*"};
        String selection = "(" + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_ACCOUNT_UID + " = ?"
                + " OR " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TEMPLATE + "=1 )"
                + " AND " + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_DESCRIPTION + " LIKE '" + prefix + "%'";
        String[] selectionArgs = new String[]{accountUID};
        String sortOrder = TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TIMESTAMP + " DESC";
        String groupBy = TransactionEntry.COLUMN_DESCRIPTION;
        String limit = Integer.toString(5);

        return queryBuilder.query(mDb, projectionIn, selection, selectionArgs, groupBy, null, sortOrder, limit);
    }

    /**
     * Updates a specific entry of an transaction
     * @param contentValues Values with which to update the record
     * @param whereClause Conditions for updating formatted as SQL where statement
     * @param whereArgs Arguments for the SQL wehere statement
     * @return Number of records affected
     */
    public int updateTransaction(ContentValues contentValues, String whereClause, String[] whereArgs){
        return mDb.update(TransactionEntry.TABLE_NAME, contentValues, whereClause, whereArgs);
    }

    /**
     * Return the number of currencies used in the transaction.
     * For example if there are different splits with different currencies
     * @param transactionUID GUID of the transaction
     * @return Number of currencies within the transaction
     */
    public int getNumCurrencies(String transactionUID) {
        Cursor cursor = mDb.query("trans_extra_info",
                new String[]{"trans_currency_count"},
                "trans_acct_t_uid=?",
                new String[]{transactionUID},
                null, null, null);
        int numCurrencies = 0;
        try {
            if (cursor.moveToFirst()) {
                numCurrencies = cursor.getInt(0);
            }
        }
        finally {
            cursor.close();
        }
        return numCurrencies;
    }

    /**
     * Deletes all transactions except those which are marked as templates.
     * <p>If you want to delete really all transaction records, use {@link #deleteAllRecords()}</p>
     * @return Number of records deleted
     */
    public int deleteAllNonTemplateTransactions(){
        String where = TransactionEntry.COLUMN_TEMPLATE + "=0";
        return mDb.delete(mTableName, where, null);
    }

    /**
     * Returns a timestamp of the earliest transaction for a specified account type and currency
     * @param type the account type
     * @param currencyCode the currency code
     * @return the earliest transaction's timestamp. Returns 1970-01-01 00:00:00.000 if no transaction found
     */
    public long getTimestampOfEarliestTransaction(AccountType type, String currencyCode) {
        return getTimestamp("MIN", type, currencyCode);
    }

    /**
     * Returns a timestamp of the latest transaction for a specified account type and currency
     * @param type the account type
     * @param currencyCode the currency code
     * @return the latest transaction's timestamp. Returns 1970-01-01 00:00:00.000 if no transaction found
     */
    public long getTimestampOfLatestTransaction(AccountType type, String currencyCode) {
        return getTimestamp("MAX", type, currencyCode);
    }

    /**
     * Returns the most recent `modified_at` timestamp of non-template transactions in the database
     * @return Last moodified time in milliseconds or current time if there is none in the database
     */
    public Timestamp getTimestampOfLastModification(){
        Cursor cursor = mDb.query(TransactionEntry.TABLE_NAME,
                new String[]{"MAX(" + TransactionEntry.COLUMN_MODIFIED_AT + ")"},
                null, null, null, null, null);

        Timestamp timestamp = TimestampHelper.getTimestampFromNow();
        if (cursor.moveToFirst()){
            String timeString = cursor.getString(0);
            if (timeString != null){ //in case there were no transactions in the XML file (account structure only)
                timestamp = TimestampHelper.getTimestampFromUtcString(timeString);
            }
        }
        cursor.close();
        return timestamp;
    }

    /**
     * Returns the earliest or latest timestamp of transactions for a specific account type and currency
     * @param mod Mode (either MAX or MIN)
     * @param type AccountType
     * @param currencyCode the currency code
     * @return earliest or latest timestamp of transactions
     * @see #getTimestampOfLatestTransaction(AccountType, String)
     * @see #getTimestampOfEarliestTransaction(AccountType, String)
     */
    private long getTimestamp(String mod, AccountType type, String currencyCode) {
        String sql = "SELECT " + mod + "(" + TransactionEntry.COLUMN_TIMESTAMP + ")"
                + " FROM " + TransactionEntry.TABLE_NAME
                + " INNER JOIN " + SplitEntry.TABLE_NAME + " ON "
                + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID + " = "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID
                + " INNER JOIN " + AccountEntry.TABLE_NAME + " ON "
                + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_UID + " = "
                + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_ACCOUNT_UID
                + " WHERE " + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_TYPE + " = ? AND "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_CURRENCY + " = ? AND "
                + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TEMPLATE + " = 0";
        Cursor cursor = mDb.rawQuery(sql, new String[]{ type.name(), currencyCode });
        long timestamp= 0;
        if (cursor != null) {
            if (cursor.moveToFirst()) {
                timestamp = cursor.getLong(0);
            }
            cursor.close();
        }
        return timestamp;
    }

}
