-- F5 в pgAdmin
SET search_path TO _00000003, public;

DO $$
DECLARE
   vStep			text := '300';
   vCurLead 	integer := 0;
   vNextLead 	integer;
   vDeadline	timestamp := '"2016-10-01 05:40:00.000000+03"'::timestamp with time zone;

   vTotalCount	integer := 0;
BEGIN
   --PERFORM dblink_connect('crm', 'dbname=inside.tensor.ru port=5432 host=ins-db-master.unix.tensor.ru user= password=');
	PERFORM dblink_connect('crm', 'dbname=' || current_database()::text || ' port=' || inet_server_port());

   RAISE NOTICE '%: ЗАПУЩЕНО. Заполнение поля "Регламент" таблицы "Лид", если оно пустое (удаление лида)', date_trunc('second', clock_timestamp())::TEXT;

	LOOP
		SELECT maxID
		INTO vNextLead
		FROM dblink('crm', 'SET search_path TO _00000003, public;
			WITH
			main_sel AS (
				SELECT
					l."@Документ" "Документ",
					(
						SELECT
							e."Регламент"
						FROM "Событие" e
						WHERE
							e."@Событие" = l."ПоследнийКонтакт"
					 ) "Регламент"
				FROM "Лид" l
				WHERE
					l."Регламент" IS NULL AND
					l."Черновик" IS NULL AND
					l."@Документ" > ' || vCurLead::TEXT || '
				ORDER BY l."@Документ"
				LIMIT ' || vStep || '
			),

			updateCTE AS (
				UPDATE "Лид" l SET "Регламент" = m."Регламент"
				FROM main_sel m
				WHERE
					l."@Документ" = m."Документ" AND
					m."Регламент" is not null
				RETURNING l."@Документ"
			),

			deleteCTE AS (
				DELETE FROM "Лид" l
				WHERE "@Документ" IN (
					SELECT
						"Документ"
					FROM main_sel m
					WHERE m."Регламент" is null
				)
				RETURNING l."@Документ"
			)
			SELECT MAX(maxID) maxID
			FROM (
				SELECT MAX("@Документ") maxID FROM updateCTE
				UNION
				SELECT MAX("@Документ") maxID FROM deleteCTE
			) q;
		') t (maxID integer);
		EXIT WHEN (vNextLead IS NULL OR clock_timestamp() > vDeadline);
		vCurLead := vNextLead;
		vTotalCount := vTotalCount + 1;
		--RAISE NOTICE '%: №: %, ID %', date_trunc('second', clock_timestamp())::text, vTotalCount, vNextLead;
	END LOOP;
   RAISE NOTICE '%: №: %', date_trunc('second', clock_timestamp())::text, vTotalCount;

	IF NOT clock_timestamp() > vDeadline THEN
		RAISE NOTICE '%: ЗАПУЩЕНО. Удаление лида в случае пустых полей "ПоследнийКонтакт", "НазваниеКлиента" и "КонтактныеДанные" и непустого значения поля "Регламент".', date_trunc('second', clock_timestamp())::TEXT;
		FOR vTheme IN (
			WITH recursive themes as (
			  SELECT
					x."@Регламент" "Регламент",
					x."Раздел",
					x."Раздел@"
			  FROM "Регламент" x
			  WHERE "Идентификатор" = '8992e41a-a499-11e1-8c02-63ba74c41964'
			  UNION ALL
			  SELECT r."@Регламент" "Регламент", r."Раздел", r."Раздел@"
			  FROM themes t
			  INNER JOIN "Регламент" r ON t."Регламент" = r."Раздел"
			)
			SELECT "Регламент"
			FROM themes
			WHERE "Раздел@" IS NOT TRUE
			ORDER BY "Регламент"
		)
		LOOP
			vCurLead := 0;
			LOOP
			  SELECT max_id
			  INTO vNextLead
			  FROM dblink('crm', '
					SET search_path TO "_00000003", public;
					WITH
					main_sel AS (
						SELECT
							l."@Документ" "Документ"
						FROM "Лид" l
						WHERE
							l."Регламент" = ' || vTheme::TEXT || ' AND
							l."Черновик" IS NULL AND
							l."ПоследнийКонтакт" IS NULL AND
							l."НазваниеКлиента" IS NULL AND
							l."КонтактныеДанные" IS NULL AND
							l."@Документ" > ' || vCurLead::TEXT || '
						ORDER BY l."@Документ"
						LIMIT ' || vStep || '
					),
					deleteCTE AS (
						DELETE FROM "Лид" l
						WHERE "@Документ" IN (
							SELECT
								"Документ"
							FROM main_sel m
						)
						RETURNING l."@Документ"
					)
					SELECT MAX("@Документ") maxID FROM deleteCTE;
			  ') t (max_id integer);
			  EXIT WHEN (vNextLead is null or clock_timestamp() > vDeadline);
			  vCurLead := vNextLead;
			  vTotalCount := vTotalCount + 1;
			  --RAISE NOTICE '%: №: %, ID %', date_trunc('second', clock_timestamp())::text, vTotalCount, vNextLead;
			END LOOP;
		END LOOP;
	END IF;

   PERFORM dblink_disconnect('crm');
   IF clock_timestamp() > vDeadline THEN
      RAISE NOTICE '!!! Выход по таймауту:  ID текущего лида %.', vNextLead;
   ELSE
      RAISE NOTICE '%: ЗАВЕРШЕНО. №: %', date_trunc('second', clock_timestamp())::text, vTotalCount;
   END IF;

EXCEPTION WHEN others THEN
   PERFORM dblink_disconnect('crm');
   RAISE NOTICE '% %', SQLERRM, SQLSTATE;
   RAISE NOTICE '%: Выход по ошибке: №: %, ID текущего лида %', date_trunc('second', clock_timestamp())::text, vTotalCount, vNextLead;
	RAISE;

END; $$