using System;
using System.Configuration;
using System.Data;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.IO;
using System.Web.Services;
using System.Threading;
using System.Timers;
using System.Web;
using edu.jhu.Cas.Servers;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using net.ivoa.data;
using net.ivoa.VOTable;
using System.Security.Principal;
/**
 * This is the main webservive interface for submiting and controlling jobs
  $Id: CasJobs.asmx.cs,v 1.97 2008/05/14 05:52:26 nli Exp $
  $evision: 1.7 $
  $Date: 2008/05/14 05:52:26 $
  */
namespace edu.jhu.Cas.Jobs {
    [WebService(Namespace = "http://Jobs.Cas.jhu.edu", Name = "CasJobs", Description = "An interface for performing basic operations on a jobsDB $Name: v3_5_18 $ $Revision: 1.97 $")]
    public class CasJobs : System.Web.Services.WebService {
        private long serviceID = ID.SERVICE_ID;
        public const string Revision = "$Name: v3_5_18 $ $Revision: 1.97 $";

        public static long CreateJob(long wsId, string qry,
            string modified_query, string target, string taskName,
            int estimate, int autoComplete, string outputType, int type) {
            return CreateJob(wsId, qry, modified_query, target, taskName,
                estimate, autoComplete, outputType, "", type);
        }
        //** this is refd as a ws in CasJobsAdmin. don't make this one a web service
        public static long CreateJob(long wsId, string qry,
            string modified_query, string target, string taskName,
            int estimate, int autoComplete, string outputType) {
            return CreateJob(wsId, qry, modified_query, target, taskName,
                estimate, autoComplete, outputType, "", 0);
        }
        public static long CreateJob(long wsId, string qry,
           string modified_query, string target, string taskName,
           int estimate, int autoComplete, string outputType, string args) {
            return CreateJob(wsId, qry, modified_query, target, taskName,
            estimate, autoComplete, outputType, args, 0);
        }
        /*
         * estimate must be a real queue!
         */
        public static long CreateJob(long wsId, string qry,
            string modified_query, string target, string taskName,
            int estimate, int autoComplete, string outputType, string args, int type) {
            if (qry == null) throw new CasJobsException("Query cannot be null");
            if (modified_query == null) throw new CasJobsException("Modified Query cannot be null");
            if (qry.Trim(new char[4] { ' ', '\t', '\r', '\n' }).Length < 5) throw new CasJobsException("Query string is too short.");
            if (modified_query.Trim(new char[4] { ' ', '\t', '\r', '\n' }).Length < 5) throw new CasJobsException("Modified query string is too short.");
            if (taskName != null && taskName.IndexOf(SYSTEM.TASKNAME) == -1) {
               /* try {
                    foreach (string s in modified_query.Split(FilterPower.QUERY_SEPERATOR))
                        SqlFactory.select("SET PARSEONLY ON \n" + s);
                }
                catch (Exception e) { if (e.Message.IndexOf("group") == -1 && e.Message.IndexOf("recognized") == -1) throw; }*/
            }
            CasServers cs = new CasServers();
            if (cs.IsInLowestQueue(wsId, estimate, target)) {
                if (qry.ToUpper().IndexOf("INTO") == -1
                    && target.ToUpper().CompareTo("MYDB") != 0 && qry.ToUpper().IndexOf("EXEC") == -1 && qry.ToUpper().Trim().StartsWith("select"))
                    throw new Exception("Queries submitted to longer queues must be directed to mydb");
            }
            string ip = " ";
            try {
                ip = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];
            }
            catch { }

            //estimate = cs.ClosestQueue(ID.SERVICE_ID, wsId, target, estimate).Queue;//ensure that estimate is always an existing value (should be handled earlier)

