/**
 * Helpers for ChurchApp in-device database
 */
package com.sharefaith.churchapp.model;

import java.io.IOException;
import java.util.HashMap;

import com.codename1.db.Cursor;
import com.codename1.db.Database;
import com.codename1.db.Row;
import com.codename1.io.Log;
import com.sharefaith.churchapp.SFUtil;

/**
 * @author costmo
 * @since 20140312
 *
 */
public class ChurchAppData
{
	/**
	 * Instance of the CN1 database abstraction
	 */
	public Database db;
	
	/**
	 * The name of the database file
	 */
	public static final String DB_FILE = "SFChurchApp.db";
	
	/**
	 * Default interface theme
	 */
	public String theme = new String( "light" );
	
	/**
	 * Default icon weight
	 */
	public String weight = new String( "thin" );
	
	/**
	 * Class constructor.
	 * 
	 * @since 20140312
	 * @author costmo
	 * @param	initializeDatabase		Whether or not database initialization should be checked and performed from this call. For efficiency, this should only be set true on app start
	 */
	public ChurchAppData( boolean initializeDatabase )
	{
		if( initializeDatabase )
		{
			this.initializeDatabase();
		}
		else
		{
			try
			{
				this.db = Database.openOrCreate( DB_FILE );
			}
			catch( IOException e )
			{
				e.printStackTrace();
				throw new RuntimeException( e.getMessage() );
			}
		}
		// TODO: Determine app theme and weight
	}
	
	
	/**
	 * Will initialize the database if necessary
	 * 
	 * @since 20140312
	 * @author costmo
	 */
	public void initializeDatabase()
	{
		try
		{
			// only uncomment this line to remove the database on installation during testing
			//Database.delete( DB_FILE );
			
			boolean created = Database.exists( DB_FILE );
			this.db = Database.openOrCreate( DB_FILE );
			
			if( this.db == null )
			{
				// This is a problem. We won't be able to run on devices that don't have SQLite, so we may want to provide a notification and exit here
				Log.p( "System does not support SQLite" );
			}
			
			if( !created )
			{
				SFUtil.p( "Installing tables" );
				String[] queries = ChurchAppData.getInitQueries();
				
				for( String sql : queries )
				{
					SFUtil.p(  sql  );
					db.execute( sql );
				}
			}
			
			// Uncomment to see where the database file lives on the file system
			// On Mac OS, this should be ~/.cn1/database/FILE_NAME
			//Log.p( Database.getDatabasePath( DB_FILE ) );
			
		}
		catch( IOException e )
		{
			e.printStackTrace();
			throw new RuntimeException( e.getMessage() );
		}
		
		
	} // initializeData
	
