CREATE DATABASE IF NOT EXISTS feuerwehr;
USE feuerwehr;

-- Tabelle für Benutzer (Admins)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    user_rank VARCHAR(50) DEFAULT 'Mitglied',
    phone VARCHAR(20),
    force_password_change BOOLEAN DEFAULT TRUE,
    two_factor_secret VARCHAR(255),
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    reset_token VARCHAR(255) DEFAULT NULL,
    reset_token_expires DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle für Aktivitäten-Logs
CREATE TABLE IF NOT EXISTS action_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    perm_incidents BOOLEAN DEFAULT FALSE,
    perm_youth BOOLEAN DEFAULT FALSE,
    perm_users BOOLEAN DEFAULT FALSE,
    perm_groups BOOLEAN DEFAULT FALSE,
    perm_settings BOOLEAN DEFAULT FALSE,
    perm_logs BOOLEAN DEFAULT FALSE,
    perm_minis BOOLEAN DEFAULT FALSE,
    perm_vehicles BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle für Einsätze
CREATE TABLE IF NOT EXISTS incidents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    incident_date DATE NOT NULL,
    incident_time TIME NOT NULL,
    location VARCHAR(255),
    borough VARCHAR(50),
    status ENUM('draft', 'published') DEFAULT 'published',
    forces_count INT DEFAULT 0,
    image_path VARCHAR(255), -- Bleibt für das Hauptbild (Vorschaubild)
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Tabelle für zusätzliche Bilder pro Einsatz
CREATE TABLE IF NOT EXISTS incident_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    incident_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE
);

-- Tabelle für Minifeuerwehr Posts
CREATE TABLE IF NOT EXISTS mini_news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    post_date DATE NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'published',
    image_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle pro Minifeuerwehr News
CREATE TABLE IF NOT EXISTS mini_news_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mini_news_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    FOREIGN KEY (mini_news_id) REFERENCES mini_news(id) ON DELETE CASCADE
);
 
-- Tabelle für Fahrzeuge
CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle für globale Einstellungen (Social Media, etc.)
CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(50) PRIMARY KEY,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initiale Einstellungen
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES 
('instagram_url', 'https://instagram.com/feuerwehr_breuberg'),
('facebook_url', 'https://facebook.com/feuerwehr_breuberg');

-- Initiale Gruppen
INSERT IGNORE INTO groups (group_name, description, perm_incidents, perm_youth, perm_users, perm_groups, perm_settings, perm_logs, perm_minis, perm_vehicles) VALUES 
('Administrator', 'Volle Systemrechte', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE),
('Einsatzabteilung', 'Aktive Einsatzkräfte', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('Jugendfeuerwehr', 'Nachwuchs 10-17 Jahre', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('Minifeuerwehr', 'Nachwuchs 6-10 Jahre', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('Alters- und Ehrenabteilung', 'Ehemalige Aktive', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('socialmedia', 'Social Media Team', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE);

-- Initiale Admin-User (Passwort muss noch mittels bcrypt gehasht werden)
-- INSERT INTO users (username, password, user_rank) VALUES ('admin', '$2b$10$hashed_password', 'Administrator');
