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

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

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

                using (var fileWriter = new StreamWriter(fileStream))
                {
                    using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                    {
                        var cmd = connection.CreateCommand();
                        cmd.CommandText = "SELECT * FROM tblSpotifyTrack2";
                        
                        var reader = await cmd.ExecuteReaderAsync();

                        var columnNames = new List<string>();
                        for(int i = 0; i < reader.FieldCount; i++)
                        {
                            columnNames.Add(reader.GetName(i));
                        }
                        
                        fileWriter.WriteLine(string.Join(";", columnNames));

                        while (await reader.ReadAsync())
                        {
                            List<string> rowValues = new List<string>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader.IsDBNull(i))
                                {
                                    rowValues.Add("NULL");
                                }
                                else
                                {
                                    rowValues.Add(reader.GetString(i));
                                }
                                
                            }
                            fileWriter.WriteLine(string.Join(";", rowValues));
                        }
                    }
                }
            }
        }
    }
}