using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;

namespace Gemini2TFS
{
	class Converter
	{
		static SqlDataAdapter issues;
		static SqlDataAdapter issueComments;
        
		static SqlCommand sqlIssuesCommand;
		static SqlCommand sqlIssueCommentsCommand;
		static DataSet ds;

        static string AttachmentsFolder = "C:\\Work\\QA\\Tools\\Gemini2TFS\\Attachments\\";

        //add your TFS project and area
        static string projectTfsName = "RadControls";
        static string area = "RadGrid";

        //Gemini project code
        static string projectGeminiCode = "RGRI";

		static void Main()
		{
			string server = GetServer();
			ArrayList itemFields = new ArrayList();

			try
			{
				// get the work item store from the TeamFoundationServer
				Console.WriteLine("Connecting to {0}...", server);
				TeamFoundationServer tfs = TeamFoundationServerFactory.GetServer(server);
				WorkItemStore store = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
                WorkItemTypeCollection workItemTypes = store.Projects[projectTfsName].WorkItemTypes;

                //sql connection to the database added in the projects folder
                using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Work\QA\Tools\Gemini2TFS\gemini.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"))
				{
				    sqlIssuesCommand = new System.Data.SqlClient.SqlCommand(
                            String.Format(@"SELECT * FROM issuesview WHERE projcode='{0}' and statusdesc <> N'Closed' AND (NOT (summary LIKE '%Prometheus%'))", projectGeminiCode),
							conn);

					sqlIssueCommentsCommand = new System.Data.SqlClient.SqlCommand(
							@"SELECT * FROM issuecomments WHERE issueid=@IssueID",
							conn);
					sqlIssueCommentsCommand.Parameters.Add("@IssueID", SqlDbType.Int);


					issues = new System.Data.SqlClient.SqlDataAdapter(sqlIssuesCommand);
					issueComments = new System.Data.SqlClient.SqlDataAdapter(sqlIssueCommentsCommand);

					ds = new DataSet();
					issues.Fill(ds, "issuesview");
					int i = 0;

					foreach (DataRow issue in ds.Tables["issuesview"].Rows)
					{
						i++;
                        String itemType = mapItemType(issue);
                        WorkItemType wit = workItemTypes[itemType];
						WorkItem workItem = new WorkItem(wit);

                        // Skip already entered Gemini issues
                        string wiqlQuery = String.Format("Select ID, Title from Issue where ([Work Item Type] = '{0}') and (Title = '{1}')", itemType, Convert.ToString(issue["summary"]).Replace("'", "''"));
						if (store.Query(wiqlQuery).Count > 0)
						{
							//workItem = store.Query(wiqlQuery)[0];
							//setComments(workItem, issue);
							//setDescription(workItem, issue);
							//workItem.Save();
							continue;
						}

						workItem.Title = Convert.ToString(issue["summary"]);
						setDescription(workItem, issue);
						setArea(workItem, issue);
						//setTriage(workItem, issue);
						setPriority(workItem, issue);
                        setAssignedTo(workItem, issue);
						setState(workItem, issue);
                        addAttachments(conn, workItem, issue);
						setComments(workItem, issue);
                        addLinks(store, conn, workItem, issue);

						if (workItem.IsValid())
						{
							workItem.Save();
						}
						else
						{
							ArrayList invalidFields = new ArrayList();

							foreach (Field field in workItem.Fields)
							{
								if (!field.IsValid)
								{
									invalidFields.Add(field);
									Console.WriteLine("Invalid Field '{0}': {1}", field.Name, field.Status.ToString());
									Console.WriteLine("Current Value: '{0}'", field.Value);
									Console.WriteLine();
								}
							}
							//Console.WriteLine("item {0} not converted", issue);
						}
					}
				}

			}
			catch (Exception ex)
			{
                Console.WriteLine("Exception caught: ", ex.Message);
			}
		}

        //mapping for the item types
        static String mapItemType(DataRow issue)
        {
            String geminiType = Convert.ToString(issue["typedesc"]);
            switch (geminiType)
            {
                case ("Bug"):
                    {
                        return "Bug";
                    }
                case ("New Feature"):
                    {
                        return "Feature Request";
                    }
                case ("Enhancement"):
                    {
                        return "Feature Request";
                    }
                case ("Task"):
                    {
                        return "Task";
                    }
                default:
                    {
                        return String.Empty;
                    }
            }
        }

