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 a person in the role of a lecturer.
 */
public class Lecturer extends Person {

    /** The biography of the lecturer. */
    private String bio;

    /** The daily fee. */
    private Float dailyFee;

    /** The hourly fee. */
    private Float hourlyFee;

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

    /**
     * SQL query to get all lecturer records from the database. (delives also
     * the inherited attributes)
     */
    private static String queryString = "SELECT p.number_pk, p.salutation, p.title, "
	    + "p.firstname, p.name, p.street, p.zipCode, "
	    + "p.city, p.country, p.annex, p.phone, p.mobile, p.fax, "
	    + "p.email, p.birthday, p.firstcontact, p.shortinfo, "
	    + "p.notices, l.bio, l.dailyfee, l.hourlyfee, p.creationdate, "
	    + "p.modificationdate FROM person p, "
	    + tableName
	    + " l WHERE p.number_pk=l.person_fk";

    /** SQL query to get all lecturer who instruct a certain presentation. */
    private static String instructQueryString = "SELECT p.number_pk, p.salutation, p.title, "
	    + "p.firstname, p.name, p.street, p.zipCode, "
	    + "p.city, p.country, p.annex, p.phone, p.mobile, p.fax, "
	    + "p.email, p.birthday, p.firstcontact, p.shortinfo, "
	    + "p.notices, l.bio, l.dailyfee, l.hourlyfee, p.creationdate, "
	    + "p.modificationdate FROM person p, lecturer l, instructor i "
	    + "WHERE p.number_pk=l.person_fk AND p.number_pk=i.lecturerid AND i.presentationid=?";

    /** SQL query to get all lecturer who supervise a certain presentation. */
    private static String superviseQueryString = "SELECT p.number_pk, p.salutation, p.title, "
	    + "p.firstname, p.name, p.street, p.zipCode, "
	    + "p.city, p.country, p.annex, p.phone, p.mobile, p.fax, "
	    + "p.email, p.birthday, p.firstcontact, p.shortinfo, "
	    + "p.notices, l.bio, l.dailyfee, l.hourlyfee, p.creationdate, "
	    + "p.modificationdate FROM person p, lecturer l, supervisor s "
	    + "WHERE p.number_pk=l.person_fk AND p.number_pk=s.lecturerid AND s.presentationid=?";

    /** SQL query to get all lecturer who conduct a certain presentation. */
    private static String conductQueryString = "SELECT p.number_pk, p.salutation, p.title, "
	    + "p.firstname, p.name, p.street, p.zipCode, "
	    + "p.city, p.country, p.annex, p.phone, p.mobile, p.fax, "
	    + "p.email, p.birthday, p.firstcontact, p.shortinfo, "
	    + "p.notices, l.bio, l.dailyfee, l.hourlyfee, p.creationdate, "
	    + "p.modificationdate FROM person p, lecturer l, conduct c "
	    + "WHERE p.number_pk=l.person_fk AND p.number_pk=c.lecturerid AND c.seminartypeid=?";

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

    /** SQL query to update a lecturer record. */
    private static String lecturerUpdateString = "UPDATE " + tableName
	    + " SET bio=?, dailyfee=?, hourlyfee=? WHERE person_fk=?";

    /** SQL statement for creating the table "lecturer" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`person_fk` integer NOT NULL,"
	    + "`bio` varchar(400),"
	    + "`dailyfee` float,"
	    + "`hourlyfee` float,"
	    + "PRIMARY KEY  (`person_fk`),"
	    + "CONSTRAINT `lecturer_fk` FOREIGN KEY (`person_fk`) REFERENCES `person` (`number_pk`) ON DELETE CASCADE)";

    /** Creates an instance of the class Lecturer with the given parameters. */
    public Lecturer(int id, String salutation, String title, String firstname,
	    String name, String street, String zipCode, String city,
	    String country, String annex, String phone, String mobile,
	    String fax, String email, Date birthday, Date firstContact,
	    String bio, Float dailyFee, Float hourlyFee, String shortinfo,
	    String notices, Timestamp creationDate, Timestamp modificationDate) {

	super(id, salutation, title, firstname, name, street, zipCode, city,
		country, annex, phone, mobile, fax, email, birthday,
		firstContact, shortinfo, notices, creationDate,
		modificationDate);

	this.bio = bio;
	this.dailyFee = dailyFee;
	this.hourlyFee = hourlyFee;
    }

