DO $$ 
	DECLARE _path character varying(256) = 'patch-2024-04-16-events';

	BEGIN
	IF EXISTS (SELECT "Id" FROM public."Versions" WHERE "Path"=_path) THEN
			RETURN;
	END IF;

	 
	IF EXISTS (SELECT "Id" FROM public."Versions" WHERE "Build">=3780) THEN
			RETURN;
	END IF;

		BEGIN

-- ***
		CREATE TABLE public."Realms" (
				"Id" uuid NOT NULL,
				"Name" varchar(128),
				CONSTRAINT "PK_Realms" PRIMARY KEY ("Id")
		);

		ALTER TABLE public."Customers" ADD "RealmId" uuid NULL;
		
		with a as
		(
			update public."Customers" set "RealmId" =  md5(random()::text || current_timestamp::text)::uuid where "ParentId" is null and "RealmId" is null returning "Title", "RealmId"
		)
		insert into public."Realms" ("Id", "Name")	select a."RealmId", a."Title" from a;


    alter table public."EventDetails" add "OrganizerId" bigint null;
    
    drop table if exists public."EventEmailInviteStatus";
    
    CREATE TABLE public."EventEmailInvite" (
      "MessageId" bigint NOT NULL,
      "OwnerId" bigint NOT NULL, 
      "AttachmentId" bigint NOT NULL,
      "Uid" varchar(256) NOT NULL,
      "EventDetailId" bigint NULL,
      "Sequence" int2 NOT NULL,
      "Method" int2 NOT NULL,
      "State" int2,
      "InsertDate" timestamp without time zone DEFAULT timezone('utc', now()) NOT NULL,
      CONSTRAINT "UIDX_EventEmailInvite" UNIQUE ("MessageId")
    );

    CREATE INDEX "IDX_EventInviteDetails_EventDetailId"
      ON public."EventEmailInvite" USING btree
      ("EventDetailId" ASC NULLS LAST)
      TABLESPACE pg_default;

    CREATE INDEX "IDX_EventInviteDetails_Uid"
      ON public."EventEmailInvite" USING btree
      ("Uid" ASC NULLS LAST)
      TABLESPACE pg_default;
      
		insert into public."Versions" ("Path", "Build") values (_path, 0);
-- **

		EXCEPTION
				WHEN OTHERS THEN RAISE NOTICE 'Create table EventEmailInvite error.';
		END;
	END;
$$
