-- Migration to add format-related settings to event_heat_settings table
-- This adds comprehensive format configuration options to individual heats

-- Add format-related columns to event_heat_settings table
ALTER TABLE event_heat_settings 
ADD COLUMN format_name VARCHAR(255) DEFAULT NULL COMMENT 'Heat-specific format name override',
ADD COLUMN discipline VARCHAR(100) DEFAULT NULL COMMENT 'Sport discipline for this heat',
ADD COLUMN difficulty_level VARCHAR(50) DEFAULT NULL COMMENT 'Difficulty level (beginner, intermediate, advanced, professional, elite)',
ADD COLUMN course_length INT DEFAULT 0 COMMENT 'Course length in meters (0 = not applicable)',
ADD COLUMN time_limit INT DEFAULT 0 COMMENT 'Time limit per run in seconds (0 = no limit)',
ADD COLUMN weather_dependent BOOLEAN DEFAULT 0 COMMENT 'Whether heat is weather dependent',
ADD COLUMN format_description TEXT DEFAULT NULL COMMENT 'Detailed format description and rules',
ADD COLUMN scoring_method VARCHAR(100) DEFAULT 'standard' COMMENT 'Scoring method (standard, comparative, elimination, head_to_head, battle)',
ADD COLUMN required_figures JSON DEFAULT NULL COMMENT 'JSON array of required figures for this heat',
ADD COLUMN judges_required INT DEFAULT 5 COMMENT 'Number of judges required for this heat',
ADD COLUMN scale_min DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Minimum score value',
ADD COLUMN scale_max DECIMAL(5,2) DEFAULT 100.00 COMMENT 'Maximum score value',
ADD COLUMN precision_decimal INT DEFAULT 0 COMMENT 'Number of decimal places for scores (0, 1, or 2)';

-- Add indexes for better performance
CREATE INDEX idx_event_heat_discipline ON event_heat_settings(discipline);
CREATE INDEX idx_event_heat_difficulty ON event_heat_settings(difficulty_level);
CREATE INDEX idx_event_heat_scoring ON event_heat_settings(scoring_method);
CREATE INDEX idx_event_heat_weather ON event_heat_settings(weather_dependent);

-- Update table comment for documentation
ALTER TABLE event_heat_settings COMMENT = 'Heat-specific configuration including format settings, flow rules, and scoring parameters';

-- Sample data update (optional - can be uncommented if needed)
-- UPDATE event_heat_settings SET 
--   format_name = (SELECT sf.name FROM events e LEFT JOIN scoring_formats sf ON e.scoring_format = sf.format_id WHERE e.id = event_heat_settings.event_id),
--   discipline = (SELECT sf.sport FROM events e LEFT JOIN scoring_formats sf ON e.scoring_format = sf.format_id WHERE e.id = event_heat_settings.event_id),
--   scale_min = (SELECT sfj.scale_min FROM events e LEFT JOIN scoring_format_judges sfj ON e.scoring_format = sfj.format_id WHERE e.id = event_heat_settings.event_id),
--   scale_max = (SELECT sfj.scale_max FROM events e LEFT JOIN scoring_format_judges sfj ON e.scoring_format = sfj.format_id WHERE e.id = event_heat_settings.event_id),
--   precision_decimal = (SELECT sfj.precision_decimal FROM events e LEFT JOIN scoring_format_judges sfj ON e.scoring_format = sfj.format_id WHERE e.id = event_heat_settings.event_id)
-- WHERE format_name IS NULL;
