-- ============================================
-- GALERIE AUFSICHT - DATENBANK SCHEMA
-- ============================================

-- Benutzer-Tabelle
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(100),
    password_hash VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
);

-- Passwort-Reset Tokens
CREATE TABLE password_resets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_token (token)
);

-- Monate (z.B. März 2026)
CREATE TABLE months (
    id INT PRIMARY KEY AUTO_INCREMENT,
    year INT NOT NULL,
    month INT NOT NULL,
    total_hours INT NOT NULL,
    is_open BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_month (year, month)
);

-- Öffnungstage
CREATE TABLE opening_days (
    id INT PRIMARY KEY AUTO_INCREMENT,
    month_id INT NOT NULL,
    day_of_month INT NOT NULL,
    day_of_week VARCHAR(10) NOT NULL,
    hours_available INT NOT NULL,
    is_buildphase BOOLEAN DEFAULT 0,
    notes VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (month_id) REFERENCES months(id) ON DELETE CASCADE,
    INDEX idx_month_id (month_id),
    UNIQUE KEY unique_day (month_id, day_of_month)
);

-- Benutzer-Monat Stundenverwaltung
CREATE TABLE user_month_hours (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    month_id INT NOT NULL,
    base_hours DECIMAL(5,2) NOT NULL,
    puffer_hours DECIMAL(5,2) NOT NULL,
    max_hours DECIMAL(5,2) NOT NULL,
    booked_hours DECIMAL(5,2) DEFAULT 0,
    plus_hours_from_previous DECIMAL(5,2) DEFAULT 0,
    minus_hours_from_previous DECIMAL(5,2) DEFAULT 0,
    final_plus_hours DECIMAL(5,2) DEFAULT 0,
    final_minus_hours DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (month_id) REFERENCES months(id) ON DELETE CASCADE,
    INDEX idx_user_month (user_id, month_id),
    UNIQUE KEY unique_user_month (user_id, month_id)
);

-- Buchungen (wer bucht welchen Tag)
CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    opening_day_id INT NOT NULL,
    booked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (opening_day_id) REFERENCES opening_days(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_opening_day_id (opening_day_id),
    UNIQUE KEY unique_booking (user_id, opening_day_id)
);

-- Audit-Log für Admin-Aktionen
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_created_at (created_at)
);
