73 lines
4.2 KiB
SQL
73 lines
4.2 KiB
SQL
-- Migration: regions + region_bounds.
|
|
-- A region has a name/description and zero or more bounding polygons. Each
|
|
-- region_bounds row stores one pasted GeoJSON geometry as raw text (see
|
|
-- admin.region.* and the superadmin-ui Regions screen).
|
|
|
|
CREATE TABLE IF NOT EXISTS `regions` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`description` text,
|
|
`added_at` text DEFAULT (CURRENT_TIMESTAMP) NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS `region_bounds` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`region_id` integer NOT NULL,
|
|
`name` text,
|
|
`description` text,
|
|
`priority` integer NOT NULL DEFAULT 0,
|
|
`geojson` text NOT NULL,
|
|
`added_at` text DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
|
|
FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
|
|
-- Seed region 1: Mahabubnagar town, bounded by the Mbnr-1 and Mbnr-2
|
|
-- polygons (see apps/backend/src/map-assets/Mbnr-{1,2}.geojson). Guarded so
|
|
-- re-runs and pre-existing rows are left untouched.
|
|
INSERT OR IGNORE INTO `regions` (`id`, `name`, `description`)
|
|
VALUES (1, 'Mahabubnagar', 'Mahabubnagar town and surrounding areas');
|
|
--> statement-breakpoint
|
|
|
|
INSERT INTO `region_bounds` (`region_id`, `name`, `description`, `priority`, `geojson`)
|
|
SELECT 1, 'Mbnr-1', 'Mahabubnagar town core coverage area', 1, '{"type":"Polygon","coordinates":[[[77.9802307,16.7224827],[77.9832452,16.7186281],[77.9885003,16.7191643],[78.0027771,16.7184524],[78.0167635,16.717821],[78.0176657,16.7225884],[78.0245685,16.7301059],[78.0346065,16.7366045],[78.0483126,16.7469324],[78.0558892,16.7506815],[78.0533183,16.7547082],[78.0497798,16.7596596],[78.0456892,16.7640471],[78.0383339,16.7641426],[78.0343234,16.758687],[78.021375,16.7570244],[78.0163704,16.758279],[78.0128516,16.7590231],[78.0120219,16.7648881],[78.0095524,16.7664414],[77.99363,16.7660548],[77.9720422,16.7596124],[77.9679337,16.7449906],[77.9802307,16.7224827]]]}'
|
|
WHERE NOT EXISTS (SELECT 1 FROM `region_bounds` WHERE `region_id` = 1);
|
|
--> statement-breakpoint
|
|
|
|
INSERT INTO `region_bounds` (`region_id`, `name`, `description`, `priority`, `geojson`)
|
|
SELECT 1, 'Mbnr-2', 'Mahabubnagar extended coverage area', 2, '{"type":"Polygon","coordinates":[[[77.9773739,16.7127921],[77.9950359,16.7080928],[78.0187929,16.70999],[78.0289444,16.7211582],[78.0570875,16.7502883],[78.0662922,16.7519867],[78.0652719,16.7684088],[78.0204341,16.7689639],[78.0023161,16.770186],[77.9984809,16.7850848],[77.983305,16.782843],[77.971574,16.7646028],[77.9686551,16.7454905],[77.9773739,16.7127921]]]}'
|
|
WHERE NOT EXISTS (SELECT 1 FROM `region_bounds` WHERE `region_id` = 1 AND `geojson` LIKE '%77.9773739%');
|
|
--> statement-breakpoint
|
|
|
|
-- Per-region Telegram delivery creds. Order notifications are fanned out to
|
|
-- every cred row of the order's region (see telegram-service.ts).
|
|
CREATE TABLE IF NOT EXISTS `region_telegram_creds` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`region_id` integer NOT NULL,
|
|
`bot_token` text NOT NULL,
|
|
`chat_id` text NOT NULL,
|
|
`added_at` text DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
|
|
FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX IF NOT EXISTS `region_telegram_creds_region_chat_unique`
|
|
ON `region_telegram_creds` (`region_id`, `chat_id`);
|
|
--> statement-breakpoint
|
|
|
|
INSERT INTO `region_telegram_creds` (`region_id`, `bot_token`, `chat_id`)
|
|
SELECT 1, '8410461852:AAGXQCwRPFbndqwTgLJh8kYxST4Z0vgh72U', '5147760058'
|
|
WHERE NOT EXISTS (SELECT 1 FROM `region_telegram_creds` WHERE `region_id` = 1 AND `chat_id` = '5147760058');
|
|
--> statement-breakpoint
|
|
|
|
-- Push-token region tracking: which region a device token belongs to.
|
|
-- Nullable so pre-existing rows and region-less saves keep working.
|
|
ALTER TABLE `notif_creds` ADD COLUMN `region_id` integer REFERENCES `regions`(`id`);
|
|
--> statement-breakpoint
|
|
ALTER TABLE `unlogged_user_tokens` ADD COLUMN `region_id` integer REFERENCES `regions`(`id`);
|
|
--> statement-breakpoint
|
|
|
|
-- Existing push tokens predate regions; attribute them to region 1.
|
|
UPDATE `notif_creds` SET `region_id` = 1 WHERE `region_id` IS NULL;
|
|
--> statement-breakpoint
|
|
UPDATE `unlogged_user_tokens` SET `region_id` = 1 WHERE `region_id` IS NULL;
|
|
--> statement-breakpoint
|