package railo.runtime.tag;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.jsp.JspException;

import railo.commons.io.IOUtil;
import railo.commons.lang.StringUtil;
import railo.commons.sql.SQLUtil;
import railo.runtime.config.ConfigImpl;
import railo.runtime.config.Constants;
import railo.runtime.db.CFTypes;
import railo.runtime.db.DataSource;
import railo.runtime.db.DataSourceManager;
import railo.runtime.db.DataSourceSupport;
import railo.runtime.db.DatasourceConnection;
import railo.runtime.db.ProcMeta;
import railo.runtime.db.ProcMetaCollection;
import railo.runtime.db.SQLCaster;
import railo.runtime.db.SQLImpl;
import railo.runtime.db.SQLItemImpl;
import railo.runtime.exp.ApplicationException;
import railo.runtime.exp.DatabaseException;
import railo.runtime.exp.PageException;
import railo.runtime.ext.tag.BodyTagTryCatchFinallySupport;
import railo.runtime.listener.ApplicationContextPro;
import railo.runtime.op.Caster;
import railo.runtime.tag.util.DeprecatedUtil;
import railo.runtime.type.Array;
import railo.runtime.type.ArrayImpl;
import railo.runtime.type.Collection.Key;
import railo.runtime.type.KeyImpl;
import railo.runtime.type.QueryImpl;
import railo.runtime.type.Struct;
import railo.runtime.type.StructImpl;
import railo.runtime.type.dt.DateTime;
import railo.runtime.type.dt.DateTimeImpl;
import railo.runtime.type.dt.TimeSpan;
import railo.runtime.type.util.KeyConstants;






public class StoredProc extends BodyTagTryCatchFinallySupport {
	//private static final int PROCEDURE_CAT=1;        
	//private static final int PROCEDURE_SCHEM=2;
	//private static final int PROCEDURE_NAME=3;
	//private static final int COLUMN_NAME=4;
	private static final int COLUMN_TYPE=5;
	private static final int DATA_TYPE=6;
	private static final int TYPE_NAME=7;
	//|PRECISION|LENGTH|SCALE|RADIX|NULLABLE|REMARKS|SEQUENCE|OVERLOAD|DEFAULT_VALUE
	

	private static final railo.runtime.type.Collection.Key KEY_SC = KeyImpl.intern("StatusCode");
	
	private static final railo.runtime.type.Collection.Key COUNT = KeyImpl.intern("count_afsdsfgdfgdsfsdfsgsdgsgsdgsasegfwef");
	
	private static final ProcParamBean STATUS_CODE;
	private static final railo.runtime.type.Collection.Key STATUSCODE = KeyImpl.intern("StatusCode");
	
	static{
		STATUS_CODE = new ProcParamBean();
		STATUS_CODE.setType(Types.INTEGER);
		STATUS_CODE.setDirection(ProcParamBean.DIRECTION_OUT);
		STATUS_CODE.setVariable("cfstoredproc.statusCode");
	}
	
	
	private List<ProcParamBean> params=new ArrayList<ProcParamBean>();
	private Array results=new ArrayImpl();

	private String procedure;
	private String datasource=null;
	private String username;
	private String password;
	private int blockfactor=-1;
	private int timeout=-1;
	private boolean debug=true;
	private boolean returncode;
	private String result="cfstoredproc";
	
	private boolean clearCache;
	private DateTimeImpl cachedbefore;
	//private String cachename="";
	private DateTime cachedafter;
	private ProcParamBean returnValue=null;
	//private Map<String,ProcMetaCollection> procedureColumnCache;
	
	public void release() {
		params.clear();
		results.clear();
		returnValue=null;
		procedure=null;
		datasource=null;
		username=null;
		password=null;
		blockfactor=-1;
		timeout=-1;
		debug=true;
		returncode=false;
		result="cfstoredproc";
		

		clearCache=false;
		cachedbefore=null;
		cachedafter=null;
		//cachename="";
		
		super.release();
	}
	
	


	/** set the value cachedafter
	*  This is the age of which the query data can be
	* @param cachedafter value to set
	**/
	public void setCachedafter(DateTime cachedafter)	{
		//railo.print.ln("cachedafter:"+cachedafter);
		this.cachedafter=cachedafter;
	}

	/** set the value cachename
	*  This is specific to JTags, and allows you to give the cache a specific name
	* @param cachename value to set
	**/
	public void setCachename(String cachename)	{
		DeprecatedUtil.tagAttribute(pageContext,"StoredProc", "cachename");
	}

	/** set the value cachedwithin
	*  
	* @param cachedwithin value to set
	**/
	public void setCachedwithin(TimeSpan cachedwithin)	{
		if(cachedwithin.getMillis()>0)
			this.cachedbefore=new DateTimeImpl(pageContext,System.currentTimeMillis()+cachedwithin.getMillis(),false);
		else clearCache=true;
	}

