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

namespace PayRollDAL.CustomeCode.Relieving
{
    public class RelievingDAL : IRelievingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public RelievingDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ── Get header + all detail rows ─────────────────────────────────

        public async Task<RelievingDTO> GetRelieving(int RelievingId, LoginDTO LoginDTO)
        {
            var param = new { relievingid = RelievingId };

            var header = await _QueryExecutor.QuerySingleAsync<RelievingDTO>(
                LoginDTO, RelievingQB.GET_RELIEVING, param).ConfigureAwait(false);

            if (header != null)
            {
                var details = await _QueryExecutor.QueryAsync<RelievingDetailDTO>(
                    LoginDTO, RelievingQB.GET_RELIEVINGDETAIL, param).ConfigureAwait(false);

                header.RelievingDetailArray = details?.ToList() ?? new List<RelievingDetailDTO>();
            }

            return header!;
        }

        // ── Insert header + detail rows (transaction provided by BLL) ────

        public async Task<int> SaveRelieving(RelievingDTO RelievingDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.SAVE_RELIEVING, RelievingDTO, DbTransaction).ConfigureAwait(false);

            if (rows > 0 && RelievingDTO.RelievingDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in RelievingDTO.RelievingDetailArray)
                {
                    detail.RelievingId = RelievingDTO.RelievingId;
                    detail.RelievingDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, RelievingQB.SAVE_RELIEVINGDETAIL, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

        // ── Update header + replace detail rows (transaction provided by BLL) ─

        public async Task<int> UpdateRelieving(RelievingDTO RelievingDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.UPDATE_RELIEVING, RelievingDTO, DbTransaction).ConfigureAwait(false);

            // GB4 parity: DeleteRelievingDetails then re-insert (RelievingBLL.SaveRelieving update path)
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.DELETE_RELIEVINGDETAIL,
                new { relievingid = RelievingDTO.RelievingId }, DbTransaction).ConfigureAwait(false);

            if (RelievingDTO.RelievingDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in RelievingDTO.RelievingDetailArray)
                {
                    detail.RelievingId = RelievingDTO.RelievingId;
                    detail.RelievingDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, RelievingQB.SAVE_RELIEVINGDETAIL, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

        // ── Delete header (details removed via FK-driven cleanup below) ──

        public async Task<string> DeleteRelieving(int RelievingId, LoginDTO LoginDTO)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.DELETE_RELIEVINGDETAIL,
                new { relievingid = RelievingId }).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.DELETE_RELIEVING,
                new { relievingid = RelievingId }).ConfigureAwait(false);

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

        // ── Picklist for grid ─────────────────────────────────────────────

        public async Task<string> GetSelectListRelieving(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = RelievingQB.GET_SELECTLIST_RELIEVING;
            var parameters = new DynamicParameters();

            int bizTransactionTypeId = GetCriteriaValue(CriteriaDTO, "BizTransactionTypeId", -1);
            if (bizTransactionTypeId != -1)
            {
                sql += "\r\n AND A.BIZTRANSACTIONTYPEID = @biztransactiontypeid";
                parameters.Add("biztransactiontypeid", bizTransactionTypeId);
            }

            int ouId = GetCriteriaValue(CriteriaDTO, "OUId", -1);
            if (ouId != -1)
            {
                sql += "\r\n AND A.OUID = @ouid";
                parameters.Add("ouid", ouId);
            }

            string searchText = GetCriteriaStringValue(CriteriaDTO, "RelievingNumber")
                ?? GetCriteriaStringValue(CriteriaDTO, "SearchText");
            if (!string.IsNullOrWhiteSpace(searchText))
            {
                sql += "\r\n AND (A.RELIEVINGNUMBER LIKE @searchtext OR B.EMPLOYEECODE LIKE @searchtext OR B.EMPLOYEENAME LIKE @searchtext)";
                parameters.Add("searchtext", $"%{searchText}%");
            }

            sql += "\r\n ORDER BY A.MODIFIEDON DESC";

            var result = await _QueryExecutor.QueryAsync<RelievingPicklistDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── Relieving Settlement (separate settlement grid, save-only endpoint) ──

        public async Task SaveRelievingSettlementDetail(RelievingSettlementDTO RelievingSettlementDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.SAVE_RELIEVINGSETTLEMENT, RelievingSettlementDTO, DbTransaction).ConfigureAwait(false);
        }

        public async Task UpdateRelievingSettlementDetail(RelievingSettlementDTO RelievingSettlementDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.UPDATE_RELIEVINGSETTLEMENT, RelievingSettlementDTO, DbTransaction).ConfigureAwait(false);
        }

        public async Task DeleteRelievingSettlementsByRelievingId(int RelievingId, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.DELETE_RELIEVINGSETTLEMENT_BY_RELIEVINGID,
                new { relievingid = RelievingId }, DbTransaction).ConfigureAwait(false);
        }

        public async Task UpdateRelievingSettlementAmount(int RelievingId, decimal SettlementAmount, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RelievingQB.UPDATE_RELIEVING_SETTLEMENTAMOUNT,
                new { relievingid = RelievingId, settlementamount = SettlementAmount }, DbTransaction).ConfigureAwait(false);
        }

        private int GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName, int defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.Number ? je.GetInt32()
                         : int.TryParse(je.GetString(), out int p) ? p : defaultValue;

                return attr != null ? Convert.ToInt32(attr.FieldValue) : defaultValue;
            }
            catch { return defaultValue; }
        }

        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString();

                return attr?.FieldValue?.ToString();
            }
            catch { return null; }
        }
    }
}