-- 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;