using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using Sony.Filtr.Database;

namespace Sony.Filtr.Playlists.Deezer
{
    public class DeezerFanLogManager
    {
        private readonly CultureInfo _defaultCulture = CultureInfo.GetCultureInfo("sv-SE");
        public async Task AddDeezerPlaylistFansStatsAsync(List<DeezerFanStat> deezerFanStats)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblDeezerPlaylistFansLog (intDeezerPlaylistId, intFans, datDay) VALUES ";
                IEnumerable<string> paramValues = deezerFanStats.Select(p => " ('" + p.DeezerEntityId + "', " + p.Fans + ", '" + p.Date.ToString("d", _defaultCulture) + "')");
                sql += string.Join(",", paramValues);

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<List<DeezerFanStat>> GetDeezerPlaylistFansStatsAsync(DateTime day)
        {
            List<DeezerFanStat> fans = new List<DeezerFanStat>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT intDeezerPlaylistId, intFans, datDay FROM tblDeezerPlaylistFansLog WHERE datDay = @datDay";
                cmd.Parameters.AddWithValue("datDay", day);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        fans.Add(new DeezerFanStat()
                        {
                            DeezerEntityId = reader.GetInt32(0),
                            Fans = reader.GetInt32(1),
                            Date = reader.GetDateTime(2),
                        });
                    }
                }
            }
            return fans;
        }

        public async Task<List<DeezerFanStat>> GetDeezerPlaylistFansStatsForUserAsync(int deezerUserId)
        {
            List<DeezerFanStat> fans = new List<DeezerFanStat>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT l.intDeezerPlaylistId, l.intFans, l.datTimestamp FROM tblDeezerPlaylistFansLog AS l " +
                                  " INNER JOIN DeezerPlaylist AS dp ON l.intDeezerPlaylistID = dp.DeezerId " +
                                  " WHERE dp.CreatorId = @deezerUserId";
                cmd.Parameters.AddWithValue("@deezerUserId", deezerUserId);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        fans.Add(new DeezerFanStat()
                        {
                            DeezerEntityId = reader.GetInt32(0),
                            Fans = reader.GetInt32(1),
                            Date = reader.GetDateTime(2),
                        });
                    }
                }
            }
            return fans;
        }

        public async Task AddDeezerUserFansStatsAsync(List<DeezerFanStat> deezerFanStats)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblDeezerUserFansLog (intDeezerUserId, intFans, datDay) VALUES ";
                IEnumerable<string> paramValues = deezerFanStats.Select(p => " ('" + p.DeezerEntityId + "', " + p.Fans + ", '" + p.Date.ToString("d", _defaultCulture) + "')");
                sql += string.Join(",", paramValues);

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<List<DeezerFanStat>> GetDeezerUserFansStatsAsync(int deezerUserId)
        {
            List<DeezerFanStat> fans = new List<DeezerFanStat>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT intDeezerUserId, intFans, datTimestamp FROM tblDeezerUserFansLog WHERE intDeezerUserId = @deezerUserId";
                cmd.Parameters.AddWithValue("@deezerUserId", deezerUserId);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        fans.Add(new DeezerFanStat()
                        {
                            DeezerEntityId = reader.GetInt32(0),
                            Fans = reader.GetInt32(1),
                            Date = reader.GetDateTime(2),
                        });
                    }
                }
            }
            return fans;
        }

        public async Task<List<DeezerFanStat>> GetDeezerUserFansStatsAsync(DateTime day)
        {
            List<DeezerFanStat> fans = new List<DeezerFanStat>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT intDeezerUserId, intFans, datDay FROM tblDeezerUserFansLog WHERE datDay = @datDay";
                cmd.Parameters.AddWithValue("datDay", day);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        fans.Add(new DeezerFanStat()
                        {
                            DeezerEntityId = reader.GetInt32(0),
                            Fans = reader.GetInt32(1),
                            Date = reader.GetDateTime(2),
                        });
                    }
                }
            }
            return fans;
        }
    }

    [Serializable]
    public class DeezerFanStat
    {
        public long DeezerEntityId { get; set; }
        public int Fans { get; set; }
        public DateTime Date { get; set; }
    }
}