-- 0. Measurement
-- ticon3
CREATE TABLE measurement_ru AS
  SELECT * FROM measurement_tmp
  WHERE is_us=false;

-- ticon3, но с косяком из-за чанков, см. диаграмму я устал объяснять
INSERT INTO measurement_ru
  SELECT * FROM measurement
  WHERE is_mapped IS NULL AND is_us=false;

-- I. удаляем дубликаты ss
DELETE FROM segment_settings
WHERE sid IN (
  SELECT sid
  FROM segment s
    INNER JOIN (
                 SELECT
                   new_segment_id,
                   max(avs) AS avs
                 FROM segment
                 WHERE new_segment_id IS NOT NULL AND new_segment_id != 0
                 GROUP BY new_segment_id
                 HAVING count > 1) ssWithDuplicate
      ON (s.new_segment_id = ssWithDuplicate.new_segment_id)
  WHERE s.avs < ssWithDuplicate.avs) AND id > 1250201;

-- II. потом маппим оставшиеся сеттинги, только руские
-- из ticon2
 CREATE TABLE segment_settings_ru AS
  SELECT
    id,
    s.new_segment_id AS sid,
    lanes,
    lane_width,
    area_type,
    bus_stops,
    parking_maneuvers,
    slope,
    trucks,
    ss.status,
    light_table,
    has_stop,
    has_yield,
    a_tags,
    coef,
    m_tags,
    old_sid
  FROM segment_settings ss INNER JOIN segment s
      ON (s.sid = ss.sid)
  WHERE s.new_segment_id IS NOT NULL AND s.new_segment_id <> 0
    AND s.tz NOT IN ('US/Pacific', 'US/Central', 'America/New_York');

-- из ticon3
 INSERT INTO segment_settings_ru
  SELECT
    id,
    ss.sid,
    lanes,
    lane_width,
    area_type,
    bus_stops,
    parking_maneuvers,
    slope,
    trucks,
    ss.status,
    light_table,
    has_stop,
    has_yield,
    a_tags,
    coef,
    m_tags,
    old_sid
  FROM segment_settings ss INNER JOIN segment s
      ON (s.sid = ss.sid)
  WHERE s.new_segment_id IS NULL
    AND tz NOT IN ('US/Pacific', 'US/Central', 'America/New_York');

TRUNCATE segment_settings;
INSERT INTO segment_settings (SELECT * FROM segment_settings_ru ORDER BY id ASC) ON CONFLICT DO NOTHING;

-- III. потом грохаем старые сегменты и не русские
CREATE TABLE segment_ru AS
  SELECT * FROM segment
  WHERE new_segment_id IS NULL
    AND tz NOT IN ('US/Pacific', 'US/Central', 'America/New_York');

TRUNCATE path_segment;
TRUNCATE segment;
INSERT INTO segment SELECT * FROM segment_ru;


-- IV. удалим лишние эрии и джобы
DELETE FROM job WHERE area_id=ANY(ARRAY(
    SELECT id FROM area WHERE time_zone IN ('US/Pacific', 'US/Central', 'America/New_York')
));
DELETE FROM area WHERE time_zone IN ('US/Pacific', 'US/Central', 'America/New_York');


-- V. DROP measurement, measurement_tmp; переименовать _ru