package com.heckmansoft.surjey.model.datastore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ParameterMetaData;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.heckmansoft.surjey.model.vo.ValueObject;
import com.heckmansoft.surjey.model.vo.list.ValueObjectList;

/**
 * Manages object-relational mapping for ValueObject objects.
 */
public class Datastore {
    static final String INITIAL_TABLE_FIELDS = "id, name";

    static DAO dao = DAO.getInstance();

    static Logger log = Logger.getLogger(Datastore.class);

    static Connection connection;
    static Transaction transaction;

    PreparedStatement statement;
    ResultSet results;

    String tableName, tableFields;

    /**
     * Constructor.
     * Note that this datastore assumes that fields "id" and "name"
     * exist in the table.  You can specify additional fields
     * using the second argument to this constructor.
     *
     * @param tableName The table being serviced by this datastore.
     * @param addTableFields A comma delimited string of additional fields.
     */
    public Datastore(String tableName, String addTableFields) {
        try {
            getConnection();
            getTransaction();
        } catch (DatastoreException e) {
            throw new RuntimeException(e);
        }
        String tableFields = INITIAL_TABLE_FIELDS;
        if (addTableFields != null) tableFields += ", " + addTableFields;
        this.tableName = tableName;
        this.tableFields = tableFields;
    }

    /**
     * Get the current connection.
     * Obtains a new one from the DAO if there is no current
     * connection or the current connection is closed.
     * @return The current connection.
     * @throws DatastoreException
     */
    public Connection getConnection() throws DatastoreException {
        try {
            // Check if connection already initialized or is closed
            if (connection == null || connection.isClosed()) {
                connection = dao.getConnection(true);
            }

            return connection;

        } catch (SQLException e) {
            log.debug(e);
            throw new DatastoreException(e);
        }
    }

    /**
     * Get the current transaction.
     * If there is no current transaction, a new one is created using
     * the current connection.
     * If there is a current transaction, the number of users of that
     * transaction is incremented.
     * @return The current transaction.
     */
    public Transaction getTransaction() throws DatastoreException {
        try {
            if (transaction == null) {
                Connection con = getConnection();
                con.setAutoCommit(false);
                // Use the connection to create a new transaction.
                transaction = new Transaction(con);
            } else {
                // Increment the number of users of the current transaction.
                transaction.incrementUsers();
            }

            return transaction;
        } catch (SQLException e) {
            log.debug(e);
            throw new DatastoreException(e);
        }
    }

    /**
     * Finds all objects managed in this datastore.
     * @return A List containing the objects.
     */
    public ValueObjectList findAll() throws DatastoreException {
        log.debug("findAll()");
        return extractList(selectAll());
    }

    /**
     * Store an object.
     * This should only be used for objects that have not been stored
     * before.
     * @param vo The object to be stored.
     * @return true if insert was successful.
     */
    public boolean insert(ValueObject vo) throws DatastoreException {
        log.debug("insert(" + vo + ")");

        try {
            statement =
                connection.prepareStatement(
                    generateInsertSQL(),
                    Statement.RETURN_GENERATED_KEYS);

            return executeInsert(vo);

        } catch (SQLException e) {
            throw new DatastoreException(e);

        }
    }

    /**
     * Store a list of objects.
     * This should only be used for objects that have not been stored
     * before.
     * This method is more efficient than calling insert() multiple times.
     * @param vos A list of objects.
     * @return true if objects were inserted successfully
     */
    public boolean insert(ValueObjectList vos) throws DatastoreException {
        log.debug("insert(" + vos + ")");

        try {
            // Prepare the insert statement
            statement =
                connection.prepareStatement(
                    generateInsertSQL(),
                    Statement.RETURN_GENERATED_KEYS);

            // Loop through the list of objects
            Iterator iterator = vos.iterator();
            while (iterator.hasNext()) {
                // Execute the insert statement
                if (!executeInsert((ValueObject) iterator.next())) {
                    // Create failed
                    return false;
                }
            }

            return true;

        } catch (SQLException e) {
            throw new DatastoreException(e);

        }
    }

    /**
     * Updates a given object that has been stored previously.
     * @param vo The object that has been updated.
     * @return true if object is found and updated.
     */
    public boolean update(ValueObject vo) throws DatastoreException {
        log.debug("update(" + vo + ")");

        try {
            // Prepare the update statement
            statement = connection.prepareStatement(generateUpdateSQL("id = ?"));

            // Execute the statement
            return executeUpdate(vo);

        } catch (SQLException e) {
            log.debug(e);
            throw new DatastoreException(e);
        }
    }

