-- 设备备件管理：独立备件库存，不改动现有物料仓库库存。
CREATE TABLE IF NOT EXISTS mes_dv_spare_part (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(64) NOT NULL, name VARCHAR(128) NOT NULL,
 specification VARCHAR(255) DEFAULT '', unit VARCHAR(32) NOT NULL,
 location VARCHAR(128) DEFAULT '',
 stock_quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
 safety_quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
 status TINYINT NOT NULL DEFAULT 0, remark VARCHAR(500) DEFAULT '',
 creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 deleted BIT(1) NOT NULL DEFAULT b'0',
 active_code VARCHAR(64) GENERATED ALWAYS AS (CASE WHEN deleted=b'0' THEN code ELSE NULL END) STORED,
 UNIQUE KEY uk_active_code(active_code),
 CONSTRAINT chk_spare_stock CHECK(stock_quantity>=0 AND safety_quantity>=0),
 CONSTRAINT chk_spare_status CHECK(status IN (0,1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备备件台账';
CREATE TABLE IF NOT EXISTS mes_dv_spare_movement (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 request_key VARCHAR(64) NOT NULL,
 spare_part_id BIGINT NOT NULL, spare_part_code VARCHAR(64) NOT NULL,
 spare_part_name VARCHAR(128) NOT NULL, specification VARCHAR(255) DEFAULT '', unit VARCHAR(32) NOT NULL,
 type TINYINT NOT NULL, quantity DECIMAL(18,3) NOT NULL,
 before_quantity DECIMAL(18,3) NOT NULL, after_quantity DECIMAL(18,3) NOT NULL,
 repair_id BIGINT NULL, repair_code VARCHAR(64) NULL, machinery_id BIGINT NULL,
 machinery_name VARCHAR(128) NULL, original_id BIGINT NULL,
 receiver_user_id BIGINT NULL, receiver_name VARCHAR(128) NULL,
 operator_user_id BIGINT NOT NULL, remark VARCHAR(500) NOT NULL,
 creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 deleted BIT(1) NOT NULL DEFAULT b'0',
 UNIQUE KEY uk_request_key(request_key),
 KEY idx_spare_time(spare_part_id,create_time),
 KEY idx_repair(repair_id), KEY idx_original(original_id),
 CONSTRAINT chk_spare_movement CHECK(quantity>0 AND before_quantity>=0 AND after_quantity>=0 AND type IN(1,2,3,4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='备件库存与领用流水';
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater)
SELECT '备件台账','mes:dv-spare:query',2,8,repair.parent_id,'spare','ep:box','mes/dv/spare/index','MesDvSpare',0,b'1',b'1',b'1','1','1'
FROM system_menu repair WHERE repair.component='mes/dv/repair/index' AND repair.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater)
SELECT '领用记录','mes:dv-spare:record',2,9,repair.parent_id,'spare-record','ep:tickets','mes/dv/spare-record/index','MesDvSpareRecord',0,b'1',b'1',b'1','1','1'
FROM system_menu repair WHERE repair.component='mes/dv/repair/index' AND repair.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu m WHERE m.component='mes/dv/spare-record/index' AND m.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '新增备件','mes:dv-spare:create',3,1,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:create' AND b.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '编辑备件','mes:dv-spare:update',3,2,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:update' AND b.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '删除备件','mes:dv-spare:delete',3,3,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:delete' AND b.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '备件入库','mes:dv-spare:receive',3,4,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:receive' AND b.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '维修领用','mes:dv-spare:issue',3,5,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:issue' AND b.deleted=0) LIMIT 1;
INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
SELECT '备件退回','mes:dv-spare:return',3,6,m.id,'','','',0,b'1',b'1',b'1','1','1'
FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:return' AND b.deleted=0) LIMIT 1;
-- 普通角色权限需通过角色管理明确配置；本脚本不批量扩大现有角色的权限。