using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Spreadsheet;
using NLog;
using PetaPoco.Business;
using Sony.Filtr.Core.Factory;
using Sony.Filtr.Core.SpotifyWeekly;
using Sony.Filtr.Database;
using Sony.Filtr.Utility;
using SpreadsheetLight;

namespace Sony.Filtr.Tasks.Tasks.Spotify
{
    public class SpotifyWeeklyTopListTask : IScheduledTask
    {
        private SpotifyWeeklyManager _spotifyWeeklyManager;
        private readonly StorageFactory _storageFactory;
        private readonly Logger _logger;
        private const string pattern = @"[^:]+$";


        public SpotifyWeeklyTopListTask(StorageFactory storageFactory, SpotifyWeeklyManager spotifyWeeklyManager)
        {
            _storageFactory = storageFactory;
            _spotifyWeeklyManager = spotifyWeeklyManager;
            _logger = LogManager.GetLogger("SpotifyWeeklyTopListTask");
        }

        private const string _GlobalCountryName = "Global";
        private const string _TopTracksPerCountryTab = "Top tracks per country";
        private const string _TopArtistsPerCountryTab = "Top artists per country";
        private const string _TopAlbumsPerCountryTab = "Top albums per country";
        private const string _TopPlaylistsPerCountryTab = "Top playlists per country";
        private const string _TopArtistsGlobalTab = "Top artists global";
        private const string _TopAlbumsGlobalTab = "Top albums global";
        private const string _TopPlaylistsGlobalTab = "Top playlists global";

        public async Task<ScheduledTaskLog> ExecuteAsync(Guid scheduledTaskLogId)
        {
            const string s3Bucket = "sme-spotify-top-reports";
            const string incomingPath = "incoming/";
            const string processedPath = "processed/";

            _logger.Debug($"Checking for files in bucket {s3Bucket} and folder {incomingPath}");
            var files = await _storageFactory.GetObjectsAsync(s3Bucket, incomingPath);

            //Uncomment in case of changing database
            //await BackfillDataForPlaylistId();
            _logger.Debug($"Found {files.Count} files");
            foreach (var file in files)
            {
                //Filename should be in format 2018-07-02.xlsx 
                var fileName = Path.GetFileNameWithoutExtension(file);
                if (!DateTime.TryParse(fileName, out var date))
                {
                    _logger.Debug($"Ignoring file {file}, could not read date from filename");
                    //Could not get date from filename, ignore file.
                    continue;
                }

                _logger.Debug($"Begin reading {file}");
                var fileContent = _storageFactory.GetObject(file, s3Bucket);

                _logger.Debug($"Begin opening {file} as excel document");
                var doc = new SLDocument(fileContent);
                _logger.Debug($"Opened excel document for date {date}");

                var isValid = IsValidDocument(doc);
                if (!isValid)
                {
                    _logger.Debug($"Ignoring file {file} because it didn't appear to be a valid file");
                    continue;
                }

                await ImportFileData(doc, date);

                _logger.Debug($"Done with {file}, moving to {processedPath}");
                await MarkUpcFileAsProcessedAsync(s3Bucket, processedPath, file);
            }

            return null;
        }

        private async Task ImportFileData(SLDocument doc, DateTime date)
        {
            _logger.Debug($"Begin reading top tracks");
            var topTracksPerCountry = GetTopTracksPerCountry(doc, date);
            var topTrackGlobal = GetTopTracksGlobal(doc, date);
            var topTracks = topTracksPerCountry.Union(topTrackGlobal).ToList();
            _logger.Debug($"Begin importing top tracks");
            await PetaPocoRepository.Instance.ImportBulkFileLoaderAsync(topTracks);
            _logger.Debug($"Done importing top tracks");

            _logger.Debug($"Begin reading top artists");
            var topArtistsPerCountry = GetTopArtistsPerCountry(doc, date);
            var topArtistsGlobal = GetTopArtistsGlobal(doc, date);
            var topArtists = topArtistsPerCountry.Union(topArtistsGlobal).ToList();
            _logger.Debug($"Begin importing top artists");
            await PetaPocoRepository.Instance.ImportBulkFileLoaderAsync(topArtists);
            _logger.Debug($"Done importing top artists");

            _logger.Debug($"Begin reading top albums");
            var topAlbumsPerCountry = GetTopAlbumsPerCountry(doc, date);
            var topAlbumsGlobal = GetTopAlbumsGlobal(doc, date);
            var topAlbums = topAlbumsPerCountry.Union(topAlbumsGlobal).ToList();
            _logger.Debug($"Begin importing top albums");
            await PetaPocoRepository.Instance.ImportBulkFileLoaderAsync(topAlbums);
            _logger.Debug($"Done importing top albums");

            _logger.Debug($"Begin reading top playlists");
            var topPlaylistsPerCountry = GetTopPlaylistsPerCountry(doc, date);
            var topPlaylistsGlobal = GetTopPlaylistsGlobal(doc, date);
            var topPlaylists = topPlaylistsPerCountry.Union(topPlaylistsGlobal).ToList();
            _logger.Debug($"Begin importing top playlists");
            await PetaPocoRepository.Instance.ImportBulkFileLoaderAsync(topPlaylists);
            _logger.Debug($"Done importing top playlists");
        }

