package org.geotools.data.sql;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import org.geotools.data.jdbc.GeoAPISQLBuilder;
import org.geotools.data.jdbc.fidmapper.FIDMapper;
import org.geotools.feature.AttributeType;
import org.geotools.feature.FeatureType;
import org.geotools.feature.GeometryAttributeType;
import org.geotools.filter.SQLEncoderException;
import org.geotools.filter.UnaliasSQLEncoder;

public class BypassSqlSQLBuilder extends GeoAPISQLBuilder {

	private static final Logger LOGGER = org.geotools.util.logging.Logging.getLogger(BypassSqlSQLBuilder.class.getPackage().getName());
	
	private BypassSqlFeatureTypeHandler ftHandler;
	
	protected Map fieldAliases;

	public BypassSqlSQLBuilder(BypassSqlFeatureTypeHandler ftHandler) {
		this(new UnaliasSQLEncoder(), ftHandler);
	}

	public BypassSqlSQLBuilder(UnaliasSQLEncoder encoder,
			BypassSqlFeatureTypeHandler ftHandler) {
		super(encoder, null, null);
		this.ftHandler = ftHandler;
	}

	/**
	 * Constructs the full SQL SELECT statement for the supplied Filter.
	 * 
	 * <p>
	 * The statement is constructed by concatenating the SELECT column list,
	 * FROM table specification and WHERE clause appropriate to the supplied
	 * Filter.
	 * </p>
	 * 
	 * @param typeName
	 *            The name of the table (feature type) to be queried
	 * @param mapper
	 *            FIDMapper to identify the FID columns in the table
	 * @param attrTypes
	 *            The specific attribute columns to be selected
	 * @param filter
	 *            The Filter that will be used by the encoder to construct the
	 *            WHERE clause
	 * 
	 * @return The fully formed SQL SELECT statement
	 * 
	 * @throws SQLEncoderException
	 *             Not thrown by this method but may be thrown by the encoder
	 *             class
	 */
	public String buildSQLQuery(String typeName, FIDMapper mapper,
			AttributeType[] attrTypes, org.opengis.filter.Filter filter)
			throws SQLEncoderException {
		String sqlStmt;

		if (this.ftHandler != null && this.ftHandler.isView(typeName)) {
			final StringBuffer sqlBuffer = new StringBuffer();
			final String sqlQuery = ftHandler.getQuery(typeName);
			fieldAliases = parseAliases(sqlQuery);

			UnaliasSQLEncoder encoder = (UnaliasSQLEncoder) super.encoder;
			encoder.setAliases(fieldAliases);
			
			FeatureType fType;
			try {
				fType = ftHandler.getFeatureTypeInfo(typeName).getSchema();
			} catch (Exception e) {
				throw new RuntimeException("should not happen!: "
						+ e.getMessage());
			}

			//String select = getSelect(sqlQuery, fType);
			String select = "select " ; 
			sqlBuffer.append(select);

			sqlColumns(sqlBuffer, mapper, attrTypes, fieldAliases);

			String from = getFrom(sqlQuery);
			sqlBuffer.append(from);

			String where = getWhere(sqlQuery, mapper, filter);
			sqlBuffer.append(where);

			String groupBy = getGroupBy(sqlQuery);
			if(groupBy != null){
				sqlBuffer.append(groupBy);
			}

			String orderBy = getOrderBy(sqlQuery);
			if(orderBy != null){
				sqlBuffer.append(orderBy);
			}

			sqlStmt = sqlBuffer.toString();
		} else {
			sqlStmt = super.buildSQLQuery(typeName, mapper, attrTypes, filter);
		}
		LOGGER.finer(sqlStmt);
		return sqlStmt;
	}

	public void sqlColumns(final StringBuffer sql, final FIDMapper mapper,
			final AttributeType[] attributes, final Map aliases) {

		String sqlExpression;
		String alias;
		
		for (int i = 0; i < mapper.getColumnCount(); i++) {
			alias = mapper.getColumnName(i);
			sqlExpression = (String)aliases.get(alias);
			sql.append(encoder.escapeName(sqlExpression) + ", ");
		}

		for (int i = 0; i < attributes.length; i++) {
			alias = attributes[i].getName();
			sqlExpression = (String)aliases.get(alias);

			String fieldName = sqlExpression;
			if(!alias.equals(sqlExpression)){
				fieldName += " AS " + alias;
			}
			if (attributes[i] instanceof GeometryAttributeType) {
				sqlGeometryColumn(sql, attributes[i]);
			} else {
				sql.append(encoder.escapeName(fieldName));
			}

			if (i < (attributes.length - 1)) {
				sql.append(", ");
			}
		}
	}
	
