package com.randomnoun.common.spring;

/* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a
 * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html)
 */

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

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;

import org.apache.log4j.Logger;

import com.randomnoun.common.Struct;

// this could probably delegate to StructuredMapCallbackHandlerResultSetExtractor these days
// 

/**
 * This class returns structured object graphs composed of lists/maps.
 * Think of it as a poor man's JDO, using Maps and Lists rather than concrete
 * classes, which allows us to omit the POJO generation step.
 *
 * <p>This ResultReader is to be used in the JdbcTemplate class to retrieve structured
 * representations of queries covering more than one table. In a simple case, say a
 * cowGroup contains many cows, and we wish to return the cowGroup ID and name as
 * well as the cow ID and name in the one query; typically, this would be done
 * through the following hypothetical SQL:
 *
 * <pre class="code">
 * SELECT cowGroupId, cowGroupName, cowId, cowName
 * FROM cowGroups, cows
 * WHERE cows.cowGroupId = cowGroups.cowGroupId
 * </pre>
 *
 * Normally, Spring would return this as a List containing a map containing four
 * keys (cowGroupId, cowGroupName, cowId and cowName), containing the values of
 * each column returned by the RDBMS; e.g. the code fragment
 *
 * <pre class="code">
             List result1 = jt.queryForList(sql);
             System.out.println(Text.structuredListToString("result1", result1));
   </pre>
             (with the SQL above) would produce this output (a three-element list):
 * <pre class="code">
              result1 = [
                0 = {
                  COWGROUPID =&gt; 1
                  COWGROUPNAME =&gt; 'ROLLINGFIELD7'
                  COWID =&gt; 1000
                  COWNAME =&gt; 'DAISY'
                }
                1 = {
                  COWGROUPID =&gt; 2
                  COWGROUPNAME =&gt; 'SLAUGHTERHOUSE5'
                  COWID =&gt; 1001
                  COWNAME =&gt; 'BUTTERCUP'
                }
                2 = {
                  COWGROUPID =&gt; 2
                  COWGROUPNAME =&gt; 'SLAUGHTERHOUSE5'
                  COWID =&gt; 1002
                  COWNAME =&gt; 'STEVE'
                }
              ]
              </pre>
 *
 * note how both BUTTERCUP and STEVE belong to the SLAUGHTERHOUSE5 cowGroup, but
 * DAISY is within it's own cowGroup. If we wanted to express this relationship
 * by having a list of cows within each cowgroup, we would have to scan through
 * this list manually, and filter each cow into it's parent cowGroup.
 *
 * <p>This object automates this process; e.g. the same structure above, when
 * processed using the following code snippet:
 *
 * <pre class="code">
              String mapping = "cowGroupId, cowGroupName, " +
                "cowId AS cows.cowId, cowName AS cows.cowName";
              List result2 = jt.query(sql, new StructuredResultReader(mapping));
              System.out.println(Text.structuredListToString("result2", result2));
             </pre>
 * ... would produce this output (a two-element list) ...
             <pre style="code">
              result2 = [
                0 = {
                  cowGroupName =&gt; 'ROLLINGFIELD7'
                  cowGroupId =&gt; 2
                  cows = [
                    0 = {
                      cowId =&gt; 1000
                      cowName =&gt; 'DAISY'
                    }
                  ]
                }
                1 = {
                  cowGroupName =&gt; 'SLAUGHTERHOUSE5'
                  cowGroupId =&gt; 1
                  cows = [
                    0 = {
                      cowId =&gt; 1001
                      cowName =&gt; 'BUTTERCUP'
                    }
                    1 = {
                      cowId =&gt; 1002
                      cowName =&gt; 'STEVE'
                    }
                  ]
                }
              ]
              </pre>
 *
 * which may be more suitable for processing. This structure can also be used within
 * JSTL, e.g.
 *
 * <pre class="code">
 *   &lt;c:out value="${result2[0].cows[1].cowName}"/&gt;
 * </pre>
 *
 * would generate the text "STEVE".
 *
 * <p>This class can generate an arbitrary number of levels within the returned structure
 * by specifying more groupings in the mapping text. This class also performs
 * camelCasing of returned columns (e.g. to return 'cowName' rather than 'COWNAME').
 *
 * <p>The guts of the interface lies in the mapping string passed to the StructuredResultReader
 * constructor. The syntax of this mapping string is a list of column names, separated by
 * commas; a column name may also have an "AS" definition, which defines how it
 * is to be represented in the returned structure.
 *
 * <i>e.g.</i> assuming a department contains many people, this mapping will group each
 * person within their department:
 *
 * <pre class="code">
 * departmentName, personName AS people.name, personId AS people.id
 * </pre>
 *
 * which will return a List of Maps, containing two keys, 'departmentName' and 'people'.
 * The people value will be a List of Maps, containing two keys, 'name' and 'id'.
 *
 * <p>If each person now has a number of addresses, we could perform a two-level grouping,
 * using the following mapping:
 *
 * <pre class="code">
 * department, personName AS people.name, personId AS people.id,
 * addressType AS people.addresses.type
 * </pre>
 *
 * <p>Each people value will now be a List of Maps containing three keys, 'name', 'id' and
 * 'addresses'; the 'addresses' value will be a List of Maps with only one key, 'type'.
 *
 * <p>Hope that all makes sense.
 *
 * <p>Implementation note: The resultset returned by the SQL must be sorted by
 * the top-level group, then by the second-level group, and so on, for this code to
 * work as expected.
 *
 * 
 * @author knoxg
 */
