-- группируем зоны и заполнеям таблицу new_road_partition
CREATE OR REPLACE FUNCTION road_processing.group_zones_for_new_road_partition(
	p_part_id INTEGER,
    p_layer_id INTEGER,
    p_object_id TEXT
)
RETURNS VOID AS $$
begin
-- удаляем старый результат
	delete from road_processing.new_road_partition
	where part_id = p_part_id and layer_id = p_layer_id and object_id = p_object_id;
	
	insert into road_processing.new_road_partition(part_id, layer_id, object_id, zone_id, geom)
	SELECT
		p_part_id as part_id, p_layer_id as layer_id, p_object_id as object_id, zone_id,
		ST_Union(geom) as geom
	FROM road_processing.new_road_shards
	where part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id
	group by zone_id;
END;
$$ LANGUAGE plpgsql;

-- функция для определения различий старых и новых зон при маппинге
CREATE OR REPLACE FUNCTION road_processing.fill_diff_zones(
	p_part_id INTEGER,
    p_layer_id INTEGER,
    p_object_id TEXT
)
RETURNS VOID AS $$
begin
	
	-- удаляем старые записи
	delete from road_processing.diff_zones
	where part_id = p_part_id and p_layer_id = layer_id  and p_object_id = object_id;
	
	insert into road_processing.diff_zones(part_id, layer_id, object_id, zone_id, diff_area, diff_len)
	select 
		p_part_id as part_id,
		p_layer_id as layer_id,
		p_object_id as object_id,
		old_road.zone_id as zone_id,
		(ST_Area(old_road.geom) - ST_Area(new_road.geom)) / NULLIF(ST_Area(old_road.geom), 0) * 100 as diff_area,
		(ST_Length(ST_Boundary(old_road.geom))-ST_Length(ST_Boundary(new_road.geom))) / NULLIF(ST_Length(ST_Boundary(new_road.geom)), 0) * 100 as diff_len
	from road_processing.old_road_partition as old_road
	join road_processing.new_road_partition as new_road on old_road.zone_id = new_road.zone_id 
	where old_road.part_id = p_part_id AND old_road.layer_id = p_layer_id AND old_road.object_id = p_object_id
	and new_road.part_id = p_part_id AND new_road.layer_id = p_layer_id AND new_road.object_id = p_object_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION road_processing.fill_large_zones(
	p_part_id INTEGER,
    p_layer_id INTEGER,
    p_object_id TEXT,
    area_threshold double precision default 50,
    len_threshold double precision default 50,
    standart_segment_len double precision default 300,
    diff_len_coefficient double precision default 2
)
RETURNS VOID AS $$
begin
-- удаляем старые записи
	delete from road_processing.large_zones
	where part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id;
	
-- находим увеличенные зоны	
	with increased_zones as (
		select zone_id
		from road_processing.diff_zones
		where (diff_area < 0 and abs(diff_area) >= area_threshold ) 
			or (diff_len < 0 and abs(diff_len) >= len_threshold )
		 and part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id
	),
-- отбираем большие зоны для разбиения
	zones_for_partitions as (
	select iz.zone_id as zone_id, nrp.geom as geom
	from increased_zones as iz 
	join road_processing.new_road_partition as nrp on iz.zone_id = nrp.zone_id
	where  ST_Length(ST_Boundary(nrp.geom))>= diff_len_coefficient*standart_segment_len AND nrp.part_id = p_part_id
		 AND nrp.layer_id = p_layer_id AND nrp.object_id = p_object_id
	)
	insert into road_processing.large_zones(part_id, layer_id, object_id, zone_id, geom)	
select 
		p_part_id as part_id,
		p_layer_id as layer_id,
		p_object_id as object_id,
		zone_id as zone_id,
		geom
	from zones_for_partitions;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION road_processing.merge_small_intersected_mapped_zones(
	p_part_id INTEGER,
    p_layer_id INTEGER,
    p_object_id TEXT,
    max_area DOUBLE PRECISION DEFAULT 20000,		-- максимальная площадь любого полигона
    max_length DOUBLE PRECISION DEFAULT 350,		-- максимальная длинна любого полигона
    min_area DOUBLE PRECISION DEFAULT 5000,      -- полигоны меньше этой площади считаются "малыми"
    min_length DOUBLE PRECISION DEFAULT 150      -- полигоны меньше этого размера также считаются "малыми"
)
--RETURNS INTEGER AS $$
RETURNS VOID AS $$
DECLARE
	merged INTEGER;
    v_small_id INTEGER;
    v_target_id INTEGER;
    v_new_geom GEOMETRY(GEOMETRY, 3857);
    v_small_geom GEOMETRY(GEOMETRY, 3857);
    v_small_area DOUBLE PRECISION;
    v_small_length DOUBLE PRECISION;
