using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Threading.Tasks;
using Medallion.Shell;
using MySql.Data.MySqlClient;
using NLog;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Core.EditorialPlaylists;
using Sony.Filtr.Core.LastModified;
using Sony.Filtr.Core.SpotifyBrowse;
using Sony.Filtr.Core.SpotifyRegion;
using Sony.Filtr.Core.TopLists;
using Sony.Filtr.DistributedCaching;
using Sony.Filtr.Globalization.v3;

namespace Sony.Filtr.Tasks.Tasks
{
    public class SyncStagingDatabaseTask : IScheduledTask
    {
        private readonly IApplicationInstanceManager _applicationInstanceManager;
        private readonly SpotifyBrowseManager _spotifyBrowseManager;
        private readonly EditorialPlaylistManager _editorialPlaylistManager;
        private readonly SpotifyRegionManager _spotifyRegionManager;
        private readonly LastModifiedManager _lastModifiedManager;
        private readonly ScheduledTaskManager _scheduledTaskManager;
        private readonly MarketGlobalizationHandler _marketGlobalizationHandler;
        private readonly TopListManager _topListManager;
        private readonly DistributedCacheHandler _distributedCacheHandler;
        private readonly Logger _logger;

        public SyncStagingDatabaseTask(IApplicationInstanceManager applicationInstanceManager, 
            SpotifyBrowseManager spotifyBrowseManager, EditorialPlaylistManager editorialPlaylistManager,
            SpotifyRegionManager spotifyRegionManager, LastModifiedManager lastModifiedManager, 
            ScheduledTaskManager scheduledTaskManager, MarketGlobalizationHandler marketGlobalizationHandler,
            TopListManager topListManager, DistributedCacheHandler distributedCacheHandler)
        {
            _applicationInstanceManager = applicationInstanceManager;
            _spotifyBrowseManager = spotifyBrowseManager;
            _editorialPlaylistManager = editorialPlaylistManager;
            _spotifyRegionManager = spotifyRegionManager;
            _lastModifiedManager = lastModifiedManager;
            _scheduledTaskManager = scheduledTaskManager;
            _marketGlobalizationHandler = marketGlobalizationHandler;
            _topListManager = topListManager;
            _distributedCacheHandler = distributedCacheHandler;
            _logger = NLog.LogManager.GetLogger("ClearDBSyncCacheTask");
        }

        public async Task<ScheduledTaskLog> ExecuteAsync(Guid scheduledTaskLogId)
        {
            _logger.Debug("Start");

            var startDatetime = DateTime.Now;

            var latestSyncDate = DateTime.Today.AddDays(-30);

            _logger.Info("Begin syncing tables.");
            await RunDbSyncScriptAsync(latestSyncDate);
            _logger.Info("Done syncing tables.");
            
            _logger.Debug("Done");

            return null;
            //return new ScheduledTasksInfo()
            //{
            //    StagingDBSyncStart = startDatetime,
            //    StagingDBSyncEnd = DateTime.Now,
            //};
        }
        
        private async Task RunDbSyncScriptAsync(DateTime latestSyncDate)
        {
            var culture = CultureInfo.GetCultureInfo("sv-SE");

            var dateString = latestSyncDate.Date.ToString("d", culture);

            //await CopyTableAsync("tblApplicationInstance");
            //_logger.Info("Clearing cache for markets.");
            //_applicationInstanceManager.ClearApplicationsCache();

            //await CopyTableAsync("tblEditorialPlaylist");

            //_logger.Info("Clearing cache for editorial playlists.");
            //await _editorialPlaylistManager.ClearGetCachesAsync();
            //await _editorialPlaylistManager.ClearIndividualPlaylistsCachesAsync();

            //await CopyTableAsync("tblSpotifyBrowseCategory");
            //await CopyTableAsync("tblSpotifyBrowseCategoryName");
            //await CopyTableAsync("tblSpotifyBrowseDate", $"Timestamp>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblSpotifyBrowseFeaturedPlaylist2", $"Date>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblSpotifyBrowsePeakPosition");

            //_logger.Info("Clearing cache for spotify browse.");
            //await _spotifyBrowseManager.ClearCachesAsync();

            //await CopyTableAsync("tblSpotifyRegion");
            //_logger.Info("Clearing cache for spotify regions.");
            //_spotifyRegionManager.ClearCache();

            //await CopyTableAsync("tblMostAdded");
            await CopyTableAsync("BuzzUser");

            //await CopyTableAsync("tblMarketGlobalizationKey");
            //await CopyTableAsync("tblMarketGlobalizationValue");

            //await CopyTableAsync("tblSpotifyArtist");
            //await CopyTableAsync("tblSpotifyAlbum");
            //await CopyTableAsync("tblSpotifyTrack");
            //await CopyTableAsync("tblSpotifyTrack2");
            //await CopyTableAsync("tblSpotifyTrackAlbum");
            //await CopyTableAsync("tblSpotifyTrackArtist");
            //await CopyTableAsync("tblSpotifyPlaylist");

            //await CopyTableAsync("tblSpotifyPlaylistTrackList");
            //await CopyTableAsync("tblSpotifyPlaylistTrackList", $"BuzzCategoryId IN (1, 5)");
            //await CopyTableAsync("tblSpotifyPlaylistTrackListHistory", $"Date>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblSpotifyPlaylistTrackListHistoryDates", $"Timestamp>'{dateString}'", dropAndRecreate: false);

            //_marketGlobalizationHandler.ClearCache();
            //_marketGlobalizationHandler.SetLastModifiedGlobal();

            //await CopyTableAsync("tblSpotifyUserFollowerLog", $"Date>'{dateString}'", dropAndRecreate: false);

            //await CopyTableAsync("tblSpotifyPlaylistStreamSummary");
            //await CopyTableAsync("tblSpotifyAnalyticsAccountDate");
            //await CopyTableAsync("tblSpotifyAnalyticsAccountPlaylistStreamInfo", $"Date>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblSpotifyAnalyticsAccountStreamInfo", $"Date>'{dateString}'", dropAndRecreate: false);

            //await CopyTableAsync("tblSpotifyPlaylistFollowerHistory", $"Date>'{dateString}'", dropAndRecreate: false);
            //_logger.Info("Updating last modified date for spotify playlist stats.");
            //_lastModifiedManager.SetLastModifyDate(null, EntityType.PlaylistStats);

            //await CopyTableAsync("tblSpotifyCharts", $"Date>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblSpotifyChartsAvailableChart", $"Date>'{dateString}'", dropAndRecreate: false);


            //await CopyTableAsync("tblSpotifyAnalyticsSonyTracks");

            //await CopyTableAsync("tblSpotifyPlaylistTrackTopList");
            //await CopyTableAsync("tblSpotifyPlaylistTrackTopListCombination");
            //await CopyTableAsync("tblSpotifyPlaylistTrackToplistTotal");

            //await CopyTableAsync("tblAppleMusicStreamSummary");

            await CopyTableAsync("tblAppleMusicCurator");
            //await CopyTableAsync("tblAppleMusicPlaylist");
            //await CopyTableAsync("tblAppleMusicArtist");
            //await CopyTableAsync("tblAppleMusicSong");
            //await CopyTableAsync("tblAppleMusicSongArtist");
            //await CopyTableAsync("tblAppleMusicPlaylistTracklist");

            //await CopyTableAsync("tblAppleMusicPlaylistTracklistHistory", $"Date>'{dateString}'", dropAndRecreate: false);
            //await CopyTableAsync("tblAppleMusicPlaylistTracklistHistoryDate", $"Date>'{dateString}'", dropAndRecreate: false);

            //await CopyTableAsync("tblAppleMusicPlaylistStreamSummary", $"Date>'{dateString}'", dropAndRecreate: false);

            //var toplistCacheKey = _topListManager.GetFilteringCombinationCacheKey();
            //_distributedCacheHandler.Remove(toplistCacheKey);
        }


