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;
}

CREATE TABLE [Application].[People](
	[PersonID] [int] NOT NULL,
	[FullName] [nvarchar](50) NOT NULL,
	[PreferredName] [nvarchar](50) NOT NULL,
	[SearchName]  AS (concat([PreferredName],N' ',[FullName])) PERSISTED NOT NULL,
	[IsPermittedToLogon] [bit] NOT NULL,
	[LogonName] [nvarchar](50) NULL,
	[IsExternalLogonProvider] [bit] NOT NULL,
	[HashedPassword] [varbinary](max) NULL,
	[IsSystemUser] [bit] NOT NULL,
	[IsEmployee] [bit] NOT NULL,
	[IsSalesperson] [bit] NOT NULL,
	[UserPreferences] [nvarchar](max) NULL,
	[PhoneNumber] [nvarchar](20) NULL,
	[FaxNumber] [nvarchar](20) NULL,
	[EmailAddress] [nvarchar](256) NULL,
	[Photo] [varbinary](max) NULL,
	[CustomFields] [nvarchar](max) NULL,
	[OtherLanguages]  AS (json_query([CustomFields],N'$.OtherLanguages')),
	[LastEditedBy] [int] NOT NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Application_People] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [USERDATA],
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [USERDATA] TEXTIMAGE_ON [USERDATA]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Application].[People_Archive])
)

ALTER PROCEDURE [Website].[ActivateWebsiteLogon]
@PersonID int,
@LogonName nvarchar(50),
@InitialPassword nvarchar(40)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    UPDATE [Application].People
    SET IsPermittedToLogon = 1,
        LogonName = @LogonName,
        HashedPassword = HASHBYTES(N'SHA2_256', @InitialPassword + FullName),
        UserPreferences = (SELECT UserPreferences FROM [Application].People WHERE PersonID = 1) -- Person 1 has User Preferences template
    WHERE PersonID = @PersonID
    AND PersonID <> 1
    AND IsPermittedToLogon = 0;

    IF @@ROWCOUNT = 0
    BEGIN
        PRINT N'The PersonID must be valid, must not be person 1, and must not already be enabled';
        THROW 51000, N'Invalid PersonID', 1;
        RETURN -1;
    END;
END;

CREATE PROCEDURE [Application].[PersonAuthenticateLookupByLogonNameV1]
@LogonName nvarchar(50),
@Password nvarchar(40)
AS
BEGIN
	SELECT PersonID, FullName, EmailAddress
	FROM [Application].[People]
	WHERE (( LogonName = @LogonName)
		AND (IsPermittedToLogon = 1)
		AND (HASHBYTES(N'SHA2_256', @Password + FullName) = HashedPassword))
END
GO

public class LogonRequest
{
    [JsonRequired]
    [MinLength(Constants.LogonNameLengthMinimum)]
    [MaxLength(Constants.LogonNameLengthMaximum)]
    public string LogonName { get; set; }

    [JsonRequired]
    [MinLength(Constants.PasswordLengthMinimum)]
    [MaxLength(Constants.PasswordLengthMaximum)]
    public string Password { get; set; }
}

public AuthenticationController(IConfiguration configuration, ILogger<AuthenticationController> logger, IOptions<Model.JwtIssuerOptions> jwtIssuerOptions)
{
    this.configuration = configuration;
    this.logger = logger;
    this.jwtIssuerOptions = jwtIssuerOptions.Value;
 }

[HttpPost("login")]
public async Task<ActionResult> Login([FromBody] Model.LoginRequest request )
{
    var claims = new List<Claim>();

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

             return this.Unauthorized();
        }

        // Setup the primary SID + name info
        claims.Add(new Claim(ClaimTypes.PrimarySid, userLogonUserDetails.PersonID.ToString()));
    }

    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>
/// WebAPI controller for handling System Dapper functionality.
/// </summary>
[Route("api/[controller]")]
[ApiController]
public class SystemController : ControllerBase
{
    /// <summary>
    /// Returns the Application version in [Major].[Minor].[Build].Revision] format.
    /// </summary>
    /// <response code="200">List of claims returned.</response>
    /// <response code="401">Unauthorised, bearer token missing or expired.</response>
    /// <returns>Returns the Application version in [Major].[Minor].[Build].Revision] format.</returns>
    [HttpGet("DeploymentVersion"), Authorize]
    public string DeploymentVersion()
    {
        return Assembly.GetExecutingAssembly().GetName().Version.ToString();
    }
}