path: root/src/main/java/de/thedevstack/conversationsplus/persistance/db/access/MessageDatabaseAccess.java

package de.thedevstack.conversationsplus.persistance.db.access;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Pair;

import de.thedevstack.android.logcat.Logging;
import de.thedevstack.conversationsplus.entities.Account;
import de.thedevstack.conversationsplus.entities.FileParams;
import de.thedevstack.conversationsplus.entities.Message;
import de.thedevstack.conversationsplus.enums.FileStatus;
import de.thedevstack.conversationsplus.enums.MessageDirection;
import de.thedevstack.conversationsplus.enums.MessageStatus;
import de.thedevstack.conversationsplus.persistance.db.migrations.FileParamsBodyToDatabaseFieldsMigration;
import de.thedevstack.conversationsplus.persistance.db.migrations.MessageStatusMigration;
import de.thedevstack.conversationsplus.utils.CryptoHelper;

/**
 *
 */
public class MessageDatabaseAccess extends AbstractDatabaseAccess {

    private static final String TABLE_NAME_MESSAGES = "messages";
    // since cplus db version 1
    public static final String TABLE_NAME_ADDITIONAL_PARAMETERS = "message_parameters";
    private static final String COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD = "httpupload";
    private static final String COLUMN_NAME_MSG_PARAMS_MSGUUID = "message_uuid";
    private static final String COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION = "treatasdownloadable_decision";
    // since cplus db version 3
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_SIZE = "file_size";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_TYPE = "file_type";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_WIDTH = "file_width";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT = "file_height";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_STATUS = "file_status";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_NAME = "file_name";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_PATH = "file_path";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_URL = "file_url";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_IV = "file_iv";
    private static final String COLUMN_NAME_MSG_PARAMS_FILE_KEY = "file_key";
    private static final String COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME = "original_file_name";
    // since cplus db version 4
    private static final String COLUMN_NAME_MSG_STATUS = "status";
    private static final String COLUMN_NAME_MSG_DIRECTION = "direction";

