/***************************************************************************
    Copyright          : (C) 2002 by Neoworks Limited. All rights reserved
    URL                : http://www.neoworks.com
 ***************************************************************************/
/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/

/*
 * Query.java
 *
 * Created on 18 April 2002, 18:23
 */

package com.neoworks.jukex.query;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import java.io.Serializable;

import com.neoworks.connectionpool.PoolManager;
import com.neoworks.util.Pair;
import com.neoworks.util.MultiMap;
import com.neoworks.util.HashMapMultiMap;

import com.neoworks.jukex.Attribute;
import com.neoworks.jukex.AttributeValue;
import com.neoworks.jukex.DatabaseObject;
import com.neoworks.jukex.TrackStoreFactory;
import com.neoworks.jukex.TrackStore;
import com.neoworks.jukex.sqlimpl.JukeXTrackStore;
import com.neoworks.jukex.sqlimpl.JukeXAttributeValue;

import sun.jdbc.rowset.CachedRowSet;

import org.apache.log4j.Category;

/**
 * A Query object represents a parsed JukeXQL query, ready to execute.
 *
 * @author Nick Vincent (<a href="mailto:nick@neoworks.com">nick@neoworks.com</a>)
 */
public class Query implements Serializable
{
	private static final Category log = Category.getInstance(Query.class.getName());
	private static final boolean logDebugEnabled = log.isDebugEnabled();
	private static final boolean logInfoEnabled = log.isInfoEnabled();

	private Expression expression = null;
	private QueryType queryType = null;
	private Set attributes = null;
	private List selectAttributes = null;
	private List orderAttributes = null;
	private String originalQuery = null;
	private int sortOrder;

	private static PoolManager _poolmanager = null;

	/**
	 * Package private constructor
	 *
	 * @param queryType
	 * @param expression
	 * @param attributes
	 * @param selectAttributes
	 * @param orderAttributes
	 * @param originalquery
	 * @param sortOrder
	 */
	Query( QueryType queryType , Expression expression , Set attributes , List selectAttributes , List orderAttributes , String originalQuery , int sortOrder )
	{
		if ( _poolmanager == null ) _poolmanager = PoolManager.getInstance();
		this.expression = expression;
		this.queryType = queryType;
		this.attributes = attributes;
		this.selectAttributes = selectAttributes;
		this.orderAttributes = orderAttributes;
		this.originalQuery = originalQuery;
		this.sortOrder = sortOrder;
		if (logDebugEnabled) log.debug("Constructing Query with String " + this.originalQuery);
	}

	/**
	 * Execute the created query and retrieve the Track objects of the results
	 *
	 * @return A <code>List</code> of <code>Track</code> objects which match
	 *         the specified query.
	 */
	public List getTracks()
	{
		if ( ! ( this.queryType instanceof QueryType.Track ) )
		{
			throw new ClassCastException( "Cannot retrieve getTracks() from a Query of type " + this.queryType.getClass().getName() );
		}

		TrackStore trackstore = TrackStoreFactory.getTrackStore();

		Connection conn = null;
		List trackids = new LinkedList();

		try
		{
			conn = _poolmanager.getConnection( JukeXTrackStore.DB_NAME );
			Statement state = conn.createStatement();

			System.out.println( this.getSQL() );

			ResultSet rs = state.executeQuery( this.getSQL() );

			while ( rs.next() )
			{
				trackids.add( new Long( rs.getLong( 1 ) ) );
			}

			return trackstore.getTracks( trackids );
		}
		catch ( Exception e )
		{
			log.error( "An error occurred whilst running a Track query" , e );
		}
		finally
		{
			try { conn.close(); } catch ( SQLException ignore ) { }
		}

		return null;
	}

	/**
	 * Get the original query String that was parsed to create this object.
	 *
	 * @return The original query String
	 */
	public String getOriginalQuery()
	{
		return this.originalQuery;
	}

	/**
	 * Get a ResultSet containing the AttributeValues resulting from execution of this Query
	 *
	 * @return An AttributeValueResultSet containing the results of executing this Query
	 */
	public AttributeValueResultSet getAttributeValues()
	{
		if ( ! ( this.queryType instanceof QueryType.Attribute ) )
		{
			throw new ClassCastException( "Cannot retrieve AttributeValues() from a Query of type " + this.queryType.getClass().getName() );
		}

		AttributeValueResultSet retval = null;

		Connection conn = null;
		try
		{
			conn = PoolManager.getInstance().getConnection( JukeXTrackStore.DB_NAME );
			Statement state = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY );
			//ResultSet rs = state.executeQuery( this.getSQL() );

			CachedRowSet cs = new CachedRowSet();
			cs.populate( state.executeQuery( this.getSQL() ) );

			retval = new AttributeValueResultSet( cs , this.selectAttributes );

			state.close();
		}
		catch ( SQLException se )
		{
			log.error( "Encountered an exception whilst trying to retrieve Attribute Values" , se );
		}
		finally
		{
			try { conn.close(); } catch ( SQLException ignore ) {}
		}

