using GB5Shared.Addon;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EntityHandler;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using PayRollDAL.CustomeCode.PayRevision;
using PayRollDAL.DTO.PayRevision;
using PayRollDAL.Query.PayRevision;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Common;
using System.Linq;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollBLL.PayRevision
{
    public class PayRevisionBLL : IPayRevisionBLL
    {
        private readonly IPayRevisionDAL _PayRevisionDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly BaseEntityAppService<PayRevisionDTO> _baseEntityAppService;
        private readonly IQueryExecutor _QueryExecutor;
        private readonly ILogger<PayRevisionBLL> _logger;
        private readonly IAddonService _addonService;

        private const string AddonTable = "TPAYREVISIONADDON";
        private const string AddonFkCol = "PAYREVISIONID";

        public PayRevisionBLL(
            IPayRevisionDAL payRevisionDAL,
            AutoNumber autoNumber,
            IValidation validation,
            BaseEntityAppService<PayRevisionDTO> baseEntityAppService,
            IQueryExecutor queryExecutor,
            ILogger<PayRevisionBLL> logger,
            IAddonService addonService)
        {
            _PayRevisionDAL = payRevisionDAL;
            _AutoNumber = autoNumber;
            _Validation = validation;
            _baseEntityAppService = baseEntityAppService;
            _QueryExecutor = queryExecutor;
            _logger = logger;
            _addonService = addonService;
        }

        public async Task<string> SavePayRevision(PayRevisionDTO dto, LoginDTO login, CancellationToken ct = default)
        {
            if (dto == null) throw new ArgumentNullException(nameof(dto));

            var trans = await _QueryExecutor.BeginTransactionAsync(login);
            try
            {
                string message = await SavePayRevisionInternal(dto, login, trans, ct);
                await _QueryExecutor.CommitAsync(trans);
                return message;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        public async Task<string> SavePayRevision(List<PayRevisionDTO> dtos, LoginDTO login, CancellationToken ct = default)
        {
            if (dtos == null || dtos.Count == 0) throw new ArgumentNullException(nameof(dtos));

            var trans = await _QueryExecutor.BeginTransactionAsync(login);
            try
            {
                var messages = new List<string>();
                foreach (var dto in dtos)
                {
                    messages.Add(await SavePayRevisionInternal(dto, login, trans, ct));
                }

                await _QueryExecutor.CommitAsync(trans);

                _logger.LogInformation("Bulk PayRevision save completed by user {UserId} — Count={Count}",
                    login.UserId, dtos.Count);

                return string.Join("; ", messages);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        // Saves a single PayRevisionDTO within an already-open transaction — shared by both the
        // single-object and bulk SavePayRevision overloads so multiple revisions in one request
        // commit/rollback together instead of each opening its own transaction.
        private async Task<string> SavePayRevisionInternal(PayRevisionDTO dto, LoginDTO login, DbTransaction trans, CancellationToken ct)
        {
            AutoNumberDTO? autoNumberDTO = null;
            bool isNew = dto.PayRevisionId == 0;

            // An omitted date (default(DateTime) = 0001-01-01) would otherwise hit SQL Server's
            // DATETIME floor (1/1/1753) on save. Default to the same open-ended sentinel the
            // rest of this module uses (see RESTORE_PREVIOUS_REVISION) instead of erroring.
            if (dto.PayRevisionEffectiveTo == default)
                dto.PayRevisionEffectiveTo = new DateOnly(9999, 12, 31);
            if (dto.PayRevisionReferenceDate == default)
                dto.PayRevisionReferenceDate = dto.PayRevisionRevisionDate;

            // Update path: UPDATE_PAYREVISION rewrites every column, including these two
            // NOT NULL business identifiers. A client that omits them on update (rather than
            // regenerating a fresh number) must not have them nulled out — preserve the
            // existing stored values instead.
            if (!isNew && (string.IsNullOrEmpty(dto.PayRevisionPayRevisionNumber)
                || string.IsNullOrEmpty(dto.PayRevisionPayReferenceNumber)))
            {
                var existing = await _PayRevisionDAL.GetPayRevision(dto.PayRevisionId, login, ct);
                if (existing != null)
                {
                    if (string.IsNullOrEmpty(dto.PayRevisionPayRevisionNumber))
                        dto.PayRevisionPayRevisionNumber = existing.PayRevisionPayRevisionNumber;
                    if (string.IsNullOrEmpty(dto.PayRevisionPayReferenceNumber))
                        dto.PayRevisionPayReferenceNumber = existing.PayRevisionPayReferenceNumber;
                }
            }

            ValidatePayRevision(dto);

            try
            {
                dto.PayRevisionModifiedById = login.UserId;
                dto.PayRevisionModifiedOn = DateTime.UtcNow;

                if (isNew)
                {
                    autoNumberDTO = await _AutoNumber.GetNumberAsync(1, "PayRevision", login);
                    dto.PayRevisionId = autoNumberDTO.StartNumber;
                    dto.PayRevisionCreatedById = login.UserId;
                    dto.PayRevisionCreatedOn = DateTime.UtcNow;

                    // Auto-generate the revision number from the AutoNumber-issued PayRevisionId
                    // (unique by construction) rather than the current date — a date-only stamp
                    // collides whenever two revisions are created the same day. Same
                    // "{prefix}-{year}-{id:Dn}" convention as ApiRequestBLL.GenerateRequestCode.
                    // PayRevisionPayReferenceNumber is NOT auto-generated — it's whatever the
                    // client sends (or stays empty/null if they don't send one).
                    // Format: REV-{year}-{last 3 digits of |PayRevisionId|} — e.g.
                    // PayRevisionId -1499998800 -> "REV-2026-800". NOT the full id: PayRevisionId
                    // is minted by the "PayRevision" AutoNumber block (a PK sequence starting
                    // from a large offset), so its full magnitude is meaningless for display.
                    if (string.IsNullOrEmpty(dto.PayRevisionPayRevisionNumber))
                        dto.PayRevisionPayRevisionNumber = $"REV-{DateTime.UtcNow:yyyy}-{Math.Abs(dto.PayRevisionId) % 1000}";
                }

                // ✅ Direct Save - Main Record
                int rows = isNew
                    ? await _PayRevisionDAL.SavePayRevision(dto, login, trans, ct)
                    : await _PayRevisionDAL.UpdatePayRevision(dto, login, trans, ct);

                if (rows <= 0)
                    throw new Exception(isNew ? ErrorResponse.SaveErrorMessage : ErrorResponse.UpdateErrorMessage);

                _logger.LogInformation("Main record saved. Id: {PayRevisionId}, EmployeeId: {EmployeeId}",
                    dto.PayRevisionId, dto.EmployeeId);

                // ✅ Save Addon records
                if (dto.PayRevisionAddon != null && dto.PayRevisionAddon.Any())
                {
                    foreach (var addon in dto.PayRevisionAddon)
                    {
                        await _PayRevisionDAL.SavePayRevisionAddon(dto.PayRevisionId, addon.FeAddon, login, trans, ct);
                    }
                    _logger.LogInformation("Addon records saved for PayRevisionId: {PayRevisionId}", dto.PayRevisionId);
                }
                else
                {
                    _logger.LogInformation("No PayRevisionAddon supplied in request for PayRevisionId: {PayRevisionId}", dto.PayRevisionId);
                }

                // ✅ Expire prior overlapping revision - FIXED: Pass correct parameters
                await ExpirePreviousRevisionAsync(dto, login, trans, ct);

                _logger.LogInformation("PayRevision {Action} by user {UserId} — Id={PayRevisionId}",
                    isNew ? "created" : "updated", login.UserId, dto.PayRevisionId);

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.PayRevisionPayRevisionNumber}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.PayRevisionPayRevisionNumber}";
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error saving PayRevision. Rolling back.");
                if (autoNumberDTO != null)
                    await _AutoNumber.RollbackAutoNumber("PayRevision", autoNumberDTO.StartNumber, login);
                throw;
            }
        }
        private static void ValidatePayRevision(PayRevisionDTO dto)
        {
            if (dto.PayRevisionRevisionDate == default)
                throw new ArgumentException("Revision Date is required.");
            if (dto.PayRevisionEffectiveFrom == default)
                throw new ArgumentException("Effective From date is required.");
            if (dto.PayRevisionEffectiveTo != default &&
                dto.PayRevisionEffectiveTo < dto.PayRevisionEffectiveFrom)
                throw new ArgumentException("Effective To must be on or after Effective From.");

            if (dto.EmployeeId == 0)
                throw new ArgumentException("EmployeeId is required.");

            // PAYREFERENCENUMBER is NOT NULL and is not auto-generated (unlike PayRevisionNumber) —
            // it must come from the caller. Validated here, after the update-path "preserve existing
            // value" fill-in above, so this only rejects a genuinely missing value on create.
            if (string.IsNullOrWhiteSpace(dto.PayRevisionPayReferenceNumber))
                throw new ArgumentException("Reference Number is required.");
        }
        private async Task ExpirePreviousRevisionAsync(
            PayRevisionDTO dto, LoginDTO login, DbTransaction trans, CancellationToken ct)
        {
            try
            {
                _logger.LogInformation("Expiring previous revisions for EmployeeId: {EmployeeId}", dto.EmployeeId);

                // Get previous revisions
                var previousRevisions = await _PayRevisionDAL.GetPreviousRevisionByScope(dto, login, trans, ct);

                if (previousRevisions != null && previousRevisions.Any())
                {
                    foreach (var prev in previousRevisions.Where(p => p.PayRevisionId != dto.PayRevisionId))
                    {
                        var expireDate = dto.PayRevisionEffectiveFrom.AddDays(-1);
                        await _PayRevisionDAL.UpdatePreviousRevision(prev.PayRevisionId, expireDate, login, trans, ct);
                        _logger.LogInformation("Expired previous revision: {PayRevisionId}", prev.PayRevisionId);
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "Failed to expire previous revisions, but continuing");
                // Don't throw - this is a non-critical operation
            }
        }

        public async Task<PayRevisionDTO> GetPayRevision(int PayRevisionId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _PayRevisionDAL.GetPayRevision(PayRevisionId, LoginDTO, ct);
                if (result != null)
                {
                    var feAddon = await _addonService.GetAddonJsonAsync(AddonTable, AddonFkCol, PayRevisionId, LoginDTO, ct);
                    result.PayRevisionAddon = new List<PayRevisionAddon>
                    {
                        new PayRevisionAddon { Id = PayRevisionId, FeAddon = feAddon }
                    };
                }
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeletePayRevision(int PayRevisionId, LoginDTO login, CancellationToken ct = default)
        {
            int processCount = await _PayRevisionDAL.CheckPayProcessExists(PayRevisionId, login, ct);
            if (processCount > 0)
                throw new ValidationException("Cannot delete: pay processing has already been completed for this revision.");

            var dto = await _PayRevisionDAL.GetPayRevision(PayRevisionId, login, ct)
                      ?? throw new ValidationException("Revision not found.");

            var trans = await _QueryExecutor.BeginTransactionAsync(login);
            try
            {
                int newerCount = await _PayRevisionDAL.CheckNewerRevisionExists(dto, login, trans, ct);
                if (newerCount > 0)
                    throw new ValidationException(
                        "Cannot delete this revision as a newer revision exists. Delete the newest revision first.");

                var predecessor = await _PayRevisionDAL.GetPredecessorRevision(dto, login, trans, ct);

                await _PayRevisionDAL.DeletePayRevisionAddon(PayRevisionId, login, trans, ct);
                await _PayRevisionDAL.DeletePayRevision(PayRevisionId, login, trans, ct);

                if (predecessor != null)
                    await _PayRevisionDAL.RestorePreviousRevision(predecessor.PayRevisionId, login, trans, ct);

                await _QueryExecutor.CommitAsync(trans);

                _logger.LogInformation("PayRevision {PayRevisionId} deleted by user {UserId}", PayRevisionId, login.UserId);
                return SuccessResponse.DeleteSuccessMessage;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        public async Task<string> GetSelectListPayRevision(
            int FirstNumber, int MaxResult, CriteriaDTO criteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _PayRevisionDAL.GetSelectListPayRevision(FirstNumber, MaxResult, criteriaDTO, LoginDTO, ct);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListPayRevision", ex);
            }
        }

        public async Task<IEnumerable<PayRevisionDTO>> GetPayRevisionList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var list = (await _PayRevisionDAL.GetPayRevisionList(CriteriaDTO, LoginDTO, ct))?.ToList()
                           ?? new List<PayRevisionDTO>();

                if (list.Count > 0)
                {
                    var ids = list.Select(x => x.PayRevisionId);
                    var addons = await _addonService.GetAddonJsonBatchAsync(AddonTable, AddonFkCol, ids, LoginDTO, ct);
                    var addonMap = addons.ToDictionary(a => a.Id, a => a.FeAddon);

                    foreach (var item in list)
                    {
                        addonMap.TryGetValue(item.PayRevisionId, out var feAddon);
                        item.PayRevisionAddon = new List<PayRevisionAddon>
                        {
                            new PayRevisionAddon { Id = item.PayRevisionId, FeAddon = feAddon! }
                        };
                    }
                }

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

        public async Task<object> GetPayRevisionByField(
            string FieldName,
            int FieldId,
            int Nature,
            LoginDTO LoginDTO,
            CancellationToken ct = default)
        {
            try
            {
                if (LoginDTO == null)
                    throw new ArgumentNullException(nameof(LoginDTO), "Login information is required");

                if (string.IsNullOrEmpty(FieldName))
                    throw new ArgumentException("FieldName cannot be null or empty");

                int ouId = 0;
                Type loginType = LoginDTO.GetType();

                var properties = loginType.GetProperties();
                _logger.LogDebug("LoginDTO properties: {Properties}", string.Join(", ", properties.Select(p => p.Name)));

                string[] possiblePropertyNames = { "WorkOUId", "OUID", "OuId", "OUId", "WorkOuId" };

                foreach (var propName in possiblePropertyNames)
                {
                    var prop = loginType.GetProperty(propName);
                    if (prop != null)
                    {
                        try
                        {
                            var value = prop.GetValue(LoginDTO);
                            if (value != null)
                            {
                                ouId = Convert.ToInt32(value);
                                if (ouId != 0)
                                {
                                    _logger.LogInformation("Found OUId from property '{PropertyName}': {OUId}", propName, ouId);
                                    break;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            _logger.LogWarning(ex, "Failed to get value from property '{PropertyName}'", propName);
                        }
                    }
                }

                if (ouId == 0)
                {
                    foreach (var prop in properties)
                    {
                        if (prop.Name.Contains("OU") || prop.Name.Contains("Ou"))
                        {
                            try
                            {
                                var value = prop.GetValue(LoginDTO);
                                if (value != null && value is int intValue && intValue != 0)
                                {
                                    ouId = intValue;
                                    _logger.LogInformation("Found OUId from property '{PropertyName}': {OUId}", prop.Name, ouId);
                                    break;
                                }
                            }
                            catch { /* Ignore */ }
                        }
                    }
                }

                if (ouId == 0)
                {
                    _logger.LogError("Could not find OUId in LoginDTO. Available properties: {Properties}",
                        string.Join(", ", properties.Select(p => $"{p.Name} ({p.PropertyType.Name})")));
                    throw new ArgumentException("Invalid Organization Unit ID. Could not determine OUId from login information.");
                }

                var validFields = new[] { "Employee", "PayGroup", "PayConfiguration" };
                if (!validFields.Contains(FieldName))
                {
                    throw new ArgumentException($"Invalid FieldName. Must be one of: {string.Join(", ", validFields)}");
                }

                var results = await _PayRevisionDAL.GetPayRevisionByField(
                    FieldName,
                    FieldId,
                    Nature,
                    LoginDTO,
                    ouId,
                    ct);

                if (results != null && results.Any())
                {
                    foreach (var result in results)
                    {
                        try
                        {
                            var feAddon = await _addonService.GetAddonJsonAsync(
                                AddonTable,
                                AddonFkCol,
                                result.PayRevisionId,
                                LoginDTO,
                                ct);

                            result.PayRevisionAddon = new List<PayRevisionAddon>
                            {
                                new PayRevisionAddon {
                                    Id = result.PayRevisionId,
                                    FeAddon = feAddon?.ToString() ?? "{}",
                                    GetAddon = "{}",
                                    Attributes = null,
                                    RepotAddon = null
                                }
                            };
                        }
                        catch (Exception ex)
                        {
                            _logger.LogWarning(ex,
                                "Failed to load addon for PayRevisionId: {PayRevisionId}",
                                result.PayRevisionId);

                            result.PayRevisionAddon = new List<PayRevisionAddon>
                            {
                                new PayRevisionAddon {
                                    Id = result.PayRevisionId,
                                    FeAddon = "{}",
                                    GetAddon = "{}",
                                    Attributes = null,
                                    RepotAddon = null
                                }
                            };
                        }
                    }
                }

                _logger.LogInformation(
                    "GetPayRevisionByField completed - FieldName: {FieldName}, FieldId: {FieldId}, OUId: {OUId}, Count: {Count}",
                    FieldName, FieldId, ouId, results?.Count ?? 0);

                if (FieldName == "Employee")
                {
                    return results?.FirstOrDefault();
                }
                else
                {
                    return results ?? new List<PayRevisionDTO>();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "Error in GetPayRevisionByField - FieldName: {FieldName}, FieldId: {FieldId}",
                    FieldName, FieldId);
                throw;
            }
        }

        public async Task<string> GetSelectListPayRevisionEmployee(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string PayRevisionPayRevisionNumber = "";
                string EmployeeCode = "";
                string EmployeeName = "";
                int BizTransactionTypeId = -1;
                int Applicable = -1;
                int Nature = 0;

                if (CriteriaDTO != null && CriteriaDTO.SectionCriteriaList != null)
                {
                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList.Count; i++)
                    {
                        if (CriteriaDTO.SectionCriteriaList[i] == null ||
                            CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList == null)
                        {
                            continue;
                        }

                        for (int j = 0; j < CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList.Count; j++)
                        {
                            var Attr = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j];

                            if (Attr == null || string.IsNullOrEmpty(Attr.FieldName))
                            {
                                continue;
                            }

                            switch (Attr.FieldName.ToLower())
                            {
                                case "payrevisionpayrevisionnumber":
                                    PayRevisionPayRevisionNumber = GetStringValue(Attr.FieldValue);
                                    break;
                                case "employeecode":
                                    EmployeeCode = GetStringValue(Attr.FieldValue);
                                    break;
                                case "employeename":
                                    EmployeeName = GetStringValue(Attr.FieldValue);
                                    break;
                                case "biztransactiontypeid":
                                case "biztransactiontype.id":
                                    BizTransactionTypeId = GetIntValue(Attr.FieldValue);
                                    break;
                                case "applicable":
                                    Applicable = GetIntValue(Attr.FieldValue);
                                    break;
                                case "nature":
                                    Nature = GetIntValue(Attr.FieldValue);
                                    break;
                            }
                        }
                    }
                }

                // GB4 parity: BizTransactionTypeId is not required — it defaults to -1 and, if
                // left unset, the query below simply matches no rows rather than erroring.
                // Applicable/Nature are caller-supplied: the DAL only applies the employee
                // (MEMPLOYEE) join and EmployeeCode/EmployeeName filter when Applicable == 5.

                // Get the result from DAL
                IEnumerable<TempPayRevisionDTO> Result = await _PayRevisionDAL.GetSelectListPayRevisionEmployee(
                    BizTransactionTypeId,
                    Applicable,
                    Nature,
                    PayRevisionPayRevisionNumber,
                    EmployeeCode,
                    EmployeeName,
                    LoginDTO);

                // Serialize to JSON string and return
                return JsonConvert.SerializeObject(Result);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<List<PayRevisionDTO>> ArrearCheck(List<PayRevisionDTO> PayRevisionDTOs, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _PayRevisionDAL.ArrearCheck(PayRevisionDTOs, LoginDTO, ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        private static string GetStringValue(object FieldValue)
        {
            if (FieldValue == null) return "";

            if (FieldValue is JsonElement Element)
            {
                return Element.ValueKind switch
                {
                    JsonValueKind.String => Element.GetString() ?? "",
                    JsonValueKind.Number => Element.ToString(),
                    JsonValueKind.True => "true",
                    JsonValueKind.False => "false",
                    _ => ""
                };
            }

            return Convert.ToString(FieldValue) ?? "";
        }
        private static int GetIntValue(object FieldValue, int DefaultValue = -1)
        {
            if (FieldValue == null) return DefaultValue;

            if (FieldValue is JsonElement Element)
            {
                if (Element.ValueKind == JsonValueKind.Number && Element.TryGetInt32(out int NumberResult))
                    return NumberResult;

                if (Element.ValueKind == JsonValueKind.String && int.TryParse(Element.GetString(), out int StringResult))
                    return StringResult;

                return DefaultValue;
            }

            return int.TryParse(Convert.ToString(FieldValue), out int Result)
                ? Result
                : DefaultValue;
        }
    }
}