        private async Task CopyTableAsync(string table, string where = null, bool dropAndRecreate = true)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["db"];
            var connectionstringBuilder = new MySqlConnectionStringBuilder(connectionString.ConnectionString);
            var host = connectionstringBuilder.Server;
            var username = connectionstringBuilder.UserID;
            var password = connectionstringBuilder.Password;
            //var database = connectionstringBuilder.Database;

            var mysqlDumpFilePath = @"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe";
            var mysqlFilePath = @"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe";

            List<string> readCommandArguments = new List<string>();
            readCommandArguments.Add($"--host={host}");
            readCommandArguments.Add($"--user={username}");
            readCommandArguments.Add($"--password={password}");
            readCommandArguments.Add("--single-transaction");
            readCommandArguments.Add("--quick");
            readCommandArguments.Add("--lock-tables=false");
            readCommandArguments.Add("--default-character-set=utf8mb4");
            //readCommandArguments.Add("--compress");
            readCommandArguments.Add("--insert-ignore");
            if (!dropAndRecreate)
            {
                readCommandArguments.Add("--add-drop-table=false");
                readCommandArguments.Add("--no-create-info");
            }
            

            //readCommandArguments.Add("--skip-extended-insert");
            if (where != null)
                readCommandArguments.Add($"--where={where}");
            
            readCommandArguments.Add("dbSony_dbo");
            readCommandArguments.Add(table);

            var writeCommandArguments = new List<string>();
            writeCommandArguments.Add($"--host={host}");
            writeCommandArguments.Add($"--user={username}");
            writeCommandArguments.Add($"--password={password}");
            writeCommandArguments.Add("--default-character-set=utf8mb4");
            writeCommandArguments.Add("--quick");
            //writeCommandArguments.Add("--compress");
            writeCommandArguments.Add("dbSonyStaging");

            if (string.IsNullOrWhiteSpace(where))
                _logger.Info($"Syncing table {table}.");
            else
                _logger.Info($"Syncing table {table}. With where-statement:" + where);

            //var commandTest = "\"" + mysqlDumpFilePath + "\"" + " " + string.Join(" ", readCommandArguments);
            //var commandTest = "\"" + mysqlDumpFilePath + "\"" + " " + new WindowsCommandLineSyntax().CreateArgumentString(readCommandArguments);
            //var readCmd = Command.Run(mysqlDumpFilePath, readCommandArguments) > new System.IO.FileInfo($"export-{table}.sql");
            //var result = await readCmd.Task;

            var copyCmd = Command.Run(mysqlDumpFilePath, readCommandArguments) |
                          Command.Run(mysqlFilePath, writeCommandArguments);

            var copyResult = await copyCmd.Task;
            if (!string.IsNullOrWhiteSpace(copyResult.StandardError))
            {
                _logger.Error("stdErr:" + copyResult.StandardError);
            }
            if (!string.IsNullOrWhiteSpace(copyResult.StandardOutput))
            {
                _logger.Info(copyResult.StandardOutput);
            }
            if (!copyResult.Success)
            {
                throw new Exception($"Could not sync table {table}!");
            }
        }
    }
}