package com.example.model;

import java.util.*;
import java.sql.*;

/** This class represents the class that does all the work for
    for accessing database and processing the result of queries */
public class ScheduleExpert {

    // Create connection and statement objects
    /** Connection object */
    private Connection myCon;
    /** Statement object */
    private Statement myStmt;

    /** The ScheduleExpert constructor */
    public ScheduleExpert() {
    }

    /**This function calls all the database functions */
    public List getNames(String url, String user, String pw) {
        List<String> names = new ArrayList<String>();

	try {

	    connecter(url, user, pw); 
	    selectNames(names);
	}

	catch (SQLException sqlEx) {
	    System.err.println(sqlEx);
	}
	return names;

    }// end of getNames method

    /**This function calls all the database functions */
    public List getTimes(String name, String url, String user, String pw) {
        List<String> times = new ArrayList<String>();

        try {

            connecter(url, user, pw);
            selectTimes(name, times);
	    closer();
        }

        catch (SQLException sqlEx) {
            System.err.println(sqlEx);
        }
        return times;

    }// end of getNames method


    /** Register the database driver and create the database
	connection and query statement objects */
    void connecter(String url, String user, String pw) throws SQLException {
	try {
	        
	    // Register the MySQL database driver
	    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	        
	    // Create the connection object    
	    final String URL=url;
	    final String username=user;
	    final String password = pw;
	    
	    myCon = DriverManager.getConnection(URL, username, password);
	        
	    // Create the statement object for the query
	    myStmt = myCon.createStatement();

	} //* end of try clause

	catch (SQLException sqlEx) {
	    throw sqlEx;
	}
	
	catch (Exception e) {
	    System.err.println(e);
	}

    } //end of the connecter method

    /** Creates the SQL command string,
	execute it, and display the results */

    void selectNames(List<String> list) throws SQLException {

	// Create the SELECT command as a String
	final String select_sql =
	"SELECT username from Account";
	
	// Perform the query
	ResultSet result = myStmt.executeQuery(select_sql);


	// Put result in linked list
	while (result.next()) {
	    String name = result.getString("username");
	    list.add(name);
	    } 
	    //while loop for result set
	
    } // select() method

    void selectTimes(String name, List<String> list) throws SQLException {

        // Create the SELECT command as a String
        final String select_sql =
	    "SELECT busy from BusyTimes where username='" + name + "' ";

        // Perform the query
        ResultSet result = myStmt.executeQuery(select_sql);


        // Put result in linked list
        while (result.next()) {
            String time = result.getString("busy");
            list.add(time);
	}
	//while loop for result set

    } // select() method


    /** Closes the database connection */
    void closer () throws SQLException {
	myCon.close();
    } 
    
} // ScheduleExpert
