/*
 * Copyright (c) 2012 - 2015 Ngewi Fet <ngewif@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.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.support.annotation.NonNull;
import android.text.TextUtils;
import android.util.Log;

import org.gnucash.android.db.DatabaseSchema;
import org.gnucash.android.db.DatabaseSchema.AccountEntry;
import org.gnucash.android.db.DatabaseSchema.CommonColumns;
import org.gnucash.android.db.DatabaseSchema.SplitEntry;
import org.gnucash.android.db.DatabaseSchema.TransactionEntry;
import org.gnucash.android.model.AccountType;
import org.gnucash.android.model.BaseModel;
import org.gnucash.android.util.TimestampHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Adapter to be used for creating and opening the database for read/write operations.
 * The adapter abstracts several methods for database access and should be subclassed
 * by any other adapters to database-backed data models.
 * @author Ngewi Fet <ngewif@gmail.com>
 *
 */
public abstract class DatabaseAdapter<Model extends BaseModel> {
	/**
	 * Tag for logging
	 */
	protected String LOG_TAG = "DatabaseAdapter";

	/**
	 * SQLite database
	 */
    protected final SQLiteDatabase mDb;

    protected final String mTableName;

    protected final String[] mColumns;

    protected volatile SQLiteStatement mReplaceStatement;

    protected volatile SQLiteStatement mUpdateStatement;

    protected volatile SQLiteStatement mInsertStatement;

    public enum UpdateMethod {
        insert, update, replace
    };

    /**
     * Opens the database adapter with an existing database
     * @param db SQLiteDatabase object
     */
    public DatabaseAdapter(SQLiteDatabase db, @NonNull String tableName, @NonNull String[] columns) {
        this.mTableName = tableName;
        this.mDb = db;
        this.mColumns = columns;
        if (!db.isOpen() || db.isReadOnly())
            throw new IllegalArgumentException("Database not open or is read-only. Require writeable database");

        if (mDb.getVersion() >= 9) {
            createTempView();
        }
        LOG_TAG = getClass().getSimpleName();
    }

