MuditaOS/module-db/Tables/MultimediaFilesTable.cpp

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

#include "MultimediaFilesTable.hpp"
#include "Common/Types.hpp"
#include <Database/QueryResult.hpp>
#include <Utils.hpp>
#include <magic_enum.hpp>
#include <inttypes.h>

namespace
{
    std::string getSorting(db::multimedia_files::SortingBy sorting)
    {
        switch (sorting) {
        case db::multimedia_files::SortingBy::TrackIdAscending:
            return "track ASC";
        case db::multimedia_files::SortingBy::TitleAscending:
        default:
            return "title ASC";
        }
    }
} // namespace

namespace db::multimedia_files
{
    TableRow CreateTableRow(const QueryResult &result)
    {
        if (result.getFieldCount() != magic_enum::enum_count<TableFields>() + 1) {
            return TableRow{};
        }

        return TableRow{
            result[0].getUInt32(),    // ID
            {result[1].getString(),   // path
             result[2].getString(),   // mediaType
             result[3].getUInt32()},  // size
            {result[4].getString(),   // title
             {result[5].getString(),  // artist
              result[6].getString()}, // album title
             result[7].getString(),   // comment
             result[8].getString(),   // genre
             result[9].getUInt32(),   // year
             result[10].getUInt32()}, // track
            {result[11].getUInt32(),  // songLength
             result[12].getUInt32(),  // bitrate
             result[13].getUInt32(),  // sample rate
             result[14].getUInt32()}, // channels
        };
    }

    auto TableRow::isValid() const -> bool
    {
        return (!fileInfo.path.empty() && Record::isValid());
    }

    auto constructMatchPattern = [](const std::vector<std::string> &paths) -> std::string {
        std::string ret;
        for (auto e = paths.begin(); e != paths.end(); e++) {
            ret += "path LIKE '" + *e + "%%'";
            if (std::next(e) != paths.end()) {
                ret += " or ";
            }
        }
        return ret;
    };

    MultimediaFilesTable::MultimediaFilesTable(Database *db) : Table(db)
    {
        createTableRow = CreateTableRow;
    }

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

    bool MultimediaFilesTable::add(TableRow entry)
    {
        return db->execute("INSERT INTO files (path, media_type, size, title, artist, album, "
                           "comment, genre, year, track, song_length, bitrate, sample_rate, channels) "
                           "VALUES(" str_c str_c u32_c str_c str_c str_c str_c str_c u32_c u32_c u32_c u32_c u32_c u32_
                           ") "
                           "ON CONFLICT(path) DO UPDATE SET "
                           "path = excluded.path, "
                           "media_type = excluded.media_type, "
                           "size = excluded.size, "
                           "title = excluded.title, "
                           "artist = excluded.artist, "
                           "album = excluded.album, "
                           "comment = excluded.comment, "
                           "genre = excluded.genre, "
                           "year = excluded.year, "
                           "track = excluded.track, "
                           "song_length = excluded.song_length, "
                           "bitrate = excluded.bitrate, "
                           "sample_rate = excluded.sample_rate, "
                           "channels = excluded.channels;",
                           entry.fileInfo.path.c_str(),
                           entry.fileInfo.mediaType.c_str(),
                           entry.fileInfo.size,
                           entry.tags.title.c_str(),
                           entry.tags.album.artist.c_str(),
                           entry.tags.album.title.c_str(),
                           entry.tags.comment.c_str(),
                           entry.tags.genre.c_str(),
                           entry.tags.year,
                           entry.tags.track,
                           entry.audioProperties.songLength,
                           entry.audioProperties.bitrate,
                           entry.audioProperties.sampleRate,
                           entry.audioProperties.channels);
    }

    bool MultimediaFilesTable::removeById(uint32_t id)
    {
        return db->execute("DELETE FROM files WHERE _id=" u32_ ";", id);
    }

