← back to Watches
database/repositories/watch-repository.js
377 lines
/**
* Watch Repository - Data Access Layer
*
* Implements Repository Pattern for clean separation of concerns
* Provides high-level data operations with caching and optimization
*/
import { getPoolManager } from '../pool-manager.js';
class WatchRepository {
constructor(poolManager) {
this.pool = poolManager || getPoolManager();
this.cache = new Map();
this.cacheTTL = 300000; // 5 minutes
}
/**
* Get all watches with optional filtering and pagination
*/
async findAll(options = {}) {
const {
series,
minYear,
maxYear,
minPrice,
maxPrice,
featured,
limitedEdition,
page = 1,
limit = 50,
orderBy = 'year_introduced',
orderDir = 'DESC'
} = options;
let query = `
SELECT
w.*,
s.case_size,
s.case_material,
s.movement_type,
ws.current_price,
ws.appreciation_percentage,
ws.cagr_percentage
FROM omega.watches w
LEFT JOIN omega.specifications s ON w.id = s.watch_id
LEFT JOIN omega.watch_statistics ws ON w.id = ws.id
WHERE w.is_active = true
`;
const params = [];
let paramCount = 1;
if (series) {
query += ` AND w.series = $${paramCount++}`;
params.push(series);
}
if (minYear) {
query += ` AND w.year_introduced >= $${paramCount++}`;
params.push(minYear);
}
if (maxYear) {
query += ` AND w.year_introduced <= $${paramCount++}`;
params.push(maxYear);
}
if (featured !== undefined) {
query += ` AND w.is_featured = $${paramCount++}`;
params.push(featured);
}
if (limitedEdition !== undefined) {
query += ` AND w.is_limited_edition = $${paramCount++}`;
params.push(limitedEdition);
}
if (minPrice || maxPrice) {
if (minPrice) {
query += ` AND ws.current_price >= $${paramCount++}`;
params.push(minPrice);
}
if (maxPrice) {
query += ` AND ws.current_price <= $${paramCount++}`;
params.push(maxPrice);
}
}
// Add ordering
const validOrderBy = ['year_introduced', 'model', 'current_price', 'appreciation_percentage'];
const orderColumn = validOrderBy.includes(orderBy) ? orderBy : 'year_introduced';
const orderDirection = orderDir.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
query += ` ORDER BY ${orderColumn} ${orderDirection}`;
// Add pagination
const offset = (page - 1) * limit;
query += ` LIMIT $${paramCount++} OFFSET $${paramCount++}`;
params.push(limit, offset);
const result = await this.pool.query(query, params, { pool: 'replica' });
// Get total count
let countQuery = `SELECT COUNT(*) FROM omega.watches w WHERE w.is_active = true`;
const countParams = params.slice(0, -2); // Remove limit and offset
// Rebuild WHERE clause for count
let countParamCount = 1;
const countConditions = [];
if (series) countConditions.push(`w.series = $${countParamCount++}`);
if (minYear) countConditions.push(`w.year_introduced >= $${countParamCount++}`);
if (maxYear) countConditions.push(`w.year_introduced <= $${countParamCount++}`);
if (featured !== undefined) countConditions.push(`w.is_featured = $${countParamCount++}`);
if (limitedEdition !== undefined) countConditions.push(`w.is_limited_edition = $${countParamCount++}`);
if (countConditions.length > 0) {
countQuery += ' AND ' + countConditions.join(' AND ');
}
const countResult = await this.pool.query(countQuery, countParams, { pool: 'replica' });
const total = parseInt(countResult.rows[0].count);
return {
watches: result.rows,
pagination: {
page,
limit,
total,
totalPages: Math.ceil(total / limit)
}
};
}
/**
* Find watch by ID with full details
*/
async findById(id) {
const cacheKey = `watch:${id}`;
const cached = this.getCached(cacheKey);
if (cached) return cached;
const result = await this.pool.query(
`SELECT omega.get_watch_details($1) as details`,
[id],
{ pool: 'replica' }
);
if (result.rows.length === 0) {
return null;
}
const watch = result.rows[0].details;
this.setCache(cacheKey, watch);
return watch;
}
/**
* Find watch by slug
*/
async findBySlug(slug) {
const result = await this.pool.query(
`SELECT id FROM omega.watches WHERE slug = $1 AND is_active = true`,
[slug],
{ pool: 'replica' }
);
if (result.rows.length === 0) {
return null;
}
return this.findById(result.rows[0].id);
}
/**
* Full-text search
*/
async search(query, limit = 20) {
// Convert search query to tsquery format
const tsquery = query
.trim()
.split(/\s+/)
.filter(word => word.length > 0)
.join(' & ');
const result = await this.pool.query(
`SELECT * FROM omega.search_watches($1, $2)`,
[tsquery, limit],
{ pool: 'replica' }
);
// Fetch full details for each result
const watches = await Promise.all(
result.rows.map(row => this.findById(row.watch_id))
);
return watches.filter(Boolean);
}
/**
* Get trending watches
*/
async getTrending(days = 7, limit = 10) {
const cacheKey = `trending:${days}:${limit}`;
const cached = this.getCached(cacheKey);
if (cached) return cached;
const result = await this.pool.query(
`SELECT * FROM omega.get_trending_watches($1, $2)`,
[days, limit],
{ pool: 'replica' }
);
const watches = result.rows;
this.setCache(cacheKey, watches, 60000); // 1 minute cache
return watches;
}
/**
* Get price history for watch
*/
async getPriceHistory(watchId) {
const result = await this.pool.query(
`SELECT * FROM omega.price_history
WHERE watch_id = $1
ORDER BY year ASC`,
[watchId],
{ pool: 'replica' }
);
return result.rows;
}
/**
* Get watch statistics
*/
async getStatistics(watchId) {
const result = await this.pool.query(
`SELECT * FROM omega.watch_statistics WHERE id = $1`,
[watchId],
{ pool: 'replica' }
);
return result.rows[0] || null;
}
/**
* Get series performance
*/
async getSeriesPerformance() {
const cacheKey = 'series:performance';
const cached = this.getCached(cacheKey);
if (cached) return cached;
const result = await this.pool.query(
`SELECT * FROM omega.series_performance ORDER BY avg_appreciation DESC`,
[],
{ pool: 'replica' }
);
this.setCache(cacheKey, result.rows);
return result.rows;
}
/**
* Calculate appreciation between years
*/
async calculateAppreciation(watchId, startYear, endYear) {
const result = await this.pool.query(
`SELECT * FROM omega.calculate_appreciation($1, $2, $3)`,
[watchId, startYear, endYear],
{ pool: 'replica' }
);
return result.rows[0] || null;
}
/**
* Track watch view
*/
async trackView(watchId, metadata = {}) {
const { sessionId, userAgent, ipAddress, referrer } = metadata;
await this.pool.query(
`INSERT INTO omega.watch_views (
watch_id, session_id, user_agent, ip_address, referrer
) VALUES ($1, $2, $3, $4, $5)`,
[watchId, sessionId, userAgent, ipAddress, referrer],
{ pool: 'primary' }
);
}
/**
* Compare multiple watches
*/
async compareWatches(watchIds) {
if (!Array.isArray(watchIds) || watchIds.length === 0) {
return [];
}
const placeholders = watchIds.map((_, i) => `$${i + 1}`).join(',');
const result = await this.pool.query(
`SELECT
w.*,
s.*,
ws.current_price,
ws.original_price,
ws.appreciation_percentage,
ws.cagr_percentage
FROM omega.watches w
LEFT JOIN omega.specifications s ON w.id = s.watch_id
LEFT JOIN omega.watch_statistics ws ON w.id = ws.id
WHERE w.id IN (${placeholders})
AND w.is_active = true`,
watchIds,
{ pool: 'replica' }
);
return result.rows;
}
/**
* Get database statistics
*/
async getDatabaseStats() {
const result = await this.pool.query(
`SELECT * FROM omega.get_system_stats()`,
[],
{ pool: 'replica' }
);
return result.rows.reduce((acc, row) => {
acc[row.metric] = row.value;
return acc;
}, {});
}
/**
* Refresh materialized views
*/
async refreshViews() {
await this.pool.query(
`SELECT omega.refresh_all_materialized_views()`,
[],
{ pool: 'primary' }
);
this.clearCache();
}
// Cache helpers
getCached(key) {
const item = this.cache.get(key);
if (!item) return null;
if (Date.now() - item.timestamp > item.ttl) {
this.cache.delete(key);
return null;
}
return item.data;
}
setCache(key, data, ttl = this.cacheTTL) {
this.cache.set(key, {
data,
timestamp: Date.now(),
ttl
});
}
clearCache() {
this.cache.clear();
}
}
export default WatchRepository;