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.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
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.Utility;

/**
 * This class models a person and serves as the superclass for several classes
 * which model different roles of persons, such as Client or Lecturer.
 */
public abstract class Person extends AbstractTable implements SimpleIDKey {

    /**
     * This list contains the ids of data records which are locked for further
     * editing, because they are actually edited.
     * <p>
     * If a data record is to get edited, its id will be inserted into this
     * list. Through this an exclusive access to the actual data record is
     * assured which is meant to avoid that mutiple editing of the same record
     * leads to inconsistencies in the data base.
     * </p>
     */
    public static List<Integer> lockedIds = new ArrayList<Integer>();

    /** The DB-ID. */
    protected int id;

    /** The salutation. */
    protected String salutation;

    /** The title. */
    protected String title;

    /** The firstname. */
    protected String firstname;

    /** The name (lastname). */
    protected String name;

    /** The street. */
    protected String street;

    /** The zip code. */
    protected String zipCode;

    /** The city. */
    protected String city;

    /** The country. */
    protected String country;

    /** The annex. */
    protected String annex;

    /** The phone number. */
    protected String phone;

    /** The number of the mobile. */
    protected String mobile;

    /** The fax number. */
    protected String fax;

    /** The email address. */
    protected String email;

    /** The birthday. */
    protected Date birthday;

    /** The date of the first contact. */
    protected Date firstContact;

    /** The short information. */
    protected String shortinfo;

    /** The notices. */
    protected String notices;

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

    /** Parameterized SQL statement for inserting a person. */
    protected static String personInsertString = "INSERT INTO " + tableName
	    + " VALUES(?, ?, ?, ?, ?, "
	    + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    /** SQL statement for creating the table "person" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`number_pk` integer NOT NULL auto_increment,"
	    + "`salutation` varchar(13) NOT NULL,"
	    + "`title` varchar(13),"
	    + "`firstname` varchar(30),"
	    + "`name` varchar(30) NOT NULL,"
	    + "`street` varchar(50),"
	    + "`zipCode` varchar(5),"
	    + "`city` varchar(30),"
	    + "`country` varchar(13),"
	    + "`annex` varchar(13),"
	    + "`phone` varchar(20),"
	    + "`mobile` varchar(20),"
	    + "`fax` varchar(20),"
	    + "`email` varchar(256),"
	    + "`birthday` date,"
	    + "`firstcontact` date,"
	    + "`shortinfo` varchar(200),"
	    + "`notices` varchar(200),"
	    + "`creationdate` timestamp NOT NULL default '0000-00-00 00:00:00',"
	    + "`modificationdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
	    + "PRIMARY KEY  (`number_pk`) )";

    /** Parameterized SQL statement for updating a person data record. */
    protected static String personUpdateString = "UPDATE "
	    + tableName
	    + " SET salutation=?, title=?, firstname=?, name=?, street=?, zipCode=?, "
	    + "city=?, country=?, annex=?, phone=?, mobile=?, fax=?, email=?, birthday=?, firstcontact=?, shortinfo=?, "
	    + "notices=?, modificationdate=? WHERE number_pk=?";

    /** Creates an instance of this class with the given values. */
    protected Person(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 shortinfo, String notices, Timestamp creationDate,
	    Timestamp modificationDate) {

	super(creationDate, modificationDate);

	this.id = id;
	this.salutation = salutation;
	this.title = title;
	this.firstname = firstname;
	this.name = name;
	this.street = street;
	this.zipCode = zipCode;
	this.city = city;
	this.country = country;
	this.annex = annex;
	this.phone = phone;
	this.mobile = mobile;
	this.fax = fax;
	this.email = email;
	this.birthday = birthday;
	this.firstContact = firstContact;
	this.shortinfo = shortinfo;
	this.notices = notices;
    }

    public String getAnnex() {
	return annex;
    }

    public Date getBirthday() {
	return birthday;
    }

    public String getCity() {
	return city;
    }

    public String getCountry() {
	return country;
    }

