MuditaOS/module-db/Tables/ContactsTable.cpp

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

#include "ContactsTable.hpp"
#include "Common/Types.hpp"
#include <log/log.hpp>
#include <Utils.hpp>

namespace ColumnName
{
    constexpr std::uint8_t id         = 0;
    constexpr std::uint8_t name_id    = 1;
    constexpr std::uint8_t numbers_id = 2;
    constexpr std::uint8_t ring_id    = 3;
    constexpr std::uint8_t address_id = 4;
    constexpr std::uint8_t speeddial  = 5;
}; // namespace ColumnName

namespace statements
{
    constexpr auto selectWithoutTemp = "SELECT * FROM contacts WHERE _id=" u32_ " AND "
                                       " contacts._id NOT IN ( "
                                       "   SELECT cmg.contact_id "
                                       "   FROM contact_match_groups cmg, contact_groups cg "
                                       "   WHERE cmg.group_id = cg._id "
                                       "       AND cg.name = 'Temporary' "
                                       "   ) ";
    constexpr auto selectWithTemp = "SELECT * FROM contacts WHERE _id=" u32_ ";";
} // namespace statements

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

ContactsTable::~ContactsTable()
{}

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

bool ContactsTable::add(ContactsTableRow entry)
{
    return db->execute("insert or ignore into contacts (name_id, numbers_id, ring_id, address_id, speeddial) "
                       " VALUES (" u32_c str_c u32_c u32_c str_ ");",
                       entry.nameID,
                       entry.numbersID.c_str(),
                       entry.ringID,
                       entry.addressID,
                       entry.speedDial.c_str());
}

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

bool ContactsTable::BlockByID(std::uint32_t id, bool shouldBeBlocked)
{
    return db->execute("UPDATE contacts SET blacklist=" u32_ " WHERE _id=" u32_ ";", shouldBeBlocked ? 1 : 0, id);
}

bool ContactsTable::update(ContactsTableRow entry)
{
    return db->execute("UPDATE contacts SET name_id=" u32_c "numbers_id=" str_c "ring_id=" u32_c "address_id=" u32_c
                       "speeddial=" str_ " WHERE _id=" u32_ ";",
                       entry.nameID,
                       entry.numbersID.c_str(),
                       entry.ringID,
                       entry.addressID,
                       entry.speedDial.c_str(),
                       entry.ID);
}

ContactsTableRow ContactsTable::getById(std::uint32_t id)
{
    auto retQuery = db->query(statements::selectWithoutTemp, id);
    return getByIdCommon(std::move(retQuery));
}

ContactsTableRow ContactsTable::getByIdWithTemporary(std::uint32_t id)
{
    debug_db_data("%s", __FUNCTION__);
    auto retQuery = db->query(statements::selectWithTemp, id);
    return getByIdCommon(std::move(retQuery));
}

ContactsTableRow ContactsTable::getByIdCommon(std::unique_ptr<QueryResult> retQuery)
{
    debug_db_data("%s", __FUNCTION__);
    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        LOG_DEBUG("No results");
        return ContactsTableRow();
    }

    debug_db_data(
        "got results: %" PRIu32 "; ID: %" PRIu32, retQuery->getRowCount(), (*retQuery)[ColumnName::id].getInt32());
    return ContactsTableRow{
        Record((*retQuery)[ColumnName::id].getUInt32()),
        .nameID    = (*retQuery)[ColumnName::name_id].getUInt32(),
        .numbersID = (*retQuery)[ColumnName::numbers_id].getString(),
        .ringID    = (*retQuery)[ColumnName::ring_id].getUInt32(),
        .addressID = (*retQuery)[ColumnName::address_id].getUInt32(),
        .speedDial = (*retQuery)[ColumnName::speeddial].getString(),
    };
}

