<?php
require_once '../includes/db.php';

try {
    $pdo->beginTransaction();
    
    echo "Creating extended sport management tables...\n";
    
    // 1) Create figure_sport_details table
    $createDetailsTable = "
    CREATE TABLE IF NOT EXISTS figure_sport_details (
        sport_id INT NOT NULL PRIMARY KEY,
        slug VARCHAR(60) UNIQUE NOT NULL,
        short_name VARCHAR(40),
        aliases JSON,
        description TEXT,
        
        family ENUM('bike','ski','board','parkour','surf','dance','other') NOT NULL,
        environment SET('snow','water','street','park','indoor','outdoor') NOT NULL,
        
        team_mode ENUM('individual','team','both') DEFAULT 'individual',
        judged TINYINT(1) DEFAULT 1,
        time_based TINYINT(1) DEFAULT 0,
        distance_based TINYINT(1) DEFAULT 0,
        primary_metric ENUM('score','time','distance','height') DEFAULT 'score',
        
        default_runs_per_heat TINYINT DEFAULT 2,
        default_best_of_rule ENUM('best','best_n_sum','average','sum_all') DEFAULT 'best',
        default_judges TINYINT DEFAULT 5,
        
        course_features JSON,
        course_notes TEXT,
        required_equipment JSON,
        protective_gear_required JSON,
        risk_level ENUM('low','medium','high') DEFAULT 'high',
        
        min_age SMALLINT NULL,
        max_age SMALLINT NULL,
        supported_genders SET('M','F','Open','X') DEFAULT 'Open',
        
        sanctioning_bodies JSON,
        rulebook_url VARCHAR(255),
        
        brand_color_hex CHAR(7),
        icon_svg MEDIUMTEXT,
        tags JSON,
        
        name_translations JSON,
        seo_title VARCHAR(150),
        seo_description VARCHAR(255),
        
        display_order INT DEFAULT 0,
        
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        CONSTRAINT fk_fig_details_sport
            FOREIGN KEY (sport_id) REFERENCES figure_sports(id)
            ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ";
    
    $pdo->exec($createDetailsTable);
    echo "✓ Created figure_sport_details table\n";
    
    // 2) Create figure_sport_formats table
    $createFormatsTable = "
    CREATE TABLE IF NOT EXISTS figure_sport_formats (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sport_id INT NOT NULL,
        name VARCHAR(60) NOT NULL,
        judged TINYINT(1) DEFAULT 1,
        time_based TINYINT(1) DEFAULT 0,
        distance_based TINYINT(1) DEFAULT 0,
        primary_metric ENUM('score','time','distance','height') DEFAULT 'score',
        runs_per_heat TINYINT DEFAULT 2,
        best_of_rule ENUM('best','best_n_sum','average','sum_all') DEFAULT 'best',
        default_judges TINYINT DEFAULT 5,
        
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        UNIQUE (sport_id, name),
        CONSTRAINT fk_fig_formats_sport
            FOREIGN KEY (sport_id) REFERENCES figure_sports(id)
            ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ";
    
    $pdo->exec($createFormatsTable);
    echo "✓ Created figure_sport_formats table\n";
    
    // 3) Get existing sports and create default details for them
    $existingSports = $pdo->query("SELECT id, sport_name FROM figure_sports ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($existingSports)) {
        echo "\nCreating default details for existing sports...\n";
        
        $insertDetails = $pdo->prepare("
            INSERT IGNORE INTO figure_sport_details 
            (sport_id, slug, short_name, description, family, environment, team_mode, judged, 
             primary_metric, default_runs_per_heat, default_best_of_rule, default_judges, 
             risk_level, supported_genders, brand_color_hex, seo_title, seo_description, display_order)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $defaultMappings = [
            'snowboard' => [
                'family' => 'board', 'environment' => 'snow,park,outdoor', 'color' => '#4B7BFF',
                'description' => 'Snowboard freestyle park and halfpipe disciplines'
            ],
            'freeski' => [
                'family' => 'ski', 'environment' => 'snow,park,outdoor', 'color' => '#00D4FF',
                'description' => 'Freeski slopestyle and halfpipe disciplines'
            ],
            'skateboard' => [
                'family' => 'board', 'environment' => 'street,park,indoor,outdoor', 'color' => '#9FB0C9',
                'description' => 'Skateboard street and park disciplines'
            ],
            'bmx' => [
                'family' => 'bike', 'environment' => 'park,indoor,outdoor', 'color' => '#39FF14',
                'description' => 'BMX freestyle park and street disciplines'
            ],
            'inline' => [
                'family' => 'board', 'environment' => 'park,street,indoor,outdoor', 'color' => '#EAF2FF',
                'description' => 'Inline skating park and street disciplines'
            ],
            'surf' => [
                'family' => 'surf', 'environment' => 'water,outdoor', 'color' => '#0099CC',
                'description' => 'Surfing competitions on various wave conditions'
            ],
            'kitesurf' => [
                'family' => 'surf', 'environment' => 'water,outdoor', 'color' => '#AD26FF',
                'description' => 'Kitesurfing big air and freestyle disciplines'
            ],
            'parkour' => [
                'family' => 'parkour', 'environment' => 'street,park,indoor,outdoor', 'color' => '#FF6B35',
                'description' => 'Parkour freestyle and speed disciplines'
            ],
            'breaking' => [
                'family' => 'dance', 'environment' => 'indoor,street', 'color' => '#9FB0C9',
                'description' => 'Breaking battles judged on multiple criteria'
            ],
            'wakeboard' => [
                'family' => 'surf', 'environment' => 'water,outdoor', 'color' => '#39FF14',
                'description' => 'Wakeboard cable and boat disciplines'
            ]
        ];
        
        foreach ($existingSports as $sport) {
            $sportName = $sport['sport_name'];
            $mapping = $defaultMappings[$sportName] ?? [
                'family' => 'other', 'environment' => 'outdoor', 'color' => '#6C757D',
                'description' => ucfirst($sportName) . ' sport disciplines'
            ];
            
            $insertDetails->execute([
                $sport['id'],
                $sportName,
                ucfirst($sportName),
                $mapping['description'],
                $mapping['family'],
                $mapping['environment'],
                'individual',
                1, // judged
                'score', // primary_metric
                2, // default_runs_per_heat
                'best', // default_best_of_rule
                5, // default_judges
                'high', // risk_level
                'Open', // supported_genders
                $mapping['color'],
                ucfirst($sportName),
                $mapping['description'],
                $sport['id'] * 10 // display_order
            ]);
            
            echo "  ✓ Created details for: $sportName\n";
        }
    }
    
    // 4) Create some default formats for existing sports
    echo "\nCreating default formats for existing sports...\n";
    
    $defaultFormats = [
        'snowboard' => ['Slopestyle', 'Halfpipe', 'Big Air'],
        'freeski' => ['Slopestyle', 'Halfpipe', 'Big Air'],
        'skateboard' => ['Street', 'Park', 'Vert'],
        'bmx' => ['Park', 'Street', 'Flatland'],
        'inline' => ['Park', 'Street', 'Vert'],
        'surf' => ['Shortboard Heat', 'Longboard Heat'],
        'kitesurf' => ['Big Air', 'Freestyle'],
        'parkour' => ['Freestyle', 'Speed Run'],
        'breaking' => ['Battle'],
        'wakeboard' => ['Cable Park', 'Boat']
    ];
    
    $insertFormat = $pdo->prepare("
        INSERT IGNORE INTO figure_sport_formats 
        (sport_id, name, judged, time_based, distance_based, primary_metric, runs_per_heat, best_of_rule, default_judges)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    
    foreach ($existingSports as $sport) {
        $sportName = $sport['sport_name'];
        $formats = $defaultFormats[$sportName] ?? ['Standard'];
        
        foreach ($formats as $formatName) {
            // Special handling for time-based formats
            $isTimeBased = (strpos(strtolower($formatName), 'speed') !== false);
            $judged = !$isTimeBased;
            $primaryMetric = $isTimeBased ? 'time' : 'score';
            $runsPerHeat = $isTimeBased ? 1 : 2;
            $bestOfRule = $isTimeBased ? 'sum_all' : 'best';
            $defaultJudges = $isTimeBased ? 0 : 5;
            
            // Special handling for battle formats
            if (strpos(strtolower($formatName), 'battle') !== false) {
                $runsPerHeat = 1;
                $bestOfRule = 'sum_all';
            }
            
            $insertFormat->execute([
                $sport['id'],
                $formatName,
                $judged,
                $isTimeBased,
                0, // distance_based
                $primaryMetric,
                $runsPerHeat,
                $bestOfRule,
                $defaultJudges
            ]);
        }
        
        echo "  ✓ Created formats for: $sportName\n";
    }
    
    $pdo->commit();
    echo "\n✅ Successfully created extended sport management system!\n";
    echo "\nNext steps:\n";
    echo "1. Update figures_management.php to include sport editing panel\n";
    echo "2. Test the new sport details and formats functionality\n";
    
} catch (PDOException $e) {
    $pdo->rollback();
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
}
?>