    /**
     * Updates a list of objects that have been stored previously.
     * More efficient than calling update() multiple times.
     * @param vos A list of updated value objects.
     * @return true if update was successful.
     */
    public boolean update(ValueObjectList vos) throws DatastoreException {
        log.debug("update(" + vos + ")");

        try {
            // Prepare the update statement
            statement = connection.prepareStatement(generateUpdateSQL("id = ?"));

            // Loop through the list of objects
            Iterator iterator = vos.iterator();
            while (iterator.hasNext()) {
                // Execute the update statement
                if (! executeUpdate((ValueObject) iterator.next())) {
                    // Update failed
                    return false;
                }
            }

            return true;

        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    /**
     * Remove a stored object.
     * @param vo Object to be removed.
     * @return true if a matching object is found and removed
     */
    public boolean remove(ValueObject vo) throws DatastoreException {
        return remove(vo.getId());
    }

    /**
     * Remove a stored object with given id.
     * @param id Id of object to be removed.
     * @return true if a matching object is found and removed
     */
    public boolean remove(String id) throws DatastoreException {
        log.debug("remove(" + id + ")");

        try {
            // Prepare the update statement
            statement = connection.prepareStatement(generateDeleteSQL("id = ?"));

            return executeDelete(id);

        } catch (SQLException e) {
            log.debug(e);
            throw new DatastoreException(e);
        }
    }

    /**
     * Remove a list of stored objects.
     * More efficient than calling remove() multiple times.
     * @param vos A list of objects to be removed.
     * @return true if remove was successful.
     */
    public boolean remove(ValueObjectList vos) throws DatastoreException {
        log.debug("remove(" + vos + ")");

        try {
            // Prepare the update statement
            statement = connection.prepareStatement(generateDeleteSQL("id = ?"));

            String id;
            ValueObject vo;

            // Loop through the list of objects
            Iterator iterator = vos.iterator();
            while (iterator.hasNext()) {
                vo = ((ValueObject) iterator.next());

                // Execute the statement
                if (! executeDelete(vo.getId())) {
                    // Update failed
                    return false;
                }
            }

            return true;

        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    //--------------------------------------------------------------
    // Utility/Helper methods
    //--------------------------------------------------------------

    /**
     * Finds all objects for current table.
     * @return A result set containing the results of the query.
     */
    protected ResultSet selectAll() throws DatastoreException {
        log.debug("selectAll()");
        return selectWhere(null, null);
    }

    /**
     * Finds a particular object by its id in the current table.
     * @param id the id of the object to find.
     * @return A result set containing the results of the query.
     */
    protected ResultSet selectById(String id) throws DatastoreException {
        log.debug("selectById("+id+")");
        List args = new ArrayList();
        args.add(Integer.valueOf(id));
        return selectWhere("id = ?", args);
    }

    /**
     * Finds all objects in the current table matching a given
     * set of criteria.
     * @param whereClause The SQL where clause to use.
     * @param args Array of Integers or Strings, with which to
     * populate the where clause.
     * @return A result set containing the results of the query.
     */
    protected ResultSet selectWhere(String whereClause, List args) throws DatastoreException {
        log.debug("selectById("+whereClause+","+args+")");
        try {
            // Prepare the statement
            statement =
                connection.prepareStatement(generateSelectSQL(whereClause));

            // Populate the statement's where clause
            populateStatement(args);

            // Execute query and get results
            return statement.executeQuery();

        } catch (SQLException e) {
            throw new DatastoreException(e);

        }
    }

    /**
     * Populate and execute the prepared insert statement.
     * @param vo The value object to use to populate the insert statement.
     * @return true if insert was successful.
     * @throws SQLException
     */
    protected boolean executeInsert(ValueObject vo) throws DatastoreException {
        log.debug("executeInsert("+vo+")");

        // Get the arguments for the insert
        List args = getArgs(vo);
        args.add(0,Integer.valueOf("0"));

        // Populate the prepared statement
        populateStatement(args);

        boolean status;

        try {
            // Execute the statement
            status = (statement.executeUpdate() == 1);

            // Check if statement executed successfully
            if (status) {
                // Get the id of the newly stored object
                results = statement.getGeneratedKeys();
                if (results.next()) {
                    vo.setId(results.getString(1));
                }
            }

            return status;

        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    /**
     * Populate and execute the prepared update statement.
     * @param vo The value object to use to populate the update statement.
     * @return true if update was successful.
     * @throws SQLException
     */
    protected boolean executeUpdate(ValueObject vo) throws DatastoreException {
        // Get the arguments for the update
        List args = getArgs(vo);

        args.add(Integer.valueOf(vo.getId()));

        // Populate the prepared statement
        populateStatement(args);

        try {
            // Execute the statement
            return (statement.executeUpdate() == 1);
        } catch (SQLException e) {
            throw new DatastoreException(e);

        }
    }

    /**
     * Populate and execute the prepared delete statement.
     * @param id The id to use in populating the statement.
     * @return true if update was successful.
     * @throws SQLException
     */
    protected boolean executeDelete(String id) throws DatastoreException {
        try {
            // Populate the prepared statement
            statement.setInt(1, Integer.parseInt(id));

            // Execute the statement
            return (statement.executeUpdate() == 1);

        } catch (SQLException e) {
            throw new DatastoreException(e);

        }

    }

    /**
     * Generate an SQL select statement.
     * @param whereClause Optional where clause to add to statement.
     * @return The SQL statement.
     */
    protected String generateSelectSQL(String whereClause) {
        log.debug("generateSelectSQL("+whereClause+")");

        String stm = "SELECT " + tableFields + " FROM " + tableName;
        if (whereClause != null) {
            stm += " WHERE " + whereClause;
        }
        return stm;
    }

    /**
     * Generate an SQL insert statement.
     * @return The SQL statement.
     */
    protected String generateInsertSQL() {
        log.debug("generateInsertSQL()");

        String[] fields = tableFields.split(", *");
        if (fields.length == 0) {
            return null;
        }

        String values = "?";
        for (int i = 1; i < fields.length; i++) {
            values += ", ?";
        }

        return "INSERT INTO " + tableName + " VALUES (" + values + ")";
    }

    /**
     * Generate an SQL update statement.
     * @param whereClause Optional where clause to add to statement.
     * @return The SQL statement.
     */
    protected String generateUpdateSQL(String whereClause) {
        log.debug("generateUpdateSQL("+whereClause+")");

        String stm = "UPDATE " + tableName + " SET ";

        String[] fields = tableFields.split(", *");
        if (fields.length == 0) {
            return null;
        }

        // Skip the first field which should be "id"
        stm += fields[1] + " = ?";
        for (int i = 2; i < fields.length; i++) {
            stm += ", " + fields[i] + " = ?";
        }


        if (whereClause != null) {
            stm += " WHERE " + whereClause;
        }
        return stm;
    }

    /**
     * Generate an SQL delete statement.
     * @param whereClause Optional where clause to add to statement.
     * @return The SQL statement.
     */
    protected String generateDeleteSQL(String whereClause) {
        log.debug("generateDeleteSQL("+whereClause+")");

        String stm = "DELETE FROM " + tableName;
        if (whereClause != null) {
            stm += " WHERE " + whereClause;
        }
        return stm;
    }

    /**
     * Populate the current prepared statement, using given args.
     * @param args An array of Integers and Strings
     * @throws SQLException
     */
    protected void populateStatement(List args) throws DatastoreException {
        log.debug("populateStatement("+args+")");

        if (args == null) return;

        try {
            Object arg;

            Iterator argsIterator = args.iterator();
            for (int i = 1; argsIterator.hasNext(); i++) {
                arg = argsIterator.next();

                if (arg instanceof String) {
                    statement.setString(i, (String) arg);
                } else if (arg instanceof Integer){
                    statement.setInt(i, ((Integer) arg).intValue());
                } else if (arg == null) {
                    statement.setString(i, "");
                } else {
                    throw new DatastoreException("Unable to populate statement with arg:"+arg.toString());
                }
            }
        } catch (SQLException e) {
            throw new DatastoreException(e);

        }
    }

    /**
     * Extract a list of objects from a given result set.
     * @param results The result set.
     */
    protected ValueObjectList extractList(ResultSet results) throws DatastoreException {
        try {
            ValueObjectList list = new ValueObjectList();

            // Iterate over the result set
            while (results.next()) {
                // Extract the question and add it to the list
                list.add(extract(results));
            }

            return list;
        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    /**
     * Extract an object from the given result set.
     * @param results The result set.
     * @return The newly extracted object.
     */
    protected ValueObject extractSingle(ResultSet results) throws DatastoreException {
        try {
            if (! results.next()) return null;
            return extract(results);
        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    /**
     * Populate a given object using the given result set.
     * @param results The result set.
     * @param vo An object to populate.
     * @return The populated object.
     */
    protected ValueObject extract(ResultSet results, ValueObject vo) throws DatastoreException {
        try {
            vo.setId(results.getString("id"));
            vo.setName(results.getString("name"));
            return vo;

        } catch (SQLException e) {
            throw new DatastoreException(e);
        }
    }

    //--------------------------------------------------------------
    // Override methods
    //--------------------------------------------------------------

    /**
     * From the given object, get a list of arguments to use for
     * constructing a query or update.
     * When subclassing, this method should be overridden and called
     * via super.getArgs(), and the resulting list should be added to.
     * @param vo Object containing data to use.
     * @return A list of arguments.
     */
    protected List getArgs(ValueObject vo) {
        List args = new ArrayList();
        args.add(vo.getName());
        return args;
    }

    /**
     * Extract an object from the given result set.
     * When subclassing, this method should be overridden and called
     * via super.extract(results,foo), where foo is a new object of
     * the type being served by the subclass.
     * @param results The result set.
     * @return The newly extracted object.
     */
    protected ValueObject extract(ResultSet results) throws DatastoreException {
        return extract(results, new ValueObject());
    }

}
