-- Adds control_point_id to scores to track the control point where a judge entered a score
-- Run on your MySQL/MariaDB database connected to this app

-- 1) Add column (safe to run if it already exists on MySQL 8+; otherwise remove IF NOT EXISTS)
ALTER TABLE `scores`
  ADD COLUMN IF NOT EXISTS `control_point_id` INT NULL AFTER `judge_id`;

-- 2) Index for lookups by control point
CREATE INDEX IF NOT EXISTS `idx_scores_control_point_id` ON `scores`(`control_point_id`);

-- 3) Optional: enforce referential integrity if event_control_points table exists
-- Uncomment if your DB supports it and existing data is consistent
-- ALTER TABLE `scores`
--   ADD CONSTRAINT `fk_scores_control_point`
--   FOREIGN KEY (`control_point_id`) REFERENCES `event_control_points`(`id`)
--   ON UPDATE CASCADE ON DELETE SET NULL;

-- 4) (Optional) If you want uniqueness per run+judge (regardless of control point), ensure a unique key exists like:
-- ALTER TABLE `scores` ADD UNIQUE KEY `uq_scores_run_judge` (`run_id`, `judge_id`);
-- If you prefer uniqueness per run+judge+control point, use:
-- ALTER TABLE `scores` ADD UNIQUE KEY `uq_scores_run_judge_cp` (`run_id`, `judge_id`, `control_point_id`);
