using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using PayRollDAL.DTO.VisitorPass;
using PayRollDAL.Query.VisitorPass;

namespace PayRollDAL.CustomeCode.VisitorPass
{
    public class VisitorPassDAL : IVisitorPassDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public VisitorPassDAL(IQueryExecutor IQueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetVisitorPass(int VisitorPassId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = VisitorPassQB.GET_VISITORPASS;
                var parameters = new { visitorpassid = VisitorPassId };

                var VisitorPassDict = new Dictionary<int, VisitorPassDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<VisitorPassDTO, VisitorPunchDTO, VisitorPassDetailDTO, VisitorPassDTO>(
                    LoginDTO,
                    sql,
                    (parent, child, secchild) =>
                    {
                        if (!VisitorPassDict.TryGetValue(parent.VisitorPassId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.VisitorPunchArray = new List<VisitorPunchDTO>();
                            existingParent.VisitorPassDetailArray = new List<VisitorPassDetailDTO>();
                            VisitorPassDict[parent.VisitorPassId] = existingParent;
                        }

                        if (child != null &&
                            !existingParent.VisitorPunchArray.Any(x => x.VisitorPunchId == child.VisitorPunchId))
                        {
                            existingParent.VisitorPunchArray.Add(child);
                        }

                        if (secchild != null &&
                            !existingParent.VisitorPassDetailArray.Any(x => x.VisitorPassDetailId == secchild.VisitorPassDetailId))
                        {
                            existingParent.VisitorPassDetailArray.Add(secchild);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "VisitorPunchId,VisitorPassDetailId"
                );

                var finalResult = VisitorPassDict.Values.FirstOrDefault();
                if (finalResult?.VisitorPassDetailArray != null)
                {
                    finalResult.VisitorPassDetailArray = finalResult.VisitorPassDetailArray
                        .OrderBy(x => x.VisitorPassDetailSlNo)
                        .ToList();
                }
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve VisitorPass data: {ex.Message}", ex);
            }
        }

        public async Task<string> GetSelectListVisitorPass(int FirstNumber, int MaxResult, int ShowCompleted, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                string searchText = "";
                if (IsCount == false)
                {
                    if (CriteriaDTO != null && CriteriaDTO.SectionCriteriaList?.Count > 0)
                    {
                        var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                        if (attributes != null)
                        {
                            foreach (var item in attributes)
                            {
                                if (item.FieldName.ToLower() == "searchtext")
                                {
                                    searchText = ((JsonElement)item.FieldValue).GetString() ?? "";
                                }
                            }
                        }
                    }
                    string SQL = VisitorPassQB.GET_SELECTLIST_VISITORPASS;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult, showcompleted = ShowCompleted, searchtext = searchText };
                    List<VisitorPassPicklistDTO> VisitorPassPicklistDTO = (await _QueryExecutor.QueryAsync<VisitorPassPicklistDTO>(LoginDTO, SQL, parameter)).ToList();

                    Json = JsonConvert.SerializeObject(VisitorPassPicklistDTO);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveVisitorPass(VisitorPassDTO VisitorPassDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>
                {
                    (VisitorPassQB.SAVE_VISITORPASS, VisitorPassDTO)
                };

                if (VisitorPassDTO.VisitorPassDetailArray != null && VisitorPassDTO.VisitorPassDetailArray.Count > 0)
                {
                    foreach (var detail in VisitorPassDTO.VisitorPassDetailArray)
                    {
                        detail.VisitorPassId = VisitorPassDTO.VisitorPassId;
                        statements.Add((
                            VisitorPassQB.SAVE_VISITORPASSDETAIL,
                            detail
                        ));
                    }
                }

                if (VisitorPassDTO.VisitorPunchArray != null && VisitorPassDTO.VisitorPunchArray.Count > 0)
                {
                    foreach (var punch in VisitorPassDTO.VisitorPunchArray)
                    {
                        punch.VisitorPassId = VisitorPassDTO.VisitorPassId;
                        statements.Add((
                            VisitorPassQB.SAVE_VISITORPUNCH,
                            punch
                        ));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
                return VisitorPassDTO.VisitorPassId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while saving VisitorPass : {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateVisitorPass(VisitorPassDTO VisitorPassDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>
                {
                    (VisitorPassQB.UPDATE_VISITORPASS, VisitorPassDTO)
                };

                if (VisitorPassDTO.VisitorPassDetailArray != null)
                {
                    foreach (var detail in VisitorPassDTO.VisitorPassDetailArray)
                    {
                        if (detail.VisitorPassDetailId == 0)
                        {
                            statements.Add((
                                VisitorPassQB.SAVE_VISITORPASSDETAIL,
                                detail
                            ));
                        }
                        else
                        {
                            statements.Add((
                                VisitorPassQB.UPDATE_VISITORPASSDETAIL,
                                detail
                            ));
                        }
                    }
                }

                if (VisitorPassDTO.VisitorPunchArray != null)
                {
                    foreach (var punch in VisitorPassDTO.VisitorPunchArray)
                    {
                        if (punch.VisitorPunchId == 0)
                        {
                            statements.Add((
                                VisitorPassQB.SAVE_VISITORPUNCH,
                                punch
                            ));
                        }
                        else
                        {
                            statements.Add((
                                VisitorPassQB.UPDATE_VISITORPUNCH,
                                punch
                            ));
                        }
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
                return VisitorPassDTO.VisitorPassId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while updating VisitorPass : {ex.Message}", ex);
            }
        }

        public async Task<string> DeleteVisitorPass(int VisitorPassId, LoginDTO LoginDTO)
        {
            try
            {
                string punchsql = VisitorPassQB.DELETE_VISITORPASS_PUNCH;
                string visitordetailsql = VisitorPassQB.DELETE_VISITORPASS_DETAIL;
                string visitorSql = VisitorPassQB.DELETE_VISITORPASS;
                var parameter = new { visitorpassid = VisitorPassId };
                var punchresult = await _QueryExecutor.ExecuteAsync(LoginDTO, punchsql, parameter);
                var visitordetailresult = await _QueryExecutor.ExecuteAsync(LoginDTO, visitordetailsql, parameter);
                var visitorresult = await _QueryExecutor.ExecuteAsync(LoginDTO, visitorSql, parameter);

                if (visitorresult > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> GetMinVisitorPassDetailId(LoginDTO LoginDTO)
        {
            return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, VisitorPassQB.GET_MIN_VISITORPASSDETAILID, null);
        }

        public async Task<int> GetMinVisitorPunchId(LoginDTO LoginDTO)
        {
            return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, VisitorPassQB.GET_MIN_VISITORPUNCHID, null);
        }
    }
}