-- Migration 007: Mass harvest de personalized keys (porte do harvest_gui.py).
-- Idempotente.

-- ============ Tablets ativos (rotacao de serial pra chamadas API) ============
-- Lista de tablet_serial que pode chamar a API Continental.
CREATE TABLE IF NOT EXISTS harvest_tablets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tablet_serial VARCHAR(40) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  notes VARCHAR(200) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tablet_serial (tablet_serial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Jobs de harvest (estado/historico) ============
-- Cada job representa uma execucao (com range definido, status, contadores).
CREATE TABLE IF NOT EXISTS harvest_jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  range_start INT UNSIGNED NOT NULL,
  range_end INT UNSIGNED NOT NULL,
  status ENUM('idle','running','paused','done','aborted') NOT NULL DEFAULT 'idle',
  total_queried INT UNSIGNED NOT NULL DEFAULT 0,
  ok_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_count INT UNSIGNED NOT NULL DEFAULT 0,
  invalid_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_serial VARCHAR(20) 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),
  KEY idx_status (status),
  KEY idx_user (user_id),
  KEY idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Set de seriais ja consultados (pra evitar repeticao) ============
-- Indexado por job_id+serial.
CREATE TABLE IF NOT EXISTS harvest_queried (
  job_id INT UNSIGNED NOT NULL,
  bvdr_serial VARCHAR(20) NOT NULL,
  status VARCHAR(40) NOT NULL,
  api_key_hex VARCHAR(64) DEFAULT NULL,
  tablet_used VARCHAR(40) DEFAULT NULL,
  queried_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (job_id, bvdr_serial),
  KEY idx_serial (bvdr_serial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
