using System;
using System.Collections;
using System.Data;

namespace Orciid.Core
{
	/// <summary>
	/// Controlled vocabulary for specific fields
	/// </summary>
	/// <remarks>
	/// ControlledList represents a list of permittable values for certain fields.
	/// Duplicate values are not allowed in a controlled list. A <see cref="Field"/>
	/// restricted to a controlled list may still hold other values.
	/// </remarks>
	public class ControlledList:
		CachableObject, IComparable
	{
		private const string classid = "Orciid.Core.ControlledList";
		/// <summary>
		/// Internal identifier
		/// </summary>
		/// <remarks>
		/// The primary key of the controlled list record in the database is its internal identifier.
		/// </remarks>
		private int id;
		/// <summary>
		/// Title
		/// </summary>
		/// <remarks>
		/// The title of the controlled list is used as the identifying property in the user interface.
		/// </remarks>
		private string title;
		/// <summary>
		/// Description
		/// </summary>
		/// <remarks>
		/// The description of the controlled list. This is just a comment, the description
		/// does not have any effect on the functionality of the system.
		/// </remarks>
		private string description;
		/// <summary>
		/// Standardized vocabulary flag
		/// </summary>
		/// <remarks>
		/// A boolean flag specifying if the list is a public standard of any kind. Such lists
		/// cannot be edited as freely as others (e.g. they should not be extended through
		/// data imports).
		/// </remarks>
		private bool standard;
		/// <summary>
		/// Origin of standardized vocabulary
		/// </summary>
		/// <remarks>
		/// A description describing the origin of the controlled list. For standard lists, this
		/// could be a URL or other resource identifier on where the list values can be obtained.
		/// If the <see cref="standard"/> flag is not set, this value has no relevance.
		/// </remarks>
		private string origin;
		/// <summary>
		/// Controlled list vocabulary
		/// </summary>
		/// <remarks>
		/// The values of the controlled list. The internal identifier (primary key in the database)
		/// of each list entry is the key for this Hashtable.
		/// </remarks>
		private Hashtable values = new Hashtable();
		/// <summary>
		/// Controlled list vocabulary
		/// </summary>
		/// <remarks>
		/// If a sorted version of the list is requested, a SortedList is created and cached here.
		/// Every time the list is modified, the cache is set to null to require a recreation later.
		/// </remarks>
		private SortedList valuecache = null;

		private int collectionid;

		/// <summary>
		/// Constructor
		/// </summary>
		/// <remarks>
		/// Creates a new controlled list. 
		/// </remarks>
		public ControlledList():
			base()
		{
		}

		/// <summary>
		/// Constructor
		/// </summary>
		/// <remarks>
		/// Does not initialize or check privileges. This constructor is used internally when
		/// controlled list objects are created from the database.
		/// </remarks>
		/// <param name="init">dummy parameter, should be <c>false</c></param>
		private ControlledList(bool init):
			base(init)
		{
		}

		/// <summary>
		/// Compares the titles of two controlled lists
		/// </summary>
		/// <remarks>
		/// This method is implemented for the IComparable interface. It allows controlled lists
		/// to be sorted by their title.
		/// </remarks>
		/// <param name="c">Another controlled list to compare this controlled list's title to</param>
		/// <returns>The result of the comparison of the controlled lists' titles</returns>
		public int CompareTo(object c)
		{
			return title.CompareTo(((ControlledList)c).title);
		}

		/// <summary>
		/// Check creation privilege
		/// </summary>
		/// <remarks>
		/// This method checks if a specified user has sufficient privileges to create this object.
		/// This method is called from the constructor, so the object already exists and therefore CanCreate
		/// does not need to be static.
		/// </remarks>
		/// <param name="user">The user to check privileges for</param>
		/// <returns><c>true</c> if the User has sufficient privileges to create this object, 
		/// <c>false</c> otherwise.</returns>
		public override bool CanCreate(User user)
		{
			return true;
		}

		/// <summary>
		/// Check modification privilege
		/// </summary>
		/// <remarks>
		/// This method checks if a specified user has sufficient privileges to modify this object.
		/// </remarks>
		/// <param name="user">The user to check privileges for</param>
		/// <returns><c>true</c> if the User has sufficient privileges to modify this object, 
		/// <c>false</c> otherwise.</returns>
		public override bool CanModify(User user)
		{
			if (collectionid == 0)
				return true;
			Collection coll = Collection.GetByID(collectionid);
			if (coll != null)
                return User.HasPrivilege(Privilege.ManageControlledLists, coll, user);
			else
				return false;
		}

		/// <summary>
		/// Value of controlled list entry
		/// </summary>
		/// <remarks>
		/// This indexed property returns the string value of a controlled list entry
		/// indexed by its internal identifier.
		/// </remarks>
		/// <value>
		/// The value of the controlled list entry indexed by its internal identifier.
		/// </value>
		/// <param name="id">
		/// The internal identifier of the controlled list entry
		/// </param>
		public string this[int id]
		{
			get
			{
				return (string)values[id];
			}
		}

		/// <summary>
		/// Internal identifier of controlled list entry
		/// </summary>
		/// <remarks>
		/// This indexed property returns the internal identifier of a controlled list entry
		/// identified by its value.
		/// </remarks>
		/// <value>
		/// The internal identifier of the controlled list entry indexed by its value, or <c>0</c>
		/// if the value was not found.
		/// </value>
		/// <param name="val">The value of the controlled list entry</param>
		public int this[string val]
		{
			get
			{
				SortedList s = GetSortedList();
				if (s.ContainsKey(val))
					return (int)s[val];
				else
					return 0;
			}
		}

		/// <summary>
		/// Retrieve a controlled list
		/// </summary>
		/// <remarks>
		/// This method returns a controlled list requested by its internal identifier. If this is
		/// the first time a controlled list is requested, all lists are preloaded.
		/// </remarks>
		/// <param name="id">the internal identifier of the controlled list</param>
		/// <returns>The controlled list with the specified internal identifier, or <c>null</c>
		/// if no controlled list with that identifier was found.</returns>
		public static ControlledList GetByID(int id)
		{
			ControlledList list = (ControlledList)GetFromCache(classid, id);
			if (list == null)
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query = new Query(conn,
						@"SELECT ID,Title,Description,Standard,Origin,CollectionID 
						FROM ControlledLists WHERE ID={id}");
					query.AddParam("id", id);
					DataTable table = conn.SelectQuery(query);
					if (table.Rows.Count == 1)
					{
						DataRow row = table.Rows[0];
						list = new ControlledList(false);
						list.id = conn.DataToInt(row["ID"], 0);
						list.title = conn.DataToString(row["Title"]);
						list.description = conn.DataToString(row["Description"]);
						list.standard = conn.DataToBool(row["Standard"], false);
						list.origin = conn.DataToString(row["Origin"]);
						list.collectionid = conn.DataToInt(row["CollectionID"], 0);
						
						query = new Query(conn,
							@"SELECT ID,ItemValue FROM ControlledListValues 
							WHERE ControlledListID={id} ORDER BY ControlledListID");
						query.AddParam("id", id);
						table = conn.SelectQuery(query);
						foreach (DataRow r in table.Rows)
							list.values.Add(conn.DataToInt(r["ID"], 0), 
								conn.DataToString(r["ItemValue"]));
						AddToCache(list);
					}
				}
			return list;
		}

		private static ArrayList GetControlledListIDs(int collectionid)
		{
			ArrayList ids = new ArrayList();
			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query = new Query(conn,
					"SELECT ID FROM ControlledLists WHERE CollectionID={id}");
				query.AddParam("id", collectionid);
				DataTable table = conn.SelectQuery(query);
				foreach (DataRow row in table.Rows)
					ids.Add(conn.DataToInt(row["ID"], 0));
			}
			return ids;
		}

		/// <summary>
		/// Return available controlled lists
		/// </summary>
		/// <remarks>
		/// This method returns all controlled lists for a given collection.
		/// Changes to the array itself do not have any effect on the controlled lists in the system,
		/// whereas changes to the controlled list objects will be reflected. 
		/// </remarks>
		/// <param name="collectionid">Internal identifier of the collection</param>
		/// <returns>Array of controlled lists, sorted by their title.</returns>
		public static ControlledList[] GetControlledLists(int collectionid)
		{
			ArrayList ids = GetControlledListIDs(collectionid);
			ControlledList[] lists = new ControlledList[ids.Count];
			for (int i = 0; i < ids.Count; i++)
				lists[i] = GetByID((int)ids[i]);
			Array.Sort(lists);
			return lists;
		}

		/// <summary>
		/// Delete controlled list from the database
		/// </summary>
		/// <remarks>
		/// Deletes a controlled list from the database. The object itself is invalid afterwards.
		/// <para>WARNING: All data contained in the controlled list is dropped from the database.
		/// This action fails if there are <see cref="Field"/>s in any catalog linked to this 
		/// controlled list.</para>
		/// </remarks>
		/// <exception cref="CoreException">Thrown if the controlled list is in use.</exception>
		public void Delete()
		{
			Delete(false);
		}

		internal void Delete(bool forcedelete)
		{
			if (collectionid > 0)
			{
				Collection coll = Collection.GetByID(collectionid);
				if (coll != null)
					User.RequirePrivilege(Privilege.ManageControlledLists, coll);
				else
					throw new PrivilegeException("Controlled list deletion denied");
			}
			if (id > 0)
			{
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query;
					if (!forcedelete)
					{
						query = new Query(conn,
							@"SELECT COUNT(*) FROM FieldDefinitions WHERE ControlledListID={id}");
						query.AddParam("id", id);
						int count = conn.DataToInt(conn.ExecScalar(query), 0);
						if (count > 0) 
							throw new CoreException("Cannot delete controlled list in use.");
					}

					query = new Query(conn,
						@"DELETE FROM ControlledListValues WHERE ControlledListID={id}");
					query.AddParam("id", id);
					conn.ExecQuery(query);

					query = new Query(conn,
						@"DELETE FROM ControlledLists WHERE ID={id}");
					query.AddParam("id", id);
					conn.ExecQuery(query);
				}
				RemoveFromCache(this, true);
				id = 0;
				ResetModified();
			}
		}