    /**
     * Creates for each element in a given ResultSet instance an Lecturer object
     * and returns a vector with the resulting Lecturer instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created Lecturer
     *         instances.
     */
    private static DistinctVector<Lecturer> getVector(ResultSet resultSet) {
	DistinctVector<Lecturer> set = new DistinctVector<Lecturer>();
	try {
	    while (resultSet.next()) {
		int number = resultSet.getInt("number_pk");
		String salutation = resultSet.getString("salutation");
		String title = resultSet.getString("title");
		String firstname = resultSet.getString("firstname");
		String name = resultSet.getString("name");
		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");
		String phone = resultSet.getString("phone");
		String mobile = resultSet.getString("mobile");
		String fax = resultSet.getString("fax");
		String email = resultSet.getString("email");
		Date birthday = resultSet.getDate("birthday");
		Date firstContact = resultSet.getDate("firstcontact");
		String shortinfo = resultSet.getString("shortinfo");
		String notices = resultSet.getString("notices");
		String bio = resultSet.getString("bio");
		Float dailyFee = resultSet.getFloat("dailyfee");
		if (resultSet.wasNull())
		    dailyFee = null;
		Float hourlyFee = resultSet.getFloat("hourlyfee");
		if (resultSet.wasNull())
		    hourlyFee = null;
		Timestamp creationDate = resultSet.getTimestamp("creationdate");
		Timestamp modificationDate = resultSet
			.getTimestamp("modificationdate");

		set.add(new Lecturer(number, salutation, title, firstname,
			name, street, zipCode, city, country, annex, phone,
			mobile, fax, email, birthday, firstContact, bio,
			dailyFee, hourlyFee, shortinfo, notices, 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 Lecturer 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 Lecturer
     *         instances.
     * @throws SQLException
     *                 If the PreparedStatement can't be created or the
     *                 execution of the query fails this exception is thrown.
     */
    public static DistinctVector<Lecturer> getVectorFromDB(
	    Vector<DBConstraint> additionalConstraints, String sortString)
	    throws SQLException {

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

	ResultSet rs = stmt.executeQuery();
	DistinctVector<Lecturer> returnValue = Lecturer.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 "lecturer" 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 or closing of the given statement fails.
     */
    public static void createLecturerTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * Inserts a lecturer record into the db. To do so, it first inserts a
     * record into the table "person", secondly a record into the table
     * "lecturer". These two records are linked by a PK-FK-association.
     * 
     * @see semorg.sql.tables.Person#insertIntoDB()
     * @return the id of the inserted lecturer record.
     */
    public int insertIntoDB() throws SQLException {

	super.insertIntoDB();

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

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

	insertStmt.setString(2, bio);

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

	if (hourlyFee != null)
	    insertStmt.setFloat(4, hourlyFee);
	else
	    insertStmt.setNull(4, Types.FLOAT);

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_LECTURER);

	return id;
    }

    /**
     * Updates a lecturer record in the database. To do so, it firstly updates
     * the corresponding record in the "person" table and secondly the record in
     * the "lecturer" table.
     * 
     * @see semorg.sql.tables.Person#updateDB()
     */
    public void updateDB() throws SQLException {
	super.updateDB();

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

	updateStmt.setString(1, bio);

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

	if (hourlyFee != null)
	    updateStmt.setFloat(3, hourlyFee);
	else
	    updateStmt.setNull(3, Types.FLOAT);

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

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_LECTURER);
    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the tables "person" and "lecturer".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class Lecturer.
     * @see semorg.sql.tables.Person#getColumns()
     * @see semorg.sql.tables.AbstractTable#getColumns(String tableAbbreviation)
     */
    public static Vector<DBColumn> getColumns() {
	Vector<DBColumn> columns = Person.getColumns();
	columns.add(columns.size() - 2, new DBColumn("l.bio", Messages
		.getString("MAP.Lecturer.Bio"), String.class));
	columns.add(columns.size() - 2, new DBColumn("l.dailyfee", Messages
		.getString("MAP.Lecturer.DailyFee"), Float.class));
	columns.add(columns.size() - 2, new DBColumn("l.hourlyfee", Messages
		.getString("MAP.Lecturer.HourlyFee"), Float.class));
	return columns;
    }

    public String getBio() {
	return bio;
    }

    public void setBio(String bio) {
	this.bio = bio;
    }

    public Float getDailyFee() {
	return dailyFee;
    }

    public void setDailyFee(Float dailyFee) {
	this.dailyFee = dailyFee;
    }

    public Float getHourlyFee() {
	return hourlyFee;
    }

    public void setHourlyFee(Float hourlyFee) {
	this.hourlyFee = hourlyFee;
    }

    /**
     * Returns for a DB-ID the Lecturer instance according to the record which
     * has the next bigger id.
     * 
     * @param lecturerId
     *                the key of the current record
     * @return the Lecturer instance corresponding to the next record in the
     *         table "lecturer" or if it does not exist the Lecturer instance
     *         with the smallest id.
     * @see semorg.sql.tables.AbstractTable#getNext(String, DBColumn, int)
     */
    public static Lecturer getNext(int lecturerId) {
	Lecturer returnValue = null;
	ResultSet rs = AbstractTable.getNext(queryString, getColumns().get(0),
		lecturerId);
	if (rs != null) {
	    // first Lecturer object in the vector is the return value
	    returnValue = Lecturer.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 Lecturer instance according to the record which
     * has the next smaller id.
     * 
     * @param lecturerId
     *                the key of the current record
     * @return the Lecturer instance corresponding to the previous record in the
     *         table "lecturer" or if it does not exist the Lecturer instance
     *         with the biggest id.
     * @see semorg.sql.tables.AbstractTable#getPrevious(String, DBColumn, int)
     */
    public static Lecturer getPrevious(int lecturerId) {
	Lecturer returnValue = null;
	ResultSet rs = AbstractTable.getPrevious(queryString, getColumns().get(
		0), lecturerId);
	if (rs != null) {
	    returnValue = Lecturer.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 presentation id all lecturers who instruct the
     * presentation according to the given id.
     * 
     * @param presentationId
     *                the id of a presentation
     * @return DistinctVector instance containing all Lecturer 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 DistinctVector<Lecturer> getLectInstructing(int presentationId)
	    throws SQLException {

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

	stmt.setInt(1, presentationId);// sets the first placeholder to the
	// given id

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

    /**
     * Provides for a given presentation id all lecturers who supervise the
     * presentation according to the given id.
     * 
     * @param presentationId
     *                the id of a presentation
     * @return DistinctVector instance containing all Lecturer 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 DistinctVector<Lecturer> getLectSupervising(int presentationId)
	    throws SQLException {

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

	stmt.setInt(1, presentationId);

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

    /**
     * Provides for a given presentation id all lecturers who conduct the
     * presentation according to the given id.
     * 
     * @param seminarTypeId
     *                the id of a presentation
     * @return DistinctVector instance containing all Lecturer 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 DistinctVector<Lecturer> getLectConducting(int seminarTypeId)
	    throws SQLException {

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

	stmt.setInt(1, seminarTypeId);

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

}