	/**
	 * @param blockfactor The blockfactor to set.
	 */
	public void setBlockfactor(double blockfactor) {
		this.blockfactor = (int) blockfactor;
	}
	
	/**
	 * @param blockfactor
	 * @deprecated replaced with setBlockfactor(double)
	 */
	public void setBlockfactor(int blockfactor) {
		DeprecatedUtil.tagAttribute(pageContext,"storedproc","blockfactor");
		this.blockfactor = blockfactor;
	}

	/**
	 * @param datasource The datasource to set.
	 */
	public void setDatasource(String datasource) {
		this.datasource = datasource;
	}

	/**
	 * @param username The username to set.
	 */
	public void setUsername(String username) {
		this.username = username;
	}

	/**
	 * @param password The password to set.
	 */
	public void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @param debug The debug to set.
	 */
	public void setDebug(boolean debug) {
		this.debug = debug;
	}

	/**
	 * @param procedure The procedure to set.
	 */
	public void setProcedure(String procedure) {
		this.procedure = procedure;
	}

	/**
	 * @param result The result to set.
	 */
	public void setResult(String result) {
		this.result = result;
	}

	/**
	 * @param returncode The returncode to set.
	 */
	public void setReturncode(boolean returncode) {
		this.returncode = returncode;
	}

	/**
	 * @param dbvarname the dbvarname to set
	 */
	public void setDbvarname(String dbvarname) {
		DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbvarname");
	}
	public void setDbtype(String dbtype) {
		DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbtype");
	}

	public void addProcParam(ProcParamBean param) {
		params.add(param);
	}

	public void addProcResult(ProcResultBean result) {
		results.setEL(result.getResultset(),result);
	}

	@Override
	public int doStartTag() throws JspException {
		
		return EVAL_BODY_INCLUDE;
	}

	private void returnValue(DatasourceConnection dc) throws PageException {
		Connection conn = dc.getConnection();
		
		
		if(SQLUtil.isOracle(conn)){
			String name=this.procedure.toUpperCase();
			int index=name.lastIndexOf('.');
			
			String pack=null,scheme=null;
			if(index!=-1){
				pack=name.substring(0,index);
				name=name.substring(index+1);
				
				index=pack.lastIndexOf('.');
				if(index!=-1){
					scheme=pack.substring(index+1);
					pack=pack.substring(0,index);
				}
				
				
			}
			
			try {
				DatabaseMetaData md = conn.getMetaData();
				
				//if(procedureColumnCache==null)procedureColumnCache=new ReferenceMap();
				//ProcMetaCollection coll=procedureColumnCache.get(procedure);
				DataSourceSupport d = ((DataSourceSupport)dc.getDatasource());
				long cacheTimeout = d.getMetaCacheTimeout();
				Map<String, ProcMetaCollection> procedureColumnCache = d.getProcedureColumnCache();
				ProcMetaCollection coll=procedureColumnCache.get(procedure);
				
				if(coll==null || (cacheTimeout>=0 && (coll.created+cacheTimeout)<System.currentTimeMillis())) {
					ResultSet res = md.getProcedureColumns(pack, scheme, name, null);
					coll=createProcMetaCollection(res);
					procedureColumnCache.put(procedure,coll);
				}
				
				index=-1;
				for(int i=0;i<coll.metas.length;i++) { 
					index++;
					
					// Return
					if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnReturn) {
						index--;
						ProcResultBean result= getFirstResult();
						ProcParamBean param = new ProcParamBean();
						
						param.setType(coll.metas[i].dataType);
						param.setDirection(ProcParamBean.DIRECTION_OUT);
						if(result!=null)param.setVariable(result.getName());
						returnValue=param;
						
					}	
					else if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnOut || coll.metas[i].columnType==DatabaseMetaData.procedureColumnInOut) {
						if(coll.metas[i].dataType==CFTypes.CURSOR){
							ProcResultBean result= getFirstResult();
							
							ProcParamBean param = new ProcParamBean();
							param.setType(coll.metas[i].dataType);
							param.setDirection(ProcParamBean.DIRECTION_OUT);
							if(result!=null)param.setVariable(result.getName());
							params.add(index, param);
						}
						else {								
							ProcParamBean param= params.get(index);
							if(coll.metas[i].dataType!=Types.OTHER && coll.metas[i].dataType!=param.getType()){
								param.setType(coll.metas[i].dataType);
							}
						}
					}	
					else if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnIn) {	
						ProcParamBean param=get(params,index);
						if(param!=null && coll.metas[i].dataType!=Types.OTHER && coll.metas[i].dataType!=param.getType()){
							param.setType(coll.metas[i].dataType);
						}
					}	
				}
				contractTo(params,index+1);
				
