CREATE TABLE `course_sessions` (
	`id` char(36) NOT NULL,
	`course_id` char(36) NOT NULL,
	`session_start_time` datetime NOT NULL,
	`session_end_time` datetime,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp,
	CONSTRAINT `course_sessions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `courses` (
	`id` char(36) NOT NULL,
	`instructor_id` char(36) NOT NULL,
	`title` varchar(255) NOT NULL,
	`description` text,
	`date` date NOT NULL,
	`start_time` time NOT NULL,
	`duration_minutes` int NOT NULL,
	`session_code` varchar(20) NOT NULL,
	`allow_anonymous` boolean NOT NULL DEFAULT false,
	`status` enum('draft','active','completed') NOT NULL DEFAULT 'draft',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp,
	`version` int NOT NULL DEFAULT 1,
	CONSTRAINT `courses_id` PRIMARY KEY(`id`),
	CONSTRAINT `courses_session_code_unique` UNIQUE(`session_code`),
	CONSTRAINT `idx_courses_session_code` UNIQUE(`session_code`)
);
--> statement-breakpoint
CREATE TABLE `join_codes` (
	`id` char(36) NOT NULL,
	`session_id` char(36) NOT NULL,
	`code` varchar(50) NOT NULL,
	`is_custom` boolean NOT NULL DEFAULT false,
	`status` enum('active','revoked') NOT NULL DEFAULT 'active',
	`generated_at` timestamp NOT NULL DEFAULT (now()),
	`revoked_at` timestamp,
	`created_by` char(36) NOT NULL,
	`usage_count` int NOT NULL DEFAULT 0,
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `join_codes_id` PRIMARY KEY(`id`),
	CONSTRAINT `idx_join_codes_unique` UNIQUE(`session_id`,`code`)
);
--> statement-breakpoint
CREATE TABLE `participants` (
	`id` char(36) NOT NULL,
	`session_id` char(36) NOT NULL,
	`join_code_id` char(36),
	`join_method` enum('code','link') NOT NULL,
	`name` varchar(255),
	`join_timestamp` datetime NOT NULL,
	`leave_timestamp` datetime,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `participants_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `session_dashboard` (
	`id` char(36) NOT NULL,
	`session_id` char(36) NOT NULL,
	`red_count` int NOT NULL DEFAULT 0,
	`yellow_count` int NOT NULL DEFAULT 0,
	`green_count` int NOT NULL DEFAULT 0,
	`total_participants` int NOT NULL DEFAULT 0,
	`active_participants` int NOT NULL DEFAULT 0,
	`elapsed_seconds` int NOT NULL DEFAULT 0,
	`last_updated` timestamp NOT NULL DEFAULT (now()),
	`version` int NOT NULL DEFAULT 1,
	CONSTRAINT `session_dashboard_id` PRIMARY KEY(`id`),
	CONSTRAINT `session_dashboard_session_id_unique` UNIQUE(`session_id`),
	CONSTRAINT `idx_session_dashboard_session_id` UNIQUE(`session_id`)
);
--> statement-breakpoint
CREATE TABLE `session_summaries` (
	`id` char(36) NOT NULL,
	`session_id` char(36) NOT NULL,
	`total_participants` int NOT NULL,
	`participants_via_link` int NOT NULL DEFAULT 0,
	`participants_via_code` int NOT NULL DEFAULT 0,
	`codes_generated_count` int NOT NULL DEFAULT 0,
	`codes_revoked_count` int NOT NULL DEFAULT 0,
	`final_red_count` int NOT NULL DEFAULT 0,
	`final_yellow_count` int NOT NULL DEFAULT 0,
	`final_green_count` int NOT NULL DEFAULT 0,
	`duration_seconds` int NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `session_summaries_id` PRIMARY KEY(`id`),
	CONSTRAINT `session_summaries_session_id_unique` UNIQUE(`session_id`),
	CONSTRAINT `idx_session_summaries_session_id` UNIQUE(`session_id`)
);
--> statement-breakpoint
CREATE TABLE `status_events` (
	`id` char(36) NOT NULL,
	`participant_id` char(36) NOT NULL,
	`status` enum('red','yellow','green') NOT NULL,
	`triggered_at` datetime NOT NULL,
	`auto_reset_at` datetime NOT NULL,
	`is_reset` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `status_events_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` char(36) NOT NULL,
	`google_id` varchar(255) NOT NULL,
	`email` varchar(255) NOT NULL,
	`name` varchar(255),
	`profile_picture_url` varchar(500),
	`role` enum('teacher','admin') NOT NULL DEFAULT 'teacher',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`is_active` boolean NOT NULL DEFAULT true,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_google_id_unique` UNIQUE(`google_id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
ALTER TABLE `course_sessions` ADD CONSTRAINT `course_sessions_course_id_courses_id_fk` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `courses` ADD CONSTRAINT `courses_instructor_id_users_id_fk` FOREIGN KEY (`instructor_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `join_codes` ADD CONSTRAINT `join_codes_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `join_codes` ADD CONSTRAINT `join_codes_created_by_users_id_fk` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `participants` ADD CONSTRAINT `participants_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `participants` ADD CONSTRAINT `participants_join_code_id_join_codes_id_fk` FOREIGN KEY (`join_code_id`) REFERENCES `join_codes`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `session_dashboard` ADD CONSTRAINT `session_dashboard_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `session_summaries` ADD CONSTRAINT `session_summaries_session_id_course_sessions_id_fk` FOREIGN KEY (`session_id`) REFERENCES `course_sessions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `status_events` ADD CONSTRAINT `status_events_participant_id_participants_id_fk` FOREIGN KEY (`participant_id`) REFERENCES `participants`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `idx_course_sessions_course_id` ON `course_sessions` (`course_id`);--> statement-breakpoint
CREATE INDEX `idx_course_sessions_is_active` ON `course_sessions` (`is_active`);--> statement-breakpoint
CREATE INDEX `idx_course_sessions_start_time` ON `course_sessions` (`session_start_time`);--> statement-breakpoint
CREATE INDEX `idx_courses_instructor_id` ON `courses` (`instructor_id`);--> statement-breakpoint
CREATE INDEX `idx_courses_status` ON `courses` (`status`);--> statement-breakpoint
CREATE INDEX `idx_courses_date` ON `courses` (`date`);--> statement-breakpoint
CREATE INDEX `idx_courses_deleted_at` ON `courses` (`deleted_at`);--> statement-breakpoint
CREATE INDEX `idx_join_codes_session_id` ON `join_codes` (`session_id`);--> statement-breakpoint
CREATE INDEX `idx_join_codes_status` ON `join_codes` (`status`);--> statement-breakpoint
CREATE INDEX `idx_join_codes_created_by` ON `join_codes` (`created_by`);--> statement-breakpoint
CREATE INDEX `idx_participants_session_id` ON `participants` (`session_id`);--> statement-breakpoint
CREATE INDEX `idx_participants_join_code_id` ON `participants` (`join_code_id`);--> statement-breakpoint
CREATE INDEX `idx_participants_join_method` ON `participants` (`join_method`);--> statement-breakpoint
CREATE INDEX `idx_participants_is_active` ON `participants` (`is_active`);--> statement-breakpoint
CREATE INDEX `idx_session_dashboard_last_updated` ON `session_dashboard` (`last_updated`);--> statement-breakpoint
CREATE INDEX `idx_session_summaries_created_at` ON `session_summaries` (`created_at`);--> statement-breakpoint
CREATE INDEX `idx_status_events_participant_id` ON `status_events` (`participant_id`);--> statement-breakpoint
CREATE INDEX `idx_status_events_status` ON `status_events` (`status`);--> statement-breakpoint
CREATE INDEX `idx_status_events_triggered_at` ON `status_events` (`triggered_at`);--> statement-breakpoint
CREATE INDEX `idx_status_events_is_reset` ON `status_events` (`is_reset`);--> statement-breakpoint
CREATE INDEX `idx_users_role` ON `users` (`role`);--> statement-breakpoint
CREATE INDEX `idx_users_created_at` ON `users` (`created_at`);