-- =====================================================
-- 报表图表管理模块数据库脚本
-- 执行前请备份数据库
-- 创建时间: 2026-06-04
-- =====================================================

-- =============================================
-- 1. 语音播报队列表 (voice_queue) - 新增
-- =============================================
DROP TABLE IF EXISTS `voice_queue`;
CREATE TABLE `voice_queue` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `event_type` varchar(50) NOT NULL COMMENT '事件类型(sample_receive/task_assign/report_submit/emergency)',
  `event_name` varchar(100) NOT NULL COMMENT '事件名称',
  `details` varchar(500) DEFAULT '' COMMENT '事件详情',
  `voice_text` varchar(500) NOT NULL COMMENT '语音播报文本',
  `priority` int DEFAULT 0 COMMENT '优先级(0普通/1重要/2紧急)',
  `status` tinyint DEFAULT 0 COMMENT '状态(0待播报/1已播报/2已取消)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_status_priority` (`status`, `priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音播报队列表';

-- =============================================
-- 2. 报表配置表
-- =============================================
DROP TABLE IF EXISTS `report_config`;
CREATE TABLE `report_config` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `report_name` varchar(100) NOT NULL COMMENT '报表名称',
  `report_code` varchar(50) NOT NULL COMMENT '报表编码',
  `report_type` varchar(20) NOT NULL COMMENT '报表类型(sample_progress/test_item/sample_record/device_record/dashboard)',
  `query_config` text COMMENT '查询条件配置JSON',
  `column_config` text COMMENT '列配置JSON',
  `chart_config` text COMMENT '图表配置JSON',
  `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_report_code` (`report_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报表配置表';

-- =============================================
-- 3. 图表配置表
-- =============================================
DROP TABLE IF EXISTS `chart_config`;
CREATE TABLE `chart_config` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `chart_name` varchar(100) NOT NULL COMMENT '图表名称',
  `chart_code` varchar(50) NOT NULL COMMENT '图表编码',
  `chart_type` varchar(20) NOT NULL COMMENT '图表类型(bar/line/pie/radar/spc/normal)',
  `data_source` varchar(100) NOT NULL COMMENT '数据源SQL或接口标识',
  `x_axis_field` varchar(50) COMMENT 'X轴字段',
  `y_axis_field` varchar(100) COMMENT 'Y轴字段(JSON数组)',
  `query_params` text COMMENT '查询参数配置JSON',
  `chart_options` text COMMENT 'ECharts配置JSON',
  `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_chart_code` (`chart_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图表配置表';

-- =============================================
-- 4. 看板配置表
-- =============================================
DROP TABLE IF EXISTS `dashboard_config`;
CREATE TABLE `dashboard_config` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `dashboard_name` varchar(100) NOT NULL COMMENT '看板名称',
  `dashboard_code` varchar(50) NOT NULL COMMENT '看板编码',
  `layout_config` text COMMENT '布局配置JSON',
  `components` text COMMENT '组件配置JSON',
  `voice_config` text COMMENT '语音播报配置JSON',
  `refresh_interval` int DEFAULT 30 COMMENT '刷新间隔(秒)',
  `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_dashboard_code` (`dashboard_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='看板配置表';

-- =============================================
-- 5. 菜单权限配置
-- =============================================
-- 报表图表管理（一级目录）- 排在最后，order_num = 21
INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `is_rersonal_button`) VALUES
(3000, '报表图表管理', 0, 21, 'reportChart', '', NULL, '', 1, 0, 'M', '0', '0', '', 'chart', 'admin', NOW(), '', NULL, '报表图表管理目录', 0),

-- 数字化语音看板（二级目录）
(3001, '数字化语音看板', 3000, 1, 'dashboard', '', NULL, '', 1, 0, 'M', '0', '0', '', 'monitor', 'admin', NOW(), '', NULL, '数字化语音看板目录', 0),

-- 试验大厅
(3002, '试验大厅', 3001, 1, 'testHall', 'report/dashboard/index', NULL, '', 1, 0, 'C', '0', '0', 'report:dashboard:list', 'monitor', 'admin', NOW(), '', NULL, '试验大厅菜单', 0),

-- 报表管理（二级目录）
(3010, '报表管理', 3000, 2, 'report', '', NULL, '', 1, 0, 'M', '0', '0', '', 'form', 'admin', NOW(), '', NULL, '报表管理目录', 0),

-- 样品进度报表
(3011, '样品进度报表', 3010, 1, 'sampleProgress', 'report/sampleProgress/index', NULL, '', 1, 0, 'C', '0', '0', 'report:sampleProgress:list', 'table', 'admin', NOW(), '', NULL, '样品进度报表菜单', 0),

-- 检测项目数据
(3012, '检测项目数据', 3010, 2, 'testItemData', 'report/testItemData/index', NULL, '', 1, 0, 'C', '0', '0', 'report:testItemData:list', 'table', 'admin', NOW(), '', NULL, '检测项目数据菜单', 0),

-- 样品领样记录
(3013, '样品领样记录', 3010, 3, 'sampleRecord', 'report/sampleRecord/index', NULL, '', 1, 0, 'C', '0', '0', 'report:sampleRecord:list', 'table', 'admin', NOW(), '', NULL, '样品领样记录菜单', 0),

-- 设备使用记录
(3014, '设备使用记录', 3010, 4, 'deviceRecord', 'report/deviceRecord/index', NULL, '', 1, 0, 'C', '0', '0', 'report:deviceRecord:list', 'table', 'admin', NOW(), '', NULL, '设备使用记录菜单', 0),

-- 智能图表（二级目录）
(3020, '智能图表', 3000, 3, 'chart', '', NULL, '', 1, 0, 'M', '0', '0', '', 'chart', 'admin', NOW(), '', NULL, '智能图表目录', 0),

-- 工作统计
(3021, '工作统计', 3020, 1, 'workStatistics', 'report/workStatistics/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:workStatistics:list', 'peoples', 'admin', NOW(), '', NULL, '工作统计菜单', 0),

-- 合格率统计
(3022, '合格率统计', 3020, 2, 'passRate', 'report/passRate/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:passRate:list', 'chart', 'admin', NOW(), '', NULL, '合格率统计菜单', 0),

-- SPC控制图
(3023, 'SPC控制图', 3020, 3, 'spcChart', 'report/spcChart/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:spcChart:list', 'chart', 'admin', NOW(), '', NULL, 'SPC控制图菜单', 0),

-- 正态分布图
(3024, '正态分布图', 3020, 4, 'normalDistribution', 'report/normalDistribution/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:normalDistribution:list', 'chart', 'admin', NOW(), '', NULL, '正态分布图菜单', 0);

-- =============================================
-- 6. 初始化看板配置数据
-- =============================================
INSERT INTO `dashboard_config` (`dashboard_name`, `dashboard_code`, `layout_config`, `components`, `voice_config`, `refresh_interval`, `status`, `create_by`) VALUES
('试验大厅看板', 'test_hall',
'[{"i":"history","x":0,"y":0,"w":8,"h":4},{"i":"future","x":8,"y":0,"w":4,"h":4},{"i":"ranking","x":0,"y":4,"w":6,"h":3},{"i":"status","x":6,"y":4,"w":6,"h":3},{"i":"result","x":0,"y":7,"w":12,"h":3}]',
'[{"id":"history","type":"calendar","title":"历史15天检测任务","api":"/report/dashboard/history15Days"},{"id":"future","type":"calendar","title":"未来15天检测任务","api":"/report/dashboard/future15Days"},{"id":"ranking","type":"ranking","title":"提交排行","api":"/report/dashboard/ranking"},{"id":"status","type":"stat","title":"待处理统计","api":"/report/dashboard/overview"},{"id":"result","type":"chart","title":"检验结果统计","api":"/report/dashboard/insResult"}]',
'{"enabled":true,"events":["sample_receive","task_assign","report_submit","emergency"],"template":"紧急通知：{eventName}，{details}"}',
30, 1, 'admin');

-- =============================================
-- 7. 初始化报表配置数据
-- =============================================
INSERT INTO `report_config` (`report_name`, `report_code`, `report_type`, `query_config`, `status`, `create_by`) VALUES
('样品进度报表', 'sample_progress', 'sample_progress', '[{"field":"entrustCode","label":"委托编号","type":"input"},{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"sampleName","label":"样品名称","type":"input"},{"field":"insState","label":"检测状态","type":"select","options":[{"value":0,"label":"待检"},{"value":1,"label":"检验中"},{"value":5,"label":"已完成"}]}]', 1, 'admin'),
('检测项目数据', 'test_item_data', 'test_item', '[{"field":"productionOrder","label":"生产订单","type":"input"},{"field":"batchNo","label":"批次号","type":"input"},{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"itemName","label":"检测项目","type":"input"}]', 1, 'admin'),
('样品领样记录', 'sample_record', 'sample_record', '[{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"operateUser","label":"操作人","type":"input"},{"field":"startTime","label":"开始时间","type":"date"},{"field":"endTime","label":"结束时间","type":"date"}]', 1, 'admin'),
('设备使用记录', 'device_record', 'device_record', '[{"field":"deviceCode","label":"设备编号","type":"input"},{"field":"deviceName","label":"设备名称","type":"input"},{"field":"useUser","label":"使用人","type":"input"}]', 1, 'admin');

-- =============================================
-- 8. 初始化图表配置数据
-- =============================================
INSERT INTO `chart_config` (`chart_name`, `chart_code`, `chart_type`, `data_source`, `x_axis_field`, `y_axis_field`, `status`, `create_by`) VALUES
('工作统计图', 'work_statistics', 'bar', '/chart/workStatistics/byUser', 'userName', '[{"field":"sampleCount","name":"样品数量"},{"field":"itemCount","name":"项目数量"}]', 1, 'admin'),
('及时率统计', 'timely_rate', 'line', '/chart/workStatistics/timelyRate', 'userName', '[{"field":"timelyRate","name":"及时率(%)"}]', 1, 'admin'),
('原材料合格率', 'raw_material_pass_rate', 'bar', '/chart/passRate/rawMaterial', 'sampleName', '[{"field":"passRate","name":"合格率(%)"}]', 1, 'admin'),
('帕累托图', 'pareto', 'bar', '/chart/passRate/pareto', 'itemName', '[{"field":"unqualifiedCount","name":"不合格数"},{"field":"cumulativePercent","name":"累计百分比"}]', 1, 'admin'),
('工序合格率', 'process_pass_rate', 'pie', '/chart/passRate/process', 'processName', '[{"field":"passRate","name":"合格率(%)"}]', 1, 'admin'),
('SPC控制图', 'spc_chart', 'line', '/chart/spc/analyze', 'subgroup', '[{"field":"xBar","name":"X-Bar"},{"field":"ucl","name":"UCL"},{"field":"lcl","name":"LCL"}]', 1, 'admin'),
('正态分布图', 'normal_distribution', 'line', '/chart/normalDistribution/analyze', 'value', '[{"field":"frequency","name":"频率"},{"field":"normalCurve","name":"正态曲线"}]', 1, 'admin');

-- =============================================
-- 9. 初始化语音播报测试数据
-- =============================================
INSERT INTO `voice_queue` (`event_type`, `event_name`, `details`, `voice_text`, `priority`, `status`) VALUES
('sample_receive', '样品接收', '样品A001已到达实验室', '样品A001已到达实验室，请及时领取', 0, 0),
('task_assign', '任务分配', '检测任务已分配给张三', '检测任务已分配给张三，请尽快开始检测', 1, 0),
('emergency', '紧急通知', '设备故障需要维修', '紧急通知：关键检测设备出现故障，请立即联系维修人员', 2, 0);

-- =====================================================
-- 执行完成
-- =====================================================