SQL & DDL: Truck Simulator DDL Final.txt

DROP TABLE IF EXISTS `aktivniprodukti`;
DROP VIEW IF EXISTS `aktivniprodukti`;

CREATE TABLE `customer` (
  `CustomerID` int NOT NULL,
  `CustomerName` varchar(100) DEFAULT NULL,
  `CustomerSurName` varchar(100) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `CustomerContact` varchar(20) DEFAULT NULL,
  `Password` varchar(255) NOT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customerfeedback` (
  `FeedbackID` int NOT NULL,
  `CustomerID` int DEFAULT NULL,
  `ProductID` int DEFAULT NULL,
  `Rating` int DEFAULT NULL,
  `Comment` text,
  `FeedbackDate` date DEFAULT NULL,
  `TransactionID` int DEFAULT NULL,
  PRIMARY KEY (`FeedbackID`),
  KEY `CustomerID` (`CustomerID`),
  KEY `ProductID` (`ProductID`),
  KEY `fk_feedback_transaction` (`TransactionID`),
  CONSTRAINT `customerfeedback_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`),
  CONSTRAINT `customerfeedback_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`),
  CONSTRAINT `fk_feedback_transaction` FOREIGN KEY (`TransactionID`) REFERENCES `procurement` (`TransactionID`),
  CONSTRAINT `customerfeedback_chk_1` CHECK ((`Rating` between 1 and 5))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `employee` (
  `EmployeeID` int NOT NULL,
  `EmployeeName` varchar(100) DEFAULT NULL,
  `EmployeeSurName` varchar(100) DEFAULT NULL,
  `Position` varchar(100) DEFAULT NULL,
  `Department` varchar(100) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`EmployeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `maintenance` (
  `MainID` int NOT NULL,
  `EmployeeID` int DEFAULT NULL,
  `ProductID` int DEFAULT NULL,
  `MainDate` date DEFAULT NULL,
  `Description` text,
  `Cost` decimal(10,2) DEFAULT NULL,
  `Status` enum('Pending','Completed') DEFAULT 'Pending',
  `StartTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `EndTime` datetime DEFAULT NULL,
  PRIMARY KEY (`MainID`),
  KEY `EmployeeID` (`EmployeeID`),
  KEY `ProductID` (`ProductID`),
  CONSTRAINT `maintenance_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`),
  CONSTRAINT `maintenance_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `procurement` (
  `TransactionID` int NOT NULL,
  `EmployeeID` int DEFAULT NULL,
  `CustomerID` int DEFAULT NULL,
  `ProductID` int DEFAULT NULL,
  `ProcurementDate` date DEFAULT NULL,
  `Quantity` int DEFAULT NULL,
  `Status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `Notified` tinyint(1) DEFAULT '0',
  `GroupID` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`TransactionID`),
  KEY `EmployeeID` (`EmployeeID`),
  KEY `CustomerID` (`CustomerID`),
  KEY `ProductID` (`ProductID`),
  CONSTRAINT `procurement_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`),
  CONSTRAINT `procurement_ibfk_2` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`),
  CONSTRAINT `procurement_ibfk_3` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `product` (
  `ProductID` int NOT NULL,
  `Model` varchar(100) DEFAULT NULL,
  `Price` decimal(10,2) DEFAULT NULL,
  `LicensePlate` varchar(50) DEFAULT NULL,
  `Status` enum('available','sold','rented','maintenance') NOT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_type` (
  `TransactionID` int NOT NULL,
  `Type` enum('Rent','Buy') NOT NULL,
  `Duration` int DEFAULT NULL,
  `MonthlyPay` decimal(10,2) DEFAULT NULL,
  `TotalPrice` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`TransactionID`),
  CONSTRAINT `t_type_ibfk_1` FOREIGN KEY (`TransactionID`) REFERENCES `procurement` (`TransactionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `trailer` (
  `ProductID` int NOT NULL,
  `Capacity` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`ProductID`),
  CONSTRAINT `trailer_ibfk_1` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `truck` (
  `ProductID` int NOT NULL,
  `HP` int DEFAULT NULL,
  PRIMARY KEY (`ProductID`),
  CONSTRAINT `truck_ibfk_1` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `views` (
  `CustomerID` int NOT NULL,
  `ProductID` int NOT NULL,
  PRIMARY KEY (`CustomerID`,`ProductID`),
  KEY `ProductID` (`ProductID`),
  CONSTRAINT `views_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`),
  CONSTRAINT `views_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `wallet` (
  `WalletID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NOT NULL,
  `Balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `CardNumber` varchar(16) DEFAULT NULL,
  `ExpiryDate` date DEFAULT NULL,
  `CVV` varchar(4) DEFAULT NULL,
  `CardHolderName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`WalletID`),
  KEY `CustomerID` (`CustomerID`),
  CONSTRAINT `wallet_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- VIEWS
CREATE VIEW `aktivniprodukti` AS
SELECT `product`.`ProductID` AS `ProductID`,
       `product`.`Model` AS `Model`,
       `product`.`Price` AS `Price`,
       `product`.`LicensePlate` AS `LicensePlate`
FROM `product`
WHERE (`product`.`Status` = 'available');