public class StructuredListResultSetExtractor
    implements ResultSetExtractor<Object> {
    
    
    
    /** Logger instance for this class */
    private static Logger logger = Logger.getLogger(StructuredListResultSetExtractor.class);  
    
    /** Contains mappings from source column names to (structured) target column names */
    Map<String, String> columnMapping;

    /** List to save results in */
    private final List<Map<String, Object>> results;
    private List<String> levels;

    /** Row mapper */
    private final RowMapper<Map<String, Object>> rowMapper;
    private Map<String, Object> lastResultRow = null;

    /** The counter used to count rows */
    private int rowNum = 0;

    /**
     * Create a new RowMapperResultReader.
     * @param rowMapper the RowMapper which creates an object for each row
     */
    public StructuredListResultSetExtractor(JdbcTemplate jt, String mappings) {
        this(new ColumnMapRowMapper(), mappings, 0);
    }

    /**
     * Create a new RowMapperResultReader.
     * @param rowMapper the RowMapper which creates an object for each row
     * @param rowsExpected the number of expected rows
     * (just used for optimized collection handling)
     */
    public StructuredListResultSetExtractor(RowMapper<Map<String, Object>> rowMapper, String mappings, int rowsExpected) {
        
        if (mappings == null) { throw new NullPointerException("mappings cannot be null"); }

        // Use the more efficient collection if we know how many rows to expect:
        // ArrayList in case of a known row count, LinkedList if unknown
        this.results = (rowsExpected > 0) ? new ArrayList<Map<String, Object>>(rowsExpected) : new LinkedList<Map<String, Object>>();
        this.rowMapper = rowMapper;
        this.columnMapping = new HashMap<String, String>();
        this.levels = new ArrayList<String>(3); // we're not going to go higher than this too often

        StringTokenizer st = new StringTokenizer(mappings, ",");
        StringTokenizer st2;
        StringTokenizer st3;
        String column = null;
        String columnTarget = null;
        String token;
        String mapping;

        while (st.hasMoreTokens()) {
            mapping = st.nextToken().trim();

            if (mapping.indexOf(' ') == -1) {
                column = mapping;
                columnTarget = mapping;
            } else {
                // parse state (note that this uses a StringTokenizer, 
                // rather than a character-based parser)
                // 
                // 0 = start of parse
                // 1 = consumed column name
                // 2 = consumed 'as'
                // 3 = consumed mapping
                int state = 0; // 0=initial, 1=got column name, 2=got 'as', 3=got mapping
                st2 = new StringTokenizer(mapping, " ");
                while (st2.hasMoreTokens()) {
                    token = st2.nextToken().trim();
                    if (token.equals("")) { continue; }

                    if (state == 0) {
                        column = token;
                        state = 1;
                    } else if (state == 1) {
                        if (!token.equalsIgnoreCase("as")) {
                            throw new IllegalArgumentException("Invalid mapping '" + mapping + "'; expected AS");
                        }
                        state = 2;
                    } else if (state == 2) {
                        columnTarget = token;
                        state = 3;
                    } else if (state == 3) {
                        throw new IllegalArgumentException("Invalid mapping '" + mapping + "'; too many tokens");
                    }
                }
            }

            // check target for levels
            int levelIdx = 0;
            st3 = new StringTokenizer(columnTarget, ".");
            if (st3.hasMoreTokens()) {
                String level = st3.nextToken();

                while (st3.hasMoreTokens()) {
                    if (levelIdx < levels.size()) {
                        if (!levels.get(levelIdx).equals(level)) {
                            throw new IllegalArgumentException("Multiple lists in mapping at level " + levelIdx + ": '" + levels.get(levelIdx) + "' and '" + level + "'");
                        }
                    } else {
                        levels.add(level);
                        // System.out.println("Levels now: " + levels);
                    }
                    level = st3.nextToken();
                    levelIdx++;
                }
            }
            columnMapping.put(column.toUpperCase(), columnTarget);
        }
    }

        /** Required to support ResultSetExtractor interface
         * 
         * @param rs resultSet to process
         * 
         * @return a List of structured Maps, as described in the class javadoc
         */
    public Object extractData(ResultSet rs) throws SQLException, DataAccessException 
        {
                // int rowNum = 0;
                while (rs.next()) {
                        // results.add(this.rowMapper.mapRow(rs, rowNum++));
                        processRow(rs);
                }
                return results;
        }
    
    
    /**
     * Used by the ResultReader interface to return the results read by this class
     * 
     * @see org.springframework.jdbc.core.ResultReader#getResults()
     *
    public List getResults() {
        return results;
    }
    */

    /**
     * Used by the ResultReader interface to process a single row from the database.
     * 
     * <p>The row is read and matched against the 'levels' specified in the 
     * object constructor. As values change, tree branches are created in the returned
     * structured List. 
     * 
     * @see org.springframework.jdbc.core.RowCallbackHandler#processRow(java.sql.ResultSet)
     */
    @SuppressWarnings("unchecked")
        public void processRow(ResultSet rs)
        throws SQLException {
        Map<String, Object> row = (Map<String, Object>) rowMapper.mapRow(rs, this.rowNum++); // ClobRowMapper always returns a Map.
                // System.out.println("Processing row " + Struct.structuredMapToString("row", row));

        // logger.debug("row is " + Struct.structuredMapToJson(row));
        
        int createLevel = 0;
        List<Map<String, Object>> createList = results;
        Map<String, Object> createRow = new HashMap<String, Object>();
        String createPrefix = "";

        // determine highest level that we can create at
        if (lastResultRow != null) {
            // System.out.println("lastResultRow processing");
            createLevel = levels.size() + 1;

            // find lowest level that has a different value
            for (Iterator<Map.Entry<String, String>> i = columnMapping.entrySet().iterator(); i.hasNext();) {
                Map.Entry<String, String> entry = (Map.Entry<String, String>) i.next();
                String column = (String) entry.getKey();
                String columnTarget = (String) entry.getValue();

                List<Map<String,Object>> containerList = results;
                Map<String, Object> containerMap = lastResultRow;

                String component;
                int pos = columnTarget.indexOf('.');
                int level = 0;

                while (pos != -1) {
                    component = columnTarget.substring(0, pos);
                    columnTarget = columnTarget.substring(pos + 1);

                    if (!containerMap.containsKey(component)) {
                        throw new IllegalStateException("Missing field '" + component + "' in " + Struct.structuredMapToString("containerMap", containerMap) + "; last result row is " + Struct.structuredMapToString("lastResultRow", lastResultRow));
                    }

                    if (component.equals(levels.get(level))) {
                        level++;
                        containerList = (List<Map<String,Object>>) containerMap.get(component);
                        containerMap = (Map<String, Object>) containerList.get(containerList.size() - 1);
                        if (containerMap==null) {
                                logger.error("null containerMap");
                        }
                    } else {
                        containerMap = (Map<String, Object>) containerMap.get(component);
                        if (containerMap==null) {
                                logger.error("null containerMap");
                        }

                    }

                    pos = columnTarget.indexOf('.');
                }

                Object thisValue = row.get(column);
                Object lastValue = containerMap.get(columnTarget);
                // System.out.println("Comparing thisValue '" + thisValue + "' to lastValue '" + lastValue + "'");

                if ((thisValue == null && lastValue != null) || (thisValue != null && !thisValue.equals(lastValue))) {
                    // values are different; create row
                    if (createLevel > level) {
                        createList = containerList;

                        // System.out.println("Reducing level to '" + level + "' because of " +
                        //   "column '" + columnTarget + "' differing (" + thisValue + " instead of previous: " + lastValue);
                        createLevel = level;
                    }
                }
            }
        }

        if (createLevel > levels.size()) {
            // rows are completely identical -- don't add it to the list
            return;
        }

        for (int i = 0; i < createLevel; i++) {
            createPrefix = createPrefix + levels.get(i) + ".";
        }

        // generate 'createRow'
        for (Iterator<Map.Entry<String, String>> i = columnMapping.entrySet().iterator(); i.hasNext(); ) {
                Map.Entry<String, String> entry = i.next();
            String column = (String) entry.getKey();
            String columnTarget = (String) entry.getValue();
            if (!columnTarget.startsWith(createPrefix)) {
                continue;
            }
            Object value = row.get(column); 

            //logger.debug("About to add column '" + columnTarget + "' from rs column '" + column + "'; createPrefix = '" + createPrefix + "' with value '" + value + "'");
            
            columnTarget = columnTarget.substring(createPrefix.length());
            //logger.debug("  columnTarget '" + columnTarget + "'");

            List<Map<String, Object>> containerList = createList;
            Map<String, Object> containerMap = createRow;
            int level = createLevel;  // ** was 0 ?
            String component;
            int pos = columnTarget.indexOf('.');

            while (pos != -1) {
                component = columnTarget.substring(0, pos);
                columnTarget = columnTarget.substring(pos + 1);

                /* if (!containerMap.containsKey(component)) {
                   containerMap.put(component, new HashMap());
                   } */

                // System.out.println("Setting component '" + component + "' in " + 
                //   Codec.structuredMapToString("containerMap", containerMap));
                if (component.equals(levels.get(level))) {
                    level++;
                    containerList = (List<Map<String, Object>>) containerMap.get(component);

                    if (containerList == null) {
                        containerList = new ArrayList<Map<String,Object>>();
                        containerMap.put(component, containerList);
                        containerList.add(new HashMap<String, Object>());
                    }

                    containerMap = (Map<String, Object>) containerList.get(containerList.size() - 1);
                    if (containerMap==null) {
                        logger.error("C null containerMap");
                    }

                } else {
                    containerMap = (Map<String, Object>) containerMap.get(component);
                    if (containerMap==null) {
                        logger.error("D null containerMap");
                    }

                }

                pos = columnTarget.indexOf('.');
            }

            containerMap.put(columnTarget, value);
        }

        createList.add(createRow);
        lastResultRow = (Map<String, Object>) results.get(results.size() - 1);
        /*
        if (logger.isDebugEnabled()) {
            logger.debug("processRow complete; added row " + Struct.structuredMapToString("createRow", createRow));
            logger.debug("resultSet now = " + Struct.structuredListToString("results", results));
        }
        */
    }


}