/**
 * Copyright (c) 2011, Martin Foster <martin@ethereal-realms.org>
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions are met:
 * 
 *     * Redistributions of source code must retain the above copyright notice, 
 *       this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright 
 *       notice, this list of conditions and the following disclaimer in the 
 *       documentation and/or other materials provided with the distribution.
 *     * Neither the name of the Ethereal-Realms.org nor the names of its
 *       contributors may be used to endorse or promote products derived
 *       from this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.lyra.Database;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.lyra.Host.Terminus;

/**
 * The Comptroller class deals with the details and intricacies of dealing with
 * the database itself.   While the database module is in itself is public this
 * provides a middle layer preventing mass code changes in case of database
 * engine or a change in standards.
 * <p>
 * In addition to the above, it was decided that all functions against the
 * database would be restricted to one thread at a time.   While many database
 * engines can handle read operations and write operations at a time, SQLite
 * tends to lock up.  So by forcing a one thread, we allow transactions and
 * other operations to not be interrupted.
 * <p>
 * @author Martin Foster
 * @version 1.0.1, %Revision, 99%, %LastChangedDate, 17/03/11 8:17PM%
 */
public class Comptroller {

	// Package classes
	Interface Db;
	
	
	/*
	 * CONSTANTS
	 */
	
	// Default Language
	private static final String DEF_LANGUAGE = "EN";
	
	// A pseudo null value so it can be compared
	private static final String PSEUDONULL = "'|->NULL<-|'";
	
	/**
	 * A secondary constructor for the class itself.  This one requires an
	 * initialised Database.Interface class to proceed.
	 * <p>
	 * @param db Database handle to manage the connections.
	 */
	public Comptroller(Interface db) {
		// Pass Up
		this.Db = db;
	}
	// End of Constructor Class
	

	/*
	 * PROPERTIES
	 * 
	 * The properties section has a different naming scheme in order to
	 * separate its operations from those normally conducted by the database
	 * engine itself.
	 */
	
	/**
	 * The language files carries all of the information necessary to support
	 * international languages without a code rewrite.
	 * <p>
	 * @return Returns the fully generated HashMap with language elements.
	 */
	public synchronized Map<String,String> fetchLanguage() {
		// Create instance of storage Hash
		Map<String,String> prop = new HashMap<String,String>();
		
		/*
		 * Queries rarely get as simple as this one.  However there are no
		 * database checks to conduct, so a fairly straight forward operation.
		 */
		Db.getProperties(prop, "SELECT Tag, Value FROM Lang.Element");
		
		// Return the generated element
		return prop;
	}
	// End of fetchLanguage Method
	
	
	/**
	 * The Global properties file contains all information necessary to
	 * configure and control the look and feel of the application itself.
	 * <p>
	 * @param prop HashMap containing the existing properties to use.
	 */
	public synchronized void fetchGlobal(Map<String, String> prop) {
		/*
		 * Queries rarely get as simple as this one.  However there are no
		 * database checks to conduct, so a fairly straight forward operation.
		 */
		Db.getProperties(prop, "SELECT Tag, Value FROM Main.Setting");
	}
	// End of fetchGlobal Method
	
	
	/*
	 * SYSTEM ELEMENTS
	 * 
	 * System elements contain information that mostly exists within the MAIN
	 * database.  
	 */
	
