BEGIN;

ALTER TABLE public.payment ADD COLUMN IF NOT EXISTS quote jsonb;
ALTER TABLE public.payment ADD COLUMN IF NOT EXISTS "couponId" bigint;
ALTER TABLE public.payment ADD COLUMN IF NOT EXISTS "idempotencyKey" character varying;
ALTER TABLE public.payment ADD COLUMN IF NOT EXISTS "verifiedAt" timestamp without time zone;

CREATE UNIQUE INDEX IF NOT EXISTS "UQ_payment_idempotency_key"
  ON public.payment ("idempotencyKey")
  WHERE "idempotencyKey" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "IDX_payment_user_created" ON public.payment ("userId", created DESC);
CREATE INDEX IF NOT EXISTS "IDX_payment_site_created" ON public.payment ("siteId", created DESC);
CREATE INDEX IF NOT EXISTS "IDX_payment_coupon_approved" ON public.payment ("couponId", approve)
  WHERE "couponId" IS NOT NULL;

DO $$
DECLARE start_value bigint;
BEGIN
  SELECT COALESCE(MAX(invoicenumber), 100000) + 1 INTO start_value FROM public.invoice;
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relkind = 'S' AND relname = 'eventsa_invoice_number_seq') THEN
    EXECUTE format('CREATE SEQUENCE eventsa_invoice_number_seq START WITH %s', start_value);
  END IF;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS "UQ_invoice_payment"
  ON public.invoice ("paymentId")
  WHERE "paymentId" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "IDX_invoice_user_created" ON public.invoice ("userId", created DESC);
CREATE INDEX IF NOT EXISTS "IDX_invoice_site_created" ON public.invoice ("siteId", created DESC);

COMMIT;