    private void createTempView() {
        //the multiplication by 1.0 is to cause sqlite to handle the value as REAL and not to round off

        // Create some temporary views. Temporary views only exists in one DB session, and will not
        // be saved in the DB
        //
        // TODO: Useful views should be add to the DB
        //
        // create a temporary view, combining accounts, transactions and splits, as this is often used
        // in the queries

        //todo: would it be useful to add the split reconciled_state and reconciled_date to this view?
        mDb.execSQL("CREATE TEMP VIEW IF NOT EXISTS trans_split_acct AS SELECT "
                        + TransactionEntry.TABLE_NAME + "." + CommonColumns.COLUMN_MODIFIED_AT + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + CommonColumns.COLUMN_MODIFIED_AT + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_UID + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_DESCRIPTION + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_DESCRIPTION + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_NOTES + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_NOTES + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_CURRENCY + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_CURRENCY + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TIMESTAMP + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_TIMESTAMP + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_EXPORTED + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_EXPORTED + " , "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_TEMPLATE + " AS "
                        + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_TEMPLATE + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_UID + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_UID + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TYPE + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_TYPE + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_VALUE_NUM + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_VALUE_NUM + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_VALUE_DENOM + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_VALUE_DENOM + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_QUANTITY_NUM + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_QUANTITY_NUM + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_QUANTITY_DENOM + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_QUANTITY_DENOM + " , "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_MEMO + " AS "
                        + SplitEntry.TABLE_NAME + "_" + SplitEntry.COLUMN_MEMO + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_UID + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_UID + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_NAME + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_NAME + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_CURRENCY + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_CURRENCY + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_PARENT_ACCOUNT_UID + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_PARENT_ACCOUNT_UID + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_PLACEHOLDER + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_PLACEHOLDER + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_COLOR_CODE + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_COLOR_CODE + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_FAVORITE + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_FAVORITE + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_FULL_NAME + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_FULL_NAME + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_TYPE + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_TYPE + " , "
                        + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_DEFAULT_TRANSFER_ACCOUNT_UID + " AS "
                        + AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_DEFAULT_TRANSFER_ACCOUNT_UID
                        + " FROM " + TransactionEntry.TABLE_NAME + " , " + SplitEntry.TABLE_NAME + " ON "
                        + TransactionEntry.TABLE_NAME + "." + TransactionEntry.COLUMN_UID + "=" + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_TRANSACTION_UID
                        + " , " + AccountEntry.TABLE_NAME + " ON "
                        + SplitEntry.TABLE_NAME + "." + SplitEntry.COLUMN_ACCOUNT_UID + "=" + AccountEntry.TABLE_NAME + "." + AccountEntry.COLUMN_UID
        );

        // SELECT transactions_uid AS trans_acct_t_uid ,
        //      SUBSTR (
        //          MIN (
        //              ( CASE WHEN IFNULL ( splits_memo , '' ) == '' THEN 'a' ELSE 'b' END ) || accounts_uid
        //          ) ,
        //          2
        //      ) AS trans_acct_a_uid ,
        //   TOTAL ( CASE WHEN splits_type = 'DEBIT' THEN splits_value_num
        //                ELSE - splits_value_num END ) * 1.0 / splits_value_denom AS trans_acct_balance ,
        //   COUNT ( DISTINCT accounts_currency_code ) AS trans_currency_count ,
        //   COUNT (*) AS trans_split_count
        //   FROM trans_split_acct GROUP BY transactions_uid
        //
        // This temporary view would pick one Account_UID for each
        // Transaction, which can be used to order all transactions. If possible, account_uid of a split whose
        // memo is null is select.
        //
        // Transaction balance is also picked out by this view
        //
        // a split without split memo is chosen if possible, in the following manner:
        //   if the splits memo is null or empty string, attach an 'a' in front of the split account uid,
        //   if not, attach a 'b' to the split account uid
        //   pick the minimal value of the modified account uid (one of the ones begins with 'a', if exists)
        //   use substr to get account uid

        mDb.execSQL("CREATE TEMP VIEW IF NOT EXISTS trans_extra_info AS SELECT " + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_UID +
                " AS trans_acct_t_uid , SUBSTR ( MIN ( ( CASE WHEN IFNULL ( " + SplitEntry.TABLE_NAME + "_" +
                SplitEntry.COLUMN_MEMO + " , '' ) == '' THEN 'a' ELSE 'b' END ) || " +
                AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_UID +
                " ) , 2 ) AS trans_acct_a_uid , TOTAL ( CASE WHEN " + SplitEntry.TABLE_NAME + "_" +
                SplitEntry.COLUMN_TYPE + " = 'DEBIT' THEN "+ SplitEntry.TABLE_NAME + "_" +
                SplitEntry.COLUMN_VALUE_NUM + " ELSE - " + SplitEntry.TABLE_NAME + "_" +
                SplitEntry.COLUMN_VALUE_NUM + " END ) * 1.0 / " + SplitEntry.TABLE_NAME + "_" +
                SplitEntry.COLUMN_VALUE_DENOM + " AS trans_acct_balance , COUNT ( DISTINCT " +
                AccountEntry.TABLE_NAME + "_" + AccountEntry.COLUMN_CURRENCY +
                " ) AS trans_currency_count , COUNT (*) AS trans_split_count FROM trans_split_acct " +
                " GROUP BY " + TransactionEntry.TABLE_NAME + "_" + TransactionEntry.COLUMN_UID
        );
    }

    /**
     * Checks if the database is open
     * @return <code>true</code> if the database is open, <code>false</code> otherwise
     */
    public boolean isOpen(){
        return mDb.isOpen();
    }

    /**
     * Adds a record to the database with the data contained in the model.
     * <p>This method uses the SQL REPLACE instructions to replace any record with a matching GUID.
     * So beware of any foreign keys with cascade dependencies which might need to be re-added</p>
     * @param model Model to be saved to the database
     */
    public void addRecord(@NonNull final Model model){
        addRecord(model, UpdateMethod.replace);
    }

