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

try {
    $pdo->beginTransaction();
    
    // Check if columns exist and add them if they don't
    $columns = [
        'required_equipment' => 'JSON NULL',
        'protective_gear_required' => 'JSON NULL'
    ];
    
    foreach ($columns as $columnName => $columnDef) {
        // Check if column exists
        $checkColumn = $pdo->query("SHOW COLUMNS FROM figure_sport_details LIKE '$columnName'");
        if ($checkColumn->rowCount() == 0) {
            // Column doesn't exist, add it
            $pdo->exec("ALTER TABLE figure_sport_details ADD COLUMN $columnName $columnDef");
            echo "Added column: $columnName\n";
        } else {
            echo "Column already exists: $columnName\n";
        }
    }
    
    // Update sports with equipment data
    $equipmentData = [
        'snowboard' => [
            'required_equipment' => ['board','bindings','boots','helmet'],
            'protective_gear_required' => ['helmet','back_protector']
        ],
        'freeski' => [
            'required_equipment' => ['skis','bindings','boots','helmet'],
            'protective_gear_required' => ['helmet','back_protector']
        ],
        'skateboard' => [
            'required_equipment' => ['skateboard','helmet'],
            'protective_gear_required' => ['helmet','knee_pads']
        ],
        'bmx' => [
            'required_equipment' => ['bike','helmet'],
            'protective_gear_required' => ['helmet','knee_pads']
        ],
        'inline' => [
            'required_equipment' => ['inline_skates','helmet'],
            'protective_gear_required' => ['helmet','wrist_guards']
        ],
        'surf' => [
            'required_equipment' => ['surfboard','leash'],
            'protective_gear_required' => ['impact_vest']
        ],
        'kitesurf' => [
            'required_equipment' => ['twin_tip','kite','bar','harness','helmet'],
            'protective_gear_required' => ['helmet','impact_vest']
        ],
        'parkour' => [
            'required_equipment' => ['shoes'],
            'protective_gear_required' => ['helmet']
        ],
        'wakeboard' => [
            'required_equipment' => ['wakeboard','life_vest','helmet'],
            'protective_gear_required' => ['helmet','impact_vest']
        ]
    ];
    
    foreach ($equipmentData as $sportName => $equipment) {
        // Get sport ID
        $sportQuery = $pdo->prepare("SELECT id FROM figure_sports WHERE sport_name = ?");
        $sportQuery->execute([$sportName]);
        $sport = $sportQuery->fetch();
        
        if ($sport) {
            // Update or insert equipment data
            $updateEquipment = $pdo->prepare("
                INSERT INTO figure_sport_details (sport_id, required_equipment, protective_gear_required)
                VALUES (?, ?, ?)
                ON DUPLICATE KEY UPDATE
                required_equipment = VALUES(required_equipment),
                protective_gear_required = VALUES(protective_gear_required)
            ");
            
            $updateEquipment->execute([
                $sport['id'],
                json_encode($equipment['required_equipment']),
                json_encode($equipment['protective_gear_required'])
            ]);
            
            echo "Updated equipment data for: $sportName\n";
        } else {
            echo "Sport not found: $sportName\n";
        }
    }
    
    $pdo->commit();
    echo "Database updated successfully!\n";
    
} catch (PDOException $e) {
    $pdo->rollback();
    echo "Error updating database: " . $e->getMessage() . "\n";
}
?>
