MessageCache.cs

//
// MessageCache.cs
//
// Author: Jeffrey Stedfast <jeff@xamarin.com>
//
// Copyright (c) 2020 Jeffrey Stedfast
//
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.
//

using System;
using System.IO;
using System.Data;
using System.Text;
using System.Threading;
using System.Data.Common;
using System.Globalization;
using System.Threading.Tasks;
using System.Collections.Generic;

using MimeKit;
using MailKit;
using MailKit.Search;

using Mono.Data.Sqlite;

namespace MailKitCache
{
	public class MessageCache : IDisposable
	{
		static readonly DateTime InvalidDateTime = DateTime.MinValue.ToUniversalTime ();

		static readonly DataTable[] DataTables;
		static readonly DataTable MessageTable;
		static readonly DataTable KeywordsTable;
		static readonly DataTable XGMLabelsTable;
		//static readonly DataTable AnnotationsTable;
		static readonly DataTable StatusTable;

		static MessageCache ()
		{
			MessageTable = CreateMessageTable ();
			KeywordsTable = CreateKeywordsTable ();
			XGMLabelsTable = CreateXGMLabelsTable ();
			//AnnotationsTable = CreateAnnotationsTable ();
			StatusTable = CreateStatusTable ();

			DataTables = new DataTable[] {
				StatusTable, MessageTable, KeywordsTable, XGMLabelsTable /*, AnnotationsTable */
			};
		}

