DatabaseAdapter.java

/*******************************************************************************
 * Copyright (c) 2010 Denis Solonenko.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Public License v2.0
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 *
 * Contributors:
 *     Denis Solonenko - initial API and implementation
 *     Abdsandryk - implement getAllExpenses method for bill filtering
 ******************************************************************************/
package ru.orangesoftware.financisto2.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.support.v4.util.LongSparseArray;
import android.util.Log;

import org.androidannotations.annotations.EBean;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.EnumMap;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import ru.orangesoftware.financisto2.R;
import ru.orangesoftware.financisto2.blotter.BlotterFilter;
import ru.orangesoftware.financisto2.datetime.DateUtils;
import ru.orangesoftware.financisto2.db.DatabaseHelper.CategoryColumns;
import ru.orangesoftware.financisto2.db.DatabaseHelper.TransactionColumns;
import ru.orangesoftware.financisto2.db.DatabaseHelper.deleteLogColumns;
import ru.orangesoftware.financisto2.filter.Criteria;
import ru.orangesoftware.financisto2.filter.WhereFilter;
import ru.orangesoftware.financisto2.model.Account;
import ru.orangesoftware.financisto2.model.Attribute;
import ru.orangesoftware.financisto2.model.Budget;
import ru.orangesoftware.financisto2.model.Category;
import ru.orangesoftware.financisto2.model.CategoryTree;
import ru.orangesoftware.financisto2.model.Currency;
import ru.orangesoftware.financisto2.model.Payee;
import ru.orangesoftware.financisto2.model.Project;
import ru.orangesoftware.financisto2.model.RestoredTransaction;
import ru.orangesoftware.financisto2.model.SystemAttribute;
import ru.orangesoftware.financisto2.model.Total;
import ru.orangesoftware.financisto2.model.TotalError;
import ru.orangesoftware.financisto2.model.Transaction;
import ru.orangesoftware.financisto2.model.TransactionAttribute;
import ru.orangesoftware.financisto2.model.TransactionStatus;
import ru.orangesoftware.financisto2.rates.ExchangeRate;
import ru.orangesoftware.financisto2.rates.ExchangeRateProvider;
import ru.orangesoftware.financisto2.rates.ExchangeRatesCollection;
import ru.orangesoftware.financisto2.rates.HistoryExchangeRates;
import ru.orangesoftware.financisto2.rates.LatestExchangeRates;
import ru.orangesoftware.orb.Expressions;

import static ru.orangesoftware.financisto2.db.DatabaseHelper.ACCOUNT_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.AccountColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.AttributeViewColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.BlotterColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CATEGORY_ATTRIBUTE_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CATEGORY_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CCARD_CLOSING_DATE_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CategoryAttributeColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CategoryViewColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CreditCardClosingDateColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.DELETE_LOG_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.EXCHANGE_RATES_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.ExchangeRateColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.PAYEE_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.TRANSACTION_ATTRIBUTE_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.TRANSACTION_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.TransactionAttributeColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.V_ALL_TRANSACTIONS;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.V_ATTRIBUTES;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.V_BLOTTER;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.V_CATEGORY;

@EBean(scope = EBean.Scope.Singleton)
public class DatabaseAdapter extends MyEntityManager {

    private final Context context;

    private boolean updateAccountBalance = true;

    public DatabaseAdapter(Context context) {
        super(context);
        this.context = context;
    }

    // ===================================================================
    // ACCOUNT
    // ===================================================================

    private static final String UPDATE_ORPHAN_TRANSACTIONS_1 = "UPDATE " + TRANSACTION_TABLE + " SET " +
            TransactionColumns.to_account_id + "=0, " +
            TransactionColumns.to_amount + "=0 " +
            "WHERE " + TransactionColumns.to_account_id + "=?";
    private static final String UPDATE_ORPHAN_TRANSACTIONS_2 = "UPDATE " + TRANSACTION_TABLE + " SET " +
            TransactionColumns.from_account_id + "=" + TransactionColumns.to_account_id + ", " +
            TransactionColumns.from_amount + "=" + TransactionColumns.to_amount + ", " +
            TransactionColumns.to_account_id + "=0, " +
            TransactionColumns.to_amount + "=0, " +
            TransactionColumns.parent_id + "=0 " +
            "WHERE " + TransactionColumns.from_account_id + "=? AND " +
            TransactionColumns.to_account_id + ">0";

