using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
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.Data.Common;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;

namespace PayRollDAL.CustomCode.PayRevision
{
    public class PayRevisionDAL : IPayRevisionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        private readonly ILogger<PayRevisionDAL> _logger;

        public PayRevisionDAL(IQueryExecutor queryExecutor, IValidation validation, ILogger<PayRevisionDAL> logger)
        {
            _QueryExecutor = queryExecutor;
            _Validation    = validation;
            _logger          = logger;
        }

        public async Task<PayRevisionDTO> GetPayRevision(int PayRevisionId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<PayRevisionDTO>(
                    LoginDTO,
                    PayRevisionQB.GET_PAYREVISION,
                    new { PayRevisionId },
                    cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectListPayRevision(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<PayRevisionPicklistDTO>(
                    LoginDTO,
                    PayRevisionQB.GET_SELECTLIST_PAYREVISION,
                    new { firstnumber = FirstNumber, maxresult = MaxResult },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<int> SavePayRevision(PayRevisionDTO PayRevisionDTO, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, PayRevisionQB.SAVE_PAYREVISION, PayRevisionDTO, trans, ct);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }
        public async Task<int> UpdatePayRevision(PayRevisionDTO PayRevisionDTO, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, PayRevisionQB.UPDATE_PAYREVISION, PayRevisionDTO, trans, ct);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }
        // TPAYREVISIONADDON is a fixed-name table whose column set grows over time (an admin adds
        // a new payroll addon field via ALTER TABLE — see AdditionDeductionBLL.cs in GB4, which did
        // the same ALTER TABLE alongside a metadata-table insert). Rather than hardcode the column
        // list in C# (which would need a redeploy every time a field is added), the column set is
        // discovered from INFORMATION_SCHEMA.COLUMNS at runtime and cached briefly — this is strictly
        // more robust than GB4's MADDONFIELDS metadata table, which could drift out of sync with the
        // real columns since the ALTER TABLE and the metadata insert were separate statements.
        // Every discovered column name still goes through this cache before ever being interpolated
        // into SQL text (identifiers can't be parameterized — only values can — so the allowlist
        // step itself is unavoidable; only its *source* is now live schema instead of a literal list).
        private sealed class AddonColumnMeta
        {
            public string ColumnName { get; set; } = "";
            public string DataType { get; set; } = "";
            public int? CharacterMaximumLength { get; set; }
        }

        private static readonly SemaphoreSlim _addonSchemaLock = new(1, 1);
        private static List<AddonColumnMeta>? _addonSchemaCache;
        private static DateTime _addonSchemaCacheExpiresAtUtc = DateTime.MinValue;
        private static readonly TimeSpan AddonSchemaCacheTtl = TimeSpan.FromMinutes(5);

        private async Task<List<AddonColumnMeta>> GetAddonColumnsAsync(LoginDTO login, CancellationToken ct)
        {
            if (_addonSchemaCache != null && DateTime.UtcNow < _addonSchemaCacheExpiresAtUtc)
                return _addonSchemaCache;

            await _addonSchemaLock.WaitAsync(ct);
            try
            {
                if (_addonSchemaCache != null && DateTime.UtcNow < _addonSchemaCacheExpiresAtUtc)
                    return _addonSchemaCache;

                var columns = await _QueryExecutor.QueryAsync<AddonColumnMeta>(
                    login, PayRevisionQB.GET_PAYREVISIONADDON_COLUMNS, cancellationToken: ct);

                _addonSchemaCache = columns?.ToList() ?? new List<AddonColumnMeta>();
                _addonSchemaCacheExpiresAtUtc = DateTime.UtcNow.Add(AddonSchemaCacheTtl);
                return _addonSchemaCache;
            }
            finally
            {
                _addonSchemaLock.Release();
            }
        }

        public async Task<int> SavePayRevisionAddon(long payRevisionId, string feAddon, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                var columns = await GetAddonColumnsAsync(LoginDTO, ct);
                var fields  = ParseAddonFields(feAddon, columns);

                var parameters = new DynamicParameters();
                parameters.Add("@PayRevisionId", payRevisionId);

                if (fields.Count == 0)
                {
                    // No addon fields supplied — ensure a placeholder row exists (matching
                    // GB4's one-row-per-revision behavior) without touching any column values.
                    // MULTIEMP is NOT NULL but has a '' default constraint, so omitting every
                    // column from the INSERT list is safe.
                    return await _QueryExecutor.ExecuteAsync(
                        LoginDTO, PayRevisionQB.INSERT_PAYREVISION_ADDON_PLACEHOLDER, parameters, trans, ct);
                }

                var setClauses    = new List<string>();
                var insertColumns = new List<string> { "PAYREVISIONID" };
                var insertValues  = new List<string> { "@PayRevisionId" };

                foreach (var (column, value) in fields)
                {
                    string paramName = "@" + column;
                    setClauses.Add($"{column} = {paramName}");
                    insertColumns.Add(column);
                    insertValues.Add(paramName);
                    parameters.Add(paramName, value);
                }

                string sql = $@"
IF EXISTS (SELECT 1 FROM TPAYREVISIONADDON WHERE PAYREVISIONID = @PayRevisionId)
BEGIN
    UPDATE TPAYREVISIONADDON
    SET    {string.Join(", ", setClauses)}
    WHERE  PAYREVISIONID = @PayRevisionId
END
ELSE
BEGIN
    INSERT INTO TPAYREVISIONADDON ({string.Join(", ", insertColumns)})
    VALUES ({string.Join(", ", insertValues)})
END;";

                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters, trans, ct);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error saving addon for PayRevisionId: {PayRevisionId}", payRevisionId);
                throw;
            }
        }

