using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MoreLinq;
using MySql.Data.MySqlClient;
using NLog;
using Sony.Filtr.AppleMusic;
using Sony.Filtr.AppleMusic.Data;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Tasks.Tasks.AppleMusic.Aggregation
{
    public class ChartsAggregation
    {
        private readonly AppleMusicChartManager _chartsManager;

        private const string _FieldTerminator = "|";
        private readonly AppleMusicApi _appleMusicApi;
        private readonly Logger _logger;

        public ChartsAggregation(AppleMusicChartManager chartsManager, AppleMusicApi appleMusicApi)
        {
            _appleMusicApi = appleMusicApi;
            _chartsManager = chartsManager;

            _logger = LogManager.GetLogger("ImportAppleMusicData");
        }

        internal async Task<List<ChartPosition>> GetAllChartPositionsPerTrackAsync(ChartType chartType, string chartName)
        {
            var allPositions = new List<ChartPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT c.storefront, s.isrc, c.date, c.timestamp, e.position " +
                                   "FROM tblAppleMusicChart AS c " +
                                   "INNER JOIN tblAppleMusicChartSongEntry AS e ON c.id = e.chartId " +
                                   "INNER JOIN tblAppleMusicSong AS s ON s.id = e.songId AND s.storefront = c.storefront " +
                                   "WHERE ChartType = @chartType " +
                                   "AND (@chartName IS NULL OR chartName = @chartName) " +
                                   "AND GenreId IS NULL " +
                                   "AND s.isrc IS NOT NULL " +
                                   "GROUP BY c.storefront, s.isrc, c.date, e.position";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@chartType", chartType);
                    command.Parameters.AddWithValue("@chartName", chartName);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var storefront = reader.GetString("storefront");
                            var isrc = reader.GetString("isrc");

                            var date = reader.GetDateTime("date");
                            var timestamp = reader.GetDateTime("timestamp");
                            var position = reader.GetInt32("position");

                            allPositions.Add(new ChartPosition()
                            {
                                Storefront = storefront,
                                ISRC = isrc,
                                Date = date,
                                TimeStamp = timestamp,
                                Position = position,
                            });
                        }
                    }
                }
            }
            return allPositions;
        }

        internal async Task<List<ChartPosition>> GetAllPlaylistChartPositionsPerTrackAsync()
        {
            var allPositions = new List<ChartPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT c.storefront, s.isrc, c.date, c.timestamp, e.position " +
                                   "FROM tblAppleMusicChart AS c " +
                                   "INNER JOIN tblAppleMusicChartsPlaylistMapping AS cp ON c.storefront = cp.displayStorefront " +
                                   "INNER JOIN tblAppleMusicChartSongEntry AS e ON c.id = e.chartId " +
                                   "INNER JOIN tblAppleMusicSong AS s ON s.id = e.songId AND s.storefront = cp.TracklistStorefront " +
                                   "WHERE ChartType = @chartType " +
                                   "AND (@chartName IS NULL OR chartName = @chartName) " +
                                   "AND GenreId IS NULL " +
                                   "AND s.isrc IS NOT NULL " +
                                   "GROUP BY c.storefront, s.isrc, c.date, e.position";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@chartType", ChartType.PlaylistBased);
                    command.Parameters.AddWithValue("@chartName", "track_chart_playlist");
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var storefront = reader.GetString("storefront");
                            var isrc = reader.GetString("isrc");

                            var date = reader.GetDateTime("date");
                            var timestamp = reader.GetDateTime("timestamp");
                            var position = reader.GetInt32("position");

                            allPositions.Add(new ChartPosition()
                            {
                                Storefront = storefront,
                                ISRC = isrc,
                                Date = date,
                                TimeStamp = timestamp,
                                Position = position,
                            });
                        }
                    }
                }
            }
            return allPositions;
        }

        internal async Task<List<AppleMusicTrackChartSummary>> BuildTrackChartSummariesAsync(List<ChartPosition> allPosition, ChartType chartType, string chartName)
        {
            var availableCharts = await _chartsManager.GetAvailableChartsOverviewAsync(chartType, chartName);
            var currentCharts = availableCharts.ToDictionary(p => p.Storefront);

            var chartTrackSummaries = allPosition.GroupBy(p => new { p.Storefront, p.ISRC }).Select(p => BuildAppleMusicTrackChartSummary(p.Key.Storefront, p.Key.ISRC, p.ToList(), currentCharts.GetValueOrDefault(p.Key.Storefront))).ToList();

            return chartTrackSummaries;
        }
        public async Task<List<AppleMusicTrackChartSummary>> BuildTrackChartSummariesAsync(List<ChartPosition> trackPositions, Dictionary<string, AvailableSongChartOverview> currentCharts)
        {
            var chartTrackSummaries = trackPositions.GroupBy(p => new { p.Storefront, p.ISRC }).Select(p => BuildAppleMusicTrackChartSummary(p.Key.Storefront, p.Key.ISRC, p.ToList(), currentCharts[p.Key.Storefront])).ToList();

            return chartTrackSummaries;
        }

        internal static AppleMusicTrackChartSummary BuildAppleMusicTrackChartSummary(string storefront, string isrc, List<ChartPosition> trackPositions, AvailableSongChartOverview chartOverview)
        {
            var peakPosition = trackPositions.Min(t => t.Position);
            var peakPositions = trackPositions.Where(p => p.Position == peakPosition).ToList();

            var currentPosition = trackPositions.FirstOrDefault(t => t.Date.Date == chartOverview.LatestDate.Date);

            ChartPosition prevPosition = null;
            if (chartOverview.PreviousDate.HasValue)
            {
                prevPosition = trackPositions.Where(p => p.Date.Date == chartOverview.PreviousDate.Value.Date).OrderByDescending(p => p.Date).FirstOrDefault();
            }

            return new AppleMusicTrackChartSummary
            {
                Storefront = storefront,
                Isrc = isrc,

                EntryPosition = trackPositions.MinBy(t => t.Date).Position,
                EntryDate = trackPositions.MinBy(t => t.Date).Date,

                LatestPosition = trackPositions.MaxBy(t => t.Date).Position,
                LatestDate = trackPositions.MaxBy(t => t.Date).Date,

                PeakPosition = trackPositions.MinBy(t => t.Position).Position,
                PeakDate = peakPositions.MinBy(t => t.Date).Date,
                PeakLatestDate = peakPositions.MaxBy(t => t.Date).Date,

                CurrentPosition = currentPosition?.Position,
                PreviousPosition = prevPosition?.Position,
                LatestUpdateDate = chartOverview.LatestDate,
                LatestUpdateTimestamp = chartOverview.LatestTimestamp,
            };
        }

        internal async Task SaveTrackChartSummariesAsync(List<AppleMusicTrackChartSummary> trackChartSummary)
        {
            const string fileDirectory = "AppleMusicCharts/";
            if (!Directory.Exists(fileDirectory))
            {
                Directory.CreateDirectory(fileDirectory);
            }

            var filename = $"ChartTrackSummary-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm", CultureInfo.InvariantCulture) + ".csv";
            var filePath = Path.Combine(fileDirectory, filename);

            var summaryRows = trackChartSummary.Select(t => string.Join(_FieldTerminator,
                t.Storefront,
                t.Isrc,
                GetBulkImportString(t.EntryDate),
                t.EntryPosition,
                GetBulkImportString(t.PeakDate),
                t.PeakPosition,
                GetBulkImportString(t.PeakLatestDate),
                GetBulkImportString(t.LatestDate),
                t.LatestPosition,

                (object)t.CurrentPosition ?? "\\N", //TODO: Cleanup, make it pretty!
                (object)t.PreviousPosition ?? "\\N",
                GetBulkImportString(t.LatestUpdateDate),
                GetBulkImportString(t.LatestUpdateTimestamp)));
            File.WriteAllLines(filePath, summaryRows);

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var bulkLoader = new MySqlBulkLoader(conn);
                bulkLoader.Local = true;
                bulkLoader.FileName = filePath;
                bulkLoader.TableName = "tblAppleMusicChartTrackSummary";
                bulkLoader.FieldTerminator = _FieldTerminator;
                bulkLoader.LineTerminator = Environment.NewLine;
                bulkLoader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;

                await bulkLoader.LoadAsync();
            }

            File.Delete(filePath);
        }

        private readonly CultureInfo _bulkImportCultureInfo = CultureInfo.GetCultureInfo("sv-se");
        private string GetBulkImportString(DateTime date)
        {
            return date.ToString(_bulkImportCultureInfo);
        }

    }
}