-- Migration 009: unificacao workshop_code <-> workshop_id
-- workshop_id e' o numero da oficina (string numerica). Quando precisar do
-- formato BCD 5 bytes pro login BVDR1, e' derivado on-the-fly (PHP).
-- A coluna workshop_code_hex sai do schema.

-- ============ 1. Migra dados existentes (se houver) ============
-- Se workshop_code_hex tiver valor e workshop_id estiver vazio,
-- converte bytes BCD -> string numerica antes de dropar a coluna.
SET @has_code := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'workshop_code_hex'
);
SET @sql := IF(@has_code = 1, "
  UPDATE companies SET
    workshop_id = CASE
      WHEN (workshop_id IS NULL OR workshop_id = '')
        AND workshop_code_hex IS NOT NULL AND CHAR_LENGTH(workshop_code_hex) = 10
      THEN CAST(CONCAT(
        SUBSTRING(workshop_code_hex, 2, 1),
        SUBSTRING(workshop_code_hex, 4, 1),
        SUBSTRING(workshop_code_hex, 6, 1),
        SUBSTRING(workshop_code_hex, 8, 1),
        SUBSTRING(workshop_code_hex, 10, 1)
      ) AS UNSIGNED)
      ELSE workshop_id
    END
", "SELECT 1");
PREPARE m FROM @sql; EXECUTE m; DEALLOCATE PREPARE m;

-- ============ 2. Amplia workshop_id de VARCHAR(8) -> VARCHAR(10) ============
-- Suporta numeros ate 10 digitos (BCD ate 10 bytes se necessario)
SET @sql := "ALTER TABLE companies MODIFY COLUMN workshop_id VARCHAR(10) NULL COMMENT 'Numero da oficina (formato numerico, ex 30235 ou 9802)'";
PREPARE m FROM @sql; EXECUTE m; DEALLOCATE PREPARE m;

-- ============ 3. Drop workshop_code_hex (se existe) ============
SET @sql := IF(@has_code = 1,
  "ALTER TABLE companies DROP COLUMN workshop_code_hex",
  "SELECT 1");
PREPARE m FROM @sql; EXECUTE m; DEALLOCATE PREPARE m;
