CREATE TABLE IF NOT EXISTS promo_reservations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  promo_code_id uuid NOT NULL REFERENCES promo(id),
  user_id uuid NOT NULL REFERENCES users(id),
  purchase_id uuid NOT NULL REFERENCES order_line(id),
  order_line_id uuid NOT NULL REFERENCES order_line(id),
  vendor_id uuid NOT NULL REFERENCES vendors(id),
  checkout_scope text NOT NULL,
  currency text NOT NULL,
  snapshot jsonb NOT NULL,
  quota_owner boolean NOT NULL DEFAULT false,
  discount_agorot integer NOT NULL CHECK (discount_agorot > 0),
  funder text NOT NULL CHECK (funder IN ('platform', 'vendor')),
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'finalized', 'failed')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS promo_reservations_active_uniq
  ON promo_reservations (promo_code_id, user_id, purchase_id)
  WHERE status IN ('pending', 'finalized');
CREATE INDEX IF NOT EXISTS promo_reservations_promo_status_idx ON promo_reservations (promo_code_id, status);
CREATE INDEX IF NOT EXISTS promo_reservations_user_idx ON promo_reservations (user_id, created_at);
CREATE INDEX IF NOT EXISTS promo_reservations_purchase_idx ON promo_reservations (purchase_id);
CREATE INDEX IF NOT EXISTS promo_reservations_checkout_idx ON promo_reservations (checkout_scope);
