package semorg.sql.tables;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
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 a public presentation. */
public class PublicPresentation extends Presentation {

    /** The name of the co-operation partner. */
    private String coopPartner;

    /** The cancel fee of a public presentation. */
    private Float cancelFee;

    /** The number of current entrants of a public presentation. */
    private String publicPresentationCurrentEntrantsString = "SELECT COUNT(*) "
	    + "FROM clientbooking WHERE presentationid=?";

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

    /**
     * SQL query to get all "public presentation" records from the database.
     * (delivers also the inherited attributes)
     */
    private static String queryString = "SELECT pr.id_pk, pr.seminartype_fk, "
	    + "pr.duration, pr.beginningday, pr.endingday, pr.startingtime, pr.endingtime, "
	    + "pr.firststartingtime, pr.lastendingtime, pr.location, pr.street, pr.zipcode, "
	    + "pr.city, pr.country, pr.annex, pr.cancelled, ppr.cooppartner, ppr.cancelfee, "
	    + "pr.creationdate, pr.modificationdate FROM presentation pr, seminartype s, "
	    + tableName
	    + " ppr "
	    + "WHERE pr.id_pk=ppr.presentation_fk AND s.id_pk=pr.seminartype_fk";

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

    /** Parameterized SQL statement for updating a public presentation. */
    private static String publicPresentationUpdateString = "UPDATE "
	    + tableName
	    + " SET cooppartner=?, cancelfee=? WHERE presentation_fk=?";

    /**
     * SQL statement for creating the table "publicpresentation" (if not
     * exists).
     */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`presentation_fk` integer NOT NULL,"
	    + "`cooppartner` varchar(100),"
	    + "`cancelfee` float,"
	    + "PRIMARY KEY  (`presentation_fk`),"
	    + "CONSTRAINT `publicpresentation_fk` FOREIGN KEY (`presentation_fk`) REFERENCES `presentation` (`id_pk`) ON DELETE CASCADE)";

    /**
     * Creates an instance of the class PublicPresentation with the given
     * parameters.
     */
    public PublicPresentation(int id, int seminarTypeId, Integer duration,
	    Date beginningDay, Date endingDay, Time startingTime,
	    Time endingTime, Time firstStartingTime, Time lastEndingTime,
	    String location, String street, String zipCode, String city,
	    String country, String annex, boolean cancelled,
	    String coopPartner, Float cancelFee, Timestamp creationDate,
	    Timestamp modificationDate) throws SQLException {

	super(id, seminarTypeId, duration, beginningDay, endingDay,
		startingTime, endingTime, firstStartingTime, lastEndingTime,
		location, street, zipCode, city, country, annex, cancelled,
		creationDate, modificationDate);

	this.coopPartner = coopPartner;
	this.cancelFee = cancelFee;
    }

