/***************************************************************************
    Copyright          : (C) 2002 by Neoworks Limited. All rights reserved
    URL                : http://www.neoworks.com
 ***************************************************************************/
/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/

/*
 * TrackStore.java
 *
 * Created on 27 March 2002, 14:19
 */

package com.neoworks.jukex.sqlimpl;

import java.net.*;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;

import java.sql.*;

import com.neoworks.jukex.*;

import com.neoworks.connectionpool.PoolManager;

import com.neoworks.util.HashMapMultiMap;
import com.neoworks.util.MultiMap;

import org.apache.log4j.Category;

/**
 * The JukeXTrackStore using a MySQL back end.  This is where all core objects
 * should be obtained from.
 *
 * @author Nick Vincent <a href="mailto:nick@neoworks.com">nick@neoworks.com</a>
 */
public class JukeXTrackStore implements TrackStore
{
	private static final Category log = Category.getInstance(JukeXTrackStore.class.getName());
	private static final boolean logDebugEnabled = log.isDebugEnabled();
	private static final boolean logInfoEnabled = log.isInfoEnabled();

	/**
	 * Name of the database to use
	 */
	public static final String DB_NAME = "jukex";
	
	private static TrackStore _instance = null;			// Singleton instance
	private static PoolManager _poolmanager = null;
	
	private static Map _playlists = null;
	private static Map _attributes = null;
	private static Map _tracksByURL = null;
	private static Map _tracksByID = null;
	private static long[] _trackIds = null;
	
	private static String INSERT_ATTRIBUTE_SQL = "INSERT INTO Attribute ( name, type) VALUES ( ? , ? )";
	private static String RETRIEVE_ATTRIBUTE_SQL = "SELECT id, name, type FROM Attribute WHERE name=? AND type=?";

	/**
	 * Get an instance of the TrackStore
	 *
	 * @return A TrackStore instance
	 */
	public synchronized static TrackStore getInstance()
	{
		if ( _instance == null )
		{
			_instance = new JukeXTrackStore();
			((JukeXTrackStore)_instance).initialise();
		}
		return _instance;
	}
	
	/**
	 * Private consrtuctor
	 */
	private JukeXTrackStore()
	{
		_poolmanager = PoolManager.getInstance();
		
		// TODO: Read the playlists in from the database on startup
		_playlists = new HashMap();
		_attributes = new HashMap();
		_tracksByURL = new HashMap();
		_tracksByID = new HashMap();
	}

	/**
	 * Initialise track store
	 */
	public void initialise()
	{
		loadPlaylists();
	}

	/**
	 * Get the total track count
	 *
	 * @return The total number of tracks in the database
	 */
	public long getTrackCount()
	{
		long retVal = -1;
		Connection conn = null;
		try
		{
			conn = _poolmanager.getConnection( DB_NAME );
			PreparedStatement ps = conn.prepareStatement("SELECT count(*) FROM Track");
			ResultSet rs = ps.executeQuery();
			if (rs.next())
			{
				retVal = rs.getLong(1);
			}
		} catch ( Exception e ) {
			log.warn( "An exception was encountered whilst trying to count the number of tracks" , e );
		} finally {
			try { conn.close(); } catch (Exception ignore) {}
		}
		return retVal;
	}

	/**
	 * Get a track from the database by URL
	 *
	 * @param url The URL of the track
	 * @return A Track object corresponding to the given URL
	 */
	public Track getTrack( URL url )
	{		
		Connection conn = null;
		Track newTrack = (Track) _tracksByURL.get( url );
		
		if ( newTrack == null )
		{
			try
			{
				conn = _poolmanager.getConnection( DB_NAME );
				PreparedStatement ps = conn.prepareStatement( "SELECT id,updated FROM Track WHERE url=?" );
				ps.setString( 1 , url.toString() );
				ResultSet rs = ps.executeQuery();

				if ( rs.next() )		// Move to the first record if possible
				{
					newTrack = new JukeXTrack( rs.getLong( 1 ) , url , new java.util.Date( rs.getLong( 2 ) ) );
					cacheTrack( newTrack );
				}
				
				ps.close();
			}
			catch ( Exception e )
			{
				log.warn( "An exception was encountered whilst trying to retrieve a track with the URL ["+url+"]" , e );
			}
			finally
			{
				try { conn.close(); } catch (Exception ignore) {}
			}
		}
		return newTrack;
	}

