Skip to content

Xbox Cloud Gaming app for PS5 Homebrew

C
39.4 KB
Raw Blame
// SPDX-License-Identifier: GPL-3.0-or-later
// Copyright (C) 2026 RafaelNGP
#include "trophy/trophy_db.h"
#include "platform/platform.h"
#include "util/json.h"
#include "util/log.h"

#include <sqlite3.h>
#include <cstdio>
#include <cstring>
#include <map>
#include <memory>
#include <string>
#include <sys/stat.h>
#include <unistd.h>
#include <vector>

namespace xc::trophy {

namespace {

uint32_t fnv1a(const std::string& str) {
    uint32_t hash = 2166136261u;
    for (char c : str) {
        hash ^= static_cast<uint8_t>(c);
        hash *= 16777619u;
    }
    return hash;
}

struct ColumnVal {
    int type = SQLITE_NULL;
    int64_t intVal = 0;
    double floatVal = 0.0;
    std::string strVal;
    std::vector<uint8_t> blobVal;
};

void backupIfMissing(const std::string& path) {
    std::string bak = path + ".bak";
    struct stat stBak{};
    if (::stat(bak.c_str(), &stBak) == 0 && stBak.st_size > 0) {
        return;
    }
    std::string data;
    if (platform::readFile(path, data) && !data.empty()) {
        platform::writeFileAtomic(bak, data);
        ::chmod(bak.c_str(), 0666);
        XC_LOGI("trophy db: backed up %s to %s (%zu bytes)", path.c_str(), bak.c_str(), data.size());
    }
}

std::string buildAppInfoJson(const std::string& originalJson, const TitleDbInfo& info) {
    auto j = json::parse(originalJson);
    json::Value out = (j && j->isObject()) ? std::move(*j) : json::Value::object();
    out.set("TITLE_ID", info.titleId);
    out.set("TITLE", info.titleName);
    out.set("CONTENT_ID", info.contentId);
    out.set("CONCEPT_ID", static_cast<int64_t>(info.conceptId));
    out.set("APP_TYPE", static_cast<int64_t>(1));
    out.set("ATTRIBUTE3", static_cast<int64_t>(1835012));
    out.set("DISPLAYLOCATION", static_cast<int64_t>(0));
    out.set("DEEPLINK_URI", "psgm:play?id=" + info.titleId);
    out.set("HUBAPP_URI", "pshome:gamehub?titleId=" + info.titleId);
    out.set("_metadata_path", info.metaDataPath);
    out.set("_org_path", "/user/app/" + info.titleId);
    out.set("_local_concept_id", info.localConceptId);

    static const char* kTitleLocales[] = {
        "TITLE_AR", "TITLE_CS", "TITLE_DA", "TITLE_DE", "TITLE_EL", "TITLE_EN",
        "TITLE_EN_GB", "TITLE_EN_US", "TITLE_ES", "TITLE_ES_419", "TITLE_FI",
        "TITLE_FR", "TITLE_FR_CA", "TITLE_HU", "TITLE_IT", "TITLE_JA",
        "TITLE_KO", "TITLE_NL", "TITLE_NO", "TITLE_PL", "TITLE_PT", "TITLE_PT_BR",
        "TITLE_RO", "TITLE_RU", "TITLE_SV", "TITLE_TH", "TITLE_TR", "TITLE_VI",
        "TITLE_ZH_CN", "TITLE_ZH_TW"
    };
    for (const char* k : kTitleLocales) {
        out.set(k, info.titleName);
    }
    return out.dump();
}

inline void updateCol(std::map<std::string, ColumnVal>& row, const std::string& col, const ColumnVal& val) {
    auto it = row.find(col);
    if (it != row.end()) {
        it->second = val;
    }
}


void setupDbPragmas(sqlite3* db) {
    if (!db) return;
    sqlite3_busy_timeout(db, 5000);
    sqlite3_exec(db, "PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY;", nullptr, nullptr, nullptr);
}

bool executeInsertRow(sqlite3* db, const std::string& tableName, const std::map<std::string, ColumnVal>& row) {
    if (row.empty()) return false;
    std::string sql = "INSERT OR REPLACE INTO " + tableName + " (";
    std::string ph = " VALUES (";
    bool first = true;
    for (const auto& [col, _] : row) {
        if (!first) { sql += ", "; ph += ", "; }
        sql += col;
        ph += "?";
        first = false;
    }
    sql += ")" + ph + ");";

    sqlite3_stmt* stmt = nullptr;
    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        XC_LOGW("trophy db: prepare insert failed for %s: %s", tableName.c_str(), sqlite3_errmsg(db));
        return false;
    }

    int idx = 1;
    for (const auto& [col, val] : row) {
        if (val.type == SQLITE_INTEGER) {
            sqlite3_bind_int64(stmt, idx, val.intVal);
        } else if (val.type == SQLITE_FLOAT) {
            sqlite3_bind_double(stmt, idx, val.floatVal);
        } else if (val.type == SQLITE_TEXT) {
            sqlite3_bind_text(stmt, idx, val.strVal.c_str(), -1, SQLITE_TRANSIENT);
        } else if (val.type == SQLITE_BLOB) {
            sqlite3_bind_blob(stmt, idx, val.blobVal.data(), static_cast<int>(val.blobVal.size()), SQLITE_TRANSIENT);
        } else {
            sqlite3_bind_null(stmt, idx);
        }
        idx++;
    }

