DO
$$
DECLARE
	_path character varying(256) := 'patch-2026_02_06-FileGroups-Final';
	_build integer := 16019;
BEGIN
	IF EXISTS (SELECT 1 FROM public."Versions" WHERE "Path" = _path) THEN
		RETURN;
	END IF;

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

	BEGIN
		IF to_regclass('public."FileGroups"') IS NULL THEN
			CREATE TABLE public."FileGroups"
			(
				"Id" bigserial NOT NULL,
				"CustomerId" int NULL,
				"Name" character varying(256) NOT NULL,
				"IsSystem" boolean NOT NULL DEFAULT(false),
				"CreatedAt" timestamp without time zone NOT NULL,
				"CreatedBy" bigint NULL,
				"UpdatedAt" timestamp without time zone NOT NULL,
				"UpdatedBy" bigint NULL,
				"IsDeleted" boolean NOT NULL DEFAULT(false),
				"IncludeFilesMask" text NULL,
				"ExcludeFilesMask" text NULL,
				"MimeTypeCheck" text NULL,
				CONSTRAINT "PK_FileGroups" PRIMARY KEY ("Id")
			);
		END IF;

		IF EXISTS (
			SELECT 1
			FROM information_schema.columns
			WHERE table_schema = 'public'
			  AND table_name = 'FileGroups'
			  AND column_name = 'MimeTypes'
		) THEN
			UPDATE public."FileGroups"
			SET "MimeTypeCheck" = "MimeTypes"
			WHERE ("MimeTypeCheck" IS NULL OR btrim("MimeTypeCheck") = '')
			  AND "MimeTypes" IS NOT NULL
			  AND btrim("MimeTypes") <> '';
		END IF;

		IF to_regclass('public."QuotaFileGroups"') IS NULL THEN
			CREATE TABLE public."QuotaFileGroups"
			(
				"Id" bigserial NOT NULL,
				"QuotaId" bigint NOT NULL,
				"FileGroupId" bigint NOT NULL,
				CONSTRAINT "PK_QuotaFileGroups" PRIMARY KEY ("Id")
			);
		END IF;
		
		IF to_regclass('public."IDX_FileGroups-CustomerId-IsSystem"') IS NULL THEN
			CREATE INDEX "IDX_FileGroups-CustomerId-IsSystem"
				ON public."FileGroups" USING btree
				("CustomerId" ASC NULLS LAST, "IsSystem" ASC NULLS LAST)
				TABLESPACE pg_default;
		END IF;

		IF to_regclass('public."UX_QuotaFileGroups-QuotaId-FileGroupId"') IS NULL THEN
			CREATE UNIQUE INDEX "UX_QuotaFileGroups-QuotaId-FileGroupId"
				ON public."QuotaFileGroups" USING btree
				("QuotaId" ASC NULLS LAST, "FileGroupId" ASC NULLS LAST)
				TABLESPACE pg_default;
		END IF;

		IF to_regclass('public."IDX_QuotaFileGroups-FileGroupId"') IS NULL THEN
			CREATE INDEX "IDX_QuotaFileGroups-FileGroupId"
				ON public."QuotaFileGroups" USING btree
				("FileGroupId" ASC NULLS LAST)
				TABLESPACE pg_default;
		END IF;

		UPDATE public."FileGroups" fg
		SET "IsDeleted" = FALSE,
			"UpdatedAt" = now(),
			"UpdatedBy" = NULL
		WHERE fg."CustomerId" IS NULL
		  AND fg."IsSystem" = TRUE
		  AND fg."IsDeleted" = TRUE
		  AND fg."Name" IN
			  (
			   'Исполняемые файлы',
			   'Архивы',
			   'Офисные документы',
			   'Изображения',
			   'Текстовые файлы',
			   'Временные файлы',
			   'Системные файлы'
			  );

		INSERT INTO public."FileGroups"
		(
			"CustomerId",
			"Name",
			"IsSystem",
			"CreatedAt",
			"CreatedBy",
			"UpdatedAt",
			"UpdatedBy",
			"IsDeleted"
		)
		SELECT NULL,
			   src."Name",
			   TRUE,
			   now(),
			   NULL,
			   now(),
			   NULL,
			   FALSE
		FROM
		(
			VALUES
				('Исполняемые файлы'),
				('Архивы'),
				('Офисные документы'),
				('Изображения'),
				('Текстовые файлы'),
				('Временные файлы'),
				('Системные файлы')
		) AS src("Name")
		WHERE NOT EXISTS
		(
			SELECT 1
			FROM public."FileGroups" fg
			WHERE fg."CustomerId" IS NULL
			  AND fg."IsSystem" = TRUE
			  AND fg."Name" = src."Name"
		);

		UPDATE public."FileGroups" fg
		SET "IncludeFilesMask" = src."IncludeFilesMask",
			"UpdatedAt" = now(),
			"UpdatedBy" = NULL
		FROM
		(
			VALUES
				('Исполняемые файлы', '*.exe, *.com, *.bat, *.cmd, *.cpl, *.js, *.jse, *.vbs, *.vbe, *.wsf, *.wsh, *.ps1, *.psm1, *.psd1, *.scr, *.pif, *.msi'),
				('Архивы', '*.zip, *.rar, *.7z, *.tar, *.gz, *.tgz, *.bz2, *.xz'),
				('Офисные документы', '*.doc, *.docx, *.xls, *.xlsx, *.ppt, *.pptx, *.pdf, *.odt, *.ods, *.odp, *.rtf'),
				('Изображения', '*.jpg, *.jpeg, *.png, *.gif, *.bmp, *.tif, *.tiff, *.webp'),
				('Текстовые файлы', '*.txt, *.csv, *.md, *.log, *.rtf'),
				('Временные файлы', '*.tmp, *.temp, *.bak, *.old, ~*'),
				('Системные файлы', '*.ini, *.cfg, *.config, *.sys, *.dll')
		) AS src("Name", "IncludeFilesMask")
		WHERE fg."CustomerId" IS NULL
		  AND fg."IsSystem" = TRUE
		  AND fg."IsDeleted" = FALSE
		  AND fg."Name" = src."Name"
		  AND (fg."IncludeFilesMask" IS NULL OR btrim(fg."IncludeFilesMask") = '');

		INSERT INTO public."Versions" ("Path", "Build") VALUES (_path, 0);

	EXCEPTION
		WHEN OTHERS THEN
			RAISE NOTICE '[ERR] %: %', _path, SQLERRM;
	END;
END;
$$;