	/**
	 * Package private method that allows other like minded classes to see if the
	 * Track already exists in the cache.
	 */
	Track getCachedTrack( long id )
	{
		return (Track) _tracksByID.get( new Long( id ) );
	}
	
	/** 
	 * Add a Track to the cache.
	 */
	void cacheTrack( Track track )
	{
		_tracksByID.put( new Long(((DatabaseObject)track).getId()) , track );
		_tracksByURL.put( track.getURL() , track );
	}
	
	/**
	 * Get a track from the database by database id
	 *
	 * @param id The id of the track
	 * @return The Track corresponding to the id given
	 */
	public Track getTrack( long id )
	{
		
		Connection conn = null;
		Track newTrack = (Track) _tracksByID.get( new Long( id ) );
		
		if ( newTrack == null )
		{
			try
			{
				conn = _poolmanager.getConnection( DB_NAME );
				PreparedStatement ps = conn.prepareStatement( "SELECT url,updated FROM Track WHERE id=?" );
				ps.setLong( 1 , id );
				ResultSet rs = ps.executeQuery();

				if ( rs.next() )		// Move to the first record if possible
				{
					URL newurl = new URL( rs.getString( 1 ) );
					newTrack = new JukeXTrack( id , newurl , new java.util.Date( rs.getLong( 2 ) ) );
					cacheTrack( newTrack );
				}
				
				ps.close();
			}
			catch ( Exception e )
			{
				log.warn( "An exception was encountered whilst trying to retrieve a track with id: "+id , e );
			}
			finally
			{
				try { conn.close(); } catch (Exception ignore) {}
			}
		}
		
		return newTrack;
	}
	
	/**
	 * Retrieve a <code>List</code> of <code>Track</code> objects with ids as
	 * specified in the passed array
	 *
	 * @param ids The array of ids of Tracks to retrieve
	 * @return A <code>List</code> of <code>Track</code> objects
	 */
	public List getTracks( long[] ids )
	{
		Connection conn = null;
		List resultList = new ArrayList( ids.length );
				
		for (int y=0 ; y < ids.length ; y++ )
		{
			Long currID = new Long( ids[y] );
			Track currTrack = (Track)_tracksByID.get( currID );
			if ( currTrack != null )
			{
				resultList.add( y , currTrack );
			}
			else
			{
				currTrack = this.getTrack( ids[y] );
				if ( currTrack != null )
				{
					resultList.add( y , currTrack );
				}
				else
				{
					log.warn( "Could not retrieve all tracks specified in a getTracks() call.  Track "+currID+" could not be found" );
					resultList.add( y , null );
				}
			}
		}
		return resultList;
	}

	/**
	 * Get a List of Tracks corresponding to a list of database ids
	 *
	 * @param ids A List of database track ids
	 * @return A List of the tracks corresponding to the ids passed in
	 */
	public List getTracks(List ids)
	{
		long [] newlist = new long[ ids.size() ];
		Iterator i = ids.iterator();
		
		int counter = 0;
		while ( i.hasNext() )
		{
			newlist[counter++] = ( (Long) i.next() ).longValue();
		}
		return getTracks( newlist );
	}

	public Track storeTrack( URL url , java.util.Date modifiedTime )
	{
		Connection conn = null;
		Track newTrack = null;
		
		try
		{
			conn = _poolmanager.getConnection( DB_NAME );
			PreparedStatement ps = conn.prepareStatement( "INSERT INTO Track ( url , updated ) VALUES ( ? , ? )" );
			ps.setString( 1 , url.toString() );
			ps.setLong( 2 , modifiedTime.getTime() );
			ps.executeUpdate();
			
			ResultSet id = conn.createStatement().executeQuery( "SELECT LAST_INSERT_ID()" );
			if ( !id.next() )
			{
				log.fatal("Something went really badly wrong whilst trying to store a track.  Could not fetch the LAST_INSERT_ID().");
			}
						
			newTrack = new JukeXTrack( id.getLong( 1 ) , url , modifiedTime );
			
			ps.close();
		}
		catch ( Exception e )
		{
			log.warn( "Exception encountered whilst storing track with url ["+url+"]" , e );
		}
		finally
		{
			try { conn.close(); } catch ( Exception ignore ) {}
		}
		
		return newTrack;
	}
    
