-- StyleScore Scoring Formats Database Migration
-- This creates tables to store scoring format presets, diversity rules, and figure dictionaries

-- Main scoring formats table
CREATE TABLE IF NOT EXISTS scoring_formats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    sport VARCHAR(100) NOT NULL,
    mode VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Judge configuration for each format
CREATE TABLE IF NOT EXISTS scoring_format_judges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    min_judges INT NOT NULL DEFAULT 5,
    max_judges INT DEFAULT NULL,
    scale_min DECIMAL(5,2) DEFAULT 0,
    scale_max DECIMAL(5,2) DEFAULT 100,
    scale_type VARCHAR(50) DEFAULT 'numeric', -- numeric, comparative, custom
    scale_custom TEXT DEFAULT NULL, -- for custom scale descriptions
    precision_decimal INT DEFAULT 0, -- 0 for whole numbers, 1 for decimal places
    drop_rule VARCHAR(100) DEFAULT 'none', -- none, drop_high_low, drop_components, etc.
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Mode-specific configuration (sections, attempts, runs, etc.)
CREATE TABLE IF NOT EXISTS scoring_format_mode_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    config_type VARCHAR(50) NOT NULL, -- sections, attempts, runs, tricks, oi
    config_key VARCHAR(100) NOT NULL, -- count, weight, max, bestN, perRound
    config_value VARCHAR(255) NOT NULL,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE,
    UNIQUE KEY unique_config (format_id, config_type, config_key)
);

-- Round configuration for each format
CREATE TABLE IF NOT EXISTS scoring_format_rounds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    round_name VARCHAR(100) NOT NULL, -- qualifying, final, heat, battle
    runs INT DEFAULT 0,
    aggregation VARCHAR(255) NOT NULL, -- best, best run, average of 2, etc.
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Criteria for each format (sections, oi, attempt, E, D, comparative)
CREATE TABLE IF NOT EXISTS scoring_format_criteria (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    criteria_type VARCHAR(50) NOT NULL, -- sections, oi, attempt, E, D, comparative
    criteria_name VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Results table headers for each format
CREATE TABLE IF NOT EXISTS scoring_format_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    header_name VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Flow configuration (heat progression)
CREATE TABLE IF NOT EXISTS scoring_format_flow (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    flow_stage VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Tiebreak rules for each format
CREATE TABLE IF NOT EXISTS scoring_format_tiebreak (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    tiebreak_rule VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Diversity rules for formats that require them
CREATE TABLE IF NOT EXISTS scoring_format_diversity_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    format_id VARCHAR(100) NOT NULL,
    rule_type VARCHAR(100) NOT NULL, -- requireDistinct, tricksDistinctGroups, anyPass
    scope VARCHAR(100) DEFAULT NULL, -- attempts, tricks
    group_name VARCHAR(100) DEFAULT NULL, -- Direction, Rotation, Jump, etc.
    min_distinct INT DEFAULT NULL,
    is_optional BOOLEAN DEFAULT 0,
    FOREIGN KEY (format_id) REFERENCES scoring_formats(format_id) ON DELETE CASCADE
);

-- Sports and their figure categories
CREATE TABLE IF NOT EXISTS figure_sports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_name VARCHAR(100) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Figure categories for each sport (Rotation, Direction, Grab, etc.)
CREATE TABLE IF NOT EXISTS figure_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_name VARCHAR(100) NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (sport_name) REFERENCES figure_sports(sport_name) ON DELETE CASCADE,
    UNIQUE KEY unique_sport_category (sport_name, category_name)
);

-- Individual figures within each category
CREATE TABLE IF NOT EXISTS figure_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_name VARCHAR(100) NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (sport_name) REFERENCES figure_sports(sport_name) ON DELETE CASCADE,
    INDEX idx_sport_category (sport_name, category_name)
);

-- Insert default sports
INSERT IGNORE INTO figure_sports (sport_name) VALUES 
('snowboard'), ('freeski'), ('skateboard'), ('bmx'), ('inline'), 
('surf'), ('kitesurf'), ('parkour'), ('breaking'), ('wakeboard');
