← back to Omega Watches 2
database/seed-additional-references.sql
187 lines
-- Additional Reference Seed Data for Omega Watches 2.0
-- Adds more watch references, MSRP snapshots, and market events
-- ============================================
-- ADDITIONAL WATCH REFERENCES
-- ============================================
-- More Speedmaster references
INSERT INTO watch_reference (brand, collection, model_name, reference_number, calibre, case_material, case_diameter_mm, dial_color, year_introduced, notes) VALUES
('Omega','Speedmaster','Dark Side of the Moon','311.92.44.51.01.003','9300','Ceramic','44.25','Black',2013,'Ceramic case, Co-Axial chronograph'),
('Omega','Speedmaster','Silver Snoopy Award 50th','310.32.42.50.02.001','3861','Steel','42.00','Silver/Blue',2020,'Snoopy caseback animation'),
('Omega','Speedmaster','First Omega in Space','311.32.40.30.01.001','1861','Steel','39.70','Black',2012,'Numbered edition, CK2998 tribute'),
('Omega','Speedmaster','57 Co-Axial Chronograph','332.10.41.51.01.001','9906','Steel','40.50','Black',2022,'New 57 line, Master Chronometer'),
('Omega','Speedmaster','Calibre 321 Ed White','311.30.40.30.01.001','321','Steel','39.70','Black',2019,'Historic Cal 321 reissue')
ON CONFLICT (brand, reference_number) DO NOTHING;
-- More Seamaster references
INSERT INTO watch_reference (brand, collection, model_name, reference_number, calibre, case_material, case_diameter_mm, dial_color, year_introduced, notes) VALUES
('Omega','Seamaster','Planet Ocean 600M','215.30.44.21.01.001','8900','Steel','43.50','Black',2016,'600m WR, ceramic bezel'),
('Omega','Seamaster','Aqua Terra 150M','220.10.41.21.03.001','8900','Steel','41.00','Blue',2017,'Teak pattern dial'),
('Omega','Seamaster','Aqua Terra 150M Green','220.10.41.21.10.001','8900','Steel','41.00','Green',2021,'Green teak dial'),
('Omega','Seamaster','Diver 300M No Time To Die','210.90.42.20.01.001','8806','Titanium','42.00','Brown/Tropical',2019,'007 Edition mesh bracelet'),
('Omega','Seamaster','Ultra Deep 6000M','215.30.46.21.01.001','8912','Steel','45.50','Black',2022,'6000m WR world record'),
('Omega','Seamaster','Railmaster','220.10.40.20.01.001','8806','Steel','40.00','Black',2017,'Anti-magnetic heritage')
ON CONFLICT (brand, reference_number) DO NOTHING;
-- More De Ville references
INSERT INTO watch_reference (brand, collection, model_name, reference_number, calibre, case_material, case_diameter_mm, dial_color, year_introduced, notes) VALUES
('Omega','De Ville','Tresor Power Reserve','435.13.40.22.02.001','8929','Steel','40.00','Silver',2020,'Manual wind, power reserve'),
('Omega','De Ville','Prestige 39.5mm','424.10.40.20.02.003','2500','Steel','39.50','Silver',2015,'Entry level automatic'),
('Omega','De Ville','Tourbillon Numbered Edition','529.53.43.22.01.001','2635','Gold','43.00','Black',2016,'Central tourbillon, limited')
ON CONFLICT (brand, reference_number) DO NOTHING;
-- Constellation additions
INSERT INTO watch_reference (brand, collection, model_name, reference_number, calibre, case_material, case_diameter_mm, dial_color, year_introduced, notes) VALUES
('Omega','Constellation','Globemaster Annual Calendar','130.33.41.22.06.001','8922','Steel','41.00','Grey',2019,'Pie-pan dial, annual calendar'),
('Omega','Constellation','Co-Axial Master Chronometer 36mm','131.10.36.20.02.001','8800','Steel','36.00','Silver',2020,'36mm unisex, silver dial'),
('Omega','Constellation','Meteorite','131.10.39.20.99.001','8800','Steel','39.00','Meteorite',2021,'Genuine meteorite dial')
ON CONFLICT (brand, reference_number) DO NOTHING;
-- ============================================
-- ADDITIONAL MSRP SNAPSHOTS
-- ============================================
-- Snoopy (one of the most popular)
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 13100, '2024-01-15', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '310.32.42.50.02.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 13600, '2025-01-15', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '310.32.42.50.02.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 14100, '2026-01-15', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '310.32.42.50.02.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- Dark Side of the Moon
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 12100, '2024-06-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '311.92.44.51.01.003'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 12600, '2025-06-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '311.92.44.51.01.003'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- Planet Ocean 600M
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 7350, '2024-03-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '215.30.44.21.01.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 7600, '2025-03-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '215.30.44.21.01.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- Aqua Terra Blue
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 6150, '2024-01-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '220.10.41.21.03.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 6400, '2025-01-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '220.10.41.21.03.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- No Time To Die 007
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 9200, '2024-01-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '210.90.42.20.01.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 9650, '2025-06-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '210.90.42.20.01.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- Cal 321 Ed White
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT wr.id, 'US', 'USD', 14100, '2024-06-01', 'https://www.omegawatches.com', '1.0.0'
FROM watch_reference wr WHERE wr.reference_number = '311.30.40.30.01.001'
ON CONFLICT (reference_id, region_code, captured_date) DO NOTHING;
-- ============================================
-- ADDITIONAL MARKET EVENTS
-- ============================================
-- Snoopy trading above retail (hot watch)
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-snp-001', 'marketplace_sold', '310.32.42.50.02.001', '2024-08-15', 28500, 'USD', 28500, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-snp-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-snp-002', 'dealer_ask', '310.32.42.50.02.001', '2025-01-10', 32000, 'USD', 32000, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-snp-002');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-snp-003', 'marketplace_sold', '310.32.42.50.02.001', '2025-06-20', 30000, 'USD', 30000, 'total_to_buyer', 'Very Good', 'very_good', 'private', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-snp-003');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-snp-004', 'marketplace_sold', '310.32.42.50.02.001', '2026-01-05', 27500, 'USD', 27500, 'total_to_buyer', 'Very Good', 'very_good', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-snp-004');
-- DSOTM ceramic
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-dsotm-001', 'marketplace_sold', '311.92.44.51.01.003', '2024-10-05', 9800, 'USD', 9800, 'total_to_buyer', 'Very Good', 'very_good', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-dsotm-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-dsotm-002', 'dealer_ask', '311.92.44.51.01.003', '2025-02-20', 10500, 'USD', 10500, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-dsotm-002');
-- Cal 321 Ed White at auction
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, hammer_price, buyers_premium, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'phillips', 'ph-321-001', 'auction_realized', '311.30.40.30.01.001', '2024-11-10', 18000, 'USD', 15000, 3000, 18000, 'price_realised', 'Mint', 'excellent', 'auction_house', 'https://www.phillips.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'ph-321-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, hammer_price, buyers_premium, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'christies', 'chr-321-001', 'auction_realized', '311.30.40.30.01.001', '2025-05-18', 16500, 'USD', 13200, 3300, 16500, 'price_realised', 'Excellent', 'excellent', 'auction_house', 'https://www.christies.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'chr-321-001');
-- No Time To Die 007 — hot model
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-nttd-001', 'marketplace_sold', '210.90.42.20.01.001', '2024-07-20', 8200, 'USD', 8200, 'total_to_buyer', 'Very Good', 'very_good', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-nttd-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-nttd-002', 'dealer_ask', '210.90.42.20.01.001', '2025-03-15', 8900, 'USD', 8900, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-nttd-002');
-- Planet Ocean
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-po-001', 'marketplace_sold', '215.30.44.21.01.001', '2024-11-05', 5800, 'USD', 5800, 'total_to_buyer', 'Good', 'good', 'private', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-po-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-po-002', 'dealer_ask', '215.30.44.21.01.001', '2025-09-01', 6100, 'USD', 6100, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-po-002');
-- Aqua Terra Blue
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-at-001', 'marketplace_sold', '220.10.41.21.03.001', '2024-05-10', 4800, 'USD', 4800, 'total_to_buyer', 'Very Good', 'very_good', 'private', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-at-001');
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'chrono24', 'c24-at-002', 'dealer_ask', '220.10.41.21.03.001', '2025-08-20', 5200, 'USD', 5200, 'total_to_buyer', 'Unworn', 'new_unworn', 'dealer', 'https://www.chrono24.com', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'c24-at-002');
-- First Omega in Space — vintage appeal
INSERT INTO market_event (source_name, source_listing_id, event_type, reference_number_raw, sale_date, price_amount, currency, hammer_price, buyers_premium, total_to_buyer, fee_semantics, condition_raw, condition_normalized, seller_type, source_url, parser_version)
SELECT 'antiquorum', 'antiq-fois-001', 'auction_realized', '311.32.40.30.01.001', '2024-05-20', 6200, 'CHF', 4960, 1240, 6200, 'price_realised', 'Very Good', 'very_good', 'auction_house', 'https://www.antiquorum.swiss', '1.0.0'
WHERE NOT EXISTS (SELECT 1 FROM market_event WHERE source_listing_id = 'antiq-fois-001');
-- ============================================
-- REFRESH MATERIALIZED VIEWS
-- ============================================
REFRESH MATERIALIZED VIEW ref_price_summary;
REFRESH MATERIALIZED VIEW source_health;