[*.cs]

# CA2007: Consider calling ConfigureAwait on the awaited task
dotnet_diagnostic.CA2007.severity = silent

# CA1051: Do not declare visible instance fields
dotnet_diagnostic.CA1051.severity = silent

# CA1303: Do not pass literals as localized parameters
dotnet_diagnostic.CA1303.severity = suggestion

# CA1031: Do not catch general exception types
dotnet_diagnostic.CA1031.severity = suggestion

# CA1820: Test for empty strings using string length
dotnet_diagnostic.CA1820.severity = none

# CA1305: Specify IFormatProvider
dotnet_diagnostic.CA1305.severity = suggestion

# CS8629: Nullable value type may be null.
dotnet_diagnostic.CS8629.severity = suggestion

# CA1819: Properties should not return arrays
dotnet_diagnostic.CA1819.severity = silent

# CA1062: Validate arguments of public methods
dotnet_diagnostic.CA1062.severity = suggestion

# CS8602: Dereference of a possibly null reference.
dotnet_diagnostic.CS8602.severity = suggestion

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
    <StartupObject>MailJanitor.Program</StartupObject>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <CheckForOverflowUnderflow>false</CheckForOverflowUnderflow>
  </PropertyGroup>

  <ItemGroup>
    <None Remove="_FOSSIL_"  />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="CommandLineParser" Version="2.6.0" />
    <PackageReference Include="mailkit" Version="2.3.1.6" />
    <PackageReference Include="Microsoft.CodeAnalysis.FxCopAnalyzers" Version="2.9.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="nuglify" Version="1.5.13" />
    <PackageReference Include="ReverseMarkdown" Version="3.8.0" />
  </ItemGroup>

