-- Datenbank erstellen (falls noch nicht vorhanden)
CREATE DATABASE IF NOT EXISTS meine_datenbank;
USE meine_datenbank;

-- Benutzer-Tabelle mit erweiterten Feldern
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_number VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    is_business BOOLEAN DEFAULT FALSE,
    company_name VARCHAR(255),
    tax_number VARCHAR(100),
    street VARCHAR(255),
    city VARCHAR(100),
    zip VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Deutschland',
    is_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(255),
    verification_expires DATETIME,
    otp_secret VARCHAR(255),
    otp_enabled BOOLEAN DEFAULT FALSE,
    reset_token VARCHAR(255),
    reset_expires DATETIME,
    created_at DATETIME NOT NULL,
    last_login DATETIME,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'inactive'
);

-- Abonnement-Pakete
CREATE TABLE IF NOT EXISTS subscription_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    emails_per_month INT NOT NULL,
    description TEXT,
    is_custom BOOLEAN DEFAULT FALSE
);

-- Benutzer-Abonnements
CREATE TABLE IF NOT EXISTS user_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    custom_emails_limit INT,
    start_date DATETIME NOT NULL,
    end_date DATETIME,
    status ENUM('active', 'canceled', 'expired') DEFAULT 'active',
    auto_renew BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- Zahlungen
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subscription_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('paypal', 'stripe') NOT NULL,
    transaction_id VARCHAR(255),
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    payment_date DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id)
);

-- Rechnungen
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    payment_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    issue_date DATETIME NOT NULL,
    due_date DATETIME NOT NULL,
    status ENUM('paid', 'unpaid', 'overdue') DEFAULT 'unpaid',
    pdf_path VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payments(id)
);

-- Team-Mitglieder
CREATE TABLE IF NOT EXISTS team_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    member_id INT NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    invited_at DATETIME NOT NULL,
    joined_at DATETIME,
    status ENUM('pending', 'active', 'inactive') DEFAULT 'pending',
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE
);

-- E-Mail-Nutzung
CREATE TABLE IF NOT EXISTS email_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subscription_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    emails_sent INT DEFAULT 0,
    emails_limit INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id)
);

-- Dokumente-Tabelle für PDF-Verwaltung
CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    is_signed BOOLEAN DEFAULT FALSE,
    signature_data TEXT,
    uploaded_at DATETIME NOT NULL,
    signed_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- OTP-Tokens für Zwei-Faktor-Authentifizierung
CREATE TABLE IF NOT EXISTS otp_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(10) NOT NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- E-Mail-Vorlagen
CREATE TABLE IF NOT EXISTS email_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    type ENUM('verification', 'reset_password', 'welcome', 'invoice', 'otp') NOT NULL,
    is_default BOOLEAN DEFAULT FALSE
);

-- Admin-Benutzer
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    role ENUM('super_admin', 'admin', 'support') NOT NULL DEFAULT 'admin',
    created_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Standardwerte einfügen
INSERT INTO subscription_plans (name, price, emails_per_month, description, is_custom) VALUES
('Basic', 0.00, 5, 'Kostenloser Plan mit 5 E-Mails pro Monat', FALSE),
('Entry', 9.99, 30, 'Einsteigerplan mit 30 E-Mails pro Monat', FALSE),
('Pro', 29.99, 200, 'Profiplan mit 200 E-Mails pro Monat', FALSE),
('Enterprise', 49.99, 500, 'Unternehmensplan mit 500 E-Mails pro Monat', FALSE),
('Individuell', 0.00, 0, 'Individueller Plan nach Absprache', TRUE);

-- Standard-E-Mail-Vorlagen
INSERT INTO email_templates (name, subject, body, type, is_default) VALUES
('Kontobestätigung', 'Bestätigen Sie Ihr Konto', '<h1>Willkommen bei unserem Service!</h1><p>Bitte klicken Sie auf den folgenden Link, um Ihr Konto zu bestätigen: {{verification_link}}</p>', 'verification', TRUE),
('Passwort zurücksetzen', 'Passwort zurücksetzen', '<h1>Passwort zurücksetzen</h1><p>Klicken Sie auf den folgenden Link, um Ihr Passwort zurückzusetzen: {{reset_link}}</p>', 'reset_password', TRUE),
('Willkommen', 'Willkommen bei unserem Service', '<h1>Willkommen bei unserem Service!</h1><p>Vielen Dank für Ihre Registrierung. Wir freuen uns, Sie an Bord zu haben!</p>', 'welcome', TRUE),
('Rechnung', 'Ihre Rechnung', '<h1>Rechnung</h1><p>Anbei finden Sie Ihre Rechnung für den Zeitraum {{billing_period}}.</p>', 'invoice', TRUE),
('OTP-Code', 'Ihr Einmalpasswort', '<h1>Ihr Einmalpasswort</h1><p>Hier ist Ihr Einmalpasswort für die Anmeldung: {{otp_code}}</p><p>Dieser Code ist 10 Minuten gültig.</p>', 'otp', TRUE);

