package com.ethostream.ethoandroid;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.net.ParseException;
import android.util.Log;

/**
 * @author Matt Boelter - mboelter@ethostream.com
 * 
 * Helper class to specifically deal with properties stored in the parent
 * SQLite database (which can be managed through the LocalSQLiteHelper Class).
 * 
 * Note: when using the constructor for a new DatabasePropertyAdapter object,
 * one must still call open before making any changes. Also, please remember to
 * close the working object when you're done!
 * 
 * TODO: Need to pare down the database management in this class to only
 * interactions having to do with the Property table.
 *
 */
public class DatabasePropertyAdapter {
	
	// Database fields
	private static final String PROPERTY_TABLE = "Property";
	private static final String PROPERTY_ID = "_id";
	private static final String PROPERTY_NAME = "PropertyName";
	private static final String PROPERTY_REMOTE_ID = "RemoteID";
	private static final String REMOTE_QUERY_SITE = "http://android.telkonet.com/dbproperties.php";
	private static final String REMOTE_PROPERTY_NAME = "PropertyName";
	private static final String REMOTE_PROPERTY_ID = "ID";
	private Context context;
	private SQLiteDatabase database;
	private LocalSQLiteHelper dbHelper;
	
	/**
	 * Constructor. Still need to call open for anything interesting to happen.
	 * @param context
	 */
	public DatabasePropertyAdapter(Context context) {
		this.context = context;
	}
	
