using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Collections;
using Whitemice.MOGI;

namespace MorrisonIndustries.Hedera
{
	public class Hedera : Whitemice.MOGI.MOGIMon.IPusher
	{
		IServer		server;
		IDbConnection	connection;
		IDbCommand	command;

		public Hedera()
		{
		}

		public void Initialize(IServer server) 
		{
			string connectionString;

			this.server = server;
			connectionString =
				"DSN=miecr;DB=miecr;";
			connection = new OdbcConnection(connectionString);
			connection.Open();
		}

		public void Shutdown()
		{
			connection.Close();
			connection = null;
		}

		public void Update(string documentName, IEnumerator interestedParties)
		{
			IDocument	document;
			Console.WriteLine("MorrisonIndustries.Hedera.Pusher updating {0}", documentName);
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					try { document = (IDocument)server.Get(documentName); }
						catch(System.ArgumentException ex)
						{ 
							Console.WriteLine("DUPLICATE KEY EXCEPTION!!!");
							return;
						}
					UpdateContact((IContact)document);
					break;
				case DocumentType.Enterprise:
					try { document = (IDocument)server.Get(documentName); }
						catch(System.ArgumentException ex)
						{ 
							Console.WriteLine("DUPLICATE KEY EXCEPTION!!!");
							return;
						}
					UpdateEnterprise((IEnterprise)document);
					break;
			}
		}

