Friederich Loheide
4159b92268
Some checks are pending
CI / PHP Lint (push) Waiting to run
CI / PHP Lint (pull_request) Waiting to run
CI / PHP Lint-1 (pull_request) Waiting to run
CI / PHP Lint-1 (push) Waiting to run
CI / Unit Tests & Static Analysis (pull_request) Waiting to run
CI / Unit Tests & Static Analysis (push) Waiting to run
Jede Display-Seite bringt jetzt ihr eigenes Erscheinungsbild mit – der Empfang sieht anders aus als der Tagungsraum nebenan: - eigenes Logo (leer = Logo aus den Einstellungen) - formatfüllendes Hintergrundbild mit einstellbarer Abdunklung (0–90 %), damit die Karte auf hellen Fotos lesbar bleibt - eigene Akzentfarbe für den Knopf (leer = Farbe aus dem Design-Tab) - Karte wahlweise hell oder dunkel; auf Fotos wirkt dunkel meist ruhiger Logo und Hintergrund lassen sich hochladen oder als URL hinterlegen; beim Löschen einer Display-Seite verschwinden die hochgeladenen Dateien mit. Nebenbei aufgeräumt: das Bildfeld (Vorschau + Upload + URL + Entfernen) lag als Funktion in admin/settings.php und wird jetzt von beiden Seiten genutzt – Ui::imageField() für die Darstellung, Upload::resolveField() für die Auswertung. Sicherheit: die Akzentfarbe landet in einem style-Attribut, deshalb wird sie sowohl beim Speichern als auch beim Ausgeben auf eine echte Hex-Farbe geprüft; ein Test hält das fest. Migration 0006, database.sql nachgezogen, 4 neue Tests (42 gesamt), Screenshots ergänzt, Version 2.8.0. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
197 lines
7.6 KiB
SQL
197 lines
7.6 KiB
SQL
-- UniFi Voucher Management System - Datenbankstruktur
|
|
|
|
CREATE TABLE IF NOT EXISTS `settings` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`setting_key` VARCHAR(100) UNIQUE NOT NULL,
|
|
`setting_value` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sites` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`site_id` VARCHAR(100) NOT NULL,
|
|
`unifi_controller_url` VARCHAR(255) NOT NULL,
|
|
`unifi_username` VARCHAR(100) NOT NULL,
|
|
`unifi_password` VARCHAR(255) NOT NULL,
|
|
`is_active` TINYINT(1) DEFAULT 1,
|
|
`public_access` TINYINT(1) DEFAULT 0,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_active` (`is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`email` VARCHAR(255) UNIQUE NOT NULL,
|
|
`name` VARCHAR(255),
|
|
`password_hash` VARCHAR(255),
|
|
`is_admin` TINYINT(1) DEFAULT 0,
|
|
`is_active` TINYINT(1) DEFAULT 1,
|
|
`microsoft_id` VARCHAR(255) UNIQUE,
|
|
`totp_secret` VARCHAR(64) NULL,
|
|
`totp_enabled` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`totp_backup_codes` TEXT NULL,
|
|
`last_login` TIMESTAMP NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_email` (`email`),
|
|
INDEX `idx_microsoft` (`microsoft_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `user_site_access` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`user_id` INT NOT NULL,
|
|
`site_id` INT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `unique_user_site` (`user_id`, `site_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `voucher_templates` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`max_uses` INT NOT NULL DEFAULT 1,
|
|
`expire_minutes` INT NOT NULL DEFAULT 480,
|
|
`description` VARCHAR(500),
|
|
`qos_rate_max_down` INT NULL,
|
|
`qos_rate_max_up` INT NULL,
|
|
`qos_usage_quota` INT NULL,
|
|
`is_active` TINYINT(1) DEFAULT 1,
|
|
`created_by` INT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `kiosks` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`site_id` INT NOT NULL,
|
|
`template_id` INT NULL,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`token` VARCHAR(64) NOT NULL,
|
|
`headline` VARCHAR(255) NULL,
|
|
`subline` VARCHAR(500) NULL,
|
|
`logo_url` VARCHAR(500) NULL,
|
|
`background_url` VARCHAR(500) NULL,
|
|
`bg_overlay` TINYINT NOT NULL DEFAULT 45,
|
|
`accent_color` VARCHAR(7) NULL,
|
|
`card_style` ENUM('light','dark') NOT NULL DEFAULT 'light',
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`daily_limit` INT NOT NULL DEFAULT 100,
|
|
`cooldown_seconds` INT NOT NULL DEFAULT 20,
|
|
`display_seconds` INT NOT NULL DEFAULT 90,
|
|
`last_used_at` TIMESTAMP NULL,
|
|
`created_by` INT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY `uniq_token` (`token`),
|
|
INDEX `idx_site` (`site_id`),
|
|
FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`template_id`) REFERENCES `voucher_templates`(`id`) ON DELETE SET NULL,
|
|
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `api_keys` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`key_prefix` VARCHAR(16) NOT NULL,
|
|
`key_hash` VARCHAR(255) NOT NULL,
|
|
`scope` VARCHAR(16) NOT NULL DEFAULT 'write',
|
|
`rate_limit` INT NOT NULL DEFAULT 0,
|
|
`created_by` INT,
|
|
`last_used_at` TIMESTAMP NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
|
INDEX `idx_prefix` (`key_prefix`),
|
|
INDEX `idx_active` (`is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `api_key_hits` (
|
|
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
`api_key_id` INT NOT NULL,
|
|
`hit_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_key_time` (`api_key_id`, `hit_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `vouchers` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`site_id` INT NOT NULL,
|
|
`user_id` INT,
|
|
`kiosk_id` INT NULL,
|
|
`voucher_code` VARCHAR(50) NOT NULL,
|
|
`voucher_name` VARCHAR(255) NOT NULL,
|
|
`max_uses` INT NOT NULL,
|
|
`expire_minutes` INT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`unifi_voucher_id` VARCHAR(100),
|
|
`status` ENUM('valid', 'used', 'expired') DEFAULT 'valid',
|
|
`used_count` INT DEFAULT 0,
|
|
`expires_at` TIMESTAMP NULL,
|
|
`synced_from_unifi` TINYINT(1) DEFAULT 0,
|
|
`last_sync` TIMESTAMP NULL,
|
|
FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
|
INDEX `idx_site` (`site_id`),
|
|
INDEX `idx_created` (`created_at`),
|
|
INDEX `idx_unifi_id` (`unifi_voucher_id`),
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_kiosk` (`kiosk_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sessions` (
|
|
`id` VARCHAR(128) PRIMARY KEY,
|
|
`user_id` INT NULL,
|
|
`data` TEXT,
|
|
`expires_at` TIMESTAMP NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
INDEX `idx_expires` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `login_attempts` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`ip_address` VARCHAR(45) NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL,
|
|
`attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_ip` (`ip_address`),
|
|
INDEX `idx_email` (`email`),
|
|
INDEX `idx_attempted` (`attempted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `audit_log` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`user_id` INT,
|
|
`action` VARCHAR(100) NOT NULL,
|
|
`entity_type` VARCHAR(50),
|
|
`entity_id` VARCHAR(100),
|
|
`details` TEXT,
|
|
`ip_address` VARCHAR(45),
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
|
INDEX `idx_user` (`user_id`),
|
|
INDEX `idx_action` (`action`),
|
|
INDEX `idx_created` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`user_id` INT NOT NULL,
|
|
`token` VARCHAR(128) NOT NULL,
|
|
`expires_at` TIMESTAMP NOT NULL,
|
|
`used` TINYINT(1) DEFAULT 0,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `unique_token` (`token`),
|
|
INDEX `idx_expires` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migrations für bestehende Installationen:
|
|
-- ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS `status` ENUM('valid', 'used', 'expired') DEFAULT 'valid';
|
|
-- ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS `used_count` INT DEFAULT 0;
|
|
-- ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS `expires_at` TIMESTAMP NULL;
|
|
-- ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS `synced_from_unifi` TINYINT(1) DEFAULT 0;
|
|
-- ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS `last_sync` TIMESTAMP NULL;
|