	public static Map parseAliases(final String sqlQueryDefinition){
		Map aliases = new HashMap();
		String sqlQ = sqlQueryDefinition.toLowerCase();
		int idxFrom = sqlQ.indexOf("from ");
		int firstField = 7 + sqlQ.indexOf("select");
		
		String fields = sqlQueryDefinition.substring(firstField, idxFrom);
		
		LOGGER.fine("fields: " + fields);
		List fieldsList = Arrays.asList(fields.split(","));
		
		for(Iterator it = fieldsList.iterator(); it.hasNext();){
			String aliasDef = (String)it.next();
			aliasDef = aliasDef.trim().toLowerCase();
			LOGGER.fine("parsing alias from '" + aliasDef + "'");
			int idx = aliasDef.indexOf(" as ");
			if(idx > 0){
				String sqlExpr, alias;
				sqlExpr = aliasDef.substring(0, idx);
				alias = aliasDef.substring(4 + idx);
				LOGGER.fine("sqlExpr: " + sqlExpr + ", alias: " + alias);
				aliases.put(alias.trim(), sqlExpr.trim());
				aliases.put(alias.trim().toUpperCase(), sqlExpr.trim().toUpperCase()); 
			}else{
				LOGGER.fine(aliasDef + " is not aliased");
				aliases.put(aliasDef, aliasDef);
				aliases.put(aliasDef.toUpperCase(), aliasDef.toUpperCase());
			}
		}
		
		return aliases;
	}

	/**
	 * Returns the "SELECT" part of the SQL query definition for FeatureType
	 * <code>fType</code>, without the column names. This allows to maintain
	 * DB specific keywords, for example, <code>SELECT TOP 100 ...</code> in
	 * SQLServer.
	 * 
	 * @param sqlQueryDefinition
	 * @param fType
	 * @return
	 * @throws SQLEncoderException
	 */
	private String getSelect(String sqlQueryDefinition, FeatureType fType)
			throws SQLEncoderException {
		AttributeType firstAtt = fType.getAttributeTypes()[0];
		String firstAttName = firstAtt.getName().toLowerCase();

		int index = sqlQueryDefinition.indexOf(firstAttName);
		if (index == -1) {
			throw new SQLEncoderException(
					"attribute "
							+ firstAttName
							+ " not found in sql query definition. It should be the first one!: "
							+ sqlQueryDefinition);
		}

		String select = sqlQueryDefinition.substring(0, index);
		return select;
	}

	public String getFrom(String sqlQueryDefinition) {
		String search = " from ";
		String searchIn = sqlQueryDefinition.toLowerCase();
		int index = searchIn.indexOf(search);
		int lastIndex = searchIn.lastIndexOf(" where ");
		if (lastIndex == -1) {
			lastIndex = searchIn.lastIndexOf("group by");
		}
		if (lastIndex == -1) {
			lastIndex = searchIn.lastIndexOf("order by");
		}
		String from;
		if (lastIndex == -1) {
			from = sqlQueryDefinition.substring(index);
		} else {
			from = sqlQueryDefinition.substring(index, lastIndex);
		}
		return from;
	}

	public String getWhere(String sqlQueryDefinition, FIDMapper mapper,
			org.opengis.filter.Filter filter) throws SQLEncoderException {
		String search = " where ";
		String searchIn = sqlQueryDefinition.toLowerCase();
		int index = searchIn.lastIndexOf(search);

		StringBuffer where = new StringBuffer();
		encoder.setFIDMapper(mapper);
		super.sqlWhere(where, filter);

		if (index > 0) {
			int lastIndex = searchIn.lastIndexOf("group by");
			if (lastIndex == -1) {
				lastIndex = searchIn.lastIndexOf("order by");
			}
			String queryWhere;
			if (lastIndex == -1) {
				queryWhere = sqlQueryDefinition.substring(index
						+ search.length());
			} else {
				queryWhere = sqlQueryDefinition.substring(index
						+ search.length(), lastIndex);
			}

			String filterWhere = where.toString();
			
			if(where.length() > 0){
				where.insert(7, queryWhere + " AND (");
				where.append(")");
			}else{
				where.append(" WHERE "  + queryWhere);
			}
		}
		return where.toString();
	}

	private String getGroupBy(String sqlQueryDefinition) {
		return null;
	}

	private String getOrderBy(String sqlQueryDefinition) {
		String sql = sqlQueryDefinition.toLowerCase();
		int idx = sql.lastIndexOf("order by");
		String orderBy = null;
		if(idx > 0){
			orderBy = " " + sqlQueryDefinition.substring(idx);
		}
		return orderBy;
	}

}