		/// <summary>
		/// Write modified controlled list to the database
		/// </summary>
		/// <remarks>
		/// This method commits a newly created or modified controlled list to the database and returns
		/// the controlled list to the unmodifiable state.
		/// </remarks>
		/// <exception cref="CoreException">Thrown if the object cannot be written
		/// to the database.</exception>
		protected override void CommitChanges()
		{
			int result;
			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query;
				if (id == 0) 
				{
					query = new Query(conn,
						@"INSERT INTO ControlledLists (Title,Description,Standard,Origin,CollectionID) 
						VALUES ({title},{description},{standard},{origin},{collectionid})");
					query.AddParam("title", title);
					query.AddParam("description", description);
					query.AddParam("standard", standard);
					query.AddParam("origin", origin);
					query.AddParam("collectionid", collectionid);
					result = conn.ExecQuery(query);
					if (result == 1)
					{
						id = conn.LastIdentity("ControlledLists");
						AddToCache(this, true);
					}
					else
						throw new CoreException("Could not write new controlled list to database");
				}
				else
				{
					query = new Query(conn,
						@"UPDATE ControlledLists SET Title={title},Description={description},
						Standard={standard},Origin={origin},CollectionID={collectionid} 
						WHERE ID={id}");
					query.AddParam("title", title);
					query.AddParam("description", description);
					query.AddParam("standard", standard);
					query.AddParam("origin", origin);
					query.AddParam("collectionid", collectionid);
					query.AddParam("id", id);
					result = conn.ExecQuery(query);
					if (result != 1)
						throw new CoreException("Could not write modified controlled list to database");
				}
			}
		}

		/// <summary>
		/// Values sorted alphabetically
		/// </summary>
		/// <remarks>
		/// This method creates a list sorted alphabetically by the values of the controlled list
		/// entries. Once a sorted list is created, it is cached until the list is modified.
		/// Modifications to the returned <see cref="SortedList"/> object will not yield permanent effects on
		/// the controlled list.
		/// </remarks>
		/// <returns>a <see cref="SortedList"/> with the controlled list entry values as keys and the
		/// values' internal identifiers as values</returns>
		public SortedList GetSortedList()
		{
			if (valuecache != null)
				return valuecache;
			SortedList v = new SortedList(values.Count);
			foreach (int id in values.Keys)
				if (!v.ContainsKey(values[id]))
					v.Add(values[id], id);
			valuecache = v;
			return (SortedList)v.Clone();
		}

		/// <summary>
		/// Internal identifier
		/// </summary>
		/// <remarks>
		/// The internal identifier of a controlled list is its primary key in the database. Therefore, it is
		/// read-only. It is <c>0</c> for newly created controlled lists.
		/// </remarks>
		/// <value>
		/// The internal identifier of the controlled list.
		/// </value>
		public int ID
		{
			get
			{
				return id;
			}
		}

		/// <summary>
		/// Internal identifier
		/// </summary>
		/// <remarks>
		/// The internal identifier is the primary key used in the database.
		/// </remarks>
		/// <returns>
		/// The internal identifier of this controlled list.
		/// </returns>
		public override int GetID()
		{
			return id;
		}

		/// <summary>
		/// Return the class identifier
		/// </summary>
		/// <remarks>
		/// Every class derived from <see cref="CachableObject"/> must implement this method. 
		/// It must return a string that is unique in the system. 
		/// The suggested format is <c>Namespace.Class</c>.
		/// </remarks>
		/// <returns>A unique string identifier for this class</returns>
		protected override string GetClassIdentifier()
		{
			return classid;
		}


		/// <summary>
		/// Title
		/// </summary>
		/// <remarks>
		/// The title of the controlled list. This value is used to identify a controlled list in the 
		/// user interface and therefore cannot be <c>null</c> or empty.
		/// </remarks>
		/// <value>
		/// The title of the controlled list.
		/// </value>
		/// <exception cref="CoreException">Thrown if the title is <c>null</c> or empty.</exception>
		public string Title
		{
			get
			{
				return title;
			}
			set
			{
				if (value != null && value.Length > 0)
				{
					MarkModified();
					title = (value.Length > 50 ? value.Substring(0, 50) : value);
				}
				else
					throw new CoreException("Controlled list title cannot be empty or null");
			}
		}
		
		/// <summary>
		/// Description
		/// </summary>
		/// <remarks>
		/// This value has no meaning in the system.
		/// </remarks>
		/// <value>
		/// The description of the controlled list.
		/// </value>
		public string Description
		{
			get
			{
				return description;
			}
			set
			{
				MarkModified();
				description = value;
			}
		}

		/// <summary>
		/// Controlled list origin
		/// </summary>
		/// <remarks>
		/// If the controlled list represents an official standard vocabulary, this value can hold information
		/// on where the original information was obtained from. This value has no meaning within the system.
		/// This value should only be set if the <see cref="Standard"/> flag is also set.
		/// </remarks>
		/// <value>
		/// The origin of this controlled list
		/// </value>
		public string Origin
		{
			get
			{
				return origin;
			}
			set
			{
				MarkModified();
				origin = value;
			}
		}

		/// <summary>
		/// Standardized vocabulary flag
		/// </summary>
		/// <remarks>
		/// A boolean flag specifying if the list is a public standard of any kind. Such lists
		/// cannot be edited as freely as others (e.g. they should not be extended through
		/// data imports).
		/// </remarks>
		/// <value>
		/// A flag specifying if the controlled list represents a standardized vocabulary.
		/// </value>
		public bool Standard
		{
			get
			{
				return standard;
			}
			set
			{
				MarkModified();
				standard = value;
			}
		}

		/// <summary>
		/// Collection Identifier
		/// </summary>
		/// <remarks>
		/// Every controlled list must be assigned to a collection.  Users must have the
		/// <see cref="Privilege.ManageControlledLists"/> privilege on a collection to be 
		/// able to modify the controlled lists or to assign a controlled list to the collection.
		/// </remarks>
		/// <value>
		/// The internal identifier of the collection this controlled list belongs to.
		/// </value>
		public int CollectionID
		{
			get
			{
				return collectionid;
			}
			set
			{
				if (collectionid == 0 && value != 0)
				{
					Collection coll = Collection.GetByID(value);
					if (coll == null)
						throw new PrivilegeException("Collection not found or access denied");
					User.RequirePrivilege(Privilege.ManageControlledLists, coll);
					MarkModified();
					collectionid = value;
				}
				else if (collectionid != value)
					throw new CoreException("Controlled lists cannot be moved between collections");
			}
		}

		/// <summary>
		/// Add controlled list values
		/// </summary>
		/// <remarks>
		/// This method adds one or more values to the controlled list. Empty, <c>null</c> and already existant values
		/// in the list are ignored. Values which cannot be written to the database are ignored.
		/// This method cannot be called on newly created controlled lists which have not been committed
		/// to the database via <see cref="ModifiableObject.Update"/>. All changes performed by this method are immediately written
		/// to the database.
		/// </remarks>
		/// <param name="listvalues">One or more string values or an array of strings</param>
		/// <exception cref="CoreException">Thrown if values are to be added to a newly created list.
		/// Call <see cref="ModifiableObject.Update"/> first.</exception>
		public void Add(params string[] listvalues)
		{
			if (collectionid != 0)
			{
				Collection coll = Collection.GetByID(collectionid);
				if (coll != null)
					User.RequirePrivilege(Privilege.ManageControlledLists, coll);
				else
					throw new PrivilegeException("Collection not found or access denied");
			}
			if (id == 0)
				throw new CoreException("Cannot add entries to newly created controlled list.");
			using (DBConnection conn = DBConnector.GetConnection())
			{
				foreach (string listvalue in listvalues)
				{
					if (listvalue != null && listvalue.Length > 0)
					{
						string v;
						if (listvalue.Length > 255) 
							v = listvalue.Substring(0, 255);
						else
							v = listvalue;
						if (!values.ContainsValue(v))
						{
							Query query = new Query(conn,
								@"INSERT INTO ControlledListValues (ControlledListID,ItemValue) 
								VALUES ({id},{value})");
							query.AddParam("id", id);
							query.AddParam("value", v);
							int result = conn.ExecQuery(query);
							if (result == 1)
							{
								values.Add(conn.LastIdentity("ControlledListValues"), v);
								valuecache = null;
							}
						}
					}
				}
			}
		}

		/// <summary>
		/// Remove controlled list values
		/// </summary>
		/// <remarks>
		/// This methods deletes one or more entries from this controlled list. The entries are identified
		/// by their internal identifier. If a specified identifier does not exist in this controlled list,
		/// it is ignored.
		/// </remarks>
		/// <param name="listvalues">array/list of internal identifiers of controlled list entries to delete</param>
		public void Remove(params int[] listvalues)
		{
			if (collectionid != 0)
			{
				Collection coll = Collection.GetByID(collectionid);
				if (coll != null)
					User.RequirePrivilege(Privilege.ManageControlledLists, coll);
				else
					throw new PrivilegeException("Collection not found or access denied");
			}
			ArrayList todelete = new ArrayList();
			foreach (int entry in listvalues)
			{
				if (values.ContainsKey(entry))
				{
					values.Remove(entry);
					valuecache = null;
					todelete.Add(entry);
				}
			}
			if (todelete.Count > 0)
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query = new Query(conn, 
						@"UPDATE FieldData SET ControlledListValue=NULL 
						WHERE ControlledListValue IN {todelete}");
					query.AddParam("todelete", todelete);
					conn.ExecQuery(query);

					query = new Query(conn,
						@"DELETE FROM ControlledListValues WHERE ID IN {todelete}");
					query.AddParam("todelete", todelete);
					conn.ExecQuery(query);
				}
		}

		/// <summary>
		/// Rename controlled list entry
		/// </summary>
		/// <remarks>
		/// This method renames an existing controlled list value.  The new value must not
		/// yet exist in the controlled list.
		/// </remarks>
		/// <param name="entry">The identifier of the list entry to rename</param>
		/// <param name="newname">The new entry value</param>
		public void Rename(int entry, string newname)
		{
			Rename(entry, newname, false);
		}

		/// <summary>
		/// Rename or merge controlled list entry
		/// </summary>
		/// <remarks>
		/// This method renames an existing controlled list value.  If the new value
		/// already exists, the two entries are merged if the allowmerge flag is set,
		/// otherwise an exception is thrown.
		/// </remarks>
		/// <param name="entry">The identifier of the list entry to rename</param>
		/// <param name="newname">The new entry value</param>
		/// <param name="allowmerge">Specifies if merging two list entries should
		/// be allowed.  If <c>false</c> and the new entry value already exists, an
		/// exception is thrown.</param>
		public void Rename(int entry, string newname, bool allowmerge)
		{
			if (collectionid != 0)
			{
				Collection coll = Collection.GetByID(collectionid);
				if (coll != null)
					User.RequirePrivilege(Privilege.ManageControlledLists, coll);
				else
					throw new PrivilegeException("Collection not found or access denied");
			}
			string v;
			if (newname == null || newname.Length == 0)
				throw new CoreException("Controlled list value cannot be null or empty");
			if (newname.Length > 255)
				v = newname.Substring(0, 255);
			else
				v = newname;
			if (!values.ContainsKey(entry) || (string)values[entry] == v)
				return;
			string oldname = (string)values[entry];
			if (values.ContainsValue(v))
			{
				if (!allowmerge)
					throw new CoreException(String.Format("The target entry '{0}' already exists", v));
				int mergeentry = -1;
				foreach (int i in values.Keys)
					if ((string)values[i] == v)
					{
						mergeentry = i;
						break;
					}
				if (mergeentry == -1)
					throw new CoreException("Internal error in ControlledList.Rename");
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query = new Query(conn,
						@"UPDATE FieldData SET FieldValue={fieldvalue} 
						WHERE FieldValue LIKE {oldfieldvalue:l} AND ControlledListValue={entry}");
					query.AddParam("fieldvalue", v);
					query.AddParam("oldfieldvalue", oldname);
					query.AddParam("entry", entry);
					conn.ExecQuery(query);

					query = new Query(conn,
						@"UPDATE FieldData SET ControlledListValue={mergeentry} 
						WHERE ControlledListValue={entry}");
					query.AddParam("mergeentry", mergeentry);
					query.AddParam("entry", entry);
					conn.ExecQuery(query);

					this.Remove(entry);
				}
			}
			else
			{
				values[entry] = v;
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query = new Query(conn,
						@"UPDATE ControlledListValues SET ItemValue={value} WHERE ID={id}");
					query.AddParam("value", v);
					query.AddParam("id", entry);
					conn.ExecQuery(query);

					query = new Query(conn,
						@"UPDATE FieldData SET FieldValue={value} 
						WHERE FieldValue LIKE {oldvalue:l} AND ControlledListValue={id}");
					query.AddParam("value", v);
					query.AddParam("oldvalue", oldname);
					query.AddParam("id", entry);
					conn.ExecQuery(query);
				}
			}
		}
	}
}
