CREATE VIEW analytic.v_ropid_departure_boards
AS SELECT final.lon,
          final.lat,
          final.route_name,
          final.note,
          final.stop_correct,
          count(1) OVER (PARTITION BY (split_part(final.stop_correct, '/'::text, 1))) AS n_ceduli,
          final.alt_idos_name
   FROM ( SELECT stops.lon,
                 stops.lat,
                 tab.route_name,
                 tab.note,
                 tab.stop_correct,
                 stops.alt_idos_name,
                 row_number() OVER (PARTITION BY tab.note) AS n_duplicit
          FROM ( SELECT rdp.route_name,
                        rdp.note,
                        CASE
                            WHEN rdp.url_query_params::text ~~ '%aswIds%'::text THEN replace("substring"(rdp.url_query_params::text, 'aswIds[^=]*?=(.+?(?=&|$))'::text), '_'::text, '/'::text)
                            WHEN rdp.url_query_params::text ~~ '%cisIds%'::text THEN "substring"(rdp.url_query_params::text, 'cisIds[^=]*?=(.+?(?=&|$))'::text)
                            ELSE NULL::text
                            END AS stop_correct
                 FROM ropid_departures_presets rdp) tab
                   LEFT JOIN ropidgtfs_cis_stops stops ON
                  CASE
                      WHEN tab.stop_correct ~~ '%/%'::text THEN tab.stop_correct = stops.id::text
                      ELSE tab.stop_correct = split_part(stops.id::text, '/'::text, 1)
                      END OR tab.stop_correct = stops.cis::text) final
   WHERE final.n_duplicit = 1
   ORDER BY final.stop_correct;
