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>
30 lines
1.5 KiB
SQL
30 lines
1.5 KiB
SQL
-- Extend supplier table for unified quote journal.
|
|
-- Adds supplier_code (symbolic identifier), supplier_type, price_uplift,
|
|
-- default_lead_time_weeks, default_import_format_code, and is_active.
|
|
|
|
-- Step 1: add columns without unique constraint (IF NOT EXISTS for idempotency)
|
|
ALTER TABLE supplier
|
|
ADD COLUMN IF NOT EXISTS supplier_code VARCHAR(100) NOT NULL DEFAULT ''
|
|
COMMENT 'Short symbolic code: treolan, haoqian-jevy, netwell',
|
|
ADD COLUMN IF NOT EXISTS supplier_type ENUM('trader','self','competitor') NOT NULL DEFAULT 'trader',
|
|
ADD COLUMN IF NOT EXISTS price_uplift DECIMAL(8,4) NOT NULL DEFAULT 1.0000
|
|
COMMENT 'Price divisor at import: raw_price / price_uplift = USD. trader=1, self=1, competitor=1.3',
|
|
ADD COLUMN IF NOT EXISTS default_lead_time_weeks INT NULL
|
|
COMMENT 'NULL means not set (not equal to 0). self=0, competitor=2, trader=NULL',
|
|
ADD COLUMN IF NOT EXISTS default_import_format_code VARCHAR(100) NULL
|
|
COMMENT 'FK to qt_import_formats.format_code',
|
|
ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1;
|
|
|
|
-- Step 2: add non-unique index if missing
|
|
ALTER TABLE supplier
|
|
ADD INDEX IF NOT EXISTS idx_supplier_type (supplier_type);
|
|
|
|
-- Step 3: seed supplier_code from supplier_name for existing rows that still have empty code
|
|
UPDATE supplier
|
|
SET supplier_code = LOWER(REPLACE(REPLACE(TRIM(supplier_name), ' ', '-'), '_', '-'))
|
|
WHERE supplier_code = '';
|
|
|
|
-- Step 4: add unique index after codes are populated
|
|
ALTER TABLE supplier
|
|
ADD UNIQUE INDEX IF NOT EXISTS uq_supplier_code (supplier_code);
|