		static DataTable CreateMessageTable ()
		{
			var table = new DataTable ("ZMESSAGES");
			table.Columns.Add (new DataColumn ("UID", typeof (long)) { AllowDBNull = false, Unique = true });
			table.Columns.Add (new DataColumn ("FETCHED", typeof (int)) { AllowDBNull = false });
			table.Columns.Add (new DataColumn ("INTERNALDATE", typeof (DateTime)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("INTERNALTIMEZONE", typeof (long)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("SIZE", typeof (long)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("FLAGS", typeof (int)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("MODSEQ", typeof (long)) { AllowDBNull = true });

			// ENVELOPE
			table.Columns.Add (new DataColumn ("DATE", typeof (DateTime)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("TIMEZONE", typeof (long)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("SUBJECT", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("FROM", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("SENDER", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("REPLYTO", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("TO", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("CC", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("BCC", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("INREPLYTO", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("MESSAGEID", typeof (string)) { AllowDBNull = true });

			// REFERENCES
			table.Columns.Add (new DataColumn ("REFERENCES", typeof (string)) { AllowDBNull = true });

			// BODYSTRUCTURE
			table.Columns.Add (new DataColumn ("BODYSTRUCTURE", typeof (string)) { AllowDBNull = true });

			// PREVIEWTEXT
			table.Columns.Add (new DataColumn ("PREVIEWTEXT", typeof (string)) { AllowDBNull = true });

			// GMail-specific features
			table.Columns.Add (new DataColumn ("XGMMSGID", typeof (long)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("XGMTHRID", typeof (long)) { AllowDBNull = true });

			// OBJECTID features
			table.Columns.Add (new DataColumn ("EMAILID", typeof (string)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("THREADID", typeof (string)) { AllowDBNull = true });

			// Set the UID as the primary key
			table.PrimaryKey = new DataColumn[] { table.Columns[0] };

			return table;
		}

		static DataTable CreateKeywordsTable ()
		{
			var table = new DataTable ("ZKEYWORDS");
			table.Columns.Add (new DataColumn ("ROWID", typeof (int)) { AutoIncrement = true });
			table.Columns.Add (new DataColumn ("UID", typeof (long)) { AllowDBNull = false });
			table.Columns.Add (new DataColumn ("KEYWORD", typeof (string)) { AllowDBNull = false });
			table.PrimaryKey = new DataColumn[] { table.Columns[0] };

			return table;
		}

		static DataTable CreateXGMLabelsTable ()
		{
			var table = new DataTable ("ZXGMLABELS");
			table.Columns.Add (new DataColumn ("ROWID", typeof (int)) { AutoIncrement = true });
			table.Columns.Add (new DataColumn ("UID", typeof (long)) { AllowDBNull = false });
			table.Columns.Add (new DataColumn ("KEYWORD", typeof (string)) { AllowDBNull = false });
			table.PrimaryKey = new DataColumn[] { table.Columns[0] };

			return table;
		}

		static DataTable CreateStatusTable ()
		{
			var table = new DataTable ("ZSTATUS");
			table.Columns.Add (new DataColumn ("ROWID", typeof (int)) { AllowDBNull = false, Unique = true });
			table.Columns.Add (new DataColumn ("UIDVALIDITY", typeof (long)) { AllowDBNull = false });
			table.Columns.Add (new DataColumn ("UIDNEXT", typeof (long)) { AllowDBNull = true });
			table.Columns.Add (new DataColumn ("HIGHESTMODSEQ", typeof (long)) { AllowDBNull = true });

			//table.Columns.Add (new DataColumn ("COUNT", typeof (long)) { AllowDBNull = false });
			//table.Columns.Add (new DataColumn ("RECENT", typeof (long)) { AllowDBNull = false });
			//table.Columns.Add (new DataColumn ("UNREAD", typeof (long)) { AllowDBNull = false });
			//table.Columns.Add (new DataColumn ("SIZE", typeof (long)) { AllowDBNull = false });

			//table.Columns.Add (new DataColumn ("APPENDLIMIT", typeof (long)) { AllowDBNull = true });
			//table.Columns.Add (new DataColumn ("MAILBOXID", typeof (string)) { AllowDBNull = true });

			table.PrimaryKey = new DataColumn[] { table.Columns[0] };

			return table;
		}

		static string EncodeFolderName (IMailFolder folder)
		{
			var builder = new StringBuilder ();

			for (int i = 0; i < folder.FullName.Length; i++) {
				switch (folder.FullName[i]) {
				case '%': builder.Append ("%25"); break;
				case '/': builder.Append ("%2F"); break;
				case ':': builder.Append ("%3A"); break;
				case '\\': builder.Append ("%5C"); break;
				default: builder.Append (folder.FullName[i]); break;
				}
			}

			return builder.ToString ();
		}

		readonly Dictionary<Stream, string> streams = new Dictionary<Stream, string> ();
		readonly IMailFolder folder;
		SqliteConnection sqlite;
		string cacheDir;

		public MessageCache (IMailFolder folder, string cacheDir)
		{
			this.cacheDir = Path.Combine (cacheDir, EncodeFolderName (folder));
			this.folder = folder;

			var builder = new SqliteConnectionStringBuilder ();
			builder.DataSource = Path.Combine (cacheDir, "index.sqlite");
			builder.DateTimeFormat = SQLiteDateFormats.Ticks;

			sqlite = new SqliteConnection (builder.ConnectionString);

			folder.UidNextChanged += OnUidNextChanged;
			folder.UidValidityChanged += OnUidValidityChanged;
			folder.HighestModSeqChanged += OnHighestModSeqChanged;

			folder.MessageExpunged += OnMessageExpunged;
			folder.MessagesVanished += OnMessagesVanished;
			folder.MessageSummaryFetched += OnMessageSummaryFetched;
		}

		public MessageSummaryItems DesiredSummaryItems {
			get; set;
		}

		public ulong? HighestModSeq {
			get; private set;
		}

		public uint? UidNext {
			get; private set;
		}

		public uint UidValidity {
			get; private set;
		}

		void ReadStatus (DbDataReader reader)
		{
			for (int i = 0; i < reader.FieldCount; i++) {
				switch (reader.GetName (i)) {
				case "UIDVALIDITY":
					UidValidity = (uint) reader.GetInt64 (i);
					break;
				case "UIDNEXT":
					if (!reader.IsDBNull (i))
						UidNext = (uint) reader.GetInt64 (i);
					else
						UidNext = null;
					break;
				case "HIGHESTMODSEQ":
					if (!reader.IsDBNull (i))
						HighestModSeq = (ulong) reader.GetInt64 (i);
					else
						HighestModSeq = null;
					break;
				}
			}
		}

		DbCommand CreateLoadStatusCommand ()
		{
			var command = sqlite.CreateCommand ();
			command.CommandText = $"SELECT * FROM {StatusTable.TableName} WHERE ROWID = @ROWID LIMIT 1";
			command.Parameters.AddWithValue ("@ROWID", 0);
			command.CommandType = CommandType.Text;
			return command;
		}

		bool LoadStatus ()
		{
			using (var command = CreateLoadStatusCommand ()) {
				using (var reader = command.ExecuteReader ()) {
					if (!reader.Read ())
						return false;

					ReadStatus (reader);

					return true;
				}
			}
		}

		async Task<bool> LoadStatusAsync (CancellationToken cancellationToken)
		{
			using (var command = CreateLoadStatusCommand ()) {
				using (var reader = await command.ExecuteReaderAsync (cancellationToken).ConfigureAwait (false)) {
					if (!await reader.ReadAsync (cancellationToken).ConfigureAwait (false))
						return false;

					ReadStatus (reader);

					return true;
				}
			}
		}

		void Load ()
		{
			foreach (var dataTable in DataTables)
				sqlite.CreateTable (dataTable);

			if (LoadStatus ())
				return;

			SaveStatus ();
		}

		async Task LoadAsync (CancellationToken cancellationToken)
		{
			foreach (var dataTable in DataTables)
				await sqlite.CreateTableAsync (dataTable, cancellationToken).ConfigureAwait (false);

			if (await LoadStatusAsync (cancellationToken).ConfigureAwait (false))
				return;

			await SaveStatusAsync (cancellationToken).ConfigureAwait (false);
		}

		DbCommand CreateSaveStatusCommand ()
		{
			var command = sqlite.CreateCommand ();
			command.Parameters.AddWithValue ("@ROWID", 0);
			command.Parameters.AddWithValue ("@UIDVALIDITY", (long) UidValidity);
			command.Parameters.AddWithValue ("@UIDNEXT", UidNext.HasValue ? (object) UidNext.Value : null);
			command.Parameters.AddWithValue ("@HIGHESTMODSEQ", HighestModSeq.HasValue ? (object) HighestModSeq.Value : null);

			command.CommandText = $"INSERT OR REPLACE INTO {StatusTable.TableName} (ROWID, UIDVALIDITY, UIDNEXT, HIGHESTMODSEQ) VALUES(@ROWID, @UIDVALIDITY, @UIDNEXT, @HIGHESTMODSEQ)";
			command.CommandType = CommandType.Text;

			return command;
		}

		void SaveStatus ()
		{
			using (var command = CreateSaveStatusCommand ())
				command.ExecuteNonQuery ();
		}

		async Task SaveStatusAsync (CancellationToken cancellationToken)
		{
			using (var command = CreateSaveStatusCommand ())
				await command.ExecuteNonQueryAsync (cancellationToken).ConfigureAwait (false);
		}

		async Task FetchMissingMetadataAsync (IMailFolder folder, MessageSummaryItems desiredSummaryItems, CancellationToken cancellationToken)
		{
			// Get the list of message UIDs that we are missing information for.
			var uids = await GetIncompleteUidsAsync (desiredSummaryItems, cancellationToken).ConfigureAwait (false);

			// Fetch the information for the messages that we are missing metadata for.
			await folder.FetchAsync (uids, desiredSummaryItems, cancellationToken).ConfigureAwait (false);
		}

		public async Task<FolderAccess> OpenAndResyncAsync (IMailFolder folder, FolderAccess access, CancellationToken cancellationToken)
		{
			var changedSince = HighestModSeq;
			var validity = UidValidity;
			var state = sqlite.State;

			await ConnectAsync (cancellationToken).ConfigureAwait (false);

			try {
				FolderAccess result;
				bool resynced;

				if (folder.SupportsQuickResync && validity > 0 && changedSince.HasValue) {
					var empty = new UniqueIdSet ();

					result = await folder.OpenAsync (access, validity, changedSince.Value, empty, cancellationToken).ConfigureAwait (false);
					resynced = true;
				} else {
					// Note: Either QRESYNC is not available or we don't have anything in our cache (yet).
					result = await folder.OpenAsync (access, cancellationToken).ConfigureAwait (false);
					resynced = false;
				}

				if (result == FolderAccess.None) {
					if (state == ConnectionState.Closed)
						Disconnect ();

					return result;
				}

				if (resynced)
					return result;

				var cachedUids = await GetAllUidsAsync (cancellationToken).ConfigureAwait (false);

				if (cachedUids.Count == 0)
					return result;

				if (folder.SupportsModSeq && changedSince.HasValue) {
					// Fetch the latest FLAGS and MODSEQ values for all of our cached messages that have changed since our last known mod-sequence value.
					// Note: The server will send us VANISHED events for any messages that have been expunged.
					await folder.FetchAsync (cachedUids, changedSince.Value, MessageSummaryItems.Flags | MessageSummaryItems.ModSeq).ConfigureAwait (false);
				} else {
					// Get the full list of message UIDs in the remote folder.
					var uids = await folder.SearchAsync (SearchQuery.All, cancellationToken).ConfigureAwait (false);

					// Purge the cache of any messages that no longer exist on the server.
					for (int i = cachedUids.Count - 1; i >= 0; i--) {
						var uid = cachedUids[i];

						if (!uids.Contains (uid)) {
							// The cached message UID no longer exists on the server.
							await ExpungeAsync (uid, cancellationToken).ConfigureAwait (false);
							cachedUids.RemoveAt (i);
						}
					}

					// Fetch the latest FLAGS and MODSEQ values for all of our cached messages.
					await folder.FetchAsync (cachedUids, MessageSummaryItems.Flags | MessageSummaryItems.ModSeq).ConfigureAwait (false);
				}

				return result;
			} catch (OperationCanceledException) {
				if (state == ConnectionState.Closed)
					Disconnect ();
				throw;
			}
		}

		public async Task ConnectAsync (CancellationToken cancellationToken)
		{
			if (sqlite.State == ConnectionState.Closed) {
				await sqlite.OpenAsync (cancellationToken).ConfigureAwait (false);
				await LoadAsync (cancellationToken).ConfigureAwait (false);
			}
		}

		public void Connect ()
		{
			if (sqlite.State == ConnectionState.Closed) {
				sqlite.Open ();
				Load ();
			}
		}

		public void Disconnect ()
		{
			if (sqlite.State != ConnectionState.Closed)
				sqlite.Close ();
		}

		void Drop (string tableName)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"DROP TABLE IF EXISTS {tableName}";
				command.CommandType = CommandType.Text;

				command.ExecuteNonQuery ();
			}
		}

		public void Clear ()
		{
			// TODO: clear message files as well (once that gets implemented)
			using (var transaction = sqlite.BeginTransaction ()) {
				Drop (MessageTable.TableName);
				Drop (KeywordsTable.TableName);
				Drop (XGMLabelsTable.TableName);

				sqlite.CreateTable (MessageTable);
				sqlite.CreateTable (KeywordsTable);
				sqlite.CreateTable (XGMLabelsTable);

				transaction.Commit ();
			}
		}

		public bool TryGetUniqueId (int index, out UniqueId uid)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.Parameters.AddWithValue ("@INDEX", (long) index);

				command.CommandText = $"SELECT UID FROM {MessageTable.TableName} ORDER BY UID LIMIT 1 OFFSET @INDEX";
				command.CommandType = CommandType.Text;

				using (var reader = command.ExecuteReader (CommandBehavior.SingleRow)) {
					if (reader.Read ()) {
						int column = reader.GetOrdinal ("UID");

						if (column != -1) {
							uid = new UniqueId ((uint) reader.GetInt64 (column));
							return true;
						}
					}

					uid = UniqueId.Invalid;

					return false;
				}
			}
		}

		public IList<UniqueId> GetAllUids ()
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"SELECT UID FROM {MessageTable.TableName}";
				command.CommandType = CommandType.Text;

				using (var reader = command.ExecuteReader ()) {
					var uids = new UniqueIdSet (SortOrder.Ascending);

					while (reader.Read ()) {
						int index = reader.GetOrdinal ("UID");
						var uid = (uint) reader.GetInt64 (index);

						uids.Add (new UniqueId (uid));
					}

					return uids;
				}
			}
		}

		public async Task<IList<UniqueId>> GetAllUidsAsync (CancellationToken cancellationToken)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"SELECT UID FROM {MessageTable.TableName}";
				command.CommandType = CommandType.Text;

				using (var reader = await command.ExecuteReaderAsync ().ConfigureAwait (false)) {
					var uids = new UniqueIdSet (SortOrder.Ascending);

					while (await reader.ReadAsync (cancellationToken).ConfigureAwait (false)) {
						int index = reader.GetOrdinal ("UID");
						var uid = (uint) reader.GetInt64 (index);

						uids.Add (new UniqueId (uid));
					}

					return uids;
				}
			}
		}

		public async Task<IList<UniqueId>> GetIncompleteUidsAsync (MessageSummaryItems missing, CancellationToken cancellationToken)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"SELECT UID FROM { MessageTable.TableName } WHERE FETCHED & @FIELDS != @FIELDS";
				command.Parameters.AddWithValue ("@FIELDS", (int) missing);
				command.CommandType = CommandType.Text;

				using (var reader = await command.ExecuteReaderAsync ().ConfigureAwait (false)) {
					var uids = new UniqueIdSet (SortOrder.Ascending);

					while (await reader.ReadAsync (cancellationToken).ConfigureAwait (false)) {
						int index = reader.GetOrdinal ("UID");
						var uid = (uint) reader.GetInt64 (index);

						uids.Add (new UniqueId (uid));
					}

					return uids;
				}
			}
		}

		DbCommand CreateExpungeMessageCommand (UniqueId uid)
		{
			var command = sqlite.CreateCommand ();
			command.CommandText = $"DELETE FROM {MessageTable.TableName} WHERE UID = @UID";
			command.Parameters.AddWithValue ("@UID", (long) uid.Id);
			command.CommandType = CommandType.Text;
			return command;
		}

		DbCommand CreateExpungeKeywordsCommand (UniqueId uid)
		{
			var command = sqlite.CreateCommand ();
			command.CommandText = $"DELETE FROM {KeywordsTable.TableName} WHERE UID = @UID";
			command.Parameters.AddWithValue ("@UID", (long) uid.Id);
			command.CommandType = CommandType.Text;
			return command;
		}

		DbCommand CreateExpungeXGMLabelsCommand (UniqueId uid)
		{
			var command = sqlite.CreateCommand ();
			command.CommandText = $"DELETE FROM {XGMLabelsTable.TableName} WHERE UID = @UID";
			command.Parameters.AddWithValue ("@UID", (long) uid.Id);
			command.CommandType = CommandType.Text;
			return command;
		}

		public void Expunge (UniqueId uid)
		{
			using (var transaction = sqlite.BeginTransaction ()) {
				try {
					using (var command = CreateExpungeMessageCommand (uid))
						command.ExecuteNonQuery ();

					using (var command = CreateExpungeKeywordsCommand (uid))
						command.ExecuteNonQuery ();

					using (var command = CreateExpungeXGMLabelsCommand (uid))
						command.ExecuteNonQuery ();

					transaction.Commit ();
				} catch {
					transaction.Rollback ();
					throw;
				}
			}
		}

		public async Task ExpungeAsync (UniqueId uid, CancellationToken cancellationToken)
		{
			using (var transaction = sqlite.BeginTransaction ()) {
				try {
					using (var command = CreateExpungeMessageCommand (uid))
						await command.ExecuteNonQueryAsync (cancellationToken).ConfigureAwait (false);

					using (var command = CreateExpungeKeywordsCommand (uid))
						await command.ExecuteNonQueryAsync (cancellationToken).ConfigureAwait (false);

					using (var command = CreateExpungeXGMLabelsCommand (uid))
						await command.ExecuteNonQueryAsync (cancellationToken).ConfigureAwait (false);

					transaction.Commit ();
				} catch {
					transaction.Rollback ();
					throw;
				}
			}
		}

		static List<string> GetMessageTableColumns (MessageSummaryItems items)
		{
			var columns = new List<string> ();

			if ((items & MessageSummaryItems.UniqueId) != 0)
				columns.Add ("UID");
			if ((items & MessageSummaryItems.InternalDate) != 0) {
				columns.Add ("INTERNALDATE");
				columns.Add ("INTERNALTIMEZONE");
			}
			if ((items & MessageSummaryItems.Size) != 0)
				columns.Add ("SIZE");
			if ((items & MessageSummaryItems.Flags) != 0)
				columns.Add ("FLAGS");
			if ((items & MessageSummaryItems.ModSeq) != 0)
				columns.Add ("MODSEQ");
			if ((items & MessageSummaryItems.Envelope) != 0) {
				columns.Add ("DATE");
				columns.Add ("TIMEZONE");
				columns.Add ("SUBJECT");
				columns.Add ("FROM");
				columns.Add ("SENDER");
				columns.Add ("REPLYTO");
				columns.Add ("TO");
				columns.Add ("CC");
				columns.Add ("BCC");
				columns.Add ("INREPLYTO");
				columns.Add ("MESSAGEID");
			}
			if ((items & MessageSummaryItems.References) != 0)
				columns.Add ("REFERENCES");
			if ((items & (MessageSummaryItems.BodyStructure | MessageSummaryItems.Body)) != 0)
				columns.Add ("BODYSTRUCTURE");
			if ((items & MessageSummaryItems.PreviewText) != 0)
				columns.Add ("PREVIEWTEXT");
			if ((items & MessageSummaryItems.GMailMessageId) != 0)
				columns.Add ("XGMMSGID");
			if ((items & MessageSummaryItems.GMailThreadId) != 0)
				columns.Add ("XGMTHRID");
			if ((items & MessageSummaryItems.EmailId) != 0)
				columns.Add ("EMAILID");
			if ((items & MessageSummaryItems.ThreadId) != 0)
				columns.Add ("THREADID");

			return columns;
		}

		static DateTimeOffset GetDateTimeOffset (DateTime utc, long timeZone)
		{
			var dateTime = new DateTime (utc.Ticks, DateTimeKind.Unspecified);
			var offset = new TimeSpan (timeZone);

			dateTime = dateTime.Add (offset);

			return new DateTimeOffset (dateTime, offset);
		}

		static void LoadInternetAddressList (InternetAddressList list, DbDataReader reader, int column)
		{
			try {
				var addresses = reader.GetInternetAddressList (column);
				list.AddRange (addresses);
				addresses.Clear ();
			} catch {
			}
		}

		void LoadMessages (List<IMessageSummary> messages, MessageSummaryItems items, DbDataReader reader, int startIndex)
		{
			int index = startIndex;

			while (reader.Read ()) {
				var message = new MessageSummary (index++);
				var internalDate = InvalidDateTime;
				long internalTimeZone = -1;
				var date = InvalidDateTime;
				long timeZone = -1;

				messages.Add (message);

				if ((items & MessageSummaryItems.Envelope) != 0)
					message.Envelope = new Envelope ();

				for (int i = 0; i < reader.FieldCount; i++) {
					if (reader.IsDBNull (i))
						continue;

					switch (reader.GetName (i)) {
					case "UID":
						message.UniqueId = reader.GetUniqueId (i, UidValidity);
						break;
					case "INTERNALDATE":
						internalDate = reader.GetDateTime (i);
						break;
					case "INTERNALTIMEZONE":
						internalTimeZone = reader.GetInt64 (i);
						break;
					case "SIZE":
						message.Size = (uint) reader.GetInt64 (i);
						break;
					case "FLAGS":
						message.Flags = reader.GetMessageFlags (i);
						break;
					case "MODSEQ":
						message.ModSeq = reader.GetUInt64 (i);
						break;
					case "DATE":
						date = reader.GetDateTime (i);
						break;
					case "TIMEZONE":
						timeZone = reader.GetInt64 (i);
						break;
					case "SUBJECT":
						message.Envelope.Subject = reader.GetString (i);
						break;
					case "FROM":
						LoadInternetAddressList (message.Envelope.From, reader, i);
						break;
					case "SENDER":
						LoadInternetAddressList (message.Envelope.Sender, reader, i);
						break;
					case "REPLYTO":
						LoadInternetAddressList (message.Envelope.ReplyTo, reader, i);
						break;
					case "TO":
						LoadInternetAddressList (message.Envelope.To, reader, i);
						break;
					case "CC":
						LoadInternetAddressList (message.Envelope.Cc, reader, i);
						break;
					case "BCC":
						LoadInternetAddressList (message.Envelope.Bcc, reader, i);
						break;
					case "INREPLYTO":
						message.Envelope.InReplyTo = reader.GetString (i);
						break;
					case "MESSAGEID":
						message.Envelope.MessageId = reader.GetString (i);
						break;
					case "REFERENCES":
						message.References = reader.GetReferences (i);
						break;
					case "BODYSTRUCTURE":
						message.Body = reader.GetBodyStructure (i);
						break;
					case "PREVIEWTEXT":
						message.PreviewText = reader.GetString (i);
						break;
					case "XGMMSGID":
						message.GMailMessageId = reader.GetUInt64 (i);
						break;
					case "XGMTHRID":
						message.GMailThreadId = reader.GetUInt64 (i);
						break;
					case "EMAILID":
						message.EmailId = reader.GetString (i);
						break;
					case "THREADID":
						message.ThreadId = reader.GetString (i);
						break;
					}
				}

				if (internalDate != InvalidDateTime)
					message.InternalDate = GetDateTimeOffset (internalDate, internalTimeZone);

				if (date != InvalidDateTime)
					message.Envelope.Date = GetDateTimeOffset (date, timeZone);
			}
		}

		void LoadKeywords (UniqueId uid, HashSet<string> keywords)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"SELECT KEYWORD FROM {KeywordsTable.TableName} WHERE UID = @UID";
				command.Parameters.AddWithValue ("@UID", (long) uid.Id);
				command.CommandType = CommandType.Text;

				using (var reader = command.ExecuteReader ()) {
					while (reader.Read ()) {
						var column = reader.GetOrdinal ("KEYWORD");

						if (column != -1)
							keywords.Add (reader.GetString (column));
					}
				}
			}
		}

		void LoadXGMLabels (UniqueId uid, HashSet<string> labels)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"SELECT LABEL FROM {XGMLabelsTable.TableName} WHERE UID = @UID";
				command.Parameters.AddWithValue ("@UID", (long) uid.Id);
				command.CommandType = CommandType.Text;

				using (var reader = command.ExecuteReader ()) {
					while (reader.Read ()) {
						var column = reader.GetOrdinal ("LABEL");

						if (column != -1)
							labels.Add (reader.GetString (column));
					}
				}
			}
		}

		public List<IMessageSummary> Fetch (int startIndex, int count, MessageSummaryItems items)
		{
			var messages = new List<IMessageSummary> ();

			if (count == 0)
				return messages;

			if ((items & (MessageSummaryItems.Flags /*| MessageSummaryItems.Annotations*/)) != 0)
				items |= MessageSummaryItems.UniqueId;

			using (var command = sqlite.CreateCommand ()) {
				var columns = GetMessageTableColumns (items);
				var builder = new StringBuilder ("SELECT ");

				if (columns.Count > 0) {
					foreach (var column in columns)
						builder = builder.Append (column).Append (", ");

					builder.Length -= 2;
				} else {
					builder.Append ("UID");
				}

				builder.Append ($"FROM {MessageTable.TableName} ORDER BY UID");

				if (count != -1) {
					command.Parameters.AddWithValue ("@LIMIT", count);
					builder.Append (" LIMIT @LIMIT");
				}

				if (startIndex > 0) {
					command.Parameters.AddWithValue ("@OFFSET", startIndex);
					builder.Append (" OFFSET @OFFSET");
				}

				command.CommandText = builder.ToString ();
				command.CommandType = CommandType.Text;

				using (var reader = command.ExecuteReader ())
					LoadMessages (messages, items, reader, startIndex);
			}

			if ((items & MessageSummaryItems.Flags) != 0) {
				foreach (var message in messages)
					LoadKeywords (message.UniqueId, message.Keywords);
			}

			if ((items & MessageSummaryItems.GMailLabels) != 0) {
				foreach (var message in messages) {
					// FIXME: IMessageSummary.GMailLabels should be a HashSet<string>.
					var labels = new HashSet<string> ();

					LoadXGMLabels (message.UniqueId, labels);

					foreach (var label in labels)
						message.GMailLabels.Add (label);
				}
			}

			return messages;
		}

		public void Insert (UniqueId uid)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"INSERT INTO {MessageTable.TableName} OR IGNORE (UID, FETCHED) VALUES(@UID, @FETCHED)";
				command.Parameters.AddWithValue ("@FETCHED", (int) MessageSummaryItems.UniqueId);
				command.Parameters.AddWithValue ("@UID", (long) uid.Id);
				command.CommandType = CommandType.Text;
				command.ExecuteNonQuery ();
			}
		}

