using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using Microsoft.MetadirectoryServices;
using MySql.Data.MySqlClient;

namespace Miis_CallExport
{
	public class MACallExport :  
		IMAExtensibleFileImport, 
		IMAExtensibleCallExport
	{
		//
		// Constructor
		//
		public MACallExport(){}
      
		// ------------------ C O N S T A N T S -----------------------------------------------
		private const string PARAM_SQLTABLENAME = "Table";
		private const string PARAM_SQLWHERECLAUSE = "SQLWhereClause";
		private const string PARAM_SQLJOINCLAUSE = "SQLJoinClause";
		private const string PARAM_SQLDATABASE = "Database";
		private const string PARAM_SQLTIMEOUT = "Timeout";
		private const string PARAM_SQLSECURITY = "Security";
		private const string PARAM_SQLPROVIDER = "Provider";

		private string EscapeStringForImport(string escape)
		{
			if(escape == null)
				return null;

			escape.Replace("\"", "\"\""); 
			return String.Format("\"{0}\"", escape);
		} // EscapeStringForImport

		private string ConstructConnectionString
			(
			string                      connectTo, 
			string                      user, 
			string                      password, 
			ConfigParameterCollection   configParameters
			)
		{
			string connectionString = "";
			
			connectionString = String.Format("Database={0};Data Source={1};User Id={2};Password={3}",
				configParameters[PARAM_SQLDATABASE].Value,
				connectTo,
				user,
				password);
		
			return connectionString;
		} //ConstructConnectionString



		public void GenerateImportFile( 
			string                      filename, 
			string                      connectTo, 
			string                      user, 
			string                      password, 
			ConfigParameterCollection   configParameters,
			bool                        fullImport, 
			TypeDescriptionCollection   types,
			ref string                  customData 
			)
		{
			if(!fullImport)
			{
				throw new TerminateRunException("This MA only supports full import"); 
			}

			//-----------------------------------------------------------------------------------------------
			// Construct the columns to select from the SQL table by
			// enumerating through the attributes of the first object type.
			// This assumes that the DB MA schema exposes all attributes available for all object types
			//-----------------------------------------------------------------------------------------------
			string sqlColumns = "";
			string objectType = "";
			ArrayList attributeList = new ArrayList();
			foreach(TypeDescription t in types)
			{
				objectType = t.Name; 
				foreach(AttributeDescription a in t.Attributes)
				{
					sqlColumns = sqlColumns + a.Name + ","; 
					attributeList.Add(a.Name);
				}
				break;
			}
			if(sqlColumns.Equals(""))
			{
				throw new TerminateRunException("No attributes in schema definition");
			}
			sqlColumns = sqlColumns.Substring(0, sqlColumns.Length-1); // remove last comma

			//-----------------------------------------------------------------------------------------------
			// Construct the SQL Select statement for the intermediate import file.
			//-----------------------------------------------------------------------------------------------
			string commandString = String.Format("SELECT {0} FROM {1}",
				sqlColumns,
				configParameters[PARAM_SQLTABLENAME].Value);

			try
			{
				if(!configParameters[PARAM_SQLJOINCLAUSE].Value.Equals(""))
				{
					commandString = String.Format("{0} {1}", commandString, configParameters[PARAM_SQLJOINCLAUSE].Value); 
				}
			}
			catch { /* ignore if the parameter "SQLJoinClause" doesn't exist */ }


			try
			{
				if(!configParameters[PARAM_SQLWHERECLAUSE].Value.Equals(""))
				{
					commandString = String.Format("{0} WHERE {1}", commandString, configParameters[PARAM_SQLWHERECLAUSE].Value); 
				}
			}
			catch { /* ignore if the parameter "SQLWhereClause" doesn't exist */ }

			//-----------------------------------------------------------------------------------------------
			// Construct the connection string and connect to the SQL database
			//-----------------------------------------------------------------------------------------------
			string connectionString = ConstructConnectionString(connectTo, user, password, configParameters);

			MySqlConnection myConnection = new MySqlConnection(connectionString);
			MySqlCommand myCommand = new MySqlCommand(commandString, myConnection);

			//-----------------------------------------------------------------------------------------------
			// Open the SQL database and create the intermediate file
			// Note that this code only supports String, Integer, GUID, and DBNULL values
			// Further datatypes must be added by extending the code.
			//-----------------------------------------------------------------------------------------------
			myConnection.Open(); 
			MySqlDataReader result = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
			StreamWriter stream = new StreamWriter(filename, false, System.Text.Encoding.Unicode);
			while(result.Read())
			{
				for(int i=0; i<result.FieldCount;i++)
				{
					//result.GetDataTypeName(i);
					string colValue = "";
					switch (result.GetDataTypeName(i).ToUpper())
					{
						case "VARCHAR":
							colValue = result.GetString(i);
							break;

						case "DATE":
							colValue = result.GetMySqlDateTime(i).ToString();
							break;

						case "INT":
							colValue = result.GetUInt32(i).ToString();
							break;

						default:
							colValue = "";
							break;
					}
					stream.WriteLine(String.Format("{0}: {1}", attributeList[i], colValue));

				}
				stream.WriteLine(); // new record, seperate by new line
			}
			stream.Close();
		} //GenerateImportFile


		// ------------------  E X P O R T  ---------------------------------------------------
		// Globals for Export routines
		private string ExportConnectionString;
		private string ExportTableName;
		private MySqlConnection ExportConnection;
		private TypeDescriptionCollection ExportTypes;
		// not needed: private bool ExportEscapeAnchor;

		public void BeginExport( 
			string                      connectTo, 
			string                      user, 
			string                      password,
			ConfigParameterCollection   configParameters,
			TypeDescriptionCollection   types
			)
		{
			//-----------------------------------------------------------------------------------------------
			// Construct the connection string and connect to the SQL database
			//-----------------------------------------------------------------------------------------------
			ExportConnectionString = ConstructConnectionString(connectTo, user, password, configParameters);
			ExportConnection = new MySqlConnection(ExportConnectionString);
			ExportConnection.Open(); 

			// Save Type (Schema) information for later to determine the anchor for a given object type
			ExportTypes = types;

			// Save the Database table name ro run the Insert/Update/Deletes
			ExportTableName = configParameters[PARAM_SQLTABLENAME].Value;

			// Save for later if in the where clause the anchor needs to get enclosed in ''
			// not needed: ExportEscapeAnchor = configParameters[PARAM_ESCAPEANCHOR].Value.ToLower().Equals("yes") ? true : false;  
		} //BeginExport

		public void ExportEntry( 
			ModificationType    modificationType, 
			string[]            changedAttributes,
			CSEntry             csentry 
			)
		{
			switch(modificationType)
			{
				case ModificationType.Replace:
					UpdateEntry(changedAttributes, csentry);
					break;

				case ModificationType.Add:
					InsertEntry(changedAttributes, csentry);
					break;

				case ModificationType.Delete:
					DeleteEntry(csentry);
					break;
			}
		} //ExportEntry

		private string EscapeStringForExport(string escape)
		{
			if(escape == null)
				return null;

			return escape.Replace("'", "''");
		} // EscapeStringForExport

		private string GetAnchorAttributeName(CSEntry cs)
		{
			TypeDescription t = ExportTypes[cs.ObjectType]; 
			//-----------------------------------------------------------
			// Note that this MA only supports one Anchor attribute
			//-----------------------------------------------------------
			if(t.AnchorAttributes.Count > 1) 
				throw new TerminateRunException(String.Format("ObjectType {0} has more than one anchor attribute", cs.ObjectType));

			foreach(AttributeDescription attr in t.AnchorAttributes)
			{
				return attr.Name; 
			}
			return null;
		} // GetAnchorAttributeName

		private bool EscapeAnchorAttribute(CSEntry cs)
		{
			TypeDescription t = ExportTypes[cs.ObjectType]; 
			//-----------------------------------------------------------
			// Note that this MA only supports one Anchor attribute
			//-----------------------------------------------------------
			if(t.AnchorAttributes.Count > 1) 
				throw new TerminateRunException(String.Format("ObjectType {0} has more than one anchor attribute", cs.ObjectType));

			foreach(AttributeDescription attr in t.AnchorAttributes)
			{
				switch (attr.DataType)
				{
					case AttributeType.String:
						return true;
					case AttributeType.Integer:
						return false;
					case AttributeType.Binary:
						return true;
					default:
						throw new TerminateRunException(String.Format("Anchor type unsupported. ObjectType: {0}, Attribute: {1}, Type: {2}", cs.ObjectType, attr.Name, attr.DataType.ToString()));
				}
			}
			return true;
		} // EscapeAnchorAttribute

		private void UpdateEntry(string[] changedAttributes, CSEntry cs)
		{
			//------------------------------------------------------------------------------
			// Build the SQL Update Query
			//------------------------------------------------------------------------------
			string commandString = String.Format("UPDATE {0} SET", ExportTableName);
			foreach(string attribName in changedAttributes)
			{
				string val = "";
				Attrib attr = cs[attribName]; 
				if(attr.IsPresent)
				{
					switch(attr.DataType)
					{
						case AttributeType.String:
							val = "'" + EscapeStringForExport(attr.StringValue) + "'";
							break;
						case AttributeType.Integer:
							val = System.Convert.ToString(attr.IntegerValue, 10);   
							break;
						default:
							throw new UnexpectedDataException(String.Format("Unknown data type for export update. Attr: {0}, Type: {1}", attribName, attr.DataType.ToString())); 
					}
				}
				else
				{
					val = "NULL";
				}

				commandString = commandString + String.Format(" {0}={1},", attribName, val);
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma

			string anchor = GetAnchorAttributeName(cs);
			string anchorValue = cs[anchor].Value.ToString();
			if(EscapeAnchorAttribute(cs))
			{
				anchorValue = String.Format("'{0}'", anchorValue); 
			}
			commandString = commandString + String.Format(" WHERE {0}={1}", anchor, anchorValue);
			//------------------------------------------------------------------------------
			// Build the SQL Update Query
			//------------------------------------------------------------------------------

			//------------------------------------------------------------------------------
			// Execute SQL Update Query
			//------------------------------------------------------------------------------
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL

		} // UpdateEntry

		private void InsertEntry(string[] changedAttributes, CSEntry cs)
		{
			//------------------------------------------------------------------------------
			// Build the SQL Insert Query
			//------------------------------------------------------------------------------
			string commandString = String.Format("INSERT INTO {0} (", ExportTableName);
			foreach(string attribName in changedAttributes)
			{
				commandString = commandString + attribName + ",";
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma
			commandString = commandString + ") VALUES (";
 
			foreach(string attribName in changedAttributes)
			{
				string val = "";
				Attrib attr = cs[attribName]; 
				if(attr.IsPresent)
				{
					switch(attr.DataType)
					{
						case AttributeType.String:
							val = "'" + EscapeStringForExport(attr.StringValue) + "'";
							break;
						case AttributeType.Integer:
							val = System.Convert.ToString(attr.IntegerValue, 10);   
							break;
						case AttributeType.Binary: // assuming GUID value (e.g. Anchor)
							val = "'" + attr.Value.ToString() + "'";  
							break;
						default:
							throw new UnexpectedDataException(String.Format("Unknown data type for export add. Attr: {0} Type: {1}" + attribName, attr.DataType.ToString())); 
					}
				}
				else
				{
					val = "NULL";
				}

				commandString = commandString + val + ",";
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma
			commandString = commandString + ")";
			//------------------------------------------------------------------------------
			// Build the SQL Insert Query
			//------------------------------------------------------------------------------

			//------------------------------------------------------------------------------
			// Execute the SQL Insert Query
			//------------------------------------------------------------------------------
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL
		} // insert entry

		private void DeleteEntry(CSEntry cs)
		{
			string anchor = GetAnchorAttributeName(cs);
			string anchorValue = cs[anchor].Value.ToString();
			if(EscapeAnchorAttribute(cs))
			{
				anchorValue = String.Format("'{0}'", anchorValue); 
			}

			// Build the SQL Delete Query
			string commandString = String.Format("DELETE FROM {0} WHERE {1}={2}", ExportTableName, anchor, anchorValue);

			// Execute the SQL Delete Query
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL
		} // DeleteEntry


		public void EndExport(
			)
		{
			ExportConnection.Close();
		} //EndExport
	}
}