-- =============================================
-- BI 决策BI大屏模块 SQL 初始化脚本
-- 包含仪表盘定义、图表配置、数据源配置相关表
-- =============================================

-- =============================================
-- 1. 仪表盘定义表
-- =============================================
CREATE TABLE IF NOT EXISTS `bi_dashboard` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(100) NOT NULL COMMENT '仪表盘名称',
    `code` varchar(100) NOT NULL COMMENT '仪表盘编码（唯一标识）',
    `group_code` varchar(50) NOT NULL COMMENT '所属分组：purchase_sales/warehouse/production_equipment/quality/hr_oa',
    `icon` varchar(100) DEFAULT NULL COMMENT '图标',
    `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
    `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    `remark` varchar(500) DEFAULT NULL COMMENT '备注',
    `creator` varchar(64) DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_group_code` (`group_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BI仪表盘定义表';

-- =============================================
-- 2. 图表配置表
-- =============================================
CREATE TABLE IF NOT EXISTS `bi_chart_config` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
    `dashboard_id` bigint NOT NULL COMMENT '所属仪表盘ID',
    `name` varchar(100) NOT NULL COMMENT '图表名称',
    `chart_type` varchar(50) NOT NULL COMMENT '图表类型：bar/line/pie/scatter/gauge/table/number',
    `data_source_id` bigint DEFAULT NULL COMMENT '数据源配置ID',
    `query_sql` text COMMENT '查询SQL（支持 {{param}} 占位符）',
    `position` json DEFAULT NULL COMMENT '布局位置 {x, y, w, h}',
    `chart_options` json DEFAULT NULL COMMENT 'ECharts 配置项覆盖',
    `refresh_interval` int NOT NULL DEFAULT 0 COMMENT '刷新间隔（秒），0表示不自动刷新',
    `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
    `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    `creator` varchar(64) DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`),
    KEY `idx_dashboard_id` (`dashboard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BI图表配置表';

-- =============================================
-- 3. 数据源配置表
-- =============================================
CREATE TABLE IF NOT EXISTS `bi_data_source_config` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(100) NOT NULL COMMENT '数据源名称',
    `type` varchar(50) NOT NULL DEFAULT 'mysql' COMMENT '类型：mysql/api',
    `config` json DEFAULT NULL COMMENT '连接配置',
    `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    `creator` varchar(64) DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BI数据源配置表';

-- =============================================
-- 4. 菜单权限
-- =============================================

-- 4.1 决策BI大屏目录
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`)
VALUES (1063000, '决策BI大屏', '', 1, 45, 0, '/bi', 'ep:data-analysis', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.2 采购销售售后大屏
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`)
VALUES (1063001, '采购销售售后大屏', '', 2, 1, 1063000, 'purchase-sales', 'ep:trend-charts', 'bi/dashboard/index', 'BiPurchaseSalesDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.3 仓库大屏
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`)
VALUES (1063002, '仓库大屏', '', 2, 2, 1063000, 'warehouse', 'ep:warehouse', 'bi/dashboard/index', 'BiWarehouseDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.4 生产设备大屏
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`)
VALUES (1063003, '生产设备大屏', '', 2, 3, 1063000, 'production-equipment', 'ep:set-up', 'bi/dashboard/index', 'BiProductionEquipmentDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.5 质量大屏
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`)
VALUES (1063004, '质量大屏', '', 2, 4, 1063000, 'quality', 'ep:circle-check', 'bi/dashboard/index', 'BiQualityDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.6 人资办公大屏
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`)
VALUES (1063005, '人资办公大屏', '', 2, 5, 1063000, 'hr-oa', 'ep:office-building', 'bi/dashboard/index', 'BiHrOaDashboard', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.7 配置管理目录
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`)
VALUES (1063006, '配置管理', '', 1, 6, 1063000, 'config', 'ep:setting', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.8 图表配置菜单
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`)
VALUES (1063007, '图表配置', '', 2, 1, 1063006, 'chart-config', 'ep:pie-chart', 'bi/config/chart/index', 'BiChartConfig', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 按钮权限
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`)
VALUES (1063071, '图表配置查询', 'bi:chart-config:query', 3, 1, 1063007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063072, '图表配置新增', 'bi:chart-config:create', 3, 2, 1063007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063073, '图表配置修改', 'bi:chart-config:update', 3, 3, 1063007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063074, '图表配置删除', 'bi:chart-config:delete', 3, 4, 1063007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 4.9 数据源配置菜单
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`)
VALUES (1063008, '数据源配置', '', 2, 2, 1063006, 'data-source-config', 'ep:connection', 'bi/config/data-source/index', 'BiDataSourceConfig', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 按钮权限
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`)
VALUES (1063081, '数据源配置查询', 'bi:data-source-config:query', 3, 1, 1063008, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063082, '数据源配置新增', 'bi:data-source-config:create', 3, 2, 1063008, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063083, '数据源配置修改', 'bi:data-source-config:update', 3, 3, 1063008, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

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`)
VALUES (1063084, '数据源配置删除', 'bi:data-source-config:delete', 3, 4, 1063008, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 5. 角色菜单关联（角色ID=1 超级管理员）
-- =============================================
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063000, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063001, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063002, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063003, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063004, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063005, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063006, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063007, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063071, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063072, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063073, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063074, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063008, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063081, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063082, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063083, '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 1063084, '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 6. 默认仪表盘数据
-- =============================================
INSERT INTO `bi_dashboard` (`id`, `name`, `code`, `group_code`, `icon`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '采购销售售后大屏', 'purchase_sales', 'purchase_sales', 'ep:trend-charts', 1, 1, '采购、销售、售后业务概览', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `bi_dashboard` (`id`, `name`, `code`, `group_code`, `icon`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '仓库大屏', 'warehouse', 'warehouse', 'ep:warehouse', 2, 1, '仓储物流业务概览', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `bi_dashboard` (`id`, `name`, `code`, `group_code`, `icon`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '生产设备大屏', 'production_equipment', 'production_equipment', 'ep:set-up', 3, 1, '生产管控、设备管理业务概览', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `bi_dashboard` (`id`, `name`, `code`, `group_code`, `icon`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (4, '质量大屏', 'quality', 'quality', 'ep:circle-check', 4, 1, '质量管理业务概览', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `bi_dashboard` (`id`, `name`, `code`, `group_code`, `icon`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (5, '人资办公大屏', 'hr_oa', 'hr_oa', 'ep:office-building', 5, 1, '人力资源、协同办公业务概览', '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 执行完成提示
-- =============================================
SELECT 'BI 决策BI大屏模块 SQL 初始化完成！' AS result;