package semorg.sql.tables;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import org.eclipse.swt.widgets.Combo;

import semorg.gui.util.DBTableChangedListener;
import semorg.sql.access.DBAccess;
import semorg.sql.util.DistinctVector;

/** This class model the enumerations used in the application. */
public class Enumeration {

    /** {@link Enumeration}s of this type refering to salutations. */
    public static final int TYPE_SALUTATION = 0;

    /** {@link Enumeration}s of this type refering to titles. */
    public static final int TYPE_TITLE = 1;

    /** {@link Enumeration}s of this type refering to salutations. */
    public static final int TYPE_COUNTRY = 2;

    /** {@link Enumeration}s of this type refering to annexes. */
    public static final int TYPE_ANNEX = 3;

    /** The name of the corresponding table in the db. */
    private static String tableName = "enumeration";

    /** SQL statement for creating the table "enumeration" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName + "` (" + "`type` integer NOT NULL,"
	    + "`value` varchar(13) NOT NULL,"
	    + "PRIMARY KEY  (`type`, `value`) )";

    /** Parameterized SQL statement for inserting a data record. */
    private static String insertString = "INSERT INTO " + tableName
	    + " VALUES(?, ?)";

    /** Parameterized SQL statement for deleting a data record. */
    private static String deleteString = "DELETE FROM " + tableName
	    + " WHERE type=? AND value=?";

    /**
     * Parameterized SQL statement for updating a data record in the table
     * enumeration.
     */
    private static String updateString = "UPDATE " + tableName + " SET "
	    + "value=? WHERE type=? AND value=?";

    /** SQL query to get all enumerations with a given type. */
    private static String queryString = "SELECT type, value" + " FROM "
	    + tableName + " WHERE type=?";

    /** SQL query to check if a salutation is already in use. */
    private static String checkForSalutationString = "(SELECT number_pk FROM person WHERE salutation=?) UNION "
	    + "(SELECT id_pk FROM company WHERE salutation=?) UNION "
	    + "(SELECT id_pk FROM company WHERE cpsalutation=?) LIMIT 1";

    /** SQL query to check if a title is already in use. */
    private static String checkForTitleString = "(SELECT number_pk FROM person WHERE title=?) UNION "
	    + "(SELECT id_pk FROM company WHERE title=?) UNION "
	    + "(SELECT id_pk FROM company WHERE cptitle=?) LIMIT 1";

    /** SQL query to check if a country name is already in use. */
    private static String checkForCountryString = "(SELECT number_pk FROM person WHERE country=?) UNION "
	    + "(SELECT id_pk FROM company WHERE country=?) UNION "
	    + "(SELECT id_pk FROM presentation WHERE country=?) LIMIT 1";

    /** SQL query to check if a annex is already in use. */
    private static String checkForAnnexString = "(SELECT number_pk FROM person WHERE annex=?) UNION "
	    + "(SELECT id_pk FROM company WHERE annex=?) UNION "
	    + "(SELECT id_pk FROM presentation WHERE annex=?) LIMIT 1";

    /** SQL queries to update all occurrences of a salutation to a new value. */
    private static String[] updateSalutationStrings = {
	    "UPDATE person SET salutation=? WHERE salutation=?",
	    "UPDATE company SET salutation=? WHERE salutation=?",
	    "UPDATE company SET cpsalutation=? WHERE cpsalutation=?" };

    /** SQL queries to update all occurrences of a title to a new value. */
    private static String[] updateTitleStrings = {
	    "UPDATE person SET salutation=? WHERE salutation=?",
	    "UPDATE company SET salutation=? WHERE salutation=?",
	    "UPDATE company SET cpsalutation=? WHERE cpsalutation=?" };

    /** SQL queries to update all occurrences of a country name to a new value. */
    private static String[] updateCountryStrings = {
	    "UPDATE person SET salutation=? WHERE salutation=?",
	    "UPDATE company SET salutation=? WHERE salutation=?",
	    "UPDATE presentation SET cpsalutation=? WHERE cpsalutation=?" };

    /** SQL queries to update all occurrences of a annexes to a new value. */
    private static String[] updateAnnexStrings = {
	    "UPDATE person SET salutation=? WHERE salutation=?",
	    "UPDATE company SET salutation=? WHERE salutation=?",
	    "UPDATE presentation SET cpsalutation=? WHERE cpsalutation=?" };

    /** The type of the current enumeration. */
    private int type;

    /** The value of the current enumeration. */
    private String value;

