package com.sharefaith.thesharefaithapp.models;

import com.appideas.base.AiDb;
import com.appideas.base.AiSTr;
import com.sharefaith.thesharefaithapp.base.SFAppData;
import com.sharefaith.thesharefaithapp.base.SFApplication;
import com.sharefaith.thesharefaithapp.base.SFConfig;
import com.sharefaith.thesharefaithapp.base.SFUtil;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

/**
 * Created by Sharefaith1 on 2014-11-02.
 */
public class SFSermonModel
{
	/**
	 * Instance of the database
	 */
	private AiDb mDb;

	/**
	 * This object's database ID
	 */
	public int mId;

	/**
	 * Preacher name
	 */
	public String mPreacher;

	/**
	 * Sermon title
	 */
	public String mTitle;

	/**
	 * An object of the series
	 */
	public SFSermonSeriesModel mSeries;

	/**
	 * Epoch date
	 */
	public int mSermonDate;

	/**
	 * Human-readable date
	 */
	public String mShowDate;

	/**
	 * URL of the video
	 */
	public String mVideoUrl;

	/**
	 * URL of the notes
	 */
	public String mNotesUrl;

	/**
	 * Local file path of downloaded notes
	 */
	public String mNotesLocation;

	/**
	 * Epoch date of notes download
	 */
	public int mNotesDownloadDate;

	/**
	 * If the sermon id downloadable
	 */
	public boolean mIsDownloadable;

	/**
	 * URL of the audio file
	 */
	public String mAudioUrl;

	/**
	 * Local file path of downloaded audio file
	 */
	public String mAudioLocation;

	/**
	 * Cuyrrent playback progress
	 */
	public int mCurrentAudioProgress;

	/**
	 * Whether or not the sermon is visible in the series list
	 */
	public boolean mIsVisible;

	/**
	 * URL to feed to the inline player
	 */
	public String mPlayableUrl;

	public String mSfTag;

	public String mSeriesTitle;

	public int mExternalId;

	public String mDescription;

	public int mAudioDownloadDate;

	public int mAudioDownloadProgress;

	public String mIncludedInString;

	public int mWpId;


	/**
	 * Class constructor
	 *
	 * @since		20141021
	 */
	public SFSermonModel( int id )
	{
		if( id < 0 )
		{
			id = 0;
		}

		this.mId = id;
		this.mSfTag = "sermons";

		this.mDb = SFApplication.getInstance().getDb();
		this.refreshMembers();
	}

	public SFSermonModel( int id, int seriesId )
	{
		if( id < 0 )
		{
			id = 0;
		}

		this.mId = id;
		this.mSfTag = "sermons";

		this.mDb = SFApplication.getInstance().getDb();
		this.refreshMembers( seriesId );
	}

	public SFSermonModel()
	{
		this( 0 );
	}

	/**
	 * Refresh member variables for a specific menu item from the datasource
	 *
	 * @since		20141021
	 */
	public void refreshMembers()
	{
		refreshMembers( 0 );
	}

	public void refreshMembers(int seriesId)
	{
		this.mPreacher = "";
		this.mTitle = "";
		this.mSeries = new SFSermonSeriesModel();
		this.mSermonDate = 0;
		this.mShowDate = "";
		this.mVideoUrl = "";
		this.mNotesUrl = "";
		this.mNotesLocation = "";
		this.mNotesDownloadDate = 0;
		this.mIsDownloadable = false;
		this.mAudioUrl = "";
		this.mAudioLocation = "";
		this.mCurrentAudioProgress = 0;
		this.mIsVisible = true; // Set this to true, and only set it false if there's a 0 in the database
		this.mPlayableUrl = "";
		this.mSeriesTitle = "";
		this.mExternalId = 0;
		this.mDescription = "";
		this.mAudioDownloadDate = 0;
		this.mAudioDownloadProgress = 0;
		this.mIncludedInString = "";
		this.mWpId = 0;

		if( this.mDb == null )
		{
			this.mDb = SFApplication.getInstance().getDb();
		}

		String sql =	"SELECT     content " +
						"FROM       section_content " +
						"WHERE      id = " + this.mId;

		String[][] result = this.mDb.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			this.mTitle =  AiSTr.denullifyString( result[ i ][ 0 ] );
		}

