← back to Watches
database/migrate-json-to-postgres.js
254 lines
/**
* JSON to PostgreSQL Migration Script
* Migrates watches.json data to PostgreSQL database
*/
import pg from 'pg';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
const { Pool } = pg;
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
// Database configuration
const pool = new Pool({
host: process.env.DB_HOST || 'localhost',
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME || 'omega_watches',
user: process.env.DB_USER || 'postgres',
password: process.env.DB_PASSWORD || 'postgres',
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// Material mapping
const MATERIAL_MAPPING = {
'Stainless steel': 'stainless_steel',
'Stainless steel with white outer case': 'stainless_steel',
'Stainless steel and gold (two-tone)': 'two_tone',
'18K gold': 'gold_18k',
'18K gold or stainless steel': 'gold_18k',
'Stainless steel or gold': 'stainless_steel',
'Black ceramic (ZrO2)': 'ceramic',
'Omega Canopus Gold (18K white gold)': 'canopus_gold'
};
function slugify(text) {
return text
.toLowerCase()
.replace(/[^\w\s-]/g, '')
.replace(/\s+/g, '-')
.replace(/-+/g, '-')
.trim();
}
function mapCaseMaterial(material) {
return MATERIAL_MAPPING[material] || 'stainless_steel';
}
async function migrateData() {
const client = await pool.connect();
try {
console.log('🚀 Starting migration from JSON to PostgreSQL...\n');
// Start transaction
await client.query('BEGIN');
// Load JSON data
const jsonPath = path.join(__dirname, '..', 'data', 'watches.json');
const rawData = fs.readFileSync(jsonPath, 'utf8');
const data = JSON.parse(rawData);
console.log(`📊 Found ${data.watches.length} watches to migrate`);
console.log(`📚 Found ${data.metadata.collections.length} series\n`);
let watchCount = 0;
let priceCount = 0;
let specCount = 0;
let featureCount = 0;
// Create a map to track features
const featureMap = new Map();
// Process each watch
for (const watch of data.watches) {
console.log(`Processing: ${watch.model}...`);
// 1. Insert watch
const watchResult = await client.query(`
INSERT INTO watches (
slug, model, series, reference, year_introduced,
description, detailed_description, image_url,
is_active
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id
`, [
watch.id,
watch.model,
watch.series,
watch.reference,
watch.yearIntroduced,
watch.description,
watch.detailedDescription || null,
watch.imageUrl || null,
true
]);
const watchId = watchResult.rows[0].id;
watchCount++;
// 2. Insert specifications
if (watch.specifications) {
const spec = watch.specifications;
await client.query(`
INSERT INTO specifications (
watch_id, case_size, case_material, case_thickness,
lug_width, movement_calibre, movement_type,
power_reserve, water_resistance, crystal_type,
dial_color, bezel_type, weight
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
`, [
watchId,
spec.caseSize || null,
spec.caseMaterial ? mapCaseMaterial(spec.caseMaterial) : null,
spec.thickness || null,
spec.lugWidth || null,
spec.movement || null,
spec.movement ? (spec.movement.includes('automatic') ? 'automatic' : 'manual') : null,
spec.powerReserve || null,
spec.waterResistance || null,
spec.crystal || null,
spec.dialColor || null,
spec.bezel || null,
spec.weight || null
]);
specCount++;
}
// 3. Insert features
if (watch.specifications && watch.specifications.features) {
for (const featureName of watch.specifications.features) {
let featureId;
// Check if feature already exists in our map
if (featureMap.has(featureName)) {
featureId = featureMap.get(featureName);
} else {
// Insert or get feature
const featureResult = await client.query(`
INSERT INTO features (name, category)
VALUES ($1, $2)
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
RETURNING id
`, [featureName, 'general']);
featureId = featureResult.rows[0].id;
featureMap.set(featureName, featureId);
}
// Link feature to watch
await client.query(`
INSERT INTO watch_features (watch_id, feature_id)
VALUES ($1, $2)
ON CONFLICT DO NOTHING
`, [watchId, featureId]);
featureCount++;
}
}
// 4. Insert price history
if (watch.priceHistory && Array.isArray(watch.priceHistory)) {
for (const pricePoint of watch.priceHistory) {
await client.query(`
INSERT INTO price_history (
watch_id, year, price, condition, source,
currency, is_estimate
) VALUES ($1, $2, $3, $4, $5, $6, $7)
`, [
watchId,
pricePoint.year,
pricePoint.price,
pricePoint.condition || 'new',
pricePoint.source || null,
'USD',
pricePoint.source && pricePoint.source.toLowerCase().includes('estimate')
]);
priceCount++;
}
}
console.log(` ✅ Migrated ${watch.model}`);
}
// Refresh materialized view
console.log('\n📊 Refreshing materialized views...');
await client.query('REFRESH MATERIALIZED VIEW watch_statistics');
// Commit transaction
await client.query('COMMIT');
console.log('\n✅ Migration completed successfully!');
console.log('\n📈 Summary:');
console.log(` Watches: ${watchCount}`);
console.log(` Specifications: ${specCount}`);
console.log(` Features: ${featureCount}`);
console.log(` Price Points: ${priceCount}`);
console.log(` Unique Features: ${featureMap.size}`);
// Show some statistics
console.log('\n📊 Database Statistics:');
const stats = await client.query(`
SELECT
series,
COUNT(*) as watch_count,
AVG(year_introduced) as avg_year
FROM watches
GROUP BY series
ORDER BY watch_count DESC
`);
console.log('\nWatches by Series:');
for (const row of stats.rows) {
console.log(` ${row.series}: ${row.watch_count} watches (avg year: ${Math.round(row.avg_year)})`);
}
const priceStats = await client.query(`
SELECT
COUNT(DISTINCT watch_id) as watches_with_prices,
COUNT(*) as total_price_points,
MIN(year) as earliest_year,
MAX(year) as latest_year,
MIN(price) as min_price,
MAX(price) as max_price
FROM price_history
`);
console.log('\nPrice History:');
const ps = priceStats.rows[0];
console.log(` Watches with prices: ${ps.watches_with_prices}`);
console.log(` Total price points: ${ps.total_price_points}`);
console.log(` Year range: ${ps.earliest_year} - ${ps.latest_year}`);
console.log(` Price range: $${ps.min_price} - $${ps.max_price.toLocaleString()}`);
} catch (error) {
await client.query('ROLLBACK');
console.error('❌ Migration failed:', error);
throw error;
} finally {
client.release();
await pool.end();
}
}
// Run migration
migrateData().catch(error => {
console.error('Migration error:', error);
process.exit(1);
});