package lab4;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Purpose: Instances of this class are used to manage the database connection and to created/remove the table <br>
 * Imports: java.sql.* <br>
 * Exports: This class. <br>
 */
public class DbManager
{
    /**
     * The database connection
     */
	public java.sql.Connection dbConnection;
	
	/**
	 * Used to maintain a singleton instance of this class.
	 */
	private static DbManager instance = null;
	
	/**
	 * PRE: none <br>
	 * POST: Returns the singleton instance of this class <br>
	 * @return The single (per JVM) instance of DbManager
	 */
	public static DbManager getInstance()
	{
	    if (instance==null)
	    {
	        instance = new DbManager(); 
	    }
	    return instance;
	    
	}
	
	/**
	 * PRE: none
	 * POST: Returns a new instance of this class, made private to ensure the singleton method is used
	 * @return The instance of DbManager
	 */
	private DbManager()
	{
	    
	}
    
    /**
     * PRE: none <BR>
     * POST: The database connection is closed <BR>
     */
	public void closeDbConnection()
	{
		if (dbConnection != null)
		{
		    try
		    {
		        getDbConnection().close();
		    }
		    catch (SQLException ex)
		    {
		        handleSqlException(ex);    
		    }
		    dbConnection = null;
		    
		}
	}
	
	
	
    /**
     * PRE: none <BR>
     * POST: The dbConnection is initialized if null and the instance is returned <BR>
     * @return the database connection
     */
	public java.sql.Connection getDbConnection()
	{
		if (dbConnection == null)
			connectDB();
		return dbConnection;
	}
	
    /**
     * PRE: none <BR>
     * POST: The dbConnection is set to the parameter passed in <BR>
     * @param the new database connection
     */
    public void setDbConnection(java.sql.Connection dbConnection)
    {
        this.dbConnection = dbConnection;
    }
    
    /**
     * PRE: none <BR>
     * POST: A new database connection is created and set to the dbConnection variable <BR>
     */
	private void connectDB()
	{
		// Load the ODBC JDBC driver
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:DMLAB4";
			Connection con = DriverManager.getConnection(url, "", "");
			//Connection con = DriverManager.getConnection("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:/ST1.mdb");
			setDbConnection(con);
		}
		catch (SQLException ex)
		{
		    handleSqlException(ex);
		}
		catch (ClassNotFoundException ex)
		{
			System.out.println("\n--- ClassNotFoundException caught ---\n");
		}
	}
	
    /**
     * PRE: The database is available (can get a connection) <BR>
     * POST: The ProductReview table is created on the database <BR>
     */
	public void createTable()
	{
	    try
	    {
	        Statement stmt = getDbConnection().createStatement();   
	    
		    stmt.executeUpdate( "CREATE TABLE ProductReview ("  +
		            "ProductID  VARCHAR (8)  NOT NULL, "    +
		            "Reviewer   VARCHAR (30) NOT NULL, "    +
		            "Rating     VARCHAR (1)  NOT NULL, "    +
		            "PRIMARY KEY( ProductID, Reviewer ))" );
		    
		    stmt.close();
	    }
	    catch (SQLException ex)
	    {
	        handleSqlException(ex);
	    }
	    
	}

    /**
     * PRE: The database is available (can get a connection) <BR>
     * POST: The ProductReview table is removed from the database <BR>
     */
	public void removeTable()
	{
	    try
	    {
	        Statement stmt = getDbConnection().createStatement();   
	    
		    stmt.executeUpdate("DROP TABLE ProductReview");
		    
		    stmt.close();
	    }
	    catch (SQLException ex)
	    {
	        handleSqlException(ex);
	    }
	    
	}
	
    /**
     * PRE: none <BR>
     * POST: The details of the SQLException are printed to the console. <BR>
     */
	public static void handleSqlException(SQLException ex)
	{
		System.out.println("\n--- SQLException caught ---\n");
		while (ex != null)
		{
			System.out.println("Message:   " + ex.getMessage());
			System.out.println("SQLState:  " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");
		}
	}
	
}