js8call/JS8_Main/Inbox.cpp

/**
 * @file Inbox.cpp
 * @brief Implementation of the message inbox
 */

#include "Inbox.h"
#include "DriftingDateTime.h"
#include "vendor/sqlite3/sqlite3.h"

#include <QLoggingCategory>

Q_DECLARE_LOGGING_CATEGORY(inbox_js8)

namespace {
constexpr char SCHEMA[] =
    "CREATE TABLE IF NOT EXISTS inbox_v1 ("
    "  id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "  blob TEXT"
    ");"
    "CREATE INDEX IF NOT EXISTS idx_inbox_v1__type ON"
    "  inbox_v1(json_extract(blob, '$.type'));"
    "CREATE INDEX IF NOT EXISTS idx_inbox_v1__params_from ON"
    "  inbox_v1(json_extract(blob, '$.params.FROM'));"
    "CREATE INDEX IF NOT EXISTS idx_inbox_v1__params_to ON"
    "  inbox_v1(json_extract(blob, '$.params.TO'));"
    "CREATE TABLE IF NOT EXISTS inbox_group_recip_v1 ("
    "  id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "  msg_id INTEGER, "
    "  callsign VARCHAR(255), "
    "  FOREIGN KEY(msg_id) REFERENCES inbox_v1(id) ON DELETE CASCADE"
    ");"
    "CREATE INDEX IF NOT EXISTS idx_inbox_group_recip_v1__callsign ON"
    "  inbox_group_recip_v1(callsign);";

// Attempt to retrieve a Message object previously serialized as a
// JSON object to the specified column; will throw on failure to
// deserialize the object.

auto get_column_message(sqlite3_stmt *const stmt, int const iCol) {
    return Message::fromJson(
        QByteArray((const char *)sqlite3_column_text(stmt, iCol),
                   sqlite3_column_bytes(stmt, iCol)));
}
} // namespace

Inbox::Inbox(QString path) : path_{path}, db_{nullptr} {}

Inbox::~Inbox() { close(); }

/**
 * Low-Level Interface
 **/

bool Inbox::isOpen() { return db_ != nullptr; }

bool Inbox::open() {
    int rc = sqlite3_open(path_.toLocal8Bit().data(), &db_);
    if (rc != SQLITE_OK) {
        close();
        return false;
    }

    rc = sqlite3_exec(db_, SCHEMA, nullptr, nullptr, nullptr);
    if (rc != SQLITE_OK) {
        return false;
    }

    return true;
}

void Inbox::close() {
    if (db_) {
        sqlite3_close(db_);
        db_ = nullptr;
    }
}

QString Inbox::error() {
    if (db_) {
        return QString::fromLocal8Bit(sqlite3_errmsg(db_));
    }
    return "";
}

int Inbox::count(QString type, QString query, QString match) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "SELECT COUNT(*) FROM inbox_v1 "
                      "WHERE json_extract(blob, '$.type') = ? "
                      "AND json_extract(blob, ?) LIKE ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return -1;
    }

    auto t8 = type.toLocal8Bit();
    auto q8 = query.toLocal8Bit();
    auto m8 = match.toLocal8Bit();
    rc = sqlite3_bind_text(stmt, 1, t8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 2, q8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 3, m8.data(), -1, nullptr);

    int count = 0;
    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW) {
        count = sqlite3_column_int(stmt, 0);
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return -1;
    }

    return count;
}

QList<QPair<int, Message>> Inbox::values(QString type, QString query,
                                         QString match, int offset, int limit) {
    if (!isOpen()) {
        return {};
    }

    const char *sql = "SELECT id, blob FROM inbox_v1 "
                      "WHERE json_extract(blob, '$.type') = ? "
                      "AND json_extract(blob, ?) LIKE ? "
                      "ORDER BY id ASC "
                      "LIMIT ? OFFSET ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return {};
    }

    auto t8 = type.toLocal8Bit();
    auto q8 = query.toLocal8Bit();
    auto m8 = match.toLocal8Bit();
    rc = sqlite3_bind_text(stmt, 1, t8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 2, q8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 3, m8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 4, limit);
    rc = sqlite3_bind_int(stmt, 5, offset);

    // qCDebug(inbox_js8) << "exec" << sqlite3_expanded_sql(stmt);

    QList<QPair<int, Message>> v;

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        try {
            v.append(
                {sqlite3_column_int(stmt, 0), get_column_message(stmt, 1)});
        } catch (...) {
            continue;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return {};
    }

    return v;
}