        private bool IsValidDocument(SLDocument doc)
        {
            var worksheetNames = doc.GetWorksheetNames();
            List<string> worksheetsThatShouldExist = new List<string>()
            {
                _TopTracksPerCountryTab,
                _TopArtistsPerCountryTab,
                _TopAlbumsPerCountryTab,
                _TopPlaylistsPerCountryTab,
                _TopArtistsGlobalTab,
                _TopAlbumsGlobalTab,
                _TopPlaylistsGlobalTab
            };
            if (worksheetNames.Intersect(worksheetsThatShouldExist).Count() != worksheetsThatShouldExist.Count)
            {
                //All worksheets not present
                return false;
            }

            return true;
        }

        private async Task MarkUpcFileAsProcessedAsync(string bucketName, string processedFolder, string filePath)
        {
            var destinationPath = Flurl.Url.Combine(processedFolder, filePath.Split('/').Last());

            await _storageFactory.CopyObjectAsync(bucketName, filePath, destinationPath);
            await _storageFactory.DeleteObjectAsync(bucketName, filePath);
        }

        private IEnumerable<SpotifyTopTrack> GetTopTracksGlobal(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet("Top tracks global");

            var items = new List<SpotifyTopTrack>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopTrack()
                {
                    Date = date,
                    Country = _GlobalCountryName,
                    TrackName = row.Cells.ElementAt(0),
                    MainArtists = row.Cells.ElementAt(1),
                    Uri = null, //Not available on global
                    Streams = 0,
                    Rank = Convert.ToInt32(row.Cells.ElementAt(2)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(3)),
                    StreamIncrease = null,
                    StreamPercentageIncrease = null,
                };

                items.Add(item);
            }

            return items;
        }

        private readonly CultureInfo _excelDoubleCulture = CultureInfo.GetCultureInfo("en-US");


        private double? GetPercentageValue(string value)
        {
            if (!string.IsNullOrWhiteSpace(value))
            {
                value = value.Replace("%", string.Empty);

                Double.TryParse(value, NumberStyles.Any, _excelDoubleCulture, out double percentageValue);
                return percentageValue;
            }
            return null;
        }

        private IEnumerable<SpotifyTopTrack> GetTopTracksPerCountry(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet(_TopTracksPerCountryTab);

            var items = new List<SpotifyTopTrack>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopTrack
                {
                    Date = date,
                    Country = row.Cells.ElementAt(0),
                    TrackName = row.Cells.ElementAt(1),
                    MainArtists = row.Cells.ElementAt(2),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(3)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(4)),
                };

