-- One row per client record of a dataset. The payload is stored RAW: nothing
-- of ours is ever merged into the client object (no _origem, no wrapper).
--
-- Two deduplication regimes, on purpose:
--
--  * UNIQUE (dataset_id, record_key) — used when the envelope declares a "key"
--    field. Re-sending the same identifier UPDATES the row (upsert), so the
--    dataset stays the current state instead of growing forever.
--
--  * KEY (dataset_id, content_hash) — used when there is NO key field. The
--    record is inserted only when that exact content hash is not present yet.
--
-- ATTENTION (intended behaviour, not an oversight): in MySQL a UNIQUE index
-- allows MULTIPLE rows whose indexed column is NULL. Records with no key
-- therefore all store record_key = NULL and coexist happily under
-- uq_dataset_records_key; their deduplication is the content_hash lookup
-- above. This is exactly what we want: the unique constraint only ever
-- constrains keyed records.
CREATE TABLE dataset_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  dataset_id BIGINT UNSIGNED NOT NULL,
  record_key VARCHAR(191) NULL,
  payload JSON NOT NULL,
  content_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_dataset_records_key (dataset_id, record_key),
  KEY idx_dataset_records_hash (dataset_id, content_hash),
  KEY idx_dataset_records_project (project_id),
  CONSTRAINT fk_dataset_records_project FOREIGN KEY (project_id) REFERENCES projects (id),
  CONSTRAINT fk_dataset_records_dataset FOREIGN KEY (dataset_id) REFERENCES datasets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;