using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sony.Filtr.Database;

namespace Sony.Filtr.Worker.Onetime
{
    public class ExportChartsData
    {
        public async Task ExecuteAsync()
        {
            FileInfo file = new FileInfo("ChartsData.csv");

            using (var fileStream = file.OpenWrite())
            {
                fileStream.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length);

                using (var fileWriter = new StreamWriter(fileStream))
                {
                    fileWriter.WriteLine(string.Join(";", "Market", "Date", "ChartPosition", "Track name", "Artist", "Streams", "Chart type", "Time window"));

                    using (var connection = DatabaseHandler.GetOpenConnection())
                    {
                        var cmd = connection.CreateCommand();
                        cmd.CommandText = "SELECT Market, Date, ChartPosition, TrackName, ArtistName, NumberOfStreams, ChartListType, ChartTimeWindowType FROM dbSony_dbo.tblSpotifyCharts " +
                                          "WHERE Market <> '' " +
                                          "ORDER BY Market, Date, ChartListType, ChartTimeWindowType, ChartPosition";

                        //cmd.CommandText = "SELECT Market, Date, ChartPosition, TrackName, ArtistName, NumberOfStreams, ChartListType, ChartTimeWindowType FROM dbSony_dbo.tblSpotifyCharts " +
                        //                  "WHERE Market = 'at' AND Date = '2013-12-01' " +
                        //                  "ORDER BY Market, Date, ChartListType, ChartTimeWindowType, ChartPosition";

                        var reader = await cmd.ExecuteReaderAsync();
                        while(await reader.ReadAsync())
                        {
                            var trackName = reader.GetString(3);
                            var artistName = reader.GetString(4);

                            var utf8 = Encoding.GetEncoding(65001);
                            var win1252 = Encoding.GetEncoding(1252);
                            
                            byte[] trackData = Encoding.Default.GetBytes(trackName);
                            trackName = Encoding.UTF8.GetString(trackData);

                            byte[] artistData = Encoding.Default.GetBytes(artistName);
                            artistName = Encoding.UTF8.GetString(artistData);

                            fileWriter.WriteLine(string.Join(";", reader.GetString(0), reader.GetDateTime(1).ToString("d", new CultureInfo("sv-se")), reader.GetString(2), trackName,
                                artistName, reader.GetInt32(5), reader.GetInt32(6), reader.GetInt32(7)));
                        }
                    }
                }
            }
        }
    }
}