BEGIN
    LOOP
        -- Находим один малый полигон (по площади или линейному размеру)
        SELECT 
            zone_id, 
            geom,
            ST_Area(geom),
            ST_MaxDistance(geom, geom)  -- приблизительный линейный размер
        INTO 
            v_small_id,
            v_small_geom,
            v_small_area,
            v_small_length
        FROM road_processing.new_road_partition
        WHERE (ST_Area(geom) <= min_area 
           OR ST_MaxDistance(geom, geom) <= min_length)
			and part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id
        LIMIT 1;
        
--         Если малых полигонов нет - выходим
        IF NOT FOUND THEN
            EXIT;
        END IF;
        
--         Ищем пересекающийся полигон с НАИМЕНЬШЕЙ бы полученной площадью и длинне
        SELECT 
            b.zone_id,
            ST_Union(v_small_geom, b.geom)
        INTO 
            v_target_id,
            v_new_geom
        FROM road_processing.new_road_partition b
        WHERE b.zone_id != v_small_id
          AND ST_Intersects(v_small_geom, b.geom) 
		  and part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id
-- выбираем наименьший по площади и длинне полигон
        ORDER BY 
			 (COALESCE(ST_MaxDistance(v_small_geom, v_small_geom), 0) + 
             COALESCE(ST_MaxDistance(b.geom, b.geom), 0)) * 0.5 + 
            (COALESCE(ST_Area(v_small_geom), 0) + COALESCE(ST_Area(b.geom), 0)) * 0.5 ASC
        LIMIT 1;
        
        -- Если ничего не найдено, выходим
        IF NOT FOUND THEN
            EXIT;
        END IF;
        
        -- Обновляем геометрию первого полигона
        UPDATE road_processing.new_road_partition 
        SET geom = v_new_geom 
        WHERE zone_id = v_target_id and part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id;
        
        GET DIAGNOSTICS merged = ROW_COUNT;
        
        -- Удаляем малый полигон
        DELETE FROM road_processing.new_road_partition WHERE zone_id = v_small_id and part_id = p_part_id AND layer_id = p_layer_id AND object_id = p_object_id;

    END LOOP;

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION road_processing.make_part_of_road_mapping(
	
p_part_id INTEGER,
    p_layer_id INTEGER,
    p_object_id TEXT,
        
    standart_segment_len DOUBLE PRECISION DEFAULT 300, -- желаемая длинна сегмента дороги
    standart_segment_wide DOUBLE PRECISION DEFAULT 50, -- предполагаемая ширина дороги
    max_segment_area DOUBLE PRECISION DEFAULT 20000,	-- максимальная площадь любого полигона
    max_segment_length DOUBLE PRECISION DEFAULT 350,	-- максимальная длинна любого полигона
    min_segment_area DOUBLE PRECISION DEFAULT 5000,     -- полигоны меньше этой площади считаются "малыми"
    min_segment_length DOUBLE PRECISION DEFAULT 150,     -- полигоны меньше этого размера также считаются "малыми"
    merge_area_tolerance_percent DOUBLE PRECISION DEFAULT 50, -- процентный порог зон для слияния по общей площади их пересечения
intersection_area_weight double precision default 0.2, -- вес площади пересечения для функции классфикации шарда road_part_classification
    boundary_length_weight double precision default 0.2, -- вес длины границы для функции классификации шарда road_part_classification
    centroid_distance_weight double precision default 0.2, -- вес расстояния между центроидами для функции классификации шарда road_part_classification
    min_distance_weight double precision default 0.2, -- вес минимального расстояния для функции классификации шарда road_part_classification
    lies_inside_weight double precision default 1.0, -- вес того лежит ли часть дороги целиком в зоне для функции классификации шарда road_part_classification
    topology_distance_tolerance DOUBLE PRECISION DEFAULT 2.0, --  параметр для функции generate_road_skeleton - дистанция в ST_SimplifyPreserveTopology
    shard_segment_len DOUBLE PRECISION DEFAULT 1.0, -- длинна "осколка" дороги. Функция fill_road_parts, параметр для ST_Segmentize
    buffer_distance DOUBLE PRECISION DEFAULT 1.0, -- величина буффера для функции ST_Buffer в process_again_and_get_better_zones
    buffer_distance_for_group_zones DOUBLE PRECISION DEFAULT 0.01, -- величина буфера в функции группировки классифицированных шардов
    unsuitable_road_zones_area_threshold double precision default 100, -- избавляемся от зон с меньшей площадью
    unsuitable_road_zones_len_threshold double precision default 100,  -- избавляемся от зон с меньшей длинной
    area_threshold double precision default 50, -- процентный порог разницы площадей зон для функции fill_large_zones
    len_threshold DOUBLE PRECISION DEFAULT 50, -- процентный порог разницы длин зон для функции fill_large_zones
    diff_len_coefficient DOUBLE PRECISION DEFAULT 2.0 -- коэффициент разницы длины зоны функции fill_large_zones
)
RETURNS VOID AS $$
begin
-- разбиваем дорогу на шарды
perform road_processing.fill_new_road_shards(p_part_id => p_part_id, p_object_id => p_object_id,
	 p_layer_id => p_layer_id, segment_len => shard_segment_len);