    int rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    if (rc != SQLITE_DONE) {
        XC_LOGW("trophy db: insert failed into %s: %s", tableName.c_str(), sqlite3_errmsg(db));
        return false;
    }
    return true;
}

std::map<std::string, ColumnVal> readCurrentRow(sqlite3_stmt* stmt) {
    std::map<std::string, ColumnVal> row;
    int cols = sqlite3_column_count(stmt);
    for (int i = 0; i < cols; ++i) {
        const char* name = sqlite3_column_name(stmt, i);
        if (!name) continue;
        int type = sqlite3_column_type(stmt, i);
        ColumnVal cv;
        cv.type = type;
        if (type == SQLITE_INTEGER) {
            cv.intVal = sqlite3_column_int64(stmt, i);
        } else if (type == SQLITE_FLOAT) {
            cv.floatVal = sqlite3_column_double(stmt, i);
        } else if (type == SQLITE_TEXT) {
            const char* txt = reinterpret_cast<const char*>(sqlite3_column_text(stmt, i));
            if (txt) cv.strVal = txt;
        } else if (type == SQLITE_BLOB) {
            const uint8_t* b = reinterpret_cast<const uint8_t*>(sqlite3_column_blob(stmt, i));
            int len = sqlite3_column_bytes(stmt, i);
            if (b && len > 0) cv.blobVal.assign(b, b + len);
        }
        row[name] = std::move(cv);
    }
    return row;
}

}  // namespace

TitleDbInfo makeTitleDbInfo(const std::string& titleId, const std::string& titleName,
                            const std::string& contentId) {
    TitleDbInfo info;
    info.titleId = titleId;
    info.titleName = titleName.empty() ? ("Game " + titleId) : titleName;

    if (titleId == "PPSA01922") {
        info.contentId = "UP1477-PPSA01922_00-FORTNITECORE0001";
        info.conceptId = 210118;
        info.localConceptId = "cid:scp:00000000000334c6";
    } else if (titleId == "PPSA03350") {
        info.contentId = "UP4433-PPSA03350_00-MINECRAFTPS50001";
        info.conceptId = 10001479;
        info.localConceptId = "cid:scp:000000000098a83e";
    } else if (titleId == "PPSA16155") {
        info.contentId = "UP1477-PPSA16155_00-SEAOFTHEIVES0001";
        info.conceptId = 210119;
        info.localConceptId = "cid:scp:00000000000334c7";
    } else {
        uint32_t h = fnv1a(titleId);
        info.conceptId = 200000 + (h % 700000);
        char hexBuf[32];
        snprintf(hexBuf, sizeof(hexBuf), "cid:scp:00000000%08x", h);
        info.localConceptId = hexBuf;
        if (!contentId.empty()) {
            info.contentId = contentId;
        } else {
            info.contentId = "UP0001-" + titleId + "_00-0000000000000000";
        }
    }

    info.metaDataPath = "/user/app/" + titleId + "/sce_sys";
    info.icon0Path = "/user/app/" + titleId + "/sce_sys/icon0.png";
    info.pic0Path = "/user/app/" + titleId + "/sce_sys/pic0.png";
    return info;
}

bool isRetailInstalledOnConsole(const std::string& sonyTitleId) {
    if (sonyTitleId.empty() || sonyTitleId == "PPSA99810" || sonyTitleId == "PPSA22520") return true;
    struct stat st{};
    if (::stat(("/user/app/" + sonyTitleId + "/app.pkg").c_str(), &st) == 0) return true;
    if (::stat(("/mnt/ext0/user/app/" + sonyTitleId + "/app.pkg").c_str(), &st) == 0) return true;
    if (::stat(("/mnt/ext1/user/app/" + sonyTitleId + "/app.pkg").c_str(), &st) == 0) return true;
    std::string paramJson;
    if (platform::readFile("/system_data/priv/appmeta/" + sonyTitleId + "/param.json", paramJson) && !paramJson.empty()) {
        if (paramJson.find("2026-10-31 00:00:00") == std::string::npos) {
            return true;
        }
    }
    return false;
}