	public Attribute getAttribute(String name)
	{
		Attribute retval = (Attribute) this._attributes.get( name );
		
		if ( retval == null )
		{
			Connection conn = null;
			try
			{
				conn = _poolmanager.getConnection( DB_NAME );
				PreparedStatement ps = conn.prepareStatement( "SELECT id,name,type FROM Attribute WHERE name=?" );
				ps.setString( 1 , name );
				ResultSet rs = ps.executeQuery();
				if (rs.next())
				{
					retval = new JukeXAttribute( rs.getInt(1) , rs.getString(2) , rs.getInt(3) );
					this._attributes.put( name , retval );
				}
				ps.close();
			}
			catch (SQLException se)
			{
				log.warn( "Encountered an SQL error attempting to retrieve an attribute" , se );
			}
			finally
			{
				try { conn.close(); } catch (SQLException se) {}
			}
		}
					
		return retval;
    }
    
	/**
	 * Get a list of all the attributes currently supported by the system
	 *
	 * @return The <code>Set</code> of all <code>Attribute</code> objects 
	 * currently available in the database.
	 */
    public Set getAttributes()
    {
		// Fetch all the Attributes from the database and make a nice list of Attribute objects for them
		Set results = new HashSet();
		
		Connection conn = null;
		try
		{
			conn = _poolmanager.getConnection( DB_NAME );
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery( "SELECT name FROM Attribute" );
			while( rs.next() )
			{
				String name = rs.getString( 1 );
				results.add( getAttribute( name ) );
			}
			st.close();
		}
		catch ( SQLException se )
		{
			log.warn( "Encountered an exception whilst fetching attributes from the database" , se );
		}
		finally
		{
			try { conn.close(); } catch ( SQLException se ) {}
		}
		
		return results;
    }
	
	/**
	 * Create a new attribute for data to support in the jukebox schema
	 *
	 * @param name The name of the Attribute
	 * @param type The type of the Attribute
	 * @exception Exception
	 */
	public Attribute createAttribute(String name, int type) throws Exception
	{
		if	( 
			name.indexOf( ' ' ) != -1 ||
			name.indexOf( '\n' ) != -1 ||
			name.indexOf( '\r' ) != -1 ||
			name.indexOf( '\t' ) != -1 
			)
		{
			throw new Exception("Attribute names cannot contain whitespace characters");
		}
		
		// Make a new attribute, add it to the database and return...
		Connection conn = null;
		int newAttributeID = 0;
		try
		{
			conn = _poolmanager.getConnection( DB_NAME );
			
			if ( getAttribute( name ) != null )
			{
				if (logDebugEnabled) log.debug( "Skipping duplicate addition of attribute ["+name+"]" );
				return getAttribute( name );
			}
			else
			{
				// Add a record to the database
				PreparedStatement ps = conn.prepareStatement( INSERT_ATTRIBUTE_SQL );
				ps.setString( 1 , name );
				ps.setInt( 2 , type );
				ps.executeUpdate();

				// Retrieve it's autonumbered ID
				PreparedStatement ps2 = conn.prepareStatement( RETRIEVE_ATTRIBUTE_SQL );
				ps2.setString( 1 , name );
				ps2.setInt( 2 , type );
				ResultSet rs = ps2.executeQuery();
				rs.next();

				newAttributeID = rs.getInt( 1 );
				
				ps.close();
				ps2.close();
			}
		}
		catch ( SQLException se )
		{
			log.warn( "Encountered an exception whilst creating an attribute in the database" , se );
		}
		finally
		{
			try { conn.close(); } catch ( SQLException se ) {}
		}
		
		Attribute newAttribute = new JukeXAttribute( newAttributeID , name , type );
		this._attributes.put( name , newAttribute );
		return newAttribute;
	}

	/**
	 * Get the name of the database the TrackStore is using
	 *
	 * @return The name of the database
	 */
	public String getDBName()
	{
		return DB_NAME;
	}

	public long[] getTrackIds()
	{
		Connection conn = null;
		if (_trackIds == null || _trackIds.length != getTrackCount())
		{
			_trackIds = new long[(int)getTrackCount()];
		} else {
			return _trackIds;
		}

		try
		{
			conn = _poolmanager.getConnection( this.DB_NAME );
			PreparedStatement ps = conn.prepareStatement( "SELECT id FROM Track" );
			
			ResultSet rs = ps.executeQuery();
			int i = 0;
			while ( rs.next() )
			{
				_trackIds[i++] = rs.getLong(1);
			}
			
			ps.close();
		}
		catch ( SQLException se )
		{
			log.warn( "Encountered an exception whilst getting track ids from the database" , se );
		}
		finally 
		{
			try { conn.close(); } catch ( SQLException ignore ) { ignore.printStackTrace(System.err); }
		}
		return _trackIds;
	}

