package railo.runtime.type.util;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.sql.Time;
import java.util.Date;

import railo.commons.lang.FormatUtil;
import railo.commons.lang.SizeOf;
import railo.commons.lang.StringUtil;
import railo.commons.sql.SQLUtil;
import railo.runtime.PageContext;
import railo.runtime.config.NullSupportHelper;
import railo.runtime.db.DataSource;
import railo.runtime.db.DatasourceConnection;
import railo.runtime.db.SQL;
import railo.runtime.db.driver.PreparedStatementPro;
import railo.runtime.db.driver.StatementPro;
import railo.runtime.dump.DumpData;
import railo.runtime.dump.DumpProperties;
import railo.runtime.dump.DumpRow;
import railo.runtime.dump.DumpTable;
import railo.runtime.dump.DumpUtil;
import railo.runtime.dump.SimpleDumpData;
import railo.runtime.exp.DatabaseException;
import railo.runtime.exp.PageException;
import railo.runtime.exp.PageRuntimeException;
import railo.runtime.functions.arrays.ArrayFind;
import railo.runtime.op.Caster;
import railo.runtime.type.Array;
import railo.runtime.type.ArrayImpl;
import railo.runtime.type.Collection;
import railo.runtime.type.Collection.Key;
import railo.runtime.type.KeyImpl;
import railo.runtime.type.Query;
import railo.runtime.type.QueryColumn;
import railo.runtime.type.QueryColumnImpl;
import railo.runtime.type.QueryImpl;
import railo.runtime.type.query.SimpleQuery;

public class QueryUtil {

	public static long sizeOf(QueryColumn column) {
		if(column instanceof QueryColumnImpl){
			return ((QueryColumnImpl)column).sizeOf();
		}
		int len = column.size();
		long size=0;
		for(int i=1;i<=len;i++){
			size+=SizeOf.size(column.get(i,null));
		}
		return size;
	}

	/**
	 * return column names as Key from a query
	 * 
	 * @param qry
	 * @return
	 */
	public static Key[] getColumnNames(Query qry) {
		Query qp = Caster.toQuery(qry,null);
		
		if(qp!=null) return qp.getColumnNames();
		String[] strNames = qry.getColumns();
		Key[] names=new Key[strNames.length];
		for(int i=0;i<names.length;i++){
			names[i]=KeyImpl.getInstance(strNames[i]);
		}
		return names;
	}

	public static String[] toStringArray(Collection.Key[] keys) {
		if(keys==null) return new String[0];
		String[] strKeys=new String[keys.length];
		for(int i=0	;i<keys.length;i++) {
			strKeys[i]=keys[i].getString();
		}
		return strKeys;
	}

