water/server/plugin/saiadmin/db/20260822_add_merchant_device_binding.sql

-- ============================================================
-- 商户设备绑定
-- 日期: 2026-08-22
-- 来源: 旧项目 xs_market_goods / xs_market_zuo_rely /
--       xs_market_zuo_rely_record 与 Store::bindZuo2Store
-- 说明: 可重复执行，不写入演示业务数据。
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_device` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '设备编号',
    `legacy_id`             BIGINT UNSIGNED NULL COMMENT '旧系统 xs_market_goods.id',
    `goods_name`            VARCHAR(200) NOT NULL DEFAULT '' COMMENT '设备名称',
    `goods_type`            SMALLINT UNSIGNED NOT NULL COMMENT '旧系统设备类型',
    `goods_sn`              VARCHAR(60) NOT NULL COMMENT '设备唯一 SN 码',
    `device_code`           VARCHAR(60) NOT NULL DEFAULT '' COMMENT '机器码',
    `model`                 VARCHAR(60) NOT NULL DEFAULT '' COMMENT '设备型号',
    `status`                TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0未启用、1运营中、2报修',
    `belong_agent_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '设备归属代理',
    `manage_agent_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '设备当前管理代理',
    `is_locked`             TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否锁定',
    `is_active`             TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否激活',
    `active_time`           DATETIME NULL COMMENT '激活时间',
    `device_status`         TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0掉线、1正常、2维护、3异常',
    `created_by`            BIGINT UNSIGNED NULL COMMENT '创建者',
    `updated_by`            BIGINT UNSIGNED NULL COMMENT '更新者',
    `create_time`           DATETIME NULL COMMENT '创建时间',
    `update_time`           DATETIME NULL COMMENT '更新时间',
    `delete_time`           DATETIME NULL COMMENT '软删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_device_sn` (`goods_sn`),
    KEY `idx_device_manager_type` (`manage_agent_id`, `goods_type`, `delete_time`),
    KEY `idx_device_status` (`status`, `device_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备台账';

CREATE TABLE IF NOT EXISTS `sa_merchant_device_binding` (
    `device_id`             BIGINT UNSIGNED NOT NULL COMMENT '设备编号',
    `device_sn`             VARCHAR(60) NOT NULL COMMENT '设备 SN 快照',
    `depend_type`           SMALLINT UNSIGNED NOT NULL COMMENT '旧系统 depend_type',
    `merchant_id`           BIGINT UNSIGNED NOT NULL COMMENT '商户编号',
    `status`                TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '绑定状态',
    `operator_id`           BIGINT UNSIGNED NOT NULL COMMENT '铺货操作人',
    `bind_time`             DATETIME NOT NULL COMMENT '绑定时间',
    `create_time`           DATETIME NOT NULL COMMENT '创建时间',
    `update_time`           DATETIME NOT NULL COMMENT '更新时间',
    PRIMARY KEY (`device_id`),
    UNIQUE KEY `uk_binding_device_sn` (`device_sn`),
    KEY `idx_binding_merchant` (`merchant_id`),
    KEY `idx_binding_operator` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备当前绑定商户关系';

CREATE TABLE IF NOT EXISTS `sa_merchant_device_binding_record` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '流水编号',
    `device_id`             BIGINT UNSIGNED NOT NULL COMMENT '设备编号',
    `device_sn`             VARCHAR(60) NOT NULL COMMENT '设备 SN 快照',
    `depend_type`           SMALLINT UNSIGNED NOT NULL COMMENT '旧系统 depend_type',
    `merchant_id`           BIGINT UNSIGNED NOT NULL COMMENT '商户编号',
    `action_type`           TINYINT UNSIGNED NOT NULL COMMENT '1绑定、2解绑',
    `operator_id`           BIGINT UNSIGNED NOT NULL COMMENT '操作人',
    `action_time`           DATETIME NOT NULL COMMENT '操作时间',
    `created_by`            BIGINT UNSIGNED NULL COMMENT '创建者',
    `create_time`           DATETIME NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_binding_record_merchant_time` (`merchant_id`, `action_time`),
    KEY `idx_binding_record_device_time` (`device_id`, `action_time`),
    KEY `idx_binding_record_sn` (`device_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备绑定解绑流水';

INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT parent_menu.`id`, permission_data.`name`, permission_data.`code`, permission_data.`slug`, 3,
       NULL, NULL, permission_data.`method`, NULL, permission_data.`sort`, NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, permission_data.`remark`, 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` parent_menu
INNER JOIN (
    SELECT '商户绑定设备列表' AS `name`, 'MerchantDeviceIndex' AS `code`, 'core:merchant:device:index' AS `slug`, 'GET' AS `method`, 48 AS `sort`, '查看商户当前绑定设备' AS `remark`
    UNION ALL SELECT '商户绑定设备', 'MerchantDeviceBind', 'core:merchant:device:bind', 'POST', 47, '设备铺货到有权访问的商户'
    UNION ALL SELECT '商户解绑设备', 'MerchantDeviceUnbind', 'core:merchant:device:unbind', 'POST', 46, '解除设备和商户关系并保留流水'
) permission_data
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`slug` = permission_data.`slug`
      AND existing_menu.`delete_time` IS NULL
WHERE parent_menu.`code` = 'Merchant'
  AND parent_menu.`delete_time` IS NULL
  AND existing_menu.`id` IS NULL;

-- 已有“我的商户”或“下级商户”页面权限的角色自动获得设备绑定权限。
INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT source_role.`role_id`, permission_menu.`id`
FROM `sa_system_role_menu` source_role
INNER JOIN `sa_system_menu` source_menu
        ON source_menu.`id` = source_role.`menu_id`
       AND source_menu.`code` IN ('MyMerchant', 'SubordinateMerchant')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` merchant_menu
        ON merchant_menu.`code` = 'Merchant'
       AND merchant_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` permission_menu
        ON permission_menu.`parent_id` = merchant_menu.`id`
       AND permission_menu.`slug` IN (
           'core:merchant:device:index',
           'core:merchant:device:bind',
           'core:merchant:device:unbind'
       )
       AND permission_menu.`delete_time` IS NULL
LEFT JOIN `sa_system_role_menu` existing_role
       ON existing_role.`role_id` = source_role.`role_id`
      AND existing_role.`menu_id` = permission_menu.`id`
WHERE existing_role.`id` IS NULL;