-- Migration 005: credenciais da oficina (codigo + senha por ano)
-- BVDR1 workshopLogin = 5B codigo + 8B senha (senha muda anualmente, codigo e' permanente).
-- Idempotente.

-- ============ Coluna workshop_code_hex em companies ============
-- Codigo da oficina: 5 bytes = 10 chars hex. Permanente por empresa.
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'companies'
    AND COLUMN_NAME = 'workshop_code_hex'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE companies ADD COLUMN workshop_code_hex VARCHAR(10) DEFAULT NULL COMMENT ''5 bytes hex (codigo da oficina BVDR1)''',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============ Tabela workshop_passwords ============
-- Uma senha por (oficina, ano). 8 bytes = 16 chars hex.
CREATE TABLE IF NOT EXISTS workshop_passwords (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  year SMALLINT UNSIGNED NOT NULL,
  password_hex VARCHAR(16) NOT NULL COMMENT '8 bytes hex (senha BVDR1)',
  notes VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_year (company_id, year),
  KEY idx_company (company_id),
  CONSTRAINT fk_wpwd_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