		return retval;
	}

	/**
	 * Get the SQL that will be executed on execution of this Query.
	 *
	 * @return The SQL generated for this query
	 */
	public String getSQL()
	{
		if (logDebugEnabled)
		{
			log.debug("Trace In: getSQL()");
			log.debug("Attributes "+this.attributes);
			log.debug("Select Attributes "+this.selectAttributes);
		}

		// Build the lists to select from and the required joins
		List selectClause = new ArrayList( 2 );
		List fromClause = new ArrayList( 10 );
		List whereClause = new ArrayList( 10 );
		List orderByClause = new ArrayList( 2 );

		// Work out which tables we need to be joining by going through all the
		// attributes in the table and joining them all in
		Iterator i = this.attributes.iterator();
		String lastAttr = null;
		String currAttr = null;
		String firstTable = null;		// The first table in the query, used later to make link tables
		while ( i.hasNext() )
		{
			currAttr = (String) i.next();
			if ( lastAttr != null ) whereClause.add( "bind_"+lastAttr+".trackid = bind_"+currAttr+".trackid" );
			if ( firstTable == null ) firstTable = ( "bind_"+currAttr );
			fromClause.add( "AttributeValue AS bind_"+currAttr );
			lastAttr = currAttr;
		}

		// Now get the rest of the SQL from the expression structure
		StringBuffer expressionWhereClauseBuilder = new StringBuffer();
		this.expression.getSQL( expressionWhereClauseBuilder );
		String expressionWhereClause = expressionWhereClauseBuilder.toString();

		if ( this.queryType instanceof QueryType.Track )
		{
			selectClause.add( "AttributeValue.trackid" );
			fromClause.add( "AttributeValue" );
			if (firstTable!=null) whereClause.add( "AttributeValue.trackid="+firstTable+".trackid" );
		}
		else
		{
			// Do SELECT clause
			selectClause.add( firstTable+".trackid AS trackid" );

			TrackStore trackstore = TrackStoreFactory.getTrackStore();
			i = this.selectAttributes.iterator();						//TODO: Make it handle duplicate, eg.g SELECT Artist,Artist,Title
			while( i.hasNext() )
			{
				Attribute currSelectAttr = trackstore.getAttribute( (String)i.next() );
				if ( currSelectAttr.getType() == Attribute.TYPE_STRING )
				{
					String currSelectAttrName = currSelectAttr.getName();
					String tmpTableName = "valueof_"+currSelectAttrName;

					selectClause.add( ""+Attribute.TYPE_STRING+" AS "+currSelectAttrName+"Type" );
					selectClause.add( "bind_"+currSelectAttrName+".attributeenumid AS "+currSelectAttrName+"ID" );
					selectClause.add( tmpTableName+".value AS "+currSelectAttrName );

					fromClause.add( "AttributeEnum AS "+tmpTableName );
					whereClause.add( tmpTableName+".id=bind_"+currSelectAttrName+".attributeenumid" );
					whereClause.add( tmpTableName+".attributeid = " + ((DatabaseObject)currSelectAttr).getId() );
				}
				else // it's an Integer here...
				{
					String currSelectAttrName = currSelectAttr.getName();
					selectClause.add( ""+Attribute.TYPE_INT+" AS "+currSelectAttrName+"Type" );
					selectClause.add( "null AS "+currSelectAttrName+"ID" );
					selectClause.add( "bind_"+currSelectAttrName+".numericvalue AS "+currSelectAttrName );
					whereClause.add( "bind_"+currSelectAttrName+".attributeid = " + ((DatabaseObject)currSelectAttr).getId() );
				}
			}
		}

		// Now build the full expression up from the lists.
		StringBuffer completeQuery = new StringBuffer();

		completeQuery.append( "SELECT DISTINCT " );
		appendListWithSeparators( completeQuery , selectClause , ", " );
		completeQuery.append( " FROM " );
		appendListWithSeparators( completeQuery , fromClause , ", " );
		if ( whereClause.size() > 0 || expressionWhereClause.length() > 0 )
		{
			completeQuery.append( " WHERE " );
			appendListWithSeparators( completeQuery , whereClause , " AND " );
			if ( whereClause.size() > 0 && expressionWhereClause.length() > 0 ) completeQuery.append( " AND " );
			completeQuery.append( expressionWhereClause );
		}

		// Now deal with the ORDER BY clause
		if ( !this.orderAttributes.isEmpty() )
		{
			completeQuery.append( " ORDER BY " );
			Iterator orderByIter = this.orderAttributes.iterator();
			if ( orderByIter.hasNext() )
			{
				completeQuery.append( (String) orderByIter.next() );
				while ( orderByIter.hasNext() )
				{
					completeQuery.append( ',' ).append( (String) orderByIter.next() );
				}
			}
			// add sort order modifier if necessary
			if (this.sortOrder == JukeXParser.SORT_ASC)
			{
				completeQuery.append(" ASC");
			} else if (this.sortOrder == JukeXParser.SORT_DESC) {
				completeQuery.append(" DESC");
			}
		}

				
		if (logDebugEnabled) log.debug( "Made SQL: "+completeQuery.toString() );

		return completeQuery.toString();
	}

	/**
	 * Fold a List into a StringBuffer, appending each entry with a separator
	 *
	 * @param sb The StringBuffer to populate
	 * @param list The List to fold
	 * @param separator The separator String to use
	 */
	private void appendListWithSeparators( StringBuffer sb , List list , String separator)
	{
		Iterator i = list.iterator();
		if ( i.hasNext() )
		{
			sb.append( (String) i.next() );
			while ( i.hasNext() )
			{
				sb.append( separator ).append( (String) i.next() );
			}
		}
	}
}
