//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Text;
using System.Text.RegularExpressions;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.Galleries.Components;
using CommunityServer.SqlDataProvider;

namespace CommunityServer.Data
{
	/// <summary>
	/// Helper class for generating dynamic SQL.
	/// </summary>
	public class SqlGenerator
	{
		private SqlGenerator()
		{
		}

		#region BuildMemberQuery

		public static string BuildMemberQuery(UserQuery query, string databaseOwner)
		{

			// Clean strings to prevent SQL Injection attacks
			query.SearchText = CleanSearchString(query.SearchText);
			query.SortClauseOverride = CleanSearchString(query.SortClauseOverride);


			StringBuilder sb = new StringBuilder();

			sb.Append("SET Transaction Isolation Level Read UNCOMMITTED ");

			// SELECT & FROM CLAUSES
			sb.AppendFormat(" SELECT P.cs_UserID From {0}.cs_vw_Users_FullUser P ",databaseOwner);


			// WHERE CLAUSE
			sb.AppendFormat(" where SettingsID = {0} ", ProviderHelper.Instance().GetSettingsID());

			if (query.Status != UserAccountStatus.All)
				sb.AppendFormat(" and cs_UserAccountStatus = {0} ", SQLHelper.GetSafeSqlInt((int)query.Status));

			if(!query.IncludeHiddenUsers)
				sb.Append(" and EnableDisplayInMemberList = 1 ");

			if (Globals.IsNullorEmpty(query.SearchText))
				query.SearchText = "%";
		
			if (query.SearchUsername && query.SearchEmail)
				sb.Append(" and ( UserName LIKE @SearchText OR Email LIKE @SearchText ) ");
			else if (query.SearchUsername)
				sb.Append(" and UserName LIKE @SearchText ");
			else if (query.SearchEmail)
				sb.Append(" and Email LIKE @SearchText ");
			else
				sb.Append(" and UserName LIKE @SearchText ");


			// Do we need to filter by a specific Role?
			if (query.RoleID != Guid.Empty)
				sb.AppendFormat(" and Exists(SELECT aUR.RoleId FROM {0}.aspnet_UsersInRoles aUR WHERE aUR.UserId = P.UserId AND aUR.RoleId = '{1}') ", databaseOwner, query.RoleID.ToString());

			// Dates
			if(query.HasJoinedDate)
				sb.AppendFormat(" and CreateDate {0} '{1}' ", DateComparerString(query.JoinedDateComparer), SQLHelper.GetSafeSqlDateTimeFormat(SQLHelper.GetSafeSqlDateTime(query.JoinedDate)));
			if(query.HasPostDate)
				sb.AppendFormat(" and LastActivityDate {0} '{1}' ", DateComparerString(query.LastPostDateComparer), SQLHelper.GetSafeSqlDateTimeFormat(SQLHelper.GetSafeSqlDateTime(query.LastPostDate)));


            // ORDER BY CLAUSE
			sb.Append(" ORDER BY ");
			if (!Globals.IsNullorEmpty(query.SortClauseOverride))
			{
				sb.AppendFormat(" {0} ", query.SortClauseOverride);
			}
			else
			{

				switch(query.SortBy)
				{
					case(SortUsersBy.Email):
					{
						sb.Append(" Email ");
						break;
					}
					case(SortUsersBy.JoinedDate):
					{
						sb.Append(" CreateDate ");
					break;
					}
					case(SortUsersBy.LastActiveDate):
					{
						sb.Append(" cs_LastActivity ");
						break;
					}
					case(SortUsersBy.Posts):
					{
						sb.Append(" TotalPosts ");
						break;
					}
					case(SortUsersBy.Username):
					{
						sb.Append(" UserName ");
						break;
					}
					case(SortUsersBy.Website):
					{
						sb.Append(" Email ");
						break;
					}
					default:
					{
						sb.Append(" UserName ");
						break;
					}
				}

				if(query.Order == SortOrder.Descending)
					sb.Append(" DESC ");

			}


			// Send back the query
			return sb.ToString();
		}

		protected static string DateComparerString(int dateComparer)
		{
			switch(dateComparer)
			{
				case -1:
				{
					return "<";
				}
				case 0:
				{
					return "=";
				}
				case 1:
				{
					return ">";
				}
				default:
				{
					return "=";
				}
			}
		}


		protected static string CleanSearchString(string searchString)
		{
			if (Globals.IsNullorEmpty(searchString))
				return null;

			// Do wild card replacements
			searchString = searchString.Replace("*", "%");

			// Strip any markup characters
			searchString = Transforms.StripHtmlXmlTags(searchString);

			// Remove known bad SQL characters
			searchString = Regex.Replace(searchString, "--|;|'|\"", " ", RegexOptions.Compiled | RegexOptions.Multiline);

			// Finally remove any extra spaces from the string
			searchString = Regex.Replace(searchString, " {1,}", " ", RegexOptions.IgnoreCase | RegexOptions.Compiled | RegexOptions.Multiline); 

			return searchString;

		}

		#endregion


	}
}
