5f8aec456b
- New unified append-only quote log table parts_log replaces three separate log tables (stock_log, partnumber_log_competitors, lot_log) - Migrations 042-049: extend supplier, create parts_log/import_formats/ ignore_rules, rework qt_lot_metadata composite PK, add lead_time_weeks to pricelist_items, backfill data, migrate ignore rules - New services: PartsLogBackfillService, ImportFormatService, UnifiedImportService; new world pricelist type (all supplier types) - qt_lot_metadata PK changed to (lot_name, pricelist_type); all queries now filter WHERE pricelist_type='estimate' - Fix pre-existing bug: qt_component_usage_stats column names quotes_last30d/quotes_last7d (no underscore) — added explicit gorm tags - Bible: full table inventory, baseline schema snapshot, updated pricelist/ data-rules/api/history/architecture docs Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
77 lines
2.5 KiB
SQL
77 lines
2.5 KiB
SQL
-- Backfill parts_log from the three legacy log tables.
|
|
-- Migration 042 seeds supplier_code = LOWER(REPLACE(supplier_name, ' ', '-')).
|
|
-- We must JOIN supplier to get the correct supplier_code for stock_log and lot_log.
|
|
-- Competitors are in qt_competitors (not supplier table), so we use c.code
|
|
-- BUT must ensure those supplier rows exist first (inserted below).
|
|
--
|
|
-- INSERT IGNORE skips rows that violate the unique index (supplier_code, partnumber, quote_date)
|
|
-- or FK constraints (unknown supplier_code).
|
|
|
|
-- Step 1: Insert competitor suppliers into supplier table so parts_log FK is satisfied.
|
|
-- Uses competitor code as supplier_code, name as supplier_name.
|
|
INSERT IGNORE INTO supplier (supplier_name, supplier_comment, supplier_code, supplier_type, price_uplift, default_lead_time_weeks, is_active)
|
|
SELECT
|
|
c.name,
|
|
CONCAT('Imported from qt_competitors id=', c.id),
|
|
c.code,
|
|
'competitor',
|
|
c.price_uplift,
|
|
2,
|
|
c.is_active
|
|
FROM qt_competitors c
|
|
WHERE c.code != '';
|
|
|
|
-- Step 2: From stock_log (self/warehouse suppliers)
|
|
-- JOIN supplier to get supplier_code from supplier_name
|
|
INSERT IGNORE INTO parts_log
|
|
(supplier_code, partnumber, vendor, price, qty, offer_type, quote_date, created_by)
|
|
SELECT
|
|
s.supplier_code,
|
|
sl.partnumber,
|
|
COALESCE(sl.vendor, ''),
|
|
sl.price,
|
|
sl.qty,
|
|
'public',
|
|
sl.date,
|
|
'migration'
|
|
FROM stock_log sl
|
|
INNER JOIN supplier s ON s.supplier_name = sl.supplier
|
|
WHERE sl.price > 0
|
|
AND sl.supplier IS NOT NULL;
|
|
|
|
-- Step 3: From partnumber_log_competitors (competitor suppliers)
|
|
-- c.code was inserted into supplier in step 1
|
|
INSERT IGNORE INTO parts_log
|
|
(supplier_code, partnumber, vendor, description, price, qty, offer_type, quote_date, created_by)
|
|
SELECT
|
|
c.code,
|
|
plc.partnumber,
|
|
COALESCE(plc.vendor, ''),
|
|
plc.description,
|
|
plc.price / NULLIF(c.price_uplift, 0),
|
|
plc.qty,
|
|
'public',
|
|
plc.date,
|
|
'migration'
|
|
FROM partnumber_log_competitors plc
|
|
INNER JOIN qt_competitors c ON c.id = plc.competitor_id
|
|
WHERE plc.price > 0;
|
|
|
|
-- Step 4: From lot_log (trader suppliers; partnumber = lot_name)
|
|
-- JOIN supplier to get supplier_code from supplier_name
|
|
INSERT IGNORE INTO parts_log
|
|
(supplier_code, partnumber, lot_name, lot_category, price, offer_type, quote_date, created_by)
|
|
SELECT
|
|
s.supplier_code,
|
|
ll.lot,
|
|
ll.lot,
|
|
l.lot_category,
|
|
ll.price,
|
|
'public',
|
|
ll.date,
|
|
'migration'
|
|
FROM lot_log ll
|
|
INNER JOIN supplier s ON s.supplier_name = ll.supplier
|
|
LEFT JOIN lot l ON l.lot_name = ll.lot
|
|
WHERE ll.price > 0;
|