CREATE TABLE Room (
                      RoomID INT PRIMARY KEY IDENTITY(1,1),
                      RoomNumber NVARCHAR(10) NOT NULL,              -- 房号
                      RoomType NVARCHAR(20) NOT NULL,                -- 房间类型（标准间、大床房等）
                      Price DECIMAL(10,2) NOT NULL,                  -- 房价
                      Status NVARCHAR(20) NOT NULL                   -- 当前状态（空闲、预订、占用、清洁中）
);
CREATE TABLE Customer (
                          CustomerID INT PRIMARY KEY IDENTITY(1,1),
                          Name NVARCHAR(50) NOT NULL,
                          IDCard NVARCHAR(20) NOT NULL,                  -- 身份证号
                          Phone NVARCHAR(20) NOT NULL
);
CREATE TABLE Booking (
                         BookingID INT PRIMARY KEY IDENTITY(1,1),
                         CustomerID INT NOT NULL,
                         RoomID INT NOT NULL,
                         BookingTime DATETIME NOT NULL,
                         CheckInTime DATETIME,
                         CheckOutTime DATETIME,
                         TotalAmount DECIMAL(10,2),
                         Status NVARCHAR(20) NOT NULL,                  -- 状态（已预订、已入住、已退房、已取消）
                         FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
                         FOREIGN KEY (RoomID) REFERENCES Room(RoomID)
);
CREATE TABLE Admin (
                       AdminID INT PRIMARY KEY IDENTITY(1,1),
                       Username NVARCHAR(50) NOT NULL UNIQUE,
                       Password NVARCHAR(255) NOT NULL
);
INSERT INTO Room (RoomNumber, RoomType, Price, Status) VALUES
                                                           (N'101', N'标准间', 300.00, N'空闲'),
                                                           (N'102', N'标准间', 300.00, N'空闲'),
                                                           (N'201', N'大床房', 400.00, N'占用'),
                                                           (N'202', N'大床房', 400.00, N'预订'),
                                                           (N'301', N'套房', 600.00, N'空闲');
INSERT INTO Customer (Name, IDCard, Phone) VALUES
                                               (N'张三', N'110101199001015678', N'13800000001'),
                                               (N'李四', N'110102199202026789', N'13800000002'),
                                               (N'王五', N'110103198503035432', N'13800000003');
-- 张三预订了房间 101，还未入住
INSERT INTO Booking (CustomerID, RoomID, BookingTime, CheckInTime, CheckOutTime, TotalAmount, Status) VALUES
    (1, 1, '2025-05-24 10:00:00', NULL, NULL, NULL, N'已预订');

-- 李四已入住房间 201
INSERT INTO Booking (CustomerID, RoomID, BookingTime, CheckInTime, CheckOutTime, TotalAmount, Status) VALUES
    (2, 3, '2025-05-23 14:00:00', '2025-05-23 14:30:00', NULL, NULL, N'已入住');

-- 王五已退房，住过房间 102
INSERT INTO Booking (CustomerID, RoomID, BookingTime, CheckInTime, CheckOutTime, TotalAmount, Status) VALUES
    (3, 2, '2025-05-20 09:00:00', '2025-05-20 10:00:00', '2025-05-22 11:00:00', 600.00, N'已退房');

INSERT INTO Admin (Username, Password) VALUES
                                           (N'admin', N'admin123'),
                                           (N'manager', N'password456');