using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.IO;
using System.Web;
using System.Web.Services;

using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Servers;
using SoftwareFX.ChartFX.Lite;
namespace edu.jhu.Cas.Jobs {
	public class CasProcedure{
		public string Name, Text;
		public CasParameter[] Params;
	}
	public class CasFunction{
		public string Name, Text;
		public CasParameter[] Params;
	}
	public class CasParameter{
		public string Name, Type;
	}
    public class CasTable {
        public string Name;
        public long Rows;
        public long Size;
    }
	public class MyDBJobs {
	
		[WebMethod(Description="Return list of table names in MyDB "
			 +"<br><b>Input:</b> WebServicesId to locat MyDB (long)")]
		public string[] Tables(long wsid) {
			return Tables(wsid, "MYDB","U");
		}
        public CasTable[] TableDetails(long wsid,string context) {
            CasUsers cus = new CasUsers();
            string conStr = "";
            try {
                conStr = context.ToUpper().CompareTo("MYDB") == 0 ? cus.MyDBServerObject(wsid).ConnectionString :
                    (new CasServers()).FindServerByTarget(ID.SERVICE_ID, wsid, context)[0].ConnectionString;
            }
            catch {
                throw new Exception("there: "+context);
            }
            ArrayList tmp = new ArrayList();
            using (SqlConnection conn = new SqlConnection(conStr)) {
                try {
                    conn.Open();
                    string xtra = context == "MyDB" ? " and so.uid <> 1 " : "";
                    SqlCommand cmd = new SqlCommand(@"
                    SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rowcnt),
    [Size] = CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(si.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')))/1024.)) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
    " + xtra + @"
GROUP BY 
    so.name 
", conn);
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read()) {
                        CasTable ct = new CasTable();
                        ct.Name = rd["TableName"].ToString();
                        ct.Rows = (long)rd["RowCount"];
                        ct.Size = (long)Math.Round(((decimal)rd["Size"]));
                        tmp.Add(ct);
                    }
                }
                finally {
                    if (conn != null) conn.Close();
                }
            }
            return (CasTable[])tmp.ToArray(typeof(CasTable));
        }
		public string[] Tables(long wsid,string target) {
			return Tables(wsid,target,"U");
		}
		public string[] Views(long wsid, string target){
			return Tables(wsid,target,"V");
		}
		public string[] Tables(long wsid, string target,string table_type){
			CasUsers cus = new CasUsers();
           
			//string conStr = cus.MyDBConnection(wsid);
			string conStr = target.ToUpper().CompareTo("MYDB") == 0?cus.MyDBServerObject(wsid).ConnectionString:
				(new CasServers()).FindServerByTarget(ID.SERVICE_ID,wsid,target)[0].ConnectionString;
			ArrayList al = new ArrayList();
            using (SqlConnection conn = new SqlConnection(conStr)) {
                try {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("select name from sysobjects where type='" + table_type + "' and category <> 2 order by name asc", conn);
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read()) {
                        al.Add(rd.GetString(0)); // tablename is the 3rd colum (zero based 2)
                    }
                }
                finally {
                    if (conn != null) conn.Close();
                }
            }
			return (string[])al.ToArray(typeof(string));
		}
		
