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

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map.Entry;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
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 = 8;

  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 final String COLUMN_UPDATED = "updated"; //$NON-NLS-1$

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

  private SQLiteDatabase database;
  private final DateFormat iso8601format = new SimpleDateFormat(
    "yyyy-MM-dd HH:mm:ss");

  /**
   * @param context
   *          The Activity in which this wrapper is used
   */
  public CurrenciesDatabase(Context 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"
      + ", " + CurrenciesDatabase.COLUMN_UPDATED
      + " TEXT"
      + ", CONSTRAINT unique_currency_pair UNIQUE ("
      + CurrenciesDatabase.COLUMN_CURRENCY + ") ON CONFLICT REPLACE)");

    this.writeConversionsToDatabase(db);
  }

  /**
   * @param db
   *          The database; <code>null</code> uses the default database
   */
  public void writeConversionsToDatabase(SQLiteDatabase db)
  {
    HashMap<String, ConversionData> currencyConversions =
      this.getConversionRates();

    if (db == null)
    {
      db = this.database;
    }

    if (!db.isOpen())
    {
      try
      {
        db = this.getWritableDatabase();
      }
      catch (SQLiteException e)
      {
        Log.e(this.getClass().toString(), "Could not open database", e);
        throw e;
      }
    }

    if (db.isOpen())
    {
      for (Entry<String, ConversionData> factorEntry : currencyConversions
        .entrySet())
      {
        ContentValues values = new ContentValues();
        values.put(CurrenciesDatabase.COLUMN_CURRENCY, factorEntry.getKey());
        values.put(CurrenciesDatabase.COLUMN_FACTOR, factorEntry.getValue()
          .getRate());
        values.put(CurrenciesDatabase.COLUMN_UPDATED,
          this.iso8601format.format(factorEntry.getValue()
            .getUpdated()));

        /* INSERT suffices here, thanks to ON CONFLICT REPLACE */
        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, ConversionData> 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! */
      this.database = this.getWritableDatabase();

      Cursor cursor =
          this.database.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);
          int updatedId =
            cursor.getColumnIndexOrThrow(CurrenciesDatabase.COLUMN_UPDATED);

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

            do
            {
              String currencyStr = cursor.getString(currency1Id);
              Double factor = cursor.getDouble(factorId);
              String updatedStr = cursor.getString(updatedId);

              Date updated = new Date(0);
              try
              {
                if (updatedStr != null)
                {
                  updated = this.iso8601format.parse(updatedStr);
                }
              }
              catch (ParseException e)
              {
                Log.e(this.getClass().toString(),
                  "Parsing ISO8601 datetime failed: '" + updatedStr + "'", e);
              }

              newCurrencyConversions.put(currencyStr, new ConversionData(
                factor, updated));
            }
            while (cursor.moveToNext());

            CurrenciesDatabase.conversionRates = newCurrencyConversions;
          }
        }
        catch (IllegalArgumentException e)
        {
          Log.e(this.getClass().toString(), "Could not retrieve column index",
            e);
        }
      }

      this.database.close();
    }
    catch (SQLiteException e1)
    {
      Log.e(this.getClass().toString(), "Could not open database", e1);
    }
  }
}