MuditaOS/module-db/Tables/SMSTable.cpp

// Copyright (c) 2017-2023, Mudita Sp. z.o.o. All rights reserved.
// For licensing, see https://github.com/mudita/MuditaOS/LICENSE.md

#include "SMSTable.hpp"
#include "Common/Types.hpp"
#include <log/log.hpp>

SMSTable::SMSTable(Database *db) : Table(db)
{}

bool SMSTable::create()
{
    return true;
}

bool SMSTable::add(SMSTableRow entry)
{
    return db->execute("INSERT or ignore INTO sms ( thread_id,contact_id, date, error_code, body, "
                       "type ) VALUES (" u32_c u32_c u32_c "0," str_c u32_ ");",
                       entry.threadID,
                       entry.contactID,
                       entry.date,
                       entry.body.c_str(),
                       entry.type);
}

bool SMSTable::removeById(uint32_t id)
{
    return db->execute("DELETE FROM sms where _id=" u32_ ";", id);
}

bool SMSTable::removeByField(SMSTableFields field, const char *str)
{
    std::string fieldName;

    switch (field) {
    case SMSTableFields::ThreadID:
        fieldName = "thread_id";
        break;

    case SMSTableFields::ContactID:
        fieldName = "contact_id";
        break;

    case SMSTableFields::Date:
        fieldName = "date";
        break;
    default:
        return false;
    }

    return db->execute("DELETE FROM sms where %q=" str_ ";", fieldName.c_str(), str);
}

bool SMSTable::update(SMSTableRow entry)
{
    return db->execute("UPDATE sms SET thread_id=" u32_c "contact_id=" u32_c "date=" u32_c "error_code=0,"
                       "body=" str_c "type=" u32_ " WHERE _id=" u32_ ";",
                       entry.threadID,
                       entry.contactID,
                       entry.date,
                       entry.body.c_str(),
                       entry.type,
                       entry.ID);
}

SMSTableRow SMSTable::getById(uint32_t id)
{
    auto retQuery = db->query("SELECT * FROM sms WHERE _id=" u32_ ";", id);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return SMSTableRow();
    }

    return SMSTableRow{
        (*retQuery)[0].getUInt32(),                       // ID
        (*retQuery)[1].getUInt32(),                       // threadID
        (*retQuery)[2].getUInt32(),                       // contactID
        (*retQuery)[3].getUInt32(),                       // date
        (*retQuery)[4].getUInt32(),                       // errorCode
        (*retQuery)[5].getString(),                       // body
        static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
    };
}

std::vector<SMSTableRow> SMSTable::getByContactId(uint32_t contactId)
{
    auto retQuery = db->query("SELECT * FROM sms WHERE contact_id=" u32_ ";", contactId);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}
std::vector<SMSTableRow> SMSTable::getByThreadId(uint32_t threadId, uint32_t offset, uint32_t limit)
{
    auto retQuery = db->query("SELECT * FROM sms WHERE thread_id=" u32_ ";", threadId);

    if (limit != 0) {
        retQuery = db->query(
            "SELECT * FROM sms WHERE thread_id=" u32_ " LIMIT " u32_ " OFFSET " u32_ ";", threadId, limit, offset);
    }

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}

std::vector<SMSTableRow> SMSTable::getByThreadIdWithoutDraftWithEmptyInput(uint32_t threadId,
                                                                           uint32_t offset,
                                                                           uint32_t limit)
{
    auto retQuery =
        db->query("SELECT * FROM sms WHERE thread_id=" u32_ " AND type!=" u32_ " UNION ALL SELECT 0 as _id, 0 as "
                  "thread_id, 0 as contact_id, 0 as "
                  "date, 0 as error_code, 0 as body, " u32_ " as type LIMIT " u32_ " OFFSET " u32_,
                  threadId,
                  SMSType::DRAFT,
                  SMSType::INPUT,
                  limit,
                  offset);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}

uint32_t SMSTable::countWithoutDraftsByThreadId(uint32_t threadId)
{
    auto queryRet =
        db->query("SELECT COUNT(*) FROM sms WHERE thread_id=" u32_ " AND type!=" u32_ ";", threadId, SMSType::DRAFT);

    if (queryRet == nullptr || queryRet->getRowCount() == 0) {
        return 0;
    }

    return uint32_t{(*queryRet)[0].getUInt32()};
}

SMSTableRow SMSTable::getDraftByThreadId(uint32_t threadId)
{
    auto retQuery =
        db->query("SELECT * FROM sms WHERE thread_id=" u32_ " AND type=" u32_ " ORDER BY date DESC LIMIT 1;",
                  threadId,
                  SMSType::DRAFT);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return SMSTableRow();
    }

    return SMSTableRow{
        (*retQuery)[0].getUInt32(),                       // ID
        (*retQuery)[1].getUInt32(),                       // threadID
        (*retQuery)[2].getUInt32(),                       // contactID
        (*retQuery)[3].getUInt32(),                       // date
        (*retQuery)[4].getUInt32(),                       // errorCode
        (*retQuery)[5].getString(),                       // body
        static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
    };
}

