using FMDAL.DTO.Asset;
using FMDAL.Query.Asset;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using static Google.Rpc.Context.AttributeContext.Types;

namespace FMDAL.CustomCode.Asset
{
    public class AssetDAL : IAssetDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public AssetDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        public async Task<string> GetAsset(int AssetId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = AssetQB.GetAsset;
                var parameters = new { assetid = AssetId };
                var AssetDict = new Dictionary<int, AssetDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<AssetDTO, AssetParameterSetDetailDTO, AssetDTO>(
                   LoginDTO,
                   sql,
                   (parent, child) =>
                   {
                       if (!AssetDict.TryGetValue(parent.AssetId, out var existingParent))
                       {
                           existingParent = parent;
                           existingParent.AssetParameterSetDetailArray = new List<AssetParameterSetDetailDTO>();
                           AssetDict[parent.AssetId] = existingParent;
                       }

                       if (child != null && child.AssetParameterSetDetailId != 0)
                       {
                           existingParent.AssetParameterSetDetailArray.Add(child);
                       }

                       return existingParent;
                   },
                    parameters,
                    splitOn: "AssetParameterSetDetailId"
                );

                var finalResult = AssetDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve ASSET data: {ex.Message}", ex);
            }
        }
        // Maps FE criteria FieldNames onto the SELECT aliases GET_SELECTLIST_ASSET actually
        // exposes (Id/Code/Name), since CriteriaBuilder's derived-table wrapping requires the
        // FieldName to resolve against an outer SELECT alias, not a raw MASSET column. Keeps
        // the picklist SELECT free of duplicate columns for the same underlying value.
        private static readonly Dictionary<string, string> _selectListFieldAliasRemap =
            new(StringComparer.OrdinalIgnoreCase)
            {
                ["AssetId"] = "Id",
                ["AssetCode"] = "Code",
                ["AssetName"] = "Name",
            };

        public async Task<string> GetSelectListAsset(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section?.AttributesCriteriaList == null) continue;
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (!string.IsNullOrWhiteSpace(attr.FieldName)
                                && _selectListFieldAliasRemap.TryGetValue(attr.FieldName, out var remapped))
                            {
                                attr.FieldName = remapped;
                            }
                        }
                    }
                }

                return await _QueryExecutor.QueryWithCriteriaJsonAsync<AssetPicklistDTO>(
                    LoginDTO, AssetQB.GET_SELECTLIST_ASSET, null!, CriteriaDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveAsset(AssetDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            dto.AssetCreatedOn = DateTime.UtcNow;
            dto.AssetCreatedById = login.UserId;
            dto.AssetModifiedOn = DateTime.UtcNow;
            dto.AssetModifiedById = login.UserId;
            await _QueryExecutor.ExecuteAsync(login, AssetQB.SAVE_ASSET, dto, tx, cancellationToken: ct).ConfigureAwait(false);
            return dto.AssetId;
        }

        public async Task UpdateAsset(AssetDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            dto.AssetModifiedOn = DateTime.UtcNow;
            dto.AssetModifiedById = login.UserId;
            await _QueryExecutor.ExecuteAsync(login, AssetQB.UPDATE_ASSET, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateAssetSnapshot(AssetSnapshotDTO snapshot, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(login, AssetQB.UPDATE_ASSET_SNAPSHOT, snapshot, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task DeleteAsset(int assetId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(login, AssetQB.DELETE_ASSET,
                new { AssetId = assetId, ModifiedById = login.UserId, ModifiedOn = DateTime.UtcNow }, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<int> CheckDuplicateAssetBatch(int assetTypeId, string assetBatch, int excludeAssetId, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor.ExecuteScalarAsync<int>(login, AssetQB.CHECK_DUPLICATE_ASSET_BATCH,
                new { AssetTypeId = assetTypeId, AssetBatch = assetBatch, ExcludeAssetId = excludeAssetId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<IEnumerable<AssetParameterSetDetailDTO>> GetAssetParameterSetDetailListByAsset(int assetId, LoginDTO login, CancellationToken ct)
        {
            return await _QueryExecutor.QueryAsync<AssetParameterSetDetailDTO>(login, AssetQB.GET_ASSETPARAMETERSETDETAIL_LIST_BY_ASSET,
                new { AssetId = assetId }, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task SaveAssetParameterSetDetail(AssetParameterSetDetailDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(login, AssetQB.SAVE_ASSETPARAMETERSETDETAIL, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateAssetParameterSetDetail(AssetParameterSetDetailDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(login, AssetQB.UPDATE_ASSETPARAMETERSETDETAIL, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task DeleteAssetParameterSetDetail(int assetParameterSetDetailId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(login, AssetQB.DELETE_ASSETPARAMETERSETDETAIL,
                new { AssetParameterSetDetailId = assetParameterSetDetailId }, tx, cancellationToken: ct).ConfigureAwait(false);
        }
    }
}