using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Services;

using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Users;
using com.referentia.ps.psdatafilelib;
using net.ivoa.data;

namespace edu.jhu.Cas.Services {
    [WebService(Namespace = "http://Services.Cas.jhu.edu", Name = "Jobs", Description = "Provides basic job management functionality.")]
    public class Jobs : System.Web.Services.WebService {
        CasJobs cj = new CasJobs();
        CasUsers cu = new CasUsers();

        [WebMethod(Description = @"
Executes a synchronous query in the shortest queue of a given context.<br/>
Returns job results on job success, myriad of exceptions otherwise.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account the query will be run under<br/>
string pw : Password of said account<br/>
string qry : Query to run<br/>
string context : Context of job<br/>
string taskname : Optional identifier of job<br/>
bool isSystem : System jobs are not visible in the web UI and require a flag with GetJobs().<br/>
<br/>
<b>Output:</b><br/>
string : A typed-csv format string containing the results of the query.<br/>
Column types are embedded in the column headers.  For example, <br/>
<p>'[ra]:Float,[dec]:Float'</p>
describes two columns, named 'ra' and 'dec', both floating points.
        ")]
        public string ExecuteQuickJob(long wsid, string pw, string qry, string context, string taskname, bool isSystem) {
            Users.Authenticate(wsid, pw);
            if (isSystem) taskname = SYSTEM.TASKNAME + taskname;
            DataSet ds = cj.SubmitShortJob(wsid, qry, context, taskname, 0);
            if (ds.Tables.Count < 1)
                return "Query had no output.";
            PSWriter psw = new PSWriter();
            StringWriter sw = new StringWriter();
            foreach (DataTable dt in ds.Tables)
                psw.WriteDataTable(dt, sw);
            return sw.ToString();
        }
        [WebMethod(Description = @"
Submits an asynchronous query to a queue longer than the shortest queue of a given context.<br/>
Returns a JobID on successful submission, exceptions otherwise.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account the query will be run under.<br/>
string pw : Password of said account<br/>
string qry : Query to run<br/>
string context : Context of job<br/>
string taskname : Optional identifier of job.<br/>
int estimate : Job will be run in the queue with the time limit nearest to 'estimate'.  Values are in minutes.<br/>
<br/>
<b>Output:</b><br/>
long : A unique identifier for the newly submitted job
")]
        public long SubmitJob(long wsid, string pw, string qry, string context, string taskname, int estimate) {
            Users.Authenticate(wsid, pw);
            return cj.SubmitJob(wsid, qry, context, taskname, estimate, 0);
        }
        [WebMethod(Description = @"
Schedules a non-quick job for cancellation.  Has no effect on quick jobs.<br/>
Users may only cancel jobs they own, otherwise an exception is thrown.<br/>
This method does nothing and returns nothing if jobid is not an actual job.<br/>
Otherwise, this method returns once the status of the specified job has changed to 'Canceling' (int = 2).<br/>
Note that a job is not completely cancelled until the status of that job is 'Cancelled' (or 'Failed'); until then it still consumes a queue slot.<br/>
Transition from 'Canceling' to 'Cancelled' is an internal operation and is not controlled though web services.<br/>
This method returns nothing on success.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account the cancellation will be run under.<br/>
string pw : Password of said account<br/>
long jobId : Identifier of the job to cancel<br/>
<br/>
<b>Output:</b><br/>
Void
")]
        public void CancelJob(long wsId, string pw, long jobId) {
            Users.Authenticate(wsId, pw);
            cj.CancelJob(wsId, jobId);
        }
        [WebMethod(Description = @"
Returns an int that represents the current status of a job.<br/>
Possible values and their meanings are shown below.<br/>
<br/>
&nbsp;0 = ready<br/>
&nbsp;1 = started<br/>
&nbsp;2 = canceling<br/>
&nbsp;3 = cancelled<br/> 
&nbsp;4 = failed<br/>
&nbsp;5 = finished<br/>
<br/>
'ready' is an idle state assigned to jobs created, but not yet processed.<br/>
'started' and 'canceling' are active states, describing jobs that are currently processing<br/>
'cancelled', 'failed' and 'finished' are final states, indicating a job's execution has terminated.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account the status will be run under.<br/>
string pw : Password of said account<br/>
long jobId : Identifier of the job whose status will be retrieved.<br/>
<br/>
<b>Output:</b><br/>
int : A number representing the current status of the requested job.  See above for what those numbers means.
")]
        public int GetJobStatus(long wsId, string pw, long jobId) {
            Users.Authenticate(wsId, pw);
            return cj.StatusJob(wsId, jobId);
        }
        [WebMethod(Description = @"
Returns an array of CJJob objects relevant to the conditions parameter.<br/>
GetJobs() will only return jobs created by the owner_wsid parameter,<br/>
unless owner_wsid has the 'admin' privilege, in which case all jobs are returnable.<br/>
The 'conditions' parameter is a specifically formatted string describing which jobs should be retrieved.<br/>
At the top level, this parameter is a list of keys, separated by ';'.<br/>
Each key set is described by a string key followed by ':' followed by a '|' separated list of conditions.<br/>
Each condition may have one of the following possible formats:<br/>
<br/>
VALUE  (equality) <br/>
VALUE, (equal or greater to VALUE)<br/>
,VALUE (less than or equal to VALUE)<br/>
V1,V2  (between V1 and V2 (inclusive))<br/>
<br/>
String and DateTime values should not be quoted and may not contain any special characters (:;,|).<br/>
DateTime values can be any format that .net will <a href=" + "\"http://msdn2.microsoft.com/en-us/library/az4se3k1.aspx\"" + @">parse</a>, so long as they do not contain any of the above special characters.<br/>
<br/>
The jobs returned are determined by the intersection of the keys, given the union of their conditions<br/>
<br/>
Example 1:<br/>
jobid : 12345;<br/>
Jobs with a jobid equal to 12345.<br/>
<br/>
Example 2:<br/>
jobid : 123|321|132;<br/>
Jobs with a jobid equal to 123 or 321 or 132.<br/>
<br/>
Example 3:<br/>
jobid : 123|321; status : 5<br/>
Jobs with a status of 5 and a jobid of 123 of 321.<br/>
<br/>
Example 4:<br/>
jobid : 123,|,122<br/>
Returns jobs with id greater or equal to 123 or less than or equal 122 (all of a users jobs).<br/>
<br/>
Example 5:<br/>
TimeEnd : 2008-04-5,<br/>
All jobs that ended after April 5, 2008.<br/>
<br/>
The following are valid keys:<br/>
long JobID : Unique identifier of job<br/>
DateTime TimeSubmit : When the job was submitted<br/>
DateTime TimeStart : When the job was started<br/>
DateTime TimeEnd : When the job completed/cancelled/failed<br/>
int Status : Current status of the job.  See the GetJobStatus() description for possible Status values.<br/>
int Queue : The queue the job ran in.<br/>
string TaskName : The user submitted descriptor for the job.  This can be null.<br/>
string Error : Error message for this job, if any<br/>
string Query : The query submitted for this job<br/>
string Context : The context of this job<br/>
string Type : The type of this job.  See GetJobTypes for a listing of possible values.<br/>
long WSID : The WebServicesID of the owner of this job.  If owner_wsid does not have the 'admin' privilege, then this has no effect.<br/>
<br/>
<b>Parameters:</b><br/>
long owner_wsid : ID of owner of jobs<br/>
string owner_pw : Password of said owner.<br/>
string conditions : A formatted string describing the conditions for the search.  See above for details on this.<br/>
bool includeSystem : Determines whether to return system jobs<br/>
<br/>
<b>Output:</b><br/>
CJJob[] : An array of all jobs matching the input conditions.<br/>
<br/>
A CJJob object contains:<br/>
long JobID : Unique identifier of job<br/>
long Rows : Number of rows this job modified, if applicable<br/>
WebServicesID : Identifier of user that submitted this job<br/>
DateTime TimeSubmit : When the job was submitted<br/>
DateTime TimeStart : When the job was started<br/>
DateTime TimeEnd : When the job completed/cancelled/failed<br/>
int Status : Current status of the job.  See the GetJobStatus() description for possible Status values.<br/>
int Queue : The queue the job ran in.<br/>
string TaskName : The user submitted descriptor for the job.  This can be null.<br/>
string OutputLoc : The URL of the output.  This field is only non-null for completed jobs where OutputType != Query<br/>
string Error : Error message for this job, if any<br/>
string Query : The query submitted for this job<br/>
string Context : The context of this job<br/>
string Type : The type of this job.  See GetJobTypes for a listing of possible values.<br/>
")]
        public CJJob[] GetJobs(long owner_wsid, string owner_pw, string conditions, bool includeSystem) {
            Users.Authenticate(owner_wsid, owner_pw);
            bool isAdmin = true;//many set out...
            try {
                cu.HasPrivilege(owner_wsid, PRIVS.ADMIN);
            } catch {
                isAdmin = false;//but few returned
            }
            if (conditions == null) conditions = "";
            conditions = conditions.Trim();
            bool preConditionAdded = false;
            string qry = @"
select jobid,rows,webservicesid,timesubmit,timestart,timeend,status,estimate as queue,
    taskname,outputloc,message as error, query, estimate as context, outputtype as type
from jobs
where ";
            if (!isAdmin) {
                qry = qry + "(webservicesid = " + owner_wsid + @") ";
                preConditionAdded = true;
            }
            if (preConditionAdded) qry = qry + " AND ";
            if (!includeSystem) {
                qry = qry + " (taskname not like '%" + SYSTEM.TASKNAME + "%') ";
                preConditionAdded = true;
            }
            string conSQL = ConditionFactory.JobConditions2SQL(conditions);
            if (conSQL != "" && preConditionAdded) qry = qry + " AND ";
            qry = qry + conSQL;
            return DataConverter.dataTable2CJJob(SqlFactory.select(qry).Tables[0]);
        }
        [WebMethod(Description = @"
Returns an array of CJQueue objects that summarize, for a particular user, their available contexts and the respective timeouts of.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account of whose contexts will be retrieved.<br/>
string pw : Password of said account<br/>
<br/>
<b>Output:</b><br/>
CJQueue[] : An array of CJQueue objects representing all available queues for the input user.<br/>
<br/>
A CJQueue object contains:<br/>
string Context : Name of the context<br/>
int Timeout : The maximum time a job is allowed to run.  The 'estimate' parameter in SubmitJob maps to the closest Timeout of a given context.<br/>
")]
        public CJQueue[] GetQueues(long wsid, string pw) {
            Users.Authenticate(wsid, pw);
            Server[] tmp = cj.ListServers(wsid);
            ArrayList al = new ArrayList();
            StringCollection keys = new StringCollection();
            foreach (Server s in cj.ListServers(wsid)) {//Servers may contain context/queue duplicate.  Make sure they don't show up.
                string key = (s.Target + s.Queue.ToString()).GetHashCode().ToString();
                if (keys.Contains(key)) continue;
                keys.Add(key);
                al.Add(s);
            }
            CJQueue[] rst = new CJQueue[al.Count];
            for (int i = 0; i < al.Count; i++) {
                Server s = (Server)al[i];
                rst[i] = new CJQueue(s.Target, s.Queue);
            }
            return rst;
        }
        const string jtq = @"
select outputtype as type, description
from [output]
where visible > 0 or outputtype = 'QUERY'
";
        [WebMethod(Description = @"
Returns an array of CJType objects that summarize, for a particular user, all available job types.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of the account of whose types will be retrieved.<br/>
string pw : Password of said account<br/>
<br/>
<b>Output:</b><br/>
CJType[] : An array representing all available job types for a user.<br/>
<br/>
A CJType object contains:<br/>
string Type : Name of the type<br/>
string Description : Description of this type<br/>
")]
        public CJType[] GetJobTypes(long wsid, string pw) {
            Users.Authenticate(wsid, pw);
            return DataConverter.dataTable2CJType(SqlFactory.select(jtq).Tables[0]);
        }
        [WebMethod(Description = @"
Submits a table extraction job.<br/>
On success, returns the jobid of the newly created job.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of account<br/>
string pw : Password of said account<br/>
string tableName : Name of mydb table to extract.<br/>
string type : describes type of output to create.  Valid types are all types returned from GetJobTypes(), excluding 'QUERY'<br/>
<br/>
<b>Output:</b><br/>
long : A unique identifier (jobid) of the newly created job.
")]
        public long SubmitExtractJob(long wsid, string pw, string tableName, string type) {
            Users.Authenticate(wsid, pw);
            ConditionFactory.ValidateString(tableName);
            string s = type.ToLower().Trim();
            bool match = false;
            foreach (CJType cjt in GetJobTypes(wsid, pw)) {
                if (cjt.Type.ToLower().Trim().CompareTo(s) == 0) {
                    match = true;
                    break;
                }
            }
            if (!match || s.CompareTo("query") == 0)
                throw new CasJobsException("'" + type + "' is not a valid type");
            return (new TableJobs()).RequestFileOutput(wsid, tableName, type, "MyDB", "");
        }
        [WebMethod(Description = @"
Loads data into a table in mydb. <br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of account<br/>
string pw : Password of said account<br/>
string tableName : Name of mydb table into which data will be loaded.<br/>
string data: ASCII encoded CSV data.<br/>
bool tableExists: If true, expects 'tableName' to exists and tries to load data into said table using the schema to determine types.<br/>
If false, creates a new table and tries to guess an appropriate schema.<br/>
<br/>
<b>Output:</b><br/>
Void
")]
        public void UploadData(long wsid, string pw, string tableName, string data, bool tableExists) {
            Users.Authenticate(wsid,pw);
            string cstring = (new CasUsers()).MyDBServerObject(wsid).ConnectionString;
            MemoryStream ms = new MemoryStream(System.Text.ASCIIEncoding.ASCII.GetBytes(data));
            CSVDataReader csvr = new CSVDataReader(ms, 1000);

            SqlCommand com = new SqlCommand();
            com.Connection = new SqlConnection(cstring);
            com.Connection.Open();

            SqlBulkCopy sbc = new SqlBulkCopy(com.Connection);
            sbc.BulkCopyTimeout = 0;
            sbc.BatchSize = 10000;
            sbc.DestinationTableName = tableName;

            try {
                if (!tableExists) {
                    com.CommandText = csvr.GetCreateTableQry(tableName);
                    com.ExecuteNonQuery();
                }
                sbc.WriteToServer(csvr);
            } finally {
                com.Connection.Close();
                com.Connection.Dispose();
                sbc.Close();
            }
        }
        [WebMethod(Description = @"
Suspends or wakes a service component.<br/>
Account must have the admin priv.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of admin account<br/>
string pw : Password of said account<br/>
string serviceName : Name of service that will have its state modified.  See /qhealth.aspx for a listing of valid service names.<br/>
string state: New state of specified service.  Values may be 'SUSPENDING' or 'WAKING'<br/>
<br/>
<b>Output:</b><br/>
Void
")]
        public void SetServiceState(long wsid, string pw, string serviceName, string state) {
            Users.Authenticate(wsid, pw);
            (new CasUsers()).HasPrivilege(wsid, PRIVS.ADMIN);
            CJobsDBState cjs = (CJobsDBState)Enum.Parse(typeof(CJobsDBState), state);
            if (!(cjs == CJobsDBState.SUSPENDING || cjs == CJobsDBState.WAKING))
                throw new CasJobsException("This method only accepts " + CJobsDBState.SUSPENDING + " and " + CJobsDBState.WAKING + " as valid states");
            ConditionFactory.ValidateString(serviceName);
            SqlFactory.insert("update servicestatus set state = '"+state+"' where [name] = '"+serviceName+"'");
        }
        [WebMethod(Description = @"
Gets the state of the specified service component.<br/>
Account must have the admin priv.<br/>
<br/>
<b>Parameters:</b><br/>
long wsid : WebServicesID of admin account<br/>
string pw : Password of said account<br/>
string serviceName : Name of service that will have its state modified.  See /qhealth.aspx for a listing of valid service names.<br/>
<br/>
<b>Output:</b><br/>
string : The state of the specified service<br/>
<br/>
Possible states include:<br/>
RUNNING = Service is functioning normally.<br/>
SUSPENDING = Service will be suspended on next cycle.<br/>
SUSPENDED = Service is suspended.  Continues to cycle, but no longer performs any other actions.<br/>
WAKING = Service will be RUNNING on next cycle.<br/>
")]
        public string GetServiceState(long wsid, string pw, string serviceName) {
            Users.Authenticate(wsid, pw);
            (new CasUsers()).HasPrivilege(wsid, PRIVS.ADMIN);
            ConditionFactory.ValidateString(serviceName);
            return SqlFactory.scalar("select state from servicestatus where [name] = '"+serviceName+"'") as string;
        }
    }

}