bool registerTitleInDatabase(const TitleDbInfo& info,
                             const std::string& appDbPath,
                             const std::string& appinfoDbPath) {
    if (info.titleId.empty() || info.titleId == "PPSA99810") {
        return false;
    }
    if (appDbPath.empty() && isRetailInstalledOnConsole(info.titleId)) {
        XC_LOGI("trophy db: preserving retail installed title %s in app.db", info.titleId.c_str());
        return true;
    }

    std::string appDb = appDbPath.empty() ? "/system_data/priv/mms/app.db" : appDbPath;
    std::string appinfoDb = appinfoDbPath.empty() ? "/system_data/priv/mms/appinfo.db" : appinfoDbPath;

    struct stat stApp{};
    if (::stat(appDb.c_str(), &stApp) != 0) {
        XC_LOGI("trophy db: %s not accessible (stat failed); skipping DB registration", appDb.c_str());
        return false;
    }

    backupIfMissing(appDb);
    backupIfMissing(appinfoDb);

    // 1. Process app.db
    sqlite3* db = nullptr;
    if (sqlite3_open_v2(appDb.c_str(), &db, SQLITE_OPEN_READWRITE, nullptr) != SQLITE_OK) {
        XC_LOGW("trophy db: failed to open %s: %s", appDb.c_str(), db ? sqlite3_errmsg(db) : "unknown");
        if (db) sqlite3_close(db);
        return false;
    }
    setupDbPragmas(db);
    if (sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr) != SQLITE_OK) {
        XC_LOGW("trophy db: failed to begin transaction on %s: %s", appDb.c_str(), sqlite3_errmsg(db));
        sqlite3_close(db);
        return false;
    }

    // 1a. tbl_contentinfo
    bool contentExists = false;
    int existingDispLocation = 0;
    {
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(db, "SELECT dispLocation FROM tbl_contentinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                contentExists = true;
                existingDispLocation = sqlite3_column_int(stmt, 0);
            }
            sqlite3_finalize(stmt);
        }
    }
    if (contentExists && existingDispLocation != 0) {
        sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
        sqlite3_close(db);
        XC_LOGI("trophy db: preserving retail installed title %s (dispLocation=%d) in app.db",
                info.titleId.c_str(), existingDispLocation);
        return true;
    }

    if (!contentExists) {
        // Find model row
        std::map<std::string, ColumnVal> modelRow;
        sqlite3_stmt* stmt = nullptr;
        const char* q = "SELECT * FROM tbl_contentinfo WHERE appType = 1 ORDER BY (CASE WHEN titleId = 'PPSA22520' THEN 0 WHEN titleId = 'PPSA01922' THEN 1 ELSE 2 END) LIMIT 1;";
        if (sqlite3_prepare_v2(db, q, -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                modelRow = readCurrentRow(stmt);
            }
            sqlite3_finalize(stmt);
        }
        if (modelRow.empty()) {
            // Fallback to any row
            if (sqlite3_prepare_v2(db, "SELECT * FROM tbl_contentinfo LIMIT 1;", -1, &stmt, nullptr) == SQLITE_OK) {
                if (sqlite3_step(stmt) == SQLITE_ROW) {
                    modelRow = readCurrentRow(stmt);
                }
                sqlite3_finalize(stmt);
            }
        }

        if (modelRow.empty()) {
            modelRow["titleId"] = {SQLITE_TEXT, 0, 0.0, info.titleId, {}};
            modelRow["titleName"] = {SQLITE_TEXT, 0, 0.0, info.titleName, {}};
            modelRow["contentId"] = {SQLITE_TEXT, 0, 0.0, info.contentId, {}};
            modelRow["conceptId"] = {SQLITE_INTEGER, static_cast<int64_t>(info.conceptId), 0.0, "", {}};
            modelRow["localConceptId"] = {SQLITE_TEXT, 0, 0.0, info.localConceptId, {}};
            modelRow["metaDataPath"] = {SQLITE_TEXT, 0, 0.0, info.metaDataPath, {}};
            modelRow["dispLocation"] = {SQLITE_INTEGER, 0, 0.0, "", {}};
            modelRow["appType"] = {SQLITE_INTEGER, 1, 0.0, "", {}};
            modelRow["uiCategory"] = {SQLITE_TEXT, 0, 0.0, "game", {}};
            modelRow["icon0Info"] = {SQLITE_TEXT, 0, 0.0, info.icon0Path, {}};
            modelRow["pic0Info"] = {SQLITE_TEXT, 0, 0.0, info.pic0Path, {}};
            modelRow["snd0Info"] = {SQLITE_TEXT, 0, 0.0, "/user/appmeta/" + info.titleId + "/snd0.at9", {}};
            modelRow["pprDeeplinkUri"] = {SQLITE_TEXT, 0, 0.0, "psgm:play?id=" + info.titleId, {}};
            modelRow["pprHubAppUri"] = {SQLITE_TEXT, 0, 0.0, "pshome:gamehub?titleId=" + info.titleId, {}};
            modelRow["AppInfoJson"] = {SQLITE_TEXT, 0, 0.0, buildAppInfoJson("{}", info), {}};
            executeInsertRow(db, "tbl_contentinfo", modelRow);
            XC_LOGI("trophy db: inserted synthetic %s into tbl_contentinfo", info.titleId.c_str());
        } else {
            updateCol(modelRow, "titleId", {SQLITE_TEXT, 0, 0.0, info.titleId, {}});
            updateCol(modelRow, "contentId", {SQLITE_TEXT, 0, 0.0, info.contentId, {}});
            updateCol(modelRow, "titleName", {SQLITE_TEXT, 0, 0.0, info.titleName, {}});
            updateCol(modelRow, "metaDataPath", {SQLITE_TEXT, 0, 0.0, info.metaDataPath, {}});
            updateCol(modelRow, "conceptId", {SQLITE_INTEGER, static_cast<int64_t>(info.conceptId), 0.0, "", {}});
            updateCol(modelRow, "localConceptId", {SQLITE_TEXT, 0, 0.0, info.localConceptId, {}});
            updateCol(modelRow, "pprDeeplinkUri", {SQLITE_TEXT, 0, 0.0, "psgm:play?id=" + info.titleId, {}});
            updateCol(modelRow, "pprHubAppUri", {SQLITE_TEXT, 0, 0.0, "pshome:gamehub?titleId=" + info.titleId, {}});
            updateCol(modelRow, "snd0Info", {SQLITE_TEXT, 0, 0.0, "/user/appmeta/" + info.titleId + "/snd0.at9", {}});
            updateCol(modelRow, "icon0Info", {SQLITE_TEXT, 0, 0.0, info.icon0Path, {}});
            updateCol(modelRow, "pic0Info", {SQLITE_TEXT, 0, 0.0, info.pic0Path, {}});
            updateCol(modelRow, "appType", {SQLITE_INTEGER, 1, 0.0, "", {}});
            updateCol(modelRow, "dispLocation", {SQLITE_INTEGER, 0, 0.0, "", {}});
            updateCol(modelRow, "uiCategory", {SQLITE_TEXT, 0, 0.0, "game", {}});
            if (modelRow.count("AppInfoJson")) {
                modelRow["AppInfoJson"] = {SQLITE_TEXT, 0, 0.0, buildAppInfoJson(modelRow["AppInfoJson"].strVal, info), {}};
            }
            executeInsertRow(db, "tbl_contentinfo", modelRow);
            XC_LOGI("trophy db: inserted %s into tbl_contentinfo", info.titleId.c_str());
        }
    } else {
        // Update existing row
        std::string existingJson;
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(db, "SELECT AppInfoJson FROM tbl_contentinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* t = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                if (t) existingJson = t;
            }
            sqlite3_finalize(stmt);
        }
        std::string updatedJson = buildAppInfoJson(existingJson, info);
        const char* uSql = "UPDATE tbl_contentinfo SET appType = 1, dispLocation = 0, titleName = ?, contentId = ?, conceptId = ?, AppInfoJson = ? WHERE titleId = ?;";
        if (sqlite3_prepare_v2(db, uSql, -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, info.titleName.c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, info.contentId.c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_int64(stmt, 3, static_cast<int64_t>(info.conceptId));
            sqlite3_bind_text(stmt, 4, updatedJson.c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 5, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
            XC_LOGI("trophy db: updated %s in tbl_contentinfo", info.titleId.c_str());
        }
    }

    // 1b. tbl_iconinfo_<uid>
    std::vector<std::string> iconTables;
    {
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'tbl_iconinfo_%';", -1, &stmt, nullptr) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* n = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                if (n) iconTables.push_back(n);
            }
            sqlite3_finalize(stmt);
        }
    }

    for (const auto& tbl : iconTables) {
        bool exists = false;
        sqlite3_stmt* stmt = nullptr;
        std::string q = "SELECT 1 FROM " + tbl + " WHERE titleId = ?;";
        if (sqlite3_prepare_v2(db, q.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) exists = true;
            sqlite3_finalize(stmt);
        }

        if (!exists) {
            std::map<std::string, ColumnVal> modelRow;
            std::string mq = "SELECT * FROM " + tbl + " ORDER BY (CASE WHEN titleId = 'PPSA22520' THEN 0 WHEN titleId = 'PPSA01922' THEN 1 ELSE 2 END) LIMIT 1;";
            if (sqlite3_prepare_v2(db, mq.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                if (sqlite3_step(stmt) == SQLITE_ROW) {
                    modelRow = readCurrentRow(stmt);
                }
                sqlite3_finalize(stmt);
            }
            if (!modelRow.empty()) {
                updateCol(modelRow, "titleId", {SQLITE_TEXT, 0, 0.0, info.titleId, {}});
                updateCol(modelRow, "titleName", {SQLITE_TEXT, 0, 0.0, info.titleName, {}});
                updateCol(modelRow, "contentId", {SQLITE_TEXT, 0, 0.0, info.contentId, {}});
                updateCol(modelRow, "conceptId", {SQLITE_INTEGER, static_cast<int64_t>(info.conceptId), 0.0, "", {}});
                updateCol(modelRow, "localConceptId", {SQLITE_TEXT, 0, 0.0, info.localConceptId, {}});
                updateCol(modelRow, "visible", {SQLITE_INTEGER, 0, 0.0, "", {}});
                updateCol(modelRow, "installStatus", {SQLITE_INTEGER, 2, 0.0, "", {}});
                updateCol(modelRow, "appDrmType", {SQLITE_INTEGER, 5, 0.0, "", {}});
                updateCol(modelRow, "deeplinkUri", {SQLITE_TEXT, 0, 0.0, "psgm:play?id=" + info.titleId, {}});
                updateCol(modelRow, "hubAppUri", {SQLITE_TEXT, 0, 0.0, "pshome:gamehub?titleId=" + info.titleId, {}});
                executeInsertRow(db, tbl, modelRow);
                XC_LOGI("trophy db: inserted %s into %s", info.titleId.c_str(), tbl.c_str());
            }
        } else {
            std::string uq = "UPDATE " + tbl + " SET conceptId = ?, localConceptId = ?, contentId = ?, titleName = ?, visible = 0, installStatus = 2, appDrmType = 5 WHERE titleId = ?;";
            if (sqlite3_prepare_v2(db, uq.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_int64(stmt, 1, static_cast<int64_t>(info.conceptId));
                sqlite3_bind_text(stmt, 2, info.localConceptId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 3, info.contentId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 4, info.titleName.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 5, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
        }
    }

    // 1c. tbl_concepticoninfo_<uid>
    std::vector<std::string> conceptTables;
    {
        sqlite3_stmt* stmt = nullptr;
        if (sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'tbl_concepticoninfo_%';", -1, &stmt, nullptr) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* n = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                if (n) conceptTables.push_back(n);
            }
            sqlite3_finalize(stmt);
        }
    }

    for (const auto& tbl : conceptTables) {
        bool exists = false;
        sqlite3_stmt* stmt = nullptr;
        std::string q = "SELECT 1 FROM " + tbl + " WHERE primaryTitleId = ?;";
        if (sqlite3_prepare_v2(db, q.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) exists = true;
            sqlite3_finalize(stmt);
        }

        if (!exists) {
            std::map<std::string, ColumnVal> modelRow;
            std::string mq = "SELECT * FROM " + tbl + " ORDER BY (CASE WHEN primaryTitleId = 'PPSA22520' THEN 0 WHEN primaryTitleId = 'PPSA01922' THEN 1 ELSE 2 END) LIMIT 1;";
            if (sqlite3_prepare_v2(db, mq.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                if (sqlite3_step(stmt) == SQLITE_ROW) {
                    modelRow = readCurrentRow(stmt);
                }
                sqlite3_finalize(stmt);
            }
            if (!modelRow.empty()) {
                updateCol(modelRow, "localConceptId", {SQLITE_TEXT, 0, 0.0, info.localConceptId, {}});
                updateCol(modelRow, "conceptId", {SQLITE_INTEGER, static_cast<int64_t>(info.conceptId), 0.0, "", {}});
                updateCol(modelRow, "conceptName", {SQLITE_TEXT, 0, 0.0, info.titleName, {}});
                updateCol(modelRow, "primaryTitleId", {SQLITE_TEXT, 0, 0.0, info.titleId, {}});
                updateCol(modelRow, "primaryTitleName", {SQLITE_TEXT, 0, 0.0, info.titleName, {}});
                updateCol(modelRow, "deeplinkUri", {SQLITE_TEXT, 0, 0.0, "psgm:play?id=" + info.titleId, {}});
                updateCol(modelRow, "hubAppUri", {SQLITE_TEXT, 0, 0.0, "pshome:gamehub?titleId=" + info.titleId, {}});
                updateCol(modelRow, "metaDataPath", {SQLITE_TEXT, 0, 0.0, info.metaDataPath, {}});
                updateCol(modelRow, "icon0Info", {SQLITE_TEXT, 0, 0.0, info.icon0Path, {}});
                updateCol(modelRow, "pic0Info", {SQLITE_TEXT, 0, 0.0, info.pic0Path, {}});
                updateCol(modelRow, "snd0Info", {SQLITE_TEXT, 0, 0.0, "/user/app/" + info.titleId + "/sce_sys/snd0.at9", {}});
                executeInsertRow(db, tbl, modelRow);
                XC_LOGI("trophy db: inserted %s into %s", info.titleId.c_str(), tbl.c_str());
            }
        } else {
            std::string uq = "UPDATE " + tbl + " SET conceptId = ?, conceptName = ?, primaryTitleName = ? WHERE primaryTitleId = ?;";
            if (sqlite3_prepare_v2(db, uq.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_int64(stmt, 1, static_cast<int64_t>(info.conceptId));
                sqlite3_bind_text(stmt, 2, info.titleName.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 3, info.titleName.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 4, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
        }
    }

    sqlite3_exec(db, "COMMIT;", nullptr, nullptr, nullptr);
    sqlite3_wal_checkpoint_v2(db, nullptr, SQLITE_CHECKPOINT_PASSIVE, nullptr, nullptr);
    sqlite3_close(db);

    // 2. Process appinfo.db
    struct stat stInfo{};
    if (::stat(appinfoDb.c_str(), &stInfo) == 0) {
        sqlite3* infoDb = nullptr;
        if (sqlite3_open_v2(appinfoDb.c_str(), &infoDb, SQLITE_OPEN_READWRITE, nullptr) == SQLITE_OK) {
            setupDbPragmas(infoDb);
            if (sqlite3_exec(infoDb, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr) != SQLITE_OK) {
                XC_LOGW("trophy db: failed to begin transaction on %s: %s", appinfoDb.c_str(), sqlite3_errmsg(infoDb));
                sqlite3_close(infoDb);
                return true;
            }

            int count = 0;
            sqlite3_stmt* stmt = nullptr;
            if (sqlite3_prepare_v2(infoDb, "SELECT COUNT(*) FROM tbl_appinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                if (sqlite3_step(stmt) == SQLITE_ROW) count = sqlite3_column_int(stmt, 0);
                sqlite3_finalize(stmt);
            }

            if (count == 0) {
                std::string modelTitle;
                // Query priority: PPSA22520, PPSA01922, any title with APP_TYPE=1, or any title at all
                const char* modelQueries[] = {
                    "SELECT titleId FROM tbl_appinfo WHERE titleId = 'PPSA22520' LIMIT 1;",
                    "SELECT titleId FROM tbl_appinfo WHERE titleId = 'PPSA01922' LIMIT 1;",
                    "SELECT titleId FROM tbl_appinfo WHERE key = 'APP_TYPE' AND val = '1' LIMIT 1;",
                    "SELECT titleId FROM tbl_appinfo LIMIT 1;"
                };
                for (const char* mq : modelQueries) {
                    sqlite3_stmt* s = nullptr;
                    if (sqlite3_prepare_v2(infoDb, mq, -1, &s, nullptr) == SQLITE_OK) {
                        if (sqlite3_step(s) == SQLITE_ROW) {
                            const char* t = reinterpret_cast<const char*>(sqlite3_column_text(s, 0));
                            if (t) modelTitle = t;
                        }
                        sqlite3_finalize(s);
                    }
                    if (!modelTitle.empty()) break;
                }

                struct AppInfoEntry {
                    int64_t metaDataId = 0;
                    std::string key;
                    std::string val;
                };
                std::vector<AppInfoEntry> entries;

                if (!modelTitle.empty()) {
                    if (sqlite3_prepare_v2(infoDb, "SELECT metaDataId, key, val FROM tbl_appinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
                        sqlite3_bind_text(stmt, 1, modelTitle.c_str(), -1, SQLITE_TRANSIENT);
                        while (sqlite3_step(stmt) == SQLITE_ROW) {
                            AppInfoEntry e;
                            e.metaDataId = sqlite3_column_int64(stmt, 0);
                            const char* k = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                            const char* v = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
                            if (k) e.key = k;
                            if (v) e.val = v;
                            entries.push_back(std::move(e));
                        }
                        sqlite3_finalize(stmt);
                    }
                }

                if (!entries.empty()) {
                    for (const auto& e : entries) {
                        std::string newVal = e.val;
                        if (e.key == "TITLE_ID") {
                            newVal = info.titleId;
                        } else if (e.key == "CONTENT_ID") {
                            newVal = info.contentId;
                        } else if (e.key == "CONCEPT_ID") {
                            newVal = std::to_string(info.conceptId);
                        } else if (e.key == "_local_concept_id") {
                            newVal = info.localConceptId;
                        } else if (e.key == "APP_TYPE") {
                            newVal = "1";
                        } else if (e.key == "ATTRIBUTE3") {
                            newVal = "1835012";
                        } else if (e.key == "DISPLAYLOCATION") {
                            newVal = "0";
                        } else if (e.key == "DEEPLINK_URI") {
                            newVal = "psgm:play?id=" + info.titleId;
                        } else if (e.key == "HUBAPP_URI") {
                            newVal = "pshome:gamehub?titleId=" + info.titleId;
                        } else if (e.key == "_metadata_path") {
                            newVal = info.metaDataPath;
                        } else if (e.key == "_org_path") {
                            newVal = "/user/app/" + info.titleId;
                        } else if (e.key == "icon0Info") {
                            newVal = info.icon0Path;
                        } else if (e.key == "pic0Info") {
                            newVal = info.pic0Path;
                        } else if (e.key == "TITLE" || (e.key.rfind("TITLE_", 0) == 0 && e.key != "TITLE_ID")) {
                            newVal = info.titleName;
                        } else if (!modelTitle.empty()) {
                            size_t pos = 0;
                            while ((pos = newVal.find(modelTitle, pos)) != std::string::npos) {
                                newVal.replace(pos, modelTitle.length(), info.titleId);
                                pos += info.titleId.length();
                            }
                        }

                        const char* iSql = "INSERT OR REPLACE INTO tbl_appinfo (titleId, metaDataId, key, val) VALUES (?, ?, ?, ?);";
                        if (sqlite3_prepare_v2(infoDb, iSql, -1, &stmt, nullptr) == SQLITE_OK) {
                            sqlite3_bind_text(stmt, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_bind_int64(stmt, 2, e.metaDataId);
                            sqlite3_bind_text(stmt, 3, e.key.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_bind_text(stmt, 4, newVal.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_step(stmt);
                            sqlite3_finalize(stmt);
                        }
                    }
                    XC_LOGI("trophy db: inserted %zu entries for %s into tbl_appinfo", entries.size(), info.titleId.c_str());
                } else {
                    auto insertEntry = [&](const std::string& key, const std::string& val) {
                        sqlite3_stmt* s = nullptr;
                        const char* iSql = "INSERT OR REPLACE INTO tbl_appinfo (titleId, metaDataId, key, val) VALUES (?, 0, ?, ?);";
                        if (sqlite3_prepare_v2(infoDb, iSql, -1, &s, nullptr) == SQLITE_OK) {
                            sqlite3_bind_text(s, 1, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_bind_text(s, 2, key.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_bind_text(s, 3, val.c_str(), -1, SQLITE_TRANSIENT);
                            sqlite3_step(s);
                            sqlite3_finalize(s);
                        }
                    };
                    insertEntry("TITLE_ID", info.titleId);
                    insertEntry("TITLE", info.titleName);
                    insertEntry("CONTENT_ID", info.contentId);
                    insertEntry("CONCEPT_ID", std::to_string(info.conceptId));
                    insertEntry("_local_concept_id", info.localConceptId);
                    insertEntry("APP_TYPE", "1");
                    insertEntry("ATTRIBUTE3", "1835012");
                    insertEntry("DISPLAYLOCATION", "0");
                    insertEntry("DEEPLINK_URI", "psgm:play?id=" + info.titleId);
                    insertEntry("HUBAPP_URI", "pshome:gamehub?titleId=" + info.titleId);
                    insertEntry("_metadata_path", info.metaDataPath);
                    insertEntry("_org_path", "/user/app/" + info.titleId);
                    insertEntry("icon0Info", info.icon0Path);
                    insertEntry("pic0Info", info.pic0Path);
                    static const char* kLocales[] = {
                        "TITLE_AR", "TITLE_CS", "TITLE_DA", "TITLE_DE", "TITLE_EL", "TITLE_EN",
                        "TITLE_EN_GB", "TITLE_EN_US", "TITLE_ES", "TITLE_ES_419", "TITLE_FI",
                        "TITLE_FR", "TITLE_FR_CA", "TITLE_HU", "TITLE_IT", "TITLE_JA",
                        "TITLE_KO", "TITLE_NL", "TITLE_NO", "TITLE_PL", "TITLE_PT", "TITLE_PT_BR",
                        "TITLE_RO", "TITLE_RU", "TITLE_SV", "TITLE_TH", "TITLE_TR", "TITLE_VI",
                        "TITLE_ZH_CN", "TITLE_ZH_TW"
                    };
                    for (const char* loc : kLocales) {
                        insertEntry(loc, info.titleName);
                    }
                    XC_LOGI("trophy db: synthesized default entries for %s into tbl_appinfo", info.titleId.c_str());
                }
            } else {
                auto updateVal = [&](const char* key, const std::string& val) {
                    sqlite3_stmt* s = nullptr;
                    if (sqlite3_prepare_v2(infoDb, "UPDATE tbl_appinfo SET val = ? WHERE titleId = ? AND key = ?;", -1, &s, nullptr) == SQLITE_OK) {
                        sqlite3_bind_text(s, 1, val.c_str(), -1, SQLITE_TRANSIENT);
                        sqlite3_bind_text(s, 2, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                        sqlite3_bind_text(s, 3, key, -1, SQLITE_TRANSIENT);
                        sqlite3_step(s);
                        sqlite3_finalize(s);
                    }
                };
                updateVal("CONTENT_ID", info.contentId);
                updateVal("CONCEPT_ID", std::to_string(info.conceptId));
                updateVal("TITLE_ID", info.titleId);
                updateVal("APP_TYPE", "1");
                updateVal("ATTRIBUTE3", "1835012");
                updateVal("_local_concept_id", info.localConceptId);
                updateVal("DISPLAYLOCATION", "0");
                updateVal("TITLE", info.titleName);
                updateVal("_metadata_path", info.metaDataPath);
                updateVal("_org_path", "/user/app/" + info.titleId);
                updateVal("icon0Info", info.icon0Path);
                updateVal("pic0Info", info.pic0Path);

                sqlite3_stmt* sLoc = nullptr;
                if (sqlite3_prepare_v2(infoDb, "UPDATE tbl_appinfo SET val = ? WHERE titleId = ? AND key LIKE 'TITLE_%' AND key != 'TITLE_ID';", -1, &sLoc, nullptr) == SQLITE_OK) {
                    sqlite3_bind_text(sLoc, 1, info.titleName.c_str(), -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(sLoc, 2, info.titleId.c_str(), -1, SQLITE_TRANSIENT);
                    sqlite3_step(sLoc);
                    sqlite3_finalize(sLoc);
                }
            }

            sqlite3_exec(infoDb, "COMMIT;", nullptr, nullptr, nullptr);
            sqlite3_wal_checkpoint_v2(infoDb, nullptr, SQLITE_CHECKPOINT_PASSIVE, nullptr, nullptr);
            sqlite3_close(infoDb);
        }
    }

    XC_LOGI("trophy db: registration completed successfully for %s (%s)", info.titleName.c_str(), info.titleId.c_str());
    return true;
}

bool removeTitleFromDatabase(const std::string& titleId,
                             const std::string& appDbPath,
                             const std::string& appinfoDbPath) {
    if (titleId.empty() || titleId == "PPSA99810" || titleId == "PPSA22520") {
        return false;
    }
    if (appDbPath.empty() && isRetailInstalledOnConsole(titleId)) {
        XC_LOGI("trophy db: preserving retail installed title %s during remove", titleId.c_str());
        return false;
    }

    std::string appDb = appDbPath.empty() ? "/system_data/priv/mms/app.db" : appDbPath;
    std::string appinfoDb = appinfoDbPath.empty() ? "/system_data/priv/mms/appinfo.db" : appinfoDbPath;

    struct stat stApp{};
    if (::stat(appDb.c_str(), &stApp) != 0) {
        return false;
    }

    backupIfMissing(appDb);
    backupIfMissing(appinfoDb);

    sqlite3* db = nullptr;
    if (sqlite3_open_v2(appDb.c_str(), &db, SQLITE_OPEN_READWRITE, nullptr) == SQLITE_OK) {
        setupDbPragmas(db);
        if (sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr) != SQLITE_OK) {
            XC_LOGW("trophy db: failed to begin transaction on %s: %s", appDb.c_str(), sqlite3_errmsg(db));
            sqlite3_close(db);
            return false;
        }

        // Never delete a retail installed game (dispLocation != 0)
        sqlite3_stmt* stmt = nullptr;
        int existingDispLocation = 0;
        if (sqlite3_prepare_v2(db, "SELECT dispLocation FROM tbl_contentinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, titleId.c_str(), -1, SQLITE_TRANSIENT);
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                existingDispLocation = sqlite3_column_int(stmt, 0);
            }
            sqlite3_finalize(stmt);
        }
        if (existingDispLocation != 0) {
            sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
            sqlite3_close(db);
            XC_LOGI("trophy db: refusing to remove retail installed title %s (dispLocation=%d)",
                    titleId.c_str(), existingDispLocation);
            return true;
        }

        // tbl_contentinfo
        if (sqlite3_prepare_v2(db, "DELETE FROM tbl_contentinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, titleId.c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
        }

        // tbl_iconinfo_%
        std::vector<std::string> iconTables;
        if (sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'tbl_iconinfo_%';", -1, &stmt, nullptr) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* n = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                if (n) iconTables.push_back(n);
            }
            sqlite3_finalize(stmt);
        }
        for (const auto& tbl : iconTables) {
            std::string q = "DELETE FROM " + tbl + " WHERE titleId = ?;";
            if (sqlite3_prepare_v2(db, q.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_text(stmt, 1, titleId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
        }

        // tbl_concepticoninfo_%
        std::vector<std::string> conceptTables;
        if (sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'tbl_concepticoninfo_%';", -1, &stmt, nullptr) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* n = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                if (n) conceptTables.push_back(n);
            }
            sqlite3_finalize(stmt);
        }
        for (const auto& tbl : conceptTables) {
            std::string q = "DELETE FROM " + tbl + " WHERE primaryTitleId = ?;";
            if (sqlite3_prepare_v2(db, q.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_text(stmt, 1, titleId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
        }

        sqlite3_exec(db, "COMMIT;", nullptr, nullptr, nullptr);
        sqlite3_wal_checkpoint_v2(db, nullptr, SQLITE_CHECKPOINT_PASSIVE, nullptr, nullptr);
        sqlite3_close(db);
        XC_LOGI("trophy db: removed %s from app.db", titleId.c_str());
    }

    struct stat stInfo{};
    if (::stat(appinfoDb.c_str(), &stInfo) == 0) {
        sqlite3* infoDb = nullptr;
        if (sqlite3_open_v2(appinfoDb.c_str(), &infoDb, SQLITE_OPEN_READWRITE, nullptr) == SQLITE_OK) {
            setupDbPragmas(infoDb);
            if (sqlite3_exec(infoDb, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr) != SQLITE_OK) {
                XC_LOGW("trophy db: failed to begin transaction on %s: %s", appinfoDb.c_str(), sqlite3_errmsg(infoDb));
                sqlite3_close(infoDb);
                return true;
            }
            sqlite3_stmt* stmt = nullptr;
            if (sqlite3_prepare_v2(infoDb, "DELETE FROM tbl_appinfo WHERE titleId = ?;", -1, &stmt, nullptr) == SQLITE_OK) {
                sqlite3_bind_text(stmt, 1, titleId.c_str(), -1, SQLITE_TRANSIENT);
                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
            sqlite3_exec(infoDb, "COMMIT;", nullptr, nullptr, nullptr);
            sqlite3_wal_checkpoint_v2(infoDb, nullptr, SQLITE_CHECKPOINT_PASSIVE, nullptr, nullptr);
            sqlite3_close(infoDb);
            XC_LOGI("trophy db: removed %s from appinfo.db", titleId.c_str());
        }
    }

    return true;
}

}  // namespace xc::trophy