using System;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Xml.Serialization;
using System.Text.RegularExpressions;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Jobs;
using net.ivoa;
using net.ivoa.VOTable;
namespace edu.jhu.Cas.Jobs {  
	/// <summary>
	/// TableJobs class provides XML Web Services for extracting tables from mydb
	/// It also contains some of the logic for finding group tables to display to users
	/// and for finding the query which created the table - although that sometimes does not work.
	/// <pre>
	/// Current version:
	/// ================
	/// $Id: TableJobs.asmx.cs 136828 2012-07-30 20:40:13Z nli $
	/// $Revision: 136828 $
	/// $Date: 2012-07-30 16:40:13 -0400 (Mon, 30 Jul 2012) $
	/// </pre>
	/// </summary>
	[WebService(Namespace="Jobs.Cas.jhu.edu",
		 Description="This is an <b>XML Web Services</b> interface to the <b>MYDB table extraction</b> (CAS)."
		 +"<br> It provides access to MyDb data and returns the result in different formats."
		 +"<br> Send comments and suggestions to <b>William O'Mullane</b> -- woullan[at]pha.jhu.edu")]
	public class TableJobs : System.Web.Services.WebService {
		
		public TableJobs(){}
		[WebMethod(Description="Return list of available Output Types & descriotios 2xn array"
			 )]
		public DataSet ListQuickOutputTypes() {	
			JobUsers js = new JobUsers();
			DataSet ds = SqlFactory.select(js,"select Outputtype,Description from Output where visible >= "+SYSTEM.QUICK_VISIBLE);
			return  ds;
		}
		public DataSet ListLongOutputTypes() {	
			   JobUsers js = new JobUsers();
			DataSet ds = SqlFactory.select(js,"select Outputtype,Description from Output where visible >= "+SYSTEM.LONG_VISIBLE);
			return  ds;
		}
		
