CREATE DATABASE `ticket_sys`;

CREATE TABLE `Log` (
  `logID` bigint(20) NOT NULL,
  `errorMessage` varchar(200) NOT NULL,
  `datetime` varchar(20) NOT NULL,
  `userID` int(11) NOT NULL,
  PRIMARY KEY (`logID`),
  KEY `userID` (`userID`),
  CONSTRAINT `Log_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `User` (`userID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

CREATE TABLE `ValidateToken` (
  `tokenID` bigint(20) NOT NULL AUTO_INCREMENT,
  `validationDate` varchar(40) NOT NULL,
  `token` text NOT NULL,
  PRIMARY KEY (`tokenID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;


CREATE TABLE `Category` (
  `categoryID` bigint(20) NOT NULL,
  `categoryname` varchar(50) NOT NULL,
  PRIMARY KEY (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

INSERT INTO Category (categoryID, categoryname) VALUES (1, 'Hardware'),
(2,'Software'),(3,'Schäden'),(4,'Personal-Probleme');

CREATE TABLE `User` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `isEmployee` tinyint(1) NOT NULL DEFAULT 0,
  `categoryId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`userID`),
  UNIQUE KEY `username` (`username`),
  KEY `categoryId` (`categoryId`),
  CONSTRAINT `User_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `Category` (`categoryID`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

INSERT INTO User (userID, username, password, isEmployee) VALUES
(1, 'Admin', '$2a$12$b0api3lk2ewEa79pmw3.I.yQneiJ3N/uuX.VT96yrycbvIWJuQFj2', 1),
(2, 'User', '$2a$12$CgCm7dgFRMWniUu0.gRI5eANuPLcuAuL.ldmaL81DmOeQI4dXCQMe', 0);

-- Benutzername: Admin Passwort: admin
-- Benutzername: User Passwort: user

CREATE TABLE `Ticket` (
  `ticketID` int(11) NOT NULL AUTO_INCREMENT,
  `ticketname` varchar(100) NOT NULL,
  `userID` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL CHECK (`status` between 1 and 3),
  `priority` tinyint(4) DEFAULT NULL CHECK (`priority` between 1 and 4),
  `opendAt` varchar(40) NOT NULL,
  `ClosedAt` varchar(40) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `categoryId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ticketID`),
  KEY `userID` (`userID`),
  KEY `categoryId` (`categoryId`),
  CONSTRAINT `Ticket_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `User` (`userID`) ON UPDATE CASCADE,
  CONSTRAINT `Ticket_ibfk_2` FOREIGN KEY (`categoryId`) REFERENCES `Category` (`categoryID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1014 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

CREATE TABLE `Messages` (
  `ticketID` int(11) NOT NULL,
  `messageID` int(11) NOT NULL,
  `Sequence` int(11) NOT NULL,
  `sendAt` varchar(20) NOT NULL,
  `content` text NOT NULL,
  `sender` int(11) NOT NULL,
  PRIMARY KEY (`messageID`),
  KEY `ticketID` (`ticketID`),
  KEY `sender` (`sender`),
  CONSTRAINT `Messages_ibfk_1` FOREIGN KEY (`ticketID`) REFERENCES `Ticket` (`ticketID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Messages_ibfk_2` FOREIGN KEY (`sender`) REFERENCES `User` (`userID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

CREATE TABLE `Attachments` (
  `ticketID` int(11) NOT NULL,
  `messageID` int(11) NOT NULL,
  `attachment` blob NOT NULL,
  PRIMARY KEY (`ticketID`,`messageID`),
  CONSTRAINT `Attachments_ibfk_1` FOREIGN KEY (`ticketID`) REFERENCES `Messages` (`messageID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;