/*
 * Copyright 2005 MBARI
 *
 * Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, Version 2.1 
 * (the "License"); you may not use this file except in compliance 
 * with the License. You may obtain a copy of the License at
 *
 * http://www.gnu.org/copyleft/lesser.html
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */


package org.mbari.sql;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

//~--- classes ----------------------------------------------------------------

/**
 * <p>Stores data returned by a query. Use as: </p>
 * <pre>
 *  Statement s = connection.getStatement();
 *  ResultSet rs = s.executeQuery("SELECT * from FOO");
 *  // Generate QueryResult object containing all the data in the ResultSet
 *  QueryResult qr = new QueryResult(rs);
 *  s.close();
 * </pre>
 *
 * @author Brian Schlining
 * @version $Id: QueryResults.java,v 1.2 2006/02/03 00:53:38 brian Exp $
 */
public class QueryResults {

    private static final Object NO_VALUE = "!_!!_!!!___NO_VALUE___!!!_!!_!";
    private static final List<Object> NO_VALUE_LIST = new ArrayList<Object>();
    static {
        NO_VALUE_LIST.add(NO_VALUE);
    }

    //~--- fields -------------------------------------------------------------

    /**
     * Used for storing values during the coalesce method call. This map is
     * Map<String, Map<Integer, Set>> where values are:
     * Map<columnName, Map<row#, values>>
     */
    private Map<String, Map<Integer, Set<?>>> duplicateMap;
    private Map<String, List<Object>> resultsMap = new TreeMap<String, List<Object>>();

    //~--- constructors -------------------------------------------------------

    /**
     * Generates a QueryResult with the given column names. The user will need
     * to populate the data by hand when using this constructor.
     *
     * @param columnNames A colleciton of
     */
    public QueryResults(Collection<String> columnNames) {
        for (String colName : columnNames) {
            resultsMap.put(colName, new ArrayList());
        }
    }

    /**
     * Generates a QueryResult from a ResultSet. This reads the metadata and all
     * data.
     *
     * @param resultSet
     * @throws SQLException
     */
    public QueryResults(ResultSet resultSet) throws SQLException {
        /*
         * Use the metadata to fetch the names of each column
         */
        Collection<String> columnNames = new ArrayList<String>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int colCount = metaData.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            columnNames.add(metaData.getColumnLabel(i));
        }

        /*
         * Create the lists to store data for each column. Fetch the data for
         * each column
         */
        List<List<Object>> dataStores = new ArrayList<List<Object>>();    // A List of Lists
        for (String colName : columnNames) {
            List<Object> data = new ArrayList();
            resultsMap.put(colName, data);
            dataStores.add(data);
        }
        

