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

namespace EntitlementDAL.Implementations;

public class LicenseDAL : ILicenseDAL
{
    private readonly IQueryExecutor _QueryExecutor;
    public LicenseDAL(IQueryExecutor queryExecutor) => _QueryExecutor = queryExecutor;

    public async Task<LicenseDTO?> GetByIdAsync(int licenseId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<LicenseDTO>(
            login, LicenseQB.GET_BY_ID, new { LicenseId = licenseId }, cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<LicenseDTO>> GetBySubscriptionAsync(int subscriptionId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<LicenseDTO>(
            login, LicenseQB.GET_BY_SUBSCRIPTION, new { SubscriptionId = subscriptionId }, cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<LicenseDTO>();

    public async Task<LicenseDTO?> GetBySubscriptionAndTypeAsync(int subscriptionId, byte licenseType, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<LicenseDTO>(
            login, LicenseQB.GET_BY_SUBSCRIPTION_AND_TYPE,
            new { SubscriptionId = subscriptionId, LicenseType = licenseType }, cancellationToken: ct).ConfigureAwait(false);

    public async Task SaveAsync(LicenseDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(login, LicenseQB.INSERT, dto, tx, cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateSeatsAsync(int licenseId, int seats, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(login, LicenseQB.UPDATE_SEATS,
            new { LicenseId = licenseId, Seats = seats }, tx, cancellationToken: ct).ConfigureAwait(false);

    public async Task<int> GetActiveAssignmentCountAsync(int licenseId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.ExecuteScalarAsync<int>(
            login, LicenseQB.GET_ACTIVE_ASSIGNMENT_COUNT, new { LicenseId = licenseId }, cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<LicenseAssignmentDTO>> GetAssignmentsByLicenseAsync(int licenseId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<LicenseAssignmentDTO>(
            login, LicenseQB.GET_ASSIGNMENTS_BY_LICENSE, new { LicenseId = licenseId }, cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<LicenseAssignmentDTO>();

    public async Task SaveAssignmentAsync(LicenseAssignmentDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(login, LicenseQB.INSERT_ASSIGNMENT, dto, tx, cancellationToken: ct).ConfigureAwait(false);

    public async Task RevokeAssignmentAsync(int licenseAssignmentId, DateTime revokedOn, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(login, LicenseQB.REVOKE_ASSIGNMENT,
            new { LicenseAssignmentId = licenseAssignmentId, RevokedOn = revokedOn }, tx, cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<LicenseUtilisationDTO>> GetUtilisationByClientAsync(int clientId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _QueryExecutor.QueryAsync<LicenseUtilisationDTO>(
            login, LicenseQB.GET_UTILISATION_BY_CLIENT, new { ClientId = clientId }, cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<LicenseUtilisationDTO>();

        // Remaining is computed here (not selectable via COUNT subtraction cleanly in one dialect-neutral
        // expression) — Allocated/Assigned come straight from the query; Remaining never goes negative.
        return rows.Select(r => new LicenseUtilisationDTO
        {
            LicenseType = r.LicenseType,
            Allocated   = r.Allocated,
            Assigned    = r.Assigned,
            Remaining   = Math.Max(0, r.Allocated - r.Assigned)
        });
    }
}