//package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.sql.PreparedStatement;
import com.mysql.jdbc.Driver;

/**
 * A somewhat generic class used for establishing connections to a
 * MySQL database.
 * @author Josh Monague
 */
public class DatabaseAccess
{
    private Properties connectionProperties;
    private final String connectionString;
    private Connection connection;

    /**
     * The recent SQLException. Set to NULL if a method successfully interacts with the database.
     */
    protected SQLException sql_error;
    /**
     * The database table where data is read/written
     */
    protected String tableName;

    /**
     * Assumes that the database is located under localhost.
     * @param db - The name of the database
     * @param userName - The user's name
     * @param password - The user's password
     */
    public DatabaseAccess(String db, String userName, String password)
    {
        connectionString = String.format("jdbc:mysql://localhost/%s", db);
        connectionProperties = new Properties();
        connectionProperties.put("user", userName);
        connectionProperties.put("password", password);
    }

    /**
     * Use this constructor if the database is not local to the current machine.
     * @param db - The name of the database
     * @param dbIPaddress - The network IP of the database
     * @param userName - The user's name
     * @param password - The user's password
     */
    public DatabaseAccess(String db, String dbIPaddress, String userName, String password)
    {
        connectionString = String.format("jdbc:mysql://%s/%s", dbIPaddress, db);
        connectionProperties = new Properties();
        connectionProperties.put("user", userName);
        connectionProperties.put("password", password);
    }

    /**
     * Attempts to establish a connection to the MySQL database.
     * Clears recent sql error on success. NEW (10/10/2016): this method
     * now throws NoClassDefFoundError if driver is not linked properly.
     * @return TRUE if the connection was successful, otherwise FALSE
     * @see DatabaseAccess#getRecentSQLError()
     */
    public boolean connect() throws NoClassDefFoundError
    {
        connection = null;
        try
        {
            //need this first line, or else the driver will not load like it would in regular java projects...
            DriverManager.registerDriver(new Driver());
            connection = DriverManager.getConnection(connectionString, connectionProperties);
            sql_error = null;
        }
        catch(SQLException e)
        {
            sql_error = e;
            return false;
        }
        return true;
    }

    /**
     * Attempts to disconnect from a MySQL database.
     * Clears recent sql error on successful disconnection.
     * @return TRUE if successfully disconnected, otherwise FALSE
     * @see DatabaseAccess#getRecentSQLError()
     */
    public boolean disconnect()
    {
        try
        {
            connection.close();
            connection = null;
            sql_error = null;
        }
        catch(SQLException e)
        {
            sql_error = e;
            return false;
        }
        catch(NullPointerException e)
        {
            ; //tried to close a null Connection object. Let's treat this case as a successful disconnect and return TRUE
        }
        return true;
    }

    /**
     * Gets the status of the MySQL connection. Use sparingly, as this method queries the database!
     * Clears recent sql error if a connection is successfully established.
     * @return
     * -1 if a connection was never made, <br>
     * 0 if there is a connection that is currently established, <br>
     * 1 if the connection is closed <br>
     * 2 if there was an SQLException retrieving the status (check getRecentSQLError for details)
     * @see DatabaseAccess#getRecentSQLError()
     */
    public int getStatus()
    {
        if(connection == null)
            return -1;
        try
        {
            Statement s = connection.createStatement();
            s.executeQuery("select 1");
            sql_error = null;
            return 0;
        }
        catch(SQLException e)
        {
            sql_error = e;
            return 2;
        }
    }

    /**
     * Returns information about the most recent SQL query that has failed.
     * @return the most recent SQL error
     */
    public SQLException getRecentSQLError()
    {
        return sql_error;
    }

    /**
     * @param tableName - the new database table name to be used for reading and writing
     */
    public void setTableName(String tableName)
    {
        this.tableName = tableName;
    }

    /**
     * Creates a new java.sql.Statement object using the current Connection instance.
     * Clears recent sql error when a Statement object is successfully instantiated.
     * @return a new Statement object
     * @see Statement
     */
    protected Statement makeStatement()
    {
        if(connection == null)
            return null;

        Statement s = null;
        try
        {
            s = connection.createStatement();
            sql_error = null;
        }
        catch(SQLException e)
        {
            sql_error = e;
        }
        return s;
    }

    /**
     * Creates a new java.sql.PreparedStatement object using the current Connection instance.
     * Clears recent sql error if a PreparedStatement object is successfully instantiated.
     * @param sql - The sql query to be used by the returned PreparedStatement object
     * @return a new PreparedStatement object
     * @see PreparedStatement
     */
    protected PreparedStatement makePreparedStatement(String sql)
    {
        if(connection == null)
            return null;

        PreparedStatement ps = null;
        try
        {
            ps = connection.prepareStatement(sql);
            sql_error = null;
        }
        catch(SQLException e)
        {
            sql_error = e;
        }
        return ps;
    }
}