		object GetValue (UniqueId uid, IMessageSummary message, string columnName)
		{
			switch (columnName) {
			case "UID":
				return (long) uid.Id;
			case "INTERNALDATE":
				if (message.InternalDate.HasValue)
					return message.InternalDate.Value.ToUniversalTime ().DateTime;
				return null;
			case "INTERNALTIMEZONE":
				if (message.InternalDate.HasValue)
					return message.InternalDate.Value.Offset.Ticks;
				return null;
			case "SIZE":
				if (message.Size.HasValue)
					return (long) message.Size.Value;
				return null;
			case "FLAGS":
				if (message.Flags.HasValue)
					return (long) message.Flags.Value;
				return null;
			case "MODSEQ":
				if (message.ModSeq.HasValue)
					return (long) message.ModSeq.Value;
				return null;
			case "DATE":
				if ((message.Envelope?.Date.HasValue).HasValue)
					return message.Envelope.Date.Value.ToUniversalTime ().DateTime;
				return null;
			case "TIMEZONE":
				if ((message.Envelope?.Date.HasValue).HasValue)
					return message.Envelope.Date.Value.Offset.Ticks;
				return null;
			case "SUBJECT":
				return message.Envelope != null ? message.Envelope.Subject : null;
			case "FROM":
				return message.Envelope?.From.ToString ();
			case "SENDER":
				return message.Envelope?.Sender.ToString ();
			case "REPLYTO":
				return message.Envelope?.ReplyTo.ToString ();
			case "TO":
				return message.Envelope?.To.ToString ();
			case "CC":
				return message.Envelope?.Cc.ToString ();
			case "BCC":
				return message.Envelope?.Bcc.ToString ();
			case "INREPLYTO":
				return message.Envelope?.InReplyTo;
			case "MESSAGEID":
				return message.Envelope?.MessageId;
			case "REFERENCES":
				return message.References?.ToString ();
			case "BODYSTRUCTURE":
				return message.Body?.ToString ();
			case "PREVIEWTEXT":
				return message.PreviewText;
			case "XGMMSGID":
				if (message.GMailMessageId.HasValue)
					return (long) message.GMailMessageId.Value;
				return null;
			case "XGMTHRID":
				if (message.GMailThreadId.HasValue)
					return (long) message.GMailThreadId.Value;
				return null;
			case "EMAILID":
				return message.EmailId;
			case "THREADID":
				return message.ThreadId;
			default:
				return null;
			}
		}

