using System.Linq;
using EntitlementDAL.DTOs;
using EntitlementDAL.Interfaces;
using EntitlementDAL.QueryBuilders;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace EntitlementDAL.Implementations;

public class ClientProvisioningDAL : IClientProvisioningDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public ClientProvisioningDAL(IQueryExecutor queryExecutor) => _QueryExecutor = queryExecutor;

    public async Task<bool> ClientCodeExistsAsync(string clientCode, LoginDTO login, CancellationToken ct)
    {
        var count = await _QueryExecutor.ExecuteScalarAsync<int>(
            login, ClientProvisioningQB.CHECK_CLIENT_CODE_EXISTS,
            new { ClientCode = clientCode }, cancellationToken: ct).ConfigureAwait(false);
        return count > 0;
    }

    public async Task InsertClientAsync(NewClientDTO dto, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(login, ClientProvisioningQB.INSERT_MCLIENT, dto, cancellationToken: ct).ConfigureAwait(false);

    public async Task<int?> GetRoleIdByCodeAsync(string roleCode, LoginDTO login, CancellationToken ct)
    {
        var rows = await _QueryExecutor.QueryAsync<int>(
            login, ClientProvisioningQB.GET_ROLEID_BY_CODE,
            new { RoleCode = roleCode }, cancellationToken: ct).ConfigureAwait(false);
        return rows.Cast<int?>().FirstOrDefault();
    }

    public async Task<byte?> GetClientDeploymentTypeAsync(int clientId, LoginDTO login, CancellationToken ct)
    {
        // MCLIENT.DEPLOYMENTTYPE is a genuinely nullable column (e.g. the -1 "NONE" sentinel row,
        // and any client predating this column) — querying it as non-nullable <byte> throws
        // Dapper's "Null object cannot be converted to a value type" the moment a real row with a
        // NULL value is hit (live-verified 2026-09-08 against ClientId=-1). Query <byte?> directly
        // instead of casting after the fact, which only worked for a missing row, not a NULL column.
        var rows = await _QueryExecutor.QueryAsync<byte?>(
            login, ClientProvisioningQB.GET_CLIENT_DEPLOYMENT_TYPE,
            new { ClientId = clientId }, cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault();
    }

    public async Task<string?> GetClientJurisdictionCodeAsync(int clientId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _QueryExecutor.QueryAsync<string?>(
            login, ClientProvisioningQB.GET_CLIENT_JURISDICTION_CODE,
            new { ClientId = clientId }, cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault();
    }
}