SMPDatabase.java

package org.thoughtcrime.SMP.database;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.telephony.PhoneNumberUtils;
import android.text.TextUtils;
import android.util.Log;
import android.util.Pair;

import org.thoughtcrime.SMP.ApplicationContext;
import org.thoughtcrime.SMP.sms.IncomingSMPMessage;
import org.thoughtcrime.SMP.sms.OutgoingSMPMessage;
import org.thoughtcrime.SMP.database.documents.IdentityKeyMismatch;
import org.thoughtcrime.SMP.database.documents.IdentityKeyMismatchList;
import org.thoughtcrime.SMP.database.model.DisplayRecord;
import org.thoughtcrime.SMP.database.model.SMPMessageRecord;
import org.thoughtcrime.SMP.jobs.TrimThreadJob;
import org.thoughtcrime.SMP.recipients.Recipient;
import org.thoughtcrime.SMP.recipients.RecipientFactory;
import org.thoughtcrime.SMP.recipients.Recipients;
import org.thoughtcrime.SMP.sms.IncomingGroupMessage;
import org.thoughtcrime.SMP.sms.IncomingTextMessage;
import org.thoughtcrime.SMP.util.JsonUtils;
import org.whispersystems.jobqueue.JobManager;
import org.whispersystems.textsecure.api.util.InvalidNumberException;

import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import static org.thoughtcrime.SMP.util.Util.canonicalizeNumber;

/**
 * Created by ludwig on 31/07/15.
 */

public class SMPDatabase extends MessagingDatabase {

	private static final String TAG = SMPDatabase.class.getSimpleName();

	public  static final String TABLE_NAME         = "smp";
	public  static final String PERSON             = "person";
					static final String DATE_RECEIVED      = "date";
					static final String DATE_SENT          = "date_sent";
	public  static final String PROTOCOL           = "protocol";
	public  static final String STATUS             = "status";
	public  static final String TYPE               = "type";
	public  static final String REPLY_PATH_PRESENT = "reply_path_present";
	public  static final String SUBJECT            = "subject";
	public  static final String SERVICE_CENTER     = "service_center";

	public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + ID + " integer PRIMARY KEY, "                +
		THREAD_ID + " INTEGER, " + ADDRESS + " TEXT, " + ADDRESS_DEVICE_ID + " INTEGER DEFAULT 1, " + PERSON + " INTEGER, " +
		DATE_RECEIVED  + " INTEGER, " + DATE_SENT + " INTEGER, " + PROTOCOL + " INTEGER, " + READ + " INTEGER DEFAULT 0, " +
		STATUS + " INTEGER DEFAULT -1," + TYPE + " INTEGER, " + REPLY_PATH_PRESENT + " INTEGER, " +
		RECEIPT_COUNT + " INTEGER DEFAULT 0," + SUBJECT + " TEXT, " + BODY + " TEXT, " +
		MISMATCHED_IDENTITIES + " TEXT DEFAULT NULL, " + SERVICE_CENTER + " TEXT);";

	public static final String[] CREATE_INDEXS = {
		"CREATE INDEX IF NOT EXISTS sms_thread_id_index ON " + TABLE_NAME + " (" + THREAD_ID + ");",
		"CREATE INDEX IF NOT EXISTS sms_read_index ON " + TABLE_NAME + " (" + READ + ");",
		"CREATE INDEX IF NOT EXISTS sms_read_and_thread_id_index ON " + TABLE_NAME + "(" + READ + "," + THREAD_ID + ");",
		"CREATE INDEX IF NOT EXISTS sms_type_index ON " + TABLE_NAME + " (" + TYPE + ");",
		"CREATE INDEX IF NOT EXISTS sms_date_sent_index ON " + TABLE_NAME + " (" + DATE_SENT + ");"
	};