		public string[] ListFunctions(long wsid,string target){
			DataSet ds = (new CasJobs()).SubmitShortJob(wsid,"select name from sysobjects where (type='fn' or type='tf') and category =0 order by name asc",
				target,SYSTEM.TASKNAME+" LIST FUNCTIONS",0);
			string[] rst = new string[ds.Tables[0].Rows.Count];
			for(int x=0;x<rst.Length;x++)
				rst[x] = ds.Tables[0].Rows[x][0].ToString();;
			return rst;
		}
        public string ViewSource(long wsid, string viewName, string context) {
            string qry = "exec sp_helptext " + viewName;
            DataSet ds = (new CasJobs()).SubmitShortJob(wsid,qry,context,SYSTEM.TASKNAME+" VIEW SOURCE ",0);
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            foreach (DataRow dr in ds.Tables[0].Rows)
                sb.Append(dr[0] as string);
            return sb.ToString();
        }
		public CasFunction FunctionInfo(long wsid, string functionName,string target){
			CasFunction f = new CasFunction();
			f.Name = functionName;
			string qry = "exec sp_helptext "+functionName+"\ngo\n"+
				"select parameter_name, data_type from INFORMATION_SCHEMA.PARAMETERS "+
				"where specific_name = '"+functionName+"'";
            DataSet info = (new CasJobs()).SubmitShortJob(wsid, qry, target, SYSTEM.TASKNAME + " FUNCTION SOURCE ", 0);
			f.Text = "";
			
			foreach(DataRow row in info.Tables[0].Rows)
				f.Text+=row[0].ToString();
			f.Params = new CasParameter[info.Tables[1].Rows.Count];
			for(int x=0;x<f.Params.Length;x++){
				CasParameter tmp = new CasParameter();
				tmp.Name = info.Tables[1].Rows[x][0].ToString();
				tmp.Type= info.Tables[1].Rows[x][1].ToString();
				f.Params[x] = tmp;
			}
			return f;
		}
		
		public string[] ListProcedures(long wsid,string target){
			DataSet ds = (new CasJobs()).SubmitShortJob(wsid,"select name from sysobjects where type='p' and category=0 order by name asc",
				target,SYSTEM.TASKNAME+" LIST FUNCTIONS",0);
			string[] rst = new string[ds.Tables[0].Rows.Count];
			for(int x=0;x<rst.Length;x++)
				rst[x] = ds.Tables[0].Rows[x][0].ToString();
			return rst;
		}
		public CasProcedure ProcedureInfo(long wsid, string procedureName,string target){
			CasProcedure p = new CasProcedure();
			p.Name = procedureName;
			string qry = "exec sp_helptext "+procedureName+"\n"+
				"select parameter_name, data_type from INFORMATION_SCHEMA.PARAMETERS "+
				"where specific_name = '"+procedureName+"'";
			string cstring = target.ToUpper().CompareTo("MYDB")==0?
				(new CasUsers()).MyDBServerObject(wsid).ConnectionString:
				(new CasServers()).FindServerByTarget(ID.SERVICE_ID,wsid,target)[0].ConnectionString;
			//DataSet info= (new CasJobs()).SubmitShortJob(wsid,qry,
			//	"MYDB",SYSTEM.TASKNAME+" PROCEDURE INFO",0);
			DataSet info = SqlFactory.userSelect(qry,cstring);
			p.Text = "";
			foreach(DataRow row in info.Tables[0].Rows)
				p.Text+=row[0].ToString();
			p.Params = new CasParameter[info.Tables[1].Rows.Count];
			for(int x=0;x<p.Params.Length;x++){
				CasParameter tmp = new CasParameter();
				tmp.Name = info.Tables[1].Rows[x][0].ToString();
				tmp.Type= info.Tables[1].Rows[x][1].ToString();
				p.Params[x] = tmp;
			}
			return p;
		}
		public static string ParamsToString(CasParameter[] p){
			string rst="";
			for(int x=0;x<p.Length;x++){
				if(p[x].Name == null || p[x].Name.Length == 0)p[x].Name="returns";
				rst+=p[x].Name+" "+p[x].Type+"\n";
			}
			return rst;
		}


