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

DO $$
DECLARE
   vStep     	TEXT := '300';
   vDeadline	timestamp := '"2016-07-27 05:40:00.000000+03"'::timestamp with time zone;
	vMainReg    integer := NULL;
   vCurLead 	integer := 0;
   vNextLead 	integer := 0;
   vTheme		integer := NULL;
   vDuration	double precision[] := NULL;
   vStartTime	timestamp;
   vAvg			double precision;
   vCnt			integer;
   vDV			double precision;
   vCounter		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=inside.tensor.ru port=5435 host=web-dev-psql.unix.tensor.ru user=postgres password=postgres');
	--PERFORM dblink_connect('crm', 'dbname=' || current_database()::text || ' port=' || inet_server_port());
   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
	)
	LOOP
      --vDuration := NULL;
		vCurLead := 0;
		LOOP
			vStartTime := clock_timestamp();
         SELECT max_id
         INTO vNextLead
         FROM dblink('crm', '
            SET search_path TO "_00000003", public;
				WITH
					sel_lead AS (
						SELECT
							l."@Документ" "Документ",
							(
								SELECT
									e."@Событие"
								FROM "Событие" e
								WHERE
									e."Тип" = 3 AND
									e."Переход" IS NOT NULL AND
									e."Документ" = l."@Документ"
								ORDER BY "Конец" DESC NULLS LAST
								LIMIT 1
							) "Событие"
						FROM "Лид" l
						WHERE
							l."Фаза" IS NOT NULL AND
							l."Регламент" = ' || vTheme::TEXT || ' AND
							l."@Документ" > ' || vCurLead::TEXT || '
						ORDER BY l."Регламент", l."@Документ"
						LIMIT ' || vStep::TEXT || '
					),
					upd AS (
						UPDATE "Лид"
						SET
							"ПоследнийИтоговыйКонтакт" = sl."Событие"
						FROM sel_lead sl
						WHERE
							"@Документ" = sl."Документ"
						RETURNING "@Документ"
					)
				SELECT max("@Документ") max_id
				FROM upd;

         ') t (max_id integer);
         EXIT WHEN (vNextLead is null or clock_timestamp() > vDeadline);
         vDV := extract('milliseconds' from clock_timestamp() - vStartTime);
         vDuration := array_append(vDuration, vDV);
         vCounter := vCounter+1;
         vCurLead := vNextLead;
			SELECT avg(t)
			INTO vAvg
			FROM unnest(vDuration) t;
			RAISE NOTICE '%: Исправлено %. Среднее время на пачку: %, Время на пачку: %, регламент %, ID текущего лида %', clock_timestamp()::text, vCounter, vAvg, vDV, vTheme, vCurLead;
		END LOOP;
	END LOOP;

   perform dblink_disconnect('crm');

   SELECT avg(t), count(*)
   INTO vAvg, vCnt
   FROM unnest(vDuration) t;

   IF clock_timestamp() > vDeadline THEN
      RAISE NOTICE '%: Выход по таймауту. Кол-во пачек: %, Среднее время на пачку: %, регламент %, ID текущего лида %', clock_timestamp()::text, vCnt, vAvg, vTheme, vCurLead;
   ELSE
      RAISE NOTICE '%: ЗАВЕРШЕНО. Кол-во пачек: %, Среднее время на пачку: %', clock_timestamp()::text, vCnt, vAvg;
   END IF;

EXCEPTION WHEN others THEN
   PERFORM dblink_disconnect('crm');
   RAISE NOTICE '% %', SQLERRM, SQLSTATE;
   RAISE NOTICE '%: Выход по ошибке: регламент %, ID текущего лида %', clock_timestamp()::text, vTheme, vСurLead;
	RAISE;

END;
$$;