-- Kicktipp-System für Antenne Peine e.V.
-- Datenbankschema

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Benutzer-Tabelle
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL UNIQUE,
  `email` varchar(255) NOT NULL UNIQUE,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `email_notifications` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ligen-Tabelle
CREATE TABLE `leagues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `short_name` varchar(10) NOT NULL,
  `openliga_id` varchar(20) NOT NULL,
  `season` varchar(10) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_league_season` (`openliga_id`, `season`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Teams-Tabelle
CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `short_name` varchar(20) NOT NULL,
  `openliga_id` int(11) NOT NULL,
  `league_id` varchar(20) NOT NULL,
  `logo_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_team_league` (`openliga_id`, `league_id`),
  FOREIGN KEY (`league_id`) REFERENCES `leagues` (`openliga_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Spiele-Tabelle
CREATE TABLE `matches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `openliga_id` int(11) NOT NULL,
  `league_id` varchar(20) NOT NULL,
  `home_team_id` int(11) NOT NULL,
  `away_team_id` int(11) NOT NULL,
  `match_date` datetime NOT NULL,
  `home_score` int(11) DEFAULT NULL,
  `away_score` int(11) DEFAULT NULL,
  `status` enum('scheduled','live','finished','cancelled') DEFAULT 'scheduled',
  `matchday` int(11) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match` (`openliga_id`, `league_id`),
  KEY `idx_match_date` (`match_date`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`league_id`) REFERENCES `leagues` (`openliga_id`) ON DELETE CASCADE,
  FOREIGN KEY (`home_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`away_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tipps-Tabelle
CREATE TABLE `predictions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `match_id` int(11) NOT NULL,
  `home_score` int(11) NOT NULL,
  `away_score` int(11) NOT NULL,
  `points` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_match` (`user_id`, `match_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_match_id` (`match_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- JWT-Token-Tabelle für Session-Management
CREATE TABLE `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_token_hash` (`token_hash`),
  KEY `idx_user_id` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- E-Mail-Logs für DSGVO-Konformität
CREATE TABLE `email_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `email_type` varchar(50) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `sent_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System-Einstellungen
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL UNIQUE,
  `setting_value` text NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Standard-Einstellungen einfügen
INSERT INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('site_name', 'Antenne Peine Kicktipp', 'Name der Website'),
('site_description', 'Offizielles Kicktipp-System von Antenne Peine e.V.', 'Beschreibung der Website'),
('primary_color', '#E30613', 'Primäre Farbe (Rot)'),
('secondary_color', '#FFFFFF', 'Sekundäre Farbe (Weiß)'),
('openliga_api_key', '', 'OpenLigaDB API-Schlüssel'),
('email_from', 'noreply@antenne-peine.de', 'Absender-E-Mail-Adresse'),
('email_from_name', 'Antenne Peine Kicktipp', 'Absender-Name'),
('points_exact', '3', 'Punkte für exaktes Ergebnis'),
('points_tendency_goals', '2', 'Punkte für Tendenz + Tore'),
('points_tendency', '1', 'Punkte für Tendenz'),
('max_tips_per_matchday', '9', 'Maximale Tipps pro Spieltag'),
('tip_deadline_minutes', '30', 'Minuten vor Spielbeginn für Tippabgabe');

-- Standard-Ligen einfügen
INSERT INTO `leagues` (`name`, `short_name`, `openliga_id`, `season`, `is_active`) VALUES
('1. Bundesliga', 'BL1', 'bl1', '2025', 1),
('2. Bundesliga', 'BL2', 'bl2', '2025', 1),
('3. Liga', 'BL3', 'bl3', '2025', 1);

SET FOREIGN_KEY_CHECKS = 1; 