CREATE TABLE `athlete_profile` (
	`user_id` varchar(36) NOT NULL,
	`date_of_birth` datetime,
	`gender` enum('female','male','nonbinary','undisclosed') NOT NULL DEFAULT 'undisclosed',
	`height_cm` int,
	`weight_kg` int,
	`primary_goal` text,
	`race_calendar` text,
	`status` enum('active','paused','cancelled') NOT NULL DEFAULT 'active',
	`joined_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `athlete_profile_user_id` PRIMARY KEY(`user_id`)
);
--> statement-breakpoint
CREATE TABLE `booking` (
	`id` varchar(36) NOT NULL,
	`session_id` varchar(36) NOT NULL,
	`user_id` varchar(36) NOT NULL,
	`status` enum('confirmed','waitlisted','cancelled','no_show','attended') NOT NULL DEFAULT 'confirmed',
	`booked_at` timestamp NOT NULL DEFAULT (now()),
	`cancelled_at` timestamp,
	`promoted_at` timestamp,
	CONSTRAINT `booking_id` PRIMARY KEY(`id`),
	CONSTRAINT `booking_session_user_uidx` UNIQUE(`session_id`,`user_id`)
);
--> statement-breakpoint
CREATE TABLE `class_session` (
	`id` varchar(36) NOT NULL,
	`template_id` varchar(36) NOT NULL,
	`coach_user_id` varchar(36) NOT NULL,
	`starts_at` timestamp NOT NULL,
	`ends_at` timestamp NOT NULL,
	`capacity` int NOT NULL,
	`waitlist_capacity` int NOT NULL DEFAULT 5,
	`status` enum('scheduled','cancelled','completed') NOT NULL DEFAULT 'scheduled',
	`notes` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `class_session_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `class_template` (
	`id` varchar(36) NOT NULL,
	`name` varchar(120) NOT NULL,
	`description` text,
	`type` enum('hyrox','strength','run','open') NOT NULL,
	`default_capacity` int NOT NULL DEFAULT 12,
	`default_duration_min` int NOT NULL DEFAULT 60,
	`archived` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `class_template_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `plan_assignment` (
	`id` varchar(36) NOT NULL,
	`user_id` varchar(36) NOT NULL,
	`plan_id` varchar(36) NOT NULL,
	`starts_on` datetime NOT NULL,
	`ends_on` datetime NOT NULL,
	`assigned_by` varchar(36) NOT NULL,
	`status` enum('active','completed','paused') NOT NULL DEFAULT 'active',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `plan_assignment_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `training_plan` (
	`id` varchar(36) NOT NULL,
	`name` varchar(160) NOT NULL,
	`description` text,
	`duration_weeks` int NOT NULL,
	`created_by` varchar(36) NOT NULL,
	`archived` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `training_plan_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `training_plan_block` (
	`id` varchar(36) NOT NULL,
	`plan_id` varchar(36) NOT NULL,
	`week_index` int NOT NULL,
	`focus` varchar(160),
	`notes` text,
	CONSTRAINT `training_plan_block_id` PRIMARY KEY(`id`),
	CONSTRAINT `training_plan_block_plan_week_uidx` UNIQUE(`plan_id`,`week_index`)
);
--> statement-breakpoint
CREATE TABLE `training_plan_session` (
	`id` varchar(36) NOT NULL,
	`block_id` varchar(36) NOT NULL,
	`day_index` int NOT NULL,
	`order_index` int NOT NULL DEFAULT 0,
	`type` enum('hyrox','strength','run','recovery','rest','race_sim') NOT NULL,
	`name` varchar(200) NOT NULL,
	`description` text,
	`target_duration_min` int,
	`target_distance_m` int,
	`recommended_class_template_id` varchar(36),
	CONSTRAINT `training_plan_session_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `user` (
	`id` varchar(36) NOT NULL,
	`email` varchar(254) NOT NULL,
	`email_verified` boolean NOT NULL DEFAULT false,
	`name` varchar(120),
	`image_url` varchar(512),
	`role` enum('athlete','coach','admin') NOT NULL DEFAULT 'athlete',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `user_id` PRIMARY KEY(`id`),
	CONSTRAINT `user_email_uidx` UNIQUE(`email`)
);
--> statement-breakpoint
ALTER TABLE `athlete_profile` ADD CONSTRAINT `athlete_profile_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `booking` ADD CONSTRAINT `booking_session_id_class_session_id_fk` FOREIGN KEY (`session_id`) REFERENCES `class_session`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `booking` ADD CONSTRAINT `booking_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `class_session` ADD CONSTRAINT `class_session_template_id_class_template_id_fk` FOREIGN KEY (`template_id`) REFERENCES `class_template`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `class_session` ADD CONSTRAINT `class_session_coach_user_id_user_id_fk` FOREIGN KEY (`coach_user_id`) REFERENCES `user`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `plan_assignment` ADD CONSTRAINT `plan_assignment_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `plan_assignment` ADD CONSTRAINT `plan_assignment_plan_id_training_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `training_plan`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `plan_assignment` ADD CONSTRAINT `plan_assignment_assigned_by_user_id_fk` FOREIGN KEY (`assigned_by`) REFERENCES `user`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `training_plan` ADD CONSTRAINT `training_plan_created_by_user_id_fk` FOREIGN KEY (`created_by`) REFERENCES `user`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `training_plan_block` ADD CONSTRAINT `training_plan_block_plan_id_training_plan_id_fk` FOREIGN KEY (`plan_id`) REFERENCES `training_plan`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `training_plan_session` ADD CONSTRAINT `training_plan_session_block_id_training_plan_block_id_fk` FOREIGN KEY (`block_id`) REFERENCES `training_plan_block`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `training_plan_session` ADD CONSTRAINT `tps_rec_class_template_fk` FOREIGN KEY (`recommended_class_template_id`) REFERENCES `class_template`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `booking_user_booked_at_idx` ON `booking` (`user_id`,`booked_at`);--> statement-breakpoint
CREATE INDEX `booking_session_status_idx` ON `booking` (`session_id`,`status`);--> statement-breakpoint
CREATE INDEX `class_session_starts_at_idx` ON `class_session` (`starts_at`);--> statement-breakpoint
CREATE INDEX `class_session_status_starts_idx` ON `class_session` (`status`,`starts_at`);--> statement-breakpoint
CREATE INDEX `plan_assignment_user_status_idx` ON `plan_assignment` (`user_id`,`status`);--> statement-breakpoint
CREATE INDEX `training_plan_session_block_day_order_idx` ON `training_plan_session` (`block_id`,`day_index`,`order_index`);