CREATE TABLE IF NOT EXISTS telegram_stop_aliases (
  id SERIAL PRIMARY KEY,
  city_id INTEGER NOT NULL REFERENCES cities(id) ON DELETE CASCADE,
  alias VARCHAR(200) NOT NULL,
  normalized_alias VARCHAR(200) NOT NULL CHECK (length(normalized_alias) > 0),
  stop_id INTEGER NOT NULL REFERENCES stops(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (city_id, normalized_alias)
);

-- Known carrier abbreviations are seeded only when the Polotsk stop is unique.
-- Ambiguous platforms must be selected explicitly by an administrator.
WITH defaults(alias, normalized_alias, stop_name) AS (
  VALUES ('АВ/ВОКЗАЛ', 'аввокзал', 'Автовокзал'),
         ('ЗОДЧЕГО', 'зодчего', 'Зодчего Иоанна')
), candidates AS (
  SELECT c.id AS city_id, d.alias, d.normalized_alias, min(s.id) AS stop_id
  FROM defaults d JOIN cities c ON c.slug = 'polotsk'
  JOIN stops s ON s.city_id = c.id AND s.is_active = true
    AND lower(trim(s.name)) = lower(d.stop_name)
  GROUP BY c.id, d.alias, d.normalized_alias HAVING count(*) = 1
)
INSERT INTO telegram_stop_aliases(city_id, alias, normalized_alias, stop_id)
SELECT city_id, alias, normalized_alias, stop_id FROM candidates
ON CONFLICT (city_id, normalized_alias) DO NOTHING;