using System;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Web;
using edu.jhu.Cas.Servers;
using net.ivoa.VOTable;
using net.ivoa.data;
using System.Data.SqlClient;

namespace edu.jhu.Cas.Web {
	/// <summary>
	/// This is the page displayed in the right frame of the MYDB screen.
	/// It has all the logic for when the user clicks on a table function etc. to display
	/// details about the object.
	///  great deal of setup is done in MYDBIndex.aspx.cs
	/// </summary>
	public class MyDBContent : System.Web.UI.Page {
        protected global::System.Web.UI.WebControls.CheckBox useCustomQuery;
        protected global::System.Web.UI.WebControls.TextBox customQuery;
        protected TextBox scidrivePath;

		protected string lmessage = "", groupInfo, user, outputType, groupTableDescription = "", 
            objSource = "Source not available";
		protected string[] space = new string[] { "NA", "NA" }, tableSpace;
		protected string ObjName;
		protected CasDBObjType ObjType;
		protected string[,] outputTypes = null;
		protected bool loggedin, published, group, canGetImages, pending, failed, isPlot;
		protected long wsid;
		protected MyDBJobs jobs;
		protected TableJobs et;
		protected HtmlInputFile upForm;
		protected System.Web.UI.WebControls.Button Button1;
		protected System.Web.UI.WebControls.Button uploadButton;
		protected System.Web.UI.WebControls.Button Button2;
		protected System.Web.UI.WebControls.Button publishButton;
		protected System.Web.UI.WebControls.Button dropButton;
		protected System.Web.UI.WebControls.Button publishDescriptionSetButton;
		protected System.Web.UI.WebControls.Button NewNoteButton;
		protected ListBox pgroupbox, upgroupbox;
		protected DropDownList extractDDL;
		protected TextBox publishDescriptionBox, renameBox,
			nresult, nradius, NewNoteBox;
		protected bool isSmallTable;
		protected string context, contextDescription = "";
		protected DropDownList targetDDL;
		protected bool isMyDB;
		protected Group[] groups, ptgroups;
		protected string type;
		protected long jobid;
		private void Page_Load(object sender, System.EventArgs e) { }

        protected void DDLEventHandler(object sender, System.EventArgs e)
        {
            if (extractDDL.Text == "SciDrive")
            {
                scidrivePath.Visible = true;
            }
            else
            {
                scidrivePath.Visible = false;
            }
        }

