using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using MaintenanceDAL.DTO.Claim;
using MaintenanceDAL.Query.Claim;
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;

namespace MaintenanceDAL.CustomCode.Claim
{
    public class ClaimDAL : IClaimDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ClaimDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        public async Task<string> GetClaim(int ClaimId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = ClaimQB.GET_CLAIM;
                var parameters = new { claimid = ClaimId };
                var ClaimDict = new Dictionary<int, ClaimDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<ClaimDTO, ClaimDetailDTO, ClaimDTO>(
                   LoginDTO,
                   sql,
                   (parent, child) =>
                   {
                       if (!ClaimDict.TryGetValue(parent.ClaimId, out var existingParent))
                       {
                           existingParent = parent;
                           existingParent.ClaimDetailArray = new List<ClaimDetailDTO>();
                           ClaimDict[parent.ClaimId] = existingParent;
                       }

                       if (child != null && child.ClaimDetailId != 0)
                       {
                           existingParent.ClaimDetailArray.Add(child);
                       }

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

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

        public async Task<string> GetSelectListClaim(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = ClaimQB.GET_SELECTLIST_CLAIM;

                DynamicParameters Params = new();
                string? claimNumber = GetCriteriaValue<string>(CriteriaDTO, "ClaimNumber");
                int? claimOStatus = GetCriteriaValue<int?>(CriteriaDTO, "ClaimOStatus");
                if (!string.IsNullOrWhiteSpace(claimNumber))
                {
                    SQL += @"AND (CL.CLAIMNUMBER LIKE @ClaimNumber OR EM.EMPLOYEENAME LIKE @ClaimNumber)
                             AND CL.CLAIMID <> -1
                            ";
                    Params.Add("ClaimNumber", $"%{claimNumber}%");
                }
                if (claimOStatus == 1)
                {
                    SQL += " AND CL.CLAIMSTATUS = @ClaimOStatus";
                    Params.Add("ClaimOStatus", claimOStatus);
                }

                var result = await _QueryExecutor.QueryAsync<ClaimPicklistDTO>(LoginDTO, SQL, Params);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private T? GetCriteriaValue<T>(CriteriaDTO criteria, string fieldName)
        {
            var attr = criteria?.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(a => a.FieldName.Equals(fieldName, StringComparison.OrdinalIgnoreCase));
            if (attr?.FieldValue == null)
                return default;
            Type targetType = Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T);
            return (T?)Convert.ChangeType(attr.FieldValue, targetType);
        }









        // Save Claim (Parent + Child)
        public async Task<int> SaveClaim(ClaimDTO claimDTO, LoginDTO loginDTO, DbTransaction DbTransaction, CancellationToken ct)
        {
            // Parent + child inserts share DbTransaction — a prior version of this method built the
            // transaction in the BLL but never actually passed it into these ExecuteAsync calls, so
            // a mid-save failure would leave already-committed detail rows orphaned under a rolled-back
            // (but effectively no-op) "rollback". Fixed: every statement below runs inside DbTransaction.
            int result = await _QueryExecutor.ExecuteAsync(
                loginDTO,
                ClaimQB.SAVE_CLAIM,
                claimDTO,
                DbTransaction,
                cancellationToken: ct
                ).ConfigureAwait(false);

            if (claimDTO.ClaimDetailArray != null && claimDTO.ClaimDetailArray.Any())
            {
                foreach (var detail in claimDTO.ClaimDetailArray)
                {
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO,
                        ClaimQB.SAVE_CLAIMDETAIL,
                        detail,
                        DbTransaction,
                        cancellationToken: ct
                        ).ConfigureAwait(false);
                }
            }

            return result;
        }













        // Update Claim (Parent + Delete Old Child + Insert New Child)
        public async Task<int> UpdateClaim(ClaimDTO claimDTO, LoginDTO loginDTO, DbTransaction DbTransaction, CancellationToken ct)
        {
            int result = await _QueryExecutor.ExecuteAsync(
                loginDTO,
                ClaimQB.UPDATE_CLAIM,
                claimDTO,
                DbTransaction,
                cancellationToken: ct
                ).ConfigureAwait(false);

            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                ClaimQB.DELETE_CLAIMDETAIL,
                new { ClaimId = claimDTO.ClaimId },
                DbTransaction,
                cancellationToken: ct
                ).ConfigureAwait(false);

            if (claimDTO.ClaimDetailArray != null && claimDTO.ClaimDetailArray.Any())
            {
                foreach (var detail in claimDTO.ClaimDetailArray)
                {
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO,
                        ClaimQB.SAVE_CLAIMDETAIL,
                        detail,
                        DbTransaction,
                        cancellationToken: ct
                        ).ConfigureAwait(false);
                }
            }

            return result;
        }






        public async Task<string> DeleteClaim(int claimId, LoginDTO loginDTO, DbTransaction DbTransaction, CancellationToken ct)
        {
            // Delete child records first — parent cannot be deleted first because the child
            // table has a foreign key. Both statements now share DbTransaction (previously
            // neither did, so a mid-delete failure could leave orphaned child rows).
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                ClaimQB.DELETE_CLAIMDETAIL,
                new { ClaimId = claimId },
                DbTransaction,
                cancellationToken: ct
                ).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                loginDTO,
                ClaimQB.DELETE_CLAIM,
                new { ClaimId = claimId },
                DbTransaction,
                cancellationToken: ct
                ).ConfigureAwait(false);

            return result > 0
                ? SuccessResponse.DeleteSuccessMessage
                : ErrorResponse.DeleteNotFoundMessage;
        }

        public async Task<bool> IsClaimAlreadyProcessed(int claimId, LoginDTO loginDTO, CancellationToken ct)
        {
            var count = await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, ClaimQB.GET_CLAIM_ALREADY_PROCESSED,
                new { ClaimId = claimId }, cancellationToken: ct).ConfigureAwait(false);
            return count > 0;
        }

        public async Task<PeriodWindowDTO?> GetPeriodWindow(int periodId, LoginDTO loginDTO, CancellationToken ct)
        {
            var items = await _QueryExecutor.QueryAsync<PeriodWindowDTO>(loginDTO, ClaimQB.GET_PERIOD_WINDOW,
                new { PeriodId = periodId }, cancellationToken: ct).ConfigureAwait(false);
            return items.FirstOrDefault();
        }

        public async Task<int?> GetBizTransactionTypeForClass(int bizTransactionClassId, int periodId, int ouId, LoginDTO loginDTO, CancellationToken ct)
        {
            var items = await _QueryExecutor.QueryAsync<int>(loginDTO, ClaimQB.GET_BIZTRANSACTIONTYPE_FOR_CLASS,
                new { BizTransactionClassId = bizTransactionClassId, PeriodId = periodId, OUId = ouId },
                cancellationToken: ct).ConfigureAwait(false);
            return items.Any() ? items.First() : null;
        }

        public async Task UpdateClaimVoucherPosting(int claimId, int voucherId, byte claimStatus, LoginDTO loginDTO, DbTransaction dbTransaction, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, ClaimQB.UPDATE_CLAIM_VOUCHER_POSTING,
                new { ClaimId = claimId, VoucherId = voucherId, ClaimStatus = claimStatus },
                dbTransaction, cancellationToken: ct).ConfigureAwait(false);
        }
    }
}