-- Forum-Tabellen für EVE Online Allianz-Website
-- Führe diese Befehle über PuTTY auf dem MySQL/MariaDB-Server aus

-- Kategorien
CREATE TABLE `forum_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `access_level` int(11) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Threads
CREATE TABLE IF NOT EXISTS `forum_threads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL,
  `last_post_at` datetime NOT NULL,
  `views` int(11) NOT NULL DEFAULT 0,
  `is_sticky` tinyint(1) NOT NULL DEFAULT 0,
  `is_locked` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `forum_threads_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `forum_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Beiträge
CREATE TABLE IF NOT EXISTS `forum_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_at` datetime NOT NULL,
  `edited_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `thread_id` (`thread_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `forum_posts_ibfk_1` FOREIGN KEY (`thread_id`) REFERENCES `forum_threads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Umfragen
CREATE TABLE IF NOT EXISTS `forum_polls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `expires_at` datetime DEFAULT NULL,
  `multiple_choices` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `thread_id` (`thread_id`),
  CONSTRAINT `forum_polls_ibfk_1` FOREIGN KEY (`thread_id`) REFERENCES `forum_threads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Umfrageoptionen
CREATE TABLE IF NOT EXISTS `forum_poll_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) NOT NULL,
  `option_text` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`),
  CONSTRAINT `forum_poll_options_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `forum_polls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Umfrage-Stimmen
CREATE TABLE IF NOT EXISTS `forum_poll_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `voted_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `option_id_user_id` (`option_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `forum_poll_votes_ibfk_1` FOREIGN KEY (`option_id`) REFERENCES `forum_poll_options` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lesemarkierungen
CREATE TABLE IF NOT EXISTS `forum_read_markers` (
  `user_id` int(11) NOT NULL,
  `thread_id` int(11) NOT NULL,
  `last_read` datetime NOT NULL,
  PRIMARY KEY (`user_id`,`thread_id`),
  KEY `thread_id` (`thread_id`),
  CONSTRAINT `forum_read_markers_ibfk_2` FOREIGN KEY (`thread_id`) REFERENCES `forum_threads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Benutzerabzeichen
CREATE TABLE IF NOT EXISTS `forum_badges` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `css_class` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Entferne bestehende Daten
DELETE FROM forum_badges;
DELETE FROM forum_categories;

-- Kategorie-Daten einfügen
INSERT INTO forum_categories (name, description, sort_order, access_level) VALUES
('Ankündigungen', 'Offizielle Ankündigungen und Informationen der Allianzführung.', 1, 1),
('Strategie & Operationen', 'Planung gemeinsamer Aktionen und Flotten.', 2, 1),
('Industrie & Handel', 'Diskussionen über Produktion, Mining, Markt.', 3, 1),
('Allgemeine Diskussion', 'Diskussionen über EVE und mehr.', 4, 1),
('Nur für Direktoren', 'Interner Bereich für die Allianzführung.', 5, 3);

-- Badge-Daten einfügen
INSERT INTO forum_badges (name, description, image_path, css_class) VALUES
('Admin', 'Administrator der Website', '/assets/images/ranks/admin.png', 'badge-admin'),
('Moderator', 'Forum-Moderator', NULL, 'badge-moderator'),
('Direktor', 'Direktor einer Korporation', '/assets/images/ranks/director.png', 'badge-director'),
('Mitglied', 'Reguläres Mitglied', '/assets/images/ranks/member.png', 'badge-member'); 