using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.DateConverter;
using GB5Shared.Validation;
using MMDAL.DTO.Pattern;
using MMDAL.Query.Item;
using MMDAL.Query.Pattern;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.Pattern
{
    public class PatternDAL : IPatternDAL
    {

        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public PatternDAL(IQueryExecutor QueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = IValidation;
        }

        public async Task<object> GetPattern(int PatternId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = PatternQB.GET_PATTERN;

                var parameters = new { patternId = PatternId }; // match SQL parameter

                var PatternDict = new Dictionary<int, PatternDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<PatternDTO, PatternDetailDTO, PatternDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!PatternDict.TryGetValue(parent.PatternId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PatternDetailArray = new List<PatternDetailDTO>();
                            PatternDict[parent.PatternId] = existingParent;
                        }

                        // Add child even if default/NULL values
                        if (child != null)
                        {
                            existingParent.PatternDetailArray.Add(child);
                        }

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

                var finalResult = PatternDict.Values.FirstOrDefault();

                // Always return JSON even if PatternDetailArray is empty

                var options = new JsonSerializerOptions
                {
                    PropertyNamingPolicy = null
                };
                options.AddGB5Converters();

                // Serialize to JSON string
                 
                string json = System.Text.Json.JsonSerializer.Serialize(finalResult, options);

                return json;
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Pattern data: {ex.Message}", ex);
            }
        }


        public async Task<string> GetItemPattern(int ItemId, LoginDTO loginDTO)
        {
            try
            {
                var sql = PatternQB.GET_ITEM_PATTERN;
                var parameters = new { itemid = ItemId };

                // Dictionary to prevent duplicate parent objects
                var PatternDict = new Dictionary<int, PatternDTO>();

                // Multi-mapping query: PatternDTO (parent) + PatternDetailDTO (child)
                var result = await _QueryExecutor.QueryMultiMapAsync<PatternDTO, PatternDetailDTO, PatternDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        // If parent does not exist in dictionary, add it
                        if (!PatternDict.TryGetValue(parent.PatternId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PatternDetailArray = new List<PatternDetailDTO>();
                            PatternDict[parent.PatternId] = existingParent;
                        }

                        // Add child if it exists and has a valid PatternDetailId
                        if (child != null && child.PatternDetailId > 0)
                        {
                            existingParent.PatternDetailArray.Add(child);
                        }

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

                // Ensure each parent has a non-null PatternDetailArray even if empty
                foreach (var parent in PatternDict.Values)
                {
                    if (parent.PatternDetailArray == null)
                        parent.PatternDetailArray = new List<PatternDetailDTO>();
                }

                // Return JSON of all patterns for this item
                return JsonConvert.SerializeObject(PatternDict.Values.ToList());
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Pattern data: {ex.Message}", ex);
            }
        }


        public async Task<int> SavePattern(PatternDTO PatternDTO, LoginDTO LoginDTO)
        {
            if (PatternDTO == null)
                throw new ArgumentNullException(nameof(PatternDTO));

            // The MAX(PATTERNDETAILID) read and the children's inserts must share ONE transaction —
            // ExecuteInTransactionAsync(statements) always opens its own fresh transaction, so a scalar
            // read executed before calling it (the previous implementation) has already released its
            // lock (if any) by the time the inserts run, letting two concurrent saves compute the same
            // MAX and generate colliding PatternDetailIds. See UpdatePattern for the same fix + the
            // live-caught bug this was modeled after.
            var tx = await _QueryExecutor.BeginTransactionAsync(LoginDTO);
            try
            {
                int maxDetailId = 0;
                if (PatternDTO.PatternDetailArray != null && PatternDTO.PatternDetailArray.Any())
                {
                    maxDetailId = await _QueryExecutor.ExecuteScalarAsync<int>(
                        LoginDTO,
                        "SELECT ISNULL(MAX(PATTERNDETAILID), 0) FROM MPATTERNDETAIL WITH (UPDLOCK, HOLDLOCK)",
                        null, tx);
                }

                await _QueryExecutor.ExecuteAsync(LoginDTO, PatternQB.SAVE_PATTERN_PARENT, PatternDTO, tx);

                if (PatternDTO.PatternDetailArray != null && PatternDTO.PatternDetailArray.Any())
                {
                    for (int i = 0; i < PatternDTO.PatternDetailArray.Count; i++)
                    {
                        var detail = PatternDTO.PatternDetailArray[i];
                        detail.PatternId = PatternDTO.PatternId;
                        detail.PatternDetailId = maxDetailId + i + 1;
                        if (detail.PatternDetailSlNo == 0)
                            detail.PatternDetailSlNo = (short)(i + 1);

                        await _QueryExecutor.ExecuteAsync(LoginDTO, PatternQB.SAVE_PATTERN_CHILD, detail, tx);
                    }
                }

                await _QueryExecutor.CommitAsync(tx);

                // Return total rows (parent + children)
                return 1 + (PatternDTO.PatternDetailArray?.Count ?? 0);
            }
            catch (Exception ex)
            {
                await _QueryExecutor.RollbackAsync(tx);
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<int> UpdatePattern(PatternDTO patternDTO, LoginDTO loginDTO)
        {
            if (patternDTO == null)
                throw new ArgumentNullException(nameof(patternDTO));

            // The MAX(PATTERNDETAILID) WITH (UPDLOCK, HOLDLOCK) read must share the SAME transaction as
            // the children re-inserts that rely on it — the previous implementation read it via a
            // separate, transaction-less ExecuteScalarAsync call, so the lock was released the instant
            // that one statement completed, before ExecuteInTransactionAsync(statements) even opened
            // its own (different) transaction for the actual writes. Two concurrent UpdatePattern calls
            // could therefore compute the same MaxPatternDetailId and generate colliding PatternDetailIds
            // — caught live while auditing UPDLOCK usage after enabling RCSI on GB5DEMO.
            var tx = await _QueryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                // 1. Update parent
                await _QueryExecutor.ExecuteAsync(loginDTO, PatternQB.UPDATE_PATTERN_PARENT, patternDTO, tx);

                // 2. Delete existing children
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    "DELETE FROM MPATTERNDETAIL WHERE PATTERNID = @PatternId",
                    new { patternDTO.PatternId }, tx);

                // 3. Get max PatternDetailId WITH LOCK, held until this same transaction commits
                int maxPatternDetailId = await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    "SELECT ISNULL(MAX(PATTERNDETAILID), 0) FROM MPATTERNDETAIL WITH (UPDLOCK, HOLDLOCK)",
                    null, tx);

                // 4. Re-insert children
                if (patternDTO.PatternDetailArray != null && patternDTO.PatternDetailArray.Any())
                {
                    short slNo = 1;
                    foreach (var detail in patternDTO.PatternDetailArray)
                    {
                        maxPatternDetailId++;
                        detail.PatternDetailId = maxPatternDetailId;
                        detail.PatternId = patternDTO.PatternId;
                        detail.PatternDetailSlNo = slNo++;

                        await _QueryExecutor.ExecuteAsync(loginDTO, PatternQB.SAVE_PATTERN_CHILD, detail, tx);
                    }
                }

                await _QueryExecutor.CommitAsync(tx);

                return 1 + (patternDTO.PatternDetailArray?.Count ?? 0);
            }
            catch (Exception ex)
            {
                await _QueryExecutor.RollbackAsync(tx);
                string error = await _Validation.HandleException(
                    ex,
                    ErrorResponse.UpdateErrorMessage
                );
                throw new Exception(error);
            }
        }





        public async Task<string> DeletePattern(int PatternId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = PatternQB.DELETE_PATTERN;
                var Parameters = new { Patternid = PatternId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<string> GetSelectListPattern(
    CriteriaDTO criteriaDTO,
    LoginDTO loginDTO)
        {
            try
            {
                int pattTag = -1;

                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (attr.FieldName.Equals("patttag", StringComparison.OrdinalIgnoreCase))
                            {
                                pattTag = 0;
                                break;
                            }
                        }
                        if (pattTag == 0) break;
                    }
                }

                string baseSql = pattTag == -1
                    ? PatternQB.GET_SELECT_LIST_PATTERN
                    : PatternQB.GET_PATTERN_BASED_ITEM;

                // ✅ APPLY CRITERIA
                string finalSql = ApplyGetSelectListPattern(baseSql, criteriaDTO);

                // ✅ BUILD PARAMETERS
                var parameters = BuildPatternParameters(criteriaDTO);

                var result = await _QueryExecutor.QueryAsync<PatternPicklistDTO>(
                    loginDTO,
                    finalSql,
                    parameters
                );

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }



        public async Task<string> GetPatternList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PatternQB.GET_PATTERN_LIST;
                var Result = await _QueryExecutor.QueryAsync<PatternDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }



        //    public async Task<string> GetSelectListPatternReport(
        //int type,
        //int firstNumber,
        //int maxResult,
        //CriteriaDTO criteriaDTO,
        //LoginDTO loginDTO,
        //bool count = false)
        //    {
        //        try
        //        {
        //            if (!count)
        //            {
        //                var result = await _QueryExecutor.QueryAsync<PatternListReportDTO>(
        //                    loginDTO,
        //                    PatternQB.GET_SELECT_LIST_PATTERN_REPORT,
        //                    new
        //                    {
        //                        Type = type,
        //                        Offset = firstNumber,
        //                        PageSize = maxResult,
        //                        Code = GetCriteriaValue<string>(criteriaDTO, "Code"),
        //                        Name = GetCriteriaValue<string>(criteriaDTO, "Name")
        //                    }
        //                );

        //                return JsonConvert.SerializeObject(result);
        //            }
        //            else
        //            {
        //                int total = await _QueryExecutor.ExecuteScalarAsync<int>(
        //                    loginDTO,
        //                    PatternQB.GET_SELECT_LIST_PATTERN_REPORT_COUNT,
        //                    new
        //                    {
        //                        Type = type,
        //                        Code = GetCriteriaValue<string>(criteriaDTO, "Code"),
        //                        Name = GetCriteriaValue<string>(criteriaDTO, "Name")
        //                    }
        //                );

        //                return total.ToString();
        //            }
        //        }
        //        catch
        //        {
        //            throw;
        //        }
        //    }


        public string ApplyGetSelectListPattern(string sql, CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return sql;

            var whereBuilder = new StringBuilder();

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section.AttributesCriteriaList == null)
                    continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr.FieldValue == null)
                        continue;

                    string condition = attr.FieldName.ToLower() switch
                    {
                        "code" => "P.PATTERNCODE LIKE '%' + @Code + '%'",
                        "name" => "P.PATTERNNAME LIKE '%' + @Name + '%'",
                        _ => null
                    };

                    if (condition != null)
                    {
                        whereBuilder.AppendLine(" AND " + condition);
                    }
                }
            }

            if (whereBuilder.Length == 0)
                return sql;

            // 🔥 Split ORDER BY safely
            var orderByIndex = sql.LastIndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);

            if (orderByIndex > -1)
            {
                var mainSql = sql.Substring(0, orderByIndex);
                var orderBySql = sql.Substring(orderByIndex);

                // If WHERE already exists, just append AND conditions
                if (mainSql.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
                {
                    return mainSql + whereBuilder + orderBySql;
                }
                else
                {
                    // Convert first AND → WHERE
                    return mainSql + " WHERE 1=1 " + whereBuilder + orderBySql;
                }
            }

            // No ORDER BY → simple append
            if (sql.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
                return sql + whereBuilder;

            return sql + " WHERE 1=1 " + whereBuilder;
        }



        private static T? GetCriteriaValue<T>(CriteriaDTO criteriaDTO, string fieldName)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return default;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section.AttributesCriteriaList == null)
                    continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (!attr.FieldName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                        continue;

                    if (attr.FieldValue == null)
                        return default;

                    // ✅ Handle JsonElement safely
                    if (attr.FieldValue is JsonElement json)
                    {
                        if (typeof(T) == typeof(string))
                            return (T)(object)json.GetString();

                        if (typeof(T) == typeof(int) || typeof(T) == typeof(int?))
                            return (T)(object)json.GetInt32();

                        if (typeof(T) == typeof(long) || typeof(T) == typeof(long?))
                            return (T)(object)json.GetInt64();

                        if (typeof(T) == typeof(bool) || typeof(T) == typeof(bool?))
                            return (T)(object)json.GetBoolean();

                        if (typeof(T) == typeof(DateTime) || typeof(T) == typeof(DateTime?))
                            return (T)(object)json.GetDateTime();
                    }

                    // ✅ Already correct type
                    if (attr.FieldValue is T value)
                        return value;

                    // ✅ Fallback for string → value types
                    return (T)Convert.ChangeType(
                        attr.FieldValue.ToString(),
                        Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T)
                    );
                }
            }

            return default;
        }

        private static object BuildPatternParameters(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO == null)
                return new { };

            return new
            {
                Code = GetCriteriaValue<string>(criteriaDTO, "Code"),
                Name = GetCriteriaValue<string>(criteriaDTO, "Name"),
                ItemId = GetCriteriaValue<int?>(criteriaDTO, "ItemId")
            };
        }


    }
}