import java.net.URL;
import java.sql.*;

/**
 * class UserDAO
 * stores and retrieves objects of type User
 *
 * @author Jim
 * @version 1.0
 */
public class UserDAO
{
    private static Connection theConn;

    /**
     * Finds a user from the database, specified by username
     *
     * @param username the target username
     * @return the User retrieved from the database
     */
    public static User getByUsername(String username)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             User u = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * " +
                "FROM Users " +
                "WHERE username = '" + username + "'";
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             // use only first row of result - should be unique
             if (rs.next()) {
                u = new User(rs.getString("firstname"),
                             rs.getString("lastname"),
                             rs.getString("address"),
                             rs.getString("username"),
                             rs.getString("password"),
                             rs.getString("datejoined")
                             );
             }
             rs.close();
             stmt.close();
             return u;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

    /**
     * Finds all users in the database.
     * Limited to first 20 users.
     * Should use larger array or collection to get more
     *
     * @return array of User objects retrieved from the database
     */
    public static User[] getAll()
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             User[] users = new User[20];
             User u = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * FROM Users";

             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             int count = 0;
             while (rs.next() && count<20) {
                u = new User(rs.getString("firstname"),
                             rs.getString("lastname"),
                             rs.getString("address"),
                             rs.getString("username"),
                             rs.getString("password"),
                             rs.getString("datejoined")
                             );
                users[count] = u;
                count++;
             }
             rs.close();
             stmt.close();
             return users;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

    /**
     * Stores a new User object to the database
     *
     * @param user the User object to be stored
     */
    public static void storeNew(User user)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             User u = null;

             Statement stmt;
             String sql;

             sql =
                "INSERT INTO Users(firstname,lastname,address,username,password,datejoined) "+
                "VALUES('" +
                user.getFirstname() + "','" +
                user.getLastname() + "','" +
                user.getAddress() + "','" +
                user.getUsername() + "','" +
                user.getPassword() + "','" +
                user.getDateJoined() + "')";

             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             int rowsAffected = stmt.executeUpdate(sql);

             System.out.println(rowsAffected + " row(s) inserted");   // to check whether insert was successful

             stmt.close();
         }
         catch (Exception e) {
             System.out.println("COULD NOT STORE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

    /**
     * Stores an updated User object to the database
     *
     * @param user the User object to be stored
     */
    public static void storeUpdated(User user)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             Statement stmt;
             String sql;

             sql =
                "UPDATE Users SET " +           // don;t set username - primary key value should not be changed
                "firstname = '" + user.getFirstname() + "', " +
                "lastname = '" + user.getLastname() + "', " +
                "address = '" + user.getAddress() + "', " +
                "password = '" + user.getPassword() + "',  " +
                "dateJoined = '" + user.getDateJoined() + "' " +
                "WHERE username = '" + user.getUsername() + "' ";

             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             int rowsAffected = stmt.executeUpdate(sql);

             System.out.println(rowsAffected + " row(s) updated");   // to check whether update was successful


             stmt.close();
         }
         catch (Exception e) {
             System.out.println("COULD NOT STORE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }


    /**
     * Deletes a user from the database, specified by username
     *
     * @param username the target username
     */
    public static void delete(String username)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             Statement stmt;
             String sql;

             sql =
                "DELETE FROM Users " +
                "WHERE username = '" + username + "'";
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             int rowsAffected = stmt.executeUpdate(sql);

             System.out.println(rowsAffected + " row(s) deleted");   // to check whether delete was successful

             stmt.close();
         }
         catch (Exception e) {
             System.out.println("COULD NOT DELETE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

     /**
      * Gets a Connection object to the database
      *
      * @return the Connection object
      */
     private static Connection getConnection() throws Exception {
        Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        //String URL = "jdbc:odbc:gcutours";
        //Connection c = DriverManager.getConnection(URL, null, null);
        Connection c = DriverManager.getConnection(
            "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" +
            "DBQ=c:/Users/student/My Documents/databases/gcutours_app.mdb"
        );
        return c;
    }
}