std::vector<SMSTableRow> SMSTable::getByText(std::string text)
{

    auto retQuery = db->query("SELECT *, INSTR(body," str_ ") pos FROM sms WHERE pos > 0;", text.c_str());

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}

std::vector<SMSTableRow> SMSTable::getByText(std::string text, uint32_t threadId)
{
    auto retQuery = db->query(
        "SELECT *, INSTR(body," str_ ") pos FROM sms WHERE pos > 0 AND thread_id=" u32_ ";", text.c_str(), threadId);
    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return {};
    }

    std::vector<SMSTableRow> ret;
    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());
    return ret;
}

std::vector<SMSTableRow> SMSTable::getLimitOffset(uint32_t offset, uint32_t limit)
{
    auto retQuery = db->query("SELECT * from sms ORDER BY date DESC LIMIT " u32_ " OFFSET " u32_ ";", limit, offset);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            (*retQuery)[0].getUInt32(),                       // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}

std::vector<SMSTableRow> SMSTable::getLimitOffsetByField(uint32_t offset,
                                                         uint32_t limit,
                                                         SMSTableFields field,
                                                         const char *str)
{

    std::string fieldName;
    switch (field) {
    case SMSTableFields::Date:
        fieldName = "date";
        break;
    case SMSTableFields::ContactID:
        fieldName = "contact_id";
        break;
    case SMSTableFields::ThreadID:
        fieldName = "thread_id";
        break;
    default:
        return std::vector<SMSTableRow>();
    }

    auto retQuery = db->query("SELECT * from sms WHERE %q=" str_ " ORDER BY date DESC LIMIT " u32_ " OFFSET " u32_ ";",
                              fieldName.c_str(),
                              str,
                              limit,
                              offset);

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<SMSTableRow>();
    }

    std::vector<SMSTableRow> ret;

    do {
        ret.push_back(SMSTableRow{
            {(*retQuery)[0].getUInt32()},                     // ID
            (*retQuery)[1].getUInt32(),                       // threadID
            (*retQuery)[2].getUInt32(),                       // contactID
            (*retQuery)[3].getUInt32(),                       // date
            (*retQuery)[4].getUInt32(),                       // errorCode
            (*retQuery)[5].getString(),                       // body
            static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
        });
    } while (retQuery->nextRow());

    return ret;
}
uint32_t SMSTable::count()
{
    std::string query = "SELECT COUNT(*) FROM sms;";

    auto queryRet = db->query(query.c_str());
    if (queryRet == nullptr || queryRet->getRowCount() == 0) {
        return 0;
    }

    return uint32_t{(*queryRet)[0].getUInt32()};
}

uint32_t SMSTable::countByFieldId(const char *field, uint32_t id)
{
    auto queryRet = db->query("SELECT COUNT(*) FROM sms WHERE %q=" u32_ ";", field, id);

    if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
        return 0;
    }

    return uint32_t{(*queryRet)[0].getUInt32()};
}

std::pair<uint32_t, std::vector<SMSTableRow>> SMSTable::getManyByType(SMSType type, uint32_t offset, uint32_t limit)
{
    auto ret   = std::pair<uint32_t, std::vector<SMSTableRow>>{0, {}};
    auto count = db->query("SELECT COUNT (*) from sms WHERE type=" u32_ ";", type);
    ret.first  = count == nullptr ? 0 : (*count)[0].getUInt32();
    if (ret.first != 0) {
        limit = limit == 0 ? ret.first : limit; // no limit intended
        auto retQuery =
            db->query("SELECT * from sms WHERE type=" u32_ " ORDER BY date ASC LIMIT " u32_ " OFFSET " u32_ ";",
                      type,
                      limit,
                      offset);

        if (retQuery == nullptr || retQuery->getRowCount() == 0) {
            ret.second = {};
        }
        else {
            do {
                ret.second.push_back(SMSTableRow{
                    {(*retQuery)[0].getUInt32()},                     // ID
                    (*retQuery)[1].getUInt32(),                       // threadID
                    (*retQuery)[2].getUInt32(),                       // contactID
                    (*retQuery)[3].getUInt32(),                       // date
                    (*retQuery)[4].getUInt32(),                       // errorCode
                    (*retQuery)[5].getString(),                       // body
                    static_cast<SMSType>((*retQuery)[6].getUInt32()), // type
                });
            } while (retQuery->nextRow());
        }
    }
    else {
        ret.second = {};
    }
    return ret;
}