BEGIN;

CREATE TABLE IF NOT EXISTS public.issued_certificate (
  id bigserial PRIMARY KEY,
  token character varying NOT NULL UNIQUE,
  kind character varying NOT NULL,
  status character varying NOT NULL DEFAULT 'active',
  title character varying NOT NULL,
  "recipientName" character varying NOT NULL,
  "eventTitle" character varying,
  "articleTitle" character varying,
  snapshot jsonb,
  "siteId" bigint,
  "userId" bigint NOT NULL,
  "articleId" bigint,
  "templateId" bigint,
  "issuedAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "revokedAt" timestamp without time zone,
  created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DO $$ BEGIN
  ALTER TABLE public.issued_certificate
    ADD CONSTRAINT "FK_issued_certificate_site" FOREIGN KEY ("siteId") REFERENCES public.site(id) ON DELETE SET NULL;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN
  ALTER TABLE public.issued_certificate
    ADD CONSTRAINT "FK_issued_certificate_user" FOREIGN KEY ("userId") REFERENCES public."user"(id) ON DELETE CASCADE;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN
  ALTER TABLE public.issued_certificate
    ADD CONSTRAINT "FK_issued_certificate_article" FOREIGN KEY ("articleId") REFERENCES public.article(id) ON DELETE SET NULL;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN
  ALTER TABLE public.issued_certificate
    ADD CONSTRAINT "FK_issued_certificate_template" FOREIGN KEY ("templateId") REFERENCES public.certificate(id) ON DELETE SET NULL;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

ALTER TABLE public.issued_certificate DROP CONSTRAINT IF EXISTS "CHK_issued_certificate_kind";
ALTER TABLE public.issued_certificate ADD CONSTRAINT "CHK_issued_certificate_kind"
  CHECK (kind IN ('attendee','article-temporary','article-final','judge','speaker'));
ALTER TABLE public.issued_certificate DROP CONSTRAINT IF EXISTS "CHK_issued_certificate_status";
ALTER TABLE public.issued_certificate ADD CONSTRAINT "CHK_issued_certificate_status"
  CHECK (status IN ('active','revoked'));

CREATE INDEX IF NOT EXISTS "IDX_issued_certificate_user" ON public.issued_certificate ("userId", "issuedAt" DESC);
CREATE INDEX IF NOT EXISTS "IDX_issued_certificate_site" ON public.issued_certificate ("siteId", "issuedAt" DESC);
CREATE INDEX IF NOT EXISTS "IDX_issued_certificate_article" ON public.issued_certificate ("articleId") WHERE "articleId" IS NOT NULL;

COMMIT;