-- ============================================================
--  MIGRACION: Marcaje multi-metodo + Dispositivos
--  Idempotente y compatible con mysql2 (sin DELIMITER).
--  Uso:  npm run migrate-marcaje
-- ============================================================
USE neg_control_asistencia;

-- ---- empleados.pin ----
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='empleados' AND COLUMN_NAME='pin');
SET @s := IF(@e=0, 'ALTER TABLE empleados ADD COLUMN pin VARCHAR(10) NULL', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---- empleados.codigo_barra ----
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='empleados' AND COLUMN_NAME='codigo_barra');
SET @s := IF(@e=0, 'ALTER TABLE empleados ADD COLUMN codigo_barra VARCHAR(40) NULL', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---- empleados.biometrico_id ----
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='empleados' AND COLUMN_NAME='biometrico_id');
SET @s := IF(@e=0, 'ALTER TABLE empleados ADD COLUMN biometrico_id VARCHAR(40) NULL', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---- asistencia.metodo ----
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='asistencia' AND COLUMN_NAME='metodo');
SET @s := IF(@e=0, "ALTER TABLE asistencia ADD COLUMN metodo ENUM('biometrico','qr','barras','pin','web','manual') NOT NULL DEFAULT 'manual'", 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---- asistencia.dispositivo ----
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='asistencia' AND COLUMN_NAME='dispositivo');
SET @s := IF(@e=0, 'ALTER TABLE asistencia ADD COLUMN dispositivo VARCHAR(80) NULL', 'DO 0');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---- Tabla de dispositivos ----
CREATE TABLE IF NOT EXISTS dispositivos (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  nombre        VARCHAR(100) NOT NULL,
  tipo          ENUM('biometrico','lector_qr','lector_barras','kiosco','otro') NOT NULL DEFAULT 'biometrico',
  ubicacion     VARCHAR(120),
  api_key       VARCHAR(64) UNIQUE,
  estado        ENUM('activo','inactivo','mantenimiento') NOT NULL DEFAULT 'activo',
  ultimo_latido TIMESTAMP NULL,
  activo        TINYINT(1) NOT NULL DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---- Datos base: fotocheck (codigo_barra), biometrico_id y PIN ----
UPDATE empleados SET codigo_barra  = codigo             WHERE codigo_barra  IS NULL OR codigo_barra  = '';
UPDATE empleados SET biometrico_id = codigo             WHERE biometrico_id IS NULL OR biometrico_id = '';
UPDATE empleados SET pin           = LPAD(1000 + id, 4, '0') WHERE pin IS NULL OR pin = '';

-- ---- Dispositivos de ejemplo (solo si la tabla esta vacia) ----
INSERT INTO dispositivos (nombre, tipo, ubicacion, api_key, estado)
SELECT 'Biometrico Principal', 'biometrico', 'Puerta de entrada', 'bio-key-8f3a1c9e2b7d4056', 'activo' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM dispositivos);

INSERT INTO dispositivos (nombre, tipo, ubicacion, api_key, estado)
SELECT 'Lector QR Recepcion', 'lector_qr', 'Recepcion', 'qr-key-2d6b8a4f1e903c75', 'activo' FROM DUAL
WHERE (SELECT COUNT(*) FROM dispositivos) < 2;

SELECT 'Migracion de marcaje aplicada correctamente.' AS mensaje;

-- ---- Foto del empleado como base64 (credenciales/fotocheck) ----
ALTER TABLE empleados MODIFY COLUMN foto MEDIUMTEXT NULL;