using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify;
using Sony.Filtr.Utility.Extensions;
using SpreadsheetLight;

namespace Sony.Filtr.Worker.Onetime
{
    public class SpotifyPlaylistTrackListContainedTracks
    {
        private readonly SpotifyPlaylistHistoricTrackListManager _spotifyPlaylistHistoricTrackListManager;

        public SpotifyPlaylistTrackListContainedTracks(SpotifyPlaylistHistoricTrackListManager spotifyPlaylistHistoricTrackListManager)
        {
            _spotifyPlaylistHistoricTrackListManager = spotifyPlaylistHistoricTrackListManager;
        }

        public async Task ExecuteAsync()
        {
            ConcurrentBag<HistoricTrackInfoTrack> tracks = new ConcurrentBag<HistoricTrackInfoTrack>();

            var file = new FileInfo("input.csv");
            List<string> lines = new List<string>();
            using(var textReader = file.OpenText())
            {
                while (!textReader.EndOfStream)
                {
                    var line = textReader.ReadLine();
                    if (line != null)
                    {
                        lines.Add(line);
                    }
                }
            }

            var isrcPairs = lines.Select(BuildIsrcReference).FilterNull().ToList();

            await isrcPairs.ItemIndex().ForEachAsync(10, async pair =>
            {
                var isrc = pair.Item.isrc;
                var date = pair.Item.Date;

                var startDate = date.AddDays(-1);
                var endDate = date.AddDays(10);

                Console.WriteLine($"Isrc: {isrc}. Date: {date}. Item {pair.Index} / {isrcPairs.Count}");

                var playlists = await GetHistoricPlaylistsWithTrackBeforeDateAsync(isrc, startDate, endDate, (int)StaticBuzzCategory.Spotify);

                if (playlists.Any())
                {
                    tracks.Add(new HistoricTrackInfoTrack()
                    {
                        ISRC = isrc,
                        LiveFrom = date,
                        PlaylistPositions = playlists
                    });
                }
            });

            var doc = new SLDocument();
            doc.SetCellValue(1, 1, "ISRC");
            doc.SetCellValue(1, 2, "LiveFrom");
            doc.SetCellValue(1, 3, "PlaylistUri");
            doc.SetCellValue(1, 4, "Playlist Name");
            doc.SetCellValue(1, 5, "Date");
            doc.SetCellValue(1, 6, "Playlist Followers");
            doc.SetCellValue(1, 7, "Playlist Listeners");
            doc.SetCellValue(1, 8, "Track Position");

            int excelRowIndex = 2;
            foreach (var track in tracks.OrderBy(p=> p.ISRC))
            {
                foreach(var position in track.PlaylistPositions)
                {
                    doc.SetCellValue(excelRowIndex, 1, track.ISRC);

                    doc.SetCellValue(excelRowIndex, 2, track.LiveFrom);
                    doc.SetCellStyle(excelRowIndex, 2, new SLStyle() { FormatCode = "yyyy-mm-dd" });

                    doc.SetCellValue(excelRowIndex, 3, position.PlaylistUri);
                    doc.SetCellValue(excelRowIndex, 4, position.Name);

                    doc.SetCellValue(excelRowIndex, 5, position.Date);
                    doc.SetCellStyle(excelRowIndex, 5, new SLStyle() { FormatCode = "yyyy-mm-dd" });

                    doc.SetCellValue(excelRowIndex, 6, position.Followers);
                    doc.SetCellValue(excelRowIndex, 7, position.Listeners);

                    doc.SetCellValue(excelRowIndex, 8, position.Index+1);

                    excelRowIndex++;
                }
            }

            doc.SaveAs("output.xlsx");
            

        }

        private IsrcDatePair BuildIsrcReference(string line)
        {
            var values = line.Split(';');

            var isrc = values.Last();

            DateTime date;
            if (DateTime.TryParse(values.First(), out date))
            {
                return new IsrcDatePair()
                {
                    isrc = isrc,
                    Date = date
                };
            }
            return null;
        }

        public async Task<List<HistoricTrackPlaylistPosition>> GetHistoricPlaylistsWithTrackBeforeDateAsync(string isrc, DateTime startDate, DateTime endDate, int buzzCategoryId)
        {
            List<HistoricTrackPlaylistPosition> results = new List<HistoricTrackPlaylistPosition>();

            using (var conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                await conn.OpenAsync();

                var sql = "SELECT pl.PlaylistUri, pl.Name, pl.User, s.intSubscribers, hi.Date, hi.PlaylistIndex, SUM(ps.UniqueUsers) as uniqueUsers " +
                          "FROM tblSpotifyPlaylistTrackListHistory AS hi " +
                          "INNER JOIN tblSpotifyTrack2 AS ti ON hi.trackId = ti.TrackId " +
                          "INNER JOIN tblSpotifyPlaylist AS pl ON pl.PlaylistUri = hi.PlaylistUri " +
                          "LEFT JOIN tblEditorialPlaylistSubscriberLog AS s ON s.strSpotifyUri = pl.PlaylistUri AND s.datDay = hi.Date " +
                          "LEFT JOIN tblSpotifyAnalyticsPlaylistStreamInfo AS ps ON ps.PlaylistUri = pl.PlaylistUri AND ps.Date = hi.Date " +
                          "WHERE ti.ISRC = @isrc AND pl.Removed = 0 AND hi.date >= @startDate AND hi.date <= @endDate AND pl.buzzCategoryId = @buzzCategoryId " +
                          "GROUP BY pl.PlaylistUri, hi.date";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@isrc", isrc);
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                cmd.Parameters.AddWithValue("@buzzCategoryId", buzzCategoryId);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlistUri = reader.GetString("playlistUri");
                    var playlistName = reader.GetString("name");
                    var followers = reader.GetIntOrFallback("intSubscribers", 0);

                    var date = reader.GetDateTime("date");
                    var index = reader.GetInt32("playlistIndex");

                    var listeners = reader.GetIntOrFallback("uniqueUsers", 0);

                    var playlist = new HistoricTrackPlaylistPosition()
                    {
                        PlaylistUri = playlistUri,
                        Name = playlistName,
                        Followers = followers,
                        Date = date,
                        Index = index,
                        Listeners = listeners
                    };

                    results.Add(playlist);
                }

                conn.Close();
            }

            return results;
        }

        public class HistoricTrackPlaylistPosition
        {
            public string PlaylistUri { get; set; }

            public string Name { get; set; }

            public int Followers { get; set; }

            public DateTime Date { get; set; }

            public int Index { get; set; }

            public int Listeners { get; set; }
        }

        public class HistoricTrackInfoTrack
        {
            public string ISRC { get; set; }

            public DateTime LiveFrom { get; set; }

            public List<HistoricTrackPlaylistPosition> PlaylistPositions { get; set; }
        }

        public class IsrcDatePair
        {
            public string isrc { get; set; }

            public DateTime Date { get; set; }
        }
    }
}