//------------------------------------------------------------------------------
//
// Copyright (c) 2002-2006 CodeSmith Tools, LLC.  All rights reserved.
// 
// The terms of use for this software are contained in the file
// named sourcelicense.txt, which can be found in the root of this distribution.
// By using this software in any fashion, you are agreeing to be bound by the
// terms of this license.
// 
// You must not remove this notice, or any other, from this software.
//
//------------------------------------------------------------------------------

using System;
using System.Text;
using System.ComponentModel;
using CodeSmith.Engine;
using System.Data;
using SchemaExplorer;

namespace CodeSmith.BaseTemplates
{
	/// <summary>
	/// Templates can be derived from this class to gain various helper methods that are useful when generating code from a SQL metadata source.
	/// </summary>
	public class SqlCodeTemplate : OutputFileCodeTemplate
	{
		/// <summary>
		/// Generates an assignment statement that adds a parameter to a ADO object for the given column.
		/// </summary>
		/// <param name="statementPrefix"></param>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetSqlParameterStatements(string statementPrefix, ColumnSchema column)
		{
			return GetSqlParameterStatements(statementPrefix, column, "sql");
		}
		
		/// <summary>
		/// Generates an assignment statement that adds a parameter to a ADO object for the given column.
		/// </summary>
		/// <param name="statementPrefix"></param>
		/// <param name="column"></param>
		/// <param name="sqlObjectName"></param>
		/// <returns></returns>
		public string GetSqlParameterStatements(string statementPrefix, ColumnSchema column, string sqlObjectName)
		{
			string statements = "\r\n" + statementPrefix + sqlObjectName + ".AddParameter(\"@" + column.Name + "\", SqlDbType." + GetSqlDbType(column) + ", this." + GetPropertyName(column) + GetSqlParameterExtraParams(statementPrefix, column);
	
			return statements.Substring(statementPrefix.Length + 2);
		}
		
		/// <summary>
		/// Returns the C# member variable name for a given identifier.
		/// </summary>
		/// <param name="value"></param>
		/// <returns></returns>
		public string GetMemberVariableName(string value)
		{
			string memberVariableName = "_" + StringUtility.ToCamelCase(value);
			
			return memberVariableName;
		}
		
