-- Schritt 1: Nur Tabellen erstellen (ohne Views)

-- Matches Tabelle
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',
    raw_data JSON NULL,
    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
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
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,
    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');
