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>
62 lines
3.4 KiB
SQL
62 lines
3.4 KiB
SQL
-- Add pricelist_type axis to qt_lot_metadata.
|
|
-- Each lot now has up to 4 configuration rows (estimate, warehouse, competitor, world).
|
|
-- Existing rows become pricelist_type='estimate'. New rows are duplicated for all types.
|
|
|
|
-- Step 1: add new columns with defaults
|
|
ALTER TABLE qt_lot_metadata
|
|
ADD COLUMN pricelist_type VARCHAR(20) NOT NULL DEFAULT 'estimate'
|
|
COMMENT 'estimate | warehouse | competitor | world',
|
|
ADD COLUMN period_days INT NOT NULL DEFAULT 90
|
|
COMMENT 'Quote window in days',
|
|
ADD COLUMN on_missing_quotes ENUM('keep','drop') NOT NULL DEFAULT 'drop'
|
|
COMMENT 'keep=use last known price, drop=exclude from pricelist';
|
|
|
|
-- Step 2: populate period_days from existing price_period_days for estimate rows
|
|
UPDATE qt_lot_metadata SET period_days = price_period_days WHERE pricelist_type = 'estimate';
|
|
|
|
-- Step 3: duplicate for warehouse type
|
|
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden)
|
|
SELECT lot_name, 'warehouse', 'weighted_avg', 7, 'drop',
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden
|
|
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';
|
|
|
|
-- Step 4: duplicate for competitor type
|
|
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden)
|
|
SELECT lot_name, 'competitor', 'weighted_median', 90, 'drop',
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden
|
|
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';
|
|
|
|
-- Step 5: duplicate for world type
|
|
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden)
|
|
SELECT lot_name, 'world', 'weighted_median', 90, 'drop',
|
|
category_id, model, specs, current_price, price_coefficient, manual_price,
|
|
price_updated_at, request_count, last_request_date, popularity_score,
|
|
meta_prices, meta_method, meta_period_days, is_hidden
|
|
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';
|
|
|
|
-- Step 6: change primary key to composite (lot_name, pricelist_type)
|
|
ALTER TABLE qt_lot_metadata
|
|
DROP PRIMARY KEY,
|
|
ADD PRIMARY KEY (lot_name, pricelist_type);
|
|
|
|
-- Step 7: insert __default__ rows for all 4 types
|
|
INSERT IGNORE INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes)
|
|
VALUES
|
|
('__default__', 'estimate', 'median', 90, 'drop'),
|
|
('__default__', 'warehouse', 'weighted_avg', 7, 'drop'),
|
|
('__default__', 'competitor', 'weighted_median', 90, 'drop'),
|
|
('__default__', 'world', 'weighted_median', 90, 'drop');
|