-- 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