using Dapper;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PetaPoco;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities.Buzz;
using Sony.Filtr.Core.LastModified;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Buzz
{
    public class BuzzAccountManager : IBuzzAccountManager
    {
        private readonly LastModifiedManager _lastModifiedManager;

        public BuzzAccountManager(LastModifiedManager lastModifiedManager)
        {
            _lastModifiedManager = lastModifiedManager;
        }

        public IEnumerable<BuzzCategory> GetBuzzCategories()
        {
            return PetaPocoRepository.Instance.Fetch<BuzzCategory>();
        }

        public BuzzCategory GetBuzzCategory(int categoryId)
        {
            return PetaPocoRepository.Instance.SingleOrDefault<BuzzCategory>(categoryId);
        }

        public List<BuzzGroup> GetBuzzGroups(IEnumerable<int> groupIds)
        {
            return PetaPocoRepository.Instance.Fetch<BuzzGroup>("WHERE Id IN (@groupIds)", new { @groupIds = groupIds.ToArray()});
        }
        public List<BuzzGroup> GetBuzzGroups(BuzzCategory category)
        {
            return PetaPocoRepository.Instance.Fetch<BuzzGroup>("WHERE CategoryID=@0", category.ID);
        }

        public BuzzGroup GetBuzzGroup(int groupId)
        {
            return PetaPocoRepository.Instance.SingleOrDefault<BuzzGroup>(groupId);
        }

        public BuzzGroup AddGroup(BuzzGroup buzzGroup)
        {
            return PetaPocoRepository.Instance.Upsert(buzzGroup);
        }

        //public async Task<List<BuzzUser>> GetBuzzUsersAsync(int? buzzCategoryId = null, ServiceType? serviceType = null)
        //{
        //    List<BuzzUser> buzzUsers = new List<BuzzUser>();
        //    using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
        //    {
        //        await conn.OpenAsync();

        //        MySqlCommand cmd = new MySqlCommand("SELECT bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, GROUP_CONCAT(bt.Tag) as tags, bu.image " +
        //                                               "FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId " +
        //                                                "WHERE (@categoryID IS NULL OR CategoryID=@categoryId) AND (@serviceType IS NULL OR ServiceType=@serviceType) " +
        //                                                "GROUP BY bu.Id", conn);
        //        cmd.Parameters.AddWithValue("@CategoryId", buzzCategoryId);
        //        cmd.Parameters.AddWithValue("@serviceType", serviceType);

        //        var reader = await cmd.ExecuteReaderAsync();
        //        while(await reader.ReadAsync())
        //        {
        //            buzzUsers.Add(BuildBuzzUser(reader));
        //        }

        //        await conn.CloseAsync();
        //    }
        //    return buzzUsers;
        //}

        public async Task<IEnumerable<BuzzUser>> GetBuzzUsersAsync(MusicService musicServiceId)
        {
            return await GetBuzzUsersAsync(musicServiceId: (int)musicServiceId);
        }

        public async Task<IEnumerable<BuzzUser>> GetBuzzUsersAsync(int? buzzCategoryId = null, int? musicServiceId = null)
        {
            List<BuzzUser> buzzUsers = new List<BuzzUser>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand(@"
SELECT 
    bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, 
    bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, 
    GROUP_CONCAT(bt.Tag) as tags, bu.image
FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId
WHERE (@categoryID IS NULL OR CategoryID=@categoryId) AND (@musicServiceId IS NULL OR musicServiceId=@musicServiceId)
GROUP BY bu.Id", conn);
                cmd.Parameters.AddWithValue("@CategoryId", buzzCategoryId);
                cmd.Parameters.AddWithValue("@musicServiceId", musicServiceId);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        buzzUsers.Add(BuildBuzzUser(reader));
                    }
                }
            }
            return buzzUsers;
        }

        /// <summary>
        ///     Optimized for returning only neccessary data about buzz users.
        /// </summary>
        /// <typeparam name="T">Type to map result from sql.</typeparam>
        /// <param name="buzzCategoryId">Id of category from <b>BuzzCategory</b> table.</param>
        /// <param name="musicServiceId">Id of service id from <see cref="MusicService"/>.</param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetBuzzUsersAsync<T>(int? buzzCategoryId = null, int? musicServiceId = null)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sbSql = new StringBuilder();
                // This select statement returns only required fields and do grouping 
                // on SQL server side to prevent memory leaks and CPU consumption.
                sbSql.Append("SELECT " +
                                "bu.Username as AccountId, " +
                                "COALESCE(bu.DisplayName, bu.Username) as AccountName, " +
                                "bu.CategoryID as CategoryId, " +
                                "bc.Name as CategoryName, " +
                                "bu.CountryCode as MarketCode " +
                            "FROM BuzzUser AS bu " +
                                "LEFT JOIN BuzzCategory AS bc ON bu.CategoryId = bc.ID ");

                if (buzzCategoryId.HasValue || musicServiceId.HasValue)
                {
                    sbSql.Append($"WHERE ");

                    if (buzzCategoryId.HasValue)
                        sbSql.Append($"CategoryID = {buzzCategoryId.Value} ");

                    bool bothValues = buzzCategoryId.HasValue && musicServiceId.HasValue;
                    if (bothValues)
                        sbSql.Append($"AND ");

                    if (musicServiceId.HasValue)
                        sbSql.Append($"musicServiceId = {musicServiceId.Value}");
                }

                sbSql.Append(" GROUP BY bu.Id");

                string sql = sbSql.ToString();

                return await conn.QueryAsync<T>(sql, commandType: System.Data.CommandType.Text);
            }
        }

        private BuzzUser BuildBuzzUser(DbDataReader reader)
        {
            return new BuzzUser()
            {
                ID = reader.GetInt32("Id"),
                Username = reader.GetString("username"),
                DisplayName = reader.GetString("displayname"),
                BuzzCategoryId = reader.GetIntOrDefault("categoryId"),
                ServiceType = (ServiceType)reader.GetInt32("serviceType"),
                MusicServiceId = reader.GetInt32("musicServiceId"),
                CountryCode = reader.GetString("countryCode"),
                ImportPlaylists = reader.GetBoolean("importPlaylists"),
                Subscribers = reader.GetIntOrDefault("subscribers"),
                PlaylistSubscribers = reader.GetLongOrDefault("playlistSubscribers"),
                Error = reader.GetBoolean("error"),
                Tags = reader.GetString("tags")?.Split(',').ToList(),
                Image = reader.GetString("image"),
            };
        }

        //public async Task<List<BuzzUser>> GetUncategorizedBuzzUsersAsync(ServiceType? serviceType = null)
        //{
        //    List<BuzzUser> buzzUsers = new List<BuzzUser>();
        //    using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
        //    {
        //        await conn.OpenAsync();

        //        MySqlCommand cmd = new MySqlCommand("SELECT bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicService, bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, GROUP_CONCAT(bt.Tag) as tags, bu.image " +
        //                                               "FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId " +
        //                                                "WHERE CategoryID IS NULL AND (@serviceType IS NULL OR ServiceType=@serviceType) " +
        //                                                "GROUP BY bu.Id", conn);
        //        cmd.Parameters.AddWithValue("@serviceType", serviceType);

        //        var reader = await cmd.ExecuteReaderAsync();
        //        while (await reader.ReadAsync())
        //        {
        //            buzzUsers.Add(BuildBuzzUser(reader));
        //        }

        //        await conn.CloseAsync();
        //    }
        //    return buzzUsers;
        //}

        public async Task<List<BuzzUser>> GetUncategorizedBuzzUsersAsync(int? musicServiceId = null)
        {
            List<BuzzUser> buzzUsers = new List<BuzzUser>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand(@"
SELECT 
    bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, 
    bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, 
    GROUP_CONCAT(bt.Tag) as tags, bu.image
FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId
WHERE CategoryID IS NULL AND (@musicServiceId IS NULL OR MusicServiceId=@musicServiceId)
GROUP BY bu.Id"
                                                        , conn);
                cmd.Parameters.AddWithValue("@musicServiceId", musicServiceId);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        buzzUsers.Add(BuildBuzzUser(reader));
                    }
                }
            }
            return buzzUsers;
        }

        public async Task<BuzzUser> GetBuzzUserAsync(string username, ServiceType serviceType)
        {
            BuzzUser buzzUser = null;
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, GROUP_CONCAT(bt.Tag) as tags, bu.image " +
                                                       "FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId " +
                                                        "WHERE Username = @username AND ServiceType=@serviceType " +
                                                        "GROUP BY bu.Id", conn);
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@serviceType", serviceType);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        buzzUser = BuildBuzzUser(reader);
                    }
                }
            }
            return buzzUser;
        }

        public async Task<BuzzUser> GetBuzzUserAsync(string username, int musicServiceId)
        {
            BuzzUser buzzUser = null;
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand(@"
SELECT 
    bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, 
    bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, 
    GROUP_CONCAT(bt.Tag) as tags, bu.image
FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId 
WHERE Username = @username AND MusicServiceId=@musicService
GROUP BY bu.Id", conn);
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@musicService", musicServiceId);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        buzzUser = BuildBuzzUser(reader);
                    }
                }
            }
            return buzzUser;
        }

        public async Task<BuzzUser> GetBuzzUserAsync(int buzzUserId)
        {
            BuzzUser buzzUser = null;
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = new MySqlCommand(@"
SELECT 
    bu.id, bu.Username, bu.DisplayName, bu.CategoryId, bu.ServiceType, bu.musicServiceId, 
    bu.CountryCode, bu.ImportPlaylists, bu.subscribers, bu.playlistSubscribers, bu.error, 
    GROUP_CONCAT(bt.Tag) as tags, bu.image
FROM BuzzUser AS bu LEFT JOIN BuzzUserTag AS bt ON bu.ID = bt.BuzzUserId
WHERE bu.id=@id
GROUP BY bu.Id", conn);
                cmd.Parameters.AddWithValue("@id", buzzUserId);

                var reader = await cmd.ExecuteReaderAsync();
                if (await reader.ReadAsync())
                {
                    buzzUser = BuildBuzzUser(reader);
                }
            }
            return buzzUser;
        }

        public async Task<BuzzUser> AddBuzzUserAsync(BuzzUser buzzUser)
        {
            var newBuzzUser = PetaPocoRepository.Instance.Upsert(buzzUser);

            await SetBuzzUserAccountTagsAsync(newBuzzUser);

            await _lastModifiedManager.SetLastModifyDateAsync(null, EntityType.FiltrUsers);

            return newBuzzUser;
        }

        public async Task<BuzzUser> UpdateBuzzUserAsync(BuzzUser buzzUser, List<string> columns = null)
        {
            PetaPocoRepository.Instance.Update(buzzUser, columns);

            if (columns != null)
                await SetBuzzUserAccountTagsAsync(buzzUser);

            await _lastModifiedManager.SetLastModifyDateAsync(null, EntityType.FiltrUsers);

            return buzzUser;
        }

        private async Task SetBuzzUserAccountTagsAsync(BuzzUser buzzUser)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var trans = conn.BeginTransaction();

                MySqlCommand delCmd = new MySqlCommand("DELETE FROM BuzzUserTag WHERE BuzzUserId=@BuzzUserId", conn, trans);
                delCmd.Parameters.Add(new MySqlParameter("@BuzzUserId", buzzUser.ID));
                await delCmd.ExecuteNonQueryAsync();

                if (buzzUser.Tags != null)
                {
                    foreach (var tag in buzzUser.Tags)
                    {
                        MySqlCommand insertCmd = new MySqlCommand("INSERT INTO BuzzUserTag (BuzzUserId, Tag) VALUES (@BuzzUserId, @Tag)", conn, trans);
                        insertCmd.Parameters.Add(new MySqlParameter("@BuzzUserId", buzzUser.ID));
                        insertCmd.Parameters.Add(new MySqlParameter("@Tag", tag));

                        await insertCmd.ExecuteNonQueryAsync();
                    }
                }

                trans.Commit();
            }
        }

        public void AddBuzzUserToGroup(BuzzUser buzzUser, BuzzGroup buzzGroup)
        {
            PetaPocoRepository.Instance.Execute(new Sql("INSERT IGNORE INTO BuzzGroupUser (UserID, GroupID) VALUES (@0, @1)", buzzUser.ID, buzzGroup.ID));
        }

        public List<BuzzGroupUserMapping> GetBuzzUserGroupMapping(BuzzCategory buzzCategory)
        {
            var sql =
                Sql.Builder.Select("*")
                    .From("BuzzGroupUser")
                    .InnerJoin("BuzzGroup")
                    .On("BuzzGroupUser.GroupID=BuzzGroup.ID")
                    .Where("BuzzGroup.CategoryID=@0", buzzCategory.ID);
            return PetaPocoRepository.Instance.Fetch<BuzzGroupUserMapping>(sql);
        }


        public async Task DeleteBuzzUserAsync(BuzzUser existingBuzzUser)
        {
            PetaPocoRepository.Instance.Delete<BuzzUser>(existingBuzzUser);

            await _lastModifiedManager.SetLastModifyDateAsync(null, EntityType.FiltrUsers);
        }

        
    }
}