CREATE TABLE `__pigeon_tasks_rebuild` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`uid` text NOT NULL,
	`title` text NOT NULL,
	`url` text,
	`status` text DEFAULT 'pending' NOT NULL,
	`priority` text,
	`claimed_by` text,
	`blocked_by` text,
	`labels` text,
	`note` text,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	`claimed_at` text,
	`blocked_at` text,
	`done_at` text,
	CONSTRAINT `tasks_uid_unique` UNIQUE(`uid`)
);
--> statement-breakpoint
INSERT INTO `__pigeon_tasks_rebuild` (
	`id`, `uid`, `title`, `url`, `status`, `priority`, `claimed_by`, `blocked_by`, `labels`, `note`,
	`created_at`, `updated_at`, `claimed_at`, `blocked_at`, `done_at`
)
SELECT
	`id`, `uid`, `title`, `url`, `status`, `priority`, `claimed_by`, `blocked_by`, `labels`, `note`,
	`created_at`, `updated_at`, `claimed_at`, `blocked_at`, `done_at`
FROM `tasks`;
--> statement-breakpoint
DROP TABLE `tasks`;
--> statement-breakpoint
ALTER TABLE `__pigeon_tasks_rebuild` RENAME TO `tasks`;
--> statement-breakpoint
CREATE INDEX `tasks_status_created_idx` ON `tasks` (`status`,`created_at`,`id`);
--> statement-breakpoint
CREATE TABLE `__pigeon_message_reads_rebuild` (
	`message_id` integer NOT NULL,
	`reader` text NOT NULL,
	`read_at` text NOT NULL,
	PRIMARY KEY(`message_id`, `reader`)
);
--> statement-breakpoint
INSERT INTO `__pigeon_message_reads_rebuild` (`message_id`, `reader`, `read_at`)
SELECT `message_id`, `reader`, `read_at` FROM `message_reads`;
--> statement-breakpoint
DROP TABLE `message_reads`;
--> statement-breakpoint
CREATE TABLE `__pigeon_messages_rebuild` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`uid` text NOT NULL,
	`body` text NOT NULL,
	`author` text,
	`mentions` text,
	`created_at` text NOT NULL,
	`resolved_at` text,
	CONSTRAINT `messages_uid_unique` UNIQUE(`uid`)
);
--> statement-breakpoint
INSERT INTO `__pigeon_messages_rebuild` (
	`id`, `uid`, `body`, `author`, `mentions`, `created_at`, `resolved_at`
)
SELECT `id`, `uid`, `body`, `author`, `mentions`, `created_at`, `resolved_at` FROM `messages`;
--> statement-breakpoint
DROP TABLE `messages`;
--> statement-breakpoint
ALTER TABLE `__pigeon_messages_rebuild` RENAME TO `messages`;
--> statement-breakpoint
CREATE INDEX `messages_created_idx` ON `messages` (`created_at`,`id`);
--> statement-breakpoint
CREATE TABLE `message_reads` (
	`message_id` integer NOT NULL,
	`reader` text NOT NULL,
	`read_at` text NOT NULL,
	PRIMARY KEY(`message_id`, `reader`),
	FOREIGN KEY (`message_id`) REFERENCES `messages`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
INSERT INTO `message_reads` (`message_id`, `reader`, `read_at`)
SELECT `message_id`, `reader`, `read_at` FROM `__pigeon_message_reads_rebuild`;
--> statement-breakpoint
DROP TABLE `__pigeon_message_reads_rebuild`;
--> statement-breakpoint
CREATE INDEX `message_reads_reader_idx` ON `message_reads` (`reader`,`read_at`);