    public int deleteAccount(long id) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            String[] sid = new String[]{String.valueOf(id)};
            Account a = load(Account.class, id);
            writeDeleteLog(TRANSACTION_TABLE, a.remoteKey);
            db.execSQL(UPDATE_ORPHAN_TRANSACTIONS_1, sid);
            db.execSQL(UPDATE_ORPHAN_TRANSACTIONS_2, sid);
            db.delete(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.TRANSACTION_ID
                    + " in (SELECT _id from " + TRANSACTION_TABLE + " where " + TransactionColumns.from_account_id + "=?)", sid);
            db.delete(TRANSACTION_TABLE, TransactionColumns.from_account_id + "=?", sid);
            int count = db.delete(ACCOUNT_TABLE, "_id=?", sid);
            db.setTransactionSuccessful();
            return count;
        } finally {
            db.endTransaction();
        }

    }

    // ===================================================================
    // TRANSACTION
    // ===================================================================

    public Transaction getTransaction(long id) {
        Transaction t = get(Transaction.class, id);
        if (t != null) {
            t.systemAttributes = getSystemAttributesForTransaction(id);
            if (t.isSplitParent()) {
                t.splits = getSplitsForTransaction(t.id);
            }
            return t;
        }
        return new Transaction();
    }

    public Cursor getBlotter(WhereFilter filter) {
        return getBlotter(V_BLOTTER, filter);
    }

    public Cursor getBlotterForAccount(WhereFilter filter) {
        WhereFilter accountFilter = enhanceFilterForAccountBlotter(filter);
        return getBlotter(V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS, accountFilter);
    }

    public static WhereFilter enhanceFilterForAccountBlotter(WhereFilter filter) {
        WhereFilter accountFilter = WhereFilter.copyOf(filter);
        accountFilter.put(Criteria.raw(BlotterColumns.parent_id + "=0 OR " + BlotterColumns.is_transfer + "=-1"));
        return accountFilter;
    }

    public Cursor getBlotterForAccountWithSplits(WhereFilter filter) {
        return getBlotter(V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS, filter);
    }

    private Cursor getBlotter(String view, WhereFilter filter) {
        long t0 = System.currentTimeMillis();
        try {
            String sortOrder = getBlotterSortOrder(filter);
            return db().query(view, BlotterColumns.NORMAL_PROJECTION,
                    filter.getSelection(), filter.getSelectionArgs(), null, null,
                    sortOrder);
        } finally {
            long t1 = System.currentTimeMillis();
            Log.i("DB", "getBlotter " + (t1 - t0) + "ms");
        }
    }

    private String getBlotterSortOrder(WhereFilter filter) {
        String sortOrder = filter.getSortOrder();
        if (sortOrder == null || sortOrder.length() == 0) {
            sortOrder = BlotterFilter.SORT_NEWER_TO_OLDER + "," + BlotterFilter.SORT_NEWER_TO_OLDER_BY_ID;
        } else {
            if (sortOrder.contains(BlotterFilter.SORT_NEWER_TO_OLDER)) {
                sortOrder += "," + BlotterFilter.SORT_NEWER_TO_OLDER_BY_ID;
            } else {
                sortOrder += "," + BlotterFilter.SORT_OLDER_TO_NEWER_BY_ID;
            }
        }
        return sortOrder;
    }

    public Cursor getAllTemplates(WhereFilter filter) {
        long t0 = System.currentTimeMillis();
        try {
            return db().query(V_ALL_TRANSACTIONS, BlotterColumns.NORMAL_PROJECTION,
                    filter.getSelection(), filter.getSelectionArgs(), null, null,
                    BlotterFilter.SORT_NEWER_TO_OLDER);
        } finally {
            long t1 = System.currentTimeMillis();
            Log.i("DB", "getBlotter " + (t1 - t0) + "ms");
        }
    }

    public Cursor getBlotterWithSplits(String where) {
        return db().query(V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS, BlotterColumns.NORMAL_PROJECTION, where, null, null, null,
                BlotterColumns.datetime + " DESC");
    }

    private static final String ACCOUNT_LAST_CATEGORY_UPDATE = "UPDATE " + ACCOUNT_TABLE
            + " SET " + AccountColumns.LAST_CATEGORY_ID + "=? "
            + " WHERE " + AccountColumns.ID + "=?";

    private static final String ACCOUNT_LAST_ACCOUNT_UPDATE = "UPDATE " + ACCOUNT_TABLE
            + " SET " + AccountColumns.LAST_ACCOUNT_ID + "=? "
            + " WHERE " + AccountColumns.ID + "=?";

    private static final String PAYEE_LAST_CATEGORY_UPDATE = "UPDATE " + PAYEE_TABLE
            + " SET last_category_id=(?) WHERE _id=?";

    private static final String CATEGORY_LAST_PROJECT_UPDATE = "UPDATE " + CATEGORY_TABLE
            + " SET last_project_id=(?) WHERE _id=?";

    private void updateLastUsed(Transaction t) {
        SQLiteDatabase db = db();
        if (t.isTransfer()) {
            db.execSQL(ACCOUNT_LAST_ACCOUNT_UPDATE, new Object[]{t.toAccountId, t.fromAccountId});
        }
        db.execSQL(ACCOUNT_LAST_CATEGORY_UPDATE, new Object[]{t.categoryId, t.fromAccountId});
        db.execSQL(PAYEE_LAST_CATEGORY_UPDATE, new Object[]{t.categoryId, t.payeeId});
        db.execSQL(CATEGORY_LAST_PROJECT_UPDATE, new Object[]{t.projectId, t.categoryId});
    }

    public long duplicateTransaction(long id) {
        return duplicateTransaction(id, 0, 1);
    }

    public long duplicateTransactionWithMultiplier(long id, int multiplier) {
        return duplicateTransaction(id, 0, multiplier);
    }

    public long duplicateTransactionAsTemplate(long id) {
        return duplicateTransaction(id, 1, 1);
    }

    private long duplicateTransaction(long id, int isTemplate, int multiplier) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            long now = System.currentTimeMillis();
            Transaction transaction = getTransaction(id);
            if (transaction.isSplitChild()) {
                id = transaction.parentId;
                transaction = getTransaction(id);
            }
            transaction.lastRecurrence = now;
            updateTransaction(transaction);
            transaction.id = -1;
            transaction.isTemplate = isTemplate;
            transaction.dateTime = now;
            transaction.remoteKey = null;
            if (isTemplate == 0) {
                transaction.recurrence = null;
                transaction.notificationOptions = null;
            }
            if (multiplier > 1) {
                transaction.fromAmount *= multiplier;
                transaction.toAmount *= multiplier;
            }
            long transactionId = insertTransaction(transaction);
            Map<Long, String> attributesMap = getAllAttributesForTransaction(id);
            LinkedList<TransactionAttribute> attributes = new LinkedList<TransactionAttribute>();
            for (long attributeId : attributesMap.keySet()) {
                TransactionAttribute ta = new TransactionAttribute();
                ta.attributeId = attributeId;
                ta.value = attributesMap.get(attributeId);
                attributes.add(ta);
            }
            if (attributes.size() > 0) {
                insertAttributes(transactionId, attributes);
            }
            List<Transaction> splits = getSplitsForTransaction(id);
            if (multiplier > 1) {
                for (Transaction split : splits) {
                    split.fromAmount *= multiplier;
                    split.remoteKey = null;
                }
            }
            transaction.id = transactionId;
            transaction.splits = splits;
            insertSplits(transaction);
            db.setTransactionSuccessful();
            return transactionId;
        } finally {
            db.endTransaction();
        }
    }

    public long insertOrUpdate(Transaction transaction) {
        return insertOrUpdate(transaction, Collections.<TransactionAttribute>emptyList());
    }

    public long insertOrUpdate(Transaction transaction, List<TransactionAttribute> attributes) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            long id = insertOrUpdateInTransaction(transaction, attributes);
            db.setTransactionSuccessful();
            return id;
        } finally {
            db.endTransaction();
        }
    }

    public long insertOrUpdateInTransaction(Transaction transaction, List<TransactionAttribute> attributes) {
        long transactionId;
        transaction.lastRecurrence = System.currentTimeMillis();
        if (transaction.id == -1) {
            transactionId = insertTransaction(transaction);
        } else {
            updateTransaction(transaction);
            transactionId = transaction.id;
            db().delete(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.TRANSACTION_ID + "=?",
                    new String[]{String.valueOf(transactionId)});
            deleteSplitsForParentTransaction(transactionId);
        }
        if (attributes != null) {
            insertAttributes(transactionId, attributes);
        }
        transaction.id = transactionId;
        insertSplits(transaction);
        updateAccountLastTransactionDate(transaction.fromAccountId);
        updateAccountLastTransactionDate(transaction.toAccountId);
        return transactionId;
    }

    public void insertWithoutUpdatingBalance(Transaction transaction) {
        updateAccountBalance = false;
        try {
            transaction.id = insertTransaction(transaction);
            insertSplits(transaction);
        } finally {
            updateAccountBalance = true;
        }
    }

    private void insertAttributes(long transactionId, List<TransactionAttribute> attributes) {
        for (TransactionAttribute a : attributes) {
            a.transactionId = transactionId;
            ContentValues values = a.toValues();
            db().insert(TRANSACTION_ATTRIBUTE_TABLE, null, values);
        }
    }

    private void insertAttributes(long transactionId, Map<Long, String> categoryAttributes) {
        if (categoryAttributes != null && categoryAttributes.size() > 0) {
            List<TransactionAttribute> attributes = new LinkedList<TransactionAttribute>();
            for (Map.Entry<Long, String> e : categoryAttributes.entrySet()) {
                TransactionAttribute a = new TransactionAttribute();
                a.attributeId = e.getKey();
                a.value = e.getValue();
                attributes.add(a);
            }
            insertAttributes(transactionId, attributes);
        }
    }

    private void insertSplits(Transaction parent) {
        List<Transaction> splits = parent.splits;
        if (splits != null) {
            for (Transaction split : splits) {
                split.id = -1;
                split.parentId = parent.id;
                split.dateTime = parent.dateTime;
                split.fromAccountId = parent.fromAccountId;
                split.payeeId = parent.payeeId;
                split.isTemplate = parent.isTemplate;
                split.status = parent.status;
                updateSplitOriginalAmount(parent, split);
                long splitId = insertTransaction(split);
                insertAttributes(splitId, split.categoryAttributes);
            }
        }
    }

    private void updateSplitOriginalAmount(Transaction parent, Transaction split) {
        if (parent.originalCurrencyId > 0) {
            split.originalCurrencyId = parent.originalCurrencyId;
            split.originalFromAmount = split.fromAmount;
            split.fromAmount = calculateAmountInAccountCurrency(parent, split.fromAmount);
        }
    }

    private long calculateAmountInAccountCurrency(Transaction parent, long amount) {
        double rate = getRateFromParent(parent);
        return (long) (rate * amount);
    }

    private double getRateFromParent(Transaction parent) {
        if (parent.originalFromAmount != 0) {
            return Math.abs(1.0 * parent.fromAmount / parent.originalFromAmount);
        }
        return 0;
    }

    private long insertTransaction(Transaction t) {
        t.updatedOn = System.currentTimeMillis();
        long id = db().insert(TRANSACTION_TABLE, null, t.toValues());
        if (updateAccountBalance) {
            if (!t.isTemplateLike()) {
                if (t.isSplitChild()) {
                    if (t.isTransfer()) {
                        updateToAccountBalance(t, id);
                    }
                } else {
                    updateFromAccountBalance(t, id);
                    updateToAccountBalance(t, id);
                    updateLastUsed(t);
                }
            }
        }
        return id;
    }

    private void updateFromAccountBalance(Transaction t, long id) {
        updateAccountBalance(t.fromAccountId, t.fromAmount);
        insertRunningBalance(t.fromAccountId, id, t.dateTime, t.fromAmount, t.fromAmount);
    }

    private void updateToAccountBalance(Transaction t, long id) {
        updateAccountBalance(t.toAccountId, t.toAmount);
        insertRunningBalance(t.toAccountId, id, t.dateTime, t.toAmount, t.toAmount);
    }

    private void updateTransaction(Transaction t) {
        Transaction oldT = null;
        if (t.isNotTemplateLike()) {
            oldT = getTransaction(t.id);
            updateAccountBalance(oldT.fromAccountId, oldT.fromAmount, t.fromAccountId, t.fromAmount);
            updateAccountBalance(oldT.toAccountId, oldT.toAmount, t.toAccountId, t.toAmount);
            updateRunningBalance(oldT, t);
        }
        t.updatedOn = System.currentTimeMillis();
        db().update(TRANSACTION_TABLE, t.toValues(), TransactionColumns._id + "=?",
                new String[]{String.valueOf(t.id)});
        if (oldT != null) {
            updateAccountLastTransactionDate(oldT.fromAccountId);
            updateAccountLastTransactionDate(oldT.toAccountId);
        }
    }

    public void updateTransactionStatus(long id, TransactionStatus status) {
        Transaction t = getTransaction(id);
        t.status = status;
        updateTransaction(t);
    }

    public void deleteTransaction(long id) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            deleteTransactionNoDbTransaction(id);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public void deleteTransactionNoDbTransaction(long id) {
        Transaction t = getTransaction(id);
        if (t.isNotTemplateLike()) {
            revertFromAccountBalance(t);
            revertToAccountBalance(t);
            updateAccountLastTransactionDate(t.fromAccountId);
            updateAccountLastTransactionDate(t.toAccountId);
        }
        String[] sid = new String[]{String.valueOf(id)};
        SQLiteDatabase db = db();
        db.delete(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.TRANSACTION_ID + "=?", sid);
        db.delete(TRANSACTION_TABLE, TransactionColumns._id + "=?", sid);
        writeDeleteLog(TRANSACTION_TABLE, t.remoteKey);
        deleteSplitsForParentTransaction(id);
    }

    private void deleteSplitsForParentTransaction(long parentId) {
        List<Transaction> splits = getSplitsForTransaction(parentId);
        SQLiteDatabase db = db();
        for (Transaction split : splits) {
            if (split.isTransfer()) {
                revertToAccountBalance(split);
            }
            db.delete(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.TRANSACTION_ID + "=?",
                    new String[]{String.valueOf(split.id)});
            writeDeleteLog(TRANSACTION_TABLE, split.remoteKey);
        }

        db.delete(TRANSACTION_TABLE, TransactionColumns.parent_id + "=?", new String[]{String.valueOf(parentId)});

    }

    private void revertFromAccountBalance(Transaction t) {
        updateAccountBalance(t.fromAccountId, -t.fromAmount);
        deleteRunningBalance(t.fromAccountId, t.id, t.fromAmount, t.dateTime);
    }

    private void revertToAccountBalance(Transaction t) {
        updateAccountBalance(t.toAccountId, -t.toAmount);
        deleteRunningBalance(t.toAccountId, t.id, t.toAmount, t.dateTime);
    }

    private void updateAccountBalance(long oldAccountId, long oldAmount, long newAccountId, long newAmount) {
        if (oldAccountId == newAccountId) {
            updateAccountBalance(newAccountId, newAmount - oldAmount);
        } else {
            updateAccountBalance(oldAccountId, -oldAmount);
            updateAccountBalance(newAccountId, newAmount);
        }
    }

    private static final String ACCOUNT_TOTAL_AMOUNT_UPDATE = "UPDATE " + ACCOUNT_TABLE
            + " SET " + AccountColumns.TOTAL_AMOUNT + "=" + AccountColumns.TOTAL_AMOUNT + "+(?) "
            + " WHERE " + AccountColumns.ID + "=?";

    private void updateAccountBalance(long accountId, long deltaAmount) {
        if (accountId <= 0) {
            return;
        }
        db().execSQL(ACCOUNT_TOTAL_AMOUNT_UPDATE, new Object[]{deltaAmount, accountId});
    }

    private static final String INSERT_RUNNING_BALANCE =
            "insert or replace into running_balance(account_id,transaction_id,datetime,balance) values (?,?,?,?)";

    private static final String UPDATE_RUNNING_BALANCE =
            "update running_balance set balance = balance+(?) where account_id = ? and datetime > ?";

    private static final String DELETE_RUNNING_BALANCE =
            "delete from running_balance where account_id = ? and transaction_id = ?";

    private void insertRunningBalance(long accountId, long transactionId, long datetime, long amount, long deltaAmount) {
        if (accountId <= 0) {
            return;
        }
        long previousTransactionBalance = fetchAccountBalanceAtTheTime(accountId, datetime);
        SQLiteDatabase db = db();
        db.execSQL(INSERT_RUNNING_BALANCE, new Object[]{accountId, transactionId, datetime, previousTransactionBalance + amount});
        db.execSQL(UPDATE_RUNNING_BALANCE, new Object[]{deltaAmount, accountId, datetime});
    }

    private void updateRunningBalance(Transaction oldTransaction, Transaction newTransaction) {
        deleteRunningBalance(oldTransaction.fromAccountId, oldTransaction.id, oldTransaction.fromAmount, oldTransaction.dateTime);
        insertRunningBalance(newTransaction.fromAccountId, newTransaction.id, newTransaction.dateTime,
                newTransaction.fromAmount, newTransaction.fromAmount);
        deleteRunningBalance(oldTransaction.toAccountId, oldTransaction.id, oldTransaction.toAmount, oldTransaction.dateTime);
        insertRunningBalance(newTransaction.toAccountId, newTransaction.id, newTransaction.dateTime,
                newTransaction.toAmount, newTransaction.toAmount);
    }

    private void deleteRunningBalance(long accountId, long transactionId, long amount, long dateTime) {
        if (accountId <= 0) {
            return;
        }
        SQLiteDatabase db = db();
        db.execSQL(DELETE_RUNNING_BALANCE, new Object[]{accountId, transactionId});
        db.execSQL(UPDATE_RUNNING_BALANCE, new Object[]{-amount, accountId, dateTime});
    }

    private long fetchAccountBalanceAtTheTime(long accountId, long datetime) {
        return DatabaseUtils.rawFetchLongValue(this, "select balance from running_balance where account_id = ? and datetime <= ? order by datetime desc, transaction_id desc limit 1",
                new String[]{String.valueOf(accountId), String.valueOf(datetime)});
    }

    // ===================================================================
    // CATEGORY
    // ===================================================================