    /**
     * Add a model record to the database.
     * <p>If unsure about which {@code updateMethod} to use, use {@link UpdateMethod#replace}</p>
     * @param model Subclass of {@link BaseModel} to be added
     * @param updateMethod Method to use for adding the record
     */
    public void addRecord(@NonNull final Model model, UpdateMethod updateMethod){
        Log.d(LOG_TAG, String.format("Adding %s record to database: ", model.getClass().getSimpleName()));
        switch(updateMethod){
            case insert:
                synchronized(getInsertStatement()) {
                    setBindings(getInsertStatement(), model).execute();
                }
                break;
            case update:
                synchronized(getUpdateStatement()) {
                    setBindings(getUpdateStatement(), model).execute();
                }
                break;
            default:
                synchronized(getReplaceStatement()) {
                    setBindings(getReplaceStatement(), model).execute();
                }
                break;
        }
    }

    /**
     * Persist the model object to the database as records using the {@code updateMethod}
     * @param modelList List of records
     * @param updateMethod Method to use when persisting them
     * @return Number of rows affected in the database
     */
    private long doAddModels(@NonNull final List<Model> modelList, UpdateMethod updateMethod) {
        long nRow = 0;
        switch (updateMethod) {
            case update:
                synchronized(getUpdateStatement()) {
                    for (Model model : modelList) {
                        setBindings(getUpdateStatement(), model).execute();
                        nRow++;
                    }
                }
                break;
            case insert:
                synchronized(getInsertStatement()) {
                    for (Model model : modelList) {
                        setBindings(getInsertStatement(), model).execute();
                        nRow++;
                    }
                }
                break;
            default:
                synchronized(getReplaceStatement()) {
                    for (Model model : modelList) {
                        setBindings(getReplaceStatement(), model).execute();
                        nRow++;
                    }
                }
                break;
        }
        return nRow;
    }

    /**
     * Add multiple records to the database at once
     * <p>Either all or none of the records will be inserted/updated into the database.</p>
     * @param modelList List of model records
     * @return Number of rows inserted
     */
    public long bulkAddRecords(@NonNull List<Model> modelList){
        return bulkAddRecords(modelList, UpdateMethod.replace);
    }

    public long bulkAddRecords(@NonNull List<Model> modelList, UpdateMethod updateMethod) {
        if (modelList.isEmpty()) {
            Log.d(LOG_TAG, "Empty model list. Cannot bulk add records, returning 0");
            return 0;
        }

        Log.i(LOG_TAG, String.format("Bulk adding %d %s records to the database", modelList.size(),
                modelList.size() == 0 ? "null": modelList.get(0).getClass().getSimpleName()));
        long nRow = 0;
        try {
            mDb.beginTransaction();
            nRow = doAddModels(modelList, updateMethod);
            mDb.setTransactionSuccessful();
        }
        finally {
            mDb.endTransaction();
        }

        return nRow;
    }

    /**
     * Builds an instance of the model from the database record entry
     * <p>When implementing this method, remember to call {@link #populateBaseModelAttributes(Cursor, BaseModel)}</p>
     * @param cursor Cursor pointing to the record
     * @return New instance of the model from database record
     */
    public abstract Model buildModelInstance(@NonNull final Cursor cursor);

    /**
     * Generates an {@link SQLiteStatement} with values from the {@code model}.
     * This statement can be executed to replace a record in the database.
     * <p>If the {@link #mReplaceStatement} is null, subclasses should create a new statement and return.<br/>
     * If it is not null, the previous bindings will be cleared and replaced with those from the model</p>
     * @return SQLiteStatement for replacing a record in the database
     */
    protected final @NonNull SQLiteStatement getReplaceStatement() {
        SQLiteStatement stmt = mReplaceStatement;
        if (stmt == null) {
            synchronized (this) {
                stmt = mReplaceStatement;
                if (stmt == null) {
                    mReplaceStatement = stmt
                            = mDb.compileStatement("REPLACE INTO " + mTableName + " ( "
                            + TextUtils.join(" , ", mColumns) + " , "
                            + CommonColumns.COLUMN_UID
                            + " ) VALUES ( "
                            + (new String(new char[mColumns.length]).replace("\0", "? , "))
                            + "?)");
                }
            }
        }
        return stmt;
    }