QList<QPair<int, Message>> Inbox::fetchForCall(const QString& callPattern)
{
    if (!isOpen()) {
        return {};
    }

    QList<QPair<int, Message>> msgs;
    msgs.append(values("STORE", "$.params.TO",   callPattern, 0, 1000));
    msgs.append(values("READ",  "$.params.FROM", callPattern, 0, 1000));
    msgs.append(values("UNREAD", "$.params.FROM", callPattern, 0, 1000));

    std::stable_sort(msgs.begin(), msgs.end(),
        [](const QPair<int, Message>& a, const QPair<int, Message>& b) {
            return QVariant::compare(a.second.params().value("UTC"),
                                     b.second.params().value("UTC")) ==
                   QPartialOrdering::Greater;
        });

    return msgs;
}

Message Inbox::value(int key) {
    if (!isOpen()) {
        return {};
    }

    const char *sql = "SELECT blob FROM inbox_v1 WHERE id = ? LIMIT 1;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return {};
    }

    rc = sqlite3_bind_int(stmt, 1, key);

    Message m;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        try {
            m = get_column_message(stmt, 0);
        } catch (...) {
            continue;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return {};
    }

    return m;
}

int Inbox::append(Message value) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "INSERT INTO inbox_v1 (blob) VALUES (?);";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return -2;
    }

    auto j8 = value.toJson();
    rc = sqlite3_bind_text(stmt, 1, j8.data(), -1, nullptr);
    rc = sqlite3_step(stmt);

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return -1;
    }

    return sqlite3_last_insert_rowid(db_);
}

bool Inbox::set(int key, Message value) {
    if (!isOpen()) {
        return false;
    }

    const char *sql = "UPDATE inbox_v1 SET blob = ? WHERE id = ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return false;
    }

    auto j8 = value.toJson();
    rc = sqlite3_bind_text(stmt, 1, j8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 2, key);

    rc = sqlite3_step(stmt);

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return false;
    }

    return true;
}

bool Inbox::del(int key) {
    if (!isOpen()) {
        return false;
    }

    const char *sql = "DELETE FROM inbox_v1 WHERE id = ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return false;
    }

    rc = sqlite3_bind_int(stmt, 1, key);
    rc = sqlite3_step(stmt);

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return false;
    }

    return true;
}

int Inbox::getLookaheadMessageIdForCallsign(const QString &callsign,
                                            int afterMsgId) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "SELECT inbox_v1.id, inbox_v1.blob FROM inbox_v1 "
                      "WHERE inbox_v1.id != ? "
                      "AND json_extract(blob, '$.type') = 'STORE' "
                      "AND json_extract(blob, '$.params.TO') LIKE ? "
                      "ORDER BY inbox_v1.id ASC "
                      "LIMIT ? OFFSET ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return -1;
    }

    auto c8 = callsign.toLocal8Bit();

    rc = sqlite3_bind_int(stmt, 1, afterMsgId);
    rc = sqlite3_bind_text(stmt, 2, c8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 3, 10);
    rc = sqlite3_bind_int(stmt, 4, 0);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    int next_id = -1;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        int i = sqlite3_column_int(stmt, 0);

        auto msg = QByteArray((const char *)sqlite3_column_text(stmt, 1),
                              sqlite3_column_bytes(stmt, 1));

        m = Message::fromJson(msg);

        auto params = m.params();
        auto text = params.value("TEXT").toString().trimmed();
        if (!text.isEmpty()) {
            next_id = i;
            break;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return -1;
    }

    return next_id;
}

/**
 * High-Level Interface
 **/

int Inbox::countUnreadFrom(QString from) {
    return count("UNREAD", "$.params.FROM", from);
}

QPair<int, Message> Inbox::firstUnreadFrom(QString from) {
    auto v = values("UNREAD", "$.params.FROM", from, 0, 1);
    if (v.isEmpty()) {
        return {};
    }
    return v.first();
}

QMap<QString, int> Inbox::getGroupMessageCounts() {
    if (!isOpen()) {
        return {};
    }

    QMap<QString, int> messageCounts;

    const char *sql = "SELECT count(id) as msg_count, json_extract(blob, "
                      "'$.params.TO') as group_name FROM inbox_v1 "
                      "WHERE json_extract(blob, '$.type') = 'STORE' "
                      "AND group_name LIKE '@%' "
                      "AND json_extract(blob, '$.params.UTC') > ? "
                      "GROUP BY group_name";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return messageCounts;
    }

    // Set a floor or 48 hours for group message retrieval
    // TODO: date formatting with the "yyyy-MM-dd HH:mm:ss" string happens
    // elsewhere as well, centralize
    // TODO: possibly make the date floor configurable
    auto d8 = DriftingDateTime::currentDateTimeUtc()
                  .addDays(-2)
                  .toString("yyyy-MM-dd HH:mm:ss")
                  .toLocal8Bit();

    rc = sqlite3_bind_text(stmt, 1, d8.data(), -1, nullptr);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        int count = sqlite3_column_int(stmt, 0);
        auto group = sqlite3_column_text(stmt, 1);

        messageCounts.insert(
            QString::fromLocal8Bit(reinterpret_cast<const char *>(group)),
            count);
    }

    rc = sqlite3_finalize(stmt);

    return messageCounts;
}

