package edu.ucar.unidata.rosetta.repository;

import edu.ucar.unidata.rosetta.domain.Data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class JdbcDataDao extends JdbcDaoSupport implements DataDao {


    protected static Logger logger = Logger.getLogger(JdbcDataDao.class);

    private SimpleJdbcInsert insertActor;

    /**
     * Looks up and retrieves a persisted Data object using the given id.
     *
     * @param id    The id of the Data object.
     * @return      The Data object corresponding to the given id.
     * @throws DataRetrievalFailureException  If unable to lookup Data with the given id.
     */
    @Override
    public Data lookupById(int id) throws DataRetrievalFailureException {
        String sql = "SELECT * FROM data WHERE userId = ?";
        List<Data> data = getJdbcTemplate().query(sql, new DataMapper(), id);
        if (data.isEmpty()) {
            String message = "Unable to find persisted Data object corresponding to id " + id;
            logger.error(message);
            throw new DataRetrievalFailureException(message);
        }
        return data.get(0);

    }

    /**
     * Persists the information in the given data object.
     *
     * @param data  The Data object to persist.
     * @throws DataRetrievalFailureException  If unable to persist the Data object.
     */
    @Override
    public void persistData(Data data) throws DataRetrievalFailureException{
        // Verify entry doesn't already exist (it shouldn't).
        String sql = "SELECT * FROM data WHERE id = ?";
        List<Data> persisted = getJdbcTemplate().query(sql, new DataMapper(), data.getId());
        if (!persisted.isEmpty()) {
            throw new DataRetrievalFailureException("Data corresponding to id \"" +  data.getId() + "\" already exists.");
        } else {
            // Persist the data object.
            this.insertActor = new SimpleJdbcInsert(getDataSource()).withTableName("data");
            SqlParameterSource parameters = new BeanPropertySqlParameterSource(data);
            int rowsAffected = insertActor.execute(parameters);
            if (rowsAffected <= 0) {
                String message = "Unable to persist Data object corresponding to id " + data.getId();
                logger.error(message);
                throw new DataRetrievalFailureException(message);
            } else {
                logger.info("Data object corresponding to id " + data.getId() + " persisted.");
            }
        }
    }

    /**
     * Updated the information corresponding to the given data object.
     *
     * @param data  The data object to update.
     * @throws DataRetrievalFailureException  If unable to update persisted Data object.
     */
    @Override
    public void updatePersistedData(Data data) throws DataRetrievalFailureException {
        String sql = "UPDATE data SET " +
                "cfType = ?, " +
                "platform = ?, " +
                "fileName = ?, " +
                "headerLineNumbers = ?, " +
                "delimiter = ? " +
                "WHERE id = ?";
        int rowsAffected  = getJdbcTemplate().update(sql, new Object[] {
                // order matters here
                data.getCfType(),
                data.getPlatform(),
                data.getFileName(),
                data.getHeaderLineNumbers(),
                data.getDelimiter(),
                data.getId()
        });
        if (rowsAffected  <= 0) {
            String message ="Unable to update persisted Data object corresponding to id " + data.getId();
            logger.error(message);
            throw new DataRetrievalFailureException(message);
        } else {
            logger.info("Updated persisted Data object corresponding to id " + data.getId());
        }
    }

    /**
     * Deletes the persisted data object information.
     *
     * @param id    The id of the Data object to delete.
     * @throws DataRetrievalFailureException  If unable to delete persisted Data object.
     */
    @Override
    public void deletePersistedData(int id) throws DataRetrievalFailureException {
        String sql = "DELETE FROM data WHERE userId = ?";
        int rowsAffected  = getJdbcTemplate().update(sql, id);
        if (rowsAffected <= 0) {
            String message = "Unable to delete Data object corresponding to id " + id;
            logger.error(message);
            throw new DataRetrievalFailureException(message);
        } else {
            logger.info("Deleted Data object corresponding to id " + id);
        }
    }

    /**
     * DataMapper only used by JdbcDataDao.
     */
    private static class DataMapper implements RowMapper<Data> {
        /**
         * Maps each row of data in the ResultSet to the Data object.
         *
         * @param rs  The ResultSet to be mapped.
         * @param rowNum  The number of the current row.
         * @return  The populated Data object.
         * @throws SQLException  If an SQLException is encountered getting column values.
         */
        public Data mapRow(ResultSet rs, int rowNum) throws SQLException {
            Data data = new Data();
            data.setId(rs.getInt("id"));
            data.setCfType(rs.getString("cfType"));
            data.setPlatform(rs.getString("platform"));
            data.setFileName(rs.getString("fileName"));
            data.setHeaderLineNumbers(rs.getInt("headerLineNumbers"));
            data.setDelimiter(rs.getString("delimiter"));
            return data;
        }
    }

}

@@ -1112,19 +1112,22 @@
 kupById(
+Str
 in
-t
+g
  id) thr
@@ -1201,37 +1201,33 @@
 FROM data WHERE 
-userI
+i
 d = ?%22;%0A        
@@ -1866,16 +1866,17 @@
 xception
+ 
 %7B%0A      
@@ -2194,18 +2194,16 @@
 g to id 
-%5C%22
 %22 +  dat
@@ -2219,10 +2219,8 @@
  + %22
-%5C%22
  alr
@@ -3323,32 +3323,68 @@
 cfType = ?, %22 +%0A
+                %22community = ?, %22 +%0A
                 
@@ -3689,24 +3689,61 @@
 etCfType(),%0A
+                data.getCommunity(),%0A
             
@@ -4563,19 +4563,22 @@
 tedData(
+Str
 in
-t
+g
  id) thr
@@ -4662,13 +4662,9 @@
 ERE 
-userI
+i
 d = 
@@ -5669,19 +5669,22 @@
 d(rs.get
-Int
+String
 (%22id%22));
@@ -5732,24 +5732,82 @@
 %22cfType%22));%0A
+            data.setCommunity(rs.getString(%22community%22));%0A
             
@@ -5954,11 +5954,14 @@
 .get
-Int
+String
 (%22he

correct copy/paste typo