-- 每月能耗预警：保留原每日规则和通知历史，增加自然月去重及独立月通知模板。
-- 先执行 energy_alert_analysis_upgrade.sql；可重复执行。不创建规则、不发送通知。
SET @energy_sql=IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='mes_energy_alert_log' AND column_name='period_type'),
'SELECT 1','ALTER TABLE mes_energy_alert_log ADD COLUMN period_type varchar(10) NOT NULL DEFAULT ''DAY'' COMMENT ''DAY每日/MONTH自然月，record_date存周期首日'' AFTER record_date');
PREPARE energy_stmt FROM @energy_sql;
EXECUTE energy_stmt;
DEALLOCATE PREPARE energy_stmt;
SET @energy_sql=IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='mes_energy_alert_log' AND index_name='uk_energy_period_recipient'),
'SELECT 1','ALTER TABLE mes_energy_alert_log ADD UNIQUE KEY uk_energy_period_recipient(tenant_id,rule_id,record_date,period_type,recipient_id)');
PREPARE energy_stmt FROM @energy_sql;
EXECUTE energy_stmt;
DEALLOCATE PREPARE energy_stmt;
SET @energy_sql=IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='mes_energy_alert_log' AND index_name='uk_energy_daily_recipient'),
'ALTER TABLE mes_energy_alert_log DROP INDEX uk_energy_daily_recipient','SELECT 1');
PREPARE energy_stmt FROM @energy_sql;
EXECUTE energy_stmt;
DEALLOCATE PREPARE energy_stmt;
INSERT INTO system_notify_template(name,code,nickname,content,type,params,status,remark,creator,updater,deleted)
SELECT '每月能耗超限预警','energy_monthly_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_monthly_warning' AND deleted=0);