678 lines
29 KiB
SQL
678 lines
29 KiB
SQL
-- DeskHub Helpdesk – Datenbankschema (MySQL/MariaDB)
|
||
-- Zeichensatz: utf8mb4
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Abteilungen
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS departments (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
email VARCHAR(150) NOT NULL,
|
||
is_default TINYINT(1) NOT NULL DEFAULT 0,
|
||
signature TEXT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Prioritäten inkl. SLA-Zeiten (in Stunden)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS priorities (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL,
|
||
color VARCHAR(7) NOT NULL DEFAULT '#888888',
|
||
response_hours INT NOT NULL DEFAULT 24,
|
||
resolution_hours INT NOT NULL DEFAULT 72,
|
||
sort_order INT NOT NULL DEFAULT 0
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Mitarbeiter (Agenten/Admins)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS staff (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL,
|
||
email VARCHAR(150) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
role ENUM('admin','agent') NOT NULL DEFAULT 'agent',
|
||
role_id INT NULL,
|
||
department_id INT NULL,
|
||
signature TEXT NULL,
|
||
phone VARCHAR(60) NULL,
|
||
avatar_path VARCHAR(255) NULL,
|
||
is_on_vacation TINYINT(1) NOT NULL DEFAULT 0,
|
||
vacation_substitute_id INT NULL,
|
||
totp_secret VARCHAR(64) NULL,
|
||
totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- Mitarbeiter <-> weitere Abteilungen (n:m, zusätzlich zur Hauptabteilung)
|
||
CREATE TABLE IF NOT EXISTS staff_department (
|
||
staff_id INT NOT NULL,
|
||
department_id INT NOT NULL,
|
||
notify TINYINT(1) NOT NULL DEFAULT 1,
|
||
PRIMARY KEY (staff_id, department_id),
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- Widerrufbare Bearer-Tokens fuer native Mitarbeiter-Apps.
|
||
-- Es wird ausschliesslich der SHA-256-Hash des Tokens gespeichert.
|
||
CREATE TABLE IF NOT EXISTS mobile_api_tokens (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
staff_id INT NOT NULL,
|
||
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
||
device_name VARCHAR(120) NULL,
|
||
expires_at DATETIME NOT NULL,
|
||
last_used_at DATETIME NULL,
|
||
revoked_at DATETIME NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Kunden-Organisationen (Firmen, denen mehrere Kontakte zugeordnet werden)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS organizations (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(150) NOT NULL,
|
||
notes TEXT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Kunden (Endnutzer im Kundenportal)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL,
|
||
email VARCHAR(150) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
phone VARCHAR(40) NULL,
|
||
organization_id INT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Hilfe-Themen (Dropdown bei Ticket-Erstellung, setzt Abteilung+Priorität)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS help_topics (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(150) NOT NULL,
|
||
department_id INT NOT NULL,
|
||
priority_id INT NOT NULL,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id),
|
||
FOREIGN KEY (priority_id) REFERENCES priorities(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Individuelle Ticket-Felder (zusätzlich zu Name/E-Mail/Betreff/Beschreibung)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS custom_fields (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
label VARCHAR(150) NOT NULL,
|
||
field_type ENUM('text','textarea','select','checkbox','number','date','list') NOT NULL DEFAULT 'text',
|
||
options TEXT NULL,
|
||
list_id INT NULL,
|
||
help_topic_id INT NULL,
|
||
is_required TINYINT(1) NOT NULL DEFAULT 0,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (help_topic_id) REFERENCES help_topics(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_field_values (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
custom_field_id INT NOT NULL,
|
||
value TEXT NULL,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (custom_field_id) REFERENCES custom_fields(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Tickets
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS tickets (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_number VARCHAR(20) NOT NULL UNIQUE,
|
||
subject VARCHAR(255) NOT NULL,
|
||
user_id INT NULL,
|
||
requester_name VARCHAR(120) NOT NULL,
|
||
requester_email VARCHAR(150) NOT NULL,
|
||
department_id INT NOT NULL,
|
||
help_topic_id INT NULL,
|
||
assigned_staff_id INT NULL,
|
||
team_id INT NULL,
|
||
priority_id INT NOT NULL,
|
||
status ENUM('open','pending','answered','on_hold','resolved','closed') NOT NULL DEFAULT 'open',
|
||
source ENUM('web','email','phone','staff') NOT NULL DEFAULT 'web',
|
||
is_spam TINYINT(1) NOT NULL DEFAULT 0,
|
||
requester_ip VARCHAR(45) NULL,
|
||
first_response_due_at DATETIME NULL,
|
||
first_responded_at DATETIME NULL,
|
||
resolution_due_at DATETIME NULL,
|
||
resolved_at DATETIME NULL,
|
||
closed_at DATETIME NULL,
|
||
is_overdue TINYINT(1) NOT NULL DEFAULT 0,
|
||
merged_into INT NULL,
|
||
locked_by INT NULL,
|
||
locked_at DATETIME NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id),
|
||
FOREIGN KEY (help_topic_id) REFERENCES help_topics(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (assigned_staff_id) REFERENCES staff(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (priority_id) REFERENCES priorities(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_access_tokens (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
||
token_enc TEXT NOT NULL,
|
||
revoked_at DATETIME NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Ticket-Verlauf (Nachrichten / interne Notizen)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS ticket_threads (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
author_type ENUM('customer','staff','system') NOT NULL,
|
||
staff_id INT NULL,
|
||
user_id INT NULL,
|
||
message MEDIUMTEXT NOT NULL,
|
||
is_internal_note TINYINT(1) NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Dateianhänge
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS ticket_attachments (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
thread_id INT NOT NULL,
|
||
original_name VARCHAR(255) NOT NULL,
|
||
stored_name VARCHAR(255) NOT NULL,
|
||
filesize INT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (thread_id) REFERENCES ticket_threads(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Wissensdatenbank
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS kb_categories (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL,
|
||
slug VARCHAR(140) NOT NULL UNIQUE,
|
||
sort_order INT NOT NULL DEFAULT 0
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS kb_articles (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
category_id INT NOT NULL,
|
||
title VARCHAR(255) NOT NULL,
|
||
slug VARCHAR(280) NOT NULL UNIQUE,
|
||
body MEDIUMTEXT NOT NULL,
|
||
is_published TINYINT(1) NOT NULL DEFAULT 1,
|
||
views 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,
|
||
FOREIGN KEY (category_id) REFERENCES kb_categories(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Aktivitätsprotokoll (Audit-Trail je Ticket)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS activity_log (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
staff_id INT NULL,
|
||
action VARCHAR(255) NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Allgemeine Einstellungen (Key-Value), z.B. Firmenlogo, Anzeigename
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS settings (
|
||
setting_key VARCHAR(100) PRIMARY KEY,
|
||
setting_value TEXT
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Vorlagen/Textbausteine für Mitarbeiter-Antworten
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS canned_responses (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
title VARCHAR(150) NOT NULL,
|
||
body MEDIUMTEXT NOT NULL,
|
||
department_id INT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- E-Mail-Adressen (mehrere je Abteilung möglich, z.B. für unterschiedliche
|
||
-- Postfächer/Absenderadressen je Priorität)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS email_addresses (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
email VARCHAR(150) NOT NULL,
|
||
label VARCHAR(150) NULL,
|
||
department_id INT NOT NULL,
|
||
priority_id INT NULL,
|
||
is_default TINYINT(1) NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (priority_id) REFERENCES priorities(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Protokoll gelöschter Tickets (für Berichte, da das Ticket selbst beim
|
||
-- Löschen samt Verlauf entfernt wird)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS deleted_tickets_log (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_number VARCHAR(20) NOT NULL,
|
||
department_id INT NULL,
|
||
priority_id INT NULL,
|
||
help_topic_id INT NULL,
|
||
assigned_staff_id INT NULL,
|
||
deleted_by INT NULL,
|
||
deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Passwort-Reset-Tokens (Kunden und Mitarbeiter gemeinsam, per account_type unterschieden)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS password_resets (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
account_type ENUM('customer','staff') NOT NULL,
|
||
email VARCHAR(150) 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
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Einfache Rate-Begrenzung für das öffentliche Ticket-Formular
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS rate_limit_log (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
bucket VARCHAR(50) NOT NULL,
|
||
identifier VARCHAR(100) NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Admin-Aktionsprotokoll (Einstellungen, Mitarbeiter- und Stammdatenänderungen)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS admin_audit_log (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
staff_id INT NULL,
|
||
action VARCHAR(255) NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Tags / Labels für Tickets
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS tags (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(60) NOT NULL UNIQUE,
|
||
color VARCHAR(7) NOT NULL DEFAULT '#64748b'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_tags (
|
||
ticket_id INT NOT NULL,
|
||
tag_id INT NOT NULL,
|
||
PRIMARY KEY (ticket_id, tag_id),
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Sperrliste: blockiert Ticket-Erstellung per E-Mail-Adresse oder Domain
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS banlist (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
match_type ENUM('email','domain') NOT NULL DEFAULT 'email',
|
||
value VARCHAR(190) NOT NULL,
|
||
reason VARCHAR(255) NULL,
|
||
created_by INT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (created_by) REFERENCES staff(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Ticket-Regeln: automatische Aktionen bei neuen Tickets anhand von Bedingungen
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS ticket_rules (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(150) NOT NULL,
|
||
match_type ENUM('all','any') NOT NULL DEFAULT 'all',
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_rule_conditions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
rule_id INT NOT NULL,
|
||
field ENUM('subject','message','requester_email','requester_name','source') NOT NULL,
|
||
operator ENUM('contains','not_contains','equals','starts_with','ends_with') NOT NULL DEFAULT 'contains',
|
||
value VARCHAR(255) NOT NULL,
|
||
FOREIGN KEY (rule_id) REFERENCES ticket_rules(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_rule_actions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
rule_id INT NOT NULL,
|
||
action_type ENUM('set_department','set_priority','set_help_topic','assign_staff','add_tag','reject') NOT NULL,
|
||
action_value VARCHAR(255) NULL,
|
||
FOREIGN KEY (rule_id) REFERENCES ticket_rules(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Kollaboratoren: zusätzliche, per Mail informierte Personen auf einem Ticket
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS ticket_collaborators (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
email VARCHAR(150) NOT NULL,
|
||
name VARCHAR(120) NULL,
|
||
added_by INT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (added_by) REFERENCES staff(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Geschäftszeiten für SLA-Berechnung (optional, siehe settings.business_hours_enabled)
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS business_hours (
|
||
weekday TINYINT NOT NULL PRIMARY KEY,
|
||
is_open TINYINT(1) NOT NULL DEFAULT 1,
|
||
open_time TIME NOT NULL DEFAULT '09:00:00',
|
||
close_time TIME NOT NULL DEFAULT '17:00:00'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS business_holidays (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
holiday_date DATE NOT NULL,
|
||
name VARCHAR(150) NOT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Teams: abteilungsübergreifende Mitarbeiter-Gruppen
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS teams (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL,
|
||
lead_id INT NULL,
|
||
notes TEXT NULL,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (lead_id) REFERENCES staff(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS team_members (
|
||
team_id INT NOT NULL,
|
||
staff_id INT NOT NULL,
|
||
PRIMARY KEY (team_id, staff_id),
|
||
FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Rollen: feinere Rechtevergabe für Agenten
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS roles (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL,
|
||
permissions TEXT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Benutzerdefinierte Listen (für Custom Fields)
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS custom_lists (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(150) NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS custom_list_items (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
list_id INT NOT NULL,
|
||
value VARCHAR(255) NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
FOREIGN KEY (list_id) REFERENCES custom_lists(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Seiten-Verwaltung (Kundenportal-Inhaltsseiten)
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS site_pages (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
slug VARCHAR(100) NOT NULL UNIQUE,
|
||
title VARCHAR(200) NOT NULL,
|
||
content TEXT NULL,
|
||
show_in_nav TINYINT(1) NOT NULL DEFAULT 1,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Aufgaben (Tasks) – an Tickets oder Mitarbeiter gebunden
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS tasks (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NULL,
|
||
title VARCHAR(255) NOT NULL,
|
||
description TEXT NULL,
|
||
assigned_to INT NULL,
|
||
status ENUM('open','in_progress','done') NOT NULL DEFAULT 'open',
|
||
due_at DATETIME NULL,
|
||
created_by INT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (assigned_to) REFERENCES staff(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (created_by) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Gespeicherte Queues (persönliche Ticket-Filter)
|
||
-- ==============================================================
|
||
CREATE TABLE IF NOT EXISTS saved_queues (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
staff_id INT NOT NULL,
|
||
name VARCHAR(150) NOT NULL,
|
||
filters TEXT NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ==============================================================
|
||
-- Seed-/Beispieldaten
|
||
-- ==============================================================
|
||
|
||
INSERT INTO departments (name, email, is_default) VALUES
|
||
('Allgemeiner Support', 'support@example.com', 1),
|
||
('Technik', 'technik@example.com', 0),
|
||
('Buchhaltung', 'buchhaltung@example.com', 0);
|
||
|
||
INSERT INTO priorities (name, color, response_hours, resolution_hours, sort_order) VALUES
|
||
('Niedrig', '#6b7280', 48, 168, 1),
|
||
('Normal', '#2563eb', 24, 72, 2),
|
||
('Hoch', '#d97706', 8, 24, 3),
|
||
('Dringend', '#dc2626', 2, 8, 4);
|
||
|
||
-- Standard-Admin-Konto: admin@example.com / admin123
|
||
-- WICHTIG: Passwort nach der ersten Anmeldung sofort ändern!
|
||
INSERT INTO staff (name, email, password_hash, role, department_id, is_active) VALUES
|
||
('Administrator', 'admin@example.com', '$2y$10$g9LizLZd7enmFOefHqwJP.FSosxEEKjmhrT5Rc63W4ZA8oIfNxO3a', 'admin', 1, 1);
|
||
|
||
INSERT INTO kb_categories (name, slug, sort_order) VALUES
|
||
('Erste Schritte', 'erste-schritte', 1),
|
||
('Häufige Fragen', 'haeufige-fragen', 2);
|
||
|
||
INSERT INTO kb_articles (category_id, title, slug, body, is_published) VALUES
|
||
(1, 'Wie erstelle ich ein Ticket?', 'wie-erstelle-ich-ein-ticket',
|
||
'<p>Klicken Sie im Kundenportal auf "Neues Ticket", wählen Sie eine Abteilung und beschreiben Sie Ihr Anliegen so genau wie möglich. Sie erhalten anschließend eine Ticketnummer, mit der Sie den Status jederzeit verfolgen können.</p>', 1),
|
||
(2, 'Wie lange dauert die Bearbeitung?', 'wie-lange-dauert-die-bearbeitung',
|
||
'<p>Die Bearbeitungszeit richtet sich nach der Priorität Ihres Tickets. Die genauen Reaktions- und Lösungszeiten finden Sie in der Ticketübersicht.</p>', 1);
|
||
|
||
-- Hilfe-Themen: Dropdown bei der Ticket-Erstellung, setzt Abteilung+Priorität automatisch
|
||
INSERT INTO help_topics (name, department_id, priority_id, is_active, sort_order) VALUES
|
||
('Allgemeine Anfrage', 1, 2, 1, 1),
|
||
('Technisches Problem', 2, 3, 1, 2),
|
||
('Rechnungsfrage', 3, 2, 1, 3);
|
||
|
||
-- Beispiel-Textbaustein für Mitarbeiter-Antworten
|
||
INSERT INTO canned_responses (title, body, department_id) VALUES
|
||
('Begrüßung', 'Vielen Dank für Ihre Nachricht. Wir haben Ihr Anliegen erhalten und kümmern uns schnellstmöglich darum.', NULL),
|
||
('Weitere Informationen benötigt', 'Um Ihnen weiterhelfen zu können, benötigen wir noch ein paar zusätzliche Informationen. Könnten Sie uns bitte mitteilen, ...', NULL);
|
||
|
||
-- Standard-E-Mail-Adressen je Abteilung
|
||
INSERT INTO email_addresses (email, label, department_id, priority_id, is_default) VALUES
|
||
('support@example.com', 'Allgemeiner Support', 1, NULL, 1),
|
||
('technik@example.com', 'Technik', 2, NULL, 1),
|
||
('buchhaltung@example.com', 'Buchhaltung', 3, NULL, 1);
|
||
|
||
-- Standard-Geschäftszeiten: Mo-Fr 9-17 Uhr, Wochenende geschlossen (nur wirksam, wenn unter
|
||
-- Einstellungen "Geschäftszeiten für SLA verwenden" aktiviert wird)
|
||
INSERT INTO business_hours (weekday, is_open, open_time, close_time) VALUES
|
||
(0, 0, '09:00:00', '17:00:00'), -- Sonntag
|
||
(1, 1, '09:00:00', '17:00:00'), -- Montag
|
||
(2, 1, '09:00:00', '17:00:00'), -- Dienstag
|
||
(3, 1, '09:00:00', '17:00:00'), -- Mittwoch
|
||
(4, 1, '09:00:00', '17:00:00'), -- Donnerstag
|
||
(5, 1, '09:00:00', '17:00:00'), -- Freitag
|
||
(6, 0, '09:00:00', '17:00:00'); -- Samstag
|
||
|
||
-- Neue Tabellen für erweiterte Features (ALTER für bestehende Installationen am Ende)
|
||
|
||
CREATE TABLE IF NOT EXISTS staff_mentions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
thread_id INT NOT NULL,
|
||
mentioned_staff_id INT NOT NULL,
|
||
mentioned_by_staff_id INT NOT NULL,
|
||
is_read TINYINT(1) NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (mentioned_staff_id) REFERENCES staff(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (mentioned_by_staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS ticket_time_entries (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL,
|
||
staff_id INT NOT NULL,
|
||
minutes INT NOT NULL,
|
||
note VARCHAR(255) NULL,
|
||
logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS recurring_tickets (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
subject VARCHAR(255) NOT NULL,
|
||
message TEXT NOT NULL,
|
||
department_id INT NOT NULL,
|
||
priority_id INT NOT NULL,
|
||
assigned_staff_id INT NULL,
|
||
interval_days INT NOT NULL DEFAULT 30,
|
||
next_run DATE NOT NULL,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
created_by INT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (priority_id) REFERENCES priorities(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (assigned_staff_id) REFERENCES staff(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (created_by) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ── Interner Chat ─────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS staff_chat_messages (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
from_staff_id INT NOT NULL,
|
||
to_staff_id INT NOT NULL,
|
||
message TEXT NOT NULL,
|
||
is_read TINYINT(1) NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (from_staff_id) REFERENCES staff(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (to_staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ── Kunden-Notizen ────────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS customer_notes (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
staff_id INT NULL,
|
||
note TEXT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ── Backup-Einstellungen ──────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS backup_settings (
|
||
key_name VARCHAR(80) PRIMARY KEY,
|
||
value TEXT NULL,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ── Ticket-Bewertungen ────────────────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS ticket_ratings (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
ticket_id INT NOT NULL UNIQUE,
|
||
user_id INT NULL,
|
||
rating TINYINT NOT NULL CHECK(rating BETWEEN 1 AND 5),
|
||
comment TEXT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ── Ticket-Präsenz (wer schaut gerade auf welches Ticket) ─────────────────
|
||
CREATE TABLE IF NOT EXISTS ticket_presence (
|
||
ticket_id INT NOT NULL,
|
||
staff_id INT NOT NULL,
|
||
last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (ticket_id, staff_id),
|
||
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|