CREATE TABLE [Sales].[CustomerCategoryPerson](
	[CustomerCategoryPersonID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerCategoryId] [int] NOT NULL,
	[PersonId] [int] NOT NULL,
	[IsWritable] [bit] NOT NULL,
	[LastUpdatedBy] [int] NOT NULL,
 CONSTRAINT [PK_CustomerCategoryPerson] PRIMARY KEY CLUSTERED 
(
	[CustomerCategoryPersonID] ASC
)...

CREATE TABLE [Sales].[CustomerPerson](
	[CustomerPersonId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NOT NULL,
	[PersonId] [int] NOT NULL,
	[IsWritable] [bit] NOT NULL,
	[LastEditedBy] [int] NOT NULL,
 CONSTRAINT [PK_CustomerPerson] PRIMARY KEY CLUSTERED 
(
	[CustomerPersonId] ASC
)...

ALTER PROCEDURE [Sales].[CustomersNameSearchUnionV1]
@UserId as int,
@SearchText nvarchar(20),
@MaximumRowsToReturn int
AS
BEGIN
	-- Individual assignment
    SELECT TOP(@MaximumRowsToReturn) [Customers].[CustomerID] as "ID", [Customers].[CustomerName] as "Name", [Customers].[IsOnCreditHold] as "IsOnCreditHold"
    FROM Sales.Customers
	INNER JOIN [Sales].[CustomerPerson] ON ([Sales].[Customers].[CustomerId] = [Sales].[CustomerPerson].[CustomerId])
    WHERE ((CustomerName LIKE N'%' + @SearchText + N'%')
		AND ([Sales].[CustomerPerson].PersonId = @UserId))
    --ORDER BY [CustomerName]

	UNION 
	
	-- group assignment
   SELECT TOP(@MaximumRowsToReturn) [Customers].[CustomerID] as "ID", [Customers].[CustomerName] as "Name", [Customers].[IsOnCreditHold] as "IsOnCreditHold"
   FROM [Sales].[Customers]
      INNER JOIN [Sales].[CustomerCategories] ON ([Sales].[Customers].[CustomerCategoryID] = [Sales].[CustomerCategories].[CustomerCategoryID])
      INNER JOIN [Sales].[CustomerCategoryPerson] ON ([Sales].[Customers].[CustomerCategoryID] = [CustomerCategoryPerson].[CustomerCategoryID])
    WHERE ((CustomerName LIKE N'%' + @SearchText + N'%')
		AND ([Sales].[CustomerCategoryPerson].PersonId = @UserId))

END;

CREATE VIEW [Sales].[CustomerPersonReadV1]
AS
-- Individual assignment
   SELECT [Sales].[Customers].[CustomerID], [Sales].[CustomerPerson].[PersonID], [Sales].[Customers].[CustomerCategoryID]
   FROM [Sales].[Customers]
      INNER JOIN [Sales].[CustomerPerson] ON ( [Sales].[Customers].[CustomerID] = CustomerPerson.CustomerID)

UNION -- Takes care of duplicates

-- Group assignment
   SELECT [Sales].[Customers].[CustomerID], [Sales].[CustomerCategoryPerson].[PersonID], [Sales].[Customers].[CustomerCategoryID]
   FROM [Sales].[Customers]
      --INNER JOIN [Sales].[CustomerCategories] ON ([Sales].[Customers].[CustomerCategoryID] = [Sales].[CustomerCategories].[CustomerCategoryID])
      INNER JOIN [Sales].[CustomerCategoryPerson] ON ([Sales].[Customers].[CustomerCategoryID] = [CustomerCategoryPerson].[CustomerCategoryID])

CREATE VIEW [Sales].[CustomerPersonWriteV1]
AS
-- Individual assignment
   SELECT [Sales].[Customers].[CustomerID], [Sales].[CustomerPerson].[PersonID], [Sales].[Customers].[CustomerCategoryID]
   FROM [Sales].[Customers]
      INNER JOIN [Sales].[CustomerPerson] ON (([Sales].[Customers].[CustomerID] = [CustomerPerson].[CustomerID]) AND ([Sales].[CustomerPerson].[IsWritable] = 1))

UNION -- Takes care of duplicates

-- Group assignment
   SELECT [Sales].[Customers].[CustomerID], [Sales].[CustomerCategoryPerson].[PersonID], [Sales].[Customers].[CustomerCategoryID]
   FROM [Sales].[Customers]
      INNER JOIN [Sales].[CustomerCategories] ON ([Sales].[Customers].[CustomerCategoryID] = [Sales].[CustomerCategories].[CustomerCategoryID])
      INNER JOIN [Sales].[CustomerCategoryPerson] ON ([Sales].[Customers].[CustomerCategoryID] = [CustomerCategoryPerson].[CustomerCategoryID] AND ([Sales].[CustomerCategoryPerson].[IsWritable] = 1))

CREATE PROCEDURE [Sales].[CustomersListV1]
@UserId as int
AS
BEGIN
SELECT [Customers].[CustomerID] as "ID", [Customers].[CustomerName] as "Name", [Customers].[IsOnCreditHold] as "IsOnCreditHold"
	FROM [Sales].[Customers]
		INNER JOIN [Sales].[CustomerPersonReadV1] ON ([Sales].[Customers].[CustomerID] = [Sales].[CustomerPersonReadV1].CustomerID)
    WHERE ([Sales].[CustomerPersonReadV1].PersonId = @UserId)
	ORDER BY Name
END

[HttpGet(), Authorize(Roles = "SalesPerson,SalesAdministrator")]
[ProducesResponseType(StatusCodes.Status200OK, Type = typeof(List<Models.CustomerListDtoV1>))]
public async Task<ActionResult<IEnumerable<Models.CustomerListDtoV1>>> Get()
{
      IEnumerable<Models.CustomerListDtoV1> response;

      using (SqlConnection db = new SqlConnection(this.connectionString))
      {
         response = await db.QueryWithRetryAsync<Models.CustomerListDtoV1>(sql: "[Sales].[CustomersListV1]", param: new { userId = HttpContext.PersonId() }, commandType: CommandType.StoredProcedure);
      }

   return this.Ok(response);
}

CREATE PROCEDURE [Sales].[CustomerCreditHoldStatusUpdateV1]
@UserID int,
@CustomerId int,
@IsOnCreditHold Bit
AS
BEGIN
    UPDATE [Sales].[Customers]
    SET IsOnCreditHold = @IsOnCreditHold, LastEditedBy = @UserID
	FROM [Sales].[Customers]
		INNER JOIN [Sales].[CustomerPersonWriteV1] ON ([Sales].[Customers].[CustomerID] = [Sales].[CustomerPersonWriteV1].CustomerID)
    WHERE (([Sales].[CustomerPersonWriteV1].PersonId = @UserId) 
		AND ([Sales].[Customers].[CustomerID] = @CustomerId )
		AND (IsOnCreditHold <> @IsOnCreditHold))
	
END

[HttpPut("{customerId}/CreditStatus", Name ="CreditHold")]
[Authorize(Roles = "Aministrator,SalesAdministrator,SalesPerson")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status409Conflict)]
public async Task<IActionResult> CustomerCeditHold(int customerId, [FromBody] Models.CustomerCreditHoldUpdateV1 request )
{
    request.UserId = HttpContext.PersonId();
    request.CustomerId = customerId;

    using (SqlConnection db = new SqlConnection(connectionString))
    {
        if (await db.ExecuteWithRetryAsync("[Sales].[CustomerCreditHoldStatusUpdateV1]", param: request, commandType: CommandType.StoredProcedure) != 1)
        {
            logger.LogWarning("Person {UserId} Customer {CustomerId} IsOnCreditHold {IsOnCreditHold} update failed", request.UserId, request.CustomerId, request.IsOnCreditHold);

            return this.Conflict();
        }
    }

    return this.Ok();
}

CREATE TABLE [Application].[People](
    [PersonID] [int] NOT NULL,
    [FullName] nvarchar NOT NULL,
    [PreferredName] nvarchar NOT NULL,
    [SearchName] AS (concat([PreferredName],N' ',[FullName])) PERSISTED NOT NULL,
    [IsPermittedToLogon] [bit] NOT NULL,
    [LogonName] nvarchar NULL,
    [IsExternalLogonProvider] [bit] NOT NULL,
    [HashedPassword] varbinary NULL,
    [IsSystemUser] [bit] NOT NULL,
    [IsEmployee] [bit] NOT NULL,
    [IsSalesperson] [bit] NOT NULL,
    [UserPreferences] nvarchar NULL,
    [PhoneNumber] nvarchar NULL,
    [FaxNumber] nvarchar NULL,
    [EmailAddress] nvarchar NULL,
    [Photo] varbinary NULL,
    [CustomFields] nvarchar NULL,
    [OtherLanguages] AS (json_query([CustomFields],N'$.OtherLanguages')),
    [LastEditedBy] [int] NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Application_People] PRIMARY KEY CLUSTERED
(
    [PersonID] ASC
)

CREATE PROCEDURE [Website].[ChangePassword]
@PersonID int,
@OldPassword nvarchar(40),
@NewPassword nvarchar(40)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    UPDATE [Application].People
    SET IsPermittedToLogon = 1,
        HashedPassword = HASHBYTES(N'SHA2_256', @NewPassword + FullName)
    WHERE PersonID = @PersonID
    AND PersonID <> 1
    AND HashedPassword = HASHBYTES(N'SHA2_256', @OldPassword + FullName);

    IF @@ROWCOUNT = 0
    BEGIN
        PRINT N'The PersonID must be valid, and the old password must be valid.';
        PRINT N'If the user has also changed name, please contact the IT staff to assist.';
        THROW 51000, N'Invalid Password Change', 1;
        RETURN -1;
    END;
END;

ALTER PROCEDURE [Website].[PersonPasswordChangeV1]
	@UserID int,
	@PasswordOld nvarchar(40),
	@PasswordNew nvarchar(40)
WITH EXECUTE AS OWNER
AS
BEGIN
    UPDATE [Application].People
    SET IsPermittedToLogon = 1
        ,HashedPassword = HASHBYTES(N'SHA2_256', @PasswordNew + FullName)
		,LastEditedBy = @UserID
    WHERE ((PersonID = @UserID )
		AND (HashedPassword = HASHBYTES(N'SHA2_256', @PasswordOld + FullName)))
END;

/// <summary>
/// Changes current user's password.
/// </summary>
/// <param name="request">Current password and new password</param>
/// <response code="200">Password changed.</response>
/// <response code="401">Unauthorised, bearer token missing or expired.</response>
/// <response code="409">Previous password invalid or User name has changed.</response>
[Authorize()]
[HttpPut(Name = "PasswordChange")]
public async Task<ActionResult> PasswordChange([FromBody] Models.PersonPasswordChangeRequest request)
{
    request.UserID = HttpContext.PersonId();

    using (SqlConnection db = new SqlConnection(connectionString))
    {
        if (await db.ExecuteWithRetryAsync("[WebSite].[PersonPasswordChangeV1]", param: request, commandType: CommandType.StoredProcedure) != 1)
        {
            logger.LogWarning("Person {0} password change failed", request.UserID);

            return this.Conflict();
        }
    }

    return this.Ok();
}

CREATE PROCEDURE [Website].[PersonPasswordResetV1]
@UserID int,
@PersonID int,
@Password nvarchar(40)
WITH EXECUTE AS OWNER
AS
BEGIN
    UPDATE [Application].People
    SET IsPermittedToLogon = 1
        ,HashedPassword = HASHBYTES(N'SHA2_256', @Password + FullName)
		,LastEditedBy = @UserID
    WHERE PersonID = @PersonID
END;

[Authorize(Roles = "Administrator")]
[HttpPut("{personId:int}", Name = "PasswordReset")]
public async Task<ActionResult> PasswordReset([Range(1, int.MaxValue, ErrorMessage = "Person id must greater than 1")] int personId, [FromBody] Models.PersonPasswordResetRequest request)
{
    request.UserId = HttpContext.PersonId();
    request.PersonID = personId;

    using (SqlConnection db = new SqlConnection(connectionString))
    {
        if (await db.ExecuteWithRetryAsync("[WebSite].[PersonPasswordResetV1]", param: request, commandType: CommandType.StoredProcedure) != 1)
        {
            logger.LogWarning("Person {0} password change failed", request.PersonID);

            return this.Conflict();
        }
    }

    return this.Ok();
}

CREATE TABLE [Application].[Permissions](
	[PermissionID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[LastEditedBy] [int] NOT NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	[ValidUntil] [datetime2](7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
 CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED 
(
	[PermissionID] ASC
)...

CREATE TABLE [Application].[PersonPermissions](
	[PersonPermissionId] [int] IDENTITY(1,1) NOT NULL,
	[PersonId] [int] NOT NULL,
	[PermIssionId] [int] NOT NULL,
	[Active] [bit] NOT NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	[ValidUntil] [datetime2](7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
 CONSTRAINT [PK_PersonPermissions] PRIMARY KEY CLUSTERED 
(
	[PersonPermissionId] ASC
)...

ALTER PROCEDURE [Website].[PersonAuthenticateLookupByLogonNameV2]
@LogonName nvarchar(50),
@Password nvarchar(40)
AS
BEGIN
	SELECT PersonID
		,FullName
		,EmailAddress
		,IsSystemUser
		,IsEmployee
		,IsSalesPerson
	FROM [Application].[People]
	WHERE (( LogonName = @LogonName)
		AND (IsPermittedToLogon = 1)
		AND (HASHBYTES(N'SHA2_256', @Password + FullName) = HashedPassword))

private class PersonAuthenticateLogonDetailsDto
{
    public int PersonID { get; set; }    

    public string FullName { get; set; }

    public string EmailAddress { get; set; }

    public bool IsSystemUser { get; set; }

    public bool IsEmployee { get; set; }

    public bool IsSalesPerson { get; set; }
}

ALTER PROCEDURE [Website].[PersonPermissionsByPersonIdV1]
	@PersonId AS int
AS
BEGIN

	SELECT [Application].[Permissions].[Name]
	FROM [Application].[Permissions]
		INNER JOIN [Application].[PersonPermissions] ON ([Application].[Permissions].PermissionID = [Application].[PersonPermissions].[PermissionId] )
	WHERE [Application].[PersonPermissions].[PersonId] = @PersonId
	ORDER BY [Application].[Permissions].[Name]

END

[HttpPost("logon")]
public async Task<ActionResult> Logon([FromBody] Models.LogonRequest request )
{
    PersonAuthenticateLogonDetailsDto userLogonUserDetails;
    IEnumerable<string> permissions;
    var claims = new List<Claim>();

    using (SqlConnection db = new SqlConnection(configuration.GetConnectionString("WorldWideImportersDatabase")))
    {
        userLogonUserDetails = await db.QuerySingleOrDefaultWithRetryAsync<PersonAuthenticateLogonDetailsDto>("[Website].[PersonAuthenticateLookupByLogonNameV2]", param: request, commandType: CommandType.StoredProcedure);
        if (userLogonUserDetails == null)
        {
            logger.LogWarning("Login attempt by user {0} failed", request.LogonName);

           return this.Unauthorized();
        }

        // Lookup the Person's permissions
        permissions = await db.QueryWithRetryAsync<string>("[Website].[PersonPermissionsByPersonIdV1]", new { userLogonUserDetails.PersonID }, commandType: CommandType.StoredProcedure);
    }

    // Setup the primary SID + name info
    claims.Add(new Claim(ClaimTypes.PrimarySid, userLogonUserDetails.PersonID.ToString()));
    if (userLogonUserDetails.IsSystemUser)
    {
       claims.Add(new Claim(ClaimTypes.Role, "SystemUser"));
    }
    if (userLogonUserDetails.IsEmployee)
    {
       claims.Add(new Claim(ClaimTypes.Role, "Employee"));
    }
    if (userLogonUserDetails.IsSalesPerson)
    {
        claims.Add(new Claim(ClaimTypes.Role, "SalesPerson"));
    }

    foreach(string permission in permissions)
    {
        claims.Add(new Claim(ClaimTypes.Role, permission));
    }

    var authSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(jwtIssuerOptions.SecretKey));

    var token = new JwtSecurityToken(
         issuer: jwtIssuerOptions.Issuer,
          audience: jwtIssuerOptions.Audience, 
          expires: DateTime.UtcNow.Add(jwtIssuerOptions.TokenExpiresAfter),
          claims: claims,
          signingCredentials: new SigningCredentials(authSigningKey, SecurityAlgorithms.HmacSha256));
                
    return this.Ok(new
   {
        token = new JwtSecurityTokenHandler().WriteToken(token),
        expiration = token.ValidTo,
    });
}

/// <summary>
/// Gets a list of the current User's roles.
/// </summary>
/// <response code="200">List of claims returned.</response>
/// <response code="401">Unauthorised, bearer token missing or expired.</response>
/// <returns>list of claims.</returns>
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK, Type = typeof(List<string>))]
public List<string> Get()
{
    List<string> claimNames = new List<string>();

    foreach (var claim in this.User.Claims.Where(c => c.Type == ClaimTypes.Role))
    {
        claimNames.Add(claim.Value);
    }

    return claimNames;
}