-- =============================================
-- Author:      Ace
-- =============================================
ALTER PROCEDURE [dbo].[_UserAuthentication]
@CharName VARCHAR(64),
@IP VARCHAR(20)
AS
SET NOCOUNT ON

DECLARE @StrUserID VARCHAR(64),
        @HardwareId VARCHAR(20),
        @CharId INT

SELECT TOP 1    @StrUserID = A.AccountID,
                @CharId = C.CharID
FROM    SILKROAD_R_SHARD.._Char C
JOIN    SILKROAD_R_SHARD.._User U
ON      C.CharID = U.CharID
JOIN    SILKROAD_R_SHARD.._AccountJID A
ON      U.UserJID = A.JID
WHERE   C.CharName16 = @CharName

SELECT TOP 1    @HardwareId = HardwareId
FROM    _User
WHERE   IP = @IP
AND     StrUserID = @StrUserID

IF @HardwareId IS NULL
BEGIN
    INSERT INTO _UserAttempts VALUES (@CharName, @IP, @HardwareId, 'HardwareId Is NULL', GETDATE())
    SELECT 0
    RETURN
END

IF EXISTS (SELECT HardwareId FROM _HardwareIdBanList WHERE HardwareId = @HardwareId)
BEGIN
    INSERT INTO _UserAttempts VALUES (@CharName, @IP, @HardwareId, 'User is hardware-banned!', GETDATE())
    SELECT 1
    RETURN
END

IF EXISTS (SELECT CharName FROM _User WHERE CharName = @CharName)
BEGIN
    INSERT INTO _UserAttempts VALUES (@CharName, @IP, @HardwareId, 'User is already logged in!', GETDATE())
    DELETE FROM _User WHERE CharName = @CharName
    SELECT 2
    RETURN
END

IF (SELECT COUNT(HardwareId) FROM _User WHERE HardwareId = @HardwareId AND CharName IS NOT NULL) >= 2
BEGIN
    INSERT INTO _UserAttempts VALUES (@CharName, @IP, @HardwareId, 'HardwareId Limit has been reached!', GETDATE())
    SELECT 3
    RETURN
END


IF NOT EXISTS (SELECT CharId FROM _UserInformation WHERE CharId = @CharId AND HardwareId = @HardwareId)
    INSERT INTO _UserInformation VALUES (@CharId, @HardwareId, @IP, GETDATE())

IF  ((SELECT I.ItemID FROM SILKROAD_R_SHARD.._Inventory I WITH(NOLOCK)
     JOIN SILKROAD_R_SHARD.._Items IT WITH(NOLOCK) ON I.ItemID = IT.ID64
     JOIN SILKROAD_R_SHARD.._CharNameList C WITH(NOLOCK) ON I.CharID = C.CharID
     WHERE C.CharName16 = @CharName AND I.Slot = 8
     AND    IT.RefItemID IN (SELECT ID FROM SILKROAD_R_SHARD.._RefObjCommon WHERE TypeID1 = 3 AND TypeID2 = 1 AND TypeID3 = 7 AND TypeID4 IN (1,2,3) AND Service = 1)) != 0)
BEGIN
    IF (SELECT COUNT(HardwareId) FROM _User WHERE HardwareId = @HardwareId AND JobEquipped = 1 AND CharName IS NOT NULL) >= 1
    BEGIN
        INSERT INTO _UserAttempts VALUES (@CharName, @IP, @HardwareId, 'Job HardwareId Limit has been reached!', GETDATE())
        SELECT 4
        RETURN
    END

    UPDATE  _User
    SET     CharName = @CharName,
            JobEquipped = 1
    WHERE   HardwareId = @HardwareId
    AND     StrUserID = @StrUserID
    AND     IP = @IP
    AND     CharName IS NULL

    SELECT 5
    RETURN
END

UPDATE  _User
SET     CharName = @CharName
WHERE   HardwareId = @HardwareId
AND     StrUserID = @StrUserID
AND     IP = @IP
AND     CharName IS NULL

SELECT 6
RETURN