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

namespace Orciid.Core
{
	/// <summary>
	/// Analytical tools
	/// </summary>
	/// <remarks>
	/// This class contains several static methods to analyze image usage data.
	/// </remarks>
	public class Analysis
	{
		/// <summary>
		/// Check if an image has collocated images
		/// </summary>
		/// <remarks>
		/// Collocated images are images that are used in the same slideshow.  Collocation is
		/// weighed by the number of slideshows the two related images appear in and by
		/// their distance in the slideshow.  The relevance of the collocation is 
		/// <code>Average Distance/Number of Slideshows</code>.  Related images that only
		/// appear in one slideshow or that have a relevance greater than 5 are not considered.
		/// Slideshows that belong to the user looking for related images
		/// are not considered.
		/// This method may return true even if collocated images are owned by other users and
		/// are not shared.
		/// </remarks>
		/// <param name="ids">An array of image identifiers</param>
		/// <param name="excludeuserid">The user identifier of the user whose slideshows
		/// should be ignored</param>
		/// <returns>An array of booleans indicating if an image represented by the image
		/// identifier in the corresponding position in <c>ids</c> has related images</returns>
		public static bool[] ImageHasRelatedImages(ImageIdentifier[] ids, int excludeuserid)
		{
			if (ids == null)
				return null;
			if (ids.Length == 0)
				return new bool[0];
			Hashtable groupedids = new Hashtable();
			foreach (ImageIdentifier id in ids)
			{
				if (groupedids.ContainsKey(id.CollectionID))
					groupedids[id.CollectionID] = (string)groupedids[id.CollectionID] + "," + id.ID.ToString();
				else
					groupedids.Add(id.CollectionID, id.ID.ToString());
			}
			ArrayList restrict = new ArrayList();
			foreach (int cid in groupedids.Keys)
				restrict.Add(String.Format(
					"(R.CollectionID={0} AND R.ID IN ({1}))", 
					cid, 
					groupedids[cid]));
			Hashtable found = new Hashtable();
			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query = new Query(conn, @"
SELECT R.ID AS I,R.CollectionID AS C FROM Images AS R WHERE ({@restrict}) AND EXISTS (
SELECT S1.CollectionID,S1.ImageID FROM Slides AS S1,Slides AS S2
WHERE (S1.SlideshowID=S2.SlideshowID AND S2.CollectionID=R.CollectionID AND S2.ImageID=R.ID)
AND (S1.CollectionID<>R.CollectionID OR S1.ImageID<>R.ID) AND
(S1.SlideshowID NOT IN (SELECT ID FROM Slideshows WHERE UserID={excludeuserid})) AND (S1.Scratch=0)
GROUP BY S1.CollectionID,S1.ImageID
HAVING COUNT(*)>1 AND AVG(ABS(S1.DisplayOrder-S2.DisplayOrder))/COUNT(*)<5)");
				query.AddParam("excludeuserid", excludeuserid);
				query.AddParam("@restrict", String.Join(" OR ", (string[])restrict.ToArray(typeof(string))));
				DataTable table = conn.SelectQuery(query);
				foreach (DataRow row in table.Rows)
					found.Add(new ImageIdentifier(conn.DataToInt(row["I"], 0), 
						conn.DataToInt(row["C"], 0)), null);
			}
			bool[] result = new bool[ids.Length];
			for (int i = 0; i < result.Length; i++)
				result[i] = found.ContainsKey(ids[i]);
			return result;											
		}