	private static final String[] MESSAGE_PROJECTION = new String[] {
		ID, THREAD_ID, ADDRESS, ADDRESS_DEVICE_ID, PERSON,
		DATE_RECEIVED + " AS " + NORMALIZED_DATE_RECEIVED,
		DATE_SENT + " AS " + NORMALIZED_DATE_SENT,
		PROTOCOL, READ, STATUS, TYPE,
		REPLY_PATH_PRESENT, SUBJECT, BODY, SERVICE_CENTER, RECEIPT_COUNT,
		MISMATCHED_IDENTITIES
	};

	private final JobManager jobManager;

	public SMPDatabase(Context context, SQLiteOpenHelper databaseHelper) {
		super(context, databaseHelper);
		this.jobManager = ApplicationContext.getInstance(context).getJobManager();
	}

	protected String getTableName() {
		return TABLE_NAME;
	}

	private void updateTypeBitmask(long id, long maskOff, long maskOn) {
		Log.w("MessageDatabase", "Updating ID: " + id + " to base type: " + maskOn);

		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.execSQL("UPDATE " + TABLE_NAME +
			" SET " + TYPE + " = (" + TYPE + " & " + (Types.TOTAL_MASK - maskOff) + " | " + maskOn +
			" )" +
			" WHERE " + ID + " = ?", new String[] {id+""});

		long threadId = getThreadIdForMessage(id);

		DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);
		notifyConversationListListeners();
	}

	public long getThreadIdForMessage(long id) {
		String sql        = "SELECT " + THREAD_ID + " FROM " + TABLE_NAME + " WHERE " + ID + " = ?";
		String[] sqlArgs  = new String[] {id+""};
		SQLiteDatabase db = databaseHelper.getReadableDatabase();

		Cursor cursor = null;

		try {
			cursor = db.rawQuery(sql, sqlArgs);
			if (cursor != null && cursor.moveToFirst())
				return cursor.getLong(0);
			else
				return -1;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public int getMessageCount() {
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		Cursor cursor     = null;

		try {
			cursor = db.query(TABLE_NAME, new String[] {"COUNT(*)"}, null, null, null, null, null);

			if (cursor != null && cursor.moveToFirst()) return cursor.getInt(0);
			else                                        return 0;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public int getMessageCountForThread(long threadId) {
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		Cursor cursor     = null;

		try {
			cursor = db.query(TABLE_NAME, new String[] {"COUNT(*)"}, THREAD_ID + " = ?",
				new String[] {threadId+""}, null, null, null);

			if (cursor != null && cursor.moveToFirst())
				return cursor.getInt(0);
		} finally {
			if (cursor != null)
				cursor.close();
		}

		return 0;
	}

	public void markAsEndSession(long id) {
		updateTypeBitmask(id, Types.KEY_EXCHANGE_MASK, Types.END_SESSION_BIT);
	}

	public void markAsPreKeyBundle(long id) {
		updateTypeBitmask(id, Types.KEY_EXCHANGE_MASK, Types.KEY_EXCHANGE_BIT | Types.KEY_EXCHANGE_BUNDLE_BIT);
	}

	public void markAsInvalidVersionKeyExchange(long id) {
		updateTypeBitmask(id, 0, Types.KEY_EXCHANGE_INVALID_VERSION_BIT);
	}

	public void markAsSecure(long id) {
		updateTypeBitmask(id, 0, Types.SECURE_MESSAGE_BIT);
	}

	public void markAsInsecure(long id) {
		updateTypeBitmask(id, Types.SECURE_MESSAGE_BIT, 0);
	}

	public void markAsPush(long id) {
		updateTypeBitmask(id, 0, Types.PUSH_MESSAGE_BIT);
	}

	public void markAsForcedSms(long id) {
		updateTypeBitmask(id, Types.PUSH_MESSAGE_BIT, Types.MESSAGE_FORCE_SMS_BIT);
	}

	public void markAsDecryptFailed(long id) {
		updateTypeBitmask(id, Types.ENCRYPTION_MASK, Types.ENCRYPTION_REMOTE_FAILED_BIT);
	}

	public void markAsDecryptDuplicate(long id) {
		updateTypeBitmask(id, Types.ENCRYPTION_MASK, Types.ENCRYPTION_REMOTE_DUPLICATE_BIT);
	}

	public void markAsNoSession(long id) {
		updateTypeBitmask(id, Types.ENCRYPTION_MASK, Types.ENCRYPTION_REMOTE_NO_SESSION_BIT);
	}

	public void markAsDecrypting(long id) {
		updateTypeBitmask(id, Types.ENCRYPTION_MASK, Types.ENCRYPTION_REMOTE_BIT);
	}

	public void markAsLegacyVersion(long id) {
		updateTypeBitmask(id, Types.ENCRYPTION_MASK, Types.ENCRYPTION_REMOTE_LEGACY_BIT);
	}

	public void markAsOutbox(long id) {
		updateTypeBitmask(id, Types.BASE_TYPE_MASK, Types.BASE_OUTBOX_TYPE);
	}

	public void markAsPendingInsecureSmsFallback(long id) {
		updateTypeBitmask(id, Types.BASE_TYPE_MASK, Types.BASE_PENDING_INSECURE_SMS_FALLBACK);
	}

	public void markAsSending(long id) {
		updateTypeBitmask(id, Types.BASE_TYPE_MASK, Types.BASE_SENDING_TYPE);
	}

	public void markAsSent(long id) {
		updateTypeBitmask(id, Types.BASE_TYPE_MASK, Types.BASE_SENT_TYPE);
	}

	public void markStatus(long id, int status) {
		Log.w("MessageDatabase", "Updating ID: " + id + " to status: " + status);
		ContentValues contentValues = new ContentValues();
		contentValues.put(STATUS, status);

		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.update(TABLE_NAME, contentValues, ID_WHERE, new String[] {id+""});
		notifyConversationListeners(getThreadIdForMessage(id));
	}

	public void markAsSentFailed(long id) {
		updateTypeBitmask(id, Types.BASE_TYPE_MASK, Types.BASE_SENT_FAILED_TYPE);
	}

	public void incrementDeliveryReceiptCount(String address, long timestamp) {
		SQLiteDatabase database = databaseHelper.getWritableDatabase();
		Cursor         cursor   = null;

		try {
			cursor = database.query(TABLE_NAME, new String[] {ID, THREAD_ID, ADDRESS, TYPE},
				DATE_SENT + " = ?", new String[] {String.valueOf(timestamp)},
				null, null, null, null);

			while (cursor.moveToNext()) {
				if (Types.isOutgoingMessageType(cursor.getLong(cursor.getColumnIndexOrThrow(TYPE)))) {
					try {
						String theirAddress = canonicalizeNumber(context, address);
						String ourAddress   = canonicalizeNumber(context, cursor.getString(cursor.getColumnIndexOrThrow(ADDRESS)));

						if (ourAddress.equals(theirAddress)) {
							database.execSQL("UPDATE " + TABLE_NAME +
									" SET " + RECEIPT_COUNT + " = " + RECEIPT_COUNT + " + 1 WHERE " +
									ID + " = ?",
								new String[] {String.valueOf(cursor.getLong(cursor.getColumnIndexOrThrow(ID)))});

							notifyConversationListeners(cursor.getLong(cursor.getColumnIndexOrThrow(THREAD_ID)));
						}
					} catch (InvalidNumberException e) {
						Log.w("SmsDatabase", e);
					}
				}
			}
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public void setMessagesRead(long threadId) {
		SQLiteDatabase database     = databaseHelper.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(READ, 1);

		database.update(TABLE_NAME, contentValues,
			THREAD_ID + " = ? AND " + READ + " = 0",
			new String[] {threadId+""});
	}

	public void setAllMessagesRead() {
		SQLiteDatabase database     = databaseHelper.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(READ, 1);

		database.update(TABLE_NAME, contentValues, null, null);
	}

	protected Pair<Long, Long> updateMessageBodyAndType(long messageId, String body, long maskOff, long maskOn) {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + BODY + " = ?, " +
				TYPE + " = (" + TYPE + " & " + (Types.TOTAL_MASK - maskOff) + " | " + maskOn + ") " +
				"WHERE " + ID + " = ?",
			new String[] {body, messageId + ""});

		long threadId = getThreadIdForMessage(messageId);

		DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);
		notifyConversationListListeners();

		return new Pair<>(messageId, threadId);
	}

	public Pair<Long, Long> copyMessageInbox(long messageId) {
		Reader           reader = readerFor(getMessage(messageId));
		SMPMessageRecord record = reader.getNext();

		ContentValues contentValues = new ContentValues();
		contentValues.put(TYPE, (record.getType() & ~Types.BASE_TYPE_MASK) | Types.BASE_INBOX_TYPE);
		contentValues.put(ADDRESS, record.getIndividualRecipient().getNumber());
		contentValues.put(ADDRESS_DEVICE_ID, record.getRecipientDeviceId());
		contentValues.put(DATE_RECEIVED, System.currentTimeMillis());
		contentValues.put(DATE_SENT, record.getDateSent());
		contentValues.put(PROTOCOL, 31337);
		contentValues.put(READ, 0);
		contentValues.put(BODY, record.getBody().getBody());
		contentValues.put(THREAD_ID, record.getThreadId());

		SQLiteDatabase db           = databaseHelper.getWritableDatabase();
		long           newMessageId = db.insert(TABLE_NAME, null, contentValues);

		DatabaseFactory.getThreadDatabase(context).update(record.getThreadId());
		notifyConversationListeners(record.getThreadId());

		jobManager.add(new TrimThreadJob(context, record.getThreadId()));
		reader.close();

		return new Pair<>(newMessageId, record.getThreadId());
	}

	protected Pair<Long, Long> insertMessageInbox(IncomingTextMessage message, long type) {
		if (message.isPreKeyBundle()) {
			type |= Types.KEY_EXCHANGE_BIT | Types.KEY_EXCHANGE_BUNDLE_BIT;
		} else if (message.isSecureMessage()) {
			type |= Types.SECURE_MESSAGE_BIT;
		} else if (message.isGroup()) {
			type |= Types.SECURE_MESSAGE_BIT;
			if      (((IncomingGroupMessage)message).isUpdate()) type |= Types.GROUP_UPDATE_BIT;
			else if (((IncomingGroupMessage)message).isQuit())   type |= Types.GROUP_QUIT_BIT;
		} else if (message.isEndSession()) {
			type |= Types.SECURE_MESSAGE_BIT;
			type |= Types.END_SESSION_BIT;
		}

		if (message.isPush()) type |= Types.PUSH_MESSAGE_BIT;

		Recipients recipients;

		if (message.getSender() != null) {
			recipients = RecipientFactory.getRecipientsFromString(context, message.getSender(), true);
		} else {
			Log.w(TAG, "Sender is null, returning unknown recipient");
			recipients = RecipientFactory.getRecipientsFor(context, Recipient.getUnknownRecipient(context), false);
		}

		Recipients groupRecipients;

		if (message.getGroupId() == null) {
			groupRecipients = null;
		} else {
			groupRecipients = RecipientFactory.getRecipientsFromString(context, message.getGroupId(), true);
		}

		boolean    unread     = org.thoughtcrime.SMP.util.Util.isDefaultSmsProvider(context) ||
			message.isSecureMessage() || message.isPreKeyBundle();

		long       threadId;

		if (groupRecipients == null) threadId = DatabaseFactory.getThreadDatabase(context).getThreadIdFor(recipients);
		else                         threadId = DatabaseFactory.getThreadDatabase(context).getThreadIdFor(groupRecipients);

		ContentValues values = new ContentValues(6);
		values.put(ADDRESS, message.getSender());
		values.put(ADDRESS_DEVICE_ID,  message.getSenderDeviceId());
		values.put(DATE_RECEIVED, System.currentTimeMillis());
		values.put(DATE_SENT, message.getSentTimestampMillis());
		values.put(PROTOCOL, message.getProtocol());
		values.put(READ, unread ? 0 : 1);

		if (!TextUtils.isEmpty(message.getPseudoSubject()))
			values.put(SUBJECT, message.getPseudoSubject());

		values.put(REPLY_PATH_PRESENT, message.isReplyPathPresent());
		values.put(SERVICE_CENTER, message.getServiceCenterAddress());
		values.put(BODY, message.getMessageBody());
		values.put(TYPE, type);
		values.put(THREAD_ID, threadId);

		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		long messageId    = db.insert(TABLE_NAME, null, values);

		if (unread) {
			DatabaseFactory.getThreadDatabase(context).setUnread(threadId);
		}

		DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);
		jobManager.add(new TrimThreadJob(context, threadId));

		return new Pair<>(messageId, threadId);
	}

	//TODO: adapt for SMP
	protected Pair<Long, Long> insertMessageInbox(IncomingSMPMessage message, long type) {
    /*
    if (message.isPreKeyBundle()) {
      type |= Types.KEY_EXCHANGE_BIT | Types.KEY_EXCHANGE_BUNDLE_BIT;
      Log.d(TAG, "insertMessageInbox.isPreKeyBundle  type: " + type);
    } else if (message.isSecureMessage()) {
      type |= Types.SECURE_MESSAGE_BIT;
      Log.d(TAG, "insertMessageInbox.isSecureMessage type: " + type);
    } else if (message.isGroup()) {
      type |= Types.SECURE_MESSAGE_BIT;
      if      (((IncomingGroupSMPMessage)message).isUpdate()) type |= Types.GROUP_UPDATE_BIT;
      else if (((IncomingGroupSMPMessage)message).isQuit())   type |= Types.GROUP_QUIT_BIT;
    } else if (message.isEndSession()) {
      type |= Types.SECURE_MESSAGE_BIT;
      type |= Types.END_SESSION_BIT;
      Log.d(TAG, "insertMessageInbox.isEndSession type: " + type);
    } else if (message.isSMPMessage()) {
      type |= Types.SMP_MESSAGE_BIT;
      Log.d(TAG, "insertMessageInbox.isSMPMessage type: " + type);
    } else if (message.isSMPSyncMessage()) {
      type |= Types.SMP_MESSAGE_BIT;
      type |= Types.SMP_SYN_MESSAGE_BIT;
      Log.d(TAG, "insertMessageInbox.isSMPSyncMessage type: " + type);
    }
    */

		if (message.isSMPMessage()) {
			type |= Types.SECURE_MESSAGE_BIT;
			type |= Types.SMP_MESSAGE_BIT;
			Log.d(TAG, "insertMessageInbox.isSMPMessage type: " + type);
		}

		if (message.isSMPSyncMessage()) {
			type |= Types.SMP_SYN_MESSAGE_BIT;
			Log.d(TAG, "insertMessageInbox.isSMPSyncMessage type: " + type);
		}

		Log.d(TAG, "insertMessageInbox.isPreKeyBundle(): " + message.isPreKeyBundle());
		Log.d(TAG, "insertMessageInbox.isSecureMessage(): " + message.isSecureMessage());
		Log.d(TAG, "insertMessageInbox.isGroup(): " + message.isGroup());
		Log.d(TAG, "insertMessageInbox.isEndSession(): " + message.isEndSession());
		Log.d(TAG, "insertMessageInbox.isSMPMessage(): " + message.isSMPMessage());
		Log.d(TAG, "insertMessageInbox.isSMPSyncMessage(): " + message.isSMPSyncMessage());

		//Log.d(TAG, "insertMessageInbox.isPush(): " + message.isPush());
		if (message.isPush()) type |= Types.PUSH_MESSAGE_BIT;

		Log.d(TAG, "insertMessageInbox type: " + type);

		Recipients recipients;

		if (message.getSender() != null) {
			recipients = RecipientFactory.getRecipientsFromString(context, message.getSender(), true);
		} else {
			Log.w(TAG, "Sender is null, returning unknown recipient");
			recipients = RecipientFactory.getRecipientsFor(context, Recipient.getUnknownRecipient(context), false);
		}

		Recipients groupRecipients;

		if (message.getGroupId() == null) {
			groupRecipients = null;
		} else {
			groupRecipients = RecipientFactory.getRecipientsFromString(context, message.getGroupId(), true);
		}

		boolean    unread     = org.thoughtcrime.SMP.util.Util.isDefaultSmsProvider(context) ||
			message.isSecureMessage() || message.isPreKeyBundle();

		long       threadId;

		if (groupRecipients == null) threadId = DatabaseFactory.getThreadDatabase(context).getThreadIdFor(recipients);
		else                         threadId = DatabaseFactory.getThreadDatabase(context).getThreadIdFor(groupRecipients);

		ContentValues values = new ContentValues(6);
		values.put(ADDRESS, message.getSender());
		values.put(ADDRESS_DEVICE_ID,  message.getSenderDeviceId());
		values.put(DATE_RECEIVED, System.currentTimeMillis());
		values.put(DATE_SENT, message.getSentTimestampMillis());
		values.put(PROTOCOL, message.getProtocol());
		values.put(READ, unread ? 0 : 1);

		if (!TextUtils.isEmpty(message.getPseudoSubject()))
			values.put(SUBJECT, message.getPseudoSubject());

		values.put(REPLY_PATH_PRESENT, message.isReplyPathPresent());
		values.put(SERVICE_CENTER, message.getServiceCenterAddress());
		values.put(BODY, message.getMessageBody());
		values.put(TYPE, type);
		values.put(THREAD_ID, threadId);

		Log.d(TAG, "insertMessageInbox values: " + values.toString());

		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		long messageId    = db.insert(TABLE_NAME, null, values);
		Log.d(TAG, "insertMessageInbox messageId: " + messageId);

		SharedPreferences prefs = context.getSharedPreferences("org.thoughtcrime.SMP.SMP", Context
			.MODE_PRIVATE);
		prefs.edit().putInt("messageId", (int) messageId).apply();
		Log.d(TAG, "insertMessageInbox after SharedPreferences");

		if (unread) {
			DatabaseFactory.getThreadDatabase(context).setUnread(threadId);
		}

		DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);

		if (!message.isSMPSyncMessage()) {
			Log.d(TAG, "insertMessageInbox.notifyConversationsListeners -> SMP");
			notifySMPConversationListeners(threadId, false);
		} else {
			Log.d(TAG, "insertMessageInbox.notifyConversationsListeners -> SMPSync");
			notifySMPConversationListeners(threadId, true);
		}

		jobManager.add(new TrimThreadJob(context, threadId));

		return new Pair<>(messageId, threadId);
	}

	protected long insertMessageOutbox(long threadId, OutgoingSMPMessage message,
	                                   long type, long date)
	{
		if      (message.isSMPSyncMessage())   type |= Types.SMP_SYN_MESSAGE_BIT;
		else if (message.isSecureMessage()) type |= Types.SECURE_MESSAGE_BIT;
		else if (message.isEndSession())    type |= Types.END_SESSION_BIT;
		else if (message.isSMPMessage())    type |= Types.SMP_MESSAGE_BIT;

		ContentValues contentValues = new ContentValues(6);
		contentValues.put(ADDRESS, PhoneNumberUtils.formatNumber(message.getRecipients().getPrimaryRecipient().getNumber()));
		contentValues.put(THREAD_ID, threadId);
		contentValues.put(BODY, message.getMessageBody());
		contentValues.put(DATE_RECEIVED, date);
		contentValues.put(DATE_SENT, date);
		contentValues.put(READ, 1);
		contentValues.put(TYPE, type);

		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		long messageId    = db.insert(TABLE_NAME, ADDRESS, contentValues);

		DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);
		jobManager.add(new TrimThreadJob(context, threadId));

		return messageId;
	}

	Cursor getMessages(int skip, int limit) {
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		return db.query(TABLE_NAME, MESSAGE_PROJECTION, null, null, null, null, ID, skip + "," + limit);
	}

	Cursor getOutgoingMessages() {
		String outgoingSelection = TYPE + " & "  + Types.BASE_TYPE_MASK + " = " + Types.BASE_OUTBOX_TYPE;
		SQLiteDatabase db        = databaseHelper.getReadableDatabase();
		return db.query(TABLE_NAME, MESSAGE_PROJECTION, outgoingSelection, null, null, null, null);
	}

	public Cursor getDecryptInProgressMessages() {
		String where       = TYPE + " & " + (Types.ENCRYPTION_REMOTE_BIT | Types.ENCRYPTION_ASYMMETRIC_BIT) + " != 0";
		SQLiteDatabase db  = databaseHelper.getReadableDatabase();
		return db.query(TABLE_NAME, MESSAGE_PROJECTION, where, null, null, null, null);
	}

	public Cursor getEncryptedRogueMessages(Recipient recipient) {
		String selection  = TYPE + " & " + Types.ENCRYPTION_REMOTE_NO_SESSION_BIT + " != 0" +
			" AND PHONE_NUMBERS_EQUAL(" + ADDRESS + ", ?)";
		String[] args     = {recipient.getNumber()};
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		return db.query(TABLE_NAME, MESSAGE_PROJECTION, selection, args, null, null, null);
	}

	public Cursor getMessage(long messageId) {
		SQLiteDatabase db     = databaseHelper.getReadableDatabase();
		Cursor         cursor = db.query(TABLE_NAME, MESSAGE_PROJECTION, ID_WHERE, new String[]{messageId + ""},
			null, null, null);
		setNotifyConverationListeners(cursor, getThreadIdForMessage(messageId));
		return cursor;
	}

	public boolean deleteMessage(long messageId) {
		Log.w("MessageDatabase", "Deleting: " + messageId);
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		long threadId     = getThreadIdForMessage(messageId);
		db.delete(TABLE_NAME, ID_WHERE, new String[] {messageId+""});
		boolean threadDeleted = DatabaseFactory.getThreadDatabase(context).update(threadId);
		notifyConversationListeners(threadId);
		return threadDeleted;
	}

	/*package */void deleteThread(long threadId) {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.delete(TABLE_NAME, THREAD_ID + " = ?", new String[] {threadId+""});
	}

	/*package*/void deleteMessagesInThreadBeforeDate(long threadId, long date) {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		String where      = THREAD_ID + " = ? AND (CASE " + TYPE;

		for (long outgoingType : Types.OUTGOING_MESSAGE_TYPES) {
			where += " WHEN " + outgoingType + " THEN " + DATE_SENT + " < " + date;
		}

		where += (" ELSE " + DATE_RECEIVED + " < " + date + " END)");

		db.delete(TABLE_NAME, where, new String[] {threadId + ""});
	}

	/*package*/ void deleteThreads(Set<Long> threadIds) {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		String where      = "";

		for (long threadId : threadIds) {
			where += THREAD_ID + " = '" + threadId + "' OR ";
		}

		where = where.substring(0, where.length() - 4);

		db.delete(TABLE_NAME, where, null);
	}

	/*package */ void deleteAllThreads() {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.delete(TABLE_NAME, null, null);
	}

	/*package*/ SQLiteDatabase beginTransaction() {
		SQLiteDatabase database = databaseHelper.getWritableDatabase();
		database.beginTransaction();
		return database;
	}

	/*package*/ void endTransaction(SQLiteDatabase database) {
		database.setTransactionSuccessful();
		database.endTransaction();
	}

	/*package*/ SQLiteStatement createInsertStatement(SQLiteDatabase database) {
		return database.compileStatement("INSERT INTO " + TABLE_NAME + " (" + ADDRESS + ", " +
			PERSON + ", " +
			DATE_SENT + ", " +
			DATE_RECEIVED  + ", " +
			PROTOCOL + ", " +
			READ + ", " +
			STATUS + ", " +
			TYPE + ", " +
			REPLY_PATH_PRESENT + ", " +
			SUBJECT + ", " +
			BODY + ", " +
			SERVICE_CENTER +
			", " + THREAD_ID + ") " +
			" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	}

	public static class Status {
		public static final int STATUS_NONE     = -1;
		public static final int STATUS_COMPLETE  = 0;
		public static final int STATUS_PENDING   = 0x20;
		public static final int STATUS_FAILED    = 0x40;
	}

	public Reader readerFor(Cursor cursor) {
		return new Reader(cursor);
	}

	public class Reader {

		private final Cursor cursor;

		public Reader(Cursor cursor) {
			this.cursor = cursor;
		}

		public SMPMessageRecord getNext() {
			if (cursor == null || !cursor.moveToNext())
				return null;

			return getCurrent();
		}

		public int getCount() {
			if (cursor == null) return 0;
			else                return cursor.getCount();
		}

		public SMPMessageRecord getCurrent() {
			long messageId          = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.ID));
			String address          = cursor.getString(cursor.getColumnIndexOrThrow(SmsDatabase.ADDRESS));
			int addressDeviceId     = cursor.getInt(cursor.getColumnIndexOrThrow(SmsDatabase.ADDRESS_DEVICE_ID));
			long type               = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.TYPE));
			long dateReceived       = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.NORMALIZED_DATE_RECEIVED));
			long dateSent           = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.NORMALIZED_DATE_SENT));
			long threadId           = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.THREAD_ID));
			int status              = cursor.getInt(cursor.getColumnIndexOrThrow(SmsDatabase.STATUS));
			int receiptCount        = cursor.getInt(cursor.getColumnIndexOrThrow(SmsDatabase.RECEIPT_COUNT));
			String mismatchDocument = cursor.getString(cursor.getColumnIndexOrThrow(SmsDatabase.MISMATCHED_IDENTITIES));

			List<IdentityKeyMismatch> mismatches = getMismatches(mismatchDocument);
			Recipients                recipients = getRecipientsFor(address);
			DisplayRecord.Body        body       = getBody(cursor);

			return new SMPMessageRecord(context, messageId, body, recipients,
				recipients.getPrimaryRecipient(),
				addressDeviceId,
				dateSent, dateReceived, receiptCount, type,
				threadId, status, mismatches);
		}

		private Recipients getRecipientsFor(String address) {
			if (address != null) {
				Recipients recipients = RecipientFactory.getRecipientsFromString(context, address, false);

				if (recipients == null || recipients.isEmpty()) {
					return RecipientFactory.getRecipientsFor(context, Recipient.getUnknownRecipient(context), false);
				}

				return recipients;
			} else {
				Log.w(TAG, "getRecipientsFor() address is null");
				return RecipientFactory.getRecipientsFor(context, Recipient.getUnknownRecipient(context), false);
			}
		}

		private List<IdentityKeyMismatch> getMismatches(String document) {
			try {
				if (!TextUtils.isEmpty(document)) {
					return JsonUtils.fromJson(document, IdentityKeyMismatchList.class).getList();
				}
			} catch (IOException e) {
				Log.w(TAG, e);
			}

			return new LinkedList<>();
		}

		protected DisplayRecord.Body getBody(Cursor cursor) {
			long type   = cursor.getLong(cursor.getColumnIndexOrThrow(SmsDatabase.TYPE));
			String body = cursor.getString(cursor.getColumnIndexOrThrow(SmsDatabase.BODY));

			if (Types.isSymmetricEncryption(type)) {
				return new DisplayRecord.Body(body, false);
			} else {
				return new DisplayRecord.Body(body, true);
			}
		}

		public void close() {
			cursor.close();
		}
	}
}