Zakhar Timoshenko
f7f0b039dd
- Implemented Telegram cancellation parsing logic in `telegramCancellationParser.js`. - Created unit tests for cancellation parsing in `telegramCancellationParser.test.js`. - Developed integration tests for Telegram import service in `telegramImport.integration.test.js`. - Added API tests for Telegram import endpoints in `telegramImportApi.test.js`. - Introduced service tests for Telegram import logic in `telegramImportService.test.js`. - Documented the Telegram cancellation import process in `telegram-cancellation-import.md`. - Built a React component for managing Telegram imports in `TelegramImportPanel.jsx`.
46 lines
2.0 KiB
SQL
46 lines
2.0 KiB
SQL
-- Admin alert forms already store UTC clock values. Give these columns an
|
|
-- explicit timezone so imports, API serialization and midnight expiry agree
|
|
-- regardless of the PostgreSQL or Node.js host timezone. Safe to rerun.
|
|
DO $$
|
|
DECLARE col text;
|
|
BEGIN
|
|
FOREACH col IN ARRAY ARRAY['start_time', 'end_time', 'created_at'] LOOP
|
|
IF EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = current_schema() AND table_name = 'alerts'
|
|
AND column_name = col AND data_type = 'timestamp without time zone') THEN
|
|
EXECUTE format('ALTER TABLE alerts ALTER COLUMN %I TYPE timestamptz USING %I AT TIME ZONE ''UTC''', col, col);
|
|
END IF;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
ALTER TABLE alerts ADD COLUMN IF NOT EXISTS notification_pending BOOLEAN NOT NULL DEFAULT false;
|
|
|
|
CREATE TABLE IF NOT EXISTS telegram_import_messages (
|
|
id SERIAL PRIMARY KEY,
|
|
city_id INTEGER NOT NULL REFERENCES cities(id),
|
|
channel_id TEXT NOT NULL,
|
|
channel_username VARCHAR(32) NOT NULL,
|
|
message_id INTEGER NOT NULL CHECK (message_id > 0),
|
|
published_at TIMESTAMPTZ NOT NULL,
|
|
edited_at TIMESTAMPTZ,
|
|
content_hash CHAR(64) NOT NULL,
|
|
raw_text TEXT NOT NULL,
|
|
has_media BOOLEAN NOT NULL DEFAULT false,
|
|
status VARCHAR(20) NOT NULL CHECK (status IN ('review', 'published', 'dismissed', 'expired')),
|
|
reasons JSONB NOT NULL DEFAULT '[]',
|
|
parsed JSONB NOT NULL DEFAULT '{}',
|
|
reviewed_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (city_id, channel_id, message_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS telegram_import_alerts (
|
|
message_id INTEGER NOT NULL REFERENCES telegram_import_messages(id) ON DELETE CASCADE,
|
|
route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
|
|
alert_id INTEGER UNIQUE REFERENCES alerts(id) ON DELETE SET NULL,
|
|
PRIMARY KEY (message_id, route_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_import_review ON telegram_import_messages(city_id, status, published_at DESC);
|