using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Web.Hosting;
using Sony.Filtr.Database;
using SpreadsheetLight;
using MySql.Data.MySqlClient;

namespace Sony.Filtr.Core.AdStats
{
    public class AdStatsFactory
    {

        private const int _startRow = 2;

        private const int _countryColumn = 1;
        private const int _platformColumn = 2;
        private const int _advertiserColumn = 3;
        private const int _nameColumn = 4;
        private const int _typeColumn = 5;
        private const int _dateColumn = 6;
        private const int _deliveredColumn = 7;
        private const int _clicksColumn = 8;
        private const int _ctrColumn = 9;

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

        public AdStatsFactory()
        {
            
        }

        public void ImportAdStatsFile(Stream uploadedFileStream, int year, int month)
        {
            var directory = HostingEnvironment.MapPath(_fileDirectory);
            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            var slDocument = new SLDocument(uploadedFileStream);

            var adStatsFromFile = ImportFromFile(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<SpotifyAdStatRow> ImportFromFile(SLDocument slDocument)
        {
            var currentRow = _startRow;

            var market = slDocument.GetCellValueAsString(currentRow, _countryColumn);

            var statRows = new List<SpotifyAdStatRow>();
            while (!string.IsNullOrWhiteSpace(market))
            {
                var dateValue = DateTime.Parse(slDocument.GetCellValueAsString(currentRow, _dateColumn), CultureInfo.GetCultureInfo("en-us"));
                
                statRows.Add(new SpotifyAdStatRow(){
                    Platform = slDocument.GetCellValueAsString(currentRow, _platformColumn),
                    Advertiser = slDocument.GetCellValueAsString(currentRow, _advertiserColumn),
                    Name = slDocument.GetCellValueAsString(currentRow, _nameColumn),
                    Type = slDocument.GetCellValueAsString(currentRow, _typeColumn),
                    Date = dateValue,
                    Delivered = slDocument.GetCellValueAsInt32(currentRow, _deliveredColumn),
                    Clicks = slDocument.GetCellValueAsInt32(currentRow, _clicksColumn),
                    ClickThroughRatePercentage = Convert.ToDouble(slDocument.GetCellValueAsString(currentRow, _ctrColumn).Replace("%", string.Empty))
                });

                currentRow++;

                market = slDocument.GetCellValueAsString(currentRow, _countryColumn);
            }

            return statRows;
        }



     

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

        }

    }

    public class SpotifyAdStatRow
    {
        public string Name { get; set; }
        public string Advertiser { get; set; }
        public string Market { get; set; }
        public string Platform { get; set; }
        public string Type { get; set; }
        public DateTime Date { get; set; }
        public int Delivered { get; set; }
        public int Clicks { get; set; }
        public double ClickThroughRatePercentage { get; set; }
    }


}