        // Parses the client-supplied FeAddon JSON into (ColumnName, Value) pairs, validated
        // against the live column list discovered by GetAddonColumnsAsync. Unknown keys are
        // dropped (and logged) rather than ever reaching SQL text — the column list always
        // comes from the schema, never from the JSON keys directly.
        private List<(string Column, object Value)> ParseAddonFields(string? feAddon, List<AddonColumnMeta> columns)
        {
            var result = new List<(string, object)>();
            if (string.IsNullOrWhiteSpace(feAddon)) return result;

            Dictionary<string, JsonElement>? raw;
            try
            {
                raw = System.Text.Json.JsonSerializer.Deserialize<Dictionary<string, JsonElement>>(
                    feAddon, new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
            }
            catch (System.Text.Json.JsonException ex)
            {
                throw new ArgumentException($"FeAddon is not valid JSON: {ex.Message}", nameof(feAddon));
            }
            if (raw == null) return result;

            foreach (var kvp in raw)
            {
                if (kvp.Key.Equals("PAYREVISIONID", StringComparison.OrdinalIgnoreCase))
                    continue; // supplied separately via payRevisionId

                var meta = columns.FirstOrDefault(c => c.ColumnName.Equals(kvp.Key, StringComparison.OrdinalIgnoreCase));
                if (meta == null)
                {
                    _logger.LogWarning("Ignoring unknown TPAYREVISIONADDON field '{Field}' in FeAddon payload.", kvp.Key);
                    continue;
                }

                object? value = ConvertAddonValue(kvp.Value, meta);
                if (value != null)
                    result.Add((meta.ColumnName, value));
            }

            return result;
        }

        private static object? ConvertAddonValue(JsonElement element, AddonColumnMeta meta)
        {
            if (element.ValueKind == JsonValueKind.Null) return null;

            switch (meta.DataType.ToLowerInvariant())
            {
                case "numeric":
                case "decimal":
                case "float":
                case "real":
                case "money":
                case "smallmoney":
                    if (element.ValueKind == JsonValueKind.Number && element.TryGetDecimal(out var num))
                        return num;
                    if (element.ValueKind == JsonValueKind.String && decimal.TryParse(element.GetString(), out var parsedNum))
                        return parsedNum;
                    throw new ArgumentException($"'{meta.ColumnName}' must be numeric.");

                case "int":
                case "bigint":
                case "smallint":
                case "tinyint":
                    if (element.ValueKind == JsonValueKind.Number && element.TryGetInt64(out var i))
                        return i;
                    if (element.ValueKind == JsonValueKind.String && long.TryParse(element.GetString(), out var parsedInt))
                        return parsedInt;
                    throw new ArgumentException($"'{meta.ColumnName}' must be an integer.");

                case "bit":
                    if (element.ValueKind == JsonValueKind.True || element.ValueKind == JsonValueKind.False)
                        return element.GetBoolean();
                    throw new ArgumentException($"'{meta.ColumnName}' must be true/false.");

                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "date":
                    if (element.ValueKind == JsonValueKind.String)
                    {
                        var s = element.GetString();
                        // Frontend sends "0" as the uniform "unset" sentinel for every addon
                        // field regardless of column type (every other FeAddon field defaults
                        // to "0" too — BasTax/Deduction1/PTCC/LOA/...) — not just blank/whitespace.
                        if (string.IsNullOrWhiteSpace(s) || s == "0")
                            return null;
                        if (DateTime.TryParse(s, out var dt))
                            return dt;
                    }
                    throw new ArgumentException($"'{meta.ColumnName}' must be a valid date.");

                default: // nvarchar, varchar, char, nchar, text, ...
                    string? str = element.ValueKind == JsonValueKind.String ? element.GetString() : element.GetRawText();
                    if (str != null && meta.CharacterMaximumLength is int maxLen and > 0 && str.Length > maxLen)
                        throw new ArgumentException($"'{meta.ColumnName}' must be at most {maxLen} character(s) (got {str.Length}).");
                    return str;
            }
        }
        public async Task<string> DeletePayRevision(int PayRevisionId, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                int result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayRevisionQB.DELETE_PAYREVISION,
                    new { PayRevisionId },
                    trans,
                    ct);

                return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }
        public async Task<IEnumerable<PayRevisionDTO>> GetPayRevisionList(CriteriaDTO criteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<PayRevisionDTO>(
                    LoginDTO,
                    PayRevisionQB.GET_PAYREVISION_LIST,
                    null,
                    cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        // ── Business logic support ────────────────────────────────────────────────
        public async Task DeletePayRevisionAddon(int PayRevisionId, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayRevisionQB.DELETE_PAYREVISION_ADDON,
                    new { PayRevisionId },
                    trans,
                    ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<int> CheckPayProcessExists(int PayRevisionId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<int>(
                    LoginDTO,
                    PayRevisionQB.CHECK_PAYPROCESS_EXISTS,
                    new { PayRevisionId },
                    cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<List<PayRevisionDTO>> GetPreviousRevisionByScope(PayRevisionDTO dto, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                string scopeFilter = dto.PayRevisionApplicable switch
                {
                    5 => PayRevisionQB.SCOPE_FILTER_EMPLOYEE,
                    4 => PayRevisionQB.SCOPE_FILTER_DAGROUP,
                    3 => PayRevisionQB.SCOPE_FILTER_PAYGROUP,
                    2 => PayRevisionQB.SCOPE_FILTER_PAYCONFIG,
                    1 => PayRevisionQB.SCOPE_FILTER_OU,
                    _ => string.Empty
                };

                string sql = PayRevisionQB.GET_PREVIOUS_REVISION_BASE + scopeFilter;

                var param = new
                {
                    Applicable    = dto.PayRevisionApplicable,
                    Nature        = dto.PayRevisionNature,
                    EffectiveFrom = dto.PayRevisionEffectiveFrom,
                    dto.EmployeeId,
                    dto.PayConfigurationId,
                    dto.DAGroupId,
                    dto.OUId,
                    dto.PayGroupId
                };

                var result = await _QueryExecutor.QueryAsync<PayRevisionDTO>(LoginDTO, sql, param, trans);
                return result?.ToList() ?? new List<PayRevisionDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task UpdatePreviousRevision(int PayRevisionId, DateOnly effectiveTo, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayRevisionQB.UPDATE_PREVIOUS_REVISION,
                    new { PayRevisionId, EffectiveTo = effectiveTo },
                    trans,
                    ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<List<PayRevisionDTO>> ArrearCheck(List<PayRevisionDTO> PayRevisionDTOs, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var Result = new List<PayRevisionDTO>();

                foreach (var dto in PayRevisionDTOs)
                {
                    string scopeFilter = dto.PayRevisionApplicable switch
                    {
                        5 => PayRevisionQB.SCOPE_FILTER_EMPLOYEE,
                        4 => PayRevisionQB.SCOPE_FILTER_DAGROUP,
                        3 => PayRevisionQB.SCOPE_FILTER_PAYGROUP,
                        2 => PayRevisionQB.SCOPE_FILTER_PAYCONFIG,
                        1 => PayRevisionQB.SCOPE_FILTER_OU,
                        _ => string.Empty
                    };

                    string sql = PayRevisionQB.CHECK_ARREAR_OVERLAP_BASE + scopeFilter + PayRevisionQB.CHECK_ARREAR_OVERLAP_ORDER;

                    var param = new
                    {
                        Applicable = dto.PayRevisionApplicable,
                        Nature     = dto.PayRevisionNature,
                        dto.PayRevisionId,
                        dto.EmployeeId,
                        dto.PayConfigurationId,
                        dto.DAGroupId,
                        dto.OUId,
                        dto.PayGroupId
                    };

                    var matches = await _QueryExecutor.QueryAsync<PayRevisionDTO>(LoginDTO, sql, param, cancellationToken: ct);
                    var matchList = matches?.ToList() ?? new List<PayRevisionDTO>();

                    // GB4 parity: conflict only if the new revision's EffectiveFrom does not
                    // come strictly after the latest matching existing revision's EffectiveFrom.
                    if (matchList.Count > 0 && dto.PayRevisionEffectiveFrom <= matchList[matchList.Count - 1].PayRevisionEffectiveFrom)
                    {
                        Result.AddRange(matchList);
                    }
                }

                return Result;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<int> CheckNewerRevisionExists(PayRevisionDTO dto, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                string scopeFilter = dto.PayRevisionApplicable switch
                {
                    5 => PayRevisionQB.SCOPE_FILTER_EMPLOYEE,
                    4 => PayRevisionQB.SCOPE_FILTER_DAGROUP,
                    3 => PayRevisionQB.SCOPE_FILTER_PAYGROUP,
                    2 => PayRevisionQB.SCOPE_FILTER_PAYCONFIG,
                    1 => PayRevisionQB.SCOPE_FILTER_OU,
                    _ => string.Empty
                };

                string sql = PayRevisionQB.CHECK_NEWER_REVISION_EXISTS_BASE + scopeFilter;

                var param = new
                {
                    Applicable    = dto.PayRevisionApplicable,
                    Nature        = dto.PayRevisionNature,
                    EffectiveFrom = dto.PayRevisionEffectiveFrom,
                    dto.PayRevisionId,
                    dto.EmployeeId,
                    dto.PayConfigurationId,
                    dto.DAGroupId,
                    dto.OUId,
                    dto.PayGroupId
                };

                return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, param, cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<PayRevisionDTO?> GetPredecessorRevision(PayRevisionDTO dto, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                string scopeFilter = dto.PayRevisionApplicable switch
                {
                    5 => PayRevisionQB.SCOPE_FILTER_EMPLOYEE,
                    4 => PayRevisionQB.SCOPE_FILTER_DAGROUP,
                    3 => PayRevisionQB.SCOPE_FILTER_PAYGROUP,
                    2 => PayRevisionQB.SCOPE_FILTER_PAYCONFIG,
                    1 => PayRevisionQB.SCOPE_FILTER_OU,
                    _ => string.Empty
                };

                string sql = PayRevisionQB.GET_PREDECESSOR_REVISION_BASE
                           + scopeFilter
                           + PayRevisionQB.GET_PREDECESSOR_REVISION_ORDER;

                var param = new
                {
                    Applicable    = dto.PayRevisionApplicable,
                    Nature        = dto.PayRevisionNature,
                    EffectiveFrom = dto.PayRevisionEffectiveFrom,
                    dto.PayRevisionId,
                    dto.EmployeeId,
                    dto.PayConfigurationId,
                    dto.DAGroupId,
                    dto.OUId,
                    dto.PayGroupId
                };

                var result = await _QueryExecutor.QueryAsync<PayRevisionDTO>(LoginDTO, sql, param, trans);
                return result?.FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task RestorePreviousRevision( int PayRevisionId, LoginDTO LoginDTO, DbTransaction trans, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayRevisionQB.RESTORE_PREVIOUS_REVISION,
                    new { PayRevisionId },
                    trans,
                    ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<List<PayRevisionDTO>> GetPayRevisionByField(string FieldName,int FieldId,int Nature,LoginDTO LoginDTO,int OUId,CancellationToken ct = default)
        {
            try
            {
                if (LoginDTO == null)
                    throw new ArgumentNullException(nameof(LoginDTO));

                if (OUId == 0)
                    throw new ArgumentException("Invalid Organization Unit ID. OUId cannot be 0.");

                _logger.LogInformation(
                    "Executing GetPayRevisionByField - FieldName: {FieldName}, FieldId: {FieldId}, Nature: {Nature}, OUId: {OUId}",
                    FieldName, FieldId, Nature, OUId);

                // Check if the login has a database name
                var dbNameProp = LoginDTO.GetType().GetProperty("DatabaseName");
                string dbName = dbNameProp?.GetValue(LoginDTO)?.ToString() ?? "Unisoftgb4";
                _logger.LogInformation("DatabaseName: {DatabaseName}", dbName);

                var results = await _QueryExecutor.QueryAsync<PayRevisionDTO>(
                    LoginDTO,
                    PayRevisionQB.GET_PAYREVISION_BY_FIELD,
                    new
                    {
                        FieldName = FieldName,
                        FieldId = FieldId,
                        Nature = Nature,
                        OUId = OUId
                    },
                    cancellationToken: ct);

                var resultList = results?.ToList() ?? new List<PayRevisionDTO>();

                _logger.LogInformation(
                    "GetPayRevisionByField returned {Count} results for FieldName: {FieldName}, FieldId: {FieldId}",
                    resultList.Count, FieldName, FieldId);

                return resultList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "Error in GetPayRevisionByField - FieldName: {FieldName}, FieldId: {FieldId}",
                    FieldName, FieldId);
                throw;
            }
        }
        public async Task<IEnumerable<TempPayRevisionDTO>> GetSelectListPayRevisionEmployee(
      int BizTransactionTypeId,
      int Applicable,
      int Nature,
      string PayRevisionPayRevisionNumber,
      string EmployeeCode,
      string EmployeeName,
      LoginDTO LoginDTO)
        {
            try
            {
                if (Applicable == 5)
                {
                    string Sql = PayRevisionQB.GET_SELECTLIST_PAYREVISION_EMPLOYEE_BASED;

                    var Parameters = new
                    {
                        biztransactiontypeid = BizTransactionTypeId,
                        applicable = Applicable,
                        nature = Nature,
                        payrevisionpayrevisionnumber = PayRevisionPayRevisionNumber ?? "",
                        employeecode = EmployeeCode ?? "",
                        employeename = EmployeeName ?? ""
                    };

                    return await _QueryExecutor.QueryAsync<TempPayRevisionDTO>(LoginDTO, Sql, Parameters);
                }
                else
                {
                    string Sql = PayRevisionQB.GET_SELECTLIST_PAYREVISION_SCOPE_BASED;

                    var Parameters = new
                    {
                        biztransactiontypeid = BizTransactionTypeId,
                        applicable = Applicable,
                        nature = Nature,
                        payrevisionpayrevisionnumber = PayRevisionPayRevisionNumber ?? ""
                    };

                    return await _QueryExecutor.QueryAsync<TempPayRevisionDTO>(LoginDTO, Sql, Parameters);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}