        [WebMethod(Description = "Return list of group visible tables")]
        public Table[] ListGroupTables(long wsid) {

            JobGroups job = new JobGroups();
            job.SetValue(JobGroups.WEB_SERVICES_ID, wsid);
            return DataConverter.dataSet2Table(SqlFactory.select(job, JobGroups.ListGroupTablesStatement));
        }
        public Table[] GetTablesByGroup(long wsid, long gid) {
            string qry = @"
select gt.tablename,u.mydbname,u.userid,u.webservicesid,g.name as groupname
from grouptables gt, groupmembers gm,users u,groups g
where gm.webservicesid = "+wsid+@"
and gm.status = 2 
and gm.gid = gt.gid 
and g.gid = gt.gid
  and gt.webservicesid = u.webservicesid --get lender user info
  and g.gid = "+gid+@"
union
select gt.tablename, u.mydbname, u.userid, u.webservicesid, g.name as groupname
from grouptables gt, users u,groups g
where 
g.gid = "+gid+@"
and g.gid = gt.gid
and gt.webservicesid = u.webservicesid --get lender user info
and g.gid = -1
";
            return DataConverter.dataSet2Table(SqlFactory.select(qry));
        }
		public ArrayList GetGroupTablesList(long wsid) {
            return new ArrayList(GetGroupTables(wsid));
		}
        public string[] GetGroupTables(long wsid) {
            Table[] tables = ListGroupTables(wsid);
            string[] rst = new string[tables.Length];
            for (int x = 0; x < rst.Length; x++)
                rst[x] = tables[x].GroupName + "." + tables[x].UserID.Trim() + "." + tables[x].Name.Trim();
            return rst;
        }
		/*
		 * returns an all-uppercase list of columns that have indices on them
		 * as well as the name of the index
		 */
		public StringDictionary ListIndices(long wsid, string tableName){
			StringDictionary sc = new StringDictionary();
			string qry = "sp_helpindex '"+tableName+"'";
			DataSet ds = (new CasJobs()).
				SubmitShortJob(wsid,qry,"MYDB",SYSTEM.TASKNAME+" listing indices",0);
			foreach(DataRow dr in ds.Tables[0].Rows)
				try{
					sc.Add(dr["index_keys"].ToString().ToUpper(),dr["index_name"].ToString());
				}catch(ArgumentException ae){ae=ae;continue;}
			return sc;
		}
		public Table[] ListPublishedTables(long wsid) {
			string publishedQuery = SYSTEM.QUERY_PREFIX+"select tableName from groupTables where webservicesid = "+wsid;
			DataSet ds = SqlFactory.select(publishedQuery);
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetPublishedTablesList(long wsid) {
			Table[] tables = ListPublishedTables(wsid);
			return Table2List(tables);
		}
		
        public const string PendingTablesQry = @"
select j.created_Table as tablename from jobs j, (
	select Created_Table as TableName, max(timestart) as timestart from jobs 
	where webservicesid = @WSID 
	and Created_Table is not null
	and Created_Table <> ''
    and timeend is null
	group by created_table
) b
where j.timestart = b.timestart
and status = 1
and Created_Table is not null
and Created_Table <> ''";
        public static string[] tParams = new string[1] { "@WSID" };
        public static SqlDbType[] tTypes = new SqlDbType[1] { SqlDbType.BigInt };
        public static int[] tSizes = new int[1] { 64 };
		public Table[] ListPendingTables(long wsid) {
			DataSet ds = SqlFactory.select_param(PendingTablesQry,tParams,tTypes,tSizes,new object[1]{wsid});
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetPendingTablesList(long wsid) {
			Table[] tables = ListPendingTables(wsid);
			return Table2List(tables);
		}
        public const string failedTablesQry = @"
select j.created_table as tablename, j.status
from jobs j
where j.jobid in (
    select max(jobid) as jobid from jobs
    where webservicesid = @WSID
      and Created_Table is not null
      and Created_Table <> ''
    group by created_table
)
and j.status = 4"
;
		public Table[] ListFailedTables(long wsid) {
            DataSet ds = SqlFactory.select_param(failedTablesQry,tParams,tTypes,tSizes,new object[1]{wsid});
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetFailedTablesList(long wsid) {
			Table[] tables = ListFailedTables(wsid);
			return Table2List(tables);
		}
		public void ImportTable(long wsid, string tableName, string localFilePath) {
			StreamReader reader = new StreamReader(localFilePath);
		}

		[WebMethod(Description="Request  file to be put in download area</b>"
			 +"<br><b>Input:</b> TableName (string)"
			 +"<br><b>Input:</b> qry (string) - always select * from tablename"
			 +"<br><b>Input:</b> outputType (string) - get list from listOutputTypes"
			 +"<br><b>Output:</b> JobId (long)"
			 +"<br><b>Target:</b> currently, target must always be mydb"
			 )]
		public long QueryFileOutput(long wsid, string qry, string outputType, string target,string args,string taskName) {
			if(target == null)throw new Exception("You must specify a target.");
			target = target.ToUpper();
			if(target.IndexOf("MYDB") == -1)
				throw new Exception("Target must be a MyDB.");
			if(outputType == null || outputType.Length < 2)
				throw new Exception("You must specify an outputType.");
			
			CasJobs cj = new CasJobs();
			long jobid = cj.SubmitNonQueryJob(wsid,qry,target,taskName,500,0,outputType,args);
			return jobid;
		}
        /*
		/// <summary>
		/// for given tbale name try to find the query which CREATED it. That could be
		/// a create statement or an into. Also wht if anohter user has such a table
		/// what if its a group table .. we want to see the source if it is a group table
		/// as well but not a table of the same name owned by someone else !
		/// So this assumes you already found out the table owners wsid befor calling it.
        /// 
		/// </summary>
        /// <!--lol, classic wil style.  wtf is he talking about-->
		/// <param name="wsid"> the table owners wsid </param>
		/// <param name="tableName"> the table</param>
		/// <returns></returns>
		public string GetSourceQuery(long wsid,string tableName)
		{
			string qry2 = @"
select t.q from (
	select query q, created_table ct, timeend te  from jobs where
	webservicesid = "+wsid+@"
	and created_table <> '' 
) t where t.ct = '"+tableName+@"' 
or t.q like '%create%table "+tableName+@"'
order by t.te desc ";
			string qry1 = "sp_helptext ["+tableName+"]";
			try{
				DataSet ds = (new CasJobs()).SubmitShortJob(wsid,qry1,"MYDB",SYSTEM.TASKNAME+" table source check",0);
				string rst = "";
				foreach(DataRow r in ds.Tables[0].Rows)
					rst+=r[0].ToString();
				return rst;
			}catch{
                string s = SqlFactory.scalar(qry2) as string;
                return s;               
			}			
		}*/
		
		public long RequestFileOutput(long wsid, string tableName, string outputType, string target,string args){
			return RequestFileOutput(wsid,tableName,outputType,target,args,"select * from "+tableName);
		}
		public long RequestFileOutput(long wsid, string tableName, string outputType, string target,string args,string qry){
			return QueryFileOutput(wsid,qry,outputType,target,args,tableName);
		}
        /**
         * remove all casjobs references to a table
         */
        public void PurgeTable(long wsid, string tableName) {
            string[] tokens = tableName.Split('.');
            if (tokens.Length != 3) {
                SqlFactory.select_param(attemptDepublishQry, adparamNames, adparamTypes, adSizes, new object[2] { wsid, tableName });
            }
            else {
                User u = (new CasUsers()).FindUser(ID.SERVICE_ID, " userid = '" + tokens[1] + "'")[0];
                SqlFactory.nonselect_param(attemptDepublishQry, adparamNames, adparamTypes, adSizes, new object[2] { u.WebServicesID, tokens[2] });
            }
            CasNotes cnotes = new CasNotes();
            cnotes.PurgeObjNotes(wsid, "Mydb", tableName, CasDBObjType.TABLE);
        }
        public const string attemptDepublishQry = @"
delete from grouptables
where webservicesid = @WSID
and tableName = @TNAME

update jobs
set created_table = null
where webservicesid = @WSID
and created_table = @TNAME
";
        public static string[] adparamNames = new string[2] { "@WSID", "@TNAME" };
        public static SqlDbType[] adparamTypes = new SqlDbType[2] { SqlDbType.BigInt, SqlDbType.VarChar };
        public static int[] adSizes = new int[2] { 64, 100 };


        public void AssignTable(long wsid, long jobid, string tableName){
            SqlFactory.select("update jobs set Created_Table = '" + tableName + "' where jobid = " + jobid);
		}
        //called by jobs that have started, but not yet completed
        //only assign the table if there is not one already
        public void PreAssignTable(long wsid, long jobid, string tableName) {
            AssignTable(wsid, jobid, tableName);
        }
        public void RollBackTable(long wsid, long jobid, string tableName) {
            SqlFactory.select("update jobs set Created_Table = null where jobid = " + jobid);
        }   
		
		[WebMethod(Description="List tables in a users mydb")]
		public string[] MyDBTables(long wsid){
			return (new MyDBJobs()).Tables(wsid,"MYDB");
		}
		[WebMethod(Description="as dataset,csv,votable,fits?"
			 +"<br><b>Input:</b> WebServicesId to locat MyDB (long)")]
		public string QuickFileOutput(long wsid, string tableName, string format){
            throw new Exception("disabled");
			CasUsers cUsers = new CasUsers();
			edu.jhu.Cas.Jobs.CasJobs cJobs = new edu.jhu.Cas.Jobs.CasJobs();
			tableName = tableName.ToLower().Trim();
			if(!tableName.StartsWith("mydb") && !tableName.StartsWith("group"))
				tableName = "mydb."+tableName;
			long count = long.Parse(
				cJobs.SubmitShortJob(wsid,
					"select count(*) from "+tableName,"MYDB",
				SYSTEM.TASKNAME+" Extract count for "+tableName,-1).Tables[0].Rows[0][0].ToString());
			
			if(count > SYSTEM.QUICK_EXTRACT_ROW_LIMIT)
				throw new Exception("Table "+tableName+" has "+count+" rows, which exceeds quick extract limit of "+SYSTEM.QUICK_EXTRACT_ROW_LIMIT);
			DataSet ds = cJobs.SubmitShortJob(wsid,"select * from "+tableName,"MYDB",
				SYSTEM.TASKNAME+" extract job for "+tableName,-1);
			CasOutput cOut = new CasOutput();
			string path = cOut.path(format);
			string url = cOut.url(format);
			string name = tableName+"_"+wsid.ToString();
			
			//try{
			switch(format.ToLower()){
				case "csv":
					name+=".csv";
				    File.Delete(path+name);
					DataConverter.dataSet2CSVFile(ds, path+name);
					break;
				case "dataset":
					name+=".xml";
					File.Delete(path+name);
					FileStream fs = new FileStream(path+name,System.IO.FileMode.Create);
					XmlSerializer serializer = new XmlSerializer(typeof(DataSet));
					serializer.Serialize(fs,ds);
					fs.Close();
					break;
				case "votable":
					name+=".xml";
					File.Delete(path+name);
					VOTABLE votable = VOTableUtil.DataSet2VOTable(ds);
					FileStream fs2 = new FileStream(path+name,System.IO.FileMode.Create);
					XmlSerializer serializer2 = new XmlSerializer(typeof(VOTABLE));
					serializer2.Serialize(fs2,votable);
					fs2.Close();
					break;
				case "fits":
					break;
				default:
					throw new Exception(format+" format is not supported.");
			}
			return url+name;
		}
		private ArrayList Table2List(Table[] tables){
			string[] rst = new string[tables.Length];
			for(int x=0;x<rst.Length; x++)
				rst[x] = tables[x].Name;
			ArrayList list = new ArrayList();
			list.AddRange(rst);
			return list;
		}
		/// <summary>
		/// Revision from CVS
		/// </summary>
		public static string Revision {
			get {
				return "$Name $ $Revision: 136828 $";
			}
		}
	}
}