		/// <summary>
		/// Generates any extra parameters that are needed for the ADO parameter statement.
		/// </summary>
		/// <param name="statementPrefix"></param>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetSqlParameterExtraParams(string statementPrefix, ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.String:
				case DbType.StringFixedLength:
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				{
					if (column.NativeType != "text" && column.NativeType != "ntext")
					{
						return ", " + column.Size + ");";
					}
					else
					{
						return ");";
					}
				}
				case DbType.Decimal:
				{
					return ");\r\n" + statementPrefix + "prm.Scale = " + column.Scale + ";\r\n" + statementPrefix + "prm.Precision = " + column.Precision + ";";
				}
				default:
				{
					return ");";
				}
			}
		}
		
		/// <summary>
		/// Returns a C# member variable declaration statement.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetMemberVariableDeclarationStatement(ColumnSchema column)
		{
			return GetMemberVariableDeclarationStatement("protected", column);
		}
		
		/// <summary>
		/// Returns a C# member variable declaration statement.
		/// </summary>
		/// <param name="protectionLevel"></param>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
		{
			string statement = protectionLevel + " ";
			statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column.Name);
			
			string defaultValue = GetMemberVariableDefaultValue(column);
			if (defaultValue != "")
			{
				statement += " = " + defaultValue;
			}
			
			statement += ";";	
			
			return statement;
		}
		
		/// <summary>
		/// Returns a typed C# reader.ReadXXX() statement.
		/// </summary>
		/// <param name="column"></param>
		/// <param name="index"></param>
		/// <returns></returns>
		public string GetSqlReaderAssignmentStatement(ColumnSchema column, int index)
		{
			string statement = "if (!reader.IsDBNull(" + index.ToString() + ")) ";
			statement += GetMemberVariableName(column.Name) + " = ";
			
			if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
			
			statement += "reader." + GetReaderMethod(column) + "(" + index.ToString() + ");";
			
			return statement;
		}
		
		/// <summary>
		/// Generates a batch of C# validation statements based on the column.
		/// </summary>
		/// <param name="table"></param>
		/// <param name="statementPrefix"></param>
		/// <returns></returns>
		public string GetValidateStatements(TableSchema table, string statementPrefix)
		{
			string statements = "";
			
			foreach (ColumnSchema column in table.Columns)
			{
				if (IncludeEmptyCheck(column))
				{
					statements += "\r\n" + statementPrefix + "if (" + GetMemberVariableName(column.Name) + " == " + GetMemberVariableDefaultValue(column) + ") this.ValidationErrors.Add(new ValidationError(ValidationTypeCode.Required, \"" + table.Name + "\", \"" + column.Name + "\", \"" + column.Name + " is required.\"));";
				}
				if (IncludeMaxLengthCheck(column))
				{
					statements += "\r\n" + statementPrefix + "if (" + GetMemberVariableName(column.Name) + ".Length > " + column.Size.ToString() + ") this.ValidationErrors.Add(new ValidationError(ValidationTypeCode.MaxLength, \"" + table.Name + "\", \"" + column.Name + "\", \"" + column.Name + " is too long.\"));";
				}
			}
			
			return statements.Substring(statementPrefix.Length + 2);
		}
		
		/// <summary>
		/// Returns the name of the public property for a given column.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetPropertyName(ColumnSchema column)
		{
			string propertyName = column.Name;
			
			if (propertyName == column.Table.Name + "Name") return "Name";
			if (propertyName == column.Table.Name + "Description") return "Description";
			
			if (propertyName.EndsWith("TypeCode")) propertyName = propertyName.Substring(0, propertyName.Length - 4);
			
			return propertyName;
		}
		
		/// <summary>
		/// Returns the C# variable type based on the given column.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetCSharpVariableType(ColumnSchema column)
		{
			if (column.Name.EndsWith("TypeCode")) return column.Name;
			
			switch (column.DataType)
			{
				case DbType.AnsiString: return "string";
				case DbType.AnsiStringFixedLength: return "string";
				case DbType.Binary: return "byte[]";
				case DbType.Boolean: return "bool";
				case DbType.Byte: return "byte";
				case DbType.Currency: return "decimal";
				case DbType.Date: return "DateTime";
				case DbType.DateTime: return "DateTime";
				case DbType.Decimal: return "decimal";
				case DbType.Double: return "double";
				case DbType.Guid: return "Guid";
				case DbType.Int16: return "short";
				case DbType.Int32: return "int";
				case DbType.Int64: return "long";
				case DbType.Object: return "object";
				case DbType.SByte: return "sbyte";
				case DbType.Single: return "float";
				case DbType.String: return "string";
				case DbType.StringFixedLength: return "string";
				case DbType.Time: return "TimeSpan";
				case DbType.UInt16: return "ushort";
				case DbType.UInt32: return "uint";
				case DbType.UInt64: return "ulong";
        case DbType.Xml: return "xml";
				case DbType.VarNumeric: return "decimal";
				default:
				{
					return "__UNKNOWN__" + column.NativeType;
				}
			}
		}
		
		/// <summary>
		/// Returns the name of the typed reader method for a given column.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetReaderMethod(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.Byte:
				{
					return "GetByte";
				}
				case DbType.Int16:
				{
					return "GetInt16";
				}
				case DbType.Int32:
				{
					return "GetInt32";
				}
				case DbType.Int64:
				{
					return "GetInt64";
				}
				case DbType.AnsiStringFixedLength:
				case DbType.AnsiString:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return "GetString";
				}
				case DbType.Boolean:
				{
					return "GetBoolean";
				}
				case DbType.Guid:
				{
					return "GetGuid";
				}
				case DbType.Currency:
				case DbType.Decimal:
				{
					return "GetDecimal";
				}
				case DbType.DateTime:
				case DbType.Date:
				{
					return "GetDateTime";
				}
				case DbType.Binary:
				{
					return "GetBytes";
				}
        case DbType.Xml:
        {
          return "GetString";
        }
				default:
				{
					return "__SQL__" + column.DataType;
				}
			}
		}
		
		/// <summary>
		/// Returns the SqlDbType based on a given column.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetSqlDbType(ColumnSchema column)
		{
			switch (column.NativeType.Trim().ToLower())
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
        case "xml": return "Xml";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		/// <summary>
		/// Determine if the given column is using a UDT.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public bool IsUserDefinedType(ColumnSchema column)
		{
			switch (column.NativeType.Trim().ToLower())
			{
				case "bigint":
				case "binary":
				case "bit":
				case "char":
				case "datetime":
				case "decimal":
				case "float":
				case "image":
				case "int":
				case "money":
				case "nchar":
				case "ntext":
				case "numeric":
				case "nvarchar":
				case "real":
				case "smalldatetime":
				case "smallint":
				case "smallmoney":
				case "sql_variant":
				case "sysname":
				case "text":
				case "timestamp":
				case "tinyint":
				case "uniqueidentifier":
				case "varbinary":
        case "xml":
				case "varchar": return false;
				default: return true;
			}
		}
		
		/// <summary>
		/// Returns a default value based on a column's data type.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetMemberVariableDefaultValue(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.Guid:
				{
					return "Guid.Empty";
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return "String.Empty";
				}
				default:
				{
					return "";
				}
			}
		}
		
		/// <summary>
		/// Determines if the given column's data type requires a max length to be defined.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public bool IncludeMaxLengthCheck(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					if (column.NativeType != "text" && column.NativeType != "ntext")
					{
						return true;
					}
					else
					{
						return false;
					}
				}
				default:
				{
					return false;
				}
			}
		}
		
		/// <summary>
		/// Determines if a given column should use a check for an Empty value.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public bool IncludeEmptyCheck(ColumnSchema column)
		{
			if (column.IsPrimaryKeyMember || column.AllowDBNull || column.Name.EndsWith("TypeCode")) return false;
	
			switch (column.DataType)
			{
				case DbType.Guid:
				{
					return true;
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return true;
				}
				default:
				{
					return false;
				}
			}
		}
		
		/// <summary>
		/// Returns a T-SQL parameter statement based on the given column.
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public string GetSqlParameterStatement(ColumnSchema column)
		{
			return GetSqlParameterStatement(column, false);
		}
		
		/// <summary>
		/// Returns a camel cased name from the given identifier.
		/// </summary>
		/// <param name="value"></param>
		/// <returns></returns>
		public string GetCamelCaseName(string value)
		{
			return StringUtility.ToCamelCase(value);
		}
		
		/// <summary>
		/// Returns a spaced out version of the identifier.
		/// </summary>
		/// <param name="value"></param>
		/// <returns></returns>
		public string GetSpacedName(string value)
		{
			return StringUtility.ToSpacedWords(value);
		}
		
		/// <summary>
		/// Returns a T-SQL parameter statement based on the given column.
		/// </summary>
		/// <param name="column"></param>
		/// <param name="isOutput"></param>
		/// <returns></returns>
		public string GetSqlParameterStatement(ColumnSchema column, bool isOutput)
		{
			string param = "@" + column.Name + " " + column.NativeType;
			
			if (!this.IsUserDefinedType(column))
			{
				switch (column.DataType)
				{
					case DbType.Decimal:
					{
						param += "(" + column.Precision + ", " + column.Scale + ")";
						break;
					}
					case DbType.AnsiString:
					case DbType.AnsiStringFixedLength:
					case DbType.String:
					case DbType.StringFixedLength:
					{
						if (column.NativeType != "text" && column.NativeType != "ntext")
						{
							if (column.Size > 0)
							{
								param += "(" + column.Size + ")";
							}
						}
						break;
					}
				}
			}
			
			if (isOutput)
			{
				param += " OUTPUT";
			}
			
			return param;
		}
	}
}