		void UpdateKeywords (UniqueId uid, HashSet<string> keywords)
		{
			var oldKeywords = new HashSet<string> (StringComparer.OrdinalIgnoreCase);

			LoadKeywords (uid, oldKeywords);

			using (var transaction = sqlite.BeginTransaction ()) {
				try {
					foreach (var keyword in oldKeywords) {
						if (keywords.Contains (keyword))
							continue;

						using (var command = sqlite.CreateCommand ()) {
							command.CommandText = $"DELETE FROM {KeywordsTable.TableName} WHERE UID = @UID AND KEYWORD = @KEYWORD";
							command.Parameters.AddWithValue ("@UID", (long) uid.Id);
							command.Parameters.AddWithValue ("@KEYWORD", keyword);
							command.CommandType = CommandType.Text;

							command.ExecuteNonQuery ();
						}
					}

					foreach (var keyword in keywords) {
						if (oldKeywords.Contains (keyword))
							continue;

						using (var command = sqlite.CreateCommand ()) {
							command.CommandText = $"INSERT INTO {KeywordsTable.TableName} (UID, KEYWORD) VALUES(@UID, @KEYWORD)";
							command.Parameters.AddWithValue ("@UID", (long) uid.Id);
							command.Parameters.AddWithValue ("@KEYWORD", keyword);
							command.CommandType = CommandType.Text;

							command.ExecuteNonQuery ();
						}
					}

					transaction.Commit ();
				} catch {
					transaction.Rollback ();
					throw;
				}
			}
		}

