-- Idempotency keys for batch task creation and other heavy write endpoints
CREATE TABLE IF NOT EXISTS idempotency_keys (
  id SERIAL PRIMARY KEY,
  key VARCHAR(255) NOT NULL,
  organization_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  endpoint VARCHAR(255) NOT NULL,
  payload_hash VARCHAR(64) NOT NULL,
  response_json JSONB NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idempotency_keys_org_key_idx ON idempotency_keys(organization_id, key);
CREATE INDEX IF NOT EXISTS idempotency_keys_expires_at_idx ON idempotency_keys(expires_at);

-- Enable tenant isolation for idempotency keys
ALTER TABLE idempotency_keys ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_iso ON idempotency_keys;
CREATE POLICY tenant_iso ON idempotency_keys USING (
  current_setting('app.is_superadmin', true) = 'true'
  OR organization_id = NULLIF(current_setting('app.current_org_id', true), '')::int
);