    /** Creates instances of this class with the given parameters. */
    public Enumeration(int type, String value) {
	this.type = type;
	this.value = value;
    }

    /**
     * Creates the table "enumeration" if it does not exist.
     * 
     * @param statement
     *                instance of the class Statement, which is used for
     *                executing the SQL statement {@link #createTableSQLString}.
     * @throws SQLException
     *                 If the execution of the given statement with the query
     *                 {@link #createTableSQLString} or its closing fails.
     */
    public static void createEnumerationTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * Creates a PreparedStatement instance with the given parameter, executes
     * the query and returns a vector of instances of this class corresponding
     * to the ResultSet of the query.
     * 
     * @param type
     *                the type of the wanted enumerations.
     * @return a DistinctVector instance filled with the created instances.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static DistinctVector<Enumeration> getVectorFromDB(int type)
	    throws SQLException {

	PreparedStatement stmt = DBAccess.dbAccess
		.getPreparedStatement(queryString);

	stmt.setInt(1, type);

	ResultSet rs = stmt.executeQuery();

	DistinctVector<Enumeration> returnValue = Enumeration.getVector(rs);
	rs.close();
	stmt.close();
	return returnValue;
    }

    /**
     * Creates for each element in a given ResultSet instance an object of this
     * class and returns a vector with the resulting class objects.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created
     *         {@link Enumeration} instances.
     */
    private static DistinctVector<Enumeration> getVector(ResultSet resultSet)
	    throws SQLException {
	DistinctVector<Enumeration> set = new DistinctVector<Enumeration>();

	while (resultSet.next()) {

	    int type = resultSet.getInt("type");
	    String value = resultSet.getString("value");

	    set.add(new Enumeration(type, value));
	}

	return set;
    }

    public String getValue() {
	return value;
    }

    public int getType() {
	return type;
    }

    public static Enumeration createNewEnumInDB(int type, String value)
	    throws SQLException {
	PreparedStatement insertStmt = DBAccess.dbAccess
		.getPreparedStatement(insertString);

	insertStmt.setInt(1, type);
	insertStmt.setString(2, value);

	insertStmt.execute();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ENUMERATION);

