-- Seelenklänge Webapp — Datenbankschema
-- Import z.B. via: mysql -h 127.0.0.1 -P 8889 -u root -proot seelenklaenge < database/schema.sql
-- (zuvor die Datenbank anlegen: CREATE DATABASE seelenklaenge CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
    email_verified_at DATETIME NULL,
    reminder_emails_enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bestätigungslinks für neue E-Mail-Adressen (Double-Opt-in)
CREATE TABLE IF NOT EXISTS email_verifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fehlversuche bei Login/Passwort vergessen, für die Drosselung (src/Core/LoginThrottle.php)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope VARCHAR(30) NOT NULL,
    identifier VARCHAR(190) NOT NULL,
    ip VARCHAR(45) NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lookup (scope, ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admins sind bewusst eine eigene Tabelle mit eigenem Login/eigener Session,
-- vollständig unabhängig vom Nutzer-Login (siehe src/Core/AdminAuth.php).
CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(150) NOT NULL,
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kategorien werden im Admin gepflegt (Name, Beschreibung, Farbe), damit sie
-- auf der Startseite mit erklärendem Text angezeigt werden können.
CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    color ENUM('blue', 'teal', 'purple', 'red', 'coral', 'orange', 'yellow', 'mint', 'sky', 'violet') NOT NULL DEFAULT 'blue',
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (slug, name, description, color, sort_order) VALUES
('seelenklaenge', 'Seelenklänge', 'Klassische Klangreisen mit Klangschalen, Gong und Stimme für tiefe Entspannung und innere Ruhe.', 'blue', 1),
('seelenklaenge_equitao', 'Seelenklänge mit Equitao', 'Klangarbeit in Verbindung mit Pferden – achtsame Begegnung zwischen Mensch und Tier in der Natur.', 'teal', 2),
('seelenklaenge_tanz', 'Seelenklänge mit Tanz', 'Klang trifft Bewegung: tänzerischer Ausdruck im Zusammenspiel mit Klangschalen und Rhythmus.', 'purple', 3),
('einzelsitzung', 'Einzelsitzung', 'Eine individuelle Klangsitzung ganz auf dich abgestimmt, im geschützten Einzelsetting.', 'red', 4)
ON DUPLICATE KEY UPDATE name = VALUES(name);

CREATE TABLE IF NOT EXISTS seminars (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    category VARCHAR(60) NOT NULL DEFAULT 'seelenklaenge',
    description TEXT NULL,
    image_path VARCHAR(255) NULL,
    location VARCHAR(200) NULL,
    price DECIMAL(10,2) NULL,
    price_eur DECIMAL(10,2) NULL,
    early_bird_price_eur DECIMAL(10,2) NULL,
    early_bird_enabled TINYINT(1) NOT NULL DEFAULT 0,
    early_bird_price DECIMAL(10,2) NULL,
    early_bird_until DATE NULL,
    created_by_admin_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    FOREIGN KEY (category) REFERENCES categories(slug) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seminar_dates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    seminar_id INT UNSIGNED NOT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    capacity INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('draft', 'published', 'cancelled') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (seminar_id) REFERENCES seminars(id) ON DELETE CASCADE,
    INDEX idx_start (start_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS registrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    seminar_date_id INT UNSIGNED NOT NULL,
    status ENUM('confirmed', 'waitlisted', 'cancelled') NOT NULL DEFAULT 'confirmed',
    booked_price_eur DECIMAL(10,2) NULL,
    regular_price_eur DECIMAL(10,2) NULL,
    booked_price DECIMAL(10,2) NULL,
    regular_price DECIMAL(10,2) NULL,
    early_bird_applied TINYINT(1) NOT NULL DEFAULT 0,
    registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_date (user_id, seminar_date_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (seminar_date_id) REFERENCES seminar_dates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(60) NOT NULL UNIQUE,
    label VARCHAR(150) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body_html TEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS smtp_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    host VARCHAR(190) NOT NULL DEFAULT '',
    port INT UNSIGNED NOT NULL DEFAULT 587,
    encryption ENUM('none', 'tls', 'ssl') NOT NULL DEFAULT 'tls',
    username VARCHAR(190) NOT NULL DEFAULT '',
    password_encrypted VARCHAR(255) NOT NULL DEFAULT '',
    from_email VARCHAR(190) NOT NULL DEFAULT '',
    from_name VARCHAR(150) NOT NULL DEFAULT 'Seelenklänge',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reminder_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed: Standard-Einstellungen
INSERT INTO settings (`key`, `value`) VALUES
    ('inactivity_days_threshold', '90')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- Seed: einmalige SMTP-Zeile (leer, wird im Admin ausgefüllt)
INSERT INTO smtp_settings (id, host, port, encryption, username, password_encrypted, from_email, from_name)
SELECT 1, '', 587, 'tls', '', '', 'noreply@seelenklaenge.local', 'Seelenklänge'
WHERE NOT EXISTS (SELECT 1 FROM smtp_settings WHERE id = 1);

-- Seed: E-Mail-Vorlagen mit Platzhaltern {{name}}, {{seminar_title}}, {{date}}, {{link}}
INSERT INTO email_templates (`key`, label, subject, body_html) VALUES
('verify_email', 'E-Mail-Adresse bestätigen', 'Bitte bestätige deine E-Mail-Adresse',
 '<p>Liebe/r {{name}},</p><p>schön, dass du dich bei Seelenklänge registriert hast. Bitte bestätige deine E-Mail-Adresse mit einem Klick auf den folgenden Link:</p><p><a href="{{link}}">{{link}}</a></p><p>Der Link ist 24 Stunden gültig. Wenn du dich nicht registriert hast, kannst du diese E-Mail ignorieren.</p>'),
('welcome', 'Willkommen nach Registrierung', 'Willkommen bei Seelenklänge',
 '<p>Liebe/r {{name}},</p><p>herzlich willkommen bei Seelenklänge! Dein Konto wurde erfolgreich erstellt.</p><p>Von Herzen,<br>Dein Seelenklänge-Team</p>'),
('password_reset', 'Passwort zurücksetzen', 'Dein neues Passwort für Seelenklänge',
 '<p>Liebe/r {{name}},</p><p>du hast angefragt, dein Passwort zurückzusetzen. Klicke auf den folgenden Link, um ein neues Passwort zu vergeben:</p><p><a href="{{link}}">{{link}}</a></p><p>Der Link ist 60 Minuten gültig. Wenn du das nicht warst, kannst du diese E-Mail ignorieren.</p>'),
('registration_confirmed', 'Anmeldebestätigung Seminar', 'Anmeldung bestätigt: {{seminar_title}}',
 '<p>Liebe/r {{name}},</p><p>deine Anmeldung für <strong>{{seminar_title}}</strong> am {{date}} ist bestätigt. Wir freuen uns auf dich!</p>'),
('waitlist_added', 'Auf Warteliste gesetzt', 'Warteliste: {{seminar_title}}',
 '<p>Liebe/r {{name}},</p><p>der Termin <strong>{{seminar_title}}</strong> am {{date}} ist aktuell ausgebucht. Wir haben dich auf die Warteliste gesetzt und melden uns, sobald ein Platz frei wird.</p>'),
('waitlist_promoted', 'Von Warteliste nachgerückt', 'Ein Platz ist frei geworden: {{seminar_title}}',
 '<p>Liebe/r {{name}},</p><p>gute Neuigkeiten: Für <strong>{{seminar_title}}</strong> am {{date}} ist ein Platz frei geworden und deine Anmeldung ist jetzt bestätigt.</p>'),
('reminder_inactivity', 'Erinnerung bei Inaktivität', 'Wir vermissen dich bei Seelenklänge',
 '<p>Liebe/r {{name}},</p><p>es ist eine Weile her, dass du bei Seelenklänge warst. Schau doch mal wieder vorbei und entdecke unsere aktuellen Seminartermine.</p><p><a href="{{link}}">Zum Kalender</a></p>'),
('account_deleted', 'Bestätigung Account-Löschung', 'Dein Seelenklänge-Konto wurde gelöscht',
 '<p>Liebe/r {{name}},</p><p>dein Konto und alle zugehörigen Anmeldungen wurden wie gewünscht gelöscht.</p>')
ON DUPLICATE KEY UPDATE label = VALUES(label);

-- Admin-Zugänge werden bewusst NICHT per Schema angelegt, sondern per CLI mit eigenem Passwort:
--   php bin/create-admin.php