-- C&C Remastered Unit Statistics Database Schema
-- Kombiniert Match-API und Replay-Parsing Daten

-- Grundlegende Tabellen
CREATE TABLE IF NOT EXISTS players (
    player_id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS matches (
    match_id VARCHAR(50) PRIMARY KEY,
    map_name VARCHAR(100) NOT NULL,
    duration_seconds INTEGER NOT NULL,
    started_at TIMESTAMP NOT NULL,
    ended_at TIMESTAMP NOT NULL,
    match_type ENUM('ranked', 'custom', 'tournament') DEFAULT 'custom',
    has_replay BOOLEAN DEFAULT FALSE,
    replay_url VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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') NOT NULL,
    result ENUM('win', 'loss', 'draw') NOT NULL,
    resources_api INTEGER NULL,  -- Von Match-API (falls vorhanden)
    resources_replay INTEGER NULL,  -- Von Replay-Parsing
    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)
);

-- Unit-Typen (Mapping von internen IDs zu lesbaren Namen)
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,  -- Wann diese Unit zuerst gebaut wurde
    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,
    FOREIGN KEY (unit_type_id) REFERENCES unit_types(unit_type_id) ON DELETE CASCADE,
    UNIQUE KEY unique_match_player_unit (match_id, player_id, unit_type_id)
);

-- Optionale: Zeitreihen-Daten für detaillierte Analyse
CREATE TABLE IF NOT EXISTS unit_timeline (
    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,
    event_type ENUM('built', 'lost') NOT NULL,
    second INTEGER NOT NULL,  -- Sekunde seit Spielstart
    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,
    FOREIGN KEY (unit_type_id) REFERENCES unit_types(unit_type_id) ON DELETE CASCADE
);

-- Indizes für Performance
CREATE INDEX idx_unit_stats_match ON unit_stats(match_id);
CREATE INDEX idx_unit_stats_player ON unit_stats(player_id);
CREATE INDEX idx_unit_stats_unit ON unit_stats(unit_type_id);
CREATE INDEX idx_unit_timeline_match ON unit_timeline(match_id);
CREATE INDEX idx_unit_timeline_player ON unit_timeline(player_id);
CREATE INDEX idx_match_players_match ON match_players(match_id);
CREATE INDEX idx_match_players_player ON match_players(player_id);

-- View für einfache Abfragen
CREATE VIEW unit_summary AS
SELECT 
    mp.match_id,
    mp.player_id,
    p.name as player_name,
    mp.faction,
    mp.result,
    ut.display_name as unit_name,
    ut.category as unit_category,
    us.built_count,
    us.lost_count,
    us.first_built_second,
    m.duration_seconds,
    m.map_name
FROM match_players mp
JOIN players p ON mp.player_id = p.player_id
JOIN matches m ON mp.match_id = m.match_id
LEFT JOIN unit_stats us ON mp.match_id = us.match_id AND mp.player_id = us.player_id
LEFT JOIN unit_types ut ON us.unit_type_id = ut.unit_type_id;

-- Beispiel-Abfragen:
-- 
-- Top 10 meistgebaute Units:
-- SELECT unit_name, SUM(built_count) as total_built 
-- FROM unit_summary 
-- WHERE built_count > 0 
-- GROUP BY unit_name 
-- ORDER BY total_built DESC LIMIT 10;
--
-- Winrate nach primärer Unit-Kategorie:
-- SELECT 
--     unit_category,
--     COUNT(*) as matches,
--     SUM(CASE WHEN result = 'win' THEN 1 ELSE 0 END) as wins,
--     SUM(CASE WHEN result = 'loss' THEN 1 ELSE 0 END) as losses,
--     ROUND(SUM(CASE WHEN result = 'win' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as winrate
-- FROM unit_summary 
-- WHERE built_count > 0 
-- GROUP BY unit_category;
--
-- Durchschnittliche Build-Times:
-- SELECT 
--     unit_name,
--     AVG(first_built_second) as avg_first_built,
--     MIN(first_built_second) as fastest_built
-- FROM unit_summary 
-- WHERE first_built_second IS NOT NULL 
-- GROUP BY unit_name 
-- ORDER BY avg_first_built;