	/**
	 * Get the Playlist with the specified name
	 *
	 * @param name The name of the playlist required
	 * @return The Playlist corresponding to the name given
	 */
	public Playlist getPlaylist( String name )
	{
		Connection conn = null;
		Playlist retval = null;
		
		retval = (Playlist) _playlists.get( name );
		if ( retval != null ) return retval;
		
		try
		{
			conn = _poolmanager.getConnection( this.DB_NAME );
			PreparedStatement ps = conn.prepareStatement( "SELECT id FROM Playlist WHERE name=?" );
			ps.setString( 1 , name );
			
			ResultSet rs = ps.executeQuery();
			if ( rs.next() )
			{
				retval = new JukeXPlaylist( name , rs.getLong( 1 ) );
			}
			
			ps.close();
		}
		catch ( SQLException se )
		{
			log.warn( "Encountered an exception whilst getting a playlist from the database" , se );
		}
		finally 
		{
			try { conn.close(); } catch ( SQLException ignore ) { ignore.printStackTrace(System.err); }
		}
		
		_playlists.put( name , retval );
		
		return retval;
	}
	
	/**
	 * Create a playlist with the specified name
	 */
	public Playlist createPlaylist( String name )
	{
		if ( name == null || name.length() == 0 ) return null;
		
		Playlist retval = this.getPlaylist( name );
		if ( retval != null )
		{
			return retval;
		}
		
		Connection conn = null;
		try
		{
			conn = _poolmanager.getConnection( this.DB_NAME );
			PreparedStatement ps = conn.prepareStatement( "INSERT INTO Playlist (name) VALUES ( ? )" );
			ps.setString( 1 , name );
			ps.executeUpdate();
			
			ResultSet rs = conn.createStatement().executeQuery( "SELECT LAST_INSERT_ID()" );
			if ( rs.next() ) 
			{
				long newid = rs.getLong( 1 );
				retval = new JukeXPlaylist( name , newid );
			}
			
			ps.close();
		}
		catch ( SQLException se )
		{
			log.warn( "Failed due to an Exception whilst creating a playlist" , se );
		}
		finally
		{
			try { conn.close(); } catch ( Exception e ) { }
		}
		
		_playlists.put( name , retval );
		
		return retval;
	}

	/**
	 * Get a Collection of all Playlists in the database
	 *
	 * @return A Collection of Playlists
	 */
	public Collection getAllPlaylists()
	{
		return _playlists.values();
	}
	
	private void loadPlaylists()
	{
		if (logDebugEnabled) log.debug( "Loading playlists from database..." );
		
		Connection conn = null;
		try
		{
			conn = _poolmanager.getConnection( this.DB_NAME );
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery( "SELECT id,name FROM Playlist" );
			while ( rs.next() )
			{
				//System.out.println( "Fetching Playlist: "+rs.getString( 2 )+" using connection "+conn );
				this.getPlaylist( rs.getString( 2 ) );		// Just throw away the return value.  Calling throws method puts the playlists in the cache anyway.
			}
			st.close();
		}
		catch ( SQLException se )
		{
			log.warn( "Encountered an exception whilst reading the playlists from the database" , se );
		}
		finally 
		{
			try { conn.close(); } catch ( Exception e ) { }
		}
	}
	
	/**
	 * Return a batch track loader
	 */
	public BatchTrackLoader getBatchTrackLoader()
	{
		return new JukeXTrackLoader();
	}
	
	private class JukeXTrackLoader implements BatchTrackLoader
	{
		List ids = null;
		
		public JukeXTrackLoader()
		{
			this.ids = new LinkedList();
		}
		
		/**
		 * Add a trackID to this batch for retrieval.
		 *
		 * Tracks are retrieved in the order in which the IDs are added.
		 *
		 * @param id The ID to add to the batch.
		 */
		public void addTrack(long id)
		{
			this.ids.add( new Long(id) );
		}
		