		void UpdateXGMLabels (UniqueId uid, HashSet<string> labels)
		{
			var oldLabels = new HashSet<string> (StringComparer.OrdinalIgnoreCase);

			LoadXGMLabels (uid, oldLabels);

			using (var transaction = sqlite.BeginTransaction ()) {
				try {
					foreach (var label in oldLabels) {
						if (labels.Contains (label))
							continue;

						using (var command = sqlite.CreateCommand ()) {
							command.CommandText = $"DELETE FROM {XGMLabelsTable.TableName} WHERE UID = @UID AND LABEL = @LABEL";
							command.Parameters.AddWithValue ("@UID", (long) uid.Id);
							command.Parameters.AddWithValue ("@LABEL", label);
							command.CommandType = CommandType.Text;

							command.ExecuteNonQuery ();
						}
					}

					foreach (var label in labels) {
						if (oldLabels.Contains (label))
							continue;

						using (var command = sqlite.CreateCommand ()) {
							command.CommandText = $"INSERT INTO {XGMLabelsTable.TableName} (UID, LABEL) VALUES(@UID, @LABEL)";
							command.Parameters.AddWithValue ("@UID", (long) uid.Id);
							command.Parameters.AddWithValue ("@LABEL", label);
							command.CommandType = CommandType.Text;

							command.ExecuteNonQuery ();
						}
					}

					transaction.Commit ();
				} catch {
					transaction.Rollback ();
					throw;
				}
			}
		}