        /*
         * Fetch the data
         */
        while (resultSet.next()) {
            for (int i = 0; i < colCount; i++) {
                List data = dataStores.get(i);
                data.add(resultSet.getObject(i + 1));
            }
        }
    }

    //~--- methods ------------------------------------------------------------

    /**
     * Tells the query resultsMap to collasce based on unique values in a column
     * @param key
     */
    public void coalesce(String key) {
        duplicateMap = new HashMap();

        /*
         * The keyColumn contains all the values (in order) for the column
         * containing the key that we are coalescing on. Typically the key
         * is a primary key. We also convert this list to an array
         */
        List<?> keyColumn = getResults(key);
        Object[] objects = keyColumn.toArray(new Object[keyColumn.size()]);
        for (int row0 = 0; row0 < objects.length; row0++) {
            Object object = objects[row0];
            if (!NO_VALUE.equals(object)) {
                int row1;
                while ((row1 = keyColumn.lastIndexOf(object)) > row0) {
                    /*
                     * We must do the following if a duplicate is found:
                     * 1) Combine the data into a single comma-separate value.
                     * 2) Tag the duplicate in the object array so we don't process it agains
                     * 3) Keep track of rows marked as duplicates so that we can remove
                     *    them from the final dataset
                     */
                    storeCoalescedValues(row0, row1);
                    objects[row1] = NO_VALUE;
                }
            }
        }

        combineCoalescedValues();
        removeDuplicates();
    }

    /**
     *
     * @return The number of columns of data in the results
     */
    public int columnCount() {
        return resultsMap.keySet().size();
    }


    /**
     * <p>Combines 'coalesced' values into a comma separated string.</p>
     *
     */
    private void combineCoalescedValues() {
        /*
         * Iterate through each columnName stored in the duplicateMap
         */
        Collection<String> columnNames = duplicateMap.keySet();
        for (String columnName : columnNames) {
            Map<Integer, Set<?>> map = duplicateMap.get(columnName);

            /*
             * Iterate through each row containing a duplicate stored for a
             * particular columnName
             */
            Collection<Integer> rows = map.keySet();
            for (Integer row : rows) {

                /*
                 * Combine all the values in a row into a comma-separated
                 * String
                 */
                Set<?> rowValues = map.get(row);
                Iterator<?> k = rowValues.iterator();
                String value = k.next().toString();
                while (k.hasNext()) {
                    value = value + ", " + k.next();
                }

                /*
                 * Store the coalesced value back in the correct row in the
                 * data storage Lists.
                 */
                resultsMap.get(columnName).set(row.intValue(), (Object) value);
            }
        }
    }

    /**
     * <p>Used to determine if the query returned a particular columnname</p>
     *
     *
     * @param columnName
     *
     * @return
     */
    public boolean containsColumnName(String columnName) {
        return resultsMap.containsKey(columnName);
    }

    //~--- get methods --------------------------------------------------------

    /**
     * <p><!-- Method description --></p>
     *
     *
     * @return
     */
    public Set<String> getColumnNames() {
        return resultsMap.keySet();
    }

    /**
     * Retrieve the data as an array. it's oriented as Object[row][col]
     * @return
     */
    public Object[][] getDataArray() {
        Set<String> keySet = resultsMap.keySet();
        int cols = keySet.size();

        /*
         * Get the length of the data
         */
        String key0 = keySet.iterator().next();
        List<Object> item = resultsMap.get(key0);
        int rows = item.size();
        Object[][] data = new Object[rows][cols];
        int col = 0;
        for (Object key : keySet) {
            List<Object> dataInColumn = resultsMap.get(key);
            for (int row = 0; row < dataInColumn.size(); row++) {
                data[row][col] = dataInColumn.get(row);
            }

            col++;
        }

        return data;
    }

    /**
     * <p>Get a list of results returned for a particular column.</p>
     *
     *
     * @param columnName The name of the data column to retrive. NOTE: This is
     * case-insensitive
     *
     * @return A List of objects reprenting the data for a given column
     */
    public List<?> getResults(String columnName) {
        Set<String> columnNames = resultsMap.keySet();
        List list = null;
        for (String column : columnNames) {
            
            /*
             * We need to make the search case-insensitive.
             */
            if (columnName.equalsIgnoreCase(column)) {
                list = resultsMap.get(column);
                break;
            }
        }
        
        if (list == null) {
            list = Collections.EMPTY_LIST;
        }
        return list;
    }

    /**
     * Retrieve the underlying storage
     * @return A Map where Key = Column name (String), Value = List of data for that column
     */
    public Map<String, List<Object>> getResultsMap() {
        return resultsMap;
    }

    //~--- methods ------------------------------------------------------------

    /**
     * <p><!-- Method description --></p>
     *
     */
    private void removeDuplicates() {
        Collection<List<Object>> dataLists = resultsMap.values();
        for (List<Object> data : dataLists) {
            data.removeAll(NO_VALUE_LIST);
        }
    }

    /**
     * <p><!-- Method description --></p>
     *
     *
     * @return
     */
    public int rowCount() {
        int rows = 0;
        Iterator<List<Object>> i = resultsMap.values().iterator();
        if (i.hasNext()) {
            rows = i.next().size();
        }

        return rows;
    }
    
    /*
     * This method is identical to rowCount but is used for web applications
     * that expect bean-style accessors.
     *
     * @return The number of rows returned by the query.
     */
    public int getLength() {
        return rowCount();
    }

    /**
     * When coalescing data on a key there may be multiple values that are the
     * same for example if a column has the following values: 500, 403, 500, 500
     * the result we want is '500, 403', not '500, 403, 500, 500'. So we store
     * values in a set to avoid duplication. These values need to be combined
     * after all rows have been coalesced.
     * @param row0
     * @param row1
     */
    private void storeCoalescedValues(int row0, int row1) {
        Collection<String> keys = resultsMap.keySet();
        for (String columnName : keys) {
            List data = resultsMap.get(columnName);
            Object obj0 = data.get(row0);
            Object obj1 = data.get(row1);

            /*
             * Only combine them if the values are actually different
             */
            if ((obj0 != null) && (obj1 != null) && !obj0.equals(obj1)) {

                /*
                 * Get the map of duplicate values for a particular column.
                 * The map returned has a key = row number and a value
                 * = set of the coalesced values
                 */
                Map<Integer, Set<?>> map = duplicateMap.get(columnName);
                if (map == null) {
                    /*
                     * map = new HashMap<Integer (row), Set (values)>
                     */
                    map = new HashMap<Integer, Set<?>>();
                    duplicateMap.put(columnName, map);
                }

                Set set = map.get(row0);
                if (set == null) {
                    set = new HashSet();
                    map.put(row0, set);
                }

                set.add(obj0);
                set.add(obj1);
            }

            data.set(row1, NO_VALUE);
        }
    }

    /**
     * <p><!-- Method description --></p>
     *
     *
     * @return
     */
    public String toString() {
        StringBuffer sb = new StringBuffer();

        /*
         * Write a header line (tab-separated)
         */
        Set<String> keySet = resultsMap.keySet();
        for (String key : keySet) {
            sb.append(key).append("\t");
        }

        sb.delete(sb.length() - 1, sb.length());    // Drop trailing tab
        sb.append("\n");

        /*
         * Write the data
         */
        Object[][] data = getDataArray();
        int rows = data.length;
        if (rows > 0) {
            int cols = data[0].length;
            for (int row = 0; row < rows; row++) {
                for (int col = 0; col < cols; col++) {
                    Object value = data[row][col];
                    if (value == null) {
                        value = " ";
                    }

                    sb.append(value);

                    if (col < cols - 1) {
                        sb.append("\t");
                    } else {
                        sb.append("\n");
                    }
                }
            }
        }

        return sb.toString();
    }
}