-- проводим классификацию каждого шарда
perform road_processing.new_road_shards_classification(
	p_part_id => p_part_id, p_object_id => p_object_id,  p_layer_id => p_layer_id,
	intersection_area_weight => intersection_area_weight, boundary_length_weight => boundary_length_weight,
	centroid_distance_weight => centroid_distance_weight, min_distance_weight => min_distance_weight,
	lies_inside_weight => lies_inside_weight);

-- группируем зоны
perform road_processing.group_zones_for_new_road_partition(p_part_id => p_part_id, p_object_id => p_object_id,  p_layer_id => p_layer_id
	);

-- определяем различия зон
perform road_processing.fill_diff_zones(p_part_id => p_part_id, p_layer_id => p_layer_id,
		 p_object_id => p_object_id
	);

-- определяем наибольшие зоны
perform road_processing.fill_large_zones(p_part_id => p_part_id, p_layer_id => p_layer_id,
		p_object_id => p_object_id,
		standart_segment_len => standart_segment_len,
		area_threshold => area_threshold, len_threshold => len_threshold,
		diff_len_coefficient=> diff_len_coefficient
	);

-- разбиваем большие зоны
perform road_processing.make_large_zones_partition(
			p_part_id => p_part_id, p_layer_id => p_layer_id, p_object_id => p_object_id,
			process_for_better_zones => process_for_better_zones, 
			process_for_split_overlapped_zones => process_for_split_overlapped_zones,
			process_for_fill_road_parts_considering_zones => process_for_fill_road_parts_considering_zones,
            standart_segment_len => standart_segment_len, standart_segment_wide => standart_segment_wide,
            max_segment_area => max_segment_area, min_segment_area => min_segment_area,
            max_segment_length => max_segment_length, min_segment_length => min_segment_length,
			merge_area_tolerance_percent => merge_area_tolerance_percent,
			intersection_area_weight => intersection_area_weight,
			boundary_length_weight => boundary_length_weight,
			centroid_distance_weight => centroid_distance_weight,
			min_distance_weight => min_distance_weight,
			lies_inside_weight => lies_inside_weight,
			shard_segment_len => shard_segment_len,
			buffer_distance => buffer_distance,			
			unsuitable_road_zones_area_threshold => unsuitable_road_zones_area_threshold,
			unsuitable_road_zones_len_threshold => unsuitable_road_zones_len_threshold
		
	);
-- производим маппинг для разбитых сегментов из больших зон (сопоставляем zone_id)
perform road_processing.new_large_zones_classification(p_layer_id => p_layer_id, p_object_id => p_object_id,
			intersection_area_weight => intersection_area_weight,
			boundary_length_weight => boundary_length_weight,
			centroid_distance_weight => centroid_distance_weight,
			min_distance_weight => min_distance_weight,
			lies_inside_weight => lies_inside_weight
	);

-- добавляем классифицированные сегменты из больших зон в итоговое разбиение
perform road_processing.manage_new_large_zones(p_layer_id => p_layer_id,
			p_object_id => p_object_id);

-- объединяем малые зоны
perform road_processing.merge_small_intersected_mapped_zones(p_part_id => p_part_id,
			p_layer_id => p_layer_id,p_object_id => p_object_id,
			max_area => max_segment_area, max_length => max_segment_length,
			min_area => min_segment_area, min_length => min_segment_length
	);
END;
$$ LANGUAGE plpgsql;