		public void Update (UniqueId uid, IMessageSummary message)
		{
			using (var transaction = sqlite.BeginTransaction ()) {
				try {
					using (var command = sqlite.CreateCommand ()) {
						var columns = GetMessageTableColumns (message.Fields & ~MessageSummaryItems.UniqueId);
						var builder = new StringBuilder ($"INSERT INTO {MessageTable.TableName} (UID, FETCHED");

						for (int i = 0; i < columns.Count; i++) {
							builder.Append (", ");
							builder.Append (columns[i]);
						}

						builder.Append (") VALUES(@UID, @FETCHED");
						command.Parameters.AddWithValue ("@UID", (long) uid.Id);
						command.Parameters.AddWithValue ("@FETCHED", (int) message.Fields);

						for (int i = 0; i < columns.Count; i++) {
							var value = GetValue (uid, message, columns[i]);
							var variable = "@" + columns[i];

							builder.Append (", ");
							builder.Append (variable);
							command.Parameters.AddWithValue (variable, value);
						}

						builder.Append (") ON CONFLICT(UID) DO UPDATE SET FETCHED = FETCHED | @FETCHED");

						for (int i = 0; i < columns.Count; i++)
							builder.AppendFormat (", {0} = @{0}", columns[i]);

						command.CommandText = builder.ToString ();
						command.CommandType = CommandType.Text;

						command.ExecuteNonQuery ();
					}

					if ((message.Fields & MessageSummaryItems.Flags) != 0)
						UpdateKeywords (uid, message.Keywords);

					if ((message.Fields & MessageSummaryItems.GMailLabels) != 0) {
						var labels = new HashSet<string> (message.GMailLabels);

						UpdateXGMLabels (uid, labels);
					}

					transaction.Commit ();
				} catch {
					transaction.Rollback ();
					throw;
				}
			}
		}

