-- Setup für Match-API Polling in bestehende NexusControl DB
-- Erweitert das bestehende Schema um Match-Daten

-- Matches Tabelle (falls nicht vorhanden)
CREATE TABLE IF NOT EXISTS matches (
    match_id VARCHAR(50) PRIMARY KEY,
    map_name VARCHAR(100) NOT NULL,
    duration_seconds INTEGER NOT NULL DEFAULT 0,
    started_at TIMESTAMP NULL,
    ended_at TIMESTAMP NULL,
    match_type ENUM('ranked', 'custom', 'tournament') DEFAULT 'ranked',
    has_replay BOOLEAN DEFAULT FALSE,
    replay_url VARCHAR(500) NULL,
    api_source VARCHAR(50) DEFAULT 'cnc_community',  -- Quelle der Daten
    raw_data JSON NULL,  -- Original API Response
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_matches_started (started_at),
    INDEX idx_matches_type (match_type),
    INDEX idx_matches_source (api_source)
);

-- Spieler Tabelle erweitern (falls vorhanden)
CREATE TABLE IF NOT EXISTS players (
    player_id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    api_source VARCHAR(50) DEFAULT 'cnc_community',
    first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_players_name (name),
    INDEX idx_players_source (api_source),
    INDEX idx_players_last_seen (last_seen_at)
);

-- Match-Zuordnung (falls nicht vorhanden)
CREATE TABLE IF NOT EXISTS match_players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    faction ENUM('Allies', 'Soviet', 'Unknown') DEFAULT 'Unknown',
    result ENUM('win', 'loss', 'draw', 'unknown') DEFAULT 'unknown',
    final_score INTEGER NULL,
    resources_collected INTEGER NULL,
    api_source VARCHAR(50) DEFAULT 'cnc_community',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (match_id) REFERENCES matches(match_id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE,
    UNIQUE KEY unique_match_player (match_id, player_id),
    
    INDEX idx_match_players_player (player_id),
    INDEX idx_match_players_result (result),
    INDEX idx_match_players_faction (faction)
);

-- Polling-Status Tabelle
CREATE TABLE IF NOT EXISTS polling_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_source VARCHAR(50) NOT NULL,
    game_type VARCHAR(50) NOT NULL,  -- tiberian-dawn, red-alert
    last_successful_poll TIMESTAMP NULL,
    last_poll_attempt TIMESTAMP NULL,
    total_matches_found INTEGER DEFAULT 0,
    total_matches_saved INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    last_error TEXT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_source_game (api_source, game_type)
);

-- Initiale Polling-Status Einträge
INSERT IGNORE INTO polling_status (api_source, game_type) VALUES
('cnc_community', 'tiberian-dawn'),
('cnc_community', 'red-alert');

-- 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;

-- Berechtigungen für den Poller (falls benötigt)
-- GRANT SELECT, INSERT, UPDATE ON u750951906_NexusControl.matches TO 'u750951906_admin'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON u750951906_NexusControl.players TO 'u750951906_admin'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON u750951906_NexusControl.match_players TO 'u750951906_admin'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON u750951906_NexusControl.polling_status TO 'u750951906_admin'@'localhost';

-- Setup abgeschlossen
