-- ============================================================
-- Add idempotency_key table
-- 2026-08-11
-- DRAFT ONLY — not executed against any database, not yet reviewed/approved.
--
-- idempotency_key backs IdempotencyRepository/IdempotencyService: a client-supplied
-- Idempotency-Key header is claimed here before POST /registration and
-- POST /payment/initiate/:registrationId run, so a retried request replays the
-- cached response instead of re-executing the mutation.
--
-- The unique index includes user_id so two different callers can never collide on
-- the same (idempotency_key, endpoint) pair — each caller gets an independent claim
-- namespace even if a key value were ever accidentally reused across users.
--
-- hit_count tracks how many times a given key has been seen (the initial claim counts
-- as 1; every subsequent request matching the same key/endpoint/user increments it),
-- regardless of whether that later hit resulted in a replay, a conflict, or a reclaim.
-- ============================================================

CREATE TABLE idempotency_key (
  id SERIAL PRIMARY KEY,
  idempotency_key VARCHAR(255) NOT NULL,
  endpoint VARCHAR(255) NOT NULL,
  request_hash VARCHAR(64) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  response_data JSONB,
  user_id INT,
  hit_count INT NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_idempotency_key_status CHECK (status IN ('pending', 'completed', 'failed'))
);

CREATE UNIQUE INDEX uq_idempotency_key_endpoint_user ON idempotency_key (idempotency_key, endpoint, user_id);
