--
-- Host: localhost    Database: azubi
-- ------------------------------------------------------


--
-- Table structure for table `assessment_answers`
--

CREATE TABLE `assessment_answers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `assessment_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `answer_value` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_assessment_question` (`assessment_id`,`question_id`),
  KEY `fk_assessment_answers_question` (`question_id`),
  CONSTRAINT `fk_assessment_answers_assessment` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_assessment_answers_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=434 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assessment_answers`
--

INSERT INTO `assessment_answers` VALUES (146,33,1,'Master','2026-03-31 12:59:18','2026-03-31 12:59:18'),(147,33,46,'Anwalt','2026-03-31 12:59:18','2026-03-31 12:59:18'),(151,33,4,'26','2026-03-31 12:59:18','2026-03-31 12:59:18'),(184,36,1,'Bundeswehr','2026-04-02 11:45:57','2026-04-02 11:45:57'),(185,36,46,'Krieg und Frieden','2026-04-02 11:45:57','2026-04-02 11:45:57'),(189,36,4,'4','2026-04-02 11:45:57','2026-04-02 11:45:57'),(191,37,1,'Deckungsbeitrag','2026-04-02 11:46:12','2026-04-02 11:46:12'),(192,37,46,'Geld','2026-04-02 11:46:12','2026-04-02 11:46:12'),(196,37,4,'1','2026-04-02 11:46:12','2026-04-02 11:46:12'),(202,38,1,'Holzi','2026-04-02 11:46:30','2026-04-02 11:46:30'),(203,38,46,'Holzer','2026-04-02 11:46:30','2026-04-02 11:46:30'),(207,38,4,'3','2026-04-02 11:46:30','2026-04-02 11:46:30'),(220,39,1,'Feier Abend GmbH','2026-04-02 11:47:02','2026-04-02 11:47:02'),(221,39,46,'Dienstleistungen','2026-04-02 11:47:02','2026-04-02 11:47:02'),(225,39,4,'17','2026-04-02 11:47:02','2026-04-02 11:47:02'),(238,40,1,'GmbH Arbeit','2026-04-02 11:47:11','2026-04-02 11:47:11'),(239,40,46,'Arbeit','2026-04-02 11:47:11','2026-04-02 11:47:11'),(243,40,4,'67','2026-04-02 11:47:11','2026-04-02 11:47:11'),(258,41,1,'Bier GmbH','2026-04-02 11:47:18','2026-04-02 11:47:18'),(259,41,46,'Alkohol','2026-04-02 11:47:18','2026-04-02 11:47:18'),(263,41,4,'67','2026-04-02 11:47:18','2026-04-02 11:47:18'),(265,42,1,'Körschberg AG','2026-04-02 11:47:39','2026-04-02 11:47:39'),(269,42,4,'900','2026-04-02 11:47:39','2026-04-02 11:47:39'),(271,42,46,'Brauerei','2026-04-02 11:47:39','2026-04-02 11:47:39'),(284,43,1,'Mr. Crabs GmbH','2026-04-02 11:47:44','2026-04-02 11:47:44'),(285,43,46,'Restaurant','2026-04-02 11:47:44','2026-04-02 11:47:44'),(289,43,4,'3','2026-04-02 11:47:44','2026-04-02 11:47:44'),(301,44,1,'Ettel.net','2026-04-02 11:47:47','2026-04-02 11:47:47'),(302,44,46,'IT 1-Mann-Klitsche','2026-04-02 11:47:47','2026-04-02 11:47:47'),(306,44,4,'1','2026-04-02 11:47:47','2026-04-02 11:47:47'),(323,45,1,'Deckungsbeitragsmaximierung','2026-04-02 11:47:51','2026-04-02 11:47:51'),(324,45,46,'Finanzdienstleistung','2026-04-02 11:47:51','2026-04-02 11:47:51'),(328,45,4,'666','2026-04-02 11:47:51','2026-04-02 11:47:51'),(394,63,52,'Max Mustermann','2026-04-16 14:00:07','2026-04-16 14:00:07'),(395,63,53,'max@mustermann.com','2026-04-16 14:00:07','2026-04-16 14:00:07'),(396,63,54,'0123456789','2026-04-16 14:00:07','2026-04-16 14:00:07'),(397,63,1,'Muster GmbH','2026-04-16 14:00:07','2026-04-16 14:00:07'),(398,63,46,'Handwerksbetrieb','2026-04-16 14:00:07','2026-04-16 14:00:07'),(399,63,49,'4','2026-04-16 14:00:07','2026-04-16 14:00:07'),(400,63,47,'Vernetzung unter den Standorten ist schlecht, kein Sharepoint oder so, keine Vereinheitlichung verschiedene WLAN Zugänge in den Standorten','2026-04-16 14:00:07','2026-04-16 14:00:07'),(401,63,50,'Kauf','2026-04-16 14:00:07','2026-04-16 14:00:07'),(402,63,51,'15000','2026-04-16 14:00:07','2026-04-16 14:00:07'),(403,63,4,'24','2026-04-16 14:00:07','2026-04-16 14:00:07'),(404,63,55,'Grundlegende Neustrukturierung','2026-04-16 14:00:07','2026-04-16 14:00:07'),(405,63,56,'WLAN Access Points mit neue Konfiguration','2026-04-16 14:00:07','2026-04-16 14:00:07'),(406,63,57,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(407,63,58,'Office 2021','2026-04-16 14:00:07','2026-04-16 14:00:07'),(408,63,62,'true','2026-04-16 14:00:07','2026-04-16 14:00:07'),(409,63,71,'Cisco','2026-04-16 14:00:07','2026-04-16 14:00:07'),(410,63,73,'true','2026-04-16 14:00:07','2026-04-16 14:00:07'),(411,63,72,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(412,63,75,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(413,63,74,'6','2026-04-16 14:00:07','2026-04-16 14:00:07'),(414,63,67,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(415,63,60,'AWS','2026-04-16 14:00:07','2026-04-16 14:00:07'),(416,63,76,'Telekom','2026-04-16 14:00:07','2026-04-16 14:00:07'),(417,63,77,'300','2026-04-16 14:00:07','2026-04-16 14:00:07'),(418,63,91,'PCs, Tablets, Drucker, Scanner','2026-04-16 14:00:07','2026-04-16 14:00:07'),(419,63,92,'32','2026-04-16 14:00:07','2026-04-16 14:00:07'),(420,63,93,'Älteste 10 Jahre','2026-04-16 14:00:07','2026-04-16 14:00:07'),(421,63,94,'Windows','2026-04-16 14:00:07','2026-04-16 14:00:07'),(422,63,95,'Samsung','2026-04-16 14:00:07','2026-04-16 14:00:07'),(423,63,96,'Andoid','2026-04-16 14:00:07','2026-04-16 14:00:07'),(424,63,99,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(425,63,100,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(426,63,103,'false','2026-04-16 14:00:07','2026-04-16 14:00:07'),(427,63,102,'true','2026-04-16 14:00:07','2026-04-16 14:00:07'),(428,63,105,'false','2026-04-16 14:00:07','2026-04-16 14:00:07');

--
-- Table structure for table `assessment_files`
--

CREATE TABLE `assessment_files` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `assessment_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `original_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stored_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `file_size` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_af_assessment` (`assessment_id`),
  KEY `fk_af_question` (`question_id`),
  CONSTRAINT `fk_af_assessment` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_af_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assessment_files`
--


--
-- Table structure for table `assessment_rate_limits`
--

CREATE TABLE `assessment_rate_limits` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempted_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_assessment_rate_limits_ip_time` (`ip_address`,`attempted_at`),
  KEY `idx_assessment_rate_limits_cleanup` (`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assessment_rate_limits`
--


--
-- Table structure for table `assessments`
--

CREATE TABLE `assessments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_name` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_email` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('draft','submitted','reviewed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assessments`
--

INSERT INTO `assessments` VALUES (33,'Master','Master Anwalt Berlin','Anwalt@Anwalt','submitted','2026-03-31 12:59:18','2026-03-31 12:59:18'),(36,'Bundeswehr','Angelo Merte','angelo@merte.bundeswehr.org','submitted','2026-04-02 11:45:57','2026-04-02 11:45:57'),(37,'Deckungsbeitrag','Sawatzky','finsawatzky@gmail.com','submitted','2026-04-02 11:46:12','2026-04-02 11:46:12'),(38,'Holzi','Holzi','holzi@holzi.de','submitted','2026-04-02 11:46:30','2026-04-02 11:46:30'),(39,'Feier Abend GmbH','Jürgen Maier','j.maier@feierabend.de','submitted','2026-04-02 11:47:02','2026-04-02 11:47:02'),(40,'GmbH Arbeit','Michael Sowak','michi.sowi@gmx.net','submitted','2026-04-02 11:47:11','2026-04-02 11:47:11'),(41,'Bier GmbH','Jan Holzwarth','jan.holzwarth@bechtle.com','reviewed','2026-04-02 11:47:18','2026-04-02 11:50:35'),(42,'Körschberg AG','Finn Körschberg','finn.körschberg@firma.de','submitted','2026-04-02 11:47:39','2026-04-02 11:47:39'),(43,'Mr. Crabs GmbH','Sponge Bob','sponge.bob@crabs.com','submitted','2026-04-02 11:47:44','2026-04-02 11:47:44'),(44,'Ettel.net','Tim Ettel','tim@ettel.net','submitted','2026-04-02 11:47:47','2026-04-15 08:09:59'),(45,'Deckungsbeitragsmaximierung','Max Profit','max.profit@deckungsbeitragsmaximierung.de','submitted','2026-04-02 11:47:51','2026-04-02 11:47:51'),(63,'Muster GmbH','Max Mustermann','max@mustermann.com','submitted','2026-04-16 14:00:07','2026-06-08 08:47:51');

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` VALUES (1,'Allgemeine Informationen',1,1,'2026-03-30 09:30:01','2026-04-13 06:39:30'),(2,'Netzwerk',4,1,'2026-03-30 09:30:01','2026-04-13 06:49:50'),(3,'Serversysteme',6,1,'2026-03-30 09:30:01','2026-04-13 06:49:50'),(4,'Sicherheit',9,1,'2026-03-30 09:30:01','2026-04-13 06:49:50'),(9,'IT-Organisation',2,1,'2026-04-13 05:56:08','2026-04-13 06:49:50'),(13,'Software',3,1,'2026-04-13 06:41:59','2026-04-14 13:32:02'),(14,'Internet Provider',5,1,'2026-04-13 06:42:17','2026-04-13 06:49:50'),(15,'Virtualisierung',7,1,'2026-04-13 06:44:20','2026-04-13 06:49:50'),(16,'Endgeräte',8,1,'2026-04-13 06:47:11','2026-04-13 06:49:50'),(17,'Backup und Archivierung',10,1,'2026-04-13 06:47:11','2026-04-13 06:49:50'),(18,'Software und Applikationen',11,0,'2026-04-13 06:47:11','2026-05-27 07:25:05'),(19,'Mail',12,1,'2026-04-13 06:47:11','2026-04-13 06:49:50'),(20,'Telefonie',13,1,'2026-04-13 06:47:11','2026-04-13 06:49:50'),(21,'Service',14,1,'2026-04-13 06:47:11','2026-04-13 06:49:50');

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '0',
  `attempted_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_attempts_lookup` (`username`,`ip_address`,`attempted_at`),
  KEY `idx_login_attempts_cleanup` (`attempted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `login_attempts`
--

INSERT INTO `login_attempts` VALUES (1,'admin','91.26.56.178',1,'2026-06-08 07:18:19'),(2,'admin','91.26.56.178',1,'2026-06-08 07:27:50'),(3,'m.laub','91.26.56.178',1,'2026-06-08 07:31:24'),(4,'m.laub','91.26.56.178',0,'2026-06-08 07:38:26'),(5,'admin','91.26.56.178',1,'2026-06-08 07:38:34'),(6,'admin','91.26.56.178',1,'2026-06-08 07:39:58'),(7,'test','91.26.56.178',1,'2026-06-08 07:40:33'),(8,'admin','91.26.56.178',1,'2026-06-08 08:45:41'),(9,'admin','91.26.56.178',1,'2026-06-08 09:37:09'),(10,'admin','91.26.56.178',1,'2026-06-08 11:15:02'),(11,'admin','91.26.56.178',1,'2026-06-09 07:38:49'),(12,'test','91.26.56.178',1,'2026-06-09 07:40:35');

--
-- Table structure for table `question_dependencies`
--

CREATE TABLE `question_dependencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int unsigned NOT NULL,
  `depends_on_question_id` int unsigned NOT NULL,
  `operator` enum('equals','not_equals') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'equals',
  `trigger_value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dependency` (`question_id`,`depends_on_question_id`),
  KEY `fk_dependencies_depends_on` (`depends_on_question_id`),
  CONSTRAINT `fk_dependencies_depends_on` FOREIGN KEY (`depends_on_question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_dependencies_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `question_dependencies`
--

INSERT INTO `question_dependencies` VALUES (24,68,67,'equals','true','2026-04-15 11:29:40'),(26,104,103,'equals','true','2026-04-16 09:34:36'),(27,69,67,'equals','true','2026-04-17 06:31:18'),(28,70,67,'equals','true','2026-04-17 06:31:37'),(29,129,128,'equals','true','2026-05-27 11:40:31'),(31,106,105,'equals','true','2026-05-27 11:43:07'),(32,107,105,'equals','true','2026-05-27 11:43:07'),(33,108,105,'equals','true','2026-05-27 12:07:42'),(34,98,99,'equals','true','2026-05-28 06:51:40');

--
-- Table structure for table `question_options`
--

CREATE TABLE `question_options` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int unsigned NOT NULL,
  `option_value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `option_label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_question_options_question` (`question_id`),
  CONSTRAINT `fk_question_options_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=305 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `question_options`
--

INSERT INTO `question_options` VALUES (20,50,'Miete','Miete',1,1),(21,50,'Kauf','Kauf',2,1),(66,84,'1 GBS/Sek SFP','1 GBS/Sek SFP',1,1),(67,84,'1 GBS/Sek BaseT','1 GBS/Sek BaseT',2,1),(68,84,'10 GBS SFP+','10 GBS SFP+',3,1),(69,84,'10GBS/S BaseT','10GBS/S BaseT',4,1),(109,101,'VPN-Benutzer','VPN-Benutzer',1,1),(110,101,'Standort-Verbindungen','Standort-Verbindungen',2,1),(111,101,'Anbindung Mobiler Büros','Anbindung Mobiler Büros',3,1),(112,101,'Zweifaktor Authentifizierung','Zweifaktor Authentifizierung',4,1),(113,101,'Security Tokens','Security Tokens',5,1),(119,104,'ISO 9001','ISO 9001',1,1),(120,104,'Tisac','Tisac',2,1),(121,104,'PCI','PCI',3,1),(122,104,'ISO 27001','ISO 27001',4,1),(123,104,'Branchenstandards','Branchenstandards',5,1),(136,76,'Telekom','Telekom',1,1),(137,76,'VSE','VSE',2,1),(138,76,'Inexio','Inexio',3,1),(139,76,'Deustche Glasfaser','Deustche Glasfaser',4,1),(140,76,'Vodafone','Vodafone',5,1),(141,76,'Intersaar','Intersaar',6,1),(142,76,'1&1','1&1',7,1),(143,76,'Sonstige','Sonstige',8,1),(157,78,'HPE','HPE',1,1),(158,78,'Fujitsu','Fujitsu',2,1),(159,78,'Lenovo','Lenovo',3,1),(160,78,'DELL','DELL',4,1),(161,78,'Wortmann','Wortmann',5,1),(162,78,'Supermicro','Supermicro',6,1),(163,78,'Sonstige','Sonstige',7,1),(170,82,'32','32',1,1),(171,82,'64','64',2,1),(172,82,'128','128',3,1),(173,82,'256','256',4,1),(174,82,'512','512',5,1),(175,82,'1024','1024',6,1),(190,90,'1 GBS/Sek SFP','1 GBS/Sek SFP',1,1),(191,90,'1 GBS/Sek BaseT','1 GBS/Sek BaseT',2,1),(192,90,'10 GB SFP+','10 GB SFP+',3,1),(193,90,'10 GB SFP+','10 GB SFP+',4,1),(194,88,'32','32',1,1),(195,88,'64','64',2,1),(196,88,'128','128',3,1),(197,88,'256','256',4,1),(198,88,'512','512',5,1),(199,88,'1024','1024',6,1),(205,96,'Windows','Windows',1,1),(206,96,'IOS','IOS',2,1),(207,96,'Android','Android',3,1),(208,94,'Windows 7','Windows 7',1,1),(209,94,'Windows 10','Windows 10',2,1),(210,94,'Windows 11','Windows 11',3,1),(211,94,'IOS','IOS',4,1),(212,94,'Android','Android',5,1),(237,134,'Microsoft','Microsoft',1,1),(238,134,'Ionos','Ionos',2,1),(239,134,'T-Online','T-Online',3,1),(240,134,'Strato','Strato',4,1),(241,134,'Domain Factory','Domain Factory',5,1),(242,134,'Google','Google',6,1),(243,134,'Sonstige','Sonstige',7,1),(244,136,'Outlook','Outlook',1,1),(245,136,'Exchange Online','Exchange Online',2,1),(246,136,'Mozilla Thunderbird','Mozilla Thunderbird',3,1),(247,136,'eM Client','eM Client',4,1),(248,136,'Sonstige','Sonstige',5,1),(249,58,'M365','M365',1,1),(250,58,'Office 2010','Office 2010',2,1),(251,58,'Office 2013','Office 2013',3,1),(252,58,'Office 2016','Office 2016',4,1),(253,58,'Office 2021','Office 2021',5,1),(254,58,'Nextcloud','Nextcloud',6,1),(255,58,'Owncloud','Owncloud',7,1),(256,58,'Sonstige','Sonstige',8,1),(257,71,'HPE','HPE',1,1),(258,71,'Cisco','Cisco',2,1),(259,71,'Sophos','Sophos',3,1),(260,71,'Ubiquiti','Ubiquiti',4,1),(261,71,'Cisco Meraki','Cisco Meraki',5,1),(262,71,'Sonstige','Sonstige',6,1),(263,80,'Windows 2008','Windows 2008',1,1),(264,80,'Windows 2008R2','Windows 2008R2',2,1),(265,80,'Windows Server 2012','Windows Server 2012',3,1),(266,80,'Windows Server 2016','Windows Server 2016',4,1),(267,80,'Windows Server 2019','Windows Server 2019',5,1),(268,80,'Windows Server 2022','Windows Server 2022',6,1),(269,80,'Linux','Linux',7,1),(270,80,'Sonstige','Sonstige',8,1),(271,86,'VMWare','VMWare',1,1),(272,86,'HyperV','HyperV',2,1),(273,86,'Nutanix','Nutanix',3,1),(274,86,'Promox','Promox',4,1),(275,86,'KVM/Linux basiert','KVM/Linux basiert',5,1),(276,86,'Sonstige','Sonstige',6,1),(277,91,'PCs','PCs',1,1),(278,91,'TCs','TCs',2,1),(279,91,'Notebooks','Notebooks',3,1),(280,91,'Tablets','Tablets',4,1),(281,91,'MACs','MACs',5,1),(282,91,'Mobile','Mobile',6,1),(283,91,'Drucker','Drucker',7,1),(284,91,'Scanner','Scanner',8,1),(285,91,'Sonstige','Sonstige',9,1),(286,98,'Sophos','Sophos',1,1),(287,98,'Fortinet','Fortinet',2,1),(288,98,'Watchguard','Watchguard',3,1),(289,98,'Lancom','Lancom',4,1),(290,98,'Sonicwall','Sonicwall',5,1),(291,98,'Cisco','Cisco',6,1),(292,98,'Sonstige','Sonstige',7,1),(293,125,'Managed Service','Managed Service',1,1),(294,125,'Full Managed Service','Full Managed Service',2,1),(295,125,'vor Ort Service','vor Ort Service',3,1),(296,125,'keinen Servicevertrag','keinen Servicevertrag',4,1),(297,142,'Kritische Ereignisse 2 Std;  Normale Service Ereignisse 24 Std;  Unkritische Ereignisse 3 Tage','Kritische Ereignisse 2 Std;  Normale Service Ereignisse 24 Std;  Unkritische Ereignisse 3 Tage',1,1),(298,142,'Kritische Ereignisse 4 Std;  Normale Service Ereignisse 24 Std;  Unkritische Ereignisse 3 Tage','Kritische Ereignisse 4 Std;  Normale Service Ereignisse 24 Std;  Unkritische Ereignisse 3 Tage',2,1),(299,142,'Kritische Ereignisse 4 Std;  Unkritische Ereignisse 3 Tage','Kritische Ereignisse 4 Std;  Unkritische Ereignisse 3 Tage',3,1),(300,143,'Wöchentlich','Wöchentlich',1,1),(301,143,'14-Tägig','14-Tägig',2,1),(302,143,'Monatlich','Monatlich',3,1),(303,143,'1/4 Jährig','1/4 Jährig',4,1),(304,143,'1/2 Jährig','1/2 Jährig',5,1);

--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `subcategory_id` int unsigned NOT NULL,
  `question_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `question_text` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `answer_type` enum('text','email','number','boolean','select','textarea') COLLATE utf8mb4_unicode_ci NOT NULL,
  `placeholder` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `allow_file_upload` tinyint(1) NOT NULL DEFAULT '0',
  `allow_multi_select` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `question_key` (`question_key`),
  KEY `fk_questions_subcategory` (`subcategory_id`),
  CONSTRAINT `fk_questions_subcategory` FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` VALUES (1,1,'company_name','Name des Unternehmens','text','Muster GmbH',0,1,1,0,0,'2026-03-30 09:30:01','2026-05-27 07:14:27'),(4,1,'employee_count','Wie viele IT-Arbeitsplätze hat Ihr Unternehmen?','number',NULL,0,1,8,0,0,'2026-03-30 09:30:01','2026-05-27 07:14:27'),(46,1,'business_field','Branche des Unternehmens','text','Schule, Anwaltskanzlei, Handwerksbetrieb, ...',0,1,2,0,0,'2026-03-31 12:36:35','2026-05-27 07:14:27'),(47,1,'pain_points','Welche Painpoints haben Sie aktuell? Gibt es einen Handlungsbedarf?','textarea','z. B. Backup ist außer Betrieb',0,1,5,0,0,'2026-04-10 07:22:49','2026-04-13 13:46:42'),(49,1,'location_count','Wie viele Standorte hat Ihr Unternehmen?','number',NULL,0,1,4,0,0,'2026-04-13 13:47:30','2026-05-27 07:14:27'),(50,1,'rent_or_buy','Soll die IT-Ausstattung zur Miete oder zum Kauf angeboten werden?','select',NULL,0,1,6,0,0,'2026-04-13 13:50:05','2026-04-13 13:50:05'),(51,1,'budget','Welches Budget haben Sie für IT-Projekte? (Euro)','number',NULL,0,1,7,0,0,'2026-04-13 13:50:05','2026-05-27 07:14:27'),(52,10,'contact_name','Name','text','Max Mustermann',0,1,1,0,0,'2026-04-13 13:57:51','2026-05-27 07:09:13'),(53,10,'contact_email','E-Mail Adresse','email','max.muster@business.com',0,1,2,0,0,'2026-04-13 13:57:51','2026-05-27 07:09:13'),(54,10,'contact_telefon','Telefon Nummer','number','0123 456789',0,1,3,0,0,'2026-04-13 13:57:51','2026-05-27 07:09:13'),(55,11,'strategy','Wie sieht ihre IT-Strategie für die nächsten Jahre aus?','textarea','z. B. Grundlegende Neustrukturierung der IT-Infrastruktur',0,1,1,0,0,'2026-04-13 14:14:06','2026-05-27 07:21:19'),(56,11,'ongoing_projects','Welche aktuellen IT-Projekte sind kurz- bzw. mittelfristig geplant?','textarea','z. B. Austauschen der Notebooks',0,1,2,0,0,'2026-04-13 14:14:06','2026-05-27 07:21:19'),(57,11,'it_documentation','Haben Sie eine aktuelle IT-Dokumentation?','boolean',NULL,0,1,3,0,0,'2026-04-13 14:14:06','2026-04-13 14:14:06'),(58,12,'office_version','Welche Office Version setzen Sie ein?','select',NULL,0,1,1,0,0,'2026-04-14 13:31:34','2026-04-14 13:31:34'),(59,12,'other_software','Welche Software setzen Sie noch ein?','text','z. B. Adobe',0,1,2,0,0,'2026-04-14 13:31:34','2026-04-14 13:31:34'),(60,12,'cloud_services','Welche Cloud-Services nutzen Sie aktuell?','text','z. B. Microsoft Azure, Amazon Web Services (AWS), ...',0,1,3,0,0,'2026-04-14 13:31:34','2026-04-14 13:31:34'),(61,15,'network_structure','Wie ist ihr Netzwerk aufgebaut? CAT-Verkabelung ? Topologie an den Standorten','textarea',NULL,0,1,1,1,0,'2026-04-15 06:39:49','2026-04-15 09:39:36'),(62,15,'network_docu','Ist eine Netzwerk-Dokumentation vorhanden?','boolean',NULL,0,1,2,0,0,'2026-04-15 09:33:52','2026-04-15 09:34:43'),(67,16,'network_contact','Gibt es einen Ansprechpartner für die Netzwerkverkabelung?','boolean',NULL,0,1,1,0,0,'2026-04-15 11:28:35','2026-04-15 11:28:35'),(68,16,'network_contact_name','Name','text',NULL,0,1,2,0,0,'2026-04-15 11:28:35','2026-04-15 11:29:40'),(69,16,'network_contact_email','E-Mail Adresse','email',NULL,0,1,3,0,0,'2026-04-15 11:28:35','2026-04-17 06:31:18'),(70,16,'network_contact_telefon','Telefon Nummer','number',NULL,0,1,4,0,0,'2026-04-15 11:28:35','2026-04-17 06:31:37'),(71,15,'network_hardware','Welche Netzwerkhardware setzen Sie ein?','select',NULL,0,1,3,0,1,'2026-04-15 11:59:31','2026-04-16 07:54:15'),(72,15,'network_support','Haben Sie Hersteller-Support auf ihre Netzwerk-Komponenten?','boolean',NULL,0,1,4,0,0,'2026-04-15 11:59:31','2026-04-15 11:59:31'),(73,15,'network_wlan','Haben Sie ein WLAN an den Standorten?','boolean',NULL,0,1,5,0,0,'2026-04-15 11:59:31','2026-04-15 11:59:31'),(74,15,'accesspoint_count','Wie viele Accesspoints setzen Sie ein?','number',NULL,0,1,6,0,0,'2026-04-15 11:59:31','2026-04-15 11:59:31'),(75,15,'guest_wlan','Haben Sie ein Gäste WLAN?','boolean',NULL,0,1,7,0,0,'2026-04-15 11:59:31','2026-04-15 11:59:31'),(76,17,'internet_provider','Welche(n) Internet-Anbieter nutzen Sie?','select',NULL,0,1,0,0,0,'2026-04-15 12:14:42','2026-05-27 07:29:29'),(77,17,'internet_speed','Welche Internet Verbindungsgeschwindigkeit liefert ihr Provider? (Mbit/s)','number',NULL,0,1,1,0,0,'2026-04-15 12:24:28','2026-04-15 12:24:28'),(78,18,'server_manufacturer','Welche Server Hersteller setzen Sie ein?','select',NULL,0,1,1,0,1,'2026-04-15 12:35:07','2026-05-27 07:40:58'),(79,18,'server_type_count','Anzahl der Server','number',NULL,0,1,2,0,0,'2026-04-15 12:35:07','2026-05-27 09:27:39'),(80,18,'server_operating_system','Welche Betriebssysteme werden eingesetzt?','select',NULL,0,1,3,0,1,'2026-04-15 12:41:46','2026-05-27 07:40:58'),(81,18,'server_roles','Welcher Serverrollen sind etabliert?','text','z. B. Mailserver, Druckserver, Fileserver',0,1,4,0,0,'2026-04-15 12:41:46','2026-04-15 12:41:46'),(82,18,'server_ram','Wieviel Arbeitsspeicher besitzen diese Server? (GB)','select',NULL,0,1,5,0,0,'2026-04-15 12:41:46','2026-05-27 09:31:28'),(83,18,'server_memory','Wieviel Festplatten Kapazität haben diese Server? (TB)','number',NULL,0,1,6,0,0,'2026-04-15 13:36:01','2026-05-27 09:31:28'),(84,18,'server_network_interface','Welche Netzwerk-Anbindung haben diese Server?','select',NULL,0,1,7,0,0,'2026-04-15 13:36:01','2026-04-15 13:36:01'),(85,18,'server_power','Gibt es eine Unterbrechungsfreie Stromversorgung?','boolean',NULL,0,1,8,0,0,'2026-04-15 13:36:01','2026-04-15 13:36:01'),(86,19,'hypervisor','Welchen Hypervisor setzen Sie ein?','select',NULL,0,1,1,0,0,'2026-04-15 13:48:29','2026-04-15 13:48:29'),(87,19,'virtual_maschines','Wieviele VMs werden auf dieser Plattform betrieben? (VM = virtuelle Maschinen)','number',NULL,0,1,2,0,0,'2026-04-15 13:48:29','2026-04-15 13:48:29'),(88,19,'virtual_ram','Wieviel Arbeitsspeicher hat die Virtualisierungsumgebung? (GB)','select',NULL,0,1,3,0,0,'2026-04-15 13:48:29','2026-05-27 09:36:27'),(89,19,'virtual_memory','Wieviel  Festplatten Kapazität hat die Virtualisierungsumgebung? (TB)','number',NULL,0,1,4,0,0,'2026-04-15 13:48:29','2026-05-27 09:36:59'),(90,19,'virtual_network_interface','Welche Netzwerk-Anbindung hat die Virtualisierungsumgebung?','select',NULL,0,1,5,0,0,'2026-04-15 13:48:29','2026-05-27 09:35:36'),(91,20,'clients','Welche Endgeräte setzen Sie ein?','select',NULL,0,1,1,0,1,'2026-04-15 14:09:57','2026-04-15 14:09:57'),(92,20,'client_count','Anzahl der Endgeräte','number',NULL,0,1,3,0,0,'2026-04-15 14:14:00','2026-05-27 09:45:06'),(93,20,'client_age','Wie alt sind die eingesetzten Endgeräte?','text','z. B. 2018',0,1,4,0,0,'2026-04-15 14:14:00','2026-05-27 09:46:35'),(94,20,'client_operating_system','Welche Betriebssysteme sind auf ihren Endgeräten installiert?','select',NULL,0,1,2,0,1,'2026-04-15 14:14:00','2026-05-27 09:45:06'),(95,20,'mobile_clients','Welche mobile Geräte werden eingesetzt?','text','z. B. Apple, Samsung, Dell, Lenovo, Huawei',0,1,5,0,0,'2026-04-15 14:14:00','2026-04-15 14:14:00'),(96,20,'mobile_clients_op','Betriebssysteme der Mobilen Geräten','select',NULL,0,1,6,0,1,'2026-04-15 14:19:15','2026-05-27 09:44:19'),(97,21,'security_software','Welche Antiviren- und Anti-Malware-Lösungen setzen Sie ein?','text','z. B. Checkpoint, Kaspersky, McAffee, ...',0,1,1,0,0,'2026-04-16 09:17:26','2026-04-16 09:17:26'),(98,21,'security_hardware','Welchen Firewall Hersteller setzen Sie ein?','select',NULL,0,1,3,0,0,'2026-04-16 09:17:26','2026-04-16 09:18:51'),(99,21,'firewall','Betreiben sie eine Firewall?','boolean',NULL,0,1,2,0,0,'2026-04-16 09:18:06','2026-04-16 09:18:06'),(100,21,'security_vpn','Nutzen Sie aktuell VPN-Verbindungen?','boolean',NULL,0,1,4,0,0,'2026-04-16 09:23:15','2026-04-16 09:23:15'),(101,21,'security_features','Gibt es Besonderheiten in der bestehenden Firewall-Konfiguration?','select',NULL,0,1,5,0,1,'2026-04-16 09:23:15','2026-04-16 09:23:15'),(102,22,'security_guidelines','Gibt es Unternehmenssicherheitsrichtlinien?','boolean',NULL,0,1,1,0,0,'2026-04-16 09:31:10','2026-04-16 09:31:10'),(103,22,'security_certificates','Hat ihr Unternehmen Zertifizierungsanforderungen?','boolean',NULL,0,1,2,0,0,'2026-04-16 09:33:09','2026-04-16 09:33:09'),(104,22,'certificates_description','Welchen Zertifizierungsanforderungen unterliegt dem Unternehmen?','select',NULL,0,1,3,0,1,'2026-04-16 09:33:09','2026-04-16 09:33:09'),(105,23,'backup_strategy_exists','Haben Sie eine Backup Strategie?','boolean',NULL,0,1,1,0,0,'2026-04-16 09:53:53','2026-05-27 11:43:24'),(106,23,'backup_rto','Maximale Wiederherstellungszeit im Notfall (RTO)','text','z. B. 12 Std.',0,1,20,0,0,'2026-04-16 09:53:53','2026-05-27 06:54:06'),(107,23,'backup_rpo','Angabe des Zeitdeltas für einen Datenverlust (RPO)','text','z. B. 6 Std.',0,1,30,0,0,'2026-04-16 09:53:53','2026-05-27 06:54:06'),(108,23,'backup_software','Welche Backup Software wird eingesetzt?','text','z. B. Veeam, Acronis, Barracuda, ...',0,1,2,0,0,'2026-04-16 09:53:53','2026-05-27 12:07:42'),(123,36,'external_it_service_exists','Gibt es einen externen IT-Dienstleister?','boolean',NULL,0,1,1,0,0,'2026-04-16 09:53:53','2026-05-28 06:59:20'),(124,36,'it_support_sla_exists','Gibt es aktuell IT-Support Vereinbarungen?','boolean',NULL,0,1,3,0,0,'2026-04-16 09:53:53','2026-05-28 06:59:20'),(125,36,'it_support_preferences','Welchen IT-Support stellen Sie sich vor?','select',NULL,0,1,4,0,0,'2026-04-16 09:53:53','2026-05-28 07:07:47'),(128,41,'archiving_solution_exists','Gibt es eine Archivierungslösung?','boolean','1',0,1,0,0,0,'2026-04-16 10:03:18','2026-05-27 11:40:31'),(129,41,'archiving_software','Mit welcher Software werden aktuell Daten archiviert?','text','z. B. DocuWare',0,1,2,0,0,'2026-04-16 10:03:18','2026-05-27 11:40:31'),(130,41,'backup_m365_future','Soll zukünftig M365 gesichert und archiviert werden?','boolean',NULL,0,1,3,0,0,'2026-04-16 10:03:18','2026-05-27 11:40:31'),(131,38,'business_applications','Welche Geschäftsanwendungen setzen Sie ein?','textarea',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(132,38,'database_server_exists','Haben Sie einen Datenbank-Server im Einsatz?','boolean',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(133,38,'software_licenses','Welche Software-Lizenzen haben Sie derzeit im Einsatz?','textarea',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(134,39,'email_provider','Wer ist Ihr E-Mail Provider?','select',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(135,39,'exchange_server_exists','Betreiben Sie einen Exchange Server?','boolean',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(136,39,'email_client','Welcher E-Mail Client kommt zum Einsatz?','select',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(137,40,'phone_system_exists','Haben Sie eine Telefonanlage?','boolean',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(138,40,'phone_system_manufacturer','Hersteller der Telefonanlage?','text',NULL,0,1,20,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(139,40,'cloud_telephony_used','Nutzen Sie bereits Cloud Telefonie?','boolean',NULL,0,1,10,0,0,'2026-04-16 10:03:18','2026-04-16 10:03:29'),(141,36,'internal_it_staff_exists','Haben Sie internes IT-Personal?','boolean',NULL,0,1,2,0,0,'2026-04-16 10:03:18','2026-05-28 06:59:20'),(142,36,'support_response_time','Welche Reaktionszeiten für IT-Support stellen Sie sich vor?','select',NULL,0,1,5,0,0,'2026-04-16 10:03:18','2026-05-28 07:07:47'),(143,36,'service_review_frequency','In welchem Rhythmus wünschen Sie eine Serviceabstimmung?','select',NULL,0,1,6,0,0,'2026-04-16 10:03:18','2026-05-28 06:59:20'),(144,40,'user_right_tel','Gibt es ein Benutzer Berechtigungskonzept?','boolean',NULL,0,1,30,0,0,'2026-05-28 06:55:47','2026-05-28 06:55:47');

--
-- Table structure for table `subcategories`
--

CREATE TABLE `subcategories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int unsigned NOT NULL,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_subcategories_category` (`category_id`),
  CONSTRAINT `fk_subcategories_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `subcategories`
--

INSERT INTO `subcategories` VALUES (1,1,'Unternehmen',1,1,'2026-03-30 09:30:01','2026-03-30 09:30:01'),(10,1,'IT-Ansprechpartner',0,1,'2026-04-13 13:52:03','2026-04-13 13:52:03'),(11,9,'IT-Organisation',0,1,'2026-04-13 14:14:06','2026-04-13 14:14:06'),(12,13,'Software',0,1,'2026-04-14 13:31:34','2026-04-14 13:31:34'),(15,2,'Netzwerk',1,1,'2026-04-14 13:47:50','2026-04-15 11:11:17'),(16,2,'Ansprechpartner Verkabelung',2,1,'2026-04-15 11:26:42','2026-04-15 11:26:42'),(17,14,'Internet Provider',1,1,'2026-04-15 12:14:42','2026-04-15 12:14:42'),(18,3,'Serversysteme',1,1,'2026-04-15 12:35:07','2026-04-15 12:35:07'),(19,15,'Virtualisierung',0,1,'2026-04-15 13:48:29','2026-04-15 13:48:29'),(20,16,'Endgeräte',0,1,'2026-04-15 14:09:57','2026-04-15 14:09:57'),(21,4,'Hardware/Software',1,1,'2026-04-16 09:17:26','2026-04-16 09:17:26'),(22,4,'Organisatorisch',2,1,'2026-04-16 09:17:26','2026-04-16 09:17:26'),(23,17,'Backup',10,1,'2026-04-16 09:53:53','2026-05-27 11:38:43'),(36,21,'IT-Support',20,1,'2026-04-16 09:53:53','2026-04-16 09:53:53'),(38,18,'Software und Applikationen',10,1,'2026-04-16 10:00:23','2026-04-16 10:00:23'),(39,19,'E-Mail',10,1,'2026-04-16 10:00:23','2026-04-16 10:00:23'),(40,20,'Telefonie',10,1,'2026-04-16 10:00:23','2026-04-16 10:00:23'),(41,17,'Archivierung',10,1,'2026-04-16 10:03:18','2026-05-27 11:38:43');


