-- Migration 010: dedup global de seriais consultados.
-- Antes: PRIMARY KEY (job_id, bvdr_serial) — permitia consultar o mesmo
-- serial em jobs diferentes (desperdicio + risco de chamar API 2x).
-- Depois: PRIMARY KEY (bvdr_serial) — global, nunca repete.

-- ============ 1. Dedup linhas duplicadas (se houver) ============
-- Mantem apenas a linha mais antiga (menor id de job) por serial.
CREATE TEMPORARY TABLE _hq_keep AS
SELECT bvdr_serial, MIN(job_id) AS keep_job
FROM harvest_queried
GROUP BY bvdr_serial;

DELETE hq FROM harvest_queried hq
LEFT JOIN _hq_keep k
  ON k.bvdr_serial = hq.bvdr_serial AND k.keep_job = hq.job_id
WHERE k.bvdr_serial IS NULL;

DROP TEMPORARY TABLE _hq_keep;

-- ============ 2. Reconfigura PRIMARY KEY ============
ALTER TABLE harvest_queried DROP PRIMARY KEY;
ALTER TABLE harvest_queried ADD PRIMARY KEY (bvdr_serial);
