CREATE TABLE `revision_rounds` (
	`id` int AUTO_INCREMENT NOT NULL,
	`project_id` int NOT NULL,
	`number` int NOT NULL,
	`title` varchar(150) NOT NULL,
	`notes` text,
	`base_model_id` int NOT NULL,
	`result_model_id` int,
	`status` enum('open','closed') NOT NULL DEFAULT 'open',
	`created_by_user_id` int,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`closed_at` datetime,
	CONSTRAINT `revision_rounds_id` PRIMARY KEY(`id`),
	CONSTRAINT `revision_rounds_project_number_unique` UNIQUE(`project_id`,`number`)
);
--> statement-breakpoint
ALTER TABLE `annotations` ADD `created_by_client_session_id` int;--> statement-breakpoint
ALTER TABLE `annotations` ADD `revision_round_id` int;--> statement-breakpoint
ALTER TABLE `revision_rounds` ADD CONSTRAINT `revision_rounds_project_id_projects_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `revision_rounds` ADD CONSTRAINT `revision_rounds_base_model_id_models_id_fk` FOREIGN KEY (`base_model_id`) REFERENCES `models`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `revision_rounds` ADD CONSTRAINT `revision_rounds_result_model_id_models_id_fk` FOREIGN KEY (`result_model_id`) REFERENCES `models`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `revision_rounds` ADD CONSTRAINT `revision_rounds_created_by_user_id_users_id_fk` FOREIGN KEY (`created_by_user_id`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `revision_rounds_base_idx` ON `revision_rounds` (`base_model_id`);--> statement-breakpoint
ALTER TABLE `annotations` ADD CONSTRAINT `annotations_created_by_client_session_id_client_sessions_id_fk` FOREIGN KEY (`created_by_client_session_id`) REFERENCES `client_sessions`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `annotations` ADD CONSTRAINT `annotations_revision_round_id_revision_rounds_id_fk` FOREIGN KEY (`revision_round_id`) REFERENCES `revision_rounds`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `annotations_round_idx` ON `annotations` (`revision_round_id`);