    private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V1 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " ("
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', "
            + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)";

    private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V3 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " ("
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_STATUS + " TEXT DEFAULT 'UNDEFINED', "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_NAME + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_PATH + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY + " TEXT DEFAULT NULL, "
            + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)";

    private static final String TABLE_ADDITIONAL_PARAMETERS_CREATE_V4 = "CREATE TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " ("
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " TEXT, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION + " TEXT DEFAULT 'NOT_DECIDED', "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH + " INTEGER DEFAULT 0, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_STATUS + " TEXT DEFAULT 'UNDEFINED', "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_NAME + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_PATH + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY + " TEXT DEFAULT NULL, "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS + " TEXT DEFAULT 'WAITING', "
            + MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + " TEXT DEFAULT 'OUT', "
            + "FOREIGN KEY(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") REFERENCES " + Message.TABLENAME + "(" + Message.UUID + ") ON DELETE CASCADE)";

    public static void updateMessageParameters(SQLiteDatabase db, Message message, String uuid) {
        String[] args = {uuid};
        db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, MessageDatabaseAccess.getAdditionalParametersContentValues(message), MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", args);
    }

    public static ContentValues getAdditionalParametersContentValues(Message message) {
        ContentValues additionalParameters = new ContentValues();
        additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID, message.getUuid());
        additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD, message.isHttpUploaded() ? 1 : 0);
        additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION, message.treatAsDownloadable().name());
        additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, message.getDirection().name());
        additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, message.getMessageStatus().name());
        if (null != message.getFileParams()) {
            FileParams fileParams = message.getFileParams();
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE, fileParams.getMimeType());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE, fileParams.getSize());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT, fileParams.getHeight());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH, fileParams.getWidth());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_NAME, fileParams.getName());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME, fileParams.getOriginalFilename());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_PATH, fileParams.getPath());
            additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL, fileParams.getUrl());
            if (null != fileParams.getIv()) {
                additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV, CryptoHelper.bytesToHex(fileParams.getIv()));
            }
            if (null != fileParams.getKey()) {
                additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY, CryptoHelper.bytesToHex(fileParams.getKey()));
            }
            if (null != fileParams.getFileStatus()) {
                additionalParameters.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_STATUS, fileParams.getFileStatus().name());
            }
        }

        return additionalParameters;
    }

    private static void populateMessageParametersFromCursor(Cursor cursor, Message message) {
        boolean isHttpUploaded = CursorHelper.getInt(cursor, COLUMN_NAME_MSG_PARAMS_HTTPUPLOAD) == 1;
        message.setHttpUploaded(isHttpUploaded);
        String downloadable = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_TREATASDOWNLOADABLE_DECISION);
        Message.Decision treatAsDownloadable = Message.Decision.NOT_DECIDED;
        try {
            treatAsDownloadable = Message.Decision.valueOf(downloadable);
        } catch (IllegalArgumentException e) {
            // Should only happen if the database is corrupted, but to be on the save side catch it here
            Logging.e("db.msg", "Unknown Decision for treatAsDownloadable found: '" + downloadable + "'");
        }

        message.setTreatAsDownloadable(treatAsDownloadable);

        String msgStatus = CursorHelper.getString(cursor, COLUMN_NAME_MSG_STATUS);
        MessageStatus messageStatus = MessageStatus.WAITING;
        try {
            messageStatus = MessageStatus.valueOf(msgStatus);
        } catch (IllegalArgumentException e) {
            // Should only happen if the database is corrupted, but to be on the save side catch it here
            Logging.e("db.msg", "Unknown MessageStatus found: '" + msgStatus + "'");
        }

        message.setMessageStatus(messageStatus);

        String msgDirection = CursorHelper.getString(cursor, COLUMN_NAME_MSG_DIRECTION);
        MessageDirection messageDirection = MessageDirection.OUT;
        try {
            messageDirection = MessageDirection.valueOf(msgDirection);
        } catch (IllegalArgumentException e) {
            // Should only happen if the database is corrupted, but to be on the save side catch it here
            Logging.e("db.msg", "Unknown MessageDirection found: '" + msgDirection + "'");
        }

        message.setDirection(messageDirection);

        String status = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_STATUS);
        FileStatus fileStatus = FileStatus.UNDEFINED;
        if (null != status && !status.isEmpty()) {
            try {
                fileStatus = FileStatus.valueOf(status);
            } catch (IllegalArgumentException e) {
                // Should only happen if the database is corrupted, but to be on the save side catch it here
                Logging.e("db.msg", "Unknown FileStatus for fileParams.fileStatus found: '" + status + "'");
            }
        }
        String url = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_URL);
        if (message.hasFileAttached() || null != url) {
            FileParams fileParams = new FileParams(message.getBody());
            String fileType = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_TYPE);
            fileParams.setMimeType(fileType);
            String name = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_NAME);
            fileParams.setName(name);
            String originalFilename = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME);
            fileParams.setOriginalFilename(originalFilename);
            String path = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_PATH);
            fileParams.setPath(path);
            fileParams.setUrl(url);
            String iv = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_IV);
            if (null != iv && !iv.isEmpty()) {
                fileParams.setIv(CryptoHelper.hexToBytes(iv));
            }
            String key = CursorHelper.getString(cursor, COLUMN_NAME_MSG_PARAMS_FILE_KEY);
            if (null != key && !key.isEmpty()) {
                fileParams.setKey(CryptoHelper.hexToBytes(key));
            }
            long fileSize = CursorHelper.getLong(cursor, COLUMN_NAME_MSG_PARAMS_FILE_SIZE);
            fileParams.setSize(fileSize);
            int imageHeight = CursorHelper.getInt(cursor, COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT);
            fileParams.setHeight(imageHeight);
            int imageWidth = CursorHelper.getInt(cursor, COLUMN_NAME_MSG_PARAMS_FILE_WIDTH);
            fileParams.setWidth(imageWidth);
            if (null != status && !status.isEmpty()) {
                fileParams.setFileStatus(fileStatus);
            }
            message.setFileParams(fileParams);
        }
    }

    public static void populateMessageParameters(SQLiteDatabase db, Message message) {
        Cursor paramsCursor = db.query(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS,
                null, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?",
                new String[] {message.getUuid()},
                null, null, null, null);
        paramsCursor.moveToNext();
        MessageDatabaseAccess.populateMessageParametersFromCursor(paramsCursor, message);
        paramsCursor.close();
    }

    public static void upgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 1 && newVersion >= 1) {
            Logging.d("db.upgrade.cplus", "Creating additional parameters table for messages.");
            db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE_V1);
            db.execSQL("INSERT INTO " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + "(" + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + ") "
                    + " SELECT " + Message.UUID + " FROM " + Message.TABLENAME);
        }
        if (oldVersion < 3 && newVersion >= 3) {
            Logging.d("db.upgrade.cplus", "Upgrade " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS);
            String[] columnDefinitions = {
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_TYPE + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE + " INTEGER DEFAULT 0",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT + " INTEGER DEFAULT 0",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH + " INTEGER DEFAULT 0",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_STATUS + " TEXT DEFAULT 'UNDEFINED'",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_NAME + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_ORIGINAL_FILE_NAME + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_PATH + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV + " TEXT DEFAULT NULL",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY + " TEXT DEFAULT NULL",
            };

            addNewColumns(db, MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, columnDefinitions);

            Logging.d("db.upgrade.cplus", "Migrate file params from message body to database fields");
            Cursor cursor = db.rawQuery("SELECT " + Message.UUID + ", " + Message.BODY + " FROM " + Message.TABLENAME + " WHERE " + Message.TYPE + "=? OR " + Message.TYPE + "=?", new String[] {String.valueOf(Message.TYPE_FILE), String.valueOf(Message.TYPE_IMAGE)});
            while (cursor.moveToNext()) {
                String uuid = CursorHelper.getString(cursor, Message.UUID);
                String body = CursorHelper.getString(cursor, Message.BODY);
                FileParams fileParams = FileParamsBodyToDatabaseFieldsMigration.getFileParams(body);
                String newBody = fileParams.getUrl();
                ContentValues values = new ContentValues();
                values.put(Message.BODY, newBody);
                db.update(Message.TABLENAME, values, Message.UUID + "=?", new String[] {uuid});

                ContentValues parameterValues = new ContentValues();
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_SIZE, fileParams.getSize());
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_HEIGHT, fileParams.getHeight());
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_WIDTH, fileParams.getWidth());
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_URL, fileParams.getUrl());
                if (null != fileParams.getIv()) {
                    parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_IV, CryptoHelper.bytesToHex(fileParams.getIv()));
                }
                if (null != fileParams.getKey()) {
                    parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_FILE_KEY, CryptoHelper.bytesToHex(fileParams.getKey()));
                }

                db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, parameterValues, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", new String[] {uuid});
            }
            cursor.close();
        }
        if (oldVersion < 4 && newVersion >= 4) {
            Logging.d("db.upgrade.cplus", "Upgrade " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + ": new fields for status and direction");
            String[] columnDefinitions = {
                    MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS + " TEXT DEFAULT 'WAITING'",
                    MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + " TEXT DEFAULT 'OUT'"
            };

            addNewColumns(db, MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, columnDefinitions);

            Logging.d("db.upgrade.cplus", "Migrate integer status from " + Message.TABLENAME + "." + Message.STATUS + " to dedicated status and direction field in " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS);
            Cursor cursor = db.rawQuery("SELECT " + Message.UUID + ", " + Message.STATUS + " FROM " + Message.TABLENAME, null);
            while (cursor.moveToNext()) {
                String uuid = CursorHelper.getString(cursor, Message.UUID);
                int status = CursorHelper.getInt(cursor, Message.STATUS);

                MessageDirection direction = MessageStatusMigration.getMessageDirection(status);
                MessageStatus newStatus = MessageStatusMigration.getMessageStatus(status);

                ContentValues parameterValues = new ContentValues();
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, newStatus.name());
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, direction.name());

                db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, parameterValues, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", new String[] {uuid});
            }
            cursor.close();
        }
        if (oldVersion < 5 && newVersion >= 5) { // ONLY NEEDED FOR DEV
            Logging.d("db.upgrade.cplus", "Fixing hick up in first try");
            String alterStatement = "ALTER TABLE " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " RENAME TO old_msg_params";
            db.execSQL(alterStatement);

            db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE_V4);

            db.execSQL("INSERT INTO " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " SELECT message_uuid, httpupload, treatasdownloadable_decision," +
                    "file_type, file_size, file_height, file_width, file_status, file_name, original_file_name, file_path, file_url, file_iv, file_key, status, 'IN' FROM old_msg_params");

            db.execSQL("DROP TABLE old_msg_params");

            Logging.d("db.upgrade.cplus", "Migrate integer status from " + Message.TABLENAME + "." + Message.STATUS + " to dedicated status and direction field in " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS);
            Cursor cursor = db.rawQuery("SELECT " + Message.UUID + ", " + Message.STATUS + " FROM " + Message.TABLENAME, null);
            while (cursor.moveToNext()) {
                String uuid = CursorHelper.getString(cursor, Message.UUID);
                int status = CursorHelper.getInt(cursor, Message.STATUS);

                MessageDirection direction = MessageStatusMigration.getMessageDirection(status);
                MessageStatus newStatus = MessageStatusMigration.getMessageStatus(status);

                ContentValues parameterValues = new ContentValues();
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_STATUS, newStatus.name());
                parameterValues.put(MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION, direction.name());

                db.update(MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS, parameterValues, MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + "=?", new String[] {uuid});
            }
            cursor.close();
        }
        if (oldVersion < 6 && newVersion >= 6) {

        }
    }

    public static void create(SQLiteDatabase db) {
        // Create Conversations+ related tables
        db.execSQL(MessageDatabaseAccess.TABLE_ADDITIONAL_PARAMETERS_CREATE_V4);
    }

    public static Pair<Long, String> getLastMessageReceived(SQLiteDatabase db, String accountUuid) {
        Cursor cursor = null;
        try {
            String sql = "select messages.timeSent,messages.serverMsgId " +
                    "from accounts " +
                    "join conversations on accounts.uuid=conversations.accountUuid " +
                    "join messages on conversations.uuid=messages.conversationUuid " +
                    "join " + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + " on messages.uuid=" + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + "." + MessageDatabaseAccess.COLUMN_NAME_MSG_PARAMS_MSGUUID + " " +
                    "where accounts.uuid=? " +
                    "and (" + MessageDatabaseAccess.TABLE_NAME_ADDITIONAL_PARAMETERS + "." + MessageDatabaseAccess.COLUMN_NAME_MSG_DIRECTION + "='IN'" +
                    " or messages.carbon=1 or messages.serverMsgId not null) order by messages.timesent desc limit 1";
            String[] args = {accountUuid};
            cursor = db.rawQuery(sql, args);
            if (cursor.getCount() == 0) {
                return null;
            } else {
                cursor.moveToFirst();
                return new Pair<>(cursor.getLong(0), cursor.getString(1));
            }
        } catch (Exception e) {
            return null;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }
}