		/**
		 * Add all of the IDs from this list to the batch for retrieval
		 *
		 * The List specified must contain Long objects.  Any other objects will be
		 * ignored.
		 *
		 * @param idlist A <code>List</code> of <code>Long</code> objects representing
		 * the IDs of Tracks to add to the batch.
		 */
		public void addTracks(List idlist)
		{
			Iterator i = idlist.iterator();
			while ( i.hasNext() )
			{
				Object curr = i.next();
				if ( curr instanceof Long )
				{
					this.ids.add( (Long) curr );
				}
			}
		}
		
		/**
		 * Retrieve the tracks with the ids that this loader has been told about.
		 *
		 * The function returns a <code>Map</code> of <code>Track</code> objects
		 * keyed by Track ID.  The Track IDs themselves are <code>Long</code>
		 * objects.
		 *
		 * @return A Map keyed by <code>Long</code> objects containing <code>Track</code> objects.
		 */
		public Map getTracks()
		{
			Map retval = new HashMap( this.ids.size() );
			Connection conn = null;
						
			// Note that this SQL has a STRAIGHT_JOIN clause included as the MySQL
			// optimiser get the table ordering wrong and ends up not using an
			// index to join Attribute, which makes the query really very slow.
			//
			// STRAIGHT_JOIN forces MySQL to include Attribute last, and it uses 
			// the correct index in this instance.
			String sql = "SELECT Track.id AS trackid, Track.url AS url, Track.updated AS updated, Attribute.name AS attrname, AttributeValue.numericvalue, AttributeEnum.id, AttributeEnum.value "+
						"FROM "+
						"Track, "+
						"AttributeValue LEFT JOIN AttributeEnum ON AttributeValue.attributeenumid = AttributeEnum.id STRAIGHT_JOIN Attribute "+
						"WHERE "+
						"Track.id = AttributeValue.trackid "+
						"AND AttributeValue.attributeid = Attribute.id "+
						"AND Track.id IN (";
			String endsql = ") ORDER BY trackid";
			
			try
			{
				conn = PoolManager.getInstance().getConnection( JukeXTrackStore.DB_NAME );
				Statement state = conn.createStatement();
				
				// Make a comma separated list for the SQL query
				StringBuffer csList = new StringBuffer();
				int size = this.ids.size();
				Iterator idIter = this.ids.iterator();
				if ( idIter.hasNext() ) csList.append( idIter.next() );
				while ( idIter.hasNext() )
				{
					csList.append( ',' ).append( idIter.next() );
				}
				
				String query = sql+csList.toString()+endsql;
				System.out.println( query );
				ResultSet rs = state.executeQuery( query );
								
				Long lastID = new Long( -1 );
				Long currID = new Long( -1 );
				MultiMap currTrackAttrs = null;
				JukeXTrackStore trackStore = (JukeXTrackStore) TrackStoreFactory.getTrackStore();
				JukeXTrack currTrack = null;
				
				while ( rs.next() )
				{
					lastID = currID;
					currID = new Long( rs.getLong( 1 ) );
					
					// Check if we've changed track
					if (!lastID.equals(currID))
					{
						currTrack = (JukeXTrack) trackStore.getCachedTrack( currID.longValue() );
						if ( currTrack == null)
						{
							// Make a new Track object
							try { currTrack = new JukeXTrack( currID.longValue() , new URL( rs.getString( 2 ) ) , new Date( rs.getLong( 3 ) ) ); } catch ( MalformedURLException mue ) { }
							// Then cache it so this doesn't happen again
							trackStore.cacheTrack( currTrack );
						}
												
						currTrackAttrs = new HashMapMultiMap();
						currTrack.setAttributes( currTrackAttrs );
						retval.put( currID , currTrack );
					}
					
					Attribute attr = trackStore.getAttribute( rs.getString( 4 ) );
					AttributeValue av = null;
					if ( attr.getType() == Attribute.TYPE_STRING )
					{
						av = new JukeXAttributeValue( rs.getLong( 6 ) , attr , rs.getString( 7 ) );
					}
					else	// if ( attr.getType() == AttributeValue.TYPE_INT )
					{
						av = new JukeXAttributeValue( attr , rs.getInt( 5 ) );
					}
					
					currTrackAttrs.put( attr , av );
				}
			}
			catch ( SQLException se )
			{
				log.warn( "Batch getter encountered an exception whilst retrieving tracks" , se );
			}
			finally
			{
				try { conn.close(); } catch ( SQLException ignore ) {}
			}
			
			return retval;
		}
		
		/**
		 * Clear the IDs to retrieve
		 */
		public void reset()
		{
			this.ids = new LinkedList();
		}
		
	}
}
