using Newtonsoft.Json;
using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Timers;
using System.Linq;
using Dfs.WayneChina.SpsDataCourier.HostModels;
using Dfs.WayneChina.SpsDataCourier.Guard;
using System.Collections.Generic;
using Dfs.WayneChina.SpsDataCourier.SpsData;
using Microsoft.EntityFrameworkCore;

namespace Dfs.WayneChina.SpsDataCourier
{
    public class Downloader
    {
        #region Fields

        private Timer _timer;
        private readonly int _interval;
        private readonly ConnectionInfo _connectionInfo;

        private HttpClient _client = new HttpClient();

        private string grantType = "password";
        private string tokenAuthPath = "token";
        private string authScheme = "bearer";

        private AuthToken currentAuthToken;
        private string _spsDbConnString;

        private long? localVersion = null;

        private readonly DbMonitor _dbMonitor;

        private readonly string _checkVersionRelativeUrl;
        private readonly string _syncDataRelativeUrl;
        private bool _excludeCurrentSite = false;
        #endregion

        #region Logger

        static NLog.Logger logger = NLog.LogManager.LoadConfiguration("NLog.config").GetLogger("SpsDataCourier");

        #endregion

        #region Constructor

        public Downloader(ConnectionInfo connectionInfo, DbMonitor dbMonitor, string spsConnString, int interval, 
            string versionRelativeUrl, string dataRelativeUrl, bool excludeCurrentSite)
        {
            _connectionInfo = connectionInfo;
            _dbMonitor = dbMonitor;
            _spsDbConnString = spsConnString;
            _interval = interval;
            _checkVersionRelativeUrl = versionRelativeUrl;
            _syncDataRelativeUrl = dataRelativeUrl;
            _excludeCurrentSite = excludeCurrentSite;
        }

        #endregion

        #region Start

        public void Start()
        {
            _timer = new Timer();
            _timer.Interval = _interval * 1000;
            _timer.Elapsed += DownloadTimerElapsed;
            _timer.Start();
        }

        #endregion

        #region Stop

        public void Stop()
        {
            if (_timer != null)
            {
                _timer.Elapsed -= DownloadTimerElapsed;
                _timer.Close();
            }
        }

        #endregion

        #region Timer elapsed event

        /// <summary>
        /// 同步云端定时器
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private async void DownloadTimerElapsed(object sender, ElapsedEventArgs e)
        {
            logger.Info("Downloader timer elapsed");

            _timer.Stop();

            //优先上传
            var count = await CheckPendingUpload();

            if (count > 0)
            {
                logger.Info($"There are {count} accounts or cards updated, wait for them to be uploaded first!");
                _timer.Start();
                return;
            }
            else
            {
                await CheckHostDataVersionAsync();
                _timer.Start();
            }
        }

        #endregion

        #region Check pending account updates

        private Task<int> CheckPendingUpload()
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var records = context.TTableaudit
                    .Where(e => e.AccountUpdated.HasValue && e.AccountUpdated.Value != 0 ||
                                e.CardInfoUpdated.HasValue && e.CardInfoUpdated.Value != 0)
                    .ToList();

                logger.Info($"Before download, Accounts updated count: " +
                    $"{records.Count(r => r.AccountUpdated.HasValue && r.AccountUpdated.Value != 0)}");

                logger.Info($"Before download, Cards updated count: " +
                    $"{records.Count(r => r.CardInfoUpdated.HasValue && r.CardInfoUpdated.Value != 0)}");

