-- 生产设备大屏(dashboard_id=3) 新增 4 类统计维度（5 个图表） | 目标库 mom-hm(远端 36.213.150.237) | 2026-10-15
-- 背景：该大屏此前仅有工单/设备计数、工单状态、工单类型、工单列表等，缺设备与维保维度的统计口径。
-- 维度（均已按远端真实数据验证非空）：
--   ① 设备按车间分布   (pie)  9 个车间 4~31 台
--   ② 点检/保养月度趋势 (line) 2026-09/10（远端仅这两个月有记录），双序列
--   ③ 点检完成率       (gauge) 81.0%  = 已完成(20)/总数
--   ④ 保养完成率       (gauge) 81.5%  = 已提交(4)/总数
--   ⑤ 点检计划类型分布  (pie)  点检计划 102 / 保养计划 284
-- 说明：月度趋势用单图双序列（点检/保养）呈现，等价于"折线×2"。
-- 口径：状态枚举 MesDvCheckRecordStatusEnum(10 草稿/20 已完成)、MesDvMaintenRecordStatusEnum(0 草稿/4 已提交)、
--       MesDvCheckPlanTypeEnum(1 点检/2 保养)。后端 DashboardServiceImpl 无缓存，status=1 且按 sort 排序，插入即生效。
-- 幂等：先按 id 删本脚本 5 行再插入。回滚：见文件末尾注释。

START TRANSACTION;

DELETE FROM bi_chart_config WHERE dashboard_id = 3 AND id IN (53, 54, 55, 56, 57);

INSERT INTO bi_chart_config
  (id, dashboard_id, name, chart_type, data_source_id, query_sql, position, chart_options, refresh_interval, sort, status,
   creator, create_time, updater, update_time, deleted)
VALUES
  (53, 3, '设备按车间分布', 'pie', NULL,
   'SELECT w.name AS name, COUNT(*) AS value FROM mes_dv_machinery m JOIN mes_md_workshop w ON w.id = m.workshop_id AND w.deleted = 0 WHERE m.deleted = 0 GROUP BY w.id, w.name ORDER BY w.id',
   '{"h": 8, "w": 6, "x": 0, "y": 12}', NULL, 0, 9, 1, '1', NOW(), '1', NOW(), b'0'),

  (54, 3, '点检·保养月度趋势', 'line', NULL,
   'SELECT t.m AS name, SUM(t.check_cnt) AS \'点检\', SUM(t.mainten_cnt) AS \'保养\' FROM (SELECT DATE_FORMAT(check_time,\'%Y-%m\') AS m, 1 AS check_cnt, 0 AS mainten_cnt FROM mes_dv_check_record WHERE deleted = 0 UNION ALL SELECT DATE_FORMAT(mainten_time,\'%Y-%m\') AS m, 0 AS check_cnt, 1 AS mainten_cnt FROM mes_dv_mainten_record WHERE deleted = 0) t GROUP BY t.m ORDER BY t.m',
   '{"h": 8, "w": 6, "x": 6, "y": 12}', NULL, 0, 10, 1, '1', NOW(), '1', NOW(), b'0'),

  (55, 3, '点检完成率', 'gauge', NULL,
   'SELECT ROUND(SUM(status = 20) * 100.0 / NULLIF(COUNT(*), 0), 1) AS value FROM mes_dv_check_record WHERE deleted = 0',
   '{"h": 4, "w": 3, "x": 0, "y": 20}', NULL, 0, 11, 1, '1', NOW(), '1', NOW(), b'0'),

  (56, 3, '保养完成率', 'gauge', NULL,
   'SELECT ROUND(SUM(status = 4) * 100.0 / NULLIF(COUNT(*), 0), 1) AS value FROM mes_dv_mainten_record WHERE deleted = 0',
   '{"h": 4, "w": 3, "x": 3, "y": 20}', NULL, 0, 12, 1, '1', NOW(), '1', NOW(), b'0'),

  (57, 3, '点检计划类型分布', 'pie', NULL,
   'SELECT CASE type WHEN 1 THEN \'点检计划\' WHEN 2 THEN \'保养计划\' ELSE \'其他\' END AS name, COUNT(*) AS value FROM mes_dv_check_plan WHERE deleted = 0 GROUP BY type ORDER BY type',
   '{"h": 8, "w": 6, "x": 0, "y": 24}', NULL, 0, 13, 1, '1', NOW(), '1', NOW(), b'0');

COMMIT;

-- 回滚：
-- DELETE FROM bi_chart_config WHERE dashboard_id = 3 AND id IN (53, 54, 55, 56, 57);