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 person in the role of an associate. */
public class Associate extends Person {

    /** The entitlement owned by the associate. */
    private int entitlement;

    /** The password for the login. */
    private String password;

    /** The occupation performed by the associate. */
    private String occupation;

    /** The name of the DB table which holds all the records. */
    private static String tableName = "associate";

    /**
     * SQL query to get all associate 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, a.entitlement, a.password, a.occupation, p.creationdate, p.modificationdate FROM person p, "
	    + tableName + " a WHERE p.number_pk=a.person_fk";

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

    /** SQL query to update a associate record. */
    private static String associateUpdateString = "UPDATE " + tableName
	    + " SET entitlement=?, password=?, occupation=? WHERE person_fk=?";

    /** SQL statement for creating the table "associate" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`person_fk` integer NOT NULL,"
	    + "`entitlement` integer NOT NULL,"
	    + "`password` varchar(6),"
	    + "`occupation` varchar(30),"
	    + "PRIMARY KEY  (`person_fk`),"
	    + "CONSTRAINT `associate_fk` FOREIGN KEY (`person_fk`) REFERENCES `person` (`number_pk`) ON DELETE CASCADE)";

    /** Creates an instance of the class Associate with the given parameters. */
    public Associate(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,
	    int entitlement, String password, String occupation,
	    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.entitlement = entitlement;
	this.password = password;
	this.occupation = occupation;
    }

    /**
     * Creates for each element in a given ResultSet instance an Associate
     * object and returns a vector with the created Associate instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created Associate
     *         instances.
     */
    private static DistinctVector<Associate> getVector(ResultSet resultSet) {
	DistinctVector<Associate> set = new DistinctVector<Associate>();
	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");
		int entitlement = resultSet.getInt("entitlement");
		String password = resultSet.getString("password");
		String occupation = resultSet.getString("occupation");
		Timestamp creationDate = resultSet.getTimestamp("creationdate");
		Timestamp modificationDate = resultSet
			.getTimestamp("modificationdate");

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

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

	ResultSet rs = stmt.executeQuery();
	DistinctVector<Associate> returnValue = Associate.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 "associate" if it does not exist.
     * 
     * @param statement
     *                instance of the class Statement, which is used for
     *                executing the SQL statement {@link #createTableSQLString}.
     */
    public static void createAssociateTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    public int getEntitlement() {
	return entitlement;
    }

    public String getOccupation() {
	return occupation;
    }

    public String getPassword() {
	return password;
    }

    public void setEntitlement(int entitlement) {
	this.entitlement = entitlement;
    }

    public void setOccupation(String occupation) {
	this.occupation = occupation;
    }

    public void setPassword(String password) {
	this.password = password;
    }

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

	super.insertIntoDB();

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

	// FK
	insertStmt.setInt(1, id);
	// NOT NULL
	insertStmt.setInt(2, entitlement);
	insertStmt.setString(3, password);
	insertStmt.setString(4, occupation);

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ASSOCIATE);

	return id;
    }

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

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

	updateStmt.setInt(1, entitlement);
	updateStmt.setString(2, password);
	updateStmt.setString(3, occupation);

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

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ASSOCIATE);
    }

    /**
     * Returns for a DB-ID the Associate instance according to the record which
     * has the next bigger id.
     * 
     * @param associateId
     *                the key of the current record
     * @return the Associate instance corresponding to the next record in the
     *         table "associate" or if it does not exist the Associate instance
     *         with the smallest id.
     * @see semorg.sql.tables.AbstractTable#getNext(String, DBColumn, int)
     */
    public static Associate getNext(int associateId) {
	Associate returnValue = null;
	ResultSet rs = AbstractTable.getNext(queryString, getColumns().get(0),
		associateId);

	if (rs != null) {
	    // getting the Associate instance from the resultset over a vector
	    // and its interator
	    returnValue = Associate.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 Associate instance according to the record which
     * has the next smaller id.
     * 
     * @param associateId
     *                the key of the current record
     * @return the Associate instance corresponding to the previous record in
     *         the table "associate" or if it does not exist the Associate
     *         instance with the biggest id.
     * @see semorg.sql.tables.AbstractTable#getPrevious(String, DBColumn, int)
     */
    public static Associate getPrevious(int associateId) {
	Associate returnValue = null;
	ResultSet rs = AbstractTable.getPrevious(queryString, getColumns().get(
		0), associateId);
	if (rs != null) {
	    returnValue = Associate.getVector(rs).iterator().next();
	    try {
		// the statement which produced the resultset
		Statement producingStatement = rs.getStatement();
		rs.close();
		if (producingStatement != null)
		    producingStatement.close();

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

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the tables "person" and "associate".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class Associate.
     * @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("a.entitlement", Messages
		.getString("MAP.Associate.Entitlement"), Integer.class));
	columns.add(columns.size() - 2, new DBColumn("a.password", Messages
		.getString("MAP.Associate.Password"), String.class));
	columns.add(columns.size() - 2, new DBColumn("a.occupation", Messages
		.getString("MAP.Associate.Occupation"), String.class));
	return columns;
    }
}
