import GRDB

public final class MailStore: Sendable {
	let dbWriter: any DatabaseWriter

	public init(dbWriter: any DatabaseWriter) {
		self.dbWriter = dbWriter
	}

	// MARK: - Accounts

	public func insertAccount(_ account: AccountRecord) throws {
		try dbWriter.write { db in
			try account.insert(db)
		}
	}

	public func accounts() throws -> [AccountRecord] {
		try dbWriter.read { db in
			try AccountRecord.fetchAll(db)
		}
	}

	// MARK: - Mailboxes

	public func upsertMailbox(_ mailbox: MailboxRecord) throws {
		try dbWriter.write { db in
			try mailbox.save(db)
		}
	}

	public func mailboxes(accountId: String) throws -> [MailboxRecord] {
		try dbWriter.read { db in
			try MailboxRecord
				.filter(Column("accountId") == accountId)
				.order(Column("name"))
				.fetchAll(db)
		}
	}

	public func mailbox(id: String) throws -> MailboxRecord? {
		try dbWriter.read { db in
			try MailboxRecord.fetchOne(db, key: id)
		}
	}

	public func updateMailboxSync(id: String, uidValidity: Int, uidNext: Int) throws {
		try dbWriter.write { db in
			try db.execute(
				sql: "UPDATE mailbox SET uidValidity = ?, uidNext = ? WHERE id = ?",
				arguments: [uidValidity, uidNext, id]
			)
		}
	}

	// MARK: - Messages

	public func insertMessages(_ messages: [MessageRecord]) throws {
		try dbWriter.write { db in
			for message in messages {
				try message.save(db)
			}
		}
	}

	public func messages(mailboxId: String) throws -> [MessageRecord] {
		try dbWriter.read { db in
			try MessageRecord
				.filter(Column("mailboxId") == mailboxId)
				.order(Column("date").desc)
				.fetchAll(db)
		}
	}

	public func message(id: String) throws -> MessageRecord? {
		try dbWriter.read { db in
			try MessageRecord.fetchOne(db, key: id)
		}
	}

	public func messagesForThread(threadId: String) throws -> [MessageRecord] {
		try dbWriter.read { db in
			try MessageRecord.fetchAll(db, sql: """
				SELECT m.* FROM message m
				JOIN threadMessage tm ON tm.messageId = m.id
				WHERE tm.threadId = ?
				ORDER BY m.date ASC
				""", arguments: [threadId])
		}
	}

	public func updateFlags(messageId: String, isRead: Bool, isFlagged: Bool) throws {
		try dbWriter.write { db in
			try db.execute(
				sql: "UPDATE message SET isRead = ?, isFlagged = ? WHERE id = ?",
				arguments: [isRead, isFlagged, messageId]
			)
		}
	}

	public func storeBody(messageId: String, text: String?, html: String?) throws {
		try dbWriter.write { db in
			try db.execute(
				sql: "UPDATE message SET bodyText = ?, bodyHtml = ? WHERE id = ?",
				arguments: [text, html, messageId]
			)
		}
	}

	// MARK: - Threads

	public func threads(accountId: String) throws -> [ThreadRecord] {
		try dbWriter.read { db in
			try ThreadRecord
				.filter(Column("accountId") == accountId)
				.order(Column("lastDate").desc)
				.fetchAll(db)
		}
	}

	public func insertThread(_ thread: ThreadRecord) throws {
		try dbWriter.write { db in
			try thread.save(db)
		}
	}

	public func linkMessageToThread(threadId: String, messageId: String) throws {
		try dbWriter.write { db in
			try ThreadMessageRecord(threadId: threadId, messageId: messageId).save(db)
		}
	}

	public func updateThread(id: String, lastDate: String, messageCount: Int, subject: String?) throws {
		try dbWriter.write { db in
			try db.execute(
				sql: "UPDATE thread SET lastDate = ?, messageCount = ?, subject = COALESCE(?, subject) WHERE id = ?",
				arguments: [lastDate, messageCount, subject, id]
			)
		}
	}

	/// Returns all message IDs linked to a thread
	public func threadMessageIds(threadId: String) throws -> [String] {
		try dbWriter.read { db in
			try String.fetchAll(
				db,
				sql: "SELECT messageId FROM threadMessage WHERE threadId = ?",
				arguments: [threadId]
			)
		}
	}

	/// Finds thread IDs that contain any of the given message IDs (by RFC 5322 Message-ID)
	public func findThreadsByMessageIds(_ messageIds: Set<String>) throws -> [String] {
		guard !messageIds.isEmpty else { return [] }
		return try dbWriter.read { db in
			let placeholders = databaseQuestionMarks(count: messageIds.count)
			let sql = """
				SELECT DISTINCT tm.threadId
				FROM threadMessage tm
				JOIN message m ON m.id = tm.messageId
				WHERE m.messageId IN (\(placeholders))
				"""
			return try String.fetchAll(db, sql: sql, arguments: StatementArguments(Array(messageIds)))
		}
	}

	/// Merges multiple threads into one, keeping the first thread ID
	public func mergeThreads(_ threadIds: [String]) throws {
		guard threadIds.count > 1 else { return }
		let keepId = threadIds[0]
		let mergeIds = Array(threadIds.dropFirst())
		try dbWriter.write { db in
			for mergeId in mergeIds {
				try db.execute(
					sql: "UPDATE threadMessage SET threadId = ? WHERE threadId = ?",
					arguments: [keepId, mergeId]
				)
				try db.execute(
					sql: "DELETE FROM thread WHERE id = ?",
					arguments: [mergeId]
				)
			}
		}
	}

	/// Access the underlying database writer (for ValueObservation)
	public var databaseReader: any DatabaseReader {
		dbWriter
	}
}
