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.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
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;

/**
 * Super class for all table classes. Encapsulates all properties and methods,
 * which have all table classes in common.
 */
public abstract class AbstractTable {

    /** A set of listeners, which react on table changes. */
    private static Set<DBTableChangedListener> tableChangedListeners = new HashSet<DBTableChangedListener>();

    /** ID for a not existing tupel (NULL-tupel). */
    public static final int NULL_ID = 0;

    /** Date of creation of a table record. */
    protected Timestamp creationDate;

    /** Date of modification of a table record. */
    protected Timestamp modificationDate;

    /**
     * Creates an AbstractTable instance.
     * 
     * @param creationDate
     *                the creationDate of a table record.
     * @param modificationDate
     *                the modificationDate of a table record.
     */
    public AbstractTable(Timestamp creationDate, Timestamp modificationDate) {
	this.creationDate = creationDate;
	this.modificationDate = modificationDate;
    }

    /** Returns the date of creation. */
    public Timestamp getCreationDate() {
	return creationDate;
    }

    /** Returns the date of modification. */
    public Timestamp getModificationDate() {
	return modificationDate;
    }

    /**
     * Sets the date of modification.
     * 
     * @param modificationDate
     *                the new modificationDate.
     */
    public void setModificationDate(Timestamp modificationDate) {
	this.modificationDate = modificationDate;
    }

    /**
     * Extends a SQL statement string with additional db constraints and / or an
     * ORDER-BY statement and returns the appropriate PreparedStatement object.
     * 
     * @param queryString
     *                SQL query string to be extended
     * @param additionalConstraints
     *                additional conditions of the WHERE-clause.
     * @param sortString
     *                sort string with the following structure
     *                <code>ORDER BY attribute [ASC|DSC]</code>.
     * @return PreparedStatement instance according to the extended query
     *         string.
     */
    protected static PreparedStatement createExtendedQueryString(
	    String queryString, Vector<DBConstraint> additionalConstraints,
	    String sortString) throws SQLException {
	String extendedQueryString = new String(queryString);

	if (additionalConstraints != null) {
	    extendedQueryString += " AND";

	    // appending of the additional DB-constraints
	    Iterator<DBConstraint> iter = additionalConstraints.iterator();
	    while (iter.hasNext()) {
		DBConstraint constraint = iter.next();

		extendedQueryString += " " + constraint.getColumnName();
		switch (constraint.getRelation()) {
		case DBConstraint.REL_EQ:
		    extendedQueryString += "=";
		    break;
		case DBConstraint.REL_NEQ:
		    extendedQueryString += "!=";
		    break;
		case DBConstraint.REL_GREATER:
		    extendedQueryString += ">";
		    break;
		case DBConstraint.REL_LESS:
		    extendedQueryString += "<";
		    break;
		case DBConstraint.REL_LIKE:
		    extendedQueryString += " LIKE ";
		    break;
		}

		extendedQueryString += "?"; // wild-card for the parameters

		// if more than one DB-constraint
		if (iter.hasNext()) {
		    switch (constraint.getConjunction()) {
		    case DBConstraint.CONJ_AND:
			extendedQueryString += " AND";
			break;
		    case DBConstraint.CONJ_OR:
			extendedQueryString += " OR";
			break;
		    }
		}
	    }
	}

	if (sortString != null)
	    extendedQueryString += " " + sortString;

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

	if (additionalConstraints != null) {
	    // set the parameters in the QueryString
	    Iterator<DBConstraint> iter = additionalConstraints.iterator();
	    int i = 1;
	    while (iter.hasNext()) {
		DBConstraint constraint = iter.next();

		if (constraint.getValue() instanceof Integer)
		    stmt.setInt(i, (Integer) constraint.getValue());

		else if (constraint.getValue() instanceof Time) {
		    stmt.setTime(i, (Time) constraint.getValue());
		}

		else if (constraint.getValue() instanceof Timestamp)
		    stmt.setTimestamp(i, (Timestamp) constraint.getValue());

		else if (constraint.getValue() instanceof Date) {
		    long valueTime = ((Date) constraint.getValue()).getTime();
		    stmt.setDate(i, new java.sql.Date(valueTime));
		}

		else if (constraint.getValue() instanceof Float)
		    stmt.setFloat(i, (Float) constraint.getValue());

		else if (constraint.getValue() instanceof Boolean)
		    stmt.setBoolean(i, (Boolean) constraint.getValue());

		else {
		    String valueString = (String) constraint.getValue();
		    if (constraint.getRelation() == DBConstraint.REL_LIKE)
			valueString = '%' + valueString + '%';
		    stmt.setString(i, valueString);
		}
		i++;
	    }
	}
	return stmt;
    }

    /**
     * Creates for {@link #creationDate} and {@link #modificationDate} two
     * DBColumn instances and returns them.
     * 
     * @param tableAbbreviation
     *                The abbreviation (used as prefix) for the name of the
     *                table.
     * @return Vector with the created DBComun instances.
     */
    protected static Vector<DBColumn> getColumns(String tableAbbreviation) {
	Vector<DBColumn> columns = new Vector<DBColumn>();
	columns.add(new DBColumn(tableAbbreviation + "creationdate", Messages
		.getString("MAP.AbstractTable.CreationDate"), Timestamp.class));
	columns.add(new DBColumn(tableAbbreviation + "modificationdate",
		Messages.getString("MAP.AbstractTable.ModificationDate"),
		Timestamp.class));
	return columns;
    }

