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.Date;
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.Utility;

/**
 * The super class for all booking classes. It encapsulates all common
 * properties and methods of all booking classes.
 */
public abstract class Booking extends AbstractTable implements SimpleIDKey {

    /**
     * A list of the ids of locked records. This list is meant to avoid the
     * deletion of records, which are curently edited.
     */
    public static List<Integer> lockedIds = new ArrayList<Integer>();

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

    /**
     * The date of enrollment of a customer into a presentation according to
     * this booking.
     */
    protected Date enrolled;

    /**
     * The date of comfirmation of the enrollment of a customer into a
     * presentation according to this booking.
     */
    protected Date confirmed;

    /** The billing date. */
    protected Date billed;

    /** The date of deregistration. */
    protected Date signedOff;

    /** The date of information. */
    protected Date messaged;

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

    /**
     * Parameterized SQL statement for inserting a record into the table
     * "booking".
     */
    protected static String bookingInsertString = "INSERT INTO " + tableName
	    + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

    /** SQL statement for creating the table "booking" if it does not exist. */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`id_pk` integer NOT NULL auto_increment,"
	    + "`enrolled` date,"
	    + "`confirmed` date,"
	    + "`billed` date,"
	    + "`signedoff` date,"
	    + "`messaged` date,"
	    + "`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 booking record. */
    protected static String bookingUpdateString = "UPDATE " + tableName
	    + " SET enrolled=?, confirmed=?, billed=?, signedoff=?, "
	    + "messaged=?, modificationdate=? WHERE id_pk=?";

    /** Creates an instance of this class with the given values. */
    protected Booking(int id, Date enrolled, Date confirmed, Date billed,
	    Date signedOff, Date messaged, Timestamp creationDate,
	    Timestamp modificationDate) {

	super(creationDate, modificationDate);

	this.id = id;
	this.enrolled = enrolled;
	this.confirmed = confirmed;
	this.billed = billed;
	this.signedOff = signedOff;
	this.messaged = messaged;
    }

    /**
     * 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);
    }

    /**
     * Executes the sql statement {@link #createTableSQLString} via the given
     * Statement object.
     * 
     * @param statement
     *                a Statement instance for executing the creation query.
     * @throws SQLException
     *                 Throws the SQL exception if the execution fails.
     */
    public static void createBookingTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * Inserts a data record into the database table "booking".
     * <p>
     * Due to doing that it creates a PreparedStatement instance from the
     * parameterized SQL query {@link #bookingInsertString} by setting the
     * parameters and finally executes the query.
     * </p>
     * <p>
     * Furthermore this method queries the auto-generated id, sets the
     * appropriate property {@link #id} and returns the id.
     * </p>
     * <p>
     * Finally it fires a TableChanged-Event-Listener to update the UI.
     * </p>
     * 
     * @return the auto-generated id of the inserted tupel.
     * @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, this exception is
     *                 thrown.
     */
    public int insertIntoDB() throws SQLException {
	PreparedStatement insertStmt = DBAccess.dbAccess
		.getPreparedStatement(bookingInsertString);

	// PK with AUTO_INCREMENT
	insertStmt.setNull(1, Types.INTEGER);

	if (enrolled != null)
	    insertStmt.setDate(2, Utility.convertToSQLDate(enrolled));
	else
	    insertStmt.setNull(2, Types.DATE);

	if (confirmed != null)
	    insertStmt.setDate(3, Utility.convertToSQLDate(confirmed));
	else
	    insertStmt.setNull(3, Types.DATE);

	if (billed != null)
	    insertStmt.setDate(4, Utility.convertToSQLDate(billed));
	else
	    insertStmt.setNull(4, Types.DATE);

	if (signedOff != null)
	    insertStmt.setDate(5, Utility.convertToSQLDate(signedOff));
	else
	    insertStmt.setNull(5, Types.DATE);

	if (messaged != null)
	    insertStmt.setDate(6, Utility.convertToSQLDate(messaged));
	else
	    insertStmt.setNull(6, Types.DATE);

	insertStmt.setTimestamp(7, Utility.convertToTimestamp(creationDate));
	insertStmt
		.setTimestamp(8, Utility.convertToTimestamp(modificationDate));

	insertStmt.executeUpdate();

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

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

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_BOOKING);

	return this.id;
    }

    /**
     * Updates a data record in the database table "Booking". Due to doing that
     * it creates a PreparedStatement instance from the parameterized SQL query
     * {@link #bookingUpdateString} by setting the parameters and finally
     * executes the query. Finally it fires a TableChanged-Event-Listener to
     * update the UI.
     * 
     * @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, this exception is
     *                 thrown.
     */
    public void updateDB() throws SQLException {
	PreparedStatement updateStmt = DBAccess.dbAccess
		.getPreparedStatement(bookingUpdateString);

	if (enrolled != null)
	    updateStmt.setDate(1, Utility.convertToSQLDate(enrolled));
	else
	    updateStmt.setNull(1, Types.DATE);

	if (confirmed != null)
	    updateStmt.setDate(2, Utility.convertToSQLDate(confirmed));
	else
	    updateStmt.setNull(2, Types.DATE);

	if (billed != null)
	    updateStmt.setDate(3, Utility.convertToSQLDate(billed));
	else
	    updateStmt.setNull(3, Types.DATE);

	if (signedOff != null)
	    updateStmt.setDate(4, Utility.convertToSQLDate(signedOff));
	else
	    updateStmt.setNull(4, Types.DATE);

	if (messaged != null)
	    updateStmt.setDate(5, Utility.convertToSQLDate(messaged));
	else
	    updateStmt.setNull(5, Types.DATE);

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

	// WHERE - part
	updateStmt.setInt(7, id);

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_BOOKING);
    }

