biz-api-cloud-zax/db/20260904_add_er_yin_printer.sql

-- 尔印独立打印终端、终端一对一绑定及持久化打印任务。
-- 先执行硬件库结构，再执行UPMS字典和按钮权限；所有新增MQTT字段保持向后兼容。
-- 本脚本需要 MySQL 8.x 兼容的窗口函数，历史序号回填和批量主键分配使用 ROW_NUMBER()。

ALTER TABLE `pjcloudx_hardware`.`hard_invoice`
    MODIFY COLUMN `hard_id` bigint(20) NULL COMMENT '来源业务或核销终端id',
    MODIFY COLUMN `pic` varchar(512) NULL COMMENT '后端生成的打印PNG地址';

-- 每个结构变更独立判断，可在兼容数据库自动提交部分DDL后安全重跑整个脚本。
SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'printer_hard_id') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `printer_hard_id` bigint(20) NULL COMMENT ''目标尔印打印终端id'' AFTER `hard_id`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'print_type') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `print_type` char(2) NOT NULL DEFAULT ''1'' COMMENT ''打印类型，1发票、2兑换券'' AFTER `order_id`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'print_no') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `print_no` int(11) NOT NULL DEFAULT 1 COMMENT ''同订单同类型打印序号'' AFTER `print_type`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'retry_version') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `retry_version` int(11) NOT NULL DEFAULT 0 COMMENT ''人工失败重试版本'' AFTER `print_no`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'attempt_count') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `attempt_count` int(11) NOT NULL DEFAULT 0 COMMENT ''终端USB累计尝试次数'' AFTER `retry_version`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'failure_stage') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `failure_stage` varchar(32) NULL COMMENT ''BINDING、IMAGE_GENERATION、MQTT_DISPATCH或TERMINAL_PRINT'' AFTER `status`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'last_error') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `last_error` varchar(2000) NULL COMMENT ''最近失败摘要'' AFTER `failure_stage`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'printed_at') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `printed_at` datetime NULL COMMENT ''终端确认完整USB数据和切纸命令写入成功时间'' AFTER `last_error`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `column_name` = 'reprint_reason') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `reprint_reason` varchar(500) NULL COMMENT ''人工重打或再次核销原因'' AFTER `printed_at`',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

-- 唯一索引尚未建立才回填历史发票，避免迁移完成后重跑时覆盖新产生的兑换券任务。
SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`statistics`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `index_name` = 'uk_hard_invoice_order_type_no') = 0,
    'UPDATE `pjcloudx_hardware`.`hard_invoice` invoice
     JOIN (
         SELECT ranked.`id`, ranked.`print_no`
         FROM (
             SELECT `id`, ROW_NUMBER() OVER (
                 PARTITION BY `tenant_id`, `order_id`
                 ORDER BY `create_time`, `id`
             ) AS `print_no`
             FROM `pjcloudx_hardware`.`hard_invoice`
         ) ranked
     ) ranked_rows ON ranked_rows.`id` = invoice.`id`
     SET invoice.`print_type` = ''1'',
         invoice.`print_no` = ranked_rows.`print_no`,
         invoice.`retry_version` = 0,
         invoice.`attempt_count` = 0',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`statistics`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `index_name` = 'uk_hard_invoice_order_type_no') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD UNIQUE KEY `uk_hard_invoice_order_type_no` (`tenant_id`, `order_id`, `print_type`, `print_no`)',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`statistics`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `index_name` = 'idx_hard_invoice_printer_status') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD KEY `idx_hard_invoice_printer_status` (`tenant_id`, `printer_hard_id`, `status`, `update_time`, `del_flag`)',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

SET @er_yin_ddl = IF(
    (SELECT COUNT(*) FROM `information_schema`.`statistics`
     WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
       AND `index_name` = 'idx_hard_invoice_source_status') = 0,
    'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD KEY `idx_hard_invoice_source_status` (`tenant_id`, `hard_id`, `status`, `del_flag`)',
    'SELECT 1'
);
PREPARE er_yin_stmt FROM @er_yin_ddl;
EXECUTE er_yin_stmt;
DEALLOCATE PREPARE er_yin_stmt;

CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_printer_binding`
(
    `id`              bigint(20) NOT NULL COMMENT '绑定关系主键',
    `source_hard_id`  bigint(20) NOT NULL COMMENT 'SUPERMARKET或CANTEEN来源业务终端id',
    `printer_hard_id` bigint(20) NOT NULL COMMENT 'EY_PRINT目标打印终端id',
    `merchant_id`     bigint(20) NOT NULL COMMENT '双方共同所属商户id快照',
    `dept_id`         bigint(20) NOT NULL COMMENT '双方共同所属商户部门id快照',
    `create_by`       varchar(64) DEFAULT NULL COMMENT '创建人',
    `create_time`     datetime DEFAULT NULL COMMENT '创建时间',
    `update_by`       varchar(64) DEFAULT NULL COMMENT '修改人',
    `update_time`     datetime DEFAULT NULL COMMENT '修改时间',
    `tenant_id`       bigint(20) NOT NULL COMMENT '租户ID',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_hard_printer_binding_source` (`tenant_id`, `source_hard_id`),
    UNIQUE KEY `uk_hard_printer_binding_printer` (`tenant_id`, `printer_hard_id`),
    KEY `idx_hard_printer_binding_merchant` (`tenant_id`, `merchant_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
  COMMENT = '业务终端与尔印打印终端一对一绑定';

CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_printer_config`
(
    `id`              bigint(20) NOT NULL COMMENT '配置主键',
    `printer_hard_id` bigint(20) NOT NULL COMMENT 'EY_PRINT打印终端id',
    `printer_model`   varchar(128) DEFAULT NULL COMMENT '打印机型号',
    `usb_vendor_id`   int(11) DEFAULT NULL COMMENT 'USB Vendor ID十进制值',
    `usb_product_id`  int(11) DEFAULT NULL COMMENT 'USB Product ID十进制值',
    `paper_width`     int(11) NOT NULL DEFAULT 80 COMMENT '纸宽，单位mm，首期固定80',
    `auto_cut`        char(1) NOT NULL DEFAULT '1' COMMENT '自动切刀开关，1开启、0关闭',
    `image_mode`      varchar(16) NOT NULL DEFAULT 'GS_V0' COMMENT '图片指令模式，GS_V0或ESC_STAR',
    `create_by`       varchar(64) DEFAULT NULL COMMENT '创建人',
    `create_time`     datetime DEFAULT NULL COMMENT '创建时间',
    `update_by`       varchar(64) DEFAULT NULL COMMENT '修改人',
    `update_time`     datetime DEFAULT NULL COMMENT '修改时间',
    `tenant_id`       bigint(20) NOT NULL COMMENT '租户ID',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_hard_printer_config_printer` (`tenant_id`, `printer_hard_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
  COMMENT = '尔印打印终端USB与ESC/POS配置';

-- 字典和权限主键使用迁移日期种子或现有最大ID后的连续号，与项目其他迁移保持一致。
SET @er_yin_dict_item_id_base = (
    SELECT GREATEST(COALESCE(MAX(`id`), 0), 2026090400000000000)
    FROM `pjcloudx`.`sys_dict_item`
);

-- 在每个已经配置hard_type的租户中加入尔印打印终端。
INSERT INTO `pjcloudx`.`sys_dict_item`
(`id`, `dict_id`, `item_value`, `label`, `dict_type`, `description`, `sort_order`, `create_by`, `create_time`, `remarks`, `del_flag`, `tenant_id`)
SELECT @er_yin_dict_item_id_base + ROW_NUMBER() OVER (ORDER BY d.`tenant_id`, d.`id`),
       d.`id`, '12', '尔印打印机器', 'hard_type', '独立USB打印终端', 12,
       'system', NOW(), '尔印80mm ESC/POS打印终端', '0', d.`tenant_id`
FROM `pjcloudx`.`sys_dict` d
WHERE d.`dict_type` = 'hard_type'
  AND d.`del_flag` = '0'
  AND NOT EXISTS (
    SELECT 1 FROM `pjcloudx`.`sys_dict_item` i
    WHERE i.`tenant_id` = d.`tenant_id` AND i.`dict_type` = 'hard_type'
      AND i.`item_value` = '12' AND i.`del_flag` = '0'
  );

-- 打印类型和状态字典按租户幂等创建，供管理端筛选与状态展示。
SET @er_yin_dict_id_base = (
    SELECT GREATEST(COALESCE(MAX(`id`), 0), 2026090400000000000)
    FROM `pjcloudx`.`sys_dict`
);

INSERT INTO `pjcloudx`.`sys_dict`
(`id`, `dict_type`, `description`, `create_by`, `create_time`, `remarks`, `system_flag`, `del_flag`, `tenant_id`)
SELECT @er_yin_dict_id_base + ROW_NUMBER() OVER (ORDER BY tenant.`tenant_id`, t.`dict_type`),
       t.`dict_type`, t.`description`, 'system', NOW(), t.`description`, '1', '0', tenant.`tenant_id`
FROM (SELECT DISTINCT `tenant_id` FROM `pjcloudx`.`sys_dict` WHERE `del_flag` = '0') tenant
CROSS JOIN (
    SELECT 'hard_print_type' AS `dict_type`, '打印凭证类型' AS `description`
    UNION ALL SELECT 'hard_print_status', '打印任务状态'
) t
WHERE NOT EXISTS (
    SELECT 1 FROM `pjcloudx`.`sys_dict` d
    WHERE d.`tenant_id` = tenant.`tenant_id` AND d.`dict_type` = t.`dict_type` AND d.`del_flag` = '0'
  );

SET @er_yin_dict_item_id_base = (
    SELECT GREATEST(COALESCE(MAX(`id`), 0), 2026090400000000000)
    FROM `pjcloudx`.`sys_dict_item`
);

INSERT INTO `pjcloudx`.`sys_dict_item`
(`id`, `dict_id`, `item_value`, `label`, `dict_type`, `description`, `sort_order`, `create_by`, `create_time`, `del_flag`, `tenant_id`)
SELECT @er_yin_dict_item_id_base + ROW_NUMBER() OVER (
           ORDER BY d.`tenant_id`, d.`dict_type`, item.`sort_order`, item.`item_value`
       ),
       d.`id`, item.`item_value`, item.`label`, d.`dict_type`, item.`label`, item.`sort_order`,
       'system', NOW(), '0', d.`tenant_id`
FROM `pjcloudx`.`sys_dict` d
JOIN (
    SELECT 'hard_print_type' AS `dict_type`, '1' AS `item_value`, '发票' AS `label`, 1 AS `sort_order`
    UNION ALL SELECT 'hard_print_type', '2', '兑换券', 2
    UNION ALL SELECT 'hard_print_status', '0', '待生成', 0
    UNION ALL SELECT 'hard_print_status', '1', '待打印', 1
    UNION ALL SELECT 'hard_print_status', '2', '已打印', 2
    UNION ALL SELECT 'hard_print_status', '3', '打印失败', 3
    UNION ALL SELECT 'hard_print_status', '4', '已取消', 4
) item ON item.`dict_type` = d.`dict_type`
WHERE d.`del_flag` = '0'
  AND NOT EXISTS (
    SELECT 1 FROM `pjcloudx`.`sys_dict_item` i
    WHERE i.`tenant_id` = d.`tenant_id` AND i.`dict_type` = d.`dict_type`
      AND i.`item_value` = item.`item_value` AND i.`del_flag` = '0'
  );

-- 在已有设备管理与打印记录菜单下补充按钮权限，不假定各环境菜单主键。
SET @er_yin_menu_id_base = (
    SELECT GREATEST(COALESCE(MAX(`menu_id`), 0), 2026090400000000000)
    FROM `pjcloudx`.`sys_menu`
);

INSERT INTO `pjcloudx`.`sys_menu`
(`menu_id`, `name`, `permission`, `parent_id`, `visible`, `sort_order`, `keep_alive`, `embedded`, `menu_type`, `create_by`, `create_time`, `del_flag`, `tenant_id`)
SELECT @er_yin_menu_id_base + ROW_NUMBER() OVER (ORDER BY parent_menu.`tenant_id`, parent_menu.`menu_id`),
       '绑定打印机', 'hard_hardManager_printer_bind', parent_menu.`menu_id`,
       '1', 20, '0', '0', '2', 'system', NOW(), '0', parent_menu.`tenant_id`
FROM `pjcloudx`.`sys_menu` parent_menu
WHERE parent_menu.`permission` = 'hard_hardManager_view' AND parent_menu.`del_flag` = '0'
  AND NOT EXISTS (
    SELECT 1 FROM `pjcloudx`.`sys_menu` existing
    WHERE existing.`tenant_id` = parent_menu.`tenant_id`
      AND existing.`permission` = 'hard_hardManager_printer_bind' AND existing.`del_flag` = '0'
  );

SET @er_yin_menu_id_base = (
    SELECT GREATEST(COALESCE(MAX(`menu_id`), 0), 2026090400000000000)
    FROM `pjcloudx`.`sys_menu`
);

INSERT INTO `pjcloudx`.`sys_menu`
(`menu_id`, `name`, `permission`, `parent_id`, `visible`, `sort_order`, `keep_alive`, `embedded`, `menu_type`, `create_by`, `create_time`, `del_flag`, `tenant_id`)
SELECT @er_yin_menu_id_base + ROW_NUMBER() OVER (ORDER BY parent_menu.`tenant_id`, parent_menu.`menu_id`),
       '重打兑换券', 'hard_hardInvoice_voucher_reprint', parent_menu.`menu_id`,
       '1', 20, '0', '0', '2', 'system', NOW(), '0', parent_menu.`tenant_id`
FROM `pjcloudx`.`sys_menu` parent_menu
WHERE parent_menu.`permission` = 'hard_hardInvoice_view' AND parent_menu.`del_flag` = '0'
  AND NOT EXISTS (
    SELECT 1 FROM `pjcloudx`.`sys_menu` existing
    WHERE existing.`tenant_id` = parent_menu.`tenant_id`
      AND existing.`permission` = 'hard_hardInvoice_voucher_reprint' AND existing.`del_flag` = '0'
  );

SET @er_yin_dict_id_base = NULL;
SET @er_yin_dict_item_id_base = NULL;
SET @er_yin_menu_id_base = NULL;
SET @er_yin_ddl = NULL;