std::vector<ContactsTableRow> ContactsTable::Search(const std::string &primaryName,
                                                    const std::string &alternativeName,
                                                    const std::string &number)
{
    std::vector<ContactsTableRow> ret;

    if (primaryName.empty() && alternativeName.empty() && number.empty()) {
        return (ret);
    }

    std::string q = "select t1.*,t2.name_primary,t2.name_alternative from contacts t1 inner join contact_name "
                    "t2 "
                    "on t1._id=t2.contact_id inner join contact_number t3 on t1._id=t3.contact_id where ";

    if (!primaryName.empty()) {
        q += "t2.name_primary like '%%" + primaryName + "%%'";
        if (!alternativeName.empty())
            q += " or ";
    }

    if (!alternativeName.empty()) {
        q += "t2.name_alternative like '%%" + alternativeName + "%%'";
        if (!number.empty())
            q += " or ";
    }

    if (!number.empty())
        q += "t3.number_e164 like '%%" + number + "%%'";

    debug_db_data("query: \"%s\"", q.c_str());
    auto retQuery = db->query(q.c_str());

    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<ContactsTableRow>();
    }
    if (retQuery->getRowCount() > 0)
        do {
            ret.push_back(ContactsTableRow{
                {(*retQuery)[ColumnName::id].getUInt32()},
                (*retQuery)[ColumnName::name_id].getUInt32(),
                (*retQuery)[ColumnName::numbers_id].getString(),
                (*retQuery)[ColumnName::ring_id].getUInt32(),
                (*retQuery)[ColumnName::address_id].getUInt32(),
                (*retQuery)[ColumnName::speeddial].getString(),
                (*retQuery)[ColumnName::speeddial + 1].getString(), // primaryName
                (*retQuery)[ColumnName::speeddial + 2].getString(), // alternativeName (WTF!)
            });
        } while (retQuery->nextRow());

    return ret;
}

std::string ContactsTable::GetSortedByNameQueryString(ContactQuerySection section)
{
    std::string query;
    if (section == ContactQuerySection::Favourites) {
        query = "SELECT contacts._id  FROM contacts"
                " INNER JOIN contact_name ON contact_name.contact_id == contacts._id "
                " LEFT JOIN contact_match_groups ON contact_match_groups.contact_id == contacts._id AND "
                " contact_match_groups.group_id = 1 "
                " WHERE group_id= 1 "
                " ORDER BY  (contact_name.name_alternative ='') ASC "
                " , UPPER(contact_name.name_alternative) ; ";
    }
    else if (section == ContactQuerySection::Mixed) {
        query = " SELECT contacts._id, "
                " CASE WHEN contact_name.name_alternative != ''"
                " THEN contact_name.name_alternative ELSE contact_name.name_primary"
                " END AS name_all"
                " FROM contacts "
                " INNER JOIN contact_name ON contact_name.contact_id == contacts._id "
                " LEFT JOIN contact_match_groups ON contact_match_groups.contact_id == contacts._id AND "
                " contact_match_groups.group_id = 1 "
                " WHERE contacts._id not in ( "
                "    SELECT cmg.contact_id "
                "    FROM contact_match_groups cmg, contact_groups cg "
                "    WHERE cmg.group_id = cg._id "
                "       AND cg.name = 'Temporary' ) "
                " ORDER BY  (name_all ='') ASC "
                " , UPPER(name_all) ; ";
    }
    return query;
}

std::vector<std::uint32_t> ContactsTable::GetIDsSortedByName(std::uint32_t limit, std::uint32_t offset)
{
    std::vector<std::uint32_t> ids;
    std::vector<std::uint32_t> ids_limit;

    std::string query = GetSortedByNameQueryString(ContactQuerySection::Favourites);
    debug_db_data("query: %s", query.c_str());
    auto queryRet = db->query(query.c_str());
    if (queryRet == nullptr) {

        return ids;
    }
    if (queryRet->getRowCount() > 0)
        do {
            ids.push_back((*queryRet)[0].getUInt32());
        } while (queryRet->nextRow());

    query = GetSortedByNameQueryString(ContactQuerySection::Mixed);
    debug_db_data("query: %s", query.c_str());
    queryRet = db->query(query.c_str());
    if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
        return ids;
    }
    if (queryRet->getRowCount() > 0)
        do {
            ids.push_back((*queryRet)[0].getUInt32());
        } while (queryRet->nextRow());

    if (limit > 0) {
        for (std::uint32_t a = 0; a < limit; a++) {
            ids_limit.push_back(ids[a + offset]);
        }
        return ids_limit;
    }

    return ids;
}

