120 lines
3.0 KiB
JavaScript
120 lines
3.0 KiB
JavaScript
function getProfileStats({ db, userId }) {
|
|
if (!userId) {
|
|
return { stats: [] };
|
|
}
|
|
const row = db
|
|
.prepare(
|
|
"SELECT COUNT(*) AS total FROM quotes WHERE quoter_user_id = ? AND hidden = 0 AND archived = 0"
|
|
)
|
|
.get(userId);
|
|
return {
|
|
stats: [
|
|
{
|
|
label: "Quotes made",
|
|
value: row?.total || 0
|
|
}
|
|
]
|
|
};
|
|
}
|
|
|
|
function getLeaderboards({ db, limit }) {
|
|
const totalQuotes = getQuoteTotals(db);
|
|
const topQuoters = getQuoteLeaders(db, limit);
|
|
const topGames = getQuoteGameLeaders(db, limit);
|
|
return {
|
|
boards: [
|
|
{
|
|
id: "total",
|
|
title: "Total quotes",
|
|
description: "Total quotes recorded.",
|
|
rowType: "text",
|
|
valueLabel: "Quotes",
|
|
rows: totalQuotes > 0 ? [{ label: "All quotes", value: totalQuotes }] : [],
|
|
emptyMessage: "No quotes recorded yet.",
|
|
topId: "quotes",
|
|
topAliases: ["quote", "totalquotes"]
|
|
},
|
|
{
|
|
id: "quoters",
|
|
title: "Top quoters",
|
|
description: "Users who created the most quotes.",
|
|
rowType: "user",
|
|
valueLabel: "Quotes",
|
|
rows: topQuoters,
|
|
emptyMessage: "No quotes recorded yet.",
|
|
topId: "quoter",
|
|
topAliases: ["quoters", "quotesmade"]
|
|
},
|
|
{
|
|
id: "games",
|
|
title: "Top quoted games",
|
|
description: "Games mentioned most in quotes.",
|
|
rowType: "game",
|
|
valueLabel: "Quotes",
|
|
rows: topGames,
|
|
emptyMessage: "No quoted games recorded yet.",
|
|
topId: "games",
|
|
topAliases: ["game", "quotegames"],
|
|
topOverride: true
|
|
}
|
|
]
|
|
};
|
|
}
|
|
|
|
function getQuoteTotals(db) {
|
|
const row = db
|
|
.prepare(
|
|
"SELECT COUNT(*) AS total FROM quotes WHERE hidden = 0 AND archived = 0"
|
|
)
|
|
.get();
|
|
return row?.total || 0;
|
|
}
|
|
|
|
function getQuoteLeaders(db, limit = 10) {
|
|
if (!hasColumn(db, "quotes", "quoter_user_id")) {
|
|
return [];
|
|
}
|
|
return db
|
|
.prepare(
|
|
"SELECT user_profiles.internal_username AS username, COUNT(*) AS value " +
|
|
"FROM quotes " +
|
|
"JOIN user_profiles ON user_profiles.id = quotes.quoter_user_id " +
|
|
"WHERE quotes.hidden = 0 AND quotes.archived = 0 " +
|
|
"GROUP BY quotes.quoter_user_id " +
|
|
"ORDER BY value DESC LIMIT ?"
|
|
)
|
|
.all(limit);
|
|
}
|
|
|
|
function getQuoteGameLeaders(db, limit = 10) {
|
|
return db
|
|
.prepare(
|
|
"SELECT game_name AS label, COUNT(*) AS value " +
|
|
"FROM quotes " +
|
|
"WHERE hidden = 0 AND archived = 0 AND game_name IS NOT NULL AND game_name != '' " +
|
|
"GROUP BY game_name " +
|
|
"ORDER BY value DESC LIMIT ?"
|
|
)
|
|
.all(limit);
|
|
}
|
|
|
|
function hasColumn(db, table, column) {
|
|
try {
|
|
const columns = db
|
|
.prepare(`PRAGMA table_info(${table})`)
|
|
.all()
|
|
.map((entry) => entry.name);
|
|
return columns.includes(column);
|
|
} catch {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
module.exports = {
|
|
getProfileStats,
|
|
getLeaderboards,
|
|
getQuoteTotals,
|
|
getQuoteLeaders,
|
|
getQuoteGameLeaders
|
|
};
|