		protected override void OnInit(System.EventArgs ea) {
			WebUtils.CheckLoggedIn(Session, Response, Request, true);
			try {
                lmessage = Request.Params["lmessage"];
                wsid = (long)Session["USER"];
				ObjName = Request.Params["ObjName"];
				if (Request.Params["ObjType"] != null)
					ObjType = (CasDBObjType)Enum.Parse(typeof(CasDBObjType), Request.Params["ObjType"], true);
				if (Request.Params["plot"] != null) { Plot(); }
				 et = new TableJobs();
				context = Request.Params["context"];
                
                if (ObjName == null && context != null && context.ToLower() != "mydb")
                {
                    try
                    {
                        contextDescription = GetContextDescription(wsid, context);
                        if (contextDescription == null) throw new Exception();
                    }
                    catch (Exception)
                    {
                        lmessage = "No description available.";
                    }
                }

				if (context == null) context = "MyDB";
				isMyDB = context.ToUpper().CompareTo("MYDB") == 0;
				jobs = new MyDBJobs();
				type = Request.Params["type"];
				if (ObjName == null) {
					space = jobs.Space(wsid);
					space[0] = String.Format("{0:#,###} kB", int.Parse(space[0].Split(' ')[0]));
					space[1] = String.Format("{0:#,###} kB", int.Parse(space[1].Split(' ')[0]));
					User u = (new CasUsers()).FindUser(edu.jhu.Cas.Util.ID.SERVICE_ID, " webservicesid = " + wsid)[0];
					user = u.Name;
					return;
				}
				if (ObjType == CasDBObjType.TABLE || ObjType == CasDBObjType.VIEW) {
                    customQuery.Text = "SELECT * FROM " + ObjName;
					group = type == "group";
					published = type == "published";
					pending = type == "pending";
					failed = type == "bad_table";
					if (isMyDB) loadDDL();
					if (ObjType == CasDBObjType.VIEW) {
						objSource = jobs.GetObjSource(wsid, ObjName, context);
					}
					try {
						if (!group) {
							Job j = (new CasJobs()).FindJob(wsid, " created_table = '" + ObjName + "' order by timeend desc")[0];
							jobid = j.JobID;
						}
						else {
							string[] tokens = ObjName.Split('.');
							Job j = DataConverter.dataSet2Job(SqlFactory.select("select * from jobs where created_table = '" + tokens[tokens.Length - 1] + "' order by timeend desc"))[0];
							jobid = j.JobID;
						}
					}
					catch {
						jobid = -1;
					}
					if (!group && isMyDB) {
						CasGroups cGroups = new CasGroups();
						groups = cGroups.FindGroups(wsid, false);
						ptgroups = cGroups.TablePublishedTo(wsid, ObjName);
						bool dopublish;
						foreach (Group g in groups) {
							dopublish = true;
							foreach (Group g1 in ptgroups) {
								if (g1.GID == g.GID)
									dopublish = false;
							}
							if (dopublish)
								pgroupbox.Items.Add(new ListItem(g.Name, g.GID.ToString()));
						}
						if (published) {
							foreach (Group g in ptgroups)
								upgroupbox.Items.Add(new ListItem(g.Name, g.GID.ToString()));
							upgroupbox.Rows = ptgroups.Length + 1 > 7 ? 7 : ptgroups.Length + 1;
							upgroupbox.SelectedIndex = -1;
						}
						pgroupbox.Rows = groups.Length + 1 > 7 ? 7 : groups.Length + 1;
						pgroupbox.SelectedIndex = -1;

					}
					RenameColumns();
					isSmallTable = false;
					//init table spaces/preview
					if (group) {
						//might not work if mydbs are not linked
						string[] chunks = ObjName.Split('.');
						/**
						* bit of a hole.  Users cannot anothers mydb unless they're asking about a shared table
							 * however, if they already know it, they could call TableSpace without restraint!
							 */
						string theirDBName = (new FilterFactory()).GetTheirMyDBName(wsid, chunks[1], chunks[2]);
						string userid = chunks[1];
						string tabname = chunks[2];
						CasUsers cu = new CasUsers();
						// need an admin id for this
						long wsidOther = cu.GetWebServiceId(edu.jhu.Cas.Util.ID.SERVICE_ID, userid);
						try {
							tableSpace = jobs.TableSpace(wsidOther, tabname, context);
						}
						catch {
							lmessage = "This table no longer exists and has been removed from publication.";
							(new TableJobs()).PurgeTable(wsid, ObjName);
							return;
						}
						tableSpace[0] = String.Format("{0:#,###} rows", int.Parse(tableSpace[0].Split(' ')[0]));
						if (tableSpace[0] == " rows") tableSpace[0] = "0 rows";
						tableSpace[1] = String.Format("{0:#,###} kB", int.Parse(tableSpace[1].Split(' ')[0]));
						if (tableSpace[1] == " kB") tableSpace[1] = "0 kB";
						isSmallTable = false;

						groupInfo = chunks[1];
						groupTableDescription = (new CasGroups()).GetPublishedTableDescription(chunks[1], chunks[2]);
						if (groupTableDescription == null || groupTableDescription == "")
							groupTableDescription = "No description available.";
					}
					else {
						try {
							tableSpace = jobs.TableSpace(wsid, ObjName, context);
							tableSpace[0] = String.Format("{0:#,###} rows", int.Parse(tableSpace[0].Split(' ')[0]));
							if (tableSpace[0] == " rows") tableSpace[0] = "0 rows";
							tableSpace[1] = String.Format("{0:#,###} kB", int.Parse(tableSpace[1].Split(' ')[0]));
							if (tableSpace[1] == " kB") tableSpace[1] = "0 kB";
							//isSmallTable = long.Parse(tableSpace[0].Replace("rows","")) <= SYSTEM.QUICK_EXTRACT_ROW_LIMIT;
						}
						catch {
							tableSpace = new string[2] { "NA", "NA" };
						}
					}

					if (published)
						publishDescriptionBox.Text = (new CasGroups()).GetPublishedTableDescription(wsid, ObjName);
					LoadOutputTypes();
				}
				else if (ObjType == CasDBObjType.PROCEDURE || ObjType == CasDBObjType.FUNCTION) {
                    objSource = jobs.GetObjSource(wsid, ObjName, context);					
				}
			}
			catch (Exception e) {
				lmessage = e.Message;
			}
		}
		protected void RenameColumns() {
			bool found = false;
			string qry = "";
			if (Request.Params["renamecols"] == null) return;
			for (int x = 0; x < Request.Params.Count; x++) {
				if (Request.Params["COL" + x] != null) {
					found = true;
					qry += "EXEC sp_rename '" + ObjName + ".[" + Request.Params["COL" + x + "OLD"] + "]', '" + Request.Params["COL" + x] + "', 'COLUMN'\n";
				}
			}
			if (found) {
				string s = "Columns in " + ObjName + " have been renamed.";
				try {
					(new CasJobs()).SubmitShortJob(wsid, qry, "MYDB", SYSTEM.TASKNAME + " renaming columns", 0);
				}
				catch (Exception e) {
					s = e.Message;
				}
				Response.Redirect("mydb.aspx?lmessage=" + s);
			}
		}
		private string getSqlType(Type dt) {
			try {
				return VOTableUtil.GetSqlDBType(dt).ToString();
			}
			catch { return "NA"; }
		}
		private string args = "";
		protected void Plot() {
			TableJobs tj = new TableJobs();
			string target = "MYDB", extractTableName = ObjName;
			args = ObjName + "," + Request.Params["xcol"] + "," + Request.Params["ycol"];
			string outputType = "plot";
			long jobid = tj.RequestFileOutput(wsid, ObjName, outputType, target, args, Request.Params["qry"]);
			Response.Redirect("output.aspx");
		}
		protected void Extract(Object server, EventArgs E) {
			TableJobs tj = new TableJobs();
			string target = "MYDB", extractTableName = ObjName;
			string outputType = isPlot ? "plot" : extractDDL.SelectedItem.Value;
            if (outputType.ToLower() == "scidrive")
            {
                args = Request.Cookies["Keystone"]["token"]+","+scidrivePath.Text;
            }
            string query = "SELECT * FROM "+extractTableName;
            if (useCustomQuery.Checked && !string.IsNullOrWhiteSpace(customQuery.Text))
            {
                query = customQuery.Text;
            }
			long jobid = tj.RequestFileOutput(wsid, extractTableName, outputType, target, args, query);
			if (jobid <= 0) throw new Exception("No job was created - has this been implemented ?");
			Response.Redirect("output.aspx");
		}
		protected void SetPublishDescription(object source, EventArgs E) {
			lmessage = "Descripton for " + ObjName + " set";
			CasGroups cGroups = new CasGroups();
			try {
				cGroups.SetPublishedTableDescription(wsid, ObjName, publishDescriptionBox.Text);
			}
			catch (Exception e) { lmessage = e.Message; }
			Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
		}
		protected void NewNote(object source, EventArgs E) {
			lmessage = "Note added";
			CasNotes cnotes = new CasNotes();
			try {
				cnotes.AddNote(wsid, context, ObjName, NewNoteBox.Text, ObjType);
			}
			catch (Exception e) { lmessage = e.Message; }
			Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
		}
		protected string GetNotesHTML() {
			if (ObjName == null) return "";
			CasNotes cnotes = new CasNotes();
			return cnotes.GetObjNotesHTML(wsid, context, ObjName, ObjType);
		}
		protected void Publish(object source, EventArgs E) {
			lmessage = ObjName + " was published.";
			CasGroups cGroups = new CasGroups();
			try {
				if (pgroupbox.GetSelectedIndices().Length == 0)
					throw new Exception("Please select at least one group to publish this table to.");
				foreach (int i in pgroupbox.GetSelectedIndices())
					cGroups.PublishTable(wsid, long.Parse(pgroupbox.Items[i].Value), ObjName);
			}
			catch (Exception e) { lmessage = e.Message; }
			Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
			Response.Write("<script>window.open('','','');");
		}
		protected void UnPublish(object source, EventArgs E) {
			lmessage = ObjName + " was unpublished.";
			CasGroups cGroups = new CasGroups();
			try {
				if (upgroupbox.GetSelectedIndices().Length == 0)
					throw new Exception("Please select at least one group to unpublish this table from.");
				foreach (int i in upgroupbox.GetSelectedIndices())
					cGroups.DePublishTable(wsid, long.Parse(upgroupbox.Items[i].Value), ObjName);
			}
			catch (Exception e) { lmessage = e.Message; }
			Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
		}
		protected void Rename(object source, EventArgs E) {
			lmessage = "";//tableName+" renamed to "+renameBox.Text.Trim();
			try {
				jobs.RenameTable(wsid, ObjName, renameBox.Text.Trim());
			}
			catch (Exception e) { lmessage = "Could not rename table."; }//e.Message;}
			Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
		}
		protected void Drop(object source, EventArgs E) {
			string args = null;
			switch (ObjType) {
				case CasDBObjType.FUNCTION: args = "functionName="; break;
				case CasDBObjType.PROCEDURE: args = "procedureName="; break;
				case CasDBObjType.VIEW:
				case CasDBObjType.TABLE: args = "tableName="; break;
				default:
					throw new CasJobsException("Cannot drop object: unknown object type");
			}
			args += ObjName;
			if (ObjType == CasDBObjType.VIEW) args += "&isview=true";
			Response.Redirect("DropTable.aspx?" + args);
		}
		protected void Edit(object source, EventArgs E) {
			Job j = new Job();
			j.WebServicesID = wsid;
            if(objSource != null)
				j.Query = objSource.Replace("CREATE", "ALTER");
			Session["JOB"] = j;
			j.TaskName = "MyTaskName";
			j.Target = "MyDB";
			Response.Redirect("submitjob.aspx?resubmit=true");
		}