    protected final @NonNull SQLiteStatement getUpdateStatement() {
        SQLiteStatement stmt = mUpdateStatement;
        if (stmt == null) {
            synchronized (this) {
                stmt = mUpdateStatement;
                if (stmt == null) {
                    mUpdateStatement = stmt
                            = mDb.compileStatement("UPDATE " + mTableName + " SET "
                            + TextUtils.join(" = ? , ", mColumns) + " = ? WHERE "
                            + CommonColumns.COLUMN_UID
                            + " = ?");
                }
            }
        }
        return stmt;
    }

    protected final @NonNull SQLiteStatement getInsertStatement() {
        SQLiteStatement stmt = mInsertStatement;
        if (stmt == null) {
            synchronized (this) {
                stmt = mInsertStatement;
                if (stmt == null) {
                    mInsertStatement = stmt
                            = mDb.compileStatement("INSERT INTO " + mTableName + " ( "
                            + TextUtils.join(" , ", mColumns) + " , "
                            + CommonColumns.COLUMN_UID
                            + " ) VALUES ( "
                            + (new String(new char[mColumns.length]).replace("\0", "? , "))
                            + "?)");
                }
            }
        }
        return stmt;
    }

    /**
     * Binds the values from the model the the SQL statement
     * @param stmt SQL statement with placeholders
     * @param model Model from which to read bind attributes
     * @return SQL statement ready for execution
     */
    protected abstract @NonNull SQLiteStatement setBindings(@NonNull SQLiteStatement stmt, @NonNull final Model model);

    /**
     * Returns a model instance populated with data from the record with GUID {@code uid}
     * <p>Sub-classes which require special handling should override this method</p>
     * @param uid GUID of the record
     * @return BaseModel instance of the record
     * @throws IllegalArgumentException if the record UID does not exist in thd database
     */
    public Model getRecord(@NonNull String uid){
        Log.v(LOG_TAG, "Fetching record with GUID " + uid);

        Cursor cursor = fetchRecord(uid);
        try {
            if (cursor.moveToFirst()) {
                return buildModelInstance(cursor);
            }
            else {
                throw new IllegalArgumentException(LOG_TAG + ": Record with " + uid + " does not exist");
            }
        } finally {
            cursor.close();
        }
    }

    /**
     * Overload of {@link #getRecord(String)}
     * Simply converts the record ID to a GUID and calls {@link #getRecord(String)}
     * @param id Database record ID
     * @return Subclass of {@link BaseModel} containing record info
     */
    public Model getRecord(long id){
        return getRecord(getUID(id));
    }

    /**
     * Returns all the records in the database
     * @return List of records in the database
     */
    public List<Model> getAllRecords(){
        List<Model> modelRecords = new ArrayList<>();
        Cursor c = fetchAllRecords();
        try {
            while (c.moveToNext()) {
                modelRecords.add(buildModelInstance(c));
            }
        } finally {
            c.close();
        }
        return modelRecords;
    }

    /**
     * Extracts the attributes of the base model and adds them to the ContentValues object provided
     * @param contentValues Content values to which to add attributes
     * @param model {@link org.gnucash.android.model.BaseModel} from which to extract values
     * @return {@link android.content.ContentValues} with the data to be inserted into the db
     */
    protected ContentValues extractBaseModelAttributes(@NonNull ContentValues contentValues, @NonNull Model model){
        contentValues.put(CommonColumns.COLUMN_UID, model.getUID());
        contentValues.put(CommonColumns.COLUMN_CREATED_AT, TimestampHelper.getUtcStringFromTimestamp(model.getCreatedTimestamp()));
        //there is a trigger in the database for updated the modified_at column
        /* Due to the use of SQL REPLACE syntax, we insert the created_at values each time
        * (maintain the original creation time and not the time of creation of the replacement)
        * The updated_at column has a trigger in the database which will update the column
         */
        return contentValues;
    }

    /**
     * Initializes the model with values from the database record common to all models (i.e. in the BaseModel)
     * @param cursor Cursor pointing to database record
     * @param model Model instance to be initialized
     */
    protected void populateBaseModelAttributes(Cursor cursor, BaseModel model){
        String uid = cursor.getString(cursor.getColumnIndexOrThrow(CommonColumns.COLUMN_UID));
        String created = cursor.getString(cursor.getColumnIndexOrThrow(CommonColumns.COLUMN_CREATED_AT));
        String modified= cursor.getString(cursor.getColumnIndexOrThrow(CommonColumns.COLUMN_MODIFIED_AT));

        model.setUID(uid);
        model.setCreatedTimestamp(TimestampHelper.getTimestampFromUtcString(created));
        model.setModifiedTimestamp(TimestampHelper.getTimestampFromUtcString(modified));
    }

