-- Schritt 2: Views erstellen (nachdem Tabellen existieren)

-- View für aktuelle Matches
CREATE OR REPLACE VIEW recent_matches AS
SELECT 
    m.match_id,
    m.map_name,
    m.duration_seconds,
    m.started_at,
    m.match_type,
    pl1.name as player1_name,
    mp1.faction as player1_faction,
    mp1.result as player1_result,
    pl2.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 players pl1 ON mp1.player_id = pl1.player_id
LEFT JOIN match_players mp2 ON m.match_id = mp2.match_id AND mp2.player_id > mp1.player_id
LEFT JOIN players pl2 ON mp2.player_id = pl2.player_id
WHERE m.started_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY m.started_at DESC;

-- View für Spieler-Statistiken
CREATE OR REPLACE VIEW player_stats AS
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 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;
