/**
 * 
 */
package de.pointedears.converter.db;

import java.util.HashMap;
import java.util.Map.Entry;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import de.pointedears.converter.app.CurrenciesActivity;

/**
 * @author pelinux
 * 
 */
public class CurrenciesDatabase extends SQLiteOpenHelper
{
  private static final String DATABASE_NAME = "currency.db"; //$NON-NLS-1$
  private static final int DATABASE_VERSION = 3;

  private static final String TABLE = "currency"; //$NON-NLS-1$
  private static final String COLUMN_CURRENCY = "currency1"; //$NON-NLS-1$
  private static final String COLUMN_FACTOR = "factor"; //$NON-NLS-1$

  private static HashMap<String, Double> conversionRates =
    new HashMap<String, Double>();
  static
  {
    /* Default conversion rates from Euro (EUR) to other currencies */
    CurrenciesDatabase.conversionRates
      .put(CurrenciesActivity.VALUE_CHF, 1.3013);
    CurrenciesDatabase.conversionRates
      .put(CurrenciesActivity.VALUE_USD, 1.3521);
  }

  /**
   * @param context
   *          The Activity in which this wrapper is used
   */
  public CurrenciesDatabase(CurrenciesActivity context)
  {
    super(context, CurrenciesDatabase.DATABASE_NAME, null,
          CurrenciesDatabase.DATABASE_VERSION);
    this.readConversionsFromDatabase();
  }

  /*
   * (non-Javadoc)
   * 
   * @see
   * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
   * .SQLiteDatabase)
   */
  @SuppressWarnings("nls")
  @Override
  public void onCreate(SQLiteDatabase db)
  {
    db.execSQL("CREATE TABLE IF NOT EXISTS " + CurrenciesDatabase.TABLE
      + " (" + CurrenciesDatabase.COLUMN_CURRENCY + " TEXT, "
      + CurrenciesDatabase.COLUMN_FACTOR
      + " NUMERIC"
      + ", CONSTRAINT unique_currency_pair UNIQUE ("
      + CurrenciesDatabase.COLUMN_CURRENCY + "))");

    HashMap<String, Double> currencyConversions =
      this.getConversionRates();
    for (Entry<String, Double> factorEntry : currencyConversions.entrySet())
    {
      ContentValues values = new ContentValues();
      values.put(CurrenciesDatabase.COLUMN_CURRENCY, factorEntry.getKey());
      values.put(CurrenciesDatabase.COLUMN_FACTOR, factorEntry.getValue());
      db.insert(CurrenciesDatabase.TABLE, CurrenciesDatabase.COLUMN_FACTOR,
        values);
    }
  }

  /*
   * (non-Javadoc)
   * 
   * @see
   * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
   * .SQLiteDatabase, int, int)
   */
  @SuppressWarnings("nls")
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
    /* NOTE: We should migrate an existing database instead */
    db.execSQL("DROP TABLE IF EXISTS " + CurrenciesDatabase.TABLE);
    this.onCreate(db);
  }

  /**
   * @return
   */
  public HashMap<String, Double> getConversionRates()
  {
    return CurrenciesDatabase.conversionRates;
  }

  /**
   * Reads currency conversions and updates the static currencyConversions field
   * of this class
   */
  public void readConversionsFromDatabase()
  {
    try
    {
      /* Get database connection, but upgrade database first if necessary! */
      SQLiteDatabase dbConn = this.getWritableDatabase();

      Cursor cursor =
          dbConn.query(true, CurrenciesDatabase.TABLE, null, null, null, null,
            null, CurrenciesDatabase.COLUMN_CURRENCY, null);

      if (cursor != null)
      {
        try
        {
          int currency1Id =
              cursor
                .getColumnIndexOrThrow(CurrenciesDatabase.COLUMN_CURRENCY);
          int factorId =
              cursor.getColumnIndexOrThrow(CurrenciesDatabase.COLUMN_FACTOR);

          /* NOTE: Don't change the default values if the table is empty */
          if (cursor.moveToFirst())
          {
            HashMap<String, Double> newCurrencyConversions =
              new HashMap<String, Double>();

            do
            {
              String currencyStr = cursor.getString(currency1Id);
              Double factor = cursor.getDouble(factorId);
              newCurrencyConversions.put(currencyStr, factor);
            }
            while (cursor.moveToNext());

            CurrenciesDatabase.conversionRates = newCurrencyConversions;
          }
        }
        catch (IllegalArgumentException e)
        {
          /* Could not retrieve column index */
          e.printStackTrace();
        }
      }

      dbConn.close();
    }
    catch (SQLiteException e1)
    {
      /* Could not open database */
      e1.printStackTrace();
    }
  }

  /**
   * Tests the database access
   */
  public void testAccess()
  {
    try
    {
      SQLiteDatabase dbConn = this.getReadableDatabase();

      Cursor myCursor =
          dbConn.query(true, CurrenciesDatabase.TABLE, null, null, null, null,
            null, CurrenciesDatabase.COLUMN_CURRENCY, null);

      @SuppressWarnings({ "unused", "nls" })
      String queryResult = "";
      if (myCursor != null)
      {
        try
        {
          int currency1Id =
              myCursor
                .getColumnIndexOrThrow(CurrenciesDatabase.COLUMN_CURRENCY);
          int factorId =
              myCursor.getColumnIndexOrThrow(CurrenciesDatabase.COLUMN_FACTOR);

          if (myCursor.moveToFirst())
          {
            do
            {
              String currencyStr = myCursor.getString(currency1Id);
              Double factor = myCursor.getDouble(factorId);

              /* DEBUG */
              queryResult +=
                  "EUR --> " + currencyStr + ": " + factor + "\n"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
            }
            while (myCursor.moveToNext());
          }
        }
        catch (IllegalArgumentException e)
        {
          /* Could not retrieve column index */
          e.printStackTrace();
        }
      }

      dbConn.close();
    }
    catch (SQLiteException e1)
    {
      /* Could not open database */
      e1.printStackTrace();
    }
  }
}