CREATE TYPE WorkoutEntryType
   AS TABLE
      (      Bodyweight DECIMAL(18,2) NULL,
            Comments NVARCHAR(500) NULL,
            Date DATETIME2(7) NOT NULL,
            Energy INT NULL,
            Id UNIQUEIDENTIFIER NOT NULL,
            Minutes FLOAT NULL,
            Rating INT NULL,
            Title NVARCHAR(50) NOT NULL,
            UserId UNIQUEIDENTIFIER NOT NULL
        );

GO

CREATE TYPE WorkoutItemType
   AS TABLE
      ( Comments NVARCHAR(100) NULL,
            [Order] INT NOT NULL,
            Reps INT NOT NULL,
            RPE INT NULL,
            Sets INT NOT NULL,
            UserExerciseId UNIQUEIDENTIFIER NOT NULL,
            Weight DECIMAL(18,2) NOT NULL,
             WorkoutEntryId UNIQUEIDENTIFIER NOT NULL
        );
GO

CREATE TYPE UserExerciseType
   AS TABLE
      (   Id UNIQUEIDENTIFIER NOT NULL,
          [Name] NVARCHAR(128) NOT NULL,
          ProtocolName NVARCHAR(450) NOT NULL,
          UserId UNIQUEIDENTIFIER NOT NULL
       );
GO

CREATE TYPE IdMappingType
   AS TABLE
      ([Id1] UNIQUEIDENTIFIER NOT NULL,
       [Id2] UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID())
GO

CREATE PROCEDURE InsertWorkouts(
@Workouts WorkoutEntryType READONLY,
@Items WorkoutItemType READONLY,
@Exercises UserExerciseType READONLY)
AS
BEGIN


    DECLARE @NewWorkoutIds IdMappingType

    INSERT INTO @NewWorkoutIds(Id1)
    SELECT Id as [Id1] FROM @Workouts

    INSERT INTO WorkoutEntry(Id,Title,Date,Comments,UserId,Rating,Energy,Bodyweight,Minutes)
    SELECT [Id2],Title,Date,Comments,UserId,Rating,Energy,Bodyweight,Minutes
    FROM @Workouts w
    JOIN @NewWorkoutIds n ON n.[Id1] = w.[Id]

    -- Insert completely brand new exercises
    INSERT INTO Exercise(Name)
    SELECT pE.Name FROM @Exercises pE
    LEFT JOIN Exercise e on e.Name = pE.Name
    WHERE e.Name IS NULL

    -- Insert new UserExercise for this user
    INSERT INTO UserExercise (UserId,ExerciseId,ExerciseProtocolId)
    SELECT pE.UserId,e.Id,ep.Id FROM @Exercises pE
    JOIN ExerciseProtocol ep on ep.Name = pE.ProtocolName
    JOIN Exercise e on e.Name = pE.Name
    LEFT JOIN UserExercise ue on ue.ExerciseId = e.Id and ue.UserId = pE.UserId
    WHERE ue.Id IS NULL

    DECLARE @NewUserExerciseIds IdMappingType

    INSERT INTO @NewUserExerciseIds
    SELECT pE.[Id],ue.Id FROM @Exercises pE
    JOIN Exercise e on e.Name = pE.Name
    JOIN UserExercise ue on ue.ExerciseId = e.Id
    WHERE ue.UserId = pE.UserId

    INSERT INTO WorkoutItem(WorkoutEntryId,UserExerciseId,Weight,Sets,Reps,Comments,[Order],RPE)
    SELECT n.[Id2],ne.Id2,Weight,Sets,Reps,Comments,[Order],RPE
    FROM @Items wi
    JOIN @NewWorkoutIds n ON n.[Id1] = wi.[WorkoutEntryId]
    JOIN @NewUserExerciseIds ne on ne.Id1 = wi.UserExerciseId

END
GO