1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223using LiteDB;
using MailSharp.MailClient.Models;

namespace MailSharp.MailClient.Services;

public interface IMessageIndexStore
{
	FolderSyncState? GetSyncState(int accountId, string folder);
	void SaveSyncState(int accountId, string folder, uint uidValidity, uint uidNext, int messageCount);

	List<IndexedMessage> GetMessages(int accountId, string folder);
	IndexedMessage? GetMessage(int accountId, string folder, uint uid);
	int GetMessageCount(int accountId, string folder);

	// Full rebuild: replaces every row for this folder in one go (used on UIDVALIDITY change, i.e.
	// the server considers the mailbox rebuilt, or the first time a folder is indexed).
	void ReplaceFolder(int accountId, string folder, List<IndexedMessage> messages);

	// Incremental add (new UIDs since the last check) - doesn't touch existing rows.
	void UpsertMessages(int accountId, string folder, List<IndexedMessage> messages);

	// Reconciliation deletes (messages gone from IMAP - removed or moved elsewhere, by this app or
	// another IMAP client) and folder-deletion cleanup (Settings > Mappen beheren).
	void RemoveMessages(int accountId, string folder, IEnumerable<uint> uids);
	void RemoveFolder(int accountId, string folder);

	// Keeps the index in sync with this app's own actions immediately, rather than waiting for the
	// next EnsureFolderIndexedAsync check to notice.
	void MarkRead(int accountId, string folder, IEnumerable<uint> uids, bool read);
	void MarkFlagged(int accountId, string folder, IEnumerable<uint> uids, bool flagged);

	// Self-heal for a message indexed before ResolveMessageDate's INTERNALDATE fallback existed (see
	// ImapService.GetMessageAsync) - a normal index rebuild only happens on UIDVALIDITY change, so an
	// already-broken date otherwise never gets corrected on its own. No-ops if the uid isn't indexed.
	void UpdateDate(int accountId, string folder, uint uid, DateTimeOffset date);

	long GetTotalSize(int accountId, string folder);
	int GetUnreadCount(int accountId, string folder);

	// Unread count among only the most recent `recentCount` messages (by UID) - what's actually
	// shown to the user (see ImapService.GetFoldersAsync/GetMessageAsync). A raw folder-wide unread
	// total isn't a useful number in practice: it counts a message from ten years ago, or 20,000
	// untouched messages sitting in Trash, exactly the same as something that arrived five minutes
	// ago - numbers nobody's actually trying to act on. Ordered/limited at the query level (not
	// GetMessages().Take()) so this stays cheap even for a folder with tens of thousands of rows.
	int GetRecentUnreadCount(int accountId, string folder, int recentCount);

	// Same scope as GetRecentUnreadCount, but the actual UIDs rather than just the count - purely
	// for diagnostics (see ImapService.GetFoldersAsync), so "the badge says 1" can be answered with
	// "which one" directly from the same query the badge itself uses, instead of cross-referencing a
	// differently-timed background job's own view of things.
	List<uint> GetRecentUnreadUids(int accountId, string folder, int recentCount);

	// Every folder this account currently has any indexed messages/sync state for - used by the
	// Maintenance page's message-count metrics to aggregate across folders without needing the
	// caller to already know the account's folder names.
	List<string> GetIndexedFolders(int accountId);

	// Filters in-memory across every indexed folder for this account - LiteDB isn't a full-text
	// search engine, but at the scale of one mailbox's message count this is fast enough (same
	// approach the old live-IMAP scan used after fetching, just without the IMAP round trip).
	List<IndexedMessage> SearchByAddress(int accountId, string address);
}

// Holds a single long-lived LiteDatabase connection for the app's lifetime (registered as a
// singleton), same pattern as the other *Store classes - a fresh connection per call would race on
// the OS file lock under concurrent requests.
public class LiteDbMessageIndexStore : IMessageIndexStore, IDisposable
{
	private readonly LiteDatabase _db;

	public LiteDbMessageIndexStore(IWebHostEnvironment env)
	{
		var dir = Path.Combine(env.ContentRootPath, "App_Data");
		Directory.CreateDirectory(dir);
		_db = new LiteDatabase(Path.Combine(dir, "messageindex.db"));

		Messages.EnsureIndex(x => x.Key);
		Messages.EnsureIndex(x => x.AccountId);
	}

	private ILiteCollection<IndexedMessage> Messages => _db.GetCollection<IndexedMessage>("messages");
	private ILiteCollection<FolderSyncState> SyncStates => _db.GetCollection<FolderSyncState>("folderSyncStates");

	private static string Key(int accountId, string folder) => $"{accountId}:{folder}";
	private static string MessageId(int accountId, string folder, uint uid) => $"{accountId}:{folder}:{uid}";

	public FolderSyncState? GetSyncState(int accountId, string folder) => SyncStates.FindById(Key(accountId, folder));

