Zakhar Timoshenko
666a0b268f
- Added TelegramStopAliasService for handling stop aliases in the database. - Created API endpoints for managing stop aliases (list, create, update, delete). - Introduced TelegramStopAliases component in the frontend for user interaction with stop aliases. - Updated TelegramImportService to utilize stop aliases for message processing. - Enhanced telegramCancellationParser to resolve stop names using aliases. - Added migration script to create telegram_stop_aliases table and seed initial data. - Updated documentation to include details about stop alias functionality.
27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
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;
|