/*
 Navicat Premium Dump SQL

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 80039 (8.0.39)
 Source Host           : localhost:3306
 Source Schema         : hotelmanagementsystem

 Target Server Type    : MySQL
 Target Server Version : 80039 (8.0.39)
 File Encoding         : 65001

 Date: 29/12/2024 09:31:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`  (
  `CustomerID` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Gender` enum('Male','Female') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Age` int NULL DEFAULT NULL,
  `ContactInfo` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `IDCardNumber` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`CustomerID`) USING BTREE,
  UNIQUE INDEX `IDCardNumber`(`IDCardNumber` ASC) USING BTREE,
  INDEX `idx_customer_name`(`Name` ASC) USING BTREE,
  CONSTRAINT `customer_chk_1` CHECK ((`Age` >= 0) and (`Age` <= 120))
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (2, 'Bob Smith', 'Male', 35, 'bob.smith@gmail.com', '456 Elm St, Springfield', '987654321098765432');
INSERT INTO `customer` VALUES (3, 'Charlie Brown', 'Male', 22, 'charlie.brown@gmail.com', '789 Pine St, Springfield', '123123123123123123');
INSERT INTO `customer` VALUES (4, 'Diana Prince', 'Female', 30, 'diana.prince@gmail.com', '321 Oak St, Springfield', '321321321321321321');
INSERT INTO `customer` VALUES (5, 'Eve Adams', 'Female', 40, 'eve.adams@gmail.com', '654 Maple St, Springfield', '654654654654654654');
INSERT INTO `customer` VALUES (6, 'Frank Miller', 'Male', 29, 'frank.miller@gmail.com', '987 Cedar St, Springfield', '987987987987987987');
INSERT INTO `customer` VALUES (7, 'Grace Lee', 'Female', 33, 'grace.lee@gmail.com', '147 Birch St, Springfield', '741741741741741741');
INSERT INTO `customer` VALUES (8, 'Hank Green', 'Male', 45, 'hank.green@gmail.com', '258 Willow St, Springfield', '852852852852852852');
INSERT INTO `customer` VALUES (9, 'Ivy Wilson', 'Female', 25, 'ivy.wilson@gmail.com', '369 Ash St, Springfield', '963963963963963963');
INSERT INTO `customer` VALUES (10, 'Jack White', 'Male', 38, 'jack.white@gmail.com', '159 Poplar St, Springfield', '159159159159159159');
INSERT INTO `customer` VALUES (11, '盖良琛', 'Male', 20, '', 'SDUST', '202211071402');
INSERT INTO `customer` VALUES (12, '张闰清', 'Male', 20, '', 'China', '202211071430');
INSERT INTO `customer` VALUES (14, '张三', 'Male', 30, '123456789', 'Beijing', '123456789');
INSERT INTO `customer` VALUES (15, 'Anna', 'Female', 25, '', '', '370000');
INSERT INTO `customer` VALUES (16, '李四', 'Female', 58, '', '', '15655');
INSERT INTO `customer` VALUES (17, 'alice', 'Male', 30, '123@163.com', 'America', '42005');

-- ----------------------------
-- Table structure for customerlog
-- ----------------------------
DROP TABLE IF EXISTS `customerlog`;
CREATE TABLE `customerlog`  (
  `LogID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NULL DEFAULT NULL,
  `Name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`LogID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customerlog
-- ----------------------------
INSERT INTO `customerlog` VALUES (1, 14, '张三', '2024-12-28 14:52:53');
INSERT INTO `customerlog` VALUES (2, 15, 'Anna', '2024-12-28 15:24:50');
INSERT INTO `customerlog` VALUES (3, 16, '李四', '2024-12-28 19:05:06');
INSERT INTO `customerlog` VALUES (4, 17, 'alice', '2024-12-28 19:59:49');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `EmployeeID` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Gender` enum('Male','Female') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Age` int NULL DEFAULT NULL,
  `ContactInfo` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `HireDate` date NOT NULL,
  `Salary` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`EmployeeID`) USING BTREE,
  INDEX `idx_employee_position`(`Position` ASC) USING BTREE,
  CONSTRAINT `employee_chk_1` CHECK ((`Age` >= 18) and (`Age` <= 65)),
  CONSTRAINT `employee_chk_2` CHECK (`Salary` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 24 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'Alice Manager', 'Female', 40, 'alice.manager@hotel.com', 'Manager', '2015-03-01', 6000.00);
INSERT INTO `employee` VALUES (2, 'Bob Cleaner', 'Male', 30, 'bob.cleaner@hotel.com', 'Cleaner', '2018-05-10', 3000.00);
INSERT INTO `employee` VALUES (3, 'Charlie FrontDesk', 'Male', 28, 'charlie.frontdesk@hotel.com', 'Front Desk', '2020-01-15', 3500.00);
INSERT INTO `employee` VALUES (4, 'Diana Waitress', 'Female', 25, 'diana.waitress@hotel.com', 'Waitress', '2019-07-20', 3200.00);
INSERT INTO `employee` VALUES (5, 'Eve Chef', 'Female', 35, 'eve.chef@hotel.com', 'Chef', '2016-11-11', 5000.00);
INSERT INTO `employee` VALUES (6, 'Frank Technician', 'Male', 42, 'frank.technician@hotel.com', 'Technician', '2014-06-25', 4500.00);
INSERT INTO `employee` VALUES (7, 'Grace Supervisor', 'Female', 38, 'grace.supervisor@hotel.com', 'Supervisor', '2013-09-30', 5500.00);
INSERT INTO `employee` VALUES (8, 'Hank Security', 'Male', 50, 'hank.security@hotel.com', 'Security', '2012-02-15', 3300.00);
INSERT INTO `employee` VALUES (9, 'Ivy Receptionist', 'Female', 29, 'ivy.receptionist@hotel.com', 'Receptionist', '2017-12-01', 3400.00);
INSERT INTO `employee` VALUES (10, 'Jack Porter', 'Male', 33, 'jack.porter@hotel.com', 'Porter', '2021-04-20', 3100.00);
INSERT INTO `employee` VALUES (11, 'Kelly IT', 'Female', 26, 'kelly.it@hotel.com', 'IT Support', '2022-01-01', 3600.00);

-- ----------------------------
-- Table structure for payment
-- ----------------------------
DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment`  (
  `PaymentID` int NOT NULL AUTO_INCREMENT,
  `StayID` int NOT NULL,
  `Amount` decimal(10, 2) NOT NULL,
  `PaymentMethod` enum('CreditCard','Cash','Online') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `PaymentDate` date NOT NULL,
  PRIMARY KEY (`PaymentID`) USING BTREE,
  INDEX `StayID`(`StayID` ASC) USING BTREE,
  INDEX `idx_payment_date`(`PaymentDate` ASC) USING BTREE,
  CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`StayID`) REFERENCES `stay` (`StayID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `payment_chk_1` CHECK (`Amount` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of payment
-- ----------------------------
INSERT INTO `payment` VALUES (3, 3, 600.00, 'CreditCard', '2024-01-07');
INSERT INTO `payment` VALUES (4, 4, 800.00, 'CreditCard', '2024-01-08');
INSERT INTO `payment` VALUES (5, 5, 750.00, 'Cash', '2024-01-10');
INSERT INTO `payment` VALUES (6, 6, 850.00, 'CreditCard', '2024-01-11');
INSERT INTO `payment` VALUES (7, 7, 700.00, 'Online', '2024-01-12');
INSERT INTO `payment` VALUES (8, 8, 650.00, 'Cash', '2024-01-13');
INSERT INTO `payment` VALUES (9, 9, 600.00, 'CreditCard', '2024-01-14');
INSERT INTO `payment` VALUES (10, 10, 500.00, 'Online', '2024-01-15');
INSERT INTO `payment` VALUES (44, 10, 20.00, 'Cash', '2025-01-01');

-- ----------------------------
-- Table structure for performance
-- ----------------------------
DROP TABLE IF EXISTS `performance`;
CREATE TABLE `performance`  (
  `PerformanceID` int NOT NULL AUTO_INCREMENT,
  `EmployeeID` int NOT NULL,
  `EvaluationDate` date NOT NULL,
  `Score` int NULL DEFAULT NULL,
  `Comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  PRIMARY KEY (`PerformanceID`) USING BTREE,
  INDEX `idx_performance_employeeid`(`EmployeeID` ASC) USING BTREE,
  CONSTRAINT `performance_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `performance_chk_1` CHECK (`Score` between 1 and 100)
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of performance
-- ----------------------------
INSERT INTO `performance` VALUES (1, 1, '2024-01-05', 95, 'Excellent leadership and management skills');
INSERT INTO `performance` VALUES (2, 2, '2024-01-05', 85, 'Good work, but needs improvement in punctuality');
INSERT INTO `performance` VALUES (3, 3, '2024-01-05', 90, 'Great at customer handling');
INSERT INTO `performance` VALUES (4, 4, '2024-01-05', 88, 'Efficient and polite');
INSERT INTO `performance` VALUES (5, 5, '2024-01-05', 92, 'Cooks with exceptional quality');
INSERT INTO `performance` VALUES (6, 6, '2024-01-05', 80, 'Satisfactory technical support');
INSERT INTO `performance` VALUES (7, 7, '2024-01-05', 89, 'Excellent supervisory skills');
INSERT INTO `performance` VALUES (8, 8, '2024-01-05', 80, 'Needs improvement in security vigilance');
INSERT INTO `performance` VALUES (9, 9, '2024-01-05', 88, 'Very friendly and professional');
INSERT INTO `performance` VALUES (10, 10, '2024-01-05', 86, 'Reliable and helpful porter');
INSERT INTO `performance` VALUES (11, 11, '2024-01-05', 91, 'Great support for IT issues');
INSERT INTO `performance` VALUES (12, 11, '2024-12-28', 70, 'Need to work Harder');

-- ----------------------------
-- Table structure for reservation
-- ----------------------------
DROP TABLE IF EXISTS `reservation`;
CREATE TABLE `reservation`  (
  `ReservationID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NOT NULL,
  `RoomID` int NOT NULL,
  `CheckInDate` date NOT NULL,
  `CheckOutDate` date NOT NULL,
  `Status` enum('Pending','Confirmed','CheckedIn','CheckedOut') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'Pending',
  PRIMARY KEY (`ReservationID`) USING BTREE,
  INDEX `CustomerID`(`CustomerID` ASC) USING BTREE,
  INDEX `RoomID`(`RoomID` ASC) USING BTREE,
  INDEX `idx_reservation_status`(`Status` ASC) USING BTREE,
  INDEX `idx_reservation_dates`(`CheckInDate` ASC, `CheckOutDate` ASC) USING BTREE,
  CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`RoomID`) REFERENCES `room` (`RoomID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `reservation_chk_1` CHECK (`CheckOutDate` > `CheckInDate`)
) ENGINE = InnoDB AUTO_INCREMENT = 61 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of reservation
-- ----------------------------
INSERT INTO `reservation` VALUES (2, 2, 3, '2024-01-02', '2024-01-06', 'Confirmed');
INSERT INTO `reservation` VALUES (3, 3, 4, '2024-01-03', '2024-01-07', 'Confirmed');
INSERT INTO `reservation` VALUES (4, 4, 5, '2024-01-04', '2024-01-08', 'CheckedIn');
INSERT INTO `reservation` VALUES (5, 5, 6, '2024-01-05', '2024-01-10', 'Confirmed');
INSERT INTO `reservation` VALUES (6, 6, 7, '2024-01-06', '2024-01-11', 'Confirmed');
INSERT INTO `reservation` VALUES (7, 7, 8, '2024-01-07', '2024-01-12', 'Confirmed');
INSERT INTO `reservation` VALUES (8, 8, 9, '2024-01-08', '2024-01-13', 'Confirmed');
INSERT INTO `reservation` VALUES (9, 9, 10, '2024-01-09', '2024-01-14', 'CheckedIn');
INSERT INTO `reservation` VALUES (10, 10, 11, '2024-01-10', '2024-01-15', 'Confirmed');
INSERT INTO `reservation` VALUES (11, 11, 12, '2024-12-28', '2024-12-31', 'Confirmed');
INSERT INTO `reservation` VALUES (12, 12, 13, '2024-12-28', '2024-12-31', 'CheckedIn');

-- ----------------------------
-- Table structure for room
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room`  (
  `RoomID` int NOT NULL AUTO_INCREMENT,
  `RoomNumber` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `RoomTypeID` int NOT NULL,
  `Status` enum('Available','Occupied') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'Available',
  PRIMARY KEY (`RoomID`) USING BTREE,
  UNIQUE INDEX `RoomNumber`(`RoomNumber` ASC) USING BTREE,
  INDEX `RoomTypeID`(`RoomTypeID` ASC) USING BTREE,
  INDEX `idx_room_status`(`Status` ASC) USING BTREE,
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`RoomTypeID`) REFERENCES `roomtype` (`RoomTypeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of room
-- ----------------------------
INSERT INTO `room` VALUES (1, '101', 2, 'Occupied');
INSERT INTO `room` VALUES (3, '103', 2, 'Available');
INSERT INTO `room` VALUES (4, '104', 2, 'Available');
INSERT INTO `room` VALUES (5, '105', 3, 'Available');
INSERT INTO `room` VALUES (6, '201', 3, 'Available');
INSERT INTO `room` VALUES (7, '202', 4, 'Available');
INSERT INTO `room` VALUES (8, '203', 4, 'Available');
INSERT INTO `room` VALUES (9, '204', 5, 'Available');
INSERT INTO `room` VALUES (10, '205', 5, 'Available');
INSERT INTO `room` VALUES (11, '301', 6, 'Available');
INSERT INTO `room` VALUES (12, '302', 7, 'Available');
INSERT INTO `room` VALUES (13, '303', 8, 'Occupied');

-- ----------------------------
-- Table structure for roomtype
-- ----------------------------
DROP TABLE IF EXISTS `roomtype`;
CREATE TABLE `roomtype`  (
  `RoomTypeID` int NOT NULL AUTO_INCREMENT,
  `TypeName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `BasePrice` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`RoomTypeID`) USING BTREE,
  UNIQUE INDEX `idx_roomtype_name`(`TypeName` ASC) USING BTREE,
  CONSTRAINT `roomtype_chk_1` CHECK (`BasePrice` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of roomtype
-- ----------------------------
INSERT INTO `roomtype` VALUES (1, 'Single', 'Single bed room, suitable for 1 person', 88.00);
INSERT INTO `roomtype` VALUES (2, 'Double', 'Double bed room, suitable for 2 people', 132.00);
INSERT INTO `roomtype` VALUES (3, 'Suite', 'Spacious suite with living area', 275.00);
INSERT INTO `roomtype` VALUES (4, 'Deluxe', 'Luxury room with premium facilities', 330.00);
INSERT INTO `roomtype` VALUES (5, 'Family', 'Family room, can accommodate 4 people', 198.00);
INSERT INTO `roomtype` VALUES (6, 'Economy', 'Economical room with basic facilities', 66.00);
INSERT INTO `roomtype` VALUES (7, 'Business', 'Room equipped for business travelers', 220.00);
INSERT INTO `roomtype` VALUES (8, 'Presidential', 'High-end suite with exclusive facilities', 550.00);
INSERT INTO `roomtype` VALUES (9, 'Accessible', 'Room designed for accessibility needs', 110.00);
INSERT INTO `roomtype` VALUES (10, 'Penthouse', 'Top floor luxury room with scenic views', 440.00);

-- ----------------------------
-- Table structure for salarychangelog
-- ----------------------------
DROP TABLE IF EXISTS `salarychangelog`;
CREATE TABLE `salarychangelog`  (
  `LogID` int NOT NULL AUTO_INCREMENT,
  `EmployeeID` int NULL DEFAULT NULL,
  `OldSalary` decimal(10, 2) NULL DEFAULT NULL,
  `NewSalary` decimal(10, 2) NULL DEFAULT NULL,
  `ChangeDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`LogID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salarychangelog
-- ----------------------------

-- ----------------------------
-- Table structure for service
-- ----------------------------
DROP TABLE IF EXISTS `service`;
CREATE TABLE `service`  (
  `ServiceID` int NOT NULL AUTO_INCREMENT,
  `ServiceName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `Price` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`ServiceID`) USING BTREE,
  CONSTRAINT `service_chk_1` CHECK (`Price` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of service
-- ----------------------------
INSERT INTO `service` VALUES (12, 'Breakfast', 'Buffet breakfast available in the morning', 20.00);
INSERT INTO `service` VALUES (13, 'Laundry', 'Clothes washing and ironing service', 15.00);
INSERT INTO `service` VALUES (14, 'Room Service', 'Food and drinks delivered to room', 25.00);
INSERT INTO `service` VALUES (15, 'Spa', 'Relaxation and massage therapy', 50.00);
INSERT INTO `service` VALUES (16, 'Fitness Center', 'Access to gym and fitness facilities', 30.00);
INSERT INTO `service` VALUES (17, 'Airport Shuttle', 'Transportation to and from the airport', 40.00);
INSERT INTO `service` VALUES (18, 'Car Rental', 'Rental cars for local travel', 60.00);
INSERT INTO `service` VALUES (19, 'Business Center', 'Access to business and meeting facilities', 70.00);
INSERT INTO `service` VALUES (20, 'Daily Cleaning', 'Room cleaning service', 10.00);
INSERT INTO `service` VALUES (21, 'Mini Bar', 'Stocked mini bar in room', 35.00);

-- ----------------------------
-- Table structure for shift
-- ----------------------------
DROP TABLE IF EXISTS `shift`;
CREATE TABLE `shift`  (
  `ShiftID` int NOT NULL AUTO_INCREMENT,
  `EmployeeID` int NOT NULL,
  `ShiftDate` date NOT NULL,
  `ShiftStartTime` time NOT NULL,
  `ShiftEndTime` time NOT NULL,
  PRIMARY KEY (`ShiftID`) USING BTREE,
  INDEX `idx_shift_employee_date`(`EmployeeID` ASC, `ShiftDate` ASC) USING BTREE,
  CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `shift_chk_1` CHECK (`ShiftEndTime` > `ShiftStartTime`)
) ENGINE = InnoDB AUTO_INCREMENT = 34 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of shift
-- ----------------------------
INSERT INTO `shift` VALUES (23, 1, '2024-01-01', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (24, 2, '2024-01-01', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (25, 3, '2024-01-01', '16:00:00', '23:59:59');
INSERT INTO `shift` VALUES (26, 4, '2024-01-01', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (27, 5, '2024-01-01', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (28, 6, '2024-01-02', '16:00:00', '23:59:59');
INSERT INTO `shift` VALUES (29, 7, '2024-01-02', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (30, 8, '2024-01-02', '16:00:00', '23:59:59');
INSERT INTO `shift` VALUES (31, 9, '2024-01-03', '08:00:00', '16:00:00');
INSERT INTO `shift` VALUES (32, 10, '2024-01-03', '16:00:00', '23:59:59');
INSERT INTO `shift` VALUES (33, 11, '2024-12-01', '06:00:00', '18:00:00');

-- ----------------------------
-- Table structure for stay
-- ----------------------------
DROP TABLE IF EXISTS `stay`;
CREATE TABLE `stay`  (
  `StayID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NOT NULL,
  `RoomID` int NOT NULL,
  `CheckInDate` date NOT NULL,
  `CheckOutDate` date NULL DEFAULT NULL,
  `PaymentInfo` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `AdditionalServices` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  PRIMARY KEY (`StayID`) USING BTREE,
  INDEX `CustomerID`(`CustomerID` ASC) USING BTREE,
  INDEX `RoomID`(`RoomID` ASC) USING BTREE,
  CONSTRAINT `stay_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stay_ibfk_2` FOREIGN KEY (`RoomID`) REFERENCES `room` (`RoomID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stay_chk_1` CHECK ((`CheckOutDate` is null) or (`CheckOutDate` >= `CheckInDate`))
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stay
-- ----------------------------
INSERT INTO `stay` VALUES (3, 3, 3, '2024-01-03', '2024-01-07', 'Paid by Online Payment', 'Breakfast');
INSERT INTO `stay` VALUES (4, 4, 4, '2024-01-04', '2024-01-08', 'Paid by Credit Card', 'Spa, Laundry');
INSERT INTO `stay` VALUES (5, 5, 5, '2024-01-05', '2024-01-10', 'Paid by Cash', 'Breakfast, Room Service');
INSERT INTO `stay` VALUES (6, 6, 6, '2024-01-06', '2024-01-11', 'Paid by Credit Card', 'Breakfast');
INSERT INTO `stay` VALUES (7, 7, 7, '2024-01-07', '2024-01-12', 'Paid by Online Payment', 'Spa');
INSERT INTO `stay` VALUES (8, 8, 8, '2024-01-08', '2024-01-13', 'Paid by Cash', 'Laundry');
INSERT INTO `stay` VALUES (9, 9, 9, '2024-01-09', '2024-01-14', 'Paid by Credit Card', 'Room Service');
INSERT INTO `stay` VALUES (10, 10, 10, '2024-01-10', '2024-01-15', 'Paid by Online Payment', 'Breakfast, Spa');

-- ----------------------------
-- View structure for customerreservationview
-- ----------------------------
DROP VIEW IF EXISTS `customerreservationview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `customerreservationview` AS select `c`.`CustomerID` AS `CustomerID`,`c`.`Name` AS `CustomerName`,`c`.`ContactInfo` AS `ContactInfo`,`ro`.`RoomNumber` AS `RoomNumber`,`rt`.`TypeName` AS `RoomType`,`r`.`CheckInDate` AS `CheckInDate`,`r`.`CheckOutDate` AS `CheckOutDate`,`r`.`Status` AS `ReservationStatus` from (((`customer` `c` join `reservation` `r` on((`c`.`CustomerID` = `r`.`CustomerID`))) join `room` `ro` on((`r`.`RoomID` = `ro`.`RoomID`))) join `roomtype` `rt` on((`ro`.`RoomTypeID` = `rt`.`RoomTypeID`)));

-- ----------------------------
-- View structure for employeeshiftstatsview
-- ----------------------------
DROP VIEW IF EXISTS `employeeshiftstatsview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `employeeshiftstatsview` AS select `e`.`EmployeeID` AS `EmployeeID`,`e`.`Name` AS `EmployeeName`,count(`s`.`ShiftID`) AS `TotalShifts`,sum(timestampdiff(HOUR,`s`.`ShiftStartTime`,`s`.`ShiftEndTime`)) AS `TotalHours` from (`employee` `e` join `shift` `s` on((`e`.`EmployeeID` = `s`.`EmployeeID`))) group by `e`.`EmployeeID`,`e`.`Name`;

-- ----------------------------
-- View structure for roomstatusview
-- ----------------------------
DROP VIEW IF EXISTS `roomstatusview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `roomstatusview` AS select `ro`.`RoomID` AS `RoomID`,`ro`.`RoomNumber` AS `RoomNumber`,`rt`.`TypeName` AS `RoomType`,`ro`.`Status` AS `RoomStatus` from (`room` `ro` join `roomtype` `rt` on((`ro`.`RoomTypeID` = `rt`.`RoomTypeID`)));

-- ----------------------------
-- View structure for roomtyperevenueview
-- ----------------------------
DROP VIEW IF EXISTS `roomtyperevenueview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `roomtyperevenueview` AS select `rt`.`TypeName` AS `RoomType`,count(`s`.`StayID`) AS `TotalStays`,sum(`p`.`Amount`) AS `TotalRevenue` from (((`stay` `s` join `room` `ro` on((`s`.`RoomID` = `ro`.`RoomID`))) join `roomtype` `rt` on((`ro`.`RoomTypeID` = `rt`.`RoomTypeID`))) join `payment` `p` on((`s`.`StayID` = `p`.`StayID`))) group by `rt`.`TypeName`;

-- ----------------------------
-- View structure for staydetailsview
-- ----------------------------
DROP VIEW IF EXISTS `staydetailsview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `staydetailsview` AS select `s`.`StayID` AS `StayID`,`c`.`Name` AS `CustomerName`,`ro`.`RoomNumber` AS `RoomNumber`,`rt`.`TypeName` AS `RoomType`,`p`.`Amount` AS `PaymentAmount`,`p`.`PaymentMethod` AS `PaymentMethod`,`s`.`AdditionalServices` AS `AdditionalServices` from ((((`stay` `s` join `customer` `c` on((`s`.`CustomerID` = `c`.`CustomerID`))) join `room` `ro` on((`s`.`RoomID` = `ro`.`RoomID`))) join `roomtype` `rt` on((`ro`.`RoomTypeID` = `rt`.`RoomTypeID`))) join `payment` `p` on((`s`.`StayID` = `p`.`StayID`)));

-- ----------------------------
-- Procedure structure for AddOrUpdateCustomer
-- ----------------------------
DROP PROCEDURE IF EXISTS `AddOrUpdateCustomer`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddOrUpdateCustomer`(
    IN p_Name VARCHAR(100),
    IN p_Gender ENUM('Male', 'Female'),
    IN p_Age INT,
    IN p_ContactInfo VARCHAR(100),
    IN p_Address TEXT,
    IN p_IDCardNumber VARCHAR(20)
)
BEGIN
    -- 检查身份证号是否已存在
    IF EXISTS (SELECT 1 FROM Customer WHERE IDCardNumber = p_IDCardNumber) THEN
        -- 更新已存在的顾客信息
        UPDATE Customer
        SET Name = p_Name,
            Gender = p_Gender,
            Age = p_Age,
            ContactInfo = p_ContactInfo,
            Address = p_Address
        WHERE IDCardNumber = p_IDCardNumber;
    ELSE
        -- 插入新的顾客信息
        INSERT INTO Customer (Name, Gender, Age, ContactInfo, Address, IDCardNumber)
        VALUES (p_Name, p_Gender, p_Age, p_ContactInfo, p_Address, p_IDCardNumber);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for DeletePendingReservations
-- ----------------------------
DROP PROCEDURE IF EXISTS `DeletePendingReservations`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePendingReservations`()
BEGIN
    -- 删除所有未确认的预订记录
    DELETE FROM Reservation
    WHERE Status = 'Pending';
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for GetCustomerTotalSpending
-- ----------------------------
DROP PROCEDURE IF EXISTS `GetCustomerTotalSpending`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomerTotalSpending`(
    IN p_CustomerID INT,
    OUT p_TotalSpending DECIMAL(10,2)
)
BEGIN
    -- 统计顾客的总消费金额
    SELECT SUM(p.Amount)
    INTO p_TotalSpending
    FROM Payment p
    JOIN Stay s ON p.StayID = s.StayID
    WHERE s.CustomerID = p_CustomerID;
    
    -- 如果没有消费记录，设置为 0
    IF p_TotalSpending IS NULL THEN
        SET p_TotalSpending = 0;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for GetStayRecordsByDate
-- ----------------------------
DROP PROCEDURE IF EXISTS `GetStayRecordsByDate`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetStayRecordsByDate`(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    -- 查询入住记录
    SELECT 
        s.StayID,
        c.Name AS CustomerName,
        ro.RoomNumber,
        s.CheckInDate,
        s.CheckOutDate
    FROM Stay s
    JOIN Customer c ON s.CustomerID = c.CustomerID
    JOIN Room ro ON s.RoomID = ro.RoomID
    WHERE s.CheckInDate >= p_StartDate AND s.CheckOutDate <= p_EndDate;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UpdateRoomStatus
-- ----------------------------
DROP PROCEDURE IF EXISTS `UpdateRoomStatus`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateRoomStatus`(
    IN p_RoomNumber VARCHAR(10),
    IN p_NewStatus ENUM('Available', 'Occupied')
)
BEGIN
    -- 更新房间状态
    UPDATE Room
    SET Status = p_NewStatus
    WHERE RoomNumber = p_RoomNumber;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table customer
-- ----------------------------
DROP TRIGGER IF EXISTS `AfterInsertCustomer_Log`;
delimiter ;;
CREATE TRIGGER `AfterInsertCustomer_Log` AFTER INSERT ON `customer` FOR EACH ROW BEGIN
    -- 记录新增顾客信息
    INSERT INTO CustomerLog (CustomerID, Name)
    VALUES (NEW.CustomerID, NEW.Name);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `BeforeUpdateEmployeeSalary_LogChanges`;
delimiter ;;
CREATE TRIGGER `BeforeUpdateEmployeeSalary_LogChanges` BEFORE UPDATE ON `employee` FOR EACH ROW BEGIN
    -- 记录薪资变化日志
    IF OLD.Salary != NEW.Salary THEN
        INSERT INTO SalaryChangeLog (EmployeeID, OldSalary, NewSalary)
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table reservation
-- ----------------------------
DROP TRIGGER IF EXISTS `BeforeInsertReservation_PreventDuplicates`;
delimiter ;;
CREATE TRIGGER `BeforeInsertReservation_PreventDuplicates` BEFORE INSERT ON `reservation` FOR EACH ROW BEGIN
    DECLARE duplicate_count INT;
    -- 检查是否存在重复预订
    SELECT COUNT(*)
    INTO duplicate_count
    FROM Reservation
    WHERE RoomID = NEW.RoomID
      AND NEW.CheckInDate < CheckOutDate
      AND NEW.CheckOutDate > CheckInDate;

    -- 如果有重复记录，则抛出错误
    IF duplicate_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Duplicate reservation detected for the same room and date range';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table stay
-- ----------------------------
DROP TRIGGER IF EXISTS `AfterInsertStay_UpdateRoomStatus`;
delimiter ;;
CREATE TRIGGER `AfterInsertStay_UpdateRoomStatus` AFTER INSERT ON `stay` FOR EACH ROW BEGIN
    -- 将房间状态更新为 "Occupied"
    UPDATE Room
    SET Status = 'Occupied'
    WHERE RoomID = NEW.RoomID;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table stay
-- ----------------------------
DROP TRIGGER IF EXISTS `AfterDeleteStay_DeletePayment`;
delimiter ;;
CREATE TRIGGER `AfterDeleteStay_DeletePayment` AFTER DELETE ON `stay` FOR EACH ROW BEGIN
    -- 删除与删除的 Stay 记录关联的支付记录
    DELETE FROM Payment
    WHERE StayID = OLD.StayID;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;