import java.util.*;
import toolkit.db.api.*;

public class SQLStmtCreatorImpl implements SQLStmtCreator
{	
	private final static String QUOTE_MARK = "\"";//"";//"`"; 
	private final static int QBE_TOTAL_ROWS = 16;
	private String tableIndex = "";
	
	/**
	 *	Create an SQL statement string.
	 *	@param	psqlQueryInfo	The psql query information used to create the SQL string.
	 *	@return	Valid SQL query string if user wants to create it in this method, else null.
	 *			If it returns a null, JReport query engine will create an SQL query string.
	 *	@see	toolkit.db.api.SQLStmtCreator.getQueryStmt(QueryInfo queryInfo)
	 */
	public String getSQLStmt(QueryInfo queryInfo)
	{
		// query name
		String name = queryInfo.getQueryName();
	
		// parameters
		ParameterInfo[] parameters = queryInfo.getParameters();
		for (int i = 0; i < parameters.length; i ++)
		{
			if (parameters[i].getName().equalsIgnoreCase("tableIndex"))
			{
				tableIndex = parameters[i].getValue();
				break;
			}
		}
			
		// select clause
		String SelectCause = getSelectColumns(queryInfo).toString();
		
		// from clause
		TableInfo[] tables = queryInfo.getTables();
		JoinInfo[] joins = queryInfo.getJoins();
		String FromCause;
		if (joins.length>0)
		{
			//----- get Outer Joins with SQL92 criterion
			JoinInfo[] sql92OuterJoins = getSQL92OuterJoins(joins);
			if ((sql92OuterJoins != null) && (sql92OuterJoins.length > 0))
			{
				FromCause = getSQL92OuterJoinsClause(tables, sql92OuterJoins).toString();
			}
			else 
			{
				FromCause = getFromClause(tables).toString();
			}	
		}	
		else
		{
			FromCause = getFromClause(tables).toString();
		}
				
		// where clause
		String wherePortion = queryInfo.getWherePortionString();
		String whereClause;
		if (wherePortion != null)
		{
			whereClause = wherePortion;
		}
		else
		{
			whereClause = getWhereClause(queryInfo).toString();
		}
		
		// order by
		OrderByInfo[] orderBys = queryInfo.getOrderBys();
		String orderByClause = getOrderByClause(orderBys).toString();
	
		//  ********* Begin to Spell a SQL statement string************
		StringBuffer ret=new StringBuffer();
		
		ret.append("SELECT ")
			.append(SelectCause)
			.append("FROM ")
			.append(FromCause);
		
		if(	whereClause.length()>0 )
		{
			ret.append(" WHERE ")
				.append(whereClause);
		}
		
		if( orderByClause.length()>0 ) 
		{
			ret.append(" ORDER BY ")
				.append(orderByClause); 
		}
		
		String SQL = replaceMappingNames(ret.toString(), queryInfo);
		System.out.println("**************************************************");
		System.out.println(name + " = "+ SQL);
		return SQL;
	}
		
		/***********************************************************
		 ***********************************************************
		 ***********************************************************/
		 
	/**
	 *	Helper method to get "SELECT" Clause to return. 
	 *	
	 */
	private StringBuffer getSelectColumns(QueryInfo queryInfo)
	{
		
		StringBuffer selectBuf=new StringBuffer();	
			
		// distinct
		boolean bDistinct = queryInfo.isDistinct();
		if (bDistinct)
		{
			selectBuf.append("DISTINCT ");
		}
		
		// select columns
		ColumnInfo[] selectColumns = queryInfo.getSelectColumns();
		for (int i = 0; i < selectColumns.length; i++)
		{
			if (selectColumns[i].isCompCol())	// computed column
			{
				selectBuf.append(selectColumns[i].getExpression())
					.append(" AS ")
					.append(QUOTE_MARK + selectColumns[i].getColumnName() + QUOTE_MARK);
			}
			else
			{
				selectBuf.append(makeColumn(selectColumns[i]));
			}
		
			if (i < selectColumns.length - 1)
			{
				selectBuf.append(", ");
			}
			else
			{
				selectBuf.append(" ");
			}
		}
		return selectBuf;
	}
		
