-- NO KILL
do $$
declare
	c1rec record;
	i integer;
	n bigint;
	coc Tshop_cocs%rowtype;
begin
	drop table if exists Tshop_cocs_tmp;
	create table Tshop_cocs_tmp (
		id			bigserial primary key,
		shop_id		bigint not null references Tshops,
		date_start	date not null,
		date_end	date not null,
		user_ids	bigint[],
		sv_id		bigint,
		asm_id		bigint,
		um_id		bigint,
		kam_id		bigint,
		aud_id		bigint,
		rgsm_id		bigint,
		ssv_id		bigint,
		cm_id		bigint,
		saud_id		bigint
	);

	create index Ishop_cocs_tmp_0_1 on Tshop_cocs_tmp using gin (user_ids);
	create index Ishop_cocs_tmp_1_1 on Tshop_cocs_tmp (sv_id,date_start);
	create index Ishop_cocs_tmp_1_2 on Tshop_cocs_tmp (sv_id,date_end DESC);
	create index Ishop_cocs_tmp_1_3 on Tshop_cocs_tmp (sv_id,date_end,id);
	create index Ishop_cocs_tmp_2_1 on Tshop_cocs_tmp (aud_id,date_start);
	create index Ishop_cocs_tmp_2_2 on Tshop_cocs_tmp (aud_id,date_end);
	create index Ishop_cocs_tmp_3_1 on Tshop_cocs_tmp (shop_id,date_start DESC);
	create index Ishop_cocs_tmp_3_2 on Tshop_cocs_tmp (shop_id,date_end DESC);	
	create index Ishop_cocs_tmp_4_1 on Tshop_cocs_tmp using gin ((user_ids||array[-shop_id]));

	n:=1;

	for c1rec in (
		select
			s.id,
			s.network_id		as shop_network_id,
			s.network_format_id	as shop_network_format_id,
			sn.network_type_id	as shop_network_type_id,
			c.id				as city_id,
			c.region_id			as region_id,
			(
				select array_agg(a order by a)
				from (
					select '2010-01-01'::date as a
					union
					select  ss.date_start as a from Tshop_svs ss where ss.shop_id=s.id
					union
					select  ss.date_end+interval '1 days'   as a from Tshop_svs ss where ss.shop_id=s.id
					union
					select ufr.date_start as a from Tshop_svs ss,Tuser_function_rules ufr
					where ss.shop_id=s.id and ss.sv_id=ufr.sv_id and ufr.date_start<=ss.date_end and ufr.date_end>=ss.date_start and ufr.enabled=1
					union
					select ufr.date_end+interval '1 days'   as a from Tshop_svs ss,Tuser_function_rules ufr
					where ss.shop_id=s.id and ss.sv_id=ufr.sv_id and ufr.date_start<=ss.date_end and ufr.date_end>=ss.date_start and ufr.enabled=1
					union
					select ufr.date_start as a from Tuser_function_rules ufr
					where
						ufr.sv_id is null and ufr.enabled=1 and
						(ufr.region_id is null or ufr.region_id=c.region_id) and
						(ufr.city_id is null or ufr.city_id=c.id) and
						(ufr.shop_network_id is null or ufr.shop_network_id=sn.id) and
						(ufr.shop_network_format_id is null or ufr.shop_network_format_id=s.network_format_id) and
						(ufr.shop_network_type_id is null or ufr.shop_network_type_id=sn.network_type_id)
					union
					select ufr.date_end+interval '1 days' as a from Tuser_function_rules ufr
					where
						ufr.sv_id is null and ufr.enabled=1 and
						(ufr.region_id is null or ufr.region_id=c.region_id) and
						(ufr.city_id is null or ufr.city_id=c.id) and
						(ufr.shop_network_id is null or ufr.shop_network_id=sn.id) and
						(ufr.shop_network_format_id is null or ufr.shop_network_format_id=s.network_format_id) and
						(ufr.shop_network_type_id is null or ufr.shop_network_type_id=sn.network_type_id)
					union
					select date_start from Ttrip_plans tp where tp.shop_id=s.id
					union
					select date_end+interval '1 days' from Ttrip_plans tp where tp.shop_id=s.id
					union
					select '2099-12-31'::date as a
				) tmp
			) as dates
		from
			Tshops s,
			Tshop_networks sn,
			Tcities c
		where
			s.network_id=sn.id and
			s.city_id=c.id
	) loop
		coc.shop_id=c1rec.id;
		for i in 1 .. (array_length(c1rec.dates,1)-1) loop
			coc.id:=n;n:=n+1;
			coc.date_start=c1rec.dates[i];
			coc.date_end=c1rec.dates[i+1]-interval '1 days';
			select sv_id into coc.sv_id from Tshop_svs ss where ss.shop_id=c1rec.id and date_start<=coc.date_start and date_end>=coc.date_end;
			
			select min(user_id) into coc.um_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=1 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

			select min(user_id) into coc.asm_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=2 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

			select min(user_id) into coc.kam_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=6 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

			select min(user_id) into coc.aud_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=7 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

			select min(user_id) into coc.rgsm_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=8 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

	
			select min(user_id) into coc.ssv_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=9 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);

			select min(user_id) into coc.cm_id
			from Tuser_function_rules ufr
			where
				ufr.enabled=1 and
				ufr.function_id=10 and
				ufr.date_start<=coc.date_start and
				ufr.date_end>=coc.date_end and
				(ufr.sv_id is null or ufr.sv_id=coc.sv_id) and
				(ufr.region_id is null or ufr.region_id=c1rec.region_id) and
				(ufr.city_id is null or ufr.city_id=c1rec.city_id) and
				(ufr.shop_network_id is null or ufr.shop_network_id=c1rec.shop_network_id) and
				(ufr.shop_network_format_id is null or ufr.shop_network_format_id=c1rec.shop_network_format_id) and
				(ufr.shop_network_type_id is null or ufr.shop_network_type_id=c1rec.shop_network_type_id);


			select array_agg(distinct user_id) into coc.user_ids
			from Ttrip_plans tp
			where
				tp.shop_id=c1rec.id and
				tp.date_start<=coc.date_start and
				tp.date_end>=coc.date_end;

			if coc.sv_id   is not null then coc.user_ids:=array_append(coc.user_ids,coc.sv_id  ); end if;
			if coc.asm_id  is not null then coc.user_ids:=array_append(coc.user_ids,coc.asm_id ); end if;
			if coc.um_id   is not null then coc.user_ids:=array_append(coc.user_ids,coc.um_id  ); end if;
			if coc.kam_id  is not null then coc.user_ids:=array_append(coc.user_ids,coc.kam_id ); end if;
			if coc.aud_id  is not null then coc.user_ids:=array_append(coc.user_ids,coc.aud_id ); end if;
			if coc.rgsm_id is not null then coc.user_ids:=array_append(coc.user_ids,coc.rgsm_id); end if;
			if coc.ssv_id  is not null then coc.user_ids:=array_append(coc.user_ids,coc.ssv_id ); end if;
			if coc.cm_id   is not null then coc.user_ids:=array_append(coc.user_ids,coc.cm_id  ); end if;
			insert into Tshop_cocs_tmp values (coc.*);
		end loop;
	end loop;
--	alter sequence tshop_cocs_tmp_id_seq restart with n;
/*	insert into Tshop_cocs_tmp (id,shop_id,date_start,date_end)
		select n+row_number() over (order by s.id),s.id,to_date('01.01.2010','DD.MM.YYYY'),to_date('31.12.2099','DD.MM.YYYY')
		from Tshops s
		where s.id not in (select shop_id from Tshop_cocs_tmp);*/

end $$;

