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.CasteCategory;
using PayRollDAL.Query.CasteCategory;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.CasteCategory
{
    public class CasteCategoryDAL : ICasteCategoryDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

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

        public async Task<CasteCategoryDTO> GetCasteCategory(int CasteCategoryId, LoginDTO LoginDTO)
        {
            var param = new { castecategoryid = CasteCategoryId };

            var header = await _QueryExecutor.QuerySingleAsync<CasteCategoryDTO>(
                LoginDTO, CasteCategoryQB.GET_CASTECATEGORY, param).ConfigureAwait(false);

            if (header != null)
            {
                var details = await _QueryExecutor.QueryAsync<CasteCategoryDetailDTO>(
                    LoginDTO, CasteCategoryQB.GET_CASTECATEGORYDETAIL, param).ConfigureAwait(false);

                header.CasteCategoryDetailArray = details?.ToList() ?? new List<CasteCategoryDetailDTO>();
            }

            return header!;
        }

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

        public async Task<int> SaveCasteCategory(CasteCategoryDTO CasteCategoryDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, CasteCategoryQB.SAVE_CASTECATEGORY, CasteCategoryDTO, DbTransaction).ConfigureAwait(false);

            if (rows > 0 && CasteCategoryDTO.CasteCategoryDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in CasteCategoryDTO.CasteCategoryDetailArray)
                {
                    detail.CasteCategoryId = CasteCategoryDTO.CasteCategoryId;
                    detail.CasteCategoryDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, CasteCategoryQB.SAVE_CASTECATEGORYDETAIL, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

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

        public async Task<int> UpdateCasteCategory(CasteCategoryDTO CasteCategoryDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, CasteCategoryQB.UPDATE_CASTECATEGORY, CasteCategoryDTO, DbTransaction).ConfigureAwait(false);

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, CasteCategoryQB.DELETE_CASTECATEGORYDETAIL,
                new { castecategoryid = CasteCategoryDTO.CasteCategoryId }, DbTransaction).ConfigureAwait(false);

            if (CasteCategoryDTO.CasteCategoryDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in CasteCategoryDTO.CasteCategoryDetailArray)
                {
                    detail.CasteCategoryId = CasteCategoryDTO.CasteCategoryId;
                    detail.CasteCategoryDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, CasteCategoryQB.SAVE_CASTECATEGORYDETAIL, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

        // ── Delete header (details removed explicitly) ─

        public async Task<string> DeleteCasteCategory(int CasteCategoryId, LoginDTO LoginDTO)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, CasteCategoryQB.DELETE_CASTECATEGORYDETAIL,
                new { castecategoryid = CasteCategoryId }).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, CasteCategoryQB.DELETE_CASTECATEGORY,
                new { castecategoryid = CasteCategoryId }).ConfigureAwait(false);

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

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

        public async Task<string> GetSelectListCasteCategory(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = CasteCategoryQB.GET_SELECTLIST_CASTECATEGORY;
            var parameters = new DynamicParameters();

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

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

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

        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; }
        }
    }
}