-- ============================================================
-- MES 注塑机数采对接
-- 数据库：mom-kyzn
-- 日期：2026-10-20
-- 说明：
--   1. 设备台账增加数采设备编号/启用标记
--   2. 新建实时、周期、工艺、参数变更、报警、原始报文表
--   3. 新增数采监控菜单与权限
--   脚本可重复执行。
-- ============================================================

-- ---------- 1. 设备台账扩展 ----------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery' AND column_name = 'collect_device_code');
SET @sql = IF(@exists = 0,
    'ALTER TABLE mes_dv_machinery ADD COLUMN collect_device_code VARCHAR(64) NULL COMMENT ''数采设备编号（MQTT devId，如 C01）'' AFTER workstation_id',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery' AND column_name = 'collect_enabled');
SET @sql = IF(@exists = 0,
    'ALTER TABLE mes_dv_machinery ADD COLUMN collect_enabled BIT(1) NOT NULL DEFAULT b''0'' COMMENT ''是否启用数采'' AFTER collect_device_code',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery' AND index_name = 'uk_collect_device_code');
SET @sql = IF(@exists = 0,
    'ALTER TABLE mes_dv_machinery ADD UNIQUE INDEX uk_collect_device_code (collect_device_code)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------- 2. 数采表 ----------
CREATE TABLE IF NOT EXISTS mes_collect_realtime (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
    sts INT DEFAULT NULL COMMENT '运行状态：1待机 2生产',
    opm INT DEFAULT NULL COMMENT '运行模式：0手动 1半自动 2电眼自动 3时间自动 4调模使用',
    asts INT DEFAULT NULL COMMENT '附加状态',
    ot DECIMAL(10,2) DEFAULT NULL COMMENT '油温',
    temps JSON DEFAULT NULL COMMENT '各段温度 T1..Tn',
    data_json JSON 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_collect_realtime_device (device_code),
    KEY idx_collect_realtime_machinery (machinery_id),
    KEY idx_collect_realtime_work_order (work_order_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-设备实时状态';

CREATE TABLE IF NOT EXISTS mes_collect_realtime_history (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
    sts INT DEFAULT NULL COMMENT '运行状态：1待机 2生产',
    opm INT DEFAULT NULL COMMENT '运行模式：0手动 1半自动 2电眼自动 3时间自动 4调模使用',
    asts INT DEFAULT NULL COMMENT '附加状态',
    ot DECIMAL(10,2) DEFAULT NULL COMMENT '油温',
    temps JSON DEFAULT NULL COMMENT '各段温度 T1..Tn',
    data_json JSON 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_collect_realtime_history (device_code, collect_time),
    KEY idx_collect_realtime_history_device_time (device_code, collect_time),
    KEY idx_collect_realtime_history_work_order (work_order_id, collect_time),
    KEY idx_collect_realtime_history_collect_time (collect_time)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-实时状态历史';

CREATE TABLE IF NOT EXISTS mes_collect_cycle (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    cycle_no BIGINT NOT NULL DEFAULT 0 COMMENT '周期序号（CYCN）',
    collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
    ecyct DECIMAL(14,4) DEFAULT NULL COMMENT '周期时间（ECYCT）',
    eiss DECIMAL(14,4) DEFAULT NULL COMMENT '射出起点（EISS）',
    eivm DECIMAL(14,4) DEFAULT NULL COMMENT '最大射速（EIVM）',
    eipm DECIMAL(14,4) DEFAULT NULL COMMENT '最大射压（EIPM）',
    esipt DECIMAL(14,4) DEFAULT NULL COMMENT '转保压时间（ESIPT）',
    esipp DECIMAL(14,4) DEFAULT NULL COMMENT '转保压压力（ESIPP）',
    esips DECIMAL(14,4) DEFAULT NULL COMMENT '转保压位置（ESIPS）',
    eipt DECIMAL(14,4) DEFAULT NULL COMMENT '射出时间（EIPT）',
    eipse DECIMAL(14,4) DEFAULT NULL COMMENT '射出终点位置（EIPSE）',
    eplst DECIMAL(14,4) DEFAULT NULL COMMENT '储料时间（EPLST）',
    eplsse DECIMAL(14,4) DEFAULT NULL COMMENT '储料终点（EPLSSE）',
    eplspm DECIMAL(14,4) DEFAULT NULL COMMENT '最大储料压力（EPLSPM）',
    et_temps JSON DEFAULT NULL COMMENT '生产温度 ET1..ETn',
    data_json JSON 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_collect_cycle (device_code, cycle_no, collect_time),
    KEY idx_collect_cycle_device_time (device_code, collect_time),
    KEY idx_collect_cycle_work_order (work_order_id, collect_time)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-周期过程数据';

CREATE TABLE IF NOT EXISTS mes_collect_tech (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    collect_time DATETIME(3) NOT NULL COMMENT '采集时间',
    data_json JSON 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_collect_tech_device (device_code),
    KEY idx_collect_tech_work_order (work_order_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-工艺参数快照';

CREATE TABLE IF NOT EXISTS mes_collect_operate_log (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    var_id VARCHAR(64) DEFAULT NULL COMMENT '变量编码',
    old_value VARCHAR(512) DEFAULT NULL COMMENT '修改前的值',
    new_value VARCHAR(512) DEFAULT NULL COMMENT '修改后的值',
    modify_time DATETIME(3) DEFAULT NULL COMMENT '参数修改时间',
    collect_time DATETIME(3) NOT NULL COMMENT '报文采集时间',
    data_json JSON 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),
    KEY idx_collect_operate_log_device_time (device_code, modify_time),
    KEY idx_collect_operate_log_work_order (work_order_id, modify_time),
    KEY idx_collect_operate_log_var (var_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-参数变更日志';

CREATE TABLE IF NOT EXISTS mes_collect_alarm (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) NOT NULL COMMENT '数采设备编号',
    task_id BIGINT DEFAULT NULL COMMENT '生产任务编号',
    work_order_id BIGINT DEFAULT NULL COMMENT '生产工单编号',
    wm_id INT DEFAULT NULL COMMENT '报警标识：2产生 0消失',
    wm_msg VARCHAR(512) DEFAULT NULL COMMENT '报警内容',
    wm_time DATETIME(3) DEFAULT NULL COMMENT '报警时间',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0未消除 1已消除',
    clear_time DATETIME(3) DEFAULT NULL COMMENT '消除时间',
    collect_time DATETIME(3) NOT NULL COMMENT '报文采集时间',
    data_json JSON 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),
    KEY idx_collect_alarm_device_status (device_code, status),
    KEY idx_collect_alarm_time (wm_time),
    KEY idx_collect_alarm_work_order (work_order_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-报警记录';

CREATE TABLE IF NOT EXISTS mes_collect_message (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    machinery_id BIGINT DEFAULT NULL COMMENT '设备台账编号',
    device_code VARCHAR(64) DEFAULT NULL COMMENT '数采设备编号',
    mqtt_topic VARCHAR(255) DEFAULT NULL COMMENT 'MQTT 主题',
    msg_type VARCHAR(32) DEFAULT NULL COMMENT '数据类型：realtime/spc/tech/opLog/alarm/unknown',
    event_time DATETIME(3) DEFAULT NULL COMMENT '设备数据时间',
    raw_payload LONGTEXT COMMENT '原始报文',
    process_status TINYINT NOT NULL DEFAULT 0 COMMENT '处理状态：0成功 1失败',
    error_msg VARCHAR(1024) DEFAULT NULL COMMENT '失败原因',
    receive_time DATETIME(3) NOT 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),
    KEY idx_collect_message_device_time (device_code, receive_time),
    KEY idx_collect_message_type_time (msg_type, receive_time),
    KEY idx_collect_message_receive_time (receive_time)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 数采-原始报文';

-- ---------- 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 (5392, '数采监控', 'mes:collect:query', 2, 9, 5300, 'collect/monitor', 'ep:monitor',
        'mes/collect/monitor/index', 'MesCollectMonitor', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
ON DUPLICATE KEY UPDATE name = VALUES(name), permission = VALUES(permission), parent_id = VALUES(parent_id),
                        path = VALUES(path), component = VALUES(component), component_name = VALUES(component_name),
                        status = VALUES(status), visible = VALUES(visible), deleted = 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 (5393, '报警确认', 'mes:collect-alarm:ack', 3, 1, 5392, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
ON DUPLICATE KEY UPDATE name = VALUES(name), permission = VALUES(permission), parent_id = VALUES(parent_id),
                        status = VALUES(status), deleted = b'0';

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