        public void DeleteTable(long wsid, string tableName) {
            if (tableName.ToUpper().IndexOf("GROUP.") != -1)
                throw new Exception("You cannot delete tables you do not own.");

            (new CasJobs()).SubmitShortJob(wsid, "drop table [" + tableName + "]",
                "MYDB", SYSTEM.TASKNAME + " DELETE TABLE " + tableName.ToUpper(), 0);
            (new TableJobs()).PurgeTable(wsid, tableName);
        }
        public void DeleteTables(long wsid, string csvTables,char separator) {
            string[] tables = csvTables.Split(separator);
            string q1 = "";
            for (int i = 0; i < tables.Length; i++) {
                if (tables[i].ToUpper().IndexOf("GROUP.") != -1)
                    throw new Exception("You cannot delete tables you do not own.");
                q1 += "drop table [" + tables[i].Replace('\n',' ').Replace('\r',' ') + "]\ngo\n";
            }
            q1 = q1.Substring(0, q1.Length - 1);
            (new CasJobs()).SubmitShortJob(wsid, q1,
                    "MYDB",  " DELETE TABLES " +csvTables, 0);
            TableJobs et = new TableJobs();
            foreach (string s in tables) {
                et.PurgeTable(wsid, s);
            }            
        }
        public void RenameTable(long wsid, string tableName, string newName) {
			if(newName == null || newName.Length < 1)
				throw new Exception("Table name cannot be null.");
			//throw new Exception("exec sp_rename "+EditTableName(tableName)+",'"+newName+"'");
			(new CasJobs()).SubmitShortJob(wsid,"exec sp_rename "+EditTableName(tableName)+",'"+newName+"'",
				"MYDB",SYSTEM.TASKNAME+" RENAME TABLE "+tableName.ToUpper(),0);
			string qry = 
				" update Jobs "+
				" set Created_Table = '"+newName+"'"+
				" where webservicesid = "+wsid+
				" and Created_Table = '"+tableName+"'";
			SqlFactory.insert(qry);
		}
		public DataSet PreviewTable(long wsid, string tableName,string target){
			return PreviewTable(wsid,tableName,SYSTEM.PREVIEW_ROWS,target);
		}
		public DataSet PreviewTable(long wsid, string tableName,int rows,string target) {
            return (new CasJobs()).SubmitShortJob(wsid, "select top " + rows + " * from " + tableName,
                target, SYSTEM.TASKNAME+" Preview " + tableName, 0);
		}
		
		/**
		 * data size, max size
		 */
		public string[] Space(long wsid) {
            DataSet ds = (new CasJobs()).SubmitShortJob(wsid,"exec sp_spaceUsed","MyDB",
				SYSTEM.TASKNAME+" MYDB SPACE",0);
			if(ds == null)return new string[2]{"NA","NA"};
			string[] rst = new string[2];
            rst[0] = ds.Tables[ds.Tables.Count-1].Rows[1][1].ToString();
			User u = (new CasUsers()).FindUser(ID.SERVICE_ID, " webservicesid = "+wsid)[0];
			rst[1] = u.MyDBMaxSize.ToString()+"000 KB";
			return rst;
		}
		
		/**
		 * rows, data size
		 */
		public string[] TableSpace(long wsid, string tableName) {
			return TableSpace(wsid,tableName,(new CasUsers()).MyDBServerObject(wsid).ConnectionString);
		}
		/**
		 * target is like "USE target" use must be one word. This one isn't logged
		 */
		public string[] TableSpace(long wsid, string tableName,string target) {
			string qry = " exec sp_spaceUsed '"+tableName+"'";
			DataSet ds = (new CasJobs()).SubmitShortJob(wsid,"exec sp_spaceUsed ["+tableName+"]",target,
				SYSTEM.TASKNAME+" "+target+" TABLESPACE",0);
			string[] rst = new string[2];
			rst[0] = ds.Tables[0].Rows[0][1].ToString().Trim()+" rows";
			rst[1] = ds.Tables[0].Rows[0][2].ToString();
			return rst;
		}
		private string EditTableName(string tableName){
			tableName = tableName.ToLower();
			if(tableName.IndexOf("group.") == -1)
				return "["+tableName+"]";
			string[] chunks = tableName.Split('.');
			try{
				return chunks[0]+"."+chunks[1]+".["+chunks[2]+"]";
			}catch(Exception e){e=e;throw new Exception("Invalid group syntax");}
		}
	}
}