		// FIXME: we probably don't need this method
		public void UpdateFlags (UniqueId uid, MessageFlags flags, HashSet<string> keywords)
		{
			using (var transaction = sqlite.BeginTransaction ()) {
				using (var command = sqlite.CreateCommand ()) {
					command.CommandText = $"UPDATE {MessageTable.TableName} SET FLAGS = @FLAGS WHERE UID = @UID";
					command.Parameters.AddWithValue ("@FLAGS", (int) flags);
					command.Parameters.AddWithValue ("@UID", (long) uid.Id);
					command.CommandType = CommandType.Text;

					command.ExecuteNonQuery ();
				}

				UpdateKeywords (uid, keywords);

				transaction.Commit ();
			}
		}

		// FIXME: we probably don't need this method
		public void UpdateModSeq (UniqueId uid, ulong modseq)
		{
			using (var command = sqlite.CreateCommand ()) {
				command.CommandText = $"UPDATE {MessageTable.TableName} SET MODSEQ = @MODSEQ WHERE UID = @UID";
				command.Parameters.AddWithValue ("@MODSEQ", (long) modseq);
				command.Parameters.AddWithValue ("@UID", (long) uid.Id);
				command.CommandType = CommandType.Text;

				command.ExecuteNonQuery ();
			}
		}

