41 lines
2.0 KiB
SQL
41 lines
2.0 KiB
SQL
-- 碎片合成功能
|
||
|
||
-- 1. product_categories 新增碎片标记
|
||
ALTER TABLE product_categories ADD COLUMN is_fragment TINYINT NOT NULL DEFAULT 0 COMMENT '是否碎片分类：0否 1是';
|
||
|
||
-- 2. 碎片合成配方表（主表）
|
||
CREATE TABLE fragment_synthesis_recipes (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
name VARCHAR(100) NOT NULL COMMENT '合成配方名称',
|
||
description VARCHAR(500) NOT NULL DEFAULT '' COMMENT '合成配方描述',
|
||
target_product_id BIGINT NOT NULL COMMENT '合成目标商品ID（products.id）',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 2禁用',
|
||
deleted_at DATETIME(3) NULL,
|
||
INDEX idx_target_product (target_product_id)
|
||
) COMMENT '碎片合成配方';
|
||
|
||
-- 3. 碎片合成配方材料表（子表）
|
||
CREATE TABLE fragment_synthesis_recipe_materials (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
recipe_id BIGINT NOT NULL COMMENT '配方ID（fragment_synthesis_recipes.id）',
|
||
fragment_product_id BIGINT NOT NULL COMMENT '碎片商品ID（products.id）',
|
||
required_count INT NOT NULL DEFAULT 1 COMMENT '该碎片所需数量',
|
||
INDEX idx_recipe (recipe_id),
|
||
INDEX idx_fragment_product (fragment_product_id)
|
||
) COMMENT '碎片合成配方材料';
|
||
|
||
-- 4. 碎片合成日志表
|
||
CREATE TABLE fragment_synthesis_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
recipe_id BIGINT NOT NULL COMMENT '配方ID',
|
||
consumed_inventory_ids JSON NOT NULL COMMENT '消耗的碎片资产ID列表',
|
||
produced_inventory_id BIGINT NOT NULL COMMENT '合成产出的资产ID（user_inventory.id）',
|
||
INDEX idx_user (user_id),
|
||
INDEX idx_recipe (recipe_id)
|
||
) COMMENT '碎片合成日志';
|