using EchoNest.Artist;
using MoreLinq;
using MySql.Data.MySqlClient;
using Sony.Filtr.Database;
using Sony.Filtr.DistributedCaching;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;

namespace Sony.Filtr.PlaylistGeneration.Autocomplete
{
    public class EchoNestDataFactory : IAutocompleteFactory
    {
        private readonly DistributedCacheHandler _cacheHandler;

        public EchoNestDataFactory(DistributedCacheHandler distributedCacheHandler)
        {
            _cacheHandler = distributedCacheHandler;
        }

        public async Task<List<ArtistAutocompleteResult>> SearchArtistsAsync(string search, int count)
        {
            var cacheKey = "EchoNestDataFactory-1.0:" + search;
            var artists = _cacheHandler.Get(cacheKey) as List<ArtistAutocompleteResult>;
            if (artists == null)
            { 
                artists = await SearchArtistsInternalAsync(search, count);

                _cacheHandler.Put(cacheKey, artists, DateTime.Now.AddHours(1));
            }
            return artists;
        }

        private async Task<List<ArtistAutocompleteResult>> SearchArtistsInternalAsync(string search, int count)
        {
            List<ArtistAutocompleteResult> artists = new List<ArtistAutocompleteResult>();
            using (MySqlConnection conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                await conn.OpenAsync();
                //const string sql = "SELECT strArtistName FROM tblAutocompleteArtistsEchoNest WHERE strArtistName LIKE @strSearch OR strArtistName LIKE Concat('the ', @strSearch) ORDER BY dblFamiliarity DESC LIMIT @count";
                const string sql = "SELECT strArtistName, dblFamiliarity FROM tblAutocompleteArtistsEchoNest WHERE strArtistName LIKE @strSearch OR strAlias LIKE @strSearch ORDER BY dblFamiliarity DESC LIMIT @count";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@strSearch", search + "%");
                cmd.Parameters.AddWithValue("@count", count);

                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    artists.Add(new ArtistAutocompleteResult()
                    {
                        Name = reader.GetString(0),
                        Score = reader.GetDouble(1)
                    });
                }

                await conn.CloseAsync();
            }
            return artists;
        }

        public async Task IndexArtistAsync(ArtistBucketItem artistBucketItem)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT IGNORE INTO tblAutocompleteArtistsEchoNest (strArtistName, dblFamiliarity, dblHotness) VALUES (@strArtistName, @dblFamiliarity, @dblHotness)";
                cmd.Parameters.AddWithValue("@strArtistName", artistBucketItem.Name);
                cmd.Parameters.AddWithValue("@dblFamiliarity", artistBucketItem.Familiarity);
                cmd.Parameters.AddWithValue("@dblHotness", artistBucketItem.Hotttnesss);
                
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public void IndexArtists(IEnumerable<ArtistBucketItem> artistBucketItems)
        {
            foreach (var artistsBatch in artistBucketItems.Batch(1000))
            {
                IndexArtistsInternal(artistsBatch);
            }
        }

        private static void IndexArtistsInternal(IEnumerable<ArtistBucketItem> artistBucketItems)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();
                var sql =
                    "INSERT IGNORE INTO tblAutocompleteArtistsEchoNest (strArtistName, dblFamiliarity, dblHotness) VALUES ";
                sql += string.Join(",",
                    artistBucketItems.Select(
                        a =>
                            string.Format("('{0}', {1}, {2})", MySqlHelper.EscapeString(a.Name),
                                a.Familiarity.Value.ToString(CultureInfo.InvariantCulture),
                                a.Hotttnesss.Value.ToString(CultureInfo.InvariantCulture))));
                cmd.CommandText = sql;

                cmd.ExecuteNonQuery();
            }
        }
    }
}