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.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;
import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
import org.apache.torque.Torque;
import org.apache.torque.TorqueException;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.om.mapper.ObjectListMapper;
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, &amp; MIN.  However, some
 * non-standard functions (like MySQL's older LEAST instead of MIN) can be
 * handled programmatically if needed {@link org.apache.torque.util.functions.AggregateFunction}
 * </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 1925092 2025-04-15 09:14:33Z mwriedt $
 */
public class SummaryHelper
{
    /** The class log. */
    private static final Logger log = LogManager.getLogger(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<SQLFunction> 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&lt;String, List&lt;Object&gt;&gt; object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI<Object>> summarize(
            final 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 {@link org.apache.torque.om.mapper.ObjectListMapper} for the supported
     *        classes.
     *
     * @return Results as a ListOrderMapCI&lt;String, List&lt;Object&gt;&gt; object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI<Object>> summarize(
            final Criteria crit,
            final List<Class<?>> resultTypes)
                    throws TorqueException
    {
        try (TorqueConnection connection = Transaction.begin(crit.getDbName()))
        {
            List<ListOrderedMapCI<Object>> result = summarize(crit, resultTypes, connection);
            Transaction.commit(connection);
            return result;
        }
    }

    /**
     * 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&lt;String, List&lt;Object&gt;&gt; object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI<Object>> summarize(
            final Criteria crit,
            final 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 {@link org.apache.torque.om.mapper.ObjectListMapper} for the supported
     *        classes.
     * @param conn The DB Connection to use.
     *
     * @return Results as a ListOrderedMapCI&lt;String,Values&gt; object.
     *
     * @throws TorqueException if a database error occurs.
     */
    public List<ListOrderedMapCI<Object>> summarize(
            final Criteria crit,
            final List<Class<?>> resultTypes,
            final Connection conn)
                    throws TorqueException
    {
        Criteria c = buildCriteria(crit);

        String databaseName = (c.getDbName() == null)
                ? Torque.getDefaultDB()
                        : c.getDbName();
                c.setDbName(databaseName);

                BasePeerImpl<List<Object>> peer = new BasePeerImpl<>(
                        new ObjectListMapper(resultTypes),
                        null, databaseName);

                // use extra ObjectListMapper in doSelect to avoid addSelectcolumns()
                // call in peer.doSelect(c, conn)
                List<List<Object>> rows
                = peer.doSelect(c, new ObjectListMapper(resultTypes), conn);

                List<ListOrderedMapCI<Object>> resultsList
                    = new ArrayList<>(rows.size());
                List<String> columnNames = new ArrayList<>();
                for (Column column : c.getSelectColumns())
                {
                    columnNames.add(column.getColumnName());
                }
                columnNames.addAll(c.getAsColumns().keySet());
                for (List<Object> row : rows)
                {
                    ListOrderedMapCI<Object> 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 if a database error occurs.
     */
    public Criteria buildCriteria(final Criteria c) throws TorqueException
    {
        c.getSelectColumns().clear();
        c.getGroupByColumns().clear();

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

        setDistinct(criteriaSelectModifiers);
        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)
        {
            log.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;
            log.debug("From table defined by a where clause");
        }

        for (Map.Entry<String, SQLFunction> entry : getAggregates().entrySet())
        {
            SQLFunction f = entry.getValue();
            Column col =  f.getColumn();
            c.addAsColumn(entry.getKey(), 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.andVerbatimSql(
                            col.getColumnName() + "=" + col.getColumnName(),
                            new Object[] {});
                    haveFromTable = true;

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

    private void setDistinct(UniqueList<String> criteriaSelectModifiers) {
        if (criteriaSelectModifiers != null
                && criteriaSelectModifiers.size() > 0
                && criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
        {
            criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
        }
    }

    /**
     * <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 to group by
     */
    public void addGroupBy(final 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 An aggregate SQL function to evaluate.
     */
    public void addAggregate(final String alias, final 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<>();
        }
        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<SQLFunction> 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 the output writer
     * @param results results to store
     * @param includeHeader boolean flag to include header
     * @throws IOException if file not found
     */
    public void dumpResults(final Writer out, final List<?> results, final boolean includeHeader)
            throws IOException
    {
        Iterator<?> i = results.iterator();
        boolean first = includeHeader;

        while (i.hasNext())
        {
            @SuppressWarnings("unchecked")
            ListOrderedMapCI<Object> rec = (ListOrderedMapCI<Object>) i.next();
            Iterator<Map.Entry<String, Object>> rI = rec.entrySet().iterator();
            StringBuilder heading = new StringBuilder();
            StringBuilder recString = new StringBuilder();
            while (rI.hasNext())
            {
                Map.Entry<String, Object> entry = rI.next();
                String colId = entry.getKey();
                if (first)
                {
                    heading.append("\"").append(colId).append("\"");
                    if (rI.hasNext())
                    {
                        heading.append(", ");
                    }
                }
                Object v = entry.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(final boolean excludeExprColumns)
    {
        this.excludeExprColumns = excludeExprColumns;
    }
}