	/**
	 *	Helper method to get "FROM" Clause to return. 
	 *	
	 */
	private StringBuffer getFromClause(TableInfo[] tables)
	{
		StringBuffer fromBuf = new StringBuffer();
		for (int i = 0; i < tables.length; i++)
		{
			fromBuf.append(makeTable(tables[i]));
			if (i < tables.length - 1 )
			{
				fromBuf.append(", ");
			}
			else
			{
				fromBuf.append(" ");
			}
		}
		return fromBuf;
	}
		
	/**
	 *	Helper method to get "INNER JOINs" information to return.  
	 *	In order to construct one of the parts of "WHERE" Clause
	 * 	(including InnerJoin and "(+)" OuterJoin)
	 */	
	private  StringBuffer getWhereClause_Joins(JoinInfo[] joins)
	{
		StringBuffer JoinsBuf = new StringBuffer();
		if (joins != null )
		{
			for (int i = 0; i < joins.length; i++)
			{
				ColumnInfo columnFrom = joins[i].getColumnFrom();
				String operator = joins[i].getOperator();
				ColumnInfo columnTo = joins[i].getColumnTo();
				String sFrom = makeColumn(columnFrom);
				String sTo = makeColumn(columnTo);
				
				// if is "(+)" OuterJoin
				if (joins[i].isOuterJoin())
				{
					if (!joins[i].isSQL92()) 
					{
						switch (joins[i].getJoinType())
						{
							case (JoinInfo.RIGHT_OUTER_JOIN):
								sFrom = sFrom + "(+)";
								break;
							case (JoinInfo.LEFT_OUTER_JOIN):
								sTo = sTo + "(+)";
								break;
							case (JoinInfo.INNER_JOIN):
							case (JoinInfo.UNION_JOIN):
							case (JoinInfo.FULL_OUTER_JOIN):
							default:
								break;
						}
					}
				}
				
				// spell the Joins Clause into string buffer
				JoinsBuf.append("(" + sFrom)
					.append(" ")
					.append(operator)
					.append(" ")
					.append(sTo + ")");
				if (i < joins.length - 1)
				{
					JoinsBuf.append(" AND ");
				}
				else
				{
					JoinsBuf.append("");
				}
			}
		}
		return JoinsBuf;
	}
	
	/**
	 *	Helper method to get the information about "OUTER JOINs" with SQL92 criterion to return.  
	 *	In order to construct one of the parts of "FROM" Clause
	 * 	For Example 
	 *	the return OuterJoin String like : tableA OUTER JOIN tableB on tableA.col1 = tableB.col1
	 */		
	private  StringBuffer getSQL92OuterJoinsClause(TableInfo[] tables, JoinInfo[] joins)
	{
		StringBuffer joinBuf = new StringBuffer();
		
		// remove the tables with relate to join info
		TableInfo[] nonJoinTables = removeJoinedTables(tables, joins);
		
		// add non joined tables first
		if(nonJoinTables.length > 0)
		{
			for (int i = 0; i < nonJoinTables.length; i++)
			{
				joinBuf.append(makeTable(nonJoinTables[i]));
				joinBuf.append(", ");
			}
		}
				
		// add joined tables info	
		int[][] info = new int[tables.length][2];
		JoinFormat[] node = new JoinFormat[tables.length];
		
		// map mapping table name to tableIndex
		for (int i=0;i<tables.length;i++)
		{
			info[i][0]=-1;
			info[i][1]=-1;
			node[i]=new JoinFormat();
		}
		
		// first, search join_info and first top position on each group.
		for(int i=0;i<joins.length;i++)
		{
			int l_Index =tableIndexName(tables,joins[i].getColumnFrom().getTable().getTableName().toString());
			int r_Index =tableIndexName(tables,joins[i].getColumnTo().getTable().getTableName().toString());
			node[l_Index].count++;
			info[l_Index][0]=0;
			info[r_Index][1]=0;
		}
		
		// find the top of table index position.
		Vector top = new Vector();
		for(int i=0;i<tables.length;i++)
		{
			if ((info[i][0]==0)&&(info[i][1]==-1))
			{
				top.addElement(tables[i]);
			}
		}
					
		// second, fill the join_info into JoinFormat information	
		for(int i=0;i<joins.length;i++)
		{
			int l_Index = tableIndexName(tables,joins[i].getColumnFrom().getTable().getTableName().toString());
			int r_Index = tableIndexName(tables,joins[i].getColumnTo().getTable().getTableName().toString());
			if(node[l_Index].current==0)
			{
				node[l_Index].addJoin(node[l_Index].count);
			}
			int i_relate=node[l_Index].current++;
			node[l_Index].relate[i_relate]=joins[i];
			node[l_Index].next[i_relate]=r_Index;
		}
			
		// append join info with "Table1 Join Table2 on Table1.ColumnA = Table2.ColumnB"			
		for(int i=0;i<top.size();i++)
		{
			String topName = ((TableInfo)top.get(i)).getTableName().toString();
			int pos = tableIndexName(tables,topName);
			StringBuffer ret1 = new StringBuffer() ;
			ret1.append(makeTable((TableInfo)top.get(i)));
			
			// recursion to construct multi outer join (with SQL92 standard) information
			joinBuf.append(depose(pos,ret1,node).toString().toString());
			if (i < top.size() - 1)
			{
				joinBuf.append(", ");
			}
			else
			{
				joinBuf.append(" ");
			}
		}
		return joinBuf;
	}
	
