-- Setup für Match-API Polling in bestehende NexusControl DB
-- Angepasst an die existierende Datenbankstruktur

-- 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',
    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 (angepasst an existierende Struktur)
CREATE TABLE IF NOT EXISTS cnc_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_cnc_players_name (name),
    INDEX idx_cnc_players_source (api_source),
    INDEX idx_cnc_players_last_seen (last_seen_at)
);

-- Match-Zuordnung (angepasst für Kompatibilität)
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,
    
    INDEX idx_match_players_match (match_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)
);

-- Unit-Typen Tabelle für Replay-Parsing
CREATE TABLE IF NOT EXISTS unit_types (
    unit_type_id VARCHAR(50) PRIMARY KEY,
    display_name VARCHAR(100) NOT NULL,
    faction ENUM('Allies', 'Soviet', 'Neutral') NOT NULL,
    category ENUM('infantry', 'vehicle', 'air', 'naval', 'building') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Unit-Statistiken pro Match/Spieler
CREATE TABLE IF NOT EXISTS unit_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    unit_type_id VARCHAR(50) NOT NULL,
    built_count INTEGER DEFAULT 0,
    lost_count INTEGER DEFAULT 0,
    first_built_second INTEGER NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_unit_stats_match (match_id),
    INDEX idx_unit_stats_player (player_id),
    INDEX idx_unit_stats_unit (unit_type_id)
);

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

-- Initiale Unit-Typen (wichtigste Units)
INSERT IGNORE INTO unit_types (unit_type_id, display_name, faction, category) VALUES
-- Allies Units
('rifle', 'Rifle Infantry', 'Allies', 'infantry'),
('medic', 'Medic', 'Allies', 'infantry'),
('engineer', 'Engineer', 'Allies', 'infantry'),
('light_tank', 'Light Tank', 'Allies', 'vehicle'),
('medium_tank', 'Medium Tank', 'Allies', 'vehicle'),
('apc', 'APC', 'Allies', 'vehicle'),
('artillery', 'Artillery', 'Allies', 'vehicle'),
('ore_truck', 'Ore Truck', 'Allies', 'vehicle'),
('ranger', 'Ranger', 'Allies', 'vehicle'),
('longbow', 'Longbow Helicopter', 'Allies', 'air'),
-- Soviet Units
('flamethrower', 'Flamethrower Infantry', 'Soviet', 'infantry'),
('grenadier', 'Grenadier', 'Soviet', 'infantry'),
('heavy_tank', 'Heavy Tank', 'Soviet', 'vehicle'),
('mammoth_tank', 'Mammoth Tank', 'Soviet', 'vehicle'),
('v2_rocket', 'V2 Rocket Launcher', 'Soviet', 'vehicle'),
('tesla_tank', 'Tesla Tank', 'Soviet', 'vehicle'),
('hind', 'Hind Helicopter', 'Soviet', 'air'),
('yak', 'Yak Aircraft', 'Soviet', 'air'),
('mig', 'MiG Aircraft', 'Soviet', 'air');

-- Views werden in separater Datei erstellt (setup_views.sql)
-- Nachdem alle Tabellen existieren
