-- 1. Tabelle für die Spiel-Typen (z.B. Klassik, Puzzle, Kids)
CREATE TABLE IF NOT EXISTS `game_typen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bezeichnung` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tabelle für die Spieler / Teams
CREATE TABLE IF NOT EXISTS `spieler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Haupttabelle für die Spiele
CREATE TABLE IF NOT EXISTS `spiele` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titel` varchar(255) NOT NULL,
  `typ_id` int(11) DEFAULT NULL,
  `ean` varchar(50) NOT NULL,
  `level` enum('Unknown','Einsteiger','Fortgeschrittene','Profi') DEFAULT 'Unknown',
  `bild_url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ean_unique` (`ean`),
  KEY `fk_typ` (`typ_id`),
  CONSTRAINT `fk_typ` FOREIGN KEY (`typ_id`) REFERENCES `game_typen` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tabelle für die Highscores
CREATE TABLE IF NOT EXISTS `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `spiel_id` int(11) NOT NULL,
  `spieler_id` int(11) NOT NULL,
  `zeit` int(11) NOT NULL COMMENT 'Zeit in Minuten',
  `hilfe` int(11) DEFAULT 0 COMMENT 'Anzahl der Hilfekarten',
  `sterne` int(11) DEFAULT NULL COMMENT 'Bewertung 1-10',
  `gespielt_am` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_spiel_score` (`spiel_id`),
  KEY `fk_spieler_score` (`spieler_id`),
  CONSTRAINT `fk_spiel_score` FOREIGN KEY (`spiel_id`) REFERENCES `spiele` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_spieler_score` FOREIGN KEY (`spieler_id`) REFERENCES `spieler` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Tabelle für den Besitzstatus
CREATE TABLE IF NOT EXISTS `besitzstatus` (
  `spieler_id` int(11) NOT NULL,
  `spiel_id` int(11) NOT NULL,
  `status` enum('besitzt','nicht besitzt','verkauft') DEFAULT 'nicht besitzt',
  PRIMARY KEY (`spieler_id`,`spiel_id`),
  KEY `fk_spiel_besitz` (`spiel_id`),
  CONSTRAINT `fk_spiel_besitz` FOREIGN KEY (`spiel_id`) REFERENCES `spiele` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_spieler_besitz` FOREIGN KEY (`spieler_id`) REFERENCES `spieler` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;