ContactsMapData ContactsTable::GetPosOfFirstLetters()
{
    ContactsMapData contactMap;
    std::string FirstLetterOfName;
    std::string FirstLetterOfNameOld;
    std::uint32_t PositionOnList  = 0;
    std::uint32_t favouritesCount = 0;
    std::string query;

    query         = GetSortedByNameQueryString(ContactQuerySection::Favourites);
    auto queryRet = db->query(query.c_str());
    if (queryRet == nullptr) {
        return contactMap;
    }
    if (queryRet->getRowCount() > 0)
        do {
            favouritesCount++;
            PositionOnList++;
        } while (queryRet->nextRow());

    query    = GetSortedByNameQueryString(ContactQuerySection::Mixed);
    queryRet = db->query(query.c_str());
    if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
        return contactMap;
    }
    if (queryRet->getRowCount() > 0)
        do {
            UTF8 FirstLetterOfNameUtf = (*queryRet)[1].getString();
            FirstLetterOfName         = FirstLetterOfNameUtf.substr(0, 1);
            if (FirstLetterOfName != FirstLetterOfNameOld) {
                contactMap.firstLetterDictionary.insert(
                    std::pair<std::string, std::uint32_t>(FirstLetterOfName, PositionOnList));
            }
            FirstLetterOfNameOld = FirstLetterOfName;
            PositionOnList++;
        } while (queryRet->nextRow());

    contactMap.favouritesCount = favouritesCount;
    contactMap.itemCount       = PositionOnList;

    return contactMap;
}

std::vector<std::uint32_t> ContactsTable::GetIDsSortedByField(
    MatchType matchType, const std::string &name, std::uint32_t groupId, std::uint32_t limit, std::uint32_t offset)
{
    std::vector<std::uint32_t> ids;

    std::string query = "SELECT DISTINCT contacts._id FROM contacts";

    query += " INNER JOIN contact_name ON contact_name.contact_id == contacts._id ";
    query += " LEFT JOIN contact_match_groups ON contact_match_groups.contact_id == contacts._id AND "
             "contact_match_groups.group_id = " +
             std::to_string(groupId);

    constexpr auto exclude_temporary = " WHERE contacts._id not in ( "
                                       "   SELECT cmg.contact_id "
                                       "   FROM contact_match_groups cmg, contact_groups cg "
                                       "   WHERE cmg.group_id = cg._id "
                                       "       AND cg.name = 'Temporary' "
                                       "   ) ";

    switch (matchType) {
    case MatchType::Name: {
        query += exclude_temporary;

        if (!name.empty()) {
            const auto names     = utils::split(name, " ");
            const auto namePart1 = names[0];
            const auto namePart2 = names.size() > 1 ? names[1] : "";

            if (!namePart1.empty() && !namePart2.empty()) {
                query += " AND (( contact_name.name_primary LIKE '" + namePart1 + "%%'";
                query += " AND contact_name.name_alternative  LIKE '" + namePart2 + "%%')";
                query += " OR ( contact_name.name_primary LIKE '" + namePart2 + "%%'";
                query += " AND contact_name.name_alternative  LIKE '" + namePart1 + "%%'))";
            }
            else {
                query += " AND ( contact_name.name_primary LIKE '" + namePart1 + "%%'";
                query += " OR contact_name.name_alternative  LIKE '" + namePart1 + "%%')";
            }
        }
    } break;

    case MatchType::TextNumber: {
        if (!name.empty()) {
            query += " INNER JOIN contact_number ON contact_number.contact_id == contacts._id AND "
                     "contact_number.number_user LIKE '%%" +
                     name + "%%'";
        }
        query += exclude_temporary;
    } break;

    case MatchType::Group:
        query += " WHERE contact_match_groups.group_id == " + std::to_string(groupId);
        break;

    case MatchType::None: {
        query += exclude_temporary;
    } break;
    }

    query += " ORDER BY group_id DESC ";
    query += " , (contact_name.name_alternative IS NULL OR contact_name.name_alternative ='') ";
    query += " AND (contact_name.name_primary IS NULL OR contact_name.name_primary ='') ASC ";
    query += " , UPPER(contact_name.name_alternative || contact_name.name_primary) ";

    if (limit > 0) {
        query += " LIMIT " + std::to_string(limit);
        query += " OFFSET " + std::to_string(offset);
    }

    query += " ;";

    debug_db_data("query: %s", query.c_str());
    auto queryRet = db->query(query.c_str());
    if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
        return ids;
    }
    if (queryRet->getRowCount() > 0)
        do {
            ids.push_back((*queryRet)[0].getUInt32());
        } while (queryRet->nextRow());

    return ids;
}