using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor
{
	public class MailJanitorContext : DbContext
    {
#nullable disable
        public DbSet<Account> Accounts { get; set; }
        public DbSet<Folder> Folders { get; set; }
        public DbSet<FolderMessage> FolderMessages { get; set; }
        public DbSet<Message> Messages { get; set; }
        public DbSet<MessageParticipant> MessageParticipants { get; set; }
        public DbSet<Participant> Participants { get; set; }
        public DbSet<Keyword> Keywords { get; set; }
        public DbSet<MessageKeyword> MessageKeywords { get; set; }
        public DbSet<MessageReference> MessageReferences { get; set; }
#nullable restore

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlite("Data source=mailjanitor.sqlite");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Message>(b =>
            {
                b.HasIndex(m => m.GlobalID);
                b.HasIndex(m => m.RfcMessageID);
                //b.Property(m => m.Date)
                //    .HasConversion(new DateTimeOffsetToBinaryConverter());
                //b.Property(m => m.DateReceived)
                //    .HasConversion(new DateTimeOffsetToBinaryConverter());
                //b.Property(m => m.DateRetrievedUTC)
                //    .HasConversion(new DateTimeToBinaryConverter());
            });

            modelBuilder.Entity<Participant>(b =>
            {
                b.HasIndex(p => new { p.Address, p.Name })
                    .IsUnique();
            });

        {
            GithubFlavored = true,
            RemoveComments = false,
            SmartHrefHandling = true,
            TableWithoutHeaderRowHandling = ReverseMarkdown.Config.TableWithoutHeaderRowHandlingOption.Default,
            UnknownTags = ReverseMarkdown.Config.UnknownTagsOption.Bypass,
        };
        private Dictionary<(string, bool), long> _keywords = new Dictionary<(string, bool), long>();
        private Dictionary<string, long> _participants = new Dictionary<string, long>();
        private int _totalCountAllFolders;
        private int _countAllFolders;
        private DateTime _startAccount;
        private bool _haveDeleted = false;


        public MailSynchronizer(CancellationToken cancellationToken)
        {
            _cancellationToken = cancellationToken;
        }

        public async Task SynchronizeAccount(string host, short port, NetworkCredential credentials,
                                             Func<IMailFolder,bool>? folderFilter = null,
                                             bool vacuumDatabase = false)
        {
            if (credentials is null)
            {
                throw new ArgumentNullException(nameof(credentials));
            }

            Account account;
            using (var work = new UnitOfWork(_cancellationToken))
            {
                account = await work.Accounts.GetOrCreateAsync(host, port, credentials.UserName);
                _participants = await work.Participants.GetDictionaryAsync(p => p.Address ?? p.Name ?? "", p => p.ID, StringComparer.InvariantCultureIgnoreCase);
                _keywords = await work.Keywords.GetDictionaryAsync(kw => (kw.Name, kw.IsLabel), kw => kw.ID, KeywordEqualityComparer.InvariantCultureIgnoreCase);
            }

            using (var client = new MailKit.Net.Imap.ImapClient())
            {
                Con.WriteLine("Connecting to server...", TraceLevel.Verbose);
                await client.ConnectAsync(host, port, true, _cancellationToken);

                            _totalCountAllFolders -= mailFolder.Count;
                            continue;
                        }
                    }

                    TimerCollection.Global.Start("SynchronizeFolder");
                    try {
                        await SynchronizeFolder(mailFolder, dbFolder);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeFolder");
                    }
                    //Con.WriteLine(); Con.WriteLine(TimerCollection.Global.ToString(), System.Diagnostics.TraceLevel.Verbose);

                    if (mailFolder.UidNext != null)
                    {
                        using (var work = new UnitOfWork(_cancellationToken))
                        {
                            dbFolder = await work.Folders.GetByIdAsync(dbFolder.ID);
                            dbFolder.UIDNext = mailFolder.UidNext.Value.Id;
                            await work.SaveChangesAsync();
                        }
                    }
                } // foreach folder

                // If we have remaining folders that aren't on the server anymore, delete them
                if (remainingFolderIDs.Any())
                {
                    using var work = new UnitOfWork(_cancellationToken);

                    // First verify that they don't have any child folders; otherwise we'd be deleting those too...
                    remainingFolderIDs = work.Folders.Select(f => remainingFolderIDs.Contains(f.ID) && !f.Children.Any(),
                                                             f => f.ID).ToList();
                    if (remainingFolderIDs.Any())
                    {
                        Con.WriteLine("Deleting local folder(s):");
                        Con.WriteLine("- " + string.Join("\n- ", await work.Folders.GetFullNamesAsync(remainingFolderIDs)));

                        work.Folders.DeleteByIDs(remainingFolderIDs);
                        await work.SaveChangesAsync();
                        _haveDeleted = true;

                    }
                }
            }

            TimerCollection.Global.Start("ClearUnunsedObjects");
            try
            {

                            Con.Write("; ETA " + (_startAccount.ToLocalTime() + TimeSpan.FromTicks(elapsed.Ticks * _totalCountAllFolders / _countAllFolders)).ToString("s").Replace('T', ' '));
                        }
                        lastProgressUpdate = DateTime.UtcNow;
                    }

                    TimerCollection.Global.Start("SynchronizeMessage");
                    try {
                        await SynchronizeMessage(summary, mailFolder, dbFolder.ID, folderMsgID, messageID);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeMessage");
                    }

                } // foreach (summary)
            }
            finally

                }
            }
        }

        private async Task SynchronizeMessage(IMessageSummary summary, IMailFolder mailFolder,
                                              long folderID, long? folderMsgID, long? messageID)
        {
            using var work = new UnitOfWork(_cancellationToken);

            try
            {
                // Deleted message: don't download it; delete it from the database instead
                if (summary.Flags.Value.HasFlag(MessageFlags.Deleted))
                {
                    if (folderMsgID != null)
                    {
                        work.FolderMessages.DeleteByID(folderMsgID.Value);
                        await work.SaveChangesAsync();
                        _haveDeleted = true;
                    }
                    return;
                }

                // Otherwise, check out the existing message
                Message? dbMessage = null;
                if (messageID != null)
                {
                    dbMessage = await work.Messages.GetByIdAsync(messageID.Value);
                }
                FolderMessage? folderMessage = null;
                if (folderMsgID == null
                    && dbMessage != null
                    && summary.Flags == dbMessage.Flags
                    && summary.InternalDate == dbMessage.DateReceived
                    && string.Join(", ", summary.Keywords) == dbMessage.Keywords)
                {
                    folderMessage = new FolderMessage
                    {
                        FolderID = folderID,
                        UID = summary.UniqueId.Id,
                        Message = dbMessage,
                    };
                    await work.FolderMessages.AddAsync(folderMessage);
                }
                else if (dbMessage == null
                        || summary.Flags != dbMessage.Flags
                        || summary.InternalDate != dbMessage.DateReceived
                        || string.Join(", ", summary.Keywords) != dbMessage.Keywords)
                {
                    MimeMessage mailMessage = await GetMailMessageAsync(summary, mailFolder);
                    // save message to database
                    if (folderMsgID != null)
                    {
                        folderMessage = await work.DB.FolderMessages
                            .Where(fm => fm.ID == folderMsgID)
                            .Include(fm => fm.Message)
                            .ThenInclude(m => m.Participants)
                            .SingleOrDefaultAsync(_cancellationToken);
                    }
                    if (folderMessage == null)
                    {
                        folderMessage = new FolderMessage
                        {
                            FolderID = folderID,
                            UID = summary.UniqueId.Id,
                            Message = dbMessage ?? new Message
                            {
                                Participants = new List<MessageParticipant>(),
                            },
                        };
                        await work.FolderMessages.AddAsync(folderMessage);
                    }
                    dbMessage = folderMessage.Message;
                    dbMessage.GlobalID = summary.GMailMessageId;
                    dbMessage.DateRetrievedUTC = DateTime.UtcNow;
                    dbMessage.Flags = summary.Flags ?? MessageFlags.None;
                    dbMessage.DateReceived = summary.InternalDate;
                    TimerCollection.Global.Start("PopulateMessageAsync");
                    try
                    {
                        await PopulateMessageAsync(dbMessage, mailMessage, summary.Keywords);
                    }
                    finally
                    {
                        TimerCollection.Global.Stop("PopulateMessageAsync");
                    }

                    // Store all separate entities contained in the mail message
                    TimerCollection.Global.Start("PopulateParticipants");
                    await PopulateParticipantsAsync(work, dbMessage, mailMessage);    // from headers
                    TimerCollection.Global.Stop("PopulateParticipants");
                    TimerCollection.Global.Start("PopulateKeywords");
                    await PopulateKeywordsAsync(work, dbMessage, summary.Keywords, summary.GMailLabels); // from summary
                    TimerCollection.Global.Stop("PopulateKeywords");
                    TimerCollection.Global.Start("PopulateReferences");
                    await PopulateReferencesAsync(work, dbMessage, mailMessage);      // from summary + headers
                    TimerCollection.Global.Stop("PopulateReferences");
                }
                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);

            }
        }

        private async Task<MimeMessage> GetMailMessageAsync(IMessageSummary summary, IMailFolder mailFolder)
        {
            MimeMessage mailMessage;
            try
            {
                TimerCollection.Global.Start("mailFolder.GetMessageAsync");
                try {
                    mailMessage = await mailFolder.GetMessageAsync(summary.UniqueId, _cancellationToken);
                } finally {
                    TimerCollection.Global.Stop("mailFolder.GetMessageAsync");
                }
            }
            catch (FormatException)
            {
                var mailEncoding = Encoding.GetEncoding("ISO-8859-1");
                // Upon failure to parse the message, try to download the entire message stream
                using Stream mailStream = await mailFolder.GetStreamAsync(summary.UniqueId, 0, (int)summary.Size.Value, _cancellationToken);
                using StreamReader reader = new StreamReader(mailStream, mailEncoding, true);

                using (var fs = new FileStream($"{summary.GMailMessageId}.eml", FileMode.Create, FileAccess.ReadWrite, FileShare.Read))
                {
                    await mailStream.CopyToAsync(fs, _cancellationToken);
                    mailStream.Position = 0;
                }
                // Now, attempt to fix the mail message, by skipping lines until one doesn't start with a spacing character
                var sbSource = new StringBuilder((int)mailStream.Length);
                while (!reader.EndOfStream)
                {
                    string line = await reader.ReadLineAsync() ?? "";
                    if (line.Length == 0 || !char.IsWhiteSpace(line[0]))
                    {
                        sbSource.Append(line);
                        sbSource.Append("\r\n");
                        sbSource.Append(await reader.ReadToEndAsync());
                    }
                }
                using var fixedStream = new MemoryStream((int)mailStream.Length);

                await fixedStream.WriteAsync(reader.CurrentEncoding.GetBytes(sbSource.ToString()), _cancellationToken);
                fixedStream.Position = 0;
                var options = new ParserOptions
                {
                    AddressParserComplianceMode = RfcComplianceMode.Loose,
                    AllowAddressesWithoutDomain = true,
                    AllowUnquotedCommasInAddresses = false,
                    CharsetEncoding = mailEncoding,
                    MaxAddressGroupDepth = 3,
                    ParameterComplianceMode = RfcComplianceMode.Loose,
                    RespectContentLength = true,
                    Rfc2047ComplianceMode = RfcComplianceMode.Loose,
                };
                mailMessage = await MimeMessage.LoadAsync(options, fixedStream, _cancellationToken);


            }

            return mailMessage;
        }

        public async Task ClearUnunsedObjectsAsync(bool vacuumDatabase = false)
        {
            using var work = new UnitOfWork(_cancellationToken);

            await Con.WriteLineAsync("Cleaning up database...", TraceLevel.Verbose);

            // Delete any messages not linked to a folder
            work.Messages.DeleteByIDs(m => !m.Folders.Any(), m => m.ID);
            int numDeleted = await work.SaveChangesAsync();

            // Delete any participants not linked to a message
            work.Participants.DeleteByIDs(p => !p.Messages.Any(), p => p.ID);
            numDeleted += await work.SaveChangesAsync();

            if (vacuumDatabase && (_haveDeleted || numDeleted > 0))
            {
                await work.DB.Database.ExecuteSqlRawAsync("VACUUM", _cancellationToken);

            }
        }

        private async Task PopulateMessageAsync(Message targetMsg, MimeMessage sourceMsg, IEnumerable<string> keywords)
        {
            try
            {
                targetMsg.RfcMessageID = sourceMsg.ResentMessageId ?? sourceMsg.MessageId; // TODO: reverse these?
                targetMsg.Subject = sourceMsg.Subject;
                targetMsg.Date = sourceMsg.Date;
                targetMsg.From = new InternetAddressList(sourceMsg.From.Concat(sourceMsg.ResentFrom)).ToString();
                targetMsg.To = new InternetAddressList(sourceMsg.To.Concat(sourceMsg.ResentTo)).ToString();
                targetMsg.Cc = new InternetAddressList(sourceMsg.Cc.Concat(sourceMsg.ResentCc)).ToString();
                targetMsg.Bcc = new InternetAddressList(sourceMsg.Bcc.Concat(sourceMsg.ResentBcc)).ToString();
                targetMsg.Keywords = string.Join(", ", keywords);

                // TODO: only do this if we've downloaded more than just the headers
                // store text (if present) and ALSO extract text from HTML (if present); they might not contain the same text.
                TimerCollection.Global.Start("ExtractBodyText");
                try {
                    targetMsg.Body = await ExtractBodyText(sourceMsg);
                } finally {
                    TimerCollection.Global.Stop("ExtractBodyText");
                }

                if (sourceMsg.Attachments.Any())
                {
                    targetMsg.AttachmentSummary = string.Join(Environment.NewLine,
                                                              sourceMsg.Attachments.Select((a, i) => $"{i + 1}. {(a is MimePart part ? part.FileName : null)} [{a.ContentType.MimeType}] " + a.Headers[HeaderId.ContentDescription]));
                }
                else
                {
                    targetMsg.AttachmentSummary = null;
                }

                TimerCollection.Global.Start("ZipArchive");
                using var zipStream = new MemoryStream();

                using (var zip = new ZipArchive(zipStream, ZipArchiveMode.Update))
                {
                    string filename = CreateMessageFilename(sourceMsg);
                    var entry = zip.CreateEntry(filename, CompressionLevel.Optimal);
                    try
                    {
                        entry.LastWriteTime = sourceMsg.Date;
                    }
                    catch
                    {
                        // ignore errors; just use today's date instead
                    }
                    using var compressedStream = entry.Open();
                    await sourceMsg.WriteToAsync(compressedStream);
                }
                targetMsg.Original = zipStream.ToArray();

                TimerCollection.Global.Stop("ZipArchive");
                // TODO: skip the above if we only have the headers
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateMessageAsync

        private static string CreateMessageFilename(MimeMessage sourceMsg, string extension = ".eml")
        {
            string? subject = MakeSafe(sourceMsg.Subject);
            InternetAddress sender = sourceMsg.From.FirstOrDefault();
            string? senderName = MakeSafe(sender?.Name ?? sender?.ToString());

            string fileName = $"{subject}{(subject != null && senderName != null ? " - " : "")}{senderName}";

            // If there's nothing left, just use a default name
            if (fileName.Length <= 3)
                return "message" + extension;
            else if (fileName.Length > 250)
                return MakeSafe(fileName.Substring(0, 250)) + extension;
            else
                return fileName + extension;
        }

        private static readonly char[] _forbiddenChars = new char[] { '<', '>', '|', '?', '*', ':', '/', '\\', '"' };
        private static readonly UnicodeCategory[] _spacingCategories = new UnicodeCategory[]
        {
            UnicodeCategory.LineSeparator,
            UnicodeCategory.ParagraphSeparator,
            UnicodeCategory.SpaceSeparator,
        };
        private static string? MakeSafe(string? text)
        {
            if (string.IsNullOrWhiteSpace(text))
                return null;

            // First, trim spaces and strip all diacritics; replace all spacing by a normal space
            char[] chars = text.Trim().Normalize(NormalizationForm.FormD)
                .Where(c => CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
                .Select(c => _spacingCategories.Contains(CharUnicodeInfo.GetUnicodeCategory(c)) ? ' ' : c)
                .ToArray();

            // Then, get rid of all remaining non-ascii characters (will get converted to '?')
            var ascii = Encoding.ASCII;
            chars = ascii.GetChars(ascii.GetBytes(chars));

            // Finally remove all forbidden characters from the file name (including '?')
            return new string(chars.Where(c => c >= ' ' && !_forbiddenChars.Contains(c)).ToArray())
                .Normalize(NormalizationForm.FormC)
                .Trim();
        }

        private async Task<string?> ExtractBodyText(MimeMessage sourceMsg)
        {
            string? bodyText = sourceMsg.TextBody?.Trim();
            if (sourceMsg.HtmlBody != null)
            {
                if (bodyText != null)
                    bodyText += Environment.NewLine + Environment.NewLine;
                else
                    bodyText = "";
                var errors = new List<Exception>();

                                throw errList.First();
                            }
                            else
                            {
                                throw new AggregateException(errList);
                            }
                        }
                        bodyText += result.Code?.Trim();
                        return bodyText;
                    }
                    catch (Exception ex)
                    {
                        errors.Add(ex);
                    }
                    try
                    {
                        var html = sourceMsg.HtmlBody;
                        // This HtmlConverter is known to sometimes get stuck in an infinite loop, so wait for a finite amount of time
                        using(var timeoutTokenSource = new CancellationTokenSource())
                        {
                            Task<string?> task = Task.Run(() => new ReverseMarkdown.Converter(_htmlConverterConfig).Convert(html)?.TrimEnd(), timeoutTokenSource.Token);
                            if (task.Wait(300000))
                            {
                                task.Dispose();
                            }
                            else
                            {
                                timeoutTokenSource.Cancel();

                                // Save the HTML to a temp file, so we can figure out why conversion takes so long
                                string tempFileName = MakeSafe(sourceMsg.MessageId) + '_' + CreateMessageFilename(sourceMsg, ".html");
                                using (var fs = new FileStream(tempFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.Read))
                                using (var writer = new StreamWriter(fs, Encoding.UTF8))
                                    await writer.WriteAsync(sourceMsg.HtmlBody);
                                File.SetLastWriteTimeUtc(tempFileName, sourceMsg.Date.UtcDateTime);
                                // TODO: remove above temp file saving

                                _ = task.ConfigureAwait(false);
                                task = task.ContinueWith((completedTask, data) => {
                                    if (data is null) throw new ArgumentNullException(nameof(data));
                                    var (gaveupAt, newFileName) = ((DateTime, string))data;
                                    Con.WriteLineErr($"\nHTML conversion finished, but at {DateTime.Now:HH:mm:ss}; we stopped waiting at {gaveupAt:HH:mm:ss}.", ConsoleColor.Cyan);
                                    var markDown = completedTask.Result;
                                    using (var writer = new StreamWriter(newFileName, false, Encoding.UTF8))
                                        writer.Write(markDown);
                                    return (string?)null;
                                }, (DateTime.Now, Path.ChangeExtension(tempFileName, ".md")), TaskScheduler.Current);
                                throw new TimeoutException("Timeout while converting HTML to markdown.");
                            }
                            bodyText += task.Result;
                        }
                        return bodyText;
                    }
                    catch (Exception ex)

                }
            }
            return bodyText;
        }

        private async Task PopulateParticipantsAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {
            try
            {
                var participantList = new List<(ParticipantField Field, string? Address, string Name)>();
                GatherParticipants(ref participantList, sourceMsg.From, ParticipantField.From);
                GatherParticipants(ref participantList, sourceMsg.To, ParticipantField.To);
                GatherParticipants(ref participantList, sourceMsg.Cc, ParticipantField.Cc);
                GatherParticipants(ref participantList, sourceMsg.Bcc, ParticipantField.Bcc);
                GatherParticipants(ref participantList, sourceMsg.ReplyTo, ParticipantField.ReplyTo);
                GatherParticipants(ref participantList, sourceMsg.Sender, ParticipantField.Sender);
                GatherParticipants(ref participantList, sourceMsg.ResentFrom, ParticipantField.ResentFrom);
                GatherParticipants(ref participantList, sourceMsg.ResentTo, ParticipantField.ResentTo);
                GatherParticipants(ref participantList, sourceMsg.ResentCc, ParticipantField.ResentCc);
                GatherParticipants(ref participantList, sourceMsg.ResentBcc, ParticipantField.ResentBcc);
                GatherParticipants(ref participantList, sourceMsg.ResentReplyTo, ParticipantField.ResentReplyTo);
                GatherParticipants(ref participantList, sourceMsg.ResentSender, ParticipantField.ResentSender);

                int order = 0;
                foreach (var (field, mailAddress, mailName) in participantList)
                {
                    string? address = mailAddress == "" ? null : mailAddress;
                    string? name = mailName == "" ? null : mailName;
                    if (name != null && name.Trim(' ', '\'', '"', '<', '>', '(', ')') == address)
                        name = null;

                    var msgParticipant = new MessageParticipant
                    {
                        Message = targetMsg,
                        Field = field,
                        Order = ++order,
                        Name = name,
                    };

                    string key = address ?? name ?? "";
                    if (_participants.TryGetValue(key, out long participantID))
                    {
                        msgParticipant.ParticipantID = participantID;
                        /* TODO: when cleaning up the database? Put the most used name for this address in the participant.
                        UPDATE Participants p
                           SET Name = (SELECT mp.Name
                                         FROM MessageParticipants mp
                                        WHERE ParticipantID = p.ID
                                          AND mp.Name IS NOT NULL
                                     GROUP BY mp.Name
                                     ORDER BY count(*) DESC
                                     ,        ID ASC
                                        LIMIT 1)
                         WHERE p.ID IN ({string.Join(', ', participantIDs)});
                        */
                    }
                    else
                    {
                        var participant = new Participant
                        {
                            Address = address,
                            Name = name,
                        };
                        await work.Participants.AddAsync(participant);
                        // We only know the IDs after saving the entities
                        await work.SaveChangesAsync();
                        _participants.TryAdd(key, participant.ID);

                        msgParticipant.ParticipantID = participant.ID;
                    }

                    await work.DB.MessageParticipants.AddAsync(msgParticipant, _cancellationToken);
                }
                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateParticipantsAsync

        private async Task PopulateKeywordsAsync(UnitOfWork work, Message targetMsg,
                                                 IEnumerable<string> keywords, IEnumerable<string> labels)
        {
            try
            {
                // Store the keywords separately, and link them to this message
                var allKeywords = keywords.Select(k => (Name: k, IsLabel: false))
                            .Concat(labels.Select(l => (Name: l, IsLabel: true)));
                var triplets = allKeywords.Select(mailKeyword => (mailKeyword.Name, mailKeyword.IsLabel,

                                                                  ID: _keywords.GetValueOrDefault(mailKeyword, 0)));
                var keywordSets = from triplet in triplets
                                  join dbMsgKeyword in work.DB.MessageKeywords.Where(mk => mk.Message == targetMsg)
                                       on triplet.ID equals dbMsgKeyword.KeywordID
                                       into msgKeywordSet
                                  from dbMsgKeyword in msgKeywordSet.DefaultIfEmpty()
                                  select (triplet.Name,
                                          triplet.IsLabel,
                                          msgKeywordID: dbMsgKeyword == null ? (long?)null : dbMsgKeyword.ID,
                                          keywordID: triplet.ID);
                int index = 0;
                foreach ((string mailKeyword, bool isLabel, long? msgKeywordID, long kwID) in keywordSets)
                {
                    index++;
                    if (msgKeywordID == null)
                    {
                        long keywordID = kwID;
                        if (keywordID == 0)
                        {
                            var keyword = new Keyword
                            {
                                Name = mailKeyword,
                                IsLabel = isLabel
                            };
                            await work.Keywords.AddAsync(keyword);
                            await work.SaveChangesAsync();
                            keywordID = keyword.ID;
                            _keywords.Add((mailKeyword, isLabel), keywordID);
                        }
                        await work.DB.MessageKeywords.AddAsync(new MessageKeyword
                        {
                            Message = targetMsg,
                            Order = index,
                            KeywordID = keywordID,
                        }, _cancellationToken);
                    }
                }
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        }

        /// <summary>
        /// Store any references between messages.
        /// </summary>
        /// <param name="work">The <see cref="UnitOfWork"/> to use when storing the references.</param>
        /// <param name="targetMsg">The (newly stored) <see cref="Message"/> that should link to the references.</param>
        /// <param name="sourceMsg">The <see cref="MimeMessage"/> containing the references.</param>
        private static async Task PopulateReferencesAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {
            try
            {
                // Include the In-Reply-To header in the list of references
                var sourceReferences = new List<string>(sourceMsg.References);
                if (!string.IsNullOrWhiteSpace(sourceMsg.InReplyTo) && !sourceReferences.Contains(sourceMsg.InReplyTo))
                    sourceReferences.Insert(0, sourceMsg.InReplyTo);

                var dbMessageRefs = await work.MessageReferences
                    .GetDictionaryAsync(mr => mr.Message == targetMsg,
                                        mr => mr.ReferencedRfcMessageID,
                                        mr => mr);
                var dbReferencedMessageIDs = work.Messages
                    .GetLookup(m => sourceReferences.Contains(m.RfcMessageID ?? ""),
                               m => m.RfcMessageID,
                               m => m?.ID); // make this a nullable long, so FirstOrDefault will return a null.
                int index = 0;
                foreach (string mailRef in sourceReferences)
                {
                    index++;
                    MessageReference? dbReference = dbMessageRefs.GetValueOrDefault(mailRef, null);
                    long? dbMessageID = null;
                    if (dbReferencedMessageIDs.Contains(mailRef))
                    {
                        dbMessageID = dbReferencedMessageIDs[mailRef].FirstOrDefault(id => id.HasValue);
                    }
                    if (dbReference == null)
                    {
                        await work.MessageReferences.AddAsync(new MessageReference
                        {
                            Message = targetMsg,
                            ReferencedRfcMessageID = mailRef,
                            InReplyTo = mailRef == sourceMsg.InReplyTo,
                            Order = index,
                            ReferencedMessageID = dbMessageID,
                        });
                    }
                    else
                    {
                        dbReference.Order = index;
                        dbReference.ReferencedMessageID = dbMessageID;
                        dbMessageRefs.Remove(mailRef);
                    }
                }

                // Any remaining references are no longer in use, so delete them
                work.MessageReferences.Delete(dbMessageRefs.Values);

                // Find any existing references to targetMsg, and fill them in
                if (targetMsg.RfcMessageID != null)
                {
                    var previousReferences = work.MessageReferences
                        .GetList(mr => mr.ReferencedRfcMessageID == targetMsg.RfcMessageID && mr.Message == null);
                    foreach (var dbReference in previousReferences)
                    {
                        dbReference.Message = targetMsg;
                    }
                }

                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateReferencesAsync

        private static void GatherParticipants(ref List<(ParticipantField Field, string? Address, string Name)> participantList, InternetAddress ia, ParticipantField field)
        {
            if (ia == null) return;
            var list = new InternetAddressList(new InternetAddress[] { ia });
            GatherParticipants(ref participantList, list, field);
        }
        private static void GatherParticipants(ref List<(ParticipantField Field, string? Address, string Name)> participantList, InternetAddressList list, ParticipantField field)
        {
            foreach (var ia in list)
            {
                if (ia is GroupAddress group)
                {
                    if (group.Members.Count > 0)
                    {
                        participantList.AddRange(group.Members
                                                 .Select(ma => ma as MailboxAddress)
                                                 .Select(ma => (field, ma?.Address, ma?.Name ?? "")));
                    }
                    else
                    {
                        participantList.Add((field, null, group.Name));
                    }
                }
                else if (ia is MailboxAddress mailbox)
                {
                    participantList.Add((field, mailbox.Address, mailbox.Name));
                }
                else
                {
                    participantList.Add((field, null, ia.Name));
                }
            }
        }

        private class KeywordEqualityComparer : IEqualityComparer<(string Name, bool IsLabel)>
        {

            public static KeywordEqualityComparer InvariantCultureIgnoreCase { get; } = new KeywordEqualityComparer();

            public bool Equals((string Name, bool IsLabel) x, (string Name, bool IsLabel) y)
            {
                return x.IsLabel == y.IsLabel
                    && x.Name.Equals(y.Name, StringComparison.InvariantCultureIgnoreCase);
            }

            public int GetHashCode((string Name, bool IsLabel) obj)
            {
                return obj.Name.GetHashCode(StringComparison.InvariantCultureIgnoreCase) ^ obj.IsLabel.GetHashCode();
            }
        }

    }
}

// <auto-generated />
using System;
using MailJanitor;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace MailJanitor.Migrations
{
    [DbContext(typeof(MailJanitorContext))]
    [Migration("20190914062021_Initial")]
    partial class Initial
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "2.2.6-servicing-10079");

            modelBuilder.Entity("MailJanitor.Models.Account", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<long?>("AllFolderID");

                    b.Property<long?>("ArchiveFolderID");

                    b.Property<long?>("DraftsFolderID");

                    b.Property<long?>("FlaggedFolderID");

                    b.Property<string>("Host")
                        .IsRequired()
                        .HasColumnType("TEXT COLLATE NOCASE");

                    b.Property<long?>("InboxFolderID");

                    b.Property<short>("Port");

                    b.Property<long?>("SentFolderID");

                    b.Property<long?>("SpamFolderID");

                    b.Property<long?>("TrashFolderID");

                    b.Property<string>("UserName")
                        .IsRequired()
                        .HasColumnType("TEXT COLLATE NOCASE");

                    b.HasKey("ID");

                    b.ToTable("Accounts");
                });

            modelBuilder.Entity("MailJanitor.Models.Folder", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<long>("AccountID");

                    b.Property<int>("Attributes");

                    b.Property<string>("Name")
                        .IsRequired();

                    b.Property<long?>("ParentFolderID");

                    b.Property<uint?>("UIDNext");

                    b.Property<uint>("UIDValidity");

                    b.HasKey("ID");

                    b.HasIndex("AccountID");

                    b.HasIndex("ParentFolderID");

                    b.ToTable("Folders");
                });

            modelBuilder.Entity("MailJanitor.Models.FolderMessage", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<long>("FolderID");

                    b.Property<long>("MessageID");

                    b.Property<uint>("UID");

                    b.HasKey("ID");

                    b.HasIndex("FolderID");

                    b.HasIndex("MessageID");

                    b.ToTable("FolderMessages");
                });

            modelBuilder.Entity("MailJanitor.Models.Keyword", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<bool>("IsLabel");

                    b.Property<string>("Name")
                        .IsRequired()
                        .HasColumnType("TEXT COLLATE NOCASE");

                    b.HasKey("ID");

                    b.HasIndex("Name", "IsLabel")
                        .IsUnique();

                    b.ToTable("Keywords");
                });

            modelBuilder.Entity("MailJanitor.Models.Message", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<string>("AttachmentSummary");

                    b.Property<string>("Bcc");

                    b.Property<string>("Body");

                    b.Property<string>("Cc");

                    b.Property<DateTimeOffset>("Date");

                    b.Property<DateTimeOffset?>("DateReceived");

                    b.Property<DateTime>("DateRetrievedUTC");

                    b.Property<int>("DownloadStatus");

                    b.Property<int>("Flags");

                    b.Property<string>("From");

                    b.Property<ulong?>("GlobalID");

                    b.Property<string>("HTMLBody");

                    b.Property<string>("Keywords");

                    b.Property<byte[]>("Original");

                    b.Property<string>("RfcMessageID");

                    b.Property<string>("Subject");

                    b.Property<string>("To");

                    b.HasKey("ID");

                    b.HasIndex("GlobalID");

                    b.HasIndex("RfcMessageID");

                    b.ToTable("Messages");
                });

            modelBuilder.Entity("MailJanitor.Models.MessageKeyword", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<long>("KeywordID");

                    b.Property<long>("MessageID");

                    b.Property<int>("Order");

                    b.HasKey("ID");

                    b.HasIndex("KeywordID");

                    b.HasIndex("MessageID");

                    b.ToTable("MessageKeywords");
                });

            modelBuilder.Entity("MailJanitor.Models.MessageParticipant", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");

                    b.Property<string>("Name");

                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");

                    b.HasIndex("MessageID");

                    b.HasIndex("ParticipantID");

                    b.ToTable("MessageParticipants");
                });

            modelBuilder.Entity("MailJanitor.Models.MessageReference", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<bool>("InReplyTo");

                    b.Property<long>("MessageID");

                    b.Property<int>("Order");

                    b.Property<long?>("ReferencedMessageID");

                    b.Property<string>("ReferencedRfcMessageID")
                        .IsRequired();

                    b.HasKey("ID");

                    b.HasIndex("MessageID");

                    b.HasIndex("ReferencedMessageID");

                    b.ToTable("MessageReferences");
                });

            modelBuilder.Entity("MailJanitor.Models.Participant", b =>
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<string>("Address")
                        .HasColumnType("TEXT COLLATE NOCASE");

                    b.Property<string>("Name")
                        .HasColumnType("TEXT COLLATE NOCASE");

                    b.HasKey("ID");

                    b.HasIndex("Address", "Name")
                        .IsUnique();

                    b.ToTable("Participants");
                });

            modelBuilder.Entity("MailJanitor.Models.Folder", b =>
                {
                    b.HasOne("MailJanitor.Models.Account", "Account")
                        .WithMany("Folders")
                        .HasForeignKey("AccountID")
                        .OnDelete(DeleteBehavior.Cascade);

                    b.HasOne("MailJanitor.Models.Folder", "ParentFolder")
                        .WithMany("Children")
                        .HasForeignKey("ParentFolderID");
                });

            modelBuilder.Entity("MailJanitor.Models.FolderMessage", b =>
                {
                    b.HasOne("MailJanitor.Models.Folder", "Folder")
                        .WithMany("Messages")
                        .HasForeignKey("FolderID")
                        .OnDelete(DeleteBehavior.Cascade);

                    b.HasOne("MailJanitor.Models.Message", "Message")
                        .WithMany("Folders")
                        .HasForeignKey("MessageID")
                        .OnDelete(DeleteBehavior.Cascade);
                });

            modelBuilder.Entity("MailJanitor.Models.MessageKeyword", b =>
                {
                    b.HasOne("MailJanitor.Models.Keyword", "Keyword")
                        .WithMany("Messages")
                        .HasForeignKey("KeywordID")
                        .OnDelete(DeleteBehavior.Cascade);

                    b.HasOne("MailJanitor.Models.Message", "Message")
                        .WithMany("KeywordList")
                        .HasForeignKey("MessageID")
                        .OnDelete(DeleteBehavior.Cascade);
                });

            modelBuilder.Entity("MailJanitor.Models.MessageParticipant", b =>
                {
                    b.HasOne("MailJanitor.Models.Message", "Message")
                        .WithMany("Participants")
                        .HasForeignKey("MessageID")
                        .OnDelete(DeleteBehavior.Cascade);

                    b.HasOne("MailJanitor.Models.Participant", "Participant")
                        .WithMany("Messages")
                        .HasForeignKey("ParticipantID")
                        .OnDelete(DeleteBehavior.Cascade);
                });

            modelBuilder.Entity("MailJanitor.Models.MessageReference", b =>
                {
                    b.HasOne("MailJanitor.Models.Message", "Message")
                        .WithMany("References")
                        .HasForeignKey("MessageID")
                        .OnDelete(DeleteBehavior.Cascade);

                    b.HasOne("MailJanitor.Models.Message", "ReferencedMessage")
                        .WithMany("ReferencedBy")
                        .HasForeignKey("ReferencedMessageID");
                });
