using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Sony.Filtr.Core.SpotifyCharts.Data;
using Sony.Filtr.Database;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Core.SpotifyCharts.Data.Internal;
using Sony.Filtr.Utility.Extensions;
using Sony.Filtr.Functional;

namespace Sony.Filtr.Core.SpotifyCharts
{

    public class SpotifyChartsFactory
    {
        public async Task<List<AvailableChartOverview>> GetAvailableChartsOverviewAsync()
        {
            var availableCharts = new List<AvailableChartOverview>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT Market, ListType, TimeWindowType, Max(Date), Max(Timestamp) FROM tblSpotifyChartsAvailableChart " +
                          "GROUP BY Market, ListType, TimeWindowType";

                var cmd = new MySqlCommand(sql, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var availableChart = new AvailableChartOverview()
                        {
                            Market = reader.GetString(0),
                            ListType = (SpotifyChartsListType)reader.GetInt32(1),
                            TimeWindow = (SpotifyChartsTimeWindow)reader.GetInt32(2),
                            LatestDate = reader.GetDateTime(3),
                            LatestTimestamp = reader.GetDateTime(4),
                        };

                        availableCharts.Add(availableChart);
                    }
                }
            }

            return availableCharts;
        }
        public async Task<Dictionary<string, List<AvailableChart>>> GetAvailableChartsAsync()
        {
            var availableCharts = new Dictionary<string, List<AvailableChart>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT Market, ListType, TimeWindowType, Date, Timestamp FROM tblSpotifyChartsAvailableChart " +
                                   // Is this as good and faster? -->    "GROUP BY Market, ListType, TimeWindowType " +
                                   "ORDER BY Market, ListType, TimeWindowType, Date DESC";

                var cmd = new MySqlCommand(sql, conn);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var chartMarket = reader.GetString(0);

                    var availableChart = new AvailableChart()
                    {
                        ListType = (SpotifyChartsListType)reader.GetInt32(1),
                        TimeWindow = (SpotifyChartsTimeWindow)reader.GetInt32(2),
                        Date = reader.GetDateTime(3),
                        AddedTimestamp = reader.GetDateTime(4)
                    };

                    if (!availableCharts.ContainsKey(chartMarket))
                    {
                        availableCharts.Add(chartMarket, new List<AvailableChart>());
                    }

                    availableCharts[chartMarket].Add(availableChart);

                }
                reader.Close();
            }

            return availableCharts;
        }

        public async Task<List<SpotifyChart>> GetProcessedChartsForMarketAsync(string market)
        {
            Func<Type, int[]> getEnumValues = type => (int[])Enum.GetValues(type);
            var listTypesMemoized = getEnumValues.LazyMemoizeThreadSafe();

            Func<DateTime, int, int, SpotifyChart> createChart = (date, listType, timeWindow) => {
                if (date > DateTime.Now) { return null; }
                if (Array.IndexOf(getEnumValues(typeof(SpotifyChartsListType)), listType) == -1) { return null; }
                if (Array.IndexOf(getEnumValues(typeof(SpotifyChartsTimeWindow)), timeWindow) == -1) { return null; }

                return new SpotifyChart() { Market = market, ChartDate = date, TimeWindow = (SpotifyChartsTimeWindow)timeWindow, ChartType = (SpotifyChartsListType)listType };
            };

            var charts = new List<SpotifyChart>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT DISTINCT Date, ListType, TimeWindowType FROM tblSpotifyChartsAvailableChart WHERE Market = @market;";
                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@market", market);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        DateTime date = reader.GetDateTime(0);
                        int listType = reader.GetInt32(1);
                        int timeWindow = reader.GetInt32(2);

                        var chart = createChart(date, listType, timeWindow);
                        if (chart != null) { charts.Add(chart); }
                    }
                }
            }

            return charts;
        }

        public async Task<List<DateTime>> GetChartDatesAsync(string market, SpotifyChartsListType chartType, SpotifyChartsTimeWindow timeWindow)
        {
            var chartDates = new List<DateTime>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT DISTINCT Date FROM tblSpotifyChartsAvailableChart WHERE Market = @market AND ListType = @chartType AND TimeWindowType = @timeWindow";
                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@market", market);
                cmd.Parameters.AddWithValue("@chartType", chartType);
                cmd.Parameters.AddWithValue("@timeWindow", timeWindow);

                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    chartDates.Add(reader.GetDateTime(0));
                }
                reader.Close();
            }

            return chartDates;
        }


        public async Task<SpotifyChart> GetChartAsync(string market, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow, DateTime date)
        {
            var chart = new SpotifyChart
            {
                ChartDate = date,
                ChartPositions = new List<ChartPosition>(),
                ChartType = listType,
                TimeWindow = timeWindow,
                Market = market
            };

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT ChartPosition, TrackUrl, Trackname, ArtistName, NumberOfStreams, ISRC " +
                                   "FROM tblSpotifyCharts " +
                                   "WHERE Date = @date AND Market = @market AND ChartListType = @listType AND ChartTimeWindowType = @timeWindow " +
                                   "ORDER BY ChartPosition";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@market", market);
                cmd.Parameters.AddWithValue("@listType", listType);
                cmd.Parameters.AddWithValue("@timeWindow", timeWindow);
                cmd.Parameters.AddWithValue("@date", date);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var trackUrl = reader.GetString("TrackUrl");
                    var tracklink = new SpotifyLink(trackUrl);

                    var position = new ChartPosition()
                    {
                        Position = reader.GetInt32("ChartPosition"),
                        TrackLink = tracklink,
                        TrackName = reader.GetString("TrackName"),
                        ArtistName = reader.GetString("ArtistName"),
                        NumberOfStreams = reader.GetInt32("NumberOfStreams"),
                        ISRC = reader.GetString("ISRC"),
                    };

                    chart.ChartPositions.Add(position);
                }
                reader.Close();
            }

            return chart;
        }


        internal async Task<List<TrackPosition>> GetAllTrackPositionsAsync(string isrc)
        {
            var trackPositions = new List<TrackPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT market, ChartListType, ChartTimeWindowType, date, ChartPosition, TrackUrl, TrackName, ArtistName, ArtworkUrl, NumberOfStreams " +
                                   "FROM tblSpotifyCharts WHERE ISRC = @isrc " +
                                   "ORDER BY Market, Date DESC, ChartListType, ChartTimeWindowType";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@isrc", isrc);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    if (reader.IsDBNull(2))
                        continue;

                    var position = new TrackPosition()
                    {
                        Market = reader.GetString(0),
                        ListType = (SpotifyChartsListType)reader.GetInt32(1),
                        TimeWindow = (SpotifyChartsTimeWindow)reader.GetInt32(2),
                        Date = reader.GetDateTime(3),
                        Position = reader.GetInt32(4),
                        TrackUrl = reader.GetString(5),
                        TrackName = reader.GetString(6),
                        ArtistName = reader.GetString(7),
                        ArtworkUrl = reader.GetString(8),
                        NumberOfStreams = reader.GetInt32(9)
                    };

                    trackPositions.Add(position);
                }
                reader.Close();
            }

            return trackPositions;
        }

        public async Task SetAvailableChartAsync(string market, DateTime date, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow, int version)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sqlString = "INSERT INTO tblSpotifyChartsAvailableChart (Market, ListType, TimeWindowType, Date, Version) " +
                                         "VALUES (@market, @listType, @timeWindowType, @date, @version) ON DUPLICATE KEY UPDATE Timestamp = CURRENT_TIMESTAMP";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@market", market);
                command.Parameters.AddWithValue("@listType", listType);
                command.Parameters.AddWithValue("@timeWindowType", timeWindow);
                command.Parameters.AddWithValue("@date", date);
                command.Parameters.AddWithValue("@version", version);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task<SimplePosition> GetLatestTrackPositionAsync(string market, string isrc, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            var sqlString = "SELECT Date, ChartPosition FROM tblSpotifyCharts " +
                "WHERE isrc = @isrc AND Market = @market AND ChartListType = @listType AND ChartTimeWindowType = @timeWindowType " +
                "ORDER BY Date DESC LIMIT 1";

            return await GetSimplePositionAsync(sqlString, isrc, market, listType, timeWindow);
        }

        public async Task<SimplePosition> GetEarliestTrackPositionAsync(string market, string isrc, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            var sqlString = "SELECT Date, ChartPosition FROM tblSpotifyCharts " +
                "WHERE isrc = @isrc AND Market = @market AND ChartListType = @listType AND ChartTimeWindowType = @timeWindowType " +
                "ORDER BY Date ASC LIMIT 1";

            return await GetSimplePositionAsync(sqlString, isrc, market, listType, timeWindow);
        }

        public async Task<SimplePosition> GetEarliestPeakPositionAsync(string market, string isrc, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            var sqlString = "SELECT Date, ChartPosition FROM tblSpotifyCharts " +
                "WHERE isrc = @isrc AND Market = @market AND ChartListType = @listType AND ChartTimeWindowType = @timeWindowType " +
                "ORDER BY ChartPosition ASC, Date ASC LIMIT 1";

            return await GetSimplePositionAsync(sqlString, isrc, market, listType, timeWindow);
        }

        public async Task<SimplePosition> GetLatestPeakPositionAsync(string market, string isrc, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            var sqlString = "SELECT Date, ChartPosition FROM tblSpotifyCharts " +
                "WHERE isrc = @isrc AND Market = @market AND ChartListType = @listType AND ChartTimeWindowType = @timeWindowType " +
                "ORDER BY ChartPosition ASC, Date DESC LIMIT 1";

            return await GetSimplePositionAsync(sqlString, isrc, market, listType, timeWindow);
        }

        public async Task<SimplePosition> GetCurrentTrackPosition(string market, string isrc, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            var sqlString = "SELECT c.Date, c.ChartPosition  FROM tblSpotifyChartsAvailableChart ac " +
                            "LEFT JOIN tblSpotifyCharts c ON c.ChartTimeWindowType = ac.TimeWindowType AND c.ChartListType = ac.ListType AND c.Market = ac.Market AND c.Date = ac.Date AND c.isrc = @isrc " +
                            "WHERE ac.Market = @market AND ac.ListType = @listType AND ac.TimeWindowType = @timeWindowType " +
                            "ORDER BY ac.Date DESC LIMIT 1";

            return await GetSimplePositionAsync(sqlString, isrc, market, listType, timeWindow);
        }

        internal async Task<List<TrackSummaryLatestAndEntryPosition>> GetEntryAndLatestPositionAsync(string isrc)
        {
            List<TrackSummaryLatestAndEntryPosition> positions = new List<TrackSummaryLatestAndEntryPosition>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT peak.market, peak.ChartListType, peak.ChartTimeWindowType, " +
                                   "maxC.date as maxDate, maxC.ChartPosition as maxDatePosition, " +
                                   "minC.date as minDate, minC.ChartPosition as minDatePosition " +
                                   "FROM ( " +
                                   "  SELECT c.isrc, c.market, c.ChartListType, c.ChartTimeWindowType, Max(c.date) as maxDate, Min(c.date) AS minDate, Min(c.ChartPosition) as peakPosition " +
                                   "  FROM tblSpotifyCharts AS c " +
                                   "  WHERE c.isrc = @isrc " +
                                   "  GROUP BY c.market, c.ChartListType, c.ChartTimeWindowType " +
                                   ") AS peak " +
                                   "INNER JOIN tblSpotifyCharts as maxC ON maxC.market = peak.market AND maxC.ChartListType = peak.ChartListType AND maxC.ChartTimeWindowType = peak.ChartTimeWindowType AND maxC.isrc = peak.isrc AND maxC.date = peak.maxDate " +
                                   "INNER JOIN tblSpotifyCharts as minC ON minC.market = peak.market AND minC.ChartListType = peak.ChartListType AND minC.ChartTimeWindowType = peak.ChartTimeWindowType AND minC.isrc = peak.isrc AND minC.date = peak.minDate " +
                                   "GROUP BY peak.market, peak.ChartListType, peak.ChartTimeWindowType";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var market = reader.GetString("market");
                            var chartListType = (SpotifyChartsListType)reader.GetInt32("ChartListType");
                            var chartTimeWindowType = (SpotifyChartsTimeWindow)reader.GetInt32("ChartTimeWindowType");

                            var latestDate = reader.GetDateTime("maxDate");
                            var latestPosition = reader.GetInt32("maxDatePosition");
                            var earliestDate = reader.GetDateTime("minDate");
                            var earliestPosition = reader.GetInt32("minDatePosition");

                            positions.Add(new TrackSummaryLatestAndEntryPosition()
                            {
                                Market = market,
                                ListType = chartListType,
                                TimeWindow = chartTimeWindowType,

                                LatestDate = latestDate,
                                LatestPosition = latestPosition,
                                EarliestDate = earliestDate,
                                EarliestPosition = earliestPosition,
                            });
                        }
                    }
                }
            }
            return positions;
        }

        internal async Task<List<TrackSummaryPeakPosition>> GetPeakPositionsAsync(string isrc)
        {
            List<TrackSummaryPeakPosition> peakPositions = new List<TrackSummaryPeakPosition>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT ct.market, ct.ChartListType, ct.ChartTimeWindowType, ct.peakPosition, Max(c.date) as latestPeakDate, Min(c.date) as earliestPeakDate " +
                                   "FROM ( " +
                                   "  SELECT isrc, market, ChartListType, ChartTimeWindowType, Min(chartPosition) as peakPosition " +
                                   "  FROM tblSpotifyCharts WHERE isrc = @isrc " +
                                   "  GROUP BY market, ChartListType, ChartTimeWindowType) as ct " +
                                   "INNER JOIN tblSpotifyCharts as c ON c.market = ct.market AND c.ChartListType = ct.ChartListType AND c.ChartTimeWindowType = ct.ChartTimeWindowType AND c.isrc = ct.isrc AND c.chartPosition = ct.peakPosition " +
                                   "GROUP BY ct.market, ct.ChartListType, ct.ChartTimeWindowType; ";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var market = reader.GetString("market");
                            var chartListType = (SpotifyChartsListType)reader.GetInt32("ChartListType");
                            var chartTimeWindowType = (SpotifyChartsTimeWindow)reader.GetInt32("ChartTimeWindowType");

                            var peakPosition = reader.GetInt32("peakPosition");
                            var latestPeakDate = reader.GetDateTime("latestPeakDate");
                            var earliestPeakDate = reader.GetDateTime("earliestPeakDate");

                            peakPositions.Add(new TrackSummaryPeakPosition()
                            {
                                Market = market,
                                ListType = chartListType,
                                TimeWindow = chartTimeWindowType,

                                PeakPosition = peakPosition,
                                LatestPeakDate = latestPeakDate,
                                EarliestPeakDate = earliestPeakDate,
                            });
                        }
                    }
                }
            }

            return peakPositions;
        }

        internal async Task<List<TrackSummaryCurrentAndPreviousPosition>> GetCurrentAndPreviousPositionAsync(string isrc)
        {
            List<TrackSummaryCurrentAndPreviousPosition> positions = new List<TrackSummaryCurrentAndPreviousPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT c2.market, c2.listtype, c2.TimeWindowType, sc1.date as currentDate, sc1.chartPosition as currentDatePosition, sc2.date as prevDate, sc2.chartPosition as prevDatePosition " +
                                   "FROM ( " +
                                   "  SELECT c1.market, c1.listtype, c1.TimeWindowType, c2.maxDate, Max(c1.date) as secondMaxDate " +
                                   "  FROM tblSpotifyChartsAvailableChart AS c1 " +
                                   "  INNER JOIN ( " +
                                   "    SELECT market, listtype, TimeWindowType, Max(date) as maxDate FROM tblSpotifyChartsAvailableChart " +
                                   "    GROUP BY market, ListType, TimeWindowType) AS c2 ON c2.market = c1.market AND c2.ListType = c1.listtype AND c2.TimeWindowType = c1.TimeWindowType " +
                                   "  WHERE c1.date != c2.maxDate " +
                                   "  GROUP BY c1.market, c1.ListType, c1.TimeWindowType ) as c2 " +
                                   "LEFT JOIN tblSpotifyCharts AS sc1 ON sc1.market = c2.market AND sc1.chartlisttype = c2.listtype AND sc1.chartTimeWindowType = c2.TimeWindowType AND sc1.date = c2.maxDate AND sc1.isrc = @isrc " +
                                   "LEFT JOIN tblSpotifyCharts AS sc2 ON sc2.market = c2.market AND sc2.chartlisttype = c2.listtype AND sc2.chartTimeWindowType = c2.TimeWindowType AND sc2.date = c2.secondMaxDate AND sc2.isrc = @isrc " +
                                   "WHERE sc1.chartposition IS NOT NULL OR sc2.chartposition IS NOT NULL ";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var market = reader.GetString("market");
                            var chartListType = (SpotifyChartsListType)reader.GetInt32("listtype");
                            var chartTimeWindowType = (SpotifyChartsTimeWindow)reader.GetInt32("TimeWindowType");

                            var currentDate = reader.GetDateTimeOrDefault("currentDate");
                            var currentDatePosition = reader.GetIntOrDefault("currentDatePosition");
                            var prevDate = reader.GetDateTimeOrDefault("prevDate");
                            var prevDatePosition = reader.GetIntOrDefault("prevDatePosition");

                            positions.Add(new TrackSummaryCurrentAndPreviousPosition()
                            {
                                Market = market,
                                ListType = chartListType,
                                TimeWindow = chartTimeWindowType,

                                CurrentDate = currentDate,
                                CurrentPosition = currentDatePosition,
                                PreviousDate = prevDate,
                                PreviousPosition = prevDatePosition,
                            });
                        }
                    }
                }
            }
            return positions;
        }

        private async Task<SimplePosition> GetSimplePositionAsync(string sql, string isrc, string market, SpotifyChartsListType listType, SpotifyChartsTimeWindow timeWindow)
        {
            SimplePosition position = null;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, conn);

                command.Parameters.AddWithValue("@market", market);
                command.Parameters.AddWithValue("@listType", listType);
                command.Parameters.AddWithValue("@timeWindowType", timeWindow);
                command.Parameters.AddWithValue("@isrc", isrc);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    if (!(await reader.IsDBNullAsync(0)) && !(await reader.IsDBNullAsync(1)))
                    {
                        position = new SimplePosition()
                        {
                            Date = reader.GetDateTime("Date"),
                            Position = reader.GetInt32("ChartPosition")
                        };
                    }
                }
                reader.Close();
            }

            return position;
        }
    }
}