117 lines
6.0 KiB
SQL
117 lines
6.0 KiB
SQL
-- SKU匹配相关表初始化脚本
|
||
|
||
-- ERP SKU表
|
||
CREATE TABLE IF NOT EXISTS `erp_skus` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`sku_code` VARCHAR(64) NOT NULL COMMENT 'SKU编码',
|
||
`name` VARCHAR(255) NOT NULL COMMENT 'SKU名称',
|
||
`platform_sku` VARCHAR(128) DEFAULT NULL COMMENT '平台SKU关联',
|
||
`alias` VARCHAR(128) DEFAULT NULL COMMENT '别名（用于匹配）',
|
||
`price` DECIMAL(10,2) DEFAULT NULL COMMENT '价格',
|
||
`stock` INT DEFAULT 0 COMMENT '库存',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY `uk_sku_code` (`sku_code`),
|
||
KEY `idx_alias` (`alias`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ERP SKU表';
|
||
|
||
-- 平台SKU表
|
||
CREATE TABLE IF NOT EXISTS `platforms` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`shop_auth_id` BIGINT NOT NULL COMMENT '店铺授权ID',
|
||
`platform_product_id` VARCHAR(128) NOT NULL COMMENT '平台商品ID',
|
||
`platform_sku_id` VARCHAR(128) DEFAULT NULL COMMENT '平台SKU ID',
|
||
`title` VARCHAR(512) DEFAULT NULL COMMENT '商品标题',
|
||
`description` TEXT COMMENT '商品描述',
|
||
`price` DECIMAL(10,2) DEFAULT NULL COMMENT '价格',
|
||
`original_price` DECIMAL(10,2) DEFAULT NULL COMMENT '原价',
|
||
`stock` INT DEFAULT 0 COMMENT '库存',
|
||
`sold` INT DEFAULT 0 COMMENT '已售数量',
|
||
`images` JSON DEFAULT NULL COMMENT '图片列表',
|
||
`specs` JSON DEFAULT NULL COMMENT '规格属性',
|
||
`status` VARCHAR(32) DEFAULT 'on_sale' COMMENT '状态：on_sale在售, off_sale下架, deleted已删除',
|
||
`sync_status` VARCHAR(32) DEFAULT 'pending' COMMENT '同步状态',
|
||
`last_sync_at` DATETIME DEFAULT NULL COMMENT '最后同步时间',
|
||
`sync_error` TEXT COMMENT '同步错误信息',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
KEY `idx_platform_product_id` (`platform_product_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_shop_auth_id` (`shop_auth_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台SKU表';
|
||
|
||
-- 平台SKU与ERP SKU绑定表
|
||
CREATE TABLE IF NOT EXISTS `platform_sku_bindings` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`platform_sku_id` BIGINT NOT NULL COMMENT '平台SKU ID',
|
||
`erp_sku_id` BIGINT NOT NULL COMMENT 'ERP SKU ID',
|
||
`is_active` TINYINT(1) DEFAULT TRUE COMMENT '是否生效',
|
||
`bound_at` DATETIME DEFAULT NULL COMMENT '绑定时间',
|
||
`unbound_at` DATETIME DEFAULT NULL COMMENT '解绑时间',
|
||
`reason` VARCHAR(255) DEFAULT NULL COMMENT '绑定原因/方式',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
KEY `idx_platform_sku_id` (`platform_sku_id`),
|
||
KEY `idx_erp_sku_id` (`erp_sku_id`),
|
||
KEY `idx_is_active` (`is_active`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台SKU与ERP SKU绑定表';
|
||
|
||
-- 匹配日志表
|
||
CREATE TABLE IF NOT EXISTS `match_logs` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`order_item_id` BIGINT NOT NULL COMMENT '订单明细ID',
|
||
`platform_sku` VARCHAR(128) NOT NULL COMMENT '平台SKU编码',
|
||
`erp_sku_id` BIGINT NOT NULL COMMENT '匹配的ERP SKU ID',
|
||
`match_type` VARCHAR(32) NOT NULL COMMENT '匹配类型：manual手动, rule规则, ai',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_platform_sku` (`platform_sku`),
|
||
KEY `idx_match_type` (`match_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='匹配日志表';
|
||
|
||
-- 未匹配日志表
|
||
CREATE TABLE IF NOT EXISTS `unmatched_logs` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`order_item_id` BIGINT NOT NULL COMMENT '订单明细ID',
|
||
`platform_sku` VARCHAR(128) NOT NULL COMMENT '平台SKU编码',
|
||
`reason` VARCHAR(255) NOT NULL COMMENT '未匹配原因',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_platform_sku` (`platform_sku`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='未匹配日志表';
|
||
|
||
-- 订单明细表（简化版，仅用于SKU匹配）
|
||
CREATE TABLE IF NOT EXISTS `order_items` (
|
||
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
||
`goods_name` VARCHAR(255) DEFAULT NULL COMMENT '商品名称',
|
||
`platform_sku` VARCHAR(128) DEFAULT NULL COMMENT '平台SKU编码',
|
||
`quantity` INT DEFAULT 1 COMMENT '数量',
|
||
`price` DECIMAL(10,2) DEFAULT NULL COMMENT '单价',
|
||
`total_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '总金额',
|
||
`match_status` TINYINT DEFAULT 0 COMMENT '匹配状态：0未匹配, 1已匹配',
|
||
`erp_sku_id` BIGINT DEFAULT NULL COMMENT 'ERP SKU ID',
|
||
`goods_id` BIGINT DEFAULT NULL COMMENT '商品ID',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_platform_sku` (`platform_sku`),
|
||
KEY `idx_match_status` (`match_status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
|
||
|
||
-- 插入测试数据
|
||
INSERT INTO `erp_skus` (`sku_code`, `name`, `alias`, `price`, `stock`) VALUES
|
||
('ERP_SKU_001', '测试ERP商品', 'ERP_ALIAS_001', 89.99, 200),
|
||
('ERP_SKU_002', '另一个ERP商品', 'ERP_ALIAS_002', 79.99, 150),
|
||
('ERP_SKU_003', '平台商品A', NULL, 99.99, 100);
|
||
|
||
INSERT INTO `platforms` (`shop_auth_id`, `platform_product_id`, `platform_sku_id`, `title`, `price`, `stock`, `status`) VALUES
|
||
(1, 'PLATFORM_SKU_001', 'PSKU_001', '测试商品标题', 99.99, 100, 'on_sale'),
|
||
(1, 'PLATFORM_SKU_002', 'PSKU_002', '另一个测试商品', 88.88, 50, 'on_sale'),
|
||
(1, 'ERP_ALIAS_001', 'PSKU_003', '别名匹配测试商品', 77.77, 30, 'on_sale'),
|
||
(1, 'NO_MATCH_SKU', 'PSKU_004', '完全不匹配的商品标题XYZ', 66.66, 20, 'on_sale'),
|
||
(1, 'OFF_SALE_SKU', 'PSKU_005', '已下架商品', 55.55, 0, 'off_sale');
|
||
|
||
INSERT INTO `platform_sku_bindings` (`platform_sku_id`, `erp_sku_id`, `is_active`, `bound_at`, `reason`) VALUES
|
||
(1, 1, TRUE, NOW(), 'manual');
|