← back to Watches
database/repositories/PriceHistoryRepository.js
381 lines
/**
* Price History Repository
* Manages price history data with time-series optimization
*/
import pool from '../connection.js';
export class PriceHistoryRepository {
/**
* Get price history for a watch
*/
async findByWatchId(watchId, options = {}) {
const {
startYear,
endYear,
condition,
orderBy = 'year',
orderDirection = 'ASC'
} = options;
let query = `
SELECT
id,
year,
price,
condition,
source,
currency,
is_estimate,
confidence_level,
notes,
auction_house,
created_at
FROM price_history
WHERE watch_id = $1
`;
const params = [watchId];
let paramCount = 1;
if (startYear) {
paramCount++;
query += ` AND year >= $${paramCount}`;
params.push(startYear);
}
if (endYear) {
paramCount++;
query += ` AND year <= $${paramCount}`;
params.push(endYear);
}
if (condition) {
paramCount++;
query += ` AND condition = $${paramCount}`;
params.push(condition);
}
query += ` ORDER BY ${orderBy} ${orderDirection}`;
const result = await pool.query(query, params);
return result.rows;
}
/**
* Get latest price for a watch
*/
async getLatestPrice(watchId, condition = null) {
let query = `
SELECT *
FROM price_history
WHERE watch_id = $1
`;
const params = [watchId];
if (condition) {
query += ` AND condition = $2`;
params.push(condition);
}
query += ` ORDER BY year DESC LIMIT 1`;
const result = await pool.query(query, params);
return result.rows[0] || null;
}
/**
* Calculate appreciation between two years
*/
async calculateAppreciation(watchId, startYear, endYear) {
const result = await pool.query(
'SELECT * FROM calculate_appreciation($1, $2, $3)',
[watchId, startYear, endYear]
);
return result.rows[0] || null;
}
/**
* Get price trends across multiple watches
*/
async getPriceTrends(options = {}) {
const {
series,
startYear,
endYear,
limit = 10
} = options;
let query = `
WITH watch_prices AS (
SELECT
w.id,
w.model,
w.series,
ph.year,
AVG(ph.price) as avg_price,
COUNT(*) as price_count
FROM watches w
INNER JOIN price_history ph ON w.id = ph.watch_id
WHERE w.is_active = true
`;
const params = [];
let paramCount = 0;
if (series) {
paramCount++;
query += ` AND w.series = $${paramCount}`;
params.push(series);
}
if (startYear) {
paramCount++;
query += ` AND ph.year >= $${paramCount}`;
params.push(startYear);
}
if (endYear) {
paramCount++;
query += ` AND ph.year <= $${paramCount}`;
params.push(endYear);
}
query += `
GROUP BY w.id, w.model, w.series, ph.year
)
SELECT
model,
series,
year,
avg_price,
price_count
FROM watch_prices
ORDER BY year ASC, avg_price DESC
`;
if (limit) {
paramCount++;
query += ` LIMIT $${paramCount}`;
params.push(limit);
}
const result = await pool.query(query, params);
return result.rows;
}
/**
* Add price point
*/
async create(priceData) {
const {
watchId,
year,
price,
condition = 'new',
source,
notes,
currency = 'USD',
auctionHouse,
lotNumber,
isEstimate = false,
confidenceLevel
} = priceData;
const query = `
INSERT INTO price_history (
watch_id, year, price, condition, source, notes,
currency, auction_house, lot_number, is_estimate,
confidence_level
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
RETURNING *
`;
const result = await pool.query(query, [
watchId,
year,
price,
condition,
source,
notes,
currency,
auctionHouse,
lotNumber,
isEstimate,
confidenceLevel
]);
return result.rows[0];
}
/**
* Bulk insert price history
*/
async bulkCreate(priceDataArray) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const results = [];
for (const priceData of priceDataArray) {
const result = await this.create(priceData);
results.push(result);
}
await client.query('COMMIT');
return results;
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
client.release();
}
}
/**
* Update price point
*/
async update(priceId, updateData) {
const allowedFields = [
'price', 'condition', 'source', 'notes',
'auction_house', 'lot_number', 'is_estimate',
'confidence_level'
];
const updates = [];
const params = [];
let paramCount = 0;
for (const [key, value] of Object.entries(updateData)) {
const snakeKey = key.replace(/([A-Z])/g, '_$1').toLowerCase();
if (allowedFields.includes(snakeKey)) {
paramCount++;
updates.push(`${snakeKey} = $${paramCount}`);
params.push(value);
}
}
if (updates.length === 0) {
throw new Error('No valid fields to update');
}
paramCount++;
params.push(priceId);
const query = `
UPDATE price_history
SET ${updates.join(', ')}
WHERE id = $${paramCount}
RETURNING *
`;
const result = await pool.query(query, params);
return result.rows[0];
}
/**
* Get year-over-year growth for a watch
*/
async getYearOverYearGrowth(watchId) {
const query = `
WITH yearly_prices AS (
SELECT
year,
AVG(price) as avg_price
FROM price_history
WHERE watch_id = $1
GROUP BY year
ORDER BY year
),
growth_calc AS (
SELECT
year,
avg_price,
LAG(avg_price) OVER (ORDER BY year) as prev_price,
CASE
WHEN LAG(avg_price) OVER (ORDER BY year) > 0 THEN
((avg_price - LAG(avg_price) OVER (ORDER BY year)) /
LAG(avg_price) OVER (ORDER BY year) * 100)
ELSE NULL
END as yoy_growth_pct
FROM yearly_prices
)
SELECT * FROM growth_calc
WHERE prev_price IS NOT NULL
ORDER BY year
`;
const result = await pool.query(query, [watchId]);
return result.rows;
}
/**
* Get price volatility (standard deviation)
*/
async getPriceVolatility(watchId, years = 10) {
const query = `
SELECT
COUNT(*) as data_points,
AVG(price) as mean_price,
STDDEV(price) as price_stddev,
MIN(price) as min_price,
MAX(price) as max_price,
CASE
WHEN AVG(price) > 0 THEN
(STDDEV(price) / AVG(price) * 100)
ELSE NULL
END as coefficient_of_variation
FROM price_history
WHERE watch_id = $1
AND year >= EXTRACT(YEAR FROM CURRENT_DATE) - $2
`;
const result = await pool.query(query, [watchId, years]);
return result.rows[0];
}
/**
* Get comparative analysis across series
*/
async getSeriesComparison(series, startYear, endYear) {
const query = `
SELECT
w.model,
w.reference,
MIN(ph.price) as min_price,
MAX(ph.price) as max_price,
AVG(ph.price) as avg_price,
COUNT(DISTINCT ph.year) as years_tracked,
CASE
WHEN MIN(ph.price) > 0 THEN
((MAX(ph.price) - MIN(ph.price)) / MIN(ph.price) * 100)
ELSE NULL
END as total_appreciation_pct
FROM watches w
INNER JOIN price_history ph ON w.id = ph.watch_id
WHERE w.series = $1
AND ph.year BETWEEN $2 AND $3
AND w.is_active = true
GROUP BY w.id, w.model, w.reference
ORDER BY total_appreciation_pct DESC NULLS LAST
`;
const result = await pool.query(query, [series, startYear, endYear]);
return result.rows;
}
/**
* Delete price point
*/
async delete(priceId) {
const query = 'DELETE FROM price_history WHERE id = $1 RETURNING *';
const result = await pool.query(query, [priceId]);
return result.rows[0];
}
}
export default new PriceHistoryRepository();