-- EVENTSA P0 hardening migration
-- Target: PostgreSQL 15 / gringodingoDB
-- Run on a restored/staging copy first. POSTGRES_SYNCHRONIZE must remain false.
BEGIN;

-- Required by TypeORM softRemove() in WorkshopService and SeminarService.
ALTER TABLE public.workshop
  ADD COLUMN IF NOT EXISTS "deletedAt" timestamp without time zone NULL;
ALTER TABLE public.seminar
  ADD COLUMN IF NOT EXISTS "deletedAt" timestamp without time zone NULL;

-- Fail loudly if historical duplicates exist instead of silently deleting data.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM public.attendee
    WHERE "workshopId" IS NOT NULL AND "userId" IS NOT NULL
    GROUP BY "userId", "workshopId" HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Duplicate attendee rows exist for user/workshop; reconcile before migration';
  END IF;
  IF EXISTS (
    SELECT 1 FROM public.attendee
    WHERE "seminarId" IS NOT NULL AND "userId" IS NOT NULL
    GROUP BY "userId", "seminarId" HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Duplicate attendee rows exist for user/seminar; reconcile before migration';
  END IF;
  IF EXISTS (
    SELECT 1 FROM public.attendee
    WHERE "serviceId" IS NOT NULL AND "userId" IS NOT NULL
    GROUP BY "userId", "serviceId" HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Duplicate attendee rows exist for user/service; reconcile before migration';
  END IF;
  IF EXISTS (
    SELECT 1 FROM public.article_user
    WHERE "articleId" IS NOT NULL AND "judgeId" IS NOT NULL
    GROUP BY "articleId", "judgeId" HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Duplicate article/judge assignments exist; reconcile before migration';
  END IF;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS "UX_attendee_user_workshop"
  ON public.attendee ("userId", "workshopId")
  WHERE "userId" IS NOT NULL AND "workshopId" IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS "UX_attendee_user_seminar"
  ON public.attendee ("userId", "seminarId")
  WHERE "userId" IS NOT NULL AND "seminarId" IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS "UX_attendee_user_service"
  ON public.attendee ("userId", "serviceId")
  WHERE "userId" IS NOT NULL AND "serviceId" IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS "UX_article_user_article_judge"
  ON public.article_user ("articleId", "judgeId")
  WHERE "articleId" IS NOT NULL AND "judgeId" IS NOT NULL;

-- Gateway callbacks are looked up by authority; a non-null authority must identify
-- only one payment row.
CREATE UNIQUE INDEX IF NOT EXISTS "UX_payment_authority"
  ON public.payment (authority)
  WHERE authority IS NOT NULL;

COMMIT;