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.Village;
using PayRollDAL.Query.Village;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.Village
{
    public class VillageDAL : IVillageDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Picklist for grid ─────────────────────────────────────────────
        // GB4 parity: VillageBLL.GetSelectListVillage

        public async Task<string> GetSelectListVillage(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string searchText = GetCriteriaStringValue(CriteriaDTO, "SearchText");
            var parameters = new DynamicParameters();
            parameters.Add("searchtext", string.IsNullOrWhiteSpace(searchText) ? null : $"%{searchText}%");

            if (IsCount)
            {
                string countSql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => VillageQB.GET_SELECTLIST_VILLAGE_COUNT_SQL,
                    DBType.PostGre => VillageQB.GET_SELECTLIST_VILLAGE_COUNT_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                var countRows = await _QueryExecutor.QueryAsync<int>(LoginDTO, countSql, parameters).ConfigureAwait(false);
                return countRows.FirstOrDefault().ToString();
            }

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => VillageQB.GET_SELECTLIST_VILLAGE_SQL,
                DBType.PostGre => VillageQB.GET_SELECTLIST_VILLAGE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);

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

        // ── Get single record ─────────────────────────────────────────────

        public async Task<VillageDTO> GetVillage(int VillageId, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => VillageQB.GET_VILLAGE_SQL,
                DBType.PostGre => VillageQB.GET_VILLAGE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var parameters = new DynamicParameters();
            parameters.Add("villageid", VillageId);

            return await _QueryExecutor.QuerySingleAsync<VillageDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
        }

        // ── Insert ────────────────────────────────────────────────────────

        public async Task<int> SaveVillage(VillageDTO VillageDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => VillageQB.SAVE_VILLAGE_SQL,
                DBType.PostGre => VillageQB.SAVE_VILLAGE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, VillageDTO, DbTransaction).ConfigureAwait(false);
        }

        // ── Update ────────────────────────────────────────────────────────

        public async Task<int> UpdateVillage(VillageDTO VillageDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => VillageQB.UPDATE_VILLAGE_SQL,
                DBType.PostGre => VillageQB.UPDATE_VILLAGE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, VillageDTO, DbTransaction).ConfigureAwait(false);
        }

        // ── Delete ────────────────────────────────────────────────────────

        public async Task<string> DeleteVillage(int VillageId, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => VillageQB.DELETE_VILLAGE_SQL,
                DBType.PostGre => VillageQB.DELETE_VILLAGE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var parameters = new DynamicParameters();
            parameters.Add("villageid", VillageId);

            int result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters).ConfigureAwait(false);
            return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
        }

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