using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Core.TrackPopularity
{
    public class SpotifyTrackPopularityManager
    {
        public async Task<List<SpotifyTrackPopularityLogValue>> GetPopularityLogAsync(SpotifyLink track)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyTrackPopularityLogValue>("WHERE strSpotifyUri=@0", track.Uri);
        }

        public async Task AddPopularityLogValuesAsync(IEnumerable<SpotifyTrackPopularityLogValue> logs)
        {
            if (logs == null || !logs.Any()) { return; }

            Func<Task> insertValues = async () => {
                var sql = new StringBuilder(@"INSERT IGNORE INTO tblSpotifyTrackPopularityLog (strSpotifyUri, datDay, intPopularity) VALUES ");

                sql.Append(String.Join(", ", logs.Select(c => $"('{MySqlHelper.EscapeString(c.TrackLink.ToString())}', '{c.Day.ToString("yyyy-MM-dd")}', {c.Popularity})")));

                using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
                {
                    var cmd = new MySqlCommand(sql.ToString(), conn);

                    await cmd.ExecuteNonQueryAsync();
                }
            };

            Func<Task> bulkLoad = async () => {
                await PetaPocoRepository.Instance.ImportBulkFileLoaderBatchesAsync(logs, MySqlBulkLoaderConflictOption.Replace);
            };

            if (logs.Count() > 300)
            {
                await bulkLoad();
            }
            else
            {
                await insertValues();
            }
        }

        public async Task<IEnumerable<SpotifyTrackPopularityLogValue>> GetPlaylistLatestTrackPopularitiesAsync(string playlistId)
        {
            string sql = @"
SELECT log.strSpotifyUri, log.datDay, log.intPopularity
FROM tblSpotifyTrackPopularityLog log
INNER JOIN (
	SELECT strSpotifyUri, MAX(datDay) AS datDay
	FROM tblSpotifyTrackPopularityLog 
	WHERE strSpotifyUri IN (SELECT TrackId FROM tblSpotifyPlaylistTrackList2 WHERE PlaylistId = @playlistId)
GROUP BY strSpotifyUri) maxDate ON log.strSpotifyUri = maxDate.strSpotifyUri AND log.datDay = maxDate.datDay;";


            var logs = new List<SpotifyTrackPopularityLogValue>();
            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddWithValue("@playlistId", playlistId);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    logs.Add(new SpotifyTrackPopularityLogValue()
                    {
                        Day = reader.GetDateTime("datDay"),
                        Popularity = reader.GetInt32("intPopularity"),
                        TrackLink = new SpotifyLink(reader.GetSafeString("strSpotifyUri"))
                    });
                }
            }

            return logs.ToArray();
        }

        //        public async Task CopyPlaylistTrackPopularitiesForDateAsync(string playlistId, DateTime date)
        //        {
        //            string sql = @"
        //INSERT IGNORE INTO tblSpotifyTrackPopularityLog (strSpotifyUri, datDay, intPopularity)
        //SELECT log.strSpotifyUri, @date, log.intPopularity
        //FROM tblSpotifyTrackPopularityLog log
        //    INNER JOIN (
        //        SELECT strSpotifyUri, MAX(datDay) AS datDay
        //        FROM tblSpotifyTrackPopularityLog 
        //        WHERE strSpotifyUri IN (SELECT TrackId FROM tblSpotifyPlaylistTrackList2 WHERE PlaylistId = @playlistId)
        //    GROUP BY strSpotifyUri) maxDate ON log.strSpotifyUri = maxDate.strSpotifyUri AND log.datDay = maxDate.datDay;";

        //            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
        //            {
        //                var copyCommand = new MySqlCommand(sql, conn);
        //                copyCommand.Parameters.AddWithValue("@playlistId", playlistId);
        //                copyCommand.Parameters.AddWithValue("@date", date.Date);
        //                await copyCommand.ExecuteNonQueryAsync();
        //            }
        //        }

        public void ClearOldPopularityLogValues()
        {
            PetaPocoRepository.Instance.Delete<SpotifyTrackPopularityLogValue>("WHERE datDay<@0", DateTime.Today.AddDays(-8));
        }

        public async Task<List<SpotifyTrackPopularityLogValue>> GetTopListAsync(DateTime day, int top)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyTrackPopularityLogValue>("WHERE datDay=@0 ORDER BY intPopularity DESC LIMIT @1", day.Date, top);
        }

        public async Task<List<SpotifyTrackPopularityLogValue>> GetTopListMostChangingAsync(int top)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyTrackPopularityLogValue>("GROUP BY strSpotifyUri ORDER BY Max(intPopularity) - Min(intPopularity) DESC LIMIT @0", top);
        }
    }
}