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.GratuityGroup;
using PayRollDAL.Query.GratuityGroup;
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 static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.GratuityGroup
{
    public class GratuityGroupDAL : IGratuityGroupDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

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

        public async Task<GratuityGroupDTO> GetGratuityGroup(int GratuityGroupId, LoginDTO LoginDTO)
        {
            string headerSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.GET_GRATUITYGROUP_SQL,
                DBType.PostGre => GratuityGroupQB.GET_GRATUITYGROUP_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string detailSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.GET_GRATUITYGROUPDETAIL_SQL,
                DBType.PostGre => GratuityGroupQB.GET_GRATUITYGROUPDETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var parameters = new DynamicParameters();
            parameters.Add("gratuitygroupid", GratuityGroupId);

            var header = await _QueryExecutor.QuerySingleAsync<GratuityGroupDTO>(
                LoginDTO, headerSql, parameters).ConfigureAwait(false);

            if (header != null)
            {
                var details = await _QueryExecutor.QueryAsync<GratuityGroupDetailDTO>(
                    LoginDTO, detailSql, parameters).ConfigureAwait(false);

                header.GratuityGroupDetailArray = details?.ToList() ?? new List<GratuityGroupDetailDTO>();
            }

            return header!;
        }

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

        public async Task<int> SaveGratuityGroup(GratuityGroupDTO GratuityGroupDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.SAVE_GRATUITYGROUP_SQL,
                DBType.PostGre => GratuityGroupQB.SAVE_GRATUITYGROUP_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string detailSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.SAVE_GRATUITYGROUPDETAIL_SQL,
                DBType.PostGre => GratuityGroupQB.SAVE_GRATUITYGROUPDETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, sql, GratuityGroupDTO, DbTransaction).ConfigureAwait(false);

            if (rows > 0 && GratuityGroupDTO.GratuityGroupDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in GratuityGroupDTO.GratuityGroupDetailArray)
                {
                    detail.GratuityGroupId = GratuityGroupDTO.GratuityGroupId;
                    detail.GratuityGroupDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, detailSql, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

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

        public async Task<int> UpdateGratuityGroup(GratuityGroupDTO GratuityGroupDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.UPDATE_GRATUITYGROUP_SQL,
                DBType.PostGre => GratuityGroupQB.UPDATE_GRATUITYGROUP_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string deleteDetailSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.DELETE_GRATUITYGROUPDETAIL_SQL,
                DBType.PostGre => GratuityGroupQB.DELETE_GRATUITYGROUPDETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string saveDetailSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.SAVE_GRATUITYGROUPDETAIL_SQL,
                DBType.PostGre => GratuityGroupQB.SAVE_GRATUITYGROUPDETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, sql, GratuityGroupDTO, DbTransaction).ConfigureAwait(false);

            var deleteParameters = new DynamicParameters();
            deleteParameters.Add("gratuitygroupid", GratuityGroupDTO.GratuityGroupId);

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, deleteDetailSql, deleteParameters, DbTransaction).ConfigureAwait(false);

            if (GratuityGroupDTO.GratuityGroupDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in GratuityGroupDTO.GratuityGroupDetailArray)
                {
                    detail.GratuityGroupId = GratuityGroupDTO.GratuityGroupId;
                    detail.GratuityGroupDetailSlNo = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, saveDetailSql, detail, DbTransaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

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

        public async Task<string> DeleteGratuityGroup(int GratuityGroupId, LoginDTO LoginDTO)
        {
            string deleteDetailSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.DELETE_GRATUITYGROUPDETAIL_SQL,
                DBType.PostGre => GratuityGroupQB.DELETE_GRATUITYGROUPDETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string deleteSql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.DELETE_GRATUITYGROUP_SQL,
                DBType.PostGre => GratuityGroupQB.DELETE_GRATUITYGROUP_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var parameters = new DynamicParameters();
            parameters.Add("gratuitygroupid", GratuityGroupId);

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, deleteDetailSql, parameters).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, deleteSql, parameters).ConfigureAwait(false);

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

        // ── Full-DTO list (header fields only), SearchText/Status filter ────

        public async Task<string> GetGratuityGroupList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => GratuityGroupQB.GET_GRATUITYGROUP_LIST_SQL,
                DBType.PostGre => GratuityGroupQB.GET_GRATUITYGROUP_LIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var searchText = ExtractLikeSearchText(CriteriaDTO);
            var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
            var parameter = new
            {
                SearchText = searchText,
                StatusEquals = statusEquals,
                StatusNotEquals = statusNotEquals
            };

            var list = (await _QueryExecutor.QueryAsync<GratuityGroupDTO>(LoginDTO, sql, parameter)
                                            .ConfigureAwait(false)).ToList();
            return JsonConvert.SerializeObject(list);
        }

        private static string? ExtractLikeSearchText(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.OperationType == CriteriaDTO.OperationType.Like && attr.FieldValue != null)
                    {
                        var text = attr.FieldValue.ToString();
                        if (!string.IsNullOrWhiteSpace(text)) return text;
                    }
                }
            }
            return null;
        }

        private static (int? EqualTo, int? NotEqualTo) ExtractStatusFilter(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return (null, null);
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null || !string.Equals(attr.FieldName, "Status", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                        return (value, null);
                    if (attr.OperationType == CriteriaDTO.OperationType.NotEqual)
                        return (null, value);
                }
            }
            return (null, null);
        }

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

        public async Task<string> GetSelectListGratuityGroup(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string codeFilter = GetCriteriaStringValue(CriteriaDTO, "Code");
                string nameFilter = GetCriteriaStringValue(CriteriaDTO, "Name");

                var parameters = new DynamicParameters();
                string filterClause = "";

                if (!string.IsNullOrWhiteSpace(codeFilter))
                {
                    filterClause += " AND A.GRATUITYGROUPCODE LIKE @code";
                    parameters.Add("code", $"%{codeFilter}%");
                }

                if (!string.IsNullOrWhiteSpace(nameFilter))
                {
                    filterClause += " AND A.GRATUITYGROUPNAME LIKE @name";
                    parameters.Add("name", $"%{nameFilter}%");
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => GratuityGroupQB.GET_SELECTLIST_GRATUITYGROUP_COUNT_SQL,
                        DBType.PostGre => GratuityGroupQB.GET_SELECTLIST_GRATUITYGROUP_COUNT_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    countSql = countSql.Replace("/*FILTER*/", filterClause);

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                        .ConfigureAwait(false);
                    return count.ToString();
                }
                else
                {
                    string sql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => GratuityGroupQB.GET_SELECTLIST_GRATUITYGROUP_SQL,
                        DBType.PostGre => GratuityGroupQB.GET_SELECTLIST_GRATUITYGROUP_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    sql = sql.Replace("/*FILTER*/", filterClause);

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

                    var result = await _QueryExecutor.QueryAsync<GratuityGroupPicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListGratuityGroup", ex);
            }
        }

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