CREATE Procedure [dbo].[Player_InsertPlayer](
                                        @Nickname               nvarchar(50),
                                        @GameNickname           nvarchar(50),
                                        @Password               nvarchar(50),
                                        @EmailAddress           nvarchar(100),
                                        @EmailStatus            int,
                                        @FirstName              nvarchar(50),
                                        @LastName               nvarchar(50),
                                        @Gender                 int,
                                        @BirthDate              datetime,
                                        @Address1               nvarchar(100),
                                        @Address2               nvarchar(100),
                                        @City                   nvarchar(50),
                                        @State                  nvarchar(50),
                                        @Country                nvarchar(2),
                                        @ZipCode                nvarchar(10),
                                        @Phone                  nvarchar(30),
                                        @Mobile                 nvarchar(30),
                                        @Fax                    nvarchar(30),
                                        @Comments               nvarchar(2000),
                                        @OperatorID             int,
                                        @AffiliateID            int,
                                        @AgentID                int,
                                        @PlayerStatus           int,
                                        @AudienceType           int = 2,
                                        @MadeDeposit            bit,
                                        @MustChangePassword     bit,
                                        @IsLock                 bit,
                                        @ChargeBack             bit,
                                        @ChargeBackDesc         nvarchar(500),
                                        @TechComments           nvarchar(500),
                                        @VisitedDepositPage     bit,
                                        @CurrencyID             int,
                                        @ExternalPlayerID       int,
                                        @ExternalHirParentID    int,
                                        @SecretQuestion         nvarchar(100),
                                        @SecretAnswer           nvarchar(50),
                                        @ChatStatus             int,
                                        @IsExternalWallet       bit = null,
                                        @GroupLimitID           int = 0) As
Begin
begin tran
BEGIN TRY

    Declare @playerID int
    DECLARE @LockOperatorID int, @IsPlayerExist int

    IF(@EmailAddress = '')
    BEGIN
        set @EmailAddress = @Nickname + '@fakeemail.com' ;
    END

    IF(@CurrencyID = -1)
    BEGIN
        SET @CurrencyID = 1
    END

        -- if there is a config with id 4 set for an operator it means it is set up with external wallet
        DECLARE @IsExternalWalletOperator bit =
            case when exists(SELECT OPERATOR_VALUE FROM TO_OPERATOR_CONFIG_VALUES where CONFIG_ID = 4 and OPERATOR_ID = @OperatorID)
            then 1 else 0 end

            INSERT INTO [TP_PLAYERS]
                   ([nickname]
                   ,[game_nickname]
                   ,[password]
                   ,[email_address]
                   ,[email_status]
                   ,[first_name]
                   ,[last_name]
                   ,[gender]
                   ,[birth_date]
                   ,[address1]
                   ,[address2]
                   ,[city]
                   ,[state]
                   ,[country]
                   ,[zip_code]
                   ,[phone]
                   ,[mobile]
                   ,[fax]
                   ,[comments]
                   ,[join_date]
                   ,[operator_id]
                   ,[affiliate_id]
                   ,[agent_id]
                   ,[player_status]
                   ,[audience_type]
                   ,[made_deposit]
                   ,[status_date]
                   ,[must_change_password]
                   ,[is_lock]
                   ,[charge_back]
                   ,[charge_back_desc]
                   ,[tech_comments]
                   ,[visited_deposit_page]
                   ,[currency_id]
                   ,[external_player_id]
                   ,[external_hierarchy_parent_id]
                   ,[security_question]
                   ,[security_answer]
                   ,[CHAT_STATUS]
                   ,is_external_wallet)

             VALUES
                   (@Nickname
                    ,@GameNickname
                    ,@Password
                    ,@EmailAddress
                    ,@EmailStatus
                    ,@FirstName
                    ,@LastName
                    ,@Gender
                    ,@BirthDate
                    ,@Address1
                    ,@Address2
                    ,@City
                    ,@State
                    ,@Country
                    ,@ZipCode
                    ,@Phone
                    ,@Mobile
                    ,@Fax
                    ,@Comments
                    ,getDate()
                    ,@OperatorID
                    ,@AffiliateID
                    ,@AgentID
                    ,@PlayerStatus
                    ,@AudienceType
                    ,@MadeDeposit
                    ,getDate()
                    ,@MustChangePassword
                    ,@IsLock
                    ,@ChargeBack
                    ,@ChargeBackDesc
                    ,@TechComments
                    ,@VisitedDepositPage
                    ,@CurrencyID
                    ,@ExternalPlayerID
                    ,@ExternalHirParentID
                    ,@SecretQuestion
                    ,@SecretAnswer
                    ,@ChatStatus
                    ,@IsExternalWalletOperator)


            Set @PlayerID = SCOPE_IDENTITY()

            IF @AffiliateID <> -1
                BEGIN
                    INSERT INTO T_AFFILIATE_PLAYERS
                    VALUES(@AffiliateID, @playerID, SYSDATETIME());
                END;

            IF @GroupLimitID <> 0
                BEGIN
                    -- on Api level the group limit is taken from cache and then validated
                    -- needs to validate on DB level too as the cache could not be update on the Api request
                    DECLARE @AssignedOnOperator bit;
                    SELECT @AssignedOnOperator = COUNT(*) FROM TG_VIP_GROUPS_LEVELS
                    WHERE OPERATOR_ID = @OperatorID
                         AND GROUP_ID = @GroupLimitID

                    IF @assignedOnOperator = 1
                        BEGIN
                            INSERT TG_VIP_GROUPS_LEVELS(PLAYER_ID, GROUP_ID) VALUES(@PlayerID, @GroupLimitID)
                        END;
                END;

            exec dbo.Player_GetPlayerByParam @OperatorID, @playerID, -1, '', ''
    commit tran
    END TRY
    BEGIN CATCH
         Declare @ErrorMsg NVarChar(1000),
                  @ErrorLine NVarChar(100),
                  @ErrorMessage NVarChar(1000),
                  @ErrorSeverity Int,
                  @ErrorState Int,
                  @DatabaseID Int,
                  @DatabaseName NVarChar(100);

          Select @ErrorLine = Convert(NVarChar, ERROR_NUMBER()),
                 @ErrorMessage = ERROR_MESSAGE(),
                 @ErrorSeverity = ERROR_SEVERITY(),
                 @ErrorState = ERROR_STATE(),
                 @DatabaseID = DB_ID(),
                 @DatabaseName = DB_NAME();

          if (@@TRANCOUNT > 0)
            rollback transaction;

          Set @ErrorMsg = 'DB:Player_InsertPlayer (Error=' + @ErrorMessage;

          IF @ErrorMessage like '%Violation of UNIQUE KEY constraint%'
          BEGIN
                EXEC dbo.General_WriteLog @MessageText = @ErrorMsg, @Level = 2
          END
          ELSE
          BEGIN
                Exec dbo.General_WriteGeneralError '0.0.0.0', @ErrorMsg;
          END

          Raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState, @DatabaseID, @DatabaseName);
    END CATCH
End