import java.sql.*;
//import java.sql.Connection;
//import java.sql.ResultSet;
//import java.sql.ResultSetMetaData;
//import java.sql.SQLException;


/**
 * Performs CREATE, INSERT, DELETE, and SELECT on a MySQL database.
 */
public class QueryMySQL 
{
	String sql;
	/**
	 * Connection to database
	 */
	private Connection con;
	
	
	/**
	 * Main method is only used for convenience.  Use JUnit test file to verify your answer.
	 * 
	 * @param args
	 * 		none expected
	 * @throws SQLException
	 * 		if a database error occurs
	 */
	public static void main(String[] args) throws SQLException
	{
		QueryMySQL q = new QueryMySQL();
		q.connect();	
		q.drop();
		q.create();
		q.insert();	
		q.delete();
		System.out.println(QueryMySQL.resultSetToString(q.query1(), 1000));
		System.out.println(QueryMySQL.resultSetToString(q.query2(), 1000));
		System.out.println(QueryMySQL.resultSetToString(q.query3(), 1000));
		q.close();
	}

	/**
	 * Makes a connection to the database and returns connection to caller.
	 * 
	 * @return
	 * 		connection
	 * @throws SQLException
	 * 		if an error occurs
	 */
	public Connection connect() throws SQLException
	{
		// TODO: Fill in the URL, uid, and pw
		String url = "jdbc:mysql://cosc304.ok.ubc.ca/db_skrogfos";
		String uid = "skrogfos";
		String pw = "36601110";
		
		System.out.println("Connecting to database.");
		// Note: Must assign connection to instance variable as well as returning it back to the caller
		// TODO: Make a connection to the database and store connection in con variable before returning it.
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException ex){
			System.err.println("Exception: " + ex);
		}
		Connection con = null;
		try{
			con = DriverManager.getConnection(url,uid,pw);
		}
		catch(SQLException ex) {
			System.err.println("Exception: " + ex);
		}
		return con;
	}
	
	/**
	 * Closes connection to database.
	 */
	public void close()
	{
		System.out.println("Closing database connection.");
		
		// TODO: Close the database connection.  Catch any exception and print out if it occurs.
		if(con!=null){
			try {
				con.close();
			}
			catch(SQLException ex){
				System.err.println("Exception: " + ex);
			}
		}
	}
	
	/**
	 * Drops the table from the database.  If table does not exist, error is ignored.
	 */
	public void drop()
	{
		System.out.println("Dropping table player.");
		
		// TODO: Drop the table player.  Catch any exception and print out if it occurs.
		try{
			sql = "DROP TABLE IF EXISTS player;";
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex);
		}
	}
	
	/**
	 * Creates the table in the database.  Table name: player
	 * Fields:
	 *  - id - integer, must auto-increment
	 *  - name - variable character field up to size 50
	 *  - salary - must hold up to 99,999,999.99 exactly
	 *  - birthdate - date
	 *  - last_update - datetime
	 */
	public void create() throws SQLException
	{
		System.out.println("Creating table player.");
		
		// TODO: Create the table player.
		try{
			sql = "CREATE TABLE IF NOT EXISTS player (" +
					"id int AUTO_INCREMENT," +
					"name varchar(50)," +
					"salary double(10,2)," +
					"birthdate date," +
					"last_update datetime," +
					"PRIMARY KEY (id))";
			Statement stmt = con.createStatement();
			stmt.executeQuery(sql);
		}
		catch (SQLException ex){
			System.err.println("Exception: " + ex);
		}
	}
	
	/**
	 * Inserts the test records in the database.  Must used a PreparedStatement.  
	 * 
	 * Data:
	 * Names = "Peyton Manning", "Aaron Rodgers", "Drew Brees", "Tom Brady", "Matt Ryan"
	 * Salaries = "19200000", "22000000", "20000000", "14120000", "20750000"
	 * Birthdates = "1976-03-04", "1983-12-02", "1979-01-15", "1977-08-03", "1985-05-17"
	 * Last_updates = "2015-01-04 11:30:30", "2015-01-04 12:30:25", "2015-01-04 13:00:45", "2015-01-05 05:00:00", "2015-01-05 23:00:00"
	 */
	public void insert() throws SQLException
	{
		System.out.println("Inserting records.");
		
		// TODO: Insert records using a PreparedStatement.
		try {
			String[][] prepsql = new String[][]{
					{"Peyton Manning", "19200000", "1976-03-04", "2015-01-04 11:30:30"},
					{"Aaron Rodgers", "22000000", "1983-12-02", "2015-01-04 12:30:25"},
					{"Drew Brees", "20000000", "1979-01-15", "2015-01-04 13:00:45"},
					{"Tom Brady", "14120000", "1977-08-03", "2015-01-05 05:00:00"},
					{"Matt Ryan", "20750000", "1985-05-17", "2015-01-05 23:00:00"}
			};
			PreparedStatement stmt = con.prepareStatement("INSERT INTO player (name, salary, birthdate, last_update) VALUES (?,?,?,?)");
			for (String[] str: prepsql){
				stmt.setString(1,str[0]);
				stmt.setString(2,str[1]);
				stmt.setString(3,str[2]);
				stmt.setString(4,str[3]);
				stmt.executeQuery();
			}
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex);
		}
	}
	
	/**
	 * Delete the row where player name is 'Tom Brady'.
	 * 
	 * @return
	 * 		number of rows deleted
	 * @throws SQLException
	 * 		if an error occurs
	 */
	public int delete() throws SQLException
	{
		System.out.println("Deleting a record.");
		
		// TODO: Delete record where name is 'Tom Brady'
		try{
			sql = "DELETE * FROM player WHERE name=\"Tom Brady\"";
			Statement stmt = con.createStatement();
			stmt.executeQuery(sql);
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex);
		}
		return 0;			
	}
	
	/**
	 * Query returns the player name and salary where rows are sorted by salary descending.
	 * 
	 * @return
	 * 		ResultSet
	 * @throws SQLException
	 * 		if an error occurs
	 */
	public ResultSet query1() throws SQLException
	{
		System.out.println("Executing query #1.");
		
		// TODO: Write SQL query
		try {
			sql = "SELECT name, salary FROM player ORDER BY salary DESC";
			Statement stmt = con.createStatement();
			ResultSet rst = stmt.executeQuery(sql);
			while (rst.next()) {
				System.out.println("Name: " + rst.getString(1));
				System.out.println("Salary: " + rst.getString(2));
			}
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex);
		}


		return null;
	}
	
	/**
	 * Query returns the player last name and salary if the player's salary is greater than the average salary of all players.
	 * 
	 * @return
	 * 		ResultSet
	 * @throws SQLException
	 * 		if an error occurs
	 */
	public ResultSet query2() throws SQLException
	{
		System.out.println("Executing query #2.");
		
		// TODO: Write SQL query
		try{
			Statement stmt = con.createStatement();
			ResultSet rst = stmt.executeQuery("stuff");
			while (rst.next()) {
				System.out.println("Name: " + rst.getString(1));
				System.out.println("Salary: " + rst.getString(2));
			}
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex);
		}
		return null;
	}
	
	/**
	 * Query returns all fields of a pair of players where a pair of players is returned if the last_update field of their records have been updated less than an hour apart.
	 * Do not duplicate pairs.  Example: Only show (Manning, Rodgers) and not also (Rodgers, Manning).
	 * 
	 * @return
	 * 		ResultSet
	 * @throws SQLException
	 * 		if an error occurs
	 */
	public ResultSet query3() throws SQLException
	{
		System.out.println("Executing query #3.");
		
		// TODO: Write SQL query
		return null;
	}
	
	/*
	 * Do not change anything below here.
	 */
	/**
     * Converts a ResultSet to a string with a given number of rows displayed.
     * Total rows are determined but only the first few are put into a string.
     * 
     * @param rst
     * 		ResultSet
     * @param maxrows
     * 		maximum number of rows to display
     * @return
     * 		String form of results
     * @throws SQLException
     * 		if a database error occurs
     */    
    public static String resultSetToString(ResultSet rst, int maxrows) throws SQLException
    {                       
        StringBuffer buf = new StringBuffer(5000);
        int rowCount = 0;
        ResultSetMetaData meta = rst.getMetaData();
        buf.append("Total columns: " + meta.getColumnCount());
        buf.append('\n');
        if (meta.getColumnCount() > 0)
            buf.append(meta.getColumnName(1));
        for (int j = 2; j <= meta.getColumnCount(); j++)
            buf.append(", " + meta.getColumnName(j));
        buf.append('\n');
                
        while (rst.next()) 
        {
            if (rowCount < maxrows)
            {
                for (int j = 0; j < meta.getColumnCount(); j++) 
                { 
                	Object obj = rst.getObject(j + 1);                	 	                       	                                	
                	buf.append(obj);                    
                    if (j != meta.getColumnCount() - 1)
                        buf.append(", ");                    
                }
                buf.append('\n');
            }
            rowCount++;
        }            
        buf.append("Total results: " + rowCount);
        return buf.toString();
    }
    
    /**
     * Converts ResultSetMetaData into a string.
     * 
     * @param meta
     * 		 ResultSetMetaData
     * @return
     * 		string form of metadata
     * @throws SQLException
     * 		if a database error occurs
     */
    public static String resultSetMetaDataToString(ResultSetMetaData meta) throws SQLException
    {
	    StringBuffer buf = new StringBuffer(5000);                                   
	    buf.append(meta.getColumnName(1)+" ("+meta.getColumnLabel(1)+", "+meta.getColumnType(1)+"-"+meta.getColumnTypeName(1)+", "+meta.getColumnDisplaySize(1)+", "+meta.getPrecision(1)+", "+meta.getScale(1)+")");
	    for (int j = 2; j <= meta.getColumnCount(); j++)
	        buf.append(", "+meta.getColumnName(j)+" ("+meta.getColumnLabel(j)+", "+meta.getColumnType(j)+"-"+meta.getColumnTypeName(j)+", "+meta.getColumnDisplaySize(j)+", "+meta.getPrecision(j)+", "+meta.getScale(j)+")");
	    return buf.toString();
    }
}