package semorg.sql.tables;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import semorg.gui.util.DBTableChangedListener;
import semorg.gui.util.Messages;
import semorg.sql.access.DBAccess;
import semorg.sql.util.DBColumn;
import semorg.sql.util.DBConstraint;
import semorg.sql.util.DistinctVector;
import semorg.sql.util.Utility;

public class SeminarType extends AbstractTable implements SimpleIDKey {

    public static List<Integer> lockedIds = new ArrayList<Integer>();

    /** The DB-ID. */
    protected int id;

    /** The shortened title of a seminartype. */
    protected String shortTitle;

    /** The title of a seminartype. */
    protected String title;

    /** The objective of a seminartype. */
    protected String objective;

    /** The methodology of a seminartype. */
    protected String methodology;

    /** The topic of a seminartype. */
    protected String topic;

    /** The routine of a seminartype. */
    protected String routine;

    /** The duration of a seminartype. */
    protected Integer duration;

    /** The documents of a seminartype. */
    protected String documents;

    /** The ausidence of a seminartype. */
    protected String audience;

    /** The requirements of a seminartype. */
    protected String requirements;

    /** The charge of a seminartype. */
    protected Float charge;

    /** The maximal number of entrants of a seminartype. */
    protected Integer maxEntrants;

    /** The minimal number of entrants of a seminartype. */
    protected Integer minEntrants;

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

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

    /** SQL statement for creating the table "seminartype" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`id_pk` integer NOT NULL auto_increment,"
	    + "`shorttitle` varchar(10) NOT NULL,"
	    + "`title` varchar(50),"
	    + "`objective` varchar(400),"
	    + "`methodology` varchar(400),"
	    + "`topic` varchar(400),"
	    + "`routine` varchar(200),"
	    + "`duration` integer,"
	    + "`documents` varchar(200),"
	    + "`audience` varchar(200),"
	    + "`requirements` varchar(200),"
	    + "`charge` float,"
	    + "`maxEntrants` integer,"
	    + "`minEntrants` integer,"
	    + "`creationdate` timestamp NOT NULL default '0000-00-00 00:00:00',"
	    + "`modificationdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
	    + "PRIMARY KEY  (`id_pk`) )";

    /** Parameterized SQL statement for updating a data record. */
    protected static String seminarTypeUpdateString = "UPDATE "
	    + tableName
	    + " SET shorttitle=?, title=?, objective=?, methodology=?, topic=?, "
	    + "routine=?, duration=?, documents=?, audience=?, requirements=?, charge=?, maxEntrants=?, minEntrants=?, "
	    + "modificationdate=? WHERE id_pk=?";

    /** SQL query to get all seminartype records from the database. */
    private static String queryString = "SELECT id_pk, shorttitle, title, objective, methodology, "
	    + "topic, routine, duration, documents, audience, requirements, charge, maxEntrants, minEntrants, "
	    + "creationdate, modificationdate FROM "
	    + tableName
	    + " WHERE true";

    /**
     * Parameterized SQL statement to get a seminartypes conducted by a given
     * lecturer.
     */
    private static String conductQueryString = "SELECT st.id_pk, st.shorttitle, st.title, st.objective, st.methodology, "
	    + "st.topic, st.routine, st.duration, st.documents, st.audience, st.requirements, st.charge, st.maxEntrants, st.minEntrants, "
	    + "st.creationdate, st.modificationdate FROM seminartype st, conduct c "
	    + "WHERE st.id_pk=c.seminartypeid AND c.lecturerid=?";

    /** Creates an instance of the class SeminarType with the given parameters. */
    public SeminarType(int id, String shortTitle, String title,
	    String objective, String methodology, String topic, String routine,
	    Integer duration, String documents, String audience,
	    String requirements, Float charge, Integer maxEntrants,
	    Integer minEntrants, Timestamp creationDate,
	    Timestamp modificationDate) {

	super(creationDate, modificationDate);

	this.id = id;
	this.shortTitle = shortTitle;
	this.title = title;
	this.objective = objective;
	this.methodology = methodology;
	this.topic = topic;
	this.routine = routine;
	this.duration = duration;
	this.documents = documents;
	this.audience = audience;
	this.requirements = requirements;
	this.charge = charge;
	this.maxEntrants = maxEntrants;
	this.minEntrants = minEntrants;

	this.creationDate = creationDate;
	this.modificationDate = modificationDate;

    }

