OpenNIT
Quelltext des aktuellen Standes
Fassung 1.1.1
001_initial_schema.sql
497 KBNur die ersten 5000 Zeilen. Vollständig steht die Datei im Download.
-- Initiales Datenbankschema (konsolidiert aus allen Migrationen)
-- ============================================================
-- IT-Dokumentationssystem - Vollstaendiges Datenbankschema
-- Version: 2.0.0
--
-- Enthaelt ALLE Tabellen fuer eine saubere Neuinstallation:
-- Kern-System, Bestell-Board, IT-Services, Adressbuch,
-- Seitenverknuepfungen, E-Mail-Benachrichtigungen, User-Settings.
--
-- SICHERHEITSHINWEISE:
-- - Revisionssichere Audit-Logs mit Hash-Chain
-- - Seitenversionen sind unveraenderbar (Trigger-geschuetzt)
-- - Audit-Log ist append-only (Trigger-geschuetzt)
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- ============================================================
-- BENUTZERVERWALTUNG
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`entra_object_id` CHAR(36) NULL COMMENT 'Azure AD Object ID (GUID) - NULL fuer lokale Benutzer',
`email` VARCHAR(255) NOT NULL,
`display_name` VARCHAR(255) NOT NULL,
`password_hash` VARCHAR(255) NULL COMMENT 'Argon2ID Hash fuer lokalen Login - NULL fuer Azure AD',
`is_active` BOOLEAN NOT NULL DEFAULT TRUE,
`is_admin` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Direkte Admin-Flag fuer initiale Einrichtung',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_login` DATETIME NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_users_email` (`email`),
INDEX `idx_users_entra_object_id` (`entra_object_id`),
INDEX `idx_users_active` (`is_active`),
INDEX `idx_users_admin` (`is_admin`),
INDEX `idx_users_last_login` (`last_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Benutzer aus Microsoft Entra ID oder lokale Benutzer';
-- Rollen
CREATE TABLE IF NOT EXISTS `roles` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`description` TEXT NOT NULL COMMENT 'Audit-taugliche Beschreibung',
`is_admin` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Globale Admin-Rolle',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_roles_name` (`name`),
INDEX `idx_roles_admin` (`is_admin`),
CONSTRAINT `fk_roles_created_by` FOREIGN KEY (`created_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Rollen fuer Berechtigungsbuendelung';
-- Capabilities (einzelne Berechtigungen)
CREATE TABLE IF NOT EXISTS `capabilities` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`description` TEXT NOT NULL COMMENT 'Menschenlesbare Beschreibung fuer Audit',
`category` VARCHAR(50) NOT NULL,
`risk_level` ENUM('low', 'medium', 'high', 'critical') NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_capabilities_name` (`name`),
INDEX `idx_capabilities_category` (`category`),
INDEX `idx_capabilities_risk` (`risk_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Atomare Berechtigungen (Capabilities)';
-- Rollen-Capabilities Zuordnung
CREATE TABLE IF NOT EXISTS `role_capabilities` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`role_id` BIGINT UNSIGNED NOT NULL,
`capability_id` BIGINT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_role_capabilities` (`role_id`, `capability_id`),
CONSTRAINT `fk_role_capabilities_role` FOREIGN KEY (`role_id`)
REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_role_capabilities_capability` FOREIGN KEY (`capability_id`)
REFERENCES `capabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_role_capabilities_created_by` FOREIGN KEY (`created_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Zuordnung Capabilities zu Rollen';
-- ============================================================
-- KERN-CAPABILITIES (Doku/Dateien/Export/Workflow/Admin)
-- ============================================================
-- Diese Rechte wurden historisch AUSSCHLIESSLICH ueber den Seed
-- (database/seeds/001_initial_data.sql) angelegt – den der Migrations-Runner
-- aber nie ausfuehrt. Dadurch konnten sie auf Installationen fehlen, die nicht
-- frisch ueber install.php aufgesetzt wurden (Symptom: „Doku" unsichtbar,
-- /topics → „Zugriff verweigert", keine Themengebiete anlegbar). Sie werden
-- daher hier idempotent (INSERT IGNORE) mitgefuehrt, damit 001 fuer sich allein
-- vollstaendig ist und nicht mehr nur am Seed bzw. an Migration 019 haengt.
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('read_page', 'Berechtigung zum Lesen von Seiteninhalten und Metadaten', 'page', 'low'),
('create_page', 'Berechtigung zum Erstellen neuer Dokumentationsseiten', 'page', 'medium'),
('edit_page', 'Berechtigung zum Bearbeiten bestehender Seiteninhalte', 'page', 'medium'),
('delete_page', 'Berechtigung zum Archivieren/Löschen von Seiten', 'page', 'high'),
('restore_version', 'Berechtigung zum Wiederherstellen alter Seitenversionen', 'page', 'medium'),
('view_versions', 'Berechtigung zum Einsehen der Versionshistorie', 'page', 'low'),
('upload_file', 'Berechtigung zum Hochladen von Dateianhängen', 'file', 'medium'),
('download_file', 'Berechtigung zum Herunterladen von Dateianhängen', 'file', 'low'),
('delete_file', 'Berechtigung zum Entfernen von Dateianhängen', 'file', 'high'),
('export_pdf', 'Berechtigung zum Exportieren einzelner Seiten als PDF', 'export', 'low'),
('export_zip', 'Berechtigung zum Exportieren von Bereichen als ZIP-Archiv', 'export', 'medium'),
('export_zip_encrypted', 'Berechtigung zum Erstellen verschlüsselter ZIP-Exporte', 'export', 'medium'),
('approve_changes', 'Berechtigung zum Freigeben von Änderungen', 'workflow', 'high'),
('manage_responsibilities', 'Berechtigung zum Verwalten von Verantwortlichkeiten und Prüfzyklen', 'workflow', 'high'),
('view_audit_log', 'Berechtigung zum Einsehen des Audit-Logs', 'admin', 'high'),
('manage_topic_permissions','Berechtigung zum Verwalten von Berechtigungen innerhalb eines Themengebiets', 'admin', 'high'),
('manage_users', 'Berechtigung zur globalen Benutzerverwaltung', 'admin', 'critical'),
('manage_roles', 'Berechtigung zur Verwaltung von Rollen und deren Capabilities', 'admin', 'critical'),
('manage_all_permissions', 'Berechtigung zur globalen Rechteverwaltung', 'admin', 'critical'),
('system_admin', 'Vollstaendige Systemadministrationsrechte', 'admin', 'critical');
-- ============================================================
-- THEMENGEBIETE (TOPICS)
-- ============================================================
CREATE TABLE IF NOT EXISTS `topics` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_id` BIGINT UNSIGNED NULL COMMENT 'NULL = Root-Topic',
`name` VARCHAR(255) NOT NULL,
`slug` VARCHAR(100) NOT NULL,
`path` VARCHAR(1000) NOT NULL COMMENT 'Materialisierter Pfad z.B. /1/5/12/',
`depth` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`sort_order` INT NOT NULL DEFAULT 0,
`description` TEXT NULL,
`image_path` VARCHAR(500) NULL COMMENT 'Pfad zum Topic-Bild',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` BIGINT UNSIGNED NULL,
`updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
`is_archived` BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_topics_slug_parent` (`parent_id`, `slug`),
INDEX `idx_topics_path` (`path`(255)),
INDEX `idx_topics_parent` (`parent_id`),
INDEX `idx_topics_sort` (`sort_order`),
INDEX `idx_topics_archived` (`is_archived`),
CONSTRAINT `fk_topics_parent` FOREIGN KEY (`parent_id`)
REFERENCES `topics` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_topics_created_by` FOREIGN KEY (`created_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Hierarchische Themengebiete';
-- ============================================================
-- BERECHTIGUNGSZUWEISUNGEN
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_role_topics` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL,
`role_id` BIGINT UNSIGNED NOT NULL,
`topic_id` BIGINT UNSIGNED NULL COMMENT 'NULL = globale Zuweisung',
`granted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`granted_by` BIGINT UNSIGNED NULL COMMENT 'NULL nur fuer System/Installer-Zuweisungen',
`valid_until` DATE NULL COMMENT 'Ablaufdatum (optional)',
`grant_reason` TEXT NOT NULL COMMENT 'Begruendung fuer Audit',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_role_topics` (`user_id`, `role_id`, `topic_id`),
INDEX `idx_urt_user` (`user_id`),
INDEX `idx_urt_role` (`role_id`),
INDEX `idx_urt_topic` (`topic_id`),
INDEX `idx_urt_valid` (`valid_until`),
INDEX `idx_urt_granted_at` (`granted_at`),
CONSTRAINT `fk_urt_user` FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_urt_role` FOREIGN KEY (`role_id`)
REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_urt_topic` FOREIGN KEY (`topic_id`)
REFERENCES `topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_urt_granted_by` FOREIGN KEY (`granted_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Explizite Rechtezuweisung pro Benutzer/Rolle/Topic';
-- ============================================================
-- SEITEN UND VERSIONEN
-- ============================================================
CREATE TABLE IF NOT EXISTS `pages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`parent_page_id` BIGINT UNSIGNED NULL COMMENT 'Fuer Unterseiten',
`title` VARCHAR(500) NOT NULL,
`slug` VARCHAR(100) NOT NULL,
`path` VARCHAR(1000) NOT NULL COMMENT 'Materialisierter Pfad',
`current_version_id` BIGINT UNSIGNED NULL COMMENT 'Aktuelle Version',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` BIGINT UNSIGNED NULL,
`updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
`is_archived` BOOLEAN NOT NULL DEFAULT FALSE,
`review_status` ENUM('aktuell', 'prüfpflichtig', 'überfällig') NOT NULL DEFAULT 'aktuell',
`ansprechpartner_type` ENUM('contact','company') NULL DEFAULT NULL,
`ansprechpartner_id` BIGINT UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_pages_slug_topic` (`topic_id`, `parent_page_id`, `slug`),
INDEX `idx_pages_path` (`path`(255)),
INDEX `idx_pages_topic` (`topic_id`),
INDEX `idx_pages_parent` (`parent_page_id`),
INDEX `idx_pages_archived` (`is_archived`),
INDEX `idx_pages_review_status` (`review_status`),
INDEX `idx_pages_ansprechpartner` (`ansprechpartner_type`, `ansprechpartner_id`),
FULLTEXT INDEX `ft_pages_title` (`title`),
CONSTRAINT `fk_pages_topic` FOREIGN KEY (`topic_id`)
REFERENCES `topics` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_pages_parent` FOREIGN KEY (`parent_page_id`)
REFERENCES `pages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_pages_created_by` FOREIGN KEY (`created_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Dokumentationsseiten';
-- Seitenversionen (IMMUTABLE - keine UPDATE/DELETE erlaubt)
CREATE TABLE IF NOT EXISTS `page_versions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`page_id` BIGINT UNSIGNED NOT NULL,
`version_number` INT UNSIGNED NOT NULL,
`content_html` LONGTEXT NOT NULL,
`content_plaintext` LONGTEXT NOT NULL COMMENT 'Fuer Volltextsuche',
`change_reason` TEXT NULL COMMENT 'Aenderungsgrund',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` BIGINT UNSIGNED NOT NULL,
`content_hash` CHAR(64) NOT NULL COMMENT 'SHA-256 des Inhalts',
`previous_version_id` BIGINT UNSIGNED NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_page_versions_number` (`page_id`, `version_number`),
INDEX `idx_versions_page` (`page_id`),
INDEX `idx_versions_created` (`created_at`),
INDEX `idx_versions_author` (`created_by`),
FULLTEXT INDEX `ft_versions_content` (`content_plaintext`),
CONSTRAINT `fk_versions_page` FOREIGN KEY (`page_id`)
REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_versions_author` FOREIGN KEY (`created_by`)
REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_versions_previous` FOREIGN KEY (`previous_version_id`)
REFERENCES `page_versions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Unveraenderbare Seitenversionen';
-- Fremdschluessel fuer current_version_id (nach Erstellung von page_versions)
ALTER TABLE `pages`
ADD CONSTRAINT `fk_pages_current_version` FOREIGN KEY (`current_version_id`)
REFERENCES `page_versions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- Seitenverknuepfungen (bidirektional)
CREATE TABLE IF NOT EXISTS `page_links` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`page_id` BIGINT UNSIGNED NOT NULL,
`linked_page_id` BIGINT UNSIGNED NOT NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT `fk_page_links_page` FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_page_links_linked` FOREIGN KEY (`linked_page_id`) REFERENCES `pages`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_page_links_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
UNIQUE KEY `uq_page_link` (`page_id`, `linked_page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Bidirektionale Seitenverknuepfungen';
-- ============================================================
-- VERANTWORTLICHKEITEN
-- ============================================================
CREATE TABLE IF NOT EXISTS `page_responsibilities` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`page_id` BIGINT UNSIGNED NOT NULL,
`technical_owner` BIGINT UNSIGNED NULL COMMENT 'Technisch Verantwortlicher',
`business_owner` BIGINT UNSIGNED NULL COMMENT 'Fachlich Verantwortlicher',
`last_review_date` DATE NULL,
`review_interval_days` INT UNSIGNED NOT NULL DEFAULT 365,
`next_review_date` DATE GENERATED ALWAYS AS (
DATE_ADD(`last_review_date`, INTERVAL `review_interval_days` DAY)
) STORED,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
`updated_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_responsibilities_page` (`page_id`),
INDEX `idx_resp_tech_owner` (`technical_owner`),
INDEX `idx_resp_business_owner` (`business_owner`),
INDEX `idx_resp_next_review` (`next_review_date`),
CONSTRAINT `fk_resp_page` FOREIGN KEY (`page_id`)
REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_resp_tech_owner` FOREIGN KEY (`technical_owner`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_resp_business_owner` FOREIGN KEY (`business_owner`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_resp_updated_by` FOREIGN KEY (`updated_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Verantwortlichkeiten und Pruefzyklen';
-- ============================================================
-- DATEIANHÄNGE
-- ============================================================
CREATE TABLE IF NOT EXISTS `page_files` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`page_id` BIGINT UNSIGNED NOT NULL,
`version_id` BIGINT UNSIGNED NULL COMMENT 'Version bei Upload',
`original_filename` VARCHAR(255) NOT NULL,
`stored_filename` CHAR(36) NOT NULL COMMENT 'UUID fuer Speicherung',
`file_path` VARCHAR(500) NOT NULL COMMENT 'Relativer Pfad im Storage',
`file_size` BIGINT UNSIGNED NOT NULL,
`mime_type` VARCHAR(100) NOT NULL,
`file_hash` CHAR(64) NOT NULL COMMENT 'SHA-256',
`uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uploaded_by` BIGINT UNSIGNED NOT NULL,
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
`deleted_at` DATETIME NULL,
`deleted_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`id`),
INDEX `idx_files_page` (`page_id`),
INDEX `idx_files_version` (`version_id`),
INDEX `idx_files_deleted` (`is_deleted`),
INDEX `idx_files_hash` (`file_hash`),
FULLTEXT INDEX `ft_files_name` (`original_filename`),
CONSTRAINT `fk_files_page` FOREIGN KEY (`page_id`)
REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_files_version` FOREIGN KEY (`version_id`)
REFERENCES `page_versions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_files_uploaded_by` FOREIGN KEY (`uploaded_by`)
REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_files_deleted_by` FOREIGN KEY (`deleted_by`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Dateianhänge zu Seiten';
-- ============================================================
-- BESTELL-BOARD (ORDERS)
-- ============================================================
CREATE TABLE IF NOT EXISTS `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(500) NOT NULL COMMENT 'Titel der Bestellung',
`info` TEXT NOT NULL COMMENT 'Referenz / Info (Kostenstelle, Ticket-ID, etc.)',
`status` ENUM('open', 'delivered', 'closed') NOT NULL DEFAULT 'open',
`is_done` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Alle Komponenten erledigt?',
`created_by` BIGINT UNSIGNED NULL COMMENT 'Ersteller (User-ID)',
`delivered_by` BIGINT UNSIGNED NULL COMMENT 'Ausgeliefert von (User-ID)',
`delivered_at` DATETIME NULL COMMENT 'Zeitpunkt der Auslieferung',
`delivery_method` VARCHAR(100) NULL COMMENT 'Lieferart (Hauspost, Abgeholt, Freitext)',
`delivery_note` TEXT NULL COMMENT 'Notiz zur Lieferung',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_orders_status` (`status`),
INDEX `idx_orders_is_done` (`is_done`),
INDEX `idx_orders_created_by` (`created_by`),
INDEX `idx_orders_created_at` (`created_at`),
CONSTRAINT `fk_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_orders_delivered_by` FOREIGN KEY (`delivered_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Bestellungen des Bestell-Boards';
CREATE TABLE IF NOT EXISTS `order_components` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Zugehoerige Bestellung',
`name` VARCHAR(500) NOT NULL COMMENT 'Komponentenname',
`is_done` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Erledigt?',
`done_by` BIGINT UNSIGNED NULL COMMENT 'Erledigt von (User-ID)',
`done_at` DATETIME NULL COMMENT 'Zeitpunkt der Erledigung',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_order_components_order_id` (`order_id`),
INDEX `idx_order_components_is_done` (`is_done`),
CONSTRAINT `fk_order_components_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_order_components_done_by` FOREIGN KEY (`done_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Komponenten einer Bestellung';
CREATE TABLE IF NOT EXISTS `order_info_changes` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Zugehoerige Bestellung',
`old_info` TEXT NULL COMMENT 'Vorheriger Info-Text',
`new_info` TEXT NOT NULL COMMENT 'Neuer Info-Text',
`changed_by` BIGINT UNSIGNED NOT NULL COMMENT 'Geaendert von (User-ID)',
`changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_order_info_changes_order_id` (`order_id`),
CONSTRAINT `fk_order_info_changes_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_order_info_changes_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Aenderungsverlauf der Bestellungs-Info';
-- ============================================================
-- IT-SERVICES (STARTBOARD)
-- ============================================================
CREATE TABLE IF NOT EXISTS `service_categories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COMMENT 'Kategoriename',
`color` VARCHAR(7) NULL COMMENT 'Hex-Farbe z.B. #38bdf8',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_service_categories_name` (`name`),
INDEX `idx_service_categories_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Kategorien fuer IT-Services';
CREATE TABLE IF NOT EXISTS `services` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(500) NOT NULL COMMENT 'Service-Name',
`url` VARCHAR(2000) NOT NULL COMMENT 'Service-URL',
`icon` VARCHAR(500) NULL COMMENT 'Dateiname in uploads/service-icons/',
`category_id` BIGINT UNSIGNED NULL COMMENT 'Zugehoerige Kategorie',
`reachability` ENUM('internal', 'external') NULL COMMENT 'Erreichbarkeit: intern oder extern',
`sort_order` INT NOT NULL DEFAULT 0,
`created_by` BIGINT UNSIGNED NULL COMMENT 'Ersteller (User-ID)',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_services_category` (`category_id`),
INDEX `idx_services_sort` (`sort_order`),
CONSTRAINT `fk_services_category` FOREIGN KEY (`category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_services_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='IT-Service-Links';
CREATE TABLE IF NOT EXISTS `user_pinned_services` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Benutzer',
`service_id` BIGINT UNSIGNED NOT NULL COMMENT 'Angepinnter Service',
`sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Reihenfolge der Pins',
`pinned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_service` (`user_id`, `service_id`),
INDEX `idx_pinned_user` (`user_id`),
CONSTRAINT `fk_pins_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pins_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Angepinnte Services pro Benutzer';
-- ============================================================
-- ADRESSBUCH (FIRMEN & KONTAKTE)
-- ============================================================
CREATE TABLE IF NOT EXISTS `companies` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`customer_number` VARCHAR(100) NULL,
`address` TEXT NULL,
`phone` VARCHAR(50) NULL,
`email` VARCHAR(255) NULL,
`website` VARCHAR(255) NULL,
`notes` TEXT NULL,
`image_path` VARCHAR(255) NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_companies_name` (`name`),
INDEX `idx_companies_customer_number` (`customer_number`),
CONSTRAINT `fk_companies_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Firmen im Adressbuch';
CREATE TABLE IF NOT EXISTS `contacts` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`company_id` BIGINT UNSIGNED NOT NULL,
`first_name` VARCHAR(100) NOT NULL,
`last_name` VARCHAR(100) NOT NULL,
`position` VARCHAR(255) NULL,
`phone` VARCHAR(50) NULL,
`mobile` VARCHAR(50) NULL,
`email` VARCHAR(255) NULL,
`notes` TEXT NULL,
`image_path` VARCHAR(255) NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_contacts_company` (`company_id`),
INDEX `idx_contacts_name` (`last_name`, `first_name`),
CONSTRAINT `fk_contacts_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_contacts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Kontakte im Adressbuch';
-- ============================================================
-- BENUTZEREINSTELLUNGEN
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_settings` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`setting_key` VARCHAR(100) NOT NULL,
`setting_value` VARCHAR(500) NULL,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uk_user_setting` (`user_id`, `setting_key`),
CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Benutzereinstellungen (Key-Value)';
-- ============================================================
-- E-MAIL-BENACHRICHTIGUNGEN
-- ============================================================
CREATE TABLE IF NOT EXISTS `email_templates` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`template_key` VARCHAR(100) NOT NULL UNIQUE,
`subject` VARCHAR(255) NOT NULL,
`body_html` TEXT NOT NULL,
`description` VARCHAR(255) DEFAULT NULL,
`updated_by` BIGINT UNSIGNED NULL,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `fk_email_templates_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='E-Mail-Vorlagen';
CREATE TABLE IF NOT EXISTS `notification_queue` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`template_key` VARCHAR(100) NOT NULL,
`variables_json` TEXT NOT NULL,
`status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
`error_message` TEXT DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`sent_at` DATETIME DEFAULT NULL,
CONSTRAINT `fk_notification_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Benachrichtigungs-Warteschlange';
-- ============================================================
-- AUDIT LOG (APPEND-ONLY, HASH-CHAIN)
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`event_id` CHAR(36) NOT NULL COMMENT 'UUID fuer Event',
`event_timestamp` DATETIME(6) NOT NULL COMMENT 'Mikrosekunden-Praezision',
`user_id` BIGINT UNSIGNED NULL COMMENT 'NULL fuer System-Events',
`session_id` VARCHAR(128) NULL,
`ip_address` VARCHAR(45) NULL COMMENT 'IPv4/IPv6',
`user_agent` VARCHAR(500) NULL,
`action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service'
) NOT NULL,
`action_type` VARCHAR(50) NOT NULL COMMENT 'z.B. login, create_page, upload_file',
`object_type` VARCHAR(50) NULL COMMENT 'z.B. page, topic, user',
`object_id` BIGINT UNSIGNED NULL,
`object_name` VARCHAR(500) NULL COMMENT 'Menschenlesbarer Name',
`details_json` JSON NULL COMMENT 'Zusaetzliche Details',
`old_value_json` JSON NULL COMMENT 'Wert vor Aenderung',
`new_value_json` JSON NULL COMMENT 'Wert nach Aenderung',
`result` ENUM('success', 'failure', 'denied') NOT NULL,
`risk_level` ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'low',
`checksum` CHAR(64) NOT NULL COMMENT 'SHA-256 Hash-Chain',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_audit_event_id` (`event_id`),
INDEX `idx_audit_timestamp` (`event_timestamp`),
INDEX `idx_audit_user` (`user_id`, `event_timestamp`),
INDEX `idx_audit_object` (`object_type`, `object_id`),
INDEX `idx_audit_action` (`action_category`, `action_type`),
INDEX `idx_audit_result` (`result`),
INDEX `idx_audit_risk` (`risk_level`, `event_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Revisionssicheres Audit-Log mit Hash-Chain';
-- ============================================================
-- SESSIONS (fuer Audit-Tracking)
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_sessions` (
`id` VARCHAR(128) NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`ip_address` VARCHAR(45) NOT NULL,
`user_agent` VARCHAR(500) NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_activity` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expires_at` DATETIME NOT NULL,
`is_valid` BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (`id`),
INDEX `idx_sessions_user` (`user_id`),
INDEX `idx_sessions_expires` (`expires_at`),
INDEX `idx_sessions_valid` (`is_valid`),
CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Aktive Benutzersessions';
-- ============================================================
-- RATE LIMITING
-- ============================================================
CREATE TABLE IF NOT EXISTS `rate_limits` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`identifier` VARCHAR(255) NOT NULL COMMENT 'IP oder User-ID',
`endpoint` VARCHAR(100) NOT NULL,
`requests` INT UNSIGNED NOT NULL DEFAULT 1,
`window_start` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_rate_limits` (`identifier`, `endpoint`, `window_start`),
INDEX `idx_rate_limits_window` (`window_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Rate Limiting Tracking';
-- ============================================================
-- VIEWS FUER AUDIT-REPORTS
-- ============================================================
CREATE OR REPLACE VIEW `v_permission_matrix` AS
SELECT
u.id AS user_id,
u.email AS user_email,
u.display_name AS user_name,
u.is_active AS user_active,
r.id AS role_id,
r.name AS role_name,
r.description AS role_description,
r.is_admin AS is_global_admin,
t.id AS topic_id,
t.name AS topic_name,
t.path AS topic_path,
CASE
WHEN urt.topic_id IS NULL THEN 'GLOBAL'
ELSE 'TOPIC-SPECIFIC'
END AS permission_scope,
c.id AS capability_id,
c.name AS capability_name,
c.description AS capability_description,
c.category AS capability_category,
c.risk_level AS capability_risk_level,
urt.granted_at,
urt.valid_until,
urt.grant_reason,
grantor.email AS granted_by_email,
CASE
WHEN urt.valid_until IS NOT NULL AND urt.valid_until < CURDATE()
THEN 'EXPIRED'
WHEN u.is_active = FALSE
THEN 'USER_INACTIVE'
ELSE 'ACTIVE'
END AS permission_status
FROM users u
INNER JOIN user_role_topics urt ON u.id = urt.user_id
INNER JOIN roles r ON urt.role_id = r.id
INNER JOIN role_capabilities rc ON r.id = rc.role_id
INNER JOIN capabilities c ON rc.capability_id = c.id
LEFT JOIN topics t ON urt.topic_id = t.id
LEFT JOIN users grantor ON urt.granted_by = grantor.id
ORDER BY
u.email,
t.path,
c.category,
c.name;
CREATE OR REPLACE VIEW `v_review_status` AS
SELECT
p.id AS page_id,
p.title AS page_title,
p.path AS page_path,
t.name AS topic_name,
pr.last_review_date,
pr.review_interval_days,
pr.next_review_date,
tech.display_name AS technical_owner_name,
tech.email AS technical_owner_email,
biz.display_name AS business_owner_name,
biz.email AS business_owner_email,
CASE
WHEN pr.next_review_date IS NULL THEN 'prüfpflichtig'
WHEN pr.next_review_date < CURDATE() THEN 'überfällig'
WHEN pr.next_review_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 'prüfpflichtig'
ELSE 'aktuell'
END AS calculated_status,
DATEDIFF(pr.next_review_date, CURDATE()) AS days_until_review
FROM pages p
LEFT JOIN page_responsibilities pr ON p.id = pr.page_id
LEFT JOIN topics t ON p.topic_id = t.id
LEFT JOIN users tech ON pr.technical_owner = tech.id
LEFT JOIN users biz ON pr.business_owner = biz.id
WHERE p.is_archived = FALSE;
CREATE OR REPLACE VIEW `v_audit_log_detailed` AS
SELECT
al.id,
al.event_id,
al.event_timestamp,
al.user_id,
u.email AS user_email,
u.display_name AS user_name,
al.session_id,
al.ip_address,
al.user_agent,
al.action_category,
al.action_type,
al.object_type,
al.object_id,
al.object_name,
al.details_json,
al.old_value_json,
al.new_value_json,
al.result,
al.risk_level,
al.checksum
FROM audit_log al
LEFT JOIN users u ON al.user_id = u.id;
-- ============================================================
-- TRIGGER FUER DATENINTEGRITAET
--
-- Hinweis: Einzeilige SIGNAL-Syntax (ohne BEGIN...END/DELIMITER)
-- damit sie sowohl im mysql-Client als auch via PDO funktionieren.
-- Topic-Trigger wurden entfernt — Pfadberechnung erfolgt im PHP-Code.
-- DROP TRIGGER IF EXISTS vor jedem CREATE macht die Migration idempotent.
-- Hinweis: Auf Shared-Hosting mit aktivem Binary-Log und ohne SUPER-Privileg
-- schlaegt CREATE TRIGGER mit Fehler 1419 fehl. Das wird im UpdateManager
-- als nicht-fatal behandelt (Trigger existieren dann bereits).
-- ============================================================
-- Revisionssicherheit: page_versions
DROP TRIGGER IF EXISTS `tr_page_versions_no_update`;
CREATE TRIGGER `tr_page_versions_no_update`
BEFORE UPDATE ON `page_versions`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPDATE auf page_versions ist nicht erlaubt (Revisionssicherheit)';
DROP TRIGGER IF EXISTS `tr_page_versions_no_delete`;
CREATE TRIGGER `tr_page_versions_no_delete`
BEFORE DELETE ON `page_versions`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE auf page_versions ist nicht erlaubt (Revisionssicherheit)';
-- Revisionssicherheit: audit_log
DROP TRIGGER IF EXISTS `tr_audit_log_no_update`;
CREATE TRIGGER `tr_audit_log_no_update`
BEFORE UPDATE ON `audit_log`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPDATE auf audit_log ist nicht erlaubt (Revisionssicherheit)';
DROP TRIGGER IF EXISTS `tr_audit_log_no_delete`;
CREATE TRIGGER `tr_audit_log_no_delete`
BEFORE DELETE ON `audit_log`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE auf audit_log ist nicht erlaubt (Revisionssicherheit)';
SET FOREIGN_KEY_CHECKS = 1;
-- Migration: 002_fix_topic_triggers.sql
-- ============================================================
-- Migration: Topic-Trigger korrigieren
--
-- Der BEFORE INSERT Trigger versucht NEW.id zu verwenden,
-- was bei AUTO_INCREMENT noch nicht existiert.
-- Die Pfad-Berechnung wird stattdessen im PHP-Code gehandhabt.
-- ============================================================
-- Trigger entfernen (falls vorhanden)
DROP TRIGGER IF EXISTS `tr_topics_before_insert`;
DROP TRIGGER IF EXISTS `tr_topics_after_insert`;
-- Hinweis: Die Pfad-Berechnung erfolgt nun im PHP-Code (TopicManager)
-- Migration: 003_add_topic_image.sql
-- ============================================================
-- Migration: Topic-Bilder hinzufügen
-- Version: 1.0.3
-- ============================================================
-- Spalte für Bild-Pfad hinzufügen
ALTER TABLE `topics`
ADD COLUMN `image_path` VARCHAR(500) NULL COMMENT 'Pfad zum Topic-Bild' AFTER `description`;
-- Migration: 004_orders_system.sql
-- ============================================================
-- Migration: Bestell-Board (Order Tracking System)
-- Version: 1.0.4
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ============================================================
-- Tabelle: orders (Bestellungen)
-- ============================================================
CREATE TABLE IF NOT EXISTS `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(500) NOT NULL COMMENT 'Titel der Bestellung',
`info` TEXT NOT NULL COMMENT 'Referenz / Info (Kostenstelle, Ticket-ID, etc.)',
`status` ENUM('open', 'delivered', 'closed') NOT NULL DEFAULT 'open' COMMENT 'Status: open=Offen, delivered=Bereit zur Auslieferung, closed=Archiviert',
`is_done` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Alle Komponenten erledigt?',
`created_by` BIGINT UNSIGNED NULL COMMENT 'Ersteller (User-ID)',
`delivered_by` BIGINT UNSIGNED NULL COMMENT 'Ausgeliefert von (User-ID)',
`delivered_at` DATETIME NULL COMMENT 'Zeitpunkt der Auslieferung',
`delivery_method` VARCHAR(100) NULL COMMENT 'Lieferart (Hauspost, Abgeholt, Freitext)',
`delivery_note` TEXT NULL COMMENT 'Notiz zur Lieferung',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_orders_status` (`status`),
INDEX `idx_orders_is_done` (`is_done`),
INDEX `idx_orders_created_by` (`created_by`),
INDEX `idx_orders_created_at` (`created_at`),
CONSTRAINT `fk_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_orders_delivered_by` FOREIGN KEY (`delivered_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Bestellungen des Bestell-Boards';
-- ============================================================
-- Tabelle: order_components (Bestellkomponenten)
-- ============================================================
CREATE TABLE IF NOT EXISTS `order_components` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Zugehoerige Bestellung',
`name` VARCHAR(500) NOT NULL COMMENT 'Komponentenname',
`is_done` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Erledigt?',
`done_by` BIGINT UNSIGNED NULL COMMENT 'Erledigt von (User-ID)',
`done_at` DATETIME NULL COMMENT 'Zeitpunkt der Erledigung',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_order_components_order_id` (`order_id`),
INDEX `idx_order_components_is_done` (`is_done`),
CONSTRAINT `fk_order_components_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_order_components_done_by` FOREIGN KEY (`done_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Komponenten einer Bestellung';
-- ============================================================
-- Tabelle: order_info_changes (Aenderungsverlauf der Info)
-- ============================================================
CREATE TABLE IF NOT EXISTS `order_info_changes` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Zugehoerige Bestellung',
`old_info` TEXT NULL COMMENT 'Vorheriger Info-Text',
`new_info` TEXT NOT NULL COMMENT 'Neuer Info-Text',
`changed_by` BIGINT UNSIGNED NOT NULL COMMENT 'Geaendert von (User-ID)',
`changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_order_info_changes_order_id` (`order_id`),
CONSTRAINT `fk_order_info_changes_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_order_info_changes_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Aenderungsverlauf der Bestellungs-Info';
-- ============================================================
-- ENUM-Erweiterung: Kategorie 'order' fuer Capabilities
-- ============================================================
ALTER TABLE `capabilities`
MODIFY COLUMN `category` VARCHAR(50) NOT NULL;
-- ============================================================
-- Neue Capability: manage_orders
-- ============================================================
INSERT INTO `capabilities` (`name`, `description`, `category`, `risk_level`)
VALUES ('manage_orders', 'Bestellungen verwalten (Bestell-Board)', 'order', 'medium');
SET FOREIGN_KEY_CHECKS = 1;
-- Migration: 005_services_system.sql
-- ============================================================
-- Migration: IT-Services System (Startboard Integration)
-- Version: 1.0.5
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ============================================================
-- Tabelle: service_categories (Kategorien fuer IT-Services)
-- ============================================================
CREATE TABLE IF NOT EXISTS `service_categories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COMMENT 'Kategoriename',
`color` VARCHAR(7) NULL COMMENT 'Hex-Farbe z.B. #38bdf8',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_service_categories_name` (`name`),
INDEX `idx_service_categories_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Kategorien fuer IT-Services';
-- ============================================================
-- Tabelle: services (IT-Service-Links)
-- ============================================================
CREATE TABLE IF NOT EXISTS `services` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(500) NOT NULL COMMENT 'Service-Name',
`url` VARCHAR(2000) NOT NULL COMMENT 'Service-URL',
`icon` VARCHAR(500) NULL COMMENT 'Dateiname in uploads/service-icons/',
`category_id` BIGINT UNSIGNED NULL COMMENT 'Zugehoerige Kategorie',
`reachability` ENUM('internal', 'external') NULL COMMENT 'Erreichbarkeit: intern oder extern',
`sort_order` INT NOT NULL DEFAULT 0,
`created_by` BIGINT UNSIGNED NULL COMMENT 'Ersteller (User-ID)',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_services_category` (`category_id`),
INDEX `idx_services_sort` (`sort_order`),
CONSTRAINT `fk_services_category` FOREIGN KEY (`category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_services_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='IT-Service-Links';
-- ============================================================
-- Tabelle: user_pinned_services (User-Pins/Favoriten)
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_pinned_services` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Benutzer',
`service_id` BIGINT UNSIGNED NOT NULL COMMENT 'Angepinnter Service',
`sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Reihenfolge der Pins',
`pinned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_service` (`user_id`, `service_id`),
INDEX `idx_pinned_user` (`user_id`),
CONSTRAINT `fk_pins_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pins_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Angepinnte Services pro Benutzer';
-- ============================================================
-- ENUM-Erweiterung: Kategorie 'service' fuer Capabilities
-- ============================================================
ALTER TABLE `capabilities`
MODIFY COLUMN `category` VARCHAR(50) NOT NULL;
-- ============================================================
-- Neue Capabilities: view_services, manage_services
-- ============================================================
INSERT INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_services', 'IT-Services ansehen und nutzen', 'service', 'low'),
('manage_services', 'IT-Services und Kategorien verwalten (anlegen, bearbeiten, loeschen)', 'service', 'medium');
SET FOREIGN_KEY_CHECKS = 1;
-- Migration: 006_contacts_system.sql
-- ============================================================
-- Migration 006: Adressbuch-System (Firmen & Kontakte)
-- ============================================================
-- Firmen
CREATE TABLE IF NOT EXISTS `companies` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`address` TEXT NULL,
`phone` VARCHAR(50) NULL,
`email` VARCHAR(255) NULL,
`website` VARCHAR(255) NULL,
`notes` TEXT NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_companies_name` (`name`),
CONSTRAINT `fk_companies_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Kontakte (gehoeren zu Firmen)
CREATE TABLE IF NOT EXISTS `contacts` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`company_id` BIGINT UNSIGNED NOT NULL,
`first_name` VARCHAR(100) NOT NULL,
`last_name` VARCHAR(100) NOT NULL,
`position` VARCHAR(255) NULL,
`phone` VARCHAR(50) NULL,
`mobile` VARCHAR(50) NULL,
`email` VARCHAR(255) NULL,
`notes` TEXT NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_contacts_company` (`company_id`),
INDEX `idx_contacts_name` (`last_name`, `first_name`),
CONSTRAINT `fk_contacts_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_contacts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities-ENUM erweitern
ALTER TABLE `capabilities`
MODIFY COLUMN `category` VARCHAR(50) NOT NULL;
-- Berechtigungen
INSERT INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_contacts', 'Adressbuch ansehen', 'contact', 'low'),
('manage_contacts', 'Kontakte und Firmen verwalten', 'contact', 'medium');
-- Migration: 007_contacts_images.sql
-- ============================================================
-- Migration 007: Bild-Upload fuer Firmen und Kontakte
-- ============================================================
ALTER TABLE `companies` ADD COLUMN `image_path` VARCHAR(255) NULL AFTER `notes`;
ALTER TABLE `contacts` ADD COLUMN `image_path` VARCHAR(255) NULL AFTER `notes`;
-- Migration: 008_audit_contact_category.sql
-- ============================================================
-- Migration 008: Audit-Log action_category um 'contact' erweitern
-- Hinweis: Enthaelt alle bekannten Werte (inkl. spaeterer Migrationen),
-- damit ein Rerun-All keine bestehenden Eintraege abschneidet (Data truncated 1265).
-- ============================================================
ALTER TABLE `audit_log`
MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- Migration: 009_fix_upload_urls.sql
-- ============================================================
-- Migration 009: Upload-URLs in page_versions reparieren
--
-- Problem: DOMDocument hat beim HTML-Purifying URLs manipuliert:
-- /uploads/ wurde zu ../uploads/, ./uploads/ oder uploads/
-- Dadurch werden Bilder relativ zur Seiten-URL aufgeloest
-- und nicht gefunden.
--
-- Fix: Alle Varianten zu /uploads/ normalisieren.
-- ============================================================
-- ../uploads/ -> /uploads/ (haeufigster Fall durch DOMDocument)
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'src="../uploads/', 'src="/uploads/')
WHERE `content_html` LIKE '%src="../uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "src='../uploads/", "src='/uploads/")
WHERE `content_html` LIKE "%src='../uploads/%";
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'href="../uploads/', 'href="/uploads/')
WHERE `content_html` LIKE '%href="../uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "href='../uploads/", "href='/uploads/")
WHERE `content_html` LIKE "%href='../uploads/%";
-- ../../uploads/ -> /uploads/ (doppelt verschachtelte Pfade)
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'src="../../uploads/', 'src="/uploads/')
WHERE `content_html` LIKE '%src="../../uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "src='../../uploads/", "src='/uploads/")
WHERE `content_html` LIKE "%src='../../uploads/%";
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'href="../../uploads/', 'href="/uploads/')
WHERE `content_html` LIKE '%href="../../uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "href='../../uploads/", "href='/uploads/")
WHERE `content_html` LIKE "%href='../../uploads/%";
-- ./uploads/ -> /uploads/
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'src="./uploads/', 'src="/uploads/')
WHERE `content_html` LIKE '%src="./uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "src='./uploads/", "src='/uploads/")
WHERE `content_html` LIKE "%src='./uploads/%";
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'href="./uploads/', 'href="/uploads/')
WHERE `content_html` LIKE '%href="./uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "href='./uploads/", "href='/uploads/")
WHERE `content_html` LIKE "%href='./uploads/%";
-- uploads/ (ohne Pfad-Prefix) -> /uploads/
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'src="uploads/', 'src="/uploads/')
WHERE `content_html` LIKE '%src="uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "src='uploads/", "src='/uploads/")
WHERE `content_html` LIKE "%src='uploads/%";
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'href="uploads/', 'href="/uploads/')
WHERE `content_html` LIKE '%href="uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "href='uploads/", "href='/uploads/")
WHERE `content_html` LIKE "%href='uploads/%";
-- Doppelte Slashes korrigieren (//uploads/ -> /uploads/)
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'src="//uploads/', 'src="/uploads/')
WHERE `content_html` LIKE '%src="//uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "src='//uploads/", "src='/uploads/")
WHERE `content_html` LIKE "%src='//uploads/%";
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, 'href="//uploads/', 'href="/uploads/')
WHERE `content_html` LIKE '%href="//uploads/%';
UPDATE `page_versions`
SET `content_html` = REPLACE(`content_html`, "href='//uploads/", "href='/uploads/")
WHERE `content_html` LIKE "%href='//uploads/%";
-- Migration: 010_audit_order_service_category.sql
-- ============================================================
-- Migration 010: Audit-Log action_category um 'order' und 'service' erweitern
-- Hinweis: Enthaelt alle bekannten Werte (inkl. spaeterer Migrationen),
-- damit ein Rerun-All keine bestehenden Eintraege abschneidet (Data truncated 1265).
-- ============================================================
ALTER TABLE `audit_log`
MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- Migration: 011_fix_capability_categories.sql
-- ============================================================
-- Migration 011: Capability-Kategorien explizit setzen
--
-- Stellt sicher, dass manage_orders, view_services und
-- manage_services in den richtigen Kategorien sind.
-- ============================================================
-- Sicherstellen dass das ENUM alle Kategorien hat
ALTER TABLE `capabilities`
MODIFY COLUMN `category` VARCHAR(50) NOT NULL;
-- manage_orders -> Kategorie 'order'
UPDATE `capabilities` SET `category` = 'order' WHERE `name` = 'manage_orders';
-- view_services und manage_services -> Kategorie 'service'
UPDATE `capabilities` SET `category` = 'service' WHERE `name` = 'view_services';
UPDATE `capabilities` SET `category` = 'service' WHERE `name` = 'manage_services';
-- view_contacts und manage_contacts -> Kategorie 'contact'
UPDATE `capabilities` SET `category` = 'contact' WHERE `name` = 'view_contacts';
UPDATE `capabilities` SET `category` = 'contact' WHERE `name` = 'manage_contacts';
-- Migration: 012_user_settings.sql
-- ============================================================
-- Migration 012: Benutzereinstellungen
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_settings` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`setting_key` VARCHAR(100) NOT NULL,
`setting_value` VARCHAR(500) NULL,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uk_user_setting` (`user_id`, `setting_key`),
CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 013_company_customer_number.sql
-- ============================================================
-- Migration 013: Kundennummer fuer Firmen
-- ============================================================
ALTER TABLE `companies` ADD COLUMN `customer_number` VARCHAR(100) NULL AFTER `name`;
CREATE INDEX `idx_companies_customer_number` ON `companies` (`customer_number`);
-- Migration: 014_page_links.sql
-- Seitenverknüpfungen (bidirektional)
CREATE TABLE IF NOT EXISTS page_links (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
page_id BIGINT UNSIGNED NOT NULL,
linked_page_id BIGINT UNSIGNED NOT NULL,
created_by BIGINT UNSIGNED,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_page_links_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
CONSTRAINT fk_page_links_linked FOREIGN KEY (linked_page_id) REFERENCES pages(id) ON DELETE CASCADE,
CONSTRAINT fk_page_links_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
UNIQUE KEY uq_page_link (page_id, linked_page_id)
) ENGINE=InnoDB;
-- Migration: 015_email_notifications.sql
-- E-Mail-Templates
CREATE TABLE IF NOT EXISTS email_templates (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
template_key VARCHAR(100) NOT NULL UNIQUE,
subject VARCHAR(255) NOT NULL,
body_html TEXT NOT NULL,
description VARCHAR(255) DEFAULT NULL,
updated_by BIGINT UNSIGNED,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_email_templates_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
-- Standard-Templates einfügen (INSERT IGNORE: bestehende angepasste Vorlagen werden nicht überschrieben)
INSERT IGNORE INTO email_templates (template_key, subject, body_html, description) VALUES
('page_changed', 'Seite geändert: {{page_title}}',
'<p>Hallo {{user_name}},</p><p>die Seite <strong>{{page_title}}</strong> im Themengebiet <strong>{{topic_name}}</strong> wurde von <strong>{{editor_name}}</strong> bearbeitet.</p><p>{{change_reason}}</p><p><a href="{{page_url}}">Seite ansehen</a></p><p>Freundliche Grüße<br>{{app_name}}</p>',
'Wird gesendet wenn eine Seite geändert wird, für die man verantwortlich ist'),
('review_due', 'Review fällig: {{page_title}}',
'<p>Hallo {{user_name}},</p><p>für die Seite <strong>{{page_title}}</strong> im Themengebiet <strong>{{topic_name}}</strong> ist heute ein Review fällig.</p><p><a href="{{page_url}}">Seite prüfen</a></p><p>Freundliche Grüße<br>{{app_name}}</p>',
'Wird gesendet wenn ein Review fällig ist');
-- Benachrichtigungs-Queue
CREATE TABLE IF NOT EXISTS notification_queue (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
template_key VARCHAR(100) NOT NULL,
variables_json TEXT NOT NULL,
status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
error_message TEXT DEFAULT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
sent_at DATETIME DEFAULT NULL,
CONSTRAINT fk_notification_queue_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- SMTP-Einstellungen werden in runtime.php gespeichert (nicht in der DB)
-- Migration: 016_page_ansprechpartner.sql
-- Ansprechpartner (Kontaktperson) fuer Seiten
-- Jede Seite kann entweder eine Firma ODER einen Kontakt als Ansprechpartner haben
ALTER TABLE pages
ADD COLUMN ansprechpartner_type ENUM('contact','company') NULL DEFAULT NULL,
ADD COLUMN ansprechpartner_id BIGINT UNSIGNED NULL DEFAULT NULL;
ALTER TABLE pages
ADD INDEX idx_pages_ansprechpartner (ansprechpartner_type, ansprechpartner_id);
-- Migration: 017_user_notifications.sql
-- Migration: In-App Benachrichtigungen
-- Erstellt am: 2026-02-28
CREATE TABLE IF NOT EXISTS user_notifications (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
type VARCHAR(50) NOT NULL DEFAULT 'info',
title VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
link VARCHAR(500) DEFAULT NULL,
is_read BOOLEAN NOT NULL DEFAULT FALSE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
read_at DATETIME DEFAULT NULL,
PRIMARY KEY (id),
INDEX idx_notif_user_unread (user_id, is_read, created_at),
INDEX idx_notif_user_created (user_id, created_at),
CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 018_notification_templates_and_tags.sql
-- Migration: Notification-Vorlagen + Tags-System
-- Erstellt am: 2026-02-28
-- ============================================================
-- In-App Notification Vorlagen
-- ============================================================
CREATE TABLE IF NOT EXISTS notification_templates (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
template_key VARCHAR(100) NOT NULL UNIQUE,
title_template VARCHAR(255) NOT NULL,
message_template TEXT NOT NULL,
icon VARCHAR(20) NOT NULL DEFAULT 'info',
type VARCHAR(50) NOT NULL DEFAULT 'info',
description VARCHAR(255) DEFAULT NULL,
is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_notif_tpl_key (template_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-Vorlagen einfuegen
INSERT INTO notification_templates (template_key, title_template, message_template, icon, type, description) VALUES
('page_changed', 'Seite bearbeitet: {{page_title}}', '{{editor_name}} hat die Seite "{{page_title}}" bearbeitet.{{change_reason_suffix}}', '📝', 'page_changed', 'Benachrichtigung wenn eine Seite bearbeitet wird, fuer die man verantwortlich ist'),
('review_due', 'Review faellig: {{page_title}}', 'Die Seite "{{page_title}}" in "{{topic_name}}" muss heute geprueft werden.', '⚠', 'review_due', 'Benachrichtigung wenn ein Review-Termin faellig ist'),
('page_created', 'Neue Seite: {{page_title}}', '{{creator_name}} hat eine neue Seite "{{page_title}}" in "{{topic_name}}" erstellt.', '📄', 'page_created', 'Benachrichtigung wenn eine neue Seite in einem eigenen Themengebiet erstellt wird'),
('page_archived', 'Seite archiviert: {{page_title}}', '{{user_name}} hat die Seite "{{page_title}}" archiviert.', '🗃', 'page_archived', 'Benachrichtigung wenn eine Seite archiviert wird, fuer die man verantwortlich ist'),
('responsibility_assigned', 'Neue Verantwortlichkeit: {{page_title}}', 'Sie wurden als {{role}} fuer die Seite "{{page_title}}" eingetragen.', '👤', 'responsibility_assigned', 'Benachrichtigung wenn man als Verantwortlicher fuer eine Seite eingetragen wird'),
('review_overdue', 'Review ueberfaellig: {{page_title}}', 'Die Seite "{{page_title}}" hat den Review-Termin ueberschritten ({{review_date}}).', '🔴', 'review_overdue', 'Benachrichtigung wenn ein Review-Termin ueberfaellig ist'),
('order_status_changed', 'Bestellung aktualisiert: {{order_title}}', 'Die Bestellung "{{order_title}}" hat einen neuen Status: {{new_status}}.', '📦', 'order_status_changed', 'Benachrichtigung wenn sich der Status einer Bestellung aendert'),
('system_announcement', '{{title}}', '{{message}}', 'ℹ', 'system', 'System-Ankuendigungen vom Administrator')
ON DUPLICATE KEY UPDATE template_key = template_key;
-- ============================================================
-- Tags-System
-- ============================================================
CREATE TABLE IF NOT EXISTS tags (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
slug VARCHAR(120) NOT NULL,
color VARCHAR(7) NOT NULL DEFAULT '#6b7280',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_tag_name (name),
UNIQUE KEY uk_tag_slug (slug),
INDEX idx_tag_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS page_tags (
page_id BIGINT UNSIGNED NOT NULL,
tag_id BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (page_id, tag_id),
INDEX idx_pt_tag (tag_id),
CONSTRAINT fk_pt_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
CONSTRAINT fk_pt_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-Tags einfuegen
INSERT INTO tags (name, slug, color) VALUES
('Wichtig', 'wichtig', '#ef4444'),
('In Arbeit', 'in-arbeit', '#f59e0b'),
('Veraltet', 'veraltet', '#9ca3af'),
('Best Practice', 'best-practice', '#22c55e'),
('SOP', 'sop', '#8b5cf6'),
('Runbook', 'runbook', '#3b82f6'),
('Architektur', 'architektur', '#06b6d4'),
('Sicherheit', 'sicherheit', '#dc2626'),
('Netzwerk', 'netzwerk', '#0891b2'),
('Monitoring', 'monitoring', '#7c3aed'),
('Backup', 'backup', '#059669'),
('FAQ', 'faq', '#d97706')
ON DUPLICATE KEY UPDATE name = name;
-- Migration: 019_page_templates.sql
-- Migration: Seiten-Vorlagen System
-- Erstellt am: 2026-02-28
CREATE TABLE IF NOT EXISTS page_templates (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
content_html LONGTEXT NOT NULL,
category VARCHAR(100) NOT NULL DEFAULT 'Allgemein',
icon VARCHAR(20) NOT NULL DEFAULT '📄',
sort_order INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_by BIGINT UNSIGNED DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_pt_slug (slug),
INDEX idx_pt_category (category),
INDEX idx_pt_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-Vorlagen einfuegen
INSERT INTO page_templates (name, slug, description, content_html, category, icon, sort_order) VALUES
(
'Server-Dokumentation',
'server-dokumentation',
'Vorlage fuer die Dokumentation eines Servers (physisch oder virtuell)',
'<h2>Serverübersicht</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Eigenschaft</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Wert</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Hostname</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">IP-Adresse(n)</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Betriebssystem</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Typ (physisch/VM/Container)</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Standort / Cluster</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">CPU / RAM / Disk</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Inbetriebnahme</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
</tbody>
</table>
<h2>Zweck & Dienste</h2>
<p>Beschreibung des Hauptzwecks und der laufenden Dienste:</p>
<ul><li>Dienst 1 (Port)</li><li>Dienst 2 (Port)</li></ul>
<h2>Netzwerk</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Interface</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">IP/Subnetz</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">VLAN</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Zweck</th></tr></thead>
<tbody><tr><td style="border:1px solid #d1d5db;padding:0.75rem;">eth0</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr></tbody>
</table>
<h2>Backup & Wiederherstellung</h2>
<p><strong>Backup-Methode:</strong> </p>
<p><strong>Backup-Zeitplan:</strong> </p>
<p><strong>Aufbewahrung:</strong> </p>
<p><strong>Wiederherstellungsprozedur:</strong> </p>
<h2>Monitoring</h2>
<p><strong>Monitoring-Tool:</strong> </p>
<p><strong>Alerting:</strong> </p>
<h2>Wartung & Updates</h2>
<p><strong>Patch-Zyklus:</strong> </p>
<p><strong>Wartungsfenster:</strong> </p>
<h2>Notizen</h2>
<p>Sonstige Hinweise und Bemerkungen.</p>',
'Infrastruktur',
'🖥',
1
),
(
'Software-Dokumentation',
'software-dokumentation',
'Vorlage fuer die Dokumentation einer Software oder Anwendung',
'<h2>Allgemein</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Eigenschaft</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Wert</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Name</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Version</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Hersteller</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Lizenztyp</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Lizenz bis</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">URL / Link</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
</tbody>
</table>
<h2>Beschreibung</h2>
<p>Wofür wird die Software eingesetzt? Welche Geschäftsprozesse unterstützt sie?</p>
<h2>Installation & Konfiguration</h2>
<h3>Systemanforderungen</h3>
<ul><li>Betriebssystem:</li><li>Speicher:</li><li>Abhängigkeiten:</li></ul>
<h3>Installationsschritte</h3>
<ol><li>Schritt 1</li><li>Schritt 2</li></ol>
<h3>Konfiguration</h3>
<p>Wichtige Konfigurationsdateien und Einstellungen:</p>
<pre><code># Beispiel-Konfiguration</code></pre>
<h2>Benutzer & Berechtigungen</h2>
<p>Welche Benutzergruppen nutzen die Software und welche Berechtigungen haben sie?</p>
<h2>Schnittstellen</h2>
<p>Welche Systeme sind angebunden? Welche APIs werden genutzt?</p>
<h2>Bekannte Probleme</h2>
<ul><li>Problem und Workaround</li></ul>
<h2>Support & Kontakt</h2>
<p><strong>Interner Ansprechpartner:</strong> </p>
<p><strong>Hersteller-Support:</strong> </p>',
'Software',
'💿',
2
),
(
'Netzwerk-Dokumentation',
'netzwerk-dokumentation',
'Vorlage fuer die Dokumentation von Netzwerkkomponenten (Switches, Router, Firewalls)',
'<h2>Übersicht</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Eigenschaft</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Wert</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Gerätename</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Typ (Switch/Router/Firewall/AP)</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Hersteller & Modell</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Firmware-Version</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Management-IP</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Standort / Rack</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Seriennummer</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
</tbody>
</table>
<h2>VLAN-Konfiguration</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">VLAN ID</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Name</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Subnetz</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Beschreibung</th></tr></thead>
<tbody><tr><td style="border:1px solid #d1d5db;padding:0.75rem;">1</td><td style="border:1px solid #d1d5db;padding:0.75rem;">Default</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr></tbody>
</table>
<h2>Port-Belegung</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Port</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Verbindung zu</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">VLAN</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Modus</th></tr></thead>
<tbody><tr><td style="border:1px solid #d1d5db;padding:0.75rem;">1</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">Access/Trunk</td></tr></tbody>
</table>
<h2>Routing</h2>
<p>Statische Routen und Routing-Protokolle:</p>
<h2>Firewall-Regeln</h2>
<p>Relevante Regeln und Policies:</p>
<h2>Notizen</h2>
<p>Sonstige Hinweise.</p>',
'Infrastruktur',
'🌐',
3
),
(
'Standard-Arbeitsanweisung (SOP)',
'standard-arbeitsanweisung',
'Vorlage fuer Standard Operating Procedures (SOPs)',
'<h2>Zweck</h2>
<p>Beschreiben Sie den Zweck dieses Verfahrens.</p>
<h2>Geltungsbereich</h2>
<p>Für wen gilt diese Anweisung? Welche Systeme/Prozesse sind betroffen?</p>
<h2>Voraussetzungen</h2>
<ul>
<li>Berechtigung / Zugang zu:</li>
<li>Werkzeug / Software:</li>
<li>Dokumentation:</li>
</ul>
<h2>Durchführung</h2>
<h3>Schritt 1: Vorbereitung</h3>
<p>Beschreibung der Vorbereitung.</p>
<h3>Schritt 2: Hauptaktion</h3>
<ol>
<li>Teilschritt 1</li>
<li>Teilschritt 2</li>
<li>Teilschritt 3</li>
</ol>
<h3>Schritt 3: Nachbereitung</h3>
<p>Beschreibung der Nachbereitung und Verifizierung.</p>
<h2>Fehlerbehandlung</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Problem</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Lösung</th></tr></thead>
<tbody><tr><td style="border:1px solid #d1d5db;padding:0.75rem;">Fehler X tritt auf</td><td style="border:1px solid #d1d5db;padding:0.75rem;">Aktion Y durchführen</td></tr></tbody>
</table>
<h2>Revisions-Historie</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Datum</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Version</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Änderung</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Autor</th></tr></thead>
<tbody><tr><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">1.0</td><td style="border:1px solid #d1d5db;padding:0.75rem;">Erstversion</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr></tbody>
</table>',
'Prozesse',
'📋',
4
),
(
'Runbook / Notfallplan',
'runbook-notfallplan',
'Vorlage fuer Runbooks und Notfallplaene bei Stoerungen',
'<blockquote><p><strong>Wichtig:</strong> Dieses Runbook beschreibt das Vorgehen bei einem Ausfall oder einer Störung. Bitte befolgen Sie die Schritte in der angegebenen Reihenfolge.</p></blockquote>
<h2>Störungsbeschreibung</h2>
<p><strong>Betroffenes System:</strong> </p>
<p><strong>Symptome:</strong> </p>
<p><strong>Auswirkung:</strong> </p>
<p><strong>Priorität:</strong> Hoch / Mittel / Niedrig</p>
<h2>Sofortmassnahmen</h2>
<ol>
<li>Prüfen, ob das Problem bekannt ist (Monitoring-Dashboard checken)</li>
<li>Betroffene Benutzer informieren</li>
<li>Eskalation starten, falls nötig</li>
</ol>
<h2>Diagnose</h2>
<h3>Prüfschritte</h3>
<ol>
<li><strong>Dienststatus prüfen:</strong><br><pre><code>systemctl status [service-name]</code></pre></li>
<li><strong>Logs prüfen:</strong><br><pre><code>journalctl -u [service-name] --since "1 hour ago"</code></pre></li>
<li><strong>Netzwerk prüfen:</strong><br><pre><code>ping [hostname]
telnet [hostname] [port]</code></pre></li>
</ol>
<h2>Behebung</h2>
<h3>Szenario A: Dienst gestoppt</h3>
<ol>
<li>Dienst neu starten</li>
<li>Logs auf Fehler prüfen</li>
<li>Funktionstest durchführen</li>
</ol>
<h3>Szenario B: Ressourcen erschöpft</h3>
<ol>
<li>Speicher / CPU Auslastung prüfen</li>
<li>Temporäre Dateien bereinigen</li>
<li>Ggf. Ressourcen erweitern</li>
</ol>
<h2>Eskalation</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Stufe</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Kontakt</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Telefon</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Wann</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">1</td><td style="border:1px solid #d1d5db;padding:0.75rem;">IT-Team</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">Sofort</td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">2</td><td style="border:1px solid #d1d5db;padding:0.75rem;">IT-Leitung</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">Nach 30 Min</td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;">3</td><td style="border:1px solid #d1d5db;padding:0.75rem;">Geschäftsführung</td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">Nach 1 Std</td></tr>
</tbody>
</table>
<h2>Nachbereitung</h2>
<ul>
<li>Root Cause Analysis dokumentieren</li>
<li>Präventivmassnahmen festlegen</li>
<li>Runbook aktualisieren</li>
</ul>',
'Prozesse',
'🚨',
5
),
(
'Meeting-Protokoll',
'meeting-protokoll',
'Vorlage fuer Meeting-Protokolle und Besprechungsnotizen',
'<h2>Meeting-Informationen</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Feld</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;text-align:left;">Wert</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"><strong>Datum</strong></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"><strong>Uhrzeit</strong></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"><strong>Ort / Link</strong></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"><strong>Teilnehmer</strong></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"><strong>Protokollant</strong></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td></tr>
</tbody>
</table>
<h2>Agenda</h2>
<ol>
<li>Punkt 1</li>
<li>Punkt 2</li>
<li>Punkt 3</li>
</ol>
<h2>Besprechungspunkte</h2>
<h3>1. Punkt 1</h3>
<p>Zusammenfassung der Diskussion.</p>
<h3>2. Punkt 2</h3>
<p>Zusammenfassung der Diskussion.</p>
<h2>Beschlüsse & Aufgaben</h2>
<table style="width:100%;border-collapse:collapse;">
<thead><tr><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Aufgabe</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Verantwortlich</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Frist</th><th style="border:1px solid #d1d5db;padding:0.75rem;background:#f3f4f6;">Status</th></tr></thead>
<tbody>
<tr><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;"></td><td style="border:1px solid #d1d5db;padding:0.75rem;">Offen</td></tr>
</tbody>
</table>
<h2>Nächster Termin</h2>
<p><strong>Datum:</strong> </p>
<p><strong>Themen:</strong> </p>',
'Allgemein',
'📝',
6
),
(
'Leere Seite',
'leere-seite',
'Leere Seite ohne vordefinierten Inhalt',
'',
'Allgemein',
'📄',
0
)
ON DUPLICATE KEY UPDATE slug = slug;
-- Migration: 020_contract_management.sql
-- Vertragsmanagement-System
-- Vertraege, Dateien fuer Firmen und Vertraege, Kuendigungsfrist-Benachrichtigungen
-- Capability fuer Vertragsmanagement
-- (Kategorie 'contract' im Singular – das historische 'contracts' kollidierte
-- mit spaeteren Statements und der Kategorie-Konvention der uebrigen Module.)
INSERT IGNORE INTO capabilities (name, description, category) VALUES
('view_contracts', 'Vertraege ansehen', 'contract'),
('manage_contracts', 'Vertraege erstellen, bearbeiten und loeschen', 'contract');
-- Admin-Rolle bekommt alle Vertrags-Capabilities
-- (Rollenname korrigiert: die globale Admin-Rolle heisst "System Admin", nicht
-- "Administrator". Die vollstaendige Admin-Vorbelegung erfolgt zusaetzlich in
-- Migration 043_rbac_module_capabilities.sql.)
INSERT IGNORE INTO role_capabilities (role_id, capability_id)
SELECT r.id, c.id FROM roles r, capabilities c
WHERE r.name = 'System Admin' AND c.name IN ('view_contracts', 'manage_contracts');
-- Vertraege
CREATE TABLE IF NOT EXISTS `contracts` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`company_id` BIGINT UNSIGNED NOT NULL,
`contract_number` VARCHAR(100) DEFAULT NULL,
`title` VARCHAR(255) NOT NULL,
`description` TEXT DEFAULT NULL,
`start_date` DATE DEFAULT NULL,
`end_date` DATE DEFAULT NULL,
`cancellation_period_months` INT UNSIGNED DEFAULT NULL,
`cancellation_notice_date` DATE DEFAULT NULL,
`auto_renew` TINYINT(1) NOT NULL DEFAULT 0,
`cost_amount` DECIMAL(12,2) DEFAULT NULL,
`cost_interval` ENUM('monthly', 'yearly') DEFAULT 'monthly',
`status` ENUM('active', 'expiring', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
`notes` TEXT DEFAULT NULL,
`created_by` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_contracts_company (`company_id`),
INDEX idx_contracts_status (`status`),
INDEX idx_contracts_end_date (`end_date`),
INDEX idx_contracts_cancellation (`cancellation_notice_date`),
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Dateien fuer Firmen
CREATE TABLE IF NOT EXISTS `company_files` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`company_id` BIGINT UNSIGNED NOT NULL,
`original_filename` VARCHAR(255) NOT NULL,
`stored_filename` VARCHAR(255) NOT NULL,
`file_size` BIGINT UNSIGNED DEFAULT 0,
`mime_type` VARCHAR(100) DEFAULT NULL,
`uploaded_by` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_company_files_company (`company_id`),
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Dateien fuer Vertraege
CREATE TABLE IF NOT EXISTS `contract_files` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`contract_id` BIGINT UNSIGNED NOT NULL,
`original_filename` VARCHAR(255) NOT NULL,
`stored_filename` VARCHAR(255) NOT NULL,
`file_size` BIGINT UNSIGNED DEFAULT 0,
`mime_type` VARCHAR(100) DEFAULT NULL,
`uploaded_by` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_contract_files_contract (`contract_id`),
FOREIGN KEY (`contract_id`) REFERENCES `contracts`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Tracking fuer gesendete Kuendigungserinnerungen (verhindert doppeltes Senden)
CREATE TABLE IF NOT EXISTS `contract_notifications_sent` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`contract_id` BIGINT UNSIGNED NOT NULL,
`notification_type` VARCHAR(50) NOT NULL DEFAULT 'cancellation_reminder',
`sent_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_contract_notif (`contract_id`, `notification_type`),
FOREIGN KEY (`contract_id`) REFERENCES `contracts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 021_contract_enhancements.sql
-- Vertragsmanagement Erweiterungen
-- Verantwortlicher, Erinnerungsdatum, Benachrichtigungsvorlage
-- Verantwortlicher fuer Vertrag
ALTER TABLE `contracts` ADD COLUMN `responsible_user_id` BIGINT UNSIGNED DEFAULT NULL AFTER `created_by`;
ALTER TABLE `contracts` ADD FOREIGN KEY (`responsible_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL;
ALTER TABLE `contracts` ADD INDEX idx_contracts_responsible (`responsible_user_id`);
-- Erinnerungsdatum (1 Monat vor Kuendigungsfrist)
ALTER TABLE `contracts` ADD COLUMN `reminder_date` DATE DEFAULT NULL AFTER `cancellation_notice_date`;
ALTER TABLE `contracts` ADD INDEX idx_contracts_reminder (`reminder_date`);
-- Benachrichtigungsvorlage fuer Vertragserinnerung
INSERT IGNORE INTO notification_templates (template_key, title_template, message_template, icon, type, description) VALUES
('contract_expiring', 'Vertrag: Kuendigungsfrist beachten - {{contract_title}}', 'Der Vertrag "{{contract_title}}" ({{company_name}}) muss bis {{cancellation_date}} gekuendigt werden. Vertragsende: {{end_date}}.', '⚠', 'warning', 'Erinnerung wenn die Kuendigungsfrist eines Vertrags naeher rueckt');
-- Migration: 022_cronjob_tracking_and_timezone.sql
-- Cronjob-Tracking und Zeitzone
-- Cronjob-Ausfuehrungen protokollieren
CREATE TABLE IF NOT EXISTS `cronjob_runs` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`job_name` VARCHAR(100) NOT NULL,
`started_at` DATETIME NOT NULL,
`finished_at` DATETIME DEFAULT NULL,
`status` ENUM('running', 'success', 'error') NOT NULL DEFAULT 'running',
`message` TEXT DEFAULT NULL,
`items_processed` INT UNSIGNED DEFAULT 0,
INDEX idx_cronjob_name (`job_name`),
INDEX idx_cronjob_started (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 023_fix_contract_enums_and_capabilities.sql
-- Fix: 'contract' als Kategorie fuer audit_log und capabilities hinzufuegen
-- Die Migration 020 konnte Capabilities nicht einfuegen weil 'contract(s)' nicht im ENUM war
-- Hinweis: Enthaelt alle bekannten Werte (inkl. spaeterer Migrationen),
-- damit ein Rerun-All keine bestehenden Eintraege abschneidet (Data truncated 1265).
-- 1. audit_log.action_category um 'contract' erweitern (und alle spaeten Werte behalten)
ALTER TABLE `audit_log` MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- 2. (entfernt) Historischer ENUM-Umbau von capabilities.category.
-- Die Spalte bleibt VARCHAR(50): Der ENUM-Umbau schlug im strikten SQL-Mode
-- fehl ("Data truncated ... at row 26"), weil bereits geseedete Kategorien
-- nicht in der ENUM-Liste standen, und wird weiter unten ohnehin wieder
-- nach VARCHAR(50) zurueckgebaut.
-- 3. Capabilities einfuegen (jetzt mit korrekter Kategorie 'contract')
INSERT IGNORE INTO capabilities (name, description, category, risk_level) VALUES
('view_contracts', 'Vertraege ansehen', 'contract', 'low'),
('manage_contracts', 'Vertraege erstellen, bearbeiten und loeschen', 'contract', 'medium');
-- 4. Admin-Rolle bekommt alle Vertrags-Capabilities
INSERT IGNORE INTO role_capabilities (role_id, capability_id)
SELECT r.id, c.id FROM roles r, capabilities c
WHERE r.is_admin = TRUE AND c.name IN ('view_contracts', 'manage_contracts');
-- Migration: 024_contract_payment_enhancements.sql
-- Migration 024: Contract Payment Enhancements
-- Adds: quarterly billing interval, first payment date, tax rate
-- Extend cost_interval enum to include quarterly
ALTER TABLE `contracts`
MODIFY COLUMN `cost_interval` ENUM('monthly', 'quarterly', 'yearly') NOT NULL DEFAULT 'monthly';
-- Add first payment date (for next-payment calculation)
ALTER TABLE `contracts`
ADD COLUMN `first_payment_date` DATE NULL DEFAULT NULL AFTER `cost_interval`;
-- Add tax rate in percent (e.g. 19.00 for 19% MwSt)
ALTER TABLE `contracts`
ADD COLUMN `tax_rate` DECIMAL(5,2) NULL DEFAULT NULL AFTER `first_payment_date`;
-- Migration: 025_loan_devices.sql
-- Migration 025: Loan Devices Module
-- Leihgeräte-Verwaltung mit Ausleihe, Rücknahme und Kalender
-- ============================================================
-- 1. loan_devices – die Geräte selbst
-- ============================================================
CREATE TABLE IF NOT EXISTS `loan_devices` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(255) NOT NULL,
`type` VARCHAR(100) NOT NULL DEFAULT '',
`serial_number` VARCHAR(255) DEFAULT NULL,
`description` TEXT DEFAULT NULL,
`image_path` VARCHAR(500) DEFAULT NULL,
`notes` TEXT DEFAULT NULL,
`status` ENUM('available','lent','reserved','maintenance','retired')
NOT NULL DEFAULT 'available',
`created_by` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_loan_devices_status` (`status`),
INDEX `idx_loan_devices_type` (`type`),
CONSTRAINT `fk_loan_devices_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 2. loan_device_accessories – Zubehörliste pro Gerät
-- ============================================================
CREATE TABLE IF NOT EXISTS `loan_device_accessories` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`device_id` BIGINT UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`description` VARCHAR(500) DEFAULT NULL,
`sort_order` INT UNSIGNED DEFAULT 0,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_loan_accessories_device` (`device_id`),
CONSTRAINT `fk_loan_accessories_device`
FOREIGN KEY (`device_id`) REFERENCES `loan_devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 3. loan_lendings – Ausleih-/Reservierungsvorgänge
-- ============================================================
CREATE TABLE IF NOT EXISTS `loan_lendings` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`device_id` BIGINT UNSIGNED NOT NULL,
`borrower_name` VARCHAR(255) NOT NULL,
`borrower_email` VARCHAR(255) DEFAULT NULL,
`borrower_phone` VARCHAR(50) DEFAULT NULL,
`purpose` TEXT DEFAULT NULL,
`planned_start` DATE NOT NULL,
`planned_end` DATE NOT NULL,
`actual_start` DATETIME DEFAULT NULL,
`actual_end` DATETIME DEFAULT NULL,
`status` ENUM('reserved','active','returned','cancelled')
NOT NULL DEFAULT 'reserved',
`issued_by` BIGINT UNSIGNED DEFAULT NULL,
`returned_by` BIGINT UNSIGNED DEFAULT NULL,
`notes` TEXT DEFAULT NULL,
`return_notes` TEXT DEFAULT NULL,
`created_by` BIGINT UNSIGNED DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_loan_lendings_device` (`device_id`),
INDEX `idx_loan_lendings_status` (`status`),
INDEX `idx_loan_lendings_dates` (`planned_start`, `planned_end`),
CONSTRAINT `fk_loan_lendings_device`
FOREIGN KEY (`device_id`) REFERENCES `loan_devices`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_loan_lendings_issued_by`
FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
CONSTRAINT `fk_loan_lendings_returned_by`
FOREIGN KEY (`returned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
CONSTRAINT `fk_loan_lendings_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 4. loan_lending_accessories – welches Zubehör ging mit raus
-- ============================================================
CREATE TABLE IF NOT EXISTS `loan_lending_accessories` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`lending_id` BIGINT UNSIGNED NOT NULL,
`accessory_id` BIGINT UNSIGNED NOT NULL,
`checked_out` TINYINT(1) NOT NULL DEFAULT 0,
`checked_in` TINYINT(1) DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uk_lending_accessory` (`lending_id`, `accessory_id`),
CONSTRAINT `fk_lla_lending`
FOREIGN KEY (`lending_id`) REFERENCES `loan_lendings`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_lla_accessory`
FOREIGN KEY (`accessory_id`) REFERENCES `loan_device_accessories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 5. Capabilities
-- ============================================================
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_loan_devices', 'Leihgeräte und Kalender ansehen', 'loan', 'low'),
('manage_loan_devices', 'Leihgeräte anlegen, bearbeiten und löschen', 'loan', 'medium'),
('issue_loan_devices', 'Leihgeräte ausgeben und zurücknehmen', 'loan', 'medium');
-- Migration: 026_loan_fixes.sql
-- Migration 026: Loan-Modul Fixes
-- Hinweis: Enthaelt alle bekannten Werte (inkl. spaeterer Migrationen),
-- damit ein Rerun-All keine bestehenden Eintraege abschneidet (Data truncated 1265).
-- 1. audit_log.action_category um 'loan' erweitern (und alle spaeten Werte behalten)
ALTER TABLE `audit_log` MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- 2. (entfernt) Historischer ENUM-Umbau von capabilities.category.
-- Die Spalte bleibt VARCHAR(50) – siehe Hinweis beim ersten entfernten
-- ENUM-Umbau weiter oben (Strict-Mode-Fehler "Data truncated ... at row 26").
-- 3. Inventarnummer-Spalte zu loan_devices hinzufuegen
ALTER TABLE `loan_devices`
ADD COLUMN `inventory_number` VARCHAR(100) DEFAULT NULL
AFTER `serial_number`;
-- 4. Loan-Capabilities einfuegen (konnten in 025 nicht eingefuegt werden,
-- da capabilities.category 'loan' noch nicht im ENUM war)
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_loan_devices', 'Leihgeräte und Kalender ansehen', 'loan', 'low'),
('manage_loan_devices', 'Leihgeräte anlegen, bearbeiten und löschen', 'loan', 'medium'),
('issue_loan_devices', 'Leihgeräte ausgeben und zurücknehmen', 'loan', 'medium');
-- Migration: 027_loan_capabilities.sql
-- Migration 027: Loan-Capabilities sicherstellen
-- Falls Migration 025 die Capabilities nicht einfuegen konnte (ENUM-Fehler)
-- und Migration 026 noch nicht die Capabilities enthielt, werden sie hier
-- sicher eingefuegt. INSERT IGNORE macht den Query idempotent.
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_loan_devices', 'Leihgeräte und Kalender ansehen', 'loan', 'low'),
('manage_loan_devices', 'Leihgeräte anlegen, bearbeiten und löschen', 'loan', 'medium'),
('issue_loan_devices', 'Leihgeräte ausgeben und zurücknehmen', 'loan', 'medium');
-- Migration: 028_loan_group_lending.sql
-- Migration 028: Multi-device lending groups
-- Allows multiple devices to be combined in a single lending process
ALTER TABLE loan_lendings
ADD COLUMN group_id VARCHAR(40) NULL DEFAULT NULL AFTER id;
CREATE INDEX idx_loan_lendings_group ON loan_lendings(group_id);
-- Migration: 029_loan_extensions.sql
-- Migration 029: Loan extension tracking
ALTER TABLE `loan_lendings`
ADD COLUMN `extended_by` BIGINT UNSIGNED NULL AFTER `returned_by`,
ADD COLUMN `extension_reason` TEXT NULL,
ADD COLUMN `extension_at` DATETIME NULL,
ADD COLUMN `extension_old_end` DATE NULL,
ADD CONSTRAINT `fk_loan_lendings_extended_by`
FOREIGN KEY (`extended_by`) REFERENCES `users`(`id`) ON DELETE SET NULL;
-- Migration: 030_ipam.sql
-- Migration 030: IPAM – IP-Adressverwaltung
-- Subnetze, IP-Adressen und flexible Zusatzinfos (z.B. Server-Details)
-- ============================================================
-- 1. Subnetze
-- ============================================================
CREATE TABLE IF NOT EXISTS `ipam_subnets` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`network` VARCHAR(45) NOT NULL COMMENT 'z.B. 192.168.1.0 oder 2001:db8::',
`cidr` TINYINT UNSIGNED NOT NULL COMMENT 'Präfixlänge, z.B. 24',
`vlan` VARCHAR(50) NULL,
`location` VARCHAR(100) NULL,
`description` TEXT NULL,
`color` VARCHAR(7) NOT NULL DEFAULT '#6b7280' COMMENT 'Hex-Farbe für Übersicht',
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_subnet_network_cidr` (`network`, `cidr`),
KEY `idx_subnet_name` (`name`),
CONSTRAINT `fk_ipam_subnet_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 2. IP-Adressen
-- ============================================================
CREATE TABLE IF NOT EXISTS `ipam_addresses` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`subnet_id` BIGINT UNSIGNED NOT NULL,
`ip_address` VARCHAR(45) NOT NULL,
`hostname` VARCHAR(255) NULL,
`type` ENUM('server','workstation','printer','switch','router','ap','camera','phone','other') NOT NULL DEFAULT 'other',
`status` ENUM('active','reserved','offline','free') NOT NULL DEFAULT 'active',
`description` TEXT NULL,
`responsible` VARCHAR(150) NULL COMMENT 'Name oder Team',
`mac_address` VARCHAR(17) NULL,
`created_by` BIGINT UNSIGNED NULL,
`updated_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ip_per_subnet` (`subnet_id`, `ip_address`),
KEY `idx_ip_status` (`status`),
KEY `idx_ip_type` (`type`),
KEY `idx_ip_hostname` (`hostname`),
CONSTRAINT `fk_ipam_addr_subnet` FOREIGN KEY (`subnet_id`) REFERENCES `ipam_subnets`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ipam_addr_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
CONSTRAINT `fk_ipam_addr_updater` FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 3. Flexible Zusatzinfos (Key-Value, z.B. Server-Details)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ipam_address_details` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`address_id` BIGINT UNSIGNED NOT NULL,
`detail_key` VARCHAR(100) NOT NULL COMMENT 'z.B. OS, CPU, RAM, Dienste',
`detail_val` TEXT NULL,
`sort_order` SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_detail_address` (`address_id`),
CONSTRAINT `fk_ipam_detail_addr` FOREIGN KEY (`address_id`) REFERENCES `ipam_addresses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 4. Capabilities
-- ============================================================
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_ipam', 'IP-Adressverwaltung ansehen', 'ipam', 'low'),
('manage_ipam_ips', 'IP-Adressen anlegen und bearbeiten', 'ipam', 'medium'),
('manage_ipam_subnets', 'Subnetze anlegen, bearbeiten und löschen', 'ipam', 'high');
-- Migration: 031_vault.sql
-- ============================================================
-- Migration 031: Passwort-Tresor (Vault)
--
-- SICHERHEITSARCHITEKTUR:
-- Alle Passwörter werden mit AES-256-GCM verschlüsselt.
-- Der Vault-Master-Key (VMK) wird mit PBKDF2-SHA256 (600k Rounds)
-- aus dem Login-Passwort des Nutzers abgeleitet und nur als
-- verschlüsselter Blob in der DB gespeichert.
-- Im Klartext ist der VMK ausschließlich in der PHP-Session.
-- ============================================================
-- Persönliche Vault-Konfiguration pro Nutzer
CREATE TABLE IF NOT EXISTS `vault_users` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`pbkdf2_salt` BINARY(32) NOT NULL COMMENT 'Salt fuer PBKDF2-Schluesselableitung',
`vmk_nonce` BINARY(12) NOT NULL COMMENT 'AES-GCM Nonce fuer VMK-Verschluesselung',
`vmk_encrypted` BLOB NOT NULL COMMENT 'AES-256-GCM(random_VMK, PBKDF2(login_password, salt))',
`vmk_tag` BINARY(16) NOT NULL COMMENT 'AES-GCM Auth-Tag',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_vault_user` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Ordner (persönlich: user_id gesetzt; Team: team_id gesetzt)
CREATE TABLE IF NOT EXISTS `vault_folders` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NULL COMMENT 'NULL bei Team-Ordnern',
`team_id` BIGINT UNSIGNED NULL COMMENT 'NULL bei persoenlichen Ordnern',
`parent_id` BIGINT UNSIGNED NULL COMMENT 'NULL = Wurzelordner',
`name` VARCHAR(200) NOT NULL,
`sort_order` SMALLINT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`parent_id`) REFERENCES `vault_folders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Passwort-Einträge (alle Felder AES-256-GCM verschlüsselt)
CREATE TABLE IF NOT EXISTS `vault_entries` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`folder_id` BIGINT UNSIGNED NULL,
`user_id` BIGINT UNSIGNED NULL COMMENT 'NULL bei Team-Eintraegen',
`team_id` BIGINT UNSIGNED NULL COMMENT 'NULL bei persoenlichen Eintraegen',
-- Titel
`title_enc` BLOB NOT NULL,
`title_nonce` BINARY(12) NOT NULL,
`title_tag` BINARY(16) NOT NULL,
-- Benutzername
`username_enc` BLOB,
`username_nonce` BINARY(12),
`username_tag` BINARY(16),
-- Passwort
`password_enc` BLOB NOT NULL,
`password_nonce` BINARY(12) NOT NULL,
`password_tag` BINARY(16) NOT NULL,
-- URL
`url_enc` BLOB,
`url_nonce` BINARY(12),
`url_tag` BINARY(16),
-- Notizen
`notes_enc` MEDIUMBLOB,
`notes_nonce` BINARY(12),
`notes_tag` BINARY(16),
-- TOTP-Secret (Base32-kodiert, verschlüsselt)
`totp_enc` BLOB,
`totp_nonce` BINARY(12),
`totp_tag` BINARY(16),
-- Favicon: nur Domain gespeichert (kein Secret)
`favicon_domain` VARCHAR(253) NULL,
`created_by` BIGINT UNSIGNED NULL,
`updated_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`folder_id`) REFERENCES `vault_folders`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Team-Tresore
CREATE TABLE IF NOT EXISTS `vault_teams` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(200) NOT NULL,
`description` TEXT,
-- TVK verschlüsselt mit PBKDF2(APP_KEY, admin_salt) → Admin-Kopie
-- Ermöglicht Admin-Operationen (Mitglieder hinzufügen) ohne Mitglied zu sein
`admin_salt` BINARY(32) NOT NULL,
`admin_key_enc` BLOB NOT NULL COMMENT 'AES-256-GCM(TVK, PBKDF2(APP_KEY, admin_salt))',
`admin_key_nonce` BINARY(12) NOT NULL,
`admin_key_tag` BINARY(16) NOT NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Team-Mitglieder: je eine Kopie des TVK, verschlüsselt mit dem VMK des Mitglieds
CREATE TABLE IF NOT EXISTS `vault_team_members` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`team_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`role` ENUM('admin','member') NOT NULL DEFAULT 'member',
`tvk_enc` BLOB NULL COMMENT 'AES-256-GCM(TVK, user_VMK) – NULL = Provisioning ausstehend',
`tvk_nonce` BINARY(12) NULL,
`tvk_tag` BINARY(16) NULL,
`joined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uq_team_member` (`team_id`, `user_id`),
FOREIGN KEY (`team_id`) REFERENCES `vault_teams`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities für Vault
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_vault', 'Persönlichen Passwort-Tresor nutzen', 'vault', 'low'),
('manage_vault_teams', 'Team-Tresore erstellen und eigene verwalten', 'vault', 'medium'),
('admin_vault_teams', 'Alle Team-Tresore im Admin-Bereich verwalten', 'vault', 'high');
-- Migration: 032_audit_ipam_vault_category.sql
-- Migration 032: Audit-Log action_category um 'ipam' und 'vault' erweitern
-- Hinweis: Enthaelt alle bekannten Werte (inkl. spaeterer Migrationen),
-- damit ein Rerun-All keine bestehenden Eintraege abschneidet (Data truncated 1265).
ALTER TABLE `audit_log` MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- Migration: 033_ipam_dhcp_range.sql
-- Migration 033: IPAM – DHCP-Bereich pro Subnetz
ALTER TABLE `ipam_subnets`
ADD COLUMN `dhcp_range_start` VARCHAR(45) NULL COMMENT 'Start der DHCP-Vergabe, z.B. 192.168.1.100' AFTER `description`,
ADD COLUMN `dhcp_range_end` VARCHAR(45) NULL COMMENT 'Ende der DHCP-Vergabe, z.B. 192.168.1.200' AFTER `dhcp_range_start`;
-- Migration: 034_tickets.sql
-- ============================================================
-- 034 Ticket-System
-- ============================================================
-- Kategorien (mit Unterkategorien)
CREATE TABLE IF NOT EXISTS `ticket_categories` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`parent_id` INT UNSIGNED NULL,
`name` VARCHAR(255) NOT NULL,
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`parent_id`) REFERENCES `ticket_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standorte (mit Unterstandorten)
CREATE TABLE IF NOT EXISTS `ticket_locations` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`parent_id` INT UNSIGNED NULL,
`name` VARCHAR(255) NOT NULL,
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`parent_id`) REFERENCES `ticket_locations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Haupt-Ticket-Tabelle
-- Hinweis: assignee_id referenziert users.id (BIGINT UNSIGNED) → muss ebenfalls BIGINT UNSIGNED sein
CREATE TABLE IF NOT EXISTS `tickets` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`subject` VARCHAR(500) NOT NULL,
`description` TEXT,
`from_email` VARCHAR(255) NOT NULL,
`from_name` VARCHAR(255) NOT NULL DEFAULT '',
`status` ENUM('new','open','waiting','solved','closed') NOT NULL DEFAULT 'new',
`assignee_id` BIGINT UNSIGNED NULL,
`category_id` INT UNSIGNED NULL,
`location_id` INT UNSIGNED NULL,
`mail_message_id` VARCHAR(500) NULL,
`solved_at` DATETIME NULL,
`closed_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 (`assignee_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`category_id`) REFERENCES `ticket_categories`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`location_id`) REFERENCES `ticket_locations`(`id`) ON DELETE SET NULL,
INDEX (`status`),
INDEX (`assignee_id`),
INDEX (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Ticket-Antworten
-- Hinweis: author_id referenziert users.id (BIGINT UNSIGNED) → muss ebenfalls BIGINT UNSIGNED sein
CREATE TABLE IF NOT EXISTS `ticket_replies` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`ticket_id` INT UNSIGNED NOT NULL,
`author_type` ENUM('staff','customer') NOT NULL,
`author_id` BIGINT UNSIGNED NULL,
`author_name` VARCHAR(255) NULL,
`body` TEXT NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- E-Mail-Vorlagen
CREATE TABLE IF NOT EXISTS `ticket_email_templates` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`event_type` VARCHAR(50) NOT NULL UNIQUE,
`subject` VARCHAR(500) NOT NULL,
`body` TEXT NOT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-E-Mail-Vorlagen
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`) VALUES
('created',
'[Ticket #{{ticket_id}}] Ihre Anfrage wurde erhalten',
'<p>Sehr geehrte(r) {{from_name}},</p><p>vielen Dank für Ihre Anfrage. Wir haben Ihr Ticket erhalten und werden uns so schnell wie möglich darum kümmern.</p><p><strong>Ticket-Nummer:</strong> #{{ticket_id}}<br><strong>Betreff:</strong> {{ticket_subject}}</p><p>Sie erhalten weitere Benachrichtigungen, sobald sich der Status ändert.</p><p>Mit freundlichen Grüßen<br>{{app_name}}</p>'),
('assigned',
'[Ticket #{{ticket_id}}] Ihre Anfrage wurde zugewiesen',
'<p>Sehr geehrte(r) {{from_name}},</p><p>Ihr Ticket wurde einem Mitarbeiter zugewiesen.</p><p><strong>Ticket-Nummer:</strong> #{{ticket_id}}<br><strong>Betreff:</strong> {{ticket_subject}}<br><strong>Zuständig:</strong> {{assignee_name}}</p><p>Mit freundlichen Grüßen<br>{{app_name}}</p>'),
('reply',
'[Ticket #{{ticket_id}}] Neue Antwort zu Ihrer Anfrage',
'<p>Sehr geehrte(r) {{from_name}},</p><p>es gibt eine neue Antwort zu Ihrem Ticket:</p><p><strong>Ticket-Nummer:</strong> #{{ticket_id}}<br><strong>Betreff:</strong> {{ticket_subject}}</p><hr style="margin:1rem 0;border:none;border-top:1px solid #e5e7eb;"><div style="background:#f9fafb;padding:1rem;border-radius:0.5rem;">{{reply_text}}</div><hr style="margin:1rem 0;border:none;border-top:1px solid #e5e7eb;"><p>Sie können auf diese E-Mail antworten, um dem Ticket eine Nachricht hinzuzufügen.</p><p>Mit freundlichen Grüßen<br>{{app_name}}</p>'),
('solved',
'[Ticket #{{ticket_id}}] Ihre Anfrage wurde als gelöst markiert',
'<p>Sehr geehrte(r) {{from_name}},</p><p>Ihr Ticket wurde als <strong>gelöst</strong> markiert.</p><p><strong>Ticket-Nummer:</strong> #{{ticket_id}}<br><strong>Betreff:</strong> {{ticket_subject}}</p><p>Falls Ihr Anliegen noch nicht vollständig gelöst ist, antworten Sie bitte <strong>innerhalb von 3 Tagen</strong> auf diese E-Mail. Andernfalls wird das Ticket automatisch geschlossen.</p><p>Mit freundlichen Grüßen<br>{{app_name}}</p>'),
('closed',
'[Ticket #{{ticket_id}}] Ihre Anfrage wurde geschlossen',
'<p>Sehr geehrte(r) {{from_name}},</p><p>Ihr Ticket wurde geschlossen.</p><p><strong>Ticket-Nummer:</strong> #{{ticket_id}}<br><strong>Betreff:</strong> {{ticket_subject}}</p><p>Für weitere Fragen schicken Sie uns bitte eine neue E-Mail.</p><p>Mit freundlichen Grüßen<br>{{app_name}}</p>');
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_tickets', 'Tickets anzeigen und lesen', 'tickets'),
('manage_tickets', 'Tickets zuweisen, beantworten und Status ändern', 'tickets'),
('admin_tickets', 'Ticket-Einstellungen, Kategorien und Standorte verwalten', 'tickets');
-- Migration: 035_ssl_checker.sql
-- ============================================================
-- 035 SSL-Zertifikat-Checker
-- ============================================================
CREATE TABLE IF NOT EXISTS `ssl_entries` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`label` VARCHAR(255) NOT NULL DEFAULT '',
`host` VARCHAR(255) NOT NULL,
`port` SMALLINT UNSIGNED NOT NULL DEFAULT 443,
-- Letzte gecheckte Zertifikatsdaten
`last_checked_at` DATETIME NULL,
`check_error` TEXT NULL,
-- Zertifikats-Metadaten
`cert_cn` VARCHAR(500) NULL COMMENT 'Common Name',
`cert_issuer_cn` VARCHAR(500) NULL,
`cert_issuer_org` VARCHAR(500) NULL,
`cert_org` VARCHAR(500) NULL COMMENT 'Subject Organization',
`cert_valid_from` DATETIME NULL,
`cert_valid_to` DATETIME NULL,
`cert_days_left` INT NULL,
`cert_serial` VARCHAR(255) NULL,
`cert_sig_algo` VARCHAR(100) NULL,
`cert_key_type` VARCHAR(50) NULL COMMENT 'RSA / EC / DSA',
`cert_key_bits` SMALLINT NULL,
`cert_sans` TEXT NULL COMMENT 'Subject Alternative Names, kommagetrennt',
`cert_ocsp_url` VARCHAR(1000) NULL,
`cert_crl_url` VARCHAR(1000) NULL,
`cert_is_wildcard` TINYINT(1) NOT NULL DEFAULT 0,
`cert_chain_depth` TINYINT NULL COMMENT 'Anzahl Zertifikate in der Chain',
-- Metadata
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
INDEX (`cert_valid_to`),
INDEX (`cert_days_left`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_ssl', 'SSL-Zertifikate anzeigen', 'ssl'),
('manage_ssl', 'SSL-Einträge anlegen und löschen', 'ssl');
-- Migration: 036_kanban.sql
-- ============================================================
-- 036 Kanban-Board
-- ============================================================
CREATE TABLE IF NOT EXISTS `kanban_categories` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`color` VARCHAR(7) NOT NULL DEFAULT '#6366f1',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `kanban_tasks` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(500) NOT NULL,
`description` TEXT NULL,
`month` CHAR(7) NOT NULL COMMENT 'YYYY-MM',
`category_id` INT UNSIGNED NULL,
`priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
`due_date` DATE NULL,
`sort_order` INT NOT NULL DEFAULT 0,
`created_by` BIGINT UNSIGNED NULL,
`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 `kanban_categories`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
INDEX (`month`),
INDEX (`category_id`),
INDEX (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Mehrere Benutzer pro Aufgabe (n:m)
CREATE TABLE IF NOT EXISTS `kanban_task_users` (
`task_id` INT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`task_id`, `user_id`),
FOREIGN KEY (`task_id`) REFERENCES `kanban_tasks`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_kanban', 'Kanban-Board anzeigen', 'kanban'),
('manage_kanban', 'Aufgaben und Kategorien verwalten', 'kanban');
-- Migration: 037_vault_extension_tokens.sql
-- ============================================================
-- 037 Vault – Browser-Extension API-Tokens
--
-- Der Token ist ein zufälliger 32-Byte-Wert (Base64URL).
-- Der VMK (Vault Master Key) wird mit einem aus dem Token
-- abgeleiteten Schlüssel (HKDF-SHA-256) erneut verschlüsselt
-- und gespeichert. Dadurch kann die Extension ohne aktive
-- Browser-Session auf den Vault zugreifen.
-- ============================================================
CREATE TABLE IF NOT EXISTS `vault_extension_tokens` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`label` VARCHAR(200) NOT NULL DEFAULT 'Chrome Extension',
`token_hash` CHAR(64) NOT NULL COMMENT 'SHA-256(token) hex',
-- VMK erneut verschlüsselt mit HKDF(token, "vault-ext-v1")
`vmk_enc` BLOB NOT NULL,
`vmk_nonce` BINARY(12) NOT NULL,
`vmk_tag` BINARY(16) NOT NULL,
`last_used_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uq_token_hash` (`token_hash`),
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 038_audit_tickets_ssl_kanban_category.sql
-- Migration 038: Audit-Log action_category um 'tickets', 'ssl', 'kanban' erweitern
ALTER TABLE `audit_log` MODIFY COLUMN `action_category` ENUM(
'auth',
'permission',
'content',
'file',
'export',
'admin',
'security',
'contact',
'order',
'service',
'contract',
'loan',
'ipam',
'vault',
'tickets',
'ssl',
'kanban'
) NOT NULL;
-- Migration: 039_email_templates_redesign.sql
-- Migration 039: Moderne E-Mail-Vorlagen mit Logo-Support und neuem Design
-- Hilfsmakro: gemeinsames Layout als Basis-HTML (wird per PHP zusammengebaut)
-- Die Vorlagen nutzen {{app_logo_url}} und {{ticket_url}} als neue Platzhalter.
-- ============================================================
-- VORLAGE 1: Ticket erstellt
-- ============================================================
-- Hinweis: INSERT IGNORE stellt sicher, dass bestehende angepasste Vorlagen
-- NICHT überschrieben werden. Zum Zurücksetzen: Admin → Ticket-E-Mails → "Standard wiederherstellen".
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`, `updated_at`)
VALUES ('created',
'Ihr Ticket #{{ticket_id}} wurde erfolgreich erstellt',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Ticket erstellt</title>
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#ffffff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
<!-- Header -->
<tr>
<td style="background:linear-gradient(135deg,#1e40af 0%,#2563eb 100%);padding:28px 36px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
{{#if app_logo_url}}
<img src="{{app_logo_url}}" alt="{{app_name}}" style="height:36px;width:auto;display:block;filter:brightness(10);" onerror="this.style.display=''none''">
{{/if}}
<div style="color:#ffffff;font-size:22px;font-weight:700;margin-top:8px;">{{app_name}}</div>
</td>
<td align="right" style="vertical-align:middle;">
<div style="background:rgba(255,255,255,0.2);border-radius:8px;padding:8px 16px;display:inline-block;">
<div style="color:rgba(255,255,255,0.8);font-size:11px;font-weight:600;text-transform:uppercase;letter-spacing:0.5px;">Ticket</div>
<div style="color:#ffffff;font-size:20px;font-weight:700;">#{{ticket_id}}</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
<!-- Status Banner -->
<tr>
<td style="background:#eff6ff;border-bottom:2px solid #bfdbfe;padding:14px 36px;">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width:32px;height:32px;background:#2563eb;border-radius:50%;text-align:center;vertical-align:middle;">
<span style="color:#fff;font-size:16px;line-height:32px;">✓</span>
</td>
<td style="padding-left:12px;color:#1e40af;font-size:14px;font-weight:600;">
Ihr Ticket wurde erfolgreich eingereicht
</td>
</tr>
</table>
</td>
</tr>
<!-- Content -->
<tr>
<td style="padding:32px 36px;">
<p style="margin:0 0 16px;color:#374151;font-size:16px;">Guten Tag, <strong>{{from_name}}</strong>,</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
wir haben Ihre Anfrage erhalten und bearbeiten sie schnellstmöglich. Sie erhalten eine weitere Benachrichtigung, sobald sich der Status Ihres Tickets ändert.
</p>
<!-- Ticket Info Box -->
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;overflow:hidden;margin-bottom:28px;">
<tr>
<td style="background:#f3f4f6;padding:12px 20px;border-bottom:1px solid #e5e7eb;">
<span style="font-size:12px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:0.5px;">Ihre Anfrage</span>
</td>
</tr>
<tr>
<td style="padding:20px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;width:120px;">Ticket-Nr.</td>
<td style="padding:6px 0;color:#111827;font-size:13px;font-weight:600;">#{{ticket_id}}</td>
</tr>
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;">Betreff</td>
<td style="padding:6px 0;color:#111827;font-size:13px;">{{ticket_subject}}</td>
</tr>
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;">Status</td>
<td style="padding:6px 0;">
<span style="background:#fef3c7;color:#92400e;font-size:12px;font-weight:600;padding:2px 10px;border-radius:20px;">Neu</span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<p style="margin:0 0 8px;color:#6b7280;font-size:13px;">
Sie können auf diese E-Mail antworten, um Ihrem Ticket weitere Informationen hinzuzufügen.
</p>
</td>
</tr>
<!-- Footer -->
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 36px;text-align:center;">
<p style="margin:0 0 4px;color:#9ca3af;font-size:12px;">Diese E-Mail wurde automatisch vom Support-System gesendet.</p>
<p style="margin:0;color:#9ca3af;font-size:12px;">© {{app_name}}</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
NOW());
-- ============================================================
-- VORLAGE 2: Ticket zugewiesen
-- ============================================================
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`, `updated_at`)
VALUES ('assigned',
'Ticket #{{ticket_id}} wurde Ihnen zugewiesen',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Ticket zugewiesen</title>
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#ffffff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
<tr>
<td style="background:linear-gradient(135deg,#1e40af 0%,#2563eb 100%);padding:28px 36px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
<div style="color:#ffffff;font-size:22px;font-weight:700;">{{app_name}}</div>
</td>
<td align="right">
<div style="background:rgba(255,255,255,0.2);border-radius:8px;padding:8px 16px;display:inline-block;">
<div style="color:rgba(255,255,255,0.8);font-size:11px;font-weight:600;text-transform:uppercase;">Ticket</div>
<div style="color:#ffffff;font-size:20px;font-weight:700;">#{{ticket_id}}</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#f0fdf4;border-bottom:2px solid #bbf7d0;padding:14px 36px;">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width:32px;height:32px;background:#16a34a;border-radius:50%;text-align:center;vertical-align:middle;">
<span style="color:#fff;font-size:18px;line-height:32px;">👤</span>
</td>
<td style="padding-left:12px;color:#15803d;font-size:14px;font-weight:600;">
Ihr Ticket wurde einem Mitarbeiter zugewiesen
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="padding:32px 36px;">
<p style="margin:0 0 16px;color:#374151;font-size:16px;">Guten Tag, <strong>{{from_name}}</strong>,</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
Ihr Ticket wurde einem Mitarbeiter zur Bearbeitung zugewiesen. Wir werden uns so schnell wie möglich bei Ihnen melden.
</p>
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;overflow:hidden;margin-bottom:28px;">
<tr>
<td style="background:#f3f4f6;padding:12px 20px;border-bottom:1px solid #e5e7eb;">
<span style="font-size:12px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:0.5px;">Ticket-Details</span>
</td>
</tr>
<tr>
<td style="padding:20px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;width:140px;">Ticket-Nr.</td>
<td style="padding:6px 0;color:#111827;font-size:13px;font-weight:600;">#{{ticket_id}}</td>
</tr>
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;">Betreff</td>
<td style="padding:6px 0;color:#111827;font-size:13px;">{{ticket_subject}}</td>
</tr>
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;">Bearbeiter</td>
<td style="padding:6px 0;color:#111827;font-size:13px;font-weight:600;">{{assignee_name}}</td>
</tr>
<tr>
<td style="padding:6px 0;color:#9ca3af;font-size:13px;">Status</td>
<td style="padding:6px 0;">
<span style="background:#dbeafe;color:#1e40af;font-size:12px;font-weight:600;padding:2px 10px;border-radius:20px;">In Bearbeitung</span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<p style="margin:0;color:#6b7280;font-size:13px;">
Sie können auf diese E-Mail antworten, um dem Ticket weitere Informationen hinzuzufügen.
</p>
</td>
</tr>
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 36px;text-align:center;">
<p style="margin:0 0 4px;color:#9ca3af;font-size:12px;">Diese E-Mail wurde automatisch vom Support-System gesendet.</p>
<p style="margin:0;color:#9ca3af;font-size:12px;">© {{app_name}}</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
NOW());
-- ============================================================
-- VORLAGE 3: Neue Antwort
-- ============================================================
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`, `updated_at`)
VALUES ('reply',
'Neue Antwort zu Ticket #{{ticket_id}}: {{ticket_subject}}',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Neue Antwort</title>
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#ffffff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
<tr>
<td style="background:linear-gradient(135deg,#1e40af 0%,#2563eb 100%);padding:28px 36px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
<div style="color:#ffffff;font-size:22px;font-weight:700;">{{app_name}}</div>
</td>
<td align="right">
<div style="background:rgba(255,255,255,0.2);border-radius:8px;padding:8px 16px;display:inline-block;">
<div style="color:rgba(255,255,255,0.8);font-size:11px;font-weight:600;text-transform:uppercase;">Ticket</div>
<div style="color:#ffffff;font-size:20px;font-weight:700;">#{{ticket_id}}</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#eff6ff;border-bottom:2px solid #bfdbfe;padding:14px 36px;">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width:32px;height:32px;background:#2563eb;border-radius:50%;text-align:center;vertical-align:middle;">
<span style="color:#fff;font-size:14px;line-height:32px;">✉</span>
</td>
<td style="padding-left:12px;color:#1e40af;font-size:14px;font-weight:600;">
Neue Antwort von {{assignee_name}}
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="padding:32px 36px;">
<p style="margin:0 0 16px;color:#374151;font-size:16px;">Guten Tag, <strong>{{from_name}}</strong>,</p>
<p style="margin:0 0 20px;color:#6b7280;font-size:14px;line-height:1.7;">
es gibt eine neue Antwort zu Ihrem Ticket <strong>„{{ticket_subject}}"</strong>:
</p>
<!-- Reply Content -->
<table width="100%" cellpadding="0" cellspacing="0" style="margin-bottom:28px;">
<tr>
<td style="background:#f9fafb;border-left:4px solid #2563eb;border-radius:0 8px 8px 0;padding:20px 24px;">
<div style="font-size:12px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:0.5px;margin-bottom:12px;">
Antwort von {{assignee_name}}
</div>
<div style="color:#374151;font-size:14px;line-height:1.8;">
{{reply_text}}
</div>
</td>
</tr>
</table>
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;overflow:hidden;margin-bottom:28px;">
<tr>
<td style="padding:16px 20px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="color:#9ca3af;font-size:13px;width:120px;padding:4px 0;">Ticket-Nr.</td>
<td style="color:#111827;font-size:13px;font-weight:600;padding:4px 0;">#{{ticket_id}}</td>
</tr>
<tr>
<td style="color:#9ca3af;font-size:13px;padding:4px 0;">Betreff</td>
<td style="color:#111827;font-size:13px;padding:4px 0;">{{ticket_subject}}</td>
</tr>
</table>
</td>
</tr>
</table>
<p style="margin:0;color:#6b7280;font-size:13px;">
Sie können auf diese E-Mail antworten, um dem Ticket weitere Informationen hinzuzufügen.
</p>
</td>
</tr>
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 36px;text-align:center;">
<p style="margin:0 0 4px;color:#9ca3af;font-size:12px;">Diese E-Mail wurde automatisch vom Support-System gesendet.</p>
<p style="margin:0;color:#9ca3af;font-size:12px;">© {{app_name}}</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
NOW());
-- ============================================================
-- VORLAGE 4: Ticket gelöst
-- ============================================================
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`, `updated_at`)
VALUES ('solved',
'Ticket #{{ticket_id}} wurde als gelöst markiert',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Ticket gelöst</title>
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#ffffff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
<tr>
<td style="background:linear-gradient(135deg,#065f46 0%,#059669 100%);padding:28px 36px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
<div style="color:#ffffff;font-size:22px;font-weight:700;">{{app_name}}</div>
</td>
<td align="right">
<div style="background:rgba(255,255,255,0.2);border-radius:8px;padding:8px 16px;display:inline-block;">
<div style="color:rgba(255,255,255,0.8);font-size:11px;font-weight:600;text-transform:uppercase;">Ticket</div>
<div style="color:#ffffff;font-size:20px;font-weight:700;">#{{ticket_id}}</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#f0fdf4;border-bottom:2px solid #bbf7d0;padding:14px 36px;">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width:32px;height:32px;background:#16a34a;border-radius:50%;text-align:center;vertical-align:middle;">
<span style="color:#fff;font-size:18px;line-height:32px;">✓</span>
</td>
<td style="padding-left:12px;color:#15803d;font-size:14px;font-weight:600;">
Ihr Ticket wurde als gelöst markiert
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="padding:32px 36px;">
<p style="margin:0 0 16px;color:#374151;font-size:16px;">Guten Tag, <strong>{{from_name}}</strong>,</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
Ihr Ticket <strong>„{{ticket_subject}}"</strong> wurde von unserem Support-Team als gelöst markiert. Wir hoffen, dass Ihr Anliegen zur Ihrer Zufriedenheit bearbeitet wurde.
</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
Sollte das Problem weiterhin bestehen, antworten Sie einfach auf diese E-Mail — wir öffnen das Ticket dann wieder für Sie.
</p>
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;overflow:hidden;margin-bottom:28px;">
<tr>
<td style="padding:16px 20px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="color:#9ca3af;font-size:13px;width:120px;padding:4px 0;">Ticket-Nr.</td>
<td style="color:#111827;font-size:13px;font-weight:600;padding:4px 0;">#{{ticket_id}}</td>
</tr>
<tr>
<td style="color:#9ca3af;font-size:13px;padding:4px 0;">Betreff</td>
<td style="color:#111827;font-size:13px;padding:4px 0;">{{ticket_subject}}</td>
</tr>
<tr>
<td style="color:#9ca3af;font-size:13px;padding:4px 0;">Status</td>
<td style="padding:4px 0;">
<span style="background:#d1fae5;color:#065f46;font-size:12px;font-weight:600;padding:2px 10px;border-radius:20px;">Gelöst</span>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 36px;text-align:center;">
<p style="margin:0 0 4px;color:#9ca3af;font-size:12px;">Diese E-Mail wurde automatisch vom Support-System gesendet.</p>
<p style="margin:0;color:#9ca3af;font-size:12px;">© {{app_name}}</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
NOW());
-- ============================================================
-- VORLAGE 5: Ticket geschlossen
-- ============================================================
INSERT IGNORE INTO `ticket_email_templates` (`event_type`, `subject`, `body`, `updated_at`)
VALUES ('closed',
'Ticket #{{ticket_id}} wurde geschlossen',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Ticket geschlossen</title>
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#ffffff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
<tr>
<td style="background:linear-gradient(135deg,#374151 0%,#4b5563 100%);padding:28px 36px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td>
<div style="color:#ffffff;font-size:22px;font-weight:700;">{{app_name}}</div>
</td>
<td align="right">
<div style="background:rgba(255,255,255,0.2);border-radius:8px;padding:8px 16px;display:inline-block;">
<div style="color:rgba(255,255,255,0.8);font-size:11px;font-weight:600;text-transform:uppercase;">Ticket</div>
<div style="color:#ffffff;font-size:20px;font-weight:700;">#{{ticket_id}}</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#f9fafb;border-bottom:2px solid #e5e7eb;padding:14px 36px;">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width:32px;height:32px;background:#6b7280;border-radius:50%;text-align:center;vertical-align:middle;">
<span style="color:#fff;font-size:14px;line-height:32px;">✕</span>
</td>
<td style="padding-left:12px;color:#374151;font-size:14px;font-weight:600;">
Ihr Ticket wurde geschlossen
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="padding:32px 36px;">
<p style="margin:0 0 16px;color:#374151;font-size:16px;">Guten Tag, <strong>{{from_name}}</strong>,</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
Ihr Ticket <strong>„{{ticket_subject}}"</strong> wurde geschlossen. Vielen Dank für Ihre Geduld.
</p>
<p style="margin:0 0 24px;color:#6b7280;font-size:14px;line-height:1.7;">
Falls Sie weitere Unterstützung benötigen, erstellen Sie gerne ein neues Ticket oder kontaktieren Sie uns direkt.
</p>
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:10px;overflow:hidden;margin-bottom:28px;">
<tr>
<td style="padding:16px 20px;">
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="color:#9ca3af;font-size:13px;width:120px;padding:4px 0;">Ticket-Nr.</td>
<td style="color:#111827;font-size:13px;font-weight:600;padding:4px 0;">#{{ticket_id}}</td>
</tr>
<tr>
<td style="color:#9ca3af;font-size:13px;padding:4px 0;">Betreff</td>
<td style="color:#111827;font-size:13px;padding:4px 0;">{{ticket_subject}}</td>
</tr>
<tr>
<td style="color:#9ca3af;font-size:13px;padding:4px 0;">Status</td>
<td style="padding:4px 0;">
<span style="background:#f3f4f6;color:#374151;font-size:12px;font-weight:600;padding:2px 10px;border-radius:20px;">Geschlossen</span>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 36px;text-align:center;">
<p style="margin:0 0 4px;color:#9ca3af;font-size:12px;">Diese E-Mail wurde automatisch vom Support-System gesendet.</p>
<p style="margin:0;color:#9ca3af;font-size:12px;">© {{app_name}}</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
NOW());
-- Migration: 040_kanban_done.sql
-- ============================================================
-- 040 Kanban: done-Flag für Aufgaben
-- ============================================================
ALTER TABLE `kanban_tasks`
ADD COLUMN `done` TINYINT(1) NOT NULL DEFAULT 0 AFTER `sort_order`;
ALTER TABLE `kanban_tasks`
ADD INDEX `idx_kanban_tasks_done` (`done`);
-- Migration: 041_fix_capability_categories.sql
-- Migration 041: Fix capability categories for modules added via migrations
-- These capabilities were inserted with the correct category but the DB may differ
-- if seeded from older versions or if INSERT IGNORE masked an update.
UPDATE `capabilities` SET `category` = 'tickets' WHERE `name` IN ('view_tickets', 'manage_tickets', 'admin_tickets') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'ssl' WHERE `name` IN ('view_ssl', 'manage_ssl') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'kanban' WHERE `name` IN ('view_kanban', 'manage_kanban') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'vault' WHERE `name` IN ('view_vault', 'manage_vault_teams', 'admin_vault_teams') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'ipam' WHERE `name` IN ('view_ipam', 'manage_ipam_ips', 'manage_ipam_subnets') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'loan' WHERE `name` IN ('view_loan_devices', 'manage_loan_devices', 'issue_loan_devices') AND (`category` IS NULL OR `category` = '');
UPDATE `capabilities` SET `category` = 'contract' WHERE `name` IN ('view_contracts', 'manage_contracts') AND (`category` IS NULL OR `category` = '');
-- Migration: 041_personal_todos.sql
-- ============================================================
-- 041: Persönliche To-Do-Liste
-- ============================================================
CREATE TABLE IF NOT EXISTS `personal_todos` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL,
`title` VARCHAR(500) NOT NULL,
`done` TINYINT(1) NOT NULL DEFAULT 0,
`sort_order` INT NOT NULL DEFAULT 0,
`done_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_todos_user` (`user_id`),
CONSTRAINT `fk_todos_user`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 042_audit_category_varchar.sql
-- ============================================================
-- Migration 042: action_category und capabilities.category von ENUM auf VARCHAR
--
-- Warum: ENUM-Spalten muessen bei jedem neuen Wert in ALLEN vorherigen
-- Migrationen nachgepflegt werden, damit ein Rerun-All keine bestehenden
-- Eintraege abschneidet (SQLSTATE 01000 / 1265 Data truncated).
-- Als VARCHAR reicht es, den neuen Wert einfach im Code zu verwenden –
-- keine Schema-Aenderung mehr noetig.
--
-- MySQL konvertiert ENUM → VARCHAR verlustfrei (alle Werte bleiben erhalten).
-- ============================================================
-- audit_log.action_category: ENUM → VARCHAR(50)
ALTER TABLE `audit_log`
MODIFY COLUMN `action_category` VARCHAR(50) NOT NULL DEFAULT '';
-- capabilities.category: ENUM → VARCHAR(50)
-- (betroffen: Migrationen 004, 005, 006, 011, 023, 026)
ALTER TABLE `capabilities`
MODIFY COLUMN `category` VARCHAR(50) NOT NULL DEFAULT '';
-- Migration: 043_assets.sql
-- ============================================================
-- 043 Inventar / Asset-Management
-- ============================================================
-- Kategorien (Client, Server, Netzwerk, ...)
CREATE TABLE IF NOT EXISTS `asset_categories` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`icon` VARCHAR(50) NOT NULL DEFAULT '🖥️',
`color` VARCHAR(7) NOT NULL DEFAULT '#6366f1',
`number_format` VARCHAR(255) NOT NULL DEFAULT 'INV-%Jahr%-%fortlaufendenummer%',
`counter_reset_yearly` TINYINT(1) NOT NULL DEFAULT 0,
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Hersteller (Dell, HP, Cisco, ...)
CREATE TABLE IF NOT EXISTS `asset_manufacturers` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`website` VARCHAR(500) NULL,
`notes` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Typen / Modelle (Latitude 3530, ProLiant DL360, ...)
CREATE TABLE IF NOT EXISTS `asset_types` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`manufacturer_id` INT UNSIGNED NOT NULL,
`category_id` INT UNSIGNED NULL COMMENT 'Optionale Kategorie-Einschraenkung',
`description` TEXT NULL,
`specs` TEXT NULL COMMENT 'Technische Daten (frei)',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`manufacturer_id`) REFERENCES `asset_manufacturers`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`category_id`) REFERENCES `asset_categories`(`id`) ON DELETE SET NULL,
INDEX (`manufacturer_id`),
INDEX (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Bilder pro Typ
CREATE TABLE IF NOT EXISTS `asset_type_images` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`type_id` INT UNSIGNED NOT NULL,
`filename` VARCHAR(500) NOT NULL,
`sort_order` INT NOT NULL DEFAULT 0,
FOREIGN KEY (`type_id`) REFERENCES `asset_types`(`id`) ON DELETE CASCADE,
INDEX (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Sequenz-Zaehler pro Kategorie (und optional Jahr)
CREATE TABLE IF NOT EXISTS `asset_number_sequences` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`category_id` INT UNSIGNED NOT NULL,
`year` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = kein Jahres-Reset',
`counter` INT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY `uq_category_year` (`category_id`, `year`),
FOREIGN KEY (`category_id`) REFERENCES `asset_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Assets (Geraete)
CREATE TABLE IF NOT EXISTS `assets` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`inventory_number` VARCHAR(100) NOT NULL UNIQUE,
`serial_number` VARCHAR(255) NULL,
`category_id` INT UNSIGNED NOT NULL,
`manufacturer_id` INT UNSIGNED NULL,
`type_id` INT UNSIGNED NULL,
`location_id` INT UNSIGNED NULL COMMENT 'FK zu ticket_locations',
`assigned_user_id` BIGINT UNSIGNED NULL,
`status` ENUM('active','storage','decommissioned') NOT NULL DEFAULT 'active',
`purchase_date` DATE NULL,
`warranty_until` DATE NULL,
`notes` TEXT NULL,
`created_by` BIGINT UNSIGNED NULL,
`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 `asset_categories`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`manufacturer_id`) REFERENCES `asset_manufacturers`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`type_id`) REFERENCES `asset_types`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`assigned_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
INDEX (`category_id`),
INDEX (`manufacturer_id`),
INDEX (`type_id`),
INDEX (`status`),
INDEX (`assigned_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`)
VALUES
('view_assets', 'Inventar einsehen', 'assets'),
('manage_assets', 'Inventar verwalten (CRUD)', 'assets'),
('admin_assets', 'Inventar-Kategorien & Typen verwalten', 'assets');
-- Standard-Kategorien
INSERT IGNORE INTO `asset_categories` (`name`, `icon`, `color`, `number_format`, `counter_reset_yearly`, `sort_order`)
VALUES
('Client', '💻', '#2563eb', 'CLI-%Jahr%-%fortlaufendenummer%', 1, 1),
('Server', '🖥️', '#7c3aed', 'SRV-%Jahr%-%fortlaufendenummer%', 1, 2),
('Netzwerk', '🌐', '#0891b2', 'NET-%Jahr%-%fortlaufendenummer%', 1, 3),
('Drucker', '🖨️', '#65a30d', 'DRU-%Jahr%-%fortlaufendenummer%', 1, 4),
('Smartphone','📱', '#dc2626', 'MOB-%Jahr%-%fortlaufendenummer%', 1, 5),
('Sonstiges', '📦', '#9ca3af', 'SON-%Jahr%-%fortlaufendenummer%', 0, 99);
-- Migration: 044_assets_assigned_to.sql
-- Add free-text assigned_to field for asset assignment (replaces FK-based assigned_user_id in forms)
ALTER TABLE `assets` ADD COLUMN `assigned_to` VARCHAR(255) NULL DEFAULT NULL AFTER `assigned_user_id`;
-- Migration: 045_asset_categories_unique_name.sql
-- ============================================================
-- 045 Asset-Kategorien: UNIQUE KEY auf name + Duplikate bereinigen
-- ============================================================
-- Duplikate entfernen: nur den Eintrag mit der kleinsten ID behalten
DELETE ac1 FROM `asset_categories` ac1
INNER JOIN `asset_categories` ac2
ON ac1.name = ac2.name AND ac1.id > ac2.id;
-- UNIQUE KEY hinzufügen (damit INSERT IGNORE bei Re-Run greift)
ALTER TABLE `asset_categories` ADD UNIQUE KEY `uq_asset_categories_name` (`name`);
-- Migration: 046_todo_notes.sql
-- ============================================================
-- 046: Notiz-Feld für persönliche To-Dos
-- ============================================================
ALTER TABLE `personal_todos`
ADD COLUMN `notes` TEXT NULL DEFAULT NULL AFTER `title`;
-- Migration: 047_ssl_reminder.sql
-- ============================================================
-- 047: SSL-Zertifikat-Ablauferinnerung
-- ============================================================
-- Tracking-Feld: speichert cert_valid_to-Wert zum Zeitpunkt der Benachrichtigung,
-- um Duplikate zu verhindern und bei Zertifikatserneuerung erneut zu benachrichtigen
ALTER TABLE `ssl_entries`
ADD COLUMN `ssl_reminder_sent_at` DATE NULL DEFAULT NULL AFTER `cert_days_left`;
-- In-App Benachrichtigungs-Vorlage
INSERT INTO notification_templates (template_key, title_template, message_template, icon, type, description)
VALUES (
'ssl_expiry',
'SSL-Zertifikat läuft ab: {{host}}',
'Das SSL-Zertifikat für {{host}} läuft in {{days_left}} Tagen ab ({{expiry_date}}).',
'🔒',
'warning',
'Benachrichtigung 5 Tage vor Ablauf eines SSL-Zertifikats'
)
ON DUPLICATE KEY UPDATE template_key = template_key;
-- E-Mail-Vorlage (konfigurierbar unter Einstellungen → E-Mail-Vorlagen)
INSERT INTO email_templates (template_key, subject, body_html, description)
VALUES (
'ssl_expiry',
'[{{app_name}}] SSL-Zertifikat läuft ab: {{host}}',
'<p>Hallo {{user_name}},</p>
<p>das SSL-Zertifikat für <strong>{{host}}</strong> läuft in <strong>{{days_left}} Tagen</strong> ab.</p>
<table style="border-collapse:collapse;width:100%;max-width:520px;margin:1rem 0;">
<tr><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;font-weight:600;background:#f9fafb;width:40%;">Host</td><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;">{{host}}</td></tr>
<tr><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;font-weight:600;background:#f9fafb;">Label</td><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;">{{label}}</td></tr>
<tr><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;font-weight:600;background:#f9fafb;">Ablaufdatum</td><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;color:#dc2626;font-weight:600;">{{expiry_date}} (in {{days_left}} Tagen)</td></tr>
<tr><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;font-weight:600;background:#f9fafb;">Aussteller</td><td style="padding:8px 12px;border-bottom:1px solid #e5e7eb;">{{issuer}}</td></tr>
</table>
<p style="margin-top:1.5rem;">
<a href="{{ssl_url}}" style="background:#2563eb;color:#fff;padding:10px 20px;text-decoration:none;border-radius:6px;display:inline-block;">SSL-Checker öffnen</a>
</p>
<p>Freundliche Grüße<br>{{app_name}}</p>',
'Wird 5 Tage vor Ablauf eines SSL-Zertifikats gesendet. Konfigurierbare Vorlage.'
)
ON DUPLICATE KEY UPDATE template_key = template_key;
-- Migration: 048_location_prefix.sql
-- Migration 048: Standort-Präfix für Inventarnummern
-- Fügt ein optionales Präfix-Feld zu Hauptstandorten hinzu,
-- das als %standortpräfix% in Inventarnummer-Formaten verwendet werden kann.
ALTER TABLE `ticket_locations`
ADD COLUMN `prefix` VARCHAR(10) NULL DEFAULT NULL
COMMENT 'Kurz-Präfix für Inventarnummern (nur Hauptstandorte empfohlen)';
-- Migration: 049_directory_persons.sql
-- Migration 049: Verzeichnis-Personen (Entra / LDAP / Manuell)
-- Interne Personenliste für Asset-Zuweisung und Ticket-Einsender.
-- Personen sind KEINE Tool-Benutzer (kein Login), sondern reine Datensätze.
CREATE TABLE IF NOT EXISTS `directory_persons` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`source` ENUM('entra','ldap','manual') NOT NULL DEFAULT 'manual'
COMMENT 'Quelle: entra=Azure AD, ldap=Active Directory, manual=manuell',
`external_id` VARCHAR(500) NULL COMMENT 'Azure objectId oder LDAP DN',
`display_name` VARCHAR(255) NOT NULL,
`first_name` VARCHAR(100) NULL,
`last_name` VARCHAR(100) NULL,
`email` VARCHAR(255) NULL,
`department` VARCHAR(255) NULL,
`job_title` VARCHAR(255) NULL,
`phone` VARCHAR(100) NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`last_synced_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_person_external` (`source`, `external_id`(191)),
INDEX `idx_person_email` (`email`),
INDEX `idx_person_name` (`display_name`(64)),
INDEX `idx_person_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 050_asset_person_id.sql
-- Migration 050: Verzeichnis-Person-Verknüpfung für Asset-Zuweisung
-- Ergänzt die bestehende assigned_to (Freitext) um eine FK auf directory_persons.
ALTER TABLE `assets`
ADD COLUMN `assigned_person_id` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'FK zu directory_persons (optional, ergänzt assigned_to)'
AFTER `assigned_to`;
ALTER TABLE `assets`
ADD CONSTRAINT `fk_assets_directory_person`
FOREIGN KEY (`assigned_person_id`) REFERENCES `directory_persons`(`id`)
ON DELETE SET NULL;
-- Migration: 051_ticket_requester_person_id.sql
-- Migration 051: Verzeichnis-Person-Verknüpfung für Ticket-Einsender
-- Ergänzt from_name/from_email um eine FK auf directory_persons.
-- Bei bekannter E-Mail (IMAP-Eingang oder manuelle Auswahl) wird die Person automatisch verknüpft.
ALTER TABLE `tickets`
ADD COLUMN `requester_person_id` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'FK zu directory_persons (auto-match über E-Mail oder manuell)'
AFTER `from_name`;
ALTER TABLE `tickets`
ADD CONSTRAINT `fk_tickets_requester_person`
FOREIGN KEY (`requester_person_id`) REFERENCES `directory_persons`(`id`)
ON DELETE SET NULL;
-- Migration: 052_lending_borrower_person_id.sql
-- Migration 052: Ausleiher-Person-Verknüpfung für loan_lendings
-- Ergänzt borrower_name/borrower_email um eine FK auf directory_persons.
-- Ermöglicht die Verknüpfung des Ausleihers mit einem Eintrag aus dem Personenverzeichnis.
ALTER TABLE `loan_lendings`
ADD COLUMN `borrower_person_id` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'FK zu directory_persons (manuell oder Auswahl aus Verzeichnis)'
AFTER `borrower_name`;
ALTER TABLE `loan_lendings`
ADD CONSTRAINT `fk_loan_lendings_borrower_person`
FOREIGN KEY (`borrower_person_id`) REFERENCES `directory_persons`(`id`)
ON DELETE SET NULL;
-- Migration: 053_loan_overdue_notification.sql
-- Überfälligkeits-Tracking für Leihvorgänge
ALTER TABLE loan_lendings
ADD COLUMN overdue_notified_at DATETIME DEFAULT NULL;
-- E-Mail-Vorlage für Überfälligkeitserinnerung (Leihgeräte)
INSERT INTO email_templates (template_key, subject, body_html, description)
VALUES (
'loan_overdue',
'Erinnerung: Leihgerät «{{device_title}}» ist überfällig',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:-apple-system,BlinkMacSystemFont,''Segoe UI'',Roboto,''Helvetica Neue'',Arial,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 8px rgba(0,0,0,0.08);">
<!-- Header -->
<tr>
<td style="background:linear-gradient(135deg,#78350f 0%,#d97706 60%,#fbbf24 100%);padding:32px 40px;text-align:center;">
<div style="font-size:28px;font-weight:900;color:#fff;letter-spacing:-0.5px;">{{app_name}}</div>
<div style="font-size:13px;color:rgba(255,255,255,0.75);margin-top:4px;">IT-Dokumentation</div>
</td>
</tr>
<!-- Warnung-Banner -->
<tr>
<td style="background:#fef2f2;border-left:4px solid #dc2626;padding:16px 40px;">
<p style="margin:0;font-size:15px;font-weight:700;color:#991b1b;">⚠️ Leihgerät überfällig</p>
<p style="margin:4px 0 0;font-size:13px;color:#b91c1c;">Das geplante Rückgabedatum ist bereits überschritten.</p>
</td>
</tr>
<!-- Body -->
<tr>
<td style="padding:32px 40px;">
<p style="margin:0 0 16px;font-size:16px;color:#1f2937;">Hallo <strong>{{borrower_name}}</strong>,</p>
<p style="margin:0 0 20px;font-size:15px;color:#374151;line-height:1.7;">
wir möchten Sie freundlich daran erinnern, dass das folgende Leihgerät bereits seit dem
<strong>{{planned_end}}</strong> zur Rückgabe fällig war:
</p>
<!-- Gerät-Box -->
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:8px;margin-bottom:24px;">
<tr>
<td style="padding:20px 24px;">
<div style="font-size:18px;font-weight:700;color:#111827;margin-bottom:8px;">💻 {{device_title}}</div>
<table cellpadding="0" cellspacing="0">
<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;">Typ:</td>
<td style="font-size:13px;color:#374151;">{{device_type}}</td>
</tr>
<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;">Ausgeliehen seit:</td>
<td style="font-size:13px;color:#374151;">{{planned_start}}</td>
</tr>
<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;">Geplante Rückgabe:</td>
<td style="font-size:13px;color:#dc2626;font-weight:700;">{{planned_end}}</td>
</tr>
{{#serial}}<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;">Seriennummer:</td>
<td style="font-size:13px;color:#374151;font-family:monospace;">{{serial_number}}</td>
</tr>{{/serial}}
</table>
</td>
</tr>
</table>
<p style="margin:0 0 20px;font-size:15px;color:#374151;line-height:1.7;">
Bitte geben Sie das Gerät so bald wie möglich zurück.<br>
Falls Sie das Gerät länger benötigen, melden Sie sich bitte beim Ausgeber, damit der Leihvorgang verlängert werden kann.
</p>
<!-- CTA Button -->
<table cellpadding="0" cellspacing="0" style="margin-bottom:24px;">
<tr>
<td style="background:#d97706;border-radius:8px;padding:12px 28px;">
<a href="{{lending_url}}" style="color:#fff;text-decoration:none;font-size:15px;font-weight:700;">Leihvorgang ansehen →</a>
</td>
</tr>
</table>
<p style="margin:0;font-size:14px;color:#6b7280;line-height:1.7;">
Mit freundlichen Grüßen<br>
<strong>{{app_name}}</strong>
</p>
</td>
</tr>
<!-- Footer -->
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 40px;text-align:center;">
<p style="margin:0;font-size:12px;color:#9ca3af;">
Diese E-Mail wurde automatisch von {{app_name}} generiert.<br>
<a href="{{base_url}}/loans" style="color:#d97706;text-decoration:none;">Zur Leihgeräte-Übersicht</a>
</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
'Wird automatisch gesendet wenn ein Leihgerät überfällig ist (geplante Rückgabe überschritten). Empfänger: Ausleiher (CC: Ausgeber).'
)
ON DUPLICATE KEY UPDATE template_key = template_key;
-- Migration: 054_todo_deadline.sql
-- ============================================================
-- 054: Deadline-Unterstützung für persönliche To-Dos
-- ============================================================
ALTER TABLE `personal_todos`
ADD COLUMN `due_date` DATE NULL DEFAULT NULL AFTER `notes`,
ADD COLUMN `due_notified_at` DATETIME NULL DEFAULT NULL AFTER `due_date`;
-- E-Mail-Vorlage für To-Do-Fälligkeitserinnerung
INSERT INTO email_templates (template_key, subject, body_html, description)
VALUES (
'todo_due_reminder',
'Erinnerung: To-Do «{{todo_title}}» ist morgen fällig',
'<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
</head>
<body style="margin:0;padding:0;background:#f3f4f6;font-family:-apple-system,BlinkMacSystemFont,''Segoe UI'',Roboto,''Helvetica Neue'',Arial,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:32px 16px;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 8px rgba(0,0,0,0.08);">
<!-- Header -->
<tr>
<td style="background:linear-gradient(135deg,#4338ca 0%,#6366f1 60%,#818cf8 100%);padding:32px 40px;text-align:center;">
<div style="font-size:28px;font-weight:900;color:#fff;letter-spacing:-0.5px;">{{app_name}}</div>
<div style="font-size:13px;color:rgba(255,255,255,0.75);margin-top:4px;">IT-Dokumentation</div>
</td>
</tr>
<!-- Hinweis-Banner -->
<tr>
<td style="background:#eff6ff;border-left:4px solid #3b82f6;padding:16px 40px;">
<p style="margin:0;font-size:15px;font-weight:700;color:#1e40af;">📋 To-Do fällig morgen</p>
<p style="margin:4px 0 0;font-size:13px;color:#1d4ed8;">Das Fälligkeitsdatum Ihres To-Dos rückt näher.</p>
</td>
</tr>
<!-- Body -->
<tr>
<td style="padding:32px 40px;">
<p style="margin:0 0 16px;font-size:16px;color:#1f2937;">Hallo <strong>{{user_name}}</strong>,</p>
<p style="margin:0 0 20px;font-size:15px;color:#374151;line-height:1.7;">
wir möchten Sie daran erinnern, dass folgendes To-Do morgen fällig ist:
</p>
<!-- To-Do-Box -->
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f9fafb;border:1px solid #e5e7eb;border-radius:8px;margin-bottom:24px;">
<tr>
<td style="padding:20px 24px;">
<div style="font-size:18px;font-weight:700;color:#111827;margin-bottom:12px;">✅ {{todo_title}}</div>
<table cellpadding="0" cellspacing="0">
<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;">Fällig am:</td>
<td style="font-size:13px;color:#4f46e5;font-weight:700;">{{due_date}}</td>
</tr>
{{#notes}}<tr>
<td style="font-size:13px;color:#9ca3af;padding-right:8px;padding-top:6px;vertical-align:top;">Notiz:</td>
<td style="font-size:13px;color:#374151;padding-top:6px;">{{notes}}</td>
</tr>{{/notes}}
</table>
</td>
</tr>
</table>
<!-- CTA Button -->
<table cellpadding="0" cellspacing="0" style="margin-bottom:24px;">
<tr>
<td style="background:#4f46e5;border-radius:8px;padding:12px 28px;">
<a href="{{todos_url}}" style="color:#fff;text-decoration:none;font-size:15px;font-weight:700;">Zur To-Do-Liste →</a>
</td>
</tr>
</table>
<p style="margin:0;font-size:14px;color:#6b7280;line-height:1.7;">
Mit freundlichen Grüßen<br>
<strong>{{app_name}}</strong>
</p>
</td>
</tr>
<!-- Footer -->
<tr>
<td style="background:#f9fafb;border-top:1px solid #e5e7eb;padding:20px 40px;text-align:center;">
<p style="margin:0;font-size:12px;color:#9ca3af;">
Diese E-Mail wurde automatisch von {{app_name}} generiert.<br>
<a href="{{todos_url}}" style="color:#6366f1;text-decoration:none;">Zur To-Do-Liste</a>
</p>
</td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>',
'Wird einen Tag vor Fälligkeit eines persönlichen To-Dos gesendet. Empfänger: der Ersteller des To-Dos.'
)
ON DUPLICATE KEY UPDATE template_key = template_key;
-- Migration: 055_monitoring_events.sql
CREATE TABLE IF NOT EXISTS `monitoring_events` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`source` VARCHAR(50) NOT NULL DEFAULT 'zabbix',
`external_id` VARCHAR(255) NULL,
`host` VARCHAR(255) NOT NULL DEFAULT '',
`hostname` VARCHAR(500) NOT NULL DEFAULT '',
`trigger_name` VARCHAR(500) NOT NULL DEFAULT '',
`severity` ENUM('not_classified','information','warning','average','high','disaster') NOT NULL DEFAULT 'warning',
`status` ENUM('problem','resolved') NOT NULL DEFAULT 'problem',
`message` TEXT NULL,
`trigger_url` VARCHAR(1000) NULL,
`raw_payload` LONGTEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`resolved_at` DATETIME NULL,
`acknowledged_at` DATETIME NULL,
`acknowledged_by` INT UNSIGNED NULL,
INDEX `idx_status` (`status`),
INDEX `idx_severity` (`severity`),
INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `monitoring_settings` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`setting_key` VARCHAR(100) NOT NULL UNIQUE,
`setting_value` TEXT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO `monitoring_settings` (`setting_key`, `setting_value`)
VALUES ('webhook_token', NULL);
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_monitoring', 'Monitoring-Ereignisse anzeigen', 'monitoring'),
('manage_monitoring', 'Monitoring-Ereignisse verwalten und löschen', 'monitoring');
-- Migration: 056_monitoring_zabbix_api.sql
INSERT IGNORE INTO `monitoring_settings` (`setting_key`, `setting_value`) VALUES
('zabbix_api_url', NULL),
('zabbix_api_token', NULL),
('zabbix_api_user', NULL),
('zabbix_api_pass', NULL),
('zabbix_last_sync', NULL),
('zabbix_sync_mode', 'webhook');
-- Migration: 057_stock.sql
-- ============================================================
-- 057: Lager / Gerätebestand
-- ============================================================
-- Kategorien (Monitor, Laptop, ThinClient, Maus, ...)
CREATE TABLE IF NOT EXISTS `stock_categories` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL,
`icon` VARCHAR(10) NOT NULL DEFAULT '📦',
`sort_order` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Artikel im Lager
CREATE TABLE IF NOT EXISTS `stock_items` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`category_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
`model` VARCHAR(255) NULL COMMENT 'Modellbezeichnung / Artikelnummer',
`quantity` INT NOT NULL DEFAULT 0,
`min_quantity` INT NOT NULL DEFAULT 0 COMMENT 'Mindestbestand für Warnung',
`location` VARCHAR(255) NULL COMMENT 'Lagerörtlichkeit (Regal, Raum, ...)',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`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 `stock_categories`(`id`) ON DELETE RESTRICT,
INDEX (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Buchungsbewegungen (Ein- / Ausbuchungen)
CREATE TABLE IF NOT EXISTS `stock_movements` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`item_id` INT UNSIGNED NOT NULL,
`type` ENUM('in','out') NOT NULL,
`quantity` INT NOT NULL,
`reason` VARCHAR(500) NULL COMMENT 'Begründung / Empfänger',
`user_id` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`item_id`) REFERENCES `stock_items`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
INDEX (`item_id`),
INDEX (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-Kategorien
INSERT IGNORE INTO `stock_categories` (`name`, `icon`, `sort_order`) VALUES
('Laptop', '💻', 10),
('Monitor', '🖥️', 20),
('ThinClient', '🖳', 30),
('Maus', '🖱️', 40),
('Tastatur', '⌨️', 50),
('Headset', '🎧', 60),
('Netzteil', '🔌', 70),
('Sonstiges', '📦', 99);
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_stock', 'Lagerbestand einsehen', 'stock'),
('manage_stock', 'Lagerbestand verwalten (Ein-/Ausbuchen, Artikel anlegen)', 'stock');
-- Migration: 058_ticket_delete_capability.sql
-- Migration: Add delete_closed_tickets capability
-- Allows users with this right to delete closed tickets from the frontend
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('delete_closed_tickets', 'Geschlossene Tickets löschen', 'tickets');
-- Migration: 059_ticket_internal_notes.sql
ALTER TABLE `ticket_replies`
ADD COLUMN `is_internal` TINYINT(1) NOT NULL DEFAULT 0 AFTER `body`;
-- Migration: 060_dell_warranty_cache.sql
-- ============================================================
-- 060 Dell Warranty Cache
-- ============================================================
CREATE TABLE IF NOT EXISTS `dell_warranty_cache` (
`service_tag` VARCHAR(50) NOT NULL PRIMARY KEY,
`raw_json` MEDIUMTEXT NOT NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`error` VARCHAR(500) NULL COMMENT 'Fehlermeldung falls Abruf fehlschlug'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 061_hp_warranty_cache.sql
-- ============================================================
-- 061 HP Warranty Cache
-- ============================================================
CREATE TABLE IF NOT EXISTS `hp_warranty_cache` (
`serial_number` VARCHAR(100) NOT NULL PRIMARY KEY,
`raw_json` MEDIUMTEXT NOT NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`error` VARCHAR(500) NULL COMMENT 'Fehlermeldung falls Abruf fehlschlug'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 062_loan_signatures.sql
-- Migration 062: Unterschriften-Felder für Leihvorgänge
-- Ermöglicht das Einholen von Unterschriften via Tablet-Seite
ALTER TABLE loan_lendings
ADD COLUMN signature_token VARCHAR(64) NULL DEFAULT NULL AFTER return_notes,
ADD COLUMN signature_requested_at DATETIME NULL DEFAULT NULL AFTER signature_token,
ADD COLUMN signature_data LONGTEXT NULL DEFAULT NULL AFTER signature_requested_at,
ADD COLUMN signature_obtained_at DATETIME NULL DEFAULT NULL AFTER signature_data,
ADD INDEX idx_loan_signature_token (signature_token);
-- Migration: 063_manufacturer_support_url.sql
-- Migration 063: Support-URL bei Herstellern
ALTER TABLE `asset_manufacturers`
ADD COLUMN `support_url` VARCHAR(500) NULL AFTER `website`;
-- Migration: 064_unifi.sql
-- ============================================================
-- Migration: UniFi Controller Integration
-- Version: 1.0.64
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Gecachte Gerätedaten aus UniFi (Switches, APs, Gateways)
CREATE TABLE IF NOT EXISTS `unifi_devices` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`site_id` VARCHAR(100) NOT NULL COMMENT 'UniFi Site-ID (z.B. default)',
`site_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Anzeigename der Site',
`device_id` VARCHAR(255) NOT NULL COMMENT 'Interner UniFi _id',
`mac` VARCHAR(17) NOT NULL COMMENT 'MAC-Adresse des Geräts',
`ip` VARCHAR(45) NULL COMMENT 'IP-Adresse',
`name` VARCHAR(255) NULL COMMENT 'Gerätename (alias oder hostname)',
`model` VARCHAR(100) NULL COMMENT 'Modellbezeichnung',
`type` VARCHAR(10) NOT NULL DEFAULT 'other' COMMENT 'uap|usw|ugw|udm|uxg|ubb|other',
`state` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=offline,1=online,2=pending,4=upgrading',
`uptime` INT UNSIGNED NULL COMMENT 'Uptime in Sekunden',
`firmware` VARCHAR(50) NULL COMMENT 'Firmware-Version',
`last_seen` DATETIME NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_site_device` (site_id, device_id),
KEY `idx_unifi_site` (site_id),
KEY `idx_unifi_state` (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte UniFi Gerätedaten pro Site';
-- Gecachte Netzwerk-/VLAN-Daten
CREATE TABLE IF NOT EXISTS `unifi_networks` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`site_id` VARCHAR(100) NOT NULL,
`site_name` VARCHAR(255) NOT NULL DEFAULT '',
`network_id` VARCHAR(255) NOT NULL COMMENT 'UniFi _id des Netzwerks',
`name` VARCHAR(255) NOT NULL COMMENT 'Netzwerkname',
`vlan_id` SMALLINT UNSIGNED NULL COMMENT 'VLAN-Tag (1-4094)',
`subnet` VARCHAR(50) NULL COMMENT 'Subnetz in CIDR-Notation',
`purpose` VARCHAR(50) NULL COMMENT 'corporate|guest|wan|...',
`is_guest` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 wenn Gast-WLAN Voucher-Auth',
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_site_network` (site_id, network_id),
KEY `idx_unifi_net_site` (site_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte UniFi Netzwerk- und VLAN-Daten';
-- Capabilities (category ist seit Migration 042 VARCHAR(50))
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_unifi', 'UniFi Gerätestatus anzeigen', 'unifi', 'low'),
('manage_unifi', 'UniFi IPAM- und VLAN-Sync ausführen', 'unifi', 'medium'),
('manage_unifi_vouchers', 'UniFi Gast-Voucher erstellen', 'unifi', 'medium');
SET FOREIGN_KEY_CHECKS = 1;
-- Migration: 065_system_settings.sql
-- Migration 065: system_settings Tabelle
-- Allgemeine Key-Value-Tabelle für systemweite Einstellungen.
-- Notschalter für lokalen Login:
-- INSERT INTO system_settings (`key`, `value`) VALUES ('local_login_force_enabled', '1')
-- ON DUPLICATE KEY UPDATE `value` = '1';
-- Zum Deaktivieren des Notschalters:
-- UPDATE system_settings SET `value` = '0' WHERE `key` = 'local_login_force_enabled';
CREATE TABLE IF NOT EXISTS `system_settings` (
`key` VARCHAR(100) NOT NULL,
`value` TEXT NOT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 066_page_locks.sql
-- Migration 066: Sperren für gleichzeitiges Bearbeiten von Seiten
-- Wenn ein Benutzer eine Seite öffnet, wird ein Lock gesetzt (2 Min TTL).
-- Heartbeat erneuert den Lock alle 60s. Bei Speichern/Verlassen wird er gelöscht.
CREATE TABLE IF NOT EXISTS `page_locks` (
`page_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`user_name` VARCHAR(255) NOT NULL DEFAULT '',
`locked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expires_at` TIMESTAMP NOT NULL,
PRIMARY KEY (`page_id`),
CONSTRAINT `fk_page_locks_page` FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_page_locks_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 067_ticket_last_reply_placeholder.sql
-- Migration 067: Add {{last_reply_text}} placeholder block to solved/closed email templates
-- Adds an optional "last reply" section so customers see the last staff reply when their
-- ticket is marked as solved or closed.
UPDATE `ticket_email_templates`
SET `body` = REPLACE(
`body`,
'{{ticket_subject}}</p>',
'{{ticket_subject}}</p>{{#if last_reply_text}}<p style="margin:1rem 0 0.25rem;"><strong>Letzte Antwort unseres Teams:</strong></p><blockquote style="margin:0.5rem 0 1rem;padding:0.75rem 1rem;background:#f9fafb;border-left:4px solid #e5e7eb;color:#374151;border-radius:0 0.375rem 0.375rem 0;">{{last_reply_text}}</blockquote>{{/if}}'
)
WHERE `event_type` IN ('solved', 'closed')
AND `body` LIKE '%{{ticket_subject}}</p>%';
-- Migration: 068_unifi_vouchers_cache.sql
-- ============================================================
-- Migration 068: UniFi Voucher Cache
-- Cached voucher data per site so the vouchers page is fast.
-- ============================================================
CREATE TABLE IF NOT EXISTS `unifi_vouchers` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`site_id` VARCHAR(100) NOT NULL COMMENT 'UniFi Site-ID (z.B. default)',
`site_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Anzeigename der Site',
`voucher_id` VARCHAR(255) NOT NULL COMMENT 'UniFi interner _id',
`code` VARCHAR(50) NOT NULL,
`quota` SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Max. Nutzungen (0=unbegrenzt)',
`duration` INT UNSIGNED NOT NULL DEFAULT 1440 COMMENT 'Gültigkeitsdauer in Minuten',
`used` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
`note` VARCHAR(255) NULL,
`expires_at` DATETIME NULL,
`created_at` DATETIME NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_site_voucher` (site_id, voucher_id),
KEY `idx_unifi_voucher_site` (site_id),
KEY `idx_unifi_voucher_expires` (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte UniFi Voucher-Daten pro Site';
-- Migration: 069_unifi_sites.sql
-- Migration 069: UniFi Site-Metadaten Cache
-- Speichert je Site ob ein Voucher-WLAN (Guest Portal mit Voucher-Auth) vorhanden ist.
-- Wird beim Gerätedaten-Refresh befüllt.
CREATE TABLE IF NOT EXISTS `unifi_sites` (
`site_id` VARCHAR(100) NOT NULL,
`site_name` VARCHAR(255) NOT NULL DEFAULT '',
`has_voucher_wlan` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 wenn min. 1 Gast-WLAN mit Voucher-Auth vorhanden',
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte UniFi Site-Metadaten (inkl. Voucher-Fähigkeit)';
-- Migration: 070_ticket_subject_brackets.sql
-- Migration 070: Ticket-E-Mail-Subjects auf [Ticket #ID]-Format umstellen
-- Damit erkennt extractTicketIdFromSubject Antworten korrekt als zugehörig.
UPDATE `ticket_email_templates` SET
`subject` = '[Ticket #{{ticket_id}}] Ihre Anfrage wurde erfolgreich erstellt'
WHERE `event_type` = 'created'
AND `subject` NOT LIKE '\[Ticket #%';
UPDATE `ticket_email_templates` SET
`subject` = '[Ticket #{{ticket_id}}] Neue Antwort: {{ticket_subject}}'
WHERE `event_type` = 'reply'
AND `subject` NOT LIKE '\[Ticket #%';
UPDATE `ticket_email_templates` SET
`subject` = '[Ticket #{{ticket_id}}] Ihre Anfrage wurde als gelöst markiert'
WHERE `event_type` = 'solved'
AND `subject` NOT LIKE '\[Ticket #%';
UPDATE `ticket_email_templates` SET
`subject` = '[Ticket #{{ticket_id}}] Ihre Anfrage wurde geschlossen'
WHERE `event_type` = 'closed'
AND `subject` NOT LIKE '\[Ticket #%';
UPDATE `ticket_email_templates` SET
`subject` = '[Ticket #{{ticket_id}}] Ihre Anfrage wurde zugewiesen'
WHERE `event_type` = 'assigned'
AND `subject` NOT LIKE '\[Ticket #%';
-- Migration: 071_backup_veeam.sql
-- ============================================================
-- Migration: Veeam Backup Integration
-- Version: 1.0.71
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Einstellungen (Key-Value wie monitoring_settings)
CREATE TABLE IF NOT EXISTS `backup_settings` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`setting_key` VARCHAR(100) NOT NULL,
`setting_value` TEXT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uq_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Veeam Backup Konfiguration';
-- Gecachte Job-Daten
CREATE TABLE IF NOT EXISTS `backup_jobs_cache` (
`id` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`type` VARCHAR(100) NULL,
`last_status` VARCHAR(20) NOT NULL DEFAULT 'none' COMMENT 'success|warning|failed|running|none',
`last_run` DATETIME NULL,
`next_run` DATETIME NULL,
`duration_sec` INT UNSIGNED NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte Veeam Backup-Job-Daten';
-- Gecachte Repository-Daten
CREATE TABLE IF NOT EXISTS `backup_repositories_cache` (
`id` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`type` VARCHAR(100) NULL,
`path` VARCHAR(500) NULL,
`capacity_gb` DECIMAL(14,2) NULL COMMENT 'Gesamtkapazität in GB',
`free_space_gb` DECIMAL(14,2) NULL COMMENT 'Freier Speicher in GB',
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gecachte Veeam Repository-Speicherdaten';
-- Capabilities (category ist seit Migration 042 VARCHAR(50))
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`) VALUES
('view_backup', 'Backup-Übersicht anzeigen', 'backup'),
('manage_backup', 'Backup-Einstellungen verwalten', 'backup');
SET FOREIGN_KEY_CHECKS = 1;
-- Migration: 072_backup_jobs_extra_fields.sql
-- ============================================================
-- Migration: Veeam Backup Jobs – erweiterte Felder
-- Version: 1.0.72
-- ============================================================
SET NAMES utf8mb4;
ALTER TABLE `backup_jobs_cache`
ADD COLUMN `schedule_info` VARCHAR(100) NULL COMMENT 'Lesbare Zeitplan-Beschreibung (Täglich, Mo–Fr …)' AFTER `next_run`,
ADD COLUMN `is_disabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `schedule_info`,
ADD COLUMN `repository_id` VARCHAR(255) NULL COMMENT 'Veeam Repository-ID (aus job.storage.backupRepositoryId)' AFTER `is_disabled`;
-- Migration: 072_hpe_warranty_cache.sql
-- ============================================================
-- 072 HPE Warranty Cache
-- ============================================================
CREATE TABLE IF NOT EXISTS `hpe_warranty_cache` (
`serial_number` VARCHAR(100) NOT NULL PRIMARY KEY,
`raw_json` MEDIUMTEXT NOT NULL,
`fetched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`error` VARCHAR(500) NULL COMMENT 'Fehlermeldung falls Abruf fehlschlug'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 073_manufacturer_warranty_provider.sql
-- ============================================================
-- 073 Hersteller: warranty_provider-Feld
-- Verknüpft einen Hersteller explizit mit einem Garantie-Dienst
-- statt auf String-Matching im Namen zu angewiesen zu sein.
-- Mögliche Werte: NULL | 'hp' | 'hpe' | 'dell'
-- ============================================================
ALTER TABLE `asset_manufacturers`
ADD COLUMN `warranty_provider` ENUM('hp','hpe','dell') NULL DEFAULT NULL
COMMENT 'Garantie-API-Anbieter (hp = HP Inc., hpe = HPE, dell = Dell)'
AFTER `support_url`;
-- Zwei fest definierte Hersteller eintragen (nur falls noch nicht vorhanden – kein Duplikat)
INSERT INTO `asset_manufacturers` (`name`, `website`, `support_url`, `warranty_provider`, `notes`)
SELECT 'HP Inc.', 'https://www.hp.com', 'https://support.hp.com', 'hp',
'HP Inc. – PCs, Notebooks, Drucker, Monitore (früher: Hewlett-Packard PSG)'
WHERE NOT EXISTS (SELECT 1 FROM `asset_manufacturers` WHERE `name` = 'HP Inc.' LIMIT 1);
INSERT INTO `asset_manufacturers` (`name`, `website`, `support_url`, `warranty_provider`, `notes`)
SELECT 'HPE', 'https://www.hpe.com', 'https://support.hpe.com', 'hpe',
'Hewlett Packard Enterprise – Server, Storage, Networking (ProLiant, Aruba, Nimble)'
WHERE NOT EXISTS (SELECT 1 FROM `asset_manufacturers` WHERE `name` = 'HPE' LIMIT 1);
-- Migration: 074_file_explorer.sql
-- File Explorer: Folders
CREATE TABLE IF NOT EXISTS file_explorer_folders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent_id BIGINT UNSIGNED NULL,
name VARCHAR(255) NOT NULL,
path TEXT NOT NULL COMMENT 'Materialized path like /1/5/12/',
created_by BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES file_explorer_folders(id) ON DELETE CASCADE,
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);
-- File Explorer: Files
CREATE TABLE IF NOT EXISTS file_explorer_files (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
folder_id BIGINT UNSIGNED NULL COMMENT 'NULL = root folder',
original_name VARCHAR(255) NOT NULL,
stored_name VARCHAR(255) NOT NULL COMMENT 'UUID-based filename on disk',
mime_type VARCHAR(150) DEFAULT NULL,
file_size BIGINT NOT NULL DEFAULT 0,
uploaded_by BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (folder_id) REFERENCES file_explorer_folders(id) ON DELETE CASCADE,
FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
);
-- Capabilities
INSERT IGNORE INTO capabilities (name, description, category, risk_level) VALUES
('view_files', 'Datei-Explorer ansehen', 'files', 'low'),
('upload_files', 'Dateien hochladen', 'files', 'medium'),
('delete_files', 'Dateien löschen', 'files', 'medium'),
('manage_file_folders', 'Ordner erstellen und löschen', 'files', 'medium');
-- Migration: 075_user_totp.sql
-- Migration 075: MFA/TOTP für lokale Benutzerkonten
ALTER TABLE users
ADD COLUMN totp_secret VARCHAR(64) NULL DEFAULT NULL AFTER password_hash,
ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER totp_secret;
-- Migration: 076_maintenance_calendar.sql
-- Migration 076: Wartungskalender – manuelle Ereignisse
CREATE TABLE IF NOT EXISTS `maintenance_events` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
`event_date` DATE NOT NULL,
`end_date` DATE NULL COMMENT 'optional: mehrtägige Ereignisse',
`type` ENUM('maintenance','review','renewal','other') NOT NULL DEFAULT 'maintenance',
`source` ENUM('manual','ssl','asset','contract') NOT NULL DEFAULT 'manual',
`ref_id` BIGINT UNSIGNED NULL COMMENT 'ID des verknüpften Objekts (Asset, Vertrag, SSL)',
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_maint_date` (`event_date`),
CONSTRAINT `fk_maint_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 077_unifi_uplink.sql
-- Migration 077: Echte Uplink-Topologiedaten für UniFi-Geräte
ALTER TABLE `unifi_devices`
ADD COLUMN `uplink_mac` VARCHAR(17) NULL DEFAULT NULL
COMMENT 'MAC des übergeordneten Geräts (aus uplink.uplink_mac)' AFTER `last_seen`,
ADD COLUMN `uplink_type` VARCHAR(10) NULL DEFAULT NULL
COMMENT 'wire | wireless' AFTER `uplink_mac`,
ADD COLUMN `uplink_port` SMALLINT UNSIGNED NULL DEFAULT NULL
COMMENT 'Port-Nr am übergeordneten Gerät' AFTER `uplink_type`;
-- Migration: 078_network_diagrams.sql
-- Migration 078: Netzwerkpläne (interaktiver Diagramm-Editor)
CREATE TABLE IF NOT EXISTS `network_diagrams` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
`data` LONGTEXT NULL DEFAULT NULL
COMMENT 'JSON: {nodes:[{id,type,label,x,y,description}], edges:[{id,from,to,label,style}]}',
`created_by` INT UNSIGNED NULL DEFAULT NULL,
`updated_by` INT UNSIGNED NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_netdiagram_created_by` (`created_by`),
KEY `idx_netdiagram_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Interaktive Netzwerkpläne';
-- Capabilities
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_netdiagram', 'Netzwerkpläne ansehen', 'page', 'low'),
('edit_netdiagram', 'Netzwerkpläne erstellen und bearbeiten', 'page', 'medium');
-- Migration: 079_webauthn_credentials.sql
-- WebAuthn / FIDO2 Sicherheitsschlüssel (YubiKey etc.)
CREATE TABLE IF NOT EXISTS `webauthn_credentials` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`credential_id` VARCHAR(700) NOT NULL COMMENT 'Base64url-kodierte Credential-ID',
`public_key` TEXT NOT NULL COMMENT 'PEM Public Key',
`sign_count` INT UNSIGNED NOT NULL DEFAULT 0,
`device_name` VARCHAR(255) NOT NULL DEFAULT 'Sicherheitsschlüssel',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_used_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_webauthn_cred_id` (`credential_id`(191)),
INDEX `idx_webauthn_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='FIDO2/WebAuthn Sicherheitsschlüssel der Benutzer';
-- Migration: 080_netdiagram_device_types.sql
-- Verwaltbare Gerätetypen für Netzwerkpläne
CREATE TABLE IF NOT EXISTS `netdiagram_device_types` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`type_key` VARCHAR(64) NOT NULL COMMENT 'Eindeutiger Schlüssel, z.B. switch, router',
`label` VARCHAR(100) NOT NULL COMMENT 'Anzeigename',
`icon_type` ENUM('emoji','image') NOT NULL DEFAULT 'emoji',
`icon_value` VARCHAR(512) NOT NULL DEFAULT '◆' COMMENT 'Unicode-Char oder Pfad zum hochgeladenen Bild',
`bg_color` CHAR(7) NOT NULL DEFAULT '#f1f5f9',
`stroke_color` CHAR(7) NOT NULL DEFAULT '#475569',
`sort_order` INT NOT NULL DEFAULT 0,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_nd_device_type_key` (`type_key`),
INDEX `idx_nd_device_sort` (`sort_order`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Gerätetypen für den Netzwerkplan-Editor';
-- Bestehende 8 Standard-Typen (Keys müssen mit gespeicherten Diagrammen übereinstimmen)
INSERT IGNORE INTO `netdiagram_device_types`
(`type_key`, `label`, `icon_type`, `icon_value`, `bg_color`, `stroke_color`, `sort_order`)
VALUES
('switch', 'Switch', 'emoji', '⇅', '#dbeafe', '#3b82f6', 10),
('router', 'Router', 'emoji', '↻', '#ede9fe', '#8b5cf6', 20),
('firewall', 'Firewall', 'emoji', '🛡', '#fee2e2', '#ef4444', 30),
('ap', 'WLAN-AP', 'emoji', '📡', '#d1fae5', '#10b981', 40),
('server', 'Server', 'emoji', '🖥', '#fef3c7', '#f59e0b', 50),
('pc', 'PC', 'emoji', '💻', '#f3f4f6', '#6b7280', 60),
('cloud', 'Cloud', 'emoji', '☁', '#cffafe', '#06b6d4', 70),
('generic', 'Gerät', 'emoji', '◆', '#f1f5f9', '#475569', 80);
-- Migration: 081_ticket_multi_assignees.sql
-- Migration 081: Mehrere Bearbeiter pro Ticket (Multi-Assignee)
--
-- Erstellt die Junction-Tabelle ticket_assignees und migriert vorhandene
-- Einzelzuweisungen (tickets.assignee_id) dorthin.
-- Das Feld tickets.assignee_id bleibt als "primärer Bearbeiter" erhalten,
-- damit bestehende Listen-Ansichten und E-Mail-Benachrichtigungen unverändert
-- weiterarbeiten.
CREATE TABLE IF NOT EXISTS `ticket_assignees` (
`ticket_id` INT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`assigned_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`ticket_id`, `user_id`),
FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Bestehende Einzelzuweisungen migrieren
INSERT IGNORE INTO `ticket_assignees` (`ticket_id`, `user_id`, `assigned_at`)
SELECT `id`, `assignee_id`, COALESCE(`updated_at`, `created_at`)
FROM `tickets`
WHERE `assignee_id` IS NOT NULL;
-- Migration: 082_ticket_watchers.sql
-- Migration 082: Ticket-Beobachter (Watchers)
--
-- Beobachter erhalten Antworten von Mitarbeitern per E-Mail,
-- aber KEINE Systembenachrichtigungen (erstellt, zugewiesen, gelöst, geschlossen).
-- Sie werden typischerweise aus dem CC-Feld eingehender E-Mails befüllt,
-- können aber auch manuell eingetragen werden.
CREATE TABLE IF NOT EXISTS `ticket_watchers` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`ticket_id` INT UNSIGNED NOT NULL,
`email` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NULL DEFAULT NULL,
`added_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uq_ticket_watcher` (`ticket_id`, `email`),
FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 083_ticket_assets.sql
-- Migration 083: Ticket ↔ Asset Verknüpfung
--
-- Ermöglicht die Zuordnung von Assets zu Tickets und umgekehrt,
-- sofern beide Module (tickets + assets) aktiviert sind.
-- n:m-Beziehung: ein Ticket kann mehrere Assets haben,
-- ein Asset kann in mehreren Tickets auftauchen.
CREATE TABLE IF NOT EXISTS `ticket_assets` (
`ticket_id` INT UNSIGNED NOT NULL,
`asset_id` INT UNSIGNED NOT NULL,
`linked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`linked_by` BIGINT UNSIGNED NULL,
PRIMARY KEY (`ticket_id`, `asset_id`),
FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 084_asset_manufacturers_unique_name.sql
-- ============================================================
-- Migration: Hersteller – Duplikat-Bereinigung & UNIQUE-Constraint
-- Version: 1.0.84
-- ============================================================
-- Verhindert, dass bei jedem Migrations-Durchlauf neue HP/HPE-Einträge
-- angelegt werden (INSERT IGNORE greift nur bei UNIQUE-Verletzungen).
-- ============================================================
SET NAMES utf8mb4;
-- 1. Duplikate bereinigen: Bei mehrfach vorhandenen Namen den Eintrag mit
-- warranty_provider behalten, alle anderen löschen.
DELETE am1 FROM `asset_manufacturers` am1
INNER JOIN `asset_manufacturers` am2
ON am1.name = am2.name
AND am1.id > am2.id
AND am2.warranty_provider IS NOT NULL;
-- Weitere Duplikate bereinigen (wenn keiner warranty_provider hat, den neueren löschen)
DELETE am1 FROM `asset_manufacturers` am1
INNER JOIN `asset_manufacturers` am2
ON am1.name = am2.name
AND am1.id > am2.id;
-- 2. UNIQUE-Index auf name setzen (verhindert zukünftige Duplikate)
ALTER TABLE `asset_manufacturers`
ADD UNIQUE KEY `uq_manufacturer_name` (`name`);
-- 3. HP Inc. und HPE sicher einfügen / aktualisieren (idempotent)
INSERT INTO `asset_manufacturers` (`name`, `website`, `support_url`, `warranty_provider`, `notes`)
VALUES
('HP Inc.',
'https://www.hp.com',
'https://support.hp.com',
'hp',
'HP Inc. – PCs, Notebooks, Drucker, Monitore (früher: Hewlett-Packard PSG)'),
('HPE',
'https://www.hpe.com',
'https://support.hpe.com',
'hpe',
'Hewlett Packard Enterprise – Server, Storage, Networking (ProLiant, Aruba, Nimble)')
ON DUPLICATE KEY UPDATE
website = VALUES(website),
support_url = VALUES(support_url),
warranty_provider= VALUES(warranty_provider),
notes = VALUES(notes);
-- Migration: 085_asset_is_loan_device.sql
-- ============================================================
-- Migration: Assets – Leihgerät-Kennzeichnung
-- Version: 1.0.85
-- ============================================================
-- Erlaubt es, ein Asset im Inventar als Leihgerät zu markieren.
-- Leihgeräte stehen dann direkt im Loans-Modul zum Verleihen
-- zur Verfügung (wird als loan_device angelegt/synchronisiert).
-- ============================================================
SET NAMES utf8mb4;
ALTER TABLE `assets`
ADD COLUMN `is_loan_device` TINYINT(1) NOT NULL DEFAULT 0
COMMENT 'Wenn 1: Gerät steht im Leihgeräte-Modul zur Verfügung'
AFTER `status`;
-- Migration: 086_loan_devices_asset_link.sql
-- ============================================================
-- Migration: Leihgeräte – Verknüpfung mit Asset-Inventar
-- Version: 1.0.86
-- ============================================================
-- Verbindet loan_devices mit dem Asset-Inventar.
-- Wenn ein Asset als Leihgerät markiert wird, wird automatisch
-- ein loan_device-Eintrag erstellt und dieser Spalte verknüpft.
-- ============================================================
SET NAMES utf8mb4;
ALTER TABLE `loan_devices`
ADD COLUMN `asset_id` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Verknüpftes Asset aus dem Inventar (optional)'
AFTER `id`,
ADD UNIQUE KEY `uq_loan_device_asset` (`asset_id`),
ADD CONSTRAINT `fk_loan_device_asset`
FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE SET NULL;
-- Migration: 087_contract_categories.sql
-- Migration 087: Vertragskategorien
-- Fügt eine frei definierbare Kategorienverwaltung für Verträge hinzu.
CREATE TABLE IF NOT EXISTS `contract_categories` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`color` VARCHAR(7) NOT NULL DEFAULT '#6b7280',
`description` VARCHAR(255) NULL DEFAULT NULL,
`sort_order` SMALLINT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_category_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `contracts`
ADD COLUMN `category_id` INT UNSIGNED NULL DEFAULT NULL AFTER `company_id`,
ADD CONSTRAINT `fk_contract_category`
FOREIGN KEY (`category_id`) REFERENCES `contract_categories`(`id`)
ON DELETE SET NULL,
ADD INDEX `idx_contracts_category` (`category_id`);
-- Migration: 088_asset_custom_fields.sql
-- Migration 088: Asset Custom Fields + Contract/Page ENUM-Erweiterungen
-- Fügt benutzerdefinierte Felder für Inventar-Kategorien und -Typen hinzu.
-- Erweitert außerdem cost_interval um 'one_time' und ansprechpartner_type um 'user'.
-- 1. Contracts: cost_interval ENUM erweitern
ALTER TABLE `contracts`
MODIFY COLUMN `cost_interval` ENUM('monthly','quarterly','yearly','one_time') NOT NULL DEFAULT 'monthly';
-- 2. Pages: ansprechpartner_type ENUM erweitern
ALTER TABLE `pages`
MODIFY COLUMN `ansprechpartner_type` ENUM('contact','company','user') NULL DEFAULT NULL;
-- 3. Benutzerdefinierte Felder pro Kategorie oder Typ
CREATE TABLE IF NOT EXISTS `asset_custom_fields` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`entity_type` ENUM('category','type') NOT NULL,
`entity_id` INT UNSIGNED NOT NULL,
`field_name` VARCHAR(100) NOT NULL,
`field_label` VARCHAR(255) NOT NULL,
`field_type` ENUM('text','dropdown') NOT NULL DEFAULT 'text',
`dropdown_options` TEXT NULL DEFAULT NULL COMMENT 'JSON-Array mit Optionen',
`sort_order` SMALLINT NOT NULL DEFAULT 0,
`is_required` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_acf_entity` (`entity_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 4. Werte der benutzerdefinierten Felder pro Asset
CREATE TABLE IF NOT EXISTS `asset_custom_field_values` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`asset_id` INT UNSIGNED NOT NULL,
`custom_field_id` INT UNSIGNED NOT NULL,
`field_value` TEXT NULL DEFAULT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_asset_field` (`asset_id`, `custom_field_id`),
CONSTRAINT `fk_acfv_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_acfv_field` FOREIGN KEY (`custom_field_id`) REFERENCES `asset_custom_fields`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Migration: 089_asset_custom_field_default_value.sql
-- Migration 089: Standard-Wert für benutzerdefinierte Felder
-- Ermöglicht das Hinterlegen eines Standard-Werts pro Custom Field,
-- der beim Anlegen eines neuen Assets automatisch vorausgefüllt wird.
ALTER TABLE `asset_custom_fields`
ADD COLUMN `default_value` TEXT NULL DEFAULT NULL AFTER `dropdown_options`;
-- Migration: 090_directory_person_image_and_ap_type.sql
-- Migration 090: Profilbild für Verzeichnis-Personen + 'directory' als Ansprechpartner-Typ
-- 1. Profilbild-Spalte für Verzeichnis-Personen
ALTER TABLE `directory_persons`
ADD COLUMN `image_path` VARCHAR(500) NULL DEFAULT NULL
COMMENT 'Relativer Pfad zum Profilbild, z.B. /uploads/directory-persons/1.jpg'
AFTER `phone`;
-- 2. 'directory' als neuen Ansprechpartner-Typ auf Seiten erlauben
ALTER TABLE `pages`
MODIFY COLUMN `ansprechpartner_type`
ENUM('contact','company','user','directory') NULL DEFAULT NULL;
-- Migration: 091_user_directory_person_link.sql
-- Verknüpfung: Benutzer ↔ Verzeichnis-Person
-- Ermöglicht automatischen Foto-Import und "Person zum Benutzer machen"
ALTER TABLE `users`
ADD COLUMN `directory_person_id` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'FK → directory_persons.id – verknüpfte Verzeichnis-Person'
AFTER `is_admin`,
ADD CONSTRAINT `fk_users_directory_person`
FOREIGN KEY (`directory_person_id`) REFERENCES `directory_persons` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE;
CREATE INDEX `idx_users_directory_person` ON `users` (`directory_person_id`);
-- Migration: 092_page_autosave_draft.sql
-- Autosave-Draft für Seiten
-- Speichert den Zwischen-Entwurf ohne Version oder Benachrichtigung zu erzeugen.
-- draft_content_html: wird beim Autosave befüllt, beim manuellen Speichern geleert
-- draft_saved_at: Zeitstempel des letzten Autosaves
ALTER TABLE `pages`
ADD COLUMN `draft_content_html` LONGTEXT NULL DEFAULT NULL
COMMENT 'Autosave-Entwurf, erzeugt keine Version'
AFTER `current_version_id`,
ADD COLUMN `draft_saved_at` DATETIME NULL DEFAULT NULL
COMMENT 'Zeitpunkt des letzten Autosaves'
AFTER `draft_content_html`;
-- Migration: 093_asset_handovers.sql
-- Migration 093: Asset-Übergaben (Ausgabe + Rücknahme) mit Unterschrift
-- Ermöglicht die dokumentierte Ausgabe und Rücknahme von Assets mit Unterschrift und PDF-Bestätigung.
-- Tabelle: Übergabe-Datensätze
CREATE TABLE IF NOT EXISTS `asset_handovers` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`asset_id` INT UNSIGNED NOT NULL,
`type` ENUM('handout', 'return') NOT NULL
COMMENT 'handout=Ausgabe, return=Rücknahme',
`person_name` VARCHAR(255) NOT NULL,
`person_email` VARCHAR(255) NULL,
`person_id` INT UNSIGNED NULL COMMENT 'Verknüpfung zu directory_persons',
-- Unterschrift (nur bei Ausgabe)
`signature_token` VARCHAR(64) NULL COMMENT '64-Hex-Token für Tablet-Unterschrift',
`signature_requested_at` DATETIME NULL,
`signature_data` LONGTEXT NULL COMMENT 'Base64-PNG der Unterschrift',
`signature_obtained_at` DATETIME NULL,
-- Generiertes PDF (Dateipfad relativ zu storage/)
`pdf_path` VARCHAR(500) NULL,
-- Notizen (vor allem für Rücknahme)
`notes` TEXT NULL,
`created_by` INT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_ah_asset` (`asset_id`),
INDEX `idx_ah_token` (`signature_token`),
INDEX `idx_ah_type` (`asset_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Tabelle: WYSIWYG-editierbare PDF-Templates
CREATE TABLE IF NOT EXISTS `asset_pdf_templates` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`template_key` VARCHAR(50) NOT NULL UNIQUE
COMMENT 'handout oder return',
`name` VARCHAR(255) NOT NULL,
`body_html` LONGTEXT NOT NULL COMMENT 'HTML-Body des PDFs mit {{Platzhaltern}}',
`updated_by` INT UNSIGNED NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard-PDF-Templates
INSERT INTO `asset_pdf_templates` (`template_key`, `name`, `body_html`) VALUES
('handout',
'Gerät-Ausgabe Bestätigung',
'<h2 style="text-align:center;color:#1e3a5f;border-bottom:2px solid #1e3a5f;padding-bottom:8px;">Bestätigung der Geräteübergabe</h2>
<p style="margin:16px 0;">Hiermit bestätige ich, <strong>{{person_name}}</strong>, den Erhalt des folgenden Geräts:</p>
<table style="width:100%;border-collapse:collapse;margin:16px 0;">
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;width:35%;">Inventarnummer</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_inventory_number}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Seriennummer</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_serial_number}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Hersteller / Modell</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_manufacturer}} {{asset_model}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Kategorie</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_category}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Ausgabedatum</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{handout_date}}</td></tr>
</table>
<p style="margin:16px 0;">Mit meiner Unterschrift bestätige ich die vollständige und unbeschädigte Übergabe des oben genannten Geräts. Ich bin für die sichere Aufbewahrung und ordnungsgemäße Verwendung verantwortlich.</p>
<div style="margin-top:32px;border-top:1px solid #e5e7eb;padding-top:16px;">
<p style="font-weight:bold;margin-bottom:8px;">Unterschrift des Empfängers:</p>
{{signature_image}}
<p style="margin-top:8px;font-size:10pt;color:#6b7280;">{{person_name}} – {{handout_date}}</p>
</div>'
),
('return',
'Gerät-Rücknahme Bestätigung',
'<h2 style="text-align:center;color:#1e3a5f;border-bottom:2px solid #1e3a5f;padding-bottom:8px;">Bestätigung der Geräterücknahme</h2>
<p style="margin:16px 0;">Hiermit bestätigen wir die Rückgabe des folgenden Geräts durch <strong>{{person_name}}</strong>:</p>
<table style="width:100%;border-collapse:collapse;margin:16px 0;">
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;width:35%;">Inventarnummer</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_inventory_number}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Seriennummer</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_serial_number}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Hersteller / Modell</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{asset_manufacturer}} {{asset_model}}</td></tr>
<tr><th style="text-align:left;padding:6px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Rückgabedatum</th><td style="padding:6px 10px;border:1px solid #e5e7eb;">{{return_date}}</td></tr>
</table>
{{notes_section}}
<p style="margin:16px 0;font-size:10pt;color:#374151;">Das Gerät wurde in einwandfreiem Zustand zurückgenommen und wieder eingelagert.</p>'
);
-- E-Mail-Vorlagen für Asset-Übergaben (erscheinen in /admin/notifications)
INSERT INTO `notification_templates`
(`template_key`, `title_template`, `message_template`, `type`, `description`, `is_enabled`)
VALUES
(
'asset_handout_confirmed',
'Bestätigung: Gerät {{asset_inventory_number}} erhalten',
'Hallo {{person_name}},\n\nhiermit bestätigen wir die Ausgabe des folgenden Geräts an Sie:\n\nInventarnummer: {{asset_inventory_number}}\nSeriennummer: {{asset_serial_number}}\nHersteller / Modell: {{asset_manufacturer}} {{asset_model}}\nAusgabedatum: {{handout_date}}\n\nIm Anhang finden Sie die Übergabebestätigung mit Ihrer Unterschrift als PDF.\n\nBei Fragen wenden Sie sich bitte an Ihre IT-Abteilung.',
'system',
'Wird nach der Unterschrift bei der Geräteausgabe an die zugewiesene Person gesendet.',
1
),
(
'asset_return_confirmed',
'Bestätigung: Gerät {{asset_inventory_number}} zurückgenommen',
'Hallo {{person_name}},\n\nhiermit bestätigen wir die Rückgabe des folgenden Geräts:\n\nInventarnummer: {{asset_inventory_number}}\nSeriennummer: {{asset_serial_number}}\nHersteller / Modell: {{asset_manufacturer}} {{asset_model}}\nRückgabedatum: {{return_date}}\n\nIm Anhang finden Sie die Rücknahmebestätigung als PDF.\n\nBei Fragen wenden Sie sich bitte an Ihre IT-Abteilung.',
'system',
'Wird bei der Geräterücknahme an die bisherige Nutzerin / den bisherigen Nutzer gesendet.',
1
)
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`);
-- Migration: 094_asset_email_templates.sql
-- Migration 094: Dedizierte E-Mail-Vorlagen für Asset-Übergaben
-- Separate Tabelle asset_email_templates (statt notification_templates, da externe Empfänger).
-- Außerdem Übergabe-Bedingungen für die Unterschriften-Seite.
-- Tabelle: E-Mail-Vorlagen für Ausgabe + Rücknahme
CREATE TABLE IF NOT EXISTS `asset_email_templates` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`template_key` VARCHAR(50) NOT NULL UNIQUE
COMMENT 'asset_handout_email oder asset_return_email',
`name` VARCHAR(255) NOT NULL,
`subject_template` VARCHAR(500) NOT NULL,
`body_html` LONGTEXT NOT NULL COMMENT 'HTML-Body mit {{Platzhaltern}}',
`is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
`updated_by` INT UNSIGNED NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Standard E-Mail-Vorlagen
INSERT INTO `asset_email_templates` (`template_key`, `name`, `subject_template`, `body_html`, `is_enabled`) VALUES
(
'asset_handout_email',
'Ausgabe-Bestätigung (E-Mail an Mitarbeiter)',
'Bestätigung: Gerät {{asset_inventory_number}} erhalten',
'<p>Hallo {{person_name}},</p>
<p>hiermit bestätigen wir die Ausgabe des folgenden Geräts an Sie:</p>
<table style="border-collapse:collapse;width:100%;margin:12px 0;">
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;width:40%;">Inventarnummer</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_inventory_number}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Seriennummer</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_serial_number}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Hersteller / Modell</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_manufacturer}} {{asset_model}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Ausgabedatum</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{handout_date}}</td></tr>
</table>
<p>Im Anhang finden Sie die Übergabebestätigung mit Ihrer Unterschrift als PDF.</p>
<p>Bei Fragen wenden Sie sich bitte an Ihre IT-Abteilung.</p>',
1
),
(
'asset_return_email',
'Rücknahme-Bestätigung (E-Mail an Mitarbeiter)',
'Bestätigung: Gerät {{asset_inventory_number}} zurückgenommen',
'<p>Hallo {{person_name}},</p>
<p>hiermit bestätigen wir die Rückgabe des folgenden Geräts:</p>
<table style="border-collapse:collapse;width:100%;margin:12px 0;">
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;width:40%;">Inventarnummer</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_inventory_number}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Seriennummer</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_serial_number}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Hersteller / Modell</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{asset_manufacturer}} {{asset_model}}</td></tr>
<tr><th style="text-align:left;padding:5px 10px;background:#f3f4f6;border:1px solid #e5e7eb;">Rückgabedatum</th><td style="padding:5px 10px;border:1px solid #e5e7eb;">{{return_date}}</td></tr>
</table>
<p>Im Anhang finden Sie die Rücknahmebestätigung als PDF.</p>
<p>Bei Fragen wenden Sie sich bitte an Ihre IT-Abteilung.</p>',
1
)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
-- Übergabe-Bedingungen (angezeigt auf der Unterschriften-Seite)
INSERT INTO `asset_pdf_templates` (`template_key`, `name`, `body_html`) VALUES
(
'handout_terms',
'Übergabe-Bedingungen (Unterschriften-Seite)',
'<h4>Datenschutzerklärung & Nutzungsbedingungen</h4>
<p>Der Arbeitgeber behält sich die Durchsicht und Kontrolle der Einzelverbindungsnachweise vor.</p>
<p>Die Geräte sind Eigentum des Unternehmens. Verlust oder Beschädigung der Geräte oder des Zubehörs ist umgehend der IT-Abteilung zu melden. Bei einem Ausscheiden aus dem Unternehmen sind die Geräte sowie Zubehör wieder abzugeben.</p>
<p>Mobilfunkverträge: Es fallen keine Kosten für Telefonate, SMS oder Internetnutzung innerhalb Deutschlands an. Die Nutzung im Ausland muss dem Arbeitgeber im Voraus mitgeteilt werden. Die Inanspruchnahme von Sonderrufnummern, Servicerufnummern sowie Abo-Diensten ist nicht gestattet.</p>'
)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
-- Veraltete Einträge aus notification_templates entfernen (wurden durch asset_email_templates ersetzt)
DELETE FROM `notification_templates`
WHERE `template_key` IN ('asset_handout_confirmed', 'asset_return_confirmed');
-- Migration: 095_netdiagram_drawio.sql
-- Migration 095: draw.io Integration
-- Erweitert die Tabelle um draw.io XML-Speicherung.
-- draw.io XML-Spalte + Format-Marker hinzufügen
ALTER TABLE network_diagrams
ADD COLUMN `drawio_xml` MEDIUMTEXT NULL COMMENT 'draw.io Diagramm-XML' AFTER `data`,
ADD COLUMN `diagram_format` ENUM('drawio') NOT NULL DEFAULT 'drawio' COMMENT 'Diagrammformat' AFTER `drawio_xml`;
-- Migration: 096_netdiagram_preview.sql
-- Migration 096: SVG Vorschau für Netzwerkpläne
-- Fügt eine Spalte für die automatisch generierte SVG-Vorschau hinzu.
ALTER TABLE network_diagrams
ADD COLUMN `preview_svg` MEDIUMTEXT NULL COMMENT 'Automatisch generierte SVG-Vorschau' AFTER `drawio_xml`;
-- ============================================================
-- Migration 002: VM- und Host-Verwaltung
--
-- Tabellen:
-- vm_clusters – Cluster-Gruppen für Hosts
-- vm_hosts – Physische Server / Hypervisoren
-- vm_vms – Virtuelle Maschinen
--
-- IPAM-Integration:
-- Beim Anlegen von VMs und Hosts wird die IP automatisch in
-- ipam_addresses eingetragen, wenn ein passendes Subnetz gefunden wird.
-- ============================================================
-- Cluster (Gruppen von Hosts)
CREATE TABLE IF NOT EXISTS `vm_clusters` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
`created_by` INT UNSIGNED NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx_vmcluster_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Physische Hosts / Hypervisoren
CREATE TABLE IF NOT EXISTS `vm_hosts` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`ip_address` VARCHAR(45) NULL,
`ipam_address_id` INT UNSIGNED NULL COMMENT 'Referenz auf ipam_addresses.id (optional)',
`hypervisor` ENUM('vmware_esxi','proxmox','hyper_v','kvm','xen','other') NOT NULL DEFAULT 'proxmox',
`cluster_id` INT UNSIGNED NULL,
`cpu_cores` INT UNSIGNED NULL,
`ram_gb` DECIMAL(10,2) NULL,
`storage_gb` DECIMAL(12,2) NULL,
`location` VARCHAR(255) NULL,
`description` TEXT NULL,
`status` ENUM('online','offline','maintenance') NOT NULL DEFAULT 'online',
`created_by` INT UNSIGNED NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx_vmhost_cluster` (`cluster_id`),
KEY `idx_vmhost_name` (`name`),
CONSTRAINT `fk_vmhost_cluster` FOREIGN KEY (`cluster_id`) REFERENCES `vm_clusters`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Virtuelle Maschinen
CREATE TABLE IF NOT EXISTS `vm_vms` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`ip_address` VARCHAR(45) NULL,
`ipam_address_id` INT UNSIGNED NULL COMMENT 'Referenz auf ipam_addresses.id (optional)',
`host_id` INT UNSIGNED NULL,
`cpu_cores` INT UNSIGNED NULL,
`ram_gb` DECIMAL(10,2) NULL,
`storage_gb` DECIMAL(12,2) NULL,
`purpose` TEXT NULL COMMENT 'Aufgabe / Verwendungszweck der VM',
`os` VARCHAR(255) NULL,
`status` ENUM('running','stopped','paused','error') NOT NULL DEFAULT 'running',
`notes` TEXT NULL,
`created_by` INT UNSIGNED NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx_vmvm_host` (`host_id`),
KEY `idx_vmvm_name` (`name`),
KEY `idx_vmvm_status` (`status`),
CONSTRAINT `fk_vmvm_host` FOREIGN KEY (`host_id`) REFERENCES `vm_hosts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Capabilities VM-Modul (granular)
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_vms', 'VMs, Hosts und Cluster ansehen (read-only)', 'vms', 'low'),
('manage_vms', 'VMs anlegen, bearbeiten und löschen', 'vms', 'medium'),
('manage_vm_hosts', 'Hosts anlegen, bearbeiten und löschen', 'vms', 'medium'),
('manage_vm_clusters', 'Cluster anlegen, bearbeiten und löschen', 'vms', 'low'),
('sync_vms', 'Hypervisor-Synchronisierung auslösen und Verbindung testen', 'vms', 'medium'),
('admin_vms', 'Hypervisor-API-Zugangsdaten konfigurieren (verschlüsselt, Admin)', 'vms', 'high');
-- Admins erhalten alle VM-Capabilities automatisch
INSERT IGNORE INTO `role_capabilities` (`role_id`, `capability_id`)
SELECT r.id, c.id
FROM `roles` r, `capabilities` c
WHERE r.is_admin = TRUE
AND c.name IN ('view_vms', 'manage_vms', 'manage_vm_hosts', 'manage_vm_clusters', 'sync_vms', 'admin_vms');
-- ============================================================
-- Migration 003: Hypervisor-Sync-Spalten
--
-- Erweitert vm_hosts um Verbindungsdaten für die automatische
-- Synchronisierung mit Proxmox VE, VMware vSphere und Hyper-V.
-- Erweitert vm_vms um die externe VM-ID des Hypervisors.
-- ============================================================
-- ============================================================
-- Migration 003: Hypervisor-Sync-Spalten
--
-- Erweitert vm_hosts um Verbindungsdaten für die automatische
-- Synchronisierung mit Proxmox VE, VMware vSphere und Hyper-V.
-- Erweitert vm_vms um die externe VM-ID des Hypervisors.
-- ============================================================
ALTER TABLE `vm_hosts`
ADD COLUMN `hypervisor_url` VARCHAR(512) NULL COMMENT 'API-URL, z.B. https://proxmox.local:8006' AFTER `description`,
ADD COLUMN `hypervisor_user` VARCHAR(255) NULL COMMENT 'Benutzername / API-Token-ID' AFTER `hypervisor_url`,
ADD COLUMN `hypervisor_password_enc` TEXT NULL COMMENT 'Verschlüsseltes Passwort (CredentialCrypto)' AFTER `hypervisor_user`,
ADD COLUMN `hypervisor_token` TEXT NULL COMMENT 'API-Token (z.B. Proxmox PVEAPIToken)' AFTER `hypervisor_password_enc`,
ADD COLUMN `hypervisor_verify_ssl` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'SSL-Zertifikat prüfen' AFTER `hypervisor_token`,
ADD COLUMN `hypervisor_node_id` VARCHAR(255) NULL COMMENT 'Proxmox-Knoten / VMware-Datacenter' AFTER `hypervisor_verify_ssl`,
ADD COLUMN `last_sync_at` TIMESTAMP NULL AFTER `hypervisor_node_id`,
ADD COLUMN `sync_status` ENUM('never','ok','error') NOT NULL DEFAULT 'never' AFTER `last_sync_at`,
ADD COLUMN `sync_error` TEXT NULL COMMENT 'Letzte Fehlermeldung beim Sync' AFTER `sync_status`;
ALTER TABLE `vm_vms`
ADD COLUMN `hypervisor_vm_id` VARCHAR(255) NULL COMMENT 'VM-ID im Hypervisor (VMID, MoRef, usw.)' AFTER `notes`,
ADD COLUMN `last_sync_at` TIMESTAMP NULL AFTER `hypervisor_vm_id`;
-- ============================================================
-- Migrationen 005–031: In initiales Schema integriert
--
-- Alle Statements sind idempotent:
-- CREATE TABLE IF NOT EXISTS → sicher bei Neuinstallation
-- INSERT IGNORE → sicher bei Neuinstallation
-- ALTER TABLE ADD COLUMN → Runner ignoriert "already exists"
-- ALTER TABLE MODIFY COLUMN → läuft problemlos mehrfach
-- ALTER TABLE ADD CONSTRAINT/INDEX → Runner ignoriert "Duplicate"
-- ============================================================
-- ============================================================
-- Migration 005: Änderungsprotokoll (Config Log)
-- ============================================================
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS `config_log_entries` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(500) NOT NULL,
`type` ENUM('update','config_change','installation','planned_maintenance') NOT NULL DEFAULT 'config_change',
`description` TEXT NULL,
`affected_system` VARCHAR(255) NULL,
`asset_id` INT UNSIGNED NULL,
`location_id` INT UNSIGNED NULL,
`ticket_ref` VARCHAR(100) NULL,
`ticket_id` INT UNSIGNED NULL,
`planned_date` DATE NULL,
`changed_at` DATETIME NULL,
`status` ENUM('planned','done','failed') NOT NULL DEFAULT 'planned',
`is_recurring` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
`recurrence_interval` ENUM('weekly','monthly','quarterly','yearly') NULL,
`notes` TEXT NULL,
`created_by` BIGINT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`location_id`) REFERENCES `ticket_locations`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
INDEX (`status`),
INDEX (`planned_date`),
INDEX (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_config_log', 'Änderungsprotokoll einsehen', 'configlog', 'low'),
('manage_config_log', 'Änderungsprotokoll-Einträge anlegen und bearbeiten', 'configlog', 'medium');
INSERT IGNORE INTO `role_capabilities` (`role_id`, `capability_id`)
SELECT r.id, c.id FROM `roles` r, `capabilities` c
WHERE r.is_admin = TRUE AND c.name IN ('view_config_log', 'manage_config_log')
AND NOT EXISTS (SELECT 1 FROM `role_capabilities` rc WHERE rc.role_id = r.id AND rc.capability_id = c.id);
-- ============================================================
-- Migration 006: Änderungsprotokoll – Erweiterungen
-- ============================================================
ALTER TABLE `config_log_entries`
ADD COLUMN `is_recurring` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER `status`;
ALTER TABLE `config_log_entries`
ADD COLUMN `recurrence_interval` ENUM('weekly','monthly','quarterly','yearly') NULL AFTER `is_recurring`;
CREATE TABLE IF NOT EXISTS `config_log_vms` (
`config_log_id` INT UNSIGNED NOT NULL,
`vm_id` INT UNSIGNED NOT NULL,
`linked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`config_log_id`, `vm_id`),
INDEX `idx_clv_entry` (`config_log_id`),
INDEX `idx_clv_vm` (`vm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 007: Lizenzmanagement
-- ============================================================
CREATE TABLE IF NOT EXISTS `licenses` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(500) NOT NULL,
`vendor` VARCHAR(255) NULL,
`product` VARCHAR(255) NULL,
`type` ENUM('perpetual','subscription','volume','oem','open_source','trial') NOT NULL DEFAULT 'subscription',
`seats` INT UNSIGNED NULL,
`seats_used` INT UNSIGNED NOT NULL DEFAULT 0,
`valid_from` DATE NULL,
`valid_until` DATE NULL,
`cost_amount` DECIMAL(12,2) NULL,
`cost_interval` ENUM('monthly','yearly','one_time') NULL,
`status` ENUM('active','expired','cancelled','trial') NOT NULL DEFAULT 'active',
`license_key_enc` TEXT NULL COMMENT 'AES-256-CBC verschlüsselter Lizenzschlüssel',
`license_file_path` VARCHAR(255) NULL,
`license_file_name` VARCHAR(255) NULL,
`notes` TEXT NULL,
`created_by` BIGINT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
INDEX `idx_lic_status` (`status`),
INDEX `idx_lic_valid_until` (`valid_until`),
INDEX `idx_lic_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `license_contracts` (
`license_id` INT UNSIGNED NOT NULL,
`contract_id` INT UNSIGNED NOT NULL,
`linked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`license_id`, `contract_id`),
INDEX `idx_liccon_license` (`license_id`),
INDEX `idx_liccon_contract` (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`) VALUES
('view_licenses', 'Lizenzen einsehen', 'licenses', 'low'),
('manage_licenses', 'Lizenzen anlegen, bearbeiten, löschen', 'licenses', 'medium');
INSERT IGNORE INTO `role_capabilities` (`role_id`, `capability_id`)
SELECT r.id, c.id FROM `roles` r, `capabilities` c
WHERE r.is_admin = TRUE AND c.name IN ('view_licenses', 'manage_licenses')
AND NOT EXISTS (SELECT 1 FROM `role_capabilities` rc WHERE rc.role_id = r.id AND rc.capability_id = c.id);
-- ============================================================
-- Migration 008: Lizenzschlüssel + Lizenzdatei (Spalten in licenses)
-- ============================================================
ALTER TABLE `licenses`
ADD COLUMN `license_key_enc` TEXT NULL
COMMENT 'AES-256-CBC verschlüsselter Lizenzschlüssel' AFTER `notes`;
ALTER TABLE `licenses`
ADD COLUMN `license_file_path` VARCHAR(255) NULL AFTER `license_key_enc`;
ALTER TABLE `licenses`
ADD COLUMN `license_file_name` VARCHAR(255) NULL AFTER `license_file_path`;
-- ============================================================
-- Migration 009: KI-Chat
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_chat_sessions` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` INT UNSIGNED NOT NULL,
`title` VARCHAR(200) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_ai_session_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `ai_chat_messages` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`session_id` INT UNSIGNED NOT NULL,
`role` ENUM('user','assistant','system') NOT NULL,
`content` TEXT NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_ai_msg_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 010: Telefonbuch-Capability
-- ============================================================
INSERT IGNORE INTO `capabilities` (`name`, `description`, `category`, `risk_level`)
VALUES ('view_phonebook', 'Internes Telefonbuch einsehen', 'phonebook', 'low');
INSERT IGNORE INTO `role_capabilities` (`role_id`, `capability_id`)
SELECT r.id, c.id FROM `roles` r, `capabilities` c WHERE c.name = 'view_phonebook';
-- ============================================================
-- Migration 011: Mobilnummer für directory_persons
-- ============================================================
ALTER TABLE `directory_persons`
ADD COLUMN `mobile` VARCHAR(100) NULL DEFAULT NULL AFTER `phone`;
-- ============================================================
-- Migration 012: KI-Aktionsprotokoll
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_action_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`session_id` BIGINT UNSIGNED NULL,
`action_type` VARCHAR(50) NOT NULL,
`action_data` TEXT NULL,
`result` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_ailog_user` (`user_id`),
INDEX `idx_ailog_action` (`action_type`),
INDEX `idx_ailog_created` (`created_at`),
CONSTRAINT `fk_ailog_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 013: KI-Token-Verbrauch & Kontext-Keywords
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_usage_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL,
`session_id` BIGINT UNSIGNED NULL,
`provider` VARCHAR(20) NOT NULL,
`model` VARCHAR(80) NOT NULL,
`prompt_tokens` INT UNSIGNED NOT NULL DEFAULT 0,
`completion_tokens` INT UNSIGNED NOT NULL DEFAULT 0,
`total_tokens` INT UNSIGNED NOT NULL DEFAULT 0,
`cost_eur` DECIMAL(10,6) DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_aiusage_user` (`user_id`),
INDEX `idx_aiusage_created` (`created_at`),
INDEX `idx_aiusage_model` (`model`),
CONSTRAINT `fk_aiusage_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `ai_context_keywords` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`context_key` VARCHAR(60) NOT NULL,
`keyword` VARCHAR(100) NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uq_context_keyword` (`context_key`, `keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 014: KI-Chat Sessions Erweiterung
-- ============================================================
ALTER TABLE `ai_chat_sessions`
ADD COLUMN `title` VARCHAR(200) DEFAULT NULL;
ALTER TABLE `ai_chat_messages`
MODIFY COLUMN `role` ENUM('user','assistant','system') NOT NULL;
-- ============================================================
-- Migration 015: KI-Chat Feedback
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_chat_feedback` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`message_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`rating` TINYINT(1) NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `idx_feedback_msg_user` (`message_id`, `user_id`),
INDEX `idx_feedback_message` (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 016: WordPress API Tokens
-- ============================================================
CREATE TABLE IF NOT EXISTS `wordpress_api_tokens` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`token_hash` CHAR(64) NOT NULL,
`created_by` BIGINT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_used_at` DATETIME NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
UNIQUE KEY `uk_token_hash` (`token_hash`),
INDEX `idx_active` (`is_active`),
CONSTRAINT `fk_wp_tokens_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- Migration 017: Loan Devices – WordPress Self-Service Flag
-- ============================================================
ALTER TABLE `loan_devices`
ADD COLUMN `wp_requestable` TINYINT(1) NOT NULL DEFAULT 1
COMMENT 'Ob das Gerät über das WordPress-Plugin selbst reserviert werden kann'
AFTER `status`;
-- ============================================================
-- Migration 018: Dateityp-Kategorie für Firmendokumente
-- ============================================================
ALTER TABLE `company_files`
ADD COLUMN `file_type` VARCHAR(20) NOT NULL DEFAULT 'document'
COMMENT 'document = Firmendokument, quote = Angebot'
AFTER `company_id`;
-- ============================================================