package experimentdatabase;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Map.Entry;

/**
 * Base class representing experiment database.
 * 
 * It initializes database file, configures the connection and manages the database session.
 * 
 * @author Tomasz Pawlak
 */
public final class Database {

	private static final String CREATE_EXPERIMENTS_TABLE = "CREATE TABLE IF NOT EXISTS experiments(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)";

	/**
	 * Columns cache.
	 */
	protected final HashMap<String, TreeSet<String>> columns;

	protected final DatabaseEngine engine;

	/**
	 * Closed flag.
	 */
	private boolean closed = false;

	/**
	 * Initializes new instance of experiment database.
	 * 
	 * @param databaseFilename
	 *            Database filename.
	 * @throws DatabaseException
	 *             If an error during initialization occurs.
	 */
	public Database(String databaseFilename) {
		this(new File(databaseFilename));
	}

	/**
	 * Initializes new instance of experiment database.
	 * 
	 * @param databaseFile
	 *            Database file.
	 * @throws DatabaseException
	 *             If an error during initialization occurs.
	 */
	public Database(File databaseFile) {

		try {
			this.engine = new DatabaseEngine(databaseFile);

			try {
				this.engine.beginTransaction();
				this.prepareSchema();
				this.columns = this.getTableInfo();
				this.engine.commit();
			} catch (SQLException ex) {
				this.engine.rollback();
				throw ex;
			}
		} catch (Exception ex) {
			throw new DatabaseException("Cannot initialize database.", ex);
		}
	}

	protected DatabaseEngine getEngine() {
		return this.engine;
	}

	/**
	 * Creates database schema.
	 * 
	 * @throws SQLException
	 */
	private void prepareSchema() throws SQLException {
		PreparedStatement st;
		st = this.engine.prepareStatement(CREATE_EXPERIMENTS_TABLE);
		st.execute();
		st.close();
	}

	private HashMap<String, TreeSet<String>> getTableInfo() throws SQLException {
		HashMap<String, TreeSet<String>> tables = new HashMap<String, TreeSet<String>>();

		PreparedStatement query = this.engine.prepareStatement("SELECT name FROM sqlite_master WHERE type='table'");
		ResultSet results = query.executeQuery();

		while (results.next()) {
			String name = results.getString("name");
			tables.put(name, this.getColumnInfo(name));
		}

		results.close();
		query.close();

		return tables;
	}

	/**
	 * Gets list of columns in a given table.
	 * 
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	private TreeSet<String> getColumnInfo(String table) throws SQLException {
		TreeSet<String> columns = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER);

		PreparedStatement st = this.engine.prepareStatement("PRAGMA table_info(`" + table + "`)");
		ResultSet result = st.executeQuery();

		while (result.next()) {
			columns.add(result.getString("name"));
		}

		result.close();
		st.close();

		return columns;
	}

	/**
	 * Closes database object. It will become useless after this call. The current transaction is being rolled back.
	 */
	public void close() {
		this.checkClosed();
		this.engine.close();
		this.closed = true;
	}

	@Override
	protected void finalize() {
		if (!this.closed) {
			this.close();
		}
	}

	/**
	 * Checks if the database object is closed. If so, then an exception is thrown.
	 * 
	 * @exception DatabaseException
	 */
	private void checkClosed() {
		if (this.closed)
			throw new DatabaseException("Database is already closed");
	}

