<?php
// Setup sample database for TV overlay demo
try {
    require_once '../includes/db.php';
    echo "<h3>Setting up StyleScore TV Overlay Database</h3>";
    
    // Create events table
    $events_table = "
    CREATE TABLE IF NOT EXISTS events (
        event_id INT AUTO_INCREMENT PRIMARY KEY,
        event_name VARCHAR(255) NOT NULL,
        discipline VARCHAR(100),
        event_date DATE,
        location VARCHAR(255),
        event_status ENUM('draft', 'active', 'completed') DEFAULT 'draft',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($events_table);
    echo "✓ Events table ready<br>";
    
    // Create participants table
    $participants_table = "
    CREATE TABLE IF NOT EXISTS participants (
        participant_id INT AUTO_INCREMENT PRIMARY KEY,
        event_id INT,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        country VARCHAR(3),
        bib_number INT,
        category VARCHAR(50),
        start_position INT,
        total_score DECIMAL(6,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (event_id) REFERENCES events(event_id)
    )";
    $pdo->exec($participants_table);
    echo "✓ Participants table ready<br>";
    
    // Create scores table
    $scores_table = "
    CREATE TABLE IF NOT EXISTS scores (
        score_id INT AUTO_INCREMENT PRIMARY KEY,
        event_id INT,
        participant_id INT,
        run_number INT DEFAULT 1,
        total_score DECIMAL(6,2),
        rank_position INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (event_id) REFERENCES events(event_id),
        FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
    )";
    $pdo->exec($scores_table);
    echo "✓ Scores table ready<br>";
    
    // Check if we need sample data
    $event_count = $pdo->query("SELECT COUNT(*) FROM events")->fetchColumn();
    if ($event_count == 0) {
        // Insert sample event
        $insert_event = "
        INSERT INTO events (event_name, discipline, event_date, location, event_status) 
        VALUES ('Freestyle World Cup 2025', 'freestyle', CURDATE(), 'Whistler, Canada', 'active')";
        $pdo->exec($insert_event);
        $event_id = $pdo->lastInsertId();
        echo "✓ Sample event created (ID: $event_id)<br>";
        
        // Insert sample participants
        $participants = [
            ['John', 'Smith', 'USA', 1, 'Men', 1],
            ['Emma', 'Johnson', 'CAN', 2, 'Women', 2],
            ['Yuki', 'Tanaka', 'JPN', 3, 'Men', 3],
            ['Sophie', 'Martin', 'FRA', 4, 'Women', 4],
            ['Lars', 'Andersson', 'SWE', 5, 'Men', 5],
            ['Maria', 'Rodriguez', 'ESP', 6, 'Women', 6]
        ];
        
        foreach ($participants as $p) {
            $insert_participant = "
            INSERT INTO participants (event_id, first_name, last_name, country, bib_number, category, start_position, total_score) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $pdo->prepare($insert_participant);
            $score = rand(750, 950) / 10; // Random score between 75.0 and 95.0
            $stmt->execute([$event_id, $p[0], $p[1], $p[2], $p[3], $p[4], $p[5], $score]);
        }
        echo "✓ Sample participants created<br>";
        
        // Insert sample scores
        $participants_result = $pdo->query("SELECT participant_id, total_score FROM participants WHERE event_id = $event_id ORDER BY total_score DESC");
        $rank = 1;
        while ($participant = $participants_result->fetch(PDO::FETCH_ASSOC)) {
            $insert_score = "
            INSERT INTO scores (event_id, participant_id, run_number, total_score, rank_position) 
            VALUES (?, ?, 1, ?, ?)";
            $stmt = $pdo->prepare($insert_score);
            $stmt->execute([$event_id, $participant['participant_id'], $participant['total_score'], $rank]);
            $rank++;
        }
        echo "✓ Sample scores created<br>";
    }
    
    echo "<br><strong>Database setup complete!</strong><br>";
    echo "<a href='index.php'>→ Open TV Overlay Control Panel</a>";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>