	/**
     * check if there is a sql restriction
	 * @param dc
	 * @param sql
	 * @throws PageException 
	 */
	public static void checkSQLRestriction(DatasourceConnection dc, SQL sql) throws PageException {
        Array sqlparts = ListUtil.listToArrayRemoveEmpty(
        		SQLUtil.removeLiterals(sql.getSQLString())
        		," \t"+System.getProperty("line.separator"));

        //print.ln(List.toStringArray(sqlparts));
        DataSource ds = dc.getDatasource();
        if(!ds.hasAllow(DataSource.ALLOW_ALTER))    checkSQLRestriction(dc,"alter",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_CREATE))   checkSQLRestriction(dc,"create",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_DELETE))   checkSQLRestriction(dc,"delete",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_DROP))     checkSQLRestriction(dc,"drop",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_GRANT))    checkSQLRestriction(dc,"grant",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_INSERT))   checkSQLRestriction(dc,"insert",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_REVOKE))   checkSQLRestriction(dc,"revoke",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_SELECT))   checkSQLRestriction(dc,"select",sqlparts,sql);
        if(!ds.hasAllow(DataSource.ALLOW_UPDATE))   checkSQLRestriction(dc,"update",sqlparts,sql);
    }

	
	private static void checkSQLRestriction(DatasourceConnection dc, String keyword, Array sqlparts, SQL sql) throws PageException {
        if(ArrayFind.find(sqlparts,keyword,false)>0) {
            throw new DatabaseException("access denied to execute \""+StringUtil.ucFirst(keyword)+"\" SQL statement for datasource "+dc.getDatasource().getName(),null,sql,dc);
        }
    }

	public static DumpData toDumpData(Query query,PageContext pageContext, int maxlevel, DumpProperties dp) {
		maxlevel--;
		Collection.Key[] keys=CollectionUtil.keys(query);
		DumpData[] heads=new DumpData[keys.length+1];
		//int tmp=1;
		heads[0]=new SimpleDumpData("");
		for(int i=0;i<keys.length;i++) {
			heads[i+1]=new SimpleDumpData(keys[i].getString());
		}
		
		StringBuilder comment=new StringBuilder(); 
		
		//table.appendRow(1, new SimpleDumpData("SQL"), new SimpleDumpData(sql.toString()));
		String template=query.getTemplate();
		if(!StringUtil.isEmpty(template))
			comment.append("Template: ").append(template).append("\n");
		//table.appendRow(1, new SimpleDumpData("Template"), new SimpleDumpData(template));

		int top = dp.getMaxlevel();        // in Query dump maxlevel is used as Top

		comment.append("Execution Time: ").append(Caster.toString(FormatUtil.formatNSAsMSDouble(query.getExecutionTime()))).append(" ms \n");
		comment.append("Record Count: ").append(Caster.toString(query.getRecordcount()));
		if ( query.getRecordcount() > top )
			comment.append( " (showing top " ).append( Caster.toString( top ) ).append( ")" );
		comment.append("\n");
		comment.append("Cached: ").append(query.isCached()?"Yes\n":"No\n");
		comment.append("Lazy: ").append(query instanceof SimpleQuery?"Yes\n":"No\n");
		
		SQL sql=query.getSql();
		if(sql!=null)
			comment.append("SQL: ").append("\n").append(StringUtil.suppressWhiteSpace(sql.toString().trim())).append("\n");
		
		//table.appendRow(1, new SimpleDumpData("Execution Time (ms)"), new SimpleDumpData(exeTime));
		//table.appendRow(1, new SimpleDumpData("recordcount"), new SimpleDumpData(getRecordcount()));
		//table.appendRow(1, new SimpleDumpData("cached"), new SimpleDumpData(isCached()?"Yes":"No"));

		DumpTable recs=new DumpTable("query","#cc99cc","#ffccff","#000000");
		recs.setTitle("Query");
		if(dp.getMetainfo())recs.setComment(comment.toString());
		recs.appendRow(new DumpRow(-1,heads));
		
		// body
		DumpData[] items;
		int recordcount=query.getRecordcount();
		int columncount=query.getColumnNames().length;
		for(int i=0;i<recordcount;i++) {
			items=new DumpData[columncount+1];
			items[0]=new SimpleDumpData(i+1);
			for(int y=0;y<keys.length;y++) {
				try {
					Object o=query.getAt(keys[y],i+1);
					if(o instanceof String)items[y+1]=new SimpleDumpData(o.toString());
                    else if(o instanceof Number) items[y+1]=new SimpleDumpData(Caster.toString(((Number)o)));
                    else if(o instanceof Boolean) items[y+1]=new SimpleDumpData(((Boolean)o).booleanValue());
                    else if(o instanceof Date) items[y+1]=new SimpleDumpData(Caster.toString(o));
                    else if(o instanceof Clob) items[y+1]=new SimpleDumpData(Caster.toString(o));								
					else items[y+1]=DumpUtil.toDumpData(o, pageContext,maxlevel,dp);
				} catch (PageException e) {
					items[y+1]=new SimpleDumpData("[empty]");
				}
			}
			recs.appendRow(new DumpRow(1,items));

			if ( i == top - 1 )
				break;
		}
		if(!dp.getMetainfo()) return recs;
		
		//table.appendRow(1, new SimpleDumpData("result"), recs);
		return recs;
	}

	public static void removeRows(Query query, int index, int count) throws PageException {
		if(query.getRecordcount()==0) 
			throw new DatabaseException("cannot remove rows, query is empty",null,null,null);
		if(index<0 || index>=query.getRecordcount()) 
			throw new DatabaseException("invalid index ["+index+"], index must be between 0 and "+(query.getRecordcount()-1),null,null,null);
		if(index+count>query.getRecordcount()) 
			throw new DatabaseException("invalid count ["+count+"], count+index ["+(count+index)+"] must less or equal to "+(query.getRecordcount()),null,null,null);
		
		for(int row=count;row>=1;row--){
			query.removeRow(index+row);
		}
	}

	public static boolean execute(PageContext pc,Statement stat, boolean createGeneratedKeys, SQL sql) throws SQLException {
		if(stat instanceof StatementPro) {
			StatementPro sp=(StatementPro) stat;
			return createGeneratedKeys?sp.execute(pc,sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):sp.execute(pc,sql.getSQLString());
		}
		return createGeneratedKeys?stat.execute(sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):stat.execute(sql.getSQLString());
	}

	public static boolean execute(PageContext pc,PreparedStatement ps) throws SQLException {
		if(ps instanceof PreparedStatementPro) {
			PreparedStatementPro psp=(PreparedStatementPro) ps;
			return psp.execute(pc);
		}
		return ps.execute();
	}

	public static String getColumnName(ResultSetMetaData meta, int column) throws SQLException {
		try {
			return meta.getColumnLabel(column);
		} catch (SQLException e) {
			return meta.getColumnName(column);
		}
	}

	public static Object getObject(ResultSet rs,int columnIndex, Class type) throws SQLException {
		if(BigDecimal.class==type) return rs.getBigDecimal(columnIndex);
		if(Blob.class==type) return rs.getBlob(columnIndex);
		if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnIndex);
		if(byte.class==type || Byte.class==type) return rs.getByte(columnIndex);
		if(Clob.class==type) return rs.getClob(columnIndex);
		if(Date.class==type) return rs.getDate(columnIndex);
		if(double.class==type || Double.class==type) return rs.getDouble(columnIndex);
		if(float.class==type || Float.class==type) return rs.getFloat(columnIndex);
		if(int.class==type || Integer.class==type) return rs.getInt(columnIndex);
		if(long.class==type || Long.class==type) return rs.getLong(columnIndex);
		if(short.class==type || Short.class==type) return rs.getShort(columnIndex);
		if(String.class==type) return rs.getString(columnIndex);
		if(Time.class==type) return rs.getTime(columnIndex);
		if(Ref.class==type) return rs.getRef(columnIndex);
		
		throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported");
	}

	public static Object getObject(ResultSet rs,String columnLabel, Class type) throws SQLException {
		if(BigDecimal.class==type) return rs.getBigDecimal(columnLabel);
		if(Blob.class==type) return rs.getBlob(columnLabel);
		if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnLabel);
		if(byte.class==type || Byte.class==type) return rs.getByte(columnLabel);
		if(Clob.class==type) return rs.getClob(columnLabel);
		if(Date.class==type) return rs.getDate(columnLabel);
		if(double.class==type || Double.class==type) return rs.getDouble(columnLabel);
		if(float.class==type || Float.class==type) return rs.getFloat(columnLabel);
		if(int.class==type || Integer.class==type) return rs.getInt(columnLabel);
		if(long.class==type || Long.class==type) return rs.getLong(columnLabel);
		if(short.class==type || Short.class==type) return rs.getShort(columnLabel);
		if(String.class==type) return rs.getString(columnLabel);
		if(Time.class==type) return rs.getTime(columnLabel);
		if(Ref.class==type) return rs.getRef(columnLabel);
		
		throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported");
	}

	/**
	 * return the value at the given position (row), returns the default empty value ("" or null) for wrong row or null values.
	 * this method only exist for backward compatibility and should not be used for new functinality
	 * @param column
	 * @param row
	 * @return
	 * @deprecated use instead QueryColumn.get(int,Object)
	 */
	public static Object getValue(QueryColumn column, int row) {//print.ds();
		if(NullSupportHelper.full()) return column.get(row, null);
		Object v = column.get(row, "");
		return v==null?"":v;
	}

	public static QueryColumnImpl duplicate2QueryColumnImpl(QueryImpl targetQuery,QueryColumn col, boolean deepCopy) {
		if(col instanceof QueryColumnImpl)
    		return ((QueryColumnImpl)col).cloneColumnImpl(deepCopy);
    	
		// fill array for column
		Array content=new ArrayImpl();
		int len=col.size();
		for(int i=1;i<=len;i++){
			content.setEL(i, col.get(i,null));
		}
		
		// create and return column
		try {
			return new QueryColumnImpl(targetQuery,col.getKey(),content,col.getType());
		} catch (PageException e) {
			throw new PageRuntimeException(e);
		}
	}
}
