-- Migration 003: Bibliotecas BVDR2 (presets OEM)
-- Idempotente: pode ser executada multiplas vezes sem erro.

-- ============ Tabela: bvdr2_libraries ============
CREATE TABLE IF NOT EXISTS bvdr2_libraries (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  brand VARCHAR(120) NOT NULL,
  vehicle_type VARCHAR(120) NOT NULL,
  dtco_type VARCHAR(60) NOT NULL,
  software_number VARCHAR(60) DEFAULT NULL,
  ctc_brand VARCHAR(120) DEFAULT NULL,
  ctc_file VARCHAR(180) DEFAULT NULL,
  parameter_count INT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_brand (brand),
  KEY idx_vehicle (vehicle_type),
  KEY idx_dtco (dtco_type),
  UNIQUE KEY uq_library (brand, vehicle_type, dtco_type, software_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Tabela: bvdr2_library_params ============
CREATE TABLE IF NOT EXISTS bvdr2_library_params (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  library_id INT UNSIGNED NOT NULL,
  identifier VARCHAR(20) NOT NULL,         -- ex: 01FD0E
  write_flag VARCHAR(8) NOT NULL,           -- ex: 01
  did_hex VARCHAR(8) NOT NULL,              -- ex: 0xFD0E
  parameter_name VARCHAR(180) DEFAULT NULL,
  value_hex TEXT NOT NULL,                  -- bytes em hex string (pode passar de 255 chars)
  value_len_bytes INT UNSIGNED NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_library (library_id),
  KEY idx_did (did_hex),
  CONSTRAINT fk_library_params FOREIGN KEY (library_id) REFERENCES bvdr2_libraries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
