using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using MySqlConnector;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Tasks.Tasks.SonyReleases;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Core.SonyMusic
{
    public class SonyMusicFactory
    {
        private const string _globalCountryCode = "_gl";
        private const string _globalDisplayName = "global";

        internal async Task<List<string>> IsSonyTracksAsync(List<string> ISRCs, string market = null)
        {
            var lookupIsrcs = ISRCs.FilterNull().ToList();

            if (!lookupIsrcs.Any())
                return new List<string>();

            var sonyTracks = new List<string>();
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var isrcValues = string.Join("','", lookupIsrcs.Select(MySqlHelper.EscapeString));
                var sql = $"SELECT DISTINCT ISRC FROM tblSpotifyAnalyticsSonyTracks str WHERE (@market IS NULL OR str.Region = @market) AND ISRC IN ('{isrcValues}')" +
                          $" UNION DISTINCT " +
                          $"SELECT DISTINCT ISRC FROM tblSonyRelease str WHERE (@market IS NULL OR str.Region = @market) AND ISRC IN ('{isrcValues}')";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@market", market);

                var reader = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection);

                while (await reader.ReadAsync())
                {
                    sonyTracks.Add(reader.GetString(0));
                }

                reader.Close();
            }
            return sonyTracks;
        }

        internal async Task<IEnumerable<IsSonyInfo>> GetSonyTrackMarketsAsync(List<string> ISRCs)
        {
            ConcurrentDictionary<string, ConcurrentBag<string>> result = new ConcurrentDictionary<string, ConcurrentBag<string>>(StringComparer.InvariantCultureIgnoreCase);

            await ISRCs.Batch(10000).ForEachAsync(1, async isrcBatch =>
            {
                using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    var sql = $"SELECT ISRC, Region FROM tblSpotifyAnalyticsSonyTracks WHERE ISRC IN ({Maybe.ToCommaSeparated(isrcBatch)})";

                    var cmd = new MySqlCommand(sql, conn);

                    var reader = await cmd.ExecuteReaderAsync();

                    while (await reader.ReadAsync())
                    {
                        var isrc = reader.GetString(0);
                        var market = reader.GetString(1);

                        var marketList = result.GetOrAdd(isrc, new ConcurrentBag<string>());
                        marketList.Add(market);
                    }
                }
            });
            return result.Select(p => new IsSonyInfo() { ISRC = p.Key.ToUpperInvariant(), Markets = p.Value.ToArray() }).ToArray();
        }

        internal async Task<List<string>> IsSonyAlbumsAsync(List<string> UPCs, MusicService? musicService, string region)
        {
            var lookupUpcs = UPCs.Where(u => !string.IsNullOrWhiteSpace(u)).ToList();

            if (!lookupUpcs.Any())
                return new List<string>();

            var sonyAlbums = new List<string>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var upcValues = string.Join("','", lookupUpcs);

                var sql = $"SELECT Upc FROM tblSonyUPCRegion WHERE Upc IN ('{upcValues}') AND ReleaseTypeId=@ReleaseTypeId";
                if (!string.IsNullOrEmpty(region) && region != "global")
                {
                    sql += " AND (@region IS NULL OR region = @region)";
                }
                if (musicService != null)
                {
                    sql += "  AND MusicServiceId = @musicServiceId";
                }
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    if (!string.IsNullOrEmpty(region) && region != "global")
                    {
                        cmd.Parameters.AddWithValue("@region", region);
                    }
                    if (musicService != null)
                    {
                        cmd.Parameters.AddWithValue("@musicServiceId", musicService);
                    }

                    cmd.Parameters.AddWithValue("@ReleaseTypeId", (int)SpotifyAnalyticsAccount.Sony);

                    using (var reader = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
                    {
                        while (await reader.ReadAsync())
                        {
                            sonyAlbums.Add(reader.GetSafeString("Upc"));
                        }
                    }
                }
            }
            return sonyAlbums;
        }

        internal async Task<Dictionary<string, bool>> GetIsSonyOverrideByIsrcAsync(IEnumerable<string> ISRCs, string countryCode)
        {
            var lookupIsrcs = ISRCs.FilterNull().ToArray();

            var isrcOverrides = new Dictionary<string, bool>();

            if (!lookupIsrcs.Any())
                return isrcOverrides;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var isrcs = Maybe.ToCommaSeparated(lookupIsrcs);

                MySqlCommand cmd;

                if (string.IsNullOrWhiteSpace(countryCode))
                {
                    cmd = new MySqlCommand($"SELECT Isrc, IsSony FROM tblSonyISRCOverride WHERE CountryCode = '{_globalCountryCode}' AND ISRC IN ({isrcs})", conn);
                }
                // If country code is supplied, check if there is an override set for country code first, then fall back to global
                else
                {
                    var sql = "SELECT * FROM ( " +
                        $"SELECT Isrc, IsSony, CountryCode FROM tblSonyISRCOverride WHERE CountryCode = @countryCode AND ISRC IN({isrcs}) " +
                        "UNION " +
                        $"SELECT Isrc, IsSony, CountryCode FROM tblSonyISRCOverride WHERE CountryCode = '{_globalCountryCode}' AND ISRC IN({isrcs}) " +
                        ") AS allOverrides GROUP BY Isrc";

                    cmd = new MySqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@countryCode", countryCode);
                }

                var reader = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection);

                while (await reader.ReadAsync())
                {
                    var isrc = reader.GetSafeString("ISRC");
                    var isSony = reader.GetBoolean("IsSony");

                    isrcOverrides.Add(isrc, isSony);
                }

                reader.Close();
            }

            return isrcOverrides;
        }

        internal async Task<List<IsrcOverride>> GetIsSonyOverridesAsync()
        {
            var isrcOverrides = new List<IsrcOverride>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT ISRC, CountryCode, IsSony FROM tblSonyISRCOverride ORDER BY Isrc, CountryCode";

                var cmd = new MySqlCommand(sql, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var countryCode = reader.GetSafeString("CountryCode");

                        if (countryCode.Equals(_globalCountryCode, StringComparison.InvariantCultureIgnoreCase))
                            countryCode = _globalDisplayName;

                        var isrcOverride = new IsrcOverride
                        {
                            Isrc = reader.GetSafeString("ISRC"),
                            CountryCode = countryCode,
                            IsSony = reader.GetBoolean("IsSony")
                        };

                        isrcOverrides.Add(isrcOverride);
                    }
                }
            }

            return isrcOverrides;
        }

        internal async Task SetIsSonyOverrideIsrcAsync(string ISRC, bool isSony, List<string> countryCodes)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT INTO tblSonyISRCOverride (ISRC, CountryCode, IsSony) " +
                          "VALUES {0} ON DUPLICATE KEY UPDATE IsSony = VALUES(IsSony)";

                var paramValues = new List<string>();

                foreach (var countryCode in countryCodes)
                {
                    var paramValue = "(" + string.Join(",", "'" + MySqlHelper.EscapeString(ISRC) + "'", "'" + MySqlHelper.EscapeString(countryCode) + "'", isSony) + ")";

                    paramValues.Add(paramValue);
                }

                sql = string.Format(sql, string.Join(",", paramValues));

                var cmd = new MySqlCommand(sql, conn);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        internal async Task SetGlobalIsSonyOverrideIsrcAsync(string ISRC, bool isSony)
        {
            var countryCodes = new List<string>() { _globalCountryCode };

            await SetIsSonyOverrideIsrcAsync(ISRC, isSony, countryCodes);
        }

        internal async Task RemoveIsSonyOverrideIsrcAsync(string ISRC, List<string> countryCodes)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "DELETE FROM tblSonyISRCOverride WHERE ISRC = @isrc AND CountryCode IN ('{0}')";

                sql = string.Format(sql, string.Join("','", countryCodes));

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@isrc", ISRC);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        internal async Task RemoveGlobalIsSonyOverrideIsrcAsync(string ISRC)
        {
            var countryCodes = new List<string>() { _globalCountryCode };

            await RemoveIsSonyOverrideIsrcAsync(ISRC, countryCodes);
        }

        internal async Task RemoveAllIsSonyOverridesForIsrcAsync(string ISRC)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "DELETE FROM tblSonyISRCOverride WHERE ISRC = @isrc";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@isrc", ISRC);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<Dictionary<string, SpotifyAnalyticsAccount>> GetSonyAlbumAsync(IEnumerable<string> upcs, MusicService musicService, string market)
        {
            Dictionary<string, SpotifyAnalyticsAccount> sonyAlbums = new Dictionary<string, SpotifyAnalyticsAccount>();
            if (!upcs.Any())
                return sonyAlbums;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT DISTINCT UPC, ReleaseTypeId FROM tblSonyUPCRegion " +
                          $"WHERE UPC IN ({Maybe.ToCommaSeparated(upcs)}) AND (@market IS NULL OR Region = @market) AND MusicServiceId = @musicServiceId";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@musicServiceId", musicService);
                    cmd.Parameters.AddWithValue("@market", market);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var upc = reader.GetString(0);
                            var releaseType = (SpotifyAnalyticsAccount)reader.GetInt32(1);

                            sonyAlbums.Add(upc, releaseType);
                        }
                    }
                }
            }

            return sonyAlbums;
        }
    }
}