	return new Enumeration(type, value);
    }

    /**
     * This method checks first if the enumeration to delete is already in use
     * and if not deletes it form the db. Otherwise the enumeration will not be
     * deleted.
     * 
     * @return <tt>true</tt> if the deleting was successful, otherwise
     *         <tt>false</tt>
     * @throws SQLException
     *                 Throws a SQL exception if the Statement instance can't be
     *                 created or executed.
     */
    public boolean removeFromDB() throws SQLException {

	PreparedStatement checkStmt = null;

	switch (type) {
	case TYPE_SALUTATION:
	    checkStmt = DBAccess.dbAccess
		    .getPreparedStatement(checkForSalutationString);
	    break;
	case TYPE_TITLE:
	    checkStmt = DBAccess.dbAccess
		    .getPreparedStatement(checkForTitleString);
	    break;
	case TYPE_COUNTRY:
	    checkStmt = DBAccess.dbAccess
		    .getPreparedStatement(checkForCountryString);
	    break;
	case TYPE_ANNEX:
	    checkStmt = DBAccess.dbAccess
		    .getPreparedStatement(checkForAnnexString);
	    break;
	}

	for (int i = 1; i <= 3; i++) {
	    checkStmt.setString(i, value);
	}
	if (checkStmt.executeQuery().next())
	    return false;

	PreparedStatement deleteStmt = DBAccess.dbAccess
		.getPreparedStatement(deleteString);

	deleteStmt.setInt(1, type);
	deleteStmt.setString(2, value);

	deleteStmt.execute();
	deleteStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ENUMERATION);

	return true;
    }

    /**
     * Replaces all occurrences of the actual enumeration to the given value.
     * Note that several database tables get updated by this method. Finally the
     * corresponding {@link DBTableChangedListener} are fired.
     * 
     * @param value
     *                the new value of the actual enumeration
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public void setValueInDB(String value) throws SQLException {

	// creating prepared statements for updating the enumerations
	PreparedStatement[] updateOtherTablesStmts = new PreparedStatement[3];
	for (int i = 0; i < updateOtherTablesStmts.length; i++) {
	    switch (type) {
	    case TYPE_SALUTATION:
		updateOtherTablesStmts[i] = DBAccess.dbAccess
			.getPreparedStatement(updateSalutationStrings[i]);
		break;
	    case TYPE_TITLE:
		updateOtherTablesStmts[i] = DBAccess.dbAccess
			.getPreparedStatement(updateTitleStrings[i]);
		break;
	    case TYPE_COUNTRY:
		updateOtherTablesStmts[i] = DBAccess.dbAccess
			.getPreparedStatement(updateCountryStrings[i]);
		break;
	    case TYPE_ANNEX:
		updateOtherTablesStmts[i] = DBAccess.dbAccess
			.getPreparedStatement(updateAnnexStrings[i]);
		break;
	    }
	}

	for (int i = 0; i < updateOtherTablesStmts.length; i++) {
	    updateOtherTablesStmts[i].setString(1, value);
	    updateOtherTablesStmts[i].setString(2, this.value);

	    updateOtherTablesStmts[i].execute();
	}

	PreparedStatement updateStmt = DBAccess.dbAccess
		.getPreparedStatement(updateString);

	updateStmt.setString(1, value);
	updateStmt.setInt(2, this.type);
	updateStmt.setString(3, this.value);

	updateStmt.execute();
	updateStmt.close();

	this.value = value;

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_PERSON);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANY);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_PRESENTATION);

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ENUMERATION);
    }

    /**
     * Adds all enumerations of the given type to the given {@link Combo} field.
     * 
     * @param combo
     *                the {@link Combo} field, which takes the enumeration
     *                values.
     * @param type
     *                the type of the enumeration to add.
     */
    private static void addEnums(Combo combo, int type) {
	try {
	    DistinctVector<Enumeration> set = getVectorFromDB(type);
	    Iterator<Enumeration> iter = set.iterator();
	    while (iter.hasNext()) {
		combo.add(iter.next().value);
	    }
	} catch (SQLException e) {
	}
    }

    /**
     * Adds all enumerations of the type {@value #TYPE_SALUTATION} to the given
     * {@link Combo} field.
     * 
     * @param salutationCombo
     *                the {@link Combo} field, which takes the enumeration
     *                values.
     */
    public static void addSalutationEnums(Combo salutationCombo) {
	addEnums(salutationCombo, TYPE_SALUTATION);
    }

    /**
     * Adds all enumerations of the type {@value #TYPE_TITLE} to the given
     * {@link Combo} field.
     * 
     * @param titleCombo
     *                the {@link Combo} field, which takes the enumeration
     *                values.
     */
    public static void addTitleEnums(Combo titleCombo) {
	addEnums(titleCombo, TYPE_TITLE);
    }

    /**
     * Adds all enumerations of the type {@value #TYPE_COUNTRY} to the given
     * {@link Combo} field.
     * 
     * @param countryCombo
     *                the {@link Combo} field, which takes the enumeration
     *                values.
     */
    public static void addCountryEnums(Combo countryCombo) {
	addEnums(countryCombo, TYPE_COUNTRY);
    }

    /**
     * Adds all enumerations of the type {@value #TYPE_ANNEX} to the given
     * {@link Combo} field.
     * 
     * @param annexCombo
     *                the {@link Combo} field, which takes the enumeration
     *                values.
     */

    public static void addAnnexEnums(Combo annexCombo) {
	addEnums(annexCombo, TYPE_ANNEX);
    }

    /**
     * Inserts the given salutation into the database.
     * 
     * @param salutation
     *                the value of the new salutation
     */
    public static void insertSalutationStringInDB(String salutation) {
	try {
	    Enumeration.createNewEnumInDB(TYPE_SALUTATION, salutation);
	} catch (SQLException e) {/* already in db, ignore */
	}
    }

    /**
     * Inserts the given title into the database.
     * 
     * @param title
     *                the value of the new title
     */

    public static void insertTitleStringInDB(String title) {
	try {
	    Enumeration.createNewEnumInDB(TYPE_TITLE, title);
	} catch (SQLException e) {/* already in db, ignore */
	}
    }

    /**
     * Inserts the given country into the database.
     * 
     * @param country
     *                the value of the new country
     */

    public static void insertcountryStringInDB(String country) {
	try {
	    Enumeration.createNewEnumInDB(TYPE_COUNTRY, country);
	} catch (SQLException e) {/* already in db, ignore */
	}
    }

    /**
     * Inserts the given annex into the database.
     * 
     * @param annex
     *                the value of the new annex
     */

    public static void insertAnnexStringInDB(String annex) {
	try {
	    Enumeration.createNewEnumInDB(TYPE_ANNEX, annex);
	} catch (SQLException e) {/* already in db, ignore */
	}
    }

}