    /**
     * Removes the given elements from the table "Booking".
     * <p>
     * <i>Hint:</i> The given elements should be ONLY deleted from the table
     * "Booking", i.e. NOT in the extending classes/tables, because the rest is
     * done by the dbms in order to fullfill a FK-constraint with an
     * ON-DELETE-cascade.
     * </p>
     * 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<Booking> it = selectedElements.iterator();

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

	// inserte the rest of 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_BOOKING);
	// because of FK-connections (on delete cascade)
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANYBOOKING);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENTBOOKING);
    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the table Booking.
     * <p>
     * <i>Hint:</i> The properties of the AbstractTable table are also
     * included.
     * </p>
     * 
     * @return the vector of DBColumn instances.
     */
    public static Vector<DBColumn> getColumns() {
	Vector<DBColumn> columns = new Vector<DBColumn>();
	columns.add(new DBColumn("b.id_pk", Messages
		.getString("MAP.Booking.IdPK"), Date.class));
	columns.add(new DBColumn("b.enrolled", Messages
		.getString("MAP.Booking.Enrolled"), Date.class));
	columns.add(new DBColumn("b.confirmed", Messages
		.getString("MAP.Booking.Confirmed"), Date.class));
	columns.add(new DBColumn("b.billed", Messages
		.getString("MAP.Booking.Billed"), Date.class));
	columns.add(new DBColumn("b.signedOff", Messages
		.getString("MAP.Booking.SignedOff"), Date.class));
	columns.add(new DBColumn("b.messaged", Messages
		.getString("MAP.Booking.Messaged"), Date.class));
	columns.addAll(AbstractTable.getColumns("b."));
	return columns;
    }

    /** Returns the billing date. */
    public Date getBilled() {
	return billed;
    }

    /**
     * Sets the billing date.
     * 
     * @param billed
     *                the new billing date
     */
    public void setBilled(Date billed) {
	this.billed = billed;
    }

    /**
     * Returns the confirmation date.
     * 
     * @return the confirmed
     */
    public Date getConfirmed() {
	return confirmed;
    }

    /**
     * Sets the confirmation date.
     * 
     * @param confirmed
     *                the new confirmation date.
     */
    public void setConfirmed(Date confirmed) {
	this.confirmed = confirmed;
    }

    /**
     * Returns the enrollment date.
     * 
     * @return the enrollement date.
     */
    public Date getEnrolled() {
	return enrolled;
    }

    /**
     * Sets the enrollment date.
     * 
     * @param enrolled
     *                the new enrollment date.
     */
    public void setEnrolled(Date enrolled) {
	this.enrolled = enrolled;
    }

    /**
     * Returns the date of information.
     * 
     * @return the date of information.
     */
    public Date getMessaged() {
	return messaged;
    }

    /**
     * Sets the date of information.
     * 
     * @param messaged
     *                the new date of information.
     */
    public void setMessaged(Date messaged) {
	this.messaged = messaged;
    }

    /**
     * Returns the date of deregistration.
     * 
     * @return the date of deregistration.
     */
    public Date getSignedOff() {
	return signedOff;
    }

    /**
     * Sets the date of deregistration.
     * 
     * @param signedOff
     *                the new date of deregistration.
     */
    public void setSignedOff(Date signedOff) {
	this.signedOff = signedOff;
    }

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

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

    /**
     * If the given object is an instance of the Booking 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 otherBooking
     *                object, which should be compared with 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 otherBooking) {
	if (otherBooking instanceof Booking)
	    return id == (((Booking) otherBooking).id);
	else
	    return super.equals(otherBooking);
    }
}