#pragma warning restore 612, 618
        }
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;

namespace MailJanitor.Migrations
{
    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Accounts",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Host = table.Column<string>(type: "TEXT COLLATE NOCASE", nullable: false),
                    Port = table.Column<short>(nullable: false),
                    UserName = table.Column<string>(type: "TEXT COLLATE NOCASE", nullable: false),
                    InboxFolderID = table.Column<long>(nullable: true),
                    DraftsFolderID = table.Column<long>(nullable: true),
                    SentFolderID = table.Column<long>(nullable: true),
                    FlaggedFolderID = table.Column<long>(nullable: true),
                    ArchiveFolderID = table.Column<long>(nullable: true),
                    AllFolderID = table.Column<long>(nullable: true),
                    TrashFolderID = table.Column<long>(nullable: true),
                    SpamFolderID = table.Column<long>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Accounts", x => x.ID);
                });

            migrationBuilder.CreateTable(
                name: "Keywords",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Name = table.Column<string>(type: "TEXT COLLATE NOCASE", nullable: false),
                    IsLabel = table.Column<bool>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Keywords", x => x.ID);
                });

            migrationBuilder.CreateTable(
                name: "Messages",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Flags = table.Column<int>(nullable: false),
                    GlobalID = table.Column<ulong>(nullable: true),
                    RfcMessageID = table.Column<string>(nullable: true),
                    Date = table.Column<DateTimeOffset>(nullable: false),
                    DateReceived = table.Column<DateTimeOffset>(nullable: true),
                    DateRetrievedUTC = table.Column<DateTime>(nullable: false),
                    From = table.Column<string>(nullable: true),
                    To = table.Column<string>(nullable: true),
                    Cc = table.Column<string>(nullable: true),
                    Bcc = table.Column<string>(nullable: true),
                    Subject = table.Column<string>(nullable: true),
                    Body = table.Column<string>(nullable: true),
                    HTMLBody = table.Column<string>(nullable: true),
                    Keywords = table.Column<string>(nullable: true),
                    AttachmentSummary = table.Column<string>(nullable: true),
                    Original = table.Column<byte[]>(nullable: true),
                    DownloadStatus = table.Column<int>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Messages", x => x.ID);
                });

            migrationBuilder.CreateTable(
                name: "Participants",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Address = table.Column<string>(type: "TEXT COLLATE NOCASE", nullable: true),
                    Name = table.Column<string>(type: "TEXT COLLATE NOCASE", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Participants", x => x.ID);
                });

            migrationBuilder.CreateTable(
                name: "Folders",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    AccountID = table.Column<long>(nullable: false),
                    Name = table.Column<string>(nullable: false),
                    UIDValidity = table.Column<uint>(nullable: false),
                    UIDNext = table.Column<uint>(nullable: true),
                    Attributes = table.Column<int>(nullable: false),
                    ParentFolderID = table.Column<long>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Folders", x => x.ID);
                    table.ForeignKey(
                        name: "FK_Folders_Accounts_AccountID",
                        column: x => x.AccountID,
                        principalTable: "Accounts",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_Folders_Folders_ParentFolderID",
                        column: x => x.ParentFolderID,
                        principalTable: "Folders",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "MessageKeywords",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    MessageID = table.Column<long>(nullable: false),
                    KeywordID = table.Column<long>(nullable: false),
                    Order = table.Column<int>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageKeywords", x => x.ID);
                    table.ForeignKey(
                        name: "FK_MessageKeywords_Keywords_KeywordID",
                        column: x => x.KeywordID,
                        principalTable: "Keywords",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_MessageKeywords_Messages_MessageID",
                        column: x => x.MessageID,
                        principalTable: "Messages",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "MessageReferences",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    MessageID = table.Column<long>(nullable: false),
                    InReplyTo = table.Column<bool>(nullable: false),
                    Order = table.Column<int>(nullable: false),
                    ReferencedRfcMessageID = table.Column<string>(nullable: false),
                    ReferencedMessageID = table.Column<long>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageReferences", x => x.ID);
                    table.ForeignKey(
                        name: "FK_MessageReferences_Messages_MessageID",
                        column: x => x.MessageID,
                        principalTable: "Messages",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_MessageReferences_Messages_ReferencedMessageID",
                        column: x => x.ReferencedMessageID,
                        principalTable: "Messages",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "MessageParticipants",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    MessageID = table.Column<long>(nullable: false),
                    ParticipantID = table.Column<long>(nullable: false),
                    Field = table.Column<int>(nullable: false),
                    Order = table.Column<int>(nullable: false),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageParticipants", x => x.ID);
                    table.ForeignKey(
                        name: "FK_MessageParticipants_Messages_MessageID",
                        column: x => x.MessageID,
                        principalTable: "Messages",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_MessageParticipants_Participants_ParticipantID",
                        column: x => x.ParticipantID,
                        principalTable: "Participants",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "FolderMessages",
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    FolderID = table.Column<long>(nullable: false),
                    MessageID = table.Column<long>(nullable: false),
                    UID = table.Column<uint>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_FolderMessages", x => x.ID);
                    table.ForeignKey(
                        name: "FK_FolderMessages_Folders_FolderID",
                        column: x => x.FolderID,
                        principalTable: "Folders",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_FolderMessages_Messages_MessageID",
                        column: x => x.MessageID,
                        principalTable: "Messages",
                        principalColumn: "ID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_FolderMessages_FolderID",
                table: "FolderMessages",
                column: "FolderID");

            migrationBuilder.CreateIndex(
                name: "IX_FolderMessages_MessageID",
                table: "FolderMessages",
                column: "MessageID");

            migrationBuilder.CreateIndex(
                name: "IX_Folders_AccountID",
                table: "Folders",
                column: "AccountID");

            migrationBuilder.CreateIndex(
                name: "IX_Folders_ParentFolderID",
                table: "Folders",
                column: "ParentFolderID");

            migrationBuilder.CreateIndex(
                name: "IX_Keywords_Name_IsLabel",
                table: "Keywords",
                columns: new[] { "Name", "IsLabel" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_MessageKeywords_KeywordID",
                table: "MessageKeywords",
                column: "KeywordID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageKeywords_MessageID",
                table: "MessageKeywords",
                column: "MessageID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageParticipants_MessageID",
                table: "MessageParticipants",
                column: "MessageID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageParticipants_ParticipantID",
                table: "MessageParticipants",
                column: "ParticipantID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageReferences_MessageID",
                table: "MessageReferences",
                column: "MessageID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageReferences_ReferencedMessageID",
                table: "MessageReferences",
                column: "ReferencedMessageID");

            migrationBuilder.CreateIndex(
                name: "IX_Messages_GlobalID",
                table: "Messages",
                column: "GlobalID");

            migrationBuilder.CreateIndex(
                name: "IX_Messages_RfcMessageID",
                table: "Messages",
                column: "RfcMessageID");

            migrationBuilder.CreateIndex(
                name: "IX_Participants_Address_Name",
                table: "Participants",
                columns: new[] { "Address", "Name" },
                unique: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "FolderMessages");

            migrationBuilder.DropTable(
                name: "MessageKeywords");

            migrationBuilder.DropTable(
                name: "MessageParticipants");

            migrationBuilder.DropTable(
                name: "MessageReferences");

            migrationBuilder.DropTable(
                name: "Folders");

            migrationBuilder.DropTable(
                name: "Keywords");

            migrationBuilder.DropTable(
                name: "Participants");

            migrationBuilder.DropTable(
                name: "Messages");

            migrationBuilder.DropTable(
                name: "Accounts");
        }
    }
}

                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");

                    b.Property<string>("Name");

                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace MailJanitor.Models
{
    public class Account
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Host { get; set; } = "";
        public short Port { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string UserName { get; set; } = "";
        public long? InboxFolderID { get; set; }
        //public Folder InboxFolder { get; set; } TODO: restore these, and use annotations to fix migration
        public long? DraftsFolderID { get; set; }
        //public Folder DraftsFolder { get; set; }
        public long? SentFolderID { get; set; }
        //public Folder SentFolder { get; set; }
        public long? FlaggedFolderID { get; set; }
        //public Folder FlaggedFolder { get; set; }
        public long? ArchiveFolderID { get; set; }
        //public Folder ArchiveFolder { get; set; }
        public long? AllFolderID { get; set; }
        //public Folder AllFolder { get; set; }
        public long? TrashFolderID { get; set; }
        //public Folder TrashFolder { get; set; }
        public long? SpamFolderID { get; set; }
        //public Folder SpamFolder { get; set; }

        public ICollection<Folder>? Folders { get; internal set; }
    }
}

using MailKit;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Text;

namespace MailJanitor.Models
{
    public class Folder
    {
        public long ID { get; set; }
        public long AccountID { get; set; }
        public Account? Account { get; set; }

        public string Name { get; set; } = "";
        public uint UIDValidity { get; set; }
        public uint? UIDNext { get; set; }
        public FolderAttributes Attributes { get; set; }
        public long? ParentFolderID { get; set; }
        public Folder? ParentFolder { get; set; }

        public ICollection<Folder>? Children { get; }
        public ICollection<FolderMessage>? Messages { get; }
    }
}

using MailKit;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace MailJanitor.Models
{
    public class FolderMessage
    {
        public long ID { get; set; }

        public long FolderID { get; set; }
        public Folder? Folder { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public uint UID { get; set; }






    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace MailJanitor.Models
{
    public class Keyword
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Name { get; set; } = "";
        public bool IsLabel { get; set; }

        public ICollection<MessageKeyword>? Messages { get; internal set; }
    }
}

{
    public class Message
    {
        public long ID { get; set; }

        public MessageFlags Flags { get; set; }
        public ulong? GlobalID { get; set; }
        public string? RfcMessageID { get; set; }

        public DateTimeOffset Date { get; set; }
        public DateTimeOffset? DateReceived { get; set; }
        public DateTime DateRetrievedUTC { get; set; } = DateTime.UtcNow;

        public string? From { get; set; }
        public string? To { get; set; }
        public string? Cc { get; set; }
        public string? Bcc { get; set; }

        public string? Subject { get; set; }
        public string? Body { get; set; }
        public string? HTMLBody { get; set; }
        public string? Keywords { get; set; }
        public string? AttachmentSummary { get; set; }

        public byte[]? Original { get; set; }
        public DownloadStatus DownloadStatus { get; set; }

        public ICollection<MessageParticipant>? Participants { get; internal set; }
        public ICollection<FolderMessage>? Folders { get; }
        public ICollection<MessageKeyword>? KeywordList { get; }
        [InverseProperty("Message")]
        public ICollection<MessageReference>? References { get; }
        [InverseProperty("ReferencedMessage")]
        public ICollection<MessageReference>? ReferencedBy { get; }
    }

    public enum DownloadStatus
    {
        Nothing,
        Headers, // only the message headers have been downloaded
        Preview, // the message headers and the preview text has been downloaded

using System;
using System.Collections.Generic;
using System.Text;

namespace MailJanitor.Models
{
    public class MessageKeyword
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public long KeywordID { get; set; }
        public Keyword? Keyword { get; set; }
        public int Order { get; set; }
    }
}

    }

    public class MessageParticipant
    {
        public long ID { get; set; }

        public long MessageID { get; set; }
        public Message? Message { get; set; }

        public long ParticipantID { get; set; }
        public Participant? Participant { get; set; }

        public ParticipantField Field { get; set; }
        public int Order { get; set; }
        public string? Name { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace MailJanitor.Models
{
    public class MessageReference
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public bool InReplyTo { get; set; }
        public int Order { get; set; }

        public string ReferencedRfcMessageID { get; set; } = "";
        public long? ReferencedMessageID { get; set; }
        public Message? ReferencedMessage { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace MailJanitor.Models
{
    public class Participant
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string? Address { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string? Name { get; set; }

        public ICollection<MessageParticipant>? Messages { get; internal set; }
    }
}

using CommandLine;

namespace MailJanitor
{
    public class CommandLineOptions
    {
        [Value(0, MetaName = "<username>", Required = true, HelpText = "User name (e-mail address).")]
        public string EmailAddress { get; set; } = "";

        [Value(1, MetaName = "<password>", Required = true, HelpText = "Password for the mail server.")]
        public string Password { get; set; } = "";

        [Value(2, MetaName = "<mail server>", Required = false, Default = "imap.gmail.com",
            HelpText = "The host name of the e-mail server. Defaults to imap.gmail.com.")]
        public string MailServer { get; set; } = "";

        [Value(3, MetaName = "<port number>", Required = false, Default = 993,
            HelpText = "The port number of the IMAP e-mail server. Defaults to 993 (IMAP over SSL).")]
        public short PortNumber { get; set; }

        [Option('v', "verbose", Required = false, HelpText = "Show more output.")]
        public bool Verbose { get; set; }

        [Option('c', "compact-database", Required = false, Default = false, HelpText = "Vacuum the database when done.")]

using System;
using System.Net;
using System.Threading;
using System.Threading.Tasks;
using CommandLine;
using MailKit;
using MC.Utilities;


namespace MailJanitor
{
    class Program
    {
        public enum ResultCode
        {

        static int Main(string[] args)
        {
            ResultCode result = ResultCode.Exception;
            try
            {
                Parser.Default.ParseArguments<CommandLineOptions>(args)
                    .WithParsed(o =>
                    {
                        using (var tokenSource = new CancellationTokenSource())
                        {
                            Console.CancelKeyPress += new ConsoleCancelEventHandler((sender, eventArgs) =>
                            {
                                eventArgs.Cancel = true; //(eventArgs.SpecialKey != ConsoleSpecialKey.ControlBreak);

                            } finally {
                                TimerCollection.Global.Stop("SynchronizeAccount");
                            }
                            bool FetchFolder(IMailFolder mailFolder)
                            {
                                var offendingAttributes = FolderAttributes.Drafts
                                                        | FolderAttributes.All
                                                        | FolderAttributes.Archive
                                                        | FolderAttributes.Trash
                                                        | FolderAttributes.Junk;
                                return (mailFolder.Attributes & offendingAttributes) == FolderAttributes.None;
                            }
                        }
                        result = ResultCode.Ok;
                    })
                    .WithNotParsed(error =>
                    {
                        result = ResultCode.WrongArgument;
                    });
            }
            catch (Exception ex)
            {
                Con.WriteLine(ex);








                // Return meaningful error codes
                if (ex is TaskCanceledException || ex is OperationCanceledException)
                    result = ResultCode.Cancelled;
                else if (ex is ServiceNotAuthenticatedException)
                    result = ResultCode.AuthenticationProblem;
                else if (ex is ServiceNotConnectedException)
                    result = ResultCode.ConnectionProblem;
                else
                    result = ResultCode.Exception;
            }
            finally
            {
                Con.WriteLine(TimerCollection.Global.ToString());
            }

            return (int)result;
        }

        // TODO: use an FTS table to enable searching for messages
    }
}

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class AccountsRepository : Repository<Account>
    {
        public AccountsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        public async Task<Account> GetOrCreateAsync(string host, short port, string userName)
        {
            Account account = await _set
                .Include(a => a.Folders)

using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FolderMessagesRepository : Repository<FolderMessage>
    {
        internal FolderMessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        /// <summary>
        /// Retrieves the highest UID in the given <paramref name="folder"/>.
        /// </summary>
        public async Task<uint> GetMaxUIDAsync(Folder folder)
        {
            var folderMessages = _db.FolderMessages
                .Where(fm => fm.Folder == folder);

            if (await folderMessages.AnyAsync())
            {
                return await folderMessages.MaxAsync(fm => fm.UID, _cancellationToken);
            }
            else
            {
                return uint.MinValue;
            }
        }

        /// <summary>
        /// Retrieves a list of <see cref="FolderMessage.ID"/>s and <see cref="Message.ID"/>s matching the given
        /// <paramref name="globalIDs"/> in the given <paramref name="dbFolderID"/>.
        /// </summary>
        /// <param name="dbFolderID">The ID of the folder containing those messages.</param>
        /// <param name="globalIDs">A list of server-wide IDs for the messages we're looking for.</param>
        /// <returns>A <see cref="Dictionary{ulong, (long? folderMsgID, long? messageID)}"/> containing, for each server-wide ID,
        /// a tuple with the corresponding <see cref="FolderMessage.ID"/> and <see cref="Message.ID"/>. These can be
        /// <see cref="null"/> if not present in the database.</returns>
        public async Task<Dictionary<ulong, (long? folderMsgID, long? messageID)>> GetIDsByGlobalIDAsync(long dbFolderID, IEnumerable<ulong> globalIDs)
        {
            return await(from dbMessage in _db.Messages
                         where globalIDs.Contains(dbMessage.GlobalID ?? 0)
                         join folderMsg in _set.Where(folderMsg => folderMsg.FolderID == dbFolderID)
                              on dbMessage.ID equals folderMsg.MessageID
                              into folderMsgs
                         from folderMsg in folderMsgs.DefaultIfEmpty()
                         select new
                         {
                             globalID = dbMessage.GlobalID ?? 0,
                             folderMsgID = folderMsg == null ? null : (long?)folderMsg.ID,
                             messageID = dbMessage == null ? null : (long?)dbMessage.ID,
                         })
                        .ToDictionaryAsync(idSet => idSet.globalID,
                                           idSet => (idSet.folderMsgID, idSet.messageID),
                                           _cancellationToken);
        }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using MailKit;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FoldersRepository : Repository<Folder>
    {
        internal FoldersRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        public async Task<Folder> GetOrCreateAsync(IMailFolder mailFolder, Account account)
        {
            if (account is null)
            {
                throw new ArgumentNullException(nameof(account));
            }

            if (_db.Entry(account).State == EntityState.Detached)
                account = _db.Accounts.Find(account.ID); // Make sure we've got an entity from the current DbContext

            // If the mail folder has a parent, make sure that exists first
            Folder? dbParentFolder = null;
            if (mailFolder.ParentFolder != null && !string.IsNullOrEmpty(mailFolder.ParentFolder.Name))
            {
                dbParentFolder = await GetOrCreateAsync(mailFolder.ParentFolder, account);
            }

            Folder? dbFolder = await _set
                .Where(f => f.AccountID == account.ID && f.Name == mailFolder.Name && f.ParentFolderID == (dbParentFolder == null ? (long?)null : dbParentFolder.ID))
                .Include(f => f.ParentFolder)
                .SingleOrDefaultAsync(_cancellationToken);
            if (dbFolder == null)
            {
                dbFolder = new Folder
                {

            }
            await _db.SaveChangesAsync(_cancellationToken);
            return dbFolder;
        }

        public Task<string> GetFullNameAsync(Folder folder)
        {
            if (folder is null)
            {
                throw new ArgumentNullException(nameof(folder));
            }

            return GetFullNameAsync(folder.ID);
        }
        public async Task<string> GetFullNameAsync(long id)
        {
            return (await GetFullNamesAsync(new long[] { id })).First();
        }

            )
              SELECT Name
                FROM FullNames
               WHERE ParentID IS NULL
            ORDER BY CASE ID {string.Join("\n", ids.Select((id, index) => $"WHEN {id} THEN {index}"))} END
            ;";
#pragma warning disable EF1000 // There's no risk of SQL injection with this query.
            return await _set.FromSqlRaw(sql).Select(f => f.Name).ToListAsync();
#pragma warning restore EF1000
        }
    }
}

using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class KeywordsRepository : Repository<Keyword>
    {
        internal KeywordsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessageReferencesRepository : Repository<MessageReference>
    {
        internal MessageReferencesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessagesRepository : Repository<Message>
    {
        internal MessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class ParticipantsRepository : Repository<Participant>
    {
        internal ParticipantsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace MailJanitor.Repositories
{
    public abstract class Repository<T> where T : class
    {
        protected readonly MailJanitorContext _db;
        protected readonly DbSet<T> _set;
        protected readonly CancellationToken _cancellationToken;

        internal Repository(MailJanitorContext db, CancellationToken cancellationToken)
        {
            _db = db ?? throw new ArgumentNullException(nameof(db));
            _set = _db.Set<T>();
            _cancellationToken = cancellationToken;
        }

        public Task<T> GetByIdAsync(long id)
        {
            return _set.FindAsync(new object[] { id }, _cancellationToken).AsTask();
        }

        public Task<T> SingleOrDefaultAsync(Expression<Func<T, bool>> where)
        {
            return _set.SingleOrDefaultAsync(where, _cancellationToken);
        }

        public IEnumerable<T> GetList(Expression<Func<T, bool>>? where = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.AsEnumerable();
        }
        public IEnumerable<T> GetList<TKey>(Expression<Func<T, bool>> where,
                                         Expression<Func<T, TKey>> orderBy,
                                         IComparer<TKey>? keyComparer = null)
        {
            return _set
                .Where(where)
                .OrderBy(orderBy, keyComparer ?? Comparer<TKey>.Default)
                .AsEnumerable();
        }

        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey>? keyComparer = null)
                                                                  where TKey: notnull
        {
            return GetDictionaryAsync((Expression<Func<T, bool>>?)null, keySelector, keyComparer);
        }
        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Expression<Func<T, bool>>? where,
                                                                  Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey>? keyComparer = null)
                                                                  where TKey : notnull
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync(keySelector,
                                            keyComparer ?? EqualityComparer<TKey>.Default,
                                            _cancellationToken);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey>? keyComparer = null)
                                                                              where TKey : notnull
        {
            return GetDictionaryAsync(null, keySelector, valueSelector, keyComparer);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Expression<Func<T, bool>>? where,
                                                                              Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey>? keyComparer = null)
                                                                              where TKey : notnull
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync(keySelector,
                                            valueSelector,
                                            keyComparer ?? EqualityComparer<TKey>.Default,
                                            _cancellationToken);
        }

        public ILookup<TKey, T> GetLookup<TKey>(Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey>? keyComparer = null)
        {
            return GetLookup((Expression<Func<T, bool>>?)null, keySelector, keyComparer);
        }
        public ILookup<TKey, T> GetLookup<TKey>(Expression<Func<T, bool>>? where,
                                                Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey>? keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey>? keyComparer = null)
        {
            return GetLookup(null, keySelector, valueSelector, keyComparer);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Expression<Func<T, bool>>? where,
                                                             Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey>? keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   valueSelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }

        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, bool>> where,
                                                    Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<TResult, bool>>? valueFilter = null,
                                                    IComparer<TResult>? valueSorter = null)
        {
            return Select(valueExpression, where, valueFilter, valueSorter);
        }
        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<T, bool>>? entityFilter = null,
                                                    Expression<Func<TResult, bool>>? valueFilter = null,
                                                    IComparer<TResult>? valueSorter = null)
        {
            IQueryable<T> list = _set;
            if (entityFilter != null)
                list = list.Where(entityFilter);
            IQueryable<TResult> result = list.Select(valueExpression);
            if (valueFilter != null)
                result = result.Where(valueFilter);

        public void Update(params T[] entities)
        {
            Update((IEnumerable<T>)entities);
        }
        public void Update(IEnumerable<T> entities)
        {
            if (entities is null)
            {
                throw new ArgumentNullException(nameof(entities));
            }

            foreach (T entity in entities)
            {
                _db.Entry(entity).State = EntityState.Modified;
            }
        }

        public void Delete(Expression<Func<T, bool>> predicate)

        public int DeleteByIDs<TKey>(Expression<Func<T, bool>> predicate, Expression<Func<T, TKey>> idSelector)
        {
            IEnumerable<TKey> ids = _set.Where(predicate).Select(idSelector).AsEnumerable();
            return DeleteByIDs(ids);
        }
        public int DeleteByID<TKey>(params TKey[] ids)
        {
            return DeleteByIDs(ids);
        }
        public virtual int DeleteByIDs<TKey>(IEnumerable<TKey> ids)
        {
            if (typeof(long).IsAssignableFrom(typeof(TKey)))
            {
                IEntityType entityType = _db.Model.FindEntityType(typeof(T));
                IReadOnlyList<IProperty> keyProperties = entityType.FindPrimaryKey().Properties;
                if (keyProperties.Count == 1)
                {
                    IProperty key = keyProperties[0];
                    if (typeof(long).IsAssignableFrom(key.ClrType))
                    {
                        // Ensure we've got Int64s, and nothing else
                        var numericIDs = ids.Select(id => Convert.ToInt64(id, CultureInfo.InvariantCulture));

                        // Prepare the SQL statement
                        string tableName = entityType.GetTableName();
                        string keyColumn = key.GetColumnName();
                        string sql = $"DELETE FROM [{tableName}] WHERE [{keyColumn}] IN ({string.Join(", ", numericIDs)})";
                        #pragma warning disable EF1000 // SQL command has been checked for SQL injection vectors, and found safe.
                        int numDeleted = _db.Database.ExecuteSqlRaw(sql);
                        #pragma warning restore EF1000

                        // Detach all corresponding entries
                        var entries = _db.ChangeTracker.Entries()
                            .Where(e => e.Metadata == entityType)
                            .Where(e => ids.Contains((TKey)e.Property(key.Name).CurrentValue));
                        foreach (var entry in entries)

    public class UnitOfWork : IDisposable
    {
        private readonly MailJanitorContext _db;
        private readonly CancellationToken _cancellationToken;

        public MailJanitorContext DB { get => _db; }

        public UnitOfWork(CancellationToken cancellationToken = default)
        {
            _cancellationToken = cancellationToken;
            _db = new MailJanitorContext();
            _db.ChangeTracker.LazyLoadingEnabled = false;
            Accounts = new AccountsRepository(_db, _cancellationToken);
            Folders = new FoldersRepository(_db, _cancellationToken);
            FolderMessages = new FolderMessagesRepository(_db, _cancellationToken);

        public FoldersRepository Folders { get; private set; }
        public FolderMessagesRepository FolderMessages { get; private set; }
        public MessagesRepository Messages { get; private set; }
        public ParticipantsRepository Participants { get; private set; }
        public KeywordsRepository Keywords { get; private set; }
        public MessageReferencesRepository MessageReferences { get; private set; }

        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, IEnumerable<TProperty>>> propertyExpression)
            where T : class
            where TProperty : class
        {
            _db.Entry(entity).Collection(propertyExpression);
        }
        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, TProperty>> propertyExpression)
            where T : class
            where TProperty : class
        {
            _db.Entry(entity).Reference(propertyExpression);
        }

        public Task<int> SaveChangesAsync()
        {
            return _db.SaveChangesAsync(_cancellationToken);

                if (disposing)
                {
                    // dispose managed state (managed objects).
                    _db.Dispose();
                }

                // set large fields to null.







                _disposedValue = true;
            }
        }

        // This code added to correctly implement the disposable pattern.
        public void Dispose()
        {
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion

    }
}

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace MC.Utilities
{
    internal static class Con
    {
        public static readonly Dictionary<TraceLevel,ConsoleColor> LevelColors = new Dictionary<TraceLevel, ConsoleColor>
        {
            { TraceLevel.Off, ConsoleColor.Black },
            { TraceLevel.Verbose, ConsoleColor.DarkGray },
            { TraceLevel.Info, ConsoleColor.Gray },
            { TraceLevel.Warning, ConsoleColor.Yellow },

                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                WriteErr(text, color);
            else
                Write(text, color);
        }

        public static void WriteLine(string text, ConsoleColor color)
        {
            Write(text + Environment.NewLine, color);
        }
        public static void Write(string text, ConsoleColor color)
        {
            ConsoleColor oldColor = ConsoleColor.Gray;

                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                await WriteErrAsync(text, color);
            else
                await WriteAsync(text, color);
        }

        public static async Task WriteLineAsync(string text, ConsoleColor color)
        {
            await WriteAsync(text + Environment.NewLine, color);
        }
        public static async Task WriteLineAsync(Exception ex, ConsoleColor color)
        {
            await WriteLineAsync(ex.ToString(), color);

            await Console.Error.WriteAsync(text);
            if (!System.Console.IsOutputRedirected)
            {
                Console.ForegroundColor = oldColor;
            }
        }

    }
}