qipai/database/migrations/2026062219_m06b_device_topology.up.sql

ALTER TABLE qipai_devices
  ADD COLUMN iccid VARCHAR(32) NULL AFTER imei,
  ADD COLUMN model VARCHAR(64) NOT NULL DEFAULT '' AFTER device_type,
  ADD COLUMN firmware_version VARCHAR(64) NOT NULL DEFAULT '' AFTER model,
  ADD COLUMN signal_strength SMALLINT NULL AFTER status,
  ADD COLUMN capabilities JSON NULL AFTER signal_strength,
  ADD COLUMN state_snapshot JSON NULL AFTER capabilities,
  ADD COLUMN last_heartbeat_at DATETIME(3) NULL AFTER last_seen_at,
  ADD COLUMN maintenance_status VARCHAR(32) NOT NULL DEFAULT 'NORMAL' AFTER last_heartbeat_at,
  ADD KEY idx_qipai_devices_tenant_imei (tenant_id, imei),
  ADD UNIQUE KEY uq_qipai_devices_tenant_iccid (tenant_id, iccid),
  ADD KEY idx_qipai_devices_store_room (tenant_id, store_id, room_id);

CREATE TABLE IF NOT EXISTS qipai_device_channels (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NOT NULL,
  channel_code VARCHAR(32) NOT NULL,
  purpose VARCHAR(32) NOT NULL,
  target_key VARCHAR(255) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_device_channel_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_device_channel_device
    FOREIGN KEY (device_id) REFERENCES qipai_devices(id),
  CONSTRAINT fk_qipai_device_channel_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_device_channel_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  UNIQUE KEY uq_qipai_device_channel_code (tenant_id, device_id, channel_code),
  UNIQUE KEY uq_qipai_device_channel_target (tenant_id, target_key),
  KEY idx_qipai_device_channel_room (tenant_id, store_id, room_id, enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_device_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  parent_device_id BIGINT UNSIGNED NOT NULL,
  child_device_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NOT NULL,
  link_type VARCHAR(32) NOT NULL DEFAULT 'SUB_1G',
  sub_id VARCHAR(64) NOT NULL,
  subtype VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'BOUND',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_device_link_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_device_link_parent
    FOREIGN KEY (parent_device_id) REFERENCES qipai_devices(id),
  CONSTRAINT fk_qipai_device_link_child
    FOREIGN KEY (child_device_id) REFERENCES qipai_devices(id),
  CONSTRAINT fk_qipai_device_link_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_device_link_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  UNIQUE KEY uq_qipai_device_link_child (tenant_id, child_device_id),
  UNIQUE KEY uq_qipai_device_link_sub (tenant_id, parent_device_id, sub_id),
  UNIQUE KEY uq_qipai_device_link_room_type (tenant_id, room_id, link_type),
  KEY idx_qipai_device_link_parent (tenant_id, parent_device_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_device_status_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  online_status VARCHAR(32) NOT NULL,
  signal_strength SMALLINT NULL,
  firmware_version VARCHAR(64) NOT NULL DEFAULT '',
  snapshot JSON NULL,
  captured_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_device_snapshot_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_device_snapshot_device
    FOREIGN KEY (device_id) REFERENCES qipai_devices(id),
  KEY idx_qipai_device_snapshot_device (tenant_id, device_id, captured_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_device_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  alert_type VARCHAR(64) NOT NULL,
  severity VARCHAR(16) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'OPEN',
  summary VARCHAR(255) NOT NULL DEFAULT '',
  first_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  last_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  resolved_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_device_alert_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_device_alert_device
    FOREIGN KEY (device_id) REFERENCES qipai_devices(id),
  CONSTRAINT fk_qipai_device_alert_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_device_alert_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  UNIQUE KEY uq_qipai_device_alert_open
    (tenant_id, device_id, alert_type, status),
  KEY idx_qipai_device_alert_store
    (tenant_id, store_id, status, severity, last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_device_maintenance_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  record_type VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  resolved_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_device_maintenance_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_device_maintenance_device
    FOREIGN KEY (device_id) REFERENCES qipai_devices(id),
  CONSTRAINT fk_qipai_device_maintenance_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_device_maintenance_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  CONSTRAINT fk_qipai_device_maintenance_user
    FOREIGN KEY (created_by) REFERENCES qipai_users(id),
  KEY idx_qipai_device_maintenance_status
    (tenant_id, store_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO qipai_permissions (code, name, category) VALUES
  ('device.read', '查看设备', 'device'),
  ('device.write', '管理设备', 'device');

INSERT IGNORE INTO qipai_role_permissions (tenant_id, role_id, permission_id)
SELECT r.tenant_id, r.id, p.id
FROM qipai_roles r
INNER JOIN qipai_permissions p
  ON (r.code = 'STORE_ADMIN' AND p.code IN ('device.read', 'device.write'))
  OR (r.code IN ('TENANT_ADMIN', 'PLATFORM_ADMIN')
      AND p.code IN ('device.read', 'device.write'))
WHERE r.deleted_at IS NULL;

INSERT IGNORE INTO qipai_schema_migrations (version, name)
VALUES ('2026062219', 'm06b_device_topology');