    bool MultimediaFilesTable::removeByField(TableFields field, const char *str)
    {
        const auto &fieldName = getFieldName(field);

        if (fieldName.empty()) {
            return false;
        }

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

    bool MultimediaFilesTable::removeAll()
    {
        return db->execute("DELETE FROM files;");
    }

    bool MultimediaFilesTable::update(TableRow entry)
    {
        return db->execute("UPDATE files SET path=" str_c "media_type=" str_c "size=" u32_c "title=" str_c
                           "artist=" str_c "album=" str_c "comment=" str_c "genre=" str_c "year=" u32_c "track=" u32_c
                           "song_length=" u32_c "bitrate=" u32_c "sample_rate=" u32_c "channels=" u32_
                           " WHERE _id=" u32_ ";",
                           entry.fileInfo.path.c_str(),
                           entry.fileInfo.mediaType.c_str(),
                           entry.fileInfo.size,
                           entry.tags.title.c_str(),
                           entry.tags.album.artist.c_str(),
                           entry.tags.album.title.c_str(),
                           entry.tags.comment.c_str(),
                           entry.tags.genre.c_str(),
                           entry.tags.year,
                           entry.tags.track,
                           entry.audioProperties.songLength,
                           entry.audioProperties.bitrate,
                           entry.audioProperties.sampleRate,
                           entry.audioProperties.channels,
                           entry.ID);
    }

    bool MultimediaFilesTable::addOrUpdate(TableRow entry, std::string oldPath)
    {
        auto path = oldPath.empty() ? entry.fileInfo.path : oldPath;

        return db->execute("BEGIN TRANSACTION; "
                           "INSERT OR IGNORE INTO files (path) VALUES (" str_ "); "
                           "UPDATE files SET path=" str_c "media_type=" str_c "size=" u32_c "title=" str_c
                           "artist=" str_c "album=" str_c "comment=" str_c "genre=" str_c "year=" u32_c "track=" u32_c
                           "song_length=" u32_c "bitrate=" u32_c "sample_rate=" u32_c "channels=" u32_
                           " WHERE path=" str_ "; "
                           "COMMIT;",
                           path.c_str(),
                           entry.fileInfo.path.c_str(),
                           entry.fileInfo.mediaType.c_str(),
                           entry.fileInfo.size,
                           entry.tags.title.c_str(),
                           entry.tags.album.artist.c_str(),
                           entry.tags.album.title.c_str(),
                           entry.tags.comment.c_str(),
                           entry.tags.genre.c_str(),
                           entry.tags.year,
                           entry.tags.track,
                           entry.audioProperties.songLength,
                           entry.audioProperties.bitrate,
                           entry.audioProperties.sampleRate,
                           entry.audioProperties.channels,
                           path.c_str());
    }

    TableRow MultimediaFilesTable::getById(uint32_t id)
    {
        auto retQuery = db->query("SELECT * FROM files WHERE _id=" u32_ ";", id);

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

        return CreateTableRow(*retQuery);
    }

    TableRow MultimediaFilesTable::getByPath(std::string path)
    {
        auto retQuery = db->query("SELECT * FROM files WHERE path=" str_ ";", path.c_str());

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

        return CreateTableRow(*retQuery);
    }

    std::vector<TableRow> MultimediaFilesTable::getLimitOffset(uint32_t offset, uint32_t limit)
    {
        auto retQuery =
            db->query("SELECT * from files ORDER BY title ASC LIMIT " u32_ " OFFSET " u32_ ";", limit, offset);

        return retQueryUnpack(std::move(retQuery));
    }

    auto MultimediaFilesTable::getArtistsLimitOffset(uint32_t offset, uint32_t limit) -> std::vector<Artist>
    {
        auto retQuery = db->query(
            "SELECT DISTINCT artist from files ORDER BY artist ASC LIMIT " u32_ " OFFSET " u32_ ";", limit, offset);

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

        std::vector<Artist> outVector;

        do {
            outVector.push_back((*retQuery)[0].getString()); // artist
        } while (retQuery->nextRow());
        return outVector;
    }

    std::vector<TableRow> MultimediaFilesTable::getLimitOffsetByField(uint32_t offset,
                                                                      uint32_t limit,
                                                                      TableFields field,
                                                                      const char *str)
    {
        std::unique_ptr<QueryResult> retQuery = nullptr;
        const auto &fieldName                 = getFieldName(field);

        if (fieldName.empty() || str == nullptr) {
            return {};
        }

        retQuery = db->query("SELECT * FROM files WHERE %q=" str_ " ORDER BY title ASC LIMIT " u32_ " OFFSET " u32_ ";",
                             fieldName.c_str(),
                             str,
                             limit,
                             offset);

        return retQueryUnpack(std::move(retQuery));
    }

    uint32_t MultimediaFilesTable::count()
    {
        auto queryRet = db->query("SELECT COUNT(*) FROM files;");
        if (!queryRet || queryRet->getRowCount() == 0) {
            return 0;
        }

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

    uint32_t MultimediaFilesTable::countArtists()
    {
        auto queryRet = db->query("SELECT COUNT (DISTINCT artist) FROM files;");
        if (!queryRet || queryRet->getRowCount() == 0) {
            return 0;
        }

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

    auto MultimediaFilesTable::getAlbumsLimitOffset(uint32_t offset, uint32_t limit) -> std::vector<Album>
    {
        auto retQuery =
            db->query("SELECT DISTINCT artist,album from files ORDER BY album ASC LIMIT " u32_ " OFFSET " u32_ ";",
                      limit,
                      offset);

        if ((retQuery == nullptr) || (retQuery->getRowCount() < 2)) {
            return {};
        }

        std::vector<Album> outVector;

        do {
            outVector.push_back({.artist = (*retQuery)[0].getString(), .title = (*retQuery)[1].getString()});
        } while (retQuery->nextRow());
        return outVector;
    }

    uint32_t MultimediaFilesTable::countAlbums()
    {
        auto queryRet = db->query("SELECT COUNT(*) FROM"
                                  "(SELECT DISTINCT artist,album from files);");
        if (!queryRet || queryRet->getRowCount() == 0) {
            return 0;
        }

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

    uint32_t MultimediaFilesTable::countByFieldId(const char *field, uint32_t id)
    {
        if (field == nullptr) {
            return 0;
        }

        auto queryRet = db->query("SELECT COUNT(*) FROM files WHERE " str_ "=" u32_ ";", field, id);
        if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
            return 0;
        }

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

    std::string MultimediaFilesTable::getFieldName(TableFields field)
    {
        return utils::enumToString(field);
    }

    auto MultimediaFilesTable::getLimitOffset(const Artist &artist, uint32_t offset, uint32_t limit)
        -> std::vector<TableRow>
    {
        return getLimitOffsetByField(offset, limit, TableFields::artist, artist.c_str());
    }

    auto MultimediaFilesTable::count(const Artist &artist) -> uint32_t
    {
        auto queryRet = db->query("SELECT COUNT(*) FROM files WHERE artist=" str_ " ;", artist.c_str());
        if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
            return 0;
        }

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

    auto MultimediaFilesTable::getLimitOffset(const Album &album, uint32_t offset, uint32_t limit)
        -> std::vector<TableRow>
    {
        std::unique_ptr<QueryResult> retQuery = db->query("SELECT * FROM files WHERE artist=" str_ " AND album=" str_
                                                          " ORDER BY title ASC LIMIT " u32_ " OFFSET " u32_ ";",
                                                          album.artist.c_str(),
                                                          album.title.c_str(),
                                                          limit,
                                                          offset);

        return retQueryUnpack(std::move(retQuery));
    }

    auto MultimediaFilesTable::count(const Album &album) -> uint32_t
    {
        auto queryRet = db->query("SELECT COUNT(*) FROM files WHERE artist=" str_ " AND album=" str_ ";",
                                  album.artist.c_str(),
                                  album.title.c_str());
        if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
            return 0;
        }

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

    auto MultimediaFilesTable::getLimitOffsetByPaths(const std::vector<std::string> &paths,
                                                     std::uint32_t offset,
                                                     std::uint32_t limit,
                                                     SortingBy sorting) -> std::vector<TableRow>
    {
        const std::string query = "SELECT * FROM files WHERE " + constructMatchPattern(paths) + " ORDER BY " +
                                  getSorting(sorting) + " LIMIT " + std::to_string(limit) + " OFFSET " +
                                  std::to_string(offset) + ";";
        std::unique_ptr<QueryResult> retQuery = db->query(query.c_str());
        return retQueryUnpack(std::move(retQuery));
    }

    auto MultimediaFilesTable::count(const std::vector<std::string> &paths) -> uint32_t
    {
        const std::string query = "SELECT COUNT(*) FROM files WHERE " + constructMatchPattern(paths) + ";";
        const auto retQuery     = db->query(query.c_str());

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

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

    auto MultimediaFilesTable::getOffsetOfSortedRecordByPath(const std::string &folderPath,
                                                             const std::string &recordPath,
                                                             SortingBy sorting) -> SortedRecord
    {
        std::unique_ptr<QueryResult> retQuery =
            db->query("SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY %q ) offset, "
                      " path FROM files WHERE path LIKE '%q%%' ) WHERE path='%q'",
                      getSorting(sorting).c_str(),
                      folderPath.c_str(),
                      recordPath.c_str());
        if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
            return SortedRecord{.path = recordPath, .offset = 0};
        }
        return SortedRecord{.path = (*retQuery)[1].getString(), .offset = (*retQuery)[0].getUInt32()};
    }
} // namespace db::multimedia_files