	/**
	 * Extracts the version information from the version view.
	 * <p>
	 * @return String containing version info.
	 */
	public synchronized String getSystemVersion() {
		
		// Straight pass through
		return Db.getString(
				"SELECT Value " +
				  "FROM main.Setting " +
				 "WHERE Tag=?", "Program.Version");
	}
	// End of getSystemVersion Method
	
	
	/*
	 * HOST ELEMENTS
	 * 
	 * Host elements contain information specific for the hosts.   Access to
	 * song database, host entries and so forth.
	 */
	
	
	/**
	 * Extracts the version information from the version view.
	 * <p>
	 * @return String containing version info.
	 */
	public synchronized String getHostVersion() {
		
		// Straight pass through
		return Db.getString(
				"SELECT Value " +
				  "FROM host.Statistic " +
				 "WHERE Key=?", "program_version");
	}
	// End of getHostVersion Method
	
	
	/**
	 * Returns a list of host entries that exist within the database.  Used
	 * to determine what action to take at program startup.
	 * <p>
	 * @return Integer containing number of clients.
	 */
	public synchronized int getHostCount() {
		// Simple and straight forward
		return Db.getInt(
				"SELECT COUNT(*) " +
				  "FROM Main.Host");
	}
	// End of getHostCount Method
	
	
	/**
	 * Returns a complete listing of all hosts stored in the database in a
	 * key/pair structure.
	 * <p>
	 * @return List of Maps containing host keys and details.
	 */
	public synchronized List<Map<String,String>> getHostComplete() {
		
		// Storage List
		List<Map<String,String>> listing = new LinkedList<Map<String,String>>();
		
		// Direct pass through
		Db.getRows(listing,
			"SELECT Host, " +
			       "Port, " +
			       "Identifier, " +
			       "Pass, " +
			       "Filename, " +
			       "'CURRENT' AS Type " +
			  "FROM Main.Host " +
		  "ORDER BY Host");
		
		// Return generated list
		return listing;
	}
	// End of getHostComplete Method
	
	
	/**
	 * Method called to return host details when there is only one host element
	 * to find in the database.
	 * <p>
	 * @return Map containing host keys and details.
	 */
	public synchronized Map<String,String> getHostDetails() {
		
		// Direct pass through
		return Db.getRow(
				"SELECT Host, " +
				       "Port, " +
				       "Identifier, " +
				       "Pass, " +
				       "Filename " +
				  "FROM Main.Host");
	}
	// End of getHostDetails Method
	
	
	/**
	 * This variant will return a singular host based on its identifier.  
	 * <p>
	 * @param ident String identifier marking this element as unique.
	 * @return Map containing host keys and details.
	 */
	public synchronized Map<String,String> getHostDetails(String ident) {
		
		// Direct pass through
		return Db.getRow(
				"SELECT Host, " +
				       "Port, " +
				       "Identifier, " +
				       "Pass, " +
				       "Filename " +
				  "FROM Main.Host " +
				 "WHERE Identifier=?", ident);
	}
	// End of getHostDetails Method
	
	
	/**
	 * While it may be more memory hungry to fetch the entire library in
	 * one hit.  This method cuts down on overall steps and execution time.
	 * <p>
	 * @return Map using File as the primary key containing the song details.
	 */
	public synchronized Map<String,Map<String,String>> getHostLibrary() {
		
		// Straight Pass Through
		return Db.getSet(
				"SELECT File, " +
				       "Artist, " +
				       "Album, " +
				       "Title, " +
				       "Genre, " +
				       "Time, " +
				       "Track, " +
				       "Date, " +
				       "Rating " +
				  "FROM Host.Song");
	}
	// End of getHostLibrary
	
	
	/**
	 * Returns a List of strings containing every identifier for a host
	 * currently in the database.   This permits the user to then select a
	 * host for connection.
	 * <p>
	 * @return Ordered list containing identifiers.
	 */
	public synchronized List<String> getHostList() {
		// Results list
		List<String> results = new LinkedList<String>();
		
		// Find all the identifiers
		Db.getColumn(results,
				"SELECT Identifier " +
				  "FROM Main.Host " +
			  "ORDER BY Identifier");
		
		// Now that the list is generated return it
		return results;
	}
	// End of getHostList Method
	
	
	/**
	 * Will fetch the filename of the assumed singular host in the database.
	 * This assumption should only be made after confirming the amount of 
	 * hosts actually available.
	 * <p>
	 * @return String containing filename.
	 */
	public synchronized String getHostSingle() {
		
		// Simple query
		return Db.getString("SELECT Filename FROM main.Host");
	}
	// End of getHostSingle Method
	
	
	/**
	 * Will fetch information for one unique song.   Returns all database
	 * elements as a HasMap/ 
	 * <p>
	 * @param file Filename of the song itself.
	 * @return Map containing rows keys and values.
	 */
	public synchronized Map<String,String> getHostSong(String file) {
		// List of all keys
		return Db.getRow(
				"SELECT File, " +
				       "Artist, " +
				       "Album, " +
				       "Title, " +
				       "Genre, " +
				       "Time, " +
				       "Track, " +
				       "Date, " +
				       "Rating " +
				  "FROM Host.Song " +
				 "WHERE File=?", file);
	}
	// End of getHostSong Method
	
	
	
	/**
	 * There is no direct and easy method to extract multiple rows and place
	 * their values into a Map<String,Integer> element.  This method will 
	 * fake the operation and return the element completely generated
	 * containing statistics on the database as recorded last in order to
	 * determine the need for a sync operation.  Elements returns are:
	 * <ul>
     *  <li>artists: number of artists</li>
     *  <li>songs: number of songs</li>
     *  <li>db_playtime: sum of all song times in the database</li>
     *  <li>db_update: last database update in UNIX time</li>
     * </ul>
	 * <p>
	 * @return Map<String,Integer> element.
	 */
	public synchronized Map<String,Integer> getHostStatistics() {
		
		// Temporary storage elements
		List<String> keys = new LinkedList<String>();
		Map<String,Integer> pass = new HashMap<String,Integer>();
		
		// List of all keys
		Db.getColumn(keys,
				"SELECT Key " +
				  "FROM Host.Statistic " +
				 "WHERE Key <> ?", "program_version");
		
		// Sort through keys
		while (keys.size() > 0) {
			// Extract first element
			String key = keys.remove(0);
			
			// Extract elements
			Integer value = Db.getInt(
					"SELECT Value " +
					  "FROM Host.Statistic " +
					 "WHERE Key=?", key);
			
			// Create new statistical element
			pass.put(key, value);
		}
		
		// Pass on generated element
		return pass;
	}
	// End of getHostStatistics Method
	
	
	/*
	 * PROGRAM ELEMENTS
	 * 
	 * Used to extract very specific elements of configuration information
	 * necessary to attach and use the more standard Map elements.
	 */
	
	/**
	 * There can be multiple language support for the application as a result
	 * it becomes necessary to track and attach various language files as
	 * necessary.
	 * <p>
	 * @param code Language code matching the ISO 639-1 name space.
	 * @return String containing location of the database.
	 */
	public synchronized String getProgLanguage(String code) {
		
		// Location Returned
		String loc;
		
		// Extract the element
		loc = Db.getString(
				"SELECT Filename " +
				  "FROM Main.Language " +
				 "WHERE Code=?", code);
		
		// The specified language works
		if (loc != null)
			return loc;
		
		// The above failed so stick to defaults
		return Db.getString(
				"SELECT Filename " +
				  "FROM Main.Language " +
				 "WHERE Code=?", DEF_LANGUAGE);
	}
	// End of getProgLang Method
	
	
	/*
	 * ORGANISER ELEMENTS
	 * 
	 * Elements related to the display and management of various components
	 * of the Lyra Organiser screen. 
	 */
	
