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 client. */
public class Client extends Person {

    /** The task of the client in a company. */
    private String task;

    /** The turnover produced by a client. */
    private Float turnover;

    /** The id of the company which employs the client. */
    private int employerId;

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

    /**
     * SQL query to get all client 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, c.task, c.turnover, c.employer_fk, p.creationdate, p.modificationdate FROM person p, "
	    + tableName + " c WHERE p.number_pk=c.person_fk";

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

    /** SQL query to update a client record. */
    private static String clientUpdateString = "UPDATE " + tableName
	    + " SET task=?, turnover=?, employer_fk=? WHERE person_fk=?";

    /** SQL statement for creating the table "client" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`person_fk` integer NOT NULL,"
	    + "`task` varchar(50),"
	    + "`turnover` float,"
	    + "`employer_fk` integer,"
	    + "PRIMARY KEY  (`person_fk`),"
	    + "CONSTRAINT `employer_fk` FOREIGN KEY (`employer_fk`) REFERENCES `company` (`id_pk`) ON DELETE SET NULL, "
	    + "CONSTRAINT `client_fk` FOREIGN KEY (`person_fk`) REFERENCES `person` (`number_pk`) ON DELETE CASCADE)";

    /** Creates an instance of the class Client with the given parameters. */
    public Client(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 task, Float turnover, int employerId, 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.task = task;
	this.turnover = turnover;
	this.employerId = employerId;
    }

    /**
     * Retrieves the employer of a client (if exists) and returns its short
     * name.
     * 
     * @return The short name of the employer of a client if the client is
     *         employed by a company which is represented in the database,
     *         otherwise the empty string.
     */
    public String getEmployerDescription() {
	// "unemployed" client
	if (employerId == Company.NULL_ID)
	    return "";

	// keyconstraint: id_pk = employerId
	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();
	keyConstraint.add(new DBConstraint(Company.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		employerId), DBConstraint.CONJ_END));

	// set of the companies which employ the client
	DistinctVector<Company> employers;
	try {
	    employers = Company.getVectorFromDB(keyConstraint, null);
	} catch (SQLException e) {
	    return "";
	}
	if (employers.size() >= 1) {
	    Company employer = employers.iterator().next();
	    return employer.getShortName();
	} else
	    return "";
    }

    /**
     * Creates for each element in a given ResultSet instance an Client object
     * and returns a vector with the resulting Client instances.
     * 
     * @param resultSet
     *                given ResultSet instance.
     * @return a DistinctVector instance filled with the created Client
     *         instances.
     */
    private static DistinctVector<Client> getVector(ResultSet resultSet) {
	DistinctVector<Client> set = new DistinctVector<Client>();
	try {
	    while (resultSet.next()) {
		int id = 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 task = resultSet.getString("task");
		Float turnover = resultSet.getFloat("turnover");
		if (resultSet.wasNull())
		    turnover = null;
		int employerId = resultSet.getInt("employer_fk");

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

		set.add(new Client(id, salutation, title, firstname, name,
			street, zipCode, city, country, annex, phone, mobile,
			fax, email, birthday, firstContact, task, turnover,
			employerId, 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 Client 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 Client
     *         instances.
     * @throws SQLException
     *                 if the PreparedStatement can't be created or the
     *                 execution of the query fails.
     */
    public static DistinctVector<Client> getVectorFromDB(
	    Vector<DBConstraint> additionalConstraints, String sortString)
	    throws SQLException {

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

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

    /**
     * <p>
     * Inserts a client record into the db.
     * </p>
     * </p>
     * To do so, it first inserts a record into the table "person", secondly a
     * record into the table "client". 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.Person#insertIntoDB()
     */
    public int insertIntoDB() throws SQLException {

	super.insertIntoDB();

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

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

	insertStmt.setString(2, task);

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

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

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENT);

	return id;
    }

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

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

	updateStmt.setString(1, task);

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

	if (employerId != Company.NULL_ID)
	    updateStmt.setInt(3, employerId);
	else
	    updateStmt.setNull(3, Types.INTEGER);

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

	updateStmt.executeUpdate();

	updateStmt.close();

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENT);
    }

    /**
     * Returns for a DB-ID the Client instance according to the record which has
     * the next bigger id.
     * 
     * @param currentId
     *                the key of the current record
     * @return the Client instance corresponding to the next record in the table
     *         "client" or if it does not exist the Client instance with the
     *         smallest id.
     * @see semorg.sql.tables.AbstractTable#getNext(String, DBColumn, int)
     */
    public static Client getNext(int currentId) {
	Client returnValue = null;
	ResultSet rs = AbstractTable.getNext(queryString, getColumns().get(0),
		currentId);
	if (rs != null) {
	    returnValue = Client.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 Client instance according to the record which has
     * the next smaller id.
     * 
     * @param currentId
     *                the key of the current record
     * @return the Client instance corresponding to the previous record in the
     *         table "client" or if it does not exist the Client instance with
     *         the biggest id.
     * @see semorg.sql.tables.AbstractTable#getPrevious(String, DBColumn, int)
     */
    public static Client getPrevious(int currentId) {
	Client returnValue = null;
	ResultSet rs = AbstractTable.getPrevious(queryString, getColumns().get(
		0), currentId);
	if (rs != null) {
	    returnValue = Client.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 "client".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class Client.
     * @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("c.task", Messages
		.getString("MAP.Client.Task"), String.class));
	columns.add(columns.size() - 2, new DBColumn("c.turnover", Messages
		.getString("MAP.Client.TurnOver"), Float.class));
	columns.add(columns.size() - 2, new DBColumn("c.employer_fk", Messages
		.getString("MAP.Client.EmployerFK"), Integer.class));
	return columns;
    }

    public int getEmployerId() {
	return employerId;
    }

    public void setEmployerId(int employerId) {
	this.employerId = employerId;
    }

    public String getTask() {
	return task;
    }

    public void setTask(String task) {
	this.task = task;
    }

    public Float getTurnover() {
	return turnover;
    }

    public void setTurnover(Float turnover) {
	this.turnover = turnover;
    }

    /**
     * Returns a Client instance for a given client id.
     * 
     * @param clientId
     *                id of the wanted Client instance
     * @return the Client instance according to the given id
     * @throws SQLException
     *                 if the client can't retrieved from the database.
     */
    public static Client getClient(int clientId) throws SQLException {
	Vector<DBConstraint> keyConstraint = new Vector<DBConstraint>();
	// key constraint: person_fk = clientId
	keyConstraint.add(new DBConstraint(Client.getColumns().get(0)
		.getInternalColumnName(), DBConstraint.REL_EQ, new Integer(
		clientId), DBConstraint.CONJ_END));
	DistinctVector<Client> clients = Client.getVectorFromDB(keyConstraint,
		null);
	return (Client) clients.iterator().next();
    }

    /**
     * Returns all Client objects which are employed by a certain company
     * specified by a given company id.
     * 
     * @param companyId
     *                the id of a company
     * @return a vector of Client objects
     * @throws SQLException
     *                 if the creation or execution of the database query fails.
     */
    public static DistinctVector<Client> getEmployees(int companyId)
	    throws SQLException {

	String employeeQueryString = queryString + " AND c.employer_fk=?";

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

	stmt.setInt(1, companyId);

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