-- =====================================================================
-- Migration 002 — Vincular dispositivos ao chip_uid (MAC EFuse) do ESP32
-- =====================================================================
-- IDEMPOTENTE: pode rodar varias vezes sem dar erro #1060.
-- Cada hardware tem um chip_uid imutavel (12 hex chars = MAC 48 bits).

-- 1) Adiciona coluna chip_uid se ainda nao existir
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'devices'
    AND column_name = 'chip_uid'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE devices ADD COLUMN chip_uid VARCHAR(16) NULL UNIQUE COMMENT ''Chip ID/MAC do ESP32 (12 hex chars) — unico por hardware'' AFTER hardware_serial',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2) Cria indice se ainda nao existir
SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'devices'
    AND index_name = 'idx_devices_chip_uid'
);
SET @sql := IF(
  @idx_exists = 0,
  'CREATE INDEX idx_devices_chip_uid ON devices (chip_uid)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