	/**
	 * Fetches album listing in complete from.  Variants exist specifically
	 * to deal when filtering is permitted.
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @param nil String for NIL filtering.
	 * @param unknown String for NULL elements.
	 * @return Multiple level array of strings containing all the information.
	 */
	public synchronized String[][] getOrganiserAlbum(Map<String,String> filter, String nil, String unknown) {
		
		// Simple pass through
		return getOrganiserFiltered(filter, "Album", nil, unknown);
	}
	// End of getOrganiserAlbum Method
	
	
	/**
	 * Fetches artist listing in complete from.  Variants exist specifically
	 * to deal when filtering is permitted.
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @param nil String for NIL filtering.
	 * @param unknown String for NULL elements.
	 * @return Multiple level array of strings containing all the information.
	 */
	public synchronized String[][] getOrganiserArtist(Map<String,String> filter, String nil, String unknown) {
		
		// Simple pass through
		return getOrganiserFiltered(filter, "Artist", nil, unknown);
	}
	// End of getOrganiserArtist Method
	
	
	/**
	 * Fetches genre listing in complete from.  Variants exist specifically
	 * to deal when filtering is permitted.
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @param nil String for NIL filtering.
	 * @param unknown String for NULL elements.
	 * @return Multiple level array of strings containing all the information.
	 */
	public synchronized String[][] getOrganiserGenre(Map<String,String> filter, String nil, String unknown) {
		
		// Simple pass through
		return getOrganiserFiltered(filter, "Genre", nil, unknown);
	}
	// End of getOrganiserGenre Method
	
	
	/**
	 * Fetches general information in complete from.  This will do the actual
	 * filtering without affecting any other element.
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @param column Table to conduct the search on.
	 * @param nil String for NIL filtering.
	 * @param unknown String for NULL elements.
	 * @return Multiple level array of strings containing all the information.
	 */
	private String[][] getOrganiserFiltered(Map<String,String> filter, String column, String nil, String unknown) {
		
		// Container for query parameters
		List<String> args = new LinkedList<String>();
		
		
		/*
		 * This section will append any filter based on the selections made in
		 * the filter section of the organiser.   The only key that will never
		 * be used in the filter are keys from the filter element itself.
		 */
		StringBuffer sb = new StringBuffer();
		
		// Loop through the keys and generate amplifications
		for (String key: filter.keySet()) {

			// We never filter by ourselves
			if (key.equals(column.toUpperCase()))
				continue;
			
			// How to start this component
			String append = (args.size() > 0)
			  ? "AND "
			  : "WHERE ";
			
			// Normal Entry
			if (filter.get(key) != null) {
				
				sb.append(append + key + "=? ");
				args.add(filter.get(key));
			}
			
			// SQL Specific NULL Handling
			else {
				
				sb.append(append + key + " IS NULL ");
			}
		}
		
		// Direct pass through
		String[][] data = Db.getRows(
				"SELECT COUNT(DISTINCT(IFNULL(" + column + "," + PSEUDONULL + "))) " +
				  "FROM Host.Song " +
				  sb.toString(), 
				
				"SELECT " + column + " " +
				  "FROM Host.Song " +
				  sb.toString() + 
			  "GROUP BY " + column + " " +
			  "ORDER BY " + column + " COLLATE NOCASE",
			  
			args.toArray(new String[0])
		  );
		
		/*
		 * Here we will check the first element of and see if it is null.  This
		 * will in turn be replaced by our own easier to understand identifier.
		 */
		if (data[0][0] == null)
			data[0][0] = unknown;
		
		
		/*
		 * We create a new array that will contain all the components of the
		 * array for passage.   Then we copy it and pass it on.
		 */
		String[][] pass = new String[data.length+1][1];
		pass[0][0] = nil;
		
		// Copy for passage.
		System.arraycopy(data,0,pass,1,data.length);
		
		// Return element
		return pass;
	}
	// End of getOrganiserFiltered Method
	
	
	/**
	 * Fetches song information in complete from.  Variants exist specifically
	 * to deal when filtering is permitted.  This element is different from
	 * single row management due to the fact that we have to account for all
	 * columns.
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @return Multiple level array of strings containing all the information.
	 */
	public synchronized String[][] getOrganiserSongs(Map<String,String> filter) {
		
		/*
		 * We make a conscious decision to convert the track numbers into an
		 * integer. That way they will be sorted appropriately in a sequence
		 * which will allow an entire album to be played.
		 */
		
		// Filtering string
		String details = getSongFilter(filter);
		
		
		// Direct pass through
		return Db.getRows(
				"SELECT COUNT(*) " +
				  "FROM Host.Song " + details, 	
				  
				"SELECT Title  AS \"Title\", " +
				       "Artist AS \"Artist\", " +
				       "Album  AS \"Album\", " +
				       "Time   AS \"Time\", " +
				       "CAST(Track AS INTEGER) AS \"Track\", " +
				       "Genre  AS \"Genre\", " +
				       "Date   AS \"Date\", " +
				       "Rating AS \"Rating\", " +
				       "file   AS \"file\" " +
				  "FROM Host.Song " +
				  details + 
			  "ORDER BY Album, " +
				       "Track, " +
				       "Title",
				       
			// Generate filtering agent
			getSongArray(filter)
		  );
	}
	// End of getOrganiserSongs Method
	
	
	/**
     * Will return a listing of all local playlists currently stored on the
	 * local side.  These do not include any smart lists that are generated
	 * automatically.
	 * <p>
	 * @return List of strings containing playlist titles.
	 */
	public synchronized List<String> getOrganiserPlaylists() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT Name " +
				  "FROM host.Playlist " +
			  "ORDER BY Name");
		
		// Convert the list to a tree
		return capture;
	}
	// End of getOrganiserPlaylist Method
	

	/**
	 * Lets the database do all of the calculations and heavy work associated
	 * with generating the status bar information.  
	 * <ul>
	 * <li>Songs: Number of songs selected;</li>
	 * <li>Selected: Number of selected songs, always zero; and</li>
	 * <li>Runtime: Runtime in minutes total of all songs.</li>
	 * </ul>
	 * <p>
	 * @param filter Map containing the elements to filter.
	 * @return String Map which contains status information.
	 */
	public synchronized Map<String,String> getOrganiserStatus(Map<String,String> filter) {
		
		// Container for query parameters
		List<String> args = new LinkedList<String>();
		
		
		/*
		 * This section will append any filter based on the selections made in
		 * the filter section of the organiser.   The only key that will never
		 * be used in the filter are keys from the filter element itself.
		 */
		StringBuffer sb = new StringBuffer();
		
		// Loop through the keys and generate amplifications
		for (String key: filter.keySet()) {

			// How to start this component
			String append = (args.size() > 0)
			  ? "AND "
			  : "WHERE ";

			// Appending the key and parameter
			if (filter.get(key) != null) {
				
				sb.append(append + key + "=? ");
				args.add(filter.get(key));
			}
			
			// We use the SQL specific phrasing
			else {
				
				sb.append(append + key + " IS NULL ");
			}
		}
		
		// Direct pass through
		return Db.getRow(
				"SELECT COUNT(*)  AS \"Songs\", " + 
				       "0         AS \"Selected\", " +
				       "SUM(Time) AS \"Runtime\" " +
				  "FROM Host.Song " + sb.toString(),
				  
			args.toArray(new String[0])
		  );
	}
	// End of getOrganiserStatus Method
	
	
	/*
	 * PLAYLSIT ELEMENTS
	 * 
	 * Playlist elements are related to the organiser.  They are however 
	 * provided a different identifier in order to prevent confusion.
	 */
	
	
	/**
	 * Will return a listing of all local playlists currently stored on the
	 * local side.  These do not include any smart lists that are generated
	 * automatically.
	 * <p>
	 * @return Set of strings containing playlist titles.
	 */
	public synchronized Set<String> getPlaylistLocal() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT Name " +
				  "FROM host.Playlist " +
			  "ORDER BY Name");
		
		// Convert the list to a tree
		return new TreeSet<String>(capture);
	}
	// End of getPlaylistLocal Method
	
	
	/**
	 * This will make use of a local playlist name in order to return its
	 * contents.  That in turn will normally be used to add the songs to
	 * to current playlist.
	 * <p>
	 * @param name String name of the playlist.
	 * @return List of strings containing songs file.
	 */
	public synchronized List<String> getPlaylistLocal(String name) {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.PlaylistSong " +
				  "WHERE Name=? " +
			  "ORDER BY Name", name);
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistLocal Method
	
	
	/**
	 * Will return a listing of all decades from the database.  This falls
	 * under the smart lists, since its a generated value and require 
	 * database lookups.
	 * <p>
	 * @return List containing all decades
	 */
	public synchronized List<String> getPlaylistDecade() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT Decade " +
				  "FROM host.Decades " +
				 "WHERE Decade IS NOT NULL " +
			  "GROUP BY Decade " + 
			  "ORDER BY Decade");
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistDecade Method
	
	
	/**
	 * Returns a list of all songs associated with a certain decade.  This
	 * permits a transference to the host for playback.
	 * <p>
	 * @param decade String containing decade.
	 * @return List of all filenames.
	 */
	public synchronized List<String> getPlaylistDecade(String decade) {

		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.Decades " +
				 "WHERE Decade=CAST(? AS INTEGER)", decade
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistDecade Method
	
	
	/**
	 * Returns a list of all songs associated with the favourite artists in
	 * the database.  This is based on the auditing levels.
	 * <p>
	 * @return List of all extracted filenames.
	 */
	public synchronized List<String> getPlaylistFArtist() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.Song " +
				 "WHERE Artist IN (SELECT Artist FROM host.favArtist)"
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistFArtist Method
	
	
	/**
	 * Returns a list of all songs associated with the favourite albums in
	 * the database.  This is based on the auditing levels.
	 * <p>
	 * @return List of all extracted filenames.
	 */
	public synchronized List<String> getPlaylistFAlbum() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.Song " +
				 "WHERE Album IN (SELECT Album FROM host.favAlbum)"
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistFAlbum Method
	
	
	/**
	 * Returns a list of all songs associated with the favourite Genres in
	 * the database.  This is based on the auditing levels.
	 * <p>
	 * @return List of all extracted filenames.
	 */
	public synchronized List<String> getPlaylistFGenre() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.Song " +
				 "WHERE Genre IN (SELECT Genre FROM host.favGenre)"
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistFGenre Method
	
	
	/**
	 * Returns a list of all songs associated with the favourite songs in
	 * the database.  This is based on the auditing levels.
	 * <p>
	 * @return List of all extracted filenames.
	 */
	public synchronized List<String> getPlaylistFSong() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.favSongs "
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistFSong Method
	
	
	/**
	 * Returns a list songs that have received a rating in order of precedence.
	 * <p>
	 * @return List of all extracted filenames.
	 */
	public synchronized List<String> getPlaylistFRating() {
		
		// Create instance of holder
		List<String> capture = new LinkedList<String>();
		
		// Generate query
		Db.getColumn(capture, 
				"SELECT File " +
				  "FROM host.favRating "
		  );
		
		// Convert the list to a tree
		return capture;
	}
	// End of getPlaylistFSong Method
	
	
	/**
	 * Removes a local playlist from the local host database.  Does not remove
	 * the songs associated with said playlist.
	 * <p>
	 * @param name String name of the playlist.
	 */
	public synchronized void deletePlaylistLocal(String name) {
		
		// Remove entry from the database
		Db.updateSingleRows(
				"DELETE FROM host.Playlist " +
				 "WHERE Name=?",
		  name
		);
	}
	// End of deletePlaylistLocal Method
	
	
	/**
	 * In order to insert songs into the playlist it becomes necessary to
	 * first create a holding entry in the appropriate table.
	 * <p>
	 * @param name String name of the playlist.
	 */
	public synchronized void insertPlaylistLocal(String name) {
		
		// Insert the playlist entry first
		Db.updateSingleRows(
				"INSERT INTO host.Playlist " +
				       "(Name) " +
				"VALUES (?)", name);
	}
	// End of insertPlaylistLocal Method
	
	
	/**
	 * Will insert all of the currently listed songs for the current playlist
	 * into this table.   The linking should then connect the Playlist and 
	 * Song database together.
	 * <p>
	 * @param playlist String containing playlist name;
	 * @param current List of HashMaps containing contents of current playlist;
	 */
	public synchronized void insertPlaylistSong(String playlist, List<Map<String,String>> current) {
		
		// Simple loop through elements
		for (int count=0; count < current.size(); count++) {
			
			// Extract current line
			Map<String,String> row = current.get(count);
			
			// Insert into the database
			Db.updateMultipleRows(
					"INSERT INTO host.PlaylistSong " +
					       "(Name, File) " +
					"VALUES (?,?)",
					
				playlist,
				row.get("file")
			  );
		}
		
		// Completed update and close off transaction
		Db.updateComplete();
	}
	// End of insertPlaylistSongs Method
	
	
	/*
	 * SONG ELEMENTS
	 * 
	 * Song elements are related to play list management.  They will allow 
	 * retrieval of songs based on certain criteria.  For example adding in 
	 * an album.
	 */
	
	
	/**
	 * Generates the string that will be pass on in order to generate the
	 * appropriate query to the database.
	 * <p>
	 * @param filter Map containing all elements to add into the filter
	 * @return String containing the filter.
	 */
	private String getSongFilter(Map<String,String> filter) {
		
		// Create string store
		StringBuffer sb = new StringBuffer();
		
		// Internal counter to determine amplifier
		int depth = 0;
		
		// Loop through keys
		for (String key: filter.keySet()) {
			
			// Determine amplifier
			String amp = (depth < 1)
				? "WHERE "
				: "AND ";
				
			// Null Handling
			if (filter.get(key) != null)
				sb.append(amp + key + "=? ");

			// SQL specific NULL handler
			else
				sb.append(amp + key + " IS NULL ");
			
			// Increment
			depth++;
		}
		
		// Return elements
		return sb.toString();
	}
	// End of getSongFilter Method
	
	
	/**
	 * Generates the necessary array to pass on for the query.
	 * <p>
	 * @param filter Map containing all elements to add into the filter.
	 * @return String array containing results.
	 */
	private String[] getSongArray(Map<String,String> filter) {
		
		// Container
		ArrayList<String> pass = new ArrayList<String>();
				
		// Loop through and append
		for (String key: filter.keySet()) {
			
			// Insert into array
			if (filter.get(key) != null)
				pass.add(filter.get(key));
		}
		
		// Return generated element
		return pass.toArray(new String[0]);
	}
	// End of getSongArray Method
	
	
	/**
	 * Will append songs from a specific album to an existing list.  This can
	 * in turn be used to add them to current play lists.  This element will
	 * sort using the <i>Track</i> element, so that the album is returned 
	 * properly in the sequence intended.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param album String containing album search key.
	 */
	public synchronized void getSongAlbum(List<String> store, String album) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				 "WHERE Album=? " +
			  "ORDER BY CAST(Track AS INTEGER)",
				 
			album
		  );
	}
	// End of getSongAlbum Method
	
	
	/**
	 * Will append songs from a specific album to an existing list.  This can
	 * in turn be used to add them to current play lists.  This element will
	 * sort using the <i>Track</i> element, so that the album is returned 
	 * properly in the sequence intended.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param filter Map containing filter elements.
	 */
	public synchronized void getSongAlbum(List<String> store, Map<String,String> filter) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				  getSongFilter(filter) +
			  "ORDER BY CAST(Track AS INTEGER)",
			
			  // Pass on the filtered set
			getSongArray(filter)
		  );
	}
	// End of getSongAlbum Method
	
	
	/**
	 * Extracts the Album Art cover information from the database.   If
	 * this information exists, a cached version will be employed.
	 * <p>
	 * @param artist String containing artist name.
	 * @param album String containing album name.
	 * @return String containing album art name cache.
	 */
	public synchronized String getSongArt(String artist, String album) {
		
		// Collect the songs returned from the database
		return Db.getString(
				"SELECT Art " +
				  "FROM Host.Song " +
				 "WHERE Artist=? " +
				   "AND Album=? " +
			  "ORDER BY Album, CAST(Track AS INTEGER)",
				 
			artist,
			album
			
		  );
	}
	// End of getSongArt Method
	
	
	/**
	 * Extracts the Album Art cover information from the database.   If
	 * this information exists, a cached version will be employed.
	 * <p>
	 * @param file String containing file location.
	 * @return String containing album art name cache.
	 */
	public synchronized String getSongArt(String file) {
		
		// Collect the songs returned from the database
		return Db.getString(
				"SELECT Art " +
				  "FROM Host.Song " +
				 "WHERE File=? " +
			  "ORDER BY Album, CAST(Track AS INTEGER)",
				 
			file
		  );
	}
	// End of getSongArt Method
	
	
	/**
	 * Will append songs from a specific artist to an existing list.  This can
	 * in turn be used to add them to current play lists.  In this case we will
	 * sort by two criteria which are the <i>Album</i> and <i>Track</i>. 
	 * Sorting by <i>Title</i> can sometimes lead to the same song being played
	 * multiple times and this will not only prevent that but allow a collection
	 * to be played in their entirety based on album.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param artist String containing artist search key.
	 */
	public synchronized void getSongArtist(List<String> store, String artist) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				 "WHERE Artist=? " +
			  "ORDER BY Album, CAST(Track AS INTEGER)",
				 
			artist
		  );
	}
	// End of getSongArtist Method
	
	
	/**
	 * Will append songs from a specific artist to an existing list.  This can
	 * in turn be used to add them to current play lists.  In this case we will
	 * sort by two criteria which are the <i>Album</i> and <i>Track</i>. 
	 * Sorting by <i>Title</i> can sometimes lead to the same song being played
	 * multiple times and this will not only prevent that but allow a collection
	 * to be played in their entirety based on album.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param filter Map containing filtering elements.
	 */
	public synchronized void getSongArtist(List<String> store, Map<String,String> filter) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				  getSongFilter(filter) +
			  "ORDER BY Album, CAST(Track AS INTEGER)",
				 
			// Pass on array of elements
			getSongArray(filter)
		  );
	}
	// End of getSongArtist Method
	
	
	/**
	 * Will append songs from a specific genre to an existing list.  This can
	 * in turn be used to add them to current play lists.  We will sort these
	 * elements by <i>Title</i> since no other criteria will really work.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param genre String containing genre search key.
	 */
	public synchronized void getSongGenre(List<String> store, String genre) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				 "WHERE Genre=? " +
			  "ORDER BY Title",
				 
			genre
		  );
	}
	// End of getSongGenre Method
	
	
	/**
	 * Will append songs from a specific genre to an existing list.  This can
	 * in turn be used to add them to current play lists.  We will sort these
	 * elements by <i>Title</i> since no other criteria will really work.
	 * <p>
	 * @param store Collections list containing songs.
	 * @param filter Map containing filter elements.
	 */
	public synchronized void getSongGenre(List<String> store, Map<String,String> filter) {
		
		// Collect the songs returned from the database
		Db.getColumn(store,
				"SELECT File " +
				  "FROM Host.Song " +
				  getSongFilter(filter) +
			  "ORDER BY Title",
				 
			// Pass on array
			getSongArray(filter)
		  );
	}
	// End of getSongGenre Method
	
	
	/*
	 * INSERT ELEMENTS
	 * 
	 * As the above implies here all INSERT operations are grouped.  This will
	 * permit transaction based operations.
	 */

	/**
	 * Inserts an entry into the audit table for the database.   
	 * <p>
	 * @param action String containing action identifier.
	 * @param filenames Filenames of the songs affected.
	 */
	public synchronized void insertAudit(String action, String...filenames) {
		
		// Loop through and complete
		for (String filename: filenames) {

			/*
			 * Simple insert instructions.  The date and time element will be
			 * appended directly using a native SQLite feature.  Since this is
			 * not server based, really anything would do.
			 */
			Db.updateMultipleRows(
					"INSERT INTO host.Audit " +
					       "(File, Action, Stamp) " +
					"VALUES (?,?,DATETIME('NOW'))", 
					
					filename,
					action
				  ); 
		}
		
		// Complete updates
		Db.updateComplete();
	}
	// End of insertAudit Method
	
	
	/**
	 * Album art is a component of the player which requires external
	 * information.  This update entry allows us to track the base 
	 * filename.
	 * <p>
	 * @param artist String containing the artist name.
	 * @param album String containing the album name.
	 * @param dest Destination album art image filename.
	 * @return boolean indicating success of operation.
	 */
	public synchronized boolean insertCache(String artist, String album, String dest) {
		
		// Straight pass through
		return Db.updateSingleRows(
					"UPDATE host.Song " +
                       "SET Art=? " +
                     "WHERE Artist=? " +
                       "AND Album=?", 
					
					dest,
					artist,
					album
				  ); 
	}
	// End of insertCache Method
	
	
	/**
	 * Album art is a component of the player which requires external
	 * information.  This update will revert the cache to an empty value.
	 * <p>
	 * @param artist String containing the artist name.
	 * @param album String containing the album name.
	 * @return boolean indicating success of operation.
	 */
	public synchronized boolean insertCache(String artist, String album) {
		
		// Straight pass through
		return Db.updateSingleRows(
					"UPDATE host.Song " +
                       "SET Art=NULL " +
                     "WHERE Artist=? " +
                       "AND Album=?", 
					
 					artist,
					album
				  ); 
	}
	// End of insertCache Method
	
	
	/**
	 * Inserts a Host into the database.  Typically used during initial use to
	 * establish a base host for use.
	 * <p>
	 * @param identifier String identifier for the host.
	 * @param host String containing host name or IP address.
	 * @param port String containing port number.
	 * @param password String containing password if necessary.
	 * @param filename String containing filename entry.
	 * @return boolean indicating success in operation
	 */
	public synchronized boolean insertHost(String identifier, String host, String port, String password, String filename) {

		/*
		 * This is one of the few instances where we will make use of the
		 * INSERT OR REPLACE statement which makes it less critical to track
		 * if an entry already exists.
		 */
		if (password != null) {
			
			// Here we account for the password entry
			return Db.updateSingleRows(
					"INSERT OR REPLACE INTO Main.Host " +
					       "(Host, Port, Identifier, Password, Filename) " +
					"VALUES (?,?,?,?,?)",
					
				host,
				port,
				identifier,
				password,
				filename
			  );
		}
		
		else {
			
			// Here we ignore the password entry
			return Db.updateSingleRows(
					"INSERT OR REPLACE INTO Main.Host " +
				       	   "(Host, Port, Identifier, Filename) " +
					"VALUES (?,?,?,?)",
					
				host,
				port,
				identifier,
				filename
			  );
		}
	}
	// End of insertHost Method
	
	/**
	 * Deals with mass insertions that can be associated with the the
	 * synch process of the database with the server.
	 * <p>
	 * @param inserts List of Maps containing new rows to insert.
	 */
	public synchronized void insertSongs(List<Map<String,String>> inserts) {

		// Loop through all new rows to insert
		INSERT: while (inserts.size() > 0) {
			
			// Extract first element
			Map<String,String> song = inserts.remove(0);
			
			// Quick store array list
			List<String> pass = new LinkedList<String>();
			
			/*
			 * Safety check.  We have to make sure that all the elements the
			 * database is looking for actually exist.  So we run a very quick
			 * check.
			 */
			for (String key: Terminus.ROW_REQUIRED.descendingSet())
				if (!song.containsKey(key)) continue INSERT;
			
			/*
			 * Start to generate the query.  Queries are custom based on the
			 * rows within the Map itself.   This means every insert is
			 * different but will not contain rows that are not necessary.
			 */
			StringBuffer sb = new StringBuffer();
			
			// Start Line
			sb.append("INSERT INTO Host.Song (");
			
			// Loop and add in column names
			for (String key: song.keySet()) {
				
				// Quick check to ensure that the elements are valid
				if (!Terminus.ROW_ALLOW.contains(key.toUpperCase()))
					continue;
				
				// Append if we make it this far
				sb.append(key + ",");
				
				// Pass into array
				pass.add(song.get(key));
			}
			
			// Remove the last comma
			sb.setLength(sb.length() - 1);
			
			
			// Middle part
			sb.append(") VALUES (");
			
			
			// Loop and add in place holders
			for (int count=0; count < pass.size(); count++) {
				// Comma addition
				if (count > 0)
					sb.append(",");
				
				// Place holder addition
				sb.append("?");
			}
			
			// Finish up query
			sb.append(")");
			

			/*
			 * Time to pass on the generated query and insert in the new
			 * information.
			 */
			Db.updateMultipleRows(sb.toString(), pass.toArray(new String[0]));
		}
		
		// Finish up the transaction
		Db.updateComplete();
	}
	// End of insertSong Method

	
	/*
	 * UPDATE ELEMENTS
	 * 
	 * As the above implies here all UPDATE operations are grouped.  This will
	 * permit transaction based operations.
	 */
	
	/**
	 * This method is a variant of the insertSongs method handling the 
	 * intricacies of updates versus inserts.  So overall the function
	 * is a bit simpler other then the handling of the UNIQUE identifier
	 * of a song.
	 * <p>
	 * @param updates A List of Map entries which contains every row
	 * that must be modified.  We do not care about comparing differences
	 * and simply replace the entire rows details.
	 */
	public synchronized void updateSongs(List<Map<String,String>> updates) {

		// Loop through all new rows to insert
		UPDATE: while (updates.size() > 0) {
			
			// Extract first element
			Map<String,String> song = updates.remove(0);
			
			// Quick store array list
			List<String> pass = new LinkedList<String>();
			
			/*
			 * Safety check.  We have to make sure that all the elements the
			 * database is looking for actually exist.  So we run a very quick
			 * check.
			 */
			for (String key: Terminus.ROW_REQUIRED.descendingSet())
				if (!song.containsKey(key)) continue UPDATE;
			
			/*
			 * Start to generate the query.  Queries are custom based on the
			 * rows within the Map itself.   This means every insert is
			 * different but will not contain rows that are not necessary.
			 */
			StringBuffer sb = new StringBuffer();
			
			// Start Line
			sb.append("UPDATE Host.Song SET ");
			
			// Loop and add in column names
			for (String key: song.keySet()) {
				
				// Always skip this element we used it later
				if (key.equals(Terminus.UNIQUE))
					continue;
				
				// Quick check to ensure that the elements are valid
				if (!Terminus.ROW_ALLOW.contains(key.toUpperCase()))
					continue;
				
				// Append if we make it this far
				sb.append(key + "=?,");
				
				// Pass into array
				pass.add(song.get(key));
			}
			
			// Remove the last comma
			sb.setLength(sb.length() - 1);
			
			
			// Insert the last component
			sb.append(" WHERE File=?");
			
			// Throw in the last element as a parameter
			pass.add(song.get(Terminus.UNIQUE));
				
			
			/*
			 * Time to pass on the generated query and insert in the new
			 * information.
			 */
			Db.updateMultipleRows(sb.toString(), pass.toArray(new String[0]));
		}
		
		// Finish up the transaction
		Db.updateComplete();
	}
	// End of updateSong Method
	

	/**
	 * Ratings are the only element of a song displayed that can be changed
	 * due to the source of the songs themselves.  Since rating is not part of
	 * the Music Player Daemon (MPD) data set we are free to play with it as
	 * need be.
	 * <p>
	 * @param file String name of the file name.
	 * @param rating String representing rating number.
	 */
	public synchronized void updateSongRating(String file, String rating) {
		
		// Insert the playlist entry first
		Db.updateSingleRows(
			"UPDATE host.Song " +
			   "SET Rating=? " +
			 "WHERE File=?", 
			
		  rating,
		  file
		);
	}
	// End of updateSongRating Method
	
	
	/**
	 * Statistics are a major component of how we do business.  This will 
	 * update the database with the server and skip the synch process in
	 * the future.
	 * <p>
	 * @param keys Keys to update on the database side.
	 * @param values Value Map to used to update.
	 */
	public synchronized void updateHostStatistics(Set<String> keys, Map<String,String> values) {
		
		// Loop through and delete all elements
		for (String key: keys) {
			
			// No need to generate a complex element
			Db.updateMultipleRows(
					"UPDATE Host.Statistic " +
					   "SET Value=? " +
					 "WHERE Key=?",
					 
					  values.get(key),
					  key
					);
		}
		
		// Commit changes
		Db.updateComplete();
	}
	// End of updateHostStatistics Method
	

	/**
	 * Will update the settings to include new state information.  The
	 * operation is a transaction and will replace all rows regardless of
	 * any real changes.
	 * <p>
	 * @param settings String Map containing values to store.
	 * @param filter String containing pre-check to limit update to necessary group.
	 */
	public synchronized void updateSettings(Map<String,String> settings, String filter) {
		
		// Loop through every entry and manage
		for (String key: settings.keySet()) {
			
			// An element in our group skip
			if (!key.startsWith(filter)) continue;
			
			// We will make use of transactions for speed
			Db.updateMultipleRows(
					"UPDATE Main.Setting " +
					   "SET Value=? " +
					 "WHERE Tag=?",
				
				settings.get(key),
				key
			  );
		}
		
		// We close the transaction and commit
		Db.updateComplete();
	}
	// End of updateSettings Method

	
	/**
	 * Will update the settings to include new state information.  The
	 * operation is a transaction and will replace all rows regardless of
	 * any real changes.
	 * <p>
	 * @param settings String Map containing values to store.
	 */
	public synchronized void updateSettings(Map<String,String> settings) {
		
		// Loop through every entry and manage
		for (String key: settings.keySet()) {
			
			// We will make use of transactions for speed
			Db.updateMultipleRows(
					"UPDATE Main.Setting " +
					   "SET Value=? " +
					 "WHERE Tag=?",
				
				settings.get(key),
				key
			  );
		}
		
		// We close the transaction and commit
		Db.updateComplete();
	}
	// End of updateSettings Method
	
	
	/**
	 * This update can only be done one way, since the interface that uses it
	 * will only disable the prompt. 
	 */
	public synchronized void updatePrompt() {
		
		// Nothing over complicated in this element
		Db.updateSingleRows(
				"UPDATE Main.Setting " +
				   "SET Value=? " +
				 "WHERE Tag=?",
				 
				 "false",
				 "State.Close.Prompt"
			);
	}
	// End of updatePrompt Method
	
	
	/*
	 * DELETE ELEMENTS
	 * 
	 * As the above implies here all DELETE operations are grouped.  This will
	 * permit transaction based operations.
	 */
	
	/**
	 * Method which deals with the removing superfluous rows from the song
	 * library.  While we could create a large query which would fit all the
	 * rows in an IN statement, transactions seem to lend themselves better
	 * for overall code simplicity and potential overflow in the entries 
	 * number in abnormally large numbers.
	 * <p>
	 * @param deletes Set containing all of the remaining keys.
	 */
	public synchronized void deleteSongs(Set<String> deletes) {
		
		// Loop through and delete all elements
		for (String key: deletes) {
			
			// No need to generate a complex element
			Db.updateMultipleRows(
					"DELETE FROM Host.Song " +
					 "WHERE File=?", key);
		}
		
		// Commit changes
		Db.updateComplete();
	}
	// End of deleteSongs Method
	
	
	/**
	 * Removes a host from the database.
	 * <p>
	 * @param ident Host identifier to remove.
	 * @return boolean indicating success of operation.
	 */
	public synchronized boolean deleteHost(String ident) {
		
		// Remove the selected host
		return Db.updateSingleRows(
				"DELETE FROM main.Host " +
				"WHERE Identifier=?", 

			ident
		  );		
	}
	// End of deleteHost Method
	
	
	/**
	 * Removes all hosts from the database.   This clearance is typically only
	 * done after the singular host entry in the database failed connection and
	 * the user wishes to try again.
	 */
	public synchronized void deleteHosts() {
		
		// Remove all hosts in one query
		Db.updateSingleRows("DELETE FROM main.Host");
	}
	// End of deleteHosts Method
}