		/// <summary>
		/// Find collocated images
		/// </summary>
		/// <remarks>
		/// Collocated images are images that are used in the same slideshow.  Collocation is
		/// weighed by the number of slideshows the two related images appear in and by
		/// their distance in the slideshow.  The relevance of the collocation is 
		/// <code>Average Distance/Number of Slideshows</code>.  Related images that only
		/// appear in one slideshow or that have a relevance greater than 5 are not considered.
		/// Slideshows that belong to the user looking for related images
		/// are not considered.
		/// </remarks>
		/// <param name="id">An image identifiers</param>
		/// <param name="excludeuserid">The user identifier of the user whose slideshows
		/// should be ignored</param>
		/// <returns>An array of image identifiers of images related to the image
		/// identified by <c>id</c></returns>
		public static ImageIdentifier[] GetRelatedImages(ImageIdentifier id, int excludeuserid)
		{
			// FogBugz case 539: Images that belong to other users and 
			// are not shared may be returned as well.
			ArrayList result = new ArrayList();
			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query = new Query(conn, @"
SELECT S1.CollectionID AS C,S1.ImageID AS I FROM Slides AS S1 INNER JOIN Slides AS S2
ON (S1.SlideshowID=S2.SlideshowID AND S2.CollectionID={collectionid} AND S2.ImageID={imageid})
WHERE (S1.CollectionID<>{collectionid} OR S1.ImageID<>{imageid}) AND
(S1.SlideshowID NOT IN (SELECT ID FROM Slideshows WHERE UserID={excludeuserid}) AND (S1.Scratch=0) AND
(S1.CollectionID IN {collrestrict}))
GROUP BY S1.CollectionID,S1.ImageID
HAVING COUNT(*)>1 AND AVG(ABS(S1.DisplayOrder-S2.DisplayOrder))/COUNT(*)<5
ORDER BY AVG(ABS(S1.DisplayOrder-S2.DisplayOrder))/COUNT(*)");
				query.AddParam("collectionid", id.CollectionID);
				query.AddParam("imageid", id.ID);
				query.AddParam("excludeuserid", excludeuserid);
				query.AddParam("collrestrict", Collection.GetCollectionIDs());
				DataTable table = conn.SelectQuery(query);
				foreach (DataRow row in table.Rows)
					result.Add(new ImageIdentifier(conn.DataToInt(row["I"], 0), 
						conn.DataToInt(row["C"], 0)));
			}
			return (ImageIdentifier[])result.ToArray(typeof(ImageIdentifier));
		}

