CREATE TABLE deal_duplicate_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  vendor_id uuid NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  source_deal_id uuid NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
  idempotency_key text NOT NULL,
  result_draft_id uuid NOT NULL REFERENCES deal_drafts(id) ON DELETE RESTRICT,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT deal_duplicate_requests_key_uq
    UNIQUE (vendor_id, source_deal_id, idempotency_key),
  CONSTRAINT deal_duplicate_requests_key_len_ck
    CHECK (length(idempotency_key) BETWEEN 1 AND 200)
);

CREATE TABLE campaign_send_attempts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_campaign_id integer NOT NULL UNIQUE,
  idempotency_key text NOT NULL,
  admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status text NOT NULL,
  provider_status text,
  send_count integer NOT NULL DEFAULT 0,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  accepted_at timestamptz,
  CONSTRAINT campaign_send_attempts_status_ck
    CHECK (status IN ('SENDING', 'AMBIGUOUS', 'ACCEPTED')),
  CONSTRAINT campaign_send_attempts_key_len_ck
    CHECK (length(idempotency_key) BETWEEN 1 AND 200),
  CONSTRAINT campaign_send_attempts_send_count_ck CHECK (send_count >= 0)
);