		public void Create(string documentName, IEnumerator interestedParties)
		{
			IDocument	document;
			Console.WriteLine("MorrisonIndustries.Hedera.Pusher creating {0}", documentName);
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					document = (IDocument)server.Get(documentName);
					InsertContact((IContact)document);
					break;
				case DocumentType.Enterprise:
					document = (IDocument)server.Get(documentName);
					InsertEnterprise((IEnterprise)document);
					break;
			}
		}

		public void Delete(string documentName, IEnumerator interestedParties)
		{
			Console.WriteLine("MorrisonIndustries.Hedera.Pusher deleting {0}", documentName);
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					DeleteContact(MOGI.Id(documentName));
					break;
				case DocumentType.Enterprise:
					DeleteEnterprise(MOGI.Id(documentName));
					break;
			}		
		}

		/// \brief Update hedera_address
		protected void UpdateAddress(string owner_id, IAddress address)
		{
			Console.WriteLine("MorrisonIndustries.Hedera.Pusher updating address dor {0}", owner_id);
			RunCommand(
				"UPDATE hedera_address " +
				"  SET name1 = ?, name2 = ?, name3 = ?," +
				"      street = ?, city = ?, state = ?," +
				"      postal_code = ?, country = ?" +
				"  WHERE address_id = ?",
				new object[] {
					address.Name1, address.Name2, address.Name3,
					address.Street, address.City, address.State,
					address.Zip, address.Country, 
					Convert.ToInt32(MOGI.Id(address.Name))
					}
				);
		}

		/// \brief Insert into hedera_address
		protected void InsertAddress(string owner_id, IAddress address)
		{
			RunCommand(
				"INSERT INTO hedera_address " +
				" (address_id, owner_id, type, name1, name2, name3," +
				"  street, city, state, postal_code, country)" +
				"  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
				new object[] { 
					Convert.ToInt32(MOGI.Id(address.Name)),
					Convert.ToInt32(owner_id),
					address.TypeAbbreviation,
					address.Name1, address.Name2, address.Name3, 
					address.Street, address.City, address.State,
					address.Zip, address.Country
					}
				);
		}

		protected void DeleteAddresses(string owner_id)
		{
			RunCommand(
				"DELETE FROM hedera_address WHERE owner_id = ?;",
				new object[] { Convert.ToInt32(owner_id) }
				);
		}

		protected void UpdateContact(IContact contact)
		{
			RunCommand(				
				"UPDATE hedera_contacts " +
				"  SET first_name = ?, middle_name = ?, " +
				"      last_name = ?, display_name = ?, " +
				"      url = ?, comment = ?, degree = ?, " +
				"      gender = ?, x_mailmkt = ?, " +
				"      x_salesperson = ?, x_headboy = ?, " +
				"      x_territory = ?, x_callfreq = ?, " +
				"      x_job_title = ?, x_division = ?" +
				" WHERE contact_id = ?;",
				new object[] {
					contact.FirstName, contact.MiddleName, contact.LastName,
					contact.DisplayName, contact.URL, contact.Comment,
					contact.Degree, contact.Gender.ToString(),
					(string)contact.ExtendedAttribute("mailmktcode"),
					(string)contact.ExtendedAttribute("salesperson"),
					(string)contact.ExtendedAttribute("headboy"),
					(string)contact.ExtendedAttribute("territory"),
					(string)contact.ExtendedAttribute("callfrequency"),
					(string)contact.ExtendedAttribute("job_title"),
					(string)contact.ExtendedAttribute("division"),
					Convert.ToInt32(MOGI.Id(contact.Name))
					}
				);
			foreach(string addressType in server.AddressTypes(DocumentType.Contact))
				if (contact.HasAddressType(addressType))
					UpdateAddress(MOGI.Id(contact.Name), contact.Address(addressType));
		}



		protected void UpdateEnterprise(IEnterprise enterprise)
		{
			RunCommand(
				"UPDATE hedera_enterprise " +
				"  SET name = ?, bank = ?, bank_code = ?, email = ?," +
				"      url = ?, x_mailmkt = ?, x_salesperson = ?," +
				"      x_territory = ?, x_purchfreq = ?, x_division = ?" +
				" WHERE enterprise_id = ?;",
				new object[] {
					enterprise.Description , enterprise.Bank, enterprise.BankCode,
					enterprise.Email1, enterprise.URL, 
					(string)enterprise.ExtendedAttribute("mailmktcode"),
					(string)enterprise.ExtendedAttribute("salesperson"),
					(string)enterprise.ExtendedAttribute("territory"),
					(string)enterprise.ExtendedAttribute("purchasefrequency"),
					(string)enterprise.ExtendedAttribute("division"),
					Convert.ToInt32(MOGI.Id(enterprise.Name))
					}
				);
			foreach(string addressType in server.AddressTypes(DocumentType.Enterprise))
				if (enterprise.HasAddressType(addressType))
					UpdateAddress(MOGI.Id(enterprise.Name), enterprise.Address(addressType));
		}

		protected void InsertContact(IContact contact)
		{
			RunCommand(
				"INSERT INTO hedera_contacts " +
				"  (contact_id, object_type, " +
				"   first_name, middle_name, last_name, display_name, " +
				"   url, comment, degree, gender, x_mailmkt, " +
				"   x_salesperson, x_headboy, x_territory, " +
				"   x_callfreq, x_job_title, x_division) " +
				"  VALUES();",
				new object[] {
					Convert.ToInt32(MOGI.Id(contact.Name)), @"C",
					contact.FirstName, contact.MiddleName,
					contact.LastName, contact.DisplayName,
					contact.URL, contact.Comment, contact.Degree,
					contact.Gender.ToString(),
					(string)contact.ExtendedAttribute("mailmktcode"),
					(string)contact.ExtendedAttribute("salesperson"),
					(string)contact.ExtendedAttribute("headboy"),
					(string)contact.ExtendedAttribute("territory"),
					(string)contact.ExtendedAttribute("callfrequency"),
					(string)contact.ExtendedAttribute("job_title"),
					(string)contact.ExtendedAttribute("division")
					}
				);
			foreach(string addressType in server.AddressTypes(DocumentType.Contact))
				if (contact.HasAddressType(addressType))
					UpdateAddress(MOGI.Id(contact.Name), contact.Address(addressType));
		}

		protected void InsertEnterprise(IEnterprise enterprise)
		{
			RunCommand(
				"INSERT INTO hedera_enterprise " +
				"  (enterprise_id, object_type, " + 
				"   name, bank, bank_code, email, url, x_mailmkt,  " +
				"   x_salesperson, x_territory, x_purchfreq, x_division) " +
				"  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
				new object[] {
					Convert.ToInt32(MOGI.Id(enterprise.Name)), @"E",
					enterprise.Description, enterprise.Bank,
					enterprise.BankCode, enterprise.Email1,
					enterprise.URL, 
					(string)enterprise.ExtendedAttribute("mailmktcode"),
					(string)enterprise.ExtendedAttribute("salesperson"),
					(string)enterprise.ExtendedAttribute("territory"),
					(string)enterprise.ExtendedAttribute("purchasefrequency"),
					(string)enterprise.ExtendedAttribute("division")
					}
				);
			foreach(string addressType in server.AddressTypes(DocumentType.Enterprise))
				if (enterprise.HasAddressType(addressType))
					InsertAddress(MOGI.Id(enterprise.Name), enterprise.Address(addressType));
		}

		protected void DeleteContact(string contactId)
		{
			RunCommand(
				"DELETE FROM hedera_contacts WHERE contact_id = ?",
				new object[] { Convert.ToInt32(contactId) }
				);
			DeleteAddresses(contactId);
		}

		protected void DeleteEnterprise(string enterpriseId)
		{
			RunCommand(
				"DELETE FROM hedera_enterprise WHERE enterprise_id = ?",
				new object[] { Convert.ToInt32(enterpriseId) }
				);
			DeleteAddresses(enterpriseId);
		}

		protected void RunCommand(string commandText, object[] parameters)
		{
			Console.WriteLine("SQL: {0}", commandText);
			command = connection.CreateCommand();
			command.CommandText = commandText;
    			foreach (object parameter in parameters)
     				   command.Parameters.Add(new OdbcParameter("", parameter));
			command.Prepare();
			command.ExecuteNonQuery();
			command.Dispose();
			command = null;			
		}
	}
}