-- Allow one account per role for the same company_number (admin / trainer / trainee)
USE trainer_attendance;

SET @idx := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND INDEX_NAME = 'company_number'
);
SET @sql := IF(@idx > 0, 'ALTER TABLE users DROP INDEX company_number', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @uq := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND INDEX_NAME = 'uq_users_company_role'
);
SET @sql := IF(@uq = 0,
  'ALTER TABLE users ADD UNIQUE KEY uq_users_company_role (company_number, role)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;