        /// <summary>
        /// Find number of slideshows an image is in
        /// </summary>
        /// <remarks>
        /// This method returns the number of slideshows the given images are used in.  This 
        /// is useful e.g. when deleting an image to see how many slideshows will be
        /// affected.
        /// </remarks>
        /// <param name="ids">An array of image identifiers</param>
        /// <returns>The number of slideshows each given image is in</returns>
		public static int[] GetImageInSlideshowCount(ImageIdentifier[] ids)
		{
			if (ids == null)
				return null;
			if (ids.Length == 0)
				return new int[0];
			
			// check if current user has curator permission on collections
			Hashtable collections = new Hashtable();
			foreach (Collection coll in Collection.GetCollections(CollectionType.Internal))
			{
				bool modify = User.HasPrivilege(Privilege.ModifyImages, coll);
				bool personal = User.HasPrivilege(Privilege.PersonalImages, coll);
				if (modify || personal)
					collections.Add(coll.ID, modify);
			}

			Hashtable groupedids = new Hashtable();
			foreach (ImageIdentifier id in ids)
			{
				if (collections.ContainsKey(id.CollectionID))
				{
					if (groupedids.ContainsKey(id.CollectionID))
						groupedids[id.CollectionID] = (string)groupedids[id.CollectionID] + "," + id.ID.ToString();
					else
						groupedids.Add(id.CollectionID, id.ID.ToString());
				}
			}

			User user = User.CurrentUser();
			
			ArrayList restrict = new ArrayList();
			foreach (int cid in groupedids.Keys)
				restrict.Add(String.Format(
					"(Slides.CollectionID={0} AND ImageID IN ({1}){2})", 
					cid, 
					groupedids[cid],
					(bool)collections[cid] ? "" : " AND UserID=" + user.ID));

			Hashtable found = new Hashtable();
			if (restrict.Count > 0)
			{
				using (DBConnection conn = DBConnector.GetConnection())
				{
					Query query = new Query(conn, 
						@"
SELECT COUNT(DISTINCT SlideshowID) AS C,ImageID,Slides.CollectionID AS CollectionID FROM Slides INNER JOIN Images 
ON (Slides.ImageID=Images.ID AND Slides.CollectionID=Images.CollectionID)
WHERE ({@restrict}) GROUP BY ImageID,Slides.CollectionID");
					query.AddParam("@restrict", String.Join(" OR ", (string[])restrict.ToArray(typeof(string))));
					DataTable table = conn.SelectQuery(query);
					foreach (DataRow row in table.Rows)
						found.Add(new ImageIdentifier(conn.DataToInt(row["ImageID"], 0), 
							conn.DataToInt(row["CollectionID"], 0)), conn.DataToInt(row["C"], 0));
				}
			}

			int[] result = new int[ids.Length];
			for (int i = 0; i < result.Length; i++)
				result[i] = found.ContainsKey(ids[i]) ? (int)found[ids[i]] : 0;
			return result;		
		}

        /// <summary>
        /// Struct holding image use information
        /// </summary>
        /// <remarks>
        /// Returned by the <see cref="GetImageInSlideshowInfo"/> method
        /// </remarks>
		public struct ImageUseInfo
		{
            /// <summary>
            /// Image Identifier
            /// </summary>
            /// <remarks>
            /// The internal identifier of the image
            /// </remarks>
			public ImageIdentifier id;
            /// <summary>
            /// User
            /// </summary>
            /// <remarks>
            /// The owner of the slideshow that uses the image
            /// </remarks>
			public User user;
            /// <summary>
            /// Slideshow
            /// </summary>
            /// <remarks>
            /// The slideshow that uses the image
            /// </remarks>
			public Slideshow show;
		}

        /// <summary>
        /// Find all slideshows an image is in
        /// </summary>
        /// <remarks>
        /// This method returns a list of slideshows that use the given image.  This 
        /// is useful e.g. when deleting an image to see if/which slideshows will be
        /// affected.
        /// </remarks>
        /// <param name="id">The identifier of the image</param>
        /// <returns>An array of <see cref="ImageUseInfo"/> objects</returns>
		public static ImageUseInfo[] GetImageInSlideshowInfo(ImageIdentifier id)
		{
			Collection coll = Collection.GetByID(id.CollectionID);
			if (coll == null)
				return null;

			User user = User.CurrentUser();
			if (user == null)
				return null;

			bool curator = User.HasPrivilege(Privilege.ModifyImages, coll);
			
			ArrayList result = new ArrayList();
			Hashtable users = new Hashtable();

			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query = new Query(conn, @"
SELECT DISTINCT UserID,Slideshows.ID AS SlideshowID
FROM Slides INNER JOIN Slideshows ON SlideshowID=Slideshows.ID
WHERE ImageID={imageid} AND CollectionID={collectionid} 
ORDER BY UserID");
				query.AddParam("imageid", id.ID);
				query.AddParam("collectionid", id.CollectionID);
				query.AddParam("userid", user.ID);
				query.AddParam("curator", curator ? 1 : 0);
				DataTable table = conn.SelectQuery(query);
				foreach (DataRow row in table.Rows)
				{
					ImageUseInfo info = new ImageUseInfo();
					info.id = id;
					int userid = conn.DataToInt(row["UserID"], 0);
					if (users.ContainsKey(userid))
						info.user = (User)users[userid];
					else
					{
						info.user = User.GetByID(userid);
						users.Add(userid, info.user);
					}
					info.show = Slideshow.GetByID(conn.DataToInt(row["SlideshowID"], 0));
					result.Add(info);
				}
					
			}
			return (ImageUseInfo[])result.ToArray(typeof(ImageUseInfo));
		}

        /// <summary>
        /// Find slideshows with unavailable images
        /// </summary>
        /// <remarks>
        /// This method checks all slideshows owned by the current user for 
        /// missing images
        /// </remarks>
        /// <returns>An array of slideshow identifiers that have missing images</returns>
		public static int[] GetUnavailableImagesInSlideshows()
		{			
			User user = User.CurrentUser();
			if (user == null)
				return null;

			ArrayList availablecolls = new ArrayList();
			foreach (Collection c in Collection.GetCollections())
				availablecolls.Add(c.ID);

			using (DBConnection conn = DBConnector.GetConnection())
			{
				Query query = new Query(conn, @"
SELECT DISTINCT SlideshowID
FROM Slides
LEFT JOIN Slideshows ON Slideshows.ID=SlideshowID 
LEFT JOIN Images ON Images.ID=ImageID AND Images.CollectionID=Slides.CollectionID
WHERE Slideshows.UserID={userid} AND
(Images.ID IS NULL OR Slides.CollectionID NOT IN {collectionids} OR 
(Images.UserID<>{userid} AND Images.UserID<>0 AND Images.UserID IS NOT NULL AND 
Images.Flags&{shared}<>{shared}))
");
				query.AddParam("userid", user.ID);
				query.AddParam("collectionids", availablecolls);
				query.AddParam("shared", (int)ImageFlag.Shared);
				using (IDataReader reader = conn.ExecReader(query))
				{
					ArrayList result = new ArrayList();
					while (reader.Read())
						result.Add(conn.DataToInt(reader.GetValue(0), 0));
					return (int[])result.ToArray(typeof(int));
				}
			}
		}
	}
}