    public String getEmail() {
	return email;
    }

    public String getFax() {
	return fax;
    }

    public String getFirstname() {
	return firstname;
    }

    public String getMobile() {
	return mobile;
    }

    public String getName() {
	return name;
    }

    public String getNotices() {
	return notices;
    }

    public int getId() {
	return id;
    }

    public String getPhone() {
	return phone;
    }

    public String getSalutation() {
	return salutation;
    }

    public String getShortinfo() {
	return shortinfo;
    }

    public String getStreet() {
	return street;
    }

    public String getTitle() {
	return title;
    }

    public String getZipCode() {
	return zipCode;
    }

    public Date getFirstContact() {
	return firstContact;
    }

    public void setAnnex(String annex) {
	this.annex = annex;
    }

    public void setBirthday(Date birthday) {
	this.birthday = birthday;
    }

    public void setCity(String city) {
	this.city = city;
    }

    public void setCountry(String country) {
	this.country = country;
    }

    public void setEmail(String email) {
	this.email = email;
    }

    public void setFax(String fax) {
	this.fax = fax;
    }

    public void setFirstContact(Date firstContact) {
	this.firstContact = firstContact;
    }

    public void setFirstname(String firstname) {
	this.firstname = firstname;
    }

    public void setMobile(String mobile) {
	this.mobile = mobile;
    }

    public void setName(String name) {
	this.name = name;
    }

    public void setNotices(String notices) {
	this.notices = notices;
    }

    public void setNumber(int number) {
	this.id = number;
    }

    public void setPhone(String phone) {
	this.phone = phone;
    }

    public void setSalutation(String salutation) {
	this.salutation = salutation;
    }

    public void setShortinfo(String shortinfo) {
	this.shortinfo = shortinfo;
    }

    public void setStreet(String street) {
	this.street = street;
    }

    public void setTitle(String title) {
	this.title = title;
    }

    public void setZipCode(String zipCode) {
	this.zipCode = zipCode;
    }

    /**
     * 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 "person" 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 createPersonTable(Statement statement)
	    throws SQLException {
	statement.execute(createTableSQLString);
	statement.close();
    }

    /**
     * Converts the actual Person object into a database person record and
     * inserts it into the table person. Furthermore this method queries the
     * auto-generated id, sets the appropriate property {@link #id} and returns
     * the id. If a salutation, title, country or annex has been inserted into
     * the form, this method also inserts the concerning properties into the
     * table "enumeration" for later use. Finally it fires a
     * TableChanged-EventListener to update the UI.
     * 
     * @return the auto-generated id of the inserted person-tupel.
     * @throws SQLException
     *                 If the auto-generated id can't resolved from the db, i.e.
     *                 it hasnt been generated, or the PreparedStatement
     *                 instance can't be created or executed etc.
     */
    public int insertIntoDB() throws SQLException {
	PreparedStatement insertPersonStmt = DBAccess.dbAccess
		.getPreparedStatement(Person.personInsertString);

	// setting the parametes in the parameterized sql statement
	// PK with AUTO_INCREMENT: has to be set by the dbms
	insertPersonStmt.setNull(1, Types.INTEGER);
	// NOT NULL --> mandatory field
	insertPersonStmt.setString(2, salutation);
	insertPersonStmt.setString(3, title);
	insertPersonStmt.setString(4, firstname);
	// NOT NULL --> mandatory field
	insertPersonStmt.setString(5, name);
	insertPersonStmt.setString(6, street);
	insertPersonStmt.setString(7, zipCode);
	insertPersonStmt.setString(8, city);
	insertPersonStmt.setString(9, country);
	insertPersonStmt.setString(10, annex);
	insertPersonStmt.setString(11, phone);
	insertPersonStmt.setString(12, mobile);
	insertPersonStmt.setString(13, fax);
	insertPersonStmt.setString(14, email);

	if (birthday != null)
	    insertPersonStmt.setDate(15, Utility.convertToSQLDate(birthday));
	else
	    insertPersonStmt.setNull(15, Types.DATE);

	if (firstContact != null)
	    insertPersonStmt
		    .setDate(16, Utility.convertToSQLDate(firstContact));
	else
	    insertPersonStmt.setNull(16, Types.DATE);

	insertPersonStmt.setString(17, shortinfo);
	insertPersonStmt.setString(18, notices);
	insertPersonStmt.setTimestamp(19, Utility
		.convertToTimestamp(creationDate));
	insertPersonStmt.setTimestamp(20, Utility
		.convertToTimestamp(modificationDate));

	insertPersonStmt.executeUpdate();

	// getting the auto-generated key
	ResultSet rs = insertPersonStmt.getGeneratedKeys();
	if (rs.next()) {
	    this.id = rs.getInt(1);
	} else {
	    // TODO: throw the correct exception
	    throw new SQLException();
	}

	rs.close();
	insertPersonStmt.close();

	if (salutation.length() > 0)
	    Enumeration.insertSalutationStringInDB(salutation);
	if (title.length() > 0)
	    Enumeration.insertTitleStringInDB(title);
	if (country.length() > 0)
	    Enumeration.insertcountryStringInDB(country);
	if (annex.length() > 0)
	    Enumeration.insertAnnexStringInDB(annex);

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_PERSON);