    /**
     * Creates for each element in a given ResultSet instance an
     * PublicPresentation object and returns a vector with the resulting
     * PublicPresentation instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created
     *         {@link PublicPresentation} instances.
     */
    private static DistinctVector<PublicPresentation> getVector(
	    ResultSet resultSet) {
	DistinctVector<PublicPresentation> set = new DistinctVector<PublicPresentation>();
	try {
	    while (resultSet.next()) {
		int id = resultSet.getInt("id_pk");
		int seminarTypeId = resultSet.getInt("seminartype_fk");
		Integer duration = resultSet.getInt("duration");
		if (resultSet.wasNull())
		    duration = null;
		Date beginningDay = resultSet.getDate("beginningday");
		Date endingDay = resultSet.getDate("endingday");
		Time startingTime = resultSet.getTime("startingtime");
		Time endingTime = resultSet.getTime("endingtime");
		Time firstStartingTime = resultSet.getTime("firststartingtime");
		Time lastEndingTime = resultSet.getTime("lastendingtime");
		String location = resultSet.getString("location");
		String street = resultSet.getString("street");
		String zipCode = resultSet.getString("zipcode");
		String city = resultSet.getString("city");
		String country = resultSet.getString("country");
		String annex = resultSet.getString("annex");
		boolean cancelled = resultSet.getBoolean("cancelled");
		String coopPartner = resultSet.getString("cooppartner");
		Float cancelFee = resultSet.getFloat("cancelfee");
		if (resultSet.wasNull())
		    cancelFee = null;
		Timestamp creationDate = resultSet.getTimestamp("creationdate");
		Timestamp modificationDate = resultSet
			.getTimestamp("modificationdate");

		set.add(new PublicPresentation(id, seminarTypeId, duration,
			beginningDay, endingDay, startingTime, endingTime,
			firstStartingTime, lastEndingTime, location, street,
			zipCode, city, country, annex, cancelled, coopPartner,
			cancelFee, 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 {@link PublicPresentation} 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
     *         {@link PublicPresentation} instances.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static DistinctVector<PublicPresentation> getVectorFromDB(
	    Vector<DBConstraint> additionalConstraints, String sortString)
	    throws SQLException {

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

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

    /*
     * (non-Javadoc)
     * 
     * @see semorg.sql.tables.Presentation#insertIntoDB()
     */
    public int insertIntoDB() throws SQLException {

	super.insertIntoDB();

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

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

	insertStmt.setString(2, coopPartner);

	if (cancelFee != null)
	    insertStmt.setFloat(3, cancelFee);
	else
	    insertStmt.setNull(3, Types.FLOAT);

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_PUBLICPRESENTATION);

	return id;
    }

    /*
     * (non-Javadoc)
     * 
     * @see semorg.sql.tables.Presentation#updateDB()
     */
    public void updateDB() throws SQLException {
	super.updateDB();

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

	updateStmt.setString(1, coopPartner);

	if (cancelFee != null)
	    updateStmt.setFloat(2, cancelFee);
	else
	    updateStmt.setNull(2, Types.FLOAT);

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

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_PUBLICPRESENTATION);

    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the table "publicpresentation". Note that this function
     * maps the names of the database columns to the names of the GUI tables.
     * 
     * @return the vector of DBColumn instances.
     * @see semorg.sql.tables.AbstractTable#getColumns(String tableAbbreviation)
     */
    public static Vector<DBColumn> getColumns() {
	Vector<DBColumn> columns = Presentation.getColumns();
	columns.add(columns.size() - 2, new DBColumn("ppr.cooppartner",
		Messages.getString("MAP.PublicPresentation.CoopPartner"),
		String.class));
	columns.add(columns.size() - 2, new DBColumn("ppr.cancelfee", Messages
		.getString("MAP.PublicPresentation.CancelFee"), Float.class));
	return columns;
    }

    /**
     * Returns a {@link Vector} of {@link DBColumn} objects for the current
     * {@link PublicPresentation} instance.<br>
     * <br>
     * Note that the second element in the vector gets replaced by a
     * {@link DBColumn} which contains the shortened title of the corresponding
     * {@link SeminarType}.
     */
    public static Vector<DBColumn> getColumnsWithSemtype() {
	Vector<DBColumn> columns = getColumns();
	columns.set(1, new DBColumn("s.shorttitle", Messages
		.getString("MAP.Presentation.SeminarTypeST"), String.class));
	return columns;
    }

    public String getCoopPartner() {
	return coopPartner;
    }

    public void setCoopPartner(String coopPartner) {
	this.coopPartner = coopPartner;
    }

    public Float getCancelFee() {
	return cancelFee;
    }

    public void setCancelFee(Float cancelFee) {
	this.cancelFee = cancelFee;
    }

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

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

	}
	return returnValue;
    }

    /**
     * Provides for a given id the corresponding public presentation.
     * 
     * @param presentationId
     *                the id of a presentation
     * @return DistinctVector instance containing all {@link PublicPresentation}
     *         instances which meets the conditions mentioned above.
     * @throws SQLException
     *                 If the creation, manipulation, execution or closing of a
     *                 PreparedStatement instance used for querying the database
     *                 fails.
     */
    public static PublicPresentation getPublicPresentation(int presentationId)
	    throws SQLException {
	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();
	keyConstraint.add(new DBConstraint(PublicPresentation.getColumns().get(
		0).getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		presentationId), DBConstraint.CONJ_END));
	DistinctVector<PublicPresentation> presentations = PublicPresentation
		.getVectorFromDB(keyConstraint, null);
	return presentations.iterator().next();
    }

    /**
     * Returns the number of the current entrants of this public presentation.
     * 
     * @return number of current entrants.
     * @throws SQLException
     *                 If the creation, manipulation, execution or closing of a
     *                 PreparedStatement instance used for querying the database
     *                 fails. Of if the database delivers a empty result set.
     */
    public int getCurrentEntrantsCount() throws SQLException {
	PreparedStatement entrantsCountStmt = DBAccess.dbAccess
		.getPreparedStatement(publicPresentationCurrentEntrantsString);

	entrantsCountStmt.setInt(1, id);

	ResultSet res = entrantsCountStmt.executeQuery();

	if (res.next()) {
	    int val = res.getInt(1);
	    res.close();
	    entrantsCountStmt.close();
	    return val;
	}

	else {
	    res.close();
	    entrantsCountStmt.close();
	    throw new SQLException();
	}
    }

}