    /**
     * Checks if the according DB table exists.
     * 
     * @see semorg.sql.tables.AbstractTable#tableOK(String)
     * @return <code>true</code>, if the table is ok, <code>false</code>
     *         otherwise.
     */
    public static boolean tableOK() {
	return AbstractTable.tableOK(tableName);
    }

    /**
     * Creates the table "seminartype" 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 createSeminarTypeTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * Converts the actual Company object into a database record and inserts it
     * into the table "seminartype". Furthermore this method queries the
     * auto-generated id, sets the appropriate property {@link #id} and returns
     * the id. Finally it fires a TableChanged-EventListener to update the UI.
     * 
     * @return the auto-generated id of the inserted data record.
     * @throws SQLException
     *                 If the auto-generated id can't resolved from the db, i.e.
     *                 it hasnt been generated, or the PreparedStatement
     *                 instance can't be created or executed etc.
     */
    public int insertIntoDB() throws SQLException {
	PreparedStatement insertStmt = DBAccess.dbAccess
		.getPreparedStatement(SeminarType.seminarTypeInsertString);

	// PK MIT AUTO_INCREMENT
	insertStmt.setNull(1, Types.INTEGER);
	// NOT NULL
	insertStmt.setString(2, shortTitle);
	insertStmt.setString(3, title);
	insertStmt.setString(4, objective);
	insertStmt.setString(5, methodology);
	insertStmt.setString(6, topic);
	insertStmt.setString(7, routine);

	if (duration != null)
	    insertStmt.setInt(8, duration);
	else
	    insertStmt.setNull(8, Types.INTEGER);

	insertStmt.setString(9, documents);
	insertStmt.setString(10, audience);
	insertStmt.setString(11, requirements);

	if (charge != null)
	    insertStmt.setFloat(12, charge);
	else
	    insertStmt.setNull(12, Types.FLOAT);

	if (maxEntrants != null)
	    insertStmt.setInt(13, maxEntrants);
	else
	    insertStmt.setNull(13, Types.INTEGER);

	if (minEntrants != null)
	    insertStmt.setInt(14, minEntrants);
	else
	    insertStmt.setNull(14, Types.INTEGER);

	insertStmt.setTimestamp(15, Utility.convertToTimestamp(creationDate));
	insertStmt.setTimestamp(16, Utility
		.convertToTimestamp(modificationDate));

	insertStmt.executeUpdate();

	ResultSet rs = insertStmt.getGeneratedKeys();
	if (rs.next()) {
	    this.id = rs.getInt(1);
	} else {
	    // TODO: korrekte Exception werfen
	    throw new SQLException();
	}

	rs.close();
	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_SEMINARTYPE);

