[*.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

# Default severity for analyzer diagnostics with category 'Globalization'
dotnet_analyzer_diagnostic.category-Globalization.severity = silent

.vs/
.vscode/
bin/
obj/
mailjanitor*.sqlite
**.sqlite-journal
**.sqlite-shm
**.sqlite-wal

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</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");
        }

using System.Text;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using MailKit;
using MC.Utilities;
using Microsoft.EntityFrameworkCore;

using MimeKit;

namespace MailJanitor
{
    public class MailSynchronizer
    {
        private readonly CancellationToken _cancellationToken;
        private readonly ReverseMarkdown.Config _htmlConverterConfig = new ReverseMarkdown.Config
        {
            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)
        {
            _startAccount = DateTime.UtcNow;
            Con.WriteLine($"{_startAccount.ToLocalTime():s} Synchronizing account {host}...", TraceLevel.Verbose);
            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);

                // Filter out all unwanted folders
                mailFolders = mailFolders.Where(mf => !mf.Attributes.HasFlag(FolderAttributes.NoSelect)
                                                    && !mf.Attributes.HasFlag(FolderAttributes.NonExistent)
                                                    && (folderFilter == null || folderFilter(mf)));

                _totalCountAllFolders = mailFolders.Sum(mf => mf.Count);
                _countAllFolders = 0;


                // Keep track of processed folders, to see if one has been deleted
                var remainingFolderIDs = new List<long>();
                using (var work = new UnitOfWork(_cancellationToken))
                {
                    remainingFolderIDs.AddRange(work.Folders.Select(f => f.AccountID == account.ID, f => f.ID));
                }

                        }
                    }

                    await SynchronizeFolder(mailFolder, dbFolder);

                    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;

                    }
                }
            }

            // clean up free messages and participants
            _participants.Clear();
            _keywords.Clear();

                    messageIDs = await work.FolderMessages.GetIDsByGlobalIDAsync(dbFolder.ID, globalIDs);
                }

                int totalCount = summaries.Count, counter = 0;
                Con.WriteLine($"\tFetching {totalCount} new and/or updated messages...", TraceLevel.Verbose);
                DateTime start = DateTime.UtcNow;
                DateTime lastProgressUpdate = DateTime.MinValue;
                foreach (IMessageSummary summary in summaries)
                {
                    counter++;
                    _countAllFolders++;

                    long? folderMsgID = null;
                    long? messageID = null;
                    if (messageIDs.TryGetValue(summary.GMailMessageId.Value, out var ids))

                }
            }
        }

        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;
                    await PopulateMessageAsync(dbMessage, mailMessage, summary.Keywords);

                    // Store all separate entities contained in the mail message
                    await PopulateParticipantsAsync(work, dbMessage, mailMessage);    // from headers
                    await PopulateKeywordsAsync(work, dbMessage, summary.Keywords, summary.GMailLabels); // from summary
                    await PopulateReferencesAsync(work, dbMessage, mailMessage);      // from summary + headers

                }
                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
            {
                mailMessage = await mailFolder.GetMessageAsync(summary.UniqueId, _cancellationToken);
            }
            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
            {

                // 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.
                targetMsg.Body = await ExtractBodyText(sourceMsg);

                if (sourceMsg.Attachments.Any())
                {
                    targetMsg.AttachmentSummary = string.Join(Environment.NewLine, sourceMsg.Attachments.Select(SummarizeAttachment));

                }
                else
                {
                    targetMsg.AttachmentSummary = null;
                }

                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();

                // 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)

                    await Con.WriteLineAsync();
                    await Con.WriteLineAsync(ex);
                    //await Con.WriteLineAsync(sourceMsg.HtmlBody?.Trim(), ConsoleColor.DarkYellow);
                }
            }
            return bodyText;
        }

        private string SummarizeAttachment(MimeEntity attachment, int index)
        {
            string? fileName = attachment is MimePart part ? part.FileName : null;
            string? size = long.TryParse(attachment.Headers[HeaderId.ContentLength], out long length) ? "; " + FormatSize(length) : null;
            string? description = attachment.Headers[HeaderId.ContentDescription];
            if (description == fileName) description = "";
            return $"{index + 1}. {fileName} [{attachment.ContentType.MimeType}{size}] {description}".TrimEnd();
        }

        private static readonly List<(string Prefix, long Multiplier)> _siMultipliers = " kMGTPEZY".Select((prefix, index) => (prefix.ToString().Trim(), (long)Math.Pow(10, index))).ToList();
        private string FormatSize(long bytes, int decimals = 1)
        {
            var (prefix, multiplier) = _siMultipliers.LastOrDefault(x => bytes < x.Multiplier);
            if (multiplier == 0)
            {
                (prefix, multiplier) = _siMultipliers.Last();
            }
            if (multiplier > 1 && decimals > 0)
                return string.Format("#,##0." + new string('0', decimals), bytes / multiplier) + ' ' + prefix + 'B';
            else
                return $"{bytes / multiplier:#,##0} {prefix}B";
        }

        private static readonly List<(string Prefix, long Multiplier)> _biMultipliers = "_KMGTPEZY".Select((prefix, index) => (Prefix: prefix.ToString().Trim(), (long)Math.Pow(2, Math.Pow(10, index))))
            .Select(x => x.Prefix == "_" ? ("", 1) : x).ToList();
        private string FormatSizeBinary(long bytes)
        {
            var (prefix, multiplier) = _biMultipliers.LastOrDefault(x => bytes < x.Multiplier);
            if (multiplier == 0)
            {
                (prefix, multiplier) = _biMultipliers.Last();
            }
            return $"{bytes / multiplier:#,##0} {prefix}iB";
        }

        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

                                                 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,

        /// <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)
                    {

                    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();
            }
        }

    }
}

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; } = 993;

        [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.")]
        public bool VacuumDatabase { get; set; }

        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);
                                tokenSource.Cancel(true);
                            });

                            var mailFetcher = new MailSynchronizer(tokenSource.Token);
                            mailFetcher.SynchronizeAccount(o.MailServer,
                                                           o.PortNumber,
                                                           new NetworkCredential(o.EmailAddress, o.Password),
                                                           FetchFolder,
                                                           o.VacuumDatabase).Wait();

                            static bool FetchFolder(IMailFolder mailFolder)
                            {
                                var offendingAttributes = FolderAttributes.Drafts
                                                        | FolderAttributes.All
                                                        | FolderAttributes.Archive
                                                        | FolderAttributes.Trash
                                                        | FolderAttributes.Junk;
                                return (mailFolder.Attributes & offendingAttributes) == FolderAttributes.None;

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)
        {
            // Prevent problems with case-sensitivity
            host = host.ToLowerInvariant();
            userName = userName.ToLowerInvariant();

            Account? account = await _set
                .Include(a => a.Folders)
                .SingleOrDefaultAsync(a => a.Host == host
                                       && a.Port == port
                                       && a.UserName == userName,
                                     _cancellationToken);
            if (account == null)
            {
                account = new Account
                {
                    Host = host,
                    Port = port,

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;
            }
        }

    }
}

{
	"folders": [
		{
			"path": "."
		}
	],
	"settings": {
		"files.exclude": {
			"**/obj": true
		}
	}
}