-- =========================================
-- 决策支持模块初始化脚本
-- 4 张新表 + 菜单 + 角色授权
-- 电力数转 phase11
-- =========================================

-- 1. KPI指标定义表
CREATE TABLE IF NOT EXISTS `bi_kpi_definition` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `code` varchar(100) NOT NULL COMMENT 'KPI编码',
    `name` varchar(200) NOT NULL COMMENT 'KPI名称',
    `category` varchar(50) NOT NULL COMMENT '分类：power_supply/device_operation/production/quality/procurement/safety',
    `unit` varchar(50) DEFAULT NULL COMMENT '单位',
    `data_source` varchar(50) NOT NULL DEFAULT 'sql' COMMENT '数据来源：sql/interface/manual',
    `query_sql` text COMMENT '查询SQL',
    `chart_type` varchar(50) DEFAULT 'number' COMMENT '默认图表类型',
    `threshold_warn` decimal(20,4) DEFAULT NULL COMMENT '预警阈值-警告',
    `threshold_critical` decimal(20,4) DEFAULT NULL COMMENT '预警阈值-严重',
    `comparison_operator` varchar(10) DEFAULT '>' COMMENT '比较运算符：>/</>=/<=',
    `refresh_interval` int DEFAULT 300 COMMENT '刷新间隔(秒)',
    `sort` int DEFAULT 0,
    `status` tinyint DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    `remark` varchar(500) DEFAULT 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',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI指标定义表';

-- 2. 预警规则表
CREATE TABLE IF NOT EXISTS `bi_alert_rule` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `kpi_id` bigint NOT NULL COMMENT '关联KPI指标ID',
    `name` varchar(200) NOT NULL COMMENT '规则名称',
    `severity` tinyint NOT NULL DEFAULT 1 COMMENT '严重级别：1-警告 2-严重 3-紧急',
    `comparison_operator` varchar(10) NOT NULL DEFAULT '>' COMMENT '比较运算符',
    `threshold_value` decimal(20,4) NOT NULL COMMENT '阈值',
    `notification_type` varchar(100) DEFAULT NULL COMMENT '通知方式：system/message/email(逗号分隔)',
    `enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用 1-启用',
    `remark` varchar(500) DEFAULT 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',
    PRIMARY KEY (`id`),
    KEY `idx_kpi_id` (`kpi_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警规则表';

