using System;
using System.Collections;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using edu.jhu.Cas;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Web;
using edu.jhu.Cas.Servers;
namespace edu.jhu.Cas.Util {
	public class FilterFactory {
		const string s = "[\\ \\n\\t\\r]+";//space
		const string nbw = "[^\\ \\t\\(\\)\\n]+";// no brackets word
		const string bw = "\\[[^\\]]+\\]"; //brackets word
		const string w = "[^\\ \\t\\n]+"; //normal word
		const RegexOptions defaultOptions = RegexOptions.IgnoreCase|RegexOptions.Multiline|RegexOptions.ExplicitCapture;
		public bool IsQuery,HasInsertClause; //ie not sp, func, etc... Init'd from StripComments
        static Regex IsValidSQLTokenRX = new Regex("^[A-Z][A-Z0-9]*$", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        public static bool IsValidSQLToken(string s) {
            return IsValidSQLTokenRX.Match(s).Success;
        }
		/**
		 * Makes a qry insert into mydb.jobid.
		 * Does nothign if query already had mydb in it
		 */
		public string EnforceMyDb(Job job) {
			if(Regex.IsMatch(job.ModifiedQuery.ToUpper(),"INTO"+s+"MYDB")) return job.ModifiedQuery;
			string replacement = "into MYDB_"+job.WebServicesID+
				".test.auto"+job.JobID+" from ";
			Regex regex = new Regex("from",defaultOptions);
			return regex.Replace(job.ModifiedQuery,replacement,1);
		}
		//* you can currently only select into ONE table per execution
		public string UseStoredProcedure(long wsid,string qry, 
			string mydbServer,string mydbLogin,string mydbPass,Server target){
			qry = InitQry(qry);
			Regex regex = new Regex("(?<1>into"+s+"[^\\ \\t\\n\\r]+)",//find the into clause now. no tmp tables or variables.
				defaultOptions);
			Match match = regex.Match(qry);
			
			string intoClause = "";
			if(match.Success){
				intoClause = match.Result("$1");
				if(intoClause.IndexOf("@") > -1 || intoClause.IndexOf("#") > -1)
					intoClause = "";
				else
					qry = qry.Replace(intoClause,"");//chop it out
			}
			if(IsQuery){						
				qry = Regex.Replace(qry,"\'","\'\'\'\'"); //double up single quotes
				qry = "N'' "+qry+" ''";//prep query as an inner arg
				qry = "'execute "+target.Host+"."+target.Catalog+".dbo./ \n"+qry+"'";//outer param
				qry = " OPENROWSET('SQLOLEDB','.';'"+mydbLogin+"';'"+mydbPass+"',"+qry+")";//rowset clause
				intoClause = Regex.Replace(intoClause,"^INTO\\s+[^.]+\\.","INTO ",defaultOptions);
				qry = "select * "+intoClause+" from "+qry;
			}else{
				//if there is no select clause then no openrowset is needed
				qry = Regex.Replace(qry,"\'","\'\'"); //double up single quotes
				qry = "N' "+qry+" '";//prep query as an inner arg
				qry = "execute "+target.Host+"."+target.Catalog+".dbo.sp_executesql \n"+qry;//outer param
			}
			return qry;
		}
		/**
		 * You cannot drop a published table. 
		 */
		public void CheckDropTable(long wsid,string qry) {
			Regex regex = new Regex("drop"+s+"table"+s+"(?<1>"+w+")",
				defaultOptions);
			Match match;
			string table;
			ArrayList publishedTables = (new TableJobs()).GetPublishedTablesList(wsid);
			for(match = regex.Match(qry); match.Success; match = match.NextMatch()) {
				table = match.Result("$1");
				if(publishedTables.Contains(table))
					throw new Exception(table+" is published.  "+
						"You must unpublish this table before you can drop it.");
			}	
		}
		public string SubstituteTargets(long wsid, string qry,int estimate,string chosenTarget){
			CasServers cServers = new CasServers();
			Server[] servers = cServers.FindTargets(ID.SERVICE_ID,wsid);
			Regex tFind;
			foreach(Server s in servers){
				if(s.Target.Trim().ToUpper().CompareTo("MYDB") == 0)continue;//mydb substitutions differ by user; do this elsewhere
				//if(s.Target.ToUpper().CompareTo(chosenTarget.ToUpper()) == 0)continue;//do not edit same-target targets
				//below is anything but a function call
				tFind = new Regex("[\\s\\n\\t]+"+s.Target+"\\.(?<1>[^\\s\\n\\t]+)",defaultOptions);
				if(tFind.IsMatch(qry)){
					Match m = tFind.Match(qry);
					while(m.Success){
						string foundthis = m.Result("$1");
						if(foundthis.Trim().EndsWith(")") && foundthis.IndexOf("(") > -1)
							throw new CasJobsException(foundthis+" is invalid: You cannot call functions from outside than their original context. The only allowed prefix for functions is 'dbo'.");
						Server replaceWith = cServers.ClosestQueue(ID.SERVICE_ID,wsid,s.Target,estimate);
					
						//qry = tFind.Replace(qry," "+replaceWith.Host+"."+replaceWith.Catalog+".dbo."+foundthis);
						qry = qry.Replace(m.ToString()," "+replaceWith.Host+"."+replaceWith.Catalog+".dbo."+foundthis);
						//qry = m.ToString()+" YATTA<br>"+qry;
						m = m.NextMatch();
					}
				}
			}
			return qry;
		}
		public string StripComments(string qry){
            qry = KillComments(qry);
			IsQuery = qry.ToUpper().StartsWith("SELECT")?true:false;
            RemoveIntoClause(0,qry,"dumb","dumb");//do not remove this, it is doing some init thing related to into table
			return qry;
		}
        public static string KillComments(string qry) {
            Regex dashComments = new Regex("\\-\\-[^\\n]*");
            qry = dashComments.Replace(qry, "");
            while (qry.IndexOf("/*") > -1) {
                int start = qry.IndexOf("/*");
                int end = qry.IndexOf("*/");
                qry = qry.Remove(start, end - start + 2);
            }
            qry = dashComments.Replace(qry, "");
            qry = qry.Trim();
            return qry;
        }
		public string AddSelectInto(string qry,string table,string target){
			if(!IsQuery)return qry;
			Regex i = new Regex(s+"into"+s,defaultOptions);
			//string prefix = target.ToUpper().IndexOf("MYDB") > -1?"":"mydb.";
            string prefix = "mydb.";
            if (!i.IsMatch(qry)) {
                qry = FilterFactory.KillComments(qry);
                Regex r = new Regex(s + "FROM" + s, defaultOptions);
                if (r.IsMatch(qry))
                    qry = r.Replace(qry, " into " + prefix + table + " from ", 1);
                else qry += " into " + prefix + table;//no from, just stick it at the end
            }
			return qry;
		}
			
		public string PrepareMyDBFunctions(long wsid, string qry, string target,string mydbuser){
			//this only applies to mydb as a target
			if(target.Trim().ToUpper().CompareTo("MYDB") != 0)return qry;
			Regex regex = new Regex("dbo\\.",defaultOptions);
			return regex.Replace(qry,mydbuser+".");
		}
		/* throws exception if query has invalid syntax */
		public void QueryIsValid(long wsid, string qry){
			/* don't allow mydb_XXXX queries */
			Regex firstTest = new Regex("MYDB_",defaultOptions);
			Match m;
			for(m = firstTest.Match(qry); m.Success; m= m.NextMatch())
				throw new Exception("Queries of format 'MYDB_<XXXXXXX>' are not allowed.");
			/* try to catch functinos being called out of context.  it will fail if you don't anyway, but this way
			 * you can maybe give a better message. */
			Regex mydbFuncs = new Regex("mydb\\.[^\\ \\t\\n\\(]+\\(",defaultOptions);
			if(mydbFuncs.IsMatch(qry)){
				Match am = mydbFuncs.Match(qry);
				throw new CasJobsException(am.Value+" is invalid: You cannot call functions from outside than their original context. The only allowed prefix for functions is 'dbo'.");
			}
		}
		/**
		 * returns modified qry if qry targets mydb, NULL otherwise
		 */
		public string CheckMyDb(long wsid,string qry,string server,string mydbUser) {
			Regex firstTest = new Regex("MYDB_",defaultOptions);//toss back invalid queries
			Match m;
			for(m = firstTest.Match(qry); m.Success; m= m.NextMatch())
				throw new Exception("Queries of format 'MYDB_<XXXXXXX>' are not allowed.");
			Regex mydbFuncs = new Regex("mydb\\.[^\\ \\t\\n\\(]+\\(",defaultOptions);
			if(mydbFuncs.IsMatch(qry)){
				Match am = mydbFuncs.Match(qry);
				throw new CasJobsException(am.Value+" is invalid: You cannot call functions from outside than their original context. The only allowed prefix for functions is 'dbo'.");
			}
			string mydbString = server+".MYDB_"+wsid;//replace all mydbs with their mydb
			int cnt = 0;
			string tmpQry= Regex.Replace(qry,"mydb",mydbString,defaultOptions);
			
			if(tmpQry.CompareTo(qry) != 0) {
				qry = tmpQry;
				++cnt;
			}
			Regex regex = new Regex("(?<1>"+mydbString+"\\.(?<2>"+nbw+"))",defaultOptions);
			Match match;
			string pattern,replacement;
			
			for(match = regex.Match(qry); match.Success; match = match.NextMatch()) {
				++cnt;
				pattern = match.Result("$1");
				replacement = match.Result(mydbString+"."+mydbUser+".$2");
				//qry = Regex.Replace(qry,pattern,replacement);
				qry = qry.Replace(pattern,replacement);
			}
			// functions should not have the server name infront 
			Regex regexF = new Regex(server+".(?<1>MYDB_"+wsid+"\\."+nbw+"\\."+nbw+"\\()",defaultOptions);
			qry = regexF.Replace(qry,"$1");
			return qry;
		}
		public string SwitchMyDb(string cString,string dbName) {
			Regex regex = new Regex("Initial\\s+Catalog\\s*\\=\\s*(?<1>[^;]+)\\;",defaultOptions);
			Match match;
			string pattern;
			for(match = regex.Match(cString); match.Success; match = match.NextMatch()) {
				pattern = match.Result("$1");
				cString = Regex.Replace(cString,pattern,dbName);
			}
			return cString;
		}
		/**  
		 * useful in groups stuff where you need that info based on the params.
		 * throws exception if user doesn't have permission
		 */
		public string GetTheirMyDBName(long yourWSID,string theirUserID,string theirTableName) {
			User lender = (new CasUsers()).FindUser(ID.SERVICE_ID," userid = '"+theirUserID+"'")[0];
			if(!(new CasGroups()).CanReadTable(yourWSID,lender.WebServicesID,theirTableName))
				throw new Exception("You do not have permission to read "+theirTableName);
			return lender.MyDBName;
		}
		public string RemoveComments(string qry) {
			Regex regex = new Regex("\\-\\-[^\\n]*\\n",
defaultOptions);
			Match match;
			for(match = regex.Match(qry); match.Success; match = match.NextMatch()) {
				qry = qry.Replace(match.ToString(),"\n");
			}
			return qry;
		}
	    private static Regex isFrom = new Regex(s+"FROM"+s,defaultOptions|RegexOptions.Compiled);
		private static Regex isInsert = new Regex(s+"INSERT"+s,defaultOptions|RegexOptions.Compiled);
		private static Regex intoReg = new Regex(s+"(?<1>into"+s+w+")",defaultOptions|RegexOptions.Compiled);
		//also sets HasIntoClause, currently handles only one into clause
		public string RemoveIntoClause(long wsid,string qry,string mydbServer,string mydbLogin){
			qry = InitQry(qry);
			Match match;
			HasInsertClause = false;
			match = intoReg.Match(qry);
			if(!match.Success)return null;
			string intoClause = match.Result("$1");
			string pTokens = qry.Substring(0,qry.IndexOf(intoClause)-1);
			if(isFrom.IsMatch(pTokens)||isInsert.IsMatch(pTokens))return null;
			if(intoClause.IndexOf("#") > -1 || intoClause.IndexOf("@") > -1)return qry;
			//do not drop insert clauses
			HasInsertClause = true;
			return qry.Replace(intoClause,"");
		}
		public string UseSpExecute(string qry, string ip , long wsid,string target){
			qry = qry.Replace("'","''");
			if(target.ToUpper().IndexOf("MYDB") == -1)
				return @"
declare  @hn nchar;
set @hn=host_name();
exec spExecuteSql2 N'"+qry+"','CasJobs',@hn,'"+ip+"','"+wsid+"'";
			else
				return 
					@"
exec sp_executeSql N'"+qry+"'";
		}
		public string CheckTarget(long wsid,string qry, Server server){
			return qry;//disabled
		}
		public static string GetCreatedTableName(string qry){
			qry = InitQry(qry);
			Regex regex= new Regex("into"+s+"(?<1>"+w+")",//find the into clause now.
				defaultOptions);
			Match match;
			
			match = regex.Match(qry);
			if(!match.Success)
				return null;
			string rst = match.Result("$1");
			try{
				rst = rst.Split('.')[1];
			}catch{}
			return rst;
		}
		private static string InitQry(string qry){
			qry = Regex.Replace(qry,"\\r",""); //take out carrige returns
			return qry;
		}
		public static string HostFromCString(string s){
			string[] chunks = s.Split(';');
			Regex r = new Regex("data source",defaultOptions);
			for(int x=0;x<chunks.Length;x++){
				if(r.IsMatch(chunks[x])){
					string[] littleChunks = chunks[x].Split('=');
					return littleChunks[1].Trim();
				}
			}
			throw new Exception("could not parse cstring; should never see this");
		}
	}
	/**
	 * mini class.  Maybe move it out of util eventually
	 */
	public class JobFilter:SqlJob {
		public JobFilter():base() {
			init();
		}
		public string FilterQueryStatement {
			get {
				StringBuilder builder = new StringBuilder();
				builder.Append("select * from filter where (target = ");
				builder.Append(TARGET+" or target is null)");
				builder.Append(" and (queue = "+QUEUE);
				builder.Append(" or queue is null)");
				return builder.ToString();
			}
		}

		public void init() {
			Add(TARGET, new JobInfo(SqlDbType.VarChar, 200, ""));
			Add(QUEUE, new JobInfo(SqlDbType.Int, 0, 0));
		}
		public static string TARGET = "@target";
		public static string QUEUE = "@queue";
	}

}
