CREATE TABLE `messages_to_assets` (
	`message_id` text NOT NULL,
	`asset_id` integer NOT NULL,
	PRIMARY KEY(`message_id`, `asset_id`),
	FOREIGN KEY (`message_id`) REFERENCES `messages`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON UPDATE no action ON DELETE no action
);

--> statement-breakpoint
-- Migrate existing asset-message relationships to the junction table
INSERT INTO `messages_to_assets` (`message_id`, `asset_id`)
SELECT `message_id`, `id` FROM `assets` WHERE `message_id` IS NOT NULL;

--> statement-breakpoint
ALTER TABLE `assets` DROP COLUMN `message_id`;