-- Test-Abfragen für die Views

-- 1. Alle Matches mit Spieler-Namen
SELECT 
    m.match_id,
    m.map_name,
    m.duration_seconds,
    m.started_at,
    m.match_type,
    p1.name as player1_name,
    mp1.faction as player1_faction,
    mp1.result as player1_result,
    p2.name as player2_name,
    mp2.faction as player2_faction,
    mp2.result as player2_result,
    m.has_replay,
    m.api_source
FROM matches m
LEFT JOIN match_players mp1 ON m.match_id = mp1.match_id 
LEFT JOIN cnc_players p1 ON mp1.player_id = p1.player_id
LEFT JOIN match_players mp2 ON m.match_id = mp2.match_id AND mp2.player_id > mp1.player_id
LEFT JOIN cnc_players p2 ON mp2.player_id = p2.player_id
WHERE m.started_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY m.started_at DESC;

-- 2. Spieler-Statistiken
SELECT 
    p.player_id,
    p.name,
    COUNT(mp.match_id) as total_matches,
    SUM(CASE WHEN mp.result = 'win' THEN 1 ELSE 0 END) as wins,
    SUM(CASE WHEN mp.result = 'loss' THEN 1 ELSE 0 END) as losses,
    SUM(CASE WHEN mp.result = 'draw' THEN 1 ELSE 0 END) as draws,
    ROUND(SUM(CASE WHEN mp.result = 'win' THEN 1 ELSE 0 END) / COUNT(mp.match_id) * 100, 2) as winrate,
    AVG(m.duration_seconds) as avg_duration,
    MAX(m.started_at) as last_match,
    p.api_source
FROM cnc_players p
LEFT JOIN match_players mp ON p.player_id = mp.player_id
LEFT JOIN matches m ON mp.match_id = m.match_id
GROUP BY p.player_id, p.name, p.api_source
ORDER BY wins DESC, total_matches DESC;

-- 3. Einfache Test-Abfrage: Sind da Daten?
SELECT COUNT(*) as total_matches FROM matches;
SELECT COUNT(*) as total_players FROM cnc_players;
SELECT COUNT(*) as total_match_players FROM match_players;
