using FrameworkDAL.DTO.Lock;
using FrameworkDAL.Query.Lock;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.Lock
{
    public class LockDAL : ILockDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IPickListExecutor _PickListExecutor;

        public LockDAL(IQueryExecutor QueryExecutor, IPickListExecutor PickListExecutor)
        {
            _QueryExecutor = QueryExecutor;
            _PickListExecutor = PickListExecutor;
        }

        public async Task<IEnumerable<LockPicklistDTO>> GetSelectListLock(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO)
        {
            try
            {
                CriteriaDTO = EnsureDefaultAutoValidationFilter(CriteriaDTO);

                string sql = LockQB.GET_SELECTLIST_LOCK_BASE;
                sql = await ApplyLockCriteria(sql, CriteriaDTO, LoginDTO);
                sql = await ApplyLockCriteria(sql, LoginDTO.UserCriteriaDTO, LoginDTO);
                sql += LockQB.GET_SELECTLIST_LOCK_PAGING_SUFFIX;

                var parameter = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                return await _QueryExecutor.QueryAsync<LockPicklistDTO>(LoginDTO, sql, parameter);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetSelectListLockCount(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                CriteriaDTO = EnsureDefaultAutoValidationFilter(CriteriaDTO);

                string sql = LockQB.GET_SELECTLIST_LOCK_COUNT_BASE;
                sql = await ApplyLockCriteria(sql, CriteriaDTO, LoginDTO);
                sql = await ApplyLockCriteria(sql, LoginDTO.UserCriteriaDTO, LoginDTO);

                return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, null);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GenericLockValidationByProgram(
            int LockId,
            GenericLockCriteriaDTO GenericLockCriteriaDTO,
            LoginDTO LoginDTO,
            CancellationToken ct)
        {
            try
            {
                // GB4: IsExcelOutput == 0 → WriteGenericLockInExcel (EPPlus workbook generation over
                // the same per-validation dynamic SQL). Not ported — see ILockDAL doc comment. Failing
                // loudly here is safer than silently falling back to the text branch and returning a
                // response shape the caller didn't ask for.
                if (GenericLockCriteriaDTO.IsExcelOutput == 0)
                {
                    throw new NotSupportedException(
                        "GenericLockValidate's Excel output (IsExcelOutput = 0) is not yet migrated to GB5. " +
                        "Call with IsExcelOutput = 1 for the text/log output, which is available.");
                }

                var lockValidations = (await _QueryExecutor
                    .QueryAsync<LockValidationDTO>(LoginDTO, LockQB.GET_LOCKVALIDATIONS_BY_LOCKID, new { LockId })
                    .ConfigureAwait(false))
                    .ToList();

                var log = new StringBuilder();
                bool anyValidationFailed = false;

                for (int i = 0; i < lockValidations.Count; i++)
                {
                    var validation = lockValidations[i];

                    if (i == 0)
                    {
                        AppendLine(log,
                            $"Log generated On:- {DateTime.Now:dd-MM-yyyy HH:mm:ss}\r\n" +
                            $"Lock Code:- {validation.LockCode}\r\n" +
                            $"Lock Description:- {validation.LockDescription}\r\n" +
                            $"For Database:- {LoginDTO.DatabaseName}\r\n" +
                            $"Lock Created By:- {LoginDTO.UserCode}\r\n");
                    }

                    string sql = SubstituteLockExpressionParams(validation.LockValidationExpression, GenericLockCriteriaDTO);

                    // GB4 parity: LockValidation.Expression is admin-authored SQL stored in
                    // MLOCKVALIDATION.EXPRESSION (configured via the Lock/LockValidation admin
                    // screens, not end-user input) and is intentionally executed dynamically —
                    // same trust boundary as GB4's NHibernate ExceuteSelectQuery(Sql, LoginDTO).
                    var rows = (await _QueryExecutor.QueryAsync<dynamic>(LoginDTO, sql).ConfigureAwait(false))
                        .Select(r => (IDictionary<string, object>)r)
                        .ToList();

                    if (rows.Count > 0)
                    {
                        anyValidationFailed = true;
                        var columns = rows[0].Keys.ToList();

                        AppendLine(log, $"{i + 1}.{validation.LockValidationDescription}\r\n\r\n");
                        AppendLine(log, string.Concat(columns.Select(c => c.PadRight(30))));

                        foreach (var row in rows)
                        {
                            AppendLine(log, string.Concat(columns.Select(c =>
                                (row[c]?.ToString() ?? "Null").PadRight(30))));
                        }
                    }

                    AppendLine(log, "\r\n\r\n");
                }

                return anyValidationFailed ? log.ToString() : "";
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: LogFrameDAL.WriteLog(path, file, message, false) appends `message` followed
        // by one TextWriter.WriteLine newline. Each GB4 WriteLog call corresponds to one AppendLine
        // call here, in the same order, so the concatenated result matches the GB4 file byte-for-byte
        // (module CRLF conventions aside).
        private static void AppendLine(StringBuilder sb, string message) => sb.Append(message).Append("\r\n");

        // GB4 parity: CommonFunctionFrameDAL.ChangedByNamedParam — plain textual ":name" → value
        // substitution into the admin-authored Expression SQL (not a parameterized query; the
        // Expression itself decides quoting/formatting for each placeholder, exactly as GB4 does).
        private static string SubstituteLockExpressionParams(string expression, GenericLockCriteriaDTO criteria)
        {
            string[] paramNames =
            {
                "ouid", "payperiodid", "payconfigid",
                "partyid", "partybranchid", "documentdetailid",
                "documentid", "paygroupid", "attendancefromdate", "attendancetodate"
            };
            object[] values =
            {
                criteria.OUId, criteria.PayPeriodId, criteria.PayConfigurationId,
                criteria.PartyId, criteria.PartyBranchId, criteria.DocumentDetailId,
                criteria.DocumentId, criteria.PayGroupId,
                criteria.AttendanceFromDate.ToString("dd-MMM-yyyy"),
                criteria.AttendanceToDate.ToString("dd-MMM-yyyy")
            };

            string formed = expression;
            for (int i = 0; i < values.Length; i++)
            {
                formed = formed.Replace(":" + paramNames[i], values[i].ToString());
            }
            return formed;
        }

        private async Task<string> ApplyLockCriteria(string sql, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            string[,] criteriaMap = new string[,]
            {
                { "Id",               "LOCKID" },
                { "Code",             "LOCKCODE" },
                { "Name",             "LOCKNAME" },
                { "IsAutoValidation", "ISAUTOVALIDATION" }
            };

            return await _PickListExecutor.ApplyCriteria(sql, criteriaMap, criteriaDTO, loginDTO);
        }

        /// <summary>
        /// Ports GB4's LockFrameBLL.GetSelectListLock behavior: unless the caller's
        /// criteria already filters explicitly on IsAutoValidation, force
        /// IsAutoValidation = 0 so auto-validation locks never leak into the general
        /// picklist (GB4 Bug #37538 / Feature #37406).
        /// </summary>
        private static CriteriaDTO EnsureDefaultAutoValidationFilter(CriteriaDTO criteriaDTO)
        {
            bool alreadyFiltered = criteriaDTO?.SectionCriteriaList != null &&
                criteriaDTO.SectionCriteriaList.Any(section =>
                    section?.AttributesCriteriaList != null &&
                    section.AttributesCriteriaList.Any(attr =>
                        string.Equals(attr?.FieldName, "IsAutoValidation", StringComparison.OrdinalIgnoreCase)));

            if (alreadyFiltered)
            {
                return criteriaDTO;
            }

            var defaultAttribute = new AttributesCriteriaDTO
            {
                FieldName = "IsAutoValidation",
                OperationType = CriteriaDTO.OperationType.Equal,
                FieldValue = "0",
                JoinType = CriteriaDTO.AttributeJoinOperationType.And
            };

            if (criteriaDTO?.SectionCriteriaList != null && criteriaDTO.SectionCriteriaList.Count > 0)
            {
                var firstSection = criteriaDTO.SectionCriteriaList[0];
                firstSection.AttributesCriteriaList ??= new List<AttributesCriteriaDTO>();

                if (firstSection.AttributesCriteriaList.Count > 0)
                {
                    firstSection.AttributesCriteriaList[^1].JoinType = CriteriaDTO.AttributeJoinOperationType.And;
                }

                firstSection.AttributesCriteriaList.Add(defaultAttribute);
                return criteriaDTO;
            }

            return new CriteriaDTO
            {
                SectionCriteriaList = new List<SectionCriteriaDTO>
                {
                    new SectionCriteriaDTO { AttributesCriteriaList = new List<AttributesCriteriaDTO> { defaultAttribute } }
                }
            };
        }
    }
}