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>
27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
-- Import formats table: stores file format definitions for unified import.
|
|
-- Column mapping JSON uses same structure as qt_competitors.column_mapping.
|
|
CREATE TABLE IF NOT EXISTS qt_import_formats (
|
|
format_code VARCHAR(100) NOT NULL PRIMARY KEY
|
|
COMMENT 'Symbolic code: treolan_b2b, s4b_export, ocs_sklad, json_connector',
|
|
name VARCHAR(255) NOT NULL,
|
|
file_type ENUM('csv','xlsx','mxl','json') NOT NULL,
|
|
header_row INT NOT NULL DEFAULT 1,
|
|
data_start_row INT NOT NULL DEFAULT 2,
|
|
delimiter VARCHAR(5) NULL
|
|
COMMENT 'For CSV only',
|
|
encoding VARCHAR(20) NOT NULL DEFAULT 'utf-8',
|
|
column_mapping JSON NULL
|
|
COMMENT 'Column mapping — same format as qt_competitors.column_mapping',
|
|
sample_filename VARCHAR(500) NULL,
|
|
notes TEXT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Add FK from supplier to import formats (deferred until qt_import_formats exists)
|
|
ALTER TABLE supplier
|
|
ADD CONSTRAINT fk_supplier_import_format
|
|
FOREIGN KEY (default_import_format_code) REFERENCES qt_import_formats(format_code)
|
|
ON DELETE SET NULL;
|