	private TreeSet<String> createTable(DataSet set) throws SQLException {
		StringBuilder builder = new StringBuilder();
		builder.append("CREATE TABLE IF NOT EXISTS `");
		builder.append(set.getTableName());
		builder.append("`(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
		for (String col : set.keySet()) {
			if ("id".equalsIgnoreCase(col))
				continue;

			builder.append(",`");
			builder.append(col);
			builder.append('`');

			if ("parent".equalsIgnoreCase(col)) {
				builder.append(" INTEGER NOT NULL REFERENCES `");
				builder.append(set.getParent().getTableName());
				builder.append("`(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED");
			} else {
				builder.append(" NUMERIC NULL");
			}
		}
		builder.append(')');

		PreparedStatement create = this.engine.prepareStatement(builder.toString());
		create.execute();
		create.close();

		TreeSet<String> currentColumns = this.getColumnInfo(set.getTableName());
		this.columns.put(set.getTableName().toLowerCase(), currentColumns);

		return currentColumns;
	}

	/**
	 * Makes sure that a tables contains columns from a given set. If not, then the columns are created.
	 * 
	 * @throws SQLException
	 * 
	 */
	private void ensureColumns(DataSet set) throws SQLException {
		// This is only a view of database, actually the database table can contain such a column even if the collection not.
		// However if the view contains a column, it is guaranteed that database table has such a column.
		TreeSet<String> currentColumns = this.columns.get(set.getTableName().toLowerCase());
		if (currentColumns == null) {
			currentColumns = this.createTable(set);

			// Even if table does not exist in database, we cannot assume that creation of a table with all columns
			// specified by the argument will fulfill our requirements. This is because, to suppress errors, we added 
			// IF NOT EXISTS clause to the following statement. In case that the table actually exists, it cannot 
			// have all required columns. So, continue...
		}

		for (String col : set.keySet()) {
			if (!currentColumns.contains(col)) {
				try {
					// create column in database
					// Note that the NUMERIC type is only a hint to database to try to convert the given value to 
					// INTEGER or REAL if possible, otherwise the value is stored as supplied (e.g. as TEXT).
					// INTEGER and REAL values are supposed to consume less storage space than the corresponding 
					// TEXT values. What's important the data type conversions are made mostly implicitly by 
					// database engine executing query. See http://www.sqlite.org/datatype3.html for more details.
					PreparedStatement addColumn = this.engine.prepareStatement(
							"ALTER TABLE `" + set.getTableName() + "` ADD COLUMN `" + col + "` NUMERIC NULL");
					addColumn.execute();
					addColumn.close();

				} catch (SQLException ex) {
					//just ignore it
				}

				currentColumns.add(col);
				// now the column exists both in our view and in database
			}
		}
	}

	/**
	 * Builds insert statement for a given table and dictionary of values. The statement is created in INSERT OR REPLACE
	 * mode, so if the value collection contains primary key, that is already occupied, than the entire tuple is being
	 * replaced, in effect if statement execution.
	 * 
	 * @param table
	 *            Table name.
	 * @param value
	 *            Dictionary of values to insert.
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement buildInsertStatement(DataSet set) throws SQLException {
		StringBuilder builder = new StringBuilder();
		builder.append("INSERT OR REPLACE INTO `");
		builder.append(set.getTableName());
		builder.append("`");

		if (set.size() > 0) {
			builder.append('(');
			for (String col : set.keySet()) {
				builder.append('`');
				builder.append(col);
				builder.append("`,");
			}

			builder.delete(builder.length() - 1, builder.length());
			builder.append(") VALUES (");

			for (int i = 0; i < set.size(); ++i) {
				builder.append("?,");
			}

			builder.delete(builder.length() - 1, builder.length());
			builder.append(')');
		} else {
			builder.append(" DEFAULT VALUES");
		}

		return this.engine.prepareStatement(builder.toString(), Statement.RETURN_GENERATED_KEYS);
	}

	/**
	 * Writes the given values to the given table. Each key in the values dictionary refers the the column name and
	 * value refers to the tuple value. If values dictionary contains primary key and such a key is already occupied in
	 * database, then such a tuple is being completely replaced by the given values.
	 * 
	 * @param table
	 *            Table to which insert data.
	 * @param values
	 *            Values to store.
	 * @return Primary key value of inserted (or replaced) tuple.
	 * @throws SQLException
	 */
	protected int writeData(DataSet set) throws SQLException {
		this.checkClosed();

		try {
			this.engine.beginTransaction();
			this.ensureColumns(set);

			PreparedStatement insert = this.buildInsertStatement(set);
			int index = 1;
			for (Entry<String, Object> entry : set.entrySet()) {
				insert.setObject(index, entry.getValue());
				++index;
			}

			insert.executeUpdate();

			ResultSet keys = insert.getGeneratedKeys();
			int lastInsertId = -1;
			if (keys.next())
				lastInsertId = keys.getInt(1);

			keys.close();
			insert.close();

			this.engine.commit();

			return lastInsertId;
		} catch (SQLException ex) {
			this.engine.rollback();
			throw ex;
		}
	}

	/**
	 * Initializes new instance of experiment, associated with this database. The experiment is not materialized in
	 * database until explicit call of Experiment.save or Experiment.close.
	 * 
	 * @see Experiment.save
	 * @see Experiment.close
	 * 
	 * @return An instance of a new experiment.
	 */
	public Experiment newExperiment() {
		this.checkClosed();

		return new Experiment(this);
	}
}
