package org.apache.torque.util;

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (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.apache.org/licenses/LICENSE-2.0
 *
 * 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.
 */

import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.apache.commons.collections.OrderedMapIterator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
import org.apache.torque.TorqueException;
import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.om.mapper.ObjectListMapper;
import org.apache.torque.om.mapper.RecordMapper;
import org.apache.torque.sql.SqlBuilder;
import org.apache.torque.util.functions.SQLFunction;

/**
 * <p>A utility to help produce aggregate summary information about a table.
 * The default assumes that the underlying DB supports the SQL 99 Standard
 * Aggregate functions, e.g. COUNT, SUM, AVG, MAX, & MIN.  However, some
 * non-standard functions (like MySQL's older LEAST instead of MIN can be
 * handled programatically if needed (@see Aggregate class)</p>
 *
 * <P>Here is a simple example to generate the results of a query like:</P>
 *
 * <pre>
 * SELECT EMPLOYEE, SUM(HOURS), MIN(HOURS), MAX(HOURS)
 *     FROM TIMESHEET WHERE TYPE = 1 GROUP BY EMPLOYEE ORDER BY EMPLOYEE ASC
 * </pre>
 * <p>Use the following code</p>
 * <pre>
 *    SummaryHelper sHelp = new SummaryHelper();
 *    Criteria c = new Criteria();
 *    c.add(TimeSheetPeer.TYPE, 1);
 *    c.addAscendingOrderBy(TimeSheetPeer.EMPLOYEE);
 *    sHelper.addGroupBy(TimeSheetPeer.EMPLOYEE);
 *    sHelper.addAggregate(FunctionFactory.Sum(TimeSheetPeer.HOURS),"Hours");
 *    sHelper.addAggregate(FunctionFactory.Min(TimeSheetPeer.HOURS),"Min_Hrs");
 *    sHelper.addAggregate(FunctionFactory.Max(TimeSheetPeer.HOURS),"Max_Hrs");
 *    List results = sHelper.summarize( c );
 * </pre>
 * <p>The results list will be an OrderedMap with a key of either the group by
 * column name or the name specified for the aggregate function (e.g. EMPLOYEE
 * or Hours).  The value will be a Village Value Class.  Below is a simple
 * way to do this.  See the dumpResults* method code for a more complex example.
 * </p>
 * <pre>
 *    String emp = results.get("EMPLOYEE").asString();
 *    int hours = results.get("Hours").asInt();
 * </pre>
 * <p>
 * Notes:</p>
 * <p>
 * If there are no group by columns specified, the aggregate is over the
 * whole table.  The from table is defined either via the Criteria.addAlias(...)
 * method or by the first table prefix in an aggregate function.</p>
 * <p>
 * This will also work with joined tables if the criteria is creates as
 * to create valid SQL.</p>
 *
 * @author <a href="mailto:greg.monroe@dukece.com">Greg Monroe</a>
 * @version $Id: SummaryHelper.java 1448414 2013-02-20 21:06:35Z tfischer $
 */
public class SummaryHelper
{
    /** The class log. */
    private static Log logger = LogFactory.getLog(SummaryHelper.class);

    /** A list of the group by columns. */
    private List<Column> groupByColumns;
    /** A ListOrderMapCI<String, Aggregate.Function> with the aggregate functions
     * to use in generating results. */
    private ListOrderedMapCI aggregates;
    /** Flag for excluding unnamed columns. */
    private boolean excludeExprColumns = false;

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     * The class of the return values are decided by the database driver,
     * which makes this method not database independent.
     *
     * @param crit The base criteria to build on.
     *
     * @return Results as a OrderMap<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     *
     * @deprecated please use
     *             summarize(org.apache.torque.criteria.Criteria)
     *             instead.
     *             This method will be removed in a future version of Torque.
     */
    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit)
            throws TorqueException
    {
        return summarize(crit, (List<Class<?>>) null);
    }

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     * The class of the return values are decided by the database driver,
     * which makes this method not database independent.
     *
     * @param crit The base criteria to build on.
     *
     * @return Results as a OrderMap<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI> summarize(
                org.apache.torque.criteria.Criteria crit)
            throws TorqueException
    {
        return summarize(crit, (List<Class<?>>) null);
    }

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     *
     * @param crit The base criteria to build on.
     * @param resultTypes the classes to which the return values of the query
     *        should be cast, or null to let the database driver decide.
     *        See org.apache.torque.om.mapper.ObjectListMapper�for the supported
     *        classes.
     *
     * @return Results as a ListOrderMapCI<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     *
     * @deprecated Please use
     *             summarize(org.apache.torque.criteria.Criteria, List<Class<?>>)
     *             instead.
     *             This method will be removed in a future version of Torque.
     */
    @Deprecated
    public List<ListOrderedMapCI> summarize(
                Criteria crit,
                List<Class<?>> resultTypes)
            throws TorqueException
    {
        Connection connection = null;
        try
        {
            connection = Transaction.begin(crit.getDbName());
            List<ListOrderedMapCI> result = summarize(crit, resultTypes, connection);
            Transaction.commit(connection);
            connection = null;
            return result;
        }
        finally
        {
            if (connection != null)
            {
                Transaction.safeRollback(connection);
            }
        }
    }

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     *
     * @param crit The base criteria to build on.
     * @param resultTypes the classes to which the return values of the query
     *        should be cast, or null to let the database driver decide.
     *        See org.apache.torque.om.mapper.ObjectListMapper�for the supported
     *        classes.
     *
     * @return Results as a ListOrderMapCI<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI> summarize(
                org.apache.torque.criteria.Criteria crit,
                List<Class<?>> resultTypes)
            throws TorqueException
    {
        Connection connection = null;
        try
        {
            connection = Transaction.begin(crit.getDbName());
            List<ListOrderedMapCI> result = summarize(crit, resultTypes, connection);
            Transaction.commit(connection);
            connection = null;
            return result;
        }
        finally
        {
            if (connection != null)
            {
                Transaction.safeRollback(connection);
            }
        }
    }

    /**
     * Return a list of OrderedMap objects with the results of the summary
     * query.  The OrderedMap objects have a key of the column name or
     * function alias and are in the order generated by the query.
     * The class of the return values are decided by the database driver,
     * which makes this method not database independent.
     *
     * @param crit The base criteria to build on.
     * @param conn The DB Connection to use.
     *
     * @return Results as a OrderMap<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     *
     * @deprecated please use
     *             summarize(org.apache.torque.criteria.Criteria, Connection)
     *             instead.
     *             This method will be removed in a future version of Torque.
     */
    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit, Connection conn)
            throws TorqueException
    {
        return summarize(crit, null, conn);
    }

    /**
     * Return a list of OrderedMap objects with the results of the summary
     * query.  The OrderedMap objects have a key of the column name or
     * function alias and are in the order generated by the query.
     * The class of the return values are decided by the database driver,
     * which makes this method not database independent.
     *
     * @param crit The base criteria to build on.
     * @param conn The DB Connection to use.
     *
     * @return Results as a OrderMap<String, List<Object>> object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI> summarize(
                org.apache.torque.criteria.Criteria crit,
                Connection conn)
            throws TorqueException
    {
        return summarize(crit, null, conn);
    }

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     *
     * @param crit The base criteria to build on.
     * @param resultTypes the classes to which the return values of the query
     *        should be cast, or null to let the database driver decide.
     *        See org.apache.torque.om.mapper.ObjectListMapper�for the supported
     *        classes.
     * @param conn The DB Connection to use.
     *
     * @return Results as a ListOrderedMapCI<String,Values> object.
     *
     * @throws TorqueException if a database error occurs.
     *
     * @deprecated please use
     *             summarize(org.apache.torque.criteria.Criteria, List<Class<?>>, Connection)
     *             instead.
     *             This method will be removed in a future version of Torque.
     */
    @Deprecated
    public List<ListOrderedMapCI> summarize(
                Criteria crit,
                List<Class<?>> resultTypes,
                Connection conn)
            throws TorqueException
    {
        Criteria c = buildCriteria(crit);
        // TODO use BasePeerImpl.doSelect instead of parsing the result manually
        String query = SqlBuilder.buildQuery(c).toString();
        RecordMapper<List<Object>> mapper = new ObjectListMapper(resultTypes);

        Statement statement = null;
        ResultSet resultSet = null;
        List<List<Object>> rows = new ArrayList<List<Object>>();
        try
        {
            statement = conn.createStatement();
            long startTime = System.currentTimeMillis();
            logger.debug("Executing query " + query);

            resultSet = statement.executeQuery(query.toString());
            long queryEndTime = System.currentTimeMillis();
            logger.trace("query took " + (queryEndTime - startTime)
                    + " milliseconds");

            while (resultSet.next())
            {
                List<Object> rowResult = mapper.processRow(resultSet, 0, null);
                rows.add(rowResult);
            }
            long mappingEndTime = System.currentTimeMillis();
            logger.trace("mapping took " + (mappingEndTime - queryEndTime)
                    + " milliseconds");
        }
        catch (SQLException e)
        {
            throw new TorqueException(e);
        }
        finally
        {
            if (resultSet != null)
            {
                try
                {
                    resultSet.close();
                }
                catch (SQLException e)
                {
                    logger.warn("error closing resultSet", e);
                }
            }
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException e)
                {
                    logger.warn("error closing statement", e);
                }
            }
        }

        List<ListOrderedMapCI> resultsList = new Vector<ListOrderedMapCI>(rows.size());
        List<String> columnNames = new ArrayList<String>();
        for (Column column : c.getSelectColumns())
        {
            columnNames.add(column.getColumnName());
        }
        columnNames.addAll(c.getAsColumns().keySet());
        for (List<Object> row : rows)
        {
            ListOrderedMapCI recordMap = new ListOrderedMapCI();
            for (int i = 0; i < row.size(); i++)
            {
                Object value = row.get(i);
                String cName = columnNames.get(i);
                if (cName == null || cName.equals(""))
                 {
                    if (excludeExprColumns())
                    {
                        continue;
                    }
                    cName = "Expr" + i;
                }
                recordMap.put(cName, value);
            }
            resultsList.add(recordMap);
        }
        return resultsList;
    }

    /**
     * Return a list of ListOrderedMapCI objects with the results of the summary
     * query.  The ListOrderedMapCI objects have a key of the column name or
     * function alias and are in the order generated by the query.
     *
     * @param crit The base criteria to build on.
     * @param resultTypes the classes to which the return values of the query
     *        should be cast, or null to let the database driver decide.
     *        See org.apache.torque.om.mapper.ObjectListMapper�for the supported
     *        classes.
     * @param conn The DB Connection to use.
     *
     * @return Results as a ListOrderedMapCI<String,Values> object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI> summarize(
                org.apache.torque.criteria.Criteria crit,
                List<Class<?>> resultTypes,
                Connection conn)
            throws TorqueException
    {
        org.apache.torque.criteria.Criteria c = buildCriteria(crit);
        // TODO use BasePeerImpl.doSelect instead of parsing the result manually
        String query = SqlBuilder.buildQuery(c).toString();
        RecordMapper<List<Object>> mapper = new ObjectListMapper(resultTypes);

        Statement statement = null;
        ResultSet resultSet = null;
        List<List<Object>> rows = new ArrayList<List<Object>>();
        try
        {
            statement = conn.createStatement();
            long startTime = System.currentTimeMillis();
            logger.debug("Executing query " + query);

            resultSet = statement.executeQuery(query.toString());
            long queryEndTime = System.currentTimeMillis();
            logger.trace("query took " + (queryEndTime - startTime)
                    + " milliseconds");

            while (resultSet.next())
            {
                List<Object> rowResult = mapper.processRow(resultSet, 0, null);
                rows.add(rowResult);
            }
            long mappingEndTime = System.currentTimeMillis();
            logger.trace("mapping took " + (mappingEndTime - queryEndTime)
                    + " milliseconds");
        }
        catch (SQLException e)
        {
            throw new TorqueException(e);
        }
        finally
        {
            if (resultSet != null)
            {
                try
                {
                    resultSet.close();
                }
                catch (SQLException e)
                {
                    logger.warn("error closing resultSet", e);
                }
            }
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException e)
                {
                    logger.warn("error closing statement", e);
                }
            }
        }

        List<ListOrderedMapCI> resultsList = new Vector<ListOrderedMapCI>(rows.size());
        List<String> columnNames = new ArrayList<String>();
        for (Column column : c.getSelectColumns())
        {
            columnNames.add(column.getColumnName());
        }
        columnNames.addAll(c.getAsColumns().keySet());
        for (List<Object> row : rows)
        {
            ListOrderedMapCI recordMap = new ListOrderedMapCI();
            for (int i = 0; i < row.size(); i++)
            {
                Object value = row.get(i);
                String cName = columnNames.get(i);
                if (cName == null || cName.equals(""))
                 {
                    if (excludeExprColumns())
                    {
                        continue;
                    }
                    cName = "Expr" + i;
                }
                recordMap.put(cName, value);
            }
            resultsList.add(recordMap);
        }
        return resultsList;
    }

    /**
     * Builds the criteria to use in summarizing the information.  Note that
     * the criteria passed in will be modified.
     *
     * @param c The base criteria to build the summary criteria from.
     * @return A criteria to use in summarizing the information.
     * @throws TorqueException
     *
     * @deprecated please use
     *             buildCriteria(org.apache.torque.criteria.Criteria)
     *             instead.
     *             This method will be removed in a future version of Torque.
     */
    @Deprecated
    public Criteria buildCriteria(Criteria c) throws TorqueException
    {
        c.getSelectColumns().clear();
        c.getGroupByColumns().clear();

        UniqueList<String> criteriaSelectModifiers;
        criteriaSelectModifiers = c.getSelectModifiers();

        if (criteriaSelectModifiers != null
            && criteriaSelectModifiers.size() > 0
            && criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
        {
            criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
        }
        c.setIgnoreCase(false);

        List<Column> cols = getGroupByColumns();
        boolean haveFromTable = !cols.isEmpty(); // Group By cols define src table.
        for (Column col : cols)
        {
            c.addGroupByColumn(col);
            c.addSelectColumn(col);
        }
        if (haveFromTable)
        {
            logger.debug("From table defined by Group By Cols");
        }

        // Check if the from table is set via a where clause.
        if (!haveFromTable && !c.isEmpty())
        {
            haveFromTable = true;
            logger.debug("From table defined by a where clause");
        }

        ListOrderedMapCI cMap = getAggregates();
        OrderedMapIterator iMap = cMap.orderedMapIterator();
        while (iMap.hasNext())
        {
            String key = (String) iMap.next();
            SQLFunction f = (SQLFunction) iMap.getValue();
            Column col =  f.getColumn();
            c.addAsColumn(key, new ColumnImpl(
                    null,
                    col.getTableName(),
                    col.getColumnName(),
                    f.getSqlExpression()));
            if (!haveFromTable)    // Last chance. Get it from the func.
            {
                {
                    // Kludgy Where table.col = table.col clause to force
                    // from table identification.
                    c.add(col,
                            (col.getColumnName()
                                    + "=" + col.getColumnName()),
                            SqlEnum.CUSTOM);
                    haveFromTable = true;

                    String table = col.getTableName();
                    logger.debug("From table, '" + table
                            + "', defined from aggregate column");
                }
            }
        }
        if (!haveFromTable)
        {
            throw new TorqueException(
                    "No FROM table defined by the GroupBy set, "
                    + "criteria.setAlias, or specified function column!");
        }
        return c;
    }

    /**
     * Builds the criteria to use in summarizing the information.  Note that
     * the criteria passed in will be modified.
     *
     * @param c The base criteria to build the summary criteria from.
     * @return A criteria to use in summarizing the information.
     * @throws TorqueException
     */
    public org.apache.torque.criteria.Criteria buildCriteria(
            org.apache.torque.criteria.Criteria c) throws TorqueException
    {
        c.getSelectColumns().clear();
        c.getGroupByColumns().clear();

        UniqueList<String> criteriaSelectModifiers;
        criteriaSelectModifiers = c.getSelectModifiers();

        if (criteriaSelectModifiers != null
            && criteriaSelectModifiers.size() > 0
            && criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
        {
            criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
        }
        c.setIgnoreCase(false);

        List<Column> cols = getGroupByColumns();
        boolean haveFromTable = !cols.isEmpty(); // Group By cols define src table.
        for (Column col : cols)
        {
            c.addGroupByColumn(col);
            c.addSelectColumn(col);
        }
        if (haveFromTable)
        {
            logger.debug("From table defined by Group By Cols");
        }

        // Check if the from table is set via a where clause.
        if (!haveFromTable && c.getTopLevelCriterion() != null)
        {
            haveFromTable = true;
            logger.debug("From table defined by a where clause");
        }

        ListOrderedMapCI cMap = getAggregates();
        OrderedMapIterator iMap = cMap.orderedMapIterator();
        while (iMap.hasNext())
        {
            String key = (String) iMap.next();
            SQLFunction f = (SQLFunction) iMap.getValue();
            Column col =  f.getColumn();
            c.addAsColumn(key, new ColumnImpl(
                    null,
                    col.getTableName(),
                    col.getColumnName(),
                    f.getSqlExpression()));
            if (!haveFromTable)    // Last chance. Get it from the func.
            {
                {
                    // Kludgy Where table.col = table.col clause to force
                    // from table identification.
                    c.and(col,
                            (col.getColumnName()
                                    + "=" + col.getColumnName()),
                            SqlEnum.CUSTOM);
                    haveFromTable = true;

                    String table = col.getTableName();
                    logger.debug("From table, '" + table
                            + "', defined from aggregate column");
                }
            }
        }
        if (!haveFromTable)
        {
            throw new TorqueException(
                    "No FROM table defined by the GroupBy set, "
                    + "criteria.setAlias, or specified function column!");
        }
        return c;
    }

    /**
     * <p>
     * Add a column that will be used to group the aggregate results by.
     * This is a first added / first listed on SQL method.  E.g.,
     * </p>
     * <pre>
     *    add(TablePeer.COL1);
     *    add(TablePeer.COL2);
     * </pre>
     *
     * <p>Generates SQL like:  SELECT .... GROUP BY Table.COL1, TABLE.COL2</p>
     *
     * @param column
     */
    public void addGroupBy(Column column)
    {
        getGroupByColumns().add(column);
    }

    /**
     * Add in an Aggregate function to the summary information.
     *
     * @param alias  A valid SQL99 column identifier ([_A-Z0-9] no spaces and
     *               no key words, e.g. function names.
     * @param function One of the inner classes from the Aggregate class.
     */
    public void addAggregate(String alias, SQLFunction function)
    {
        getAggregates().put(alias, function);
    }

    /**
     *  Resets the class internal variables to their initial states so
     *  the class can be re-used like a new class.
     */
    public void clear()
    {
        getGroupByColumns().clear();
        getAggregates().clear();
        setExcludeExprColumns(false);
    }

    public List<Column> getGroupByColumns()
    {
        if (groupByColumns == null)
        {
            groupByColumns = new Vector<Column>();
        }
        return groupByColumns;
    }

    /**
     * Get the order map list of aggregate functions to use in
     * summarizing this table's informations.  The key is used
     * as the result column alias.
     *
     * @return the avgColumns.  Will always return a ListOrderedMap object.
     */
    public ListOrderedMapCI getAggregates()
    {
        if (aggregates == null)
        {
            aggregates = new ListOrderedMapCI();
        }
        return aggregates;
    }

    /**
     * Convenience method to dump a summary results list to an output writer
     * in a semi-CSV format. E.g., there is no handling of embedded
     * quotes/special characters.
     *
     * @param out
     * @param results
     * @param includeHeader
     * @throws IOException
     */
    public void dumpResults(Writer out, List<?> results, boolean includeHeader)
                                                            throws IOException
    {
        Iterator<?> i = results.iterator();
        boolean first = includeHeader;

        while (i.hasNext())
        {
            ListOrderedMapCI rec = (ListOrderedMapCI) i.next();
            OrderedMapIterator rI = rec.orderedMapIterator();
            StringBuilder heading = new StringBuilder();
            StringBuilder recString = new StringBuilder();
            while (rI.hasNext())
            {
                String colId = (String) rI.next();
                if (first)
                {
                    heading.append("\"").append(colId).append("\"");
                    if (rI.hasNext())
                    {
                        heading.append(", ");
                    }
                }
                Object v = rI.getValue();
                recString.append(v.toString());
                if (rI.hasNext())
                {
                    recString.append(", ");
                }
            }
            if (first)
            {
                first = false;
                out.write(heading.toString());
                out.write("\n");
            }
            out.write(recString.toString());
            out.write("\n");
        }
    }

    /**
     * Should the results include unnamed columns, e.g. EXPR{index#}.
     *
     * @return the excludeExprColumns
     */
    public boolean excludeExprColumns()
    {
        return excludeExprColumns;
    }

    /**
     * <p>Define if unnamed output columns which get labeled as EXPR{index#})
     * should be included in the the output set.</p>
     * <p>
     * Note these are generally added by the criteria
     * processing to handle special cases such as case insensitive ordering.
     * </p>
     *
     * @param excludeExprColumns if True, these columns won't be included.
     */
    public void setExcludeExprColumns(boolean excludeExprColumns)
    {
        this.excludeExprColumns = excludeExprColumns;
    }
}
