/******************************************************************************************************
*
* FILE:    	MySQLImport.java
* AUTHOR:  	David Miller  http://www.sqlmagic.com/d/
* ABOUT: 	A demonstration of how to extend com.fivevoltlogic.mytunes.Chord to provide
*			customized functionality.
* DATE:		September 1, 2003
*
******************************************************************************************************/

import com.fivevoltlogic.mytunes.*;
import java.io.IOException;
import java.util.List;
import java.sql.*;

public class MySQLImport extends Chord {

	// sql classes to provide connection, query and 
	private Connection con;
	private PreparedStatement insertTrack,
							  insertPlaylist,
							  insertPlaylistTrack;
	private ResultSet rs;
	private Statement stmt;

	public MySQLImport() throws SQLException, ClassNotFoundException, IllegalAccessException, java.lang.InstantiationException, IOException {

		// will read the values in from ~/.mytunes.xml;
		// these properties will be stored in the props Properties instance
		super();
	
		
		// connect to the database with the appropriate parameters
		Class.forName((String)props.get("mysqlimport.driver")).newInstance();
		this.con = DriverManager.getConnection("jdbc:mysql://" + (String) props.get("mysqlimport.host") + "/" + (String) props.get("mysqlimport.database") + "?user=" + (String) props.get("mysqlimport.user") + "&password=" + (String) props.get("mysqlimport.password"));
		this.stmt = con.createStatement();
	}

	public void onStart() {
		
		try {

			// clear all existing information from the database
			this.stmt.executeUpdate("DELETE FROM Tracks");
			this.stmt.executeUpdate("DELETE FROM Playlists");
			this.stmt.executeUpdate("DELETE FROM PlaylistTracks");			
	
			// prepare statements for queries
			// refer to mytunes.mysql to see the details on the tables
			this.insertTrack = con.prepareStatement("INSERT INTO Tracks VALUES(" + 
					"?, " + 	// 01 trackId 
					"?, " + 	// 02 trackAlbum
					"?, " + 	// 03 trackArtist
					"?, " + 	// 04 trackBitRate
					"?, " + 	// 05 trackComment
					"?, " + 	// 06 trackComposer
					"?, " + 	// 07 trackDateAdded
					"?, " +		// 08 trackDateModified
					"?, " + 	// 09 trackDiscCount
					"?, " + 	// 10 trackDiscNumber
					"?, " + 	// 11 trackDuration
					"?, " + 	// 12 trackEqualizer
					"?, " + 	// 13 trackGenre
					"?, " + 	// 14 trackKind
					"?, " + 	// 15 trackLocation
					"?, " + 	// 16 trackName
					"?, " + 	// 17 trackPlayedCount
					"?, " + 	// 18 trackPlayedDate
					"?, " + 	// 19 trackRating
					"?, " + 	// 20 trackSample
					"?, " + 	// 21 trackSize
					"?, " + 	// 22 trackCount
					"?, " + 	// 23 trackNumber
					"?, " + 	// 24 trackYear
					"'false'" +	// 25 trackCurrent
					")");	

			this.insertPlaylist = con.prepareStatement("INSERT INTO Playlists VALUES("
					"?, " +		// 01 playlistId
					"?, " +		// 02 playlistName
					"?, " +		// 03 playlistSmart
					"'false'" +	// 04 playlistCurrent
					")");		
					
			this.insertPlaylistTrack = con.prepareStatement("INSERT INTO PlaylistTracks VALUES("
					"?, " +		// 01 playlistId
					"?, " +		// 02 trackId
					"? " +		// 03 trackIndex
					")");
			
		} catch (Exception e) { this.onError(e.getMessage()); }

	}
	
