-- 独立能耗管理：新增台账及一级菜单，不关联其他业务表。
-- 可重复执行。执行前请确认目标库，菜单只默认分配给超级管理员（role_id=1）。
CREATE TABLE IF NOT EXISTS `mes_energy_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录编号',
  `record_date` date NOT NULL COMMENT '统计日期',
  `energy_type` varchar(20) NOT NULL COMMENT 'WATER水/ELECTRICITY电/STEAM蒸汽',
  `usage_area` varchar(100) NOT NULL COMMENT '使用区域（手工填写）',
  `product_category` varchar(50) NOT NULL COMMENT '产品类别（本模块）',
  `usage_purpose` varchar(100) NOT NULL COMMENT '用途/工序（手工填写）',
  `quantity` decimal(18,3) NOT NULL COMMENT '用量，水m³/电kWh/蒸汽t',
  `unit_price` decimal(12,4) NOT NULL DEFAULT 0 COMMENT '元/能源单位',
  `amount` decimal(26,2) NOT NULL DEFAULT 0 COMMENT '费用=用量*单价，四舍五入到分',
  `recorder` varchar(50) NOT NULL COMMENT '录入人姓名（手工填写）',
  `remark` varchar(500) NOT NULL 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',
  `tenant_id` bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_energy_date` (`tenant_id`, `deleted`, `record_date`, `energy_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='独立能耗手工台账';

START TRANSACTION;
SET @energy_root = (SELECT id FROM system_menu WHERE parent_id=0 AND path='/energy' AND deleted=b'0' LIMIT 1);
SET @energy_next = (SELECT IFNULL(MAX(id),0)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'能耗管理','',1,65,0,'/energy','lucide:zap','','',0,1,1,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_root IS NULL;
SET @energy_root = COALESCE(@energy_root,@energy_next);

SET @energy_record = (SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='record' AND deleted=b'0' LIMIT 1);
SET @energy_next = (SELECT MAX(id)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'能耗记录','energy:record:query',2,1,@energy_root,'record','lucide:clipboard-list','energy/record/index','EnergyRecord',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_record IS NULL;
SET @energy_record = COALESCE(@energy_record,@energy_next);

SET @energy_analysis = (SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='analysis' AND deleted=b'0' LIMIT 1);
SET @energy_next = (SELECT MAX(id)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'数据分析','energy:analysis:query',2,2,@energy_root,'analysis','lucide:chart-no-axes-combined','energy/analysis/index','EnergyAnalysis',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_analysis IS NULL;
SET @energy_analysis = COALESCE(@energy_analysis,@energy_next);

SET @energy_next = (SELECT MAX(id)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'新增能耗记录','energy:record:create',3,1,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:create' AND deleted=b'0');
SET @energy_next = (SELECT MAX(id)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'修改能耗记录','energy:record:update',3,2,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:update' AND deleted=b'0');
SET @energy_next = (SELECT MAX(id)+1 FROM system_menu);
INSERT INTO system_menu (id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,create_time,updater,update_time,deleted)
SELECT @energy_next,'删除能耗记录','energy:record:delete',3,3,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:delete' AND deleted=b'0');

INSERT INTO system_role_menu (role_id,menu_id,creator,create_time,updater,update_time,deleted,tenant_id)
SELECT 1,m.id,'1',NOW(),'1',NOW(),0,r.tenant_id FROM system_menu m JOIN system_role r ON r.id=1 AND r.deleted=b'0'
WHERE m.deleted=b'0' AND (m.id=@energy_root OR m.parent_id=@energy_root OR m.parent_id=@energy_record)
AND NOT EXISTS (SELECT 1 FROM system_role_menu rm WHERE rm.role_id=1 AND rm.menu_id=m.id AND rm.deleted=b'0');
COMMIT;