50 lines
1.8 KiB
SQL
50 lines
1.8 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
firstname VARCHAR(100) NOT NULL,
|
|
lastname VARCHAR(100) NOT NULL,
|
|
email VARCHAR(190) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('member', 'editor', 'admin') NOT NULL DEFAULT 'member',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS work_logs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
member_id INT NOT NULL,
|
|
actor_id INT NOT NULL,
|
|
hours DECIMAL(5,2) NOT NULL,
|
|
note VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX (member_id),
|
|
INDEX (actor_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
setting_key VARCHAR(100) PRIMARY KEY,
|
|
setting_value TEXT NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS password_resets (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
token_hash VARCHAR(255) NOT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX (user_id),
|
|
INDEX (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
actor_id INT NOT NULL,
|
|
action VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX (actor_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT INTO users (firstname, lastname, email, password_hash, role)
|
|
VALUES
|
|
('Max', 'Mustermann', 'max@example.com', '$2y$10$Q4NnU8X3uGQfM4p4RkWnOe7d5Vw5O3p5uT4Q2a5W0uX1fJ1cR5h3C', 'admin'),
|
|
('Lisa', 'Beispiel', 'lisa@example.com', '$2y$10$Q4NnU8X3uGQfM4p4RkWnOe7d5Vw5O3p5uT4Q2a5W0uX1fJ1cR5h3C', 'editor'),
|
|
('Tom', 'Mitglied', 'tom@example.com', '$2y$10$Q4NnU8X3uGQfM4p4RkWnOe7d5Vw5O3p5uT4Q2a5W0uX1fJ1cR5h3C', 'member');
|