            JobPub job = new JobPub();
            job.SetValue(JobPub.WEB_SERVICES_ID, wsId);
            job.SetValue(JobPub.QUERY, qry);
            job.SetValue(JobPub.MODIFIED_QUERY, modified_query);
            job.SetValue(JobPub.TASK_NAME, taskName);
            job.SetValue(JobPub.TARGET, target);
            job.SetValue(JobPub.ESTIMATE, estimate);
            job.SetValue(JobPub.AUTO_COMPLETE, autoComplete);
            job.SetValue(JobPub.OUT_TYPE, outputType);
            job.SetValue(JobPub.IP, ip);
            job.SetValue(JobPub.PARAMS, args);
            job.SetValue(JobPub.TYPE, type);

            string[] queries = modified_query.Split(FilterPower.QUERY_SEPERATOR);
            string createdTable = null;
            foreach (string s in queries) {
                string tmp = FilterPower.GetSelectIntoTable(s);
                if (tmp != null) {
                    createdTable = tmp.Split('.')[2];//yes, its only assigning the first one found.
                    break;
                }
            }
            if (createdTable == null) {
                StringCollection sc = FilterPower.GetCreatedTables(qry);
                if (sc.Count > 0)//again, just the first one
                    createdTable = sc[0];
            }
            Object o = SqlFactory.scalar(job, job.CreateJobStatement);
            long jobid = long.Parse(o.ToString());
            if (createdTable != null) {
                (new TableJobs()).PreAssignTable(wsId, jobid, createdTable);
            }
            return jobid;
        }
        public static long CreateJob(long wsId, string qry,
            string modified_query, string target, string taskName,
            int estimate, int autoComplete) {
            return CreateJob(wsId, qry, modified_query, target, taskName,
                estimate, autoComplete, "QUERY");
        }
        [WebMethod(Description = "Returns error-as-string if syntax error is found, null if no errors.")]
        public string CheckSyntax(long wsId, string qry, string context) {
            string rst = null;
            try {
                SubmitShortJob(wsId, "SET PARSEONLY ON " + qry.Replace("go","  "), context, SYSTEM.TASKNAME + " SYNTAX", 0);
            }
            catch (Exception e) {
                if (e.Message.IndexOf("group") == -1 && e.Message.IndexOf("recognized") == -1) rst = e.Message;
            }
            return rst;
        }
        [WebMethod(Description = "Returns a sql-server plan based on target and qry.")]
        public DataSet GetPlan(long wsid, string target, string qry) {
            qry = FilterPower.PrepPlanQuery(qry);
            return SubmitShortJob(wsid, "set showplan_all on\ngo\n" + qry, target, SYSTEM.TASKNAME + " GetPlan", 0);
        }
        [WebMethod(Description = "Submits a short job.  Returns an exception if job takes too long, Dataset if job did not.  Autocomplete is currently disabled; the param does nothing.")]
        public object[][] SubmitShortJobObject(long wsid, string qry, string target, string taskname,
            int autocomplete) {
            DataSet ds = SubmitShortJob(wsid, qry, target, taskname, autocomplete);

            object[][] rst = new object[ds.Tables[0].Rows.Count][];

            for (int y = 0; y < rst.Length; y++) {
                DataRow row = ds.Tables[0].Rows[y];
                rst[y] = new object[ds.Tables[0].Columns.Count];
                for (int x = 0; x < ds.Tables[0].Columns.Count; x++)
                    rst[y][x] = ds.Tables[0].Rows[y][x];
            }
            return rst;
        }
        [WebMethod(Description = "Submits a short job.  Returns an exception if job takes too long, Dataset if job did not.  Autocomplete is currently disabled; the param does nothing.")]
        public VOTABLE SubmitShortJobVOTABLE(long wsid, string qry, string target, string taskname,
            int autocomplete) {
            DataSet ds = SubmitShortJob(wsid, qry, target, taskname, autocomplete);
            DataWrapper dw = WrapperFactory.GetDataWrapper(ds);
            return VOTableWrapper.Wrapper2VOTable(dw);
        }
        public Server[] getVirtualHostAndMyDBServer(long wsid, string target, int queue) {
            CasUsers cUsers = new CasUsers();
            cUsers.CreateMyDB(wsid);//does nothing if already exists
            Server virtualHost;
            Server mydbServer = cUsers.MyDBServerObject(wsid);
            if (target.ToUpper().IndexOf("MYDB") > -1)
                virtualHost = mydbServer;
            else {
                virtualHost = (new CasServers()).ClosestQueue(ID.SERVICE_ID, wsid, target, queue);
            }
            if (virtualHost == null || virtualHost.ConnectionString == null ||
                virtualHost.ConnectionString.Length < 2)
                throw new Exception(
                    "Error retrieving host info.");
            mydbServer.Queue = virtualHost.Queue;//server queue is dominant
            return new Server[2] { virtualHost, mydbServer };
        }
        [WebMethod(Description = "Submits a short job.  Returns an exception if job takes too long, Dataset if job did not.  Autocomplete is currently disabled; the param does nothing.")]
        public DataSet SubmitShortJob(long wsid, string qry, string target, string taskname,
            int autocomplete) {
            int actualEstimate = 1;
            Server[] servers = getVirtualHostAndMyDBServer(wsid, target, actualEstimate);
            string mQuery = GetModifiedQuery(wsid, qry, target, actualEstimate, out actualEstimate);
            long jobNum = CreateJob(wsid, qry, mQuery, target, taskname,
                actualEstimate, autocomplete);
            QueryObject qObject = null;
            Job job = null;
            try {
                job = FindJob(wsid, "jobid = " + jobNum)[0];
                string ip = "0.0.0.0";
                try { ip = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"]; }
                catch { }
                qObject = new DRStatement(job, servers[1], servers[0], true, ip);
            }
            catch (Exception e) {
                ToughJobs.FailJob(0, wsid, jobNum, e.Message + "\n" + e.StackTrace, "");//really not expecting failures here, so include the stacktrace
                throw;
            }
            return RunJob(job, servers[1], qObject);//this method will fail its own jobs
        }
        public DataSet RunJob(Job job, Server mydbServer, QueryObject qObject) {
            CasJobsAdmin cja = new CasJobsAdmin();
            cja.StartJob(job.WebServicesID, job.JobID, qObject.VirtualHost.Name, job.Created_Table);
            try {
                qObject.Run();
            }
            catch { }//intentionally raises exception if forcefully cancelled
            if (qObject.Error == null) {
                long rows = qObject.Rows;
                ToughJobs.FinishedJob(0, job.WebServicesID,
                    job.JobID, "Query Complete", rows, job.Created_Table);
                return qObject.dataSet;
            } else {
                ToughJobs.FailJob(0, job.WebServicesID, job.JobID, qObject.Error.Message, "");
                throw new Exception(qObject.Error.Message, qObject.Error);
            }
        }
        public long SubmitNonQueryJob(long wsid, string qry,
            string target, string taskname, int estimate,
            int autocomplete, string outputType) {
            return SubmitNonQueryJob(wsid, qry, target, taskname, estimate, autocomplete, outputType, "");
        }
        public long SubmitNonQueryJob(long wsid, string qry,
            string target, string taskname, int estimate,
            int autocomplete, string outputType, string args) {
            //this is a high-queue only method.  bump up the estimate past the lowest queue
            Server[] min2 = (new CasServers()).GetTwoLowestQueues(wsid, target);
            if (estimate <= min2[0].Queue) estimate = min2[1].Queue;
            bool isQuery = outputType.ToLower().CompareTo("query") == 0;
            long rst = -1;

            string mQuery = GetModifiedQuery(wsid, qry, target, estimate, out estimate);
            rst = CreateJob(wsid, qry, mQuery, target, taskname,
                estimate, autocomplete, outputType, args);
            return rst;
        }
        [WebMethod(Description = "Creates new job entry and schedules a query for later completion.   Returns the jobid of the created job.  This method is only necessary for long jobs. Autocomplete is disabled (param does nothing)")]
        public long SubmitJob(long wsid, string qry, string target,
            string taskname, int estimate, int autocomplete) {
            return SubmitNonQueryJob(wsid, qry, target, taskname, estimate,
                autocomplete, "QUERY");
        }
        [WebMethod(Description = "Submits a heartbeat job to JobsService")]
        public long Heartbeat(long wsid, string target, int queue) {
            (new CasUsers()).HasPrivilege(wsid, PRIVS.SIGHT);
            return CreateJob(wsid, "this is not a query", "this is not a modified query", target, SYSTEM.TASKNAME + " lubdub", queue, 0, "HEARTBEAT");
        }
        [WebMethod(Description = "Schedules a job for cancellation. Only works for non-short queue jobs.")]
        public void CancelJob(long wsId, long jobId) {
            try {
                CasJobsAdmin cAdmin = new CasJobsAdmin();
                cAdmin.IsOwnerOrSight(wsId, jobId);
            }
            catch {
                throw new Exception("You do not have permission to cancel this query.");
            }
            JobPub job = new JobPub();
            job.addJobId(jobId);
            job.SetValue(JobPub.STATUS, QUERY_STATUS.CANCEL_PENDING);
            SqlFactory.insert(job, JobPub.CancelStatement);
        }
        [WebMethod(Description = "Returns a job's current status.  This is an int, where 0=ready,1=started,2=cancelling,3=cancelled,4=failed,5=finished")]
        public int StatusJob(long wsId, long jobId) {
            JobPub job = new JobPub();
            job.addJobId(jobId);
            job.SetValue(JobPub.WEB_SERVICES_ID, wsId);
            Object o = SqlFactory.scalar(job, JobPub.StatusJobStatement);
            return int.Parse(o.ToString());
        }
        [WebMethod(Description = "Searches a usr's jobs with parameters given in predicate.  Predicate is of where clause form, ex. 'taskname=\"some_name\" and rows=some_number")]
        public DataSet FindJobDataSet(long wsId, string predicate) {
            CasUsers users = new CasUsers();
            string qry = "Select * from jobs where ";
            bool seer = false;
            try {
                users.HasPrivilege(wsId, PRIVS.SIGHT);
                seer = true;
            } catch {
                try {
                    users.HasPrivilege(wsId, PRIVS.ADMIN);
                    seer = true;
                } catch {
                    qry += " webservicesid = " + wsId;
                }
            }
            JobPub job = new JobPub();
            if (predicate.Length > 1 && !seer)
                qry += " and ";
            qry += predicate;
            DataSet dataSet = SqlFactory.select(job, qry);
            return dataSet;
        }
        [WebMethod(Description = "A join of jobs and status, where j is jobs and s is status.  setSize is the number of jobs to return.  Position is a multiplier on setSize, ie top 20, or next 20, etc.  User can append select and/or where.  justCount set to true returns a dataset with the total # of jobs for the specified conditions.  Set size is how many jobs to return, position is the nth set of the specified size.  So if you had 10 jobs, setSize was 2 and position was 1, it would return jobs  3&4.  maxChars is the maximum # of characters to return for any single textfield.  Setting this to -1 will return everything.")]
        public DataSet FindJobWithStatusDataSet(long wsId, int setSize, int position,
            string select, string where, bool justCount, int maxChars) {
            /**
             * This is a USER method; it does not return jobs with taskname of __SYSTEM__
             */
            JobPub job = new JobPub();
            job.SetValue(JobPub.WEB_SERVICES_ID, wsId);
            if (select == null || select.Length <= 1)
                select = "*";
            if (where.Length > 1)
                where = " and " + where;
            string qry = justCount ? "select count(*) " : "select top " + setSize + " " + select;
            string sysClause = " and j.taskName not like '%" + SYSTEM.TASKNAME + "%'";
            string sysClause2 = " and jj.taskName not like '%" + SYSTEM.TASKNAME + "%'";
            qry += " from jobs j, status s " +
                "where webservicesid = " + wsId +
                " and j.status = s.status " + where +
                sysClause;

            qry = justCount ? qry : qry + " and timesubmit not in(select top " + (setSize * position) + " timesubmit " +
                " from jobs jj where jj.webservicesid = " + wsId + " " + where + sysClause2;
            qry = justCount ? qry : qry + " order by timesubmit desc)order by timesubmit desc";
            string max = maxChars > -1 ? "SET TEXTSIZE " + maxChars + "\n" : "";
            DataSet dataSet = SqlFactory.select(job, max + qry);
            return dataSet;
        }
        [WebMethod(Description = "Same as FindJobDataSet, but returns in Job format")]
        public Job[] FindJob(long wsId, string predicate) {
            DataSet ds = FindJobDataSet(wsId, predicate);
            Job[] jobs = DataConverter.dataSet2Job(ds);
            return jobs;
        }
        [WebMethod(Description = "List available targets and queues.")]
        public Server[] ListServers(long wsid) {
            CasServers servf = new CasServers();
            Server[] servs = servf.FindServers(ID.SERVICE_ID, wsid);

            for (int i = 0; i < servs.Length; i++) {
                servs[i].User = null;
                servs[i].Pass = null;
            }
            return servs;
        }
        [WebMethod(Description = "Return an html representation of the job status.")]
        public string getStatusHTML(long wsid, int status) {
            JobPub job = new JobPub();
            job.SetValue(JobPub.STATUS, status);
            string qry = job.StatusHTMLStatment;
            string stat = (string)SqlFactory.scalar(job, qry);
            return stat;
        }
        private string GetModifiedQuery(long wsid, string qry, string target, int estimate, out int actualestimate) {
            CasUsers cUsers = new CasUsers();
            cUsers.CreateMyDB(wsid);
            //string ip = "0.0.0.0";
            //try {ip = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];}catch{}
            Server mydb = cUsers.MyDBServerObject(wsid);
            string mydb_h_c_u = "[" + mydb.Host + "].mydb_" + wsid + "." + mydb.User + ".";
            return FilterPower.QueryToQueries(wsid, mydb_h_c_u, target, estimate, qry, out actualestimate);
        }

        [WebMethod(Description = "Lists available output downloads")]
        public Job[] GetOutputDownloadsAsJobs(long wsid) {
            return DataConverter.dataSet2Job(GetOutputDownloads(wsid));
        }
        public DataSet GetOutputDownloads(long wsid) {
            JobPub job = new JobPub();
            job.SetValue(JobPub.WEB_SERVICES_ID, wsid);
            return SqlFactory.select(job, JobPub.GetOutputDownloadsStatement);
        }
        [WebMethod(Description = "Lists pending output")]
        public Job[] GetPendingOutputAsJobs(long wsid) {
            return DataConverter.dataSet2Job(GetPendingOutput(wsid));
        }
        public DataSet GetPendingOutput(long wsid) {
            JobPub job = new JobPub();
            job.SetValue(JobPub.WEB_SERVICES_ID, wsid);
            return SqlFactory.select(job, JobPub.GetPendingOutputStatement);
        }
        [WebMethod(Description = "Lists failed output jobs")]
        public Job[] GetFailedOutputAsJobs(long wsid) {
            return DataConverter.dataSet2Job(GetFailedOutput(wsid));
        }
        public DataSet GetFailedOutput(long wsid) {
            JobPub job = new JobPub();
            job.SetValue(JobPub.WEB_SERVICES_ID, wsid);
            return SqlFactory.select(job, JobPub.GetFailedOutputStatement);
        }
    }
}