	/**
	 *	Helper method to get the "WHERE" Clause to return
	 *	the return string include : 
	 *					joins condition information ( exclude Outer Joins with SQL92 criterion)
	 *					and condition information
	 *					QBE condition information
	 *					sub link information
	 */		
	private StringBuffer getWhereClause(QueryInfo queryInfo)
	{
		StringBuffer whereBuf=new StringBuffer();
		
		// joins condition information ( exclude Outer Joins with SQL92 criterion )
		StringBuffer joinsBuf =new StringBuffer();
		JoinInfo[] joins = queryInfo.getJoins();
		JoinInfo[] newjoins = removeSQL92OuterJoins(joins);
		joinsBuf = getWhereClause_Joins(newjoins);
	 	
		// and condition information
		StringBuffer andsBuf =new StringBuffer();
		AndInfo[] ands = queryInfo.getAnds();
		andsBuf = getWhereClause_And(ands);
		
		// QBE condition information
		StringBuffer qbesBuf = new StringBuffer();
		QBEInfo[] qbes = queryInfo.getQbes();
		qbesBuf = getWhereClause_QBE(qbes);
	
		// sub link information
		StringBuffer linksBuf = new StringBuffer();
		SubLinkInfo[] subLinks = queryInfo.getSubLinks();
		linksBuf = getWhereClause_SubLink(subLinks);
		
		//  ********* Begin to Spell an Where Clause************
		// assemble four parts to SQL string
		boolean bAdded = false;
		//joins condition information
		if (joinsBuf.length() > 0)
		{
			whereBuf.append("(")
				.append(joinsBuf.toString())
				.append(")");
			bAdded = true;
		}
		// and condition information
		if (andsBuf.length() > 0)
		{
			whereBuf.append(bAdded ? " AND " : "")
				.append("(")
				.append(andsBuf.toString())
				.append(")");
			bAdded = true;
		}
		// QBE condition information
		if (qbesBuf.length() > 0)
		{
			whereBuf.append(bAdded ? " AND " : "")
				.append("(")
				.append(qbesBuf.toString())
				.append(")");
			bAdded = true;
		}
		// sub link information
		if (linksBuf.length() > 0)
		{
			whereBuf.append(bAdded ? " AND " : "")
				.append("(")
				.append(linksBuf.toString())
				.append(")");
		}
		return whereBuf;
	}
	
