-- =====================================================================
-- Migration 001 — Tabela de personalized keys dos tacografos
-- =====================================================================
-- Cada TCO tem uma personalized_key (16 bytes) que serve de input pro
-- pipeline SHA-256 3-stage do BVDR2. O app consulta esta tabela atraves
-- do endpoint /api/seedkey antes de fazer qualquer write.

CREATE TABLE IF NOT EXISTS tachograph_keys (
  tachograph_serial VARCHAR(40) NOT NULL PRIMARY KEY,
  model VARCHAR(20) NOT NULL DEFAULT 'BVDR2' COMMENT 'BVDR2 / BVDR1 / DTCO1390',
  personalized_key_hex CHAR(32) NOT NULL COMMENT '16 bytes em hex (lowercase)',
  notes TEXT NULL,
  source VARCHAR(40) NULL COMMENT 'tablet_dflash / api_continental / manual',
  registered_by_user_id INT UNSIGNED NULL,
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tk_user FOREIGN KEY (registered_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Workshop ID padrao (4 chars ASCII) — usado pelo pipeline SHA-256 stage 3.
-- Pode ser sobrescrito por company.workshop_id na consulta.
INSERT INTO settings (k, v) VALUES ('default_workshop_id', '9800')
  ON DUPLICATE KEY UPDATE v = v;

-- Seed: SN 45001612 (firmware_seedkey hardcoded — validado em prod 2026-05-17)
INSERT INTO tachograph_keys
  (tachograph_serial, model, personalized_key_hex, source, notes)
VALUES
  ('45001612', 'BVDR2', '62fb5114eecba0061f1b8fc95524e89c', 'tablet_dflash',
   'Seed inicial — device de teste validado em 2026-05-17')
ON DUPLICATE KEY UPDATE
  personalized_key_hex = VALUES(personalized_key_hex),
  source = VALUES(source);