//    public long insertOrUpdate(Category category, List<Attribute> attributes) {
//        SQLiteDatabase db = db();
//        db.beginTransaction();
//        try {
//            long id;
//            if (category.id == -1) {
//                id = insertCategory(category);
//            } else {
//                updateCategory(category);
//                id = category.id;
//            }
//            addAttributes(id, attributes);
//            category.id = id;
//            db.setTransactionSuccessful();
//            return id;
//        } finally {
//            db.endTransaction();
//        }
//    }

    public void addAttributes(long categoryId, List<Attribute> attributes) {
        SQLiteDatabase db = db();
        db.delete(CATEGORY_ATTRIBUTE_TABLE, CategoryAttributeColumns.CATEGORY_ID + "=?", new String[]{String.valueOf(categoryId)});
        if (attributes != null && attributes.size() > 0) {
            ContentValues values = new ContentValues();
            values.put(CategoryAttributeColumns.CATEGORY_ID, categoryId);
            for (Attribute a : attributes) {
                values.put(CategoryAttributeColumns.ATTRIBUTE_ID, a.id);
                db.insert(CATEGORY_ATTRIBUTE_TABLE, null, values);
            }
        }
    }

//    private long insertCategory(Category category) {
//        CategoryTree tree = getCategoriesTree(false);
//        long parentId = category.getParentId();
//        if (parentId == Category.NO_CATEGORY_ID) {
////            if (!tree.isEmpty()) {
////                return insertAsLast(category, tree);
////            }
//        } else {
//            LongSparseArray<Category> map = tree.asIdMap();
//            Category parent = map.get(parentId);
//            if (parent != null && parent.hasChildren()) {
//                //CategoryTree children = parent.children;
//                //return insertAsLast(category, children);
//            }
//        }
//        return insertChildCategory(parentId, category);
//    }

//    private long insertAsLast(Category category, CategoryTree tree) {
////        long mateId = tree.getAt(tree.size() - 1).id;
////        return insertMateCategory(mateId, category);
//        return 0;
//    }

//    private long updateCategory(Category category) {
//        Category oldCategory = getCategory(category.id);
//        if (oldCategory.getParentId() == category.getParentId()) {
//            updateCategory(category.id, category.title, category.type);
//            updateChildCategoriesType(category.type, category.left, category.right);
//        } else {
//            moveCategory(category);
//        }
//        return category.id;
//    }