	public void SaveSyncState(int accountId, string folder, uint uidValidity, uint uidNext, int messageCount) =>
		SyncStates.Upsert(new FolderSyncState
		{
			Id = Key(accountId, folder),
			UidValidity = uidValidity,
			UidNext = uidNext,
			MessageCount = messageCount
		});

	public List<IndexedMessage> GetMessages(int accountId, string folder) =>
		[.. Messages.Find(x => x.Key == Key(accountId, folder))];

	// O(1) primary-key lookup (unlike GetMessages, which scans/returns every row for the folder) -
	// for callers that only need one message's current state, e.g. checking whether it's still
	// unread before deciding whether a cache-hit read needs an IMAP round trip after all (see
	// ImapService.LoadMimeMessageAsync).
	public IndexedMessage? GetMessage(int accountId, string folder, uint uid) =>
		Messages.FindById(MessageId(accountId, folder, uid));

	public int GetMessageCount(int accountId, string folder) =>
		Messages.Count(x => x.Key == Key(accountId, folder));

	public void ReplaceFolder(int accountId, string folder, List<IndexedMessage> messages)
	{
		var key = Key(accountId, folder);
		Messages.DeleteMany(x => x.Key == key);
		Stamp(accountId, folder, messages);
		if (messages.Count > 0) Messages.InsertBulk(messages);
	}

	public void UpsertMessages(int accountId, string folder, List<IndexedMessage> messages)
	{
		Stamp(accountId, folder, messages);
		foreach (var m in messages) Messages.Upsert(m);
	}

	private static void Stamp(int accountId, string folder, List<IndexedMessage> messages)
	{
		var key = Key(accountId, folder);
		foreach (var m in messages)
		{
			m.Id = MessageId(accountId, folder, m.Uid);
			m.Key = key;
			m.AccountId = accountId;
			m.Folder = folder;
		}
	}

	public void RemoveMessages(int accountId, string folder, IEnumerable<uint> uids)
	{
		foreach (var uid in uids) Messages.Delete(MessageId(accountId, folder, uid));
	}

	public void RemoveFolder(int accountId, string folder)
	{
		var key = Key(accountId, folder);
		Messages.DeleteMany(x => x.Key == key);
		SyncStates.Delete(Key(accountId, folder));
	}

	public void MarkRead(int accountId, string folder, IEnumerable<uint> uids, bool read)
	{
		foreach (var uid in uids)
		{
			var m = Messages.FindById(MessageId(accountId, folder, uid));
			if (m == null || m.IsRead == read) continue;
			m.IsRead = read;
			Messages.Update(m);
		}
	}

	public void UpdateDate(int accountId, string folder, uint uid, DateTimeOffset date)
	{
		var m = Messages.FindById(MessageId(accountId, folder, uid));
		if (m == null || m.Date == date) return;
		m.Date = date;
		Messages.Update(m);
	}

	public void MarkFlagged(int accountId, string folder, IEnumerable<uint> uids, bool flagged)
	{
		foreach (var uid in uids)
		{
			var m = Messages.FindById(MessageId(accountId, folder, uid));
			if (m == null || m.IsFlagged == flagged) continue;
			m.IsFlagged = flagged;
			Messages.Update(m);
		}
	}

	public long GetTotalSize(int accountId, string folder)
	{
		var key = Key(accountId, folder);
		return Messages.Find(x => x.Key == key).Sum(x => x.SizeBytes);
	}

	public int GetUnreadCount(int accountId, string folder) =>
		Messages.Count(x => x.Key == Key(accountId, folder) && !x.IsRead);

	public int GetRecentUnreadCount(int accountId, string folder, int recentCount) =>
		Messages.Query()
			.Where(x => x.Key == Key(accountId, folder))
			.OrderByDescending(x => x.Uid)
			.Limit(recentCount)
			.ToEnumerable()
			.Count(x => !x.IsRead);

	public List<uint> GetRecentUnreadUids(int accountId, string folder, int recentCount) =>
		[.. Messages.Query()
			.Where(x => x.Key == Key(accountId, folder))
			.OrderByDescending(x => x.Uid)
			.Limit(recentCount)
			.ToEnumerable()
			.Where(x => !x.IsRead)
			.Select(x => x.Uid)];

	public List<string> GetIndexedFolders(int accountId) =>
		[.. SyncStates.Find(x => x.Id.StartsWith(accountId + ":"))
			.Select(x => x.Id[(accountId.ToString().Length + 1)..])];

	public List<IndexedMessage> SearchByAddress(int accountId, string address)
	{
		// From/To/Cc are always written as "" rather than null (see FetchIndexedMessagesAsync), but
		// tolerate null here anyway defensively - a document from a different code path or a partial
		// write shouldn't crash every future search.
		return [.. Messages.Find(x => x.AccountId == accountId)
			.Where(m =>
				(m.From ?? "").Contains(address, StringComparison.OrdinalIgnoreCase) ||
				(m.To ?? "").Contains(address, StringComparison.OrdinalIgnoreCase) ||
				(m.Cc ?? "").Contains(address, StringComparison.OrdinalIgnoreCase))];
	}

	public void Dispose() => _db.Dispose();
}