	/**
	 *	Helper method to get the "And" information in Where Clause to return
	 *	In order to construct the one part of Where Clause  
	 */		
	private  StringBuffer getWhereClause_And(AndInfo[] ands)
	{
		StringBuffer andsBuf = new StringBuffer();
		if (ands.length > 0)
		{
			String logic = null;
			boolean bNewGrp = true;
			for (int i = 0; i < ands.length; i++)
			{
				andsBuf.append(logic == null ? "" : " " + logic + " ")
					.append(bNewGrp ? "(" : "")
					.append(ands[i].getLeftExpr())
					.append(" ")
					.append(ands[i].getOperator())
					.append(" ")
					.append(ands[i].getRightExpr());
				if (ands[i].isEndOfGroup())
				{
					andsBuf.append(")");
					bNewGrp = true;
				}
				else
				{
					bNewGrp = false;
				}
				logic = ands[i].getLogic();
			}
		}
		return andsBuf;
	}
	
	/**
	 *	Helper method to get the "QBE" information in Where Clause to return
	 *	In order to construct the one part of Where Clause  
	 */	
	private  StringBuffer getWhereClause_QBE(QBEInfo[] qbes)
	{
		StringBuffer qbesBuf = new StringBuffer();
		boolean isAND = false;
		if (qbes.length > 0)
		{
			// concatenate rows with " OR "
			for (int r = 0; r < QBE_TOTAL_ROWS; r++)
			{
				String cond = null;
				StringBuffer ret = new StringBuffer();
				// concatenate column of a row with " AND "
				for (int i = 0; i < qbes.length; i++)
				{
					cond = (String)qbes[i].getCriteria().get(new Integer(r));
					if (cond != null && cond.trim().length() > 0)
					{
						if (isAND)
						{
							ret.append(" AND ");
						}
						ret.append("(")
							.append(makeColumn(qbes[i].getColumnInfo()))
							.append(" ")
							.append(cond)
							.append(")");
						isAND = true;
					}
				}
				if (ret.length()>0)
				{
					qbesBuf.append(" OR ")
						.append(ret.toString());
						
				}
				isAND = false;
			}
			qbesBuf.delete(0, 4);	// delete the first " OR " which is unwanted
		}
		return qbesBuf;
	}
	
	/**
	 *	Helper method to get the "sub link" information in Where Clause to return
	 *	In order to construct the one part of Where Clause  
	 */	
	private  StringBuffer getWhereClause_SubLink(SubLinkInfo[] subLinks)
	{	
		StringBuffer linksBuf = new StringBuffer();
		if (subLinks.length > 0)
		{
			for (int i = 0; i < subLinks.length; i++)
			{
				linksBuf.append(makeColumn(subLinks[i].getSubColumn()))
					.append(" ")
					.append(subLinks[i].getOperator())
					.append(" ")
					.append(subLinks[i].getValue());
				if (i < subLinks.length - 1)
				{
					linksBuf.append(" AND ");
				}
			}
		}
		return linksBuf;
	}
	
	/**
	 *	Helper method to get the "Order By" information to return
	 *	 
	 */
	private  StringBuffer getOrderByClause(OrderByInfo[] orderBys)
	{
		StringBuffer orderBuf = new StringBuffer();
		if (orderBys.length > 0)
		{
			for (int i = 0; i < orderBys.length; i++)
			{
				orderBuf.append(makeColumn(orderBys[i].getColumn()))
					.append(" ")
					.append(orderBys[i].getOrder());
				if (i < orderBys.length - 1)
				{
					orderBuf.append(", ");
				}
				else
				{
					orderBuf.append(" ");
				}
			}
		}
		return orderBuf;
	}
				
				
/********************************************************************
 **********************************************************************/	

 	/**
	 *	Helper method to construct Table expression for SQL
	 */	private String makeTable(TableInfo ti)
    {
		return QUOTE_MARK + ti.getTableName() + tableIndex +  QUOTE_MARK;
	}

    /**
	 *	Helper method to construct Column expression for SQL
	 */
	private String makeColumn(ColumnInfo ci)
    {
		return QUOTE_MARK + ci.getTable().getTableName() + tableIndex + QUOTE_MARK + "." 
			+ QUOTE_MARK + ci.getColumnName() + QUOTE_MARK;
	}
	
