using System;
using System.Collections;
using System.Collections.Specialized;
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 abstract class FilterPower {
        public const string INTO_IDENT = "CASJOBS_INTO_TABLE";
        public const char QUERY_SEPERATOR = '\u03a0';//or pi
        public const string QUERY_PREFIX = "";//transaction isolation level is now set in the command
        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\\r]+"; //normal word
        const RegexOptions defaultOptions = RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.Compiled;
        public static bool DoWebLog {
            get {
                string test = System.Configuration.ConfigurationManager.AppSettings["do_weblog"];
                return test == null ? false : bool.Parse(test);
            }
        }
        /**
         * if this is a sqlexception resulting from a non-existant object, it returns the name of the
         * object that does not exist.  else, return null;
         */
        public static string GetInvalidObjectFromError(Exception e) {
            if (e is System.Data.SqlClient.SqlException && e.Message.IndexOf("Invalid object name") > -1)
                return getinvalidRegx.Match(e.Message).Result("$1");
            else return null;
        }
        static Regex getinvalidRegx = new Regex("\'(?<1>[^\']+)'", defaultOptions);
        /**
         * if this is a sqlexception resulting from a non-existant column, it returns the names of the
         * columns that do not exist.  else, return null;
         */
        public static string[] GetInvalidColumnsFromError(Exception e) {
            if (e is System.Data.SqlClient.SqlException && e.Message.IndexOf("Invalid column name") > -1) {
                Match m = getinvalidRegx.Match(e.Message);
                string rst = "";
                while (m.Success) {
                    rst += m.Result("$1") + ",";
                    m = m.NextMatch();
                }
                return rst.Trim(',').Split(',');
            } else return null;
        }

        /** 
         * returns the into table for a query processed through Query2Queries (not a statement)
         * returns null if there isn't one.
         */
        public static string GetSelectIntoTable(string qry) {
            int i = qry.IndexOf(INTO_IDENT);
            if (i < 0) return null;
            int s = qry.IndexOf(":", i) + 1;
            int e = qry.IndexOf("*/", i);
            qry = qry.Substring(s, e - s);
            return qry.Substring(qry.IndexOf('.') + 1);
        }
        static Regex GetCreatedTableRegex = new Regex("(?<1>create" + s + "table" + s + "(?<2>[^\\ \\t\\n\\r]+))",//find the into clause now. no tmp tables or variables.
       defaultOptions);
        public static StringCollection GetCreatedTables(string qry) {
            StringCollection rst = new StringCollection();
            Match m = GetCreatedTableRegex.Match(qry);
            while (m.Success) {
                rst.Add(m.Result("$2"));
                m = m.NextMatch();
            }
            return rst;
        }
        static string getHost(long wsid, string target, int queue, string mydbhost, Server[] allservers) {
            target = target.ToLower().Trim();
            string chosenHost = null;
            if (target.CompareTo("mydb") == 0)
                chosenHost = mydbhost.ToLower().Trim().Trim(new char[] { '[', ']' });
            else {
                foreach (Server s in allservers) {
                    if (s.Target.ToLower().Trim() == target && s.Queue == queue)
                        chosenHost = s.Host.ToLower().Trim();
                }
            }
            if (chosenHost == null) throw new CasJobsException("Could not resolve " + target + "/" + queue);
            return chosenHost;
        }
        /**
         * should be called first.  this formats the statement into something that will generate plan, while
         * preserving the gist of the query.  should then create a plan, then call query2queries
         */
        public static string InitQuery(long wsid, string qry, string[] mydbhcu, string target, int estimate) {
            Server[] allservers = (new CasServers()).FindServers(ID.SERVICE_ID, wsid);
            string chosenHost = getHost(wsid, target, estimate, mydbhcu[0], allservers);
            DenyDirectMyDBQueries(qry);
            qry = FilterFactory.KillComments(qry);
            qry = qry.Replace("..", ".");// '..' means nothing in cjobs context syntax
            qry = DebracketQuery(qry);//get rid of brackets
            qry = SubstituteSelfGroups(wsid, qry);
            qry = SubstituteMyDB(qry, mydbhcu);
            qry = PrepareMyDBFunctions(qry, target, mydbhcu[2]);
            qry = SubstituteContexts(wsid, qry, estimate, target, chosenHost, allservers);
            qry = SubstituteGroups(wsid, qry, mydbhcu[2]);
            return qry;
        }
        //don't let queries like mydb_xxxx go through
        public static void DenyDirectMyDBQueries(string qry) {
            qry = qry.ToUpper();
            if (qry.IndexOf("MYDB_") > -1) throw new CasJobsException("Queries may not directly reference MyDB catalogs.");
        }
        public static string PrepPlanQuery(string qry) {
            qry = FilterFactory.KillComments(qry).Trim();
            if (qry.ToUpper().StartsWith("SELECT"))
                qry = RemoveIntoClause(qry, new string[] { "[MYDBHOST]", "[MYDBCAT]", "[MYDBUSER]" });
            return qry;
        }
        /** 
         * temporarily replace brackets with equiv not requiring brackets
         */
        static string DebracketQuery(string qry) {
            Match match = debracketRegx.Match(qry);
            while (match.Success) {
                string s = match.Result("$1");
                /*
                * i'm guessing here.  trying to replace all chars that make this bracket word an invalid non-bracket word
                */
                string r = s.Replace(" ", WS);
                r = r.Replace("\n", NL);
                r = r.Replace("\r", CR);
                r = r.Replace("\t", TAB);
                r = r.Replace(".", DOT);
                r = OBR + r + CBR;
                qry = qry.Replace("[" + s + "]", r);
                match = match.NextMatch();
            }
            return qry;
        }
        static Regex debracketRegx = new Regex("\\[(?<1>[^\\]]+)\\]",//[any not closing bracket]
           defaultOptions);
        const string WS = "__WS_";
        const string NL = "__NL_";
        const string TAB = "__TAB_";
        const string DOT = "__DOT_";
        const string CR = "__CR_";
        const string OBR = "__OBR_";
        const string CBR = "__CBR_";
        /** 
         * puts the brackets back
         */
        static string RebracketQuery(string qry) {
            qry = qry.Replace(WS, " ");
            qry = qry.Replace(NL, "\n");
            qry = qry.Replace(CR, "\r");
            qry = qry.Replace(TAB, "\t");
            qry = qry.Replace(DOT, ".");
            qry = qry.Replace(OBR, "[");
            qry = qry.Replace(CBR, "]");
            return qry;
        }
        /**
         * splits a query into separate queries, based on separator.
         */
        static string[] SplitQuery(string qry) {
            Match m = splitQueryRegx.Match(qry);
            int offset = 0;
            while (m.Success) {
                qry = qry.Remove(m.Index + offset, m.Result("$1").Length);//remove the 'go'
                qry = qry.Insert(m.Index + offset, QUERY_SEPERATOR + "");//replace with an easy to find separator
                offset += 1 - m.Result("$1").Length;
                m = m.NextMatch();
            }
            qry = qry.Trim(' ').Trim('\n').Trim('\t').Trim('\r');
            qry = qry.Trim(QUERY_SEPERATOR);//kill the last one, in case it's there
            return qry.Split(QUERY_SEPERATOR);
        }
        static Regex splitQueryRegx = new Regex("(?<1>^[\\s\\t]*GO[\\s\\t]*\\n?)", defaultOptions);

        /*
         * converts a query into a formatted and filtered series of queries.
         * does not do groups; this should be done before.  Should be run only after running InitQuery
         * expects a deprefixed query
         */
        public static string QueryToQueries(long wsid, string mydb_h_c_u, string target, int estimate, string qry, out int actualqueue) {
            string[] mydbhcu = mydb_h_c_u.Split('.');
            string queries = "";
            string cQry = "";
            actualqueue = (new CasServers()).ClosestQueue(ID.SERVICE_ID, wsid, target, estimate).Queue;
            qry = InitQuery(wsid, qry, mydbhcu, target, actualqueue);
            string[] sQry = SplitQuery(qry);
            for (int i = 0; i < sQry.Length; i++) {
                cQry = sQry[i];
                string cUpper = cQry.ToUpper().Trim();
                CheckDropTable(wsid, qry);
                if (cUpper.IndexOf("SELECT") > -1) cQry = RemoveIntoClause(cQry, mydbhcu);
                cQry = RebracketQuery(cQry);
                if (cUpper.IndexOf("SELECT") > -1) cQry = QUERY_PREFIX + cQry;
                queries += cQry;
                queries += QUERY_SEPERATOR;
            }
            queries = queries.Trim(QUERY_SEPERATOR);//cut the last one
            return queries;
        }
        /*  checks a single query and makes sure it is valid  */
        static void ValidateQuery(string qry) {
            if (MYDB_Regx.Match(qry).Success) throw new Exception("Please use format 'MYDB.<OBJECT> rather than 'MYDB_<XXXXXXX>.<OBJECT>'.");
            //do the filters here
        }
        static Regex MYDB_Regx = new Regex("MYDB_", defaultOptions);//toss back invalid queries
        /**
         * if it exists, moves select into clause out from query and into formatted comment.
         */
        static string RemoveIntoClause(string qry, string[] mydbhcu) {
            Match match = removeIntoRegx.Match(qry);
            if (!match.Success) return qry;
            string intoClause = match.Result("$1");
            string intoTable = match.Result("$2").Trim();
            if (intoTable.StartsWith("@") || intoTable.StartsWith("#")) return qry;// do not modify inserts into tmp or vars
            if (previousWord(qry, match.Index - 1).ToLower().CompareTo(INSERT) == 0) return qry;//do not modify 'insert into's
            qry = qry.Replace(intoClause, "");
            if (intoTable.ToUpper().IndexOf("MYDB_") == -1) {
                intoTable = "mydb." + intoTable;
                intoTable = SubstituteMyDB(intoTable, mydbhcu);
            }
            qry = qry + EncloseKey(INTO_IDENT, intoTable);
            return qry;
        }
        const string INSERT = "insert";
        static Regex removeIntoRegx = new Regex("(?<1>into" + s + "(?<2>[^\\ \\t\\n\\r]+))",//find the into clause now. no tmp tables or variables.
           defaultOptions);
        static string previousWord(string qry, int start_at) {
            string rst = "";
            int ci = start_at;
            for (int i = start_at; i >= 0; i--)//churn through white space
                if (isWhiteSpace(qry[i])) continue;
                else {
                    ci = i;
                    break;
                }
            for (int i = ci; i >= 0; i--) {//churn through all non whitespace
                if (!isWhiteSpace(qry[i]) && i > 0) continue;
                else {
                    ci = i;
                    break;
                }
            }
            if (ci == start_at) return rst;
            return qry.Substring(ci, start_at - ci).Trim();
        }
        static bool isWhiteSpace(char c) { return c == ' ' || c == '\n' || c == '\t' || c == '\r'; }
        static string SubstituteMyDB(string qry, string[] mydbhcu) {
            Match m = mydbRegx.Match(qry);
            while (m.Success) {
                string m2 = m.Result("$2").Trim();
                if (m2 != null && m2.EndsWith(")") && m2.IndexOf("(") > -1)
                    qry = qry.Replace(m.Value, mydbhcu[1] + "." + mydbhcu[2] + "." + m.Result("$2"));
                else
                    qry = qry.Replace(m.Value, mydbhcu[0] + "." + mydbhcu[1] + "." + mydbhcu[2] + "." + m.Result("$2"));
                m = m.NextMatch();
            }
            return qry;
        }
        static Regex mydbRegx = new Regex("(?<1>mydb\\.)(?<2>[^\\ \\t\\n\\r]+)", defaultOptions);
        static string EncloseKey(string key, string val) { return "/*" + key + ":" + val + "*/"; }

        /* prefixes functions in mydb context with mydbuser */
        static string PrepareMyDBFunctions(string qry, string target, string mydbuser) {
            if (target.Trim().ToUpper().CompareTo("MYDB") != 0) return qry;
            return dboRegex.Replace(qry, mydbuser + ".");
        }
        static Regex dboRegex = new Regex("dbo\\.", defaultOptions);
        /* modifies a query to use spExecuteSql2 */
        public static string GetLogStart(long wsid, string qry, string ip) {
            string safeQry = qry.Replace("'", "''");
            string pt1 = @"
declare @hn nchar; 
set @hn=host_name(); 
declare @date datetime; 
set @date = getdate(); 
declare @cpustart int; 
set @cpustart = @@CPU_BUSY; 
declare @ip nchar; 
set @ip = '" + ip + @"'; 
select @cpustart as cpustart, @ip as ip, @hn as hn into #CJOBS_TMP12345
exec weblog.dbo.spLogSqlStatement N'" + safeQry + "','CasJobs',@hn,@ip,'" + wsid + @"',@date;
";
            return pt1;
        }
        public static string GetCloser(long wsid, string message) {
            if (message != null) message = message.Replace("'", "''");
            return @"
declare @hn nchar; 
select @hn=hn from #CJOBS_TMP12345; 
declare @cpustart int; 
select @cpustart=cpustart from #CJOBS_TMP12345; 
declare @ip nchar; 
select @ip=ip from #CJOBS_TMP12345; 
declare @edate datetime; 
set @edate = getdate(); 
declare @btime int; 
set @btime = @@CPU_BUSY - @cpustart; 
declare @r int; 
set @r = @@rowcount; 
declare @date datetime; 
set @date = getdate(); 
exec weblog.dbo.spLogSqlPerformance 'CasJobs',@hn,@ip,'" + wsid + @"',@date,@btime,@edate,@r,'" + message + "';";
        }
        /* 
         * modifies a query non-mydb contexts, ie dr4.photobj to literal values.
         * searches for each valid context and looks for context.dboject pattern.
         */
        static string SubstituteContexts(long wsid, string qry, int estimate, string chosenTarget, string chosenHost, Server[] allservers) {
            chosenTarget = chosenTarget.ToLower().Trim();
            Regex tFind;
            CasServers cServers = new CasServers();
            foreach (Server s in allservers) {
                if (estimate < 5 && s.Queue >= 5) continue;//if quick, look at only quick servers.  hacky, should fix
                if (estimate >= 5 && s.Queue < 5) continue;//if long, only look at long.  yes, still hacky
                if (s.Target.Trim().ToUpper().CompareTo("MYDB") == 0) continue;//mydb substitutions differ by user; do this elsewhere
                //below is anything but a function call
                tFind = new Regex("[\\s\\n\\t]+" + s.Target + "\\.(?<1>[^\\s\\n\\t]+)", defaultOptions);
                if (tFind.IsMatch(qry)) {//found a matching context?
                    Match m = tFind.Match(qry);
                    while (m.Success) {
                        bool isFunction = false;
                        string foundthis = m.Result("$1");
                        if (foundthis.IndexOf('.') > -1) {
                            m = m.NextMatch();
                            continue;//can happen if there is a group with the same name as a context
                        }
                        if (foundthis.Trim().EndsWith(")") && foundthis.IndexOf("(") > -1) {
                            isFunction = true;
                            if (s.Host.ToLower().Trim().CompareTo(chosenHost) != 0)
                                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);
                        if (isFunction)
                            qry = qry.Replace(m.Value, " [" + replaceWith.Catalog + "].dbo." + foundthis);
                        else
                            qry = qry.Replace(m.Value, " [" + replaceWith.Host + "].[" + replaceWith.Catalog + "].dbo." + foundthis);
                        m = m.NextMatch();
                    }
                }
            }
            return qry;
        }
        /**
         * You cannot drop a published table. 
         * Throws an exception is table is published.
         * Purges cjobs info about a table if it is not.
         * You could purge info either before or after a suspect query is run.
         * Might as well do it before.
         */
        static void CheckDropTable(long wsid, string qry) {
            Match match;
            string table;
            ArrayList publishedTables = (new TableJobs()).GetPublishedTablesList(wsid);
            TableJobs et = new TableJobs();
            for (match = dropTableRegx.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.");
                else
                    et.PurgeTable(wsid, table);
            }
        }
        static Regex dropTableRegx = new Regex("drop" + s + "table" + s + "(?<1>" + w + ")", defaultOptions);
        /*
         * looks for valid <groupname>.<user>.<dboject> patters and replaces them with 
         * literals if permission is valid.
         */
        static string SubstituteGroups(long wsid, string qry, string mydbLogin) {
            CasUsers cUsers = new CasUsers();
            Regex regx;
            ArrayList gtables = (new edu.jhu.Cas.Jobs.TableJobs()).GetGroupTablesList(wsid);
            CasGroups cgroups = new CasGroups();
            foreach (object o in gtables) {
                string[] gobject = o.ToString().Split('.');
                string gregexs = "(" + OBR + ")?" + gobject[0] + "(" + CBR + ")?\\." + "(" + OBR + ")?"
                    + gobject[1] + "(" + CBR + ")?\\." + "(" + OBR + ")?" + gobject[2] + "(" + CBR + ")?";
                regx = new Regex(gregexs, RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture | RegexOptions.IgnorePatternWhitespace);
                Match m = Regex.Match(qry, gregexs);
                while (m.Success) {
                    string[] gobj = m.Value.Replace(OBR, "").Replace(CBR, "").Split('.');
                    long theirwsid = cUsers.GetWebServiceId(ID.SERVICE_ID, gobj[1]);
                    User lender = cUsers.FindUser(ID.SERVICE_ID, " userid = '" + gobj[1] + "'")[0];
                    if (!cgroups.CanReadTable(wsid, lender.WebServicesID, gobj[2]))
                        throw new Exception("You do not have permission to read " + gobj[2]);
                    Server theirMyDB = cUsers.MyDBServerObject(theirwsid);
                    string replacement = "[" + theirMyDB.Host + "]." + "[" + theirMyDB.Catalog + "].[" + mydbLogin + "].[" + gobj[2] + "]";
                    qry = qry.Replace(m.Value, replacement);
                    m = m.NextMatch();
                }
            }
            return qry;
        }
        /**
         * allows users to use group syntax on their own tables
         */
        static string SubstituteSelfGroups(long wsid, string qry) {
            User u = (new CasUsers()).FindUser(ID.SERVICE_ID, " webservicesid = " + wsid)[0];
            string formattedUserid = u.UserID.ToLower().Trim();
            Match m = grpRegx.Match(qry);
            while (m.Success) {
                string guserid = m.Result("$1");
                if (guserid == null) continue;
                guserid = guserid.ToLower().Trim();
                if (guserid == null) continue;
                if (guserid.CompareTo(formattedUserid) == 0)
                    qry = qry.Replace(m.Value, "mydb." + m.Result("$2"));
                m = m.NextMatch();
            }
            return qry;
        }
        public static Regex grpRegx = new Regex("(?<3>(?<4>" + w + ")\\.(?<1>" + w + ")\\.(?<2>" + nbw + "))", defaultOptions);
    }
}