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 | 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 27x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 2x 4x 4x 2x 2x 2x 2x 2x 4x 4x 3x 1x 24x | // src/services/db/deals.db.ts
import { getPool } from './connection.db';
import { WatchedItemDeal } from '../../types';
import type { Pool, PoolClient } from 'pg';
import type { Logger } from 'pino';
import { logger as globalLogger } from '../logger.server';
import { handleDbError } from './errors.db';
export class DealsRepository {
// 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;
}
/**
* Finds the best current sale price for each of a user's watched items.
* It considers only currently active flyers and handles ties by preferring the deal
* that is valid for the longest time.
*
* @param userId - The ID of the user whose watched items are being checked.
* @param logger - The logger instance for context-specific logging.
* @returns A promise that resolves to an array of WatchedItemDeal objects.
*/
async findBestPricesForWatchedItems(
userId: string,
logger: Logger = globalLogger,
): Promise<WatchedItemDeal[]> {
logger.debug({ userId }, 'Finding best prices for watched items.');
const query = `
WITH UserWatchedItems AS (
-- Select all items the user is watching
SELECT master_item_id FROM user_watched_items WHERE user_id = $1
),
RankedPrices AS (
-- Find all current sale prices for those items and rank them
SELECT
fi.master_item_id,
mgi.name AS item_name,
fi.price_in_cents,
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,
f.flyer_id,
f.valid_to,
-- Rank prices for each item, lowest first. In case of a tie, the deal that ends later is preferred.
ROW_NUMBER() OVER(PARTITION BY fi.master_item_id ORDER BY fi.price_in_cents ASC, f.valid_to DESC) as rn
FROM flyer_items fi
JOIN flyers f ON fi.flyer_id = f.flyer_id
JOIN stores s ON f.store_id = s.store_id
JOIN master_grocery_items mgi ON fi.master_item_id = mgi.master_grocery_item_id
WHERE
fi.master_item_id IN (SELECT master_item_id FROM UserWatchedItems)
AND f.valid_to >= CURRENT_DATE -- Only consider active flyers
AND fi.price_in_cents IS NOT NULL
)
-- Select only the #1 ranked (lowest) price for each item
SELECT
master_item_id,
item_name,
price_in_cents AS best_price_in_cents,
store,
flyer_id,
valid_to
FROM RankedPrices
WHERE rn = 1
ORDER BY item_name;
`;
try {
const { rows } = await this.db.query<WatchedItemDeal>(query, [userId]);
return rows;
} catch (error) {
handleDbError(
error,
logger,
'Database error in findBestPricesForWatchedItems',
{ userId },
{
defaultMessage: 'Failed to find best prices for watched items.',
},
);
}
}
}
export const dealsRepo = new DealsRepository();
|