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.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 company. */
public class CompanyBooking extends Booking {

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

    /** The id of the company which booked the presentation. */
    private int companyId;

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

    /**
     * SQL query to get all companybooking 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.companyid, "
	    + "b.creationdate, b.modificationdate FROM booking b, " + tableName
	    + " cb " + "WHERE b.id_pk=cb.booking_fk";

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

    /** SQL query to update a companybooking record. */
    private static String companyBookingUpdateString = "UPDATE " + tableName
	    + " " + "SET presentationid=?, companyid=? " + "WHERE booking_fk=?";

    /** SQL statement for creating the table "companybooking" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`booking_fk` integer NOT NULL,"
	    + "`presentationid` integer NOT NULL,"
	    + "`companyid` integer NOT NULL,"
	    + "PRIMARY KEY  (`booking_fk`),"
	    + "CONSTRAINT `companybooking_fk` FOREIGN KEY (`booking_fk`) REFERENCES `booking` (`id_pk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `cipres_fk` FOREIGN KEY (`presentationid`) REFERENCES `companyinternalpresentation` (`presentation_fk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `company_fk` FOREIGN KEY (`companyid`) REFERENCES `company` (`id_pk`) ON DELETE CASCADE)";

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

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

	this.presentationId = presentationId;
	this.companyId = companyId;
    }

    /**
     * Creates for each element in a given ResultSet instance an CompanyBooking
     * object and returns a vector with the resulting CompanyBooking instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created CompanyBooking
     *         instances.
     */
    private static DistinctVector<CompanyBooking> getVector(ResultSet resultSet) {
	DistinctVector<CompanyBooking> set = new DistinctVector<CompanyBooking>();
	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 companyId = resultSet.getInt("companyid");

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

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

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

	ResultSet rs = stmt.executeQuery();
	DistinctVector<CompanyBooking> returnValue = CompanyBooking
		.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 "companybooking" 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 createCompanyBookingTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * <p>
     * Inserts a companybooking record into the db.
     * </p>
     * <p>
     * To do so, it first inserts a record into the table "booking", secondly a
     * record into the table "companybooking". 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(companyBookingInsertString);

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

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

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANYBOOKING);

	return id;
    }

    /**
     * <p>
     * Updates a companybooking 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 "companybooking" 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 updateAssoStmt = DBAccess.dbAccess
		.getPreparedStatement(companyBookingUpdateString);

	updateAssoStmt.setInt(1, presentationId);
	updateAssoStmt.setInt(2, companyId);

	// WHERE - part
	updateAssoStmt.setInt(3, id);

	updateAssoStmt.executeUpdate();

	updateAssoStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_COMPANYBOOKING);

    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the tables "companybooking".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class CompanyBooking.
     * @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.CompanyBooking.PresentationId"),
		Integer.class));
	columns.add(columns.size() - 2, new DBColumn("cb.companyid", Messages
		.getString("MAP.CompanyBooking.CompanyId"), Integer.class));
	return columns;
    }

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

    public void setCompanyId(int companyId) {
	this.companyId = companyId;
    }

    public int getPresentationId() {
	return presentationId;
    }

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

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

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

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

	stmt.setInt(1, companyId);

	ResultSet rs = stmt.executeQuery();
	DistinctVector<CompanyBooking> returnValue = CompanyBooking
		.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 CompanyBooking
     *         instances corresponding to the presentation with the given id.
     * @throws SQLException
     *                 if the creation or execution of the database query fails.
     */
    public static DistinctVector<CompanyBooking> 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<CompanyBooking> returnValue = CompanyBooking
		.getVector(rs);
	rs.close();
	stmt.close();
	return returnValue;
    }

    /**
     * Returns a CompanyBooking instance for a given id of a companybooking
     * record.
     * 
     * @param bookingId
     *                the id of the wanted CompanyBooking instance
     * @return a CompanyBooking object correspoding to the given id.
     * @throws SQLException
     *                 if the creating or executing of database query fails.
     */
    public static CompanyBooking getCompanyBooking(int bookingId)
	    throws SQLException {
	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();
	keyConstraint.add(new DBConstraint(CompanyBooking.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		bookingId), DBConstraint.CONJ_END));
	DistinctVector<CompanyBooking> clients = CompanyBooking
		.getVectorFromDB(keyConstraint, null);
	return clients.iterator().next();
    }
}
