package com.chicagoandroid.w180;

import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper

{

private static final String DATABASE_NAME = "w180";

private static final int DATABASE_VERSION = 1;

private static CursorFactory factory = null;

private static final String LOG_TAG = DatabaseHelper.class.getCanonicalName();

private static final String QUERY_FETCH = "SELECT _id, latitude, longitude, created FROM locations ORDER BY ";

private final Context context;

private SQLiteDatabase db;

public DatabaseHelper(Context context)

{

super(context, DATABASE_NAME, factory, DATABASE_VERSION);

this.context = context;

db = getReadableDatabase();

Toast.makeText(context, "Creating instance of DatabaseHelper\n" + DATABASE_NAME, Toast.LENGTH_SHORT).show();

}

private void executeMultipleSQL(SQLiteDatabase db, String[] sql)

{

for (String line : sql)

{

if (line.trim().length() > 0)

{

Log.i("DATABASE  executing", line);

db.execSQL(line);

}

}

}

public LocationCursor fetch(LocationCursor.SortBy sortBy)

{

LocationCursor cursor = null;

String sql = QUERY_FETCH + sortBy.toString();

try

{

SQLiteDatabase db = getReadableDatabase();

cursor = (LocationCursor) db.rawQueryWithFactory(new LocationCursor.Factory(), sql, null, null);

cursor.moveToFirst();

} catch (Exception e)

{

Log.e(LOG_TAG, e.getMessage());

}

return cursor;

}

public void insert(LocationMark mark)

{

String sql = "INSERT INTO locations (_id,  latitude, longitude, created) VALUES (NULL, ?, ?, datetime('now'))";

Object[] bindArgs = new Object[] { mark.getLatitude(), mark.getLongitude() };

try

{

getWritableDatabase().execSQL(sql, bindArgs);

Toast.makeText(context, "inserted into databse \n" + mark.toString(), Toast.LENGTH_SHORT).show();

} catch (SQLException e)

{

Log.e("DATABASE  insert", e.getMessage());

}

}

@Override

public void onCreate(SQLiteDatabase db)

{

Toast.makeText(context, "DATABASE  onCreate\n", Toast.LENGTH_SHORT).show();

int query = R.string.sql_onCreate;

String[] sql = sql(query);

db.beginTransaction();

try

{

executeMultipleSQL(db, sql);

Log.i("DATABASE  onCreate", "success");

Toast.makeText(context, "DATABASE  created! \n" + query, Toast.LENGTH_SHORT).show();

// db.setTransactionSuccessful();

} catch (SQLException e)

{

Log.e("DATABASE ERROR onCreate", e.getMessage());

Toast.makeText(context, "DATABASE ERROR onCreate\n" + e.getMessage(), Toast.LENGTH_SHORT).show();

}

}

@Override

public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2)

{

// TODO Auto-generated method stub

}

public List<LocationMark> selectAll()

{

String table = "locations";

String[] columns = new String[] { "_id", "latitude", "longitude", "created" };

String selection = null;

String[] selectionArgs = null;

String groupBy = null;

String having = null;

String orderBy = "_id desc";

Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);

List<LocationMark> marks = new ArrayList<LocationMark>();

if (cursor.moveToFirst())

{

do

{

String id = cursor.getString(0);

double latitude = cursor.getDouble(1);

double longtitude = cursor.getDouble(2);

String date = cursor.getString(3);

LocationMark mark = new LocationMark(latitude, longtitude);

mark.setDateCreatedFromDatabase(date);

marks.add(mark);

} while (cursor.moveToNext());

}

if (cursor != null && !cursor.isClosed())

{

cursor.close();

}

return marks;

}

private String[] sql(int stringId)

{

return context.getString(stringId).split(";");

}

}