-- ============================================================
-- 总成检具（OP320）NG点位表
-- 对照 GP12：
--   mes_gp12_items      -> mes_gauge_points      （点位/不良项字典）
--   mes_product_gp12    -> mes_product_gauge_ng  （工件NG记录，多选点位）
-- 设备界面点位：J1 ~ J56
-- ============================================================

-- 1) 点位字典表（下拉复选数据源，可后续扩展描述）
CREATE TABLE IF NOT EXISTS `mes_gauge_points` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `point_code` varchar(20) NOT NULL COMMENT '点位编码，如 J1',
  `point_name` varchar(100) DEFAULT NULL COMMENT '点位名称/说明',
  `oprno` varchar(20) DEFAULT NULL COMMENT '适用工位，空=通用',
  `sort` int(11) DEFAULT 0 COMMENT '排序号',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1删除 2停用）',
  `create_by` varchar(64) NOT NULL DEFAULT 'system' COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) NOT NULL DEFAULT 'system' COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  `remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mes_gauge_points_code` (`point_code`),
  KEY `idx_mes_gauge_points_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='总成检具点位字典';

-- 2) 工件 NG 点位记录表（点 NG 并复选后写入）
-- ng_points 存多选结果，逗号分隔，例如：J1,J3,J15
CREATE TABLE IF NOT EXISTS `mes_product_gauge_ng` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `sn` varchar(64) NOT NULL COMMENT '壳体号/工件码',
  `oprno` varchar(20) DEFAULT NULL COMMENT '工位号',
  `line_sn` varchar(10) DEFAULT NULL COMMENT '产线编号',
  `result` varchar(10) NOT NULL DEFAULT 'NG' COMMENT '判定结果，默认NG',
  `ng_points` varchar(1000) NOT NULL COMMENT 'NG点位，逗号分隔，如 J1,J5,J12',
  `ng_point_count` int(11) DEFAULT 0 COMMENT 'NG点位数量',
  `ucode` varchar(64) DEFAULT NULL COMMENT '操作人',
  `remark` varchar(1000) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) NOT NULL DEFAULT 'system' COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) NOT NULL DEFAULT 'system' COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_mes_product_gauge_ng_sn` (`sn`),
  KEY `idx_mes_product_gauge_ng_oprno` (`oprno`),
  KEY `idx_mes_product_gauge_ng_create_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='总成检具工件NG点位记录';

-- 3) 初始化 J1 ~ J56 点位字典
DELETE FROM `mes_gauge_points` WHERE `point_code` REGEXP '^J([1-9]|[1-4][0-9]|5[0-6])$';

INSERT INTO `mes_gauge_points`
(`id`, `point_code`, `point_name`, `oprno`, `sort`, `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`)
VALUES
('gauge_pt_J01', 'J1',  '检具点位J1',  NULL, 1,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J02', 'J2',  '检具点位J2',  NULL, 2,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J03', 'J3',  '检具点位J3',  NULL, 3,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J04', 'J4',  '检具点位J4',  NULL, 4,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J05', 'J5',  '检具点位J5',  NULL, 5,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J06', 'J6',  '检具点位J6',  NULL, 6,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J07', 'J7',  '检具点位J7',  NULL, 7,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J08', 'J8',  '检具点位J8',  NULL, 8,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J09', 'J9',  '检具点位J9',  NULL, 9,  '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J10', 'J10', '检具点位J10', NULL, 10, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J11', 'J11', '检具点位J11', NULL, 11, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J12', 'J12', '检具点位J12', NULL, 12, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J13', 'J13', '检具点位J13', NULL, 13, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J14', 'J14', '检具点位J14', NULL, 14, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J15', 'J15', '检具点位J15', NULL, 15, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J16', 'J16', '检具点位J16', NULL, 16, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J17', 'J17', '检具点位J17', NULL, 17, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J18', 'J18', '检具点位J18', NULL, 18, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J19', 'J19', '检具点位J19', NULL, 19, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J20', 'J20', '检具点位J20', NULL, 20, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J21', 'J21', '检具点位J21', NULL, 21, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J22', 'J22', '检具点位J22', NULL, 22, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J23', 'J23', '检具点位J23', NULL, 23, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J24', 'J24', '检具点位J24', NULL, 24, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J25', 'J25', '检具点位J25', NULL, 25, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J26', 'J26', '检具点位J26', NULL, 26, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J27', 'J27', '检具点位J27', NULL, 27, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J28', 'J28', '检具点位J28', NULL, 28, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J29', 'J29', '检具点位J29', NULL, 29, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J30', 'J30', '检具点位J30', NULL, 30, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J31', 'J31', '检具点位J31', NULL, 31, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J32', 'J32', '检具点位J32', NULL, 32, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J33', 'J33', '检具点位J33', NULL, 33, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J34', 'J34', '检具点位J34', NULL, 34, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J35', 'J35', '检具点位J35', NULL, 35, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J36', 'J36', '检具点位J36', NULL, 36, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J37', 'J37', '检具点位J37', NULL, 37, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J38', 'J38', '检具点位J38', NULL, 38, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J39', 'J39', '检具点位J39', NULL, 39, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J40', 'J40', '检具点位J40', NULL, 40, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J41', 'J41', '检具点位J41', NULL, 41, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J42', 'J42', '检具点位J42', NULL, 42, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J43', 'J43', '检具点位J43', NULL, 43, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J44', 'J44', '检具点位J44', NULL, 44, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J45', 'J45', '检具点位J45', NULL, 45, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J46', 'J46', '检具点位J46', NULL, 46, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J47', 'J47', '检具点位J47', NULL, 47, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J48', 'J48', '检具点位J48', NULL, 48, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J49', 'J49', '检具点位J49', NULL, 49, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J50', 'J50', '检具点位J50', NULL, 50, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J51', 'J51', '检具点位J51', NULL, 51, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J52', 'J52', '检具点位J52', NULL, 52, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J53', 'J53', '检具点位J53', NULL, 53, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J54', 'J54', '检具点位J54', NULL, 54, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J55', 'J55', '检具点位J55', NULL, 55, '0', 'system', NOW(), 'system', NOW(), NULL),
('gauge_pt_J56', 'J56', '检具点位J56', NULL, 56, '0', 'system', NOW(), 'system', NOW(), NULL);