	/**
	 *	Remove joined tables from TableInfo array, otherwise they will
	 *	be added in FROM clause twice.  Joined tables are appended when
	 *	handling joins.
	 */
	private static TableInfo[] removeJoinedTables(TableInfo[] tables, JoinInfo[] joins)
	{
		boolean[] removed = new boolean[tables.length];
		int cnt = tables.length;
		for (int i = 0; i < tables.length; i++)
		{
			removed[i] = false;
		}

		for (int i = 0; i < tables.length; i++)
		{
			for (int j = 0; j < joins.length; j++)
			{
				TableInfo tableFrom = joins[j].getColumnFrom().getTable();
				TableInfo tableTo = joins[j].getColumnTo().getTable();
				if (tables[i].equals(tableFrom) || tables[i].equals(tableTo))
				{
					removed[i] = true;
					cnt--;
					break;
				}
			}
		}

		TableInfo[] ret = new TableInfo[cnt];
		cnt = 0;
		for (int i = 0; i < tables.length; i++)
		{
			if (!removed[i])
			{
				ret[cnt] = tables[i];
				cnt++;
			}
		}
		return ret;
	}
	
	/**
	 *	construct Join info format:
	 *	just like : "Table1 Join Table2 on Table1.ColumnA = Table2.ColumnB"
	 *	
	 */	
	private StringBuffer depose(int pos,StringBuffer ret,JoinFormat[] node)
	{
		if(node[pos].count==0) 
		{
			return ret ;
		}
	
		for(int i=0;i<node[pos].count;i++)
		{
			StringBuffer sb=new StringBuffer();
			ColumnInfo columnFrom = node[pos].relate[i].getColumnFrom();
			String operator = node[pos].relate[i].getOperator();
			ColumnInfo columnTo = node[pos].relate[i].getColumnTo();
	
			String joinSpec = null;
			switch (node[pos].relate[i].getJoinType())
			{
			case (JoinInfo.INNER_JOIN):
				joinSpec = "INNER JOIN";
				break;
			case (JoinInfo.RIGHT_OUTER_JOIN):
				joinSpec = "RIGHT JOIN";
				break;
			case (JoinInfo.LEFT_OUTER_JOIN):
				joinSpec = "LEFT JOIN";
				break;
			case (JoinInfo.OUTER_JOIN):
			case (JoinInfo.FULL_OUTER_JOIN):
			default:
				break;
			}
			int next=node[pos].next[i];
			
			// recursion to the next node
			sb=depose(next,sb.append(makeTable(columnTo.getTable())),node);
			ret.append(" ")
				.append(joinSpec)
				.append(" ");
			ret.append(sb.toString());
			ret.append(" ON (")
				.append(makeColumn(columnFrom))
				.append(" ")
				.append(operator)
				.append(" ")
				.append(makeColumn(columnTo))
				.append(")");
			ret.insert(0,'(');
			ret.insert(ret.length(),')');	
		}
		return ret;
	}
	
	/**
	 *	Help Method :get tableIndex from its mapping table name 
	 *	
	 */	
	private int tableIndexName(TableInfo[] tables,String tableName)
		{
			int index = -1;
			for(int i=0;i<tables.length;i++)
			{
				if(tables[i].getTableName().equalsIgnoreCase(tableName))
				{
					index=i;
					break;
				}
			}
			return index;
		}
		
