import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DBAccess {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public void readDataBase() throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://localhost/cs435_lab4?"
							+ "user=final&password=project");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();


			//question1(statement);
			//question2Add(statement);
			//question2Del(statement);
			//question2Driver(statement);
			//question3(statement);
			question4(statement);


			/*
			// Result set get the result of the SQL query
			resultSet = statement
					.executeQuery("select * from bus");
			writeResultSet(resultSet);


			// PreparedStatements can use variables and are more efficient
			preparedStatement = connect
					.prepareStatement("insert into  feedback.comments values (default, ?, ?, ?, ? , ?, ?)");
			// "myuser, webpage, datum, summery, COMMENTS from feedback.comments");
			// Parameters start with 1
			preparedStatement.setString(1, "Test");
			preparedStatement.setString(2, "TestEmail");
			preparedStatement.setString(3, "TestWebpage");
			preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
			preparedStatement.setString(5, "TestSummary");
			preparedStatement.setString(6, "TestComment");
			preparedStatement.executeUpdate();

			preparedStatement = connect
					.prepareStatement("SELECT myuser, webpage, datum, summery, COMMENTS from feedback.comments");
			resultSet = preparedStatement.executeQuery();
			writeResultSet(resultSet);

			// Remove again the insert comment
			preparedStatement = connect
					.prepareStatement("delete from feedback.comments where myuser= ? ; ");
			preparedStatement.setString(1, "Test");
			preparedStatement.executeUpdate();
			resultSet = statement
					.executeQuery("select * from trip");
			writeMetaData(resultSet);
			*/


		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	private void question1(Statement statement) throws SQLException{
		String startName = "Pittsburgh";
		String destName = "Canada";
		String date = "2012-02-19";

		ResultSet result = statement.executeQuery("SELECT t.StartLocationName, t.DestinationName, tof.TripDate, tof.ScheduledStartTime, tof.ScheduledArrivalTime, tof.DriverName, tof.BusID "+
												  "FROM trip t, tripoffering tof "+
												  "WHERE t.TripNumber = tof.TripNumber "+
												  "AND t.StartLocationName = '" + startName +"' "+
												  "AND t.DestinationName = '" + destName + "' " +
												  "AND tof.TripDate = '" + date + "'");
		writeResultSet(result);
	}

	private void question2Add(Statement statement) throws SQLException{

		String tripNumber = "1";
		String date = "2012-01-20";
		String schedStartTime = "12:00PM";
		String schedArrivalTime = "2:00PM";
		String driverName = "Bob";
		String busId = "1";

		statement.executeUpdate("INSERT INTO tripoffering "+
												"VALUES (" + tripNumber +
												", '" + date +
												"', '" + schedStartTime +
												"', '" + schedArrivalTime +
												"', '" + driverName +
												"', " + busId + ")");

		writeResultSet(statement.executeQuery("SELECT * FROM tripoffering"));
	}

	private void question2Del(Statement statement) throws SQLException{
		String tripNumber = "1";
		String date = "2012-01-20";
		String schedStartTime = "12:00PM";

		statement.executeUpdate("DELETE FROM tripoffering "+
												"WHERE TripNumber = '" + tripNumber + "' "
												+ "AND TripDate = '" + date + "' "
												+ "AND ScheduledStartTime = '" + schedStartTime + "'");

		writeResultSet(statement.executeQuery("SELECT * FROM tripoffering"));
	}

	private void question2Driver(Statement statement) throws SQLException{
		String tripNumber = "1";
		String date = "2012-01-20";
		String schedStartTime = "12:00PM";

		String driverName = "Alicia";

		statement.executeUpdate("UPDATE tripoffering "+
												"SET DriverName = '" + driverName + "' "
												+ "WHERE TripNumber = '" + tripNumber + "' "
												+ "AND TripDate = '" + date + "' "
												+ "AND ScheduledStartTime = '" + schedStartTime + "'");

		writeResultSet(statement.executeQuery("SELECT * FROM tripoffering"));

	}



	private ResultSet question3(Statement statement) throws SQLException{

        String tripNumber = "1";

        ResultSet result = statement.executeQuery("SELECT tsi.TripNumber, tsi.StopNumber, tsi.SequenceNumber, s.StopAddress " +
                                                  "FROM tripstopinfo tsi, stop s " +
                                                  "WHERE tsi.TripNumber = '" + tripNumber +"' " +
                                                  "AND s.StopNumber = tsi.StopNumber");

        writeResultSet(result);
        return result;
    }

    private ResultSet question4(Statement statement) throws SQLException{
        String driverName = "Bob";
        Calendar cal = Calendar.getInstance();
        java.sql.Date inputDate = new java.sql.Date(2012-02-19);
        String startDate = inputDate.toString();
        cal.setTime(inputDate);
        cal.add(Calendar.DATE,7);


        SimpleDateFormat myFormat = new SimpleDateFormat("yyyy-MM-dd");
        String endDate = myFormat.format(cal.getTime().toString());

        System.out.println("SELECT * " +
                                                  "FROM tripoffering " +
                                                  "WHERE DriverName = '" + driverName +"' " +
                                                  " AND  TripDate >= '" + startDate + "'" +
                                                  " AND  TripDate <= '" + endDate + "'");

        ResultSet result = statement.executeQuery("SELECT * " +
                                                  "FROM tripoffering " +
                                                  "WHERE DriverName = '" + driverName +"' " +
                                                  " AND  TripDate >= '" + startDate + "'" +
                                                  " AND  TripDate <= '" + endDate + "'");

        writeResultSet(result);
        return result;
    }


	private void writeMetaData(ResultSet resultSet) throws SQLException {
		// Now get some metadata from the database
		// Result set get the result of the SQL query

		System.out.println("The columns in the table are: ");

		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			System.out.println("Column " + i + " "
					+ resultSet.getMetaData().getColumnName(i));
		}
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		ResultSetMetaData rsmd = resultSet.getMetaData();

		int totalColumns = rsmd.getColumnCount();

		for(int j = 1; j <= totalColumns; j++){
			System.out.printf("%20s", rsmd.getColumnName(j));
		}

		while (resultSet.next()) {
			System.out.println("");
			for(int i = 1; i <= totalColumns; i ++){
				System.out.printf("%20s", resultSet.getObject(i).toString());
			}
		}
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
}