//    private void moveCategory(Category category) {
//        CategoryTree tree = getCategoriesTree(false);
//        LongSparseArray<Category> map = tree.asIdMap();
//        Category oldCategory = map.get(category.id);
//        if (oldCategory != null) {
//            Category oldParent = map.get(oldCategory.getParentId());
//            if (oldParent != null) {
//                oldParent.removeChild(oldCategory);
//            } else {
//                //tree.remove(oldCategory);
//            }
//            Category newParent = map.get(category.getParentId());
//            int newCategoryType = category.type;
//            if (newParent != null) {
//                newParent.addChild(oldCategory);
//                newCategoryType = newParent.type;
//            } else {
//                //tree.add(oldCategory);
//            }
//            tree.reIndex();
//            updateCategoryTreeInTransaction(tree);
//            updateCategory(category.id, category.title, newCategoryType);
//            updateChildCategoriesType(newCategoryType, oldCategory.left, oldCategory.right);
//        }
//    }


//    public Category getCategory(long id) {
//        if (id == Category.NO_CATEGORY_ID) return Category.noCategory(context);
//        if (id == Category.SPLIT_CATEGORY_ID) return Category.splitCategory(context);
//        Category category = getCategoryNoParent(id);
//        if (category != null) {
//            if (category.parentId > 0)  {
//                Category parent = getCategoryNoParent(category.parentId);
//                if (parent != null) {
//                    category.parent = parent;
//                } else {
//                    category.parent = new Category(category.parentId);
//                }
//            };
//            return category;
//        }
//        return new Category(-1);
//    }
//
//    protected Category getCategoryNoParent(long id) {
//        return get(Category.class, id);
//    }
//
//    public Category getCategoryByLeft(long left) {
//        Category category = createQuery(Category.class).where(Expressions.eq("left", left)).uniqueResult();
//        if (category != null) {
//            return category;
//        } else {
//            return new Category(-1);
//        }
//    }
//
//    public CategoryTree getCategoriesTree(boolean includeNoCategory) {
//        return null;
////        Cursor c = getCategories(includeNoCategory);
////        try {
////            return CategoryTree.createFromCursor(c, new NodeCreator<Category>() {
////                @Override
////                public Category createNode(Cursor c) {
////                    return Category.formCursor(c);
////                }
////            });
////        } finally {
////            c.close();
////        }
//    }

//    public CategoryTree getAllCategoriesTree() {
//        return null;
////        Cursor c = getAllCategories();
////        try {
////            return CategoryTree.createFromCursor(c, new NodeCreator<Category>() {
////                @Override
////                public Category createNode(Cursor c) {
////                    return Category.formCursor(c);
////                }
////            });
////        } finally {
////            c.close();
////        }
//    }