		if( seriesId >0 )
		{
			this.mSeries = new SFSermonSeriesModel( seriesId );
		}
		else
		{
			sql =
					"SELECT playlist_section_content_id " +
							" FROM sermon_to_playlist " +
							" WHERE sermon_section_content_id = " + this.mId;
			result = this.mDb.query( sql );
			if( result.length > 0 )
			{
				this.mSeries = new SFSermonSeriesModel( AiSTr.denullifyInt( result[0][0] ) );
			}
		}
		this.mSeriesTitle = this.mSeries.mTitle;
		this.mPreacher = this.mSeries.mPreacher;

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

			if( key.equals( "sermondate" ) )
			{
				this.mSermonDate = Integer.valueOf( value );

				Date convertDate = new Date( this.mSermonDate * 1000L );
				SimpleDateFormat dateFormat = new SimpleDateFormat( "MMMM d\nyyyy" );
				this.mShowDate = dateFormat.format( convertDate );
			}
			else if( key.equals( "videourl" ) )
			{
				this.mVideoUrl = value.trim();
			}
			else if( key.equals( "notesurl" ) )
			{
				this.mNotesUrl = value.trim();
			}
			else if( key.equals( "noteslocation" ) )
			{
				this.mNotesLocation = value.trim();
			}
			else if( key.equals( "isdownloadable" ) )
			{
				this.mIsDownloadable = AiDb.fixBoolean( AiSTr.denullifyInt( value ) );
			}
			else if( key.equals( "audiourl" ) )
			{
				this.mAudioUrl = value.trim();
				this.mPlayableUrl = this.mAudioUrl;
			}
			else if( key.equals( "audiolocation" ) )
			{
				this.mAudioLocation = value.trim();
				this.mPlayableUrl = this.mAudioUrl; // prefer a local copy to remote
			}
			else if( key.equals( "currentaudioprogress" ) )
			{
				this.mCurrentAudioProgress = AiSTr.denullifyInt( value );
			}
			else if( key.equals( "isvisible" ) )
			{
				this.mIsVisible = AiDb.fixBoolean( AiSTr.denullifyInt( value ) );
			}
			else if( key.equals( "preacher" ) )
			{
				this.mPreacher = value.trim(); // overrides the series preacher
			}
			else if( key.equals( "seriestitle" ) )
			{
				this.mSeriesTitle = value.trim();
			}
			else if( key.equals( "externalid" ) )
			{
				this.mExternalId = AiSTr.denullifyInt( value.trim() );
			}
			else if( key.equals( "description" ) )
			{
				this.mDescription = value.trim();
			}
			else if( key.equals( "audiodownloaddate" ) )
			{
				this.mAudioDownloadDate = AiSTr.denullifyInt( value.trim() );
			}
			else if( key.equals( "audiodownloadprogress" ) )
			{
				this.mAudioDownloadProgress = AiSTr.denullifyInt( value.trim() );
			}
			else if( key.equals( "includedIn" ))
			{
				this.mIncludedInString = value.trim();
			}
			else if( key.equals( "wpid" ))
			{
				this.mWpId = AiSTr.denullifyInt( value.trim() );
			}

		}
	}

	/**
	 * Get instances of all menu items for the main navigation menu
	 *
	 * @since		20141021
	 */
	public static SFSermonModel[] getAll()
	{
		Integer[] idArray = SFSermonModel.getAllIds();
		SFSermonModel[] items = new SFSermonModel[idArray.length];

		for( int i = 0; i < idArray.length; i++ )
		{
			items[i] = new SFSermonModel( idArray[i] );
		}

		return items;
	}

	/**
	 * Get the ID for all instances that should appear in the nav menu
	 *
	 * @since		20141021
	 */
	public static Integer[] getAllIds()
	{
		AiDb db = SFApplication.getInstance().getDb();
		SFPostModel baseModel = new SFPostModel();

		String sql =	"SELECT     id " +
				"FROM       section_content " +
				"WHERE      ondevice_section_id = " +
				"				(SELECT id FROM ondevice_sections WHERE sf_tag = '" + baseModel.mSfTag + "') AND " +
				"			parent_id = 0 " +
				"ORDER BY	position_index DESC";
		String[][] result = db.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;
	}

	/**
	 * Get a model object from its external object ID
	 *
	 */
	public static SFSermonModel objectFromExternalId( int externalId, int seriesId )
	{
		SFSermonModel model = new SFSermonModel( 0 );

		AiDb db = SFApplication.getInstance().getDb();

		String sql =	"SELECT     section_content.id AS id " +
						"FROM       section_content, section_content_meta " +
						"WHERE      section_content.ondevice_section_id = " +
						"				(SELECT id FROM ondevice_sections WHERE sf_tag = '" + model.mSfTag + "') AND " +
						"			section_content.parent_id = " + seriesId + " AND " +
						"			section_content_meta.section_content_id = section_content.id AND " +
						"			section_content_meta.meta_key = 'externalid' AND " +
						"			section_content_meta.meta_value = " + externalId + " " +
						"ORDER BY	section_content.position_index DESC " +
						"LIMIT		0, 1";
		// The caller can send -1 as the series ID to get the first series in which this sermon appears
		if( seriesId < 0 )
		{
			sql =	"SELECT     section_content.id AS id " +
					"FROM       section_content, section_content_meta " +
					"WHERE      section_content.ondevice_section_id = " +
					"				(SELECT id FROM ondevice_sections WHERE sf_tag = '" + model.mSfTag + "') AND " +
					"			section_content.parent_id > 0 AND " +
					"			section_content_meta.section_content_id = section_content.id AND " +
					"			section_content_meta.meta_key = 'externalid' AND " +
					"			section_content_meta.meta_value = " + externalId + " " +
					"ORDER BY	section_content.position_index DESC " +
					"LIMIT		0, 1";
		}
		String[][] result = db.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			int id =  AiSTr.denullifyInt( result[ i ][ 0 ] );
			model = new SFSermonModel( id );
		}

		return model;
	}

	public static int saveSermonFromSyncObject( HashMap<String,String> input )
	{
		AiDb db = SFApplication.getInstance().getDb();
		SFAppData appData = new SFAppData();

		int count;
		int sectionId = 0 ;
		//int[] sectionContentIds;
		String sql =
				"SELECT section_content_id " +
						" FROM section_content_meta " +
						" WHERE meta_key = 'sermondate' " +
						" AND 	section_content_id " +
						" IN " +
						" (SELECT section_content_id " +
						" FROM 	section_content_meta " +
						" WHERE meta_key = 'externalid' " +
						" AND 	meta_value = '" + input.get( "id" ) + "')";
		String[][] results = db.query( sql );

		count = results.length;
		//Log.d("mTag","id = " + input.get( "id" ));
		//Log.d("mTag","count = " + results.length);
		if( count > 1 )
		{
			SFSermonModel.removeSermonFromExternalID( AiSTr.denullifyInt( input.get( "id" ) ) );
		}
		if( count !=1 )
		{
			sectionId = prepareDatabaseForNewSermon();
		}
		else
		{

			sectionId = AiSTr.denullifyInt( results[0][0] );
		}


		try
		{
			sql =
                    "UPDATE section_content " +
                            "SET	ondevice_section_id = 0, " +
                            " 		parent_id = 0, " +
                            "		position_index = " + input.get( "date" ) + ", " +
                            "		content = '" +  AiSTr.dbEscape( URLDecoder.decode( input.get( "title" ), "UTF-8" ) ) + "', " +
                            "		last_update = 0 " +
                            "WHERE id = " + sectionId;
		} catch (UnsupportedEncodingException e)
		{
			e.printStackTrace();
		}
		db.noReturnQuery( sql );

		String[] keys = {"externalid","description","sermondate","videourl","notesurl","isdownloadable","audiourl","preacher","seriestitle"};
		String[] values = {"id","description","date","videourl","notesurl","isdownloadable","audiourl","preacher","series"};

		for( int i = 0; i < keys.length; i++ )
		{
			if(input.get( values[i] ) == null)
			{
				return -1;
			}
			try
			{
				sql =
                        "UPDATE section_content_meta " +
                                " SET	meta_value = '" + AiSTr.dbEscape( URLDecoder.decode( input.get( values[i] ) , "UTF-8" ) ) + "'" +
                                " WHERE section_content_id = " + sectionId +
                                " AND meta_key = '" + keys[i] + "'";
			} catch (UnsupportedEncodingException e)
			{
				e.printStackTrace();
			}
			db.noReturnQuery( sql );
		}
		if( input.get( "includedIn" ) != null)
		{
			try
			{
				sql =
                        "UPDATE section_content_meta " +
                                " SET	meta_value = '" + AiSTr.dbEscape( URLDecoder.decode( input.get( "includedIn" ) , "UTF-8" ) ) + "'" +
                                " WHERE section_content_id = " + sectionId +
                                " AND meta_key = 'includedIn'";
			} catch (UnsupportedEncodingException e)
			{
				e.printStackTrace();
			}
			db.noReturnQuery( sql );
		}

		sql =
				"UPDATE section_content_meta " +
						" SET	meta_value = '1'" +
						" WHERE section_content_id = " + sectionId +
						" AND meta_key = 'isvisible'";
		db.noReturnQuery( sql );

		//Log.d( "mTag","preacher = " + input.get( "preacher" )  + ", series = " + input.get( "series" ));
		Integer[] includedIn = new Integer[0];
		try
		{
			includedIn = getPlaylistsByPreacherAndSeries( URLDecoder.decode( input.get("preacher"), "UTF-8"),URLDecoder.decode( input.get("series"), "UTF-8"));
		} catch (UnsupportedEncodingException e)
		{
			e.printStackTrace();
		}

		sql =
				"DELETE FROM sermon_to_playlist " +
						" WHERE sermon_section_content_id = " + sectionId;
		db.noReturnQuery( sql );

		for( int i = 0; i < includedIn.length; i++ )
		{
			sql =
					"INSERT INTO sermon_to_playlist " +
							" (sermon_section_content_id, playlist_section_content_id) " +
							" VALUES (" + sectionId + ", " + includedIn[i] + ")";
			db.noReturnQuery( sql );
		}

		SFSermonModel model = new SFSermonModel( sectionId );
		model.saveHashValue();

		/*String title = input.get( "title" );
		String preacher = input.get( "preacher" );
		String seriestitle = input.get( "series" );
		int sermondate = Integer.valueOf( input.get( "date" ) );
		String videourl = input.get( "videourl" );
		String notesurl = input.get( "notesurl" );
		int isdownloadable = Integer.valueOf( input.get( "isdownloadable" ) );
		String audiourl = input.get( "audiourl" );
		String description = input.get( "description" );
		int isvisible = 1;
		int externalId = Integer.valueOf( input.get( "id" ) );
		String includedInString = input.get( "includedIn" );

		SFSermonModel sermon = SFSermonModel.objectFromExternalId( externalId );
		int returnValue = sermon.mId;

		if( returnValue == 0 )
		{
			returnValue = SFSermonModel.prepareSermonForSeriesId( seriesId );
		}

		try
		{
			title = URLDecoder.decode( title, "UTF-8" );
			preacher =  URLDecoder.decode( preacher, "UTF-8" );
			seriestitle =  URLDecoder.decode( seriestitle, "UTF-8" );
			videourl = URLDecoder.decode( videourl, "UTF-8" );
			notesurl =  URLDecoder.decode( notesurl, "UTF-8" );
			audiourl =  URLDecoder.decode( audiourl, "UTF-8" );
			description =  URLDecoder.decode( description, "UTF-8" );
			includedInString = URLDecoder.decode( includedInString, "UTF-8" );
		}
		catch( Exception e )
		{

		}

		sermon.mId = returnValue;
		sermon.mAudioUrl = audiourl;
		sermon.mCurrentAudioProgress = 0;
		sermon.mIsDownloadable = AiDb.fixBoolean( isdownloadable );
		sermon.mIsVisible = AiDb.fixBoolean( isvisible );
		sermon.mNotesUrl = notesurl;
		sermon.mPreacher = preacher;
		sermon.mSeriesTitle = seriestitle;
		sermon.mSermonDate = sermondate;
		sermon.mTitle = title;
		sermon.mExternalId = externalId;
		sermon.mVideoUrl = videourl;
		sermon.mDescription = description;
		sermon.mIncludedInString = includedInString;

		sermon.saveMembers();

		return returnValue;*/
		return 1;
	}

	public static Integer[] getPlaylistsByPreacherAndSeries( String preacher, String series )
	{
		//Log.d( "mTag","hit getplaylist by preacher and series" );
		AiDb db = SFApplication.getInstance().getDb();
		Integer[] returnValue = null;

		String sql =
				" SELECT id " +
						" FROM section_content " +
						" WHERE content = '" + AiSTr.dbEscape(series) + "' " +
						" AND	id IN " +
						"	(SELECT section_content_id " +
						" 	FROM section_content_meta " +
						" 	WHERE meta_key = 'preacher' " +
						" 	AND   meta_value = '" +  AiSTr.dbEscape(preacher) + "' " +
						" 	AND	section_content_id " +
						" 	IN " +
						"		(SELECT playlist_section_content_id " +
						"	 	FROM playlist_to_section ))";
		String[][] resultsPreacherAndSeries = db.query( sql );

		sql =
				" SELECT id " +
						" FROM section_content " +
						" WHERE content = '' " +
						" AND	id IN " +
						"	(SELECT section_content_id " +
						" 	FROM section_content_meta " +
						" 	WHERE meta_key = 'preacher' " +
						" 	AND   meta_value = '" + AiSTr.dbEscape(preacher) + "' " +
						" 	AND	section_content_id " +
						" 	IN " +
						"		(SELECT playlist_section_content_id " +
						"	 	FROM playlist_to_section ))";
		String[][] resultsPreacher = db.query( sql );

		sql =
				" SELECT id " +
						" FROM section_content " +
						" WHERE content = '" + AiSTr.dbEscape(series) + "' " +
						" AND	id IN " +
						"	(SELECT section_content_id " +
						" 	FROM section_content_meta " +
						" 	WHERE meta_key = 'preacher' " +
						" 	AND   meta_value = '' " +
						" 	AND	section_content_id " +
						" 	IN " +
						"		(SELECT playlist_section_content_id " +
						"	 	FROM playlist_to_section ))";
		String[][] resultsSeries = db.query( sql );
		sql =
				" SELECT id " +
						" FROM section_content " +
						" WHERE content = '' " +
						" AND id IN " +
						"	(SELECT section_content_id " +
						" 	FROM section_content_meta " +
						" 	WHERE meta_key = 'preacher' " +
						" 	AND   meta_value = '' " +
						" 	AND	section_content_id " +
						" 	IN " +
						"		(SELECT playlist_section_content_id " +
						"	 	FROM playlist_to_section ))";
		String[][] resultAllPlaylist = db.query( sql );
		returnValue = new Integer[resultsPreacher.length + resultsSeries.length + resultAllPlaylist.length + resultsPreacherAndSeries.length];
		//Log.d("mTag","returnValue length = " + returnValue.length);
		int index = 0;
		for( int i = 0; i < resultsPreacher.length; i++ )
		{
			returnValue[index] = AiSTr.denullifyInt( resultsPreacher[i][0] );
			index++;
		}
		for( int i = 0; i < resultsSeries.length; i++ )
		{
			returnValue[index] = AiSTr.denullifyInt( resultsSeries[i][0] );
			index++;
		}
		for( int i = 0; i < resultAllPlaylist.length; i++ )
		{
			returnValue[index] = AiSTr.denullifyInt( resultAllPlaylist[i][0] );
			index++;
		}
		for( int i = 0; i < resultsPreacherAndSeries.length; i++ )
		{
			returnValue[index] = AiSTr.denullifyInt( resultsPreacherAndSeries[i][0] );
			index++;
		}
		return returnValue;
	}

	public static int prepareDatabaseForNewSermon()
	{
		AiDb db = SFApplication.getInstance().getDb();

		int returnValue = db.insertBlank( "section_content", "id"  );

		String sql =
				"UPDATE	section_content " +
				"SET	ondevice_section_id = 0, " +
				"		parent_id = 0, " +
				"		position_index = 0 " +
				"WHERE	id = " + returnValue;
		db.noReturnQuery( sql );

		String[] keys = {
			"description", "sermondate", "videourl",
			"notesurl", "noteslocation", "isdownloadable",
			"audiourl", "audiolocation", "audiodownloaddate",
			"audiodownloadprogress", "currentaudioprogress", "isvisible",
			"preacher", "seriestitle", "externalid", "includedIn"
		};

		for( int i = 0; i < keys.length; i++ )
		{
			sql =
					"INSERT INTO	section_content_meta " +
					"				(section_content_id, meta_key, meta_value) " +
					"VALUES			( " + returnValue + ", '" + keys[i] + "', '' )";
			db.noReturnQuery( sql );
		}

		return returnValue;
	}

	public void saveMembers()
	{
		String sql =
				"UPDATE		section_content " +
				"SET		content = '" + AiSTr.dbEscape( this.mTitle ) + "', " +
				"			ondevice_section_id = 0 " +
				"WHERE		id = " + this.mId;
		this.mDb.noReturnQuery( sql );

		HashMap<String,String> input = new HashMap<String,String>();
		input.put( "description", this.mDescription );
		input.put( "sermondate", String.valueOf( this.mSermonDate ) );
		input.put( "videourl", this.mVideoUrl );
		input.put( "notesurl", this.mNotesUrl );
		input.put( "noteslocation", this.mNotesLocation );
		input.put( "isdownloadable", String.valueOf( AiDb.fixDbBoolean( this.mIsDownloadable ) ) );
		input.put( "audiourl", this.mAudioUrl );
		input.put( "audiolocation", this.mAudioLocation );
		input.put( "audiodownloaddate", String.valueOf( this.mAudioDownloadDate ) );
		input.put( "audiodownloadprogress", String.valueOf( this.mAudioDownloadProgress ) );
		input.put( "currentaudioprogress", String.valueOf( this.mCurrentAudioProgress ) );
		input.put( "isvisible", String.valueOf( AiDb.fixDbBoolean( this.mIsVisible ) ) );
		input.put( "preacher", this.mPreacher );
		input.put( "seriestitle", this.mSeriesTitle );
		input.put( "externalid", String.valueOf( this.mExternalId ) );
		input.put( "includedIn", this.mIncludedInString );

		for( HashMap.Entry<String,String> map : input.entrySet() )
		{
			sql =
					"UPDATE		section_content_meta " +
					"SET		meta_value = '" + AiSTr.dbEscape( map.getValue() ) + "' " +
					"WHERE		section_content_id = " + this.mId + " AND " +
					"			meta_key = '" + map.getKey() + "' ";
			this.mDb.noReturnQuery( sql );
		}

		this.refreshMembers();
	}

	public static void setOrder()
	{
		AiDb db = SFApplication.getInstance().getDb();

		SFSermonSeriesModel[] seriesModels = SFSermonSeriesModel.getAll();

		// iterate each series
		for( int i = 0; i < seriesModels.length; i++ )
		{
			SFSermonSeriesModel seriesModel = seriesModels[i];
			SFSermonModel[] sermonModels = seriesModel.sermonsForSeries();

			// find the latest sermon in each series
			int latestDate = 0;
			for( int j = 0; j < sermonModels.length; j++ )
			{
				SFSermonModel sermonModel = sermonModels[j];

				int postDate = 0;
				String sql =
						"SELECT     meta_value " +
						"FROM       section_content_meta " +
						"WHERE      section_content_id = " + sermonModel.mId + " AND " +
						"			meta_key = 'sermondate'";

				String[][] result = db.query( sql );
				for( int k = 0; k < result.length; k++ )
				{
					postDate =  AiSTr.denullifyInt( result[ k ][ 0 ] );
				}

				sql =
					"UPDATE		section_content " +
					"SET		position_index = " + postDate + " " +
					"WHERE		id = " + sermonModel.mId;
				db.noReturnQuery( sql );

				latestDate = (postDate > latestDate) ? postDate : latestDate;
			} // for( int j = 0; j < sermonModels.length; j++ )

			String sql =
				"UPDATE		section_content " +
				"SET		position_index = " + latestDate + " " +
				"WHERE		id = " + seriesModel.mId;
			db.noReturnQuery( sql );
		} // for( int i = 0; i < seriesModels.length; i++ )
	} // setOrder()

	public void remove()
	{
		SFConfig config = new SFConfig();

		if( this.mAudioLocation.length() > 0 )
		{
			String filePath = config.mCustomAssetPath + this.mAudioLocation;
			File file = new File( filePath );
			file.delete();
		}
		if( this.mNotesLocation.length() > 0 )
		{
			String filePath = config.mCustomAssetPath + this.mNotesLocation;
			File file = new File( filePath );
			file.delete();
		}

		String sql =
				"DELETE FROM	section_content_meta " +
				"WHERE			section_content_id = " + this.mId;
		this.mDb.noReturnQuery( sql );

		sql =
				"DELETE FROM	section_content " +
				"WHERE			id = " + this.mId;
		this.mDb.noReturnQuery( sql );
	}

	public static void removeSermonFromExternalID( int id )
	{
		AiDb db = SFApplication.getInstance().getDb();
		String sql =
				"SELECT section_content_id " +
				"FROM section_content_meta " +
				"WHERE meta_value = " + id
				;
		String[][] results = db.query( sql );
		for(int i = 0; i < results.length; i++)
		{
			sql =
					"DELETE FROM	section_content_meta " +
					"WHERE			section_content_id = " + AiSTr.denullifyInt( results[i][0] );
			db.noReturnQuery( sql );

			sql =
					"DELETE FROM	section_content " +
					"WHERE			id = " + AiSTr.denullifyInt( results[i][0] );
			db.noReturnQuery( sql );

			sql =
					"DELETE FROM sermon_to_playlist " +
							"WHERE sermon_section_content_id = " + AiSTr.denullifyInt( results[i][0] );
			db.noReturnQuery( sql );
		}
	}

	public void setNowPlaying()
	{
		int count = 0;
		String sql =
				"SELECT     COUNT(*) as count " +
				"FROM       config_lookup " +
				"WHERE      lookup_key = 'nowplayingsermonid'";

		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		config_lookup " +
				"SET		lookup_value = '" + this.mId + "' " +
				"WHERE		lookup_key = 'nowplayingsermonid'";
		}
		else
		{
			sql =
				"INSERT INTO	config_lookup " +
				"				( lookup_key, lookup_value ) " +
				"VALUES			( 'nowplayingsermonid', '" + this.mId + "' ) ";
		}
		this.mDb.noReturnQuery( sql );
	}

	public void saveAudioPlayProgress( int value )
	{
		int count = 0;
		String sql =
				"UPDATE		section_content_meta " +
				"SET		meta_value = '" + value + "' " +
				"WHERE      section_content_id = " + this.mId + " AND " +
				"			meta_key = 'currentaudioprogress'";

		this.mDb.noReturnQuery( sql );
	}

	public static SFSermonModel getNowPlaying()
	{
		AiDb db = SFApplication.getInstance().getDb();

		int sermonId = 0;
		String sql =
			"SELECT     lookup_value " +
			"FROM       config_lookup " +
			"WHERE      lookup_key = 'nowplayingsermonid'";

		String[][] result = db.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			sermonId =  AiSTr.denullifyInt( result[ i ][ 0 ] );
		}

		return new SFSermonModel( sermonId );
	}

	public void removeDownload()
	{
		SFConfig config = new SFConfig();

		if( this.mAudioLocation.length() > 0 )
		{
			String filePath = config.mCustomAssetPath + this.mAudioLocation;
			File file = new File( filePath );
			file.delete();
		}
		if( this.mNotesLocation.length() > 0 )
		{
			String filePath = config.mCustomAssetPath + this.mNotesLocation;
			File file = new File( filePath );
			file.delete();
		}

		String sql =
				"UPDATE			section_content_meta " +
				"SET			meta_value = '' " +
				"WHERE			section_content_id = " + this.mId + " AND " +
				"				meta_key IN ( 'audiolocation', 'noteslocation','audiodownloadprogress','audiodownloaddate' )";
		this.mDb.noReturnQuery( sql );
	}

	public static SFSermonModel[] getDownloadedSermons( boolean includeNowPlaying )
	{
		SFApplication application = SFApplication.getInstance();
		AiDb db = application.getDb();

		int counter = 0;
		String sql =
				"SELECT		section_content_id " +
				"FROM		section_content_meta " +
				"WHERE		meta_key = 'audiolocation' AND " +
				"			meta_value != '' " +
				"ORDER BY 	section_content_id DESC";
		String[][] result = db.query( sql );
		SFSermonModel[] returnValue = new SFSermonModel[result.length];
		if( includeNowPlaying && application.getNowPlayingSermon()!= null && application.getNowPlayingSermon().mId != 0 )
		{
			boolean isdownloaded = false;
			for( int i = 0; i < result.length; i++ )
			{
				int sermonId =  AiSTr.denullifyInt( result[ i ][ 0 ] );
				if( application.getNowPlayingSermon().mId == sermonId )
				{
					isdownloaded = true;
					returnValue[counter] = application.getNowPlayingSermon();
					counter++;
					break;
				}
			}
			if( !isdownloaded )
			{
				returnValue = null;
				returnValue = new SFSermonModel[(result.length + 1)];
				returnValue[counter] = application.getNowPlayingSermon();
				counter++;
			}
		}
		for( int i = 0; i < result.length; i++ )
		{
			int sermonId =  AiSTr.denullifyInt( result[ i ][ 0 ] );
			if( application.getNowPlayingSermon() == null || application.getNowPlayingSermon().mId != sermonId )
			{
				returnValue[counter] = new SFSermonModel( sermonId );
				counter++;
			}
		}

		return returnValue;
	}

	public String saveHashValue()
	{
		SFAppData appData = new SFAppData();
		String returnvalue = calculateHashValue();

		String sql =
				"DELETE FROM sync_objects " +
						"WHERE sync_object_id = " + mExternalId +
						" AND object_type = 'sermon'";
		mDb.noReturnQuery( sql );

		sql =
				"INSERT INTO sync_objects " +
						"( sync_object_id, hash_value, object_type ) " +
						"VALUES ( " + mExternalId + ", '" + returnvalue + "', 'sermon' )"
		;
		mDb.noReturnQuery( sql );

		//Log.d( "mTag", "SERMON HASHED STRING = " + returnvalue );

		return returnvalue;
	}

	public String calculateHashValue()
	{
		String hashContent = mDescription.trim();
		int isDownloadable = 0;
		if( mIsDownloadable)
		{
			isDownloadable = 1;
		}

		String stingToHash = mTitle + hashContent + mSermonDate + mSeriesTitle +mPreacher + mAudioUrl+ mNotesUrl + mVideoUrl + isDownloadable + mIncludedInString;

		//testing
		//Log.d("mTag","SERMON HASH STRING = "+ stingToHash);

		return SFUtil.md5( stingToHash );
	}
}