using FrameworkDAL.DTO.Code;
using FrameworkDAL.DTO.Entity;
using FrameworkDAL.Query.CodeDefine;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.EndPointResource;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.CodeDefine
{
    public class CodeDefineDAL : ICodeDefineDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public CodeDefineDAL(IQueryExecutor QueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = IValidation;
        }
        public async Task<string> GetCodeDefine(string EntityCode, string CategoryCode, LoginDTO LoginDTO)
        {
            try
            {
                // ── SPECIAL CASE: CategoryCode provided ──────────────────────────────────
                if (!string.IsNullOrEmpty(CategoryCode))
                {
                    // Try PROC_GENERATE_CODE with the given CategoryCode
                    string? generatedCode = await _QueryExecutor.ExecuteScalarAsync<string>(
                        LoginDTO,
                        CodeDefineQB.TRY_EXECUTE_PROC_GENERATE_CODE,
                        new { categorycode = CategoryCode, inputjson = "{}" }
                    );

                    if (!string.IsNullOrEmpty(generatedCode))
                    {
                        return generatedCode;
                    }

                    // Proc returned null — resolve EntityId from CategoryCode for regular logic fallback
                    int entityIdFromCategory = await _QueryExecutor.ExecuteScalarAsync<int>(
                        LoginDTO,
                        CodeDefineQB.GET_ENTITYID_BY_CATEGORYCODE,
                        new { categorycode = CategoryCode }
                    );

                    if (entityIdFromCategory != 0)
                    {
                        return await GetRegularCodeDefine(entityIdFromCategory, LoginDTO);
                    }

                    return JsonConvert.SerializeObject(new List<CodeDefineDTO>());
                }

                // ── REGULAR CASE: EntityCode provided ────────────────────────────────────
                IList<EntityDTO> entities = (await _QueryExecutor
                    .QueryAsync<EntityDTO>(LoginDTO, CodeDefineQB.GET_ENTITY_BY_CODE, new { entitycode = EntityCode }))
                    .ToList();

                if (entities.Count == 1)
                {
                    return await GetRegularCodeDefine(entities[0].EntityId, LoginDTO);
                }
                else if (entities.Count > 1)
                {
                    throw new FoundException("More than one entry found in Entity table for the given EntityCode. Check configuration.");
                }
                else
                {
                    throw new NotFoundException("No entry found in Entity table for the given EntityCode. Check configuration.");
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<string> GetRegularCodeDefine(int EntityId, LoginDTO LoginDTO)
        {
            IEnumerable<CodeDefineDTO> result = await _QueryExecutor
                .QueryAsync<CodeDefineDTO>(LoginDTO, CodeDefineQB.GET_LOAD_CODE_DEFINE_BY_PARAMS, new { entityid = EntityId });
            return JsonConvert.SerializeObject(result);
        }
        public async Task<string> GetSelectListCodeDefine(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (!IsCount)
                {
                    string SQL = CodeDefineQB.GET_SELECTLIST_CODEDEFINE;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    List<CodeDefinePicklistDTO> CodeDefinePicklistDTOs = (await _QueryExecutor.QueryAsync<CodeDefinePicklistDTO>(LoginDTO, SQL, parameter)).ToList();
                    Json = JsonConvert.SerializeObject(CodeDefinePicklistDTOs);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }

                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveCodeDefine(CodeDefineDTO CodeDefineDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>
                {
                    (CodeDefineQB.SAVE_CODEDEFINE, CodeDefineDTO)
                };

                foreach (var seg in CodeDefineDTO.CodeDefineSegmentArray)
                    statements.Add((CodeDefineSegmentQB.SAVE_CODEDEFINESEGMENT, seg));

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
                return 1;
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateCodeDefine(CodeDefineDTO CodeDefineDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>
                {
                    // Delete all existing segments first (delete-and-reinsert pattern)
                    (CodeDefineSegmentQB.DELETE_SEGMENTS_BY_CODEDEFINE_ID, new { codedefineId = CodeDefineDTO.CodeDefineId }),
                    // Update header
                    (CodeDefineQB.UPDATE_CODEDEFINE, CodeDefineDTO)
                };

                // Re-insert all segments
                foreach (var seg in CodeDefineDTO.CodeDefineSegmentArray)
                    statements.Add((CodeDefineSegmentQB.SAVE_CODEDEFINESEGMENT, seg));

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
                return 1;
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteCodeDefine(int CodeDefineId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = CodeDefineQB.DELETE_CODEDEFINE;
                var Parameters = new { CodeDefineid = CodeDefineId };
                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);
            }
        }
    }
}