using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.CodeDefine;
using GB5Shared.DTO.Framework.Entity;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.Query.CodeGeneration;
using GB5Shared.QueryExecutor;


namespace GB5Shared.GenerateAutoNumber
{
    /// <summary>
    /// Generic Code Generation Service for all entities
    /// Supports 4 generation types: Auto(0), Manual(1), Formula(2), Hidden(3)
    /// </summary>
    public class CodeGeneration
    {
        private readonly IQueryExecutor _queryExecutor;

        public CodeGeneration(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        /// <summary>
        /// Assigns code to any entity object based on CodeDefine configuration
        /// Works with any DTO: EmployeeDTO, CustomerDTO, ProductDTO, etc.
        /// </summary>
        /// <typeparam name="T">Entity DTO type</typeparam>
        /// <param name="entityName">Entity name (e.g., "Employee", "Customer", "Product")</param>
        /// <param name="obj">Entity object</param>
        /// <param name="loginDto">Login context</param>
        /// <returns>Object with assigned code</returns>
        public async Task<T> AssignCode<T>(string entityName, T obj, LoginDTO loginDto)
        {
            if (string.IsNullOrWhiteSpace(entityName))
                throw new ArgumentException("Entity name is required", nameof(entityName));
            if (obj == null)
                throw new ArgumentNullException(nameof(obj));
            if (loginDto == null)
                throw new ArgumentNullException(nameof(loginDto));

            var originalDatabase = loginDto.DatabaseName;

            try
            {
                string entityPrefix = ExtractEntityPrefix(obj.GetType().ToString());

                var entityDto = await GetEntityByCodeAsync(entityName, loginDto);
                if (entityDto == null)
                    throw new NotFoundException($"Entity '{entityName}' not found in MENTITY table");

                if (entityDto.EntityIsCodeApplicable == 1)
                    return obj;

                var codeDefines = await LoadCodeDefinesAsync(entityDto, obj, loginDto);

                var codeProperty = obj.GetType().GetProperty(entityPrefix + "Code");
                var currentCode = codeProperty?.GetValue(obj, null) as string;

                // If no CodeDefine exists — fallback to manual only if code is provided
                if (codeDefines == null || codeDefines.Count == 0)
                {
                    if (!string.IsNullOrWhiteSpace(currentCode))
                        return obj;

                    throw new NotFoundException(
                        $"No CodeDefine found and no manual code entered for {entityName}");
                }

                if (codeDefines.Count > 1)
                {
                    // List the actual conflicting rows instead of a bare "expected only one" —
                    // makes it immediately clear which CodeDefineId to send, and whether the
                    // category filter ran at all (if this lists every configured category
                    // unfiltered, the caller's chosen category value never made it through).
                    string candidates = string.Join("; ", codeDefines.Select(cd =>
                        $"CodeDefineId={cd.CodeDefineId} ({cd.CodeDefineCategoryCode} - {cd.CodeDefineCategoryName})"));

                    throw new FoundException(
                        $"Entity '{entityName}' has {codeDefines.Count} CodeDefine categories configured, " +
                        $"and no single one was selected: {candidates}. " +
                        $"Set the '{entityPrefix}CodeDefineId' field to one of the CodeDefineId values above and resend.");
                }

                var codeDefine = codeDefines[0];

                // Manual Mode: GENERATIONTYPE = 1
                if (codeDefine.CodeDefineGenerationType == 1)
                {
                    if (!string.IsNullOrWhiteSpace(currentCode))
                    {
                        // Set CodeDefineId if property exists
                        var codeDefineIdProp = obj.GetType().GetProperty("CodeDefineId");
                        if (codeDefineIdProp != null && codeDefineIdProp.PropertyType == typeof(int))
                        {
                            codeDefineIdProp.SetValue(obj, codeDefine.CodeDefineId, null);
                        }
                        return obj;  // Use manual code
                    }

                    throw new NotFoundException(
                        $"GENERATIONTYPE is MANUAL. Please enter a code for {entityName}.");
                }

                // Auto Mode: GENERATIONTYPE = 0 or 2
                await ProcessCodeGeneration(codeDefine, obj, entityPrefix, loginDto);
                // Set CodeDefineId if property exists
                var autoCodeDefineIdProp = obj.GetType().GetProperty("CodeDefineId");
                if (autoCodeDefineIdProp != null && autoCodeDefineIdProp.PropertyType == typeof(int))
                {
                    autoCodeDefineIdProp.SetValue(obj, codeDefine.CodeDefineId, null);
                }

                return obj;
            }
            catch
            {
                loginDto.DatabaseName = originalDatabase;
                throw;
            }
            finally
            {
                loginDto.DatabaseName = originalDatabase;
            }

        }

        /// <summary>
        /// Extract entity prefix from full DTO type name
        /// Example: "PayRollDAL.DTO.Employee.EmployeeDTO" -> "Employee"
        /// </summary>
        private string ExtractEntityPrefix(string fullTypeName)
        {
            string[] parts = fullTypeName.Split('.');
            string typeName = parts[parts.Length - 1];

            // Remove "DTO" suffix if present
            if (typeName.Length > 3 && typeName.EndsWith("DTO", StringComparison.OrdinalIgnoreCase))
            {
                return typeName.Substring(0, typeName.Length - 3);
            }

            return typeName;
        }

        /// <summary>
        /// Load CodeDefine records based on entity configuration
        /// Handles both simple and category-based scenarios
        /// </summary>
        private async Task<IList<CodeDefineDTO>> LoadCodeDefinesAsync<T>(
            EntityDTO entityDto,
            T obj,
            LoginDTO loginDto)
        {
            // MENTITY.ISMULTICATEGORY/CATEGORYMEMBERID have proven unreliable/stale for some
            // tenants — e.g. EMPLOYEE here has ISMULTICATEGORY=0, yet genuinely has 2 MCODEDEFINE
            // rows configured (UNIEMP, Employee). So instead of trusting that flag to decide the
            // branch up front, always load every MCODEDEFINE row for this EntityId first, and only
            // fall back to category-based disambiguation when more than one row actually comes back.
            var allDefines = await QueryListAsync<CodeDefineDTO>(
                loginDto,
                CodeGenerationQB.GET_CODEDEFINE_BY_ENTITYID,
                new { entityid = entityDto.EntityId });

            if (allDefines == null || allDefines.Count <= 1)
                return allDefines ?? new List<CodeDefineDTO>();

            // More than one CodeDefine row exists for this entity — must disambiguate via category.
            // NOTE: -1 is this system's sentinel for "not set" — every other value, including large
            // negative IDs (e.g. -1399995678), is a real, valid foreign key. Do not use `<= 0` here.
            if (entityDto.EntityCategoryMemberId == -1)
                throw new FoundException(
                    $"Multiple CodeDefine entries found for entity '{entityDto.EntityCode}', but MENTITY.CATEGORYMEMBERID " +
                    "is not configured to disambiguate them.");

            var entityMember = await _queryExecutor.QuerySingleAsync<EntityMemberDTO>(
                loginDto,
                CodeGenerationQB.GET_ENTITYMEMBERS,
                new { entitymemberid = entityDto.EntityCategoryMemberId });

            if (entityMember == null)
                throw new NotFoundException("EntityMember not found in MENTITYMEMBER table");

            // Extract the caller-specified category value from the object
            // (e.g. EmployeeDTO.EmployeeCodeDefineId, sent by the caller to pick which
            // CodeDefine category — UNIEMP vs Employee — applies to this save).
            var categoryValue = ExtractCategoryValue(obj, entityMember.EntityMemberMemberName);
            int? categoryCodeDefineId = categoryValue == null ? null : Convert.ToInt32(categoryValue);

            var matches = allDefines
                .Where(cd => categoryCodeDefineId.HasValue && cd.CodeDefineId == categoryCodeDefineId.Value)
                .ToList();

            return matches;
        }

        /// <summary>
        /// Extract category value from object property (handles nested Id properties)
        /// </summary>
        private object ExtractCategoryValue<T>(T obj, string propertyName)
        {
            var property = obj.GetType().GetProperty(propertyName);
            if (property == null)
                throw new NotFoundException($"Property '{propertyName}' not found in {obj.GetType().Name}");

            object value = property.GetValue(obj, null);

            // Handle nested objects with Id property (e.g., Department.DepartmentId)
            if (value != null)
            {
                var valueType = value.GetType();
                if (valueType.FullName != null && valueType.FullName.Contains("Unisoft."))
                {
                    var idProperty = valueType.GetProperty("Id");
                    if (idProperty != null)
                    {
                        value = idProperty.GetValue(value, null);
                    }
                }
            }

            return value;
        }

        /// <summary>
        /// Main code generation orchestrator - handles all 4 generation types
        /// Uses MCODEDEFINE.GENERATIONTYPE to determine logic flow
        /// </summary>
        private async Task ProcessCodeGeneration<T>(
            CodeDefineDTO codeDefine,
            T obj,
            string entityPrefix,
            LoginDTO loginDto)
        {
            // Find the Code property dynamically (e.g., "EmployeeCode", "CustomerCode")
            var codeProperty = obj.GetType().GetProperty(entityPrefix + "Code");
            if (codeProperty == null)
            {
                throw new NotFoundException(
                    $"Code property '{entityPrefix}Code' not found in {obj.GetType().Name}. " +
                    $"Expected property name based on entity prefix.");
            }

            // Get current code value (may be null or empty)
            object currentValue = codeProperty.GetValue(obj, null);
            string currentCode = currentValue as string;

            // Process based on GENERATIONTYPE from MCODEDEFINE table
            // 0 = Auto, 1 = Manual, 2 = Formula, 3 = Hidden
            switch (codeDefine.CodeDefineGenerationType)
            {
                case 0: // AUTO - System generates code automatically
                    {
                        string generatedCode = await GenerateAutoCodeAsync(codeDefine, loginDto);
                        codeProperty.SetValue(obj, generatedCode, null);
                        break;
                    }

                case 1: // MANUAL - User must provide code (validate only)
                    {
                        if (string.IsNullOrWhiteSpace(currentCode))
                        {
                            throw new NotFoundException(
                                $"{entityPrefix}Code is required. GENERATIONTYPE is MANUAL (1) - user must provide code value.");
                        }

                        // Validate the manually provided code against constraints
                        ValidateManualCode(currentCode.Trim(), codeDefine, entityPrefix);
                        codeProperty.SetValue(obj, currentCode.Trim(), null);
                        break;
                    }

                case 2: // FORMULA - Generate based on formula from MFORMULA table
                    {
                        string generatedCode = await GenerateFormulaBasedCodeAsync(codeDefine, loginDto);
                        codeProperty.SetValue(obj, generatedCode, null);
                        break;
                    }

                case 3: // HIDDEN - Auto-generate but typically hidden in UI
                    {
                        string generatedCode = await GenerateAutoCodeAsync(codeDefine, loginDto);
                        codeProperty.SetValue(obj, generatedCode, null);
                        break;
                    }

                case 4: // SEGMENT - Delegates to PROC_GENERATE_CODE stored procedure
                    {
                        string generatedCode = await GenerateSegmentCodeAsync(codeDefine, obj, loginDto);
                        codeProperty.SetValue(obj, generatedCode, null);
                        break;
                    }

                default:
                    throw new MethodNotAllowedException(
                        $"Invalid GENERATIONTYPE '{codeDefine.CodeDefineGenerationType}' in MCODEDEFINE table. " +
                        $"Valid values: 0=Auto, 1=Manual, 2=Formula, 3=Hidden, 4=Segment");
            }
        }

        /// <summary>
        /// Generate AUTO code (GenerationType 0 or 3)
        /// Uses: CATPREFIX + Zero-padded Number + CATSUFFIX from MCODEDEFINE
        /// Updates: CATLASTNO in MCODEDEFINE table
        /// </summary>
        private async Task<string> GenerateAutoCodeAsync(CodeDefineDTO codeDefine, LoginDTO loginDto)
        {
            // Atomically increment CATLASTNO and read back the new value in one round trip —
            // see INCREMENT_AND_GET_CATLASTNO for why this must not be a separate read+write.
            int newLastNumber = await IncrementAndGetLastNumberAsync(codeDefine.CodeDefineId, loginDto);

            // Validate against CATENDNO
            if (codeDefine.CodeDefineCatEndNo.HasValue && newLastNumber > codeDefine.CodeDefineCatEndNo.Value)
            {
                throw new MethodNotAllowedException(
                    $"Code generation failed: Next number ({newLastNumber}) exceeds CATENDNO ({codeDefine.CodeDefineCatEndNo}). " +
                    $"Update MCODEDEFINE.CATENDNO for CODEDEFINEID={codeDefine.CodeDefineId}");
            }

            // Build code using CATPREFIX, number, CATSUFFIX, and CODESIZE
            string generatedCode = BuildCodeString(
                newLastNumber,
                codeDefine.CodeDefineCodeSize,
                codeDefine.CodeDefineCatPrefix,
                codeDefine.CodeDefineCatSuffix);

            return generatedCode;
        }

        /// <summary>
        /// Generate FORMULA-based code (GenerationType 2)
        /// Uses: Formula from MFORMULA table referenced by FORMULAID
        /// </summary>
        private async Task<string> GenerateFormulaBasedCodeAsync(CodeDefineDTO codeDefine, LoginDTO loginDto)
        {
            if (!codeDefine.CodeDefineFormulaId.HasValue || codeDefine.CodeDefineFormulaId.Value <= 0)
            {
                throw new MissingMethodException(
                    $"FORMULAID not set in MCODEDEFINE (CODEDEFINEID={codeDefine.CodeDefineId}). " +
                    $"Formula-based generation requires valid FORMULAID.");
            }

            // Load formula from MFORMULA table
            var formula = await GetFormulaByIdAsync(codeDefine.CodeDefineFormulaId.Value, loginDto);
            if (formula == null)
            {
                throw new NotFoundException($"Formula not found in MFORMULA for FORMULAID={codeDefine.CodeDefineFormulaId}");
            }

            // Evaluate formula to get numeric result
            int calculatedNumber = await EvaluateFormulaAsync(formula, loginDto);

            // Increment the calculated number
            int newLastNumber = calculatedNumber + 1;

            // Validate against CATENDNO
            if (codeDefine.CodeDefineCatEndNo.HasValue && newLastNumber > codeDefine.CodeDefineCatEndNo.Value)
            {
                throw new MethodNotAllowedException(
                    $"Formula-based generation failed: Calculated number ({newLastNumber}) exceeds CATENDNO ({codeDefine.CodeDefineCatEndNo})");
            }

            // Build code
            string generatedCode = BuildCodeString(
                newLastNumber,
                codeDefine.CodeDefineCodeSize,
                codeDefine.CodeDefineCatPrefix,
                codeDefine.CodeDefineCatSuffix);

            // Update CATLASTNO
            await UpdateLastNumberAsync(codeDefine.CodeDefineId, newLastNumber, loginDto);

            return generatedCode;
        }

        /// <summary>
        /// Validate MANUAL code (GenerationType 1)
        /// Checks: Length, Prefix, Suffix constraints from MCODEDEFINE
        /// </summary>
        private void ValidateManualCode(string code, CodeDefineDTO codeDefine, string entityName)
        {
            // Check CODESIZE constraint
            if (code.Length > codeDefine.CodeDefineCodeSize)
            {
                throw new MethodNotAllowedException(
                    $"Manual code '{code}' (length={code.Length}) exceeds CODESIZE ({codeDefine.CodeDefineCodeSize}) " +
                    $"defined in MCODEDEFINE for {entityName}");
            }

            // Check CATPREFIX constraint (if defined)
            string prefix = codeDefine.CodeDefineCatPrefix ?? string.Empty;
            if (!string.IsNullOrEmpty(prefix) && !code.StartsWith(prefix, StringComparison.OrdinalIgnoreCase))
            {
                throw new MethodNotAllowedException(
                    $"Manual code '{code}' must start with CATPREFIX '{prefix}' as defined in MCODEDEFINE");
            }

            // Check CATSUFFIX constraint (if defined)
            string suffix = codeDefine.CodeDefineCatSuffix ?? string.Empty;
            if (!string.IsNullOrEmpty(suffix) && !code.EndsWith(suffix, StringComparison.OrdinalIgnoreCase))
            {
                throw new MethodNotAllowedException(
                    $"Manual code '{code}' must end with CATSUFFIX '{suffix}' as defined in MCODEDEFINE");
            }
        }

        /// <summary>
        /// Build code string using MCODEDEFINE values
        /// Format: CATPREFIX + Zero-padded Number + CATSUFFIX
        /// Example: "EMP" + "0001" + "" = "EMP0001" (if CODESIZE=7)
        /// </summary>
        private string BuildCodeString(int number, int totalSize, string prefix, string suffix)
        {
            prefix = prefix ?? string.Empty;
            suffix = suffix ?? string.Empty;

            string numberStr = number.ToString();

            // Calculate total fixed length
            int fixedLength = prefix.Length + suffix.Length + numberStr.Length;

            if (fixedLength > totalSize)
            {
                string candidate = prefix + numberStr + suffix;
                throw new MethodNotAllowedException(
                    $"Generated code '{candidate}' (length={candidate.Length}) exceeds CODESIZE ({totalSize}). " +
                    $"Adjust CATPREFIX, CATSUFFIX, or CODESIZE in MCODEDEFINE table.");
            }

            // Calculate zero padding needed
            int zerosNeeded = totalSize - prefix.Length - suffix.Length - numberStr.Length;
            string zeros = new string('0', Math.Max(0, zerosNeeded));

            return prefix + zeros + numberStr + suffix;
        }

        /// <summary>
        /// Update CATLASTNO in MCODEDEFINE table after successful code generation
        /// </summary>
        private async Task UpdateLastNumberAsync(int codeDefineId, int newLastNumber, LoginDTO loginDto)
        {
            await _queryExecutor.ExecuteAsync(
                loginDto,
                CodeGenerationQB.UPDATE_CATLASTNO,
                new { catlastno = newLastNumber, codedefineid = codeDefineId });
        }

        /// <summary>
        /// Atomically increments CATLASTNO and returns the new value — see
        /// INCREMENT_AND_GET_CATLASTNO for why plain auto-numbering must use this, not
        /// UpdateLastNumberAsync, to avoid duplicate codes within a single request.
        /// </summary>
        private async Task<int> IncrementAndGetLastNumberAsync(int codeDefineId, LoginDTO loginDto)
        {
            return await _queryExecutor.ExecuteScalarAsync<int>(
                loginDto,
                CodeGenerationQB.INCREMENT_AND_GET_CATLASTNO,
                new { codedefineid = codeDefineId });
        }

        /// <summary>
        /// Evaluate formula from MFORMULA table to get numeric value
        /// </summary>
        private async Task<int> EvaluateFormulaAsync(dynamic formula, LoginDTO loginDto)
        {
            if (formula.FormulaType == 0)
            {
                throw new MissingMethodException("FormulaType 0 not supported for code generation");
            }

            string expression;

            if (formula.FormulaType == 1) // Function-based formula
            {
                if (formula.DataType == 0 || formula.DataType == 2) // Numeric data types
                {
                    expression = loginDto.DatabaseType == 0
                        ? $"ISNULL({formula.FunctionName}, 0)"  // SQL Server
                        : $"NVL({formula.FunctionName}, 0)";    // Oracle
                }
                else
                {
                    throw new MissingMethodException(
                        "Only numeric DataType supported for formula-based code generation");
                }
            }
            else // Expression-based formula
            {
                expression = formula.Expression;
            }

            // Execute SELECT to get numeric value
            string selectSql = loginDto.DatabaseType == 0
                ? $"SELECT {expression} AS EntityId"
                : $"SELECT {expression} AS EntityId FROM dual";

            var results = await QueryListAsync<EntityDTO>(loginDto, selectSql, null);

            if (results == null || results.Count == 0)
            {
                throw new NotFoundException("Formula evaluation returned no results");
            }

            return results[0].EntityId;
        }

        /// <summary>
        /// Load entity metadata from MENTITY table
        /// </summary>
        private async Task<EntityDTO> GetEntityByCodeAsync(string entityCode, LoginDTO loginDto)
        {
            string sql = CodeGenerationQB.GET_ENTITY_BY_CODE;
            return await _queryExecutor.QuerySingleAsync<EntityDTO>(
                loginDto,
                sql,
                new { entitycode = entityCode });
        }

        /// <summary>
        /// Load formula from MFORMULA table - MUST BE IMPLEMENTED
        /// Expected properties: FormulaType, DataType, FunctionName, Expression
        /// </summary>
        private async Task<dynamic> GetFormulaByIdAsync(int formulaId, LoginDTO loginDto)
        {
            // TODO: Implement loading formula from MFORMULA table
            // Example SQL: "SELECT * FROM MFORMULA WHERE FORMULAID = @formulaid"

            string sql = "SELECT * FROM MFORMULA WHERE FORMULAID = @formulaid";
            var formula = await _queryExecutor.QuerySingleAsync<dynamic>(
                loginDto,
                sql,
                new { formulaid = formulaId });

            if (formula == null)
            {
                throw new NotFoundException($"Formula not found in MFORMULA for FORMULAID={formulaId}");
            }

            return formula;
        }

        /// <summary>
        /// Generate SEGMENT code (GenerationType 4).
        /// Serializes the entity DTO to JSON and calls PROC_GENERATE_CODE via a
        /// DECLARE/SELECT wrapper so the OUTPUT parameter is returned as a result set,
        /// avoiding the need for DynamicParameters.
        /// </summary>
        private async Task<string> GenerateSegmentCodeAsync<T>(
            CodeDefineDTO codeDefine,
            T obj,
            LoginDTO loginDto)
        {
            string inputJson = JsonSerializer.Serialize(obj);

            var parameters = new
            {
                categorycode = codeDefine.CodeDefineCategoryCode,
                inputjson    = inputJson
            };

            var result = await _queryExecutor.QuerySingleAsync<GeneratedCodeResult>(
                loginDto,
                CodeGenerationQB.EXECUTE_PROC_GENERATE_CODE,
                parameters);

            string generatedCode = result?.GeneratedCode;

            if (string.IsNullOrWhiteSpace(generatedCode))
                throw new NotFoundException(
                    $"PROC_GENERATE_CODE returned empty code for CODEDEFINEID={codeDefine.CodeDefineId}. " +
                    $"Check MCODEDEFINESEGMENT rows and that mandatory segments have values.");

            return generatedCode;
        }

        /// <summary>Minimal result DTO for capturing PROC_GENERATE_CODE output.</summary>
        private sealed class GeneratedCodeResult
        {
            public string GeneratedCode { get; set; }
        }

        #region Helper Methods

        private async Task<IList<T>> QueryListAsync<T>(LoginDTO loginDto, string sql, object parameters)
        {
            var result = await _queryExecutor.QueryAsync<T>(loginDto, sql, parameters);
            return result?.ToList() ?? new List<T>();
        }

        #endregion
    }
}

//using System;
//using System.Collections.Generic;
//using System.Linq;
//using System.Threading.Tasks;
//using GB5Shared.DTO.Framework.CodeDefine;
//using GB5Shared.DTO.Framework.Criteria;
//using GB5Shared.DTO.Framework.Entity;
//using GB5Shared.DTO.Framework.Login;
//using GB5Shared.GB5Exception;
//using GB5Shared.Query.CodeGeneration;
//using GB5Shared.QueryExecutor;
//using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;
//using static GB5Shared.GB5Constant.Constant;

//namespace GB5Shared.GenerateAutoNumber
//{
//    public class CodeGeneration
//    {
//        private readonly IQueryExecutor _queryExecutor;

//        public CodeGeneration(IQueryExecutor queryExecutor)
//        {
//            _queryExecutor = queryExecutor;
//        }

//        /// <summary>
//        /// Assigns code to the given object based on CodeDefine configuration.
//        /// Logic preserved from original synchronous implementation but corrected & hardened.
//        /// </summary>
//        public async Task<T> AssignCode<T>(string objectName, T obj, LoginDTO loginDto)
//        {
//            if (string.IsNullOrWhiteSpace(objectName))
//                throw new ArgumentException("objectName required", nameof(objectName));
//            if (obj == null)
//                throw new ArgumentNullException(nameof(obj));
//            if (loginDto == null)
//                throw new ArgumentNullException(nameof(loginDto));

//            var originalDatabase = loginDto.DatabaseName;

//            try
//            {
//                // Determine prefix from POCO type name (strip trailing "DTO")
//                string prefix = "";
//                string entityObjectCode = obj.GetType().ToString();
//                string[] splitPocoName = entityObjectCode.Split('.');
//                string entityCode = splitPocoName[splitPocoName.Length - 1];

//                if (entityCode.Length > 3)
//                {
//                    var dtoSuffix = entityCode.Substring(entityCode.Length - 3);
//                    if (dtoSuffix.Equals("DTO", StringComparison.OrdinalIgnoreCase))
//                    {
//                        prefix = entityCode.Remove(entityCode.Length - 3, 3);
//                    }
//                }

//                Type persistentType = typeof(T);

//                // Load entity metadata
//                var entityDto = await GetEntityByCodeAsync(objectName, loginDto);
//                if (entityDto == null)
//                    throw new NotFoundException("Entity Entry is not found for the given entity");

//                // If code generation not applicable (1 => No according to your logic)
//                if (entityDto.EntityIsCodeApplicable == 1)
//                    return obj;

//                // Prepare SQL and parameters to load code define(s)
//                string sqlToLoadCodeDefines;
//                object parametersForCodeDefine = null!;

//                if (entityDto.EntityCategoryMemberId == -1)
//                {
//                    sqlToLoadCodeDefines = CodeGenerationQB.GET_ENTITY_BY_ID;
//                    parametersForCodeDefine = new { entityid = entityDto.EntityId };
//                }
//                else
//                {
//                    // Load the entity member (category) to find the category member name
//                    sqlToLoadCodeDefines = CodeGenerationQB.GET_ENTITYMEMBERS;
//                    var para = new { entitymemberid = entityDto.EntityCategoryMemberId };
//                    var entityMember = await _queryExecutor.QuerySingleAsync<EntityMemberDTO>(loginDto, CodeGenerationQB.GET_ENTITYMEMBERS, para);
//                    if (entityMember == null)
//                        throw new NotFoundException("Entry Not Found For the supplied object in EntityMember Table");

//                    // Reflect to get the value of the member from the object
//                    var propInfo = obj.GetType().GetProperty(entityMember.EntityMemberMemberName);
//                    if (propInfo == null)
//                        throw new NotFoundException(entityMember.EntityMemberMemberName + " is not Found in given object and CategoryMemberId field is set to Entity object");

//                    object tempCategoryValue = propInfo.GetValue(obj, null)!;
//                    if (tempCategoryValue != null)
//                    {
//                        var tempCategoryType = tempCategoryValue.GetType();
//                        if (tempCategoryType.FullName != null && tempCategoryType.FullName.Contains("Unisoft."))
//                        {
//                            var idProp = tempCategoryType.GetProperty("Id");
//                            if (idProp == null)
//                                throw new NotFoundException("Id is not Found in given object and CategoryMemberId field is set to Entity object");

//                            tempCategoryValue = idProp.GetValue(tempCategoryValue, null)!;
//                        }
//                    }

//                    // Prepare SQL that loads codedefines by entityid + codedefineid (category id)
//                    sqlToLoadCodeDefines = CodeGenerationQB.LOAD_CODE_DEFINE_BY_PARAMS_BASED_ON_CATEGORY_ID_SQL;
//                    parametersForCodeDefine = new
//                    {
//                        entityid = entityDto.EntityId,
//                        codedefineid = tempCategoryValue
//                    };
//                }

//                // Query code define rows (expect 0,1 or >1 results)
//                var codeDefinePocos = await QueryListAsync<CodeDefineDTO>(loginDto, sqlToLoadCodeDefines, parametersForCodeDefine);
//                if (codeDefinePocos == null) codeDefinePocos = new List<CodeDefineDTO>();

//                if (codeDefinePocos.Count == 1)
//                {
//                    var codeDefine = codeDefinePocos[0];

//                    // Ensure target property exists: Prefix + "Code"
//                    var codeProp = obj.GetType().GetProperty(prefix + "Code");
//                    if (codeProp == null)
//                        throw new NotFoundException("Code is not found in the Save object but Entry is found..");

//                    // current code value (may be null)
//                    object currentCodeValue = codeProp.GetValue(obj, null)!;

//                    // Decide generation type and set Code on object
//                    switch (codeDefine.CodeDefineGenerationType) // adapt property name if different
//                    {
//                        case 0: // Auto
//                        case 3: // Hidden (also auto)
//                            {
//                                string generatedCode = GenerateCode(codeDefine, loginDto);
//                                codeProp.SetValue(obj, generatedCode, null);
//                                break;
//                            }
//                        case 1: // Manual
//                            {
//                                if (currentCodeValue is string s && !string.IsNullOrEmpty(s))
//                                {
//                                    codeProp.SetValue(obj, s, null);
//                                }
//                                else
//                                {
//                                    throw new NotFoundException("Code is Not Supplied..Since Code generation type is Manual");
//                                }
//                                break;
//                            }
//                        case 2: // Formula
//                            {
//                                string generatedCode = GenerateCode(codeDefine, loginDto);
//                                codeProp.SetValue(obj, generatedCode, null);
//                                break;
//                            }
//                        default:
//                            throw new MethodNotAllowedException("Unknown GenerationType in CodeDefine.");
//                    }
//                }
//                else if (codeDefinePocos.Count > 1)
//                {
//                    throw new FoundException("More Than one Entries found in CodeDefine table based on Given EntityCode >> " + objectName);
//                }
//                else // 0 rows
//                {
//                    var targetProp = obj.GetType().GetProperty(prefix + "Code");
//                    if (targetProp != null)
//                    {
//                        throw new NotFoundException("No Entries found in CodeDefine table based on Given EntityCode >> " + objectName);
//                    }
//                }

//                // restore DB name if modified earlier
//                loginDto.DatabaseName = originalDatabase;
//                return obj;
//            }
//            catch
//            {
//                throw;
//            }
//        }


//        /// <summary>
//        /// Helper to load Entity metadata by entity code.
//        /// </summary>
//        private async Task<EntityDTO> GetEntityByCodeAsync(string objectName, LoginDTO loginDto)
//        {
//            try
//            {
//                string sql = CodeGenerationQB.GET_ENTITY_BY_CODE;
//                var param = new { entitycode = objectName };
//                return await _queryExecutor.QuerySingleAsync<EntityDTO>(loginDto, sql, param);
//            }
//            catch
//            {
//                throw;
//            }
//        }

//        /// <summary>
//        /// Generates a code according to CodeDefine DTO. This implements the same logic as your original GenerateCode,
//        /// but uses async update for CATLASTNO and clearer variable names.
//        /// </summary>
//        private string GenerateCode(CodeDefineDTO codeDefine, LoginDTO loginDto)
//        {
//            if (codeDefine == null) throw new ArgumentNullException(nameof(codeDefine));

//            try
//            {
//                string resultCode = string.Empty;
//                int totalLength = codeDefine.CodeDefineCodeSize;
//                int lastNumber = codeDefine.CodeDefineCatLastNo;
//                string prefix = codeDefine.CodeDefineCatPrefix ?? "";
//                string suffix = codeDefine.CodeDefineCatSuffix ?? "";

//                // Helper to format code with leading zeros as required
//                string BuildFromNumber(int number)
//                {
//                    string numberStr = number.ToString();
//                    int fixedPartLength = prefix.Length + suffix.Length + numberStr.Length;
//                    if (fixedPartLength > totalLength)
//                    {
//                        string candidate = prefix + numberStr + suffix;
//                        throw new MethodNotAllowedException($"Code can not be generated since generated code >> {candidate} is more than the size specified in Entity Table for the given entity. Please correct.");
//                    }

//                    int zerosNeeded = totalLength - (prefix.Length + suffix.Length + numberStr.Length);
//                    // zerosNeeded may be zero or more; if >0 produce string with zeros
//                    string zeros = new string('0', Math.Max(0, zerosNeeded));
//                    return prefix + zeros + numberStr + suffix;
//                }

//                // Auto or Hidden generation
//                if (codeDefine.CodeDefineGenerationType == 0 || codeDefine.CodeDefineGenerationType == 3)
//                {
//                    lastNumber = lastNumber + 1;
//                    if (lastNumber > codeDefine.CodeDefineCatEndNo)
//                        throw new MethodNotAllowedException("Check the setting in Code Define table,lastnumber is greater than end number");

//                    resultCode = BuildFromNumber(lastNumber);

//                    // Persist updated last number
//                    string updateSql = $"UPDATE MCODEDEFINE SET CATLASTNO = {lastNumber} WHERE CodeDefineId = {codeDefine.CodeDefineId}";
//                    // Use async execute in your real environment - here call a sync helper for compatibility
//                    ExecuteNonQueryAsync(loginDto, updateSql).GetAwaiter().GetResult();
//                }
//                else if (codeDefine.CodeDefineGenerationType == 2) // formula based
//                {
//                    // Evaluate formula
//                    // Note: I assume CodeDefineDTO.CodeDefineFormulaId exists (per original code). Adapt property names.
//                    if (codeDefine.CodeDefineFormulaId == null)
//                    {
//                        throw new MissingMethodException("Formula id not set for formula based generation.");
//                    }

//                    // Load formula object - replace GetFormulaById with your actual access method
//                    var formula = GetFormulaById(codeDefine.CodeDefineFormulaId, loginDto);
//                    if (formula == null)
//                        throw new NotFoundException("Formula not found.");

//                    string finalExpression;
//                    if (formula.FormulaType == 0) // original code threw if Formula type was 0 (strange)
//                    {
//                        throw new MissingMethodException("Only formula type is supported..");
//                    }
//                    else if (formula.FormulaType == 1) // Function
//                    {
//                        if (formula.DataType == 0 || formula.DataType == 2)
//                        {
//                            if (loginDto.DatabaseType == 0) // SQL Server
//                                finalExpression = $"ISNULL({formula.FunctionName},0)";
//                            else // Oracle
//                                finalExpression = $"NVL({formula.FunctionName},0)";
//                        }
//                        else
//                        {
//                            throw new MissingMethodException("Only number datatype supported..");
//                        }
//                    }
//                    else
//                    {
//                        finalExpression = formula.Expression;
//                    }

//                    // Execute select to get the numeric value
//                    string selectSql = loginDto.DatabaseType == 0
//                        ? $"SELECT {finalExpression} AS EntityId"
//                        : $"SELECT {finalExpression} AS EntityId FROM dual";

//                    var rows = ExceuteSelectQuery<EntityDTO>(selectSql, loginDto);
//                    if (rows != null && rows.Count > 0)
//                    {
//                        lastNumber = rows[0].EntityId;
//                    }

//                    lastNumber = lastNumber + 1;
//                    if (lastNumber > codeDefine.CodeDefineCatEndNo)
//                        throw new MethodNotAllowedException("Check the setting in Code Define table,lastnumber is greater than end number");

//                    resultCode = BuildFromNumber(lastNumber);

//                    // Persist CATLASTNO
//                    string updateSql = $"UPDATE MCODEDEFINE SET CATLASTNO = {lastNumber} WHERE CodeDefineId = {codeDefine.CodeDefineId}";
//                    ExecuteNonQueryAsync(loginDto, updateSql).GetAwaiter().GetResult();
//                }
//                else
//                {
//                    throw new MethodNotAllowedException("Unsupported CodeDefineGenerationType.");
//                }

//                return resultCode;
//            }
//            catch
//            {
//                throw;
//            }
//        }

//        #region --- Helper placeholders - adapt these to your actual QueryExecutor implementation ---

//        // QueryListAsync: returns list of DTOs for a SQL and parameters.
//        // Replace with your IQueryExecutor method that returns a collection.
//        private async Task<IList<T>> QueryListAsync<T>(LoginDTO loginDto, string sql, object parameters)
//        {
//            // TODO: Replace this with the correct call to your IQueryExecutor that returns a list.
//            // Example: return await _queryExecutor.QueryAsync<T>(loginDto, sql, parameters);
//            return (await _queryExecutor.QueryAsync<T>(loginDto, sql, parameters)).ToList();
//        }

//        // Execute a non-query (update) - adapt to your QueryExecutor
//        private async Task ExecuteNonQueryAsync(LoginDTO loginDto, string sql)
//        {
//            // TODO: Replace with whatever method executes an update/insert (and does not return rows)
//            await _queryExecutor.ExecuteAsync(loginDto, sql, null!);
//        }

//        // Execute a select and return a list synchronously (used in GenerateCode for formula path)
//        private IList<T> ExceuteSelectQuery<T>(string sql, LoginDTO loginDto)
//        {
//            // TODO: Replace with synchronous or asynchronous call as per your environment.
//            // Here I call async and wait because original code expects synchronous call.
//            return QueryListAsync<T>(loginDto, sql, null!).GetAwaiter().GetResult();
//        }

//        // Load formula by id - placeholder. Replace with your real method or query.
//        private dynamic GetFormulaById(int formulaId, LoginDTO loginDto)
//        {
//            // TODO: implement loading of formula object from DB
//            // For example:
//            // return _queryExecutor.QuerySingleAsync<Unisoft.FrameworkDAL.Poco.Formula.Formula>(loginDto, "SELECT ... WHERE Id=@id", new { id = formulaId }).GetAwaiter().GetResult();
//            throw new NotImplementedException("GetFormulaById must be implemented to load the formula metadata.");
//        }

//        #endregion
//    }
//}