-- ============================================
-- Liga-Spielplan System für NexusControl
-- Erstellt: 2024-12-30
-- ============================================
-- ANLEITUNG: 
-- 1. In phpMyAdmin die richtige Datenbank auswählen
-- 2. Diese Datei importieren oder SQL ausführen
-- 
-- HINWEIS: Tabellen haben Präfix "lsp_" um Konflikte mit 
-- bestehender "leagues"-Tabelle zu vermeiden!

-- ============================================
-- SCHRITT 1: Alte Tabellen löschen (falls vorhanden)
-- ============================================
DROP TABLE IF EXISTS lsp_match_log;
DROP TABLE IF EXISTS lsp_match_images;
DROP TABLE IF EXISTS lsp_matches;
DROP TABLE IF EXISTS lsp_participants;
DROP TABLE IF EXISTS lsp_leagues;
DROP TABLE IF EXISTS lsp_seasons;

-- ============================================
-- SCHRITT 2: Tabellen erstellen (OHNE Foreign Keys)
-- ============================================

-- Seasons (z.B. "Season 9")
CREATE TABLE lsp_seasons (
    season_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    beschreibung TEXT,
    start_date DATE,
    end_date DATE,
    status ENUM('geplant', 'aktiv', 'beendet', 'archiviert') DEFAULT 'geplant',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Leagues (z.B. "Liga 1", "Liga 2", ...)
CREATE TABLE lsp_leagues (
    league_id INT PRIMARY KEY AUTO_INCREMENT,
    season_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    spiel_id INT,
    spieltyp ENUM('Einzel', 'Team') DEFAULT 'Einzel',
    beschreibung TEXT,
    logo VARCHAR(255),
    punkte_sieg INT DEFAULT 3,
    punkte_unentschieden INT DEFAULT 1,
    punkte_niederlage INT DEFAULT 0,
    punkte_abzug_gelbe_karte INT DEFAULT 1,
    status ENUM('geplant', 'aktiv', 'beendet', 'archiviert') DEFAULT 'geplant',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_season (season_id),
    INDEX idx_spiel (spiel_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Participants (Spieler oder Teams in einer Liga)
CREATE TABLE lsp_participants (
    participant_id INT PRIMARY KEY AUTO_INCREMENT,
    league_id INT NOT NULL,
    spieler_id INT,
    team_id INT,
    heimmap_id INT,
    punkte INT DEFAULT 0,
    siege INT DEFAULT 0,
    unentschieden INT DEFAULT 0,
    niederlagen INT DEFAULT 0,
    karten_fuer INT DEFAULT 0,
    karten_gegen INT DEFAULT 0,
    gelbe_karten INT DEFAULT 0,
    aktiv TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_league (league_id),
    INDEX idx_spieler (spieler_id),
    INDEX idx_team (team_id),
    INDEX idx_heimmap (heimmap_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Matches (Spielplan mit Hin-/Rückrunde)
CREATE TABLE lsp_matches (
    match_id INT PRIMARY KEY AUTO_INCREMENT,
    league_id INT NOT NULL,
    spieltag INT NOT NULL,
    runde ENUM('HIN', 'RUECK') NOT NULL,
    heim_participant_id INT NOT NULL,
    gast_participant_id INT NOT NULL,
    heim_map_id INT,
    gespielt_am DATETIME,
    heim_punkte INT,
    gast_punkte INT,
    heim_karten INT DEFAULT 0,
    gast_karten INT DEFAULT 0,
    heim_gelbe_karten INT DEFAULT 0,
    gast_gelbe_karten INT DEFAULT 0,
    status ENUM('offen', 'gespielt', 'abgesagt', 'verschoben') DEFAULT 'offen',
    bericht TEXT,
    pruefer_id INT,
    geprueft_am DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_league (league_id),
    INDEX idx_spieltag (spieltag),
    INDEX idx_runde (runde),
    INDEX idx_status (status),
    INDEX idx_heim (heim_participant_id),
    INDEX idx_gast (gast_participant_id),
    INDEX idx_pruefer (pruefer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Match Images (mehrere Bilder pro Spiel)
CREATE TABLE lsp_match_images (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    dateiname VARCHAR(255) NOT NULL,
    original_name VARCHAR(255),
    dateityp VARCHAR(50),
    dateigroesse INT,
    beschreibung VARCHAR(255),
    sort_order INT DEFAULT 0,
    uploaded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match (match_id),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Match Log (Änderungsprotokoll)
CREATE TABLE lsp_match_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    pruefer_id INT,
    aktion ENUM('erstellt', 'ergebnis', 'bearbeitet', 'bild_hinzugefuegt', 'bild_entfernt', 'abgesagt') NOT NULL,
    alte_daten JSON,
    neue_daten JSON,
    kommentar TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match (match_id),
    INDEX idx_pruefer (pruefer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SCHRITT 3: Foreign Keys hinzufügen
-- ============================================
ALTER TABLE lsp_leagues 
    ADD CONSTRAINT fk_lsp_leagues_season FOREIGN KEY (season_id) REFERENCES lsp_seasons(season_id) ON DELETE CASCADE;

ALTER TABLE lsp_participants 
    ADD CONSTRAINT fk_lsp_participants_league FOREIGN KEY (league_id) REFERENCES lsp_leagues(league_id) ON DELETE CASCADE;

ALTER TABLE lsp_matches 
    ADD CONSTRAINT fk_lsp_matches_league FOREIGN KEY (league_id) REFERENCES lsp_leagues(league_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_lsp_matches_heim FOREIGN KEY (heim_participant_id) REFERENCES lsp_participants(participant_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_lsp_matches_gast FOREIGN KEY (gast_participant_id) REFERENCES lsp_participants(participant_id) ON DELETE CASCADE;

ALTER TABLE lsp_match_images 
    ADD CONSTRAINT fk_lsp_images_match FOREIGN KEY (match_id) REFERENCES lsp_matches(match_id) ON DELETE CASCADE;

ALTER TABLE lsp_match_log 
    ADD CONSTRAINT fk_lsp_log_match FOREIGN KEY (match_id) REFERENCES lsp_matches(match_id) ON DELETE CASCADE;

-- ============================================
-- FERTIG! Tabellen erstellt:
-- - lsp_seasons
-- - lsp_leagues
-- - lsp_participants
-- - lsp_matches
-- - lsp_match_images
-- - lsp_match_log
-- ============================================
