-- 先执行 energy_management_init.sql；新增预警规则、通知历史、通知模板和导出权限。
-- 不创建示例规则，不向任何用户发送通知。可重复执行。
CREATE TABLE IF NOT EXISTS mes_energy_alert_rule (
 id bigint NOT NULL AUTO_INCREMENT,
 name varchar(100) NOT NULL,
 energy_type varchar(20) NOT NULL,
 usage_area varchar(100) NOT NULL DEFAULT '',
 product_category varchar(50) NOT NULL DEFAULT '',
 metric varchar(20) NOT NULL COMMENT 'QUANTITY每日用量/AMOUNT每日费用',
 threshold decimal(29,3) NOT NULL,
 enabled bit(1) NOT NULL DEFAULT b'1',
 recipient_ids varchar(1100) NOT NULL COMMENT '站内通知接收人ID，逗号分隔',
 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_rule_tenant(tenant_id,deleted,enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='独立能耗每日预警规则';

CREATE TABLE IF NOT EXISTS mes_energy_alert_log (
 id bigint NOT NULL AUTO_INCREMENT,
 rule_id bigint NOT NULL, rule_name varchar(100) NOT NULL,
 record_date date NOT NULL, energy_type varchar(20) NOT NULL,
 scope varchar(200) NOT NULL, metric varchar(20) NOT NULL,
 actual_value decimal(45,3) NOT NULL, threshold decimal(29,3) NOT NULL,
 recipient_id bigint NOT NULL, recipient_name varchar(100) NOT NULL,
 message_id bigint DEFAULT NULL,
 status varchar(20) NOT NULL COMMENT 'SENT成功/FAILED失败，可重试',
 error_message 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),
 UNIQUE KEY uk_energy_daily_recipient(tenant_id,rule_id,record_date,recipient_id),
 KEY idx_energy_log_date(tenant_id,deleted,record_date,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能耗预警通知历史及发送结果';

START TRANSACTION;
INSERT INTO system_notify_template(name,code,nickname,content,type,params,status,remark,creator,updater,deleted)
SELECT '每日能耗超限预警','energy_daily_warning','能耗管理',
'【能耗预警】{date}，{ruleName}：{scope}的{energyName}{metricName}为{actual}{unit}，超过每日阈值{threshold}{unit}。请核查台账及用能情况。',
1,'["date","ruleName","scope","energyName","metricName","actual","unit","threshold"]',0,'独立能耗管理每日阈值通知','1','1',0
FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM system_notify_template WHERE code='energy_daily_warning' AND deleted=0);

SET @energy_root=(SELECT id FROM system_menu WHERE parent_id=0 AND path='/energy' AND deleted=0 LIMIT 1);
SET @energy_record=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='record' AND deleted=0 LIMIT 1);
SET @energy_analysis=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='analysis' AND deleted=0 LIMIT 1);
SET @energy_alert=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='alert' AND deleted=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,updater,deleted)
SELECT @energy_next,'能耗预警','energy:alert:query',2,3,@energy_root,'alert','lucide:bell-ring','energy/alert/index','EnergyAlert',0,1,0,1,'1','1',0
FROM DUAL WHERE @energy_alert IS NULL AND @energy_root IS NOT NULL;
SET @energy_alert=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='alert' AND deleted=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,updater,deleted)
SELECT @energy_next,'新增预警规则','energy:alert:create',3,1,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:create' AND deleted=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,updater,deleted)
SELECT @energy_next,'修改预警规则','energy:alert:update',3,2,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:update' AND deleted=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,updater,deleted)
SELECT @energy_next,'删除预警规则','energy:alert:delete',3,3,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:delete' AND deleted=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,updater,deleted)
SELECT @energy_next,'检查预警','energy:alert:check',3,4,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:check' AND deleted=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,updater,deleted)
SELECT @energy_next,'导出能耗台账','energy:record:export',3,4,@energy_record,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_record IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:record:export' AND deleted=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,updater,deleted)
SELECT @energy_next,'导出能耗分析','energy:analysis:export',3,1,@energy_analysis,'','','','',0,1,0,1,'1','1',0 FROM DUAL
WHERE @energy_analysis IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:analysis:export' AND deleted=0);

INSERT INTO system_role_menu(role_id,menu_id,creator,updater,deleted,tenant_id)
SELECT 1,m.id,'1','1',0,r.tenant_id FROM system_menu m JOIN system_role r ON r.id=1 AND r.deleted=0
WHERE m.deleted=0 AND (m.id=@energy_alert OR m.parent_id=@energy_alert OR m.permission IN ('energy:record:export','energy:analysis:export'))
AND NOT EXISTS(SELECT 1 FROM system_role_menu rm WHERE rm.role_id=1 AND rm.menu_id=m.id AND rm.deleted=0);
COMMIT;