238 lines
10 KiB
TypeScript
238 lines
10 KiB
TypeScript
import { Router, Request, Response } from "express";
|
|
import { searchTransceivers, getTransceiverById } from "../db/queries";
|
|
import { pool } from "../db/client";
|
|
|
|
export const transceiverRouter = Router();
|
|
|
|
// GET /api/transceivers — Search/list transceivers
|
|
transceiverRouter.get("/", async (req: Request, res: Response) => {
|
|
try {
|
|
const q = (p: string) => req.query[p] ? String(req.query[p]) : undefined;
|
|
const result = await searchTransceivers({
|
|
q: q("q"),
|
|
form_factor: q("form_factor"),
|
|
speed: q("speed"),
|
|
speed_gbps: q("speed_gbps") ? parseFloat(q("speed_gbps")!) : undefined,
|
|
category: q("category"),
|
|
fiber_type: q("fiber_type"),
|
|
reach_min: q("reach_min") ? parseInt(q("reach_min")!) : undefined,
|
|
reach_max: q("reach_max") ? parseInt(q("reach_max")!) : undefined,
|
|
wdm_type: q("wdm_type"),
|
|
coherent: q("coherent") === "true" ? true : q("coherent") === "false" ? false : undefined,
|
|
market_status: q("market_status"),
|
|
vendor: q("vendor"),
|
|
verified: q("verified") as "price" | "image" | "details" | "full" | undefined,
|
|
limit: q("limit") ? parseInt(q("limit")!) : 50,
|
|
offset: q("offset") ? parseInt(q("offset")!) : 0,
|
|
});
|
|
res.json({ success: true, ...result });
|
|
} catch (err) {
|
|
console.error("Search transceivers error:", err);
|
|
res.status(500).json({ success: false, error: "Internal server error" });
|
|
}
|
|
});
|
|
|
|
// GET /api/transceivers/:id — Get single transceiver with latest prices per vendor
|
|
transceiverRouter.get("/:id", async (req: Request, res: Response) => {
|
|
try {
|
|
const transceiver = await getTransceiverById(String(req.params.id));
|
|
if (!transceiver) {
|
|
res.status(404).json({ success: false, error: "Transceiver not found" });
|
|
return;
|
|
}
|
|
|
|
// Latest price per source vendor — last 30 days, exclude anomalous prices
|
|
const pricesResult = await pool.query(
|
|
`SELECT DISTINCT ON (po.source_vendor_id)
|
|
po.price, po.currency, po.url, po.time, po.stock_level, po.is_verified,
|
|
v.name AS vendor_name, v.type AS vendor_type, v.website AS vendor_website
|
|
FROM price_observations po
|
|
JOIN vendors v ON po.source_vendor_id = v.id
|
|
WHERE po.transceiver_id = $1
|
|
AND po.time > NOW() - INTERVAL '30 days'
|
|
AND COALESCE(po.is_anomalous, false) = false
|
|
ORDER BY po.source_vendor_id, po.time DESC`,
|
|
[transceiver.id]
|
|
);
|
|
|
|
// Flag: price is only "verified" if observed within 7 days with real URL
|
|
const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
|
|
const prices = pricesResult.rows.map((row) => ({
|
|
vendor_name: row.vendor_name,
|
|
vendor_type: row.vendor_type,
|
|
vendor_website: row.vendor_website,
|
|
price: parseFloat(row.price),
|
|
currency: row.currency,
|
|
url: row.url,
|
|
stock_level: row.stock_level,
|
|
observed_at: row.time,
|
|
is_verified: !!(row.url && new Date(row.time) > sevenDaysAgo),
|
|
is_same_product: true,
|
|
}));
|
|
|
|
// Market comparison: strictly equivalent products from other vendors.
|
|
// Require hard technical evidence; unknown reach/fiber data must not act as
|
|
// a wildcard, otherwise breakout/AOC/cable variants leak into optic matches.
|
|
// Only real scraped prices (url IS NOT NULL), last 30 days, max 1 per source vendor
|
|
const comparableResult = await pool.query(
|
|
`SELECT DISTINCT ON (po.source_vendor_id)
|
|
po.price, po.currency, po.url, po.time,
|
|
sv.name AS vendor_name, sv.type AS vendor_type,
|
|
t2.part_number, t2.standard_name, t2.id AS comparable_id,
|
|
t2.form_factor AS comp_form_factor, t2.speed_gbps AS comp_speed_gbps,
|
|
t2.reach_meters AS comp_reach_meters, t2.reach_label AS comp_reach_label,
|
|
t2.fiber_type AS comp_fiber_type, t2.wavelengths AS comp_wavelengths
|
|
FROM transceivers t1
|
|
JOIN transceivers t2 ON (
|
|
(
|
|
t2.form_factor = t1.form_factor
|
|
OR (
|
|
t1.form_factor IN ('QSFP-DD', 'QSFP-DD800')
|
|
AND t2.form_factor IN ('QSFP-DD', 'QSFP-DD800')
|
|
)
|
|
)
|
|
AND t2.speed_gbps = t1.speed_gbps
|
|
AND t1.reach_meters IS NOT NULL AND t1.reach_meters > 0
|
|
AND t2.reach_meters IS NOT NULL AND t2.reach_meters > 0
|
|
AND LEAST(t1.reach_meters, t2.reach_meters)::numeric / GREATEST(t1.reach_meters, t2.reach_meters) >= 0.85
|
|
AND NULLIF(t1.fiber_type, '') IS NOT NULL
|
|
AND NULLIF(t2.fiber_type, '') IS NOT NULL
|
|
AND LOWER(t2.fiber_type) = LOWER(t1.fiber_type)
|
|
AND (
|
|
(
|
|
LOWER(t1.fiber_type) IN ('copper', 'dac', 'cu')
|
|
AND LOWER(t2.fiber_type) IN ('copper', 'dac', 'cu')
|
|
AND COALESCE(NULLIF(UPPER(t1.wavelengths), ''), 'N/A') IN ('N/A', 'NA', 'NONE')
|
|
AND COALESCE(NULLIF(UPPER(t2.wavelengths), ''), 'N/A') IN ('N/A', 'NA', 'NONE')
|
|
)
|
|
OR (
|
|
substring(t1.wavelengths from '(\\d{3,4})') IS NOT NULL
|
|
AND substring(t2.wavelengths from '(\\d{3,4})') IS NOT NULL
|
|
AND ABS((substring(t1.wavelengths from '(\\d{3,4})'))::int - (substring(t2.wavelengths from '(\\d{3,4})'))::int) <= 15
|
|
)
|
|
)
|
|
AND (
|
|
(
|
|
(t1.part_number || ' ' || COALESCE(t1.standard_name, '') || ' ' || COALESCE(t1.category, '')) !~* '(breakout|aoc|dac|twinax|active optical cable)'
|
|
AND (t2.part_number || ' ' || COALESCE(t2.standard_name, '') || ' ' || COALESCE(t2.category, '')) !~* '(breakout|aoc|dac|twinax|active optical cable)'
|
|
)
|
|
OR (
|
|
(t1.part_number || ' ' || COALESCE(t1.standard_name, '') || ' ' || COALESCE(t1.category, '')) ~* '(breakout|aoc|dac|twinax|active optical cable)'
|
|
AND (t2.part_number || ' ' || COALESCE(t2.standard_name, '') || ' ' || COALESCE(t2.category, '')) ~* '(breakout|aoc|dac|twinax|active optical cable)'
|
|
)
|
|
)
|
|
AND t2.id != t1.id
|
|
)
|
|
JOIN price_observations po ON po.transceiver_id = t2.id
|
|
JOIN vendors sv ON po.source_vendor_id = sv.id
|
|
WHERE t1.id = $1
|
|
AND po.time > NOW() - INTERVAL '30 days'
|
|
AND po.price > 0
|
|
AND po.url IS NOT NULL
|
|
AND COALESCE(po.is_anomalous, false) = false
|
|
-- Exclude vendors that already appear in direct prices
|
|
AND sv.id NOT IN (
|
|
SELECT source_vendor_id FROM price_observations
|
|
WHERE transceiver_id = $1 AND time > NOW() - INTERVAL '30 days'
|
|
)
|
|
ORDER BY po.source_vendor_id, po.time DESC
|
|
LIMIT 10`,
|
|
[transceiver.id]
|
|
);
|
|
|
|
const comparablePrices = comparableResult.rows.map((row) => ({
|
|
vendor_name: row.vendor_name,
|
|
vendor_type: row.vendor_type,
|
|
price: parseFloat(row.price),
|
|
currency: row.currency,
|
|
url: row.url,
|
|
observed_at: row.time,
|
|
is_verified: !!(row.url && new Date(row.time) > sevenDaysAgo),
|
|
is_same_product: false, // different SKU, same spec class
|
|
comparable_part: row.part_number || row.standard_name,
|
|
comparable_id: row.comparable_id,
|
|
// Spec details for side-by-side comparison in dashboard
|
|
comp_form_factor: row.comp_form_factor,
|
|
comp_speed_gbps: row.comp_speed_gbps ? parseFloat(row.comp_speed_gbps) : null,
|
|
comp_reach_meters: row.comp_reach_meters,
|
|
comp_reach_label: row.comp_reach_label,
|
|
comp_fiber_type: row.comp_fiber_type,
|
|
comp_wavelengths: row.comp_wavelengths,
|
|
}));
|
|
|
|
const allPrices = [...prices, ...comparablePrices];
|
|
|
|
// Price anomaly detection: flag if max/min ratio > 10x (same-product prices only)
|
|
const samePricesEur = allPrices
|
|
.filter((p) => p.is_same_product && p.price > 0)
|
|
.map((p) => {
|
|
// Normalize to EUR for comparison
|
|
if (p.currency === "EUR") return p.price;
|
|
if (p.currency === "USD") return p.price * 0.92;
|
|
if (p.currency === "GBP") return p.price * 1.17;
|
|
return p.price;
|
|
});
|
|
|
|
let priceAnomaly: { ratio: number; min_eur: number; max_eur: number } | null = null;
|
|
if (samePricesEur.length >= 2) {
|
|
const minEur = Math.min(...samePricesEur);
|
|
const maxEur = Math.max(...samePricesEur);
|
|
const ratio = minEur > 0 ? Math.round((maxEur / minEur) * 10) / 10 : 0;
|
|
if (ratio >= 10) {
|
|
priceAnomaly = { ratio, min_eur: Math.round(minEur * 100) / 100, max_eur: Math.round(maxEur * 100) / 100 };
|
|
}
|
|
}
|
|
|
|
// Last time ANY competitor scraper looked at this transceiver (regardless of result)
|
|
const lastScanResult = await pool.query(
|
|
`SELECT MAX(po.time) AS last_scan
|
|
FROM price_observations po
|
|
JOIN vendors v ON po.source_vendor_id = v.id
|
|
WHERE po.transceiver_id = $1
|
|
AND v.is_competitor = true`,
|
|
[transceiver.id]
|
|
);
|
|
const lastCompetitorScan = lastScanResult.rows[0]?.last_scan ?? null;
|
|
|
|
// Has any competitor ever listed a price for this exact product?
|
|
const competitorHasProduct = prices.some(
|
|
(p) => p.vendor_type !== "flexoptix" && p.price > 0
|
|
);
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
...transceiver,
|
|
competitor_prices: allPrices,
|
|
price_anomaly: priceAnomaly,
|
|
last_competitor_scan: lastCompetitorScan,
|
|
competitor_has_product: competitorHasProduct,
|
|
},
|
|
});
|
|
} catch (err) {
|
|
console.error("Get transceiver error:", err);
|
|
res.status(500).json({ success: false, error: "Internal server error" });
|
|
}
|
|
});
|
|
|
|
// GET /api/transceivers/:id/compatibility — Compatible switches for a transceiver
|
|
transceiverRouter.get("/:id/compatibility", async (req: Request, res: Response) => {
|
|
try {
|
|
const result = await pool.query(
|
|
`SELECT sw.id, sw.model, sw.series, sw.category, sw.total_ports,
|
|
sw.max_speed_gbps, sw.switching_capacity_tbps, sw.lifecycle_status,
|
|
v.name as vendor_name, c.status, c.notes as compat_notes
|
|
FROM compatibility c
|
|
JOIN switches sw ON c.switch_id = sw.id
|
|
LEFT JOIN vendors v ON sw.vendor_id = v.id
|
|
WHERE c.transceiver_id::text = $1 AND c.status = 'compatible'
|
|
ORDER BY v.name, sw.model`,
|
|
[String(req.params.id)]
|
|
);
|
|
res.json({ success: true, data: result.rows });
|
|
} catch (err) {
|
|
console.error("Get transceiver compatibility error:", err);
|
|
res.status(500).json({ success: false, error: "Internal server error" });
|
|
}
|
|
});
|