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

/** Models the booking of a presentation by a client. */
public class ClientBooking extends Booking {

    /** The id of the presantation booked by the client. */
    private int presentationId;

    /** The id of the client who booked the presentation. */
    private int clientId;

    /** The of the debitor for a client booking. */
    private int debitorId;

    /** The id of substitute for a client. */
    private int substituteId;

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

    /**
     * SQL query to get all clientbooking records from the database. (delives
     * also the inherited attributes)
     */
    private static String queryString = "SELECT b.id_pk, b.enrolled, "
	    + "b.confirmed, b.billed, b.signedoff, b.messaged, "
	    + "cb.presentationid, cb.clientid, cb.debitorid, cb.substitute_fk, "
	    + "b.creationdate, b.modificationdate FROM booking b, " + tableName
	    + " cb " + "WHERE b.id_pk=cb.booking_fk";

    /** SQL query to insert a clientbooking record. */
    private static String clientBookingInsertString = "INSERT INTO "
	    + tableName + " VALUES(?, ?, ?, ?, ?)";

    /** SQL query to update a clientbooking record. */
    private static String clientBookingUpdateString = "UPDATE " + tableName
	    + " "
	    + "SET presentationid=?, clientid=?, debitorid=?, substitute_fk=? "
	    + "WHERE booking_fk=?";

    /** SQL statement for creating the table "clientbooking" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`booking_fk` integer NOT NULL,"
	    + "`presentationid` integer NOT NULL,"
	    + "`clientid` integer NOT NULL,"
	    + "`debitorid` integer,"
	    + "`substitute_fk` integer,"
	    + "PRIMARY KEY  (`booking_fk`), "
	    + "CONSTRAINT `booking_fk` FOREIGN KEY (`booking_fk`) REFERENCES `booking` (`id_pk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `ppres_fk` FOREIGN KEY (`presentationid`) REFERENCES `publicpresentation` (`presentation_fk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `cl_fk` FOREIGN KEY (`clientid`) REFERENCES `client` (`person_fk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `deb_fk` FOREIGN KEY (`debitorid`) REFERENCES `client` (`person_fk`) ON DELETE SET NULL, "
	    + "CONSTRAINT `substitute_fk` FOREIGN KEY (`substitute_fk`) REFERENCES `client` (`person_fk`) ON DELETE SET NULL)";

    /** Creates an instance of the class Client with the given parameters. */
    public ClientBooking(int id, Date enrolled, Date confirmed, Date billed,
	    Date signedOff, Date messaged, int presentationId, int clientId,
	    int debitorId, int substituteId, Timestamp creationDate,
	    Timestamp modificationDate) throws SQLException {

	super(id, enrolled, confirmed, billed, signedOff, messaged,
		creationDate, modificationDate);

	this.presentationId = presentationId;
	this.clientId = clientId;
	this.debitorId = debitorId;
	this.substituteId = substituteId;
    }

    /**
     * Returns the first- and the lastname of the substitute for a
     * ClientBooking.
     * 
     * @return a String with the structure
     *         <code>firstname + " " + lastname</code> or the empty string if
     *         no substitute exists.
     */
    public String getSubstituteDescription() {

	if (substituteId == Client.NULL_ID) // substitute does not exist
	    return "";

	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();

	keyConstraint.add(new DBConstraint(Client.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		substituteId), DBConstraint.CONJ_END));
	DistinctVector<Client> substitutes;

