Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | 2x 2x 2x 2x 2x 2x 6x 2x 2x 2x 2x 6x 6x 38x 6x 6x 6x 6x 6x 6x 6x 6x 6x 6x 6x 6x 1x 6x 6x 6x 6x 6x 6x 6x 6x 6x 6x 6x 5x 4x 1x 6x 6x 6x 6x 6x 6x 6x 6x 6x 4x 4x 4x 4x 4x 6x 6x 6x 6x 6x 6x 3x 2x 1x | // src/services/db/gamification.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db';
import { handleDbError } from './errors.db';
import type { Logger } from 'pino';
import { Achievement, UserAchievement, LeaderboardUser } from '../../types';
export class GamificationRepository {
// The repository only needs an object with a `query` method, matching the Pool/PoolClient interface.
// Using `Pick` makes this dependency explicit and simplifies testing by reducing the mock surface.
private db: Pick<Pool | PoolClient, 'query'>;
constructor(db: Pick<Pool | PoolClient, 'query'> = getPool()) {
this.db = db;
}
/**
* Retrieves the master list of all available achievements.
* @returns A promise that resolves to an array of Achievement objects.
*/
async getAllAchievements(logger: Logger): Promise<Achievement[]> {
try {
const res = await this.db.query<Achievement>(
'SELECT * FROM public.achievements ORDER BY points_value ASC, name ASC',
);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getAllAchievements',
{},
{
defaultMessage: 'Failed to retrieve achievements.',
},
);
}
}
/**
* Retrieves all achievements earned by a specific user.
* Joins with the achievements table to include details like name and description.
* @param userId The UUID of the user.
* @returns A promise that resolves to an array of Achievement objects earned by the user.
*/
async getUserAchievements(
userId: string,
logger: Logger,
): Promise<(UserAchievement & Achievement)[]> {
try {
const query = `
SELECT
ua.user_id,
ua.achievement_id,
ua.achieved_at,
a.name,
a.description,
a.icon,
a.points_value,
a.created_at
FROM public.user_achievements ua
JOIN public.achievements a ON ua.achievement_id = a.achievement_id
WHERE ua.user_id = $1
ORDER BY ua.achieved_at DESC;
`;
const res = await this.db.query<UserAchievement & Achievement>(query, [userId]);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getUserAchievements',
{ userId },
{
defaultMessage: 'Failed to retrieve user achievements.',
},
);
}
}
/**
* Manually awards a specific achievement to a user.
* This calls a database function that handles the logic of checking if the user
* already has the achievement and awarding points if they don't.
* @param userId The UUID of the user to award the achievement to.
* @param achievementName The name of the achievement to award.
* @returns A promise that resolves when the operation is complete.
*/
async awardAchievement(userId: string, achievementName: string, logger: Logger): Promise<void> {
try {
await this.db.query('SELECT public.award_achievement($1, $2)', [userId, achievementName]);
} catch (error) {
handleDbError(
error,
logger,
'Database error in awardAchievement',
{ userId, achievementName },
{
fkMessage: 'The specified user or achievement does not exist.',
defaultMessage: 'Failed to award achievement.',
},
);
}
}
/**
* Retrieves a leaderboard of users with the most points.
* @param limit The number of users to return.
* @returns A promise that resolves to an array of leaderboard user objects.
*/
async getLeaderboard(limit: number, logger: Logger): Promise<LeaderboardUser[]> {
try {
const query = `
SELECT
user_id,
full_name,
avatar_url,
points,
RANK() OVER (ORDER BY points DESC) as rank
FROM public.profiles
ORDER BY points DESC, full_name ASC
LIMIT $1;
`;
const res = await this.db.query<LeaderboardUser>(query, [limit]);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getLeaderboard',
{ limit },
{
defaultMessage: 'Failed to retrieve leaderboard.',
},
);
}
}
}
|