using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Diagnostics;
using System.IO.MemoryMappedFiles;
using System.Linq;
using EventCyborgEvent.Business;
using EventCyborgEvent.Entities;
using EventCyborgEvent.Infrastructure;
using System.Device.Location;
using EventCyborgEvent.Infrastructure.Common;
using EventCyborgEvent.Infrastructure.Enums;
using EventCyborgEvent.Utils;

namespace EventCyborgEvent.Services
{
    public class AmazonRedshift
    {
        #region Variables

        private readonly TopicService _topicService;
        private readonly EventInfoService _eventInfoService;
        private readonly UserService _userService;
        private readonly UserFollowService _userFollowService;
        private readonly MediaService _mediaService;
        private readonly GlobalSettingsService _globalSettingsService = new GlobalSettingsService();
        public TopicService TopicService
        {
            get { return _topicService ?? new TopicService(); }
        }
        public EventInfoService EventInfoService
        {
            get { return _eventInfoService ?? new EventInfoService(); }
        }
        public UserService UserService
        {
            get { return _userService ?? new UserService(); }
        }
        public UserFollowService UserFollowService
        {
            get { return _userFollowService ?? new UserFollowService(); }
        }
        public MediaService MediaService
        {
            get { return _mediaService ?? new MediaService(); }
        }

        public string GeoHelperDatabaseFile
        {
            get
            {
                var allSettigns = _globalSettingsService.GetAll();
                string url;
                url = allSettigns.Find(GlobalSettingsColumn.ItemKey, "GeoHelperDatabaseFile").ItemValue;
                return url;
            }
        }
        public string AmazonRedshiftDatabaseName
        {
            get
            {
                var allSettigns = _globalSettingsService.GetAll();
                string dbName;
                dbName = allSettigns.Find(GlobalSettingsColumn.ItemKey, "AmazonRedshiftDatabaseName").ItemValue;
                return dbName;
            }
        }

        public int tagsCount = 0;
        // Server, e.g. "examplecluster.xyz.us-west-2.redshift.amazonaws.com"
        string server = "aigendars.crqnzxnrzu09.eu-west-1.redshift.amazonaws.com";

        // Port, e.g. "5439"
        string port = "5439";

        // MasterUserName, e.g. "masteruser".
        string masterUsername = "redshift";

        // MasterUserPassword, e.g. "mypassword".
        string masterUserPassword = "!Term2015#$";

        // DBName, e.g. "dev"
        //string DBName = "aigendaredshiftdb";

        private List<string> tags;

        #endregion