//    public LongSparseArray<Category> getAllCategoriesMap() {
//        return getAllCategoriesTree().asIdMap();
//    }
//
//    public List<Category> getCategoriesList(boolean includeNoCategory) {
//        Cursor c = getCategories(includeNoCategory);
//        return categoriesAsList(c);
//    }
//
//    public Cursor getAllCategories() {
//        return db().query(V_CATEGORY, CategoryViewColumns.NORMAL_PROJECTION,
//                null, null, null, null, null);
//    }
//
//    public List<Category> getAllCategoriesList() {
//        Cursor c = getAllCategories();
//        return categoriesAsList(c);
//    }
//
//    private List<Category> categoriesAsList(Cursor c) {
//        ArrayList<Category> list = new ArrayList<Category>();
//        try {
//            while (c.moveToNext()) {
//                Category category = Category.formCursor(c);
//                list.add(category);
//            }
//        } finally {
//            c.close();
//        }
//        return list;
//    }
//
//    public Cursor getCategories(boolean includeNoCategory) {
//        return db().query(V_CATEGORY, CategoryViewColumns.NORMAL_PROJECTION,
//                includeNoCategory ? CategoryViewColumns._id + ">=0" : CategoryViewColumns._id + ">0", null, null, null, null);
//    }
//
//    public Cursor getCategoriesWithoutSubtree(long id) {
//        SQLiteDatabase db = db();
//        long left = 0, right = 0;
//        Cursor c = db.query(CATEGORY_TABLE, new String[]{CategoryColumns.left.name(), CategoryColumns.right.name()},
//                CategoryColumns._id + "=?", new String[]{String.valueOf(id)}, null, null, null);
//        try {
//            if (c.moveToFirst()) {
//                left = c.getLong(0);
//                right = c.getLong(1);
//            }
//        } finally {
//            c.close();
//        }
//        return db.query(V_CATEGORY, CategoryViewColumns.NORMAL_PROJECTION,
//                "(NOT (" + CategoryViewColumns.left + ">=? AND " + CategoryColumns.right + "<=?)) AND " + CategoryViewColumns._id + ">=0",
//                new String[]{String.valueOf(left), String.valueOf(right)}, null, null, null);
//    }
//
//    public List<Category> getCategoriesWithoutSubtreeAsList(long categoryId) {
//        List<Category> list = new ArrayList<Category>();
//        Cursor c = getCategoriesWithoutSubtree(categoryId);
//        try {
//            while (c.moveToNext()) {
//                Category category = Category.formCursor(c);
//                list.add(category);
//            }
//            return list;
//        } finally {
//            c.close();
//        }
//    }
//
//    private static final String INSERT_CATEGORY_UPDATE_RIGHT = "UPDATE " + CATEGORY_TABLE + " SET " + CategoryColumns.right + "=" + CategoryColumns.right + "+2 WHERE " + CategoryColumns.right + ">?";
//    private static final String INSERT_CATEGORY_UPDATE_LEFT = "UPDATE " + CATEGORY_TABLE + " SET " + CategoryColumns.left + "=" + CategoryColumns.left + "+2 WHERE " + CategoryColumns.left + ">?";
//
//    public long insertChildCategory(long parentId, Category category) {
//        //DECLARE v_leftkey INT UNSIGNED DEFAULT 0;
//        //SELECT l INTO v_leftkey FROM `nset` WHERE `id` = ParentID;
//        //UPDATE `nset` SET `r` = `r` + 2 WHERE `r` > v_leftkey;
//        //UPDATE `nset` SET `l` = `l` + 2 WHERE `l` > v_leftkey;
//        //INSERT INTO `nset` (`name`, `l`, `r`) VALUES (NodeName, v_leftkey + 1, v_leftkey + 2);
//        int type = getActualCategoryType(parentId, category);
//        return insertCategory(CategoryColumns.left.name(), parentId, category.title, type);
//    }
//
//    public long insertMateCategory(long categoryId, Category category) {
//        //DECLARE v_rightkey INT UNSIGNED DEFAULT 0;
//        //SELECT `r` INTO v_rightkey FROM `nset` WHERE `id` = MateID;
//        //UPDATE `	nset` SET `r` = `r` + 2 WHERE `r` > v_rightkey;
//        //UPDATE `nset` SET `l` = `l` + 2 WHERE `l` > v_rightkey;
//        //INSERT `nset` (`name`, `l`, `r`) VALUES (NodeName, v_rightkey + 1, v_rightkey + 2);
//        Category mate = getCategory(categoryId);
//        long parentId = mate.getParentId();
//        int type = getActualCategoryType(parentId, category);
//        return insertCategory(CategoryColumns.right.name(), categoryId, category.title, type);
//    }
//
//    private int getActualCategoryType(long parentId, Category category) {
//        int type = category.type;
//        if (parentId > 0) {
//            Category parent = getCategory(parentId);
//            type = parent.type;
//        }
//        return type;
//    }
//
//    private long insertCategory(String field, long categoryId, String title, int type) {
//        int num = 0;
//        SQLiteDatabase db = db();
//        Cursor c = db.query(CATEGORY_TABLE, new String[]{field},
//                CategoryColumns._id + "=?", new String[]{String.valueOf(categoryId)}, null, null, null);
//        try {
//            if (c.moveToFirst()) {
//                num = c.getInt(0);
//            }
//        } finally {
//            c.close();
//        }
//        String[] args = new String[]{String.valueOf(num)};
//        db.execSQL(INSERT_CATEGORY_UPDATE_RIGHT, args);
//        db.execSQL(INSERT_CATEGORY_UPDATE_LEFT, args);
//        ContentValues values = new ContentValues();
//        values.put(CategoryColumns.title.name(), title);
//        int left = num + 1;
//        int right = num + 2;
//        values.put(CategoryColumns.left.name(), left);
//        values.put(CategoryColumns.right.name(), right);
//        values.put(CategoryColumns.type.name(), type);
//        long id = db.insert(CATEGORY_TABLE, null, values);
//        updateChildCategoriesType(type, left, right);
//        return id;
//    }
//
//    private static final String CATEGORY_UPDATE_CHILDREN_TYPES = "UPDATE " + CATEGORY_TABLE + " SET " + CategoryColumns.type + "=? WHERE " + CategoryColumns.left + ">? AND " + CategoryColumns.right + "<?";
//
//    private void updateChildCategoriesType(int type, int left, int right) {
//        db().execSQL(CATEGORY_UPDATE_CHILDREN_TYPES, new Object[]{type, left, right});
//    }
//
//    private static final String DELETE_CATEGORY_UPDATE1 = "UPDATE " + TRANSACTION_TABLE
//            + " SET " + TransactionColumns.category_id + "=0 WHERE "
//            + TransactionColumns.category_id + " IN ("
//            + "SELECT " + CategoryColumns._id + " FROM " + CATEGORY_TABLE + " WHERE "
//            + CategoryColumns.left + " BETWEEN ? AND ?)";
//    private static final String DELETE_CATEGORY_UPDATE2 = "UPDATE " + CATEGORY_TABLE
//            + " SET " + CategoryColumns.left + "=(CASE WHEN " + CategoryColumns.left + ">%s THEN "
//            + CategoryColumns.left + "-%s ELSE " + CategoryColumns.left + " END),"
//            + CategoryColumns.right + "=" + CategoryColumns.right + "-%s"
//            + " WHERE " + CategoryColumns.right + ">%s";
//
//    public void deleteCategory(long categoryId) {
//        //DECLARE v_leftkey, v_rightkey, v_width INT DEFAULT 0;
//        //
//        //SELECT
//        //	`l`, `r`, `r` - `l` + 1 INTO v_leftkey, v_rightkey, v_width
//        //FROM `nset`
//        //WHERE
//        //	`id` = NodeID;
//        //
//        //DELETE FROM `nset` WHERE `l` BETWEEN v_leftkey AND v_rightkey;
//        //
//        //UPDATE `nset`
//        //SET
//        //	`l` = IF(`l` > v_leftkey, `l` - v_width, `l`),
//        //	`r` = `r` - v_width
//        //WHERE
//        //	`r` > v_rightkey;
//        SQLiteDatabase db = db();
//        int left = 0, right = 0;
//        Cursor c = db.query(CATEGORY_TABLE, new String[]{CategoryColumns.left.name(), CategoryColumns.right.name()},
//                CategoryColumns._id + "=?", new String[]{String.valueOf(categoryId)}, null, null, null);
//        try {
//            if (c.moveToFirst()) {
//                left = c.getInt(0);
//                right = c.getInt(1);
//            }
//        } finally {
//            c.close();
//        }
//        db.beginTransaction();
//        try {
//            Category category = load(Category.class, categoryId);
//            writeDeleteLog(CATEGORY_TABLE, category.remoteKey);
//            int width = right - left + 1;
//            String[] args = new String[]{String.valueOf(left), String.valueOf(right)};
//            db.execSQL(DELETE_CATEGORY_UPDATE1, args);
//            db.delete(CATEGORY_TABLE, CategoryColumns.left + " BETWEEN ? AND ?", args);
//            db.execSQL(String.format(DELETE_CATEGORY_UPDATE2, left, width, width, right));
//            db.setTransactionSuccessful();
//        } finally {
//            db.endTransaction();
//        }
//    }
//
//    private void updateCategory(long id, String title, int type) {
//        ContentValues values = new ContentValues();
//        values.put(CategoryColumns.title.name(), title);
//        values.put(CategoryColumns.type.name(), type);
//        values.remove("updated_on");
//        values.put(CategoryColumns.updated_on.name(), System.currentTimeMillis());
//        db().update(CATEGORY_TABLE, values, CategoryColumns._id + "=?", new String[]{String.valueOf(id)});
//    }
//
//    public void insertCategoryTreeInTransaction(CategoryTree tree) {
//        db().delete("category", "_id > 0", null);
//        insertCategoryInTransaction(tree);
//        updateCategoryTreeInTransaction(tree);
//    }
//
//    private void insertCategoryInTransaction(CategoryTree tree) {
////        for (Category category : tree) {
////            reInsertCategory(category);
////            if (category.hasChildren()) {
////                insertCategoryInTransaction(category.children);
////            }
////        }
//    }
//
//    public void updateCategoryTree(CategoryTree tree) {
//        SQLiteDatabase db = db();
//        db.beginTransaction();
//        try {
//            updateCategoryTreeInTransaction(tree);
//            db.setTransactionSuccessful();
//        } finally {
//            db.endTransaction();
//        }
//    }
//
//    private static final String WHERE_CATEGORY_ID = CategoryColumns._id + "=?";
//
//    private void updateCategoryTreeInTransaction(CategoryTree tree) {
//        int left = 1;
//        int right = 2;
//        ContentValues values = new ContentValues();
//        String[] sid = new String[1];
////        for (Category c : tree) {
////            values.put(CategoryColumns.left.name(), c.left);
////            values.put(CategoryColumns.right.name(), c.right);
////            sid[0] = String.valueOf(c.id);
////            db().update(CATEGORY_TABLE, values, WHERE_CATEGORY_ID, sid);
////            if (c.hasChildren()) {
////                updateCategoryTreeInTransaction(c.children);
////            }
////            if (c.left < left) {
////                left = c.left;
////            }
////            if (c.right > right) {
////                right = c.right;
////            }
////        }
//        values.put(CategoryColumns.left.name(), left - 1);
//        values.put(CategoryColumns.right.name(), right + 1);
//        sid[0] = String.valueOf(Category.NO_CATEGORY_ID);
//        db().update(CATEGORY_TABLE, values, WHERE_CATEGORY_ID, sid);
//    }

    // ===================================================================
    // ATTRIBUTES
    // ===================================================================

    public List<Attribute> getAttributesForCategory(long categoryId) {
        LongSparseArray<Attribute> attributesMap = getAllAttributesMap();
        Cursor c = db().query(V_ATTRIBUTES, AttributeViewColumns.NORMAL_PROJECTION,
                AttributeViewColumns.CATEGORY_ID + "=?", new String[]{String.valueOf(categoryId)},
                null, null, AttributeViewColumns.NAME);
        return collectAttributesFromCursor(attributesMap, c);
    }

    public List<Attribute> getAllAttributesForCategory(Category category) {
        LongSparseArray<Attribute> attributesMap = getAllAttributesMap();
        Cursor c = db().query(V_ATTRIBUTES, AttributeViewColumns.NORMAL_PROJECTION,
                AttributeViewColumns.CATEGORY_LEFT + "<= ? AND " + AttributeViewColumns.CATEGORY_RIGHT + " >= ?",
                new String[]{String.valueOf(category.left), String.valueOf(category.right)},
                null, null, AttributeViewColumns.NAME);
        return collectAttributesFromCursor(attributesMap, c);
    }

    protected List<Attribute> collectAttributesFromCursor(LongSparseArray<Attribute> attributesMap, Cursor c) {
        try {
            ArrayList<Attribute> list = new ArrayList<Attribute>(c.getCount());
            while (c.moveToNext()) {
                long attributeId = c.getLong(AttributeViewColumns.Indicies.ID);
                Attribute a = attributesMap.get(attributeId);
                if (a != null) {
                    list.add(a);
                }
            }
            return list;
        } finally {
            c.close();
        }
    }

    public Map<Long, String> getAttributesMapping() {
        Cursor c = db().query(V_ATTRIBUTES, AttributeViewColumns.NORMAL_PROJECTION, null, null, null, null,
                AttributeViewColumns.CATEGORY_ID + ", " + AttributeViewColumns.NAME);
        try {
            HashMap<Long, String> attributes = new HashMap<Long, String>();
            StringBuilder sb = null;
            long prevCategoryId = -1;
            while (c.moveToNext()) {
                long categoryId = c.getLong(AttributeViewColumns.Indicies.CATEGORY_ID);
                String name = c.getString(AttributeViewColumns.Indicies.NAME);
                if (prevCategoryId != categoryId) {
                    if (sb == null) {
                        sb = new StringBuilder();
                        sb.append("[");
                    } else {
                        attributes.put(prevCategoryId, sb.append("]").toString());
                        sb.setLength(1);
                    }
                    prevCategoryId = categoryId;
                }
                if (sb.length() > 1) {
                    sb.append(", ");
                }
                sb.append(name);
            }
            if (sb != null) {
                attributes.put(prevCategoryId, sb.append("]").toString());
            }
            return attributes;
        } finally {
            c.close();
        }
    }

    public Map<Long, String> getAllAttributesForTransaction(long transactionId) {
        Cursor c = db().query(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.NORMAL_PROJECTION,
                TransactionAttributeColumns.TRANSACTION_ID + "=? AND " + TransactionAttributeColumns.ATTRIBUTE_ID + ">=0",
                new String[]{String.valueOf(transactionId)},
                null, null, null);
        try {
            HashMap<Long, String> attributes = new HashMap<Long, String>();
            while (c.moveToNext()) {
                long attributeId = c.getLong(TransactionAttributeColumns.Indicies.ATTRIBUTE_ID);
                String value = c.getString(TransactionAttributeColumns.Indicies.VALUE);
                attributes.put(attributeId, value);
            }
            return attributes;
        } finally {
            c.close();
        }
    }

    public EnumMap<SystemAttribute, String> getSystemAttributesForTransaction(long transactionId) {
        Cursor c = db().query(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.NORMAL_PROJECTION,
                TransactionAttributeColumns.TRANSACTION_ID + "=? AND " + TransactionAttributeColumns.ATTRIBUTE_ID + "<0",
                new String[]{String.valueOf(transactionId)},
                null, null, null);
        try {
            EnumMap<SystemAttribute, String> attributes = new EnumMap<SystemAttribute, String>(SystemAttribute.class);
            while (c.moveToNext()) {
                long attributeId = c.getLong(TransactionAttributeColumns.Indicies.ATTRIBUTE_ID);
                String value = c.getString(TransactionAttributeColumns.Indicies.VALUE);
                attributes.put(SystemAttribute.forId(attributeId), value);
            }
            return attributes;
        } finally {
            c.close();
        }
    }


    /**
     * Sets status=CL (Cleared) for the selected transactions
     *
     * @param ids selected transactions' ids
     */
    public void clearSelectedTransactions(long[] ids) {
        String sql = "UPDATE " + TRANSACTION_TABLE + " SET " + TransactionColumns.status + "='" + TransactionStatus.CL + "'," + TransactionColumns.updated_on + "='" + System.currentTimeMillis() + "' ";
        runInTransaction(sql, ids);
    }

    /**
     * Sets status=RC (Reconciled) for the selected transactions
     *
     * @param ids selected transactions' ids
     */
    public void reconcileSelectedTransactions(long[] ids) {
        String sql = "UPDATE " + TRANSACTION_TABLE + " SET " + TransactionColumns.status + "='" + TransactionStatus.RC + "'," + TransactionColumns.updated_on + "='" + System.currentTimeMillis() + "' ";
        runInTransaction(sql, ids);
    }

    /**
     * Deletes the selected transactions
     *
     * @param ids selected transactions' ids
     */
    public void deleteSelectedTransactions(long[] ids) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            for (long id : ids) {
                deleteTransactionNoDbTransaction(id);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void runInTransaction(String sql, long[] ids) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            int count = ids.length;
            int bucket = 100;
            int num = 1 + count / bucket;
            for (int i = 0; i < num; i++) {
                int x = bucket * i;
                int y = Math.min(count, bucket * (i + 1));
                String script = createSql(sql, ids, x, y);
                db.execSQL(script);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private String createSql(String updateSql, long[] ids, int x, int y) {
        StringBuilder sb = new StringBuilder(updateSql)
                .append(" WHERE ")
                .append(TransactionColumns.is_template)
                .append("=0 AND ")
                .append(TransactionColumns.parent_id)
                .append("=0 AND ")
                .append(TransactionColumns._id)
                .append(" IN (");
        for (int i = x; i < y; i++) {
            if (i > x) {
                sb.append(",");
            }
            sb.append(ids[i]);
        }
        sb.append(")");
        return sb.toString();
    }

    private static final String UPDATE_LAST_RECURRENCE =
            "UPDATE " + TRANSACTION_TABLE + " SET " + TransactionColumns.last_recurrence + "=? WHERE " + TransactionColumns._id + "=?";

    public long[] storeMissedSchedules(List<RestoredTransaction> restored, long now) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            int count = restored.size();
            long[] restoredIds = new long[count];
            HashMap<Long, Transaction> transactions = new HashMap<Long, Transaction>();
            for (int i = 0; i < count; i++) {
                RestoredTransaction rt = restored.get(i);
                long transactionId = rt.transactionId;
                Transaction t = transactions.get(transactionId);
                if (t == null) {
                    t = getTransaction(transactionId);
                    transactions.put(transactionId, t);
                }
                t.id = -1;
                t.dateTime = rt.dateTime.getTime();
                t.status = TransactionStatus.RS;
                t.isTemplate = 0;
                restoredIds[i] = insertOrUpdate(t);
                t.id = transactionId;
            }
            for (Transaction t : transactions.values()) {
                db.execSQL(UPDATE_LAST_RECURRENCE, new Object[]{now, t.id});
            }
            db.setTransactionSuccessful();
            return restoredIds;
        } finally {
            db.endTransaction();
        }
    }

    /**
     * @param accountId
     * @param period
     * @return
     */
    public int getCustomClosingDay(long accountId, int period) {
        String where = CreditCardClosingDateColumns.ACCOUNT_ID + "=? AND " +
                CreditCardClosingDateColumns.PERIOD + "=?";

        Cursor c = db().query(CCARD_CLOSING_DATE_TABLE, new String[]{CreditCardClosingDateColumns.CLOSING_DAY},
                where, new String[]{Long.toString(accountId), Integer.toString(period)}, null, null, null);

        int res = 0;
        try {
            if (c != null) {
                if (c.getCount() > 0) {
                    c.moveToFirst();
                    res = c.getInt(0);
                } else {
                    res = 0;
                }
            } else {
                // there is no custom closing day in database for the given account id an period
                res = 0;
            }
        } catch (SQLiteException e) {
            res = 0;
        } finally {
            if (c != null) c.close();
        }
        return res;
    }


    public void setCustomClosingDay(long accountId, int period, int closingDay) {
        ContentValues values = new ContentValues();
        values.put(CreditCardClosingDateColumns.ACCOUNT_ID, Long.toString(accountId));
        values.put(CreditCardClosingDateColumns.PERIOD, Integer.toString(period));
        values.put(CreditCardClosingDateColumns.CLOSING_DAY, Integer.toString(closingDay));
        db().insert(CCARD_CLOSING_DATE_TABLE, null, values);
    }

    public void deleteCustomClosingDay(long accountId, int period) {
        String where = CreditCardClosingDateColumns.ACCOUNT_ID + "=? AND " +
                CreditCardClosingDateColumns.PERIOD + "=?";
        String[] args = new String[]{Long.toString(accountId), Integer.toString(period)};
        db().delete(CCARD_CLOSING_DATE_TABLE, where, args);
    }

    public void updateCustomClosingDay(long accountId, int period, int closingDay) {
        // delete previous content
        deleteCustomClosingDay(accountId, period);

        // save new value
        setCustomClosingDay(accountId, period, closingDay);
    }

    /**
     * Re-populates running_balance table for all accounts
     */
    public void rebuildRunningBalances() {
        List<Account> accounts = getAllAccountsList();
        for (Account account : accounts) {
            rebuildRunningBalanceForAccount(account);
        }
    }

    /**
     * Re-populates running_balance for specific account
     *
     * @param account selected account
     */
    public void rebuildRunningBalanceForAccount(Account account) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            String accountId = String.valueOf(account.getId());
            db.execSQL("delete from running_balance where account_id=?", new Object[]{accountId});
            WhereFilter filter = new WhereFilter("");
            filter.put(Criteria.eq(BlotterFilter.FROM_ACCOUNT_ID, accountId));
            filter.asc("datetime");
            filter.asc("_id");
            Cursor c = getBlotterForAccountWithSplits(filter);
            Object[] values = new Object[4];
            values[0] = accountId;
            try {
                long balance = 0;
                while (c.moveToNext()) {
                    long parentId = c.getLong(BlotterColumns.parent_id.ordinal());
                    int isTransfer = c.getInt(BlotterColumns.is_transfer.ordinal());
                    if (parentId > 0) {
                        if (isTransfer >= 0) {
                            // we only interested in the second part of the transfer-split
                            // which is marked with is_transfer=-1 (see v_blotter_for_account_with_splits)
                            continue;
                        }
                    }
                    long fromAccountId = c.getLong(BlotterColumns.from_account_id.ordinal());
                    long toAccountId = c.getLong(BlotterColumns.to_account_id.ordinal());
                    if (toAccountId > 0 && toAccountId == fromAccountId) {
                        // weird bug when a transfer is done from an account to the same account
                        continue;
                    }
                    balance += c.getLong(DatabaseHelper.BlotterColumns.from_amount.ordinal());
                    values[1] = c.getString(DatabaseHelper.BlotterColumns._id.ordinal());
                    values[2] = c.getString(DatabaseHelper.BlotterColumns.datetime.ordinal());
                    values[3] = balance;
                    db.execSQL("insert into running_balance(account_id,transaction_id,datetime,balance) values (?,?,?,?)", values);
                }
            } finally {
                c.close();
            }
            updateAccountLastTransactionDate(account.id);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private static final String[] SUM_FROM_AMOUNT = new String[]{"sum(from_amount)"};

    public long fetchBudgetBalance(LongSparseArray<Category> categories, LongSparseArray<Project> projects, Budget b) {
        String where = Budget.createWhere(b, categories, projects);
        Cursor c = db().query(V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS, SUM_FROM_AMOUNT, where, null, null, null, null);
        try {
            if (c.moveToNext()) {
                return c.getLong(0);
            }
        } finally {
            c.close();
        }
        return 0;
    }

    public void recalculateAccountsBalances() {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            Cursor accountsCursor = db.query(ACCOUNT_TABLE, new String[]{AccountColumns.ID}, null, null, null, null, null);
            try {
                while (accountsCursor.moveToNext()) {
                    long accountId = accountsCursor.getLong(0);
                    recalculateAccountBalances(accountId);
                }
            } finally {
                accountsCursor.close();
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void recalculateAccountBalances(long accountId) {
        long amount = fetchAccountBalance(accountId);
        ContentValues values = new ContentValues();
        values.put(AccountColumns.TOTAL_AMOUNT, amount);
        db().update(ACCOUNT_TABLE, values, AccountColumns.ID + "=?", new String[]{String.valueOf(accountId)});
        Log.i("DatabaseImport", "Recalculating amount for " + accountId);
    }

    private long fetchAccountBalance(long accountId) {
        Cursor c = db().query(V_BLOTTER_FOR_ACCOUNT_WITH_SPLITS, new String[]{"SUM(" + BlotterColumns.from_amount + ")"},
                BlotterColumns.from_account_id + "=? and (" + BlotterColumns.parent_id + "=0 or " + BlotterColumns.is_transfer + "=-1)",
                new String[]{String.valueOf(accountId)}, null, null, null);
        try {
            if (c.moveToFirst()) {
                return c.getLong(0);
            }
            return 0;
        } finally {
            c.close();
        }
    }

    public void saveRate(ExchangeRate r) {
        replaceRate(r, r.date);
    }

    public void replaceRate(ExchangeRate rate, long originalDate) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            replaceRateInTransaction(rate, originalDate, db);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void replaceRateInTransaction(ExchangeRate rate, long originalDate, SQLiteDatabase db) {
        deleteRateInTransaction(rate.fromCurrencyId, rate.toCurrencyId, originalDate, db);
        saveBothRatesInTransaction(rate, db);
    }

    private void saveBothRatesInTransaction(ExchangeRate r, SQLiteDatabase db) {
        r.date = DateUtils.atMidnight(r.date);
        saveRateInTransaction(db, r);
        saveRateInTransaction(db, r.flip());
    }

    private void saveRateInTransaction(SQLiteDatabase db, ExchangeRate r) {
        ContentValues values = r.toValues();
        values.remove("updated_on");
        values.put(CategoryColumns.updated_on.name(), System.currentTimeMillis());
        db.insert(EXCHANGE_RATES_TABLE, null, values);
    }

    public void saveDownloadedRates(List<ExchangeRate> downloadedRates) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            for (ExchangeRate r : downloadedRates) {
                if (r.isOk()) {
                    replaceRateInTransaction(r, r.date, db);
                }
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public ExchangeRate findRate(Currency fromCurrency, Currency toCurrency, long date) {
        long day = DateUtils.atMidnight(date);
        Cursor c = db().query(EXCHANGE_RATES_TABLE, ExchangeRateColumns.NORMAL_PROJECTION, ExchangeRateColumns.NORMAL_PROJECTION_WHERE,
                new String[]{String.valueOf(fromCurrency.id), String.valueOf(toCurrency.id), String.valueOf(day)}, null, null, null);
        try {
            if (c.moveToFirst()) {
                return ExchangeRate.fromCursor(c);
            }
        } finally {
            c.close();
        }
        return null;
    }

    public List<ExchangeRate> findRates(Currency fromCurrency) {
        List<ExchangeRate> rates = new ArrayList<ExchangeRate>();
        Cursor c = db().query(EXCHANGE_RATES_TABLE, ExchangeRateColumns.NORMAL_PROJECTION, ExchangeRateColumns.from_currency_id + "=?",
                new String[]{String.valueOf(fromCurrency.id)}, null, null, ExchangeRateColumns.rate_date + " desc");
        try {
            while (c.moveToNext()) {
                rates.add(ExchangeRate.fromCursor(c));
            }
        } finally {
            c.close();
        }
        return rates;
    }

    public List<ExchangeRate> findRates(Currency fromCurrency, Currency toCurrency) {
        List<ExchangeRate> rates = new ArrayList<ExchangeRate>();
        Cursor c = db().query(EXCHANGE_RATES_TABLE, ExchangeRateColumns.NORMAL_PROJECTION,
                ExchangeRateColumns.from_currency_id + "=? and " + ExchangeRateColumns.to_currency_id + "=?",
                new String[]{String.valueOf(fromCurrency.id), String.valueOf(toCurrency.id)},
                null, null, ExchangeRateColumns.rate_date + " desc");
        try {
            while (c.moveToNext()) {
                rates.add(ExchangeRate.fromCursor(c));
            }
        } finally {
            c.close();
        }
        return rates;
    }

    public ExchangeRateProvider getLatestRates() {
        LatestExchangeRates m = new LatestExchangeRates();
        Cursor c = db().query(EXCHANGE_RATES_TABLE, ExchangeRateColumns.LATEST_RATE_PROJECTION, null, null, ExchangeRateColumns.LATEST_RATE_GROUP_BY, null, null);
        fillRatesCollection(m, c);
        return m;
    }

    public ExchangeRateProvider getHistoryRates() {
        HistoryExchangeRates m = new HistoryExchangeRates();
        Cursor c = db().query(EXCHANGE_RATES_TABLE, ExchangeRateColumns.NORMAL_PROJECTION, null, null, null, null, null);
        fillRatesCollection(m, c);
        return m;
    }

    private void fillRatesCollection(ExchangeRatesCollection m, Cursor c) {
        try {
            while (c.moveToNext()) {
                ExchangeRate r = ExchangeRate.fromCursor(c);
                m.addRate(r);
            }
        } finally {
            c.close();
        }
    }

    public void deleteRate(ExchangeRate rate) {
        deleteRate(rate.fromCurrencyId, rate.toCurrencyId, rate.date);
    }

    public void deleteRate(long fromCurrencyId, long toCurrencyId, long date) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            deleteRateInTransaction(fromCurrencyId, toCurrencyId, date, db);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void deleteRateInTransaction(long fromCurrencyId, long toCurrencyId, long date, SQLiteDatabase db) {
        long d = DateUtils.atMidnight(date);
        db.delete(EXCHANGE_RATES_TABLE, ExchangeRateColumns.DELETE_CLAUSE,
                new String[]{String.valueOf(fromCurrencyId), String.valueOf(toCurrencyId), String.valueOf(d)});
        db.delete(EXCHANGE_RATES_TABLE, ExchangeRateColumns.DELETE_CLAUSE,
                new String[]{String.valueOf(toCurrencyId), String.valueOf(fromCurrencyId), String.valueOf(d)});
    }

    public Total getAccountsTotalInHomeCurrency() {
        Currency homeCurrency = getHomeCurrency();
        return getAccountsTotal(homeCurrency);
    }

    /**
     * Calculates total in every currency for all accounts
     */
    public Total[] getAccountsTotal() {
        List<Account> accounts = getAllAccountsList();
        Map<Currency, Total> totalsMap = new HashMap<Currency, Total>();
        for (Account account : accounts) {
            if (account.shouldIncludeIntoTotals()) {
                Currency currency = account.currency;
                Total total = totalsMap.get(currency);
                if (total == null) {
                    total = new Total(currency);
                    totalsMap.put(currency, total);
                }
                total.balance += account.totalAmount;
            }
        }
        Collection<Total> values = totalsMap.values();
        return values.toArray(new Total[values.size()]);
    }

    /**
     * Calculates total in home currency for all accounts
     */
    public Total getAccountsTotal(Currency homeCurrency) {
        ExchangeRateProvider rates = getLatestRates();
        List<Account> accounts = getAllAccountsList();
        BigDecimal total = BigDecimal.ZERO;
        for (Account account : accounts) {
            if (account.shouldIncludeIntoTotals()) {
                if (account.currency.id == homeCurrency.id) {
                    total = total.add(BigDecimal.valueOf(account.totalAmount));
                } else {
                    ExchangeRate rate = rates.getRate(account.currency, homeCurrency);
                    if (rate == ExchangeRate.NA) {
                        return new Total(homeCurrency, TotalError.lastRateError(account.currency));
                    } else {
                        total = total.add(BigDecimal.valueOf(rate.rate * account.totalAmount));
                    }
                }
            }
        }
        Total result = new Total(homeCurrency);
        result.balance = total.longValue();
        return result;
    }

    public boolean singleCurrencyOnly() {
        long currencyId = getSingleCurrencyId();
        return currencyId > 0;
    }

    private long getSingleCurrencyId() {
        Cursor c = db().rawQuery("select distinct " + AccountColumns.CURRENCY_ID + " from " + ACCOUNT_TABLE +
                " where " + AccountColumns.IS_INCLUDE_INTO_TOTALS + "=1 and " + AccountColumns.IS_ACTIVE + "=1", null);
        try {
            if (c.getCount() == 1) {
                c.moveToFirst();
                return c.getLong(0);
            }
            return -1;
        } finally {
            c.close();
        }
    }

    public void setDefaultHomeCurrency() {
        Currency homeCurrency = getHomeCurrency();
        long singleCurrencyId = getSingleCurrencyId();
        if (homeCurrency == Currency.EMPTY && singleCurrencyId > 0) {
            Currency c = get(Currency.class, singleCurrencyId);
            c.isDefault = true;
            saveOrUpdate(c);
        }
    }

    public void purgeAccountAtDate(Account account, long date) {
        long nearestTransactionId = findNearestOlderTransactionId(account, date);
        if (nearestTransactionId > 0) {
            SQLiteDatabase db = db();
            db.beginTransaction();
            try {
                Transaction newTransaction = createTransactionFromNearest(account, nearestTransactionId);
                breakSplitTransactions(account, date);
                deleteOldTransactions(account, date);
                insertWithoutUpdatingBalance(newTransaction);
                db.execSQL(INSERT_RUNNING_BALANCE, new Object[]{account.id, newTransaction.id, newTransaction.dateTime, newTransaction.fromAmount});
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }
    }

    private Transaction createTransactionFromNearest(Account account, long nearestTransactionId) {
        Transaction nearestTransaction = get(Transaction.class, nearestTransactionId);
        long balance = getAccountBalanceForTransaction(account, nearestTransaction);
        Transaction newTransaction = new Transaction();
        newTransaction.fromAccountId = account.id;
        newTransaction.dateTime = DateUtils.atDayEnd(nearestTransaction.dateTime);
        newTransaction.fromAmount = balance;
        Payee payee = insertPayee(context.getString(R.string.purge_account_payee));
        newTransaction.payeeId = payee != null ? payee.id : 0;
        newTransaction.status = TransactionStatus.CL;
        return newTransaction;
    }

    private static final String BREAK_SPLIT_TRANSACTIONS_1 = UPDATE_ORPHAN_TRANSACTIONS_1 + " " +
            "AND " + TransactionColumns.datetime + "<=?";
    private static final String BREAK_SPLIT_TRANSACTIONS_2 = UPDATE_ORPHAN_TRANSACTIONS_2 + " " +
            "AND " + TransactionColumns.datetime + "<=?";

    private void breakSplitTransactions(Account account, long date) {
        SQLiteDatabase db = db();
        long dayEnd = DateUtils.atDayEnd(date);
        db.execSQL(BREAK_SPLIT_TRANSACTIONS_1, new Object[]{account.id, dayEnd});
        db.execSQL(BREAK_SPLIT_TRANSACTIONS_2, new Object[]{account.id, dayEnd});
        db.delete(TRANSACTION_ATTRIBUTE_TABLE, TransactionAttributeColumns.TRANSACTION_ID
                        + " in (SELECT _id from " + TRANSACTION_TABLE + " where " + TransactionColumns.datetime + "<=?)",
                new String[]{String.valueOf(dayEnd)}
        );
    }

    public void deleteOldTransactions(Account account, long date) {
        SQLiteDatabase db = db();
        long dayEnd = DateUtils.atDayEnd(date);
        db.delete("transactions", "from_account_id=? and datetime<=? and is_template=0",
                new String[]{String.valueOf(account.id), String.valueOf(dayEnd)});
        db.delete("running_balance", "account_id=? and datetime<=?",
                new String[]{String.valueOf(account.id), String.valueOf(dayEnd)});
    }

    public long getAccountBalanceForTransaction(Account a, Transaction t) {
        return DatabaseUtils.rawFetchLongValue(this, "select balance from running_balance where account_id=? and transaction_id=?",
                new String[]{String.valueOf(a.id), String.valueOf(t.id)});
    }

    public long findNearestOlderTransactionId(Account account, long date) {
        return DatabaseUtils.rawFetchId(this,
                "select _id from v_blotter where from_account_id=? and datetime<=? order by datetime desc limit 1",
                new String[]{String.valueOf(account.id), String.valueOf(DateUtils.atDayEnd(date))});
    }

    public long findLatestTransactionDate(long accountId) {
        return DatabaseUtils.rawFetchLongValue(this,
                "select datetime from running_balance where account_id=? order by datetime desc limit 1",
                new String[]{String.valueOf(accountId)});
    }

    private static final String ACCOUNT_LAST_TRANSACTION_DATE_UPDATE = "UPDATE " + ACCOUNT_TABLE
            + " SET " + AccountColumns.LAST_TRANSACTION_DATE + "=? WHERE " + AccountColumns.ID + "=?";

    private void updateAccountLastTransactionDate(long accountId) {
        if (accountId <= 0) {
            return;
        }
        long lastTransactionDate = findLatestTransactionDate(accountId);
        db().execSQL(ACCOUNT_LAST_TRANSACTION_DATE_UPDATE, new Object[]{lastTransactionDate, accountId});
    }

    public void updateAccountsLastTransactionDate() {
        List<Account> accounts = getAllAccountsList();
        for (Account account : accounts) {
            updateAccountLastTransactionDate(account.id);
        }
    }

//    public void restoreNoCategory() {
//        Category c = getCategoryNoParent(Category.NO_CATEGORY_ID);
//        if (c == null) {
//            db().execSQL("INSERT INTO category (_id, title, left, right) VALUES (0, 'No category', 1, 2)");
//        }
//        CategoryTree tree = getCategoriesTree(false);
//        tree.reIndex();
//        updateCategoryTree(tree);
//    }

    public long getLastRunningBalanceForAccount(Account account) {
        return DatabaseUtils.rawFetchLongValue(this, "select balance from running_balance where account_id=? order by datetime desc, transaction_id desc limit 1",
                new String[]{String.valueOf(account.id)});
    }

    public long writeDeleteLog(String tableName, String remoteKey) {
        if (remoteKey == null) {
            return 0;
        }
        if (remoteKey == "") {
            return 0;
        }
        ContentValues row = new ContentValues();
        row.put(deleteLogColumns.TABLE_NAME, tableName);
        row.put(deleteLogColumns.REMOTE_KEY, remoteKey);
        row.put(deleteLogColumns.DELETED_ON, System.currentTimeMillis());
        return db().insert(DELETE_LOG_TABLE, null, row);
    }

}