package com.etretatlogiciels.headfirstsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This Java code is written because I can't figure out how to use SQL statements to
 * massage <i>t_gregslist</i> into these other tables, as illustrated on pages 338
 * and 381, using <code>UPDATE</code> or other SQL statements as if:
 * </p>
 *
 * <pre>
 * UPDATE gregslist
 *   SET zipcode_id =
 *      SELECT zipcode.id
 *         FROM zipcode JOIN gregslist ON zipcode.zipcode = gregslist.zipcode; </pre>
 * <p> ...worked. Run this AFTER launching MySQL and running
 * <i>C:\Users\russ\dev\sql\gregslist.sql</i>.
 * </p>
 *
 * @author Russell Bateman
 */
public class HeadFirstSqlChapter9
{
	private static final String	CONNECTION_URL = "jdbc:mysql://localhost/headfirst_sql?user=root&password=test123";
	private static Connection	conn           = null;

	private static final void startUp()
	{
		try
		{
			Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

			conn = DriverManager.getConnection( CONNECTION_URL );
		}
		catch( IllegalAccessException e )
		{
			System.out.println( "IllegalAccessException" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
		catch( InstantiationException e )
		{
			System.out.println( "InstantiationException" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
		catch( ClassNotFoundException e )
		{
			System.out.println( "ClassNotFoundException" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
		catch( SQLException e )
		{
			System.out.println( "SQLException: getConnection()" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
	}

	/**
	 * S Q L &nbsp; &nbsp;  s t a t e m e n t s
	 * </p>
	 *
	 * <p>
	 * The main table, <i>t_gregslist</i>, has a field, <i>zipcode</i>, out of which we've
	 * already built a new, auxiliary table, <i>t_zipcode</i>. The latter contains a list
	 * of all the discrete zipcodes originally in <i>t_gregslist</i>. This method walks
	 * the main table to fill its (new) field, <i>zipcode_id</i>, with the corresponding
	 * <i>id</id> from <i>t_zipcode</i>. Then, we drop the <i>zipcode</i> column from the
	 * main table since we no longer need it.
	 */
	public static final void fixUpZipcodes()
	{
		try
		{
			String		query     = null;
			String		update    = null;
			ResultSet	gl_result = null;

			Statement	stmt = conn.createStatement();

			/* Walk t_gregslist to fill its zipcode_id field with the corresponding
			 * id from the t_zipcode table.
			 */
			try
			{
				query = "SELECT id, zipcode FROM t_gregslist;";
				System.out.println( query );
				gl_result = stmt.executeQuery( query );

				/* Now result is full of ids and zipcodes. Use them, one by one, to
				 * fill in the gregslist zipcode_id field with the id key out of the
				 * zipcode table.
				 */
				while( gl_result.next() )
				{
					int			gl_id   = gl_result.getInt   ( "id" );
					String		zipcode = gl_result.getString( "zipcode" );
					Statement	stmt_a  = conn.createStatement();

					query = "SELECT id FROM t_zipcode WHERE zipcode = '" + zipcode + "';";
					System.out.println( query );
					ResultSet	zip_result = stmt_a.executeQuery( query );

					if( zip_result.next() )
					{
						/* Alter zipcode_id in t_gregslist, the column we're going to
						 * keep, to hold the id in the new t_zipcode table.
						 */
						int			zid    = zip_result.getInt( "id" );
						Statement	stmt_b = conn.createStatement();

						update = "UPDATE t_gregslist SET zipcode_id = '" + zid + "' WHERE id = '" + gl_id + "';";
						System.out.println( update );
						stmt_b.executeUpdate( update );
					}
				}

				// now drop column zipcode from t_gregslist...
				Statement	stmt_c = conn.createStatement();

				update = "ALTER TABLE t_gregslist DROP column zipcode;";
				System.out.println( update );
				stmt_c.executeUpdate( update );

			}
			catch( SQLException e )
			{
				System.out.println( "SQLException: " + e.getMessage() );
				System.out.println( "SQLState:     " + e.getSQLState() );
				System.out.println( "VendorError:  " + e.getErrorCode() );
				e.printStackTrace();
			}
		}
		catch( Exception e )
		{
			System.out.println( "Holy crap, Batman!" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
	}

	/**
	 * Shortened version of <tt>fixUpTable()</tt> when the table, field and id field names
	 * are purely orthogonal.
	 *
	 * @param whichTable - the auxiliary table (not <i>t_gregslist</i>).
	 * @see <tt>fixUpTable( String whichTable, String whichField )</tt>
	 */
	public static final void fixUpTable( String whichTable )
	{
		String	whichField = whichTable.substring( 2, whichTable.length() );
		fixUpTable( whichTable, whichField, whichField + "_id" );
	}

	/**
	 * P r e p a r e d &nbsp; &nbsp;  s t a t e m e n t s
	 * </p>
	 *
	 * <p>
	 * We use a prepared statement here to do any table/field combination and not
	 * just <i>t_zipcode</i>/<i>zipcode</i> as above.
	 * </p>
	 *
	 * <p>
	 * Nota bene: Prepared statements enclose anything you pass to the pronoun or place-holder
	 * with single quotes. For this reason, you cannot pass
	 * </p>
	 *
	 * <ul>
	 * 	<li> a table name. </li>
	 * 	<li> a column name. </li>
	 *	<li> the column name to drop in an <tt>ALTER</tt> statement. </li>
	 * </ul>
	 *
	 * <p>
	 * Therefore, prepared statements cannot be used to wildcard just everything.
	 * </p>
	 *
	 * @param whichTable - the auxiliary table (not <i>t_gregslist</i>) whose id
	 * 				column will be copied to <i>t_gregslist</i>'s column.
	 * @param whichField - the column in <i>t_gregslist</i> and in the auxiliary
	 * 				table that contains the value that interests us on each row.
	 */
	public static final void fixUpTable( String whichTable, String whichField, String idField )
	{
		String	query = null, update = null;

		try
		{
			ResultSet	gl_result = null;

			/* Walk t_gregslist to fill its whichField with the corresponding
			 * id from the whichTable.
			 */
			try
			{
				Statement	stmt = conn.createStatement();

				query = "SELECT id, " + whichField + " FROM t_gregslist;";
				System.out.println( query );
				gl_result = stmt.executeQuery( query );

				/* Now result is full of ids and whichFields. Use them, one by one, to
				 * fill in the t_gregslist whichField_id with the id key out of the
				 * whichTable.
				 */
				while( gl_result.next() )
				{
					int		gl_id      = gl_result.getInt   ( "id" );
					String	fieldValue = gl_result.getString( whichField );

					query = "SELECT id FROM " + whichTable + " WHERE " + whichField + " = ?";
					PreparedStatement	stmt_a = conn.prepareStatement( query );

					stmt_a.setString( 1, fieldValue );
					System.out.println( "Query statement: " + stmt_a.toString() );
					ResultSet	sub_result = stmt_a.executeQuery();

					if( sub_result.next() )
					{
						int	zid = sub_result.getInt( "id" );

						update = "UPDATE t_gregslist SET " + idField + " = ? WHERE id = ?;";

						PreparedStatement	stmt_b = conn.prepareStatement( update );

						stmt_b.setInt( 1, zid );
						stmt_b.setInt( 2, gl_id );
						System.out.println( "Update statement: " + stmt_b.toString() );
						stmt_b.executeUpdate();
					}
				}

				// now drop column profession from t_gregslist...
				Statement	stmt_c = conn.createStatement();

				update = "ALTER TABLE t_gregslist DROP COLUMN " + whichField + ";";

				System.out.println( update );
				stmt_c.executeUpdate( update );

			}
			catch( SQLException e )
			{
				System.out.println( "SQLException: " + e.getMessage() );
				System.out.println( "SQLState:     " + e.getSQLState() );
				System.out.println( "VendorError:  " + e.getErrorCode() );
				e.printStackTrace();
			}
		}
		catch( Exception e )
		{
			System.out.println( "Holy crap, Batman!" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
	}

	/**
	 * Create an intermediate table that links <i>t_gregslist</i> by
	 * <i>contact_id</i> with <i>t_interests</i> by <i>interest_id</i>.
	 * This intermediate table is called a <i>junction table</i> (see
	 * <i>Head First SQL</i>, page 315). The new table's name will be
	 * <i>t_contact_interest</i>.
	 * </p>
	 *
	 * <p>
	 * Do this also for <i>t_contact_seeking</i>, etc.
	 * </p>
	 *
	 * <p>
	 * See pages 316, 338 and 381.
	 * </p>
	 */
	public static final void linkUpInterests( String junctionTable, String otherTable, String fieldName )
	{
		String	create  = null, query = null, update = null, oldTableField = null;

		if( fieldName == null )
			fieldName = otherTable.substring( 2, otherTable.length() );
		oldTableField = fieldName;
		if( fieldName.equals( "interest" ) )
			oldTableField += "s";

		String	otherId = fieldName + "_id";

		try
		{
			ResultSet	otab_result = null;
			ResultSet	sub_result  = null;

			try
			{
				Statement	stmt = conn.createStatement();

				// create the junction table...
				create = "CREATE TABLE " + junctionTable
							+ "("
							+ " contact_id INTEGER, "
							+  otherId + " INTEGER "
							+ ");";
				System.out.println( create );
				stmt.executeUpdate( create );

				/* Get a list of both the id and important fields in the other table.
				 */
				query = "SELECT id, " + fieldName + " FROM " + otherTable + ";";
				System.out.println( query );
				otab_result = stmt.executeQuery( query );

				/* Now result is full of ids and principal fields (like 'interest').
				 * Use them, one by one, to fill in the junction table's if fields
				 * with the id key out of the other table.
				 */
				while( otab_result.next() )
				{
					int		other_id   = otab_result.getInt   ( "id" );
					String	fieldValue = otab_result.getString( fieldName );

					Statement	stmt_a = conn.createStatement();

					/* Get a list of rows in t_gregslist that share the interest named
					 * in 'fieldValue'.
					 */
					query = "SELECT id FROM t_gregslist WHERE " + oldTableField + " LIKE '%" + fieldValue + "%';";
					System.out.println( query );
					sub_result = stmt_a.executeQuery( query );

					if( sub_result.next() )
					{
						/* Alter the id field in the junction table to hold the id of
						 * the field in question. Also, alter the contact_id field to
						 * refer to t_gregslist.
						 */
						int	gl_id = sub_result.getInt( "id" );

						update = "INSERT INTO " + junctionTable + "( contact_id, " + otherId + " ) VALUES( ?, ? );";

						PreparedStatement	stmt_b = conn.prepareStatement( update );

						stmt_b.setInt( 1, gl_id );
						stmt_b.setInt( 2, other_id );
						System.out.println( "Update statement: " + stmt_b.toString() );
						stmt_b.executeUpdate();
					}
				}

				// now drop column profession from t_gregslist...
				Statement	stmt_c = conn.createStatement();

				update = "ALTER TABLE t_gregslist DROP COLUMN " + oldTableField + ";";

				System.out.println( update );
				stmt_c.executeUpdate( update );

			}
			catch( SQLException e )
			{
				System.out.println( "SQLException: " + e.getMessage() );
				System.out.println( "SQLState:     " + e.getSQLState() );
				System.out.println( "VendorError:  " + e.getErrorCode() );
				e.printStackTrace();
			}
		}
		catch( Exception e )
		{
			System.out.println( "Holy crap, Batman!" );
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
	}

	public static void main( String[] args )
	{
		startUp();
		fixUpZipcodes();
		System.out.println( "---------------------------------------------------------------" );
		fixUpTable( "t_profession", "profession", "prof_id" );
		System.out.println( "---------------------------------------------------------------" );
		fixUpTable( "t_status" );
		System.out.println( "---------------------------------------------------------------" );
		linkUpInterests( "t_contact_interest", "t_interests", "interest" );
		System.out.println( "---------------------------------------------------------------" );
		linkUpInterests( "t_contact_seeking", "t_seeking", null );
	}
}