CREATE OR ALTER PROCEDURE SpTransactionTsk
@TransactionInfo NVARCHAR(MAX)
AS 
BEGIN 
   SET NOCOUNT ON
   
 BEGIN TRY
 BEGIN TRANSACTION
  -- FIRSTLY CREATE A TEMPORARY TABLE FOR THE INPUTS
  CREATE TABLE #Customer(TempCustomerId INT IDENTITY PRIMARY KEY, CustomerId INT);
  CREATE TABLE #Room(TempRoomdId INT IDENTITY PRIMARY KEY, RoomId INT);
  CREATE TABLE #Transaction(TempTransactionId INT IDENTITY PRIMARY KEY, TransactionId INT);
  CREATE TABLE #ServiceTransaction(TempServiceTransactonId INT IDENTITY PRIMARY KEY, ServiceTransactionId INT);
  CREATE TABLE #BookingTransaction(BookingTransactonId INT IDENTITY PRIMARY KEY, BookingTransactionId INT);

  -- DELARE VARIABLES TO STORE VALUES
  DECLARE @CustomerIds VARCHAR = ISNULL(JSON_VALUE(@TransactionInfo, '$.CustomerId'), '');
        DECLARE @RoomIds VARCHAR(50) = ISNULL(JSON_VALUE(@TransactionInfo, '$.RoomIds'), '');
        DECLARE @ServiceIds VARCHAR(50) = ISNULL(JSON_VALUE(@TransactionInfo, '$.ServiceIds'), '');

  DECLARE @CurrentDate SMALLDATETIME = GETDATE();
  -- CHECK IF CUSTOMER ID IS PRESENT
  IF @CustomerIds = ''
   BEGIN 
    PRINT 'No Customer Specified'
    ROLLBACK TRANSACTION
    RETURN
   END
  ELSE
    BEGIN
    INSERT INTO #Customer (CustomerId)
    SELECT CAST(value AS INT) AS ServiceId FROM STRING_SPLIT(@CustomerIds, ',')
   END

  DECLARE @NewTransactionId INT;
     EXEC SpTransactionIns @Amount = 100.00, @PaidAmount = 50.00, @DueAmount = 50.00, @TransactionDate = @CurrentDate,
      @Status = 'Pending', @TransactionType = 'Payment', @BranchId = 1, @CustomerId = 10,
      @UpdatedDate = @CurrentDate, @PersonUser = 1,
      @TransactionId = @NewTransactionId OUTPUT;
    
  INSERT INTO #Transaction(TransactionId) VALUES (@NewTransactionId)
  
  -- CHECK IF ROOM ID ARE PRESENT
  IF @RoomIds = '' 
   BEGIN
    PRINT 'No Room Specified'
    ROLLBACK TRANSACTION
    RETURN
   END
  ELSE
    BEGIN
    INSERT INTO #Room(RoomId) SELECT CAST(value AS INT) AS RoomId FROM STRING_SPLIT(@RoomIds, ',');
    DECLARE @TotalRooms INT = (SELECT COUNT(RoomId) FROM #Room)
    
    DECLARE @CurrentRoomId INT;
    DECLARE @Counter INT = 1;

    


    WHILE @Counter <= @TotalRooms
     BEGIN 
      SELECT @CurrentRoomId = RoomId FROM #Room WHERE TempRoomdId = @Counter;
      IF @CurrentRoomId NOT IN (SELECT RoomId FROM Room)
       BEGIN
        SELECT 'SORRY THE ROOM DOESN''T EXIST' AS InvalidRoomId
        ROLLBACK TRANSACTION
        RETURN
       END
      ELSE
       BEGIN 
        DECLARE @NewRoomStatus VARCHAR(50);
        EXEC SpRoomStatusCheck @CurrentRoomId, @RoomStatus = @NewRoomStatus OUTPUT;

     
        IF @NewRoomStatus = 'occupied'
         BEGIN
          PRINT 'The room is already booked';
          ROLLBACK TRANSACTION
          RETURN;
         END
        ELSE
          BEGIN
          DECLARE @NewBookingTransactionId INT;
         
          EXEC SpBookingTransactionIns
            @Transacti,
           @RoomId = @CurrentRoomId,
           @SubTotal = 99.99,
           @CheckIn = @CurrentDate,
           @CheckOut = NULL,
           @CreatedDate = @CurrentDate,
           @UpdatedDate = NULL,
            @Pers
            @BookingTransacti OUTPUT;
          INSERT INTO #BookingTransaction VALUES (@NewBookingTransactionId)

          EXEC SpRoomStatusUpd @CurrentRoomId;
          END
      END

     SET @Counter = @Counter + 1;

     END

    END

    -- FOR SERVICE TRANSACTION
    IF @ServiceIds = '' 
    BEGIN
     PRINT 'No ServiceIds Specified'
    END
   ELSE
    BEGIN
     INSERT INTO #ServiceTransaction(ServiceTransactionId) SELECT CAST(value AS INT) AS Service FROM STRING_SPLIT(@ServiceIds, ',');
     DECLARE @TotalService INT = (SELECT COUNT(ServiceTransactionId) FROM #ServiceTransaction)
    
     DECLARE @CurrentServiceId INT;
     DECLARE @ServiceCounter INT = 1;
     WHILE @ServiceCounter <= @TotalService
      BEGIN
        SELECT @CurrentServiceId = ServiceTransactionId FROM #ServiceTransaction WHERE TempServiceTransact
       IF @CurrentServiceId NOT IN (SELECT ServiceId FROM Service)
        BEGIN
         SELECT 'SORRY THE SERVICE DOESN''T EXIST' AS InvalidServiceId
         ROLLBACK TRANSACTION
         RETURN
      END
      ELSE 
       BEGIN
        DECLARE @ServiceTransactionId INT;
        EXEC SpServiceTransactionIns @ServiceId = @CurrentServiceId,
         @Transacti,
        @SubTotal = 0,
        @CreatedDate = @CurrentDate,
        @UpdatedDate = @CurrentDate,
         @Pers
         @ServiceTransacti OUTPUT;

        INSERT INTO #ServiceTransaction(ServiceTransactionId) VALUES (@ServiceTransactionId)
       END
      END

    END
  
  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH 
  PRINT ERROR_MESSAGE()
   ROLLBACK TRANSACTION
 END CATCH
 SELECT * FROM [Transaction] WHERE TransactionId IN (SELECT TransactionId FROM #Transaction);
 SELECT * FROM BookingTransaction WHERE BookingTransactionId IN (SELECT BookingTransactionId FROM #BookingTransaction);
 
 SELECT * FROM ServiceTransaction WHERE ServiceTransactionId IN (SELECT ServiceTransactionId FROM #ServiceTransaction);
END;