bool Inbox::markGroupMsgDeliveredForCallsign(int msgId, QString callsign) {
    if (!isOpen()) {
        return false;
    }

    const char *sql = "SELECT count(id) as msg_count FROM inbox_group_recip_v1 "
                      "WHERE msg_id = ? AND callsign = ? LIMIT 1;";

    sqlite3_stmt *exists_stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &exists_stmt, nullptr);
    if (rc != SQLITE_OK) {
        return false;
    }

    auto cs8 = callsign.toLocal8Bit();

    rc = sqlite3_bind_int(exists_stmt, 1, msgId);
    rc = sqlite3_bind_text(exists_stmt, 2, cs8.data(), -1, nullptr);

    bool recordExists = false;
    rc = sqlite3_step(exists_stmt);
    int count = sqlite3_column_int(exists_stmt, 0);
    recordExists = (count > 0);

    rc = sqlite3_finalize(exists_stmt);

    if (!recordExists) {
        sql =
            "INSERT INTO inbox_group_recip_v1 (msg_id, callsign) VALUES (?,?);";

        sqlite3_stmt *insert_stmt;
        rc = sqlite3_prepare_v2(db_, sql, -1, &insert_stmt, nullptr);
        if (rc != SQLITE_OK) {
            return false;
        }

        rc = sqlite3_bind_int(insert_stmt, 1, msgId);
        rc = sqlite3_bind_text(insert_stmt, 2, cs8.data(), -1, nullptr);

        rc = sqlite3_step(insert_stmt);

        rc = sqlite3_finalize(insert_stmt);
        if (rc != SQLITE_OK) {
            return false;
        }
    }

    return true;
}

int Inbox::getNextGroupMessageIdForCallsign(const QString &group_name,
                                            const QString &callsign) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "SELECT inbox_v1.id, inbox_v1.blob FROM inbox_v1 "
                      "LEFT JOIN inbox_group_recip_v1 ON "
                      "(inbox_group_recip_v1.msg_id=inbox_v1.id AND "
                      "inbox_group_recip_v1.callsign = ?) "
                      "WHERE json_extract(blob, '$.type') = 'STORE' "
                      "AND json_extract(blob, '$.params.TO') LIKE ? "
                      "AND json_extract(blob, '$.params.UTC') > ? "
                      "AND inbox_group_recip_v1.id IS NULL "
                      "ORDER BY inbox_v1.id ASC "
                      "LIMIT ? OFFSET ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return -1;
    }

    auto c8 = callsign.toLocal8Bit();
    auto g8 = group_name.toLocal8Bit();

    // Set a floor or 48 hours for group message retrieval
    // TODO: date formatting with the "yyyy-MM-dd HH:mm:ss" string happens
    // elsewhere as well, centralize
    // TODO: possibly make the date floor configurable
    auto d8 = DriftingDateTime::currentDateTimeUtc()
                  .addDays(-2)
                  .toString("yyyy-MM-dd HH:mm:ss")
                  .toLocal8Bit();

    rc = sqlite3_bind_text(stmt, 1, c8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 2, g8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 3, d8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 4, 10);
    rc = sqlite3_bind_int(stmt, 5, 0);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    int next_id = -1;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        int i = sqlite3_column_int(stmt, 0);

        auto msg = QByteArray((const char *)sqlite3_column_text(stmt, 1),
                              sqlite3_column_bytes(stmt, 1));

        m = Message::fromJson(msg);

        auto params = m.params();
        auto text = params.value("TEXT").toString().trimmed();
        if (!text.isEmpty()) {
            next_id = i;
            break;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return -1;
    }

    return next_id;
}

