using System.Data;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using System.Data.Common;
using Newtonsoft.Json;
using SwDAL.DTO.ObjectGroup;
using SwDAL.Query.ObjectGroup;

namespace SwDAL.CustomCode.ObjectGroup;

public class ObjectGroupDAL : IObjectGroupDAL
{
    private readonly IQueryExecutor _QueryExecutor;

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

    public async Task<ObjectGroupDTO?> GetById(int objectGroupId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<ObjectGroupDTO>(
               loginDTO, ObjectGroupQB.GET_BY_ID,
               new { ObjectGroupId = objectGroupId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<string> GetSelectListObjectGroup(int first, int max, LoginDTO loginDTO)
    {
        var result = await _QueryExecutor.QueryAsync<ObjectGroupPickListDTO>(
            loginDTO, ObjectGroupQB.GET_SELECT_LIST,
            new { firstnumber = first, maxresult = max, TenantId = loginDTO.ClientId })
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<Result<string>> SaveObjectGroup(
        ObjectGroupDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedById  = loginDTO.UserId;
        dto.CreatedOn    = DateTime.UtcNow;
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, ObjectGroupQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Object group saved.")
            : Result<string>.Failure("Failed to save object group.");
    }

    public async Task<Result<string>> UpdateObjectGroup(
        ObjectGroupDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, ObjectGroupQB.UPDATE, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Object group updated.")
            : Result<string>.Failure("Failed to update object group.");
    }

    public async Task<Result<string>> DeleteObjectGroup(
        int objectGroupId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ObjectGroupQB.DELETE,
            new { ObjectGroupId = objectGroupId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Object group deleted.")
            : Result<string>.Failure("Object group not found or already deleted.");
    }

    public async Task<IEnumerable<ObjectGroupDetailDTO>> GetDetails(
        int objectGroupId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<ObjectGroupDetailDTO>(
               loginDTO, ObjectGroupQB.GET_DETAILS,
               new { ObjectGroupId = objectGroupId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<Result<string>> InsertDetail(
        ObjectGroupDetailDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.TenantId = loginDTO.ClientId;
        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, ObjectGroupQB.INSERT_DETAIL, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Object reference inserted.")
            : Result<string>.Failure("Failed to insert object reference.");
    }

    public async Task<Result<string>> DeleteDetail(
        int objectReferenceId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ObjectGroupQB.DELETE_DETAIL_BY_ID,
            new { ObjectReferenceId = objectReferenceId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Object reference deleted.")
            : Result<string>.Failure("Object reference not found or already deleted.");
    }

    public async Task<Result<string>> DeleteAllDetails(
        int objectGroupId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _QueryExecutor.ExecuteAsync(
            loginDTO, ObjectGroupQB.DELETE_DETAILS_BY_GROUP,
            new { ObjectGroupId = objectGroupId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return Result<string>.Success("All object references deleted.");
    }
    public async Task<ObjectGroupDTO?> GetObjectGroupWithDetail(int objectGroupId,LoginDTO loginDTO,CancellationToken ct)
    {
        var objectGroupDict = new Dictionary<int, ObjectGroupDTO>();

        await _QueryExecutor.QueryMultiMapAsync<ObjectGroupDTO,ObjectGroupDetailDTO,ObjectGroupDTO>(loginDTO,
            ObjectGroupQB.GET_OBJECTGROUP_WITH_DETAIL,
            (group, detail) =>
            {
                if (!objectGroupDict.TryGetValue(group.ObjectGroupId, out var currentGroup))
                {
                    currentGroup = group;
                    currentGroup.ObjectDetails = new List<ObjectGroupDetailDTO>();

                    objectGroupDict.Add(
                        currentGroup.ObjectGroupId,
                        currentGroup);
                }

                if (detail != null &&
                    detail.ObjectReferenceId > 0 &&
                    !currentGroup.ObjectDetails.Any(x =>
                        x.ObjectReferenceId == detail.ObjectReferenceId))
                {
                    currentGroup.ObjectDetails.Add(detail);
                }

                return currentGroup;
            },
            new
            {
                ObjectGroupId = objectGroupId,
                TenantId = loginDTO.ClientId
            },
            splitOn: "ObjectReferenceId");

        return objectGroupDict.Values.FirstOrDefault();
    }
}