using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Sony.Filtr.Database;
using SpreadsheetLight;
using MySqlConnector;
using Microsoft.AspNetCore.Hosting;

namespace Sony.Filtr.Core.InfluentialPlaylists
{
    public class InfluentialPlaylistsFactory
    {
        private IHostingEnvironment _hostingEnvironment;
        private const int _perCountryStartRow = 2;
        private const int _perCountryRankColumn = 1;
        private const int _perCountryMarketColumn = 2;
        private const int _perCountryPlaylistUriColumn = 3;
        private const int _perCountryPlaylistNameColumn = 4;

        private const int _globalStartRow = 2;
        private const int _globalRankColumn = 1;
        private const int _globalPlaylistUriColumn = 2;
        private const int _globalPlaylistNameColumn = 3;

        private const string _fileDirectory = @"/import/influentialplaylists/";

        public InfluentialPlaylistsFactory(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        public void ImportPlaylistFile(Stream uploadedFileStream, int year, int month)
        {
            var directory = Path.Combine(_hostingEnvironment.ContentRootPath, _fileDirectory);
            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            var slDocument = new SLDocument(uploadedFileStream);

            var countryRankings = ImportPerCountryRanking(slDocument);
            var countryRankingDataFilePath = WriteDataToFile(countryRankings, year, month);
            BulkAddToDatabase(countryRankingDataFilePath);


            var globalRankings = ImportGlobalRanking(slDocument);
            var globalRankingDataFilePath = WriteDataToFile(globalRankings, year, month);
            BulkAddToDatabase(globalRankingDataFilePath);

            //File.Delete(countryRankingDataFilePath);
            File.Delete(globalRankingDataFilePath);
        }

        

        private List<PlaylistRankItem> ImportPerCountryRanking(SLDocument slDocument)
        {
            slDocument.SelectWorksheet("Per country");

            var rankItems = new List<PlaylistRankItem>();

            var currentRow = _perCountryStartRow;

            var chartPosition = slDocument.GetCellValueAsString(currentRow, _perCountryRankColumn);

            while (!string.IsNullOrWhiteSpace(chartPosition))
            {
                rankItems.Add(new PlaylistRankItem()
                {
                    Rank = Convert.ToInt32(chartPosition),
                    Market = slDocument.GetCellValueAsString(currentRow, _perCountryMarketColumn),
                    PlaylistUri = slDocument.GetCellValueAsString(currentRow, _perCountryPlaylistUriColumn),
                    PlaylistName = slDocument.GetCellValueAsString(currentRow, _perCountryPlaylistNameColumn)
                });

                currentRow++;

                chartPosition = slDocument.GetCellValueAsString(currentRow, _perCountryRankColumn);
            }

            return rankItems;
        }

        private List<PlaylistRankItem> ImportGlobalRanking(SLDocument slDocument)
        {
            slDocument.SelectWorksheet("Global");

            var rankItems = new List<PlaylistRankItem>();

            var currentRow = _globalStartRow;

            var chartPosition = slDocument.GetCellValueAsString(currentRow, _globalRankColumn);

            while (!string.IsNullOrWhiteSpace(chartPosition))
            {
                rankItems.Add(new PlaylistRankItem()
                {
                    Rank = Convert.ToInt32(chartPosition),
                    Market = "Global",
                    PlaylistUri = slDocument.GetCellValueAsString(currentRow, _globalPlaylistUriColumn),
                    PlaylistName = slDocument.GetCellValueAsString(currentRow, _globalPlaylistNameColumn)
                });

                currentRow++;

                chartPosition = slDocument.GetCellValueAsString(currentRow, _globalRankColumn);
            }

            return rankItems;
        }

        private string WriteDataToFile(List<PlaylistRankItem> rankItems, int year, int month)
        {
            var filePath = Path.Combine(_hostingEnvironment.ContentRootPath, _fileDirectory + "bulkImport_" + Guid.NewGuid() + ".csv");
            
            using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate))
            {
                using (var fileWriter = new StreamWriter(saveFile, Encoding.UTF8))
                {
                    foreach (var item in rankItems)
                    {
                        var separatedValues = string.Join("|",
                            item.Rank.ToString(),
                            item.Market,
                            year,
                            month,
                            item.PlaylistName,
                            item.PlaylistUri
                            );

                        fileWriter.WriteLine(separatedValues);
                    }
                }
            }

            return filePath;
        }

        private void BulkAddToDatabase(string fileName)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var bulkLoader = new MySqlBulkLoader(conn);
                bulkLoader.Local = true;
                bulkLoader.FileName = fileName;
                bulkLoader.TableName = "tblTopPlaylists";
                bulkLoader.FieldTerminator = "|";
                bulkLoader.LineTerminator = Environment.NewLine;
                bulkLoader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
                bulkLoader.CharacterSet = "utf8";
                bulkLoader.Load();
            }

        }

        public List<string> GetMarkets()
        {
            var markets = new List<string>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                const string sql = "SELECT DISTINCT Market FROM tblTopPlaylists";
                var cmd = new MySqlCommand(sql, conn);

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    markets.Add(reader.GetString(0));
                }
                reader.Close();
            }

            return markets;
        }

        public List<TopPlaylistDate> GetMarketDates(string market)
        {
            var dates = new List<TopPlaylistDate>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                const string sql = "SELECT DISTINCT Year, Month FROM tblTopPlaylists WHERE Market = @market ORDER BY Year DESC, Month DESC";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@market", market);

                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    dates.Add(
                        new TopPlaylistDate() {Year = reader.GetInt32(0), Month = reader.GetInt32(1)}
                    );

                }

                reader.Close();
            }

            return dates;
        }


        internal List<PlaylistRankItem> GetChart(string market, TopPlaylistDate date)
        {
            var dates = new List<PlaylistRankItem>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var sql = string.Format("SELECT Rank, Market, PlaylistName, PlaylistURI FROM tblTopPlaylists" +
                    " WHERE Market = @market AND Year = @year AND Month = @month" +
                    " ORDER BY Year DESC, Month DESC");

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@market", market);
                cmd.Parameters.AddWithValue("@year", date.Year);
                cmd.Parameters.AddWithValue("@month", date.Month);

                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    dates.Add(
                        new PlaylistRankItem()
                        {
                            Rank = reader.GetInt32(0), Market = reader.GetString(1),
                            PlaylistName =  reader.GetString(2), PlaylistUri = reader.GetString(3)
                        }
                    );

                }

                reader.Close();
            }

            return dates;
        }
    }

    public class PlaylistRankItem
    {
        public int Rank { get; set; }
        public string Market { get; set; }
        public string PlaylistUri { get; set; }
        public string PlaylistName { get; set; }
        public int Move { get; set; }
        public bool New { get; set; }
    }

    public class TopPlaylistDate
    {
        public int Year { get; set; }
        public int Month { get; set; }
    }
}