        #region Log methods
        /// <summary> Used for inserting new record in activityLog table (AmazonRedshiftDB) </summary>
        public void InsertLog(Activity activityLog)
        {

            string query = "";

            if (activityLog.ActivityValue.Count > 1)
            {
                foreach (var row in activityLog.ActivityValue)
                {
                    query +=
                        string.Format(
                            "insert into tagging.activityLog(userid,externalentityid,activityvalue,clicktype,actiondate,latitude,longitude,related_topics)" +
                            " values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}'); ",
                            activityLog.UserId, activityLog.ExternalEntityId, row, (int)activityLog.ActivityType,
                            activityLog.TimeOfActivity, activityLog.Latitude, activityLog.Longitude, activityLog.RelatedTopics);
                }
            }
            else
            {
                if (activityLog.ActivityValue.Count == 1)
                {
                    query =
                        string.Format(
                            "insert into tagging.activityLog(userid,externalentityid,activityvalue,clicktype,actiondate,latitude,longitude,related_topics)" +
                            " values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}')",
                            activityLog.UserId, activityLog.ExternalEntityId, activityLog.ActivityValue[0],
                            (int)activityLog.ActivityType, activityLog.TimeOfActivity, activityLog.Latitude,
                            activityLog.Longitude, activityLog.RelatedTopics);
                }
                else
                {
                    query =
                        string.Format(
                            "insert into tagging.activityLog(userid,externalentityid,activityvalue,clicktype,actiondate,latitude,longitude,related_topics)" +
                                         " values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}')",
                            activityLog.UserId, activityLog.ExternalEntityId, string.Empty,
                            (int)activityLog.ActivityType, activityLog.TimeOfActivity, activityLog.Latitude,
                            activityLog.Longitude, activityLog.RelatedTopics);
                }
            }



            string connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            try
            {
                // Make a connection using the psqlODBC provider.

                conn.Open();

                // Try a simple query.
                string sql = query;

                var da = new OdbcDataAdapter();
                da.InsertCommand = new OdbcCommand();
                da.InsertCommand.CommandText = query;
                da.InsertCommand.Connection = conn;

                var nesto = da.InsertCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>Returns latitude and longitude using GeoCoordinateWatcher. </summary>
        public double[] GetUserLatitudeAndLongitutde()
        {
            GeoCoordinateWatcher watcher = new GeoCoordinateWatcher();
            watcher.TryStart(false, TimeSpan.FromMilliseconds(1000));

            double latitude;
            double longitutde;

            if (watcher.Position.Location.IsUnknown != true)
            {
                GeoCoordinate coord = watcher.Position.Location;

                latitude = coord.Latitude;
                longitutde = coord.Longitude;

            }
            else
            {
                GeoHelper geoHelper = new GeoHelper(GeoHelperDatabaseFile);
                double[] res = geoHelper.GetlatLong();
                if (res != null)
                {
                    latitude = res[0];
                    longitutde = res[1];
                }
                else
                {
                    string publicIp = geoHelper.GetIp();
                    res = geoHelper.GetlatLongByIpApi(publicIp);
                    latitude = res[0];
                    longitutde = res[1];
                }

            }

            return new[]
            {
                latitude,
                longitutde
            };

        }


        public void InsertListLog(List<Activity> activityLogs)
        {


            string query =
                "insert into tagging.activityLog(userid,externalentityid,activityvalue,clicktype,actiondate,latitude,longitude)values";

            foreach (var activityLog in activityLogs)
            {
                query += string.Format("('{0}','{1}','{2}',{3},'{4}','{5}','{6}')",
                                         activityLog.UserId, activityLog.ExternalEntityId, activityLog.ActivityValue, (int)activityLog.ActivityType, activityLog.TimeOfActivity, activityLog.Latitude, activityLog.Longitude) + ",";
            }

            query = query.Remove(query.Length - 1);
            string connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);
            try
            {
                // Make a connection using the psqlODBC provider.
                conn.Open();

                // Try a simple query.
                string sql = query;

                OdbcDataAdapter da = new OdbcDataAdapter();
                da.InsertCommand = new OdbcCommand();
                da.InsertCommand.CommandText = query;
                da.InsertCommand.Connection = conn;

                var nesto = da.InsertCommand.ExecuteReader();

            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region Construct Tag Clusters

        public MemoryMappedFile JoinTagsOnUsers(MemoryMappedFile matrix, List<string> tags)
        {
            string query = string.Format(@"SELECT firstlog.activityvalue Tag1, firstLog.userid UserId, secondLog.activityvalue
                                        Tag2 FROM tagging.activityLog firstLog
                                        JOIN tagging.activityLog secondLog ON firstLog.userid = secondLog.userid
                                        WHERE firstLog.activityid < secondLog.activityid AND activityvalue<>''");

            string connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);
            try
            {
                // Make a connection using the psqlODBC provider.
                conn.Open();

                OdbcDataAdapter da = new OdbcDataAdapter();
                da.SelectCommand = new OdbcCommand();
                da.SelectCommand.CommandText = query;
                da.SelectCommand.Connection = conn;

                var reader = da.SelectCommand.ExecuteReader();

                while (reader.Read())
                {
                    var tag1 = reader["Tag1"].ToString();
                    var tag2 = reader["Tag2"].ToString();
                    if (tag1 == tag2) continue;
                    var index1 = tags.IndexOf(tag1);
                    var index2 = tags.IndexOf(tag2);

                    //matrix[index1][index2] = matrix[index2][index1] += 1;
                    var view = matrix.CreateViewAccessor();
                    var plusOne = (double)(view.ReadDouble(index1 * tags.Count + index2) + 1);
                    view.Write<double>(index1 * tags.Count + index2, ref plusOne);
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return matrix;
        }


        /// <summary>
        /// Construct matrix according to the connection of tags by events.
        /// </summary>
        /// <param name="matrix"></param>
        /// <returns>Updated matrix with connected tags.</returns>
        public MemoryMappedFile JoinTagsOnEvents(MemoryMappedFile matrix, List<string> tags)
        {
            var eventTags = TopicService.Topic_JoinTagsOnEvents();
            foreach (DataRow row in eventTags.Tables[0].Rows)
            {
                if (row["Topic1"].ToString() == row["Topic2"].ToString()) continue;
                var index1 = tags.IndexOf(row["Topic1"].ToString());
                var index2 = tags.IndexOf(row["Topic2"].ToString());


                var view = matrix.CreateViewAccessor();
                var plusFive = (double)(view.ReadDouble(index1 * tags.Count + index2) + 5);
                view.Write<double>(index1 * tags.Count + index2, ref plusFive);


                var test = view.ReadDouble(104 * tags.Count + 80); // index1 = 104 , index2 = 80

            }
            var view1 = matrix.CreateViewAccessor();
            var test1 = view1.ReadDouble(104 * tags.Count + 80);
            return matrix;
        }


        /// <summary>
        /// Constructs the input matrix for the MCL algorithm.
        /// </summary>
        /// <returns>The resulting matrix ready for clustering.</returns>
        public MemoryMappedFile ConstructTagMatrix()
        {
            var allTopics = TopicService.Topic_GetAllDistinctTopics();
            tags = new List<string>();
            foreach (DataRow topic in allTopics.Tables[0].Rows)
            {
                tags.Add(topic["TopicValue"].ToString());
            }

            var matrix = MemoryMappedFile.CreateNew("tes132345tData", tags.Count * tags.Count);
            var view = matrix.CreateViewAccessor();
            //var matrix = new double[tags.Count][];
            for (int i = 0; i < tags.Count; i++)
            {
                //matrix[i] = new double[tags.Count];
                //matrix[i][i] = 1;
                var one = 1.0;
                view.Write<double>(i * tags.Count + i, ref one);

            }

            matrix = JoinTagsOnUsers(matrix, tags);
            //matrix = JoinTagsOnEvents(matrix, tags);
            return matrix;
        }

        public void GenerateTagClusters()
        {
            try
            {
                var allTopics = TopicService.Topic_GetAllDistinctTopics();
                var size = allTopics.Tables[0].Rows.Count;
                MCLAlgorithm mclAlgorithm = new MCLAlgorithm();

                var matrixConstruction = ConstructTagMatrix();

                var matrixNormalisation = mclAlgorithm.MatrixNormalisation(matrixConstruction, size);

                //var matrixExponential = mclAlgorithm.MatrixProductExponential(matrixNormalisation,
                //    mclAlgorithm.matrixPower, size);

                //var matrixInflation = mclAlgorithm.MatrixInflate(matrixExponential, mclAlgorithm.matrixPower, size);

                //matrixInflation = mclAlgorithm.Repeat(matrixInflation, size);

                //var matrixInterpretated = mclAlgorithm.InterpretResulting(matrixInflation, size);

                //List<List<int>> groups = mclAlgorithm.groups;

                //CleanTagClusters();
                //for (var i = 0; i < groups.Count; i++)
                //{
                //    for (var j = 0; j < groups[i].Count; j++)
                //    {
                //        InsertTagClustersRecord(i + 1, tags[groups[i][j] - 1]);
                //    }
                //}
            }
            catch { }
        }

        public void InsertTagClustersRecord(int tagClusterId, string tag)
        {
            var query = string.Format("INSERT INTO tagging.TagClusters(tagclusterid,tagvalue)" +
                                         " VALUES('{0}','{1}')", tagClusterId, tag);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);
            try
            {
                // Make a connection using the psqlODBC provider.
                conn.Open();

                var da = new OdbcDataAdapter { InsertCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        public void CleanTagClusters()
        {
            var query = string.Format("DELETE FROM tagging.TagClusters");

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);
            try
            {
                // Make a connection using the psqlODBC provider.

                conn.Open();

                var da = new OdbcDataAdapter { DeleteCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region Construct Event Clusters
        //try-catch proverki za da ne otide matricata vo nepovrat

        /// <summary>
        /// Construct matrix according to the connection of topics by events.
        /// </summary>
        /// <param name="matrix"></param>
        /// <returns>Updated matrix with connected tags.</returns>
        public MemoryMappedFile JoinEventsOnTopics(MemoryMappedFile matrix, List<string> tags)
        {
            try
            {
                var eventTopics = EventInfoService.Event_JoinEventsOnTopics();
                var view = matrix.CreateViewAccessor();
                foreach (DataRow row in eventTopics.Tables[0].Rows)
                {

                    if (!tags.Contains(row["EventId1"].ToString()) || !tags.Contains(row["EventId2"].ToString())) continue;
                    if (row["EventId1"].ToString() == row["EventId2"].ToString()) continue;

                    var index1 = tags.IndexOf(row["EventId1"].ToString());
                    var index2 = tags.IndexOf(row["EventId2"].ToString());
                    var plusFive = (double)(view.ReadDouble(index1 * tags.Count + index2) + 5);
                    view.Write<double>(index1 * tags.Count + index2, ref plusFive);
                }
                return matrix;
            }

            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }


        /// <summary>
        /// Construct matrix according to the connection of topics by events.
        /// </summary>
        /// <param name="matrix"></param>
        /// <returns>Updated matrix with connected tags.</returns>
        public MemoryMappedFile JoinEventsOnUsers(MemoryMappedFile matrix, List<string> tags)
        {
            int i = 1;
            try
            {

                var eventUsers = EventInfoService.Event_JoinEventsOnUsers();

                var mmf = MemoryMappedFile.OpenExisting("EventsMemoryMaped3");
                var view = mmf.CreateViewAccessor();
                foreach (DataRow row in eventUsers.Tables[0].Rows)
                {
                    var dar = eventUsers.Tables[0].Rows.Count;
                    i++;
                    var c = i;
                    if (!tags.Contains(row["EventId1"].ToString()) || !tags.Contains(row["EventId2"].ToString())) continue;
                    if (row["EventId1"].ToString() == row["EventId2"].ToString()) continue;

                    var index1 = tags.IndexOf(row["EventId1"].ToString());
                    var index2 = tags.IndexOf(row["EventId2"].ToString());

                    //var plusThree = 3.0;

                    var plusThree = (view.ReadDouble(index1 * tags.Count + index2) + 3);
                    view.Write(index1 * tags.Count + index2, ref plusThree);

                    var test = view.ReadDouble(49 * tags.Count + 54); // index1 = 49 , index2 = 54

                    //if (index1 == 49 && index2 == 54)
                    //{
                    //    var sdas = "";
                    //}


                }

                return matrix;
            }

            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }


        /// <summary>
        /// Constructs the input matrix for the MCL algorithm.
        /// </summary>
        /// <returns>The resulting matrix ready for clustering.</returns>
        public MemoryMappedFile ConstructEventsMatrix()
        {
            try
            {
                var allEvents = TopicService.Topic_GetAllDistinctEvents();

                tags = new List<string>();
                foreach (DataRow singleEvent in allEvents.Tables[0].Rows)
                {
                    tags.Add(singleEvent["EventID"].ToString());
                }

                var matrix = MemoryMappedFile.CreateNew("EventsMemoryMaped3", (long)tags.Count * tags.Count);
                var view = matrix.CreateViewAccessor();
                //var matrix = new double[tags.Count][];

                for (int i = 0; i < (long)tags.Count; ++i)
                {
                    //matrix[i] = new double[tags.Count];
                    //matrix[i][i] = 1;
                    var one = 1.0;

                    view.Write<double>(i * (long)tags.Count + i, ref one);
                }

                matrix = JoinEventsOnUsers(matrix, tags);
                //matrix = JoinEventsOnTopics(matrix, tags);
                return matrix;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        public void GenerateEventClusters()
        {
            MCLAlgorithm mclAlgorithm = new MCLAlgorithm();

            var allEvents = TopicService.Topic_GetAllDistinctEvents();

            var size = allEvents.Tables[0].Rows.Count;

            var matrixConstruction = ConstructEventsMatrix();

            var matrixNormalisation = mclAlgorithm.MatrixNormalisation(matrixConstruction, size);

            //var matrixExponential = mclAlgorithm.MatrixProductExponential(matrixNormalisation,
            //    mclAlgorithm.matrixPower, size);

            //var matrixInflation = mclAlgorithm.MatrixInflate(matrixExponential, mclAlgorithm.matrixPower, size);

            //matrixInflation = mclAlgorithm.Repeat(matrixInflation, size);

            //var matrixInterpretated = mclAlgorithm.InterpretResulting(matrixInflation, size);

            //List<List<int>> groups = mclAlgorithm.groups;

            //CleanEventClusters();

            //for (var i = 0; i < groups.Count; i++)
            //{
            //    for (var j = 0; j < groups[i].Count; j++)
            //    {
            //        InsertEventClustersRecord(i + 1, Convert.ToInt64(tags[groups[i][j] - 1]));
            //    }
            //}
        }

        public void InsertEventClustersRecord(int tagEventClusterId, long eventId)
        {
            var query = string.Format("INSERT INTO tagging.EventClusters(eventclusterid,eventid)" +
                                         " VALUES('{0}','{1}')", tagEventClusterId, eventId);


            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { InsertCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        public void CleanEventClusters()
        {
            var query = string.Format("DELETE FROM tagging.EventClusters");

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            try
            {
                // Make a connection using the psqlODBC provider.
                conn.Open();

                var da = new OdbcDataAdapter { DeleteCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region Get recommended topics for users

        public DataTable GetTagsList(List<string> tags, int numberOfTags)
        {
            var retList = new List<string>();

            var tagClusterIds = GetTopicClusterIds(tags);

            var query = string.Format("SELECT * FROM tagging.TagClusters WHERE tagclusterid={0}", tagClusterIds.FirstOrDefault());
            for (var i = 0; i < tagClusterIds.Count; i++)
                query += string.Format(" OR tagclusterid={0}", tagClusterIds[i]);
            query += string.Format(" ORDER BY RANDOM() LIMIT " + numberOfTags);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var dt = new DataTable();
            dt.Columns.Add(new DataColumn("tagvalue"));

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public List<int> GetTopicClusterIds(List<string> tags)
        {
            if (!tags.Any())
                return new List<int>();
            var retList = new List<int>();
            var query = string.Format("SELECT * FROM tagging.TagClusters WHERE tagvalue='{0}'", tags.FirstOrDefault());
            for (var i = 1; i < tags.Count; i++)
                query += string.Format(" OR tagvalue='{0}'", tags[i]);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };

                var reader = da.SelectCommand.ExecuteReader();


                while (reader.Read())
                {
                    retList.Add(int.Parse(reader["tagclusterid"].ToString()));
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return retList;
        }

        public Dictionary<string, int> GetRecommendedTagsWithCount(List<string> tags, int numberOfTags)
        {
            var retDict = new Dictionary<string, int>();
            var topTags = GetTagsList(tags, numberOfTags);

            foreach (DataRow tag in topTags.Rows)
            {
                try
                {
                    int count;
                    retDict[tag["tagvalue"].ToString()] = TopicService.GetTotalItems(string.Format("TopicValue = '{0}'", tag), out count);
                }
                catch (Exception exc) { }
            }

            return retDict;
        }

        public Dictionary<string, int> GetRecommendedTopicsForUser(string userId, int numberOfTags)
        {
            Dictionary<string, int> retDict = null;
            var topicsList = new List<string>();
            var queryEvents = string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1}) AND userid='{2}' GROUP BY externalentityid ORDER BY num DESC LIMIT {3}", (int)ActivityTypeEnumerator.EventClick, (int)ActivityTypeEnumerator.EventFollowClick, userId, numberOfTags);
            var queryTopics = string.Format("SELECT actlog.activityvalue AS TopicValue, COUNT(actlog.activityvalue) AS num FROM tagging.activityLog actlog WHERE actlog.ClickType={0} AND userid='{1}' GROUP BY actlog.activityvalue ORDER BY num DESC LIMIT {2}", (int)ActivityTypeEnumerator.TagClick, userId, numberOfTags);
            var queryRandomTopics = string.Format("SELECT tagvalue AS TopicValue FROM tagging.tagclusters ORDER BY RANDOM() LIMIT {0}", numberOfTags);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();

                var da = new OdbcDataAdapter
                {
                    SelectCommand = new OdbcCommand { CommandText = queryTopics, Connection = conn }
                };

                var dt1 = new DataTable();
                var topicValueColumn = new DataColumn("TopicValue", Type.GetType("System.String"));
                dt1.Columns.Add(topicValueColumn);
                dt1.PrimaryKey = new[] { topicValueColumn };
                da.Fill(dt1);
                topicsList.AddRange((from DataRow dataRow in dt1.Rows select dataRow["TopicValue"].ToString()));

                if (topicsList.Count < numberOfTags)
                {
                    da = new OdbcDataAdapter
                    {
                        SelectCommand = new OdbcCommand { CommandText = queryEvents, Connection = conn }
                    };

                    var dt2 = new DataTable();
                    var externalEntityIdColumn = new DataColumn("externalentityid", Type.GetType("System.String"));
                    dt2.Columns.Add(externalEntityIdColumn);
                    da.Fill(dt2);

                    var tListTopics = (from DataRow dataRow in dt2.Rows
                                       select TopicService.Find(string.Format("RelatedEntityId = {0}", dataRow["externalentityid"]))).Aggregate(new TList<Topic>(),
                                           (res, list) =>
                                           {
                                               res.AddRange(list);
                                               return res;
                                           });
                    topicsList.AddRange(tListTopics.Where(x => !topicsList.Contains(x.TopicValue)).TakeWhile((member, index) => topicsList.Count <= numberOfTags).Select(x => x.TopicValue));

                    if (topicsList.Count < numberOfTags)
                    {
                        da = new OdbcDataAdapter
                        {
                            SelectCommand = new OdbcCommand { CommandText = queryRandomTopics, Connection = conn }
                        };
                        var dt3 = new DataTable();
                        var topicValueColumnDt3 = new DataColumn("TopicValue", Type.GetType("System.String"));
                        dt3.Columns.Add(topicValueColumnDt3);
                        dt3.PrimaryKey = new[] { topicValueColumnDt3 };
                        da.Fill(dt3);
                        topicsList.AddRange((from DataRow dataRow in dt3.Rows where !topicsList.Contains(dataRow["TopicValue"].ToString()) select dataRow["TopicValue"].ToString())
                                  .TakeWhile((member, index) => topicsList.Count <= numberOfTags));
                    }
                }
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }

            retDict = new Dictionary<string, int>();
            foreach (var tag in topicsList)
            {
                int count;
                retDict[tag] = TopicService.GetTotalItems(string.Format("TopicValue = '{0}'", tag), out count);
            }

            return retDict;
        }

        public Dictionary<string, int> GetTrendingTopics(TopicsEnumerator type, int numberOfTags, int lastMonths)
        {
            var st1 = new Stopwatch();

            Dictionary<string, int> retDict = null;
            var topicsList = new List<string>();
            var queryContents =
                type == TopicsEnumerator.User ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{2}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.SpeakerClick, (int)ActivityTypeEnumerator.UserFollowClick, lastMonths) :
                type == TopicsEnumerator.Event ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{2}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.EventClick, (int)ActivityTypeEnumerator.EventFollowClick, lastMonths) :
                type == TopicsEnumerator.Video ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.VideoView, (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment, (int)ActivityTypeEnumerator.VideoShare, lastMonths) :
                type == TopicsEnumerator.Image ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.ImageView, (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment, (int)ActivityTypeEnumerator.ImageShare, lastMonths) :
                type == TopicsEnumerator.Slide ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.SlideView, (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment, (int)ActivityTypeEnumerator.SlideShare, lastMonths) :
                string.Format(@"SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog
                                WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}
                                OR ClickType={4} OR ClickType={5} OR ClickType={6} OR ClickType={7}
                                OR ClickType={8} OR ClickType={9} OR ClickType={10} OR ClickType={11}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{12}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50",
                                (int)ActivityTypeEnumerator.VideoView, (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment, (int)ActivityTypeEnumerator.VideoShare,
                                (int)ActivityTypeEnumerator.ImageView, (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment, (int)ActivityTypeEnumerator.ImageShare,
                                (int)ActivityTypeEnumerator.SlideView, (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment, (int)ActivityTypeEnumerator.SlideShare,
                                lastMonths);
            var queryAllContents = type == TopicsEnumerator.Event ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{2}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.EventClick, (int)ActivityTypeEnumerator.EventFollowClick, lastMonths) :
                type == TopicsEnumerator.Video ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.VideoView, (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment, (int)ActivityTypeEnumerator.VideoShare, lastMonths) :
                type == TopicsEnumerator.Image ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.ImageView, (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment, (int)ActivityTypeEnumerator.ImageShare, lastMonths) :
                type == TopicsEnumerator.Slide ?
                string.Format("SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{4}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50", (int)ActivityTypeEnumerator.SlideView, (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment, (int)ActivityTypeEnumerator.SlideShare, lastMonths) :
                string.Format(@"SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog
                                WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}
                                OR ClickType={4} OR ClickType={5} OR ClickType={6} OR ClickType={7}
                                OR ClickType={8} OR ClickType={9} OR ClickType={10} OR ClickType={11}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{12}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50",
                                (int)ActivityTypeEnumerator.VideoView, (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment, (int)ActivityTypeEnumerator.VideoShare,
                                (int)ActivityTypeEnumerator.ImageView, (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment, (int)ActivityTypeEnumerator.ImageShare,
                                (int)ActivityTypeEnumerator.SlideView, (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment, (int)ActivityTypeEnumerator.SlideShare,
                                lastMonths);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            st1.Start();
            try
            {
                conn.Open();
                var da = new OdbcDataAdapter
                {
                    SelectCommand = new OdbcCommand { CommandText = queryContents, Connection = conn }
                };

                Debug.WriteLine("Open conn and execute query : " + st1.ElapsedMilliseconds);

                st1.Reset();
                st1.Start();
                var dt2 = new DataTable();
                var externalColumn2 = new DataColumn("externalentityid", Type.GetType("System.String"));
                dt2.Columns.Add(externalColumn2);
                dt2.PrimaryKey = new[] { externalColumn2 };
                da.Fill(dt2);

                foreach (DataRow row in dt2.Rows)
                {
                    var contentId = row["externalentityid"].ToString();
                    int count;
                    var contentWithTopics =
                        type != TopicsEnumerator.AllMedia
                        ? TopicService.Find(string.Format("RelatedEntityId = {0} AND RelatedEntityType={1}", contentId,
                            (int)type))
                        : TopicService.GetPaged(
                            string.Format(
                                "RelatedEntityId = {0} AND (RelatedEntityType={1} OR RelatedEntityType={2} OR RelatedEntityType={3})",
                                contentId, (int)TopicsEnumerator.Video, (int)TopicsEnumerator.Image,
                                (int)TopicsEnumerator.Slide), "", 0, numberOfTags, out count);
                    if (contentWithTopics == null || !contentWithTopics.Any()) continue;
                    foreach (var item in contentWithTopics.TakeWhile(item => topicsList.Count() != numberOfTags).Where(item => !topicsList.Contains(item.TopicValue)))
                    {
                        topicsList.Add(item.TopicValue);
                    }
                }

                Debug.WriteLine("Iterate through results from AR : " + st1.ElapsedMilliseconds);

                if (topicsList.Count() < numberOfTags)
                {
                    int remained;
                    var topicsRemained = TopicService.GetPaged(string.Format("RelatedEntityType={0}", (int)type), "", 0,
                        numberOfTags, out remained);
                    foreach (
                        var item in
                            topicsRemained.Where(item => !topicsList.Contains(item.TopicValue))
                                .TakeWhile(item => topicsList.Count() < numberOfTags))
                    {
                        topicsList.Add(item.TopicValue);
                    }
                }
                st1.Reset();
                st1.Start();
                retDict = new Dictionary<string, int>();
                if (type != TopicsEnumerator.AllMedia && type != TopicsEnumerator.User && type != TopicsEnumerator.Event)
                    retDict = MediaService.GetTrendingTopicsForMedia((int)type).Tables[0].Rows.Cast<DataRow>().ToDictionary(row => row["TopicValue"].ToString(), row => int.Parse(row["CntTopics"].ToString()));

                // todo: da se proveri koga type=event dali moze da se izvadat site rezultati so 1 povik do baza
                foreach (var tag in topicsList)
                {
                    int count;
                    switch (type)
                    {
                        case TopicsEnumerator.AllMedia:
                            retDict[tag] = TopicService.GetTotalItems(
                            string.Format(
                                "TopicValue = '{0}' AND (RelatedEntityType='{1}' OR RelatedEntityType='{2}' OR RelatedEntityType='{3}')",
                                tag, (int)TopicsEnumerator.Video, (int)TopicsEnumerator.Image,
                                (int)TopicsEnumerator.Slide), out count);
                            break;
                        case TopicsEnumerator.User:
                        case TopicsEnumerator.Event:
                            count = TopicService.GetEventCountByTopicValueAndRelatedEntityType(tag, (int)type, 0);
                            retDict[tag] = count;
                            break;
                        //case TopicsEnumerator.Video:
                        //    count = TopicService.GetEventCountByTopicValueAndRelatedEntityType(tag, (int)type, (int)MediaTypeEnum.Video);
                        //    retDict[tag] = count;
                        //    break;
                        //case TopicsEnumerator.Slide:
                        //    count = TopicService.GetEventCountByTopicValueAndRelatedEntityType(tag, (int)type, (int)MediaTypeEnum.Slide);
                        //    retDict[tag] = count;
                        //    break;
                        //case TopicsEnumerator.Image:
                        //    count = TopicService.GetEventCountByTopicValueAndRelatedEntityType(tag, (int)type, (int)MediaTypeEnum.Image);
                        //    retDict[tag] = count;
                        //    break;
                    }
                }
                st1.Stop();
                Debug.WriteLine("Iterate through each topic and get the count : " + st1.ElapsedMilliseconds);

            }
            catch (Exception ex)
            {
                LogUtility.LogException(ex);
            }
            finally
            {
                conn.Close();
            }

            return retDict ?? GetRecommendedTagsWithCount(topicsList, numberOfTags);
        }

        public Dictionary<string, int> GetMostlyClickedTopicsForUser(string userId, int lastMonths, int numberOfTags, bool countTopicsNeeded = false, OdbcConnection openConnection = null)
        {
            Dictionary<string, int> retDict = null;
            var topicsList = new List<string>();
            var queryTopics = string.Format("SELECT actlog.activityvalue, COUNT(actlog.activityvalue) AS num FROM tagging.activityLog actlog WHERE actlog.ClickType={0} AND userid='{1}' AND actiondate > ADD_MONTHS(CURRENT_DATE,-{2}) GROUP BY actlog.activityvalue ORDER BY num DESC LIMIT {3}", (int)ActivityTypeEnumerator.TagClick, userId, lastMonths, numberOfTags);
            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = openConnection ?? new OdbcConnection(connString);

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                var da = new OdbcDataAdapter
                {
                    SelectCommand = new OdbcCommand { CommandText = queryTopics, Connection = conn }
                };

                var dt1 = new DataTable();
                var activityValueColumn = new DataColumn("activityvalue", Type.GetType("System.String"));
                dt1.Columns.Add(activityValueColumn);
                dt1.PrimaryKey = new[] { activityValueColumn };
                da.Fill(dt1);

                retDict = new Dictionary<string, int>();
                foreach (DataRow row in dt1.Rows)
                {
                    int count;
                    var item = row["activityvalue"].ToString();
                    retDict[item] = countTopicsNeeded ? TopicService.GetTotalItems(string.Format("TopicValue = '{0}'", item), out count) : 0;
                }
            }
            catch (Exception ex)
            {
                LogUtility.LogException(ex);
            }
            finally
            {
                conn.Close();
            }
            return retDict;
        }

        public List<string> GetMostPopularTopics(int lastMonths, int numberOfTags)
        {
            var topicsList = new List<string>();
            var queryAllContents =
                string.Format(
                @"SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}
                OR ClickType={4} OR ClickType={5} OR ClickType={6} OR ClickType={7}
                OR ClickType={8} OR ClickType={9} OR ClickType={10} OR ClickType={11}) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{12}) GROUP BY externalentityid ORDER BY num DESC LIMIT 50",
                (int)ActivityTypeEnumerator.VideoView, (int)ActivityTypeEnumerator.VideoLike,
                (int)ActivityTypeEnumerator.VideoComment, (int)ActivityTypeEnumerator.VideoShare,
                (int)ActivityTypeEnumerator.ImageView, (int)ActivityTypeEnumerator.ImageLike,
                (int)ActivityTypeEnumerator.ImageComment, (int)ActivityTypeEnumerator.ImageShare,
                (int)ActivityTypeEnumerator.SlideView, (int)ActivityTypeEnumerator.SlideLike,
                (int)ActivityTypeEnumerator.SlideComment, (int)ActivityTypeEnumerator.SlideShare,
                lastMonths);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();

                var da = new OdbcDataAdapter
                {
                    SelectCommand = new OdbcCommand { CommandText = queryAllContents, Connection = conn }
                };

                var dt1 = new DataTable();
                var externalColumn = new DataColumn("externalentityid", Type.GetType("System.String"));
                dt1.Columns.Add(externalColumn);
                dt1.PrimaryKey = new[] { externalColumn };
                da.Fill(dt1);

                foreach (DataRow row in dt1.Rows)
                {
                    var contentId = row["externalentityid"].ToString();
                    int count;
                    var contentsWithTopics = TopicService.GetPaged(
                        string.Format(
                            "RelatedEntityId = '{0}' AND (RelatedEntityType='{1}' OR RelatedEntityType='{2}' OR RelatedEntityType='{3}')",
                            contentId, (int)TopicsEnumerator.Video, (int)TopicsEnumerator.Image,
                            (int)TopicsEnumerator.Slide), "RelatedEntityId", 0, 50, out count);
                    if (contentsWithTopics == null || !contentsWithTopics.Any()) continue;
                    foreach (
                        var item in
                            contentsWithTopics.TakeWhile(item => topicsList.Count() != numberOfTags)
                                .Where(item => !topicsList.Contains(item.TopicValue)))
                    {
                        topicsList.Add(item.TopicValue);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return topicsList;
        }

        #endregion

        #region Get recommended Events for users

        public DataTable GetEventsList(List<string> events, int numberOfEvents)
        {
            var retList = new List<string>();

            var eventClusterIds = GetEventClusterIds(events);

            var query = string.Format("SELECT * FROM tagging.EventClusters WHERE eventclusterid={0}", eventClusterIds.FirstOrDefault());
            for (var i = 0; i < eventClusterIds.Count; i++)
                query += string.Format(" OR eventclusterid={0}", eventClusterIds[i]);
            query += string.Format(" ORDER BY RANDOM() LIMIT " + numberOfEvents);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var dt1 = new DataTable();
            var eventIdColumn = new DataColumn("eventid", Type.GetType("System.String"));
            dt1.Columns.Add(eventIdColumn);
            dt1.PrimaryKey = new[] { eventIdColumn };

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return dt1;
        }

        public List<int> GetEventClusterIds(List<string> events)
        {
            if (!events.Any())
                return new List<int>();
            var retList = new List<int>();
            var query = string.Format("SELECT * FROM tagging.EventClusters WHERE eventid='{0}'", events.FirstOrDefault());
            for (var i = 1; i < events.Count; i++)
                query += string.Format(" OR eventid='{0}'", events[i]);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var dt1 = new DataTable();
            var eventIdColumn = new DataColumn("eventid", Type.GetType("System.String"));
            dt1.Columns.Add(eventIdColumn);
            dt1.PrimaryKey = new[] { eventIdColumn };

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return (from DataRow row in dt1.Rows
                    select int.Parse(row[""].ToString())).ToList();
        }

        public DataTable GetRecommendedEventsTable(string topics, int numberOfEvents, DateTime dateTo, double minLat = 0.0, double maxLat = 0.0, double minLon = 0.0, double maxLon = 0.0)
        {
            if (topics == null) return new DataTable();

            var result =
                TopicService.Event_GetEventsForConferenceWidget(
                    "\'" + topics + "\'", numberOfEvents, dateTo, minLat,
                    maxLat, minLon, maxLon);
            if (result != null && result.Tables.Count > 0)
                return result.Tables[0];
            return new DataTable();
        }

        #endregion

        #region People

        /// <summary>
        /// Construct matrix according to the connection of users by events.
        /// </summary>
        /// <param name="matrix"></param>
        /// <returns>Updated matrix with connected tags.</returns>
        public double[][] JoinUsersOnEvents(double[][] matrix, List<string> tags)
        {
            try
            {
                var userEvents = UserService.User_JoinUsersOnEvents();

                foreach (DataRow row in userEvents.Tables[0].Rows)
                {
                    if (!tags.Contains(row["UserId1"].ToString()) || !tags.Contains(row["UserId2"].ToString())) continue;
                    if (row["UserId1"].ToString() == row["UserId2"].ToString()) continue;

                    var index1 = tags.IndexOf(row["UserId1"].ToString());
                    var index2 = tags.IndexOf(row["UserId2"].ToString());
                    matrix[index1][index2] = matrix[index2][index1] += 3;
                }
                return matrix;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        public double[][] UsersFollowUsers(double[][] matrix, List<string> tags)
        {
            try
            {
                var followUsers = UserFollowService.GetAll().ToList();

                foreach (var row in followUsers)
                {
                    if (!tags.Contains(row.UserId.ToString()) || !tags.Contains(row.FollowedUserId.ToString()))
                        continue;
                    if (row.UserId.ToString() == row.FollowedUserId.ToString()) continue;

                    var index1 = tags.IndexOf(row.UserId.ToString());
                    var index2 = tags.IndexOf(row.FollowedUserId.ToString());
                    matrix[index1][index2] = matrix[index2][index1] += 5;
                }
                return matrix;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

        }

        public double[][] ConstructUsersMatrix()
        {
            try
            {
                var allUsers = UserService.GetAll().Distinct().ToList();

                tags = new List<string>();
                foreach (var singleUser in allUsers)
                {
                    tags.Add(singleUser.UserId.ToString());
                }

                var matrix = new double[allUsers.Count()][];
                for (int i = 0; i < allUsers.Count(); i++)
                {
                    matrix[i] = new double[allUsers.Count];
                    matrix[i][i] = 1;
                }

                matrix = JoinUsersOnEvents(matrix, tags);
                matrix = UsersFollowUsers(matrix, tags);
                return matrix;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        public void GenerateUserClusters()
        {
            MCLAlgorithm mclAlgorithm = new MCLAlgorithm();

            var matrixConstruction = ConstructUsersMatrix();

            //var matrixNormalisation = mclAlgorithm.MatrixNormalisation(matrixConstruction);

            //var matrixExponential = mclAlgorithm.MatrixProductExponential(matrixNormalisation, mclAlgorithm.matrixPower);

            //var matrixInflation = mclAlgorithm.MatrixInflate(matrixExponential, mclAlgorithm.matrixPower);

            //matrixInflation = mclAlgorithm.Repeat(matrixInflation);

            //var matrixInterpretated = mclAlgorithm.InterpretResulting(matrixInflation);

            List<List<int>> groups = mclAlgorithm.groups;

            CleanUserClusters();

            for (var i = 0; i < groups.Count; i++)
            {
                for (var j = 0; j < groups[i].Count; j++)
                {
                    InsertEventClustersRecord(i + 1, Convert.ToInt64(tags[groups[i][j] - 1]));
                }
            }
        }

        public void InsertUserClustersRecord(int tagUserClusterId, Guid userId)
        {
            var query = string.Format("INSERT INTO tagging.UserClusters(userClusterId,userId)" +
                                         " VALUES('{0}','{1}')", tagUserClusterId, userId);


            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { InsertCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        public void CleanUserClusters()
        {
            var query = string.Format("DELETE FROM tagging.UserClusters");

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { DeleteCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet GetUserRecentActivitiesObjects(string userId)
        {
            Stopwatch st = new Stopwatch();
            var topicsList = new List<string>();

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var conn = new OdbcConnection(connString);


            DataSet ds = new DataSet();
            try
            {
                st.Start();
                conn.Open();
                st.Stop();
                Debug.WriteLine(" 1 1 " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();
                #region events




                Dictionary<string, int> clickedTopics = new Dictionary<string, int>();

                DataTable clickedEventIds = new DataTable();
                DataTable eventsFromTopics = new DataTable();

                DataColumn eventIdColumn = new DataColumn("EventId");
                eventIdColumn.DataType = System.Type.GetType("System.Int64");

                clickedEventIds.Columns.Add(eventIdColumn);

                DataColumn popularityColumn = new DataColumn("PopularityValue");
                popularityColumn.DataType = System.Type.GetType("System.Double");

                clickedEventIds.Columns.Add(popularityColumn);

                clickedEventIds.PrimaryKey = new DataColumn[] { eventIdColumn };



                var queryEvents = string.Format("select top 20 externalentityid as EventId, count(externalentityid) as PopularityValue from tagging.activityLog WHERE userid = '{0}' and clicktype in ({1},{2}) group by externalentityid order by PopularityValue desc;", userId, (int)ActivityTypeEnumerator.EventClick, (int)ActivityTypeEnumerator.EventFollowClick);



                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = queryEvents, Connection = conn } };
                //var reader = da.SelectCommand.ExecuteReader();

                da.Fill(clickedEventIds);
                st.Stop();
                Debug.WriteLine(" 1 clickedEventIds " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();

                //while (reader.Read())
                //{
                //  var clicked = reader["externalentityid"].ToString();
                //  DataRow dr = clickedEventIds.NewRow();

                //  dr["EventId"] = clicked;
                //  clickedEventIds.Rows.Add(dr);

                //}



                clickedTopics = GetMostlyClickedTopicsForUser(userId, 1, 20, false, conn);
                st.Stop();
                Debug.WriteLine(" 1 get mostly clicked topics " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();

                DataTable dt = GetRecommendedEventsTable(String.Join(",", clickedTopics.Select(x => x.Key)), 15, DateTime.Now.AddMonths(3));

                st.Stop();
                Debug.WriteLine(" 1 get recommended events " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();

                eventsFromTopics = new DataView(dt).ToTable(false, "EventId");
                clickedEventIds.Merge(eventsFromTopics);


                #endregion
                st.Stop();
                Debug.WriteLine(" 1 2 " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();
                #region users
                DataTable finalUsersList = new DataTable();

                DataColumn UserIdColumn = new DataColumn("UserId");
                UserIdColumn.DataType = System.Type.GetType("System.Guid");

                finalUsersList.Columns.Add(UserIdColumn);
                finalUsersList.PrimaryKey = new DataColumn[] { UserIdColumn };

                DataColumn UserpopularityColumn = new DataColumn("PopularityValue");
                UserpopularityColumn.DataType = System.Type.GetType("System.Double");

                finalUsersList.Columns.Add(UserpopularityColumn);

                var queryUsers = string.Format("Select top 20 activityvalue as UserId, count(activityvalue) as PopularityValue FROM tagging.activityLog Where clicktype = {0} and userid = '{1}' group by activityvalue order by PopularityValue desc ", (int)ActivityTypeEnumerator.UserFollowClick, userId);

                da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = queryUsers, Connection = conn } };

                da.Fill(finalUsersList);


                #endregion
                st.Stop();
                Debug.WriteLine(" 1 3 " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();
                #region Media
                DataTable finalMediaList = new DataTable();

                DataColumn MediaIdColumn = new DataColumn("MediaId");
                MediaIdColumn.DataType = System.Type.GetType("System.Int64");

                finalMediaList.Columns.Add(MediaIdColumn);
                finalMediaList.PrimaryKey = new DataColumn[] { MediaIdColumn };

                DataColumn mediaPopularityColumn = new DataColumn("PopularityValue");
                mediaPopularityColumn.DataType = System.Type.GetType("System.Double");

                finalMediaList.Columns.Add(mediaPopularityColumn);

                var queryMedia = string.Format("Select top 20 externalentityid as MediaId, count(externalentityid) as PopularityValue FROM tagging.activityLog Where clicktype in ({0},{1}) and userid = '{2}' group by externalentityid order by PopularityValue desc ", (int)ActivityTypeEnumerator.PresentationClick, (int)ActivityTypeEnumerator.VideoClick, userId);

                da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = queryMedia, Connection = conn } };
                //reader = da.SelectCommand.ExecuteReader();
                da.Fill(finalMediaList);



                #endregion
                st.Stop();
                Debug.WriteLine(" 1 4 " + st.ElapsedMilliseconds);
                st.Reset();

                st.Start();
                ds.Tables.Add(clickedEventIds);
                ds.Tables.Add(finalUsersList);
                ds.Tables.Add(finalMediaList);
                st.Stop();
                Debug.WriteLine(" 1 1 " + st.ElapsedMilliseconds);
                st.Reset();
                //dodadi gi site listi vo datatable;

                //ds = GetUserObjectsActivityWall(userId);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return ds;
        }

        public DataSet GetUserObjectsActivityWall(string userId, GeoHelper geoHelperInstanca)
        {

            SystemUserService systemUser = new SystemUserService();
            MediaService mediaService = new MediaService();
            //TODO:(dimkov) create logic to add popularity
            DataSet ds = new DataSet();
            Stopwatch st = new Stopwatch();
            st.Start();
            ds = GetUserRecentActivitiesObjects(userId);
            st.Stop();
            Debug.WriteLine(" 1 " + st.ElapsedMilliseconds);
            st.Reset();

            //GeoHelper geoHelper = new GeoHelper(GeoHelperDatabaseFile);
            GeoHelper geoHelper = geoHelperInstanca ?? new GeoHelper(GeoHelperDatabaseFile);

            double[] latLong = geoHelper.GetlatLong();
            double[] latLongExpanded = geoHelper.GetNearby(latLong[0], latLong[1], 300);
            double minLat = latLongExpanded[0];
            double maxLat = latLongExpanded[1];
            double minLong = latLongExpanded[2];
            double maxLong = latLongExpanded[3];
            st.Start();
            var rEvents = EventInfoService.GetRecommendedByLocationAndPopularity(DateTime.Now.AddMonths(-2),
                DateTime.Now.AddMonths(3), minLat, maxLat, minLong, maxLong);
            var rUsers = systemUser.GetRecommendedByLocationAdnPopularity(minLat, maxLat, minLong, maxLong);
            var rMedia = mediaService.GetByPopularityAndLocation(DateTime.Now.AddMonths(-2), DateTime.Now, minLat,
                maxLat, minLong, maxLong);
            st.Stop();
            Debug.WriteLine(" 2 " + st.ElapsedMilliseconds);
            st.Reset();

            st.Start();
            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].Merge(rEvents.Tables[0]);
            }
            else
            {
                ds.Tables.Add("Table1");
                ds.Tables[0].Merge(rEvents.Tables[0]);
            }

            if (ds.Tables.Count > 1)
            {

                ds.Tables[1].Merge(rUsers.Tables[0]);
            }
            else
            {
                ds.Tables.Add("Table2");
                ds.Tables[1].Merge(rUsers.Tables[0]);


            }
            if (ds.Tables.Count > 2)
            {
                ds.Tables[2].Merge(rMedia.Tables[0]);
            }
            else
            {
                ds.Tables.Add("Table3");
                ds.Tables[2].Merge(rMedia.Tables[0]);


            }
            st.Stop();
            Debug.WriteLine(" 3 " + st.ElapsedMilliseconds);
            st.Reset();
            return ds;
        }


        #endregion

        #region Get recommended media for user

        public List<long> GetMediaList(List<string> media, int numberOfMedia, MediaTypeEnum type)
        {
            var mediaClusterIds = GetMediaClusterIds(media, type);

            var query =
                type == MediaTypeEnum.Video ?
                string.Format("SELECT * FROM tagging.VideoClusters WHERE videoclusterid={0}", mediaClusterIds.FirstOrDefault()) :
                type == MediaTypeEnum.Image ?
                string.Format("SELECT * FROM tagging.ImageClusters WHERE imageclusterid={0}", mediaClusterIds.FirstOrDefault()) :
                string.Format("SELECT * FROM tagging.SlideClusters WHERE slideclusterid={0}", mediaClusterIds.FirstOrDefault());
            for (var i = 0; i < mediaClusterIds.Count; i++)
                query +=
                    type == MediaTypeEnum.Video ?
                    string.Format(" OR videoclusterid={0}", mediaClusterIds[i]) :
                    type == MediaTypeEnum.Image ?
                    string.Format(" OR imageclusterid={0}", mediaClusterIds[i]) :
                    string.Format(" OR slideclusterid={0}", mediaClusterIds[i]);
            query += string.Format(" ORDER BY RANDOM() LIMIT " + numberOfMedia);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var index = type == MediaTypeEnum.Video
                    ? "videoid"
                    : type == MediaTypeEnum.Image ? "imageid" : "slideid";

            var dt1 = new DataTable();
            var mediaColumn = new DataColumn(index);
            dt1.Columns.Add(mediaColumn);
            dt1.PrimaryKey = new[] { mediaColumn };

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return (from DataRow row in dt1.Rows
                    select long.Parse(row[index].ToString())).ToList();
        }

        public List<int> GetMediaClusterIds(List<string> media, MediaTypeEnum type)
        {
            if (!media.Any())
                return new List<int>();
            var query =
                type == MediaTypeEnum.Video ?
                string.Format("SELECT * FROM tagging.VideoClusters WHERE videoid='{0}'", media.FirstOrDefault()) :
                type == MediaTypeEnum.Image ?
                string.Format("SELECT * FROM tagging.ImageClusters WHERE imageid='{0}'", media.FirstOrDefault()) :
                string.Format("SELECT * FROM tagging.SlideClusters WHERE slideid='{0}'", media.FirstOrDefault());
            for (var i = 1; i < media.Count; i++)
                query +=
                    type == MediaTypeEnum.Video ?
                    string.Format(" OR videoid='{0}'", media[i]) :
                    type == MediaTypeEnum.Image ?
                    string.Format(" OR imageid='{0}'", media[i]) :
                    string.Format(" OR slideid='{0}'", media[i]);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var index = type == MediaTypeEnum.Video
                    ? "videoclusterid"
                    : type == MediaTypeEnum.Image ? "imageclusterid" : "slideclusterid";

            var dt1 = new DataTable();
            var mediaColumn = new DataColumn(index);
            dt1.Columns.Add(mediaColumn);
            dt1.PrimaryKey = new[] { mediaColumn };

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return (from DataRow row in dt1.Rows
                    select int.Parse(row[index].ToString())).ToList();
        }

        public List<long> GetRecommendedMediaForUser(string userId, int numberOfMedia, MediaTypeEnum type, int lastMonths, string topics)
        {
            var mediaList = new List<string>();
            var recomMedia = new List<long>();
            const string queryString = "SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND userid='{4}' AND (','+related_topics+',' LIKE '%,{5},%' ) AND actiondate > ADD_MONTHS(CURRENT_DATE,-{6}) GROUP BY externalentityid ORDER BY num DESC LIMIT 30";

            var queryRandomMedia =
                type == MediaTypeEnum.Video ?
                string.Format("SELECT videoid FROM tagging.videoclusters ORDER BY RANDOM() LIMIT {0}", numberOfMedia) :
                type == MediaTypeEnum.Image ?
                string.Format("SELECT imageid FROM tagging.imageclusters ORDER BY RANDOM() LIMIT {0}", numberOfMedia) :
                string.Format("SELECT slideid FROM tagging.slideclusters ORDER BY RANDOM() LIMIT {0}", numberOfMedia);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var index = type == MediaTypeEnum.Video
                        ? "videoid"
                        : type == MediaTypeEnum.Image ? "imageid" : "slideid";

            try
            {
                conn.Open();

                foreach (var topic in topics.Split(new[] { ',' }))
                {
                    var queryEvents =
                        type == MediaTypeEnum.Video
                            ? string.Format(queryString, (int)ActivityTypeEnumerator.VideoView,
                                (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment,
                                (int)ActivityTypeEnumerator.VideoShare, userId, topic, lastMonths)
                            : type == MediaTypeEnum.Image
                                ? string.Format(queryString, (int)ActivityTypeEnumerator.ImageView,
                                    (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment,
                                    (int)ActivityTypeEnumerator.ImageShare, userId, topic, lastMonths)
                                : string.Format(queryString, (int)ActivityTypeEnumerator.SlideView,
                                    (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment,
                                    (int)ActivityTypeEnumerator.SlideShare, userId, topic, lastMonths);

                    var da = new OdbcDataAdapter
                    {
                        SelectCommand = new OdbcCommand { CommandText = queryEvents, Connection = conn }
                    };

                    var dt1 = new DataTable();
                    var externalColumn = new DataColumn(index, Type.GetType("System.String"));
                    dt1.Columns.Add(externalColumn);

                    // ova pravi problem kaj auth user i paga celiot metod... ako misles deka treba da se promeni, slobodno
                    //dt1.PrimaryKey = new[] { externalColumn };

                    da.Fill(dt1);

                    mediaList.AddRange((from DataRow row in dt1.Rows
                                        select row[index].ToString()).Distinct());
                }

                recomMedia = GetMediaList(mediaList, numberOfMedia, type);

                if (string.IsNullOrEmpty(topics) && mediaList.Count < numberOfMedia)
                {
                    var da = new OdbcDataAdapter
                    {
                        SelectCommand = new OdbcCommand { CommandText = queryRandomMedia, Connection = conn }
                    };

                    var dt2 = new DataTable();
                    var externalColumn2 = new DataColumn(index, Type.GetType("System.String"));
                    dt2.Columns.Add(externalColumn2);
                    da.Fill(dt2);

                    recomMedia.AddRange(
                        (from DataRow row in dt2.Rows
                         select long.Parse(row[index].ToString())).Distinct().TakeWhile((member, ind) => recomMedia.Count <= numberOfMedia)
                        );
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return recomMedia;
        }

        /// <summary>
        /// Get recommended data for user for Media page. It retrieves 3 tables (videos, pictures and slides) in a DataSet.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="numberOfMedia"></param>
        /// <param name="lastMonths"></param>
        /// <returns>DataSet</returns>
        public DataSet GetRecommendedMediaForUsersDataSet(string userId, int numberOfMedia, int lastMonths = 3)
        {
            var mediaLists = GetRecommendedMediaLists(userId, lastMonths);
            return MediaService.GetDataForRecommendedMedia(mediaLists[0], mediaLists[1], mediaLists[2], Guid.Parse(userId));
        }

        public List<string> GetRecommendedMediaLists(string userId, int lastMonths = 3)
        {
            const string queryString = "SELECT externalentityid, COUNT(externalentityid) AS num FROM tagging.activityLog WHERE (ClickType={0} OR ClickType={1} OR ClickType={2} OR ClickType={3}) AND userid='{4}' AND actiondate > ADD_MONTHS(CURRENT_DATE,-{5}) GROUP BY externalentityid ORDER BY num DESC LIMIT 30";

            var enumList = new[] { MediaTypeEnum.Video, MediaTypeEnum.Image, MediaTypeEnum.Slide };
            var mediaLists = new List<string>();
            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            try
            {
                conn.Open();

                foreach (var type in enumList)
                {
                    var index = type == MediaTypeEnum.Video
                        ? "videoid"
                        : type == MediaTypeEnum.Image ? "imageid" : "slideid";

                    var queryEvents =
                        type == MediaTypeEnum.Video
                            ? string.Format(queryString, (int)ActivityTypeEnumerator.VideoView,
                                (int)ActivityTypeEnumerator.VideoLike, (int)ActivityTypeEnumerator.VideoComment,
                                (int)ActivityTypeEnumerator.VideoShare, userId, lastMonths)
                            : type == MediaTypeEnum.Image
                                ? string.Format(queryString, (int)ActivityTypeEnumerator.ImageView,
                                    (int)ActivityTypeEnumerator.ImageLike, (int)ActivityTypeEnumerator.ImageComment,
                                    (int)ActivityTypeEnumerator.ImageShare, userId, lastMonths)
                                : string.Format(queryString, (int)ActivityTypeEnumerator.SlideView,
                                    (int)ActivityTypeEnumerator.SlideLike, (int)ActivityTypeEnumerator.SlideComment,
                                    (int)ActivityTypeEnumerator.SlideShare, userId, lastMonths);

                    var da = new OdbcDataAdapter
                    {
                        SelectCommand = new OdbcCommand { CommandText = queryEvents, Connection = conn }
                    };

                    var dt1 = new DataTable();
                    var externalColumn = new DataColumn(index, Type.GetType("System.String"));
                    dt1.Columns.Add(externalColumn);

                    da.Fill(dt1);

                    mediaLists.Add(String.Join(",", (from DataRow row in dt1.Rows
                                                     select row[index].ToString()).Distinct()));
                }

                return mediaLists;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return new List<string> { "", "", "" };

        }

        #endregion

        #region Activity wall functions

        public List<string> GetUsersActiveList(int minutes = 15)
        {
            var retList = new List<string>();

            var query = string.Format("SELECT userid FROM tagging.ActivityLog WHERE actiondate > dateadd(m,-{0},'{1}') AND userid <> '' AND userid IS NOT NULL GROUP BY userid", minutes, DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"));

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);
            var conn = new OdbcConnection(connString);

            var dt1 = new DataTable();
            var mediaColumn = new DataColumn("userid");
            dt1.Columns.Add(mediaColumn);
            dt1.PrimaryKey = new[] { mediaColumn };

            try
            {
                conn.Open();

                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);

                retList.AddRange(from DataRow row in dt1.Rows select row["userid"].ToString());
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return retList;
        }


        /// <summary>
        /// Check whether unregistered user has been inactive for the last month.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="months"></param>
        /// <returns>Boolean</returns>
        public bool HasUserBeenActive(string userId, int months = 1)
        {
            var query = string.Format(@"SELECT DISTINCT userid FROM tagging.ActivityLog
                                        WHERE userid='{0}' AND ActionDate < ADD_MONTHS(CURRENT_DATE,-{1})
                                        ORDER BY ActionDate DESC LIMIT 1", userId, months);

            var connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, AmazonRedshiftDatabaseName, masterUsername,
                    masterUserPassword, port);

            var dt1 = new DataTable();
            var mediaColumn = new DataColumn("userid");
            dt1.Columns.Add(mediaColumn);
            dt1.PrimaryKey = new[] { mediaColumn };

            var conn = new OdbcConnection(connString);
            try
            {
                conn.Open();
                var da = new OdbcDataAdapter { SelectCommand = new OdbcCommand { CommandText = query, Connection = conn } };
                da.Fill(dt1);

                if (dt1.Rows.Count > 0)
                    return true;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        #endregion
    }
}