-- Sound for a new request. Until now the toggle existed only in the browser and
-- was forgotten on every reload, so nothing ever played. These columns make the
-- choice per restaurant: a built-in tone by name, or an uploaded file.
ALTER TABLE `restaurant_settings` ADD `sound_enabled` integer DEFAULT 1 NOT NULL;
--> statement-breakpoint
ALTER TABLE `restaurant_settings` ADD `sound_order` text DEFAULT 'zvono' NOT NULL;
--> statement-breakpoint
ALTER TABLE `restaurant_settings` ADD `sound_call` text DEFAULT 'dvoton' NOT NULL;
--> statement-breakpoint
ALTER TABLE `restaurant_settings` ADD `sound_volume` integer DEFAULT 70 NOT NULL;
--> statement-breakpoint
ALTER TABLE `restaurant_settings` ADD `sound_order_asset_id` text;
--> statement-breakpoint
ALTER TABLE `restaurant_settings` ADD `sound_call_asset_id` text;
--> statement-breakpoint
-- Also browser-only until now, for the same reason.
ALTER TABLE `restaurant_settings` ADD `show_total` integer DEFAULT 1 NOT NULL;
--> statement-breakpoint

-- Who was covering the table when the request came in. Copied onto the ticket
-- at that moment, because a table changes hands and a report from two months
-- ago must not follow today's assignment.
ALTER TABLE `tickets` ADD `waiter_email` text;
--> statement-breakpoint
CREATE INDEX `idx_tickets_restaurant_waiter_created` ON `tickets` (`restaurant_id`,`waiter_email`,`created_at`);
--> statement-breakpoint
UPDATE `tickets`
SET `waiter_email` = (
	SELECT COALESCE(d.`claimed_email`, d.`assigned_email`)
	FROM `dining_tables` d WHERE d.`id` = `tickets`.`table_id`
)
WHERE `waiter_email` IS NULL;
--> statement-breakpoint

-- Web push, so a waiter is told about a new order with the app closed. One row
-- per installed app: the same person on a phone and a tablet has two.
CREATE TABLE `push_subscriptions` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`email` text NOT NULL,
	`endpoint` text NOT NULL,
	`p256dh` text NOT NULL,
	`auth` text NOT NULL,
	`user_agent` text DEFAULT '' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`last_sent_at` text,
	`failure_count` integer DEFAULT 0 NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `push_subscriptions_endpoint_unique` ON `push_subscriptions` (`endpoint`);
--> statement-breakpoint
CREATE INDEX `idx_push_subscriptions_restaurant_email` ON `push_subscriptions` (`restaurant_id`,`email`);
--> statement-breakpoint

-- Installation-wide values that are not tied to one restaurant. Used for the
-- VAPID key pair, which is generated on first use so a cPanel installation
-- never needs a terminal to set push up.
CREATE TABLE `app_settings` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint

-- The 'queue' role is new: an account that may only open the operations screen
-- and clear requests off it. Waiters keep the 'staff' role, which from this
-- version means the phone app at /moj-sto and nothing else. Nobody is moved
-- automatically — existing staff accounts stay waiters, which is what they
-- were being used as.
CREATE INDEX `idx_memberships_restaurant_role` ON `memberships` (`restaurant_id`,`role`);
--> statement-breakpoint
PRAGMA optimize;