UPDATE `dining_tables`
SET `zone` = UPPER(SUBSTR(`zone`, 1, 1)) || SUBSTR(`zone`, 2)
WHERE `zone` <> '' AND SUBSTR(`zone`, 1, 1) <> UPPER(SUBSTR(`zone`, 1, 1));
--> statement-breakpoint
CREATE TABLE `restaurant_zones` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`name` text NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `restaurant_zones_name_unique` ON `restaurant_zones` (`restaurant_id`,`name`);
--> statement-breakpoint
CREATE INDEX `idx_restaurant_zones_restaurant_sort` ON `restaurant_zones` (`restaurant_id`,`sort_order`);
--> statement-breakpoint
INSERT INTO `restaurant_zones` (`id`, `restaurant_id`, `name`, `sort_order`)
SELECT 'zone_' || lower(hex(randomblob(16))), z.`restaurant_id`, z.`zone`, 0
FROM (SELECT DISTINCT `restaurant_id`, `zone` FROM `dining_tables` WHERE `zone` <> '') z;
--> statement-breakpoint
ALTER TABLE `dining_tables` ADD `assigned_email` text;
--> statement-breakpoint
ALTER TABLE `dining_tables` ADD `claimed_email` text;
--> statement-breakpoint
ALTER TABLE `dining_tables` ADD `claimed_at` text;
--> statement-breakpoint
CREATE INDEX `idx_dining_tables_assigned` ON `dining_tables` (`restaurant_id`,`assigned_email`);
--> statement-breakpoint
CREATE INDEX `idx_dining_tables_claimed` ON `dining_tables` (`restaurant_id`,`claimed_email`);
--> statement-breakpoint
PRAGMA optimize;