admin
06ea149d41
Führt Login mit vier Rollen (Admin, Teamleiter, Mitarbeiter, Beobachter) ein, über die Teammitglieder eigenen Urlaub inkl. Pflicht-Vertreter eintragen können. Die Auslastungsberechnung berücksichtigt Urlaub jetzt als reduzierte Kapazität. Neue Konten vergeben ihr Passwort selbst beim ersten Login. Migrationsskript für bestehende Mitarbeiter aus der Vorversion inklusive. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
82 lines
2.6 KiB
SQL
82 lines
2.6 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS employees (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
first_name TEXT NOT NULL,
|
|
last_name TEXT NOT NULL,
|
|
email TEXT,
|
|
role TEXT,
|
|
weekly_capacity_hours REAL NOT NULL DEFAULT 40
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS teams (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS team_members (
|
|
team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (team_id, employee_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
start_date TEXT,
|
|
end_date TEXT,
|
|
color TEXT DEFAULT '#4f46e5'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
start_date TEXT NOT NULL,
|
|
end_date TEXT NOT NULL,
|
|
estimated_hours REAL NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'geplant'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS task_dependencies (
|
|
task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
depends_on_task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (task_id, depends_on_task_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS assignments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
|
|
allocated_hours_per_week REAL NOT NULL DEFAULT 0,
|
|
UNIQUE (task_id, employee_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
employee_id INTEGER UNIQUE REFERENCES employees(id) ON DELETE SET NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT,
|
|
role TEXT NOT NULL DEFAULT 'mitarbeiter' CHECK (role IN ('admin','teamleiter','mitarbeiter','beobachter'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
expires_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS vacations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
|
|
start_date TEXT NOT NULL,
|
|
end_date TEXT NOT NULL,
|
|
substitute_employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE RESTRICT,
|
|
note TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_vacations_employee ON vacations(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_vacations_substitute ON vacations(substitute_employee_id);
|