		static string GetFileName (UniqueId uid, string section, int offset, int length)
		{
			var fileName = uid.Id.ToString (CultureInfo.InvariantCulture);

			if (!string.IsNullOrEmpty (section))
				fileName += "." + section;

			if (offset != 0)
				fileName += "." + offset.ToString (CultureInfo.InvariantCulture);

			return fileName;
		}

		public Stream CreateStream (UniqueId? uid, string section, int offset, int length)
		{
			var tmpDir = Path.Combine (cacheDir, "tmp");

			if (!Directory.Exists (tmpDir))
				Directory.CreateDirectory (tmpDir);

			string fileName;

			if (uid.HasValue)
				fileName = GetFileName (uid.Value, section, offset, length);
			else
				fileName = Guid.NewGuid ().ToString ();

			do {
				var path = Path.Combine (tmpDir, fileName);

				if (!File.Exists (path)) {
					Stream stream;
					bool isFile;

					try {
						stream = File.Create (path);
						isFile = true;
					} catch {
						stream = new MemoryStream ();
						isFile = false;
					}

					try {
						stream.SetLength (length);

						if (stream is FileStream) {
							lock (streams)
								streams.Add (stream, path);
						}

						return stream;
					} catch {
						stream.Dispose ();

						if (isFile && File.Exists (path))
							File.Delete (path);

						throw;
					}
				}

				fileName = Guid.NewGuid ().ToString ();
			} while (true);
		}

		string GetFilePath (UniqueId uid, string section, int offset, int length)
		{
			var fileName = GetFileName (uid, section, offset, length);
			var hex = uid.Id.ToString ("X8");
			var dir = cacheDir;

			for (int i = 0; i < 8; i += 2)
				dir = Path.Combine (dir, hex.Substring (i, 2));

			return Path.Combine (dir, fileName);
		}

		public Stream CommitStream (Stream stream, UniqueId uid, string section, int offset, int length)
		{
			string tmpFileName;

			lock (streams) {
				if (!streams.TryGetValue (stream, out tmpFileName))
					return stream;

				streams.Remove (stream);
			}

			var path = GetFilePath (uid, section, offset, length);
			var dir = Path.GetDirectoryName (path);

			try {
				if (!Directory.Exists (dir))
					Directory.CreateDirectory (dir);

				if (!File.Exists (path))
					File.Move (tmpFileName, path);

				stream.Dispose ();
				stream = null;

				return File.OpenRead (path);
			} catch {
				if (stream != null) {
					stream.Seek (0, SeekOrigin.Begin);
					return stream;
				}

				throw;
			}
		}

		public bool TryGetStream (UniqueId uid, string section, int offset, int length, out Stream stream)
		{
			var path = GetFilePath (uid, section, offset, length);

			if (File.Exists (path)) {
				try {
					stream = File.OpenRead (path);
					return true;
				} catch {
				}
			}

			stream = null;

			return false;
		}

		void OnUidNextChanged (object sender, EventArgs e)
		{
			if (UidNext == folder.UidNext?.Id)
				return;

			UidNext = folder.UidNext?.Id;
			SaveStatus ();
		}

		void OnUidValidityChanged (object sender, EventArgs e)
		{
			if (UidValidity == folder.UidValidity)
				return;

			Clear ();

			UidValidity = folder.UidValidity;
			SaveStatus ();
		}

		void OnHighestModSeqChanged (object sender, EventArgs e)
		{
			if (HighestModSeq == folder.HighestModSeq)
				return;

			HighestModSeq = folder.HighestModSeq;
			SaveStatus ();
		}

		void OnMessageExpunged (object sender, MessageEventArgs e)
		{
			if (TryGetUniqueId (e.Index, out var uid))
				Expunge (uid);
		}

		void OnMessagesVanished (object sender, MessagesVanishedEventArgs e)
		{
			foreach (var uid in e.UniqueIds)
				Expunge (uid);
		}

		void OnMessageSummaryFetched (object sender, MessageSummaryFetchedEventArgs e)
		{
			UniqueId uid;

			if (e.Message.UniqueId.IsValid)
				uid = e.Message.UniqueId;
			else if (!TryGetUniqueId (e.Message.Index, out uid))
				return;

			Update (uid, e.Message);
		}

		public void Dispose ()
		{
			if (sqlite != null) {
				Disconnect ();

				folder.UidNextChanged -= OnUidNextChanged;
				folder.UidValidityChanged -= OnUidValidityChanged;
				folder.HighestModSeqChanged -= OnHighestModSeqChanged;

				folder.MessageExpunged -= OnMessageExpunged;
				folder.MessagesVanished -= OnMessagesVanished;
				folder.MessageSummaryFetched -= OnMessageSummaryFetched;

				sqlite.Dispose ();
				sqlite = null;
			}
		}
	}
}