	/**
	 * Queries that initialize the database
	 * 
	 * @since 20140312
	 * @author costmo
	 */
	public static String[] getInitQueries()
	{
		ChurchAppData cad = new ChurchAppData( false );
		// This code could have been reduced by introducing a HashTable of section names/values, but I wanted the SQL to be easy to follow.
		// Since it is only run on initial app installation, efficiency isn't a huge concern
		return new String[]
		{
			// top-level configuration table
			"CREATE TABLE config_lookup ( lookup_key TEXT, lookup_value TEXT );",
			"INSERT INTO config_lookup ( lookup_key, lookup_value ) VALUES( 'last_sync', '0' );",
			"INSERT INTO config_lookup ( lookup_key, lookup_value ) VALUES( 'sync_data', '' );",
			"INSERT INTO config_lookup ( lookup_key, lookup_value ) VALUES( 'theme', '" + cad.theme + "' );",
			"INSERT INTO config_lookup ( lookup_key, lookup_value ) VALUES( 'weight', '" + cad.weight + "' );",
			
			// possible section/feature tables
			"CREATE TABLE ondevice_section_meta( ondevice_section_data_id INTEGER DEFAULT 0, meta_key TEXT, meta_value TEXT );",
			"CREATE TABLE ondevice_sections( _id INTEGER PRIMARY KEY, sf_tag TEXT, display_label TEXT, ui_location INTEGER DEFAULT 0, position_index INTEGER DEFAULT 0, last_update INTEGER DEFAULT 0 );",
			// add items on the tab bar
			"INSERT INTO ondevice_sections( sf_tag, display_label, ui_location, position_index ) VALUES( 'welcome', 'Welcome', " + SFUtil.ICON_LOCATION_TABBAR + ", 0 );",
			"INSERT INTO ondevice_sections( sf_tag, display_label, ui_location, position_index ) VALUES( 'sermons', 'Sermons', " + SFUtil.ICON_LOCATION_TABBAR + ", 1 );",
			"INSERT INTO ondevice_sections( sf_tag, display_label, ui_location, position_index ) VALUES( 'posts', 'Posts', " + SFUtil.ICON_LOCATION_TABBAR + ", 2 );",
			"INSERT INTO ondevice_sections( sf_tag, display_label, ui_location, position_index ) VALUES( 'more', 'More', " + SFUtil.ICON_LOCATION_TABBAR + ", 3 );",
			
			// add items on the "More" screen
			"INSERT INTO ondevice_sections( sf_tag, display_label, ui_location, position_index ) VALUES( 'bible', 'Bible', " + SFUtil.ICON_LOCATION_MORESCREEN + ", 0 );",
			// add items that will not be displayed by default
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'calendar', 'Calendar' );",
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'about', 'About' );",
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'contact', 'Contact' );",
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'map', 'Map' );",
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'social', 'Social' );",
			"INSERT INTO ondevice_sections( sf_tag, display_label ) VALUES( 'documents', 'Documents' );",
			
			// connect app icons
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'welcome'), 'icon', 'tabbar_welcome' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'welcome'), 'icon_selected', 'tabbar_welcome_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'sermons'), 'icon', 'tabbar_sermons' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'sermons'), 'icon_selected', 'tabbar_sermons_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'posts'), 'icon', 'tabbar_posts' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'posts'), 'icon_selected', 'tabbar_posts_selected' );",

			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'more'), 'icon', 'tabbar_more' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'more'), 'icon_selected', 'tabbar_more_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'bible'), 'icon', 'tabbar_bible' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'bible'), 'icon_selected', 'tabbar_bible_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'calendar'), 'icon', 'tabbar_calendar' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'calendar'), 'icon_selected', 'tabbar_calendar_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'about'), 'icon', 'tabbar_about' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'about'), 'icon_selected', 'tabbar_about_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'contact'), 'icon', 'tabbar_contact' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'contact'), 'icon_selected', 'tabbar_contact_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'map'), 'icon', 'tabbar_map' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'map'), 'icon_selected', 'tabbar_map_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'social'), 'icon', 'tabbar_social' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'social'), 'icon_selected', 'tabbar_social_selected' );",
			
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'documents'), 'icon', 'tabbar_documents' );",
			"INSERT INTO ondevice_section_meta( ondevice_section_data_id, meta_key, meta_value ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'documents'), 'icon_selected', 'tabbar_documents_selected' );",

			// extra information that may be pertinent to the sections
			"CREATE TABLE section_content_meta( section_content_id INTEGER DEFAULT 0, meta_key TEXT, meta_value TEXT );",
			"CREATE TABLE section_content( _id INTEGER PRIMARY KEY, ondevice_section_id INTEGER DEFAULT 0, parent_id INTEGER DEFAULT 0, position_index INTEGER DEFAULT 0, content TEXT, last_update INTEGER DEFAULT 0 );",
			"INSERT INTO section_content( ondevice_section_id, content ) VALUES( (SELECT _id FROM ondevice_sections WHERE sf_tag = 'welcome'), 'Welcome content goes here' );",
			"INSERT INTO section_content_meta( section_content_id, meta_key, meta_value ) VALUES( (SELECT _id FROM section_content WHERE ondevice_section_id = (SELECT _id FROM ondevice_sections WHERE sf_tag = 'welcome')), 'header_image', 'banner_1536.png' );"
		};
	} // getInitQueries()
	
	/**
	 * Gets a single value from a table with meta_key/meta_value pairs
	 * 
	 * @since 20140312
	 * @author costmo
	 * @param	fromTable			The table to query
	 * @param	idColumn			The column holding the foreign key
	 * @param	idValue				The value of the foreign key
	 * @param	key					the meta_key value to find
	 */
	public String getValueForMetaKey( String fromTable, String idColumn, int idValue, String key )
	{
		String returnValue = new String( "" );
		
		String sql = new String(
				"SELECT		meta_value "
			+ 	"FROM		" + fromTable + " "
			+ 	"WHERE		" + idColumn + " = " + idValue + " AND "
			+ 	"			meta_key = '" + key + "'"
		);
		
		try
		{
			Cursor cursor = this.db.executeQuery( sql );
			while( cursor.next() )
			{
				Row row = cursor.getRow();
				returnValue = row.getString( 0 );
			}
		}
		catch( IOException e )
		{
			e.printStackTrace();
			throw new RuntimeException( e.getMessage() );
		}
		
		return returnValue;
	} // getValueForMetaKey
	
	/**
	 * Gets multiple values from a table with meta_key/meta_value pairs
	 * 
	 * @since 20140312
	 * @author costmo
	 * @param	fromTable			The table to query
	 * @param	idColumn			The column holding the foreign key
	 * @param	idValue				The value of the foreign key
	 * @param	keys				An array of key values to find
	 */
	public HashMap<String,String> getValueForMetaKey( String fromTable, String idColumn, int idValue, String[] keys )
	{
		HashMap<String,String> returnValue = new HashMap<String,String>();
		
		String inClause = new String( "" );
		
		int counter = 0;
		for( String key : keys )
		{
			inClause = inClause + "'" + key + "'";
					
			if( (counter + 1) < keys.length )
			{
				inClause = inClause + ", ";
			}
			counter++;
		}
		
		String sql = new String(
				"SELECT		meta_key, meta_value "
			+ 	"FROM		" + fromTable + " "
			+ 	"WHERE		" + idColumn + " = " + idValue + " AND "
			+ 	"			meta_key IN ( " + inClause + " )"
		);

		try
		{
			Cursor cursor = this.db.executeQuery( sql );
			while( cursor.next() )
			{
				Row row = cursor.getRow();
				returnValue.put( row.getString( 0 ), row.getString( 1 ) );
			}
		}
		catch( IOException e )
		{
			e.printStackTrace();
			throw new RuntimeException( e.getMessage() );
		}
		
		return returnValue;
	} // getValueForMetaKey
	
	/**
	 * Factory method to get an implementer of this class by its tag.
	 * 
	 * @since 20140312
	 * @author costmo
	 * @param		currentForm			The form that triggered the orientation change
	 * @param		tabIndex			The index of the currently selected tab
	 */
	public static String sectionTagByLocationAndIndex( int uiLocation, int tabIndex )
	{
		String sql = new String(
				"SELECT		sf_tag "
			+ 	"FROM		ondevice_sections "
			+ 	"WHERE		ui_location = " + uiLocation  + " AND "
			+ 	"			position_index = " + tabIndex
		);
		
		ChurchAppData cad = new ChurchAppData( false );
		String tag = new String( "" );
		try
		{
			Cursor cursor = cad.db.executeQuery( sql );
			while( cursor.next() )
			{
				Row row = cursor.getRow();
				tag = row.getString( 0 );
			}
		}
		catch( IOException e )
		{
			e.printStackTrace();
			throw new RuntimeException( e.getMessage() );
		}
		
		return tag;
	}
}