using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Grpc.Core;
using MaintenanceDAL.DTO.KeyComponent;
using MaintenanceDAL.Query.KeyComponent;
using Newtonsoft.Json;

namespace MaintenanceDAL.CustomCode.KeyComponent
{
    public class KeyComponentDAL : IKeyComponentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;


        public KeyComponentDAL(IQueryExecutor QueryExecutor,IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }


        /// <summary>
        /// Retrieves a KeyComponent by its ID along with its associated details.
        /// </summary>
        /// <param name="KeyComponentId"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<string> GetKeyComponent(int KeyComponentId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = KeyComponentQB.GET_KEYCOMPONENT;
                var parameters = new { keycomponentid = KeyComponentId };

                var KeyComponentDict = new Dictionary<int, KeyComponentDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<KeyComponentDTO, KeyComponentDetailDTO, KeyComponentDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!KeyComponentDict.TryGetValue(parent.KeyComponentId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.KeyComponentDetailArray = new List<KeyComponentDetailDTO>();
                            KeyComponentDict[parent.KeyComponentId] = existingParent;
                        }

                        if (child != null && child.KeyComponentDetailId != 0)
                        {
                            existingParent.KeyComponentDetailArray.Add(child);
                        }

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

                var finalResult = KeyComponentDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve KeyComponent data: {ex.Message}", ex);
            }
        }



        /// <summary>
        /// Saves a KeyComponent along with its associated KeyComponentDetail records.
        /// </summary>
        /// <param name="KeyComponentDTO"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<int> SaveKeyComponent(KeyComponentDTO KeyComponentDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string keyComponentSql = KeyComponentQB.SAVE_KEYCOMPONENT;
                string detailSql = KeyComponentQB.SAVE_KEYCOMPONENT_DETAIL;
                string countSql = KeyComponentQB.GET_KEYCOMPONENT_COUNT;

                // DUPLICATE CHECK
                int existingCount = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql,
                    new
                    {
                        KeyComponentDTO.AssetId,
                        KeyComponentDTO.ItemId,
                        KeyComponentDTO.MachineId,
                        KeyComponentDTO.KeyComponentId
                    });

                if (existingCount > 0)
                {
                    throw new MethodNotAllowedException(
                        "Duplicate KeyComponent record exists."
                    );
                }

                // TRANSACTION BUILD
                var statements = new List<(string sql, object param)>
        {
            (keyComponentSql, KeyComponentDTO)
        };

                if (KeyComponentDTO.KeyComponentDetailArray?.Count > 0)
                {
                    foreach (var detail in KeyComponentDTO.KeyComponentDetailArray)
                    {
                        detail.KeyComponentId = KeyComponentDTO.KeyComponentId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

                return KeyComponentDTO.KeyComponentId;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }


        /// <summary>
        /// Updates a KeyComponent along with its associated KeyComponentDetail records.
        /// </summary>
        /// <param name="KeyComponentDTO"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<int> UpdateKeyComponent(KeyComponentDTO KeyComponentDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((KeyComponentQB.UPDATE_KEYCOMPONENT, KeyComponentDTO));

            foreach (var d in KeyComponentDTO.KeyComponentDetailArray)
            {
                d.KeyComponentId = KeyComponentDTO.KeyComponentId;
                if (d.KeyComponentDetailId == 0)
                    statements.Add((KeyComponentQB.SAVE_KEYCOMPONENT_DETAIL, d));
                else
                    statements.Add((KeyComponentQB.UPDATE_KEYCOMPONENT_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return KeyComponentDTO.KeyComponentId;
        }

        public async Task<int> DeleteKeyComponentDetail(int KeyComponentId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    KeyComponentQB.DELETE_KEYCOMPONENT_DETAIL,
                    new { KeyComponentid = KeyComponentId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting KeyComponent detail for ID {KeyComponentId}", ex);
            }
        }


        public async Task<int> DeleteKeyComponent(int KeyComponentId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    KeyComponentQB.DELETE_KEYCOMPONENT,
                    new { KeyComponentid = KeyComponentId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting KeyComponent with ID {KeyComponentId}", ex);
            }
        }

        /// <summary>
        /// Retrieves a list of KeyComponents based on specified criteria, including their associated details.
        /// </summary>
        /// <param name="criteriaDTO"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        public async Task<IEnumerable<KeyComponentDTO>> GetSelectListKeyComponent(CriteriaDTO criteriaDTO, LoginDTO LoginDTO)
        {
            var KeyComponentDict = new Dictionary<long, KeyComponentDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<KeyComponentDTO, KeyComponentDetailDTO, KeyComponentDTO>(
                LoginDTO,
                KeyComponentQB.GET_SELECTLIST_KEYCOMPONENT,
                (keycomponent, detail) =>
                {
                    if (!KeyComponentDict.TryGetValue(keycomponent.KeyComponentId, out var currentkeycomp))
                    {
                        currentkeycomp = keycomponent;
                        currentkeycomp.KeyComponentDetailArray = new List<KeyComponentDetailDTO>();
                        KeyComponentDict.Add(currentkeycomp.KeyComponentId, currentkeycomp);
                    }

                    if (detail != null &&
                        !currentkeycomp.KeyComponentDetailArray.Any(k => k.KeyComponentId == detail.KeyComponentId))
                    {
                        currentkeycomp.KeyComponentDetailArray.Add(detail);
                    }

                    return currentkeycomp;
                },
                null,
                splitOn: "KeyComponentDetailId"
            );

            return KeyComponentDict.Values.ToList();
        }

    }
}