/**
 * 
 */
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 = 2;

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

  private static HashMap<String, HashMap<String, Double>> conversionRates =
    new HashMap<String, HashMap<String, Double>>();
  static
  {
    HashMap<String, Double> conversionFactors = new HashMap<String, Double>();
    conversionFactors.put(CurrenciesActivity.VALUE_EUR, 0.767842293);
    conversionFactors.put(CurrenciesActivity.VALUE_USD, 1.03413);
    CurrenciesDatabase.conversionRates.put(CurrenciesActivity.VALUE_CHF,
      conversionFactors);

    conversionFactors = new HashMap<String, Double>();
    conversionFactors.put(CurrenciesActivity.VALUE_CHF, 1.30235077);
    conversionFactors.put(CurrenciesActivity.VALUE_USD, 1.3468);
    CurrenciesDatabase.conversionRates.put(CurrenciesActivity.VALUE_EUR,
      conversionFactors);

    conversionFactors = new HashMap<String, Double>();
    conversionFactors.put(CurrenciesActivity.VALUE_CHF, 0.966996412);
    conversionFactors.put(CurrenciesActivity.VALUE_EUR, 0.742500743);
    CurrenciesDatabase.conversionRates.put(CurrenciesActivity.VALUE_USD,
      conversionFactors);
  }
  private final CurrenciesActivity context;

  /**
   * @param context
   *          The Activity in which this wrapper is used
   */
  public CurrenciesDatabase(CurrenciesActivity context)
  {
    super(context, CurrenciesDatabase.DATABASE_NAME, null,
          CurrenciesDatabase.DATABASE_VERSION);
    this.context = context;
    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_CURRENCY1 + " TEXT, "
      + CurrenciesDatabase.COLUMN_CURRENCY2 + " TEXT, "
      + CurrenciesDatabase.COLUMN_FACTOR
      + " NUMERIC"
      + ", CONSTRAINT unique_currency_pair UNIQUE ("
      + CurrenciesDatabase.COLUMN_CURRENCY1 + ", "
      + CurrenciesDatabase.COLUMN_CURRENCY2 + "))");

    HashMap<String, HashMap<String, Double>> currencyConversions =
      this.getConversionRates();
    for (String key : currencyConversions.keySet())
    {
      for (Entry<String, Double> factorEntry : currencyConversions.get(key)
        .entrySet())
      {
        ContentValues values = new ContentValues();
        values.put(CurrenciesDatabase.COLUMN_CURRENCY1, key);
        values.put(CurrenciesDatabase.COLUMN_CURRENCY2, 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, 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();

      @SuppressWarnings("nls")
      Cursor cursor =
          dbConn.query(true, CurrenciesDatabase.TABLE, null, null, null, null,
            null, CurrenciesDatabase.COLUMN_CURRENCY1 + ","
              + CurrenciesDatabase.COLUMN_CURRENCY2, null);

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

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

            do
            {
              currency1Str = cursor.getString(currency1Id);
              String currency2Str = cursor.getString(currency2Id);
              Double factor = cursor.getDouble(factorId);

              if (lastCurrency1Str == null
                || !lastCurrency1Str.equals(currency1Str))
              {
                /*
                 * NOTE: Update outer map when we see a new currency;
                 * ORDER BY ensures we don't see a currency1 twice except
                 * consecutively
                 */
                if (mapForCurrency != null)
                {
                  newCurrencyConversions.put(lastCurrency1Str, mapForCurrency);
                }

                lastCurrency1Str = new String(currency1Str);

                /* NOTE: New currency1: Reset inner map */
                mapForCurrency = newCurrencyConversions.get(currency1Str);
              }

              /* If we did not see this currency1 before */
              if (mapForCurrency == null)
              {
                mapForCurrency = new HashMap<String, Double>();
              }

              /*
               * NOTE: Update inner map after each table row; assignment to
               * mapForCurrency above ensures we are putting the factor
               * into the correct map.
               */
              mapForCurrency.put(currency2Str, factor);
            }
            while (cursor.moveToNext());

            /*
             * NOTE: Update from last table row; cursor not empty, so we can
             * skip the test for null
             */
            newCurrencyConversions.put(currency1Str, mapForCurrency);

            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();

      @SuppressWarnings("nls")
      Cursor myCursor =
          dbConn.query(true, CurrenciesDatabase.TABLE, null, null, null, null,
            null, CurrenciesDatabase.COLUMN_CURRENCY1 + ","
              + CurrenciesDatabase.COLUMN_CURRENCY2, null);

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

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

              /* DEBUG */
              queryResult +=
                  currency1Str + " --> " + currency2Str + ": " + factor + "\n";
            }
            while (myCursor.moveToNext());
          }
        }
        catch (IllegalArgumentException e)
        {
          /* Could not retrieve column index */
          e.printStackTrace();
        }
      }

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