	/**
	 * Retrieves record with id <code>rowId</code> from database table
	 * @param rowId ID of record to be retrieved
	 * @return {@link Cursor} to record retrieved
	 */
	public Cursor fetchRecord(long rowId){
		return mDb.query(mTableName, null, DatabaseSchema.CommonColumns._ID + "=" + rowId,
				null, null, null, null);
	}

    /**
     * Retrieves record with GUID {@code uid} from database table
     * @param uid GUID of record to be retrieved
     * @return {@link Cursor} to record retrieved
     */
    public Cursor fetchRecord(@NonNull String uid){
        return mDb.query(mTableName, null, CommonColumns.COLUMN_UID + "=?" ,
                new String[]{uid}, null, null, null);
    }

	/**
	 * Retrieves all records from database table
	 * @return {@link Cursor} to all records in table <code>tableName</code>
	 */
	public Cursor fetchAllRecords(){
		return fetchAllRecords(null, null, null);
	}

    /**
     * Fetch all records from database matching conditions
     * @param where SQL where clause
     * @param whereArgs String arguments for where clause
     * @param orderBy SQL orderby clause
     * @return Cursor to records matching conditions
     */
    public Cursor fetchAllRecords(String where, String[] whereArgs, String orderBy){
        return mDb.query(mTableName, null, where, whereArgs, null, null, orderBy);
    }

	/**
	 * Deletes record with ID <code>rowID</code> from database table.
	 * @param rowId ID of record to be deleted
	 * @return <code>true</code> if deletion was successful, <code>false</code> otherwise
	 */
	public boolean deleteRecord(long rowId){
        Log.d(LOG_TAG, "Deleting record with id " + rowId + " from " + mTableName);
		return mDb.delete(mTableName, DatabaseSchema.CommonColumns._ID + "=" + rowId, null) > 0;
	}

    /**
     * Deletes all records in the database
     * @return Number of deleted records
     */
    public int deleteAllRecords(){
        return mDb.delete(mTableName, null, null);
    }

    /**
     * Returns the string unique ID (GUID) of a record in the database
     * @param uid GUID of the record
     * @return Long record ID
     * @throws IllegalArgumentException if the GUID does not exist in the database
     */
    public long getID(@NonNull String uid){
        Cursor cursor = mDb.query(mTableName,
                new String[] {DatabaseSchema.CommonColumns._ID},
                DatabaseSchema.CommonColumns.COLUMN_UID + " = ?",
                new String[]{uid},
                null, null, null);
        long result = -1;
        try{
            if (cursor.moveToFirst()) {
                result = cursor.getLong(cursor.getColumnIndexOrThrow(DatabaseSchema.CommonColumns._ID));
            } else {
                throw new IllegalArgumentException(mTableName + " with GUID " + uid + " does not exist in the db");
            }
        } finally {
            cursor.close();
        }
        return result;
    }

    /**
     * Returns the string unique ID (GUID) of a record in the database
     * @param id long database record ID
     * @return GUID of the record
     * @throws IllegalArgumentException if the record ID does not exist in the database
     */
    public String getUID(long id){
        Cursor cursor = mDb.query(mTableName,
                new String[]{DatabaseSchema.CommonColumns.COLUMN_UID},
                DatabaseSchema.CommonColumns._ID + " = " + id,
                null, null, null, null);

        String uid = null;
        try {
            if (cursor.moveToFirst()) {
                uid = cursor.getString(cursor.getColumnIndexOrThrow(DatabaseSchema.CommonColumns.COLUMN_UID));
            } else {
                throw new IllegalArgumentException(mTableName + " Record ID " + id + " does not exist in the db");
            }
        } finally {
            cursor.close();
        }
        return uid;
    }

