55de4920b2
026: Remove invalid price observations (sub-manufacturing-cost), disable
optictransceiver.com (domain repurposed as plant shop), fix verification
function to accept low/medium/high data_confidence values
027: Clean up FS.COM USD→EUR converted prices, force re-scrape with
new de.fs.com EUR-primary scraper
45 lines
1.4 KiB
SQL
45 lines
1.4 KiB
SQL
-- Migration 027: Fix FS.COM price observations currency
|
|
--
|
|
-- FS.COM scraper previously scraped www.fs.com (USD) and tried to convert USD→EUR.
|
|
-- Now scrapes de.fs.com (EUR) directly — prices are real EUR values.
|
|
-- This migration cleans up any existing FS.COM prices that were derived (USD→EUR)
|
|
-- and marks them for re-scraping on next run.
|
|
--
|
|
-- Applied: 2026-04-06 (after fs-com.ts BASE_URL switch to de.fs.com)
|
|
|
|
-- Delete price observations from FS.COM that look like converted USD prices
|
|
-- (prices stored as USD but attributed to de.fs.com source after the switch)
|
|
-- We just delete them all so the next scrape gets fresh EUR prices
|
|
DELETE FROM price_observations po
|
|
USING vendors v
|
|
WHERE po.source_vendor_id = v.id
|
|
AND v.slug = 'fs-com'
|
|
AND po.time < NOW() - INTERVAL '7 days';
|
|
|
|
-- Reset verification for affected transceivers so they get re-verified
|
|
UPDATE transceivers t
|
|
SET
|
|
price_verified = FALSE,
|
|
price_verified_eur = NULL,
|
|
fully_verified = FALSE,
|
|
updated_at = NOW()
|
|
FROM vendors v
|
|
WHERE t.vendor_id = v.id
|
|
AND v.slug = 'fs-com'
|
|
AND t.price_verified = TRUE
|
|
AND t.price_verified_eur IS NOT NULL;
|
|
|
|
-- Report remaining FS.COM observations
|
|
SELECT
|
|
v.name AS vendor,
|
|
po.currency,
|
|
COUNT(*) AS count,
|
|
ROUND(AVG(po.price), 2) AS avg_price,
|
|
MIN(po.time) AS oldest,
|
|
MAX(po.time) AS newest
|
|
FROM price_observations po
|
|
JOIN vendors v ON po.source_vendor_id = v.id
|
|
WHERE v.slug = 'fs-com'
|
|
GROUP BY v.name, po.currency
|
|
ORDER BY count DESC;
|