	return this.id;

    }

    /**
     * Converts the actual {@link SeminarType} object into a database record and
     * updates a existing record to the changes made. Finally it fires a
     * TableChanged-EventListener to update the UI.
     * 
     * @throws SQLException
     *                 If the PreparedStatement instance can't be created or
     *                 executed, this exception is thrown.
     */
    public void updateDB() throws SQLException {

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

	updateStmt.setString(1, shortTitle);
	updateStmt.setString(2, title);
	updateStmt.setString(3, objective);
	updateStmt.setString(4, methodology);
	updateStmt.setString(5, topic);
	updateStmt.setString(6, routine);

	if (duration != null)
	    updateStmt.setInt(7, duration);
	else
	    updateStmt.setNull(7, Types.INTEGER);

	updateStmt.setString(8, documents);
	updateStmt.setString(9, audience);
	updateStmt.setString(10, requirements);

	if (charge != null)
	    updateStmt.setFloat(11, charge);
	else
	    updateStmt.setNull(11, Types.FLOAT);

	if (maxEntrants != null)
	    updateStmt.setInt(12, maxEntrants);
	else
	    updateStmt.setNull(12, Types.INTEGER);

	if (minEntrants != null)
	    updateStmt.setInt(13, minEntrants);
	else
	    updateStmt.setNull(13, Types.INTEGER);

	updateStmt.setTimestamp(14, new Timestamp(System.currentTimeMillis()));

	// WHERE - Teil
	updateStmt.setInt(15, id);

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_SEMINARTYPE);
    }

    /**
     * Removes the given elements from the corresponding DB table . Due to the
     * FK connections (with an ON-DELETE-cascade) this method fires several
     * TableChanged-Events to update the UI.
     * 
     * @param selectedElements
     *                the elements, which should be deleted from the database.
     * @throws SQLException
     *                 Throws a SQL exception if the Statement instance can't be
     *                 created or executed.
     */
    public static void removeFromDB(Iterable selectedElements)
	    throws SQLException {

	String deleteString = "DELETE FROM " + tableName + " WHERE ";
	Iterator<SeminarType> it = selectedElements.iterator();

	// add first element
	int idToDelete = it.next().getId();
	deleteString += "id_pk=" + idToDelete + " ";

	// add the remaining elements
	while (it.hasNext()) {
	    idToDelete = it.next().getId();
	    deleteString += "OR id_pk=" + idToDelete + " ";
	}
	Statement stmt = DBAccess.dbAccess.getStatement();
	stmt.executeUpdate(deleteString);
	stmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_SEMINARTYPE);
	// because of FK-connection (on delete cascade)
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_PRESENTATION);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_PUBLICPRESENTATION);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANYINTERNALPRESENTATION);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_BOOKING);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANYBOOKING);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENTBOOKING);

    }

    /**
     * Creates for each element in a given ResultSet instance an instance 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 SeminarType} instances.
     */
    private static DistinctVector<SeminarType> getVector(ResultSet resultSet) {
	DistinctVector<SeminarType> set = new DistinctVector<SeminarType>();
	try {
	    while (resultSet.next()) {

		int id = resultSet.getInt("id_pk");
		String shortTitle = resultSet.getString("shorttitle");
		String title = resultSet.getString("title");
		String objective = resultSet.getString("objective");
		String methodology = resultSet.getString("methodology");
		String topic = resultSet.getString("topic");
		String routine = resultSet.getString("routine");
		Integer duration = resultSet.getInt("duration");
		if (resultSet.wasNull())
		    duration = null;
		String documents = resultSet.getString("documents");
		String audience = resultSet.getString("audience");
		String requirements = resultSet.getString("requirements");
		Float charge = resultSet.getFloat("charge");
		if (resultSet.wasNull())
		    charge = null;
		Integer maxEntrants = resultSet.getInt("maxEntrants");
		if (resultSet.wasNull())
		    maxEntrants = null;
		Integer minEntrants = resultSet.getInt("minEntrants");
		if (resultSet.wasNull())
		    minEntrants = null;

		Timestamp creationDate = resultSet.getTimestamp("creationdate");
		Timestamp modificationDate = resultSet
			.getTimestamp("modificationdate");

		set.add(new SeminarType(id, shortTitle, title, objective,
			methodology, topic, routine, duration, documents,
			audience, requirements, charge, maxEntrants,
			minEntrants, creationDate, modificationDate));
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return set;
    }

    /**
     * Creates a PreparedStatement instance with the given parameters, executes
     * the query and returns a vector of instances of this class corresponding
     * to the ResultSet of the query.
     * 
     * @param additionalConstraints
     *                additional conditions of the WHERE-clause
     * @param sortString
     *                sort string with the following structure
     *                <code>ORDER BY attribute [ASC|DSC]</code>
     * @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<SeminarType> getVectorFromDB(
	    Vector<DBConstraint> additionalConstraints, String sortString)
	    throws SQLException {

	PreparedStatement stmt = AbstractTable.createExtendedQueryString(
		queryString, additionalConstraints, sortString);

	ResultSet rs = stmt.executeQuery();

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

    /**
     * Returns for a given id the corresponding {@link SeminarType} object.
     * 
     * @param semTypeId
     *                the DB-ID of the wanted {@link SeminarType} object
     * @return {@link SeminarType} object corresponding to the given id or
     *         <tt>null</tt> if the data record does not exist.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static SeminarType getSeminarTypeFromDB(int semTypeId)
	    throws SQLException {

	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();
	keyConstraint.add(new DBConstraint(SeminarType.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		semTypeId), DBConstraint.CONJ_END));
	DistinctVector<SeminarType> semTypes = getVectorFromDB(keyConstraint,
		null);

	if (semTypes.size() > 0)
	    return semTypes.iterator().next();
	else
	    return null;
    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the table corresponding to this class.
     * 
     * @return the vector of DBColumn instances.
     * @see semorg.sql.tables.AbstractTable#getColumns(String tableAbbreviation)
     */
    public static Vector<DBColumn> getColumns() {

	Vector<DBColumn> columns = new Vector<DBColumn>();
	columns.add(new DBColumn("id_pk", Messages
		.getString("MAP.SeminarType.IdPK"), Integer.class));
	columns.add(new DBColumn("shorttitle", Messages
		.getString("MAP.SeminarType.ShortTitle"), String.class));
	columns.add(new DBColumn("title", Messages
		.getString("MAP.SeminarType.Title"), String.class));
	columns.add(new DBColumn("objective", Messages
		.getString("MAP.SeminarType.Objective"), String.class));
	columns.add(new DBColumn("methodology", Messages
		.getString("MAP.SeminarType.Methodology"), String.class));
	columns.add(new DBColumn("topic", Messages
		.getString("MAP.SeminarType.Topic"), String.class));
	columns.add(new DBColumn("routine", Messages
		.getString("MAP.SeminarType.Routine"), String.class));
	columns.add(new DBColumn("duration", Messages
		.getString("MAP.SeminarType.Duration"), Integer.class));
	columns.add(new DBColumn("documents", Messages
		.getString("MAP.SeminarType.Documents"), String.class));
	columns.add(new DBColumn("audience", Messages
		.getString("MAP.SeminarType.Audience"), String.class));
	columns.add(new DBColumn("requirements", Messages
		.getString("MAP.SeminarType.Requirements"), String.class));
	columns.add(new DBColumn("charge", Messages
		.getString("MAP.SeminarType.Charge"), Float.class));
	columns.add(new DBColumn("maxEntrants", Messages
		.getString("MAP.SeminarType.MaxEntrants"), Integer.class));
	columns.add(new DBColumn("minEntrants", Messages
		.getString("MAP.SeminarType.MinEntrants"), Integer.class));
	columns.addAll(AbstractTable.getColumns(""));// TODO: correct?
	return columns;

    }

    public String getAudience() {
	return audience;
    }

    public void setAudience(String audience) {
	this.audience = audience;
    }

    public Float getCharge() {
	return charge;
    }

    public void setCharge(Float charge) {
	this.charge = charge;
    }

    public String getDocuments() {
	return documents;
    }

    public void setDocuments(String documents) {
	this.documents = documents;
    }

    public Integer getDuration() {
	return duration;
    }

    public void setDuration(Integer duration) {
	this.duration = duration;
    }

    public Integer getMaxEntrants() {
	return maxEntrants;
    }

    public void setMaxEntrants(Integer maxEntrants) {
	this.maxEntrants = maxEntrants;
    }

    public String getMethodology() {
	return methodology;
    }

    public void setMethodology(String methodology) {
	this.methodology = methodology;
    }

    public Integer getMinEntrants() {
	return minEntrants;
    }

    public void setMinEntrants(Integer minEntrants) {
	this.minEntrants = minEntrants;
    }

    public String getObjective() {
	return objective;
    }

    public void setObjective(String objective) {
	this.objective = objective;
    }

    public String getRequirements() {
	return requirements;
    }

    public void setRequirements(String requirements) {
	this.requirements = requirements;
    }

    public String getRoutine() {
	return routine;
    }

    public void setRoutine(String routine) {
	this.routine = routine;
    }

    public String getShortTitle() {
	return shortTitle;
    }

    public void setShortTitle(String shortTitle) {
	this.shortTitle = shortTitle;
    }

    public String getTitle() {
	return title;
    }

    public void setTitle(String title) {
	this.title = title;
    }

    public String getTopic() {
	return topic;
    }

    public void setTopic(String topic) {
	this.topic = topic;
    }

    public int getId() {
	return id;
    }

    /**
     * Returns for a DB-ID the {@link SeminarType} object according to the data
     * record which has the next bigger id.
     * 
     * @param semTypeId
     *                the key of the current record
     * @return the {@link SeminarType} instance corresponding to the next record
     *         in the table "seminartype" or if it does not exist the object
     *         with the smallest id.
     * @see semorg.sql.tables.AbstractTable#getNext(String, DBColumn, int)
     */
    public static SeminarType getNext(int semTypeId) {
	SeminarType returnValue = null;
	ResultSet rs = AbstractTable.getNext(queryString, getColumns().get(0),
		semTypeId);
	if (rs != null) {
	    returnValue = SeminarType.getVector(rs).iterator().next();
	    try {
		Statement producingStatement = rs.getStatement();
		rs.close();
		if (producingStatement != null)
		    producingStatement.close();

	    } catch (SQLException e) {
		e.printStackTrace();
	    }

	}
	return returnValue;
    }

    /**
     * Returns for a DB-ID the {@link SeminarType} instance according to the
     * record which has the next smaller id.
     * 
     * @param semTypeId
     *                the key of the current record
     * @return the {@link SeminarType} instance corresponding to the previous
     *         record in the table "seminartype" or if it does not exist the
     *         object with the biggest id.
     * @see semorg.sql.tables.AbstractTable#getPrevious(String, DBColumn, int)
     */
    public static SeminarType getPrevious(int semTypeId) {
	SeminarType returnValue = null;
	ResultSet rs = AbstractTable.getPrevious(queryString, getColumns().get(
		0), semTypeId);
	if (rs != null) {
	    returnValue = SeminarType.getVector(rs).iterator().next();
	    try {
		Statement producingStatement = rs.getStatement();
		rs.close();
		if (producingStatement != null)
		    producingStatement.close();

	    } catch (SQLException e) {
		e.printStackTrace();
	    }

	}
	return returnValue;
    }

    /**
     * Returns for a given lecturer id the seminartypes whose are conducted by
     * the lecturer corresponding to the id.
     * 
     * @param lecturerId
     *                the id of the lecturer of interest
     * @return Vector containing the {@link SeminarType} instances.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static DistinctVector<SeminarType> getSemTypeConductedBy(
	    int lecturerId) throws SQLException {

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

	stmt.setInt(1, lecturerId);

	ResultSet rs = stmt.executeQuery();
	DistinctVector<SeminarType> returnValue = SeminarType.getVector(rs);
	rs.close();
	stmt.close();
	return returnValue;
    }

    /** Returns the id. */
    public int hashCode() {
	return id;
    }

    /**
     * Compares the ids of a given object and the current one.
     * 
     * @param otherSeminarType
     *                object to be compared with the actual object.
     * @return <code>true</code> if the id of the given object and the actual
     *         object are the same, <code>false</code> otherwise.
     */
    public boolean equals(SeminarType otherSeminarType) {
	return id == otherSeminarType.id;
    }

    /**
     * If the given object is an instance of the {@link SeminarType} class this
     * method compares the id of the given and the actual object, otherwise it
     * calls the equal-method of the AbstractTable class with the given object.
     * 
     * @param otherSeminarType
     *                object to be compared with the actual object.
     * @return <code>true</code> if the id of the given object and the actual
     *         object are the same, <code>false</code> otherwise.
     */
    public boolean equals(Object otherSeminarType) {
	if (otherSeminarType instanceof SeminarType)
	    return id == (((SeminarType) otherSeminarType).id);
	else
	    return super.equals(otherSeminarType);
    }

}