		static void setComments(WorkItem item, DataRow issue)
		{
			sqlIssueCommentsCommand.Parameters["@IssueID"].Value = Convert.ToInt32(issue["IssueId"]);
			issueComments.Fill(ds, "issuecomments");

            //add comments in work item history
			foreach (DataRow issueComment in ds.Tables["issuecomments"].Rows)
			{
				DateTime dt = Convert.ToDateTime(issueComment["created"]);
				string user = Convert.ToString(issueComment["username"]);
				item.History =
						string.Format("On {0}, {1} commented :{2} {3}",
						dt.ToLongDateString(),
						user,
						Environment.NewLine,
						Convert.ToString(issueComment["comment"]));
				item.Save();
			}
			ds.Tables["issuecomments"].Clear();
		}

		static void setDescription(WorkItem item, DataRow issue)
		{
			string s = Convert.ToString(issue["longdesc"]);
            //format Gemini issue description
            //the code here helps formatting rad editor html used in our Gemini distribution
            s = Regex.Replace(s, @"<br\s*(/>|>)", Environment.NewLine);
            s = Regex.Replace(s, @"<li>", Environment.NewLine + "-");
            s = Regex.Replace(s, @"&nbsp;", " ");
            s = Regex.Replace(s, @"&amp;", "&");
            s = Regex.Replace(s, @"&lt;", "<");
            s = Regex.Replace(s, @"&gt;", ">");
            s = Regex.Replace(s, @"<(.|\n)*?>", string.Empty);

            //add "Gemini ID" and "Reported By" into description
            item.Description = String.Format("Gemini #{0}\r\n Reported by {1}\r\n", Convert.ToInt32(issue["issueid"]), Convert.ToString(issue["reporteddesc"])) + s;
		}

		static void setArea(WorkItem item, DataRow issue)
		{
			Dictionary<string,string> map = new Dictionary<string,string>();
            // Case sensiteve mapping from Gemini categories to Tfs areas

            map.Add("Core product",                          projectTfsName + "\\" + area);
            map.Add("Browser related",                       projectTfsName + "\\" + area + "\\Client-Side");
            map.Add("Property Builder, Design-Time support", projectTfsName + "\\" + area + "\\Design-Time");
            map.Add("Documentation",                         projectTfsName + "\\" + area + "\\Documentation");
            map.Add("Client-side API & Events",              projectTfsName + "\\" + area + "\\Client-Side");
            map.Add("Online examples",                       projectTfsName + "\\" + area + "\\Examples");
            map.Add("Skins",                                 projectTfsName + "\\" + area + "\\Skins");
            //add your custom mapping here

            item.AreaPath = map[Convert.ToString(issue["compname"])];
		}

		static void setAssignedTo(WorkItem item, DataRow issue)
		{
			Dictionary<string, string> map = new Dictionary<string, string>();
            map.Add("", "John Doe");
            map.Add("* Unassigned *	", "John Doe");
            //add your custom mapping here
			item.Fields["Assigned To"].Value = map[Convert.ToString(issue["assigneddesc"])];
		}

		static void setState(WorkItem item, DataRow issue)
		{
			Dictionary<string, string> map = new Dictionary<string, string>();
            //add your custom mapping here
			map.Add("Open",					"Active");
            map.Add("In Progress",			"Active");
			map.Add("For Verification",		"Resolved");
            //map.Add("Closed",				"Closed");
            map.Add("Reopened",				"Active");
            //map.Add("Require More Info",	"Active");
            map.Add("Postponed",			"Active");

			switch (map[Convert.ToString(issue["statusdesc"])])
			{
				case "Active":
                    item.Fields["State"].Value = "Active";
                    break;
				case "Resolved":
					item.Fields["State"].Value = "Active";
					item.Save();
					item.Fields["State"].Value = "Resolved";
					break;
                //we don't need closed issues into TFS, one may unmark the lines below
                //case "Closed":
                //    item.Fields["State"].Value = "Active";
                //    item.Save();
                //    item.Fields["State"].Value = "Resolved";
                //    item.Save();
                //    item.Fields["State"].Value = "Closed";
                //    break;
			}
		}

		static void setTriage(WorkItem item, DataRow issue)
		{
            //if you need some
		}

		static void setPriority(WorkItem item, DataRow issue)
		{
            //only bugs have priority in TFS
            if ("Bug".Equals(Convert.ToString(issue["typedesc"])))
            {
                //add a custom mapping here
                Dictionary<string, string> map = new Dictionary<string, string>();
                map.Add("Minor", "2");
                map.Add("Show Stopper", "1");
                map.Add("Major", "1");
                map.Add("Trivial", "3");
                item.Fields["Priority"].Value = Convert.ToInt32(map[Convert.ToString(issue["prioritydesc"])]);
            }
		}

		static void setActivatedBy(WorkItem item, DataRow issue)
		{
            //add your custom mapping here if needed

            //Dictionary<string, string> map = new Dictionary<string, string>();
            //map.Add("* Unassigned * ", "John Doe");
            //
            //item.Fields["Activated By"].Value = map[Convert.ToString(issue["reporteddesc"])];

            //or enter that as is
            item.Fields["Activated By"].Value = Convert.ToString(issue["reporteddesc"]);
		}

