package com.sharefaith.thesharefaithapp.base;

import android.text.Html;
import android.util.Log;

import com.appideas.base.AiDb;
import com.appideas.base.AiSTr;

import org.json.JSONException;
import org.json.JSONObject;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;

/**
 * Created by Sharefaith1 on 2014-10-22.
 * class for interaction with app data
 */
public class SFAppData
{
	AiDb mDb;
	SFApplication mApplication = SFApplication.getInstance();

	/**
	 * Constructor
	 * gets reference to Database
	 */
	public SFAppData()
	{
		this.mDb = mApplication.getDb();
	}

	/**
	 * Get the tag of the first section
	 * @return
	 */
	public String getFirstSectionTag()
	{
		// TODO: Remove this for version 2.1
		String sql =
				"SELECT COUNT(*) " +
						" FROM ondevice_sections " +
						" WHERE position_index = 0";
		String[][] results = this.mDb.query( sql );
		Integer count = AiSTr.denullifyInt( results[0][0] );
		if( count != 1 )
		{
			this.fixNonZeroIndex();
		}

		sql =
				"SELECT sf_tag " +
						" FROM ondevice_sections " +
						" WHERE position_index = 0";
		results = this.mDb.query( sql );
		return AiSTr.denullifyString( results[0][0] );

	}