    /**
     * Checks, whether a table exists or not.
     * 
     * @return <code>true</code>, if the DESCRIBE statement can be executed
     *         for the table, <code>false</code> otherwise.
     */
    protected static boolean tableOK(String tableName) {

	try {
	    Statement stmt = DBAccess.dbAccess.getStatement();
	    stmt.execute("DESCRIBE " + tableName);
	    return true;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return false;
	}
    }

    /**
     * Returns for a data record the following one, i.e. the record whose key is
     * the next bigger to the key of the given record or, if it does not exists,
     * the record with the smallest key.
     * <p>
     * <i>Hint:</i> The mentioned behavior is only ensured, if the ID-column is
     * used as the <code>sortColumn</code>.
     * </p>
     * 
     * @param queryString
     *                the sql statement to be executed.
     * @param sortColumn
     *                the property which is used for sorting the result set of
     *                the sql statement.
     * @param currentKey
     *                the current key.
     * @return ResultSet, which inlcudes one of the above-mentioned data records
     *         or <code>null</code> if the table is empty.
     */
    protected static ResultSet getNext(String queryString, DBColumn sortColumn,
	    int currentKey) {
	try {
	    // return the record with the next bigger key
	    Statement stmt = DBAccess.dbAccess.getStatement();
	    ResultSet rs = stmt.executeQuery(queryString + " AND "
		    + sortColumn.getInternalColumnName() + ">" + currentKey
		    + " ORDER BY " + sortColumn.getInternalColumnName()
		    + " ASC LIMIT 1");

	    // true, if rs is not empty
	    if (rs.isBeforeFirst())
		return rs;

	    rs.close();
	    stmt.close();

	    // return record with the smallest key
	    Statement stmt2 = DBAccess.dbAccess.getStatement();
	    ResultSet rs2 = stmt2.executeQuery(queryString + " ORDER BY "
		    + sortColumn.getInternalColumnName() + " ASC LIMIT 1");

	    // true, if rs is not empty
	    if (rs2.isBeforeFirst())
		return rs2;

	    rs2.close();
	    stmt2.close();

	    // no rows in the db table
	    return null;

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

    /**
     * Returns for a data record the previous one, i.e. the record whose key is
     * the next smaller to the key of the given record or, if it does not
     * exists, the record with the biggest key.
     * <p>
     * <i>Hint:</i> The mentioned behavior is only ensured, if the ID-column is
     * used as the <code>sortColumn</code>.
     * </p>
     * 
     * @param queryString
     *                the sql statement to be executed.
     * @param sortColumn
     *                the property which is used for sorting the result set of
     *                the sql statement.
     * @param currentKey
     *                the current key.
     * @return ResultSet, which inlcudes one of the above-mentioned data records
     *         or <code>null</code> if the table is empty.
     */
    protected static ResultSet getPrevious(String queryString,
	    DBColumn sortColumn, int currentKey) {
	try {
	    // return the record with the next smaller key
	    Statement stmt = DBAccess.dbAccess.getStatement();
	    ResultSet rs = stmt.executeQuery(queryString + " AND "
		    + sortColumn.getInternalColumnName() + "<" + currentKey
		    + " ORDER BY " + sortColumn.getInternalColumnName()
		    + " DESC LIMIT 1");

	    if (rs.isBeforeFirst())
		return rs;

	    rs.close();
	    stmt.close();

	    // return record with the biggest key
	    Statement stmt2 = DBAccess.dbAccess.getStatement();
	    ResultSet rs2 = stmt2.executeQuery(queryString + " ORDER BY "
		    + sortColumn.getInternalColumnName() + " DESC LIMIT 1");

	    if (rs2.isBeforeFirst())
		return rs2;

	    rs2.close();
	    stmt2.close();

	    // empty db table
	    return null;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return null;
	}
    }

    /**
     * Iterates over the set of listeners, which react on changes of the table
     * and fires the one according to the wanted type.
     * 
     * @param type
     *                type of the listener, which should be fired.
     */
    protected static void fireTableChangedEvent(int type) {
	Iterator<DBTableChangedListener> iter = tableChangedListeners
		.iterator();
	while (iter.hasNext()) {
	    DBTableChangedListener listener = iter.next();
	    if (listener.getType() == type)
		listener.dBTableChanged();
	}
    }

    /**
     * Inserts a listener into a set of listeners.
     * 
     * @param listener
     *                DBTableChangedListener instance, which should be inserted.
     */
    public static void addDBTableChangedListener(DBTableChangedListener listener) {
	tableChangedListeners.add(listener);
    }

    /**
     * Removes a listener from a set of listeners.
     * 
     * @param listener
     *                DBTableChangedListener instance, which is to be removed.
     */
    public static void removeDBTableChangedListener(
	    DBTableChangedListener listener) {
	tableChangedListeners.remove(listener);
    }
}
