--SP to create bookings. 
/ We will need to pass customerId, roomIds or room numbers with date range and this should mark those rooms as occupied for those days. /

-- FIRST CREATE A SP TO UPDATE THE ROOM STATUS
CREATE PROCEDURE SpRoomStatusUpdate
@RoomId INT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE Room SET RoomStatus = 'Occupied' WHERE RoomId = @RoomId;

 PRINT 'updated room status for Room Id ' + CAST(@RoomId AS VARCHAR)
END

-- CREATE A SP TO CHECK FOR ROOM AVAILABILITY
CREATE PROCEDURE SpRoomStatusCheck
@RoomId INT,
@RoomStatus NVARCHAR(50) OUTPUT
AS 
BEGIN 
 SELECT @RoomStatus = RoomStatus FROM Room WHERE RoomId = @RoomId;
END

-- CHECK IF THE TRANSACTION EXIST FOR THE CUSTOMER

ALTER PROCEDURE SpTransactionCustomerCheck
    @CustomerId INT,
    @NewCustomerId VARCHAR(50) OUTPUT,
 @TransactionId INT OUTPUT
AS
BEGIN
    SELECT @NewCustomerId = 'Exists', @TransactionId = TransactionId FROM [Transaction] WHERE CustomerId = @CustomerId;
END


 




-- Corrected Room Booking Procedure
CREATE PROCEDURE SpRoomBookingTsk
    @RoomId INT,
    @RentingDays INT,
    @BookingTransactionId INT OUTPUT,
    @PaidAmount MONEY,
    @TransactionType VARCHAR(20),
    @BranchId INT,
    @CustomerId INT
AS
BEGIN
    -- FIRST CHECK IF THE ROOM IS AVAILABLE
    DECLARE @NewRoomStatus VARCHAR(50);
    EXEC SpRoomStatusCheck @RoomId, @RoomStatus = @NewRoomStatus OUTPUT; -- Corrected the stored procedure call

    -- IF THE ROOM IS ALREADY BOOKED
    IF @NewRoomStatus = 'Occupied'
    BEGIN
        PRINT 'The room is already booked';
    END
    -- IF THE ROOM ISN'T ALREADY BOOKED
    ELSE
    BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
            -- VARIABLE TO STORE CHECKIN DATE + CHECKOUT DATE
            -- CHECKIN DATE
            DECLARE @CheckInDate VARCHAR(50);
            SET @CheckInDate = GETDATE();
            
            -- CHECKOUT DATE
            DECLARE @CheckoutDate VARCHAR(50);
            SET @CheckoutDate = DATEADD(MONTH, @RentingDays, @CheckInDate);
            
            -- VARIABLE TO GET PRICE FROM ROOM
            DECLARE @RoomPrice MONEY;
            SELECT @RoomPrice = Price FROM Room WHERE RoomId = @RoomId;

   DECLARE @DueAmount MONEY;
   SET @DueAmount = @RoomPrice - @PaidAmount;

   DECLARE @Status VARCHAR(50);
   IF @DueAmount > 0
    SET @Status = 'Pending'
   ELSE
    SET @Status = 'Paid'
            
            -- UPDATE THE ROOM STATUS AS BOOKED
            EXEC SpRoomStatusUpdate @RoomId; -- Corrected the stored procedure name

            DECLARE @CustomerTrans VARCHAR(50);
            DECLARE @ExistingTransactionId INT;
            EXEC SpTransactionCustomerCheck @CustomerId, @NewCustomerId = @CustomerTrans OUTPUT, @TransactionId = @ExistingTransactionId OUTPUT;

            IF @CustomerTrans = 'Exists'
            BEGIN
                DECLARE @NewBookingTransactionId INT; 
                EXEC SpInsertBookingTransaction
                    @TransactionId = @ExistingTransactionId,
                    @RoomId = @RoomId,
                    @SubTotal = @RoomPrice,
                    @CheckIn = @CheckInDate,
                    @CheckOut = @CheckoutDate, 
                    @CreatedDate = @CheckInDate,
                    @UpdatedDate = @CheckInDate,
                    @PersonUser = 123,
                    @BookingTransactionId = @NewBookingTransactionId OUTPUT; -- Corrected the output parameter
            END
            ELSE
            BEGIN
                DECLARE @NewTransactionId INT;
                EXEC SpInsertTransaction
                    @Amount = @RoomPrice,
                    @PaidAmount = @PaidAmount,
                    @DueAmount = @DueAmount,
                    @TransactionDate = @CheckInDate,
                    @Status = @Status,
                    @TransactionType = @TransactionType,
                    @BranchId = @BranchId,
                    @CustomerId = @CustomerId,
                    @UpdatedDate = @CheckoutDate, -- Corrected the parameter name
                    @PersonUser = 1,
                    @TransactionId = @NewTransactionId OUTPUT;

                DECLARE @CurrentBookingTransactionId INT; 
                EXEC SpInsertBookingTransaction
                    @TransactionId = @NewTransactionId,
                    @RoomId = @RoomId,
                    @SubTotal = @RoomPrice,
                    @CheckIn = @CheckInDate,
                    @CheckOut = @CheckoutDate, 
                    @CreatedDate = @CheckInDate,
                    @UpdatedDate = @CheckoutDate,
                    @PersonUser = 1,
                    @BookingTransactionId = @CurrentBookingTransactionId OUTPUT;
            END
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION
        END CATCH
        COMMIT TRANSACTION
    END
END



DECLARE @VeryNewBookingTransactionId INT;



EXEC SpRoomBookingTsk
    @RoomId = 4,
    @RentingDays = 4,
    @BookingTransactionId = @VeryNewBookingTransactionId OUTPUT,
    @PaidAmount = 120,
    @TransactionType = 'Cash',
    @BranchId = 1,
    @CustomerId = 3;