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 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 | 2x 2x 2x 2x 2x 2x 2x 23x 23x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 23x 23x 23x 133x 23x 23x 23x 23x 23x 23x 23x 23x 23x 23x 23x 3x 2x 1x 23x 23x 23x 23x 23x 23x 3x 3x 2x 23x 23x 5x 5x 4x 2x 2x 23x 23x 23x 23x 23x 23x 23x 23x 23x 23x 5x 5x 4x 2x 2x 3x 23x 23x 23x 23x 23x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 3x 1x 3x 3x 3x 3x 3x 3x 1x 1x 1x 1x 1x 1x 1x 1x 3x 1x 2x 23x 23x 23x 1x 3x 3x 23x 23x 23x 23x 23x 23x 23x 23x 2x 1x 23x 23x 23x 23x 23x 23x 23x 23x 23x 3x 2x 1x 23x 23x 23x 23x 23x 23x 3x 3x 2x 1x 1x 2x 1x 1x 2x 2x 23x 23x 23x 23x 23x 23x 23x 23x 2x 1x 1x 23x 23x 23x 23x 23x 23x 23x 5x 5x 4x 2x 3x 2x 1x 4x 4x 4x 23x 23x 23x 23x 23x 2x 2x 1x 1x 3x 2x 23x 23x 23x 23x 23x 23x 23x 23x 23x 4x 4x 23x 23x 23x 23x 23x 23x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 21x 23x 23x 23x 23x 23x 2x 2x 1x 1x 1x 1x 1x 23x 23x 23x 23x 23x 23x 55x 55x 55x 18x 18x 18x 18x 18x 18x 18x 18x 18x 18x 18x 1x 23x 23x 23x 23x 23x 23x 23x 23x 5x 5x 4x 1x 23x 23x 23x 23x 23x 23x 23x 1x 53x 53x 23x 1x 1x 1x 1x 1x 1x 1x 1x 1x 1x 4x 4x 1x 1x 3x 2x 1x 1x 3x 1x 1x 23x 23x 23x 23x 23x 23x 23x 23x 23x 3x 1x 3x 1x 23x 23x 23x 23x 23x 2x 2x 2x 2x 1x 1x 2x 2x 1x 5x 5x 2x 2x 2x 2x 2x 2x 2x 2x 4x 2x 2x 3x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 3x 3x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 3x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x | // src/services/db/admin.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool, withTransaction } from './connection.db';
import { NotFoundError, handleDbError } from './errors.db';
import type { Logger } from 'pino';
import {
SuggestedCorrection,
MostFrequentSaleItem,
Recipe,
RecipeComment,
UnmatchedFlyerItem,
ActivityLogItem,
Receipt,
AdminUserView,
Profile,
Flyer,
} from '../../types';
export class AdminRepository {
// 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 all pending suggested corrections from the database.
* Joins with users and flyer_items to provide context for the admin.
* @returns A promise that resolves to an array of SuggestedCorrection objects.
*/
// prettier-ignore
async getSuggestedCorrections(logger: Logger): Promise<SuggestedCorrection[]> {
try {
const query = `
SELECT
sc.suggested_correction_id,
sc.flyer_item_id,
sc.user_id,
sc.correction_type,
sc.suggested_value,
sc.status,
sc.updated_at,
sc.created_at,
fi.item as flyer_item_name,
fi.price_display as flyer_item_price_display,
u.email as user_email
FROM public.suggested_corrections sc
JOIN public.flyer_items fi ON sc.flyer_item_id = fi.flyer_item_id
LEFT JOIN public.users u ON sc.user_id = u.user_id
WHERE sc.status = 'pending'
ORDER BY sc.created_at ASC;
`;
const res = await this.db.query<SuggestedCorrection>(query);
return res.rows;
} catch (error) {
handleDbError(error, logger, 'Database error in getSuggestedCorrections', {}, {
defaultMessage: 'Failed to retrieve suggested corrections.',
});
}
}
/**
* Approves a correction and applies the change to the corresponding flyer item.
* This function runs as a transaction to ensure data integrity.
* @param correctionId The ID of the correction to approve.
*/
// prettier-ignore
async approveCorrection(correctionId: number, logger: Logger): Promise<void> {
try {
// The database function `approve_correction` now contains all the logic.
// It finds the correction, applies the change, and updates the status in a single transaction.
// This simplifies the application code and keeps the business logic in the database.
await this.db.query('SELECT public.approve_correction($1)', [correctionId]);
logger.info(`Successfully approved and applied correction ID: ${correctionId}`);
} catch (error) {
handleDbError(error, logger, 'Database transaction error in approveCorrection', { correctionId }, {
fkMessage: 'The suggested master item ID does not exist.',
defaultMessage: 'Failed to approve correction.',
});
}
}
/**
* Rejects a correction by updating its status.
* @param correctionId The ID of the correction to reject.
*/
// prettier-ignore
async rejectCorrection(correctionId: number, logger: Logger): Promise<void> {
try {
const res = await this.db.query(
"UPDATE public.suggested_corrections SET status = 'rejected' WHERE suggested_correction_id = $1 AND status = 'pending' RETURNING suggested_correction_id",
[correctionId]
);
if (res.rowCount === 0) {
throw new NotFoundError(`Correction with ID ${correctionId} not found or not in 'pending' state.`);
}
logger.info(`Successfully rejected correction ID: ${correctionId}`);
} catch (error) {
if (error instanceof NotFoundError) throw error;
handleDbError(error, logger, 'Database error in rejectCorrection', { correctionId }, {
defaultMessage: 'Failed to reject correction.',
});
}
}
/**
* Updates the suggested value of a pending correction.
* @param correctionId The ID of the correction to update.
* @param newSuggestedValue The new value to set for the suggestion.
* @returns A promise that resolves to the updated SuggestedCorrection object.
*/
// prettier-ignore
async updateSuggestedCorrection(correctionId: number, newSuggestedValue: string, logger: Logger): Promise<SuggestedCorrection> {
try {
const res = await this.db.query<SuggestedCorrection>(
"UPDATE public.suggested_corrections SET suggested_value = $1 WHERE suggested_correction_id = $2 AND status = 'pending' RETURNING *",
[newSuggestedValue, correctionId]
);
if (res.rowCount === 0) {
throw new NotFoundError(`Correction with ID ${correctionId} not found or is not in 'pending' state.`);
}
return res.rows[0];
} catch (error) {
if (error instanceof NotFoundError) {
throw error;
}
handleDbError(error, logger, 'Database error in updateSuggestedCorrection', { correctionId }, {
defaultMessage: 'Failed to update suggested correction.',
});
}
}
/**
* Retrieves application-wide statistics for the admin dashboard.
* @returns A promise that resolves to an object containing various application stats.
*/
// prettier-ignore
async getApplicationStats(logger: Logger): Promise<{
flyerCount: number;
userCount: number;
flyerItemCount: number;
storeCount: number;
pendingCorrectionCount: number;
recipeCount: number;
}> {
try {
// Run count queries in parallel for better performance
const flyerCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.flyers');
const userCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.users');
const flyerItemCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.flyer_items');
const storeCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.stores');
const pendingCorrectionCountQuery = this.db.query<{ count: string }>("SELECT COUNT(*) FROM public.suggested_corrections WHERE status = 'pending'");
const recipeCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.recipes');
const [
flyerCountRes,
userCountRes,
flyerItemCountRes,
storeCountRes,
pendingCorrectionCountRes,
recipeCountRes
] = await Promise.all([
flyerCountQuery, userCountQuery, flyerItemCountQuery, storeCountQuery, pendingCorrectionCountQuery, recipeCountQuery
]);
return {
flyerCount: parseInt(flyerCountRes.rows[0].count, 10),
userCount: parseInt(userCountRes.rows[0].count, 10),
flyerItemCount: parseInt(flyerItemCountRes.rows[0].count, 10),
storeCount: parseInt(storeCountRes.rows[0].count, 10),
pendingCorrectionCount: parseInt(pendingCorrectionCountRes.rows[0].count, 10),
recipeCount: parseInt(recipeCountRes.rows[0].count, 10),
};
} catch (error) {
handleDbError(error, logger, 'Database error in getApplicationStats', {}, {
defaultMessage: 'Failed to retrieve application statistics.',
});
}
}
/**
* Retrieves daily statistics for user registrations and flyer uploads for the last 30 days.
* @returns A promise that resolves to an array of daily stats.
*/
// prettier-ignore
async getDailyStatsForLast30Days(logger: Logger): Promise<{ date: string; new_users: number; new_flyers: number; }[]> {
try {
const query = `
WITH date_series AS (
SELECT generate_series(
(CURRENT_DATE - interval '29 days'),
CURRENT_DATE,
'1 day'::interval
)::date AS day
),
daily_users AS (
SELECT created_at::date AS day, COUNT(*) AS user_count
FROM public.users
WHERE created_at >= (CURRENT_DATE - interval '29 days')
GROUP BY 1
),
daily_flyers AS (
SELECT created_at::date AS day, COUNT(*) AS flyer_count
FROM public.flyers
WHERE created_at >= (CURRENT_DATE - interval '29 days')
GROUP BY 1
)
SELECT
to_char(ds.day, 'YYYY-MM-DD') as date,
COALESCE(du.user_count, 0)::int AS new_users,
COALESCE(df.flyer_count, 0)::int AS new_flyers
FROM date_series ds
LEFT JOIN daily_users du ON ds.day = du.day
LEFT JOIN daily_flyers df ON ds.day = df.day
ORDER BY ds.day ASC;
`;
const res = await this.db.query(query);
return res.rows;
} catch (error) {
handleDbError(error, logger, 'Database error in getDailyStatsForLast30Days', {}, {
defaultMessage: 'Failed to retrieve daily statistics.',
});
}
}
/**
* Calls a database function to get the most frequently advertised items.
* @param days The number of past days to look back.
* @param limit The maximum number of items to return.
* @returns A promise that resolves to an array of the most frequent sale items.
*/
async getMostFrequentSaleItems(
days: number,
limit: number,
logger: Logger,
): Promise<MostFrequentSaleItem[]> {
// This is a secure parameterized query. The values for `days` and `limit` are passed
// separately from the query string. The database driver safely substitutes the `$1` and `$2`
// placeholders, preventing SQL injection attacks.
// Never use template literals like `WHERE f.valid_from >= NOW() - '${days} days'`
// as that would be a major security vulnerability.
try {
const query = `
SELECT
fi.master_item_id,
mi.name as item_name,
COUNT(fi.flyer_item_id)::int as sale_count
FROM public.flyer_items fi
JOIN public.flyers f ON fi.flyer_id = f.flyer_id
JOIN public.master_grocery_items mi ON fi.master_item_id = mi.master_grocery_item_id
WHERE
f.valid_from >= NOW() - ($1 || ' days')::interval
AND fi.master_item_id IS NOT NULL
GROUP BY
fi.master_item_id, mi.name
ORDER BY
sale_count DESC, mi.name ASC
LIMIT $2;
`;
const res = await this.db.query<MostFrequentSaleItem>(query, [days, limit]);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getMostFrequentSaleItems',
{ days, limit },
{
defaultMessage: 'Failed to get most frequent sale items.',
},
);
}
}
/**
* Updates the status of a recipe comment (e.g., for moderation).
* @param commentId The ID of the comment to update.
* @param status The new status ('visible', 'hidden', 'reported').
* @returns A promise that resolves to the updated RecipeComment object.
*/
async updateRecipeCommentStatus(
commentId: number,
status: 'visible' | 'hidden' | 'reported',
logger: Logger,
): Promise<RecipeComment> {
try {
const res = await this.db.query<RecipeComment>(
'UPDATE public.recipe_comments SET status = $1 WHERE recipe_comment_id = $2 RETURNING *',
[status, commentId],
);
if (res.rowCount === 0) {
throw new NotFoundError(`Recipe comment with ID ${commentId} not found.`);
}
return res.rows[0];
} catch (error) {
if (error instanceof NotFoundError) {
throw error;
}
handleDbError(
error,
logger,
'Database error in updateRecipeCommentStatus',
{ commentId, status },
{
checkMessage: 'Invalid status provided for recipe comment.',
defaultMessage: 'Failed to update recipe comment status.',
},
);
}
}
/**
* Retrieves all flyer items that could not be automatically matched to a master item.
* @returns A promise that resolves to an array of unmatched flyer items with context.
*/
async getUnmatchedFlyerItems(logger: Logger): Promise<UnmatchedFlyerItem[]> {
try {
const query = `
SELECT
ufi.unmatched_flyer_item_id,
ufi.status,
ufi.updated_at,
ufi.created_at,
fi.flyer_item_id as flyer_item_id,
fi.item as flyer_item_name,
fi.price_display,
f.flyer_id as flyer_id,
s.name as store_name,
json_build_object(
'store_id', s.store_id,
'name', s.name,
'logo_url', s.logo_url,
'locations', COALESCE(
(SELECT json_agg(
json_build_object(
'address_line_1', a.address_line_1,
'city', a.city,
'province_state', a.province_state,
'postal_code', a.postal_code
)
)
FROM public.store_locations sl
JOIN public.addresses a ON sl.address_id = a.address_id
WHERE sl.store_id = s.store_id),
'[]'::json
)
) as store
FROM public.unmatched_flyer_items ufi
JOIN public.flyer_items fi ON ufi.flyer_item_id = fi.flyer_item_id
JOIN public.flyers f ON fi.flyer_id = f.flyer_id
JOIN public.stores s ON f.store_id = s.store_id
WHERE ufi.status = 'pending'
ORDER BY ufi.created_at ASC;
`;
const res = await this.db.query<UnmatchedFlyerItem>(query);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getUnmatchedFlyerItems',
{},
{
defaultMessage: 'Failed to retrieve unmatched flyer items.',
},
);
}
}
/**
* Updates the status of a recipe (e.g., for moderation).
* @param recipeId The ID of the recipe to update.
* @param status The new status ('private', 'pending_review', 'public', 'rejected').
* @returns A promise that resolves to the updated Recipe object.
*/
async updateRecipeStatus(
recipeId: number,
status: 'private' | 'pending_review' | 'public' | 'rejected',
logger: Logger,
): Promise<Recipe> {
try {
const res = await this.db.query<Recipe>(
'UPDATE public.recipes SET status = $1 WHERE recipe_id = $2 RETURNING *',
[status, recipeId],
);
if (res.rowCount === 0) throw new NotFoundError(`Recipe with ID ${recipeId} not found.`);
return res.rows[0];
} catch (error) {
if (error instanceof NotFoundError) {
throw error;
}
handleDbError(
error,
logger,
'Database error in updateRecipeStatus',
{ recipeId, status },
{
checkMessage: 'Invalid status provided for recipe.',
defaultMessage: 'Failed to update recipe status.',
},
);
}
}
/**
* Resolves an unmatched flyer item by linking it to a master grocery item.
* This is a transactional operation.
* @param unmatchedFlyerItemId The ID from the `unmatched_flyer_items` table.
* @param masterItemId The ID of the `master_grocery_items` to link to.
*/
async resolveUnmatchedFlyerItem(
unmatchedFlyerItemId: number,
masterItemId: number,
logger: Logger,
): Promise<void> {
try {
await withTransaction(async (client) => {
// First, get the flyer_item_id from the unmatched record
const unmatchedRes = await client.query<{
flyer_item_id: number;
}>(
'SELECT flyer_item_id FROM public.unmatched_flyer_items WHERE unmatched_flyer_item_id = $1 FOR UPDATE',
[unmatchedFlyerItemId],
);
if (unmatchedRes.rowCount === 0) {
throw new NotFoundError(
`Unmatched flyer item with ID ${unmatchedFlyerItemId} not found.`,
);
}
const { flyer_item_id } = unmatchedRes.rows[0];
// Next, update the original flyer_items table with the correct master_item_id
await client.query(
'UPDATE public.flyer_items SET master_item_id = $1 WHERE flyer_item_id = $2',
[masterItemId, flyer_item_id],
);
// Finally, update the status of the unmatched record to 'resolved'
await client.query(
"UPDATE public.unmatched_flyer_items SET status = 'resolved' WHERE unmatched_flyer_item_id = $1",
[unmatchedFlyerItemId],
);
logger.info(
`Successfully resolved unmatched item ${unmatchedFlyerItemId} to master item ${masterItemId}.`,
);
});
} catch (error) {
if (error instanceof NotFoundError) {
throw error;
}
handleDbError(
error,
logger,
'Database transaction error in resolveUnmatchedFlyerItem',
{ unmatchedFlyerItemId, masterItemId },
{
fkMessage: 'The specified master item ID does not exist.',
defaultMessage: 'Failed to resolve unmatched flyer item.',
},
);
}
}
/**
* Ignores an unmatched flyer item by updating its status.
* @param unmatchedFlyerItemId The ID from the `unmatched_flyer_items` table.
*/
async ignoreUnmatchedFlyerItem(unmatchedFlyerItemId: number, logger: Logger): Promise<void> {
try {
const res = await this.db.query(
"UPDATE public.unmatched_flyer_items SET status = 'ignored' WHERE unmatched_flyer_item_id = $1 AND status = 'pending'",
[unmatchedFlyerItemId],
);
if (res.rowCount === 0) {
throw new NotFoundError(
`Unmatched flyer item with ID ${unmatchedFlyerItemId} not found or not in 'pending' state.`,
);
}
} catch (error) {
if (error instanceof NotFoundError) throw error;
handleDbError(
error,
logger,
'Database error in ignoreUnmatchedFlyerItem',
{ unmatchedFlyerItemId },
{ defaultMessage: 'Failed to ignore unmatched flyer item.' },
);
}
}
/**
* Retrieves a paginated list of recent activities from the activity log.
* @param limit The number of log entries to retrieve.
* @param offset The number of log entries to skip (for pagination).
* @returns A promise that resolves to an array of ActivityLogItem objects.
*/
// prettier-ignore
async getActivityLog(limit: number, offset: number, logger: Logger): Promise<ActivityLogItem[]> {
try {
const res = await this.db.query<ActivityLogItem>('SELECT * FROM public.get_activity_log($1, $2)', [limit, offset]);
return res.rows;
} catch (error) {
handleDbError(error, logger, 'Database error in getActivityLog', { limit, offset }, {
defaultMessage: 'Failed to retrieve activity log.',
});
}
}
/**
* Defines a type for JSON-compatible data structures, allowing for nested objects and arrays.
* This provides a safer alternative to `any` for objects intended for JSON serialization.
*/
async logActivity(
logData: {
userId?: string | null;
action: string;
displayText: string;
icon?: string | null;
details?: Record<string, unknown> | null;
},
logger: Logger,
): Promise<void> {
const { userId, action, displayText, icon, details } = logData;
try {
await this.db.query(
`INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
VALUES ($1, $2, $3, $4, $5)`,
[
userId || null,
action,
displayText,
icon || null,
details ? JSON.stringify(details) : null,
],
);
} catch (error) {
logger.error({ err: error, logData }, 'Database error in logActivity');
// We don't re-throw here to prevent logging failures from crashing critical paths.
}
}
/**
* Increments the failed login attempt counter for a user.
* @param userId The ID of the user.
* @returns A promise that resolves to the new number of failed login attempts, or -1 on error.
*/
async incrementFailedLoginAttempts(userId: string, logger: Logger): Promise<number> {
try {
const res = await this.db.query<{ failed_login_attempts: number }>(
`UPDATE public.users
SET failed_login_attempts = failed_login_attempts + 1, last_failed_login = NOW()
WHERE user_id = $1
RETURNING failed_login_attempts`,
[userId],
);
if (res.rowCount === 0) {
logger.warn(
{ userId },
'Attempted to increment failed login attempts for a non-existent user.',
);
return 0; // Should not happen if called after user lookup, but safe to handle.
}
return res.rows[0].failed_login_attempts;
} catch (error) {
logger.error({ err: error, userId }, 'Database error in incrementFailedLoginAttempts');
return -1; // Return -1 to indicate an error occurred.
}
}
/**
* Resets the failed login attempt counter for a user upon successful login.
* @param userId The ID of the user.
* @param ipAddress The IP address from which the successful login occurred.
*/
async resetFailedLoginAttempts(userId: string, ipAddress: string, logger: Logger): Promise<void> {
try {
await this.db.query(
`UPDATE public.users
SET failed_login_attempts = 0, last_failed_login = NULL, last_login_ip = $2, last_login_at = NOW()
WHERE user_id = $1 AND failed_login_attempts > 0`,
[userId, ipAddress],
);
} catch (error) {
// This is a non-critical operation, so we just log the error and continue.
logger.error({ err: error, userId }, 'Database error in resetFailedLoginAttempts');
}
}
/**
* Updates the logo URL for a specific brand.
* @param brandId The ID of the brand to update.
* @param logoUrl The new URL for the brand's logo.
*/
// prettier-ignore
async updateBrandLogo(brandId: number, logoUrl: string, logger: Logger): Promise<void> {
try {
const res = await this.db.query(
'UPDATE public.brands SET logo_url = $1 WHERE brand_id = $2',
[logoUrl, brandId]
);
if (res.rowCount === 0) {
throw new NotFoundError(`Brand with ID ${brandId} not found.`);
}
} catch (error) {
if (error instanceof NotFoundError) throw error;
handleDbError(error, logger, 'Database error in updateBrandLogo', { brandId }, {
defaultMessage: 'Failed to update brand logo in database.',
});
}
}
/**
* Updates the status of a specific receipt.
* @param receiptId The ID of the receipt to update.
* @param status The new status for the receipt.
* @returns A promise that resolves to the updated Receipt object.
*/
async updateReceiptStatus(
receiptId: number,
status: 'pending' | 'processing' | 'completed' | 'failed',
logger: Logger,
): Promise<Receipt> {
try {
const res = await this.db.query<Receipt>(
`UPDATE public.receipts SET status = $1, processed_at = CASE WHEN $1 IN ('completed', 'failed') THEN now() ELSE processed_at END WHERE receipt_id = $2 RETURNING *`,
[status, receiptId],
);
if (res.rowCount === 0) throw new NotFoundError(`Receipt with ID ${receiptId} not found.`);
return res.rows[0];
} catch (error) {
if (error instanceof NotFoundError) throw error;
handleDbError(
error,
logger,
'Database error in updateReceiptStatus',
{ receiptId, status },
{
checkMessage: 'Invalid status provided for receipt.',
defaultMessage: 'Failed to update receipt status.',
},
);
}
}
async getAllUsers(
logger: Logger,
limit?: number,
offset?: number,
): Promise<{ users: AdminUserView[]; total: number }> {
try {
// Get total count
const countRes = await this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.users');
const total = parseInt(countRes.rows[0].count, 10);
// Build query with optional pagination
let query = `
SELECT u.user_id, u.email, u.created_at, p.role, p.full_name, p.avatar_url
FROM public.users u JOIN public.profiles p ON u.user_id = p.user_id ORDER BY u.created_at DESC`;
const params: number[] = [];
Iif (limit !== undefined) {
query += ` LIMIT $${params.length + 1}`;
params.push(limit);
}
if (offset !== undefined) {
query += ` OFFSET $${params.length + 1}`;
params.push(offset);
}
const res = await this.db.query<AdminUserView>(query, params.length > 0 ? params : undefined);
return { users: res.rows, total };
} catch (error) {
handleDbError(
error,
logger,
'Database error in getAllUsers',
{},
{
defaultMessage: 'Failed to retrieve all users.',
},
);
}
}
/**
* Updates the role of a specific user.
* @param userId The ID of the user to update.
* @param role The new role to assign ('user' or 'admin').
* @returns A promise that resolves to the updated Profile object.
*/
async updateUserRole(userId: string, role: 'user' | 'admin', logger: Logger): Promise<Profile> {
try {
const res = await this.db.query<Profile>(
'UPDATE public.profiles SET role = $1 WHERE user_id = $2 RETURNING *',
[role, userId],
);
if (res.rowCount === 0) {
throw new NotFoundError(`User with ID ${userId} not found.`);
}
return res.rows[0];
} catch (error) {
if (error instanceof NotFoundError) {
throw error;
}
handleDbError(
error,
logger,
'Database error in updateUserRole',
{ userId, role },
{
fkMessage: 'The specified user does not exist.',
checkMessage: 'Invalid role provided for user.',
defaultMessage: 'Failed to update user role.',
},
);
}
}
/**
* Retrieves all flyers that have been flagged with a 'needs_review' status.
* @param logger The logger instance.
* @returns A promise that resolves to an array of Flyer objects.
*/
async getFlyersForReview(logger: Logger): Promise<Flyer[]> {
try {
const query = `
SELECT
f.*,
json_build_object(
'store_id', s.store_id,
'name', s.name,
'logo_url', s.logo_url,
'locations', COALESCE(
(SELECT json_agg(
json_build_object(
'address_line_1', a.address_line_1,
'city', a.city,
'province_state', a.province_state,
'postal_code', a.postal_code
)
)
FROM public.store_locations sl
JOIN public.addresses a ON sl.address_id = a.address_id
WHERE sl.store_id = s.store_id),
'[]'::json
)
) as store
FROM public.flyers f
LEFT JOIN public.stores s ON f.store_id = s.store_id
WHERE f.status = 'needs_review'
ORDER BY f.created_at DESC;
`;
const res = await this.db.query<Flyer>(query);
return res.rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in getFlyersForReview',
{},
{
defaultMessage: 'Failed to retrieve flyers for review.',
},
);
}
}
}
|