                items.Add(item);
            }

            return items;
        }

        private IEnumerable<SpotifyTopArtist> GetTopArtistsPerCountry(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet(_TopArtistsPerCountryTab);

            var items = new List<SpotifyTopArtist>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopArtist()
                {
                    Date = date,
                    Country = row.Cells.ElementAt(0),
                    MainArtists = row.Cells.ElementAt(1),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(2)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(3)),
                };

                items.Add(item);
            }

            return items;
        }

        private IEnumerable<SpotifyTopAlbum> GetTopAlbumsPerCountry(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet(_TopAlbumsPerCountryTab);

            var items = new List<SpotifyTopAlbum>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopAlbum
                {
                    Date = date,
                    Country = row.Cells.ElementAt(0),
                    AlbumName = row.Cells.ElementAt(1),
                    AlbumArtist = row.Cells.ElementAt(2),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(3)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(4)),
                };

                items.Add(item);
            }

            return items;
        }

        private IEnumerable<SpotifyTopPlaylist> GetTopPlaylistsPerCountry(SLDocument doc, DateTime date)
        {
            var regex = new Regex(pattern);
            doc.SelectWorksheet(_TopPlaylistsPerCountryTab);
            var items = new List<SpotifyTopPlaylist>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                try
                {
                    var item = new SpotifyTopPlaylist
                    {
                        Date = date,
                        Country = row.Cells.ElementAt(0),
                        PlaylistName = row.Cells.ElementAt(1),
                        PlaylistUri = row.Cells.ElementAt(2),
                        Rank = Convert.ToInt32(row.Cells.ElementAt(3)),
                        Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(4)),
                        PlaylistId = regex.Match(row.Cells.ElementAt(2)).Value.ToString()
                    };

                    items.Add(item);
                }
                catch (Exception ex)
                {
                    _logger.Error(ex, $"Error in parsing csv document. Method: GetTopPlaylistsPerCountry. Rows processed:{items.Count} ");
                }
            }
            
            return items;
        }

        private IEnumerable<SpotifyTopArtist> GetTopArtistsGlobal(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet(_TopArtistsGlobalTab);

            var items = new List<SpotifyTopArtist>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopArtist
                {
                    Date = date,
                    Country = _GlobalCountryName,
                    MainArtists = row.Cells.ElementAt(0),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(1)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(2)),
                };

                items.Add(item);
            }

            return items;
        }

        private IEnumerable<SpotifyTopAlbum> GetTopAlbumsGlobal(SLDocument doc, DateTime date)
        {
            doc.SelectWorksheet(_TopAlbumsGlobalTab);

            var items = new List<SpotifyTopAlbum>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopAlbum
                {
                    Date = date,
                    Country = _GlobalCountryName,
                    Licensor = null, //Not available on global
                    AlbumName = row.Cells.ElementAt(0),
                    AlbumArtist = row.Cells.ElementAt(1),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(2)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(3)),
                };

                items.Add(item);
            }

            return items;
        }

        private IEnumerable<SpotifyTopPlaylist> GetTopPlaylistsGlobal(SLDocument doc, DateTime date)
        {
            var regex = new Regex(pattern);
            doc.SelectWorksheet(_TopPlaylistsGlobalTab);
            var items = new List<SpotifyTopPlaylist>();
            var rows = GetRowStringData(doc);
            foreach (var row in rows)
            {
                var item = new SpotifyTopPlaylist
                {
                    Date = date,
                    Country = _GlobalCountryName,
                    PlaylistName = row.Cells.ElementAt(0),
                    PlaylistUri = row.Cells.ElementAt(1),
                    Rank = Convert.ToInt32(row.Cells.ElementAt(2)),
                    Movement = Maybe.ToIntOrDefault(row.Cells.ElementAt(3)),
                    PlaylistId = regex.Match(row.Cells.ElementAt(1)).Value.ToString()
            };

                items.Add(item);
            }

            return items;
        }

        private async Task BackfillDataForPlaylistId()
        {
            var regex = new Regex(pattern);
            var spotifyPlaylistsWithNullId = _spotifyWeeklyManager.GetSpotifyPlaylistsWithNullId();
            var splittedLists = Maybe.SplitList<SpotifyTopPlaylist>(spotifyPlaylistsWithNullId, 1000);
            
            foreach (var list in splittedLists)
            {
                var sqlCommand = new StringBuilder();
                foreach (var row in list)
                {
                    row.PlaylistId = regex.Match(row.PlaylistUri).Value.ToString();
                    sqlCommand.Append("UPDATE tblSpotifyWeeklyTopPlaylist SET PlaylistId=\"" + regex.Match(row.PlaylistUri).Value + "\" WHERE PlaylistUri=\"" + row.PlaylistUri + "\";");
                }
                var commandToString = sqlCommand.ToString();
                await _spotifyWeeklyManager.SetSpotifyPlaylistsId(commandToString);
            }
        }

        private static IEnumerable<Row> GetRowStringData(SLDocument doc, bool skipHeader = true)
        {
            var cells = doc.GetCells();
            var sharedStrings = doc.GetSharedStrings();

            List<Row> rows = new List<Row>();
            foreach (var cell in cells)
            {
                List<string> cellValues = new List<string>();
                foreach (var cell1 in cell.Value)
                {
                    if (cell1.Value.DataType == CellValues.SharedString)
                    {
                        var index = Convert.ToInt32(cell1.Value.NumericValue);
                        var sharedString = sharedStrings.ElementAt(index);
                        var sharedStringValue = sharedString.ToPlainString();

                        if (cell1.Key - cellValues.Count > 1 )
                        {
                            cellValues.Add(String.Empty);
                        }
                        cellValues.Add(sharedStringValue);
                        
                    }
                    else if (cell1.Value.DataType == CellValues.Number)
                    {
                        cellValues.Add(cell1.Value.NumericValue.ToString(CultureInfo.InvariantCulture));
                    }
                    else if (cell1.Value.DataType == CellValues.String)
                    {
                        cellValues.Add(cell1.Value.CellText);
                    }
                }
                if (rows.Count != 0 && cellValues.Count < rows[0].Cells.Count)
                {
                    cellValues.Add(String.Empty);
                }
                rows.Add(new Row() { Cells = cellValues });
            }

            if (skipHeader)
                return rows.Skip(1);

            return rows;
        }

        private class Row
        {
            public List<string> Cells { get; set; }
        }
    }

}