				//if(res!=null)print.out(new QueryImpl(res,"columns").toString());
			} 
			catch (SQLException e) {
			    throw new DatabaseException(e,dc);
			}
			
			
		}
		
		// return code
		if(returncode) {
			returnValue=STATUS_CODE;
		}
	}

	private static ProcParamBean get(List<ProcParamBean> params, int index) {
		try{
			return params.get(index);
		}
		catch(Throwable t){
			return null;
		}
		
	}




	private void contractTo(List<ProcParamBean> params, int paramCount) {
		if(params.size()>paramCount){
			for(int i=params.size()-1;i>=paramCount;i--){
				params.remove(i);
			}
		}
	}




	private ProcMetaCollection createProcMetaCollection(ResultSet res) throws SQLException {
		/*
		try {
			print.out(new QueryImpl(res,"qry"));
		} catch (PageException e) {}
		*/
		ArrayList<ProcMeta> list=new ArrayList<ProcMeta>();
		while(res.next()) {
			list.add(new ProcMeta(res.getInt(COLUMN_TYPE),getDataType(res)));
		}
		return new ProcMetaCollection(list.toArray(new ProcMeta[list.size()]));
	}




	private int getDataType(ResultSet res) throws SQLException {
		int dataType=res.getInt(DATA_TYPE);	
		if(dataType==Types.OTHER) {
			String  strDataType= res.getString(TYPE_NAME);
			if("REF CURSOR".equalsIgnoreCase(strDataType))dataType=CFTypes.CURSOR;
			if("CLOB".equalsIgnoreCase(strDataType))dataType=Types.CLOB;
			if("BLOB".equalsIgnoreCase(strDataType))dataType=Types.BLOB;
		}
		return dataType;
	}




	private ProcResultBean getFirstResult() {
		Iterator<Key> it = results.keyIterator();
		if(!it.hasNext()) return null;
			
		return (ProcResultBean) results.removeEL(it.next());
	}

	@Override
	public int doEndTag() throws JspException {
		long startNS=System.nanoTime();
		
		Object ds=datasource;
		if(StringUtil.isEmpty(datasource)){
			ds=((ApplicationContextPro)pageContext.getApplicationContext()).getDefDataSource();
			if(StringUtil.isEmpty(ds))
				throw new ApplicationException(
						"attribute [datasource] is required, when no default datasource is defined",
						"you can define a default datasource as attribute [defaultdatasource] of the tag "+Constants.CFAPP_NAME+" or as data member of the "+Constants.APP_CFC+" (this.defaultdatasource=\"mydatasource\";)");
		}
		
		
		
	    Struct res=new StructImpl();
		DataSourceManager manager = pageContext.getDataSourceManager();
		DatasourceConnection dc = ds instanceof DataSource?
				manager.getConnection(pageContext,(DataSource)ds,username,password):
				manager.getConnection(pageContext,Caster.toString(ds),username,password);
		
		// create returnValue 
		returnValue(dc);
		
		// create SQL 
		StringBuilder sql=createSQL();
		

		// add returnValue to params
		if(returnValue!=null){
			params.add(0,returnValue);
		}
		
		SQLImpl _sql=new SQLImpl(sql.toString());
		CallableStatement callStat=null;
		try {
		    callStat = dc.getConnection().prepareCall(sql.toString());
		    		//ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
    				//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
		    
		    if(blockfactor>0)callStat.setFetchSize(blockfactor);
		    if(timeout>0)callStat.setQueryTimeout(timeout);
		    
	// set IN register OUT
		    Iterator<ProcParamBean> it = params.iterator();
			ProcParamBean param;
			int index=1;
		    while(it.hasNext()) {
		    	param= it.next();
		    	param.setIndex(index);
		    	_sql.addItems(new SQLItemImpl(param.getValue()));
	    		if(param.getDirection()!=ProcParamBean.DIRECTION_OUT) {
	    			SQLCaster.setValue(pageContext.getTimeZone(),callStat, index, param);
		    	}
		    	if(param.getDirection()!=ProcParamBean.DIRECTION_IN) {
		    		registerOutParameter(callStat,param);
		    	}
		    	index++;
			}
		    
	// cache
		    boolean isFromCache=false;
		    boolean hasCached=cachedbefore!=null || cachedafter!=null;
		    Object cacheValue=null;
		    String dsn = ds instanceof DataSource?((DataSource)ds).getName():Caster.toString(ds);
			if(clearCache) {
				hasCached=false;
				pageContext.getQueryCache().remove(pageContext,_sql,dsn,username,password);
			}
			else if(hasCached) {
				cacheValue = pageContext.getQueryCache().get(pageContext,_sql,dsn,username,password,cachedafter);
			}
			int count=0;
			if(cacheValue==null){
				// execute
				boolean isResult=callStat.execute();
				
			    Struct cache=hasCached?new StructImpl():null;
	
			    // resultsets
			    ProcResultBean result;
			    
			    index=1;
				do {
			    	if(isResult){
			    		ResultSet rs=callStat.getResultSet();
			    		if(rs!=null) {
							try{
								result=(ProcResultBean) results.get(index++,null);
								if(result!=null) {
									railo.runtime.type.Query q = new QueryImpl(rs,result.getMaxrows(),result.getName(),pageContext.getTimeZone());	
									count+=q.getRecordcount();
									setVariable(result.getName(), q);
									if(hasCached)cache.set(KeyImpl.getInstance(result.getName()), q);
								}
							}
							finally{
								IOUtil.closeEL(rs);
							}
						}
			    	}
			    }
			    while((isResult=callStat.getMoreResults()) || (callStat.getUpdateCount() != -1));

			    // params
			    it = params.iterator();
			    while(it.hasNext()) {
			    	param= it.next();
			    	if(param.getDirection()!=ProcParamBean.DIRECTION_IN){
			    		Object value=null;
			    		if(!StringUtil.isEmpty(param.getVariable())){
			    			try{
			    				value=SQLCaster.toCFType(callStat.getObject(param.getIndex()));
			    			}
			    			catch(Throwable t){}
			    			value=emptyIfNull(value);
			    			
			    			if(param==STATUS_CODE) res.set(STATUSCODE, value);
			    			else setVariable(param.getVariable(), value);
			    			if(hasCached)cache.set(KeyImpl.getInstance(param.getVariable()), value);
			    		}
			    	}
				}
			    if(hasCached){
			    	cache.set(COUNT, Caster.toDouble(count));
			    	pageContext.getQueryCache().set(pageContext,_sql,dsn,username,password,cache,cachedbefore);
			    }
			    
			}
			else if(cacheValue instanceof Struct) {
				Struct sctCache = (Struct) cacheValue;
				count=Caster.toIntValue(sctCache.removeEL(COUNT),0);
				
				Iterator<Entry<Key, Object>> cit = sctCache.entryIterator();
				Entry<Key, Object> ce;
				while(cit.hasNext()){
					ce = cit.next();
					if(STATUS_CODE.getVariable().equals(ce.getKey().getString()))
						res.set(KEY_SC, ce.getValue());
					else setVariable(ce.getKey().getString(), ce.getValue());
				}
				isFromCache=true;
			}
			
		    // result
		    long exe;
		    
		    setVariable(this.result, res);
		    res.set(KeyConstants._executionTime,Caster.toDouble(exe=(System.nanoTime()-startNS)));
		    res.set(KeyConstants._cached,Caster.toBoolean(isFromCache));
		    
		    if(pageContext.getConfig().debug() && debug) {
		    	boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
				if(logdb)
					pageContext.getDebugger().addQuery(null,dsn,procedure,_sql,count,pageContext.getCurrentPageSource(),(int)exe);
			}
		    
		    
		}
		catch (SQLException e) {
		    throw new DatabaseException(e,new SQLImpl(sql.toString()),dc);
		}
		finally {
		    if(callStat!=null){
			    try {
					callStat.close();
				} catch (SQLException e) {}
		    }
		    manager.releaseConnection(pageContext,dc);
		}
		return EVAL_PAGE;
	}

	private void setVariable(String name, Object value) throws PageException {
		pageContext.setVariable(name, value);
	}




	private StringBuilder createSQL() {
		StringBuilder sql=new StringBuilder();
		if(returnValue!=null)sql.append("{? = call ");
		else sql.append("{ call ");
		sql.append(procedure);
		sql.append('(');
		int incount=params.size();
		
		for(int i=0;i<incount;i++) {
			if(i==0)sql.append('?');
			else sql.append(",?");
		}
		sql.append(") }");
		return sql;
		
	}




	private Object emptyIfNull(Object object) {
		if(object==null)return "";
		return object;
	}

	private void registerOutParameter(CallableStatement proc, ProcParamBean param) throws SQLException {
		if(param.getScale()==-1)proc.registerOutParameter(param.getIndex(),param.getType());
		else proc.registerOutParameter(param.getIndex(),param.getType(),param.getScale());
	}

	/**
	 * @param b
	 */
	public void hasBody(boolean b) {
		
	}

	/**
	 * @param timeout the timeout to set
	 */
	public void setTimeout(double timeout) {
		this.timeout = (int) timeout;
	}
	
	
}