-- 3. 预警记录表
CREATE TABLE IF NOT EXISTS `bi_alert_record` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `rule_id` bigint NOT NULL COMMENT '关联规则ID',
    `kpi_id` bigint NOT NULL COMMENT '关联KPI指标ID',
    `kpi_code` varchar(100) NOT NULL COMMENT 'KPI编码(冗余)',
    `kpi_name` varchar(200) NOT NULL COMMENT 'KPI名称(冗余)',
    `actual_value` decimal(20,4) NOT NULL COMMENT '实际值',
    `threshold_value` decimal(20,4) NOT NULL COMMENT '阈值(冗余)',
    `severity` tinyint NOT NULL DEFAULT 1 COMMENT '严重级别',
    `alert_message` varchar(500) DEFAULT NULL COMMENT '预警消息',
    `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-未处理 1-已确认 2-已处理',
    `handler_id` bigint DEFAULT NULL COMMENT '处理人ID',
    `handle_time` datetime DEFAULT NULL COMMENT '处理时间',
    `handle_remark` varchar(500) DEFAULT NULL COMMENT '处理备注',
    `triggered_time` datetime NOT NULL COMMENT '触发时间',
    `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',
    PRIMARY KEY (`id`),
    KEY `idx_kpi_code` (`kpi_code`),
    KEY `idx_status` (`status`),
    KEY `idx_triggered_time` (`triggered_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警记录表';

-- 4. 负荷预测数据表
CREATE TABLE IF NOT EXISTS `bi_forecast_data` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `forecast_code` varchar(100) NOT NULL COMMENT '预测编码，如：load_forecast_daily',
    `forecast_name` varchar(200) NOT NULL COMMENT '预测名称',
    `point_time` datetime NOT NULL COMMENT '预测时间点',
    `forecast_value` decimal(20,4) NOT NULL COMMENT '预测值',
    `actual_value` decimal(20,4) DEFAULT NULL COMMENT '实际值(回填用于校验)',
    `lower_bound` decimal(20,4) DEFAULT NULL COMMENT '置信区间下限',
    `upper_bound` decimal(20,4) DEFAULT NULL COMMENT '置信区间上限',
    `confidence_level` decimal(5,2) DEFAULT 95.00 COMMENT '置信水平(%)',
    `model_version` varchar(50) DEFAULT NULL COMMENT '模型版本',
    `dimension` varchar(100) DEFAULT NULL COMMENT '维度，如：region/line/station',
    `dimension_value` varchar(100) DEFAULT NULL COMMENT '维度值',
    `remark` varchar(500) DEFAULT 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',
    PRIMARY KEY (`id`),
    KEY `idx_forecast_code` (`forecast_code`),
    KEY `idx_point_time` (`point_time`),
    KEY `idx_dimension` (`dimension`, `dimension_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='负荷预测数据表';

-- =========================================
-- 菜单
-- =========================================
-- 挂在现有「决策支持」(1063000) 下
-- 菜单 ID 从 1064000 起

-- 先删除已存在的菜单（幂等）
DELETE FROM `system_menu` WHERE `id` BETWEEN 1064000 AND 1064099 AND `deleted` = b'0';

-- 电力KPI看板
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064000, '电力KPI看板', '', 2, 6, 1063000, 'kpi-dashboard', 'ep:data-board', 'bi/decision/kpi-dashboard/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 预警中心
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064001, '预警中心', '', 2, 7, 1063000, 'alert-center', 'ep:warning', 'bi/decision/alert-center/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 负荷预测
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064002, '负荷预测', '', 2, 8, 1063000, 'forecast-analysis', 'ep:trend-charts', 'bi/decision/forecast-analysis/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 趋势分析
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064003, '趋势分析', '', 2, 9, 1063000, 'trend-analysis', 'ep:data-line', 'bi/decision/trend-analysis/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- KPI指标定义（挂在配置管理 1063006 下）
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064004, 'KPI指标定义', '', 2, 3, 1063006, 'kpi-config', 'ep:set-up', 'bi/decision/kpi-config/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 预警规则配置（挂在配置管理 1063006 下）
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064005, '预警规则配置', '', 2, 3, 1063006, 'alert-rule', 'ep:bell', 'bi/decision/alert-rule/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 按钮权限
-- KPI指标定义 按钮
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064061, 'KPI指标查询', 'bi:decision:kpi:query', 3, 1, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064062, 'KPI指标新增', 'bi:decision:kpi:create', 3, 2, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064063, 'KPI指标修改', 'bi:decision:kpi:update', 3, 3, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064064, 'KPI指标删除', 'bi:decision:kpi:delete', 3, 4, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 预警规则配置 按钮
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064071, '预警规则查询', 'bi:decision:alert-rule:query', 3, 1, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064072, '预警规则新增', 'bi:decision:alert-rule:create', 3, 2, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064073, '预警规则修改', 'bi:decision:alert-rule:update', 3, 3, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064074, '预警规则删除', 'bi:decision:alert-rule:delete', 3, 4, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 预警中心 按钮
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064081, '预警记录查询', 'bi:decision:alert:query', 3, 1, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064082, '预警确认', 'bi:decision:alert:confirm', 3, 2, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1064083, '预警处理', 'bi:decision:alert:handle', 3, 3, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- =========================================
-- 角色授权（角色 2=超级管理员, 109/111 参见已有配置）
-- =========================================
-- 超级管理员 全部授权
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`)
SELECT r.id, m.id, '1', NOW()
FROM `system_role` r CROSS JOIN `system_menu` m
WHERE r.id IN (2) AND m.id BETWEEN 1064000 AND 1064099 AND m.deleted = b'0'
AND NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.role_id = r.id AND rm.menu_id = m.id);