int Inbox::getLookaheadGroupMessageIdForCallsign(const QString &group_name,
                                                 const QString &callsign,
                                                 int afterMsgId) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "SELECT inbox_v1.id, inbox_v1.blob FROM inbox_v1 "
                      "LEFT JOIN inbox_group_recip_v1 ON "
                      "(inbox_group_recip_v1.msg_id=inbox_v1.id AND "
                      "inbox_group_recip_v1.callsign = ?) "
                      "WHERE inbox_v1.id != ? "
                      "AND json_extract(blob, '$.type') = 'STORE' "
                      "AND json_extract(blob, '$.params.TO') LIKE ? "
                      "AND json_extract(blob, '$.params.UTC') > ? "
                      "AND inbox_group_recip_v1.id IS NULL "
                      "ORDER BY inbox_v1.id ASC "
                      "LIMIT ? OFFSET ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return -1;
    }

    auto c8 = callsign.toLocal8Bit();
    auto g8 = group_name.toLocal8Bit();

    // Set a floor or 48 hours for group message retrieval
    // TODO: date formatting with the "yyyy-MM-dd HH:mm:ss" string happens
    // elsewhere as well, centralize
    // TODO: possibly make the date floor configurable
    auto d8 = DriftingDateTime::currentDateTimeUtc()
                  .addDays(-2)
                  .toString("yyyy-MM-dd HH:mm:ss")
                  .toLocal8Bit();

    rc = sqlite3_bind_text(stmt, 1, c8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 2, afterMsgId);
    rc = sqlite3_bind_text(stmt, 3, g8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 4, d8.data(), -1, nullptr);
    rc = sqlite3_bind_int(stmt, 5, 10);
    rc = sqlite3_bind_int(stmt, 6, 0);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    int next_id = -1;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        int i = sqlite3_column_int(stmt, 0);

        auto msg = QByteArray((const char *)sqlite3_column_text(stmt, 1),
                              sqlite3_column_bytes(stmt, 1));

        m = Message::fromJson(msg);

        auto params = m.params();
        auto text = params.value("TEXT").toString().trimmed();
        if (!text.isEmpty()) {
            next_id = i;
            break;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return -1;
    }

    return next_id;
}

int Inbox::countUnreadForCallsign(const QString &callsign) {
    if (!isOpen()) {
        return 0;
    }

    const char *sql = "SELECT inbox_v1.blob FROM inbox_v1 "
                      "WHERE json_extract(blob, '$.type') = 'STORE' "
                      "AND json_extract(blob, '$.params.TO') LIKE ?;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return 0;
    }

    auto c8 = callsign.toLocal8Bit();

    rc = sqlite3_bind_text(stmt, 1, c8.data(), -1, nullptr);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    int count = 0;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        auto msg = QByteArray((const char *)sqlite3_column_text(stmt, 0),
                              sqlite3_column_bytes(stmt, 0));

        m = Message::fromJson(msg);

        auto params = m.params();
        auto text = params.value("TEXT").toString().trimmed();
        if (!text.isEmpty()) {
            count++;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return 0;
    }

    return count;
}

int Inbox::countGroupUnreadForCallsign(const QString &group_name,
                                            const QString &callsign) {
    if (!isOpen()) {
        return -1;
    }

    const char *sql = "SELECT inbox_v1.blob FROM inbox_v1 "
                      "LEFT JOIN inbox_group_recip_v1 ON "
                      "(inbox_group_recip_v1.msg_id=inbox_v1.id AND "
                      "inbox_group_recip_v1.callsign = ?) "
                      "WHERE json_extract(blob, '$.type') = 'STORE' "
                      "AND json_extract(blob, '$.params.TO') LIKE ? "
                      "AND json_extract(blob, '$.params.UTC') > ? "
                      "AND inbox_group_recip_v1.id IS NULL;";

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        return 0;
    }

    auto c8 = callsign.toLocal8Bit();
    auto g8 = group_name.toLocal8Bit();

    // Set a floor or 48 hours for group message retrieval
    // TODO: date formatting with the "yyyy-MM-dd HH:mm:ss" string happens
    // elsewhere as well, centralize
    // TODO: possibly make the date floor configurable
    auto d8 = DriftingDateTime::currentDateTimeUtc()
                  .addDays(-2)
                  .toString("yyyy-MM-dd HH:mm:ss")
                  .toLocal8Bit();

    rc = sqlite3_bind_text(stmt, 1, c8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 2, g8.data(), -1, nullptr);
    rc = sqlite3_bind_text(stmt, 3, d8.data(), -1, nullptr);

    // qCDebug(inbox_js8) << "exec " << sqlite3_expanded_sql(stmt);

    int count = 0;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Message m;

        auto msg = QByteArray((const char *)sqlite3_column_text(stmt, 0),
                              sqlite3_column_bytes(stmt, 0));

        m = Message::fromJson(msg);

        auto params = m.params();
        auto text = params.value("TEXT").toString().trimmed();
        if (!text.isEmpty()) {
            count++;
        }
    }

    rc = sqlite3_finalize(stmt);
    if (rc != SQLITE_OK) {
        return 0;
    }

    return count;
}


Q_LOGGING_CATEGORY(inbox_js8, "inbox.js8", QtWarningMsg)