using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using MySql.Data.MySqlClient;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Core.EditorialPlaylists
{
    public class EditorialPlaylistStatsFactory
    {
        private CultureInfo _defaultCulture = CultureInfo.GetCultureInfo("sv-SE");

        public async Task AddPlaylistSubscriberStatisticsAsync(IEnumerable<PlaylistStat> stats)
        {
            if (!stats.Any()) { return; }

            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblSpotifyPlaylistFollowerHistory (playlistId, Date, Followers) VALUES ";
                IEnumerable<string> paramValues = stats.Select(p => " ('" + p.PlaylistId + "', '" + p.Date.ToString("d", _defaultCulture)  + "', " + p.Subscribers + ")");
                sql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                await cmd.ExecuteNonQueryAsync();
            }
        }

        internal async Task<List<PlaylistStat>> GetStatsAsync(List<string> playlistIds, DateTime startDate, DateTime? endDate)
        {
            ConcurrentBag<PlaylistStat> stats = new ConcurrentBag<PlaylistStat>();

            var playlistsBatches = playlistIds.Batch(10000).ToList();

            await playlistsBatches.ForEachAsync(5, async batch =>
            {
                var batchStats = await GetStatsInternalAsync(batch.ToList(), startDate, endDate);
                batchStats.ForEach(stats.Add);
            });
            
            return stats.ToList();
        }

        private async Task<List<PlaylistStat>> GetStatsInternalAsync(List<string> playlistIds, DateTime startDate, DateTime? endDate)
        {
            List<PlaylistStat> playlistStats = new List<PlaylistStat>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT PlaylistId, Date, Followers FROM tblSpotifyPlaylistFollowerHistory WHERE 1=1 ";
                if (playlistIds.Any())
                    sql += " AND PlaylistId IN ('" + string.Join("','", playlistIds) + "')";
                sql += " AND (@startDate IS NULL OR Date >= @startDate) AND (@endDate IS NULL OR Date <= @endDate)";

                using(var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@startDate", startDate.Date);
                    cmd.Parameters.AddWithValue("@endDate", endDate);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var playlistId = reader.GetString(0);
                            var datetime = reader.GetDateTime(1);
                            var subscribers = reader.GetUInt32(2);

                            playlistStats.Add(new PlaylistStat() { Date = datetime, PlaylistId = playlistId, Subscribers = subscribers });
                        }
                    }
                }
                    
            }
            return playlistStats;
        }
        internal async Task<Dictionary<DateTime, uint>> GetCombinedPlaylistStatsAsync(List<SpotifyLink> playlists, DateTime startDate, DateTime? endDate)
        {
            var stats = new Dictionary<DateTime, uint>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT Date, SUM(Followers) FROM tblSpotifyPlaylistFollowerHistory WHERE 1=1 ";
                if (playlists.Any())
                    sql += " AND playlistId IN ('" + string.Join("','", playlists.Select(p=> p.ExtractPlaylistID())) + "')";
                sql += " AND (@startDate IS NULL OR Date >= @startDate) AND (@endDate IS NULL OR Date <= @endDate)";
                sql += " GROUP BY Date";

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                cmd.CommandTimeout = 120;
                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var datetime = reader.GetDateTime(0);
                        var subscribers = (uint)reader.GetInt64(1);
                        stats.Add(datetime, subscribers);
                    }
                }
            }
            return stats;
        }

        internal async Task<List<CombinedStat>> GetCombinedPlaylistStatsAsync(string spotifyUsername, DateTime? startDate, DateTime? endDate)
        {
            var stats = new List<CombinedStat>();

            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT Date, SUM(Followers) as followers, MAX(Timestamp) as latestTimestamp " +
                          "FROM tblSpotifyPlaylistFollowerHistory AS pfh" +
                          "INNER JOIN tblSpotifyPlaylist AS p ON p.playlistId = pfh.playlistId " +
                          "WHERE p.User = @username " +
                          "AND (@datStartDate IS NULL OR datDay >= @datStartDate) AND (@datEndDate IS NULL OR datDay <= @datEndDate) " +
                          "GROUP BY Date";

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@username", MySqlHelper.EscapeString(spotifyUsername));
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var date = reader.GetDateTime("Date");
                        var subscribers = reader.GetLong("followers");
                        var timestamp = reader.GetUtcDateTime("latestTimestamp");

                        stats.Add(new CombinedStat()
                        {
                            Date = date,
                            Subscribers = subscribers,
                            Timestamp = timestamp,
                        });
                    }
                }
            }
            return stats;
        }


    }

    public class CombinedStat
    {
        public long Subscribers { get; set; }

        public DateTime Date { get; set; }

        public DateTime Timestamp { get; set; }

    }
}