	public void onTrack(Track t) {

		try {
			
			// get the information about the track and populate the PreparedStatement with the values
			this.insertTrack.setInt(1, t.getId());
			this.insertTrack.setString(2, t.getAlbum());
			this.insertTrack.setString(3, t.getArtist());
			this.insertTrack.setInt(4, t.getBitRate());
			this.insertTrack.setString(5, t.getComment());
			this.insertTrack.setString(6, t.getComposer());
			
			// convert the XML date format into an SQL format
			if (t.getDateAdded() != null) {
				this.insertTrack.setTimestamp(7, new Timestamp(t.getDateAdded().getTime()));				
			} 

			// convert the XML date format into an SQL format			
			if (t.getDateModified() != null) {
				this.insertTrack.setTimestamp(8, new Timestamp(t.getDateModified().getTime()));			
			} 

			this.insertTrack.setInt(9, t.getDiscCount());
			this.insertTrack.setInt(10, t.getDiscNumber());
			this.insertTrack.setInt(11, t.getDuration());
			this.insertTrack.setString(12, t.getEqualizer());
			this.insertTrack.setString(13, t.getGenre());
			this.insertTrack.setString(14, t.getKind());
			this.insertTrack.setString(15, t.getLocation());
			this.insertTrack.setString(16, t.getName());
			this.insertTrack.setInt(17, t.getPlayedCount());

			// convert the XML date format into an SQL format
			if (t.getPlayedDate() != null) {
				this.insertTrack.setTimestamp(18, new Timestamp(t.getPlayedDate().getTime()));
			} else {
				this.insertTrack.setTimestamp(18, null);
			}
			
			this.insertTrack.setInt(19, t.getRating());
			this.insertTrack.setInt(20, t.getSampleRate());
			this.insertTrack.setInt(21, t.getSize());
			this.insertTrack.setInt(22, t.getTrackCount());
			this.insertTrack.setInt(23, t.getTrackNumber());
			this.insertTrack.setInt(24, t.getYear());


			// execute the query
			insertTrack.executeUpdate();
		
		} catch (SQLException e) { this.onError(e.getMessage()); }
			
	}
	
	public void onPlaylist(Playlist p) {

		try {
		
			// note: Playlist.isSmart() always returns false,
			// as this feature isn't complete yet
			String b = new Boolean(p.isSmart()).toString();			
			insertPlaylist.setInt(1, p.getId());
			insertPlaylist.setString(2, p.getName());

			// as of right now, we don't check to see if a playlist is smart or not,
			// so we'll default to false for now...
			insertPlaylist.setString(3, "false");			

			// execute the query
			insertPlaylist.executeUpdate();

			// get a list of the database ids of all tracks in this playlist,
			List tracks = p.getTracks();
			for (int i = 0; i < tracks.size(); i++) {

				// prepare the statement
				int t = ((Integer)tracks.get(i)).intValue();
				insertPlaylistTrack.setInt(1, p.getId());
				insertPlaylistTrack.setInt(2, t);
				insertPlaylistTrack.setInt(3, i + 1);

				// insert the query
				insertPlaylistTrack.executeUpdate();
			}

		} catch (Exception e) {
			this.onError(e.getMessage()); 
		}
		
	}

	
	// simply echo the error to the screen
	public void onError(String message) {		
		System.out.println("error: " + message);
	}

	
	// close the database connection 
	public void onFinish() {
		try {
			con.close();
		} catch (SQLException e) { this.onError(e.getMessage()); }
	}	


	// if we have a command-line argument, we interpret it to be the path
	// to the user's config file; if there are no command-line arguments,
	// look for the config file at ~/.mytunes.xml
	public static void main(String[] args) {
	
		try {

			if (args.length != 0) {

				System.out.println("Usage: java MySQLImport");
				System.exit(0);			
				
			} else  {

				// create an instance of MyTunesLibrary and set
				// an instance of this class to be the handlers
				MyTunesLibrary lib = new MyTunesLibrary();
				lib.setHandler(new MySQLImport());

				// begin parsing, at which point this class's methods
				// will be invoked as callbacks throughout the process
				lib.parse();				
			} 

		} catch (IOException e) {
			System.out.println("io: " + e.getMessage());
		} catch (SQLException e) {
			System.out.println("sql: " + e.getMessage());
		} catch (org.xml.sax.SAXException e) {
			System.out.println("sax: " + e.getMessage());
		} catch (ClassNotFoundException e) {
			System.out.println("cnf: " + e.getMessage());
		} catch (InstantiationException e) {
			System.out.println("i: " + e.getMessage());
		} catch (IllegalAccessException e) {
			System.out.println("iae: " + e.getMessage());
		}
	}	
}