	try {
	    substitutes = Client.getVectorFromDB(keyConstraint, null);
	} catch (SQLException e) {
	    return "";
	}
	if (substitutes.size() >= 1) {
	    Client substitute = substitutes.iterator().next();
	    return substitute.getFirstname() + " " + substitute.getName();
	} else
	    return "";
    }

    /**
     * Creates for each element in a given ResultSet instance an ClientBooking
     * object and returns a vector with the resulting ClientBooking instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created ClientBooking
     *         instances.
     */
    private static DistinctVector<ClientBooking> getVector(ResultSet resultSet) {
	DistinctVector<ClientBooking> set = new DistinctVector<ClientBooking>();
	try {
	    while (resultSet.next()) {
		int id = resultSet.getInt("id_pk");
		Date enrolled = resultSet.getDate("enrolled");
		Date confirmed = resultSet.getDate("confirmed");
		Date billed = resultSet.getDate("billed");
		Date signedOff = resultSet.getDate("signedOff");
		Date messaged = resultSet.getDate("messaged");
		int presentationId = resultSet.getInt("presentationid");
		int clientId = resultSet.getInt("clientid");
		int debitorId = resultSet.getInt("debitorid");
		int substituteId = resultSet.getInt("substitute_fk");

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

		set
			.add(new ClientBooking(id, enrolled, confirmed, billed,
				signedOff, messaged, presentationId, clientId,
				debitorId, substituteId, 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 ClientBooking instances 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 ClientBooking
     *         instances.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static DistinctVector<ClientBooking> getVectorFromDB(
	    Vector<DBConstraint> additionalConstraints, String sortString)
	    throws SQLException {

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

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

    /**
     * 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 "clientbooking" 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 createClientBookingTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * <p>
     * Inserts a clientbooking record into the db.
     * </p>
     * <p>
     * To do so, it first inserts a record into the table "booking", secondly a
     * record into the table "clientbooking". These two records are linked by a
     * PK-FK-association.
     * </p>
     * <p>
     * Finally it fires a TableChanged-Event-Listener to update the UI.
     * </p>
     * 
     * @see semorg.sql.tables.Booking#insertIntoDB()
     */
    public int insertIntoDB() throws SQLException {

	super.insertIntoDB();

	PreparedStatement insertStmt = DBAccess.dbAccess
		.getPreparedStatement(clientBookingInsertString);

	// FK
	insertStmt.setInt(1, id);

	insertStmt.setInt(2, presentationId);
	insertStmt.setInt(3, clientId);

	if (debitorId != AbstractTable.NULL_ID)
	    insertStmt.setInt(4, debitorId);
	else
	    insertStmt.setNull(4, Types.INTEGER);

	if (substituteId != Client.NULL_ID)
	    insertStmt.setInt(5, substituteId);
	else
	    insertStmt.setNull(5, Types.INTEGER);

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENTBOOKING);

	return id;
    }

    /**
     * <p>
     * Updates a clientbooking record in the database.
     * </p>
     * <p>
     * To do so, it firstly updates the corresponding record in the "booking"
     * table and secondly the record in the "clientbooking" table.
     * </p>
     * <p>
     * Finally it fires a TableChanged-Event-Listener to update the UI.
     * </p>
     * 
     * @see semorg.sql.tables.Person#updateDB()
     */
    public void updateDB() throws SQLException {
	super.updateDB();

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

	updateStmt.setInt(1, presentationId);
	updateStmt.setInt(2, clientId);

	if (debitorId != AbstractTable.NULL_ID)
	    updateStmt.setInt(3, debitorId);
	else
	    updateStmt.setNull(3, Types.INTEGER);
	if (substituteId != Client.NULL_ID)
	    updateStmt.setInt(4, substituteId);
	else
	    updateStmt.setNull(4, Types.INTEGER);

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

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENTBOOKING);

    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the tables "clientbooking".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class ClientBooking.
     * @see semorg.sql.tables.AbstractTable#getColumns(String tableAbbreviation)
     */
    public static Vector<DBColumn> getColumns() {
	Vector<DBColumn> columns = Booking.getColumns();
	columns.add(columns.size() - 2, new DBColumn("cb.presentationid",
		Messages.getString("MAP.ClientBooking.PresentationId"),
		Integer.class));
	columns.add(columns.size() - 2, new DBColumn("cb.clientid", Messages
		.getString("MAP.ClientBooking.ClientId"), Integer.class));
	columns.add(columns.size() - 2, new DBColumn("cb.debitorid", Messages
		.getString("MAP.ClientBooking.DebitorId"), Integer.class));
	columns.add(columns.size() - 2, new DBColumn("c.substitute_fk",
		Messages.getString("MAP.ClientBooking.SubstituteFK"),
		Integer.class));
	return columns;
    }

    /**
     * Returns a ClientBooking instance for a given id of a clientbooking
     * record.
     * 
     * @param bookingId
     *                the id of the wanted ClientBooking instance
     * @return a ClientBooking object correspoding to the given id.
     * @throws SQLException
     *                 if the creating or executing of database query fails.
     */
    public static ClientBooking getClientBooking(int bookingId)
	    throws SQLException {

	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();

	keyConstraint.add(new DBConstraint(ClientBooking.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		bookingId), DBConstraint.CONJ_END));

	DistinctVector<ClientBooking> clients = ClientBooking.getVectorFromDB(
		keyConstraint, null);

	return clients.iterator().next();
    }

    /**
     * Returns for a DB-ID the ClientBooking instance according to the record
     * which has the next bigger id.
     * 
     * @param currentId
     *                the key of the current record
     * @return the ClientBooking instance corresponding to the next record in
     *         the table "clientbooking" or if it does not exist the
     *         ClientBooking instance with the smallest id.
     * @see semorg.sql.tables.AbstractTable#getNext(String, DBColumn, int)
     */
    public static ClientBooking getNext(int currentId) {
	ClientBooking returnValue = null;
	ResultSet rs = AbstractTable.getNext(queryString, getColumns().get(0),
		currentId);
	if (rs != null) {
	    returnValue = ClientBooking.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 ClientBooking instance according to the record
     * which has the next smaller id.
     * 
     * @param currentId
     *                the key of the current record
     * @return the ClientBooking instance corresponding to the previous record
     *         in the table "clientbooking" or if it does not exist the
     *         ClientBooking instance with the biggest id.
     * @see semorg.sql.tables.AbstractTable#getPrevious(String, DBColumn, int)
     */
    public static ClientBooking getPrevious(int currentId) {
	ClientBooking returnValue = null;
	ResultSet rs = AbstractTable.getPrevious(queryString, getColumns().get(
		0), currentId);
	if (rs != null) {
	    returnValue = ClientBooking.getVector(rs).iterator().next();
	    try {
		Statement producingStatement = rs.getStatement();
		rs.close();
		if (producingStatement != null)
		    producingStatement.close();

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

	}
	return returnValue;
    }

    public int getClientId() {
	return clientId;
    }

    public void setClientId(int clientId) {
	this.clientId = clientId;
    }

    public int getDebitorId() {
	return debitorId;
    }

    public void setDebitorId(int debitorId) {
	this.debitorId = debitorId;
    }

    public int getPresentationId() {
	return presentationId;
    }

    public void setPresentationId(int presentationId) {
	this.presentationId = presentationId;
    }

    /**
     * Returns all bookings of a client according to the given client id.
     * 
     * @param clientId
     *                the id of a client
     * @return a DistinctVector instance filled with all ClientBooking instances
     *         corresponding to the client with the given id.
     * @throws SQLException
     *                 if the creation or execution of the database query fails.
     */
    public static DistinctVector<ClientBooking> getBookingsOfClient(int clientId)
	    throws SQLException {

	String extendedQueryString = queryString + " AND cb.clientid=?";

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

	stmt.setInt(1, clientId);

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

    /**
     * Returns all bookings for a presentation for a given presentation id.
     * 
     * @param presentationId
     *                the id of a presentation
     * @return a DistinctVector instance filled with all ClientBooking instances
     *         corresponding to the presentation with the given id.
     * @throws SQLException
     *                 if the creation or execution of the database query fails.
     */
    public static DistinctVector<ClientBooking> getBookingsForPresentation(
	    int presentationId) throws SQLException {

	String extendedQueryString = queryString + " AND cb.presentationid=?";

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

	stmt.setInt(1, presentationId);

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

    public int getSubstituteId() {
	return substituteId;
    }

    public void setSubstituteId(int substituteId) {
	this.substituteId = substituteId;
    }

}