	/**
	 *	Replace mapping names (parameters) in raw SQL statement 
	 *	with values .
	 *	@param	stmt	raw SQL statement string to be replaced.
	 *	@param	queryInfo	The psql query information used to create the SQL string.					
	 *	@return	final SQL string to be sent to DBMS
	 */
	private String replaceMappingNames(String stmt, QueryInfo queryInfo)
	{
		StringBuffer ret = new StringBuffer(stmt.length());

		Vector ps = MappingNameFinder.find(stmt);
		MappingNameFinder.Position p = null;

		ParameterInfo[] params = queryInfo.getParameters();
		ParameterInfo param = null;
		String paramType = null, paramValue = null;

		int lastEnd = 0;
		for (int i = 0; i < ps.size(); i++)
		{
			p = (MappingNameFinder.Position)ps.elementAt(i);
			param = null;

			// append normal string
			ret.append(stmt.substring(lastEnd, p.begin));

			// append value of parameter
			for (int j = 0; j < params.length; j++)	// search ParameterInfo first
			{
				if (params[j].getName().equalsIgnoreCase(p.name))
				{
					param = params[j];
					break;
				}
			}

			if (param != null)	// mapping name stands for a parameter
			{
				paramType = param.getType();
				paramValue = param.getValue();
				switch (p.leader)
				{
					case '@' :
					{
						if (paramType.equals(ParameterInfo.TYPE_INTEGER) 
							|| paramType.equals(ParameterInfo.TYPE_NUMBER)
							|| paramType.equals(ParameterInfo.TYPE_CURRENCY))
						{
							ret.append(paramValue);
						}
						else if (paramType.equals(ParameterInfo.TYPE_STRING))
						{
							ret.append("\'" + paramValue + "\'");
						}
						else if (paramType.equals(ParameterInfo.TYPE_BOOLEAN))
						{
							ret.append(paramValue);
						}
						else if (paramType.equals(ParameterInfo.TYPE_DATE) 
							|| paramType.equals(ParameterInfo.TYPE_TIME)
							|| paramType.equals(ParameterInfo.TYPE_DATETIME))
						{
							ret.append("cdate(\'" + paramValue + "\')");
						}
						break;
					}
					case ':' :
					{
						ret.append(paramValue);
						break;
					}
				}
			}
			lastEnd = p.end;
		}
		ret.append(stmt.substring(lastEnd));
		return (ret.toString());
	}
	
	/**
	 *	Help Method : to get Outer Joins with SQL92 criterion
	 *	return : JoinInfo[] is Outer Joins with SQL92 criterion
	 */
	private JoinInfo[] getSQL92OuterJoins(JoinInfo[] joins)
	{
		JoinInfo[] sql92OuterJoins;
		Vector v = new Vector();
		for (int i = 0; i < joins.length; i++)
		{
			if ((joins[i].getJoinType() != JoinInfo.INNER_JOIN) && joins[i].isSQL92())
			{
				v.addElement(joins[i]);
			}
		}
		if (v.size()>0)
		{
			sql92OuterJoins = new JoinInfo[v.size()];
			for (int i = 0; i < v.size(); i++)
			{
				sql92OuterJoins[i] = (JoinInfo)v.elementAt(i);
			}
		}
		else
		{
			sql92OuterJoins = null;
		}
		return sql92OuterJoins;
	}
	
	/**
	 *	Help Method : to remove the Outer Joins with SQL92 criterion from JoinInfo[]
	 *	return : JoinInfo[] is join info array which is exclude Outer Joins with SQL92 criterion
	 */
	private  JoinInfo[] removeSQL92OuterJoins(JoinInfo[] joins)
	{
		boolean[] removed = new boolean[joins.length];
		int cnt = joins.length;
		JoinInfo[] newJoins;
		for (int i = 0; i < joins.length; i++)
		{
			removed[i] = false;
		}
		for (int i = 0; i < joins.length; i++)
		{
			if ((joins[i].getJoinType() != JoinInfo.INNER_JOIN) && joins[i].isSQL92())
			{
				removed[i] = true;
				cnt--;
			}
		}
		if (cnt>0)
		{
			newJoins = new JoinInfo[cnt];
			cnt = 0;
			for (int i = 0; i < joins.length; i++)
			{
				if (!removed[i])
				{
					newJoins[cnt] = joins[i];
					cnt++;
				}
			}
		}
		else 
		{
			newJoins = null;
		}
		return newJoins;
	}
	
} // class SQLStmtCreatorImpl end 


class JoinFormat
{
	JoinInfo[] relate ;
	int[] next;
	int count;
	int current;
		
	JoinFormat()
	{
		this.count=0;
		this.current=0;
	}
	void addJoin(int size)
	{
		this.relate = new JoinInfo[size];
		this.next = new int[size];
	}
     
}
	