-- Add status and weight columns to event_control_points table
-- Run this SQL script to add the new fields needed for control point management

-- Add status column (1 = active, 0 = inactive)
ALTER TABLE event_control_points 
ADD COLUMN status TINYINT(1) DEFAULT 1 COMMENT 'Control point status: 1=active, 0=inactive';

-- Add weight column for score calculation
ALTER TABLE event_control_points 
ADD COLUMN weight DECIMAL(3,2) DEFAULT 1.00 COMMENT 'Weight multiplier for score calculation (0.00-2.00)';

-- Update existing records to have default values
UPDATE event_control_points 
SET status = 1, weight = 1.00 
WHERE status IS NULL OR weight IS NULL;

-- Add indexes for better performance
CREATE INDEX idx_control_points_status ON event_control_points(status);
CREATE INDEX idx_control_points_weight ON event_control_points(weight);

-- Add check constraints to ensure valid values
ALTER TABLE event_control_points 
ADD CONSTRAINT chk_control_point_status CHECK (status IN (0, 1));

ALTER TABLE event_control_points 
ADD CONSTRAINT chk_control_point_weight CHECK (weight >= 0.00 AND weight <= 2.00);
