-- Patch 20A: immutable, unique article tracking number; idempotent migration.
BEGIN;
ALTER TABLE public.article ADD COLUMN IF NOT EXISTS "trackingCode" varchar(80);

CREATE OR REPLACE FUNCTION public.eventsa_set_article_tracking_code()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE event_prefix text;
BEGIN
  IF NEW."trackingCode" IS NOT NULL AND btrim(NEW."trackingCode") <> '' THEN
    RETURN NEW;
  END IF;
  SELECT LEFT(TRIM(BOTH '-' FROM regexp_replace(lower(COALESCE(s.slug,'event')), '[^a-z0-9]+', '-', 'g')), 32)
    INTO event_prefix FROM public.site s WHERE s.id = NEW."siteId";
  event_prefix := COALESCE(NULLIF(event_prefix, ''), 'event');
  NEW."trackingCode" := event_prefix || '-' || lpad(NEW.id::text, GREATEST(length(NEW.id::text), 4), '0');
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS "TRG_eventsa_article_tracking" ON public.article;
CREATE TRIGGER "TRG_eventsa_article_tracking"
BEFORE INSERT ON public.article
FOR EACH ROW EXECUTE FUNCTION public.eventsa_set_article_tracking_code();

UPDATE public.article a SET "trackingCode" =
  COALESCE(NULLIF(LEFT(TRIM(BOTH '-' FROM regexp_replace(lower(s.slug), '[^a-z0-9]+', '-', 'g')), 32), ''), 'event')
  || '-' || lpad(a.id::text, GREATEST(length(a.id::text), 4), '0')
FROM public.site s
WHERE a."siteId" = s.id AND (a."trackingCode" IS NULL OR btrim(a."trackingCode") = '');
UPDATE public.article a SET "trackingCode" = 'event-' || lpad(a.id::text, GREATEST(length(a.id::text), 4), '0')
WHERE a."trackingCode" IS NULL OR btrim(a."trackingCode") = '';
CREATE UNIQUE INDEX IF NOT EXISTS "UQ_eventsa_article_tracking" ON public.article ("trackingCode");
COMMIT;