	/**
	 * Open parent database in a writable state. This should also create a new
	 * database if necessary, but seems not to :(
	 * 
	 * TODO: Find out the issue here
	 * 
	 * @return
	 * @throws SQLException
	 */
	public DatabasePropertyAdapter open() throws SQLException {
		dbHelper = new LocalSQLiteHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	
	/**
	 * Cleanly close parent database.
	 */
	public void close() {
		dbHelper.close();
	}
	
	/**
	 * @return int - number of records in the property table.
	 */
	public int size()
	{
		Cursor cursor = database.query(PROPERTY_TABLE, null, null, null, null, null, null);
		int size = cursor.getCount();
		cursor.close();
		return size;
	}
	
	public int getMaxRemoteID()
	{
		Cursor cursor = database.rawQuery("SELECT MAX("+PROPERTY_REMOTE_ID+") FROM "+PROPERTY_TABLE, null);
		cursor.moveToFirst();
		if (cursor.isNull(0))
		{
			return 0;
		}
		else 
		{
			return cursor.getInt(0);
		}
	}
	
	/**
	 * Performs a simple database insert, adding a given record to the Property
	 * table.
	 * 
	 * @param rowData (ContentValues) Schema - _id:int(AUTOGENERATED,
	 * 															DO NOT INCLUDE)
	 * 										 - PropertyName:String, not null
	 * 										 - RemoteID:int, not null
	 * @return long - rowId of inserted record
	 */
	public long createProperty(ContentValues rowData) 
	{
		return database.insert(PROPERTY_TABLE, null, rowData);
	}
	
	/**
	 * If the Property table does not exist, this function will create it. If it
	 * does exist, this function will not complain.
	 * 
	 */
	public void createPropertyTable()
	{
		database.execSQL("CREATE TABLE IF NOT EXISTS " 
				+ PROPERTY_TABLE 
				+ "(" 
				+ PROPERTY_ID + " INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL, " 
				+ PROPERTY_NAME + " VARCHAR(255)  NULL, "
				+ PROPERTY_REMOTE_ID + " INTEGER NOT NULL UNIQUE);"
				+ ")");
		
		// The following code will replace the table with a virtual table
		// capable of FTS3 (Full Text Search). It is fast, but is limited to
		// whole word searches, so is not the default choice.
		/*
		final String FTS_TABLE_CREATE = "CREATE VIRTUAL TABLE " 
			+ PROPERTY_TABLE + " USING fts3 " 
			+ "("
			+ PROPERTY_NAME + " VARCHAR(255)  NULL, "
			+ PROPERTY_REMOTE_ID + " INTEGER NOT NULL);"
			+ ")";
		database.execSQL(FTS_TABLE_CREATE);
		*/
	}
	public void dropPropertyTable()
	{
		database.execSQL("DROP TABLE IF EXISTS " + PROPERTY_TABLE);
	}
	
	/**
	 * This function fetches JSON formated information from a remote site
	 * that conducts a SQL query. Takes in an int argument that determines where
	 * in the list to start forming the list. Use 0 to return all.
	 * 
	 * TODO: Clean up the post and string sections to streamline variables
	 * 
	 * CURRENTLY REMOTE QUERY RETURNS ANY PROPERTY WITH AN HSIA STATUS
	 * NOT EQUAL TO UNVERIFIED, NEW, LOST, OR CANCELED. ENSURE THAT THIS IS
	 * DESIRED DATA SET.
	 * 
	 * @param init - int value that determines the starting propertyID of the 
	 * returned list.
	 * 
	 * @return ArrayList<String> with the properties
	 */
	public ArrayList<ContentValues> getRemotePropertyList(int init)
	{
		InputStream is = null;
		ArrayList<NameValuePair> queryResult = new ArrayList<NameValuePair>();
		StringBuilder sb = null;
		String result;
		ArrayList<ContentValues> list = new ArrayList<ContentValues>();
		
		//httpPost
		try{
			HttpClient httpclient = new DefaultHttpClient();
			HttpPost httppost = new HttpPost(REMOTE_QUERY_SITE + "?INIT=" + init);
			httppost.setEntity(new UrlEncodedFormEntity(queryResult));
			HttpResponse response = httpclient.execute(httppost);
			HttpEntity entity = response.getEntity();
			is = entity.getContent();
		}catch(Exception e){
			Log.e("log_tag", "Error in http connection"+e.toString());
			return new ArrayList<ContentValues>(0);
		}
		//convertString
		try{
			BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
			sb = new StringBuilder();
			sb.append(reader.readLine() + "\n");
			String line="0";
			while ((line = reader.readLine()) != null) {
				sb.append(line + "\n");
			}
			is.close();
			result=sb.toString();
		}catch(Exception e){
			Log.e("log_tag", "Error converting result "+e.toString());
			return new ArrayList<ContentValues>(0);
		}
		//parseJSON
		try{
			JSONArray jArray = new JSONArray(result);
			JSONObject json_data=null;
			for(int i=0;i<jArray.length();i++){
				json_data = jArray.getJSONObject(i);
				ContentValues cv = new ContentValues();
				cv.put(PROPERTY_NAME, json_data.getString(REMOTE_PROPERTY_NAME));
				cv.put(PROPERTY_REMOTE_ID, json_data.getString(REMOTE_PROPERTY_ID));
				list.add(cv);
			}
		}
		catch(JSONException e1){
			return new ArrayList<ContentValues>(0);
		} catch (ParseException e1) {
			e1.printStackTrace();
			return new ArrayList<ContentValues>(0);
		}
		return list;
	}
	
	/**
	 * This function will unconditionally update the Property table without the
	 * need to pass in a list of properties. Useful for streamlining a forced
	 * update from outside this class.
	 * 
	 * 
	 * @return List of ContentValues, containing the new data added to the 
	 * database
	 */
	public ArrayList<ContentValues> refreshProperty()
	{
		Log.i(this.toString(), "refreshPoperty()=========================================");
		ArrayList<ContentValues> list = getRemotePropertyList(0);
		dropPropertyTable();
		createPropertyTable();
		for(int i = 0; i < list.size(); i++)
		{
			createProperty(list.get(i));
		}
		
		return list;
	}
	
	/**
	 * This function should unconditionally updates the Property table in the 
	 * database. 
	 * 
	 * TODO: Do we need to return the used list?
	 * 
	 * @param inputList - ArrayList<String> of names to add to table
	 * @return ArrayList<String> of PropertyNames
	 */
	public ArrayList<ContentValues> refreshProperty(ArrayList<ContentValues> inputList)
	{
		Log.i(this.toString(), "refreshPoperty()=========================================");
		dropPropertyTable();
		createPropertyTable();
		for(int i = 0; i < inputList.size(); i++)
		{
			createProperty(inputList.get(i));
		}
		
		return inputList;
	}
	
	/**
	 * This function should conditionally update the Property table if there is 
	 * a difference between the number of names from the remote list and the 
	 * number of rows in the local table.
	 * 
	 * 
	 */
	public void conditionalRefreshProperty()
	{
		Log.i(this.toString(), "conditionalRefresh=========================================");
		ArrayList<ContentValues> list = getRemotePropertyList(0);
		if (list.size() != size())
		{
			refreshProperty(list);
		}
	}
	
	/**
	 * Delete a property with a given rowId. Probably don't need this function.
	 * 
	 * TODO: Remove?
	 * 
	 * @param rowId
	 * @return boolean - true on success.
	 */
	public boolean deleteProperty(long rowId) {
		return database.delete(PROPERTY_TABLE, PROPERTY_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Return a Cursor over the list of all property in the database
	 * 
	 * @return Cursor over all properties
	 */
	public Cursor getAll() {
		return database.query(PROPERTY_TABLE, null, null, null, null, null, null);
	}
	
	/**
	 * getMatches will perform a SQL query on the database which will match
	 * PropertyNames similar to the constraint passed in. The specific purpose
	 * is for creating a list of autocomplete matches in a textbox input field.
	 * 
	 * See commented snippet about using with FTS3.
	 * 
	 * @param constraint - String which limits the result set to properties with
	 * names similar to the constraint.
	 * @return Cursor over the result set.
	 */
	public Cursor getMatches(String constraint)
	{
		
		constraint = constraint.replace(' ', '%');
		
		Log.i(this.toString(), "GET MATCHES=========================================");
		Log.i(this.toString(), "select");
		Log.i(this.toString(), PROPERTY_NAME);
		Log.i(this.toString(), "from");
		Log.i(this.toString(), PROPERTY_TABLE);
		Log.i(this.toString(), "where");
		Log.i(this.toString(), PROPERTY_NAME + " LIKE %" + constraint + "%");
		if (constraint.length() > 2)
		{
			/*
			return database.query(PROPERTY_TABLE, new String[] {PROPERTY_ID, PROPERTY_NAME}, 
					PROPERTY_NAME + " LIKE ?",
					new String[] {"%"+constraint+"%"}, null, null, null);
			*/
			
			// Using below to debug number of results
			
			Cursor cursor = database.query(PROPERTY_TABLE, new String[] {PROPERTY_ID, PROPERTY_NAME}, 
					PROPERTY_NAME + " LIKE ?",
					new String[] {"%"+constraint+"%"}, null, null, PROPERTY_NAME + " ASC");
			Log.i(this.toString(), "ROWS IN DATA SET: " + cursor.getCount() + " ===========================================");
			return cursor;
			
			// The below snippet is the MATCH query for use with an FTS table.
			/*
			Cursor cursor = query(new String[] {PROPERTY_ID, PROPERTY_NAME}, 
					PROPERTY_NAME + " MATCH ?",
					new String[] {constraint});
			Log.i(this.toString(), "ROWS IN DATA SET: " + cursor.getCount() + " ===========================================");
			return cursor; 
			*/
		}
		else
		{
			return null;
		}
	}
	
	/**
	 * Returns a cursor over any properties with an exact match to the passed 
	 * PropertyName. Result set returns all data for the records.
	 * 
	 * TODO: This is currently used to derive a RemoteID, and shouldn't be used
	 * as such as it is very possible that there are multiple properties with 
	 * the same PropertyName. Consider changing the behavior  
	 * 
	 * @param PropertyName (String) - must match record's name exactly
	 * @return Cursor pointing to ALL properties with the exact name.
	 */
	public Cursor getProperty(String PropertyName)
	{
		return database.query(PROPERTY_TABLE, null, PROPERTY_NAME + " = ?", new String[] {PropertyName}, null, null, null);
	}
	
	/**
	 * Return a Cursor positioned at the defined property
	 * 
	 * TODO: test, rewrite query
	 * 
	 */
	public Cursor getProperty(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, PROPERTY_TABLE, new String[] {
				PROPERTY_ID},
				PROPERTY_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/*
	/**
	 * Custom built query function depends on this. Only used for FTS tables.
	 * 
	 * @return HashMap of tables
	 *
	private static HashMap<String,String> buildColumnMap() {
        HashMap<String,String> map = new HashMap<String,String>();
        map.put(PROPERTY_NAME, PROPERTY_NAME);
        map.put(PROPERTY_REMOTE_ID, PROPERTY_REMOTE_ID);
        map.put(BaseColumns._ID, "rowid AS " +
                BaseColumns._ID);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
        map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " +
                SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
        return map;
    }


    /**
     * Use this to replace the database's query when using an FTS table.
     * 
     * Performs a database query.
     * @param selection The selection clause
     * @param selectionArgs Selection arguments for "?" components in the selection
     * @param columns The columns to return
     * @return A Cursor over all rows matching the query
     *
    private Cursor query(String[] columns, String selection, String[] selectionArgs) {
    	//database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
        /* The SQLiteBuilder provides a map for all possible columns requested to
         * actual columns in the database, creating a simple column alias mechanism
         * by which the ContentProvider does not need to know the real column names
         *
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(PROPERTY_TABLE);
        builder.setProjectionMap(buildColumnMap());

        Cursor cursor = builder.query(database,
                columns, selection, selectionArgs, null, null, null);

        if (cursor == null) {
            return null;
        } else if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }
        return cursor;
    }
    */

	
}