/**
 * Copyright (c) 2012, The National Archives <pronom@nationalarchives.gsi.gov.uk>
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following
 * conditions are met:
 *
 *  * Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 *  * Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *
 *  * Neither the name of the The National Archives nor the
 *    names of its contributors may be used to endorse or promote products
 *    derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR
 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package uk.gov.nationalarchives.droid.report.dao;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import uk.gov.nationalarchives.droid.core.interfaces.filter.expressions.Criterion;
import uk.gov.nationalarchives.droid.core.interfaces.filter.expressions.QueryBuilder;
import uk.gov.nationalarchives.droid.profile.SqlUtils;

/**
 * JPA implementation of JpaPlanetsXMLDaoImpl.
 * 
 * @author Alok Kumar Dash.
 */
public class SqlReportDaoImpl implements ReportDao {

    private static String formatfilter = "formatfilter";
    
    private final Log log = LogFactory.getLog(getClass());

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * Flushes the DROID entity manager.
     */
    void flush() {
        entityManager.flush();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public List<ReportLineItem> getReportData(Criterion filter, ReportFieldEnum reportField) {
        return getReportData(filter, reportField, new ArrayList<GroupByField>());
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public List<ReportLineItem> getReportData(Criterion filter, ReportFieldEnum reportField, 
            List<GroupByField> groupByFields) {
        final Query query = getQuery(reportField, groupByFields, filter);
        final List<?> results = query.getResultList();
        List<ReportLineItem> reportData = new ArrayList<ReportLineItem>();
        if (results != null && !results.isEmpty()) {
            reportData = reportField.getType().populateReportedData(results);
        }
        return reportData;        
    }
    
    
    private Query getQuery(ReportFieldEnum reportField, List<GroupByField> groupByFields, Criterion filter) {
        final String selectStatement = getSelectStatement(reportField, groupByFields);
        final FilterInfo filterInfo = getFilterInfo(filter);
        final String groupingStatement = getGroupingStatement(groupByFields);
        final String queryString = selectStatement + filterInfo.getFilterSubQuery() + groupingStatement;
        final Query query = entityManager.createNativeQuery(queryString);
        setFilterParameters(query, filterInfo.getFilterValues());
        return query;
    }
    
    
    // Get select statement for aggregate queries on the report field, 
    // including any grouping field in the results.
    private String getSelectStatement(ReportFieldEnum reportField, List<GroupByField> groupByFields) {
        final ReportFieldType selectType = reportField.getType();
        return "SELECT "
            + selectType.getSelectFieldString(groupByFields)
            + " FROM profile_resource_node AS profile ";
    }

    
    private FilterInfo getFilterInfo(Criterion filter) {
        final FilterInfo filterInfo = new FilterInfo();
        final QueryBuilder queryBuilder = QueryBuilder.forAlias("profileResourceNode").createAlias("format");
        queryBuilder.add(filter);
        final String ejbQl = queryBuilder.toEjbQl();
        
        // If we have a filter, get its SQL query string and parameter values:
        if (ejbQl.length() > 0) {
            filterInfo.setFilterSubQuery(buildFilterSubQuery(ejbQl));
            filterInfo.setFilterValues(queryBuilder.getValues());
        }
        return filterInfo;
    }
    
    
    private String getGroupingStatement(List<GroupByField> groupByFields) {
        StringBuilder groupByFieldQuery = new StringBuilder();
        if (groupByFields != null && groupByFields.size() > 0) {
            
            // Determine if any fields are grouping on puid or format metadata
            boolean puidGroup = false;
            boolean formatMetadataGroup = false;
            for (GroupByField group : groupByFields) {
                final ReportFieldEnum groupField = group.getGroupByField();
                puidGroup = puidGroup | groupOnPUID(groupField);
                formatMetadataGroup = formatMetadataGroup | groupOnFormatMetadata(groupField);
            }
                
            // If any grouping fields are on the format metadata group, we have to join to all tables:
            if (formatMetadataGroup) {
                groupByFieldQuery.append(" INNER JOIN identification as ident on ident.node_id = profile.node_id " 
                    + " INNER JOIN format as format on format.puid = ident.puid ");
            } else if (puidGroup) { // if grouping on puids, join only to identification table:
                groupByFieldQuery.append(" INNER JOIN identification as format on format.node_id = profile.node_id "); 
            }
            
            // add group by clause:
            groupByFieldQuery.append(getGroupByClause(groupByFields));
        }
        return groupByFieldQuery.toString();
    }
    
    private String getGroupByClause(List<GroupByField> groupByFields) {
        StringBuilder groupByFieldQuery = new StringBuilder();
        groupByFieldQuery.append("GROUP BY ");
        String separator = "";
        for (GroupByField group : groupByFields) {
            final String groupFieldName = group.getGroupByField().getType().getField();
            final String groupFunction = group.getFunction();
            groupByFieldQuery.append(separator);
            if (groupFunction != null && !groupFunction.isEmpty()) {
                groupByFieldQuery.append(groupFunction + "(");
            }
            groupByFieldQuery.append(groupFieldName);
            if (groupFunction != null && !groupFunction.isEmpty()) {
                groupByFieldQuery.append(") ");
            }
            separator = ", ";
        }
        return groupByFieldQuery.toString();
    }
    
    private String buildFilterSubQuery(final String ejbQl) {
        final String filterSQL = SqlUtils.transformEJBtoSQLFields(ejbQl, "filter", formatfilter);
        String subQuery = " INNER JOIN (SELECT DISTINCT filter.node_id "
            + " FROM profile_resource_node AS filter ";

        // Only add joins to referenced tables, to increase performance:
        if (filterOnFormats(filterSQL)) {
            if (filterOnFormatMetadata(filterSQL)) { // filtering on format metadata, so have to join to format table:
                subQuery += " INNER JOIN identification as formatident ON formatident.node_id = filter.node_id "
                    + " INNER JOIN format as formatfilter ON formatfilter.puid = formatident.puid ";
            } else { // only filtering on the PUID, so only join to identification table.
                subQuery += " INNER JOIN identification as formatfilter ON formatfilter.node_id = filter.node_id ";
            }
        }
        subQuery += " WHERE " + filterSQL + ") AS filtered "
            + " ON filtered.node_id = profile.node_id ";
        return subQuery;
    }
    
    
    private boolean filterOnFormats(final String queryString) {
        return queryString.contains(formatfilter);
    }
    
    
    private boolean filterOnFormatMetadata(final String queryString) {
        return queryString.contains("formatfilter.name") || queryString.contains("formatfilter.mime_type");
    }
    
    
    private boolean groupOnPUID(ReportFieldEnum groupByField) {
        return groupByField.equals(ReportFieldEnum.PUID);
    }
    
    
    private boolean groupOnFormatMetadata(ReportFieldEnum groupByField) {
        return groupByField.equals(ReportFieldEnum.FILE_FORMAT)
                || groupByField.equals(ReportFieldEnum.MIME_TYPE);
    }
    
    private class FilterInfo {
        private String filterSubQuery = "";
        private Object[] filterValues = new Object[0];
        
        public String getFilterSubQuery() {
            return filterSubQuery;
        }
        
        public Object[] getFilterValues() {
            return filterValues;
        }
        
        public void setFilterSubQuery(final String subQuery) {
            filterSubQuery = subQuery;
        }
        
        public void setFilterValues(final Object[] values) {
            filterValues = values;
        }
    }
    
    
    private void setFilterParameters(Query q, Object[] filterParams) {
        int pos = 1;
        for (Object param : filterParams) {
            Object transformedValue = SqlUtils.transformParameterToSQLValue(param);
            q.setParameter(pos++, transformedValue);
        }
    }       
    
}
