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>
385 lines
18 KiB
SQL
385 lines
18 KiB
SQL
-- Baseline schema: RFQ_LOG database as of 2026-03-21
|
||
-- State BEFORE migrations 042–049 (parts_log refactor).
|
||
-- Server version: 11.8.3-MariaDB-0+deb13u1
|
||
--
|
||
-- Tables dropped vs. earlier schema (not present here):
|
||
-- qt_client_local_migrations — old sync mechanism, removed
|
||
-- qt_price_overrides — model existed, feature never implemented
|
||
-- qt_users — model existed, auth never implemented in PriceForge
|
||
--
|
||
-- Tables kept but not referenced in Go code:
|
||
-- machine, machine_log — legacy machine quote log, historical data only
|
||
|
||
CREATE TABLE `lot` (
|
||
`lot_name` char(255) NOT NULL COMMENT 'Партномер Нетвелл',
|
||
`lot_category` char(255) DEFAULT NULL COMMENT 'Категория компоненты',
|
||
`lot_description` varchar(10000) DEFAULT NULL COMMENT 'Длинное описание для спецификаций',
|
||
UNIQUE KEY `lot_name` (`lot_name`),
|
||
KEY `lot_name_2` (`lot_name`),
|
||
KEY `idx_lot_category` (`lot_category`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `lot_log` (
|
||
`lot_log_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Номер строки (авто)',
|
||
`lot` char(255) NOT NULL COMMENT 'Партномер Нетвелл',
|
||
`supplier` char(255) NOT NULL COMMENT 'Поставщик',
|
||
`date` date NOT NULL COMMENT 'Дата получения цен',
|
||
`price` double NOT NULL COMMENT 'Цена',
|
||
`quality` char(255) DEFAULT NULL COMMENT 'Качество товара',
|
||
`comments` varchar(15000) DEFAULT NULL COMMENT 'Комментарии',
|
||
PRIMARY KEY (`lot_log_id`),
|
||
UNIQUE KEY `lot_log_id` (`lot_log_id`),
|
||
KEY `lot` (`lot`),
|
||
KEY `supplier` (`supplier`),
|
||
CONSTRAINT `lot_log_ibfk_1` FOREIGN KEY (`lot`) REFERENCES `lot` (`lot_name`),
|
||
CONSTRAINT `lot_log_ibfk_2` FOREIGN KEY (`supplier`) REFERENCES `supplier` (`supplier_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `machine` (
|
||
`machine_name` char(255) NOT NULL COMMENT 'Модель устройства',
|
||
`machine_description` varchar(10000) DEFAULT NULL COMMENT 'Описание',
|
||
UNIQUE KEY `machine_name` (`machine_name`),
|
||
KEY `machine_name_2` (`machine_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `machine_log` (
|
||
`machine_log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'номер строки (авто)',
|
||
`date` date NOT NULL DEFAULT current_timestamp() COMMENT 'Дата получения',
|
||
`supplier` char(255) NOT NULL COMMENT 'Поставщик',
|
||
`country` char(16) DEFAULT NULL,
|
||
`opty` char(16) DEFAULT NULL,
|
||
`type` char(255) DEFAULT NULL,
|
||
`machine` char(255) NOT NULL,
|
||
`customer_requirement` char(255) DEFAULT NULL,
|
||
`variant` char(255) DEFAULT NULL,
|
||
`price_gpl` float DEFAULT NULL,
|
||
`price_estimate` float DEFAULT NULL,
|
||
`qty` int(11) DEFAULT NULL,
|
||
`quality` char(255) DEFAULT NULL,
|
||
`carepack` char(255) DEFAULT NULL,
|
||
`lead_time_weeks` int(11) DEFAULT NULL,
|
||
`prepayment_percent` decimal(10,0) DEFAULT NULL,
|
||
`price_got` float NOT NULL,
|
||
`Comment` varchar(10000) DEFAULT NULL,
|
||
PRIMARY KEY (`machine_log_id`),
|
||
KEY `machine_log_id` (`machine_log_id`),
|
||
KEY `supplier` (`supplier`),
|
||
KEY `machine` (`machine`),
|
||
CONSTRAINT `machine_log_ibfk_1` FOREIGN KEY (`machine`) REFERENCES `machine` (`machine_name`),
|
||
CONSTRAINT `machine_log_ibfk_2` FOREIGN KEY (`supplier`) REFERENCES `supplier` (`supplier_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `partnumber_log_competitors` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`competitor_id` bigint(20) UNSIGNED NOT NULL,
|
||
`partnumber` varchar(255) NOT NULL,
|
||
`description` varchar(500) DEFAULT NULL,
|
||
`vendor` varchar(255) DEFAULT NULL,
|
||
`price` decimal(12,2) NOT NULL,
|
||
`price_loccur` decimal(12,2) DEFAULT NULL,
|
||
`currency` varchar(10) DEFAULT NULL,
|
||
`qty` decimal(12,4) NOT NULL DEFAULT 1.0000,
|
||
`date` date NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_plc_competitor_id` (`competitor_id`),
|
||
KEY `idx_plc_competitor_lot_date` (`competitor_id`,`date`),
|
||
CONSTRAINT `fk_plc_competitor` FOREIGN KEY (`competitor_id`) REFERENCES `qt_competitors` (`id`) ON UPDATE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_categories` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`code` varchar(20) NOT NULL,
|
||
`name` varchar(100) NOT NULL,
|
||
`name_ru` varchar(100) DEFAULT NULL,
|
||
`display_order` bigint(20) DEFAULT 0,
|
||
`is_required` tinyint(1) DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_qt_categories_code` (`code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_client_schema_state` (
|
||
`username` varchar(100) NOT NULL,
|
||
`hostname` varchar(255) NOT NULL DEFAULT '',
|
||
`last_applied_migration_id` varchar(128) DEFAULT NULL,
|
||
`app_version` varchar(64) DEFAULT NULL,
|
||
`last_sync_at` datetime DEFAULT NULL,
|
||
`last_sync_status` varchar(32) DEFAULT NULL,
|
||
`pending_changes_count` int(11) NOT NULL DEFAULT 0,
|
||
`pending_errors_count` int(11) NOT NULL DEFAULT 0,
|
||
`configurations_count` int(11) NOT NULL DEFAULT 0,
|
||
`projects_count` int(11) NOT NULL DEFAULT 0,
|
||
`estimate_pricelist_version` varchar(128) DEFAULT NULL,
|
||
`warehouse_pricelist_version` varchar(128) DEFAULT NULL,
|
||
`competitor_pricelist_version` varchar(128) DEFAULT NULL,
|
||
`last_sync_error_code` varchar(128) DEFAULT NULL,
|
||
`last_sync_error_text` text DEFAULT NULL,
|
||
`last_checked_at` datetime NOT NULL,
|
||
`updated_at` datetime NOT NULL,
|
||
PRIMARY KEY (`username`,`hostname`),
|
||
KEY `idx_qt_client_schema_state_checked` (`last_checked_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_competitors` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(255) NOT NULL,
|
||
`code` varchar(100) NOT NULL,
|
||
`delivery_basis` varchar(50) NOT NULL DEFAULT 'DDP',
|
||
`currency` varchar(10) NOT NULL DEFAULT 'USD',
|
||
`column_mapping` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`column_mapping`)),
|
||
`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(),
|
||
`price_uplift` decimal(8,4) NOT NULL DEFAULT 1.3000 COMMENT 'Price divisor: effective_price = price / price_uplift. Default 1.3.',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `code` (`code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
-- NOTE: column names are quotes_last30d and quotes_last7d (NO underscore before digit).
|
||
-- Go model uses explicit gorm:"column:..." tags for these fields.
|
||
CREATE TABLE `qt_component_usage_stats` (
|
||
`lot_name` varchar(255) NOT NULL,
|
||
`quotes_total` bigint(20) DEFAULT 0,
|
||
`quotes_last30d` bigint(20) DEFAULT 0,
|
||
`quotes_last7d` bigint(20) DEFAULT 0,
|
||
`total_quantity` bigint(20) DEFAULT 0,
|
||
`total_revenue` decimal(14,2) DEFAULT 0.00,
|
||
`trend_direction` enum('up','stable','down') DEFAULT 'stable',
|
||
`trend_percent` decimal(5,2) DEFAULT 0.00,
|
||
`last_used_at` datetime(3) DEFAULT NULL,
|
||
PRIMARY KEY (`lot_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_configurations` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`uuid` varchar(36) NOT NULL,
|
||
`user_id` bigint(20) UNSIGNED DEFAULT NULL,
|
||
`owner_username` varchar(100) NOT NULL DEFAULT '',
|
||
`app_version` varchar(64) DEFAULT NULL,
|
||
`project_uuid` char(36) DEFAULT NULL,
|
||
`name` varchar(200) NOT NULL,
|
||
`items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`items`)),
|
||
`total_price` decimal(12,2) DEFAULT NULL,
|
||
`notes` text DEFAULT NULL,
|
||
`is_template` tinyint(1) DEFAULT 0,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`custom_price` decimal(12,2) DEFAULT NULL,
|
||
`server_count` bigint(20) DEFAULT 1,
|
||
`server_model` varchar(100) DEFAULT NULL,
|
||
`support_code` varchar(20) DEFAULT NULL,
|
||
`article` varchar(80) DEFAULT NULL,
|
||
`pricelist_id` bigint(20) UNSIGNED DEFAULT NULL,
|
||
`warehouse_pricelist_id` bigint(20) UNSIGNED DEFAULT NULL,
|
||
`competitor_pricelist_id` bigint(20) UNSIGNED DEFAULT NULL,
|
||
`disable_price_refresh` tinyint(1) NOT NULL DEFAULT 0,
|
||
`only_in_stock` tinyint(1) NOT NULL DEFAULT 0,
|
||
`line_no` int(11) DEFAULT NULL,
|
||
`price_updated_at` timestamp NULL DEFAULT NULL,
|
||
`vendor_spec` longtext DEFAULT NULL CHECK (json_valid(`vendor_spec`)),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_qt_configurations_uuid` (`uuid`),
|
||
KEY `fk_qt_configurations_user` (`user_id`),
|
||
KEY `idx_qt_configurations_owner_username` (`owner_username`),
|
||
KEY `idx_qt_configurations_project_uuid` (`project_uuid`),
|
||
KEY `idx_qt_configurations_pricelist_id` (`pricelist_id`),
|
||
KEY `idx_qt_configurations_warehouse_pricelist_id` (`warehouse_pricelist_id`),
|
||
KEY `idx_qt_configurations_competitor_pricelist_id` (`competitor_pricelist_id`),
|
||
KEY `idx_qt_configurations_project_line_no` (`project_uuid`,`line_no`),
|
||
CONSTRAINT `fk_qt_configurations_competitor_pricelist_id` FOREIGN KEY (`competitor_pricelist_id`) REFERENCES `qt_pricelists` (`id`) ON UPDATE CASCADE,
|
||
CONSTRAINT `fk_qt_configurations_pricelist_id` FOREIGN KEY (`pricelist_id`) REFERENCES `qt_pricelists` (`id`) ON UPDATE CASCADE,
|
||
CONSTRAINT `fk_qt_configurations_project_uuid` FOREIGN KEY (`project_uuid`) REFERENCES `qt_projects` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE,
|
||
CONSTRAINT `fk_qt_configurations_warehouse_pricelist_id` FOREIGN KEY (`warehouse_pricelist_id`) REFERENCES `qt_pricelists` (`id`) ON UPDATE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
-- NOTE: PK is (lot_name) before migration 046; becomes (lot_name, pricelist_type) after.
|
||
CREATE TABLE `qt_lot_metadata` (
|
||
`lot_name` varchar(255) NOT NULL,
|
||
`category_id` bigint(20) UNSIGNED DEFAULT NULL,
|
||
`vendor` varchar(50) DEFAULT NULL,
|
||
`model` varchar(100) DEFAULT NULL,
|
||
`specs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specs`)),
|
||
`current_price` decimal(12,2) DEFAULT NULL,
|
||
`price_method` enum('manual','median','average','weighted_median') DEFAULT 'median',
|
||
`price_period_days` bigint(20) DEFAULT 90,
|
||
`price_updated_at` datetime(3) DEFAULT NULL,
|
||
`request_count` bigint(20) DEFAULT 0,
|
||
`last_request_date` date DEFAULT NULL,
|
||
`popularity_score` decimal(10,4) DEFAULT 0.0000,
|
||
`price_coefficient` decimal(5,2) DEFAULT 0.00,
|
||
`manual_price` decimal(12,2) DEFAULT NULL,
|
||
`meta_prices` varchar(1000) DEFAULT NULL,
|
||
`meta_method` varchar(20) DEFAULT NULL,
|
||
`meta_period_days` bigint(20) DEFAULT 90,
|
||
`is_hidden` tinyint(1) DEFAULT 0,
|
||
PRIMARY KEY (`lot_name`),
|
||
KEY `fk_qt_lot_metadata_category` (`category_id`),
|
||
CONSTRAINT `fk_qt_lot_metadata_category` FOREIGN KEY (`category_id`) REFERENCES `qt_categories` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_partnumber_books` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`version` varchar(30) NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
||
`created_by` varchar(100) NOT NULL DEFAULT '',
|
||
`is_active` tinyint(1) NOT NULL DEFAULT 0,
|
||
`partnumbers_json` longtext NOT NULL DEFAULT '[]',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_qt_partnumber_books_version` (`version`),
|
||
KEY `idx_qt_partnumber_books_is_active` (`is_active`),
|
||
KEY `idx_qt_partnumber_books_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_partnumber_book_items` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`partnumber` varchar(255) NOT NULL,
|
||
`lots_json` longtext NOT NULL,
|
||
`description` varchar(10000) DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_qt_partnumber_book_items_partnumber` (`partnumber`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_pricelists` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`source` varchar(20) NOT NULL DEFAULT 'estimate',
|
||
`version` varchar(20) NOT NULL,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`created_by` varchar(100) DEFAULT NULL,
|
||
`is_active` tinyint(1) DEFAULT 1,
|
||
`usage_count` bigint(20) DEFAULT 0,
|
||
`expires_at` datetime(3) DEFAULT NULL,
|
||
`notification` varchar(500) DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_qt_pricelists_source_version` (`source`,`version`),
|
||
KEY `idx_qt_pricelists_source_created_at` (`source`,`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
-- NOTE: gains lead_time_weeks INT NULL in migration 047.
|
||
CREATE TABLE `qt_pricelist_items` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`pricelist_id` bigint(20) UNSIGNED NOT NULL,
|
||
`lot_name` varchar(255) NOT NULL,
|
||
`lot_category` varchar(50) DEFAULT NULL,
|
||
`price` decimal(12,2) NOT NULL,
|
||
`price_method` varchar(20) DEFAULT NULL,
|
||
`price_period_days` bigint(20) DEFAULT 90,
|
||
`price_coefficient` decimal(5,2) DEFAULT 0.00,
|
||
`manual_price` decimal(12,2) DEFAULT NULL,
|
||
`meta_prices` varchar(1000) DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_pricelist_lot` (`pricelist_id`,`lot_name`),
|
||
KEY `idx_qt_pricelist_items_category` (`lot_category`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_pricelist_sync_status` (
|
||
`username` varchar(100) NOT NULL,
|
||
`last_sync_at` datetime NOT NULL,
|
||
`updated_at` datetime NOT NULL,
|
||
`app_version` varchar(64) DEFAULT NULL,
|
||
PRIMARY KEY (`username`),
|
||
KEY `idx_qt_pricelist_sync_status_last_sync` (`last_sync_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_pricing_alerts` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`lot_name` varchar(255) NOT NULL,
|
||
`alert_type` enum('high_demand_stale_price','price_spike','price_drop','no_recent_quotes','trending_no_price') NOT NULL,
|
||
`severity` enum('low','medium','high','critical') DEFAULT 'medium',
|
||
`message` text NOT NULL,
|
||
`details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
|
||
`status` enum('new','acknowledged','resolved','ignored') DEFAULT 'new',
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_projects` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`uuid` char(36) NOT NULL,
|
||
`owner_username` varchar(100) NOT NULL,
|
||
`code` varchar(100) NOT NULL,
|
||
`variant` varchar(100) NOT NULL DEFAULT '',
|
||
`name` varchar(200) DEFAULT NULL,
|
||
`tracker_url` varchar(500) DEFAULT NULL,
|
||
`is_active` tinyint(1) NOT NULL DEFAULT 1,
|
||
`is_system` tinyint(1) NOT NULL DEFAULT 0,
|
||
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
||
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uuid` (`uuid`),
|
||
UNIQUE KEY `idx_qt_projects_code_variant` (`code`,`variant`),
|
||
KEY `idx_qt_projects_owner_username` (`owner_username`),
|
||
KEY `idx_qt_projects_is_active` (`is_active`),
|
||
KEY `idx_qt_projects_is_system` (`is_system`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_scheduler_runs` (
|
||
`job_name` varchar(100) NOT NULL,
|
||
`last_started_at` datetime DEFAULT NULL,
|
||
`last_finished_at` datetime DEFAULT NULL,
|
||
`last_status` varchar(20) NOT NULL DEFAULT 'idle',
|
||
`last_error` text DEFAULT NULL,
|
||
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
||
PRIMARY KEY (`job_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_schema_migrations` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`filename` varchar(255) NOT NULL,
|
||
`applied_at` datetime(3) DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_qt_schema_migrations_filename` (`filename`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `qt_vendor_partnumber_seen` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`source_type` varchar(32) NOT NULL,
|
||
`vendor` varchar(255) NOT NULL DEFAULT '',
|
||
`partnumber` varchar(255) NOT NULL,
|
||
`description` varchar(10000) DEFAULT NULL,
|
||
`last_seen_at` datetime(3) NOT NULL,
|
||
`is_ignored` tinyint(1) NOT NULL DEFAULT 0,
|
||
`is_pattern` tinyint(1) NOT NULL DEFAULT 0,
|
||
`ignored_at` datetime(3) DEFAULT NULL,
|
||
`ignored_by` varchar(100) DEFAULT NULL,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_qt_vendor_partnumber_seen_partnumber` (`partnumber`),
|
||
KEY `idx_qt_vendor_partnumber_seen_vendor_partnumber` (`vendor`,`partnumber`),
|
||
KEY `idx_qt_vendor_partnumber_seen_ignored` (`is_ignored`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `stock_ignore_rules` (
|
||
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`target` varchar(20) NOT NULL,
|
||
`match_type` varchar(20) NOT NULL,
|
||
`pattern` varchar(500) NOT NULL,
|
||
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_stock_ignore_rule` (`target`,`match_type`,`pattern`),
|
||
KEY `idx_stock_ignore_target` (`target`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
CREATE TABLE `stock_log` (
|
||
`stock_log_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`partnumber` varchar(255) NOT NULL,
|
||
`supplier` varchar(255) DEFAULT NULL,
|
||
`date` date NOT NULL,
|
||
`price` decimal(12,2) NOT NULL,
|
||
`quality` varchar(255) DEFAULT NULL,
|
||
`comments` text DEFAULT NULL,
|
||
`vendor` varchar(255) DEFAULT NULL,
|
||
`qty` decimal(14,3) DEFAULT NULL,
|
||
PRIMARY KEY (`stock_log_id`),
|
||
KEY `idx_stock_log_lot_date` (`partnumber`,`date`),
|
||
KEY `idx_stock_log_date` (`date`),
|
||
KEY `idx_stock_log_vendor` (`vendor`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|
||
|
||
-- NOTE: extended in migration 042 with supplier_code, supplier_type, price_uplift, etc.
|
||
CREATE TABLE `supplier` (
|
||
`supplier_name` char(255) NOT NULL COMMENT 'Название поставщика',
|
||
`supplier_comment` varchar(10000) DEFAULT NULL COMMENT 'Примечание',
|
||
UNIQUE KEY `supplier_name` (`supplier_name`),
|
||
KEY `supplier_name_2` (`supplier_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
|