package com.example.usstates;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import org.apache.http.util.ByteArrayBuffer;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class StatesDbAdapter {
  public static final int STATES_DB_VERSION = 1;
  public static final String STATES_DB_NAME = "usstates.db";
  public static final String STATES_TAB_NAME = "usstates";
  public static final String KEY_STATE_ID = "_id";
  public static final String KEY_STATE_ABBR = "state_abbreviation";
  public static final String KEY_STATE_NAME = "state_name";
  public static final String KEY_STATE_NICK = "state_nickname";
  public static final String KEY_STATE_FLAG = "state_flag";
  public static final String KEY_STATE_CAPITAL = "state_capital";
  private StatesDbOpenHelper dbHelper;
  private Context context;
  private SQLiteDatabase db;

  public StatesDbAdapter(Context _context) {
    this.context = _context;
    dbHelper = new StatesDbOpenHelper(context, STATES_DB_NAME, null,
        STATES_DB_VERSION);
    open();
  }

  @Override
  protected void finalize() throws Throwable {
    super.finalize();
    close();
  }

  public void open() throws SQLiteException {
    try {
      db = dbHelper.getWritableDatabase();
    }
    catch (SQLiteException ex) {
      db = dbHelper.getReadableDatabase();
    }
  }

  public void close() {
    db.close();
  }

  public Cursor getAllStates() {
    return db.query(STATES_TAB_NAME, null, null, null, null, null,
        KEY_STATE_NAME);
  }

  private class StatesDbOpenHelper extends SQLiteOpenHelper {
    private Context context;
    private static final String SQL_DROP_USSTATES_TAB = "DROP TABLE IF EXISTS "
        + STATES_TAB_NAME;
    private static final String SQL_CREATE_USSTATES_TAB = "CREATE TABLE "
        + STATES_TAB_NAME + " ( " + KEY_STATE_ID + " INTEGER PRIMARY KEY, "
        + KEY_STATE_ABBR + " TEXT, " + KEY_STATE_NAME + " TEXT, "
        + KEY_STATE_NICK + " TEXT, " + KEY_STATE_FLAG + " BLOB, "
        + KEY_STATE_CAPITAL + " TEXT " + " );";

    public StatesDbOpenHelper(Context _context, String name,
        CursorFactory factory, int version) {
      super(_context, name, factory, version);
      context = _context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      db.execSQL(SQL_DROP_USSTATES_TAB);
      db.execSQL(SQL_CREATE_USSTATES_TAB);
      db.beginTransaction();
      loadStateData(db);
      loadStateImages(db);
      db.setTransactionSuccessful();
      db.endTransaction();

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      //
    }

    private boolean loadStateData(SQLiteDatabase db) {
      InputStream is;
      try {
        is = context.getResources().openRawResource(R.raw.states);
      }
      catch (Resources.NotFoundException nfe) {
        Log.d(context.getPackageName(),
            "Error opening states data file for read", nfe);
        return false;
      }
      BufferedReader br;
      br = new BufferedReader(new InputStreamReader(is));
      try {
        String line;
        ContentValues cv = new ContentValues(4);
        int count = 0;
        Log.d("StatesDbOpenHelper.loadStateData()", "Loading state records");
        while ((line = br.readLine()) != null) {
          String[] values = line.split("[|]");
          if (values.length == 4) {
            cv.put(KEY_STATE_ABBR, values[0]);
            cv.put(KEY_STATE_NAME, values[1]);
            cv.put(KEY_STATE_NICK, values[2]);
            cv.put(KEY_STATE_CAPITAL, values[3]);
            db.insert(STATES_TAB_NAME, null, cv);
            count++;
          }
        }
        br.close();
        Log.d("StatesDbOpenHelper.loadStateData()", "Loaded " + count
            + " state records");
      }
      catch (IOException ioe) {
        Log.d(context.getPackageName(), "Error reading states datafile", ioe);
      }
      return true;
    }

    private boolean loadStateImages(SQLiteDatabase db) {
      Cursor c = db.rawQuery("select " + KEY_STATE_ABBR + " FROM "
          + STATES_TAB_NAME, null);
      String update = "UPDATE " + STATES_TAB_NAME + " SET " + KEY_STATE_FLAG
          + " = ? WHERE " + KEY_STATE_ABBR + " = ?";
      SQLiteStatement stmt = db.compileStatement(update);
      String abbr = "";
      byte[] flagImage = null;
      if (c.moveToFirst()) {
        String resName;
        int resId;
        int colNum = c.getColumnIndex(KEY_STATE_ABBR);
        do {
          abbr = c.getString(colNum);
          resName = abbr.toLowerCase() + "_fl_t";
          // resName = "R.raw." + abbr.toLowerCase() + "_fl_t";
          resId = context.getResources().getIdentifier(resName, "raw",
              context.getPackageName());
          if (resId > 0) {
            InputStream is;
            try {
              is = context.getResources().openRawResource(resId);
            }
            catch (Resources.NotFoundException nfe) {
              Log.d("StatesDbOpenHelper.loadStateImages()", "Image for " + abbr
                  + ", id " + resId + " (" + resName + ") not found", nfe);
              continue;
            }
            try {
              BufferedInputStream bis = new BufferedInputStream(is);
              ByteArrayBuffer baf = new ByteArrayBuffer(500);
              int current = 0;
              while ((current = bis.read()) != -1) {
                baf.append((byte) current);
              }
              flagImage = baf.toByteArray();
              stmt.bindString(2, abbr);
              stmt.bindBlob(1, flagImage);
              try {
                stmt.executeInsert();
              }
              catch (SQLException sqle) {
                Log.d("StatesDbOpenHelper.loadStateImages()",
                    "Error inserting image for " + abbr, sqle);
              }
            }
            catch (Exception e) {
              Log.d("StatesDbOpenHelper.loadStateImages()",
                  "Error: " + e.toString());
            }
          }
        } while (c.moveToNext());
        return true;
      }
      else {
        return false;
      }
    }

  } // class StatesDbOpenHelper

} // class StatesDbAdapter