                return Task.FromResult(records.Count);
            }
        }

        #endregion

        #region Check host data version

        private async Task CheckHostDataVersionAsync()
        {
            if (currentAuthToken != null && currentAuthToken.IsTokenValid())
            {
                using (var context = new GuardDbContext())
                {
                    var currentVersion = context.DataVersion
                        .OrderByDescending(v => v.LastUpdate).FirstOrDefault();

                    if (currentVersion != null)
                    {
                        localVersion = currentVersion.VersionNo;
                        logger.Info($"Setting local version to {localVersion}");

                        logger.Info($"Current local version: {currentVersion.VersionNo}");
                        await CheckVersionStartDownloadAsync(currentVersion.VersionNo);
                    }
                    else if(currentVersion == null)
                    {
                        logger.Info($"Local version does not exist!");
                        await CheckVersionStartDownloadAsync(0);
                    }
                }
            }
            else
            {
                logger.Info("No valid token now, start to get it.");
                await GetTokenAsync(_connectionInfo.UserName, _connectionInfo.Password, _connectionInfo.AuthServiceBaseUrl);
            }
        }

        #endregion

        #region Compare version and start download

        private async Task CheckVersionStartDownloadAsync(long versionNo)
        {
            var hostVersionInfo = await GetDataVersionAsync(versionNo);

            if (hostVersionInfo != null)
            {
                logger.Info($"Host data version: {hostVersionInfo.VersionNo}");
                long innerDataVersion = 0;
                if (hostVersionInfo.VersionNo != versionNo)
                {
                    _dbMonitor.Stop(); // stop for a while
                    innerDataVersion = await DownloadDataAsync(versionNo);
                    logger.Info($"Inner data version: {innerDataVersion}");
                }

                if (innerDataVersion != 0)
                {
                    logger.Info($"Inner data version: {innerDataVersion}, host data version: {hostVersionInfo.VersionNo}");
                    hostVersionInfo.VersionNo = innerDataVersion;
                }

                await SaveHostVersion(hostVersionInfo);
                _dbMonitor.Start();

                localVersion = hostVersionInfo.VersionNo;
            }
        }

        /// <summary>
        /// 数据版本同步
        /// </summary>
        /// <param name="versionNo"></param>
        /// <returns></returns>
        private async Task<HostVersionInfo> GetDataVersionAsync(long versionNo)
        {
            string baseUrl = _connectionInfo.AccountServiceBaseUrl;
            //string accountingUrl = _connectionInfo.AccountServiceRelativeUrl;
            string versionUrl = string.Format(_checkVersionRelativeUrl + "?excludingCurrentBu={0}&versionNo={1}", _excludeCurrentSite, versionNo);
            string url = string.Concat(baseUrl, versionUrl);
            logger.Info($"Version url: {url}");

            _client.DefaultRequestHeaders.Clear();
            _client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(authScheme, currentAuthToken.AccessToken);
            _client.DefaultRequestHeaders.Add("DeviceSN", _connectionInfo.DeviceSN);

            try
            {
                var versionResult = await _client.GetAsync(url).ConfigureAwait(false);

                if (versionResult.IsSuccessStatusCode)
                {
                    HostVersionResult hostVersionResult = null;

                    await versionResult.Content.ReadAsStringAsync().ContinueWith(x =>
                    {
                        hostVersionResult = JsonConvert.DeserializeObject<HostVersionResult>(x?.Result);
                    });

                    if (hostVersionResult != null && hostVersionResult.Result != null)
                    {
                        return hostVersionResult.Result;
                    }
                }
                else
                {
                    logger.Warn($"Get host version, status code: {versionResult.StatusCode}");
                    var content = await versionResult.Content.ReadAsStringAsync();
                    versionResult.Content?.Dispose();
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }

            return null;
        }

        private async Task SaveHostVersion(HostVersionInfo hostVersionInfo)
        {
            try
            {
                using (var context = new GuardDbContext())
                {
                    if (!context.DataVersion.Any(v => v.VersionNo == hostVersionInfo.VersionNo))
                    {
                        var version = new DataVersion();
                        version.VersionNo = hostVersionInfo.VersionNo;
                        version.CommitFlag = 1;
                        version.LastUpdate = DateTime.Now;
                        context.DataVersion.Add(version);

                        await context.SaveChangesAsync();

                        logger.Info($"Host version no: {hostVersionInfo.VersionNo}, saved into db");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error($"Exception in saving host version info: {ex.ToString()}");
            }
        }

        #endregion

        #region Download

        private async Task<long> DownloadDataAsync(long versionNo)
        {
            logger.Info("==============================");
            logger.Info($"Start data synchronizing, from local version: {versionNo}");

            string versionSyncRelativeUrl = string.Format(_syncDataRelativeUrl + "?versionNo={0}&excludingCurrentBu={1}", versionNo, _excludeCurrentSite);
            var url = string.Concat(_connectionInfo.AuthServiceBaseUrl, versionSyncRelativeUrl);

            _client.DefaultRequestHeaders.Clear();
            _client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(authScheme, currentAuthToken.AccessToken);
            _client.DefaultRequestHeaders.Add("DeviceSN", _connectionInfo.DeviceSN);

            logger.Info($"sync data url: {url}");

            try
            {
                var response = await _client.GetAsync(url).ConfigureAwait(false);
                
                logger.Info($"Downloading finished, status code: {response.StatusCode}");

                HostDataResponse hostDataResponse = null;

                if (response.IsSuccessStatusCode)
                {
                    await response.Content.ReadAsStringAsync().ContinueWith(x =>
                    {
                        logger.Info($"data response: {x?.Result}");
                        hostDataResponse = JsonConvert.DeserializeObject<HostDataResponse>(x?.Result);
                    });

                    if (hostDataResponse != null && hostDataResponse.Result != null)
                    {
                        await SaveOfflineAccounts(hostDataResponse.Result.OfflineAccountInfoList, hostDataResponse.Result.VersionNo);
                        logger.Info($"Saved account info at: {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}, " +
                            $"total count: {hostDataResponse.Result.OfflineAccountInfoList?.Count}");

                        await SaveOfflineCardInfoList(hostDataResponse.Result.OfflineCardInfoList, hostDataResponse.Result.VersionNo);
                        logger.Info($"Saved card info at: {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}, " +
                            $"total count: {hostDataResponse.Result.OfflineCardInfoList?.Count}");

                        await SaveOfflineGrayInfoList(hostDataResponse.Result.OfflineGrayInfoList, hostDataResponse.Result.VersionNo);
                        logger.Info($"Saved gray info at: {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}, " +
                            $"total count: {hostDataResponse.Result.OfflineGrayInfoList?.Count}");

                        await SaveOfflineBlackCardInfoList(hostDataResponse.Result.OfflineBlackCardInfoList, hostDataResponse.Result.VersionNo);
                        logger.Info($"Saved black card info at: {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}, " +
                            $"total count: {hostDataResponse.Result.OfflineBlackCardInfoList?.Count}");

                        await SaveOfflineCardRepLossInfoList(hostDataResponse.Result.OfflineCardRepLossList,
                            hostDataResponse.Result.VersionNo);
                        logger.Info($"Saved CardRepLoss info at: {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}, " +
                                    $"total count: {hostDataResponse.Result.OfflineCardRepLossList?.Count}");

                        var firstAccount = hostDataResponse?.Result?.OfflineAccountInfoList.FirstOrDefault();
                        var firstCard = hostDataResponse?.Result?.OfflineCardInfoList.FirstOrDefault();
                        var firstBlackCard = hostDataResponse?.Result?.OfflineBlackCardInfoList.FirstOrDefault();
                        var firstGrayInfo = hostDataResponse?.Result?.OfflineGrayInfoList.FirstOrDefault();
                        var firstCardRepLoss = hostDataResponse?.Result?.OfflineBlackCardInfoList.FirstOrDefault();

                        logger.Info($"size: {hostDataResponse?.Result?.Size}, first account: {firstAccount?.acctID}, " +
                            $"first card: {firstCard?.cardNo}, first black card: {firstBlackCard?.cardNo}," +
                            $"first gray info: {firstGrayInfo?.cardNo}, first cardRepLoss info: {firstCardRepLoss?.cardNo}");

                        return hostDataResponse.Result.VersionNo;
                    }
                }
                else
                {
                    var content = await response.Content.ReadAsStringAsync();
                    response.Content?.Dispose();
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }

            return versionNo;
        }

        #endregion

        #region Persist Offline AccountInfo

        private async Task SaveOfflineAccounts(List<OfflineAccountInfo> offlineAccountInfoList, long versionNo)
        {
            var accounts = new List<Account>();

            foreach (var accountInfo in offlineAccountInfoList)
            {
                var account = new Account();
                account.Gid = Convert.ToUInt64(accountInfo.gid);
                account.AccountSNo = accountInfo.acctSNo;
                account.SNo = accountInfo.sno;
                account.AccountId = accountInfo.acctID;
                account.AccountName = accountInfo.belongTo;
                account.Address = accountInfo.address;
                account.PhoneNo = accountInfo.phoneNo;
                account.AccountType = accountInfo.acctType;
                account.Amount = Convert.ToInt32(accountInfo.amount);
                account.AmountType = 0;
                account.FuelNo = accountInfo.fuelNo;
                account.Credit = accountInfo.gift;
                account.State = accountInfo.acctState;
                account.AccountDate = accountInfo.acctDate;
                account.CertType = accountInfo.certfType;
                account.CertNo = accountInfo.certfNo;
                account.RechargeTotal = accountInfo.rechgTotal;
                account.TMac = accountInfo.tmac;
                account.WaitMalloc = accountInfo.waitMalloc;
                account.EnableSms = accountInfo.enableSms;
                account.UploadFlag = accountInfo.uploadFlag;
                account.VersionNo = versionNo;
                account.LastUpdate = DateTime.Now;
                accounts.Add(account);
            }

            try
            {
                using (var context = new GuardDbContext())
                {
                    context.Account.AddRange(accounts);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsDbAcctInfo(accounts);

            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsDbAcctInfo(IEnumerable<Account> accounts)
        {
            var spsAccounts = ConvertToSpsAccounts(accounts);

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                if (localVersion == null || localVersion.HasValue && localVersion.Value == 0)
                {
                    spsDbContext.TAcctinfo.AddRange(spsAccounts);
                    await spsDbContext.SaveChangesAsync();
                    await CleanSyncCreatedAccounts(spsAccounts.Select(a => a.Gid).ToList());
                }
                else
                {
                    logger.Info("Insert or update acct info...");

                    var acctIds = spsAccounts.Select(a => a.AcctId).ToList();
                    
                    var matchedAccounts = spsDbContext.TAcctinfo
                        .AsEnumerable()
                        .Where(a => acctIds.Contains(a.AcctId))
                        .ToList();

                    var newAccounts = spsAccounts
                        .Except(matchedAccounts, new GenericComparer<TAcctinfo, string>(a => a.AcctId))
                        .ToList();

                    var existingAccounts = spsAccounts
                        .Except(newAccounts, new GenericComparer<TAcctinfo, string>(a => a.AcctId))
                        .ToList();

                    if (existingAccounts != null && existingAccounts.Count > 0)
                    {
                        existingAccounts.ForEach(a => logger.Info($"    current batch, update, acct id: {a.AcctId}"));

                        foreach (var account in existingAccounts)
                        {
                            var acctinfo = matchedAccounts.FirstOrDefault(x => x.AcctId == account.AcctId);
                            account.Gid = acctinfo.Gid; //Gid is the key, could not be updated.
                            spsDbContext.Entry(acctinfo).CurrentValues.SetValues(account);
                        }
                        await spsDbContext.SaveChangesAsync();
                        await CleanSyncUpdatedAccounts(matchedAccounts.Select(a => a.Gid).ToList());
                    }

                    if (newAccounts != null && newAccounts.Count > 0)
                    {
                        newAccounts.ForEach(a => logger.Info($"    current batch, insert, acct id: {a.AcctId}"));
                        spsDbContext.TAcctinfo.AddRange(newAccounts);
                        await spsDbContext.SaveChangesAsync();
                        await CleanSyncCreatedAccounts(newAccounts.Select(a => a.Gid).ToList());
                    }
                }

                foreach (var spsAccount in spsAccounts)
                {
                    logger.Info($"  acct id: {spsAccount.AcctId}, gid: {spsAccount.Gid}, recharge total: {spsAccount.RechgTotal}, balance: {spsAccount.Amount}");
                }
            }
        }

        private async Task CleanSyncCreatedAccounts(List<ulong> accountGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var accountsGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => accountGids.Contains(Convert.ToUInt64(a.AccountCreated))).ToList();

                context.RemoveRange(accountsGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncUpdatedAccounts(List<ulong> accountGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var accountsUpdatedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => accountGids.Contains(Convert.ToUInt64(a.AccountUpdated))).ToList();

                context.RemoveRange(accountsUpdatedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TAcctinfo> ConvertToSpsAccounts(IEnumerable<Account> accounts)
        {
            var spsAccounts = new List<TAcctinfo>();
            foreach (var account in accounts)
            {
                var acctinfo = new TAcctinfo();

                acctinfo.AcctSno = Convert.ToByte(account.SNo);
                acctinfo.Sno = account.SNo;
                acctinfo.AcctId = account.AccountId;
                acctinfo.BelongTo = account.AccountName;
                acctinfo.Address = account.Address;
                acctinfo.PhoneNo = account.PhoneNo;
                acctinfo.AcctType = account.AccountType;
                acctinfo.Amount = account.Amount;
                acctinfo.AmtType = account.AmountType;
                acctinfo.FuelNo = account.FuelNo;
                acctinfo.Gift = account.Credit;
                acctinfo.AcctState = account.State;
                acctinfo.AcctDate = account.AccountDate;
                acctinfo.CertfType = account.CertType;
                acctinfo.CertfNo = account.CertNo;
                acctinfo.RechgTotal = account.RechargeTotal;
                acctinfo.Tmac = account.TMac;
                acctinfo.Waitmalloc = account.WaitMalloc;
                acctinfo.EnableSms = account.EnableSms;
                acctinfo.UploadFlag = account.UploadFlag;

                spsAccounts.Add(acctinfo);
            }
            return spsAccounts;
        }

        #endregion

        #region Persist Offline Card Info

        private async Task SaveOfflineCardInfoList(List<OfflineCardInfo> offlineCardInfoList, long versionNo)
        {
            var cards = new List<Card>();
            foreach (var cardinfo in offlineCardInfoList)
            {
                var card = new Card();
                card.Gid = Convert.ToUInt64(cardinfo.gid);
                card.CardSNo = cardinfo.cardSNo;
                card.SNo = cardinfo.sno;
                card.CardId = cardinfo.cardID;
                card.CardNo = cardinfo.cardNo;
                card.CTC = cardinfo.ctc;
                card.CTCTime = cardinfo.ctctime;
                card.AccountGid = 0; // not available
                card.AccountId = cardinfo.acctID;
                card.UserNo = cardinfo.userNo;
                card.Holder = cardinfo.holder;
                card.PhoneNo = cardinfo.phoneNo;
                card.DMaxPay = cardinfo.dmaxPay;
                card.MMaxPay = cardinfo.mmaxPay;
                card.YMaxPay = 0; // not available
                card.OnceMaxPay = cardinfo.onceMaxPay;
                card.LimitCar = cardinfo.bLimitCar;
                card.CarNo = cardinfo.carno;
                card.Status = cardinfo.cStatus;
                card.UserPin = cardinfo.userPIN;
                card.OverDate = cardinfo.overDate;
                card.KcDate = cardinfo.kcDate;
                card.LimitOil = cardinfo.lmtOil;
                card.CardType = cardinfo.cardType;
                card.AuthStr = ""; // not available
                card.TempCheckStr = ""; // not available
                card.DiscountNo = cardinfo.discountNo;
                card.StartDate = cardinfo.startdate;
                card.PreMalloc = cardinfo.pre_malloc;
                card.Balance = cardinfo.money;
                card.RechargeTotal = cardinfo.rechgTotal;
                card.IntegralTotal = Convert.ToUInt32(cardinfo.integralTotal);
                card.CardClass = cardinfo.cardClass;
                card.TMac = cardinfo.tmac;
                card.LimitTimes = cardinfo.limitTimes;
                card.UploadFlag = cardinfo.uploadFlag;
                card.CTCFlag = cardinfo.ctcflag;
                card.EnableSms = cardinfo.enableSms;
                card.VersionNo = versionNo;
                card.LastUpdate = DateTime.Now;

                cards.Add(card);
            }

            try
            {
                using (var context = new GuardDbContext())
                {
                    context.Card.AddRange(cards);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsDbCardInfo(cards);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsDbCardInfo(IEnumerable<Card> cards)
        {
            var spsCards = ConvertToSpsCards(cards);

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                if (localVersion == null || localVersion.HasValue && localVersion.Value == 0)
                {
                    var validCards = spsCards.Where(c => c.CStatus != 2);
                    spsDbContext.TCardinfo.AddRange(validCards);
                    await spsDbContext.SaveChangesAsync();
                    await CleanSyncCreatedCards(spsCards.Select(c => c.Gid).ToList());
                }
                else
                {
                    logger.Info("Insert, update or remove card info...");
                    var cardInfoComparer = new GenericComparer<TCardinfo, string>(c => c.CardNo);

                    var validCards = spsCards
                        .Where(c => c.CStatus != 2)
                        .ToList();

                    var validCardNos = validCards
                        .Select(a => a.CardNo)
                        .ToList();

                    var closedCardNos = spsCards
                        .Where(c => c.CStatus == 2)
                        .Select(x => x.CardNo)
                        .ToList();

                    if (closedCardNos.Count > 0)
                    {
                        closedCardNos.ForEach(c => logger.Info("Closed/Deactivated card no: " + c));

                        var closedCards = spsDbContext.TCardinfo
                            .AsEnumerable()
                            .Where(c => closedCardNos.Contains(c.CardNo))
                            .ToList();

                        spsDbContext.RemoveRange(closedCards);
                        await spsDbContext.SaveChangesAsync();
                        await CleanSyncDeletedCards(closedCards.Select(c => c.CardNo).ToList());
                    }

                    // These are db records, not host records.
                    var matchedCards = spsDbContext.TCardinfo
                        .AsEnumerable()
                        .Where(c => validCardNos.Contains(c.CardNo))
                        .ToList();

                    var newCards = validCards
                        .Except(matchedCards, cardInfoComparer)
                        .ToList();

                    var existingCards = validCards
                        .Except(newCards, cardInfoComparer)
                        .ToList();

                    if (existingCards != null && existingCards.Count > 0)
                    {
                        existingCards.ForEach(c => logger.Info($"   current batch, update, card no: {c.CardNo}, balance: {c.Money}, PreMalloc: {c.PreMalloc}"));

                        foreach (var card in existingCards)
                        {
                            var cardinfo = matchedCards.FirstOrDefault(x => x.CardNo == card.CardNo);
                            card.Gid = cardinfo.Gid; //Gid is the key, could not be updated.
                            spsDbContext.Entry(cardinfo).CurrentValues.SetValues(card);
                        }
                        await spsDbContext.SaveChangesAsync();
                        await CleanSyncUpdatedCards(matchedCards.Select(c => c.Gid).ToList());
                    }

                    if (newCards != null && newCards.Count > 0)
                    {
                        logger.Info($"new cards count, before : {newCards.Count}");
                        newCards.ForEach(c => logger.Info($"    current batch, insert, card no: {c.CardNo}"));
                        //
                        // This is a very strange case, there are two records of card '11000120215000003172'
                        // one of them contains a blank space ' '.
                        //
                        var cardWithSpace = newCards.Where(c => c.CardNo.Contains(' '));
                        int cnt = cardWithSpace.Count();
                        logger.Info($"card no w space, count: {cnt}");
                        
                        if (cardWithSpace.Count() > 0)
                        {
                            for (int i = 0; i < cnt; i++)
                            {
                                var result = newCards.Remove(cardWithSpace.ToArray()[i]);
                                logger.Info($"remove result: {result}");
                            }
                        }

                        logger.Info($"new cards count, after: {newCards.Count}");

                        var distinctNewCards = newCards.Distinct(cardInfoComparer);
                        logger.Info($"distinct new cards, count: {distinctNewCards.Count()}");

                        spsDbContext.TCardinfo.AddRange(distinctNewCards);
                        await spsDbContext.SaveChangesAsync();
                        await CleanSyncCreatedCards(distinctNewCards.Select(c => c.Gid).ToList());
                    }
                }

                foreach (var card in spsCards)
                {
                    logger.Info($"  card no: {card.CardNo}, gid: {card.Gid}, balance: {card.Money}");
                }
            }
        }

        private async Task CleanSyncCreatedCards(List<ulong> cardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var cardsGeneratedBySync = context
                    .TTableaudit
                    .AsEnumerable()
                    .Where(a => cardGids.Contains(Convert.ToUInt64(a.CardInfoCreated)))
                    .ToList();

                context.RemoveRange(cardsGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncUpdatedCards(List<ulong> cardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var cardsUpdatedBySync = context
                    .TTableaudit
                    .AsEnumerable()
                    .Where(a => cardGids.Contains(Convert.ToUInt64(a.CardInfoUpdated)))
                    .ToList();

                foreach (var cardGid in cardsUpdatedBySync)
                {
                    logger.Info($"  gid for updated card (to be cleaned): {cardGid.CardInfoUpdated}, " +
                                $"created at: {cardGid.OperationTime.ToString("yyyy-MM-dd HH:mm:ss")}");
                }

                context.RemoveRange(cardsUpdatedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncDeletedCards(List<string> cardNos)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var cardsDeletedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => cardNos.Contains(a.CardInfoDeleted)).ToList();

                context.RemoveRange(cardsDeletedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TCardinfo> ConvertToSpsCards(IEnumerable<Card> cards)
        {
            var spsCards = new List<TCardinfo>();

            foreach (var card in cards)
            {
                var cardinfo = new TCardinfo();

                cardinfo.CardSno = card.CardSNo;
                cardinfo.Sno = card.SNo;
                cardinfo.CardId = card.CardId;
                cardinfo.CardNo = card.CardNo;
                cardinfo.Ctc = card.CTC;
                cardinfo.Ctctime = card.CTCTime;
                cardinfo.AcctGid = card.AccountGid;
                cardinfo.AcctId = card.AccountId;
                cardinfo.UserNo = card.UserNo;
                cardinfo.Holder = card.Holder;
                cardinfo.PhoneNo = card.PhoneNo;
                cardinfo.DmaxPay = card.DMaxPay;
                cardinfo.MmaxPay = card.MMaxPay;
                cardinfo.YmaxPay = card.YMaxPay;
                cardinfo.OnceMaxPay = card.OnceMaxPay;
                cardinfo.BLimitCar = card.LimitCar;
                cardinfo.Carno = card.CarNo;
                cardinfo.CStatus = card.Status;
                cardinfo.UserPin = card.UserPin;
                cardinfo.OverDate = card.OverDate;
                cardinfo.KcDate = card.KcDate;
                cardinfo.OperNo = string.IsNullOrEmpty(card.OperatorNo) ? "0" : card.OperatorNo;
                cardinfo.BLmtGood = card.LimitGood;
                cardinfo.LmtOil = card.LimitOil;
                cardinfo.CardType = card.CardType;
                cardinfo.AuthStr = card.AuthStr;
                cardinfo.TmpChkStr = card.TempCheckStr;
                cardinfo.DiscountNo = card.DiscountNo;
                cardinfo.Startdate = card.StartDate;
                cardinfo.PreMalloc = card.PreMalloc;
                cardinfo.Money = card.Balance;
                cardinfo.RechgTotal = card.RechargeTotal;
                cardinfo.IntegralTotal = card.IntegralTotal;
                cardinfo.CardClass = card.CardClass;
                cardinfo.Tmac = card.TMac;
                cardinfo.LimitTimes = card.LimitTimes;
                cardinfo.UploadFlag = card.UploadFlag;
                cardinfo.Ctcflag = Convert.ToInt32(card.CTCFlag);
                cardinfo.EnableSms = card.EnableSms;

                spsCards.Add(cardinfo);
            }

            return spsCards;
        }

        #endregion

        #region Persist Offline Gray Info

        private List<GrayTrade> ConvertToLocalGrayTrades(IEnumerable<OfflineGrayInfo> offlineGrayInfoList, long versionNo)
        {
            var grayTrades = new List<GrayTrade>();

            foreach (var grayinfo in offlineGrayInfoList)
            {
                var grayTrade = new GrayTrade();
                grayTrade.Gid = Convert.ToUInt64(grayinfo.gid);
                grayTrade.SNo = grayinfo.sno;
                grayTrade.PumpType = grayinfo.pumpType;
                grayTrade.CardNo = grayinfo.cardNo;
                grayTrade.PayModeId = grayinfo.paymodeID;
                grayTrade.TrdType = grayinfo.trdType;
                grayTrade.CommId = grayinfo.commID;
                grayTrade.Price = grayinfo.prc;
                grayTrade.Volume = grayinfo.vol;
                grayTrade.Amount = grayinfo.mon;
                grayTrade.PayAmount = grayinfo.realMON;
                grayTrade.CardBalance = Convert.ToUInt32(grayinfo.cardBal);
                grayTrade.CTC = grayinfo.ctc;
                grayTrade.TtcTime = grayinfo.ttctime;
                grayTrade.TtcTimeEnd = grayinfo.ttctimeEnd;
                grayTrade.TTC = grayinfo.ttc;
                grayTrade.SeqNo = grayinfo.seqNo;
                grayTrade.NozzleNo = grayinfo.nozNo;
                grayTrade.PumpNo = grayinfo.pumpNo;
                grayTrade.PayTermId = grayinfo.payTemID;
                grayTrade.VolumeTotalizer = Convert.ToUInt32(grayinfo.endPump);
                grayTrade.DiscountNo = grayinfo.discountNo;
                grayTrade.PsamAsn = grayinfo.psamasn;
                grayTrade.PsamTac = grayinfo.psamtac;
                grayTrade.PsamTid = grayinfo.psamtid;
                grayTrade.PsamTtc = uint.Parse(grayinfo.psamttc);
                grayTrade.Tac = grayinfo.tac;
                grayTrade.GMac = grayinfo.gmac;
                grayTrade.TMac = grayinfo.tmac;
                grayTrade.UploadFlag = grayinfo.uploadFlag;
                grayTrade.OperationType = grayinfo.operationType;

                grayTrade.VersionNo = versionNo;
                grayTrade.LastUpdate = DateTime.Now;

                grayTrades.Add(grayTrade);
            }

            return grayTrades;
        }

        private async Task SaveOfflineGrayInfoList(IEnumerable<OfflineGrayInfo> offlineGrayInfoList, long versionNo)
        {
            var grayTrades = ConvertToLocalGrayTrades(offlineGrayInfoList, versionNo);

            var groupList = grayTrades.GroupBy(x => new { x.CardNo, x.CTC, x.TtcTime }, 
                (key, grp) => new
                {
                    Key1 = key.CardNo,
                    Key2 = key.CTC,
                    Key3 = key.TtcTime,
                    Result = grp.ToList()
                });

            foreach (var grp in groupList)
            {
                logger.Info("----------------------------");
                foreach (var item in grp.Result)
                {
                    logger.Info($"card no: {item.CardNo}, ctc: {item.CTC}, ttc time: {item.TtcTime.ToString("yyyy-MM-dd HH:mm:ss")}, op typ: {item.OperationType}");
                }
            }

            var validGrayTrades = new List<GrayTrade>();

            foreach (var group in groupList)
            {
                if (group.Result.Count == 2)
                {
                    if (group.Result[0].OperationType != group.Result[1].OperationType)
                    {
                        logger.Info($"{group.Key1}, {group.Key2}, {group.Key3}, Gray and Ungray, skip");
                    }
                }
                else
                {
                    validGrayTrades.AddRange(group.Result);
                }
            }

            foreach (var vgi in validGrayTrades)
            {
                logger.Info($"valid gray info, card no: {vgi.CardNo}, ctc: {vgi.CTC}, ttc time: {vgi.TtcTime.ToString("yyyy-MM-dd HH:mm:ss")}, op typ: {vgi.OperationType}");
            }

            try
            {
                using (var context = new GuardDbContext())
                {
                    context.GrayTrade.AddRange(grayTrades);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsGrayInfo(validGrayTrades);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsGrayInfo(IEnumerable<GrayTrade> grayTrades)
        {
            var toBeAddedSpsGrayInfos = ConvertToSpsGrayInfo(grayTrades.Where(t => t.OperationType == 0));
            var toBeDeletedSpsGrayInfos = ConvertToSpsGrayInfo(grayTrades.Where(t => t.OperationType == 2));

            var spsGrayInfos = ConvertToSpsGrayInfo(grayTrades);

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                ////// Handle the to be added ///////

                if (toBeAddedSpsGrayInfos.Count > 0)
                {
                    logger.Info($"To be added gray info, count: {toBeAddedSpsGrayInfos.Count}");
                }
                // find out existing gray info, do we need this check?

                var matchedGrayInfos = spsDbContext.TGrayinfo
                    .AsEnumerable()
                    .Where(g => toBeAddedSpsGrayInfos.Where(gt => gt.CardNo == g.CardNo && gt.Ctc == g.Ctc && gt.Ttctime == g.Ttctime).Count() >= 1)
                    .ToList();

                foreach (var item in matchedGrayInfos)
                {
                    logger.Info($"to be added, match, card no: {item.CardNo}, ctc: {item.Ctc}, ttc time: {item.Ttctime.Value.ToString("yyyy-MM-dd HH:mm:ss")}");
                }

                // should insert them into sps db.
                var newGrayInfos = toBeAddedSpsGrayInfos.Except(matchedGrayInfos, 
                        new GenericComparer<TGrayinfo, string>(g => string.Concat(g.CardNo, g.Ttc.ToString(), g.Ttctime.Value.ToString("yyyy-MM-dd HH:mm:ss"))));

                logger.Info($"should insert new gray info, count: {newGrayInfos.Count()}");

                spsDbContext.TGrayinfo.AddRange(newGrayInfos);

                ////// Handle the to be deleted ///////

                if (toBeDeletedSpsGrayInfos.Count > 0)
                {
                    logger.Info($"To be deleted gray info, count: {toBeDeletedSpsGrayInfos.Count}");
                }

                var matchedToBeRemovedGrayInfos = spsDbContext.TGrayinfo
                    .AsEnumerable()
                    .Where(g => toBeDeletedSpsGrayInfos.Where(gt => gt.CardNo == g.CardNo && gt.Ctc == g.Ctc && gt.Ttctime == g.Ttctime).Count() >= 1)
                    .ToList();

                foreach (var item in matchedToBeRemovedGrayInfos)
                {
                    logger.Info($"gray info matched to be deleted, card no: {item.CardNo}, ctc: {item.Ctc}, gid: {item.Gid}");
                }

                spsDbContext.TGrayinfo.RemoveRange(matchedToBeRemovedGrayInfos);

                await spsDbContext.SaveChangesAsync();

                foreach (var grayinfo in spsGrayInfos)
                {
                    logger.Info($"gray info, card no: {grayinfo.CardNo}, gid: {grayinfo.Gid}");
                }

                await CleanSyncGrayInfo(newGrayInfos.Select(g => g.Gid).ToList());
                await CleanSyncDeletedGrayInfo(matchedToBeRemovedGrayInfos.Select(g => g.CardNo).ToList());
            }
        }

        private async Task CleanSyncGrayInfo(List<ulong> grayInfoGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var grayInfosGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => grayInfoGids.Contains(Convert.ToUInt64(a.GrayInfoCreated)))
                    .ToList();

                context.RemoveRange(grayInfosGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncDeletedGrayInfo(List<string> grayInfoCardNos)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var grayInfosRemovedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => grayInfoCardNos.Contains(a.GrayInfoDeleted))
                    .ToList();

                context.RemoveRange(grayInfosRemovedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TGrayinfo> ConvertToSpsGrayInfo(IEnumerable<GrayTrade> grayTrades)
        {
            var spsGrayInfoList = new List<TGrayinfo>();

            foreach (var grayTrade in grayTrades)
            {
                var grayinfo = new TGrayinfo();

                grayinfo.Sno = grayTrade.SNo;
                grayinfo.PumpType = grayTrade.PumpType;
                grayinfo.CardNo = grayTrade.CardNo;
                grayinfo.PaymodeId = grayTrade.PayModeId;
                grayinfo.TrdType = grayTrade.TrdType;
                grayinfo.CommId = grayTrade.CommId;
                grayinfo.Prc = grayTrade.Price;
                grayinfo.Vol = grayTrade.Volume;
                grayinfo.Mon = grayTrade.Amount;
                grayinfo.RealMon = grayTrade.PayAmount;
                grayinfo.CardBal = grayTrade.CardBalance;
                grayinfo.Ctc = grayTrade.CTC;
                grayinfo.Ttctime = grayTrade.TtcTime;
                grayinfo.TtctimeEnd = grayTrade.TtcTimeEnd;
                grayinfo.Ttc = grayTrade.TTC;
                grayinfo.SeqNo = grayTrade.SeqNo;
                grayinfo.NozNo = grayTrade.NozzleNo;
                grayinfo.PumpNo = grayTrade.PumpNo;
                grayinfo.PayTemId = grayTrade.PayTermId;
                grayinfo.EndPumpId = grayTrade.VolumeTotalizer;
                grayinfo.DiscountNo = grayTrade.DiscountNo;
                grayinfo.Psamasn = grayTrade.PsamAsn;
                grayinfo.Psamtac = grayTrade.PsamTac;
                grayinfo.Psamtid = grayTrade.PsamTid;
                grayinfo.Psamttc = grayTrade.PsamTtc;
                grayinfo.Tac = grayTrade.Tac;
                grayinfo.Gmac = grayTrade.GMac;
                grayinfo.Tmac = grayTrade.TMac;
                grayinfo.UploadFlag = grayTrade.UploadFlag;

                spsGrayInfoList.Add(grayinfo);
            }

            return spsGrayInfoList;
        }

        #endregion

        #region Persist Offline Black Card Info

        private async Task SaveOfflineBlackCardInfoList(List<OfflineBlackCardInfo> offlineBlackCardInfoList, long versionNo)
        {
            ////////// add black card
            
            var offlineBaseBlackCardInfoList = offlineBlackCardInfoList.Where(c => c.blackType == 0).ToList();

            var baseBlackCards = new List<BaseBlackCard>();
            foreach (var baseBlackCardInfo in offlineBaseBlackCardInfoList)
            {
                var baseBlackCard = new BaseBlackCard();
                baseBlackCard.Gid = Convert.ToUInt64(baseBlackCardInfo.gid);
                baseBlackCard.CardNo = baseBlackCardInfo.cardNo;
                baseBlackCard.DateTime = baseBlackCardInfo.blackDate;
                baseBlackCard.AccountGid = Convert.ToUInt64(baseBlackCardInfo.acctGid);
                baseBlackCard.AccountId = baseBlackCardInfo.acctId;
                baseBlackCard.CardType = baseBlackCardInfo.cardType;
                baseBlackCard.DiscountNo = baseBlackCardInfo.discountNo;
                baseBlackCard.Reason = baseBlackCardInfo.reason;
                baseBlackCard.UploadFlag = baseBlackCardInfo.uploadFlag;
                baseBlackCard.OperationType = baseBlackCardInfo.operationType;

                baseBlackCard.VersionNo = versionNo;
                baseBlackCard.LastUpdate = DateTime.Now;

                baseBlackCards.Add(baseBlackCard);
            }
            
            logger.Info($"base blackcard record, count: {baseBlackCards.Count}");
            await SaveBaseBlackCards(baseBlackCards);

            ////////// add black card

            var offlineAddBlackCardInfoList = offlineBlackCardInfoList
                .Where(c => c.blackType == 1)
                .ToList();
            
            var addBlackCards = new List<AddBlackCard>();
            foreach (var addBlackCardInfo in offlineAddBlackCardInfoList)
            {
                var addBlackCard = new AddBlackCard();
                addBlackCard.Gid = Convert.ToUInt64(addBlackCardInfo.gid);
                addBlackCard.CardNo = addBlackCardInfo.cardNo;
                addBlackCard.DateTime = addBlackCardInfo.blackDate;
                addBlackCard.AccountGid = Convert.ToUInt64(addBlackCardInfo.acctGid);
                addBlackCard.AccountId = addBlackCardInfo.acctId;
                addBlackCard.CardType = addBlackCardInfo.cardType;
                addBlackCard.DiscountNo = addBlackCardInfo.discountNo;
                addBlackCard.Reason = addBlackCardInfo.reason;
                addBlackCard.UploadFlag = addBlackCardInfo.uploadFlag;
                addBlackCard.OperationType = addBlackCardInfo.operationType;

                addBlackCard.VersionNo = versionNo;
                addBlackCard.LastUpdate = DateTime.Now;

                addBlackCards.Add(addBlackCard);
            }

            logger.Info($"addblackcard record, count: {addBlackCards.Count}");

            await SaveAddBlackCards(addBlackCards);

            ////////// delete black card

            var offlineDeleteBlackCardInfoList = offlineBlackCardInfoList
                .Where(c => c.blackType == 2)
                .ToList();

            var deleteBlackCards = new List<DeleteBlackCard>();
            foreach (var deleteBlackCardInfo in offlineDeleteBlackCardInfoList)
            {
                var deleteBlackCard = new DeleteBlackCard();

                deleteBlackCard.Gid = Convert.ToUInt64(deleteBlackCardInfo.gid);
                deleteBlackCard.CardNo = deleteBlackCardInfo.cardNo;
                deleteBlackCard.DateTime = deleteBlackCardInfo.blackDate;
                deleteBlackCard.AccountGid = Convert.ToUInt64(deleteBlackCardInfo.acctGid);
                deleteBlackCard.AccountId = deleteBlackCardInfo.acctId;
                deleteBlackCard.CardType = deleteBlackCardInfo.cardType;
                deleteBlackCard.DiscountNo = deleteBlackCardInfo.discountNo;
                deleteBlackCard.Reason = deleteBlackCardInfo.reason;
                deleteBlackCard.UploadFlag = deleteBlackCardInfo.uploadFlag;
                deleteBlackCard.OperationType = deleteBlackCardInfo.operationType;

                deleteBlackCard.VersionNo = versionNo;
                deleteBlackCard.LastUpdate = DateTime.Now;

                deleteBlackCards.Add(deleteBlackCard);
            }

            logger.Info($"deleteblackcard record, count: {deleteBlackCards.Count}");

            await SaveDeleteBlackCards(deleteBlackCards);
        }

        #region Base BlackCard processing

        private async Task SaveBaseBlackCards(List<BaseBlackCard> baseBlackCards)
        {
            try
            {
                using (var context = new GuardDbContext())
                {
                    context.BaseBlackCard.AddRange(baseBlackCards);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsBaseBlackCards(baseBlackCards);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsBaseBlackCards(IEnumerable<BaseBlackCard> baseBlackCards)
        {
            var comparer = new GenericComparer<TBlackcard, string>(c => c.CardNo);
            var spsBaseBlackCards = ConvertToSpsBaseBlackCards(baseBlackCards);
            var toBeAddedBaseBlackCards = ConvertToSpsBaseBlackCards(baseBlackCards.Where(c => c.OperationType == 0));
            var toBeUpdatedBaseBlackCards = ConvertToSpsBaseBlackCards(baseBlackCards.Where(c => c.OperationType == 1));
            var toBeRemovedBaseBlackCards = ConvertToSpsBaseBlackCards(baseBlackCards.Where(c => c.OperationType == 2));

            if (toBeUpdatedBaseBlackCards.Count > 0)
                logger.Warn($"Base black cards, there are records to be updated??? count: {toBeUpdatedBaseBlackCards.Count}");

            try
            {
                using (var context = new SpsDbContext(_spsDbConnString))
                {
                    // handle `to be added Base BlackCard records`
                    var existingToBeAdded = context.TBlackcard
                        .AsEnumerable()
                        .Where(bc => toBeAddedBaseBlackCards.Any(c => c.CardNo == bc.CardNo))
                        .ToList();

                    var pendingAdd = toBeAddedBaseBlackCards.Except(existingToBeAdded, comparer).ToList();
                    pendingAdd.ForEach(c => logger.Info($"       bc, add, card no: {c.CardNo}"));

                    context.TBlackcard.AddRange(pendingAdd);

                    // handle `to be removed Base BlackCard records`
                    var matchedToBeRemoved = context.TBlackcard
                        .AsEnumerable()
                        .Where(bc => toBeRemovedBaseBlackCards.Any(c => c.CardNo == bc.CardNo))
                        .ToList();
                    
                    matchedToBeRemoved.ForEach(c => logger.Info($"       bc, del, card no: {c.CardNo}"));
                    context.TBlackcard.RemoveRange(matchedToBeRemoved);

                    await context.SaveChangesAsync();

                    await CleanSyncInsertedBaseBlackCards(pendingAdd.Select(a => a.Gid).ToList());
                    await CleanSyncRemovedBaseBlackCards(matchedToBeRemoved.Select(c => c.CardNo).ToList());

                    spsBaseBlackCards
                        .ForEach(c => logger.Info($"base blackcard, card no: {c.CardNo}, gid: {c.Gid}"));
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task CleanSyncInsertedBaseBlackCards(List<ulong> baseBlackCardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var baseBlackCardsGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => baseBlackCardGids.Contains(Convert.ToUInt64(a.BaseBlackCardCreated)))
                    .ToList();

                context.RemoveRange(baseBlackCardsGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncRemovedBaseBlackCards(List<string> baseBlackCardCardNos)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var baseBlackCardsDeletedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => baseBlackCardCardNos.Contains(a.BaseBlackCardDeleted))
                    .ToList();

                context.RemoveRange(baseBlackCardsDeletedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TBlackcard> ConvertToSpsBaseBlackCards(IEnumerable<BaseBlackCard> baseBlackCards)
        {
            var spsBaseBlackCards = new List<TBlackcard>();

            foreach (var baseBlackCard in baseBlackCards)
            {
                var baseblackcard = new TBlackcard();
                baseblackcard.CardNo = baseBlackCard.CardNo;
                baseblackcard.BlackDate = baseBlackCard.DateTime;
                baseblackcard.AcctGid = baseBlackCard.AccountGid;
                baseblackcard.AcctId = baseBlackCard.AccountId;
                baseblackcard.CardType = baseBlackCard.CardType;
                baseblackcard.DiscountNo = baseBlackCard.DiscountNo;
                baseblackcard.Reason = baseBlackCard.Reason;
                baseblackcard.UploadFlag = baseBlackCard.UploadFlag;

                spsBaseBlackCards.Add(baseblackcard);
            }

            return spsBaseBlackCards;
        }

        #endregion

        #region AddBlackCard processing

        private async Task SaveAddBlackCards(List<AddBlackCard> addBlackCards)
        {
            try
            {
                using (var context = new GuardDbContext())
                {
                    context.AddBlackCard.AddRange(addBlackCards);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsAddBlackCards(addBlackCards);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsAddBlackCards(IEnumerable<AddBlackCard> addBlackCards)
        {
            var comparer = new GenericComparer<TAddblackcard, string>(c => c.CardNo);

            var toBeAddedAddBlackCards = ConvertToSpsAddBlackCards(addBlackCards.Where(c => c.OperationType == 0));
            var toBeUpdatedAddBlackCards = ConvertToSpsAddBlackCards(addBlackCards.Where(c => c.OperationType == 1));
            var toBeRemovedAddBlackCards = ConvertToSpsAddBlackCards(addBlackCards.Where(c => c.OperationType == 2));

            var spsAddBlackCards = ConvertToSpsAddBlackCards(addBlackCards);

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                // handle `to be added AddBlackCard records`
                var matchedInToBeAdded = spsDbContext.TAddblackcard
                    .AsEnumerable()
                    .Where(abc => toBeAddedAddBlackCards.Where(c => c.CardNo == abc.CardNo).Count() >= 1)
                    .ToList();

                var pendingAddedAddBlackCard = toBeAddedAddBlackCards.Except(matchedInToBeAdded, comparer).ToList();
                pendingAddedAddBlackCard.ForEach(c => logger.Info($"     abc, pending add, card no: {c.CardNo}"));

                spsDbContext.TAddblackcard.AddRange(pendingAddedAddBlackCard);

                // handle `to be updated AddBlackCard records`

                var pendingUpdateAddBlackCard = toBeAddedAddBlackCards.Except(pendingAddedAddBlackCard, comparer).ToList();

                pendingUpdateAddBlackCard.ForEach(c => logger.Info($"    abc, pending upd within add, card no: {c.CardNo}"));

                toBeUpdatedAddBlackCards.AddRange(pendingUpdateAddBlackCard);

                var matchedToBeUpdated = spsDbContext.TAddblackcard
                    .AsEnumerable()
                    .Where(abc => toBeUpdatedAddBlackCards.Where(c => c.CardNo == abc.CardNo).Count() >= 1)
                    .ToList();

                var candidatesToBeInserted = toBeUpdatedAddBlackCards
                    .Except(matchedToBeUpdated, comparer)
                    .ToList();

                if (candidatesToBeInserted.Count > 0)
                    logger.Info($"      abc, pending upd, some are new??? count: {candidatesToBeInserted.Count}");

                var toBeUpdated = toBeUpdatedAddBlackCards
                    .Except(candidatesToBeInserted, comparer)
                    .ToList();

                toBeUpdated.ForEach(c => logger.Info($"     abc, pending upd, card no: {c.CardNo}"));

                foreach (var card in toBeUpdated)
                {
                    var match = matchedToBeUpdated.FirstOrDefault(c => c.CardNo == card.CardNo);
                    if (match != null)
                    {
                        card.Gid = match.Gid;
                        spsDbContext.Entry(match).CurrentValues.SetValues(card);
                    }
                }

                // handle `to be removed AddBalckCard records`
                var matchedToBeRemoved = spsDbContext.TAddblackcard
                    .AsEnumerable()
                    .Where(abc => toBeRemovedAddBlackCards.Where(c => c.CardNo == abc.CardNo).Count() >= 1)
                    .ToList();

                matchedToBeRemoved.ForEach(c => logger.Info($"     abc, pending del, card no: {c.CardNo}"));

                spsDbContext.TAddblackcard.RemoveRange(matchedToBeRemoved);

                await spsDbContext.SaveChangesAsync();

                await CleanSyncInsertedAddBlackCards(pendingAddedAddBlackCard.Select(a => a.Gid).ToList());
                await CleanSyncUpdatedAddBlackCards(toBeUpdated.Select(c => c.Gid).ToList());
                await CleanSyncDeletedAddBlackCards(matchedToBeRemoved.Select(c => c.CardNo).ToList());

                spsAddBlackCards.ForEach(c => 
                    logger.Info($"addblackcard, card no: {c.CardNo}, gid: {c.Gid}"));
            }
        }

        private async Task CleanSyncInsertedAddBlackCards(List<ulong> addBlackCardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var addBlackCardsGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => addBlackCardGids.Contains(Convert.ToUInt64(a.BlacklistedCardCreated)))
                    .ToList();

                context.RemoveRange(addBlackCardsGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncUpdatedAddBlackCards(List<ulong> addBlackCardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var addBlackCardsUpdatedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => addBlackCardGids.Contains(Convert.ToUInt64(a.BlacklistedCardUpdated)))
                    .ToList();

                context.RemoveRange(addBlackCardsUpdatedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncDeletedAddBlackCards(List<string> addBlackCardCardNos)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var addBlackCardsDeletedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => addBlackCardCardNos.Contains(a.BlacklistedCardDeleted))
                    .ToList();

                context.RemoveRange(addBlackCardsDeletedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TAddblackcard> ConvertToSpsAddBlackCards(IEnumerable<AddBlackCard> addBlackCards)
        {
            var spsAddBlackCards = new List<TAddblackcard>();

            foreach (var addBlackCard in addBlackCards)
            {
                var addblackcard = new TAddblackcard();
                addblackcard.CardNo = addBlackCard.CardNo;
                addblackcard.BlackDate = addBlackCard.DateTime;
                addblackcard.AcctGid = addBlackCard.AccountGid;
                addblackcard.AcctId = addBlackCard.AccountId;
                addblackcard.CardType = addBlackCard.CardType;
                addblackcard.DiscountNo = addBlackCard.DiscountNo;
                addblackcard.Reason = addBlackCard.Reason;
                addblackcard.UploadFlag = addBlackCard.UploadFlag;

                spsAddBlackCards.Add(addblackcard);
            }

            return spsAddBlackCards;
        }

        #endregion

        #region DeleteBlackCard processing

        private async Task SaveDeleteBlackCards(List<DeleteBlackCard> deleteBlackCards)
        {
            try
            {
                using (var context = new GuardDbContext())
                {
                    context.DeleteBlackCard.AddRange(deleteBlackCards);
                    await context.SaveChangesAsync();
                }

                await SaveToSpsDeleteBlackCards(deleteBlackCards);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }
        }

        private async Task SaveToSpsDeleteBlackCards(IEnumerable<DeleteBlackCard> deleteBlackCards)
        {
            var comparer = new GenericComparer<TDeleteblackcard, string>(c => c.CardNo);
            var toBeAddedDeleteBlackCards = ConvertToSpsDeleteBlackCards(deleteBlackCards.Where(c => c.OperationType == 0));
            var toBeRemovedDeleteBlackCards = ConvertToSpsDeleteBlackCards(deleteBlackCards.Where(c => c.OperationType == 2));

            var spsDeleteBlackCards = ConvertToSpsDeleteBlackCards(deleteBlackCards);

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                // handle `to be added DeleteBlackCard records`
                var existingInToBeAdded = spsDbContext.TDeleteblackcard
                    .AsEnumerable()
                    .Where(dbc => toBeAddedDeleteBlackCards.Where(c => c.CardNo == dbc.CardNo).Count() >= 1)
                    .ToList();

                var newDeleteBlackCards = toBeAddedDeleteBlackCards.Except(existingInToBeAdded, comparer).ToList();
                newDeleteBlackCards.ForEach(c => logger.Info($"      dbc, add, card no: {c.CardNo}"));
                spsDbContext.TDeleteblackcard.AddRange(newDeleteBlackCards);

                // handle `to be updated DeleteBlackCard records`
                var toBeUpdated = toBeAddedDeleteBlackCards.Except(newDeleteBlackCards, comparer).ToList();
                toBeUpdated.ForEach(c => logger.Info($"      dbc, upd, card no: {c.CardNo}, time: {c.BlackDate.Value}"));
                foreach (var card in toBeUpdated)
                {
                    var match = existingInToBeAdded.FirstOrDefault(c => c.CardNo == card.CardNo);
                    if (match != null)
                    {
                        card.Gid = match.Gid;
                        spsDbContext.Entry(match).CurrentValues.SetValues(card);
                    }
                }

                // handle `to be removed DeleteBlackCard records`
                var matchedToBeRemoved = spsDbContext.TDeleteblackcard
                    .AsEnumerable()
                    .Where(dbc => toBeRemovedDeleteBlackCards.Where(c => c.CardNo == dbc.CardNo).Count() >= 1)
                    .ToList();
                matchedToBeRemoved.ForEach(c => logger.Info($"       dbc, del, card no: {c.CardNo}"));
                spsDbContext.TDeleteblackcard.RemoveRange(matchedToBeRemoved);

                await spsDbContext.SaveChangesAsync();

                spsDeleteBlackCards
                    .ForEach(c => logger.Info($"deleteblackcard, card no: {c.CardNo}, gid: {c.Gid}"));

                await CleanSyncAddedDeleteBlackCards(newDeleteBlackCards.Select(d => d.Gid).ToList());
                await CleanSyncRemovedDeleteBlackCards(matchedToBeRemoved.Select(c => c.CardNo).ToList());
            }
        }

        private async Task CleanSyncAddedDeleteBlackCards(List<ulong> deleteBlackCardGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var deleteBlackCardsGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => deleteBlackCardGids.Contains(Convert.ToUInt64(a.ReleasedCardCreated)))
                    .ToList();

                context.RemoveRange(deleteBlackCardsGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private async Task CleanSyncRemovedDeleteBlackCards(List<string> deleteBlackCardCardNos)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var deleteBlackCardsRemovedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => deleteBlackCardCardNos.Contains(a.ReleasedCardDeleted))
                    .ToList();

                context.RemoveRange(deleteBlackCardsRemovedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TDeleteblackcard> ConvertToSpsDeleteBlackCards(IEnumerable<DeleteBlackCard> deleteBlackCards)
        {
            var spsDeleteBlackCards = new List<TDeleteblackcard>();

            foreach (var deleteBlackCard in deleteBlackCards)
            {
                var deleteblackcard = new TDeleteblackcard();
                deleteblackcard.CardNo = deleteBlackCard.CardNo;
                deleteblackcard.BlackDate = deleteBlackCard.DateTime;
                deleteblackcard.AcctGid = deleteBlackCard.AccountGid;
                deleteblackcard.AcctId = deleteBlackCard.AccountId;
                deleteblackcard.CardType = deleteBlackCard.CardType;
                deleteblackcard.DiscountNo = deleteBlackCard.DiscountNo;
                deleteblackcard.Reason = deleteBlackCard.Reason;
                deleteblackcard.UploadFlag = deleteBlackCard.UploadFlag;

                spsDeleteBlackCards.Add(deleteblackcard);
            }

            return spsDeleteBlackCards;
        }

        #endregion

        #endregion

        #region Persist Offline CardRepLoss

        private async Task SaveOfflineCardRepLossInfoList(List<OfflineCardRepLoss> offlineCardRepLossInfoList,
            long versionNo)
        {
            if (offlineCardRepLossInfoList == null || offlineCardRepLossInfoList?.Count == 0)
            {
                return;
            }

            logger.Info($"cardRepLossInfoList, count: {offlineCardRepLossInfoList.Count}");
            var comparer = new GenericComparer<TCardreploss, string>(c => c.CardNo);
            var spsCardRepLoss = ConvertToSpsCardRepLoss(offlineCardRepLossInfoList);
            foreach (var item in spsCardRepLoss)
            {
                logger.Info($"To be added CardRepLoss, card no: {item.CardNo}, operType: {item.OperType}");
            }

            using (var spsDbContext = new SpsDbContext(_spsDbConnString))
            {
                var matches = spsDbContext.TCardreploss
                    .AsEnumerable()
                    .Where(tc =>
                        spsCardRepLoss.Where(s => s.CardNo == tc.CardNo && s.LossTime == tc.LossTime).Count() >= 1)
                    .ToList();
                
                foreach (var c in matches)
                {
                    logger.Info($"cardreploss, matched, cardNo: {c.CardNo}, lossTime: {c.LossTime?.ToString("yyyy-MM-dd HH:mm:ss")}");
                }

                var toBeAdded = spsCardRepLoss.Except(matches, comparer).ToList();
                if (toBeAdded.Count == 0)
                {
                    logger.Info($"cardreploss, no new records to be added");
                    return;
                }

                foreach (var c in toBeAdded)
                {
                    logger.Info($"cardreploss, to be added, cardNo: {c.CardNo}, lossTime: {c.LossTime?.ToString("yyyy-MM-dd HH:mm:ss")}");
                }

                await spsDbContext.TCardreploss.AddRangeAsync(toBeAdded);
                
                int result = 0;
                try
                {
                    result = await spsDbContext.SaveChangesAsync();
                    logger.Info($"Cardreploss, inserted {result} records");
                }
                catch (Exception ex)
                {
                    logger.Error($"Exception raised in saving CardReploss: {ex}");
                }

                var cardRepLossCreated =
                    await spsDbContext.TTableaudit.Where(r => r.LostCardCreated != 0).ToListAsync();

                if (cardRepLossCreated.Count == result)
                {
                    var toBeCleandGids = spsCardRepLoss.Select(r => r.Gid).ToList();
                    foreach (var gid in toBeCleandGids)
                    {
                        logger.Info($"to be cleand gid: {gid}");
                    }
                    await CleanSyncCardRepLoss(toBeCleandGids);
                }
            }
        }

        private async Task CleanSyncCardRepLoss(List<ulong> cardRepLosGids)
        {
            using (var context = new SpsDbContext(_spsDbConnString))
            {
                var cardRepLossGeneratedBySync = context.TTableaudit
                    .AsEnumerable()
                    .Where(a => cardRepLosGids.Contains(Convert.ToUInt64(a.LostCardCreated)))
                    .ToList();

                context.RemoveRange(cardRepLossGeneratedBySync);
                await context.SaveChangesAsync();
            }
        }

        private List<TCardreploss> ConvertToSpsCardRepLoss(IEnumerable<OfflineCardRepLoss> cardRepLosses)
        {
            var cardRepLossList = new List<TCardreploss>();

            foreach (var crp in cardRepLosses)
            {
                var cardRepLoss = new TCardreploss();
                cardRepLoss.CardNo = crp.cardNo;
                cardRepLoss.OperNo = crp.operNo;
                cardRepLoss.OperType = crp.operType;
                cardRepLoss.Sno = Convert.ToUInt32(crp.sNo);
                cardRepLoss.LossTime = crp.LossTime;
                cardRepLoss.Reason = crp.reason;

                cardRepLossList.Add(cardRepLoss);
            }
            return cardRepLossList;
        }

        #endregion

        #region Get token

        private async Task<AuthToken> GetTokenAsync(string userName, string password, string baseUrl)
        {
            logger.Info("Downloader starts to get token...");

            _client.DefaultRequestHeaders.Clear();
            string tokenUrl = string.Concat(baseUrl, tokenAuthPath);
            var formParam = new AuthenticationParameter(grantType, userName, password);

            try
            {
                var response = await _client.PostAsync(tokenUrl, new FormUrlEncodedContent(formParam.Params)).ConfigureAwait(false);

                logger.Info($"Token response for downloader, StatusCode = {(int)response.StatusCode}");

                if (response.IsSuccessStatusCode)
                {
                    await response.Content.ReadAsStringAsync().ContinueWith(x =>
                    {
                        currentAuthToken = JsonConvert.DeserializeObject<AuthToken>(x?.Result);
                        currentAuthToken.TokenRetrievedTime = DateTime.Now;
                    });
                }
                else
                {
                    var content = await response.Content.ReadAsStringAsync();
                    response.Content?.Dispose();
                }

                return currentAuthToken;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return null;
            }
        }

        #endregion
    }
}