-- --------------------------------------------------------
-- Host:                         10.10.5.31
-- Server-Version:               8.0.42-0ubuntu0.24.04.1 - (Ubuntu)
-- Server-Betriebssystem:        Linux
-- HeidiSQL Version:             12.10.0.7000
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Exportiere Datenbank-Struktur für hikos
CREATE DATABASE IF NOT EXISTS `hikos` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `hikos`;

-- Exportiere Struktur von Tabelle hikos.aduser
CREATE TABLE IF NOT EXISTS `aduser` (
  `aduser_id` int NOT NULL AUTO_INCREMENT,
  `aduser_samaccountname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `aduser_sid` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `aduser_ruleset_id` int NOT NULL DEFAULT '-1',
  PRIMARY KEY (`aduser_id`),
  UNIQUE KEY `aduser_samaccountname` (`aduser_samaccountname`),
  UNIQUE KEY `aduser_sid` (`aduser_sid`),
  KEY `FK_aduser_ruleset` (`aduser_ruleset_id`),
  CONSTRAINT `FK_aduser_ruleset` FOREIGN KEY (`aduser_ruleset_id`) REFERENCES `ruleset` (`ruleset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.aduser: ~0 rows (ungefähr)

-- Exportiere Struktur von Tabelle hikos.contact
CREATE TABLE IF NOT EXISTS `contact` (
  `contact_id` int NOT NULL AUTO_INCREMENT,
  `contact_owner_id` int NOT NULL DEFAULT '-1',
  `contact_aduser_id` int DEFAULT NULL,
  `contact_displayname` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `contact_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_homeoffice` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_room` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_department_id` int DEFAULT NULL,
  `contact_location_id` int DEFAULT NULL,
  PRIMARY KEY (`contact_id`) USING BTREE,
  UNIQUE KEY `contact_aduser_id` (`contact_aduser_id`),
  KEY `FK_contact_department` (`contact_department_id`),
  KEY `FK_contact_location` (`contact_location_id`),
  CONSTRAINT `FK_contact_aduser_2` FOREIGN KEY (`contact_aduser_id`) REFERENCES `aduser` (`aduser_id`),
  CONSTRAINT `FK_contact_department` FOREIGN KEY (`contact_department_id`) REFERENCES `department` (`department_id`),
  CONSTRAINT `FK_contact_location` FOREIGN KEY (`contact_location_id`) REFERENCES `location` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.contact: ~1 rows (ungefähr)
REPLACE INTO `contact` (`contact_id`, `contact_owner_id`, `contact_aduser_id`, `contact_displayname`, `contact_phone`, `contact_mobile`, `contact_homeoffice`, `contact_email`, `contact_room`, `contact_department_id`, `contact_location_id`) VALUES
	(1, -1, NULL, 'Bergner, Jan', '1142', '123', '123', '123', '123', NULL, NULL);

-- Exportiere Struktur von Tabelle hikos.contactkeyword
CREATE TABLE IF NOT EXISTS `contactkeyword` (
  `contactkeyword_contact` int NOT NULL,
  `contactkeyword_keyword` int NOT NULL,
  PRIMARY KEY (`contactkeyword_contact`,`contactkeyword_keyword`),
  KEY `FK_contactkeyword_keyword` (`contactkeyword_keyword`),
  CONSTRAINT `FK_contactkeyword_contact` FOREIGN KEY (`contactkeyword_contact`) REFERENCES `contact` (`contact_id`),
  CONSTRAINT `FK_contactkeyword_keyword` FOREIGN KEY (`contactkeyword_keyword`) REFERENCES `keyword` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.contactkeyword: ~0 rows (ungefähr)

-- Exportiere Struktur von Tabelle hikos.department
CREATE TABLE IF NOT EXISTS `department` (
  `department_id` int NOT NULL AUTO_INCREMENT,
  `department_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `department_name` (`department_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.department: ~0 rows (ungefähr)

-- Exportiere Struktur von Tabelle hikos.flag
CREATE TABLE IF NOT EXISTS `flag` (
  `flag_id` int NOT NULL AUTO_INCREMENT,
  `flag_contact_id` int NOT NULL,
  `flag_key` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `flag_value` longtext COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`flag_id`),
  UNIQUE KEY `flag_contact_id_flag_key` (`flag_contact_id`,`flag_key`),
  CONSTRAINT `FK__contact` FOREIGN KEY (`flag_contact_id`) REFERENCES `contact` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.flag: ~0 rows (ungefähr)

-- Exportiere Struktur von Tabelle hikos.keyword
CREATE TABLE IF NOT EXISTS `keyword` (
  `keyword_id` int NOT NULL AUTO_INCREMENT,
  `keyword_owner` int NOT NULL DEFAULT '-1',
  `keyword_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`keyword_id`),
  UNIQUE KEY `keyword_owner_keyword_name` (`keyword_owner`,`keyword_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.keyword: ~2 rows (ungefähr)
REPLACE INTO `keyword` (`keyword_id`, `keyword_owner`, `keyword_name`) VALUES
	(1, -1, 'Test'),
	(2, 1, 'Demo1');

-- Exportiere Struktur von Tabelle hikos.location
CREATE TABLE IF NOT EXISTS `location` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `location_address` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `location_zip` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `location_city` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `location_name` (`location_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.location: ~0 rows (ungefähr)

-- Exportiere Struktur von Tabelle hikos.ruleset
CREATE TABLE IF NOT EXISTS `ruleset` (
  `ruleset_id` int NOT NULL AUTO_INCREMENT,
  `ruleset_name` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `ruleset_default_contact_read` int NOT NULL DEFAULT '1',
  `ruleset_default_contact_write` int NOT NULL DEFAULT '0',
  `ruleset_default_contact_delete` int NOT NULL DEFAULT '0',
  `ruleset_default_keyword_read` int NOT NULL DEFAULT '1',
  `ruleset_default_keyword_write` int NOT NULL DEFAULT '0',
  `ruleset_default_keyword_delete` int NOT NULL DEFAULT '0',
  `ruleset_default_keyword_attach` int NOT NULL DEFAULT '0',
  `ruleset_default_keyword_detach` int NOT NULL DEFAULT '0',
  `ruleset_default_aduser_read` int NOT NULL DEFAULT '0',
  `ruleset_default_aduser_write` int NOT NULL DEFAULT '0',
  `ruleset_default_aduser_delete` int NOT NULL DEFAULT '0',
  `ruleset_default_location_read` int NOT NULL DEFAULT '1',
  `ruleset_default_location_write` int NOT NULL DEFAULT '0',
  `ruleset_default_location_delete` int NOT NULL DEFAULT '0',
  `ruleset_default_department_read` int NOT NULL DEFAULT '1',
  `ruleset_default_department_write` int NOT NULL DEFAULT '0',
  `ruleset_default_department_delete` int NOT NULL DEFAULT '0',
  `ruleset_self_contact_read` int NOT NULL DEFAULT '1',
  `ruleset_self_contact_write` int NOT NULL DEFAULT '1',
  `ruleset_self_keyword_attach` int NOT NULL DEFAULT '1',
  `ruleset_self_keyword_detach` int NOT NULL DEFAULT '1',
  `ruleset_private_contact_read` int NOT NULL DEFAULT '1',
  `ruleset_private_contact_write` int NOT NULL DEFAULT '1',
  `ruleset_private_keyword_add` int NOT NULL DEFAULT '1',
  `ruleset_private_keyword_delete` int NOT NULL DEFAULT '1',
  `ruleset_private_keyword_attach` int NOT NULL DEFAULT '1',
  `ruleset_private_keyword_detach` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`ruleset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.ruleset: ~2 rows (ungefähr)
REPLACE INTO `ruleset` (`ruleset_id`, `ruleset_name`, `ruleset_default_contact_read`, `ruleset_default_contact_write`, `ruleset_default_contact_delete`, `ruleset_default_keyword_read`, `ruleset_default_keyword_write`, `ruleset_default_keyword_delete`, `ruleset_default_keyword_attach`, `ruleset_default_keyword_detach`, `ruleset_default_aduser_read`, `ruleset_default_aduser_write`, `ruleset_default_aduser_delete`, `ruleset_default_location_read`, `ruleset_default_location_write`, `ruleset_default_location_delete`, `ruleset_default_department_read`, `ruleset_default_department_write`, `ruleset_default_department_delete`, `ruleset_self_contact_read`, `ruleset_self_contact_write`, `ruleset_self_keyword_attach`, `ruleset_self_keyword_detach`, `ruleset_private_contact_read`, `ruleset_private_contact_write`, `ruleset_private_keyword_add`, `ruleset_private_keyword_delete`, `ruleset_private_keyword_attach`, `ruleset_private_keyword_detach`) VALUES
	(-1, 'Default_Ruleset', 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
	(0, 'Default_Admin_Ruleset', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);

-- Exportiere Struktur von Tabelle hikos.sessions
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `token` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Exportiere Daten aus Tabelle hikos.sessions: ~4 rows (ungefähr)
REPLACE INTO `sessions` (`id`, `username`, `token`, `created_at`, `expires_at`) VALUES
	(5, 'admin', 'qITgAy47f5gmlBG72y_vJWh5IC7_aXQGKsL6M7IFTWU', '2025-05-20 18:14:30', '2025-05-21 18:14:30'),
	(6, 'admin', 'Vjde9ngqPdUchF_I2_FzekK6Kb3-wBtZy_7mFPtRMV8', '2025-05-20 19:18:58', '2025-05-21 19:18:58'),
	(7, 'admin', 'E2znECPx1Ppcwsby4ky7sQMf55Wax0KttnFmV83rqoc', '2025-05-21 04:21:43', '2025-05-22 04:21:43'),
	(8, 'admin', 'Y7MVV-Nd4i1BPEerPLbv0jM0gJ4dYnnqZ0OtxnVWUXw', '2025-05-21 04:22:08', '2025-05-22 04:22:08');

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;