package semorg.sql.access;

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

import semorg.sql.tables.Associate;
import semorg.sql.tables.Booking;
import semorg.sql.tables.Client;
import semorg.sql.tables.ClientBooking;
import semorg.sql.tables.Company;
import semorg.sql.tables.CompanyBooking;
import semorg.sql.tables.CompanyInternalPresentation;
import semorg.sql.tables.Conduct;
import semorg.sql.tables.Enumeration;
import semorg.sql.tables.Instructor;
import semorg.sql.tables.Lecturer;
import semorg.sql.tables.Person;
import semorg.sql.tables.Presentation;
import semorg.sql.tables.PublicPresentation;
import semorg.sql.tables.SeminarType;
import semorg.sql.tables.Supervisor;

/**
 * <p>
 * Implements the access to the database. Therefor this class fulfills the
 * following functions:
 * <ul>
 * <li> Creating the HUSemOrg-DB and its tables <br>
 * <br>
 * Note that the db user needs the rights to access the catalog husemorg to be
 * able to create it.</li>
 * <li>Establish the connection to the MySQL database.</li>
 * <li>Creating and providing {@link Statement}s and {@link PreparedStatement}s
 * such that the application can communicate with the database.</li>
 * </ul>
 * <i>Hint:</i> The user <i>semorg</i> which uses the application to access
 * the database must exist already and must have the appropriate rights.
 * </p>
 */
public class DBAccess {
    /** Provides a static db access. */
    public static DBAccess dbAccess;

    // TODO: make it configurable --> PropertyFile
    private static String chosenDataBaseDriver = "com.mysql.jdbc.Driver";

    private static String chosenDriverURL = "jdbc:mysql:///";

    private static String databaseName = "husemorg";

    private static String mysqlURL = "jdbc:mysql:///mysql";

    private static String databaseUsername = "semorg";

    private static String databasePassword = "semorg";

    private Connection connection = null;

    /**
     * This method tries to connect to the catalog husemorg and if that fails it
     * tries to create it.
     * 
     * @throws SQLException
     *                 if the connection to the database can not be created or
     *                 the creating of the catalog and the tables fails.
     * @throws ClassNotFoundException
     *                 if the database driver can not be loaded.
     * 
     * @see #createDatabaseAndConnect()
     */
    private DBAccess() throws SQLException, ClassNotFoundException {
	// load the db driver
	Class.forName(chosenDataBaseDriver);

	try {
	    // create connection to db
	    connection = DriverManager.getConnection(chosenDriverURL
		    + databaseName, databaseUsername, databasePassword);
	} catch (SQLException e) {
	    // try to create the database
	    System.out
		    .println("The database schema husemorg does not exist and is now created.");
	    createDatabaseAndConnect();
	}
    }

    /**
     * Creates a connection to the MySQL database and creates the catalog for
     * the application if it does not exist. Finally it connects to the new
     * created catalog.
     * 
     * @throws SQLException
     *                 if the creation, execution or closing of the
     *                 {@link Statement} fails.
     */
    private void createDatabaseAndConnect() throws SQLException {
	// TODO: set the rights for creating the catalog...
	Connection conn = DriverManager.getConnection(mysqlURL,
		databaseUsername, databasePassword);
	Statement stmt = conn.createStatement();
	stmt.execute("CREATE DATABASE IF NOT EXISTS " + databaseName);
	stmt.close();
	conn.close();

	connection = DriverManager.getConnection(
		chosenDriverURL + databaseName, databaseUsername,
		databasePassword);
	createTables();
    }

    /* creates all tables in the database. */
    private void createTables() throws SQLException {
	Person.createPersonTable(connection.createStatement());
	Associate.createAssociateTable(connection.createStatement());
	Lecturer.createLecturerTable(connection.createStatement());
	Company.createCompanyTable(connection.createStatement());
	SeminarType.createSeminarTypeTable(connection.createStatement());
	Client.createClientTable(connection.createStatement());
	Presentation.createPresentationTable(connection.createStatement());
	PublicPresentation.createPublicPresentationTable(connection
		.createStatement());
	CompanyInternalPresentation
		.createCompanyInternalPresentationTable(connection
			.createStatement());
	Booking.createBookingTable(connection.createStatement());
	CompanyBooking.createCompanyBookingTable(connection.createStatement());
	ClientBooking.createClientBookingTable(connection.createStatement());
	Conduct.createConductTable(connection.createStatement());
	Supervisor.createSupervisorTable(connection.createStatement());
	Instructor.createInstructorTable(connection.createStatement());
	Enumeration.createEnumerationTable(connection.createStatement());
    }

    /** Initiates the static database access attribute {@link #dbAccess}. */
    public static void init() throws ClassNotFoundException, SQLException {
	dbAccess = new DBAccess();
    }

    /**
     * Returns for a given SQL query as a string the appropriate
     * {@link PreparedStatement} instance.
     * 
     * @param sqlString the SQL query as a {@link String}.
     */
    public PreparedStatement getPreparedStatement(String sqlString)
	    throws SQLException {
	return connection.prepareStatement(sqlString);
    }

    /**
     * Returns for a given SQL query as a string the appropriate
     * {@link Statement} instance.
     */
    public Statement getStatement() throws SQLException {
	return connection.createStatement();
    }

}
