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

namespace IDMSDAL.Implementations;

public class DomainItemDAL : IDomainItemDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public DomainItemDAL(IQueryExecutor queryExecutor)
    {
        _QueryExecutor = queryExecutor;
    }

    // ── Reads ─────────────────────────────────────────────────────────────────

    public async Task<DeliveryItemDTO?> GetDeliveryItemAsync(
        int deliveryItemId, LoginDTO login, CancellationToken ct)
    {
        return await _QueryExecutor.QuerySingleAsync<DeliveryItemDTO>(
            login, DomainItemQB.GET_BY_ID,
            new { DeliveryItemId = deliveryItemId }).ConfigureAwait(false);
    }

    public async Task<IEnumerable<DeliveryItemDTO>> GetDeliveryItemsByDomainAsync(
        int engagementId, string domainCode, LoginDTO login, CancellationToken ct)
    {
        return await _QueryExecutor.QueryAsync<DeliveryItemDTO>(
            login, DomainItemQB.GET_BY_ENGAGEMENT_DOMAIN,
            new { EngagementId = engagementId, DomainCode = domainCode },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<DeliveryItemDTO>();
    }

    public async Task<IEnumerable<DomainProgressDTO>> GetDomainProgressAsync(
        int engagementId, LoginDTO login, CancellationToken ct)
    {
        return await _QueryExecutor.QueryAsync<DomainProgressDTO>(
            login, DomainItemQB.GET_DOMAIN_PROGRESS,
            new { EngagementId = engagementId },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<DomainProgressDTO>();
    }

    // ── Writes ────────────────────────────────────────────────────────────────

    public async Task<Result<string>> SaveDeliveryItemAsync(
        DeliveryItemDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        try
        {
            dto.DeliveryItemCreatedById  = login.UserId;
            dto.DeliveryItemCreatedOn    = DateTime.UtcNow;
            dto.DeliveryItemModifiedById = login.UserId;
            dto.DeliveryItemModifiedOn   = DateTime.UtcNow;

            int rows = await _QueryExecutor.ExecuteAsync(
                login, DomainItemQB.INSERT, dto, tx,
                cancellationToken: ct).ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Delivery item saved.")
                : Result<string>.Failure("Failed to save delivery item.");
        }
        catch (Exception)
        {
            throw;
        }
    }

    public async Task<Result<string>> UpdateDeliveryItemAsync(
        DeliveryItemDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        try
        {
            dto.DeliveryItemModifiedById = login.UserId;
            dto.DeliveryItemModifiedOn   = DateTime.UtcNow;

            int rows = await _QueryExecutor.ExecuteAsync(
                login, DomainItemQB.UPDATE, dto, tx,
                cancellationToken: ct).ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Delivery item updated.")
                : Result<string>.Failure("Item not found, already immutable, or not updated.");
        }
        catch (Exception)
        {
            throw;
        }
    }

    public async Task<Result<string>> CompleteDeliveryItemAsync(
        int deliveryItemId, int completedById, decimal? actualHours, DateTime? completedOn,
        LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        try
        {
            var param = new
            {
                DeliveryItemId           = deliveryItemId,
                CompletedOn              = completedOn ?? DateTime.UtcNow,
                CompletedById            = completedById,
                ActualHours              = actualHours,
                DeliveryItemModifiedById = login.UserId,
                DeliveryItemModifiedOn   = DateTime.UtcNow
            };

            int rows = await _QueryExecutor.ExecuteAsync(
                login, DomainItemQB.COMPLETE, param, tx,
                cancellationToken: ct).ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Delivery item completed.")
                : Result<string>.Failure("Item not found or already immutable.");
        }
        catch (Exception)
        {
            throw;
        }
    }

    public async Task<Result<string>> MarkItemImmutableAsync(
        int deliveryItemId, string signOffHash, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        try
        {
            var param = new
            {
                DeliveryItemId           = deliveryItemId,
                SignOffHash              = signOffHash,
                DeliveryItemModifiedById = login.UserId,
                DeliveryItemModifiedOn   = DateTime.UtcNow
            };

            int rows = await _QueryExecutor.ExecuteAsync(
                login, DomainItemQB.MARK_IMMUTABLE, param, tx,
                cancellationToken: ct).ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Item marked immutable.")
                : Result<string>.Failure("Item not found.");
        }
        catch (Exception)
        {
            throw;
        }
    }

    public async Task<Result<string>> BulkInsertDeliveryItemsAsync(
        IEnumerable<DeliveryItemDTO> items, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        try
        {
            var now = DateTime.UtcNow;
            var itemList = items.Select(item =>
            {
                item.DeliveryItemCreatedById  = login.UserId;
                item.DeliveryItemCreatedOn    = now;
                item.DeliveryItemModifiedById = login.UserId;
                item.DeliveryItemModifiedOn   = now;
                return item;
            }).ToList();

            await _QueryExecutor.BulkInsertAsync(
                login, DomainItemQB.INSERT, itemList, tx).ConfigureAwait(false);

            return Result<string>.Success("Items seeded.");
        }
        catch (Exception)
        {
            throw;
        }
    }
}