std::vector<ContactsTableRow> ContactsTable::getLimitOffset(std::uint32_t offset, std::uint32_t limit)
{
    auto retQuery = db->query("SELECT * from contacts WHERE contacts._id NOT IN "
                              " ( SELECT cmg.contact_id "
                              "    FROM contact_match_groups cmg, contact_groups cg "
                              "    WHERE cmg.group_id = cg._id "
                              "        AND cg.name = 'Temporary' "
                              " ) "
                              "ORDER BY name_id LIMIT " u32_ " OFFSET " u32_ ";",
                              limit,
                              offset);

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

    std::vector<ContactsTableRow> ret;
    if (retQuery->getRowCount() > 0)
        do {
            ret.push_back(ContactsTableRow{
                {(*retQuery)[ColumnName::id].getUInt32()},       // ID
                (*retQuery)[ColumnName::name_id].getUInt32(),    // nameID
                (*retQuery)[ColumnName::numbers_id].getString(), // numbersID
                (*retQuery)[ColumnName::ring_id].getUInt32(),    // ringID
                (*retQuery)[ColumnName::address_id].getUInt32(), // addressID
                (*retQuery)[ColumnName::speeddial].getString(),  // speed dial key
            });
        } while (retQuery->nextRow());

    return ret;
}

std::vector<ContactsTableRow> ContactsTable::getLimitOffsetByField(std::uint32_t offset,
                                                                   std::uint32_t limit,
                                                                   ContactTableFields field,
                                                                   const char *str)
{

    std::string fieldName;
    switch (field) {
    case ContactTableFields::SpeedDial:
        fieldName = "speeddial";
        break;
    default:
        return std::vector<ContactsTableRow>();
    }

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

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

    std::vector<ContactsTableRow> ret;
    if (retQuery->getRowCount() > 0)
        do {
            ret.push_back(ContactsTableRow{
                {(*retQuery)[ColumnName::id].getUInt32()},
                (*retQuery)[ColumnName::name_id].getUInt32(),
                (*retQuery)[ColumnName::numbers_id].getString(),
                (*retQuery)[ColumnName::ring_id].getUInt32(),
                (*retQuery)[ColumnName::address_id].getUInt32(),
                (*retQuery)[ColumnName::speeddial].getString(),
            });
        } while (retQuery->nextRow());

    return ret;
}

std::uint32_t ContactsTable::count()
{
    auto queryRet = db->query("SELECT COUNT(*) FROM contacts "
                              " WHERE contacts._id not in ( "
                              "    SELECT cmg.contact_id "
                              "    FROM contact_match_groups cmg, contact_groups cg "
                              "    WHERE cmg.group_id = cg._id "
                              "        AND cg.name = 'Temporary' "
                              "    ); ");

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

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

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

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

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