    /**
     * Returns the currency code (according to the ISO 4217 standard) of the account
     * with unique Identifier <code>accountUID</code>
     * @param accountUID Unique Identifier of the account
     * @return Currency code of the account. "" if accountUID
     *      does not exist in DB
     */
    public String getAccountCurrencyCode(@NonNull String accountUID) {
        Cursor cursor = mDb.query(DatabaseSchema.AccountEntry.TABLE_NAME,
                new String[] {DatabaseSchema.AccountEntry.COLUMN_CURRENCY},
                DatabaseSchema.AccountEntry.COLUMN_UID + "= ?",
                new String[]{accountUID}, null, null, null);
        try {
            if (cursor.moveToFirst()) {
                return cursor.getString(0);
            } else {
                throw new IllegalArgumentException("Account " + accountUID + " does not exist");
            }
        } finally {
            cursor.close();
        }
    }


    /**
     * Returns the commodity GUID for the given ISO 4217 currency code
     * @param currencyCode ISO 4217 currency code
     * @return GUID of commodity
     */
    public String getCommodityUID(String currencyCode){
        String where = DatabaseSchema.CommodityEntry.COLUMN_MNEMONIC + "= ?";
        String[] whereArgs = new String[]{currencyCode};

        Cursor cursor = mDb.query(DatabaseSchema.CommodityEntry.TABLE_NAME,
                new String[]{DatabaseSchema.CommodityEntry.COLUMN_UID},
                where, whereArgs, null, null, null);
        try {
            if (cursor.moveToNext()) {
                return cursor.getString(cursor.getColumnIndexOrThrow(DatabaseSchema.CommodityEntry.COLUMN_UID));
            } else {
                throw new IllegalArgumentException("Currency code not found in commodities");
            }
        } finally {
            cursor.close();
        }
    }

    /**
     * Returns the {@link org.gnucash.android.model.AccountType} of the account with unique ID <code>uid</code>
     * @param accountUID Unique ID of the account
     * @return {@link org.gnucash.android.model.AccountType} of the account.
     * @throws java.lang.IllegalArgumentException if accountUID does not exist in DB,
     */
    public AccountType getAccountType(@NonNull String accountUID){
        String type = "";
        Cursor c = mDb.query(DatabaseSchema.AccountEntry.TABLE_NAME,
                new String[]{DatabaseSchema.AccountEntry.COLUMN_TYPE},
                DatabaseSchema.AccountEntry.COLUMN_UID + "=?",
                new String[]{accountUID}, null, null, null);
        try {
            if (c.moveToFirst()) {
                type = c.getString(c.getColumnIndexOrThrow(DatabaseSchema.AccountEntry.COLUMN_TYPE));
            } else {
                throw new IllegalArgumentException("account " + accountUID + " does not exist in DB");
            }
        } finally {
            c.close();
        }
        return AccountType.valueOf(type);
    }

    /**
     * Updates a record in the table
     * @param recordId Database ID of the record to be updated
     * @param columnKey Name of column to be updated
     * @param newValue  New value to be assigned to the columnKey
     * @return Number of records affected
     */
    protected int updateRecord(String tableName, long recordId, String columnKey, String newValue) {
        ContentValues contentValues = new ContentValues();
        if (newValue == null) {
            contentValues.putNull(columnKey);
        } else {
            contentValues.put(columnKey, newValue);
        }
        return mDb.update(tableName, contentValues,
                DatabaseSchema.CommonColumns._ID + "=" + recordId, null);
    }

    /**
     * Updates a record in the table
     * @param uid GUID of the record
     * @param columnKey Name of column to be updated
     * @param newValue  New value to be assigned to the columnKey
     * @return Number of records affected
     */
    public int updateRecord(@NonNull String uid, @NonNull String columnKey, String newValue) {
        return updateRecords(CommonColumns.COLUMN_UID + "= ?", new String[]{uid}, columnKey, newValue);
    }

    /**
     * Overloaded method. Updates the record with GUID {@code uid} with the content values
     * @param uid GUID of the record
     * @param contentValues Content values to update
     * @return Number of records updated
     */
    public int updateRecord(@NonNull String uid, @NonNull ContentValues contentValues){
        return mDb.update(mTableName, contentValues, CommonColumns.COLUMN_UID + "=?", new String[]{uid});
    }

