package semorg.sql.tables;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
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.KeyPair;
import semorg.sql.util.Utility;

/**
 * Models the association "Instructor" between the classes Lecturer and
 * Presentation: a Lecturer is an Instructor of a Presentation (association has
 * a * cardinality).
 */
public class Instructor extends AbstractTable {

    /**
     * A list of id-pairs of locked records. This list is meant to prevent the
     * manipulation / deletion of records whose association is currently edited.
     */
    public static List<KeyPair> lockedIds = new ArrayList<KeyPair>();

    /** The id of the lecturer. */
    protected int lecturerId;

    /** The id of the presentation. */
    protected int presentationId;

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

    /** SQL query to insert a instructor record. */
    // TODO: renaming
    protected static String supervisorInsertString = "INSERT INTO " + tableName
	    + " VALUES(?, ?, ?, ?)";

    /** SQL query to delete a instructor record. */
    // TODO: renaming
    protected static String supervisorDeleteString = "DELETE FROM " + tableName
	    + " WHERE lecturerid=? AND presentationid=?";

    /** SQL statement for creating the table "instructor" (if not exists). */
    private static String createTableSQLString = "CREATE TABLE IF NOT EXISTS `"
	    + tableName
	    + "` ("
	    + "`lecturerid` integer NOT NULL,"
	    + "`presentationid` integer NOT NULL,"
	    + "`creationdate` timestamp NOT NULL default '0000-00-00 00:00:00',"
	    + "`modificationdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
	    + "PRIMARY KEY  (`lecturerid`, `presentationid`),"
	    + "CONSTRAINT `instr_lecturer_fk` FOREIGN KEY (`lecturerid`) REFERENCES `lecturer` (`person_fk`) ON DELETE CASCADE, "
	    + "CONSTRAINT `instr_pres_fk` FOREIGN KEY (`presentationid`) REFERENCES `presentation` (`id_pk`) ON DELETE CASCADE)";

    /** Creates an instance of the class Instructor with the given parameters. */
    public Instructor(int lecturerId, int presentationId,
	    Timestamp creationDate, Timestamp modificationDate) {

	super(creationDate, modificationDate);

	this.lecturerId = lecturerId;
	this.presentationId = presentationId;
    }

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

    /**
     * Inserts a instructor record into the db using the
     * {@link #supervisorInsertString}. Finally it fires a
     * TableChangedEvent-Listener with the appropriate type.
     * 
     * @see semorg.gui.util.DBTableChangedListener - overview of all listener
     *      types
     * @throws SQLException
     *                 If the creation, manipulation, execution or closing of a
     *                 PreparedStatement instance used for querying the database
     *                 fails.
     */
    public void insertIntoDB() throws SQLException {
	PreparedStatement insertStmt = DBAccess.dbAccess
		.getPreparedStatement(supervisorInsertString);

	insertStmt.setInt(1, lecturerId);
	insertStmt.setInt(2, presentationId);

	insertStmt.setTimestamp(3, Utility.convertToTimestamp(creationDate));
	insertStmt
		.setTimestamp(4, Utility.convertToTimestamp(modificationDate));

	insertStmt.executeUpdate();

	insertStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_INSTRUCTOR);

    }

    /**
     * Delets a instructor record from the db using the
     * {@link #supervisorDeleteString}. Finally it fires a
     * TableChangedEvent-Listener with the appropriate type.
     * 
     * @see semorg.gui.util.DBTableChangedListener - overview of all listener
     *      types
     * @throws SQLException
     *                 If the creation, manipulation, execution or closing of a
     *                 PreparedStatement instance used for querying the database
     *                 fails.
     */
    public void deleteFromDB() throws SQLException {

	PreparedStatement deleteStmt = DBAccess.dbAccess
		.getPreparedStatement(supervisorDeleteString);

	deleteStmt.setInt(1, lecturerId);
	deleteStmt.setInt(2, presentationId);

	deleteStmt.executeUpdate();

	deleteStmt.close();

	AbstractTable
		.fireTableChangedEvent(DBTableChangedListener.TYPE_INSTRUCTOR);
    }

    /**
     * Returns a vector of {@link semorg.sql.util.DBColumn} instances according to
     * the properties of the table "instructor".
     * 
     * @return the vector of DBColumn instances according to the properties of
     *         the class Instructor.
     * @see semorg.sql.tables.AbstractTable#getColumns(String tableAbbreviation)
     */
    public static Vector<DBColumn> getColumns() {
	Vector<DBColumn> columns = new Vector<DBColumn>();
	columns.add(new DBColumn("lecturerid", Messages
		.getString("MAP.Instructor.LecturerId"), Integer.class));
	columns.add(new DBColumn("presentationid", Messages
		.getString("MAP.Instructor.PresentationId"), Integer.class));
	columns.addAll(AbstractTable.getColumns(""));
	return columns;
    }

    public int getLecturerId() {
	return lecturerId;
    }

    public int getPresentationId() {
	return presentationId;
    }
}