		protected void FetchImages(object source, EventArgs E) {
			Response.Redirect("Images.aspx?tableName=" + ObjName);
		}

		private void LoadOutputTypes() {

			DataSet ds = isSmallTable ? et.ListQuickOutputTypes() : et.ListLongOutputTypes();
			int typeCount = ds.Tables[0].Rows.Count;

			for (int r = 0; r < typeCount; r++) {
				if (ds.Tables[0].Rows[r][1].ToString().IndexOf("Plot") > -1) continue;//handled by plot function
				extractDDL.Items.Add(new ListItem(ds.Tables[0].Rows[r][1].ToString(),
					ds.Tables[0].Rows[r][0].ToString()));
			}
		}
		protected void TableInfo(object source, EventArgs E) {
			Job j = (new CasJobs()).FindJob(wsid, " created_table = '" + ObjName + "' order by timeend desc")[0];
			Response.Redirect("jobdetails.aspx?id=" + j.JobID);
		}
		private void InitializeComponent() { }

		protected void NSearch(object source, EventArgs E) {
			CasJobs cJobs = new CasJobs();
			try {
				cJobs.SubmitShortJob(wsid, @"
				ALTER TABLE " + ObjName + @" ADD search_id int null
				"
					, "MyDB", SYSTEM.TASKNAME + " neighbors alter table", 0);

			}
			catch { }
			try {
				cJobs.SubmitShortJob(wsid, @"
				select top 0 * into " + nresult.Text + " from " + ObjName
					, "MyDB", SYSTEM.TASKNAME + " neighbors create table", 0);
			}
			catch {
				lmessage = "Could not create table " + nresult.Text.Trim() + ".  Does this table already exist?";
				Response.Redirect("MyDB.aspx?lmessage=" + lmessage);
			}
			string mcolbase = "matched_id";
			string mcol = "" + mcolbase;
			for (int x = 0; x < 10; x++) {
				try {
					cJobs.SubmitShortJob(wsid, @"
				ALTER TABLE " + nresult.Text + @" ADD " + mcol + @" bigint null
				"
						, "MyDB", SYSTEM.TASKNAME + " neighbors alter table", 0);
					break;
				}
				catch {
					mcol = mcolbase + "_" + x;
					continue;
				}
			}
			try {
				cJobs.SubmitShortJob(wsid, @"
				declare @id int;
				set @id=0
				update " + ObjName + @"
				set @id=@id+1,
				search_id = @id 
				"
					, "MyDB", SYSTEM.TASKNAME + " neighbors alter table", 0);
			}
			catch { }
			string thetable = ObjName.Trim();
			if (!thetable.StartsWith("group.")) {
				thetable = "mydb." + thetable;
			}
			long jobid = cJobs.SubmitJob(wsid, @"
CREATE TABLE #UPLOAD(
   up_ra FLOAT,
   up_dec FLOAT,
   up_id int
)
INSERT INTO #UPLOAD
SELECT RA AS UP_RA,DEC AS UP_DEC,search_id AS UP_ID
FROM " + thetable + @" 
CREATE TABLE #tmp (
			  up_id int,
			   objid bigint
)
INSERT INTO #tmp
EXEC spgetneighbors " + nradius.Text + @"
INSERT INTO mydb." + nresult.Text.Trim() + @"
select a.*,t.objid as " + mcol + @" from #tmp t, " + thetable + " a  where t.up_id = a.search_id ",
				targetDDL.SelectedItem.Text,
				"Neighbors of '" + ObjName + "', results are in '" + nresult.Text + "'", 500, 0);
			SqlFactory.insert("update jobs set created_table = '" + nresult.Text + "' where jobid = " + jobid);
			Response.Redirect("jobdetails.aspx?id=" + jobid);
		}
		private void loadDDL() {
			if (targetDDL.Items.Count < 2) {
				Server[] targets = (new CasServers()).FindTargets(edu.jhu.Cas.Util.ID.SERVICE_ID, wsid);
				foreach (Server s in targets) {
					if (s.Target.ToLower() != "mydb") // do not want mydb for neighbors
						targetDDL.Items.Add(s.Target);
				}
				for (int x = targetDDL.Items.Count - 1; x >= 0; x--)
					if (targetDDL.Items[x].Text.ToUpper().IndexOf("DR") > -1 &&
						targetDDL.Items[x].Text.ToUpper().IndexOf("EDR") == -1) {
						targetDDL.Items[x].Selected = true;
						break;
					}
			}
		}

        private string GetContextDescription(long wsid, string context)
        {
            string result = null;
            CasUsers cus = new CasUsers();
            string conStr = "";

            conStr = context.ToUpper().CompareTo("MYDB") == 0 ? cus.MyDBServerObject(wsid).ConnectionString :
                (new CasServers()).FindServerByTarget(edu.jhu.Cas.Util.ID.SERVICE_ID, wsid, context)[0].ConnectionString;


            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT comment FROM SiteConstants WHERE name = 'Description'";
                    result = (string)cmd.ExecuteScalar();
                }
            }

            return result;
        }
	}
}