-- Migration to add checkpoint and assignment tables, and rework the ranking_records table.

-- 1. Create stage_checkpoints table
CREATE TABLE `stage_checkpoints` (
  `id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_stage_checkpoints_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
);
CREATE INDEX `idx_stage_checkpoints_stage` ON `stage_checkpoints` (`stage_id`);

-- 2. Create checkpoint_assignments table
CREATE TABLE `checkpoint_assignments` (
  `id` char(36) NOT NULL,
  `checkpoint_id` char(36) NOT NULL,
  `volunteer_user_id` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE (`checkpoint_id`, `volunteer_user_id`),
  CONSTRAINT `fk_checkpoint_assignments_checkpoint` FOREIGN KEY (`checkpoint_id`) REFERENCES `stage_checkpoints` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_checkpoint_assignments_user` FOREIGN KEY (`volunteer_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE INDEX `idx_checkpoint_assignments_checkpoint` ON `checkpoint_assignments` (`checkpoint_id`);
CREATE INDEX `idx_checkpoint_assignments_user` ON `checkpoint_assignments` (`volunteer_user_id`);


-- 3. Drop old ranking tables
-- 3. Drop old ranking_checkins table (if it exists)
DROP TABLE IF EXISTS `ranking_checkins`;

-- Note: The ranking_records table is now defined in the main init.sql
-- to support the AI/camera based workflow. This migration will no longer
-- drop and recreate it, to avoid conflicts.