        // helper method to get the TFS server name being connected to.
        private static string GetServer()
        {
			string server = "http://vss:8082";
            if (server != null)
            {
                server = server.Trim();
            }

            while (server == null || server.Length == 0)
            {
                Console.WriteLine("Please enter your Team Foundation Server Name,\nor you can set it in TEAM_FOUNDATION_SERVER_NAME environment variable:");
                server = Console.ReadLine();
                server = server.Trim();
            }
            return server;
        }

        private static void addAttachments(SqlConnection conn, WorkItem item, DataRow issue)
        {
            string issueID = Convert.ToString(issue["IssueId"]);

            String selectQuery = "SELECT issueattachments.thefilename, issueattachments.contentlength, issueattachments.filecontent " +
                                 "FROM issueattachments INNER JOIN issuesview ON issuesview.issueid = issueattachments.issueid " +
                                 "WHERE (issuesview.statusdesc <> N'Closed') AND (issuesview.issueid = '" + issueID + "')";

            SqlCommand sqlIssueAttachmentsCommand = new SqlCommand(selectQuery, conn);
            SqlDataAdapter issueAttachmentsAdapter = new SqlDataAdapter(sqlIssueAttachmentsCommand);

            ds = new DataSet();
            issueAttachmentsAdapter.Fill(ds, "issueattachments");

            foreach (DataRow row in ds.Tables["issueattachments"].Rows)
            {
                string filePath = Convert.ToString(row["thefilename"]);
                string fileName = GetFileNameFromPath(filePath);
                int length = Convert.ToInt32(row["contentlength"]);
                byte[] content = (byte[])row["filecontent"];

                //create directory for this issue attachments
                String issueDirPath = AttachmentsFolder + projectGeminiCode + "\\" + issueID;
                DirectoryInfo issuedir = new DirectoryInfo(issueDirPath);
                if (!issuedir.Exists)
                {
                    issuedir.Create();
                }

                string path = issueDirPath + "\\" + fileName;

                // Create a file to hold the output.
                FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
                BinaryWriter bw = new BinaryWriter(fs);

                bw.Write(content);
                bw.Flush();

                // Close the output file.
                bw.Close();
                fs.Close();

                try
                {
                    //add attachment into work item
                    Attachment attachment = new Attachment(path, "");
                    item.Attachments.Add(attachment);
                    item.Save();
                }
                catch (Exception e)
                {
                    //log about failures
                    Console.WriteLine("Error adding attachment to WorkItem " + item.Id + ": " + e.Message);
                    continue;
                }
            }

            ds.Tables["issueattachments"].Clear();
        }

        private static void addLinks(WorkItemStore store, SqlConnection conn, WorkItem item, DataRow issue)
        {
            string issueID = Convert.ToString(issue["IssueId"]);

            String selectQuery = "SELECT     issueid AS OtherIssueID, summary AS Title " +
                                 "FROM         issuesview " +
                                 "WHERE     (issueid = " +
                                                      " (SELECT     issuelinks.otherissueid " +
                                                      "  FROM          issuelinks LEFT OUTER JOIN issuesview AS issuesview_1 ON issuelinks.issueid = issuesview_1.issueid " +
                                                      "  WHERE      (issuesview_1.issueid = '" + issueID + "')))";

            SqlCommand sqlIssueLinksCommand = new SqlCommand(selectQuery, conn);

            SqlDataAdapter issueLinksAdapter = new SqlDataAdapter(sqlIssueLinksCommand);

            ds = new DataSet();
            issueLinksAdapter.Fill(ds, "issuelinks");

            foreach (DataRow row in ds.Tables["issuelinks"].Rows)
            {
                string linkedIssueTitle = Convert.ToString(row["Title"]);

                //check if an item with such title is already added so that we can link both items
                string wiqlQuery = String.Format("Select ID, Title from Issue where (Title = '{0}')", linkedIssueTitle.Replace("'", "''"));
                if (store.Query(wiqlQuery).Count > 0)
                {
                    WorkItem relatedItem = store.Query(wiqlQuery)[0];

                    //create and add new related link
                    RelatedLink link = new RelatedLink(relatedItem.Id);
                    item.Links.Add(link);
                    item.Save();
                }
                else
                {
                    string linkedIssueID = Convert.ToString(row["OtherIssueID"]);

                    //at least we can add some description
                    item.Description = String.Format("Issue is linked to Gemini ID #{0}\r\n", linkedIssueID) + item.Description;
                }
            }
        }

        private static String GetFileNameFromPath(String filePath)
        {
            int index = filePath.LastIndexOf('\\');
            return filePath.Substring(index + 1);
        }
    }
}