    /**
     * Updates all records which match the {@code where} clause with the {@code newValue} for the column
     * @param where SQL where clause
     * @param whereArgs String arguments for where clause
     * @param columnKey Name of column to be updated
     * @param newValue New value to be assigned to the columnKey
     * @return Number of records affected
     */
    public int updateRecords(String where, String[] whereArgs, @NonNull String columnKey, String newValue){
        ContentValues contentValues = new ContentValues();
        if (newValue == null) {
            contentValues.putNull(columnKey);
        } else {
            contentValues.put(columnKey, newValue);
        }
        return mDb.update(mTableName, contentValues, where, whereArgs);
    }

    /**
     * Deletes a record from the database given its unique identifier.
     * <p>Overload of the method {@link #deleteRecord(long)}</p>
     * @param uid GUID of the record
     * @return <code>true</code> if deletion was successful, <code>false</code> otherwise
     * @see #deleteRecord(long)
     */
    public boolean deleteRecord(@NonNull String uid){
        return deleteRecord(getID(uid));
    }

    /**
     * Returns an attribute from a specific column in the database for a specific record.
     * <p>The attribute is returned as a string which can then be converted to another type if
     * the caller was expecting something other type </p>
     * @param recordUID GUID of the record
     * @param columnName Name of the column to be retrieved
     * @return String value of the column entry
     * @throws IllegalArgumentException if either the {@code recordUID} or {@code columnName} do not exist in the database
     */
    public String getAttribute(@NonNull String recordUID, @NonNull String columnName){
        return getAttribute(mTableName, recordUID, columnName);
    }

    /**
     * Returns an attribute from a specific column in the database for a specific record and specific table.
     * <p>The attribute is returned as a string which can then be converted to another type if
     * the caller was expecting something other type </p>
     * <p>This method is an override of {@link #getAttribute(String, String)} which allows to select a value from a
     * different table than the one of current adapter instance
     * </p>
     * @param tableName Database table name. See {@link DatabaseSchema}
     * @param recordUID GUID of the record
     * @param columnName Name of the column to be retrieved
     * @return String value of the column entry
     * @throws IllegalArgumentException if either the {@code recordUID} or {@code columnName} do not exist in the database
     */
    protected String getAttribute(@NonNull String tableName, @NonNull String recordUID, @NonNull String columnName){
        Cursor cursor = mDb.query(tableName,
                new String[]{columnName},
                AccountEntry.COLUMN_UID + " = ?",
                new String[]{recordUID}, null, null, null);

        try {
            if (cursor.moveToFirst())
                return cursor.getString(cursor.getColumnIndexOrThrow(columnName));
            else {
                throw new IllegalArgumentException(String.format("Record with GUID %s does not exist in the db", recordUID));
            }
        } finally {
            cursor.close();
        }
    }

    /**
     * Returns the number of records in the database table backed by this adapter
     * @return Total number of records in the database
     */
    public long getRecordsCount(){
        String sql = "SELECT COUNT(*) FROM " + mTableName;
        SQLiteStatement statement = mDb.compileStatement(sql);
        return statement.simpleQueryForLong();
    }

    /**
     * Expose mDb.beginTransaction()
     */
    public void beginTransaction() {
        mDb.beginTransaction();
    }

    /**
     * Expose mDb.setTransactionSuccessful()
     */
    public void setTransactionSuccessful() {
        mDb.setTransactionSuccessful();
    }

    /// Foreign key constraits should be enabled in general.
    /// But if it affects speed (check constraints takes time)
    /// and the constrained can be assured by the program,
    /// or if some SQL exec will cause deletion of records
    /// (like use replace in accounts update will delete all transactions)
    /// that need not be deleted, then it can be disabled temporarily
    public void enableForeignKey(boolean enable) {
        if (enable){
            mDb.execSQL("PRAGMA foreign_keys=ON;");
        } else {
            mDb.execSQL("PRAGMA foreign_keys=OFF;");
        }
    }

    /**
     * Expose mDb.endTransaction()
     */
    public void endTransaction() {
        mDb.endTransaction();
    }
}