	return this.id;
    }

    /**
     * Converts the actual Person object into a database person record and
     * updates a existing record to the changes made. If a salutation, title,
     * country or annex has been inserted into the form, this method also
     * inserts the concerning properties into the table "enumeration" for later
     * use. Finally it fires a TableChanged-EventListener to update the UI.
     * 
     * @throws SQLException
     *                 If the PreparedStatement instance can't be created or
     *                 executed, this exception is thrown.
     */
    public void updateDB() throws SQLException {

	PreparedStatement updatePersonStmt = DBAccess.dbAccess
		.getPreparedStatement(personUpdateString);

	updatePersonStmt.setString(1, salutation);
	updatePersonStmt.setString(2, title);
	updatePersonStmt.setString(3, firstname);
	updatePersonStmt.setString(4, name);
	updatePersonStmt.setString(5, street);
	updatePersonStmt.setString(6, zipCode);
	updatePersonStmt.setString(7, city);
	updatePersonStmt.setString(8, country);
	updatePersonStmt.setString(9, annex);
	updatePersonStmt.setString(10, phone);
	updatePersonStmt.setString(11, mobile);
	updatePersonStmt.setString(12, fax);
	updatePersonStmt.setString(13, email);

	if (birthday != null)
	    updatePersonStmt.setDate(14, Utility.convertToSQLDate(birthday));
	else
	    updatePersonStmt.setNull(14, Types.DATE);

	if (firstContact != null)
	    updatePersonStmt
		    .setDate(15, Utility.convertToSQLDate(firstContact));
	else
	    updatePersonStmt.setNull(15, Types.DATE);

	updatePersonStmt.setString(16, shortinfo);
	updatePersonStmt.setString(17, notices);

	updatePersonStmt.setTimestamp(18, new Timestamp(System
		.currentTimeMillis()));

	// WHERE - Part
	updatePersonStmt.setInt(19, id);

	updatePersonStmt.executeUpdate();

	updatePersonStmt.close();

	if (salutation.length() > 0)
	    Enumeration.insertSalutationStringInDB(salutation);
	if (title.length() > 0)
	    Enumeration.insertTitleStringInDB(title);
	if (country.length() > 0)
	    Enumeration.insertcountryStringInDB(country);
	if (annex.length() > 0)
	    Enumeration.insertAnnexStringInDB(annex);

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_PERSON);
    }

    /**
     * Removes the given elements from the table "person".
     * <p>
     * <i>Hint:</i> The given elements should be ONLY deleted from the table
     * "person", i.e. NOT in the extending classes/tables, because the rest is
     * done by the dbms in order to fullfill a FK-constraint with an
     * ON-DELETE-cascade.
     * </p>
     * Due to the FK connections (with an ON-DELETE-cascade) this method fires
     * several TableChanged-Events to update the UI.
     * 
     * @param selectedElements
     *                the elements, which should be deleted from the database.
     * @throws SQLException
     *                 Throws a SQL exception if the Statement instance can't be
     *                 created or executed.
     */
    public static void removeFromDB(Iterable selectedElements)
	    throws SQLException {
	String deleteString = "DELETE FROM " + tableName + " WHERE ";
	Iterator<Person> it = selectedElements.iterator();

	// insert first element
	int idToDelete = it.next().getId();
	deleteString += "number_pk=" + idToDelete + " ";

	// insert the rest of the elements
	while (it.hasNext()) {
	    idToDelete = it.next().getId();
	    deleteString += "OR number_pk=" + idToDelete + " ";
	}
	Statement stmt = DBAccess.dbAccess.getStatement();
	stmt.executeUpdate(deleteString);
	stmt.close();

	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_PERSON);
	// because of FK-connections (on delete cascade)
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_ASSOCIATE);
	AbstractTable.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENT);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_LECTURER);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_BOOKING);
	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_CLIENTBOOKING);
    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the table "person". 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 = new Vector<DBColumn>();
	// add all columns from the person table
	columns.add(new DBColumn("p.number_pk", Messages
		.getString("MAP.Person.NumberPK"), Integer.class));
	columns.add(new DBColumn("p.salutation", Messages
		.getString("MAP.Person.Salutation"), String.class));
	columns.add(new DBColumn("p.title", Messages
		.getString("MAP.Person.Title"), String.class));
	columns.add(new DBColumn("p.firstname", Messages
		.getString("MAP.Person.Firstname"), String.class));
	columns.add(new DBColumn("p.name", Messages
		.getString("MAP.Person.Name"), String.class));
	columns.add(new DBColumn("p.street", Messages
		.getString("MAP.Person.Street"), String.class));
	columns.add(new DBColumn("p.zipCode", Messages
		.getString("MAP.Person.ZipCode"), String.class));
	columns.add(new DBColumn("p.city", Messages
		.getString("MAP.Person.City"), String.class));
	columns.add(new DBColumn("p.country", Messages
		.getString("MAP.Person.Country"), String.class));
	columns.add(new DBColumn("p.annex", Messages
		.getString("MAP.Person.Annex"), String.class));
	columns.add(new DBColumn("p.phone", Messages
		.getString("MAP.Person.Phone"), String.class));
	columns.add(new DBColumn("p.mobile", Messages
		.getString("MAP.Person.Mobile"), String.class));
	columns.add(new DBColumn("p.fax", Messages.getString("MAP.Person.Fax"),
		String.class));
	columns.add(new DBColumn("p.email", Messages
		.getString("MAP.Person.Email"), String.class));
	columns.add(new DBColumn("p.birthday", Messages
		.getString("MAP.Person.Birthday"), Date.class));
	columns.add(new DBColumn("p.firstcontact", Messages
		.getString("MAP.Person.FirstContact"), Date.class));
	columns.add(new DBColumn("p.shortinfo", Messages
		.getString("MAP.Person.ShortInfo"), String.class));
	columns.add(new DBColumn("p.notices", Messages
		.getString("MAP.Person.Notices"), String.class));
	// add all columns from the AbstractTable table
	columns.addAll(AbstractTable.getColumns("p."));
	return columns;
    }

    /** Returns the id. */
    public int hashCode() {
	return id;
    }

    /**
     * If the given object is an instance of the Person class this method
     * compares the id of the given and the actual object, otherwise it calls
     * the equal-method of the AbstractTable class with the given object.
     * 
     * @param otherPerson
     *                object to be compared with the actual object.
     * @return <code>true</code> if the id of the given object and the actual
     *         object are the same, <code>false</code> otherwise.
     */
    public boolean equals(Object otherPerson) {
	if (otherPerson instanceof Person)
	    return id == (((Person) otherPerson).id);
	else
	    return super.equals(otherPerson);
    }
}