	// As a temporary fix to crashes caused by people turning off position index 0, re-do the position indexes
	// TODO: Remove this for version 2.1
	public void fixNonZeroIndex()
	{
		Integer currentIndex = 0;

		//Log.d( "mTag", "FIXING NON-ZERO INDEX" );

		String sql =
				"SELECT id" +
						" FROM ondevice_sections " +
						" ORDER BY position_index";
		String[][] results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			Integer currentId = AiSTr.denullifyInt( results[i][0] );
			sql = "UPDATE ondevice_sections SET position_index = " + currentIndex + " WHERE id = " + currentId;
			this.mDb.noReturnQuery( sql );
			currentIndex++;
		}
	}

	/**
	 * get the Section Ids of nav items outside of more
	 * @return Top level section ids
	 */
	public Integer[] getTopLevelSectionIds()
	{
		String sql =	"SELECT     id " +
				"FROM       ondevice_sections " +
				"WHERE      parent_id = 0 " +
				" AND sf_tag IN ( " + getTagsToShow() + " ) " +
				"ORDER BY   position_index";
		String[][] result = this.mDb.query( sql );
		Integer[] returnValue = new Integer[result.length];
		for( int i = 0; i < result.length; i++ )
		{
			returnValue[i] = AiSTr.denullifyInt( result[i][0] );
		}

		return returnValue;
	}
	//// TODO: 11/29/16 update this for new nav sections

	/**
	 * get a list of the current section types so old versions won't pull in the new sections
	 * @return String
     */
	public String getTagsToShow()
	{
		return "'posts', 'sermons', 'contact', 'location', 'donate', 'bible', 'more', 'inbox', 'calendar', 'newsletter','link','stream'";
	}

	/**
	 *
	 * @param sectionId
	 * @param parentSectionId
	 * @return
	 */
	public ArrayList<HashMap<String,String>> getContentForSectionId( int sectionId, int parentSectionId )
	{
		ArrayList<HashMap<String,String>> returnValue = new ArrayList<HashMap<String,String>>();

		String sql =	"SELECT     id, position_index, content, last_update " +
						"FROM       section_content " +
						"WHERE      ondevice_section_id = "  + sectionId + "  AND " +
						" 			parent_id = " + parentSectionId + " " +
						"ORDER BY   position_index";
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			HashMap<String,String> infoDictionary = new HashMap<String,String>();
			infoDictionary.put( "id", Integer.toString( AiSTr.denullifyInt( result[ i ][ 0 ] ) ) );
			infoDictionary.put( "position_index", Integer.toString( AiSTr.denullifyInt( result[ i ][ 1 ] ) ) );
			infoDictionary.put( "content", AiSTr.denullifyString( result[i][2] ) );
			infoDictionary.put( "last_update", Integer.toString( AiSTr.denullifyInt( result[i][3] ) ) );

			returnValue.add( i, infoDictionary );
		}

		return returnValue;
	}

	/**
	 *
	 * @param sectionContentId
	 * @return
	 */
	public HashMap<String,String> getMetaForSectionContentId( int sectionContentId )
	{
		HashMap<String,String> returnValue = new HashMap<String,String>();

		String sql =	"SELECT     meta_key, meta_value " +
						"FROM       section_content_meta " +
						"WHERE      section_content_id = "  + sectionContentId;
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue.put( AiSTr.denullifyString( result[i][0] ), AiSTr.denullifyString( result[i][1] ) );
		}

		return returnValue;
	}

	/**
	 *
	 * @param sectionId
	 * @return
	 */
	public HashMap<String,String> getIconsForSectionId( int sectionId )
	{
		HashMap<String,String> returnValue = new HashMap<String,String>();
		returnValue.put( "icon", "" );
		returnValue.put( "icon_selected", "" );

		String sql =	"SELECT     meta_key, meta_value " +
						"FROM       ondevice_section_meta " +
						"WHERE      meta_key IN ( 'icon', 'icon_selected' ) AND " +
						"			ondevice_section_data_id = " + sectionId;
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue.put( AiSTr.denullifyString( result[i][0] ), AiSTr.denullifyString( result[i][1] ) );
		}

		return returnValue;
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public int getSectionIdByPosition( int position )
	{
		String sql =
				"SELECT id FROM ondevice_sections " +
						" WHERE position_index = " + position;
		String[][] results = this.mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param sectionId
	 * @return
	 */
	public int getContentIdBySectionId( int sectionId)
	{
		int returnValue;
		String sql =
				"SELECT id FROM section_content " +
						" WHERE ondevice_section_id = " + sectionId;
		String[][] results = this.mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param sectionContentId
	 * @return
	 */
	public String[] getBibleVersions(int sectionContentId )
	{
		String[] returnValue;
		/* This does the odd null exception, but not sure how it happens in-the-wild.
		Random rand = new Random();
		if( rand.nextInt(10) > 5) {
			this.mDb.query("DELETE FROM section_content_meta " +
					" WHERE section_content_id = " + sectionContentId + " AND meta_key = 'version' ");
		}*/
		String sql =
				"SELECT meta_value FROM section_content_meta " +
						" WHERE section_content_id = " + sectionContentId + " AND meta_key = 'version' ";
		String[][] results = this.mDb.query( sql );
		returnValue = new String[results.length];
		for( int i = 0; i < results.length; i++)
		{
			//Log.d("mTag","version " + i + " = " + AiSTr.denullifyString( results[i][0] ));
			returnValue[i] = AiSTr.denullifyString( results[i][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @return
	 */
	public int getBibleVersionId()
	{
		String sql =
				"SELECT id " +
						" FROM bible_versions " +
						" WHERE abbreviation = '" + mApplication.getSfCurrentBible().mAbreviation + "'";
		String[][] results = this.mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param abbreviation
	 * @return
	 */
	public int getBookIndex(String abbreviation)
	{
		int bibleVersionId = getBibleVersionId();
		String sql=
				"SELECT book_order " +
						" FROM bible_version_to_book " +
						" WHERE version_id = " + bibleVersionId + " AND book_id = " +
						" (SELECT id FROM bible_books WHERE abbreviation = '" + abbreviation + "')";
		String[][] results = this.mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] )-1;

	}

	/**
	 *
	 * @param abbreviation
	 * @return
	 */
	public HashMap<String,String> getBibleContent(String abbreviation)
	{
		HashMap<String,String> returnValue = new HashMap<String, String>();
		String sql =
				"SELECT   id, full_name, copyright, sync_version, language, api_provider, api_key, " +
						" path, is_downloaded, access_time " +
						" FROM bible_versions " +
						" WHERE abbreviation = '" + abbreviation + "'";
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue.put( "id", AiSTr.denullifyString( result[i][0] ) );
			returnValue.put( "full_name", AiSTr.denullifyString( result[i][1] ) );
			returnValue.put( "copyright", AiSTr.denullifyString( result[i][2] ) );
			returnValue.put( "sync_version", AiSTr.denullifyString( result[i][3] ) );
			returnValue.put( "language", AiSTr.denullifyString( result[i][4] ) );
			returnValue.put( "api_provider", AiSTr.denullifyString( result[i][5] ) );
			returnValue.put( "api_key", AiSTr.denullifyString( result[i][6] ) );
			returnValue.put( "path", AiSTr.denullifyString( result[i][7] ) );
			returnValue.put( "is_downloaded", Integer.toString( AiSTr.denullifyInt( result[i][8] ) ) );
			returnValue.put( "access_time", Integer.toString( AiSTr.denullifyInt( result[i][9] ) ) );
		}
		return returnValue;
	}

	/**
	 *
	 * @return
	 */
	public String getBiblePosition()
	{
		String sql =
				"SELECT lookup_value " +
						" FROM config_lookup " +
						" WHERE lookup_key = 'bibleposition' ";
		String[][] results = this.mDb.query( sql );
		return AiSTr.denullifyString( results[0][0] );
	}

	public ArrayList<HashMap<String,String>> getBooksData(int versionId)
	{
		ArrayList<HashMap<String,String>> returnValue = new ArrayList<HashMap<String, String>>();
		String[][] results;
		String sql =
				"SELECT bible_books.abbreviation, bible_books.full_name, bible_version_to_book.book_id, " +
						" bible_version_to_book.last_chapter, bible_version_to_book.book_order " +
						" FROM bible_books, bible_version_to_book " +
						" WHERE bible_version_to_book.version_id = " + versionId +
						" AND bible_version_to_book.book_id = bible_books.id " +
						" ORDER BY bible_version_to_book.book_order ";
		results = this.mDb.query( sql );
		for(int i = 0; i < results.length; i++)
		{
			HashMap<String,String> map = new HashMap<String, String>();
			map.put( "abbreviation", AiSTr.denullifyString( results[i][0] ) );
			map.put( "full_name", AiSTr.denullifyString( results[i][1] ) );
			map.put( "book_id", AiSTr.denullifyString( results[i][2] ) );
			map.put( "last_chapter", Integer.toString( AiSTr.denullifyInt( results[i][3] ) ) );
			map.put( "book_order", Integer.toString( AiSTr.denullifyInt( results[i][4] ) ) );
			returnValue.add( map );
		}

		return returnValue;
	}

	/**
	 *
	 * @return
	 */
	public int getBibleTheme()
	{
		String[][] results;
		String sql =
				"SELECT lookup_value " +
						" FROM config_lookup " +
						" WHERE lookup_key = 'bibletheme'";
		results = this.mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] ) ;
	}

	/**
	 *
	 * @param theme
	 */
	public void setBibleTheme(int theme)
	{
		String sql =
				"UPDATE		config_lookup " +
						"SET		lookup_value = '" + theme + "' " +
						"WHERE		lookup_key =  'bibletheme'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @return
	 */
	public int getBibleFont()
	{
		String[][] results;
		String sql =
				"SELECT lookup_value " +
						" FROM config_lookup " +
						" WHERE lookup_key = 'biblefont'";
		results = this.mDb.query( sql );
		return  AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param font
	 */
	public void setBibleFont(int font)
	{
		String sql =
				"UPDATE		config_lookup " +
						"SET		lookup_value = '" + font + "' " +
						"WHERE		lookup_key =  'biblefont'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 */
	public void setBiblePosition()
	{

		String path = mApplication.getSfCurrentBible().mAbreviation + "/" + mApplication.getCurrentBook().mAbbreviation + "/" +
				( mApplication.chapterIndex + 1 ) + ".html";
		//Log.d("mTag","Hit setBiblePosition, path = " + path);
		String sql =
				"UPDATE		config_lookup " +
						" SET		lookup_value = '" + path + "' " +
						" WHERE		lookup_key =  'bibleposition'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @param path
	 */
	public void addPathToBibleAccessHistory( String path )
	{
		//Log.d("mTag","Adding " + path + " to history");
		String sql =
				"INSERT INTO bible_access_history " +
						" (path, access_time) " +
						" Values ('" + path +"', " + SFUtil.currentTimestamp() + ")";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @return
	 */
	public ArrayList<HashMap<String,String>> getMoreData()
	{
		ArrayList<HashMap<String,String>> returnValue = new ArrayList<HashMap<String, String>>(  );
		String sql =
				"SELECT sf_tag, display_label, position_index " +
						" FROM ondevice_sections " +
						" WHERE parent_id = ( SELECT external_id FROM ondevice_sections WHERE sf_tag = 'more')" +
						" AND sf_tag IN ( " + getTagsToShow() + " ) " +
						" ORDER BY position_index ASC";
		String[][] results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			HashMap<String,String> map = new HashMap<String, String>(  );
			map.put( "sf_tag", AiSTr.denullifyString( results[i][0] ) );
			map.put( "display_label", AiSTr.denullifyString( results[i][1] ) );
			map.put( "position_index", AiSTr.denullifyString( results[i][2] ) );
			int position = AiSTr.denullifyInt( results[i][2] );
			sql =
					"SELECT meta_key, meta_value " +
							" FROM ondevice_section_meta " +
							" WHERE ondevice_section_data_id = " +
							" (SELECT id FROM ondevice_sections WHERE position_index = " + position + ") ";
			String[][] thumbnailDataRaw = this.mDb.query( sql );
			HashMap<String,String> thumbnailDataMap = new HashMap<String, String>(  );
			for( int j = 0; j < thumbnailDataRaw.length; j++ )
			{
				thumbnailDataMap.put( AiSTr.denullifyString( thumbnailDataRaw[j][0] ),AiSTr.denullifyString( thumbnailDataRaw[j][1] ) );
			}
			map.put( "thumbnail", thumbnailDataMap.get( "thumbnail" ) );
			map.put( "thumbnailorigin", thumbnailDataMap.get( "thumbnailorigin" ) );
			returnValue.add( map );
		}
		return returnValue;
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public Integer[] getPostSectionContentIDByPosition( int position )
	{
		Integer[] returnValue;
		String sql =
				"SELECT id " +
						" FROM section_content " +
						" WHERE id " +
						" IN " +
						"	(SELECT post_section_content_id " +
						" 	FROM post_to_section " +
						" 	WHERE ondevice_section_id = " +
						" 		(SELECT id FROM ondevice_sections WHERE position_index = " + position + "))" +
						" ORDER BY position_index DESC";
		String[][] results = this.mDb.query( sql );
		returnValue = new Integer[results.length];
		for(int i = 0; i < results.length; i++)
		{
			returnValue[i] = AiSTr.denullifyInt( results[i][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public Integer[] getPlaylistSectionContentIDByPosition( int position )
	{
		Integer[] returnValue;
		String/* sql =
				"SELECT playlist_section_content_id " +
						" FROM playlist_to_section " +
						" WHERE ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections WHERE position_index = " + position + ")";
		String[][] results = this.mDb.query( sql );*/

		sql =
				"SELECT id " +
						" FROM section_content " +
						" WHERE id " +
						" IN " +
						"	(SELECT playlist_section_content_id " +
						" 	FROM playlist_to_section " +
						" 	WHERE ondevice_section_id = " +
						" 		(SELECT id FROM ondevice_sections WHERE position_index = " + position + "))" +
						" ORDER BY position_index DESC";
		String[][] results = this.mDb.query( sql );
		returnValue = new Integer[results.length];
		for(int i = 0; i < results.length; i++)
		{
			returnValue[i] = AiSTr.denullifyInt( results[i][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @return
	 */
	public Integer[] getSermonSectionContentIDByPosition()
	{
		Integer[] returnValue;
		String sql =
				"SELECT id " +
						" FROM section_content " +
						" WHERE id " +
						" IN " +
						"	(SELECT sermon_section_content_id " +
						" 	FROM sermon_to_playlist " +
						" 	WHERE playlist_section_content_id = " + mApplication.getSfCurrentPlaylist().mId + ")" +
						" ORDER BY position_index DESC";
		String[][] results = this.mDb.query( sql );
		returnValue = new Integer[results.length];
		for(int i = 0; i < results.length; i++)
		{
			returnValue[i] = AiSTr.denullifyInt( results[i][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public String getLinkURL(int position)
	{
		String sql =
				"SELECT content " +
						" FROM section_content " +
						" WHERE ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections WHERE position_index = " + position + ")";
		String[][] results = this.mDb.query( sql );
		if(results.length != 0)
		{
			return AiSTr.denullifyString( results[0][0] );
		}
		return null;
	}

	/**
	 *
	 * @param sectionId
	 * @return
	 */
	public HashMap<String,String> getSectionMetaById( int sectionId )
	{
		HashMap<String,String> returnValue = new HashMap<String,String>();
		String sql =	"SELECT    sf_tag, display_label, position_index, last_update, id " +
						"FROM      ondevice_sections " +
						"WHERE     id = " + sectionId;
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue.put( "sf_tag", AiSTr.denullifyString( result[i][0] ) );
			returnValue.put( "display_label", AiSTr.denullifyString( result[i][1] ) );
			returnValue.put( "position_index", Integer.toString( AiSTr.denullifyInt( result[i][2] ) ) );
			returnValue.put( "last_update", Integer.toString( AiSTr.denullifyInt( result[i][3] ) ) );
			returnValue.put( "id", Integer.toString( AiSTr.denullifyInt( result[i][4] ) ) );
		}

		return returnValue;
	}

	/**
	 *
	 * @param input
	 * @param sectionContentId
	 */
	public void saveOrUpdateSectionContentMeta( HashMap<String,String> input, int sectionContentId )
	{
		for( HashMap.Entry<String,String> map : input.entrySet() )
		{
			int count = 0;
			String sql =
					"SELECT		COUNT(*) as count " +
					"FROM		section_content_meta " +
					"WHERE		section_content_id = " + sectionContentId + " AND " +
					"			meta_key = '" + map.getKey() + "' ";
			String[][] result = this.mDb.query( sql );
			for( int i = 0; i < result.length; i++ )
			{
				count = AiSTr.denullifyInt( result[ i ][ 0 ] );
			}

			if( count > 0 )
			{
				sql =
					"UPDATE		section_content_meta " +
					"SET		meta_value = '" + AiSTr.dbEscape( map.getValue() ) + "' " +
					"WHERE		section_content_id = " + sectionContentId + " AND " +
					"			meta_key = '" + map.getKey() + "' ";
			}
			else
			{
				sql =
					"INSERT INTO	section_content_meta " +
					"				(section_content_id, meta_key, meta_value) " +
					" VALUES		( " + sectionContentId + ", '" + map.getKey() + "', '" + AiSTr.dbEscape( map.getValue() ) + "' )";
			}
			this.mDb.noReturnQuery( sql );
		}
	}

	/**
	 *
	 * @return
	 */
	public String[] getChaptersToPurge()
	{
		String[] returnValue;
		Long cutoffTime = SFUtil.currentTimestamp() - 600000;
		String sql =
				"SELECT path " +
						" FROM bible_access_history " +
						" WHERE access_time < " + cutoffTime;
		String[][] results = this.mDb.query( sql );
		returnValue = new String[results.length];
		for( int i = 0; i < results.length; i++)
		{
			returnValue[i] = AiSTr.denullifyString( results[i][0] );
		}
		sql =
				"DELETE " +
						" FROM bible_access_history " +
						" WHERE access_time < " + cutoffTime;
		this.mDb.query( sql );
		return returnValue;
	}

	/**
	 *
	 * @param abbreviation
	 */
	public void setDataForVersionDownload( String abbreviation )
	{
		String sql =
				" DELETE " +
						" FROM bible_access_history " +
						" WHERE path LIKE '" + abbreviation + "/%'";
		this.mDb.noReturnQuery( sql );

		sql =
				"UPDATE bible_versions " +
						" SET is_downloaded = 1 " +
						" WHERE abbreviation = '" + abbreviation + "'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @param abbreviation
	 */
	public void setDataForVersionDelete( String abbreviation )
	{
		String sql =
				"UPDATE bible_versions " +
						" SET is_downloaded = 0 " +
						" WHERE abbreviation = '" + abbreviation + "'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @param input
	 */
	public void saveThemeFromHashMap( HashMap<String,String> input )
	{
		for( HashMap.Entry<String,String> map : input.entrySet() )
		{
			if( map.getKey().equals( "theme" ) && Arrays.asList( this.validThemes() ).contains( map.getValue() ) )
			{
				String sql =
					"UPDATE		config_lookup " +
					"SET		lookup_value = '" + map.getValue() + "' " +
					"WHERE		lookup_key = 'theme' ";
				this.mDb.noReturnQuery( sql );
			}
			else if( map.getKey().equals( "navcolor" ) && map.getValue().length() > 5 )
			{
				String sql =
					"UPDATE		config_lookup " +
					"SET		lookup_value = '" + map.getValue() + "' " +
					"WHERE		lookup_key = 'navcolor' ";
				this.mDb.noReturnQuery( sql );
			}
		}
	}

	/**
	 *
	 * @return
	 */
	public String getFontSize()
	{
		String sql =
				" SELECT lookup_value " +
						" FROM config_lookup " +
						" WHERE lookup_key = 'fontsize' ";
		String[][] results = this.mDb.query( sql );
		if(results.length < 1)
		{
			sql =
					" INSERT INTO config_lookup " +
							" (lookup_key,lookup_value) " +
							" VALUES ('fontsize', 'size_medium') ";
			this.mDb.noReturnQuery( sql );
			return "size_medium";
		}
		else
		{
			return AiSTr.denullifyString( results[0][0] );
		}
	}

	/**
	 *
	 * @param fontSize
	 */
	public void setFontSize( String fontSize )
	{
		String sql =
				" Update config_lookup " +
						" SET lookup_value = '" + fontSize + "' " +
						" WHERE lookup_key = 'fontsize' ";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @return
	 */
	public Integer[] getAppObjectIds()
	{
		String sql =	"SELECT	DISTINCT	meta_value " +
				"FROM       section_content_meta " +
				"WHERE      meta_key = 'externalid'";
		String[][] result = this.mDb.query( sql );
		Integer[] returnValue = new Integer[this.mDb.mQueryCount];
		for( int i = 0; i < result.length; i++ )
		{
			int currentId =  Integer.valueOf( AiSTr.denullifyInt( result[ i ][ 0 ] ) );

			returnValue[i] = currentId;
		}

		return returnValue;
	}

	/**
	 *
	 * @param input
	 */
	public void saveSectionsFromHashMap( HashMap<String,String> input )
	{
		//Log.d("mTag","Hit saveSectionsFromHashMap");
		this.setupOnDeviceSections( input );


		String appTitle = input.get( "apptitle" );
		try
		{
			appTitle = Html.fromHtml( URLDecoder.decode( appTitle, "UTF-8" ) ).toString();
		}
		catch( Exception e )
		{

		}

		String sql =
				"UPDATE		config_lookup " +
				"SET		lookup_value = '" + AiSTr.dbEscape( appTitle ) + "' " +
				"WHERE		lookup_key = 'apptitle'";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 */
	public void checkPlaylists()
	{
		String[] sectionIds;
		String[] playlistOnSectionId;
		String sql = "SELECT id FROM ondevice_sections ";
		String[][] results = this.mDb.query( sql );
		sectionIds = new String[results.length];
		for( int i = 0; i < results.length; i++ )
		{
			sectionIds[i] = AiSTr.denullifyString( results[i][0] );
		}

		sql = "SELECT DISTINCT ondevice_section_id FROM playlist_to_section";
		results = this.mDb.query( sql );
		playlistOnSectionId = new String[results.length];
		for( int i = 0; i < results.length; i++ )
		{
			playlistOnSectionId[i] = AiSTr.denullifyString( results[i][0] );
		}

		for( int i = 0; i < playlistOnSectionId.length; i++ )
		{
			boolean  isValid = false;
			for( int j = 0; j < sectionIds.length; j++ )
			{
				if(playlistOnSectionId[i].equals( sectionIds[j] ))
				{
					isValid = true;
					break;
				}
			}
			if(!isValid)
			{
				sql =
						"DELETE FROM playlist_to_section " +
								" WHERE ondevice_section_id = " + playlistOnSectionId[i];
				this.mDb.noReturnQuery( sql );
			}
		}

	}

	/**
	 *
	 * @param input
	 */
	public void saveLinkData( HashMap<String,String> input )
	{
		String externalId = input.get( "primaryId" );
		int id;

		String sql =
				"SELECT id " +
						" FROM ondevice_sections " +
						" WHERE external_id = " + externalId;
		String[][] results = this.mDb.query( sql );
		if(results.length > 0)
		{
			id = AiSTr.denullifyInt( results[0][0] );

			sql =
					"DELETE FROM section_content " +
							" WHERE ondevice_section_id = " + id;
			this.mDb.noReturnQuery( sql );
			sql =
					"INSERT INTO section_content " +
							" ( ondevice_section_id, content ) " +
							" VALUES( " + id + ", '" + input.get( "donateurl" ) + "')";
			this.mDb.noReturnQuery( sql );
		}

	}

	/**
	 *
	 * @param input
	 * @param keys
	 */
	public void saveMetaData( HashMap<String, String> input, String[] keys )
	{
		String includedIn = input.get( "includedin" );

		String sql =
				"SELECT COUNT(*) " +
						" FROM section_content " +
						" WHERE ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections " +
						"	WHERE external_id = " + includedIn + ")";
		String[][] results = this.mDb.query( sql );
		if( results.length > 0 )
		{
			int count = AiSTr.denullifyInt( results[0][0] );
			sql=
					"SELECT id FROM ondevice_sections " +
							" WHERE external_id = " + includedIn;
			results = this.mDb.query( sql );
			if( results.length > 0 )
			{
				if (results[0][0] != null)
				{
					int ondeviceSectionId = AiSTr.denullifyInt( results[0][0] );
					if ( count != 1 )
					{

						if ( count > 1 )
						{
							sql =
									"SELECT id FROM section_content " +
											" WHERE ondevice_section_id = "+ ondeviceSectionId;
							results = this.mDb.query( sql );
							for( int i = 0; i < results.length; i++ )
							{
								sql =
										"DELETE FROM section_content_meta " +
												" WHERE section_content_id = " + results[i][0];
								this.mDb.noReturnQuery( sql );

								sql =
										"DELETE FROM section_content " +
												" WHERE id = " + results[i][0];
								this.mDb.noReturnQuery( sql );
							}
						}


						sql =
								"INSERT INTO section_content " +
										" ( ondevice_section_id ) " +
										" VALUES( " + ondeviceSectionId + " ) ";
						this.mDb.noReturnQuery( sql );
						for (int i = 0; i < keys.length; i++)
						{
							sql =
									"INSERT INTO section_content_meta " +
											" (section_content_id, meta_key, meta_value) " +
											" VALUES ( " +
											"	(SELECT id FROM section_content WHERE ondevice_section_id = " + ondeviceSectionId + "), " +
											"'" + keys[i] + "', '" + AiSTr.dbEscape( input.get( keys[i] ) ) + "')";
							this.mDb.noReturnQuery( sql );
						}
					}
					else
					{

						for (int i = 0; i < keys.length; i++)
						{
							sql =
									"UPDATE section_content_meta " +
											" SET  meta_value = '" +AiSTr.dbEscape( input.get( keys[i] ) ) + "' " +
											" WHERE meta_key = '" + keys[i] + "' AND section_content_id = " +
											" 	(SELECT id FROM section_content WHERE ondevice_section_id = " + ondeviceSectionId + ")";
							this.mDb.noReturnQuery( sql );
						}
					}
				}
			}
		}
	}

	/**
	 *
	 * @param position
	 * @param keys
	 * @return
	 */
	public HashMap<String, String> getMetaData(int position, String[] keys )
	{
		//Log.d("mTag"," Hit getLocationData ***********");
		//String keys[] = {"address","city","state"};
		HashMap<String,String> returnHashMap = new HashMap<String, String>();
		String sql = "";
		int sectionId = -1;

		sql =
				"SELECT id FROM section_content " +
						"WHERE ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections " +
						" WHERE position_index = " + position + ")";
		String results[][] = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			sectionId =  AiSTr.denullifyInt( results[ i ][ 0 ] );
			//Log.d( "mTag","sectionId = " + sectionId );
		}

		if(sectionId == -1)
		{
			return null;
		}
		for( int i = 0; i < keys.length; i++ )
		{
			//Log.d( "mTag","key = " + keys[i] );
			sql =
					"SELECT meta_value FROM section_content_meta " +
							" WHERE section_content_id = " + sectionId + " AND meta_key = '" + keys[i] + "'";
			results = this.mDb.query( sql );
			if( results.length < 1 )
			{
				return null;
			}
			returnHashMap.put( keys[i], AiSTr.denullifyString( results[0][0] ) );
			//Log.d("mTag", keys[i] + " = " + results[0][0] );
		}
		return returnHashMap;
	}


	/**
	 *
	 * @param input
	 */
	private void setupOnDeviceSections( HashMap<String, String> input )
	{
		//Log.d("mTag","Hit setupOnDeviceSections");
		Integer[] currentSectionIds = this.currentSectionIds();
		Integer[] incomingSectionIds = new Integer[input.size()];

		String sql = "";
		String[][] results;
		Integer externalId = 0;


		int itemCount = 0;
		for( HashMap.Entry<String,String> map : input.entrySet() )
		{
			String[] split = map.getKey().split( "_" );
			if( split.length == 2 && split[0].equals( "tag" ) )
			{
				itemCount++;
			}
		}

		for( Integer i = 0; i < itemCount; i++ )
		{
			if(input.get( "tag_" + i ) != null )
			{
				HashMap<String, String> saveInput = new HashMap<String, String>();
				saveInput.put( "sf_tag", input.get( "tag_" + i ) );
				saveInput.put( "display_label", input.get( "title_" + i ) );
				//Log.d("mTag","display_label = " +saveInput.get("display_label"));
				saveInput.put( "position_index", input.get( "priority_" + i ) );
				//Log.d("mTag","position = " +saveInput.get("position_index"));
				saveInput.put( "external_id", input.get( "externalid_" + i ) );
				saveInput.put( "titles_enabled", input.get( "titlesenabled_" + i ) );
				saveInput.put( "layout", input.get( "layout_" + i ) );
				saveInput.put( "parent_id", input.get( "parentid_" + i ) );
				saveInput.put( "image", input.get( "image_" + i ) );
				saveInput.put( "ui_location", "1" );
				saveInput.put( "last_update", input.get( "lastsync" ) );

				externalId = Integer.valueOf( input.get( "externalid_" + i ) );
				incomingSectionIds[i] = externalId;
				try
				{
					String saveTitle = URLDecoder.decode( input.get( "title_" + i ), "UTF-8" );
					saveInput.put( "display_label", saveTitle );
				} catch (Exception e)
				{

				}
				saveInput.put( "ui_location", "1" );
				saveInput.put( "last_update", input.get( "lastsync" ) );

				if (this.externalSectionIdExists( externalId ))
				{
					this.updateSection( saveInput );
				} else
				{
					this.addSection( saveInput );
				}
			}
			else {
				itemCount++;
			}
		}

		// TODO: Remove local items that are not in the remote
		for( Integer localId : currentSectionIds )
		{
			//Log.d("mTag","remove " + localId );
			if( !Arrays.asList( incomingSectionIds ).contains( localId ) )
			{
				//Log.d("mTag","remove " + localId );
				// REMOVE THE SECTION
				sql =
						" DELETE FROM ondevice_sections " +
								" WHERE external_id = " + localId;
				this.mDb.noReturnQuery( sql );
			}
		}

		this.fixNonZeroIndex();

		SFBackgroundSync sync = new SFBackgroundSync( this.mApplication );
		sync.addHash( 0, "section" );
	}

	/**
	 *
	 * @param input
	 */
	public void addSection( HashMap<String,String> input )
	{
		String displayLabel = input.get( "display_label" );
		try
		{
			displayLabel = URLDecoder.decode( displayLabel, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		String sql =
				"INSERT INTO	ondevice_sections " +
				"				(id, sf_tag, display_label, " +
				"				ui_location, position_index, last_update, " +
				"				external_id, titles_enabled, layout, " +
				"				parent_id ) " +

				"				VALUES " +
				"				( NULL, '" + input.get( "sf_tag" ) + "', '" + AiSTr.dbEscape( displayLabel ) + "', " +
				"				1, " + Integer.valueOf( input.get( "position_index" ) ) + ", " + Integer.valueOf( input.get( "last_update" ) ) + ", " +
				"				" + Integer.valueOf( input.get( "external_id" ) ) + ", " + Integer.valueOf( input.get( "titles_enabled" ) ) + ", " + Integer.valueOf( input.get( "layout" ) ) + ", " +
				"				" + Integer.valueOf( input.get( "parent_id" ) ) + ")";
		this.mDb.query( sql );
		Integer sectionId = this.mDb.getLastIdFromTable( "ondevice_sections" );
		if( sectionId < 1 )
		{
			return;
		}

		String imageUrl = input.get( "image" );
		try
		{
			imageUrl = URLDecoder.decode( imageUrl, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		this.replaceSectionImage( sectionId, imageUrl );
	}

	/**
	 *
	 * @param input
	 */
	public void updateSection( HashMap<String,String> input )
	{
		// UPDATE DB
		Integer sectionId = 0;
		String sql = "SELECT id FROM ondevice_sections WHERE external_id = " + Integer.valueOf( input.get( "external_id" ) );
		String result[][] = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			sectionId =  AiSTr.denullifyInt( result[ i ][ 0 ] );
		}
		if( sectionId < 1 )
		{
			return;
		}

		String displayLabel = input.get( "display_label" );
		try
		{
			displayLabel = URLDecoder.decode( displayLabel, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		sql =
				"UPDATE		ondevice_sections " +
				"SET		sf_tag = '" + input.get( "sf_tag" ) + "', display_label = '" + AiSTr.dbEscape( displayLabel ) + "', ui_location = 1, " +
				"			position_index = " + Integer.valueOf( input.get( "position_index" ) ) + ", last_update = " + Integer.valueOf( input.get( "last_update" ) ) + ", external_id = " + Integer.valueOf( input.get( "external_id" ) ) + ", " +
				"			titles_enabled = " + Integer.valueOf( input.get( "titles_enabled" ) ) + ", layout = " + Integer.valueOf( input.get( "layout" ) ) + ", parent_id = " + Integer.valueOf( input.get( "parent_id" ) ) + " " +
				"WHERE		id = " + sectionId;
		this.mDb.noReturnQuery( sql );

		// Replace the section image
		String imageUrl = "";
		sql = "SELECT meta_value FROM ondevice_section_meta WHERE ondevice_section_data_id = " + sectionId + " AND meta_key = 'thumbnailorigin'";
		result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			imageUrl =  AiSTr.denullifyString( result[ i ][ 0 ] );
		}

		String newImageUrl = input.get( "image" );
		try
		{
			imageUrl = URLDecoder.decode( imageUrl, "UTF-8" );
			newImageUrl = URLDecoder.decode( newImageUrl, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		if( !imageUrl.equals( newImageUrl ) )
		{
			this.replaceSectionImage( sectionId, newImageUrl );
		}
	}

	/**
	 *
	 * @param sectionId
	 * @param url
	 */
	public void replaceSectionImage( Integer sectionId, String url )
	{
		// DELETE
		String sql = "DELETE FROM ondevice_section_meta WHERE ondevice_section_data_id = " + sectionId;
		this.mDb.noReturnQuery( sql );

		try
		{
			url = URLDecoder.decode( url, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		sql = "INSERT INTO ondevice_section_meta (ondevice_section_data_id, meta_key, meta_value) VALUES(" + sectionId + ", 'thumbnail', '')";
		this.mDb.noReturnQuery( sql );
		sql = "INSERT INTO ondevice_section_meta (ondevice_section_data_id, meta_key, meta_value) VALUES(" + sectionId + ", 'thumbnailorigin', '" + url + "')";
		this.mDb.noReturnQuery( sql );

		// GET
		if( url.length() > 0 )
		{
			SFCustomDownload dl = new SFCustomDownload();
			String savedFile = "";
			try
			{
				savedFile = dl.downloadFile( url );
			} catch (Exception e)
			{
				// If we got an exception, we just record empty file
				Log.e( "PROBLEM", "Something happened trying to get this: " + url );
			}

			// SAVE
			sql = "UPDATE ondevice_section_meta SET meta_value = '" + savedFile + "' WHERE ondevice_section_data_id = " + sectionId + " AND  meta_key = 'thumbnail'";
			this.mDb.noReturnQuery( sql );
		}

	}

	/**
	 *
	 * @return
	 */
	public Integer[] currentSectionIds()
	{
		String sql =
				"SELECT external_id FROM ondevice_sections";
		String[][] result = this.mDb.query( sql );
		Integer[] returnValue = new Integer[this.mDb.mQueryCount];
		for( int i = 0; i < result.length; i++ )
		{
			int currentId =  Integer.valueOf( AiSTr.denullifyInt( result[ i ][ 0 ] ) );

			returnValue[i] = currentId;
		}

		return returnValue;
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public boolean externalSectionIdExists( Integer externalId )
	{
		boolean returnValue = false;

		String sql =
				"SELECT COUNT(*) AS count FROM ondevice_sections WHERE external_id = " + externalId;
		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			int count =  Integer.valueOf( AiSTr.denullifyInt( result[ i ][ 0 ] ) );
			if( count > 0 )
			{
				returnValue = true;
			}
		}

		return returnValue;
	}

	/**
	 *
	 */
	public void deleteAllDatabaseData()
	{

		String sql=
				"DELETE FROM config_lookup";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM ondevice_section_meta";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM ondevice_sections";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM section_content";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM section_content_meta";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM sync_objects";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM playlist_to_section";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM post_to_section";
		this.mDb.noReturnQuery( sql );

		sql=
				"DELETE FROM sermon_to_playlist";
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @return
	 */
	public String[] validThemes()
	{
		String returnValue[] = { "inspire" };

		return returnValue;
	}

	/**
	 *
	 */
	public static void saveSyncTime()
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql =
				"UPDATE     config_lookup " +
				"SET      	lookup_value = '" + String.valueOf( SFUtil.currentTimestamp() ) + "' " +
				"WHERE      lookup_key = 'last_sync'";
		db.noReturnQuery( sql );
	}

	/**
	 *
	 * @param input
	 */
	public static void saveSyncTime( int input )
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql =
				"UPDATE     config_lookup " +
						"SET      	lookup_value = '" + String.valueOf( input ) + "' " +
						"WHERE      lookup_key = 'last_sync'";
		db.noReturnQuery( sql );
	}

	/**
	 *
	 * @param from
	 * @param to
	 */
	public static void changeExternalId( String from, String to )
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql = "UPDATE section_content_meta SET meta_value = '" + to + "' WHERE meta_key = 'externalid' AND meta_value = '" + from + "'";
		db.noReturnQuery( sql );
	}

	/**
	 *
	 */
	public static void recordDemoDataRearranged()
	{
		AiDb db = SFApplication.getInstance().getDb();

		int count = 0;
		String sql = "SELECT COUNT(*) as count FROM config_lookup WHERE lookup_key = 'demoidsrearranged'";
		String[][] result = db.query( sql );
		Integer[] returnValue = new Integer[db.mQueryCount];
		for( int i = 0; i < result.length; i++ )
		{
			count =  Integer.valueOf( AiSTr.denullifyInt( result[ i ][ 0 ] ) );
		}

		if( count < 1 )
		{
			sql =  "INSERT INTO config_lookup ( lookup_key, lookup_value ) VALUES	( 'demoidsrearranged', '1' )";
			db.noReturnQuery( sql );
		}
	}

	// TODO: 1/2/17 optimize and condense hash methods

	/**
	 *
	 * @param primaryId
	 * @return
	 */
	public String getLinkStringToHash( int primaryId )
	{
		String returnValue = "";
		String sql =
				"SELECT     content " +
						" FROM       section_content " +
						" WHERE      ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections WHERE external_id = " + primaryId + ")";
		String[][] result = this.mDb.query( sql );
		if(result.length>0)
		{
			returnValue = AiSTr.denullifyString( result[0][0] ) + primaryId;
		}

		return returnValue;
	}

	/**
	 *
	 * @param primaryId
	 * @return
	 */
	public String getConnectStringToHash( int primaryId )
	{
		String returnValue = "";
		String sql =
				"SELECT     meta_key, meta_value " +
						" FROM       section_content_meta " +
						" WHERE      section_content_id = " +
						" (SELECT id FROM section_content WHERE ondevice_section_id = " +
						" 	(SELECT id FROM ondevice_sections WHERE external_id = " + primaryId + "))";
		String[][] result = this.mDb.query( sql );
		if(result.length>0)
		{
			HashMap<String,String> map = new HashMap<String, String>();
			for( int i = 0; i < result.length; i++ )
			{
				map.put( AiSTr.denullifyString( result[i][0] ),AiSTr.denullifyString( result[i][1] ) );
			}
			returnValue = map.get( "email" ) + map.get( "phone" ) + map.get( "facebook" ) + map.get( "twitter" ) +
					map.get( "content" ) + primaryId;
		}

		return returnValue;
	}

	/**
	 *
	 * @param primaryId
	 * @return
	 */
	public String getLocationStringToHash( int primaryId )
	{
		String returnValue = "";
		String sql =
				"SELECT     meta_key, meta_value " +
						" FROM       section_content_meta " +
						" WHERE      section_content_id = " +
						" (SELECT id FROM section_content WHERE ondevice_section_id = " +
						" 	(SELECT id FROM ondevice_sections WHERE external_id = " + primaryId + "))";
		String[][] result = this.mDb.query( sql );
		if(result.length>0)
		{
			HashMap<String,String> map = new HashMap<String, String>();
			for( int i = 0; i < result.length; i++ )
			{
				map.put( AiSTr.denullifyString( result[i][0] ),AiSTr.denullifyString( result[i][1] ) );
			}
			returnValue = map.get( "address" ) + map.get( "city" ) + map.get( "state" ) + map.get( "zip" ) + primaryId;
		}

		return returnValue;
	}

	/**
	 *
	 * @param primaryId
	 * @return
	 */
	public String getStreamStringToHash( int primaryId )
	{
		String returnValue = "";
		String sql =
				"SELECT     meta_key, meta_value " +
						" FROM       section_content_meta " +
						" WHERE      section_content_id = " +
						" (SELECT id FROM section_content WHERE ondevice_section_id = " +
						" 	(SELECT id FROM ondevice_sections WHERE external_id = " + primaryId + "))";
		String[][] result = this.mDb.query( sql );
		if(result.length>0)
		{
			HashMap<String,String> map = new HashMap<String, String>();
			for( int i = 0; i < result.length; i++ )
			{
				map.put( AiSTr.denullifyString( result[i][0] ),AiSTr.denullifyString( result[i][1] ) );
			}
			String[] keys = {"streamid", "includedIn", "type", "content", "url"};
			for( int i = 0; i < keys.length; i++ )
			{
				returnValue += map.get( keys[i] );
			}
		}

		return returnValue;
	}

	/**
	 *
	 * @param syncObjectId
	 * @param objectType
	 * @return
	 */
	// objectType comes from the incoming/remote hash database
	public String getStringToHash( int syncObjectId, String objectType )
	{
		String rawReturnValue = "";
		String returnValue = "";
		String sql = "";
		String[][] result;

		if(objectType.equals( "theme" ))
		{
			sql =
					"SELECT     lookup_value " +
							"FROM       config_lookup " +
							"WHERE      lookup_key = 'navcolor' "
			;
			//Log.d("mTag","Theme query = " + sql );
			result = this.mDb.query( sql );
			//Log.d("mTag","Theme string to hash = " + result[0][0] );

			return AiSTr.denullifyString( result[0][0].trim() );
		}
		if( objectType.equals( "section" ))
		{
			//Log.d("mTag","Hit get section string to hash *********************************");
			sql =
					"SELECT     id, position_index, display_label, layout, parent_id, external_id, sf_tag, titles_enabled " +
					"FROM       ondevice_sections " +
					"			ORDER BY position_index"
			;
			result = this.mDb.query( sql );
			for( int i = 0; i < result.length; i++ )
			{
				String imageSource = "";

				int sectionId = AiSTr.denullifyInt( result[i][0] );
				int positionIndex = AiSTr.denullifyInt( result[i][1] );
				String displayLabel = AiSTr.denullifyString( result[i][2] );
				int layout = AiSTr.denullifyInt( result[i][3] );
				int parentId = AiSTr.denullifyInt( result[i][4] );
				int externalId = AiSTr.denullifyInt( result[i][5] );
				String sfTag = AiSTr.denullifyString( result[i][6] );
				int titlesEnabled = AiSTr.denullifyInt( result[i][7] );

				sql =
						"SELECT 	meta_value " +
						"FROM 		ondevice_section_meta " +
						"WHERE 		meta_key = 'thumbnailorigin' AND ondevice_section_data_id = " + sectionId;
				String[][] imgResult = this.mDb.query( sql );
				for( int j = 0; j < result.length; j++ )
				{
					imageSource = AiSTr.denullifyString( imgResult[0][0] );
				}

				returnValue += positionIndex + displayLabel + layout + parentId + externalId + sfTag + titlesEnabled + imageSource;
			}
			//Log.d("mTag","*********************************************** Sections String to hash = " + returnValue );

			return returnValue;
		}

		if (objectType.equals( "inbox" ))
		{
			// TODO: 4/1/16 string to hash for inbox
		}
		else
		{

			int sectionContentid = 0;
			String inClause = "";
			ArrayList<String> keys = this.getHashFieldMap( objectType );
			HashMap<String, String> values = new HashMap<String, String>();


			sql =
					"SELECT     section_content_id " +
							"FROM       section_content_meta " +
							"WHERE      meta_key = 'externalid' AND" +
							"	meta_value = '" + syncObjectId + "'";
			result = this.mDb.query( sql );
			for (int j = 0; j < result.length; j++)
			{
				sectionContentid = AiSTr.denullifyInt( result[j][0] );
			}
			// "title" is first key
			for (int i = 1; i < keys.size(); i++)
			{
				String key = keys.get( i );
				inClause += "'" + key + "'";
				if ((i + 1) != keys.size())
				{
					inClause += ", ";
				}
			}
			if (objectType.equals( "location" ) || objectType.equals( "connect" ))
			{
				if (objectType.equals( "location" ))
				{
					//Log.d( "mTag", "Hit get location string to hash *********************************" );
				}
				sql =
						"SELECT meta_value FROM section_content_meta " +
								" WHERE section_content_id = " + sectionContentid + " AND meta_key = '" + keys.get( 0 ) + "'";
				result = this.mDb.query( sql );
				for (int i = 0; i < result.length; i++)
				{
					rawReturnValue = AiSTr.denullifyString( result[i][0] );
					returnValue += rawReturnValue.trim();
				}
			} else
			{
				sql =
						"SELECT     content " +
								"FROM       section_content " +
								"WHERE      id = " + sectionContentid;
				result = this.mDb.query( sql );
				for (int i = 0; i < result.length; i++)
				{
					rawReturnValue = AiSTr.denullifyString( result[i][0] );
					returnValue += rawReturnValue.trim();
				}
			}

			sql = "SELECT     meta_key, meta_value " +
					"FROM       section_content_meta " +
					"WHERE      section_content_id = " + sectionContentid + " AND " +
					"			meta_key IN ( " + inClause + " ) ";
			result = this.mDb.query( sql );
			for (int i = 0; i < result.length; i++)
			{
				values.put( AiSTr.denullifyString( result[i][0] ), AiSTr.denullifyString( result[i][1] ) );
			}

			for (int i = 1; i < keys.size(); i++)
			{
				String key = keys.get( i );
				if (values.get( key ) != null)
				{
					rawReturnValue = values.get( key );
					returnValue += rawReturnValue.trim();
				}
			}
		}

		//SFMainActivity mainActivity = SFMainActivity.instance;
		//String s = "string to hash: " + returnValue;
		//mainActivity.longInfo( "mTag", s );
		//Log.d("mtag","string to hash = " + returnValue );

		return returnValue;
	}

	/**
	 *
	 * @param objectType
	 * @return
	 */
	public ArrayList<String> getHashFieldMap( String objectType )
	{
		ArrayList<String> returnValue = new ArrayList<String>();

		if( objectType.equals( "post" ) || objectType.equals( "newsletter" ) )
		{
			String[] values = new String[] { "title", "content", "postdate", "thumbnailorigin", "includedin" };
			returnValue.addAll( Arrays.asList( values ) );
		}
		else if( objectType.equals( "sermon" ) )
		{

			String[] values = new String[] { "title", "description", "sermondate", "seriestitle", "preacher", "audiourl", "notesurl", "videourl", "isdownloadable" };
			returnValue.addAll( Arrays.asList( values ) );
		}
		else if( objectType.equals( "playlist" ) )
		{

			String[] values = new String[] { "title", "preacher", "thumbnailorigin", "includedIn" };
			returnValue.addAll( Arrays.asList( values ) );
		}
		else if( objectType.equals( "connect" ) )
		{
			String[] values = new String[] { "email", "phone", "facebook", "twitter", "content","includedin" };
			returnValue.addAll( Arrays.asList( values ) );
		}
		else if( objectType.equals( "location" ) )
		{
			String[] values = new String[] { "address", "city", "state", "zip"};
			returnValue.addAll( Arrays.asList( values ) );
		}
		else if( objectType.equals( "donate" ) )
		{
			String[] values = new String[]{ "donateurl","includedin" };
			returnValue.addAll( Arrays.asList( values ) );
		}

		// theme, sections

		return returnValue;
	}

	/**
	 *
	 * @param externalId
	 */
	public void hashImage( int externalId )
	{
		//Log.d("mTag","HIT hashImage");
		SFUtil util = new SFUtil();
		String imageHash = getPathOfImageToHash( externalId );
		//Log.d("mTag","file name of image to hash = " +imageHash);
		if( imageHash.equals( "" ))
		{
			//Log.d( "mtag","Image hash empty" );
			return;
		}
		try
		{
			imageHash = util.md5HashOfPath( SFApplication.getInstance().getFilesDir() + "/appfiles/custom/" + imageHash );
			addHashData( externalId,imageHash, "image");
			//Log.d( "mtag","hashed image for " + externalId + ": " + imageHash );
		} catch (Exception e)
		{
			e.printStackTrace();
			Log.e("error","ERROR hashing image ");
		}
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public String getPathOfImageToHash( int externalId )
	{
		String sql =
				"SELECT	meta_value " +
						"FROM 	section_content_meta " +
						"WHERE	meta_key = 'thumbnail' AND " +
						"		section_content_id = " +
						"		(SELECT section_content_id " +
						" 		FROM 	section_content_meta " +
						"		WHERE	meta_value = " + externalId + ")"
				;
		String[][] rawReturnValue = this.mDb.query( sql );
		if( rawReturnValue.length <1 )
		{
			return "";
		}
		return AiSTr.denullifyString( rawReturnValue[0][0] );
		//return returnValue;
	}

	/**
	 *
	 * @param id
	 * @return
	 */
	public int getExternalIdById(int id)
	{
		int returnvalue=-1;
		String sql = "SELECT meta_value " +
				"FROM section_content_meta " +
				"WHERE meta_key = externalid " +
				"AND section_content_id = " + id + ""
				;
		return returnvalue;
	}

	/**
	 *
	 * @param id
	 * @return
	 */
	public String getTagById(int id)
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql = "SELECT sf_tag " +
				"FROM ondevice_sections " +
				"WHERE id = " +
				"(SELECT ondevice_section_id FROM section_content WHERE id = " + id + ")"
				;
		String[][] returnQuery = db.query( sql );
		sql = returnQuery[0][0];
		return sql;
	}

	/**
	 *
	 * @param allObjectsHashData
	 */
	public void firstTimeAddHashes(ArrayList allObjectsHashData)
	{
		String[] syncMetaData = new String[3];
		for( int i = 0; i < allObjectsHashData.size(); i++ )
		{
			syncMetaData = (String[]) allObjectsHashData.get( i );

			String id = syncMetaData[0];
			String hash = syncMetaData[1];
			String type = syncMetaData[2];

			if(!type.equals( "contact" )||!type.equals( "location" )||!type.equals( "donate" ))
			{

			}
			else
			{

			}
		}
	}

	/**
	 *
	 * @param id
	 * @param hash
	 * @param type
	 */
	public void addHashData(int id, String hash, String type)
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql;

		if( type.equals( "theme" ) || type.equals( "section" ) )
		{
			if( type.equals( "section" ) )
			{
				type = "sections";
			}
			if( SFApplication.getInstance().isHashTableEmpty )
			{
				sql = "INSERT INTO sync_objects " +
						"( sync_object_id, hash_value, object_type ) " +
						"VALUES ( " + 0 + ", '" + hash +"', '" + type + "')"
						;
			}
			else
			{
				sql =
						"UPDATE	sync_objects " +
								"SET hash_value = '" + hash + "' " +
								"WHERE object_type = '" + type + "'"
				;
			}
		}
		else
		{

			int count;
			sql =
					"SELECT	COUNT(*) AS count " +
							"FROM sync_objects " +
							"WHERE sync_object_id = " + id + " AND " +
							"object_type = '" + type + "'"
			;
			String[][] result = db.query( sql );
			count = AiSTr.denullifyInt( result[0][0] );

			//Log.d("mTag","count = " + count);

			if (count > 0)
			{
				sql =
						"UPDATE	sync_objects " +
								"SET hash_value = '" + hash + "' " +
								"WHERE sync_object_id = " + id + " AND " +
								"object_type = '" + type + "' "
				;
			} else
			{
				sql =
						"INSERT INTO sync_objects " +
								"(sync_object_id, hash_value, object_type) " +
								"VALUES	( " + id + ", '" + hash + "', '" + type + "' )";
			}
		}
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @param id
	 */
	public void removeHashData(int id)
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql=
				"DELETE " +
				"FROM sync_objects " +
				"WHERE sync_object_id = "+id
				;
		db.noReturnQuery( sql );
	}

	/**
	 *
	 * @param externalID
	 * @return
	 */
	public int getParentId( int externalID )
	{
		String sql ="SELECT parent_id " +
				"FROM section_content " +
				"WHERE id = " +
				"	( SELECT section_content_id " +
				"	FROM section_content_meta " +
				"	WHERE meta_key = 'externalid' " +
				"	AND meta_value = " + externalID +" )"
				;
		String[][] results = mDb.query( sql );
		return AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public int getLayout( int position )
	{
		String sql =
				"Select layout From ondevice_sections " +
						" WHERE position_index = " + position;
		String results[][] = mApplication.getDb().query( sql );
		return AiSTr.denullifyInt( results[0][0] );
	}

	/**
	 *
	 * @param position
	 * @return
	 */
	public boolean getIsTitleEnabled( int position )
	{
		boolean returnValue = true;

		String sql =
				"Select titles_enabled From ondevice_sections " +
						" WHERE position_index = " + position;
		String results[][] = mApplication.getDb().query( sql );
		if( results.length>0 )
		{
			returnValue = AiSTr.dbToBool( AiSTr.stringToInt( results[0][0] ) );
		}

		return returnValue;
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public String getPostContentByExternalId(String externalId)
	{
		//Log.d("mTag","external id = " + externalId);
		String sql =
				"Select meta_value " +
						" From section_content_meta " +
						" WHERE meta_key = 'content' " +
						" AND section_content_id IN " +
						"	(SELECT section_content_id " +
						"	FROM section_content_meta " +
						"	WHERE meta_key = 'externalid' " +
						"	AND meta_value = '" + externalId + "')";
		String results[][] = this.mDb.query( sql );
		if(results.length > 0)
		{
			if( AiSTr.denullifyString( results[0][0] ).length()>100)
			{
				return AiSTr.denullifyString( results[0][0] ).substring( 0, 100 );
			}
			else
			{
				return AiSTr.denullifyString( results[0][0] );
			}
		}
		return "";
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public String getSermonDescriptionByExternalId(String externalId)
	{
		//Log.d("mTag","external id = " + externalId);
		String sql =
				"Select meta_value " +
						" From section_content_meta " +
						" WHERE meta_key = 'description' " +
						" AND section_content_id IN " +
						"	(SELECT section_content_id " +
						"	FROM section_content_meta " +
						"	WHERE meta_key = 'externalid' " +
						"	AND meta_value = '" + externalId + "')";
		String results[][] = this.mDb.query( sql );
		if(results.length > 0)
		{
			if( AiSTr.denullifyString( results[0][0] ).length()>100)
			{
				return AiSTr.denullifyString( results[0][0] ).substring( 0, 100 );
			}
			else
			{
				return AiSTr.denullifyString( results[0][0] );
			}
		}
		return "";
	}

	/**
	 *
	 * @param sectionId
	 * @return
	 */
	public int getParentPositionForPostSectionId( int sectionId )
	{
		//Log.d( "mtag","section = " + sectionId );
		int returnValue = -1;
		String sql =
				"SELECT position_index " +
						" FROM ondevice_sections " +
						" WHERE id IN " +
						" 	(SELECT ondevice_section_id " +
						"	FROM post_to_section " +
						"	WHERE post_section_content_id = " + sectionId + ")";
		String[][] results = this.mDb.query( sql );
		if(results.length>0)
		{
			returnValue = AiSTr.denullifyInt( results[0][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param sectionId
	 * @return
	 */
	public int getParentPositionForPlaylistSectionId( int sectionId )
	{
		//Log.d( "mtag","section = " + sectionId );
		int returnValue = -1;
		String sql =
				"SELECT position_index " +
						" FROM ondevice_sections " +
						" WHERE id = " +
						" 	(SELECT ondevice_section_id " +
						"	FROM playlist_to_section " +
						"	WHERE playlist_section_content_id = " + sectionId + ")";
		String[][] results = this.mDb.query( sql );
		if(results.length>0)
		{
			returnValue = AiSTr.denullifyInt( results[0][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public int getPostSectionIdByExternalId( int externalId )
	{
		int returnValue = -1;
		String sql =
				"SELECT post_section_content_id " +
						" FROM post_to_section " +
						" WHERE post_section_content_id " +
						" IN " +
						" 	(SELECT section_content_id " +
						" 	FROM 	section_content_meta " +
						" 	WHERE 	meta_key = 'externalid' " +
						" 	AND		meta_value = '" + externalId + "')";
		String[][] results = this.mDb.query( sql );
		if(results.length>0)
		{
			returnValue = AiSTr.denullifyInt( results[0][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param externalId
	 * @return
	 */
	public int getPlaylistSectionIdBySermonExternalId( int externalId )
	{
		int returnValue = -1;
		String sql =
				"SELECT playlist_section_content_id " +
						" FROM sermon_to_playlist " +
						" WHERE sermon_section_content_id " +
						" IN " +
						" 	(SELECT section_content_id " +
						" 	FROM 	section_content_meta " +
						" 	WHERE 	meta_key = 'externalid' " +
						" 	AND		meta_value = '" + externalId + "')";
		String[][] results = this.mDb.query( sql );
		if(results.length>0)
		{
			returnValue = AiSTr.denullifyInt( results[0][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @return
	 */
	public int getInboxSectionId()
	{
		int returnValue = -1;
		String sql =
				"SELECT id " +
						" FROM ondevice_sections " +
						" WHERE sf_tag = 'inbox' ";
		String[][] results = this.mDb.query( sql );
		if( results.length > 0 )
		{
			returnValue = AiSTr.denullifyInt( results[0][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param playlistID
	 * @return
	 */
	public  boolean getTitlesEnabledByPlaylistId( int playlistID )
	{
		//Log.d( "mtag","playlist id = " + playlistID );
		boolean returnValue = true;
		String sql =
				"SELECT titles_enabled " +
						" FROM ondevice_sections " +
						" WHERE id " +
						" IN " +
						" 	(SELECT ondevice_section_id " +
						" 	FROM 	playlist_to_section " +
						" 	WHERE 	playlist_section_content_id = " + playlistID + " )";
		String[][] results = this.mDb.query( sql );
		if(results.length>0)
		{
			//Log.d( "mtag", results[0][0] );
			returnValue = AiSTr.dbToBool( AiSTr.stringToInt( results[0][0] ) );
		}
		return returnValue;
	}

	/**
	 *
	 * @param input
	 */
	public void saveInboxItems(ArrayList<HashMap<String,String>> input )
	{
		int inboxSectionId = getInboxSectionId();
		Integer[] itemsSectionIds = getNotificationSectionIds( inboxSectionId );
		for (int i = 0; i < input.size(); i++)
		{
			HashMap<String, String> data = input.get( i );

			//Log.d( "mtag", "notification hashmap keys = " + data.keySet().toString() );
			//Log.d( "mtag", "notification hashmap values = " + data.values().toString() );

			String externalid = AiSTr.denullifyString( data.get( "externalid" ) );
			String postdate = AiSTr.denullifyString( data.get( "postdate" ) );
			String payload = AiSTr.denullifyString( data.get( "payload" ) );
			String title = AiSTr.denullifyString( data.get( "title" ) );
			String image = AiSTr.denullifyString( data.get( "image" ) );
			String objecttype = AiSTr.denullifyString( data.get( "objecttype" ) );

			String[] keys = {"objecttype","payload","postdate","image","externalid"};
			String[] values = {objecttype,payload,postdate,image,externalid};

			String sql =
					"SELECT id " +
							" FROM section_content " +
							" WHERE position_index = " + postdate + " " +
							" AND	content = '" + title + "'";
			String[][] results = this.mDb.query( sql );
			if( results.length == 0 )
			{
				//add
				int sectionId = this.mDb.insertBlank( "section_content","id" );
				sql =
						"UPDATE section_content " +
								" SET 	ondevice_section_id = " + inboxSectionId + ", " +
								"		parent_id = 0, " +
								"		position_index = " + postdate + ", " +
								"		content = '" + title + "', " +
								"		last_update = " + SFUtil.currentTimestamp() + " " +
								" WHERE id = " + sectionId;
				this.mDb.noReturnQuery( sql );

				for(int j = 0; j < keys.length; j++ )
				{
					sql =
							"INSERT INTO section_content_meta " +
									" (section_content_id, meta_key, meta_value) " +
									" VALUES (" + sectionId +", '" + keys[j] + "', '" + values[j] + "')";
					this.mDb.noReturnQuery( sql );
				}
			}
		}

	}

	/**
	 *
	 * @param inboxSectionId
	 * @return
	 */
	public Integer[] getNotificationSectionIds( int inboxSectionId )
	{
		Integer[] returnValue = null;
		String sql =
				"SELECT id " +
						" FROM section_content " +
						" WHERE ondevice_section_id = " + inboxSectionId;
		String[][] results = this.mDb.query( sql );
		returnValue = new Integer[results.length];
		for( int i = 0; i < results.length; i++ )
		{
			returnValue[i] = AiSTr.denullifyInt( results[i][0] );
		}

		return returnValue;
	}

	/**
	 *
	 * @param input
	 */
	public void processInbox( ArrayList<HashMap<String, String>> input )
	{

		Integer ondeviceSectionId = 0;

		String sql = "SELECT id FROM ondevice_sections WHERE sf_tag = 'inbox'";
		String[][] results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			ondeviceSectionId = AiSTr.denullifyInt( results[i][0] );
		}

		sql = "SELECT id FROM section_content WHERE	ondevice_section_id = " + ondeviceSectionId;
		results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			Integer sectionContentId = AiSTr.denullifyInt( results[i][0] );
			sql = "DELETE FROM section_content_meta WHERE section_content_id = " + sectionContentId;
			this.mDb.noReturnQuery( sql );
			sql = "DELETE FROM section_content WHERE id = " + sectionContentId;
			this.mDb.noReturnQuery( sql );
		}

		ArrayList<String> queries = new ArrayList<String>(  );
		for( int i = 0; i < input.size(); i++ )
		{
			HashMap<String,String> object = input.get( i );

			sql =
					"INSERT INTO	section_content " +
					"				(id, ondevice_section_id, parent_id, position_index, content, last_update) " +
					"VALUES			(NULL, " + ondeviceSectionId + ", 0, " +
					"				" + Integer.valueOf( object.get( "postdate" ) ) + ", " +
					"				'" + AiSTr.dbEscape( object.get( "title" ) ) + "', " +
					"				" + Integer.valueOf( object.get( "postdate" ) ) + ")";
			this.mDb.noReturnQuery( sql );
			Integer newSectionContentId = this.mDb.getLastIdFromTable( "section_content" );

			queries.add( "INSERT INTO section_content_meta (section_content_id, meta_key, meta_value) VALUES (" + newSectionContentId + ", 'objecttype', '" + object.get( "objecttype" ) + "')" );
			queries.add( "INSERT INTO section_content_meta (section_content_id, meta_key, meta_value) VALUES (" + newSectionContentId + ", 'payload', '" + AiSTr.dbEscape( object.get( "payload" ) ) + "')" );
			queries.add( "INSERT INTO section_content_meta (section_content_id, meta_key, meta_value) VALUES (" + newSectionContentId + ", 'image', '" + object.get( "image" ) + "')" );
			queries.add( "INSERT INTO section_content_meta (section_content_id, meta_key, meta_value) VALUES (" + newSectionContentId + ", 'externalid', '" + AiSTr.dbEscape( object.get( "externalid" ) ) + "')" );
			queries.add( "INSERT INTO section_content_meta (section_content_id, meta_key, meta_value) VALUES (" + newSectionContentId + ", 'postdate', '" + AiSTr.dbEscape( object.get( "postdate" ) ) + "')" );
		}

		Iterator<String> iterator = queries.iterator();
		while( iterator.hasNext() )
		{
			sql = iterator.next();
			this.mDb.noReturnQuery( sql );
		}

		this.saveInboxhash();
	}

	/**
	 *
	 */
	public void saveInboxhash()
	{
		String stringToHash = "";
		String sql =
				"SELECT		sc.content as content, sc.position_index as position, sc.id as id " +
				"FROM		section_content sc, ondevice_sections ods " +
				"WHERE      ods.sf_tag = 'inbox' AND " +
				"			sc.ondevice_section_id = ods.id " +
				"ORDER BY	sc.position_index";
		String[][] results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			String content = AiSTr.denullifyString( results[i][0] );
			Integer postDate = AiSTr.denullifyInt( results[i][1] );
			Integer cotentId = AiSTr.denullifyInt( results[i][2] );
			String foo = "";

			HashMap<String,String> metaData = new HashMap<String, String>(  );
			String innerSql = "SELECT meta_key, meta_value FROM section_content_meta WHERE section_content_id = " + cotentId;
			String[][] innerResults = this.mDb.query( innerSql );
			for( int j = 0; j < innerResults.length; j++ )
			{
				String key = AiSTr.denullifyString( innerResults[j][0] );
				String value = AiSTr.denullifyString( innerResults[j][1] );
				metaData.put( key, value );
			}

			stringToHash += content + metaData.get( "payload" ) + postDate +  metaData.get( "image" ) +  metaData.get( "externalid" );
		}

		String hashValue = SFUtil.md5( stringToHash );

		sql = "UPDATE sync_objects SET hash_value = '" + hashValue + "' WHERE object_type = 'inbox'";
		this.mDb.noReturnQuery( sql );
	}


	// Bible data methods

	/**
	 *
	 * @return
	 */
	public String[] getBibleVersionsOnDevice()
	{
		String[] returnValue = null;
		String sql =
				"SELECT abbreviation " +
						" FROM bible_versions ";
		String[][] results = this.mDb.query( sql );
		returnValue = new String[results.length];
		for( int i = 0; i < results.length; i++ )
		{
			returnValue[i] = AiSTr.denullifyString( results[i][0] );
		}
		return returnValue;
	}

	/**
	 *
	 * @param version
	 */
	public void removeBibleVersion( String version )
	{
		//Log.d("mtag","hit remove bible version");
		String sql =
				"DELETE FROM bible_version_to_book " +
						" WHERE version_id = " +
						"	(SELECT id FROM bible_versions WHERE abbreviation = '" + version + "')";
		this.mDb.noReturnQuery( sql );
				sql =
				"DELETE FROM bible_versions " +
						" WHERE abbreviation = '" + version + "'";
		this.mDb.noReturnQuery( sql );
				sql =
						"DELETE FROM section_content_meta " +
								" WHERE meta_key = 'version' " +
								" AND meta_value = '" + version + "'";
		this.mDb.noReturnQuery( sql );

	}

	/**
	 *
	 * @param versionData
	 * @param version
	 */
	public void addBibleVersion( JSONObject versionData, String version )
	{
		//Log.d( "mtag","hit add bible version" );
		int versionId = this.mDb.insertBlank( "bible_versions", "id" );
		try
		{
			String full_name 	= String.valueOf( versionData.get("full_name") );
			full_name			= URLDecoder.decode( full_name,"UTF-8" );
			String copyright	= String.valueOf( versionData.get("copyright") );
			copyright			= URLDecoder.decode( copyright,"UTF-8" );
			String sync_version	= String.valueOf( versionData.get( "sync_version" ) );
			String language		= String.valueOf( versionData.get( "language" ) );
			String api_provider	= String.valueOf( versionData.get( "api_provider" ) );
			String api_key		= String.valueOf( versionData.get( "api_key" ) );
			String path			= String.valueOf( versionData.get( "path" ) );
			String access_time	= String.valueOf( versionData.get( "access_time" ) );

			String sql =
					" UPDATE bible_versions " +
							" SET abbreviation = '" + version + "', " +
							"	full_name = '" + full_name + "', " +
							"	copyright = '" + copyright + "', " +
							"	sync_version = '" + sync_version + "', " +
							"	language = '" + language + "', " +
							"	api_provider = '" + api_provider + "', " +
							"	api_key = '" + api_key + "', " +
							"	path = '" + path + "', " +
							"	is_downloaded = 0, " +
							"	access_time = " + access_time + " " +
							" WHERE id = " + versionId;
			//Log.d("mtag","update bible_version sql = " + sql );
			this.mDb.noReturnQuery( sql );
			JSONObject booksInfo = (JSONObject) versionData.get( "book_info" );
			//Log.d( "mtag","bookInfo: " +booksInfo.toString() );
			boolean haveBooks = bibleBooksPopulated();
			for( int i = 0; i < booksInfo.length(); i++ )
			{
				Iterator<?> keys = booksInfo.keys();
				//Log.d( "mtag","keys:  " + keys.toString() );

				while( keys.hasNext() ) {
					String key = (String)keys.next();
					//Log.d( "mtag","key = " + key );
					if ( booksInfo.get(key) instanceof JSONObject ) {
						JSONObject bookInfo = (JSONObject) booksInfo.get(key);
						addBibleBookForVersion( bookInfo,versionId );
						if(!haveBooks)
						{
							addBibleBooks( bookInfo,key );
						}
						//Log.d( "mTag", "bookInfo = " + bookInfo.toString() );
					}
				}
			}
			sql = " SELECT id FROM section_content " +
					" WHERE ondevice_section_id = " +
					" 	(SELECT id FROM ondevice_sections " +
					"		WHERE sf_tag = 'bible')";
			String[][] results = this.mDb.query( sql );
			if( results.length>0 )
			{
				int sectionContentId = AiSTr.denullifyInt( results[0][0] );
				sql = " INSERT INTO section_content_meta " +
						" (section_content_id, meta_key, meta_value) " +
						"VALUES (" + sectionContentId + ", 'version', '" + version + "')";
				this.mDb.noReturnQuery( sql );
			}


		} catch (Exception e)
		{
			e.printStackTrace();
			Log.e( "error", "Exception in add bible version" );
		}

	}

	/**
	 *
	 * @return
	 */
	public boolean bibleBooksPopulated()
	{
		String sql =
				"SELECT COUNT(*) as count FROM bible_books ";
		String[][] results = this.mDb.query( sql );
		Integer count = AiSTr.denullifyInt( results[0][0] );
		if( count < 1 )
		{
			//Log.d( "mTag", "DELETING BIBLE BOOKS IF THEY EXIST" );
			sql =
					"DELETE FROM bible_books ";
			this.mDb.noReturnQuery( sql );
			return false;
		}
		else
			//Log.d( "mTag", "BIBLE BOOKS EXIST" );
		return true;
	}

	/**
	 *
	 * @param bookInfo
	 * @param abbreviation
	 */
	public void addBibleBooks( JSONObject bookInfo,String abbreviation)
	{
		try
		{
			int id = bookInfo.getInt( "order" );
			String full_name = String.valueOf( bookInfo.get( "name" ) );
			full_name = URLDecoder.decode( full_name,"UTF-8" );

			String sql =
					"INSERT INTO bible_books " +
							" (id, abbreviation, full_name ) " +
							" VALUES (" + id + ", '" + abbreviation + "', '" + full_name + "') ";
			this.mDb.noReturnQuery( sql );
		} catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 *
	 * @param bookInfo
	 * @param versionId
	 */
	public void addBibleBookForVersion(JSONObject bookInfo, int versionId )
	{
		//Log.d("mtag","hit addBibleBookForVersion");
		//Log.d( "mtag","bookInfo: " +bookInfo.toString() );
		try
		{
			int book_id = bookInfo.getInt( "order" );
			//Log.d( "mtag","book id = " + book_id );
			int last_chapter = bookInfo.getInt( "last_chapter" );
			//Log.d( "mtag","last chapter = " + last_chapter );
			int book_order = bookInfo.getInt( "order" );
			//Log.d( "mtag","book order = " + book_order );

			String sql =
					"INSERT INTO bible_version_to_book " +
							" (version_id, book_id, last_chapter, book_order ) " +
							" VALUES (" + versionId + ", " + book_id + ", " + last_chapter + ", " + book_order + ")";
			//Log.d( "mtag","insert into bible version to book sql: " + sql );
			this.mDb.noReturnQuery( sql );
		} catch (JSONException e)
		{
			e.printStackTrace();
			//Log.e( "mtag","Exception in add Bible book for version" );
		}
	}

	/**
	 *
	 * @param primaryID
	 * @param id
	 */
	public void holyHash( int primaryID,int id )
	{
		String hash = "";
		String sql =
		"SELECT	abbreviation " +
				" FROM bible_versions " +
				" ORDER BY abbreviation ";
		String[][] results = this.mDb.query( sql );
		for( int i = 0; i < results.length; i++ )
		{
			hash += AiSTr.denullifyString( results[i][0] ) + primaryID;
		}
		hash = SFUtil.md5( hash );
		//Log.d( "mTag","Bible hash = " + hash );
		addHashData( id,hash,"bible" );
	}

	// Calendar data manipulation methods

	/**
	 *
	 * @param calendarId
	 * @param includedIn
	 * @param parsedContent
	 * @param content
	 */
	public void addCalendar( String calendarId, String includedIn, String parsedContent, String content )
	{
		//Log.d( "mtag","Hit add Calendar" );
		int sectionContentId = 0;
		String sql =
				"SELECT id FROM section_content " +
						" WHERE ondevice_section_id = " +
						" (SELECT id FROM ondevice_sections " +
						"	WHERE external_id = " + includedIn + ")";
		String[][] results = this.mDb.query( sql );
		if( results != null && results.length > 0 )
		{
			sectionContentId = AiSTr.denullifyInt( results[0][0] );
		}
		else
		{
			// Insert into section_content
			sql =
					" SELECT id FROM ondevice_sections " +
							" WHERE external_id =  " + includedIn;
			results = this.mDb.query( sql );
			if( results.length > 0 )
			{
				sectionContentId = mDb.insertBlank( "section_content", "id" );
			}
			else
			{
				Log.e( "error","ERROR: Can't find ondevice_section_id" );
			}
		}
		// update section_content
		sql =
				" UPDATE section_content " +
						" SET ondevice_section_id = " +
						"	( SELECT id FROM ondevice_sections WHERE external_id = " + includedIn + " ), " +
						" content = '" + calendarId + "', " +
						" last_update = " + SFUtil.currentTimestamp() +
						" WHERE id = " + sectionContentId;
		this.mDb.noReturnQuery( sql );

		// update section_content_meta
		sql =
				"DELETE FROM section_content_meta " +
						" WHERE section_content_id = " + sectionContentId;
		this.mDb.noReturnQuery( sql );

		String[] keys = {"calendarid","includedin","parsedcontent","content"};
		String[] values = {calendarId,includedIn,AiSTr.dbEscape( parsedContent ),AiSTr.dbEscape( content )};
		//Log.d( "mtag","content in values = " + values[3] );
		for ( int i = 0; i < keys.length; i++ )
		{
			sql =
					"INSERT INTO section_content_meta " +
							" (section_content_id, meta_key, meta_value) " +
							" VALUES ( " + sectionContentId + ", '" + keys[i] + "', '" + values[i].trim() + "') ";
			//mApplication.longInfo( "mtag","sql = " +sql );
			//Log.d( "mtag","sql = " + sql );
			this.mDb.noReturnQuery( sql );
		}
	}

	/**
	 *
	 * @param id
	 * @param includedIn
	 */
	public void hashCalendar(  int id, String includedIn )
	{
		//Log.d( "mtag","Hit hash Calendar" );
		String sectionId;
		String[] keys = {"calendarid","includedin","content"};
		String[] values = new String[keys.length];

		String sql =
				" SELECT section_content_id FROM section_content_meta " +
						" WHERE meta_key = 'includedin'" +
						" AND meta_value = '" + includedIn + "'";
		String[][] results = this.mDb.query( sql );
		if( results != null && results.length > 0 )
		{
			sectionId = results[0][0];

			try
			{
				for (int i = 0; i < keys.length; i++)
				{
					sql =
							" SELECT meta_value FROM section_content_meta " +
									" WHERE meta_key = '" + keys[i] + "'" +
									" AND section_content_id = " + sectionId;
					results = this.mDb.query( sql );
					if (results != null && results.length > 0)
					{
						values[i] = results[0][0];
					}

				}

				String hash = values[0].trim() + values[1].trim() + values[2].trim();
				//Log.d( "mtag","calender hash string = " + hash );

				hash = SFUtil.md5( hash );
				addHashData( id, hash, "calendar" );
			}
			catch (Exception e)
			{
				e.getStackTrace();
				Log.e("ERROR","Could not hash calendar");
			}
		}
	}

	/**
	 *
	 * @return
	 */
	public String getEventData()
	{
		String returnString = "";
		String sql = "";
		int sectionPosition = mApplication.currentCalendarPosition;
		//Log.d( "mtag","event data section id = " + sectionPosition );

		sql =
				" SELECT meta_value FROM section_content_meta " +
						" WHERE meta_key = 'parsedcontent' " +
						" AND section_content_id = " +
						"	( SELECT id FROM section_content WHERE ondevice_section_id = " +
						"		( SELECT id FROM ondevice_sections WHERE position_index = " +sectionPosition + ") " +
						"   )";
		String[][] results = this.mDb.query( sql );
		if( results != null && results.length > 0 )
		{
			returnString = results[0][0];
		}

		return returnString;
	}

	// utility for testing

	/**
	 *
	 * @param sql
	 */
	public void debugQuery(String sql)
	{
		this.mDb.noReturnQuery( sql );
	}

	/**
	 *
	 * @param content
	 */
	public void setStreamData( String content )
	{
		int sectionContentId ;
		String sql =

				" SELECT id FROM section_content " +
				" 	WHERE ondevice_section_id = " +
				"		( SELECT id FROM ondevice_sections " +
				"		WHERE position_index = " + mApplication.currentVideoStreamPosition + " ) ";
		String[][] results = this.mDb.query( sql );
		if( results != null && results.length > 0 )
		{
			sectionContentId = AiSTr.denullifyInt( results[0][0] );

			sql =
					" UPdate section_content_meta " +
							" SET meta_value = '" + content + "' " +
							" (section_content_id, meta_key, meta_value) " +
							" WHERE section_content_id = " + sectionContentId +
							" AND meta_key = 'content'";
			this.mDb.noReturnQuery( sql );
		}
	}
}