CREATE TABLE `table_visits` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`table_id` text NOT NULL,
	`status` text DEFAULT 'open' NOT NULL,
	`opened_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`closed_at` text,
	`closed_by` text,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`table_id`) REFERENCES `dining_tables`(`id`) ON UPDATE no action ON DELETE restrict,
	CONSTRAINT `table_visits_status_check` CHECK (`status` IN ('open', 'closed'))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `table_visits_one_open_per_table` ON `table_visits` (`table_id`) WHERE `status` = 'open';
--> statement-breakpoint
CREATE INDEX `idx_table_visits_restaurant_status_opened` ON `table_visits` (`restaurant_id`,`status`,`opened_at`);
--> statement-breakpoint
ALTER TABLE `tickets` ADD `visit_id` text REFERENCES table_visits(id) ON DELETE set null;
--> statement-breakpoint
CREATE INDEX `idx_tickets_visit_created` ON `tickets` (`visit_id`,`created_at`);
--> statement-breakpoint
INSERT INTO `table_visits` (`id`, `restaurant_id`, `table_id`, `status`, `opened_at`)
SELECT 'visit_migrated_' || lower(hex(randomblob(16))), t.`restaurant_id`, t.`table_id`, 'open', MIN(t.`created_at`)
FROM `tickets` t
WHERE t.`status` = 'open'
GROUP BY t.`restaurant_id`, t.`table_id`;
--> statement-breakpoint
UPDATE `tickets`
SET `visit_id` = (
	SELECT v.`id`
	FROM `table_visits` v
	WHERE v.`restaurant_id` = `tickets`.`restaurant_id`
	  AND v.`table_id` = `tickets`.`table_id`
	  AND v.`status` = 'open'
	LIMIT 1
)
WHERE `status` = 'open';
--> statement-breakpoint
PRAGMA optimize;